Monsieur Excel
Pour tout savoir faire sur Excel !

30 novembre 2012

Tracé efficace d’un histogramme

Le modèle reproduit ci-dessous représente les éléments permettant de trouver – à l’aide d’une recherche dichotomique (dont nous parlerons un autre jour) – le prix de vente qui permettra de maximiser la marge bénéficiaire.

Nous ne nous intéressons pas ici aux formules utilisées dans les lignes 13 à 18, mais ce ne devrait pas être trop dur pour vous de retrouver ces formules…

Ce qui nous intéresse ici, c’est la façon efficace de créer le graphe représenté dans le coin supérieur droit, avec les marges en fonction du prix de vente.

Nous avons tout d’abord, en bas à gauche, représenté le graphe obtenu après avoir sélectionné – grâce à la touche [Ctrl] – les blocs B13:F13 et B18:F18.

Problème – Excel a cru que le premier bloc, puisqu’il est intégralement numérique, représentait une première série et – du coup – a tracé deux séries sans étiquettes des X autres que les étiquettes par défaut 1, 2, 3 …

Il va donc falloir perdre du temps à détruire la première série, puis indiquer à Excel où se trouvent les étiquettes des X.

En bas à droite, nous avons le graphe obtenu après avoir sélectionné le seul bloc B18:F18. Du coup, nous avons un progrès par rapport au cas précédent, puisqu’il ne reste plus que la seconde étape, indiquer à Excel où se trouvent les étiquettes des X.

Morale de l’histoire – La première des deux solutions était la plus naturelle, car elle prenait à la fois les étiquettes des X et les valeurs des Y. Mais, à l’usage, c’est la seconde solution – pourtant moins naturelle ! – qui nous fait perdre le moins de temps…

Remarque – En fait, dans les versions anciennes d’Excel (il faut au moins remonter de 15 ans en arrière !), quand les étiquettes des X étaient numériques, une case à cocher ou un dialogue vous demandait s’il s’agissait des étiquettes des X ou d’une première série. Le problème était donc résolu en un instant… Quelle régression aujourd’hui par rapport à cela !

C’est bien pour cela que je déclare – et ce depuis des années ! – que chaque nouvelle version d’Excel, tout en apportant son lot d’améliorations, apporte en même temps un lot presque égal de régressions. Cela m’anène à avoir une réaction totalement schizophrène : l’utilisateur d’Excel que je suis peste contre ces régressions, et le consultant que je suis aussi y trouve du grain à moudre pour ses clients…


26 novembre 2012

Couleurs alternées par client

Nous disposons d’un modèle dans lequel il y a – après un tri sur les clients puis sur les dates – un nombre variables de lignes par client. Nous souhaitons faire alterner automatiquement la couleur de fond à chaque nouveau client.


Nous avons pour cela sélectionné le bloc A2:E17 et avons entré le format conditionnel suivant pour les cellules à fond jaune : =mod(arrondi(somme(1/nb.si($A$2:$A2;$A$2:$A2)););2)

Cette solution a été proposée par Roberto Mensa, qui a déjà eu l’occasion d’alimenter notre blog avec ses suggestions créatives.

Remarque 1 – Roberto joue souvent au jeu consistant à trouver les formules les plus compactes, au détriment parfois de leur lisibilité. C’est ainsi que – pour économiser un caractère – il n’a pas entré de second argument à la fonction arrondi(), sachant que « 0 » est la valeur par défaut de cet argument !

Remarque 2 – Pour comprendre l’utilisation faite ici de la fonction nb.si(), voyez l’article précédent, dans lequel nous l’avons utilisée de la même façon…

Remarque 3 – Pour que vous puissiez mieux comprendre la formule utilisée pour le format conditionnel, nous l’avons reprise en colonne G. Mais en l’entrant cette fois-ci en formule matricielle, alors que ce n’est ni possible – ni, heureusement, nécessaire ! – à l’intérieur du format conditionnel…

21 novembre 2012

Calcul du nombre de modalités

Nous avons déjà publié le 1er juin 2007 un article intitulé « Calcul du nombre de modalités » où nous montrions comment calculer le nombre de valeurs différentes dans une colonne.

Cette fonction est en effet une fonction qui manque cruellement à la série des fonction DB…().

Aujourd’hui, nous rouvrons le sujet pour vous montrer trois façons de définir cette fonction.

Formule de D1 : =somme(si(equiv(A2:A15;A:A;0)=ligne(A2:A15);1;0))
Formule de D2 : =somme(1*(equiv(A2:A15;A:A;0)=ligne(A2:A15)))
Formule de D3 : =sommeprod(1/nb.si(A2:A15;A2:A15))

