Beaucoup de gens pensent qu’il n’y a pas d’alternative à la consolidation des données Excel dans Google Sheets. Bien sûr, il n’y a pas de commande à proprement parler. Mais vous pouvez consolider les données de plusieurs feuilles à l’aide d’une formule dans Google Sheets.
Ne sous-estimez pas Google Sheets ! Il existe une fonction puissante dans Google Sheets pour effectuer la consolidation des données de manière bien meilleure ! Savez-vous quelle fonction/formule il s’agit ?
Je parle de la fonction Requête. Avec cette fonction, vous pouvez spécifier la plage de données dans plusieurs feuilles de deux manières.
Entrée manuelle (codée en dur) : Utilisez-la lorsque vous avez moins de feuilles à combiner.
Utilisation d’une fonction personnalisée : Utilisez-la lorsque vous avez plusieurs feuilles à combiner.
J’utilise uniquement les fonctions natives, sans script Apps ou plugins tiers, pour consolider les données de plusieurs feuilles.
La consolidation des données de cette manière ne se limite pas aux feuilles dans un seul fichier. Vous pouvez également utiliser des feuilles provenant de différents fichiers en les important.
Comprendre la consolidation des données de plusieurs feuilles
Je vais combiner les données de plusieurs feuilles (onglets multiples dans un classeur ou un fichier) en une seule feuille, puis les résumer.
Et qu’en est-il de la consolidation des données dans deux classeurs dans Google Sheets ?
Si c’est le cas, vous devez utiliser la fonction IMPORTRANGE pour importer d’abord les données dans une feuille maîtresse.
Je vais sauter cette partie dans ce tutoriel pour éviter toute confusion inutile.
Avantages de l’utilisation d’une formule (fonction de requête)
Voici quelques-uns des avantages de la consolidation des données de plusieurs feuilles à l’aide d’une formule dans Google Sheets :
- Consolidation des données sans script signifie de meilleures performances de la feuille de calcul.
- Les formules sont plus rapides et plus fiables si nous les écrivons correctement.
- Si nous utilisons un plugin (je ne connais pas de tel plugin au moment de la rédaction de cet article), nous ne pouvons pas garantir la disponibilité de ce plugin en permanence. Même si vous en trouvez un, ajoutez-le après avoir lu les avis des utilisateurs.
- Pas besoin de payer un développeur pour coder un script Apps pour vous.
- L’utilisation d’une formule pour consolider les données vous offre plus de flexibilité. Les données consolidées se mettront automatiquement à jour lorsque nous modifierons les données sources.
Comment consolider les données de plusieurs feuilles à l’aide d’une formule dans Google Sheets
Suivez les instructions étape par étape ci-dessous.
Ci-dessous, vous pouvez voir quatre feuilles (captures d’écran). Les deux premières feuilles, je veux dire les onglets de feuille, contiennent des données d’exemple que nous allons combiner dans la troisième feuille et résumer dans la quatrième feuille.
Nous pouvons combiner et résumer les données à l’aide d’une seule formule. Mais pour simplifier les étapes, je les fais séparément.
Remarque : Vous obtiendrez le détail de la combinaison et de la résumé des données à l’aide d’une seule requête dans la dernière partie de ce tutoriel.
Il y a donc deux étapes – la consolidation des données et la combinaison. Nous pouvons utiliser les troisième et quatrième feuilles à cet effet. Je vais diviser le tutoriel en conséquence.
Les deux premières feuilles contiennent des données d’exemple. Voici ce qu’elles contiennent.
Le nom de ma première feuille est « junesheet », et voici les données qu’elle contient.
Maintenant, voyons les données de la deuxième feuille intitulée « julysheet ».
Vous pouvez ouvrir mon fichier d’exemple ci-dessous pour voir mes données et mes formules.
Échantillon de consolidation des données
Comment combiner les données de plusieurs feuilles à l’aide de la formule de requête dans Google Sheets
Voyons maintenant comment combiner les données des deux feuilles ci-dessus en une seule feuille dans le même fichier Google Sheets. Je veux dire dans la troisième feuille intitulée « Combined ».
Voici la formule de requête pour combiner les données des deux feuilles ci-dessus en une seule feuille.
=requête({junesheet!A2:H5;julysheet!A2:H5},"Select * where Col1 is not null ")
La formule est pour nos données d’exemple ci-dessus. Vous pouvez l’utiliser telle quelle, mais en apportant les modifications nécessaires à vos données de feuille de calcul. Les modifications que vous devrez peut-être apporter sont les suivantes.
- Modifier la plage A2:H5 par votre plage de données réelle.
- Modifier les noms de feuille par vos noms de feuille réels.
- J’ai utilisé deux feuilles à combiner. Supposons qu’il y ait une autre feuille à inclure nommée « augsheet ». Mettez simplement un point-virgule et entrez le nom de la troisième feuille avec la plage.
Par exemple :
{junesheet!A2:H5;julysheet!A2:H5;augsheet!A2:H5}Important : Combinez uniquement des types de données similaires (évitez les types de données mixtes dans une colonne unique). Ne combinez jamais une colonne de texte avec une colonne de date ou une colonne numérique.
Cela va à l’encontre de la consolidation des données à l’aide de la requête. La requête renverra une sortie incorrecte lorsque des types de données mixtes sont présents dans les colonnes.
Plage infinie dans la consolidation
Si vous le souhaitez, vous pouvez utiliser des plages infinies telles que A2:H dans la formule. Dans ce cas, la formule ressemblera à ceci.
=requête({junesheet!A2:H;julysheet!A2:H},"Select * where Col1 is not null ")
Avez-vous plusieurs feuilles à combiner ? Alors vous pouvez essayer l’une de ces fonctions avec des noms personnalisés.
- REF_SHEET_TABS : Référence une liste de noms d’onglets dans une requête dans Google Sheets.
- COPY_TO_MASTER_SHEET : Combinez les données de plusieurs onglets dans Google Sheets.
Nous voulons résumer ou consolider les données que nous avons combinées à partir de plusieurs feuilles. Voici les étapes à suivre. Avant cela, regardez d’abord les données combinées ci-dessous.
Résumé ou consolidation des données de plusieurs feuilles à l’aide de la formule de requête dans Google Sheets
La partie ci-dessous utilise la fonction Requête. Vous pouvez également le faire en utilisant le tableau croisé dynamique.
En utilisant une simple formule de requête supplémentaire dans la dernière feuille intitulée « Consolidate », nous pouvons obtenir le résultat souhaité.
=requête(Combined!A1:H10,"select D, sum(H) where A is not null group by D")
Voir cette formule expliquée à l’aide d’une image ci-dessous.
J’ai utilisé le regroupement de requête pour effectuer une agrégation de somme dans cette formule de consolidation des données.
Dans nos données d’exemple, la colonne D contient la description de l’élément. J’ai résumé les données en fonction de ce champ d’élément.
Lorsque le même élément apparaît plusieurs fois, cette formule le fusionne en une seule ligne et additionne ses valeurs dans la colonne H.
Les données consolidées ressembleront à ceci.
C’est tout. J’espère que vous avez apprécié mon tutoriel et appris comment consolider les données de plusieurs feuilles à l’aide d’une formule dans Google Sheets.
Comme je l’ai promis au début, voici la seule formule qui combine et résume les données.
=requête({junesheet!A2:H;julysheet!A2:H},"select Col4, sum(Col8) where Col1 is not null group by Col4")
Que faire si le nombre de colonnes est différent dans chaque onglet ?
Normalement, cela ne se produit pas. Mais au cas où le nombre de colonnes dans la plage de données de chaque onglet serait différent, la méthode ci-dessus ne fonctionne pas.
Vous pouvez essayer la formule ci-dessous pour d’abord combiner les données, puis les consolider.
Google Sheets: Combinez deux tables avec un nombre de colonnes différent dans une requête.
La fonction Requête est très pratique si vous l’utilisez correctement. Prenez donc votre temps et apprenez la fonction Requête ci-dessus.
Au départ, je pensais partager ici quelques tutoriels supplémentaires sur la requête.
Mais il existe plus de 100 tutoriels sur la requête sur Info Inspired. Alors, mieux vaut utiliser la barre de recherche pour les trouver.
