Monsieur Excel
Pour tout savoir faire sur Excel !

23 mai 2015

Formats conditionnels (a)

Les formats conditionnels d’Excel sont apparus pour la première fois en 1997. Il était alors possible de définir jusqu’à trois formats conditionnels d’un coup pour une cellule ou un groupe de cellules.

Les formats conditionnels sont extrêmement utiles, et ce pour de multiples raisons. Ils permettent par exemple de mettre en relief les valeurs les plus grandes, ou les plus petites, ou les doublons,… ou je ne sais quoi. La seule limite réelle est celle de votre imagination !

Avec Excel 2007 et l’apparition du ruban, le fonctionnement des formats conditionnels a été fortement modifié. On peut en particulier noter trois changements significatifs : la grande variété des commandes, la possibilité de modifier le format numérique, et le nombre bien plus grand de conditions possibles.

La grande variété des commandes

Comme le montre la copie d’écran ci-dessous, provenant d’un Excel 2010, il y a maintenant un grand nombre de commandes menant à des sous-commandes conduisant elles-mêmes à des dialogues, ce qui est indiqué par les «  » terminant le nom des sous-commandes.


Cette grande variété des commandes pose un problème. Avec toutes les commandes et les sous-commandes, on peut effectivement définir une grande variété de formats conditionnels, mais cela cache un piège majeur dont nous parlerons dans le prochain article.

La possibilité de modifier le format numérique

Jusqu’à Excel 2003, le format conditionnel permettait de modifier la police, le style, la couleur de la police ou du fond, et enfin l’encadrement.

Depuis Excel 2007, on a aussi accès dans via le format conditionnel à la commande « Nombre », ce qui permet de modifier aussi le format numérique, de la même façon qu’on peut le faire avec les formats numériques standards ou personnalisés.

Nombre de conditions possibles fortement augmenté

Jusqu’à Excel 2003, le format conditionnel permettait de définir trois conditions au maximum, donc de disposer pour une cellule de quatre formats possibles. Cette limite a sauté avec Excel 207 et l’on peut à présent définir un grand nombre de formats conditionnels. Je ne suis pas parvenu à en trouver la limite sur Internet, mais elle doit être conséquente.

Ceci dit, cela la possibilité de définir de multiples formats conditionnels pose aussi un problème sérieux, dont nous parlerons aussi dans le prochain article.

16 mai 2015

Formats personnalisés (c)

Dans les deux articles précédents, nous avons analysé les deux syntaxes possibles pour les formats personnalisés. La seconde syntaxe permet de prendre des libertés avec l’affichage de résultas dans Excel.

Nous avons repris ci-dessous le bloc A1:D5 utilisé dans le premier article. Puis nous avons entré en A8, recopiée vers la droite, la formule =A1. Et nous avons donné au bloc A8:D8 le format :
"-9 876,54";"Alfred";"1 234,56";"-56,78"


Remarque – Pour confondre encore mieux l’utilisateur, nous avons cadré la cellule B8 à gauche et la cellule D8 à droite. En effet, leurs valeurs réelles avaient entraîné des cadrages incohérents avec le résultat affiché…

Quand nous consultons la ligne 8, nous voyons que nous pouvons forcer Excel à afficher n’importe quoi, et tout cas un résultat très éloigné du résultat attendu !

Ceci dit, Excel ne set trompe pas et ne se fie qu’à la valeur réelle des cellules. Ainsi, quand nous entrons en A12 (recopiée à droite) la formule =A8 et que nous donnons un format Standard à la ligne 12, nous voyons que le résultat n’est pas faussé et que les vraies résultats attendus sont affichés.

Il y a aussi des utilisations sérieuses de cette possibilité de modifier le formatage. Par exemple, en A14 dont le contenu est 0,2, en utilisant le format reproduit en C14, nous parvenons à afficher à la fois un texte et un paramètre dans la même cellule. Et, pour mieux tromper le touriste, nous avons cadré la cellule A14 à gauche…

Il y a de multiples usages pour les formats personnalisés « trompeurs ». On peut par exemple utiliser un format personnalisé pour cacher un paramètre « sous » un texte, ce qui est plus léger que de devoir passer par un nom et qui représente une bonne façon de protéger un paramètre pour qu’il ne soit pas modifié par n’importe qui…

10 mai 2015

Formats personnalisés (b)

Dans l’article précédent, nous avons vu que les formats personnalisés d’Excel correspondaient – dans l’ordre – à « positif;négatif;nul;texte ».

En vous disant cela, j’ai fait – pour que ce premier article ne soit pas trop long – un mensonge par omission…