Les deux premières formules sont matricielles, donc validées avec [Ctrl]-[Maj]-[Entrée].

La première formule reprend la solution proposée dans l’article publié il y a cinq ans. Elle est assez compréhensible : quand le numéro de la ligne est le numéro de la première ligne où l’on trouve la valeur considérée, c’est la première occurrence de cette valeur et cela compte donc pour 1.

La seconde formule montre que l’on peut très bien remplacer le « si », dans une telle situation, par un simple produit par « 1 », qui a la vertu de transformer les « VRAI » en « 1 ».

La troisième formule présente le double avantage d’être plus compacte et de ne pas être une formule matricielle. Nous y notons une particularité : le second argument, au lieu d’être un élément unique, comme c’est normalement prévu dans le mode d’emploi de la fonction, est en fait un vecteur.

Quant nous évaluons la partie nb.si(A2:A15;A2:A15) de la formule, nous obtenons le vecteur {2;1;4;3;1;2;4;1;4;1;1;4;3;3}. On va donc compter 1/ 2 pour chaque élément apparaissant deux fois, 1/3 pour chaque élément apparaissant trois fois,… Au total, nous aurons donc le nombre de modalités.

Remarque 1 – Ce n’est pas la première fois que nous découvrons des ressources inespérées d’Excel en « détournant » le mode d’emploi officiel d’une fonction…

Remarque 2 – Il y a enfin, assez logiquement, une formule encore plus courte, mais de nouveau matricielle, avec =somme(1/nb.si(A2:A15;A2:A15)). Ce n'est pas surprenant car, comme nous l'avons déjà expliqué à plusieurs reprises, la fonction sommeprod() sert souvent à « éviter » une formule matricielle puisqu'elle génère naturellement une boucle...

16 novembre 2012

Trouver la énième occurrence


Dans l’article « Trouver la seconde occurrence » du 24 mai 2007, nous avons montré une solution pour trouver dans un vecteur la position de la seconde ou de la troisième occurrence d’une valeur ou d’un texte.

Quelques jours plus tard, avec l’article « Où est la dernière occurrence ? » du 28 mai 2007, nous avons montré comment trouver la toute dernière occurrence.

Dans le premier cas, chaque position était identifiée à partir de la position de l’occurrence précédente avec des formules directes. Dans le second cas, il fallait utiliser une formule matricielle.


Dans l’exemple ci-dessus, nous avons entré la formule suivante en C1, qui a été ensuite recopiée vers le bas : =sierreur(petite.valeur(si($A$1:$A$20=$B$1;ligne($A$1:$A$20);"");ligne());"")

C’est une formule matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

Remarque 1 – Comme nous l’avons dit dans l’article précédent, si nous utilisons Excel 2003 ou une version antérieure, nous serons forcés de remplacer sierreur(…) par si(esterreur(…) ;… ;…) en dupliquant de fait la formule entière.

Remarque 2 –  Il y a bien entendu d’autres solutions possibles, par exemple, en version plus longue mais compatible Excel 2003 : =si(ligne()<=nb.si(A:A; $B$1);petite.valeur(si($A$1:$A$20=$B$1;ligne($A$1:$A$20);"");ligne());"")

12 novembre 2012

La fonction sierreur()


La fonction sierreur() est une des nouvelles fonctions d’Excel 2007. Elle teste une expression et, si cette expression aboutit à une erreur, affiche à sa place le résultat que vous voulez.

La syntaxe de la fonction est =sierreur(valeur;valeur_si_erreur)

L’aide d’Excel souligne le fait que le premier argument peut être une formule matricielle, mais en fournissant un exemple totalement ridicule où l’on ne voit pas ce que la formule matricielle apporte par rapport à la simple recopie vers le bas d’une formule simple, non matricielle. Pourquoi faire simple quand on peut faire compliqué !

Dans l’exemple ci-dessus, nous avons entré en C2 la formule =sierreur(A2/B2; ""). Cette formule nous a servi à filtrer les erreurs #DIV/0! en C4 et #NA! en C8.

La fonction sierreur() est très pratique pour éviter – comme on le voit souvent dans des modèles professionnels – le doublement des expressions dans le style :
=si(estna(index(G:G;equiv(A3;B:B;0)));"";index(G:G;equiv(A3;B:B;0)))

En effet, avec de tells formules, l’expression index(G:G;equiv(A3;B:B;0)) sera calculée en général deux fois, la première pour vérifier s’il y a erreur, la seconde pour engendrer le résultat.

