Il semble que la clause DISTINCT ne fasse pas partie de la requête Google Sheets. Alors, comment obtenir un comptage cumulatif de valeurs distinctes dans Google Sheets ?
J’ai une formule pour vous à utiliser dans Google Spreadsheets (et non dans Excel Spreadsheets).
Laissez-moi d’abord vous expliquer le comptage cumulatif de valeurs distinctes. Pour cela, j’utilise le tableau ci-dessous dans les cellules B1 à C.
Il y a quelques dates en séquence dans la première colonne. La deuxième colonne contient des chaînes de caractères (noms d’entreprises).
Mes données d’exemple se trouvent dans les cellules B2 à C11 et le comptage cumulatif distinct à partir de ces données se trouve dans les cellules E2 à F5. J’ai calculé le comptage cumulatif distinct des entreprises (colonne B) de la manière suivante.
Si une entreprise (j’ai simplement utilisé quelques lettres de l’alphabet pour représenter les entreprises, vous pouvez utiliser de vrais noms d’entreprises) se répète à une date ultérieure, elle doit être ignorée dans le comptage cumulatif.
Par exemple, les entreprises « A » et « B » se répètent dans les cellules C5 et C6 (02/05/2020). Elles sont déjà présentes dans les cellules C2 et C3 (01/05/2020).
Le comptage cumulatif distinct sera donc le même (3) le 01/05/2020 et le 02/05/2020.
Si une entreprise se répète à la même date (ce qui n’est pas le cas dans nos données d’exemple), par exemple « A » deux fois le 01/05/2020, elle ne sera comptée qu’une seule fois.
Veuillez vous référer à la capture d’écran ci-dessous pour comprendre comment le comptage cumulatif distinct des valeurs est calculé. La colonne D contient le comptage distinct et la colonne E contient le comptage cumulatif distinct.
Formulaire de comptage cumulatif (Running) du comptage distinct dans Google Sheets
Ici, en quelques étapes, vous pouvez apprendre à écrire une formule de tableau dans Google Sheets pour le comptage distinct cumulatif (également appelé running distinct count).
Plusieurs fonctions sont impliquées. Essayez de vous concentrer sur le résultat plutôt que sur les fonctions. Vous pourrez apprendre les fonctions impliquées ultérieurement dans mon guide des fonctions de Google Sheets.
Tout d’abord, ouvrez un nouveau fichier (Google Sheets). Référez-vous à l’image ci-dessus pour obtenir les données d’exemple dans B1:C11. Créez-les dans la même plage sur votre feuille.
Suivez ensuite les étapes expliquées une par une sous différents titres.
Supprimer les lignes non distinctes en utilisant Sortn et insérer une colonne de nombres
J’ai mis en évidence les valeurs distinctes pour votre référence dans le tableau ci-dessus. Voir la colonne C dans le tableau.
Nous devons supprimer les lignes 5, 6 et 10 pour rendre le tableau distinct. Faisons cela avec une formule de combinaison SORTN et ROW.
Voici la formule de l’étape 1 pour générer le comptage cumulatif/running des valeurs distinctes dans Google Sheets.
=SORTN({B2:B,row(B2:B)^0,C2:C},9^9,2,3,1)
Insérez cette formule SORTN qui supprime les valeurs non distinctes dans la cellule E2 (elle insère également une colonne de nombres).
Explication de la formule
Dans {B2:B,row(B2:B)^0,C2:C}, une nouvelle table est créée qui contient une colonne supplémentaire avec des valeurs numériques (le nombre 1 dans chaque ligne). La formule ROW sert à renvoyer cette colonne supplémentaire entre les colonnes B et C.
Il s’agit de la plage dans SORTN.
Syntaxe de SORTN (juste pour votre référence rapide) : SORTN(plage, [n], [display_ties_mode], [sort_column], [is_ascending], [sort_column2, …], [is_ascending2, …])
Dans la formule, ignorez simplement 9^9 (un grand nombre qui représente ‘n’) et 2 (mode de traitement des égalités) et 1 (ordre de tri appelé is_ascending). Ce sera la même chose même si votre plage de données est différente.
Mais vous devez comprendre l’utilisation de 3 (colonne de tri). Elle fait référence à la colonne C qui contient les noms des entreprises. Donc cela devrait être 2, non ?
Pas du tout ! Puisque nous avons utilisé la plage {B2:B,row(B2:B)^0,C2:C}, la colonne C est devenue la troisième colonne.
Array_Constrain pour limiter les deux premières colonnes
Dans cette deuxième étape, nous allons supprimer la troisième colonne (noms d’entreprises) de la sortie ci-dessus. Utilisons simplement la fonction Array_Constrain comme indiqué ci-dessous. Je modifie la formule E2 elle-même.
Formule de l’étape 2 pour le comptage distinct cumulatif :
=array_constrain(SORTN({B2:B,row(B2:B)^0,C2:C},9^9,2,3,1),9^9,2)
Nous avons maintenant la colonne des dates et la colonne des nombres. Les noms des entreprises ont été supprimés par Array_Constrain.
Préparation des données pour générer le comptage cumulatif de valeurs distinctes dans Google Sheets
Dans la première étape, nous avons supprimé certaines lignes indésirables (lignes en double) pour rendre les données uniques. Nous voulons que ces lignes supprimées soient ajoutées ci-dessous la sortie de l’étape 2, d’une manière ‘unique’.
D’une manière unique ?
Oui ! La première colonne devrait contenir les dates des lignes supprimées et nous voulons que chaque date supprimée soit suivie d’un 0 dans la deuxième colonne. Vous comprendrez l’utilité de cela dans l’étape suivante.
Ici, nous pouvons suivre la logique suivante en utilisant deux formules UNIQUES.
Extrayez toutes les dates uniques de la première colonne B2:B en utilisant unique(B2:B) et insérez une colonne de valeurs d’erreur en tant que deuxième colonne de cette sortie en utilisant unique(B2:B)/0.
Utilisez IFERROR pour convertir les valeurs d’erreur en 0. Voici la formule de l’étape 3 dans la cellule G2.
=ArrayFormula(iferror({unique(B2:B),unique(B2:B)/0},0))
Note: Les cellules G2 à G contiendront des valeurs de date au lieu de dates. Ignorez-les simplement. J’ai formaté ces cellules en dates en sélectionnant G2:G et en cliquant sur Format > Nombre > Date.
En fait, nous voulons simplement extraire les dates de la ligne 5, 6 et 10. Mais la formule renvoie toutes les dates. Ce n’est pas un problème puisque les valeurs associées à toutes les dates sont de 0.
Les deux étapes suivantes sont très importantes. Ce que nous voulons, c’est le comptage cumulatif de valeurs distinctes. Pour cela, d’abord, nous devons générer une table de comptage distinct des valeurs. Comment ?
Regroupement de requêtes pour générer le comptage distinct
Dans les étapes précédentes, nous avons généré les données nécessaires pour obtenir le comptage cumulatif des valeurs distinctes (en utilisant Query) dans Google Sheets.
Combinons les deux tables E2:F et G2:H (je veux dire la formule de l’étape 2 et la formule de l’étape 3).
ArrayFormula( {array_constrain(SORTN({B2:B,row(B2:B)^0,C2:C},9^9,2,3,1),9^9,2); iferror({unique(B2:B),unique(B2:B)/0},0)} )
Ensuite, regroupons-les en utilisant la fonction Query.
Il y a deux colonnes. La première colonne est une colonne de dates et la deuxième colonne est une colonne numérique qui contient des 1 ou des 0. Regroupons la première colonne et comptons la deuxième colonne.
Nous obtiendrons donc le comptage distinct. Voici la formule de l’étape 4 (modifiez la formule E2 comme indiqué ci-dessous).
=ArrayFormula( query( {array_constrain(SORTN({B2:B,row(B2:B)^0,C2:C},9^9,2,3,1),9^9,2); iferror({unique(B2:B),unique(B2:B)/0},0)}, "Select Col1, sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''" ) )
Nous obtiendrons donc le comptage distinct, pas le comptage distinct cumulatif.
Formule Google Sheets pour le comptage cumulatif de valeurs distinctes
Nous y sommes presque ! La somme cumulée des valeurs dans F2:F sera le comptage cumulatif des valeurs distinctes des données de B1:C.
Cela signifie que la colonne F2:F (comptage distinct) restera une colonne d’aide. Mais ne vous inquiétez pas ! Plus tard, nous pourrons également la supprimer.
Nous pouvons utiliser MMULT ou SUMIF pour la somme cumulée. Comme SUMIF ne nous permet pas d’utiliser une expression comme plage_somme (plus tard, nous utiliserons une expression au lieu de F2:F), je choisis MMULT.
Note (Hors sujet) : en raison du problème de plage_somme mentionné ci-dessus, SUMIF ne peut pas être utilisé comme formule personnalisée dans un tableau croisé dynamique ou dans la commande Filtrer. Je pense que l’équipe de développement de Google Sheets devrait résoudre ce problème pour donner des ailes à SUMIF.
Dans la cellule G2, utilisez la formule de somme cumulée/running ci-dessous qui générera le comptage cumulatif des valeurs distinctes (formule de l’étape 5).
=ArrayFormula( if(len(E2:E), MMULT( IF(ROW(E2:E)>=TRANSPOSE(ROW(E2:E))=TRUE,1,0), n(F2:F) ), ) )
Pour expliquer cette formule, veuillez vous référer à la section « Ressources » ci-dessous. Choisissez le dernier tutoriel. Il indique qu’il s’agit d’Excel. Ne vous inquiétez pas. La formule de ce tutoriel s’applique également à Google Sheets.
Il est temps de vous montrer le résultat de la formule ci-dessus.
Nous avons maintenant notre sortie souhaitée ! Maintenant, je vais supprimer la colonne de comptage distinct F et ne garder que la colonne de comptage distinct cumulatif G.
Supprimer une colonne supplémentaire de la formule du comptage distinct cumulatif (Formule finale)
La formule E2 (formule de l’étape 4) renvoie deux colonnes : une colonne de dates et une colonne de comptage distinct. Nous allons les séparer ou, pourrait-on dire, les diviser en deux colonnes.
Première colonne (dans E2) – Query de la colonne 1
J’ai utilisé une autre requête avec la formule de l’étape 4 pour extraire la première colonne (alternativement, nous pouvons utiliser Array_Constrain).
=query( ARRAYFORMULA( query( {array_constrain(SORTN({B2:B,row(B2:B)^0,C2:C},9^9,2,3,1),9^9,2); iferror({unique(B2:B),unique(B2:B)/0},0)}, "Select Col1, sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''" ) ), "Select Col1" )
Il suffit de comprendre que nous pouvons extraire la deuxième colonne en modifiant la dernière partie de la formule ci-dessus de Col1 à Col2 comme suit.
Deuxième colonne – Query de la colonne 2 :
=query( ARRAYFORMULA( query( {array_constrain(SORTN({B2:B,row(B2:B)^0,C2:C},9^9,2,3,1),9^9,2); iferror({unique(B2:B),unique(B2:B)/0},0)}, "Select Col1, sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''" ) ), "Select Col2" )
Remplacez E2:E dans la formule de somme cumulée en G2 par la requête ci-dessus. Vous obtiendrez des erreurs #VALUE en raison de tailles de matrice incompatibles.
C’est parce que nous avons utilisé E2:E dans la formule de G2 qui est une plage ouverte, mais la formule qui a remplacé F2:F renvoie une plage limitée/fermée.
Remplacez donc E2:E par indirect("E2:E"&counta(unique(B2:B))+1) (deux fois dans la formule de G2 – voir la mise en évidence de la couleur cyan sur l’image de la formule ci-dessous).
Supprimez également If(Len(E2:E) car il n’est pas nécessaire dans une plage fermée. Veuillez vous référer à l’image ci-dessous (surlignage jaune).
Formule finale pour générer le comptage cumulatif de valeurs distinctes dans Google Sheets :
=ArrayFormula(MMULT(IF(ROW(indirect("E2:E"&counta(unique(B2:B))+1))>=TRANSPOSE(ROW(indirect("E2:E"&counta(unique(B2:B))+1)))=TRUE,1,0),n(query(ARRAYFORMULA(query({array_constrain(SORTN({B2:B,row(B2:B)^0,C2:C},9^9,2,3,1),9^9,2);iferror({unique(B2:B),unique(B2:B)/0},0)},"Select Col1, sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''")),"Select Col2"))))
Sample_Sheet_7820
Ressources :
- Normal and Array-Based Running Total Formula in Google Sheets
- Running Count of Multiple Values in a List in Google Sheets
- Running Count in Google Sheets – Formula Examples
- How to Calculate Running Balance in Google Sheets
- Array Formula for Conditional Running Total in Google Sheets
- Running Total Array Formula in Excel [Formula Options]