Il y a en effet deux types de formats personnalisés dans Excel. Le premier correspond à la définition présentée dans l’article précédent.

Le second type de format personnalisé correspond, dans l’ordre, à :
[condition 1]format 1;[condition 2]format 2;format 3

Les conditions doivent être encadrées de crochets droits. Il peut y avoir au maximum trois formats, et donc deux conditions.

Nous voyons dans le tableau ci-dessous deux exemples d’applications. Dans ces tableaux, la formule de B2 est =A2, et elle a été reproduite vers le bas.


Dans le bloc B2:B6, nous constatons l’effet d’un format personnalisé qui – selon la valeur – affiche le résultat en , en k€ ou en M€.

Remarque 1 – Attention ! Vous n’avez pas le droit de saisir « >=10^6 » pour la première condition. Le symbole d’exponentiation n’est pas reconnu…

Dans le bloc B8:B13, nous voyons comment des notes de 0 à 20 peuvent être évaluées avec un A pour les bonnes notes, un B pour les notes passables, et la valeur avec une décimale pour les mauvaises notes.

Remarque 2 – Les formats personnalisés avec des conditions ne peuvent gérer que des conditions simples. Il n’est par exemple pas possible de faire référence à une cellule dans une condition.


04 mai 2015

Formats personnalisés (a)

Dans l’article précédet, nous nous sommes intéressés au formatage en k€ ou en M€.

Au sujet du formatage, je constate souvent – quand j’interviens en entreprise comme consultant – que de nombreux utilisateurs ne maîtrisent pas bien les formats personnalisés d’Excel.

La commande « Format personnalisé » d’Excel donne accès à l’écran dont la partie supérieure est reproduite dans l’encart bleu de la copie d’écran ci-dessous.


Nous avons entré =1000*pi() en A2, =-A2 en B2, =somme(A2:B2) en C2 et un texte en D2.

Nous avons formaté les lignes 3 à 6 en utilisant, depuis le bloc « Nombre » de l’onglet « Accueil », les outils de formatage en euros, dollars, séparateur des milliers et pourcentages. On peut se poser la question de savoir quel format Excel a précisément utilisé quand on sélectionne un de ces formats. Pour le savoir, cliquez dans la cellule, passez par la commande « Format de cellule » et sélectionnez la dernière option, « Personnalisé », comme dans notre copie d’écran.

Vous constatez ainsi, avec les formats reproduits en colonne E, que les deux formats monétaires utilisés par Excel dans le ruban sont bien plus complexes que ce que l’on pourrait croire a priori.

Comment interpréter les formats personnalisés?

Pour moi, deux choses sont particulièrement importantes à comprendre en ce qui concerne les formats personnalisés d’Excel.

Un « 0 » représente un chiffre obligatoire, un « # » représente un chiffre facultatif. Ainsi, la valeur 3,6 est représentée en 4 par le format « 0 », en « 3,60 » par le format « 0,00 » et en 03,6 par le format « 00,## ».

Il peut y avoir jusqu’à quatre formats séparés par des « ; ». Ces quatre formats, quand ils sont tous présents, correspondent – dans l’ordre – à « positif;négatif;nul;texte ». Comme par hasard, c’est l’ordre dans lequel j’ai rangé les éléments dans les colonnes A à D.

Remarque 1 – Si vous voulez entièrement masquer le contenu d’une cellule, le format à utiliser est « ;;; ». C’est plus invisible qu’une police blanche sur fond blanc. En effet, dans ce dernier cas, le contenu de la cellule devient visible par contraste quand elle fait partie d’une sélection.

Remarque 2 – Si vous voulez savoir à quoi correspondent tous les autres codes utilisés dans les formats, consultez l’aide d’Excel…

28 avril 2015

Formatage direct en k€ ou en M€

Pour faire la suite à l’article précédent qui parlait de formats, il est peut-être temps de reprendre un des premiers articles de ce blog, datant du 10 janvier 2006. Cet article a eu beaucoup de succès, avec déjà vingt commentaires.

Trop souvent, dans les modèles, pour afficher une valeur en kilo-euros ou en méga-euros – je trouve les termes « keuros » ou « meuros » très laids – les utilisateurs d’Excel divisent les valeurs par 1.000 et 1 million, pour ensuite les re-multiplier par ces valeurs quand il leur faut récupérer le montant original.
Cela n’est pas efficace car, en divisant puis en re-multipliant par 1 million, on réduit la précision finale d’Excel. En outre, cette solution est très lourde.