Cette fonction est très utile et permet donc d’alléger vos modèles, en taille comme en temps de calcul. Elle comporte cependant deux écueils :

  • elle ne permet pas de filtrer selon le type d’erreur, comme on peut le faire une fonction comme estna() qui permet de ne filtrer que les erreurs de type #NA!
  • elle aboutira à une erreur  #NOM! quand votre modèle sera ouvert avec une version d’Excel antérieure à la version 2007.

08 novembre 2012

La commande « Remplacer »

La commande « Remplacer », au même titre d’ailleurs que la commande 
« Rechercher », reconnaît les deux jokers usuels :

- le « ? » pour exactement un caractère, une lettre, un chiffre ou un caractère typographique 

- le « * » pour n’importe quoi, c’est-à-dire n’importe quels caractères et n’importe quelle longueur.

Ces mêmes jokers fonctionnent de la même façon dans Word, Powerpoint, dans l’Explorateur de fichiers,...


Donc, quand vous décidez de remplacer « t?t? » par « toto », vous remplacez « titi » ou « tata
» par « toto », mais pas le texte « tétin » par « toton » car il y a dans ce cas une lettre de trop. Ce dernier changement aurait en revanche eu lieu si l’on avait remplacé le texte « t?t?* » par « toto* ».

Un énorme bug...

Depuis qu’Excel existe, et bien entendu malgré mes remarques – dès le début – à Microsoft, il y a dans Excel un énorme bug, qui n’a jamais été corrigé.

Ce bug, c’est que – dans Excel – les jokers sont reconnus comme tels dans le champ « Rechercher : » mais pas dans le champ « Remplacer par : » où – et c’est complètement idiot ! – ils sont pris à la lettre.

Si donc vous décidez de remplacer « f* » par « p* », pour ne modifier que l’initiale de ce qui débute par un « f », tout ce qui commence par « f » sera remplacé – quel que soit la continuation du texte – par la chaîne de deux caractères « p* » !

Peut-être ce bug sera-t-il corrigé pour le cinquantième anniversaire d’Excel ?

Effet du remplacement

Le remplacement s’effectue dans les cellules sélectionnées dès que plus d’une cellule a été sélectionnée. Sinon, il s’effectue dans toute la feuille et même dans toutes les feuilles sélectionnées si vous en avez sélectionné plusieurs.

Un conseil utile – Pour effectuer plusieurs remplacements dans une cellule unique – par exemple y remplacer toutes les références à $A$1 par $B$2 – sélectionnez à la fois la cellule visée et une cellule voisine vierge ou ne contenant pas la chaîne de caractères à remplacer.

Un conseil important – Chaque fois que vous effectuez un remplacement, pensez à vérifier le nombre de remplacements effectués : cela vous permettra de voir immédiatement s’il y a une grosse erreur dans votre remplacement.

Heureusement, avec [Ctrl]-z, vous pouvez toujours annuler le remplacement abusif que vous auriez pu effectuer.

04 novembre 2012

La commande « Rechercher »

La commande « Rechercher » affiche le dialogue suivant avec ses trois principaux menus déroulants dans la partie inférieure du dialogue, dont vous pouvez voir les options reproduites sous la copie l’écran de la fenêtre « Rechercher et remplacer ».
On peut donc effectuer une recherche dans la feuille ou dans le classeur. En fait, on peut faire mieux encore en effectuant la recherche dans un ensemble de feuilles sélectionnées grâce à la touche [Ctrl] (pour ajouter ou ôter une feuille) et/ou à la touche [Maj] (pour sélectionner toutes les feuilles entre la feuille précédente et la feuille sélectionnée, bornes comprises).

Si l’on désire par exemple trouver dans une feuille toutes les cellules utilisant la fonction equiv(), il suffit de rechercher « iv( ». En effet, le « v » seul peut aussi être associé à recherchev()

Si l’on désire trouver les références à d’autres feuilles, il suffit de rechercher « ! ». Pour trouver les références à un autre classeur, cherchez « [ «  ou « ] »…

Avec la clef « he?( », vous trouverez les utilisations de rechercheh() et de recherchev(), mais pas celles de cherche() ni celles de recherche(). En revanche, avec « he*( », vous trouverez toutes les utilisations de ces quatre fonctions.

Si par exemple vous souhaitez trouver toutes les cellules comportant un texte qui se termine par 2012, et non celles qui contiennent la valeur 2012, il faut chercher « _2012 ». Ne tapez surtout pas l’underscore « _ », je l’ai mis ici uniquement pour indiquer qu’il faut alors taper un espace avant « 2012 »…

Enfin, pour rechercher précisément un des deux jokers, il faut utiliser, selon le cas,  « ~? » ou « ~* ».