Aujourd’hui nous allons voir comment compter sur Excel les cellules qui peuvent vérifier plusieurs critères, par exemple à la fois le critère 1 et le critère 2, ou bien soit le critère 1 soit le critère 2. C’est souvent ce problème d’avoir plusieurs valeurs possibles que l’on rencontre. Nous allons voir plusieurs types de formules et aboutir à la formule générale qui permet de valider n’importe quel critère imaginable.
Compter les cellules qui vérifient un seul critère pouvant prendre plusieurs valeurs (A=1 ou 2)
Dans la liste ci-dessous, comment faire pour connaitre le nombre de cellules en vérifiant un seul critère mais en autorisant plusieurs valeurs pour ce critère? Par exemple le nombre de voitures vendues avec pour seul critère la marque mais ce critère peut être soit Audi soit Renault?
Pour cela nous allons tout simplement utiliser la fonction NB.SI et rajouter une utilisation à chaque valeur possible.
Compter les cellules qui vérifient à la fois plusieurs critères (A=1 et B=2)
Comment faire pour calculer le nombre de Renault rouges vendues? Il faut vérifier à la fois le critère sur la marque et sur la couleur.
Pour cela nous allons utiliser la fonction NB.SI.ENS qui permet justement de saisir plusieurs plages et une valeur à vérifier pour chaque plage.
=NB.SI.ENS(plage1 à vérifier; critère à vérifier pour la plage1; plage2; critère2; etc)
Nous allons utiliser cette fonction avec deux critères mais vous pouvez en ajouter trois ou plus
Nous allons donc utiliser la formule =NB.SI.ENS(B2:B12; « Renault »;C2:C12; « Rouge »)
Cela nous renvoie donc bien le résultat attendu: seules 2 ventes vérifient à la fois les deux critères
Compter les cellules qui vérifient à la fois plusieurs critères (A=1 et B=2) – Méthode matricielle
Il existe une autre méthode pour obtenir le même résultat, à l’aide d’une formule matricielle. Cela est plus complexe à appréhender, mais nous verrons par la suite que cette méthode permet des utilisations bien plus puissantes.
La formule matricielle {=(B2:B12= « Renault »)*(C2:C12= « Rouge »)} renvoie 1 si les 2 conditions sont vérifiées et zéro sinon.
La formule à utiliser pour obtenir le nombre de lignes qui vérifient les deux conditions est donc
{=SOMME((B2:B12= « Renault »)*(C2:C12= « Rouge »))}
Compter les cellules qui vérifient soit un critère, soit un autre critère (A=1 OU B=2)
Une opération plus compliquée est de compter les cellules qui vérifient un critère ou l’autre. Par exemple, si l’on souhaite connaitre les ventes qui sont soit des Renault, soit des voitures rouges?
On ne peut pas simplement sommer les deux critères comme au premier paragraphe.
En effet dans ce cas les lignes vérifiant les deux critères à la fois sont comptées deux fois. On obtient donc la réponse 10 alors que l’on attend 8.
Une astuce est de soustraire alors le nombre de lignes vérifiant les 2 critères, la formule devient donc:
=NB.SI(B2:B12; « Renault »)+NB.SI(C2:C12; « Rouge »)-NB.SI.ENS(B2:B12; « Renault »;C2:C12; « Rouge »)
Compter les cellules qui vérifient soit un critère, soit un autre critère (A=1 OU B=2) – Méthode matricielle
Cependant cette méthode n’est pas très pratique si l’on souhaite aller vers des critères plus complexes, aussi nous allons voir une autre méthode avec une formule matricielle.
Nous allons obtenir un tableau qui nous dit combien de critères sont vérifiés par chaque ligne avec la formule
{=(B2:B12= « Renault »)+(C2:C12= « Rouge »)}
Il ne nous reste plus qu’à sommer cette colonne en remplaçant tous les nombres positifs par des 1 (si deux critères sont vérifiés, on ne compte la ligne qu’une fois). C’est ce que l’on fait avec la formule
{=SOMME(SI((B2:B12= »Renault »)+(C2:C12= »Rouge »);1;0))}
L’avantage de cette formule est qu’elle est plus lisible et nous allons encore pouvoir l’améliorer!
Compter les cellules vérifiant une combinaison complexe de critères
Les formules matricielles vue ci dessous vont nous permettre d’élaborer des critères complexes, par exemple A et (B ou C) ou bien (A et B) ou C.
Il faut à chaque fois écrire les vecteurs de conditions (colonne=critère) et remplacer OU par le signe + et ET par une multiplication, le tout à l’intérieur d’une somme.
La formule pour calculer le nombre de vente de marque Renault ET (couleur bleu OU rouge) est donc
{=SOMME(SI((B2:B12= « Renault »)*((C2:C12= « Bleu »)+(C2:C12= « Rouge »));1;0))}
En revanche, la formule pour compter le nombre de voitures qui sont soit des Renault bleues, soit rouges, la formule est:
{=SOMME(SI(((B2:B12= « Renault »)*(C2:C12= « Bleu »))+(C2:C12= « Rouge »);1;0))}
Seule la position des parenthèses change. Il est possible de compliquer encore la chose en rajoutant des critères (3 colonnes) ou avec des expression logiques plus complexes, mais la formule aura toujours la même forme générale. Vous pouvez donc vérifier n’importe quel critère même complexe avec ce modèle de formule!
