De nombreuses personnes pensent qu’il n’y a pas d’alternative de consolidation des données Excel dans Google Sheets. Bien sûr, il n’y a pas de commande spécifique pour cela. Mais vous pouvez consolider les données de plusieurs feuilles en utilisant une formule dans Google Sheets.
Ne sous-estimez pas Google Sheets! Il existe une fonction puissante dans Google Sheets pour consolider les données de manière beaucoup plus efficace! Savez-vous quelle fonction/formule il s’agit?
Je parle de la fonction Query. Avec cette fonction, vous pouvez spécifier la plage de données de plusieurs feuilles de deux manières différentes.
-
Entrée manuelle (codée en dur) : Utilisez cette méthode lorsque vous avez moins de feuilles à combiner.
-
Utilisation d’une fonction personnalisée nommée : Utilisez cette méthode lorsque vous avez plusieurs feuilles à combiner.
J’utilise uniquement les fonctions natives, sans script d’application ou extensions tierces, pour consolider les données de plusieurs feuilles.
La consolidation des données de cette manière ne se limite pas aux feuilles d’un seul fichier. Vous pouvez également utiliser des feuilles provenant de fichiers différents 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 fichier) en une seule feuille, puis les résumer.
Et qu’en est-il de la consolidation des données de 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 principale.
Je vais sauter cette partie dans ce tutoriel pour éviter toute confusion inutile.
Avantages de l’utilisation d’une formule (fonction Query)
Voici quelques avantages de la consolidation des données de plusieurs feuilles à l’aide d’une formule dans Google Sheets :
- La consolidation des données sans script améliore les performances du tableur.
- Les formules sont plus rapides et plus fiables si nous les écrivons correctement.
- Si nous utilisons une extension (je ne connais aucune extension de ce type au moment où j’écris cet article), nous ne pouvons pas garantir sa disponibilité en permanence. Même si vous en trouvez une, ajoutez-la après avoir lu les avis des utilisateurs.
- Pas besoin de payer un centime pour engager un développeur pour coder un script d’application pour vous.
- L’utilisation d’une formule pour consolider les données 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 en utilisant une formule dans Google Sheets
Veuillez suivre les instructions étape par étape ci-dessous.
Ci-dessous, vous pouvez voir quatre feuilles (captures d’écran). Les deux premières feuilles, ou onglets, 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 en utilisant une seule formule. Mais pour simplifier les étapes, je vais le faire séparément.
Remarque : Vous obtiendrez les détails pour combiner et résumer les données à l’aide d’une seule requête dans la dernière partie de ce tutoriel.
Il y a donc deux étapes à suivre : la consolidation des données et la combinaison. Les troisième et quatrième feuilles peuvent être utilisées à cette fin. Je vais diviser le tutoriel ci-dessous en conséquence.
Les deux premières feuilles contiennent des données d’exemple. Voici à quoi elles ressemblent.
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 nommé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 en utilisant la formule Query dans Google Sheets
Voyons maintenant comment combiner les données des deux feuilles ci-dessus dans une seule feuille du même fichier Google Sheets, à savoir la troisième feuille appelée « Combined ».
Voici la formule Query pour combiner les données des deux feuilles ci-dessus en une seule feuille :
=query({JuneSheet!A2:H5;JulySheet!A2:H5},"Select * where Col1 is not null ")
La formule est conçue pour nos données d’exemple ci-dessus. Vous pouvez l’utiliser telle quelle, mais avec des modifications adaptées à vos propres données de feuille de calcul. Voici les modifications que vous devrez peut-être apporter :
- Changez la plage A2:H5 par votre plage de données réelle.
- Changez les noms de feuille par les noms de vos feuilles réelles.
- J’ai utilisé deux feuilles à combiner. Supposons qu’il y ait une autre feuille à inclure appelée « AugSheet ». Ajoutez 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 : Ne combinez que des types de données similaires (évitez les types de données mixtes dans une seule colonne). 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 fonction Query. La requête renverra un résultat incorrect lorsque des types de données mixtes se trouvent dans les colonnes.
Plage infinie dans la consolidation
Si vous le souhaitez, vous pouvez utiliser des plages infinies comme A2:H dans la formule. Dans ce cas, la formule sera la suivante :
=query({JuneSheet!A2:H;JulySheet!A2:H},"Select * where Col1 is not null ")
Avez-vous plusieurs feuilles à combiner ? Vous pouvez essayer l’une de ces fonctions personnalisées.
- REF_SHEET_TABS : Fait 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 les données combinées ci-dessous.
Résumé ou consolidation des données de plusieurs feuilles en utilisant la formule Query dans Google Sheets
La partie suivante utilise la fonction Query. Vous pouvez également le faire en utilisant un tableau croisé dynamique.
En utilisant une autre formule Query dans la feuille « Consolidate », nous pouvons obtenir le résultat souhaité.
=query(Combined!A1:H10,"select D, sum(H) where A is not null group by D")
Voici cette formule expliquée à l’aide d’une image ci-dessous.
J’ai utilisé le regroupement Query pour effectuer une agrégation somme dans cette formule de consolidation des données.
Dans notre 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 fusionne les données en une seule ligne et somme 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 en utilisant 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.
=query({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 feuille ?
Cela n’arrive généralement pas, mais au cas où le nombre de colonnes de la plage de données de chaque feuille serait différent, la méthode ci-dessus ne fonctionnerait pas.
Vous pouvez essayer la formule ci-dessous pour d’abord combiner les données, puis les consolider.
Google Sheets : Combine Two Tables with Different Numbers of Columns in a Query
La fonction Query est très pratique si vous l’utilisez correctement. Prenez donc votre temps et apprenez les bases de la fonction Query grâce à ce tutoriel.
Au départ, je pensais partager quelques tutoriels Query supplémentaires ici.
Mais il y a plus de 100 tutoriels Query sur Crawlan.com. Il vaut mieux utiliser la barre de recherche pour les trouver.
