Compter les valeurs numériques uniques en fonction de critères dans Excel
Dans une feuille de calcul Excel, vous pouvez être confronté à un problème consistant à compter le nombre de valeurs numériques uniques basées sur une condition spécifique. Par exemple, comment puis-je compter les valeurs uniques de « Qty » pour le produit « T-shirt » à partir du rapport comme illustré dans la capture d'écran ci-dessous ? Dans cet article, je vais montrer quelques formules pour accomplir cette tâche dans Excel.
- Compter les valeurs numériques uniques en fonction de critères dans Excel 2019, 2016 et versions antérieures
- Compter les valeurs numériques uniques en fonction de critères dans Excel 365
Compter les valeurs numériques uniques en fonction de critères dans Excel 2019, 2016 et versions antérieures
Dans Excel 2019 et les versions antérieures, vous pouvez combiner les fonctions SOMME, FRÉQUENCE et SI pour créer une formule permettant de compter des valeurs uniques basées sur des critères. La syntaxe générique est :
Formule matricielle, appuyez simultanément sur Ctrl + Maj + Entrée.
- critère_plage: La plage de cellules correspondant aux critères spécifiés;
- critère: La condition sur laquelle vous souhaitez compter les valeurs uniques;
- plage: La plage de cellules contenant les valeurs uniques à compter.
Veuillez appliquer la formule ci-dessous dans une cellule vide, puis appuyez simultanément sur Ctrl + Maj + Entrée pour obtenir le bon résultat, voir capture d'écran :
Explication de la formule :
=SOMME(--(FRÉQUENCE(SI(A2:A12=E2,C2:C12),C2:C12)>0))
- SI(A2:A12=E2,C2:C12) : Cette fonction SI renvoie la valeur dans la colonne C si le produit dans la colonne A est « T-shirt », le résultat est un tableau comme ceci : {FAUX;300;500;FAUX;400;FAUX;300;FAUX;FAUX;FAUX;350}.
- FRÉQUENCE(SI(A2:A12=E2,C2:C12),C2:C12)= FRÉQUENCE({FAUX;300;500;FAUX;400;FAUX;300;FAUX;FAUX;FAUX;350},{200;300;500;350;400;450;300;550;200;260;350}) : La fonction FRÉQUENCE est utilisée pour compter chacune des valeurs numériques dans la liste du tableau, et retourne le résultat suivant : {0;2;1;1;1;0;0;0;0;0;0;0}.
- --(FRÉQUENCE(SI(A2:A12=E2,C2:C12),C2:C12)>0)=--({0;2;1;1;1;0;0;0;0;0;0;0}>0) : Vérifie si chaque valeur dans le tableau est supérieure à 0, et obtient ce résultat : {FAUX;VRAI;VRAI;VRAI;VRAI;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX}. Ensuite, le double signe négatif convertit les VRAI et FAUX en 1 et 0, retournant un tableau comme ceci : {0;1;1;1;1;0;0;0;0;0;0;0}.
- SOMME(--(FRÉQUENCE(SI(A2:A12=E2,C2:C12),C2:C12)>0))=SOMME({0;1;1;1;1;0;0;0;0;0;0;0}) : Enfin, utilisez la fonction SOMME pour additionner ces valeurs, et obtenez le total : 4.
Conseils :
Si vous souhaitez compter les valeurs uniques basées sur plus d'une condition, il suffit d'ajouter d'autres critères à la formule avec le caractère * :
Compter les valeurs numériques uniques en fonction de critères dans Excel 365
Dans Excel 365, la combinaison des fonctions LIGNES, UNIQUE et FILTRE peut aider à compter les valeurs numériques uniques basées sur des critères. La syntaxe générique est :
- plage: La plage de cellules contenant les valeurs uniques à compter.
- critère_plage: La plage de cellules correspondant aux critères spécifiés;
- critère: La condition sur laquelle vous souhaitez compter les valeurs uniques;
Veuillez copier ou entrer la formule suivante dans une cellule, puis appuyez sur la touche Entrée pour obtenir le résultat, voir capture d'écran :
Explication de la formule :
=LIGNES(UNIQUE(FILTRE(C2:C12,A2:A12=E2)))
- A2:A12=E2 : Cette expression vérifie si la valeur dans la cellule E2 existe dans la plage A2:A12, et obtient ce résultat : {FAUX;VRAI;VRAI;FAUX;VRAI;FAUX;VRAI;FAUX;FAUX;FAUX;VRAI}.
- FILTRE(C2:C12,A2:A12=E2) : La fonction FILTRE est utilisée pour filtrer les valeurs numériques dans la liste du tableau, et retourne le résultat suivant : {0;2;1;1;1;0;0;0;0;0;0;0}.
- UNIQUE(FILTRE(C2:C12,A2:A12=E2))=UNIQUE({300;500;400;300;350}) : Ici, la fonction UNIQUE est utilisée pour extraire les valeurs uniques de la liste du tableau pour obtenir ce résultat : {300;500;400;350}.
- LIGNES(UNIQUE(FILTRE(C2:C12,A2:A12=E2)))=LIGNES({300;500;400;350}) : La fonction LIGNES retourne le nombre de lignes basé sur une plage de cellules ou un tableau, donc le résultat est : 4.
Conseils :
1. Si la valeur correspondante n'existe pas dans la plage de données, vous obtiendrez une valeur d'erreur. Pour remplacer la valeur d'erreur par 0, veuillez appliquer la formule suivante :
2. Pour compter les valeurs uniques basées sur plusieurs conditions, il suffit d'ajouter d'autres critères à la formule avec le caractère * comme ceci :
Fonction relative utilisée :
- SOMME :
- La fonction SOMME Excel renvoie la somme des valeurs fournies.
- FRÉQUENCE :
- La fonction FRÉQUENCE calcule la fréquence à laquelle les valeurs se produisent dans une plage de valeurs, puis retourne un tableau vertical de nombres.
- LIGNES :
- La fonction LIGNES retourne le nombre de lignes dans une référence donnée ou un tableau.
- UNIQUE :
- La fonction UNIQUE retourne une liste de valeurs uniques dans une liste ou une plage.
- FILTRE :
- La fonction FILTRE aide à filtrer une plage de données en fonction des critères que vous définissez.
Plus d'articles :
- Compter les valeurs numériques uniques ou les dates dans une colonne
- Supposons que vous ayez une liste de nombres qui contient des doublons, et maintenant, vous souhaitez compter le nombre de valeurs uniques ou les valeurs qui n'apparaissent qu'une seule fois dans la liste comme indiqué dans la capture d'écran ci-dessous. Dans cet article, nous parlerons de certaines formules utiles pour résoudre cette tâche rapidement et facilement dans Excel.
- Compter toutes les correspondances/doublons entre deux colonnes
- Comparer deux colonnes de données et compter toutes les correspondances ou doublons dans les deux colonnes peut être une tâche courante pour la plupart d'entre nous. Par exemple, vous avez deux colonnes de noms, certains noms apparaissent à la fois dans la première et la deuxième colonne, maintenant, vous voulez compter toutes les correspondances (les correspondances situées n'importe où dans les deux colonnes) entre deux colonnes comme indiqué dans la capture d'écran ci-dessous. Ce tutoriel introduira quelques formules pour atteindre cet objectif dans Excel.
- Compter le nombre de cellules égales à l'une des nombreuses valeurs
- Supposons que j'ai une liste de produits dans la colonne A, maintenant, je veux obtenir le nombre total de produits spécifiques Pomme, Raisin et Citron listés dans la plage C4:C6 depuis la colonne A comme indiqué dans la capture d'écran ci-dessous. Normalement, dans Excel, les fonctions simples NB.SI et NB.SI.ENS ne fonctionneront pas dans ce scénario. Cet article discutera de la façon de résoudre ce problème rapidement et facilement avec la combinaison des fonctions SOMMEPROD et NB.SI.
Les meilleurs outils de productivité pour Office
Kutools pour Excel - Vous aide à vous démarquer de la foule
Kutools pour Excel compte plus de 300 fonctionnalités, garantissant que ce dont vous avez besoin n'est qu'à un clic...
Office Tab - Active la lecture et l'édition par onglets dans Microsoft Office (y compris Excel)
- Une seconde pour basculer entre des dizaines de documents ouverts !
- Réduit des centaines de clics de souris pour vous chaque jour, dites adieu à la main de la souris.
- Augmente votre productivité de 50 % lors de la consultation et de l'édition de plusieurs documents.
- Apporte des onglets efficaces à Office (y compris Excel), tout comme Chrome, Edge et Firefox.