Compter le nombre de lignes contenant des valeurs spécifiques dans Excel
Il peut être facile pour nous de compter le nombre de cellules avec une valeur spécifique dans une feuille de calcul Excel. Cependant, obtenir le nombre de lignes contenant des valeurs spécifiques peut être plutôt compliqué. Dans ce cas, une formule plus complexe basée sur les fonctions SOMME, PRODUITMAT, TRANSPOSE et COLONNE peut vous aider. Ce tutoriel expliquera comment créer cette formule pour traiter cette tâche dans Excel.
Compter le nombre de lignes contenant des valeurs spécifiques
Par exemple, vous avez une plage de valeurs dans une feuille de calcul, et maintenant, vous devez compter le nombre de lignes avec une valeur donnée « 300 » comme indiqué dans la capture d'écran ci-dessous :
Pour obtenir le nombre de lignes contenant des valeurs spécifiques, la syntaxe générique est :
Formule matricielle, il faut appuyer simultanément sur Ctrl + Maj + Entrée.
- données : La plage de cellules à vérifier si elle contient la valeur spécifique ;
- X : La valeur spécifique que vous utilisez pour compter les lignes.
1. Veuillez entrer ou copier la formule ci-dessous dans une cellule vide où vous souhaitez afficher le résultat :
2. Ensuite, appuyez simultanément sur Ctrl + Maj + Entrée pour obtenir le bon résultat, voir capture d'écran :
Explication de la formule :
=SOMME(--(PRODUITMAT(--($A$2:$C$12=300);TRANSPOSE(COLONNE($A$2:$C$12)))>0))
- --$A$2:$C$12=300 : Cette expression vérifie si la valeur « 300 » existe dans la plage A2:C12, et générera un résultat sous forme de tableau VRAI et FAUX. Le double signe négatif est utilisé pour convertir les VRAI en 1 et les FAUX en 0. Vous obtiendrez donc le résultat suivant : {0,0,0;1,0,0;0,0,0;0,1,1;0,0,0;0,1,0;0,0,0;1,0,0;0,0,1;0,0,0;1,1,1}. Le tableau composé de 11 lignes et 3 colonnes servira de array1 dans la fonction PRODUITMAT.
- TRANSPOSE(COLONNE($A$2:$C$12)) : La fonction COLONNE ici est utilisée pour obtenir le numéro de colonne de la plage A2:C12, elle renvoie un tableau à 3 colonnes comme ceci : {1,2,3}. Puis la fonction TRANSPOSE échange ce tableau en un tableau à 3 lignes {1;2;3}, servant de array2 dans la fonction PRODUITMAT.
- PRODUITMAT(--($A$2:$C$12="Joanna");TRANSPOSE(COLONNE($A$2:$C$12))) : Cette fonction PRODUITMAT renvoie le produit matriciel des deux tableaux ci-dessus, vous obtiendrez le résultat suivant : {0;1;0;5;0;2;0;1;3;0;6}.
- SOMME(--(PRODUITMAT(--($A$2:$C$12="Joanna");TRANSPOSE(COLONNE($A$2:$C$12)))>0))= SOMME(--{0;1;0;5;0;2;0;1;3;0;6}>0) : D'abord, vérifiez les valeurs du tableau supérieures à 0 : Si une valeur est supérieure à 0, un VRAI est affiché ; si inférieure à 0, un FAUX est affiché. Puis le double signe négatif force les VRAI et FAUX à devenir 1 et 0, vous obtiendrez donc ceci : SOMME({0;1;0;1;0;1;0;1;1;0;1}). Enfin, la fonction SOMME additionne les valeurs du tableau pour retourner le résultat : 6.
Conseils :
Si vous devez compter le nombre de lignes contenant un texte spécifique dans une feuille de calcul, veuillez appliquer la formule ci-dessous, et n'oubliez pas d'appuyer simultanément sur les touches Ctrl + Maj + Entrée pour obtenir le total :
Fonction relative utilisée :
- SOMME :
- La fonction SOMME Excel renvoie la somme des valeurs fournies.
- PRODUITMAT :
- La fonction PRODUITMAT Excel renvoie le produit matriciel de deux tableaux.
- TRANSPOSE :
- La fonction TRANSPOSE renverra un tableau dans une nouvelle orientation basée sur une certaine plage de cellules.
- COLONNE :
- La fonction COLONNE renvoie le numéro de colonne où la formule apparaît ou renvoie le numéro de colonne de la référence donnée.
Plus d'articles :
- Compter les lignes si elles répondent à des critères internes
- Supposons que vous ayez un rapport des ventes de produits de cette année et de l'année dernière, et maintenant, vous devrez peut-être compter les produits dont les ventes de cette année sont supérieures à celles de l'année dernière, ou les ventes de cette année sont inférieures à celles de l'année dernière comme indiqué dans la capture d'écran ci-dessous. Normalement, vous pouvez ajouter une colonne d'aide pour calculer la différence de vente entre les deux années, puis utiliser NB.SI pour obtenir le résultat. Mais, dans cet article, je vais introduire la fonction SOMMEPROD pour obtenir directement le résultat sans aucune colonne d'aide.
- Compter les lignes si elles répondent à plusieurs critères
- Compter le nombre de lignes dans une plage basée sur plusieurs critères, dont certains dépendent de tests logiques qui fonctionnent au niveau de la ligne, la fonction SOMMEPROD dans Excel peut vous aider.
- 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 parlera de la façon de résoudre rapidement et facilement cette tâche 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.