Sommaire
Pour compter les lignes avec la logique OR, vous pouvez utiliser une formule basée sur la fonction SUMPRODUCT. Dans l'exemple illustré, la formule en G6 est:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
où groupe (B5: B15), couleur1 (C5: C15) et couleur2 (D5: D15) sont des plages nommées.
Explication
L'un des problèmes les plus délicats dans Excel est de compter les lignes dans un ensemble de données avec «logique OU». Il existe deux scénarios de base: (1) vous voulez compter les lignes où une valeur dans une colonne est "x" OU "y" (2) vous voulez compter les lignes où une valeur, "x", existe dans une colonne OU une autre .
Dans cet exemple, l'objectif est de compter les lignes où group = "a" AND Color1 OR Color2 sont "red". Cela signifie que nous travaillons avec le scénario 2 ci-dessus.
Avec COUNTIFS
Vous pouvez dans un premier temps accéder à la fonction COUNTIFS, qui gère plusieurs critères de manière native. Cependant, la fonction COUNTIFS joint les conditions à la logique AND, de sorte que tous les critères doivent être TRUE pour être inclus dans le décompte:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Cela rend COUNTIFS inutilisable, sauf si nous utilisons plusieurs instances de COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Traduction: compter les lignes où groupe est "a" et couleur1 est "rouge" + compter les lignes où groupe est "a" et couleur2 est "rouge" - compter les lignes où groupe est "a" et couleur1 est "rouge" et couleur2 est " rouge "(pour éviter le double comptage).
Cela fonctionne, mais vous pouvez voir que c'est une formule quelque peu compliquée et redondante.
Avec logique booléenne
Une meilleure solution consiste à utiliser la logique booléenne et à traiter le résultat avec la fonction SUMPRODUCT. (Si vous avez besoin d'une introduction à l'algèbre booléenne, cette vidéo fournit une introduction.) Dans l'exemple illustré, la formule en G6 est:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
où groupe (B5: B15), couleur1 (C5: C15) et couleur2 (D5: D15) sont des plages nommées.
La première partie du problème est de tester group = "a" ce que nous faisons comme ceci:
(group="a")
Étant donné que la plage B5: B15 contient 11 cellules, cette expression renvoie un tableau de 11 valeurs TRUE et FALSE comme ceci:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Chaque TRUE représente une ligne où le groupe est "A".
Ensuite, nous devons vérifier la valeur "rouge" dans la colonne 1 ou la colonne 2. Nous faisons cela avec deux expressions jointes par addition (+), puisque l'addition correspond à la logique OR en algèbre booléenne:
(color1="red")+(color2="red")
Excel évalue automatiquement les valeurs VRAI et FAUX comme 1 et 0 lors de toute opération mathématique, de sorte que le résultat de l'expression ci-dessus est un tableau comme celui-ci:
(2;0;0;1;1;0;1;0;0;0;1)
Le premier nombre du tableau est 2, car Color1 et Color2 sont «rouges» dans la première ligne. Pour les raisons expliquées ci-dessous, nous devons nous prémunir contre cette situation en vérifiant les valeurs supérieures à zéro:
((2;0;0;1;1;0;1;0;0;0;1))>0
Maintenant, nous avons à nouveau un tableau de valeurs TRUE et FALSE:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Le tableau ci-dessous résume la manière dont Excel évalue la logique des couleurs expliquée ci-dessus:
À ce stade, nous avons les résultats du test de Group = "a" dans un tableau:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Et les résultats du test "rouge" dans Color1 ou Color2 dans un autre tableau:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
L'étape suivante consiste à rassembler ces deux tableaux avec la "logique ET". Pour ce faire, nous utilisons la multiplication (*), car la multiplication correspond à la logique ET en algèbre booléenne.
Après avoir multiplié les deux tableaux ensemble, nous avons un seul tableau de 1 et de 0, qui est livré directement à la fonction SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
La fonction SOMMEPROD renvoie la somme des nombres, 2, comme résultat final. C'est le nombre de lignes où group = "a" ET Color1 OU Color2 sont "red".
Pour éviter le double comptage
Nous ne voulons pas compter deux fois les lignes où Color1 et Color2 sont "rouges". C'est pourquoi nous vérifions les résultats de (color1 = "red") + (color2 = "red") pour les valeurs supérieures à zéro dans le code ci-dessous:
((color1="red")+(color2="red"))>0
Sans cette vérification, le 2 de la première ligne des données apparaîtrait dans le tableau final et entraînerait le renvoi incorrect de 3 par la formule comme décompte final.
Option FILTRE
Une bonne chose à propos de la logique booléenne est qu'elle fonctionne parfaitement avec les dernières fonctions d'Excel, comme XLOOKUP et FILTER. Par exemple, la fonction FILTER peut utiliser exactement la même logique expliquée ci-dessus pour extraire les lignes correspondantes:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Le résultat de FILTER est les deux lignes qui répondent aux critères, comme indiqué ci-dessous:
Si vous souhaitez en savoir plus sur ces nouvelles fonctions, nous avons une présentation et une formation vidéo.
