Apprenez comment compter facilement les valeurs uniques à l’aide de la fonction QUERY dans Google Sheets grâce à ce tutoriel étape par étape.
Comprendre les données d’exemple
L’échantillon comprend trois colonnes : Date, Produit et Quantité, représentant la quantité de ventes quotidiennes de Pommes, Bananes et Oranges.
Le 10/11/2023, nous avons eu deux ventes de Pommes et deux ventes d’Oranges. Alors que le nombre total de ventes est de 4, le nombre d’éléments uniques est de 2.
Maintenant, en plus de compter les valeurs uniques dans QUERY (requête 1), nous devons également trouver la quantité moyenne de ventes (requête 2). Comment pouvons-nous y parvenir?
Comptez les valeurs uniques dans Google Sheets en utilisant QUERY avec UNIQUE
Avant de plonger dans la formule, permettez-moi d’expliquer la logique.
Tout d’abord, nous devons obtenir les valeurs uniques de deux colonnes : la colonne que nous voulons regrouper, qui est la colonne des dates, et la colonne pour compter les éléments uniques.
Bien que nous puissions utiliser =UNIQUE(A2:B) pour y parvenir, je préfère une approche plus flexible, surtout lorsque les colonnes de regroupement et d’éléments uniques ne sont pas nécessairement adjacentes.
Spécifier les plages séparément est la méthode idéale. Par conséquent, j’utiliserai la formule =UNIQUE(HSTACK(A2:A, B2:B)) pour obtenir des valeurs uniques de ces deux colonnes. Comme vous le savez peut-être, la fonction HSTACK dans Google Sheets est utilisée pour combiner deux plages ou plus en une seule plage.
Ces données uniques seront utilisées dans la formule QUERY pour compter les valeurs uniques. Voici la formule QUERY :
=QUERY(UNIQUE(HSTACK(A2:A, B2:B)), "select Col1, count(Col2) where Col1 is not null group by Col1")
Cette formule dans la cellule E2 renverra le résultat affiché dans la « requête 1 » dans notre capture d’écran.
Pour obtenir le résultat affiché comme « requête 2 », insérez d’abord la formule ci-dessus dans la cellule E8. Ensuite, utilisez une autre QUERY dans la cellule G8 pour calculer la moyenne de la dernière colonne (Quantité). Dans ce cas, la fonction UNIQUE n’est pas requise. Voici la formule :
=QUERY(HSTACK(A2:A, C2:C), "select avg(Col2) where Col1 is not null group by Col1")
Analyse des formules :
Syntaxe de la fonction QUERY :
QUERY(data, query, [headers])
Dans la formule « requête 1 » :
- data : L’ensemble de données sur lequel la requête est effectuée. Dans ce cas, il s’agit de la liste unique des combinaisons de dates et d’éléments obtenue en appliquant la fonction UNIQUE à la plage empilée horizontalement en utilisant HSTACK(A2:A, B2:B).
- query : La chaîne de requête qui définit l’opération à effectuer sur les données. Dans l’exemple fourni, la requête est « select Col1, count(Col2) where Col1 is not null group by Col1 ». Cette requête sélectionne les valeurs uniques dans Col1 (dates) et compte les occurrences de chaque valeur dans Col2 (éléments), en excluant les valeurs nulles dans Col1, et regroupe les résultats par Col1.
Dans la formule « requête 2 » :
- data : L’ensemble de données sur lequel la requête est effectuée. Dans ce cas, il s’agit de la plage empilée horizontalement en utilisant HSTACK(A2:A, C2:C).
- query : Dans ce cas, la chaîne de requête est « select avg(Col2) where Col1 is not null group by Col1 ». Cette chaîne de requête renverra une colonne unique contenant la moyenne des valeurs de Col2 pour chaque valeur de Col1.
Comment utiliser le comptage en cours pour compter les valeurs uniques dans la QUERY de Google Sheets
Cette approche consiste à utiliser une colonne d’aide, qui peut être mise en place soit sous forme de colonne physique, soit sous forme de colonne virtuelle. Pour commencer, nous devons configurer la colonne d’aide.
Colonne d’aide (comptage en cours)
Pour renvoyer le comptage en cours des valeurs dans une colonne, comme A2:A, nous pouvons utiliser la formule matricielle suivante COUNTIFS (dans la cellule D2) :
=ArrayFormula(COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A)))
Cependant, pour trouver le comptage en cours des colonnes de date et d’élément, nous devons combiner les deux colonnes en une seule colonne. Voici la formule du comptage en cours pour cela :
=ArrayFormula(COUNTIFS(A2:A&B2:B, A2:A&B2:B, ROW(A2:A), "<="&ROW(A2:A)))
Cette formule renverra 1 pour la première occurrence de chaque combinaison unique de date et d’élément, 2 pour la deuxième occurrence, et ainsi de suite.
Nous pouvons ensuite remplacer toute valeur supérieure à 1 par 0 pour obtenir le compte unique des combinaisons de date et d’élément. Voici la formule pour cela :
=ArrayFormula((COUNTIFS(A2:A&B2:B, A2:A&B2:B, ROW(A2:A), "<="&ROW(A2:A))<2)*1)
Comptage unique dans la QUERY
Vous pouvez utiliser la colonne de comptage en cours (D2:D) de deux manières dans votre requête de comptage unique : comme colonne physique ou comme colonne virtuelle. Dans les deux cas, il vous suffit de additionner la colonne de comptage en cours pour obtenir le nombre unique d’éléments par date.
Voici la formule « requête 1 » (cellule E2) :
=QUERY(A2:D, "Select Col1, sum(Col4) where Col1 is not null group by Col1")
Explication :
Cette formule QUERY additionne les valeurs de Col4 regroupées par les valeurs uniques de Col1, en excluant les valeurs nulles. Le résultat est un tableau montrant chaque valeur unique de Col1 avec la somme correspondante des valeurs de Col4.
Voici la formule « requête 2 » (cellule E8) :
=QUERY(A2:D, "Select Col1, sum(Col4), avg(Col3) where Col1 is not null group by Col1")
Explication :
Cette formule QUERY sélectionne les valeurs uniques de Col1 ainsi que la somme des valeurs de Col4 et la moyenne des valeurs de Col3. Les résultats sont regroupés par les valeurs uniques de Col1, en excluant les valeurs nulles. Le résultat est un tableau affichant chaque valeur unique de Col1, sa somme correspondante dans Col4 et la moyenne dans Col3.
Si vous souhaitez utiliser une colonne d’aide virtuelle à la place, remplacez A2:D dans les deux formules par ce qui suit :
=HSTACK(A2:D, ArrayFormula((COUNTIFS(A2:A&B2:B, A2:A&B2:B, ROW(A2:A), "<="&ROW(A2:A))<2)*1))
Conclusion
Nous avons exploré deux approches distinctes pour compter les valeurs uniques à l’aide de la fonction QUERY dans Google Sheets.
Le choix d’utiliser QUERY dans ce contexte est de produire une sortie tabulaire. Vous pourriez également opter pour COUNTUNIQUEIFS et des LAMBDA MAP imbriqués pour obtenir un résultat similaire sans utiliser QUERY. Cependant, pour plus de simplicité, j’ai démontré la méthode QUERY.
De plus, vos besoins de comptage unique avec QUERY peuvent varier. Si vous cherchez une solution personnalisée, n’hésitez pas à partager votre feuille d’exemple avec nous dans les commentaires ci-dessous.
J’espère que vous avez apprécié cette discussion sur le comptage unique dans Google Sheets avec QUERY. N’hésitez pas à me poser des questions ou à laisser des commentaires. Vous pouvez également consulter crawlan.com pour en savoir plus sur Google Sheets et d’autres astuces de marketing en ligne.
