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…

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 !

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 !

Modalités ou valeurs différentes ?

Nous souhaitons calculer en C1 le nombre de valeurs différentes – en ne s’intéressant donc qu’aux contenus numériques –, et en C2 le nombre de contenus différents, c’est-à-dire en prenant aussi les textes comme références.

Formule de C1 : =somme(si(frequence(A1:A13;A1:A13)>0;1))

Formule de C2 : =sommeprod(1/nb.si(A1:A13;A1:A13))

Remarque 1 – Il est important de signaler que ces deux formules n’ont pas besoin d’être validées comme formules matricielles : une simple validation leur suffit ! Elles marchent encore en matriciel, mais ce n’est pas nécessaire…

Remarque 2 – Il est intéressant de noter le comportement étrange de C2 quand on efface le contenu d’une cellule de la liste. Parfois, le bon résultat est obtenu, parfois le résultat n’est pas entier, parfois enfin l'on obtient le message d’erreur #DIV/0!

J’obtiens les trois cas de figure, selon les tirages, quand j’utilise des valeurs aléatoires pour les nombres avec la formule =ent(10*alea()) et en effaçant l'une des cellules, par exemple A9. En lançant des calculs consécutifs avec [F9], on observe alors les trois cas de figure.

Un problème avec les tris

Il faut se méfier des tris car les cellules triées changent de position après le tri, mais les références à des cellules concernées par le tri ne sont pas actualisées pour autant !

On le voit bien dans l’exemple ci-dessous. Les cellules G3 et G5 font référence – l’une en relatif et l’autre en absolu – à des cellules du bloc A1:D8. Nous les avons mises en couleur afin que l’on puisse bien voir comment cela se présente.

Après le tri, G3 et G5 affichent des résultats ne correspondant pas du tout aux étiquettes en F3 et F5.

Ce qui est amusant, c’est que les formats de nos deux cellules de référence ont bien été déplacés dans le tri, comme leur contenu. En revanche, leurs références en G3 et G5 n’ont pas été actualisées !

Si vous voulez être sûr que votre modèle reste valable même après un tri, la formule de G3 doit être bien plus lourde :
=index(B2:D8;equiv("Dépt. 2";A2:A8;0);equiv("Prod 2";B1:D1;0)).

Et l’on doit faire de même pour G5…