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 assez compliqué. Dans ce cas, une formule plus complexe basée sur les fonctions SUM, MMULT, TRANSPOSE et COLUMN peut vous rendre service. Ce tutoriel expliquera comment créer cette formule pour gérer ce travail 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 illustré ci-dessous :
Pour obtenir le nombre de lignes contenant des valeurs spécifiques, la syntaxe générique est :
Array formula, should press Ctrl + Shift + Enter keys together.
- data : 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 mettre le résultat:
2. Puis appuyez Ctrl + Maj + Entrée clés ensemble pour obtenir le résultat correct, voir capture d'écran:
Explication de la formule:
=SUM(--(MMULT(--($A$2:$C$12=300),TRANSPOSE(COLUMN($A$2:$C$12)))>0))
- --2 $A$ : 12$C$=300: Cette expression vérifie si la valeur « 300 » existe dans la plage A2:C12 et générera un résultat de tableau VRAI et Faux. Le double signe négatif est utilisé pour convertir les VRAI en 1 et les FAUX en 0. Ainsi, vous obtiendrez 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;11}. Le tableau composé de 3 lignes et 1 colonnes fonctionnera comme le tableauXNUMX dans la fonction MMULT.
- TRANSPOSER(COLONNE($A$2:$C$12)): La fonction COLUMN est ici utilisée pour obtenir le numéro de colonne de la plage A2:C12, elle renvoie un tableau à 3 colonnes comme celui-ci : {1,2,3}. Et puis la fonction TRANSPOSE échange ce tableau en un tableau à 3 lignes {1;2;3}, fonctionnant comme le tableau2 dans la fonction MMULT.
- MMULT(--($A$2:$C$12="Joanna"),TRANSPOSE(COLONNE($A$2:$C$12))): Cette fonction MMULT 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}.
- SUM(--(MMULT(--($A$2:$C$12="Joanna"),TRANSPOSE(COLUMN($A$2:$C$12)))>0))= SUM(--{0;1;0;5;0;2;0;1;3;0;6}>0): Tout d'abord, vérifiez les valeurs dans le tableau supérieures à 0 : Si une valeur est supérieure à 0, un VRAI est affiché ; si inférieur à 0, un FAUX est affiché. Et puis le double signe négatif force les VRAI et FAUX à être des 1 et des 0, vous obtiendrez donc ceci : SUM({0;1;0;1;0;1;0;1;1;0;1}). Enfin, la fonction SUM additionne les valeurs du tableau pour renvoyer 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 sur le bouton Ctrl + Maj + Entrée clés ensemble pour obtenir le nombre total :
Fonction relative utilisée:
- SOMME:
- La fonction Excel SUM renvoie la somme des valeurs fournies.
- MMULT :
- La fonction Excel MMULT renvoie le produit matriciel de deux tableaux.
- TRANSPOSER:
- 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 dont la formule apparaît ou renvoie le numéro de colonne d'une référence donnée.
Plus d'articles:
- Compter les lignes si répondre aux critères internes
- Supposons que vous ayez un rapport sur les ventes de produits de cette année et de l'année dernière, et maintenant, vous devrez peut-être compter les produits pour lesquels 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 ci-dessous capture d'écran affichée. Normalement, vous pouvez ajouter une colonne d'aide pour calculer la différence de vente entre les deux années, puis utiliser COUNTIF pour obtenir le résultat. Mais, cet article, je vais présenter la fonction SOMMEPROD pour obtenir le résultat directement sans aucune colonne d'aide.
- Compter les lignes si plusieurs critères sont satisfaits
- Comptez le nombre de lignes dans une plage en fonction de plusieurs critères, dont certains dépendent des tests logiques qui fonctionnent au niveau de la ligne, la fonction SOMMEPROD dans Excel peut vous rendre service.
- Compter le nombre de cellules est égal à 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 Apple, Grape et Lemon répertoriés dans la plage C4: C6 de la colonne A comme illustré ci-dessous. Normalement, dans Excel, les fonctions simples COUNTIF et COUNTIFS ne fonctionneront pas dans ce scénario. Cet article, je vais parler de la façon de résoudre ce travail rapidement et facilement avec la combinaison des fonctions SUMPRODUCT et COUNTIF.
Les meilleurs outils de productivité de bureau
Kutools for Excel - vous aide à vous démarquer de la foule
Kutools pour Excel possède plus de 300 fonctionnalités, Assurez-vous que ce dont vous avez besoin est à portée de clic...
Onglet Office - Activer la lecture et l'édition par onglets dans Microsoft Office (y compris Excel)
- Une seconde pour basculer entre des dizaines de documents ouverts!
- Réduisez chaque jour des centaines de clics de souris, dites adieu à la souris.
- Augmente votre productivité de 50% lors de l'affichage et de l'édition de plusieurs documents.
- Apporte des onglets efficaces à Office (y compris Excel), tout comme Chrome, Edge et Firefox.
