Comment obtenir le nombre d’occurrences de valeurs dans chaque ligne dans Google Sheets ? Voici une formule matricielle équivalente à la formule COUNTIF que vous pouvez glisser-déposer. Il s’agit d’une formule utile dans Google Sheets si vous souhaitez compter les occurrences d’éléments dans chaque ligne d’un ensemble de données.
Tout d’abord, permettez-moi de vous présenter un exemple de jeu de données, puis la formule glisser-déposer COUNTIF. Cela sera facile pour moi de vous expliquer le sujet et pour vous de comprendre ce que j’ai voulu dire.
Formule COUNTIF Non-Matrice pour Obtenir le Nombre d’Occurrences dans Chaque Ligne dans Google Sheets
La formule COUNTIF dans la cellule C2 compte les occurrences de la valeur (nom) dans la cellule B2 dans la plage B2:B11.
=countif(B2:B11,B2)
Lorsque vous faites glisser la formule vers le bas, la plage à compter ainsi que le critère changent. Par exemple, dans la cellule B3, le critère sera à partir de la cellule B3 et la plage sera B3:B12.
Cela signifie que la formule COUNTIF compte l’occurrence des valeurs dans la plage à partir de la ligne où elle a été saisie.
Bien que la formule COUNTIF ci-dessus soit très simple, elle présente les inconvénients des formules glisser-déposer, tels que :
-
Soit vous devez copier-coller la formule dans toute la colonne (C2:C), soit vous devez copier-coller au fur et à mesure que de nouvelles lignes sont ajoutées à l’ensemble de données.
-
Si vous insérez une ligne dans la plage existante, vous devrez peut-être copier-coller la formule de la cellule au-dessus dans la cellule adjacente de la nouvelle ligne insérée.
J’ai une formule matricielle pour compter les occurrences dans chaque ligne, en remplacement de la formule COUNTIF ci-dessus, dans Google Sheets.
La formule peut vous sembler complexe. Mais vous pouvez oublier la formule et la logique et l’utiliser directement.
Pour ceux qui sont intéressés, je fournirai également l’explication ci-dessous.
Formule Matricielle pour Obtenir le Nombre d’Occurrences de Valeurs dans Chaque Ligne
La formule dans la cellule C2 :
=ArrayFormula(array_constrain(sort({COUNTIFS(index(sort({B2:B,row(B2:B)},2,0),0,1),index(sort({B2:B,row(B2:B)},2,0),0,1),ROW(A2:A),"<="&ROW(A2:A)),sort({B2:B,row(B2:B)},2,0)},3,1),match(2,1/(B1:B<>""))-row(B1),1))
Logique de la Formule: Comptage Progressif Inversé
Mise à jour : J’ai une nouvelle formule simple pour remplacer la précédente. Vous pouvez la trouver ici – Comptage Progressif Inversé Simplifié dans Google Sheets.
Pour comprendre la logique de la formule composite ci-dessus qui renvoie le nombre d’occurrences de valeurs dans chaque ligne dans Google Sheets, vous devez voir cette sortie de comptage progressif.
À des fins d’explication, j’ai marqué le comptage progressif du nom « Florence ».
Si nous pouvons inverser le comptage progressif, nous obtiendrons le nombre d’occurrences du nom ‘Florence’ comme le fait COUNTIF (veuillez vous référer à la capture d’écran n°1) tel que 3, 2, 1 au lieu de 1, 2, 3.
L’inversion du comptage progressif est la logique que j’ai utilisée dans ma formule matricielle qui renvoie le nombre d’occurrences dans chaque ligne.
Voyons comment inverser la formule de comptage progressif dans Google Sheets dans l’explication de la formule ci-dessous.
Explication – Comment la Formule Compte l’Occurrence dans Chaque Ligne?
Je n’utiliserai qu’une plage limitée dans l’explication. Je veux dire que la plage sera fermée, c’est-à-dire B2:B9 au lieu de B2:B.
Une fois terminé, nous pouvons rendre la référence ouverte. Cette explication se déroule en trois étapes :
- Inversion des Données du Bas vers le Haut.
- Comptage Progressif de la Colonne Inversée.
- Inversion de l’Inversion.
Inversion des Données du Bas vers le Haut
Tout d’abord, créez un tableau à deux colonnes en utilisant les accolades. La première colonne contient les noms existants dans B2:B9 et la deuxième colonne contient les numéros de ligne de 2 à 9.
=ArrayFormula({B2:B9,row(B2:B9)})
Ensuite, triez la deuxième colonne par ordre décroissant. C’est ce que fait la formule ci-dessous.
Ici, c’est la formule que j’ai utilisée pour inverser les données du bas vers le haut (ArrayFormula inutile avec SORT).
=sort({B2:B9,row(B2:B9)},2,0)
Cela a déjà été présenté dans mon précédent tutoriel – Comment Inverser une Colonne dans Google Sheets – Colonnes Finies et Infinies.
Comptage Progressif de la Colonne Inversée
Saisissez cette formule de comptage progressif dans la cellule F2, qui renvoie le comptage progressif des noms inversés dans la colonne G.
=ARRAYFORMULA(COUNTIFS(G2:G9,G2:G9,ROW(A2:A9),"<="&ROW(A2:A9)))
Remarque : Le comptage progressif a été expliqué ici : Exemples de Formules de Comptage Progressif dans Google Sheets.
Vous pouvez remplacer G2:G9 (deux fois) dans la formule par la formule 1. La formule 1 renvoie deux colonnes dans la plage G2:H9, n’est-ce pas ? Mais nous ne voulons que les valeurs dans G2:G9.
Nous pouvons donc utiliser la fonction Index pour extraire uniquement la première colonne du résultat de la formule 1.
Formule générique :
=index(formule 1,0,1)
Voici la formule :
=index(sort({B2:B9,row(B2:B9)},2,0),0,1)
Maintenant, nous pouvons modifier la formule dans la cellule F2 comme suit :
=ARRAYFORMULA(COUNTIFS(index(sort({B2:B9,row(B2:B9)},2,0),0,1),index(sort({B2:B9,row(B2:B9)},2,0),0,1),ROW(A2:A9),"<="&ROW(A2:A9)))
Maintenant, combinez les formules 1 et 2 ci-dessus. Cela ressemblerait à ceci :
={formule 1,formule 2}
Voici la formule combinée dans la cellule F2 :
={ARRAYFORMULA(COUNTIFS(index(sort({B2:B9,row(B2:B9)},2,0),0,1),index(sort({B2:B9,row(B2:B9)},2,0),0,1),ROW(A2:A9),"<="&ROW(A2:A9))),sort({B2:B9,row(B2:B9)},2,0)}
N’oubliez pas de supprimer la formule dans la cellule G2 pour étendre cette sortie de formule à la colonne G2:H.
Après avoir appliqué la formule ci-dessus, nous aurons les données comme indiqué dans la capture d’écran 4 ci-dessus.
Maintenant, il ne nous reste plus qu’à inverser le retournement et à supprimer les deuxième et troisième colonnes (colonne G et H). Ainsi, nous obtiendrons le nombre d’occurrences des noms dans chaque ligne dans Google Sheets.
Inversion du Retournement
Pour inverser le retournement, triez la troisième colonne contenant les numéros de ligne (dans la sortie de la formule F2) par ordre croissant.
Formule générique :
=sort(Formule 3,3,1)
Voici la formule d’inversion du retournement et sa sortie.
=sort({ARRAYFORMULA(COUNTIFS(index(sort({B2:B9,row(B2:B9)},2,0),0,1),index(sort({B2:B9,row(B2:B9)},2,0),0,1),ROW(A2:A9),"<="&ROW(A2:A9))),sort({B2:B9,row(B2:B9)},2,0)},3,1)
Nous voulons seulement la première colonne F2:F9 qui renvoie le nombre d’occurrences dans chaque ligne, n’est-ce pas ?
Étapes Finales :
Tout d’abord, ouvrez les plages dans la formule. Pour ce faire, remplacez B2:B9 par B2:B et A2:A9 par A2:A.
=sort({ARRAYFORMULA(COUNTIFS(index(sort({B2:B,row(B2:B)},2,0),0,1),index(sort({B2:B,row(B2:B)},2,0),0,1),ROW(A2:A),"<= "&ROW(A2:A))),sort({B2:B,row(B2:B)},2,0)},3,1)
Cette plage ouverte remplit des 0 à la fin de la plage dans la colonne F. Nous pouvons contrôler la formule pour remplir uniquement les occurrences dans F10:F s’il y a des valeurs dans B10:B.
Utilisons la fonction Array_Constrain pour extraire le nombre d’occurrences dans la plage marquée. Comment ?
Formule générique :
=Array_Constrain(Formule 5,8,1)
Dans cette formule générique, 8 représente le nombre de lignes à renvoyer (le nombre de lignes dans la plage B2:B9) et 1 représente la colonne.
Voici le problème. Nous savons que nous voulons 1 colonne. Mais qu’en est-il des lignes ?
Nous pouvons trouver dynamiquement le nombre de lignes (8) dans la plage B2:B en utilisant cette formule.
=ArrayFormula(match(2,1/(B1:B<>""))-row(B1))
Nous pouvons donc modifier la formule générique ci-dessus en remplaçant le nombre de lignes 8 par la formule ci-dessus comme ceci.
=Array_Constrain(Formule 6,ArrayFormula(match(2,1/(B1:B<>""))-row(B1)),1)
Formule finale dans la cellule C2 :
=Array_Constrain(sort({ARRAYFORMULA(COUNTIFS(index(sort({B2:B,row(B2:B)},2,0),0,1),index(sort({B2:B,row(B2:B)},2,0),0,1),ROW(A2:A),"<= "&ROW(A2:A))),sort({B2:B,row(B2:B)},2,0)},3,1),ArrayFormula(match(2,1/(B1:B<>""))-row(B1)),1)
Vous pouvez supprimer les deux fonctions ArrayFormula à l’intérieur et n’utiliser qu’une seule au début.
C’est tout.
J’espère avoir bien expliqué comment obtenir le nombre d’occurrences dans chaque ligne à l’aide d’une formule matricielle dans Google Sheets.
Merci de votre attention. Profitez-en bien !
