Sheets 
Agenda 
Forms 
Apps Script 
Gmail 
Chat 
Meet 
Contacts 
Chrome 
Sites 
Looker Studio 
Slides 
Docs 
Drive 
AppSheet 
Android 
Chromebook 
Equipements Google 
Google Cloud Platform 
Google Photos 
Maps 
Youtube 
Google Sheets : rendez votre formule Query dynamique avec des cases à cocher
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 […]
Google Sheets : rendez votre formule Query dynamique avec des cases à cocher
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
Vous obtiendrez une feuille comme celle-ci :
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.
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.
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);""))
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 :
| A | B | C | D |
| Col1 | Col4 |
=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.
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);"")))
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)
- 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.
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.
Besoin d'un peu plus d'aide sur Sheets ?
Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Sheets, que ce soit pour votre entreprise ou pour vos besoins personnels !
Découvrir nos formations Sheets- Articles connexes
- Plus de l'auteur
