Numeriblog Google Workspace Google Sheets, le tableur surpuissant Google Sheets : rendez votre formule Query dynamique avec des cases à cocher

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 […]

personnes ont consulté cet article

3 minutes

Rédigé par Jérémi PICCIONI - il y a 1 an

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
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.

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

Rédacteur

S’abonner
Notification pour
guest
0 Commentaires
Commentaires en ligne
Afficher tous les commentaires