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 : 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 : Dans un même classeur (fichier) Google Sheets, j’ai cinq onglets identiques par région. Je voudrais en tant que responsable […]
Ce que vous allez découvrir
- Comment associer des requêtes QUERY dans une vue matricielle sur une feuille Google Sheets
- Créer un tableau croisé dynamique (TCD) à partir d'une table de données
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 :
Dans un même classeur (fichier) Google Sheets, j’ai cinq onglets identiques par région.
Je voudrais en tant que responsable commercial, les fusionner pour avoir un tableau de bord national puis faire un TCD (tableau croisé dynamique ).
Défi relevé ! Voici la réponse apportée (plusieurs solutions existent, je fais le choix ici d’utiliser =QUERY + {}). Suivez ce tutoriel pour devenir un pro !
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 fictive. Voici l’URL de Page Random Data Generator. Plus besoin d’importer ou d’en créer une pour l’occasion, pas mal non ?
Ce qui nous permet d’avoir ce fichier comme base d’exercices, vous pouvez exporter vos données générées de cette manière. 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 de calcul, 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.
Ps : n’oubliez pas de renommer vos feuilles afin de pouvoir retrouver vos données plus facilement.
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 de calcul 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 :
Merci d’avoir lu cet article, vous pouvez consulter notre boutique dès maintenant pour en apprendre davantage sur la suite Google Workspace, par exemple vous pouvez retrouver notre escape game 100% dédié à l’univers Google.
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
Thierry VANOFFE, consultant, formateur, coach Google Workspace CEO de Numericoach, leader de la formation Google Workspace en France. Passionné par Google, ce blog me permet de partager cette passion et distiller tutos, trucs, astuces, guides sur les outils Google. N'hésitez pas à me solliciter pour vos projets de formation.
