Connaissez-vous la manière d’utiliser la fonction Query de Google Sheets pour filtrer uniquement les chiffres d’une colonne contenant des données de types différents ? Avec la fonction Filter, c’est facile à faire. Mais Query est capable de manipuler les données, il est donc intéressant d’apprendre à l’utiliser.
Récemment, j’ai essayé de faire un calcul de pourcentage dans Query en utilisant la fonction d’agrégation Count. Malheureusement, Count dans Query compte à la fois les textes et les chiffres. De plus, comme vous le savez peut-être, la fonction Query de Google Sheets fonctionne de manière imprévisible lorsque les données de types différents sont dans une seule colonne. Dans de tels cas, le type de données majoritaire détermine le type de données de la colonne dans Query. Les autres valeurs, c’est-à-dire les types de données minoritaires, sont considérées comme des valeurs nulles. À ce stade, filtrer uniquement les chiffres d’une colonne dans Query est pertinent.
Comment utiliser la fonction Query de Google Sheets pour filtrer uniquement les chiffres d’une colonne de données mixtes
Tout d’abord, voyons la formule et un exemple. Ensuite, je vous expliquerai ce qui peut se produire si nous appliquons la fonction d’agrégation Count avec cette formule. Nous verrons d’abord comment utiliser la formule dans une seule colonne.
Query à une seule colonne
Formule :
=QUERY(A3:A,"select A where A matches '[0-9-.]+' ",0)Avec cet exemple, j’espère que vous pouvez apprendre comment utiliser la fonction Query de Google Sheets pour filtrer uniquement les chiffres. J’ai utilisé l’expression régulière Matches dans la clause « Where ». Vous pouvez suivre cet exemple dans votre feuille de calcul et filtrer uniquement les chiffres de la colonne de données mixtes. Veuillez noter que les valeurs renvoyées par la formule ci-dessus peuvent être au format texte. Alors, comment convertir ces chiffres au format texte en valeurs numériques dans la fonction Query de Google Sheets ? Voyez cette formule :
=ARRAYFORMULA(VALUE(QUERY(A3:A,"select A where A matches '[0-9-.]+' ",0)))J’ai simplement utilisé la fonction Value et ArrayFormula pour convertir les chiffres du format texte au format numérique. J’espère que vous avez pu apprendre comment utiliser la fonction Query de Google Sheets pour filtrer uniquement les chiffres lorsque la colonne contient des données de types différents.
Query pour filtrer les chiffres d’une colonne de données mixtes : Utilisation réelle
Maintenant, voyons deux formules Query ci-dessous. Toutes les deux comptent la colonne A avec et sans filtrage des valeurs textuelles. Les deux formules d’exemple ci-dessus montrent comment utiliser Query dans une colonne de données de types différents pour obtenir le résultat souhaité. La formule avec la bordure rouge compte toutes les valeurs de la colonne A. Mais la réponse peut être correcte ou non, car Query saute parfois des valeurs dans de telles colonnes de données mixtes.
Formule 1 :
=QUERY(A3:A,"Select A,count(A) group by A label Count(A)''")Mais la deuxième formule avec la bordure bleue (que j’ai mise à jour ci-dessous) filtre la colonne pour les valeurs numériques, puis applique le comptage.
Formule 2 :
=QUERY( ARRAYFORMULA(VALUE(QUERY(A3:A,"select A where A matches '[0-9-.]+' ",0))), "Select Col1, Count(Col1) group by Col1 label Count(Col1)''" )Note: Les identifiants de colonne doivent être Col1, Col2, … au lieu de A, B,… lors de l’utilisation d’une expression comme données dans Query. Veuillez vérifier les résultats des deux formules dans la capture d’écran pour comprendre la différence.
Query dans plusieurs colonnes et filtrage des chiffres
Nous utilisons généralement Query avec des données à plusieurs colonnes. Vous ne pourrez peut-être pas appliquer la même approche VALUE que celle décrite ci-dessus dans ce cas. Vous devez donc savoir comment utiliser Query pour filtrer uniquement les chiffres d’une colonne de données mixtes lorsqu’il y a plus d’une colonne dans le tableau.
Voici deux colonnes. Je veux regrouper la colonne A et faire la somme de la colonne B uniquement pour les données de la colonne B qui sont de type numérique. La formule suivante renverrait une erreur « Impossible d’analyser la chaîne de requête pour le paramètre 2 de la fonction QUERY : AVG_SUM_ONLY_NUMERIC ».
=query(A3:B,"Select A,sum(B) where A is not null group by A")Nous pouvons utiliser la Query suivante pour filtrer les lignes ne contenant que des chiffres dans la colonne B de type de données mixtes.
=QUERY(A3:B,"select * where B matches '[0-9-.]+' ",0)Pour effectuer l’agrégation, nous devons formater les valeurs de la deuxième colonne en chiffres. Nous pouvons utiliser les fonctions HSTACK, LET et CHOOSECOLS pour cela.
=LET(data, QUERY(A3:B,"select * where B matches '[0-9-.]+' ",0), HSTACK(CHOOSECOLS(data,1), ARRAYFORMULA(VALUE(CHOOSECOLS(data,2)))))Comment l’utiliser ? Dans la formule ci-dessus, remplacez la formule Query par votre formule Query. Ensuite, spécifiez les colonnes individuelles séparées par des virgules dans HSTACK. Cela signifie que CHOOSECOLS(data,1) renvoie la première colonne et ARRAYFORMULA(VALUE(CHOOSECOLS(data,2))) renvoie la deuxième colonne après avoir converti les valeurs en nombres. Si votre plage est A3:C et que vous souhaitez extraire trois colonnes, spécifiez CHOOSECOLS(data,3) dans le HSTACK pour la troisième colonne. Je laisse la partie de l’agrégation de côté. Vous avez maintenant un ensemble de données propre. Vous pouvez donc effectuer d’autres tâches dessus. C’est tout pour le moment. Profitez-en !
Crawlan.com
