Compter les lignes répondant à plusieurs critères sous Excel
Pour compter le nombre de lignes d’une plage en fonction de plusieurs critères — dont certains reposent sur des tests logiques appliqués ligne par ligne — la fonction SOMMEPROD d’Excel s’avère particulièrement utile.
Par exemple, j’ai un rapport de produits incluant les ventes prévues et les ventes réelles, et je souhaite compter les lignes contenant « Pomme » pour lesquelles la vente réelle dépasse la vente prévue, comme illustré dans la capture d’écran ci-dessous. Pour accomplir cette tâche efficacement, la fonction idéale est SOMMEPROD.
Compter les lignes répondant à plusieurs critères avec la fonction SOMMEPROD
Compter les lignes répondant à plusieurs critères avec la fonction SOMMEPROD
Pour compter les lignes répondant à plusieurs critères à l’aide de la fonction SOMMEPROD sous Excel, la syntaxe générique est la suivante :
- logical1, logical2Les expressions logiques employées pour comparer des valeurs.
1. Pour compter le nombre de lignes « Pomme » dont la vente réelle dépasse la vente prévue, utilisez la formule suivante :
Remarque : Dans la formule ci-dessus, C2:C10>B2:B10 est la première expression logique, qui compare les valeurs de la colonne C à celles de la colonne B ; A2:A10=E2 est la deuxième expression logique, qui vérifie si la cellule E2 figure dans la colonne A.
2. Appuyez ensuite sur la touche Entrée pour obtenir le résultat souhaité. Voir la capture d’écran :
Explication de la formule :
=SOMMEPROD(($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2))
- $C$2:$C$10>$B$2:$B$10 : Cette expression logique compare, ligne par ligne, les valeurs de la colonne C à celles de la colonne B. Lorsque la valeur en colonne C est supérieure à celle en colonne B, le résultat est VRAI ; sinon, il est FAUX. Elle renvoie un tableau de ce type : {VRAI;FAUX;VRAI;FAUX;FAUX;FAUX;FAUX;VRAI;VRAI}.
- $A$2:$A$10=E2 : Cette formule logique vérifie si la valeur de la cellule E2 est présente dans la plage A2:A10, et renvoie un résultat sous cette forme : {VRAI;FAUX;VRAI;FAUX;VRAI;VRAI;FAUX;VRAI;FAUX}.
- ($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2) : L’opération de multiplication combine ces deux tableaux en un seul, renvoyant un résultat tel que {1;0;1;0;0;0;0;1;0}.
- SOMMEPROD(($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2)) = SOMMEPROD({1;0;1;0;0;0;0;1;0}) : La fonction SOMMEPROD additionne les nombres du tableau et renvoie le résultat : 3.
Fonction associée utilisée :
- SUMPRODUCT:
- La fonction SOMMEPROD permet de multiplier deux colonnes (ou davantage) ou des plages de données, puis d’obtenir la somme des produits ainsi calculés.
Autres articles :
- Compter les lignes répondant à des critères internes
- Supposons que vous disposiez d’un rapport des ventes de produits pour cette année et pour l’année dernière, et que vous souhaitiez compter les produits dont les ventes cette année sont supérieures (ou inférieures) à celles de l’année dernière, comme illustré dans la capture d’écran ci-dessous. Habituellement, vous pouvez ajouter une colonne auxiliaire pour calculer la différence de ventes entre les deux années, puis utiliser NB.SI pour obtenir le résultat. Dans cet article, toutefois, je vous présente la fonction SOMMEPROD, qui vous permet d’obtenir directement le résultat sans aucune colonne auxiliaire.
- Comparer deux colonnes et compter les correspondances
- Par exemple, j’ai deux listes de données dans les colonnes A et C, et je souhaite comparer ces colonnes pour compter les lignes où la valeur de la colonne A apparaît également dans la colonne C, comme illustré dans la capture d’écran ci-dessous. Dans ce cas, la fonction SOMMEPROD s’impose comme la solution idéale pour réaliser cette opération sous Excel.
- Compter le nombre de cellules égales à l’une parmi plusieurs valeurs
- Supposons que vous disposiez d’une liste de produits en colonne A et que vous souhaitiez obtenir le nombre total d’occurrences des produits spécifiques — Pomme, Raisin et Citron — répertoriés dans la plage C4:C6 à partir de cette même colonne A, comme illustré dans la capture d’écran ci-dessous. En règle générale, les fonctions NB.SI et NB.SI.ENS d’Excel ne permettent pas de gérer ce type de scénario. Dans cet article, je vous explique comment résoudre cette tâche rapidement et facilement en combinant les fonctions SOMMEPROD et NB.SI.
Les meilleurs outils de productivité Office
Kutools pour Excel – Vous permet de vous démarquer de la foule
Kutools pour Excel propose plus de 300 fonctionnalités,garantissant que ce dont vous avez besoin est toujours à portée de clic...
Office Tab – Active la lecture et l’édition par onglets dans Microsoft Office (y compris Excel)
- Passez d’un document à l’autre en une seconde, même avec des dizaines de documents ouverts !
- Réduisez de centaines vos clics de souris chaque jour et dites adieu au syndrome de la main de la souris.
- Augmentez votre productivité de 50 % en consultant et en modifiant simultanément plusieurs documents.
- Apporte à Office (y compris Excel) une efficacité comparable à celle des navigateurs tels que Chrome, Edge et Firefox.