En utilisant SUMPRODUCT compter avec plusieurs critères ou
Comme je l’ai mentionné dans beaucoup de mes blogs, un SUMPRODUCT est une fonction très polyvalente et peut être utilisée à des fins multiples. Dans cet article, nous verrons comment nous pouvons utiliser cette fonction pour compter les valeurs avec plusieurs critères OR.
Générique link: / summing-excel-sumproduct-function [SUMPRODUCT]
Formule à compter avec plusieurs critères ou
|
= |
Criteria1: il s’agit de tout critère qui renvoie un tableau VRAI et FAUX.
Critère2: Il s’agit du prochain critère que vous souhaitez vérifier.
De même, vous pouvez avoir autant de critères que vous le souhaitez.
La formule générique ci-dessus est souvent modifiée pour répondre aux exigences pour compter avec plusieurs critères OU. Mais la formule de base est la suivante. Nous verrons d’abord comment cela fonctionne à travers un exemple, puis nous discuterons d’autres scénarios dans lesquels vous devrez modifier un peu cette formule.
Exemple: compter les utilisateurs si le code du concessionnaire ou l’année correspond à l’aide de SUMPRODUCT
Nous avons donc ici un ensemble de données de vendeurs. Les données contiennent de nombreuses colonnes. Ce que nous devons faire est de compter le nombre d’utilisateurs qui ont le code « INKA » ou l’année est « 2016 ». Assurez-vous que si quelqu’un a les deux (code « inka » et année 2016), il doit être compté comme 1.
Donc, ici, nous avons deux critères. Nous utilisons la formule SUMPRODUCT mentionnée ci-dessus:
|
= |
Ici, le code et l’année sont des plages nommées.
Cela renvoie 7. Dans les données, nous avons 5 enregistrements de code INKA et 4 enregistrements de l’année 2016.
Mais 2 enregistrements ont à la fois « INKA » et 2016 comme code et année respectivement.
Ces enregistrements sont comptés comme 1. Et c’est ainsi que nous obtenons 7.
Comment ça marche?
Voyons donc comment la formule est résolue étape par étape, puis je discuterai de son fonctionnement.
|
= |
|
1⇒`SUMPRODUCT*`(–\{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;…}+\{FALSE;FALSE;FALSE;TRUE;TRUE;…})>0 |
|
2⇒`SUMPRODUCT*`(–\{1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0 |
|
3⇒`SUMPRODUCT*`(–(\{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;…}) |
|
4⇒`SUMPRODUCT*`(\{1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
|
5⇒7 |
Dans la première étape, la valeur de I3 (« INKA ») est comparée à chaque cellule de la plage de codes. Cela renvoie un tableau de TRUE et FALSE. VRAI pour chaque match. Pour économiser de l’espace, je n’ai pas montré tous VRAI-FAUX. De même, la valeur de K3 (2016) correspond à chaque cellule de la plage d’année.
Dans l’étape suivante, nous ajoutons ces deux tableaux qui aboutissent à un nouveau tableau de valeurs numériques. Comme vous le savez peut-être, TRUE est traité comme 1 et FALSE comme 0 dans Excel. Donc, quand TRUE et TRUE sont ajoutés, nous obtenons 2 et le reste, vous pouvez le comprendre.
Dans l’étape suivante, nous vérifions quelle valeur est supérieure à 0 dans le tableau.
Cela convertit à nouveau le tableau en un vrai faux tableau. Pour chaque valeur 0 que nous obtenons, False et rest sont convertis en true. Maintenant, le nombre de valeurs TRUE dans le tableau est notre réponse. Mais comment les compter? Voici comment.
link: / tips-the-double-negatives-in-excel [Doubles signes négatifs (-)] sont utilisés pour convertir les valeurs booléennes en 1 et 0.
Ainsi, chaque valeur TRUE du tableau est convertie en 1 et FALSE en 0. Dans la dernière étape, le SUMPRODUCT résume ce tableau et nous obtenons notre réponse comme 7.
Ajouter plus ou des critères à compter à l’aide de SUMPRODUCT Donc, si vous avez besoin d’ajouter plus ou des critères à compter, vous pouvez simplement ajouter des critères en utilisant le signe + à la fonction. Par exemple, si vous souhaitez ajouter un autre critère à la formule ci-dessus afin qu’elle ajoute le nombre d’employés ayant vendu plus de 5 produits.
La formule SOMMEPROD ressemblera simplement à ceci:
|
= |
Facile! n’est-ce pas?
Mais disons que vous voulez avoir deux critères de la plage Code *. Disons que vous voulez compter « INKB ». Donc comment fais-tu cela? Une méthode utilise la technique ci-dessus, mais ce serait répétitif. Disons que je veux ajouter 10 critères supplémentaires de la même gamme. Dans de tels cas, cette technique n’est pas si intelligente pour compter avec SUMPRODUCT.
Disons que nous avons des données disposées comme ça.
Les codes de critères sont sur une ligne I2: J2. La disposition des données est ici importante. La formule SOMMEPROD pour les paramètres de comptage de 3 critères OR sera:
|
= |
C’est la formule SUMPRODUCT à compter avec plusieurs critères lorsque plusieurs critères d’une plage sont écrits dans une ligne. Cela renvoie la réponse correcte qui est 10.
Si vous tapez une année dans J3, la formule ajoutera également ce nombre.
Ceci est utilisé lorsque les critères sont sur une seule ligne. Cela fonctionnera-t-il lorsque les critères sont dans une colonne pour la même plage? Non, ce ne sera pas le cas.
Dans cet exemple, nous avons plusieurs codes à compter, mais ces codes de type sont écrits dans une colonne. Lorsque nous utilisons la formule SUMPRODUCT ci-dessus, nous obtenons une erreur ans # N / A. Nous n’entrerons pas dans la façon dont cette erreur s’est produite car cela rendrait cet article trop long. Voyons comment nous pouvons faire en sorte que cela fonctionne.
Pour que cette formule fonctionne, vous devez envelopper les critères de code dans lien: / fonctions-mathématiques-excel-transpose-fonction [fonction TRANSPOSE]. Cela fera fonctionner la formule.
|
= |
Il s’agit de la formule pour compter avec plusieurs ou conditions dans la même plage lorsque les critères sont répertoriés dans une colonne.
Alors oui mon pote, j’espère avoir été assez clair et que ça avait du sens. J’espère que cela sert votre objectif d’être ici. Si cette formule n’a pas résolu votre problème, faites-moi savoir vos besoins dans la section commentaires ci-dessous. Je serai plus qu’heureux de vous aider de quelque manière que ce soit. Vous pouvez mentionner tout doute lié Excel / VBA. Jusque-là, continuez à apprendre, continuez à exceller.
Articles liés
link: / summing-excel-sumproduct-function [Comment utiliser la fonction SOMMEPROD dans Excel]: Renvoie la SOMME après la multiplication des valeurs dans plusieurs tableaux dans Excel. Cette fonction peut être utilisée pour effectuer plusieurs tâches. C’est l’une des fonctions les plus polyvalentes.
link: / counting-countifs-with-dynamic-criteria-range [COUNTIFS with Dynamic Criteria Range]: Pour compter avec une plage de critères dynamiques, nous utilisons simplement la fonction INDIRECT. Cette fonction peut lier: / counting-countifs-with-or-for-multiple-criteria [COUNTIFS With OR For Multiple Criteria]: Compter les cellules qui ont plusieurs critères qui correspondent en utilisant la fonction OR. Pour mettre une logique OR dans la fonction COUNTIFS, vous n’aurez pas besoin d’utiliser la fonction OR.
link: / tips-using-the-if-and-or-functions-together-in-microsoft-excel-2010-2013 [Using the IF with AND / OR Functions in Microsoft Excel]: Ces fonctions logiques sont utilisées pour effectuer des calculs de critères multiples. Avec IF, les fonctions OR et AND sont utilisées pour inclure ou exclure des correspondances.
link: / logical-excel-or-function [Comment utiliser la fonction OR dans Microsoft Excel]: La fonction est utilisée pour inclure toutes les valeurs TRUE dans plusieurs critères. lien: / formules-logiques-compte-cellules-qui-contiennent-ceci-ou-cela [Comment compter les cellules qui contiennent ceci ou cela dans Excel dans Excel]: Pour les cellules qui contiennent ceci ou cela, nous pouvons utiliser le Fonction SOMMEPROD. Voici comment vous effectuez ces calculs.
Articles populaires:
lien: / clavier-formule-raccourcis-50-excel-raccourcis-pour-augmenter-votre-productivité [50 raccourcis Excel pour augmenter votre productivité] | Accélérez votre tâche. Ces 50 raccourcis vous permettront de travailler encore plus rapidement sur Excel.
lien: / formules-et-fonctions-introduction-de-vlookup-function [Comment utiliser la fonction RECHERCHEV Excel] | C’est l’une des fonctions les plus utilisées et les plus populaires d’Excel qui est utilisée pour rechercher des valeurs à partir de différentes plages et feuilles. link: / tips-countif-in-microsoft-excel [Comment utiliser le]
lien: / formules-et-fonctions-introduction-de-vlookup-function [Excel]
lien: / tips-countif-in-microsoft-excel [Fonction COUNTIF] | Comptez les valeurs avec des conditions en utilisant cette fonction étonnante.
Vous n’avez pas besoin de filtrer vos données pour compter des valeurs spécifiques. La fonction Countif est indispensable pour préparer votre tableau de bord.
lien: / excel-formule-et-fonction-excel-sumif-function [Comment utiliser la fonction SUMIF dans Excel] | C’est une autre fonction essentielle du tableau de bord. Cela vous aide à résumer les valeurs sur des conditions spécifiques.