Monsieur Excel
Pour tout savoir faire sur Excel !

26 décembre 2013

Autre graphe en ligne coloré

Dans le dernier article, nous avons vu comment colorer un graphe en ligne, avec des segments en trois couleurs :
  • Le vert quand les valeurs sont en croissance
  • Le bleu quand elles restent stationnaire
  • Le rouge quand les valeurs décroissent
Cette coloration du graphe en ligne était obtenue à l’aide d’une macro, et cela marchait très bien.

Aujourd’hui, nous présentons la solution proposée par le Frankens team, le groupe d’artistes Excel auquel appartiennent Roberto Mensa et Krisztina Szabó, déjà souvent cités dans ce blog.

Cette solution repose sur la définition d’une série de noms, que vous pouvez consulter dans la partie inférieure droite de la copie d’écran ci-dessous. Il vous faut d’abord créer un graphe en ligne, sans marqueur et avec un trait rouge, défini par la formule en H1. C’est un graphe élastique, qui s’adapte donc au nombre de données présentes dans la colonne A.

Ensuite, sélectionnez ce graphe et collez dans la barre de formule la formule en H2. Faites-en un graphe en ligne vert, sans marqueur.

Puis sélectionnez le graphe et collez dans la barre de formule la formule en H3. Faites-en un graphe en ligne bleu, sans marqueur.

Remarque 1 – Nous verrons dans le prochain article pourquoi nous le traçons en bleu et non en jaune comme cela était le cas dans la version originale Frankens.

Enfin, sélectionnez le graphe et collez dans la barre de formule la formule en H4. Faites-en un graphe en ligne blanc, sans marqueur.

Et, pour terminer, sélectionnez le graphe et collez dans la barre de formule la formule en H5, la même que celle en H1, au numéro de série près ! Faites-en un graphe sans trait, mais avec un marqueur circulaire noir de taille 5.

Remarque 2 – Ce graphe est naturellement en trois couleurs : cela nous évite la macro publiée dans l’article précédent, mais en revanche la création de ce graphe – et en particulier celle des noms utilisés – n’est manifestement pas à la portée de l’utilisateur lambda d’Excel !

20 décembre 2013

Un graphe en ligne « coloré »

Le graphe en waterfall (ou bridge) est un grand classique de la modélisation financière. C’est en fait un histogramme qui représente les recettes en vert et les dépenses en rouge.

Pour en savoir plus sur les waterfalls, lisez donc les articles « Création d’un graphe en waterfall » du 15 août 2011 et « Des graphes en waterfall gratuits ! » du 4 décembre 2011.

Sur le même principe, Brian Canes a proposé d’appliquer ce concept à un simple graphe en ligne : quand une valeur est supérieure à la valeur précédente, on colore le segment en vert ; quand elle est inférieure, en rouge ; et quand elle ne change pas, en bleu :


Une petite macro de quelques lignes (cf. copie d’écran ci-dessus) vous permet de transformer n’importe quel simple graphe en ligne en « graphe en ligne coloré ».

14 décembre 2013

Marre d’être espionné ?

Aujourd’hui, tout ce qui transite par Internet est vulnérable. Nous souffrons déjà, sans forcément le savoir, de la vulnérabilité de nos données face à la curiosité insatiable de la NSA (« National Security Agency », officiellement –
«
No Secrets Anymore
» en réalité). Par ailleurs, nous subissons aussi l’espionnage permanent institutionnalisé par Google, Facebook, LinkedIn, …  et tant d’autres.

S’il y a une chose dans Excel qui soit pire encore que son « Aide », c’est bien la protection assurée par les mots de passe « protégeant » les feuilles, les classeurs et le VBA.

C’est pourquoi j’ai mis au point un produit, que je suis en train de finaliser, dont l’objectif sera de coder vos données de telle façon que même la NSA avec tous ses ordinateurs, ne pourra les décrypter.


Nous voyons ci-dessus le modèle original, dans lequel nous avons décidé de coder la partie en fond jaune. En dessous de cette copie d’écran, nous voyons le résultat obtenu après codage. Nous avons élargi les colonnes afin que vous puissiez voir le contenu complet de chaque cellule.

Les entreprises qui sont intéressées par cette application peuvent me contacter à thiriez@hec.fr.

09 décembre 2013

1er argument de « Sous-total »

Nous avons vu dans l’article précédent que le premier argument de la fonction sous-total() était « 9 ». Les valeurs possibles pour ce premier argument sont les valeurs situées dans les intervalles 1-11 et 101-111.

Les valeurs de 1 à 11 de ce premier argument correspondent exactement aux mêmes fonctions que le premier argument (le n° de fonction) de la fonction agregat().

Quand on ajoute 100 à ce premier argument, on ignore le contenu des lignes qui sont masquées à l’écran. On retrouve là aussi une des possibilités de la fonction agregat(), quand le n° d’option prend une des valeurs 1, 3, 5 ou 7.

Il y a donc une forte corrélation entre les modes de fonctionnement de ces deux fonctions.


En conclusion, et en accord avec le commentaire posté par Claude Duigou dans l’article précédent, je ne vois pas bien ce qu’apporte la fonction sous.total(), surtout maintenant que nous avons la fonction agregat()

Cette dernière est bien plus riche, le seul élément négatif étant – comme je l’avais noté dans mon article du 24 novembre – le très mauvais choix effectué par Microsoft dans le paramétrage de l’argument n° d’option.

04 décembre 2013

La commande « Sous-total »

Dans l’onglet « Données », il y a une commande dont nous n’avons encore jamais eu l’occasion de parler dans ce blog, la commande « Sous-total ».

Prenons une base classique, comme vous pouvez le voir dans la copie d’écran ci-dessous, cliquons n’importe où dans cette base et appelons la commande « Sous-total ».


Après avoir coché toutes les dates dans le dialogue (comme ci-dessus), puis validé par « OK », nous obtenons le résultat ci-dessous, dans lequel Excel a inséré une ligne pour chaque sous-total de région et ajouté une ligne finale pour le total général.


Il a en outre créé un plan grâce auquel nous pouvons, en cliquant sur le « 2 », ne garder que les sous-totaux et le total général ou, en en cliquant sur le « 1 », ne garder que ce dernier. Enfin, en cliquant sur le « 3 », on réaffiche tout…

Il a aussi entré des formules dans les lignes de totalisation :
En C5 : =sous.total(9;C2:C4)
En C18 : =sous.total(9;C2:C16)

Nous découvrons là un intérêt notable de la fonction sous.total() : elle ne prend pas en compte les valeurs intermédiaires dues aux autres utilisations de cette fonction. Si, en ligne 18, nous avions calculé la somme avec la formule =somme(C2:C16), nous aurions obtenu un résultat presque deux fois trop grand…

Nous expliquerons dans le prochain article pourquoi le premier argument de la fonction est un « 9 »…