Google Sheets : rendez votre formule Query dynamique avec des cases à cocher

0
232

QUERY offre de nombreuses fonctionnalités aux utilisateurs, en particulier à ceux qui traitent de grandes quantités d’informations, comme pour Angeline par exemple, qui travaille au département des Ressources Humaines d’une entreprise de 4000 salariés.

Angeline souhaite consulter des informations provenant d’une grande base de données et afficher seulement les colonnes dont elle a besoin, simplement en cochant les cases desdites colonnes. 

Dans cet article, nous allons voir comment rendre la fonction QUERY dynamique pour afficher les valeurs sélectionnées en cochant des cases.

En effet, la syntaxe de la formule utilisée doit être de type :

=query(plage_de_la_base_de_donnée; “Select Col1, Col4, Col7”;1)

La partie dynamique de la fonction devra donc générer la chaîne de caractères : « select Col1, Col4,Col7 »

Cette chaîne de caractères devra donc être construite automatiquement pour ne pas avoir à la modifier manuellement dans la formule QUERY en fonction des colonnes que nous souhaitons afficher en résultat.  

Cet article est inspiré de la vidéo en anglais de Learn Google Spreadsheets dont je recommande vivement la chaîne Youtube.

Les fonctions dont nous aurons besoin sont : 

  • COLONNE ([référence cellule])
  • SI (expression_logique; valeur_si_true; valeur_si_false)
  • TEXTJOIN (délimiteur; ignorer_cellules_vides; texte1; [texte2;…]) ← fonction comme JOIN mais avec la possibilité d’ignorer des cellules vides 😃
  • QUERY (données; requête; [en_têtes])

Comment effectuer une requête dynamique avec des cases à cocher dans Google Sheets

Dans cet exemple nous allons travailler sur une feuille source s’appelant : « Base de données ».

Vous pouvez utiliser ce site page Random Data Generator  si vous souhaitez vous entraîner sur des données fictives.

La première étape consiste à inclure les cases à cocher sur une ligne se trouvant au-dessus des en-têtes de la base de données. Ces cases serviront ensuite à sélectionner les informations que nous voulons voir apparaître en résultat de la requête QUERY().

Pour cela nous allons :

  • Insérer une ligne au-dessus des en-têtes de la base de donnée.
  • Choisir la plage où les cases à cocher doivent être placées.
  • Aller dans : « Données » > « Validation de données ».  
    • Dans « Critères » sélectionner : « Case à cocher »
    • Dans « En cas de données incorrectes » choisir : « Refuser la saisie »
    • Enregistrer
Validation des donnees-Google sheets

Vous obtiendrez une feuille comme celle-ci :

Base de donnees-Google sheets

Première étape : utilisation de la fonction COLONNE() 

Pour démarrer la configuration, nous allons ajouter des lignes en haut des cases à cocher pour y implémenter la fonction COLONNE() en combinaison avec la fonction ARRAYFORMULA().

La fonction COLONNE() indique le numéro de la colonne dans laquelle une cellule de référence se trouve.

Ex1: la fonction colonne inscrite ainsi =COLONNE() en cellule C7 sera égale à « 3 » (car « C » est la troisième colonne).

Ex2 : la même fonction toujours inscrite en C7 mais ayant comme argument la cellule A1  =colonne(A1) donnera comme résultat « 1 » (car « A » est la première colonne).

Pour répéter la fonction sur une plage de données, nous utiliserons ARRAYFORMULA().

Nous allons donc inscrire la formule suivante en cellule A3 :

= ArrayFormula(COLONNE(A6:K6))

Ce qui aura pour résultat d’indiquer les numéros de colonne au-dessus de chaque en-tête de notre base de données.

Base de donnees-Google Sheets

Pour définir la cellule A6, comme début du tableau, la fonction suivante sera utilisée :

= ArrayFormula(COLONNE(A6:K6)-COLONNE(A6)+1)

En cas d’ajout de colonnes à gauche, cette formule évitera de perdre le référent en début de tableau. Il sera ainsi possible d’ajouter des colonnes à gauche de la première colonne de la base de données afin que les calculs de numéro de colonne ne soient pas faussés.

Deuxième étape : utilisation de la fonction IF()

Dans la cellule A2, la formule sera utilisée :

= ArrayFormula(IF(A5:K5=VRAI;A3:K3;""))