La solution la plus efficace revient à créer un format personnalisé. Pour cela, il faut savoir que, dans un format personnalisé, tout espace placé hors de guillemets – sauf dans le cas précis des formats de type # ##0 – provoque une division par 1.000.

Dans le tableau ci-dessous, nous avons entré un montant en A1, qui a ensuite été recopié jusqu’en B3. Dans la colonne B, nous avons utilisé deux formats personnalisés...


Le format de B2 contient un espace avant les guillemets : # ##0,00 " k€"

Celui de B3 contient deux espaces avant les guillemets : # ##0,00  " M€"

Notez au passage que, même sur ces formats personnalisés, les boutons « Ajouter une décimale » et « Réduire les décimales » continuent à fonctionner.

Pourquoi est-ce l’espace qui joue ce rôle de division ?

Tout simplement car c’est le caractère de séparation des milliers dans les codes de formatage de l’Excel français. Si vous utilisez un Excel anglais, c’est le format #,##0.00," k$" que vous utiliserez donc pour les milliers de dollars.

Le même principe s’applique aux formats non monétaires, comme nous pouvons le voir avec le bloc A7:A8…

22 avril 2015

Format numérique en fraction

Un format numérique assez peu connu, et donc peu utilisé, est le format en fraction. Quand on choisit de format, on obtient le choix entre les options représentées dans le rectangle bleu en bas de la copie d’écran.

En fait, plutôt que de passer par cette option, quand on sait ce que l’on fait, il est plus rapide de passer directement par la commande « Format personnalisé ».

Nous avons représenté dans la copie d’écran les résultats obtenus pour deux fractions, grâce à la formule =A$1/A$2 entrée en A5 et recopiée à droite.

Nous constatons ce que cela donne en A6:B10 avec les formats indiqués en C6:C10.


Le principe est simple : chaque « ? » représente un chiffre.

Excel recherche le résultat le plus proche. On constate ainsi dans la cellule B7 que, malgré le format à deux chiffres en haut et en bas, le résultat est une fraction entre nombres à un chiffre : cela signifie que ce rapport est le plus proche : Excel, dans ce cas, affiche intelligemment 3/8 au lieu de 30/80.

Notons que l’on peut mettre un nombre quelconque comme dénominateur. Excel trouvera alors le numérateur associé le mieux adapté.

Remarque – Si l’on essaye de faire l’inverse en figeant le numérateur, Excel n’en tient aucun compte et ne signale pas que le format est erroné. Pire encore, il affiche n’importe quoi ! Ainsi, quand on utilise le format #" "125/??? en B10, il affiche 125/115, ce qui n’a aucun rapport avec le 125/333 que l’on y obtenait avant !


16 avril 2015

Modalités, valeurs différentes (b)

Dans l’article précédent, nous avons vu comment – à l’aide de formules non matricielles – calculer le nombre de modalités différentes dans un vecteur vertical, et le nombre de valeurs différentes.

Aujourd’hui, nous vous proposons deux formules nouvelles, matricielles.

Formule de D1 : =somme(si(estnum($A$1:$A$13)*ligne($1:$13)=equiv($A$1:$A$13;A:A;0);1))
Formule de D2 : =somme(si(ligne($1:$13)=equiv($A$1:$A$13;A:A;0);1))
Ces deux formules sont matricielles, donc validées avec [Ctrl]-[Maj]-[Entrée].

Tant qu’il n’y a aucune cellule vide dans le vecteur, les résultats sont identiques à ceux de C1:C2.


On peut aussi les écrire sans utiliser le si() :

Formule de D1 :
=somme(estnum($A$1:$A$13)*(ligne($1:$13)=equiv($A$1:$A$13;A:A;0);1)))
Formule de D2 : =somme(1*(ligne($1:$13)=equiv($A$1:$A$13;A:A;0);1)))

Ce qui est étrange, c’est ce qui se passe quand il y a une cellule vide dans le vecteur :
  • quand il n’y a aucun 0, C2 donne #DIV/0!, D1 et D2 donnent #N/A
  • quand C2 donne un résultat non entier, D1 et D2 donnent le bon résultat.
  • quand C2 affiche un résultat entier, C2 affiche un nombre trop grand d’une unité car il compte la cellule vide comme une modalité.
Nous avons donc deux solutions, grâce aux formules en C1:C2 et D1:D2. Mais aucune des deux ne parvient à bien gérer le cas où au moins une cellule du vecteur est vide…

Si quelqu’un a une bonne idée pour résoudre ce problème, nous afficherons sa solution !