Je suis sûr que notre sujet traitant de la gestion des lignes masquées avec la fonction QUERY de Google Sheets est nouveau pour vous.
En utilisant la fonction QUERY dans Google Sheets, vous pouvez sélectionner uniquement les lignes visibles. Cela signifie que lorsque vous utilisez une formule QUERY sur des données filtrées (ou contenant des lignes masquées), seules les lignes visibles seront incluses. Cela n’est pas possible dans le cas habituel !
Dans ce tutoriel, vous apprendrez comment ajouter cette fonctionnalité puissante à une formule QUERY en utilisant une approche de contournement. Pour cela, nous utiliserons une colonne d’aide virtuelle (une colonne supplémentaire qui n’est pas physiquement présente dans l’ensemble de données).
Les lignes masquées sont un cauchemar pour de nombreux utilisateurs de feuilles de calcul car elles entraînent des résultats indésirables. Plusieurs fonctions puissantes, telles que SUMIF, Query et SUMPRODUCT, échouent à gérer les lignes masquées ou filtrées.
La seule façon possible de gérer les lignes masquées est d’utiliser la fonction SOUSTOTAL et les numéros de fonction associés. Cependant, la fonction SOUSTOTAL a une limitation. Elle n’accepte pas les conditions ou ne se développe pas automatiquement avec ARRAYFORMULA.
Avec notre concept de colonne d’aide virtuelle, vous pouvez surmonter certains des dilemmes liés à la gestion des lignes masquées avec Query. Alors, allons-y.
Google Sheets : Comment gérer les lignes masquées avec Query et une colonne d’aide virtuelle
Voici les données d’exemple. Elles contiennent des noms d’articles, des noms des vendeurs, des quantités vendues et des montants de vente dans les colonnes A, B, C et D respectivement.
Nous allons masquer 2 à 3 lignes et voir comment exclure ces lignes lors de la manipulation de ces données à l’aide de la fonction Query. Habituellement, il n’est pas possible de sélectionner uniquement les lignes visibles avec Query. Mais avec notre concept de colonne d’aide virtuelle, nous pouvons le faire.
Voici les étapes pour créer cette colonne d’aide virtuelle dans Google Sheets.
Comment créer une colonne d’aide virtuelle pour que Query sélectionne uniquement les lignes visibles
Il existe deux méthodes. Je ne recommande pas la méthode n°1 ci-dessous, car elle n’est plus intéressante depuis le lancement de la fonction BYROW dans Google Sheets. Veuillez donc faire défiler vers le bas et suivre l’approche n°2.
Approche n°1 (Ancienne méthode)
Ajoutons une nouvelle colonne à la fin des données, appelée colonne d’aide. Bien sûr, nous supprimerons cette colonne d’aide physique plus tard.
Nous avons la formule SOUSTOTAL suivante en E2, que nous devons copier-coller vers le bas de la colonne.
=Soustotal(109,D2)
Lorsque vous utilisez cette formule, vous devez choisir n’importe quel champ numérique.
Remplacez Soustotal(109, par Soustotal(103, si vous n’avez pas de colonne numérique.
Assurez-vous cependant qu’il n’y a pas de cellules vides dans la plage de colonne indiquée ici, D2:D7.
Voici la deuxième étape pour la gestion des lignes masquées avec Query.
Allez dans le menu Affichage > Afficher les formules.
Ensuite, supprimez tous les préfixes de signe « = » dans le soustotal, de sorte qu’il ressemble à la plage E2:E7 ci-dessous.
À nouveau, allez dans le menu Affichage et cliquez sur Afficher les formules pour supprimer la sélection.
Appliquez la formule ci-dessous dans n’importe quelle cellule vide en dehors de la plage ci-dessus pour joindre toutes les chaînes de texte de la colonne E.
=join(« ; »,E2:E7)
Cela renvoie le texte combiné de la colonne E. Vous pouvez copier le résultat de la formule, cliquer avec le bouton droit de la souris sur la cellule, puis appliquer le collage de la valeur.
Ajoutez des accolades au début et à la fin de la chaîne comme ci-dessous. Votre colonne d’aide virtuelle est prête !
{soustotal(109,D2);soustotal(109,D3);soustotal(109,D4);soustotal(109,D5);soustotal(109,D6);soustotal(109,D7)}
Maintenant, vous pouvez supprimer la colonne d’aide E. Maintenant, nous avons un long texte à utiliser comme colonne d’aide virtuelle pour Query.
Approche n°2 (Nouvelle méthode)
Dans l’approche n°1, nous avons copié-collé la formule E2, c’est-à-dire =soustotal(109,D2), vers le bas, édité et combiné pour gérer les lignes masquées avec Query.
Cela n’est plus nécessaire car nous pouvons désormais étendre automatiquement la formule E2 vers le bas à l’aide de BYROW comme indiqué ci-dessous en E2.
=byrow(D2:D7,lambda(r,soustotal(109,r)))
Si vous spécifiez une plage de texte (champ) au lieu de la plage numérique D2:D7, utilisez le numéro de fonction 103 dans le Soustotal.
Fusionner la colonne d’aide virtuelle avec Query et gérer les lignes masquées
Permettez-moi d’appliquer une formule Query simple pour ne sélectionner que les lignes visibles à partir de nos données d’exemple ci-dessus.
Au préalable, vous pouvez masquer une ou deux lignes à l’aide du menu Données > Filtre ou en cliquant avec le bouton droit de la souris et en masquant manuellement deux lignes.
D’abord, voyons la formule Query habituelle, qui extraira toutes les données de la source, y compris les lignes masquées.
=query({A2:D7}, »Sélectionner Col1, Col2, Col3, Col4″)
Maintenant, voyons notre nouvelle formule Query avec la colonne d’aide virtuelle ci-dessous.
Selon l’approche n°1 :
=query({A2:D7,{soustotal(109,D2);soustotal(109,D3);soustotal(109,D4);soustotal(109,D5);soustotal(109,D6);soustotal(109,D7)}}, »sélectionner Col1, Col2, Col3, Col4 where Col5>0″)
Selon l’approche n°2 (recommandée) :
=query({A2:D7,byrow(D2:D7,lambda(r,soustotal(109,r)))}, »sélectionner Col1,Col2,Col3,Col4 where Col5>0″)
Nous avons quatre colonnes dans nos données d’exemple. La colonne 5 est notre colonne d’aide virtuelle.
Lorsque nous masquons des lignes, la valeur dans la colonne d’aide virtuelle correspondant à cette ligne devient zéro.
Elle sera donc automatiquement exclue de la sortie de la Query en raison de notre clause « Où », c’est-à-dire où Col5>0, dans Query.
J’espère que vous avez apprécié ce tutoriel et que vous avez appris la fonctionnalité/le contournement de gestion des lignes masquées avec Query dans Google Sheets.
À bientôt pour un autre tutoriel sur les feuilles de calcul.
Sample_Sheet_171220