La fonction IF() va aller vérifier la valeur des cases à cocher. Pour rappel, les cases à cocher renvoient un résultat de type Boolean. Si la case est cochée, le résultat est égal à “vrai”. Si la case est décochée le résultat vaut “faux”.

Avec cette configuration seul le numéro des colonnes dont les cases sont cochées apparaissent.

Array Formula-Google Sheets

Nous pouvons à présent emboîter les fonctions pour n’en faire qu’une seule :

Dans la fonction

= ArrayFormula(IF(A5:K5=VRAI;A3:K3;""))

Remplacez la plage A3:K3 par COLONNE(A6:K6)-COLONNE(A6)+1

= ArrayFormula(IF(A5:K5=VRAI; COLONNE(A6:K6)-COLONNE(A6)+1;""))

Noter que la valeur  =VRAI, est optionnelle dans la fonction IF() puisque les cases de la plage A5:K5 ne renvoient que des valeurs “VRAI” ou “FAUX”. Nous pouvons donc la supprimer pour éviter toute confusion.

Maintenant, il faut ajouter “Col” devant le numéro de la colonne afin de respecter la syntaxe de la formule QUERY(). Pour cela, nous pouvons concaténer le texte avec le symbole “&”:

= ArrayFormula(IF(A5:K5;"Col"&(COLONNE (A6:K6)-COLONNE(A6)+1);""))
Col-Goole Sheets

Troisième étape : utilisation de la fonction TEXTJOIN()

Maintenant, la troisième fonction :

= TEXTJOIN (",";VRAI;A2:K2)

Attention, les colonnes que nous voulons voir apparaître en résultat de QUERY() doivent être séparées par des virgules (,) pour respecter la syntaxe de la partie SELECT de la formule QUERY().  L’argument “VRAI” de la formule TEXTJOIN() supprime les espaces vides.

Exemple : 

ABCD
Col1Col4

=TEXTJOIN(“,”;FAUX;A2:D2) donnera ➡ Col1,,,Col4 

alors que : 

=TEXTJOIN(“,”;VRAI;A2:D2) donnera  ➡ Col1,Col4 

Avec cette fonction, tous les noms des colonnes cochées seront obtenus en résultat.

Text Join-Google Sheets

Combinez les fonctions :

Remplacez la plage A2:K2, par ArrayFormula(IF (A5:K5; »Col »& (COLONNE (A6:K6) -COLONNE (A6) +1); » »))

Et concaténer de nouveau avec: « SELECT « &

="SELECT "&TEXTJOIN (","; VRAI; ArrayFormula(IF(A5:K5;"Col"&(COLONNE(A6:K6)-COLONNE(A6)+1);"")))
Select-Google Sheets

Pour éviter toute confusion, vous pouvez supprimer les informations de la ligne 2.

Coupez la formule et collez-la dans une nouvelle feuille de calcul.

Quatrième étape : QUERY()

Dans la nouvelle feuille Résultats, utilisez la formule suivante :

= QUERY('Base de données'!A2:K;"";1)
Query-Google Sheets
  • Copiez la formule de la cellule A1 et remplacez-la par les guillemets dans Query (A2).
  • Le premier résultat affichera une erreur, et pour la corriger nous devons mettre la première plage (‘Base de données’! A2:K) entre accolades { } .
= QUERY({'Base de données'! A2:K};"SELECT "&TEXTJOIN (","; VRAI; ArrayFormula (IF ('Base de données'! A1: K1;"Col"&(COLONNE ('Base de données '!A2:K2)-COLONNE('Base de données'!A2)+1);"")));1)

Nous obtiendrons les informations des colonnes sélectionnées dans la feuille précédente.

Filtre avec check box-Google Sheets

Maintenant, Angeline obtient les informations de manière plus précise, sans avoir besoin de recourir aux formats de page (masquer ou regrouper les colonnes).

Conclusion

Avec quatre fonctions et l’aide de QUERY(), nous pouvons faciliter la visibilité des données. Je vous donne accès au document utilisé dans cet exemple ici pour que vous puissiez voir les formules de plus près.

Si vous avez trouvé une faute d’orthographe, veuillez nous en informer en sélectionnant le texte en question et en appuyant sur Ctrl + Entrée .

- Accompagnement professionnel ? -Formation professionnelle et services Google Workspace

LAISSER UN COMMENTAIRE

S'il vous plaît entrez votre commentaire!
S'il vous plaît entrez votre nom ici

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.