Calcul du nombre de modalités

Remarque – Même si le calcul du nombre de modalités ne présente pas pour vous un intérêt particulier, vous pourrez lire cet article avec intérêt si vous vous intéressez aux formules matricielles ou à l’une des fonctions suivantes : SommeProd, Equiv, Fréquence ou Nb.Si.

Excel propose 12 fonctions de bases de données nommées BD…(). Il manque la 13^ème. Microsoft serait-elle donc une société superstitieuse ? En effet, une fonction très utile dans cette famille serait la fonction BdNbMod() qui compterait le nombre de modalités (c’est-à-dire de valeurs différentes) dans une colonne donnée.

Pour combler cette lacune, je vous propose dans cet article 6 façons différentes de combler cette lacune, avec 3 paires de solutions, chaque paire étant constituée par une formule matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée], et une formule un chouïa plus longue, mais non matricielle.

La fonction Frequence()

Notre première solution (D1 et D2) utilise la fonction frequence(), une fonction maîtrisée par peu d’utilisateurs Excel.

La formule equiv(A2:A14;A2:A14;0) donne {1;2;3;4;5;1;3;8;3;10;11;1;4}, c’est-à-dire la place de la première occurrence de la valeur courante dans le vecteur.

La formule FREQUENCE(EQUIV(A2:A14;A2:A14;0);EQUIV(A2:A14;A2:A14;0))>0 nous donne ici le résultat {VRAI;VRAI;VRAI;VRAI;VRAI;FAUX;FAUX;VRAI;FAUX;
VRAI;VRAI;FAUX;FAUX;FAUX}, avec VRAI chaque fois que l’occurrence est la première. On calcule donc le nombre de fois où l’occurrence est la première, ce qui est le nombre d’occurrences différentes.

Pour cet exemple comme pour les deux suivants (D3:D4) et (D5:D6), la seconde formule utilise la fonction SommeProd() pour éviter d’avoir à saisir la formule comme matricielle.

La fonction Equiv()

Là, c’est tout simple, si vous savez comment fonctionne la fonction equiv(… ;0)…

Nous comptons tout simplement le nombre de fois où la position de la première occurrence de la valeur actuelle est bien celle de la ligne actuelle.

Nous avons vu ci-dessus comment la fonction ()Equiv() était évaluée. Le reste ne devrait pas vous poser de problème.

La fonction Nb.Si()

Cette troisième solution est la plus compacte et, comme la solution précédente, elle aussi très astucieuse.

La formule NB.SI(A2:A14;A2:A14) s’évalue {3;1;3;2;1;3;3;1;3;1;1;3;2}. En divisant 1 par ce vecteur, on obtient le vecteur {0,333;1;0,333;0,5;1;0,333;0,333;1;0,333;1;1;0,333;0,5}. Ce qui fait donc un total de 1 pour chaque modalité différente !

En guise de conclusion

La richesse d’Excel transparaît bien dans l’exercice de ce jour. Non seulement nous avons 6 formules différentes pour résoudre notre problème – et l’on pourrait aisément en trouver d’autres encore ! – mais nous avons aussi le choix entre des formules naturelles et des formules matricielles.

Ces dernières sont plus compactes mais un peu plus dangereuses : si un utilisateur les modifie puis les revalide en oubliant de les valider en formule matricielle, il cassera ce joli jouet !