Monsieur Excel
Pour tout savoir faire sur Excel !

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 !

10 avril 2015

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.


04 avril 2015

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…

29 mars 2015

Somme.Si.Ens() & Nb.Si.Ens()

Avec Excel 2007, Microsoft nous a donné deux nouvelles fonctions de calcul, qui enrichissent les fonctions Somme.Si() et Nb.Si() qui ont fait l’objet de nos articles récents du 17 et du 23 mars.

Il s’agit des fonctions Somme.Si.Ens() et Nb.Si.Ens() dont l’aide ne dit pas ce que signifie le « Ens » ainsi ajouté : Ensemble ?

L’exemple ci-dessous illustre leur utilisation avec à titre de comparaison le même résultat obtenu à chaque fois avec une fonction BD…().


Les formules utilisées sont les suivantes :

Personnes de 27 ans :
I5 : =BDSOMME(A1:F26;E1;H1:H2)
I6 : =SOMME.SI.ENS(E:E;D:D;27)
I11 : =BDNB(A1:F26;E1;H1:H2)
I12 : =NB.SI.ENS(D:D;27)

Hommes de 27 ans :
I8 : =BDSOMME(A1:F26;E1;H1:I2)
I9 : =SOMME.SI.ENS(E:E;D:D;27;A:A;"m")
I14 : =BDNB(A1:F26;E1;H1:I2)
I15 : =NB.SI.ENS(D:D;27;A:A;"m")

Remarque 1 – En fait, ces nouvelles fonctions ne font rien que l’on ne sache déjà faire avec une fonction BD…() mais elles évitent la création d’une zone de critères comme H1:H2 ou H1:I2, selon le cas. Ceci dit, cette simplification a un inconvénient car elle aboutit à une moindre lisibilité du modèle : il faut en effet voir la formule pour comprendre d’où vient le résultat.

Remarque 2 – Faites très attention, car le champ sur lequel on fait la somme est en première position dans Somme.Si.Ens() alors qu’il était en troisième position dans Somme.Si(). Microsoft a été obligé de changer sa place car il n’y a qu’un champ de sommation alors qu’il peut y avoir plusieurs doublons « colonne;valeur ».

23 mars 2015

Somme.Si() : des variantes

Pour en terminer avec les fonctions somme.si() et nb.si(), il faut savoir que la condition exprimée dans le second argument doit toujours être une condition unique. Si ce n’est pas le cas, il ne vous reste plus qu’à utiliser soit une formule matricielle – dont nous avons vu de multiples exemples dans ce blog –, soit la fonction SommeProd(), dont nous avons aussi parlé à de multiples reprises.

Même si somme.si() et nb.si() nous limitent à une condition unique, il est tout à fait possible d’exprimer, à travers une telle condition, une grande variété de conditions simples. Nous vous en proposons deux exemples ci-dessous, en C16 et en C18 :


En C16, nous avons calculé le chiffre d’affaires réalisé avec les commerciaux autres que A, grâce à la formule : =somme.si(D2:D10;"<>"&C12;E2:E10).

En C18, nous avons calculé la somme des chiffres d’affaires supérieurs à la moyenne, grâce à la formule : =somme.si(E2:E10;">"&moyenne(E2:E10);E2:E10).

Les colonnes F et G sont totalement superflues pour ces calculs. Nous les avons mises là pour que vous puissiez vérifier que les résultats des cellules C16 et C18 sont exacts.

Enfin, si l’envie vous vient un jour d’évaluer une « moyenne si » et si vous n'avez pas Excel 2013 qui a introduit cette fonction, il suffit tout simplement pour cela de diviser pour cela une somme.si() par un nb.si() !

17 mars 2015

Somme.Si() et Nb.Si()

Dans de nombreux articles de ce blog, nous utilisons des formules matricielles, donc validées avec [Ctrl]-[Maj]-[Entrée]. Ces formules sont très puissantes, mais leur utilisation n’est pas à la portée du premier venu.

Aujourd’hui, nous découvrons les fonctions Somme.Si() et Nb.Si(), qui permettent de faire – avec des formules non matricielles, donc plus simplement – certaines des choses que l’on peut faire avec des formules matricielles.

Dans le tableau ci-dessous, nous cherchons à calculer le chiffre d'affaires et le nombre de factures associés à une famille de produits sélectionnée en C12 via un menu déroulant, une validation de cellule par liste avec la liste A;B;C.


En C13, nous calculons le chiffre d’affaires pour la famille sélectionnée dans la cellule C12. 
En C14, nous calculons le nombre de factures émises pour cette famille. 
Les formules sont les suivantes :
En C13 : =somme.si(D2:D10;C12;E2:E10)
En C14 : =nb.si(D2:D10;C12)

Les syntaxes de ces deux fonctions sont donc :
=somme.si(matrice à tester;condition;matrice de résultat)
=nb.si (matrice à tester;condition)

Si la condition n’est pas une valeur ou une référence à une cellule, elle doit être entrée sous forme de texte.

Remarque 1 – La formule de C14 aurait aussi pu être =nb.si(D2:D10;"="&C12).

Remarque 2 – Même si les syntaxes nous permettent de faire référence à des matrices – et cela fonctionne ! –, il est plus courant d’utiliser des vecteurs à ce niveau.

11 mars 2015

Les valeurs en double, en mieux

Dans l’article précédent, nous avons vu comment définir rapidement un format conditionnel qui met en relief soit toutes les valeurs présentes dans au moins deux cellules de la sélection, soit toutes les valeurs uniques.

Je concluais l’article en disant que je déconseillais de définir des formats conditionnels sans passer par la commande « Gérer les règles ». Nous verrons donc ajourd’hui comment, en utilisant cette commande, nous pouvons obtenir le même résultat et – pourquoi pas ? – faire encore mieux au passage…

Voici la solution que je propose pour définir un format conditionnel dépendant d’une formule – ce qui permet de passer par « Gérer les règles… » :

Du coup, j’en ai profité pour mettre en E1 le nombre de répétitions que l’on veut mettre en relief. Avec « 1 », on sélectionne les valeurs uniques ; avec « 2 » celles qui sont exactement doublées, et ainsi de suite…


J’ai ajouté un curseur, officiellement une « toupie », en contrôle de formulaire qui permet de faire varier les valeurs de E1 de 0 à 3.

Quand E1 est à « 0 », les cellules B7 et C7 affichent le format conditionnel, alors qu’elles sont toutes les deux identiques… Mais on sait que la notion de vide dans Excel est complexe.

L’article « La dernière non vide ou la dernière non nulle ? » du 13 octobre 2012 abordait d’ailleurs à ce sujet un thème connexe…