Ce didacticiel montre comment utiliser leExcel Fonction COUNTIF et COUNTIFStion dans Excel pour compter les données qui répondent à certains critères.
Présentation de la fonction COUNTIF
Vous pouvez utiliser la fonction COUNTIF dans Excel pour compter les cellules qui contiennent une valeur spécifique, compter les cellules supérieures ou égales à une valeur, etc.
(Remarquez comment les entrées de formule apparaissent)
Syntaxe et arguments de la fonction COUNTIF :
=COUNTIF (plage, critères)
gamme - La plage de cellules à compter.
Critères - Les critères qui contrôlent quelles cellules doivent être comptées.
Qu'est-ce que la fonction COUNTIF ?
La fonction COUNTIF est l'une des anciennes fonctions utilisées dans les feuilles de calcul. En termes simples, il est excellent pour analyser une plage et vous dire combien de cellules remplissent cette condition. Nous verrons comment la fonction fonctionne avec du texte, des nombres et des dates; ainsi que certaines des autres situations qui pourraient survenir.
Exemple de base
Commençons par examiner cette liste d'éléments aléatoires. Nous avons des nombres, des cellules vides et des chaînes de texte.
Si vous voulez savoir combien d'éléments correspondent exactement aux critères, vous pouvez spécifier ce que vous voulez rechercher comme deuxième argument. Un exemple de cette formule pourrait ressembler à
=COUNTIF(A2:A9, "Pomme")
Cette formule renverrait le nombre 3, car il y a 3 cellules dans notre plage qui répondent à ce critère. Alternativement, nous pouvons utiliser une référence de cellule au lieu de coder en dur une valeur. Si nous écrivions « Apple » dans la cellule G2, nous pourrions changer la formule en
=COUNTIF(A2:A9, G2)
Lorsqu'il s'agit de nombre, il est important de faire la distinction entre les nombres et les nombres stockés sous forme de texte. En règle générale, vous ne mettez pas de guillemets autour des nombres lorsque vous écrivez des formules. Donc, pour écrire une formule qui vérifie le nombre 5, vous écririez
=COUNTIF(A2:A9, 5)
Enfin, nous pourrions également vérifier les cellules vides en utilisant une chaîne de longueur nulle. Nous écririons cette formule sous la forme
=COUNTIF(A2:A9, "")
Noter: cette formule compte à la fois les cellules qui sont vraiment vides, ainsi que celles qui sont vides à la suite d'une formule, comme une fonction SI.
Matchs partiels
La fonction COUNTIF prend en charge l'utilisation de caractères génériques, « * » ou « ? », dans les critères. Regardons cette liste de produits de boulangerie savoureux :
Pour trouver tous les éléments commençant par Apple, nous pourrions écrire « Apple* ». Donc, pour obtenir une réponse de 3, notre formule en D2 est
=COUNTIF(A2:A5, "Pomme*")
Noter: La fonction COUNTIF n'est pas sensible à la casse, vous pouvez donc également écrire "apple*" si vous le souhaitez.
De retour à nos produits de boulangerie, nous pourrions également vouloir savoir combien de tartes nous avons dans notre liste. Nous pouvons trouver cela en plaçant le caractère générique au début de notre terme de recherche et en écrivant
=COUNTIF(A2:A5, "*tarte")
Cette formule donne le résultat de 2.
Nous pouvons également utiliser des caractères génériques pour vérifier les cellules contenant du texte. Revenons à notre liste originale de données.
Pour compter le nombre de cellules qui ont au moins du texte, donc sans compter les nombres ou les cellules vides, nous pouvons écrire
=COUNTIF(A2:A9, "*")
Vous pouvez voir que notre formule renvoie correctement un résultat de 4.
Opérateurs de comparaison dans COUNTIF
Lors de l'écriture des critères jusqu'à présent, nous avons laissé entendre que notre opérateur de comparaison est "=". En fait, on aurait pu écrire ceci :
=COUNTIF(A2:A9, "=Pomme")
C'est un caractère supplémentaire à écrire cependant, il est donc généralement omis. Cependant, cela signifie que vous pouvez utiliser les autres opérateurs tels que supérieur à, inférieur à ou différent de. Regardons cette liste d'âges enregistrés :
Si nous voulions savoir combien d'enfants ont au moins 5 ans, nous pouvons écrire une comparaison « supérieur ou égal à » comme suit :
=COUNTIF(A2:A8, ">=5")
Noter: L'opérateur de comparaison est toujours donné sous forme de chaîne de texte et doit donc être entre guillemets.
De même, vous pouvez également rechercher des éléments inférieurs à une valeur donnée. Si nous avons besoin de savoir combien sont inférieurs à 8, nous pouvons écrire
=COUNTIF(A2:A8, "<8")
Cela nous donne le résultat souhaité de 5. Imaginons maintenant que tous les enfants de 6 ans partent en sortie. Combien d'enfants restera-t-il ? Nous pouvons le comprendre en utilisant une comparaison « pas égal à » comme celle-ci :
=COUNTIF(A2:A8, "6")
Maintenant, nous pouvons rapidement voir que nous avons 6 enfants qui n'ont pas 6 ans.
Dans ces exemples de comparaison jusqu'à présent, nous avons codé en dur les valeurs que nous voulions. Vous pouvez également utiliser une référence de cellule. L'astuce est que vous devez concaténer l'opérateur de comparaison avec la référence de cellule. Disons que nous mettons le nombre 7 dans la cellule C2 et que nous voulons que notre formule dans D2 montre combien d'enfants ont moins de 7 ans.
Notre formule dans D2 doit ressembler à ceci :
=COUNTIF(A2:A8, "<"&C2)
Noter: Faites particulièrement attention lorsque vous écrivez ces formules pour savoir si vous devez mettre un élément entre guillemets ou à l'extérieur. Les opérateurs sont toujours à l'intérieur des guillemets, les références de cellules sont toujours à l'extérieur des guillemets. Les nombres sont à l'extérieur si vous faites une correspondance exacte, mais à l'intérieur si vous faites un opérateur de comparaison.
Travailler avec des dates
Nous avons vu comment vous pouvez donner un texte ou un nombre comme critère, mais qu'en est-il lorsque nous devons travailler avec des dates ? Voici une liste d'échantillons rapide avec laquelle nous pouvons travailler :
Pour compter le nombre de dates après le 4 mai, nous devons faire preuve de prudence. Les ordinateurs stockent les dates sous forme de nombres, nous devons donc nous assurer que l'ordinateur utilise le bon nombre. Si nous écrivions cette formule, obtiendrions-nous le bon résultat ?
=COUNTIF(A2:A9, "
La réponse est "peut-être". Comme nous avons omis l'année de nos critères, l'ordinateur supposera que nous parlons de l'année en cours. Si toutes les dates avec lesquelles nous travaillons sont pour l'année en cours, alors nous obtiendrons la bonne réponse. S'il y a des dates qui sont dans le futur, nous obtiendrions la mauvaise réponse. De plus, une fois l'année suivante commencée, cette formule renverra un résultat différent. En tant que telle, cette syntaxe devrait probablement être évitée.
Comme il peut être difficile d'écrire correctement les dates dans une formule, il est recommandé d'écrire la date que vous souhaitez utiliser dans une cellule, puis vous pouvez utiliser cette référence de cellule dans votre formule COUNTIF. Alors, écrivons la date du 7 mai 2020 dans la cellule C2, puis nous pouvons mettre notre formule dans C4.
La formule en C4 est
=COUNTIF(A2:A9, "<"&C2)
Nous savons maintenant que le résultat de 7 est correct et que la réponse ne changera pas de manière inattendue si nous ouvrons cette feuille de calcul dans le futur.
Avant de quitter cette section, il est courant d'utiliser la fonction AUJOURD'HUI lorsque vous travaillez avec des dates. Nous pouvons l'utiliser comme nous le ferions pour une référence de cellule. Par exemple, nous pourrions changer la formule précédente pour qu'elle soit la suivante :
=COUNTIF(A2:A9, "<"&AUJOURD'HUI())
Maintenant, notre formule sera toujours mise à jour au fur et à mesure que le temps réel progresse, et nous aurons un nombre d'éléments inférieur à celui d'aujourd'hui.
Critères multiples et COUNTIFS
La fonction COUNTIF d'origine a été améliorée en 2007 lorsque COUNTIFS est sorti. La syntaxe entre les deux est très similaire, cette dernière vous permettant de donner des plages et des critères supplémentaires. Vous pouvez facilement utiliser COUNTIFS dans toutes les situations où COUNTIF existe. C'est juste une bonne idée de savoir que les deux fonctions existent.
Regardons ce tableau de données :
Pour savoir combien de personnes se trouvent dans les niveaux de rémunération 1 à 2, vous pouvez écrire une somme de fonctions COUNTIF comme celle-ci :
=COUNTIF(B2:B7, ">=1")-COUNTIF(B2:B7, ">2")
Cette formule fonctionnera, car vous trouvez tout ce qui est supérieur à 1, mais en soustrayant ensuite le nombre d'enregistrements qui sont au-delà de votre point de coupure. Alternativement, vous pouvez utiliser COUNTIFS comme ceci :
=COUNTIFS(B2:B7, ">=1", B2:B7, "<=2")
Ce dernier est plus intuitif à lire, vous voudrez donc peut-être utiliser cette route. De plus, COUNTIFS est plus puissant lorsque vous devez prendre en compte plusieurs colonnes. Disons que nous voulons savoir combien de personnes sont dans la gestion et dans le niveau de rémunération 1. Vous ne pouvez pas faire cela avec juste un COUNTIF; vous auriez besoin d'écrire
=COUNTIFS(A2:A7, "Gestion", B2:B7, 1)
Cette formule vous donnerait le résultat correct de 2. Avant de quitter cette section, considérons une logique de type Or. Et si on voulait savoir combien de personnes sont en gestion ou ? Vous auriez besoin d'ajouter quelques COUNTIFS ensemble, mais il y a deux façons de le faire. Le plus simple est de l'écrire comme ceci :
=NB.SI(A2:A7, "RH")+NB.SI(A2:A7, "Gestion")
Vous pouvez également utiliser un tableau et écrire cette formule matricielle :
=SUM(COUNTIF(A2:A7, {"RH", "Gestion"}))
Noter: Les formules matricielles doivent être confirmées à l'aide de « Ctrl+Maj+Entrée » et pas seulement « Entrée ».
Comment cette formule fonctionnera si elle verra que vous avez donné un tableau en entrée. Il calculera ainsi le résultat de deux fonctions COUNTIF différentes et les stockera dans un tableau. La fonction SUM additionnera ensuite tous les résultats de notre tableau pour créer une seule sortie. Ainsi, notre formule sera évaluée comme suit :
=SOMME(COUNTIF(A2:A7, {"RH", "Gestion"})) =SOMME({2, 3}) =5
Compter les valeurs uniques
Maintenant que nous avons vu comment utiliser un tableau avec la fonction COUNTIF, nous pouvons aller plus loin pour nous aider à compter le nombre de valeurs uniques dans une plage. Tout d'abord, regardons à nouveau notre liste de départements.
=SOMME(1/COUNTIF(A2:A7,A2:A7))
Nous pouvons voir qu'il y a 6 cellules de données, mais il n'y a que 3 éléments différents. Pour que le calcul fonctionne, nous aurions besoin que chaque élément valent 1/N, où N est le nombre de fois qu'un élément est répété. Par exemple, si chaque HR ne valait que 1/2, alors lorsque vous les additionnerez, vous obtiendrez un compte de 1, pour 1 valeur unique.
Revenons à notre COUNTIF, qui est conçu pour déterminer combien de fois un élément apparaît dans une plage. En D2, nous écrirons la formule matricielle
=SOMME(1/COUNTIF(A2:A7, A2:A7))
Comment cette formule fonctionnera, pour chaque cellule de la plage A2:A7, elle vérifiera combien de fois elle apparaît. Avec notre échantillon, cela va produire un tableau de
{2, 2, 3, 3, 3, 1}
Ensuite, nous transformons tous ces nombres en fractions en effectuant une division. Maintenant, notre tableau ressemble à
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Lorsque nous additionnons tout cela, nous obtenons le résultat souhaité de 3.
Countif avec deux ou plusieurs conditions - La fonction Countifs
Jusqu'à présent, nous n'avons travaillé qu'avec la fonction COUNTIF. La fonction COUNTIF ne peut gérer qu'un seul critère à la fois. Pour COUNTIF avec plusieurs critères, vous devez utiliser la fonction COUNTIFS. COUNTIFS se comporte exactement comme COUNTIF. Vous ajoutez simplement des critères supplémentaires. Jetons un coup d'œil à l'exemple ci-dessous.
=COUNTIFS(B2:B7,"=130")
COUNTIF & COUNTIFS dans Google Sheets
La fonction COUNTIF & COUNTIFS fonctionne exactement de la même manière dans Google Sheets que dans Excel :
