Google Sheets : comment consolider des données dans une même feuille.

Lors d’une récente formation Google Sheets dispensée par Numericoach, cette question de Charlotte est tombée :

Défi relevé ! Voici la réponse apportée (plusieurs solutions existent, je fais le choix ici d’utiliser =QUERY + {}).

Créons tout d’abord une base de données pour reproduire ce cas d’usage. En effet, les données d’origine étant sensibles, nous ne les partagerons pas ici. Pour cela, j’utilise un excellent site qui m’aide en quelques clics à générer une base de données fictives. Voici l’URL de Page Random Data Generator. Pas mal non ?

Ce qui nous permet d’avoir ce fichier comme base d’exercices. Comme la maison ne recule devant aucun sacrifice, nous vous partageons ce fichier pour que vous puissiez, cher lecteur, vous entraîner avec les formules. Canon !

Le fichier est composé de trois feuilles, les deux premières construites sur la même structure et la troisième pour la consolidation des deux premières. Vous pourrez et devrez évidemment modifier dans la formule sur votre fichier les noms de feuilles et plages de cellules.

Cette base RH contient les mêmes champs pour les feuilles 1 et 2.

Pour assembler le tout, il est opportun ici d’utiliser une formule matricielle (qui va assembler des tableaux) et Query, puissante formule couteau suisse qui va notamment exécuter la requête mais aussi retirer les lignes vides.

Comment associer des requêtes QUERY dans une vue matricielle sur une feuille Google Sheets

La formule proposée à Charlotte dans la cellule A1 de la feuille appelée Consolidation est donc :

={QUERY('Feuille 1'!A:L;"select * where A is not null") ; QUERY('Feuille 2'!A2:L;"select * where A is not null")}

Décomposons pour mieux comprendre cette formule qui peut paraître à première vue barbare.

={ ; }

vous permet d’assembler les tableaux. Le « ; » permet de les mettre les uns en dessous des autres contrairement à un « \ ». Je vous invite à découvrir sur le blog les articles consacrés aux formules matricielles. Si vous avez comme Charlotte, cinq onglets, il faudra en effet rajouter des « ; » comme ci-dessus entre les deux formules QUERY.

=QUERY(‘Feuille 1’!A:L; »select * where A is not null »)

exécute la requête et donc retourne la plage A:L de la Feuille 1. Le « Select * » permet d’obtenir toutes les colonnes quand la colonne A n’est pas vide et c’est indispensable pour retirer les lignes vides ! (where A is not null).

QUERY(‘Feuille 2’!A2:L; »select * where A is not null »)

Dans le second argument, j’ai pris le soin de démarrer la plage à A2 pour ne pas répéter les entêtes.

Je vous invite à tester, modifier la formule dans le fichier d’exemple et compléter cet article en ajoutant un commentaire avec d’autres formules possibles afin d’arriver au même résultat.

La formule matricielle affiche parfois un message d’erreur difficile à interpréter. Voici un article qui permet de trouver les solutions.

Si besoin de monter rapidement et efficacement avec l’excellente formule =QUERY, je ne peux que vous conseiller ce guide pratique rassemblant plus de 80 pages et 62 fichiers d’exercices.

Créer un tableau croisé dynamique (TCD) à partir d’une table de données

Charlotte demandait de pouvoir également faire un TCD rassemblant les données de toutes ses feuilles, ça sera effectivement plus facile avec cet unique onglet de consolidation.

Démonstration :