Monsieur Excel
Pour tout savoir faire sur Excel !

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…



05 mars 2015

Les valeurs en double, rapidement

Dans le tableau ci-dessous, nous avons listé les étudiants ayant rendu tel ou tel devoir. Nous souhaitons mettre en relief rapidement tous ceux ayant rendu au moins deux devoirs.

Cela peut se faire en quelques secondes à l’aide de la commande de format conditionnel représentée en bas de la copie d’écran. Cette commande permet de mettre en relief soit les valeurs uniques, soit les valeurs présentes au moins à deux reprises.


Avantage

L’avantage principal de cette technique est son extrême rapidité : il ne faut que quelques secondes pour obtenir le résultat désiré.

Inconvénient

Pour ma part, je déconseille en général de définir un format conditionnel sans passer par la commande « Gérer les règles ». 

En effet, cette commande est la seule commande de format conditionnel qui permet, lors de la définition d’une règle, de vérifier si l’une ou plusieurs des cellules concernées contient déjà d’autres formats conditionnels. Et cela me semble très important !