Un petit topo sur les fonctions NB.SI, NB.SI.ENS, SOMME.SI, SOMME.SI.ENS, MOYENNE.SI, MOYENNE.SI.ENS, MAX.SI.ENS, MIN.SI.ENS.
Un peu d'histoire
Au début pour faire des dénombrements, des sommes et moyennes que sur une partie des valeurs d'une plage on devait passer par des formules matricielles ou dans certain cas par la fonction SOMMEPROD.
Puis est apparue les fonctions NB.SI et SOMME.SI,
suivie de NB.SI.ENS, SOMME.SI.ENS, MOYENNE.SI, MOYENNE.SI.ENS,
et enfin MAX.SI.ENS, MIN.SI.ENS remplaçant la plupart de ces formules et simplifiant leur structure.
Les fonctions NB.SI, SOMME.SI et MOYENNE.SI sont plus limitées que celles avec .ENS dont je les ai toujours considérées comme obsolètes. D'autant plus que les 2 premières ont une syntaxe dont la logique est "inverse" aux version ENS ce qui peut prêter à la confusion. (Microsoft n'a pas créé MAX.SI ni MIN.SI mais directement la version ENS).
Je ne traiterais donc que les fonctions ENS (ensemble de critères ?) dans cet article.
NB.SI.ENS
La plus simple, qui me permettra de montrer des mécaniques et problèmes communs à cette famille de fonctions.
Rôle
Compter/dénombrer les valeurs répondant à certain critères (caractéristiques).
Syntaxe
= NB.SI.ENS( Plage_critères1 ; Critères1 [ ; Plage_critères2 ...Plage_critères127 ; Critères127] )- Plage_critères1 à Plage_critères127 : Plage à évoluer. Ne peut être une matrice ou table dynamique !
- Critères1 à Critères127 : Nombre, expression, référence de cellule, texte ou matrice déterminant les cellules à prendre en compte.
On utilise des paires d'arguments Plage_critères et Critères pour spécifier où on recherche et ce que l'on recherche.
Toutes les Plage_critères doivent avoir la même taille (même nombre de cellules).
Tableau servant pour tous les exemples
Exemples
Recherche sur une valeur numérique ou une date
Recherche sur une chaîne de texte
On peut utiliser :
- ? pour remplacer une lettre.
- * pour remplacer 0 ou plusieurs lettres.
Cas des cellules vides
Cas de valeurs booléennes
Valeurs logiques VRAI et FAUX.
Rendre l'argument Plage dynamique
La plage recherchée via l'argument Plage_critère n'est pas en "dur" (saisie dans la formule) mais calculée dynamiquement dans ce cas.
On utilise la fonction INDEX avec l'argument No_lig à 0 permettant de renvoyer la colonne en entier.
Rendre l'argument Critère dynamique
La valeur recherchée via l'argument Critère n'est pas en "dur" (saisie dans la formule) mais calculée dynamiquement dans ce cas.
Ajout d'un ET logique
Les autres paires d'arguments Plage_critères et Critères génère forcément des ET logique (un ET diminue le nombre de résultats).
Ajout d'un OU logique
La seule solution est de simplement utiliser plusieurs fonctions en additionnant leurs résultats (un OU augmente le nombre de résultats).
Utilisation d'une matrice en tant que critère et récupération d'une table dynamique
Une autre solution est d'utiliser une constante matricielle renvoyant une table dynamique (voir Formules de tableaux dynamiques, une nouvelle logique de conception et Excel et les matrices (pas celles du cours de mathématiques)) et pourquoi en faire la somme.
On en fait la somme (sur les anciennes versions il faudra valider par CTRL + MAJ + ENTRER).
SOMME.SI.ENS
Rôle
Faire la somme de valeurs si elles correspondent ou d'autres valeurs associées correspondent à certain critères (caractéristiques).
Syntaxe
= SOMME.SI.ENS( Somme_plage ; Plage_critères1 ; Critères1 [ ; Plage_critères2 ...Plage_critères127 ; Critères127] )- Somme_plage : La plage de cellules à additionner. Ne peut être une matrice ou table dynamique !
- Plage_critères1 à Plage_critères127 : Plage à évoluer. Ne peut être une matrice ou table dynamique !
- Critères1 à Critères127 : Nombre, expression, référence de cellule, texte ou matrice déterminant les cellules à prendre en compte.
Exemple
Forme peu usité (plages 2D)
Pour toutes ces fonctions on peut utiliser les plages à 2 dimensions (lignes/colonnes).
MOYENNE.SI.ENS
Rôle
Faire la moyenne de valeurs si elles correspondent ou d'autres valeurs associées correspondent à certain critères (caractéristiques).
Syntaxe
= MOYENNE.SI.ENS( Plage_moyenne ; Plage_critères1 ; Critères1 [ ; Plage_critères2 ...Plage_critères127 ; Critères127])- Plage_moyenne : La plage de cellules dont on doit faire la moyenne. Ne peut être une matrice ou table dynamique !
- Plage_critères1 à Plage_critères127 : Plage à évoluer. Ne peut être une matrice ou table dynamique !
- Critères1 à Critères127 : Nombre, expression, référence de cellule, texte ou matrice déterminant les cellules à prendre en compte.
Exemple
MAX.SI.ENS et MIN.SI.ENS
Rôle
Trouver la valeur maximale/minimale d'une plage de cellules si elles correspondent ou d'autres valeurs associées correspondent à certain critères (caractéristiques).
Syntaxe
= MAX.SI.ENS( Plage_max ; Plage_critères1 ; Critères1 [ ; Plage_critères2 ...Plage_critères127 ; Critères127])
= MIN.SI.ENS( Plage_min ; Plage_critères1 ; Critères1 [ ; Plage_critères2 ...Plage_critères127 ; Critères127])- Plage_max/min : La plage de cellules dont on doit trouver la valeur maximale/minimale. Ne peut être une matrice ou table dynamique !
- Plage_critères1 à Plage_critères127 : Plage à évoluer. Ne peut être une matrice ou table dynamique !
- Critères1 à Critères127 : Nombre, expression, référence de cellule, texte ou matrice déterminant les cellules à prendre en compte.
Exemples
Et comment faire d'autres calculs de ce type comme une MEDIANE.SI.ENS qui n'existe pas dans les fonctions ?
Il va falloir utiliser les matrices / tables dynamiques ou la fonction FILTRE.
Quelques explications pour = MEDIANE(SIERREUR(B2:B7*(1/(C2:C7="A"));""))
L'objectif est de remplacer les valeurs ne correspondant pas à A en texte pour qu'elles soient ignorées par la fonction MEDIANE.
- Le 1/(C2:C7="A") permet de récupérer la matrice { 1 ; 1 ; #DIV/0 ; #DIV/0 ; 1 ; 1 } (FAUX <=> 0 et 1/0 => #DIV/0).
- Via SIERREUR les #DIV/0 sont remplacés par la chaîne vide "".
Erreur de comparaison des fonctions .SI.ENS
Ces fonctions ont la "mauvaise" (peut-être) habitude de convertir automatiquement les valeurs des plages ce qui peut parfois être gênant.
Exemple : On veut dénombrer les valeurs d'un tableau
NB.SI.ENS trouve 4 valeurs identiques (un quadruplon) alors qu'elles sont toutes uniques !
Les cellules B2 (Texte 29-5-1), B3 (Texte 37040), B6 (Date 29/05/2001), B7 (Nombre 37040) sont considérées comme ayant le même contenu.
- Pour Excel les dates sont des nombres de jours depuis le 0/1/1900 donc 29/05/2001 de B6 et 37040 de B7 sont identiques.
- NB.SI.ENS convertit le texte de B3 en numérique donc la même chose que B7.
- NB.SI.ENS convertit le texte de B2 en date donc la même chose que B6 (la saisie des dates avec tiret est une variante de celle avec slash (/).
Contournement :
Revenir au veilles méthodes 😔
=SOMME(($B$2:$B$7=B2)*1)- Les cellules B6 et B7 seront toujours considérées comme identiques.
Remarque : Même problème avec l'outil Mise en forme conditionnelle (Valeurs en double) mais pas avec l'outil Supprimer les doublons.
Conclusion
Fonctions très simples à utiliser mais le non support des tableaux dynamiques pour les arguments de type Plage limite leur utilisation dans les formules complexes. On assiste de ce fait à un étonnant retour aux anciennes structures matricielles.
Merci pour votre attention bienveillante.