Dans ce tutoriel Google Sheets, vous découvrirez une fonction personnalisée ainsi qu’une formule pour combiner dynamiquement les données verticalement à partir de plusieurs onglets de feuilles.
Google Sheets et des applications similaires permettent d’organiser les données dans des onglets de feuilles au sein d’un classeur, facilitant ainsi l’organisation des données. Cependant, cela pose un problème.
Par exemple, si vous avez des données mensuelles organisées dans différents onglets dans Google Sheets, la création d’un résumé peut nécessiter de copier et coller ces données dans une feuille principale. Alternativement, vous pourriez avoir besoin de spécifier chaque plage individuellement dans une formule VSTACK comme suit:
=VSTACK(Onglet1!A2:C5, Onglet2!A2:C5, Onglet3!A2:C5)
Et si vous spécifiez la plage sous forme de liste et l’utilisez de manière dynamique ?
Pour contourner le besoin de copier-coller ou de spécifier individuellement les plages de données, nous vous présentons la fonction COPY_TO_MASTER_SHEET.
Caractéristiques de la formule pour combiner dynamiquement les onglets de feuilles verticalement
La formule (également la fonction nommée) combine les données de plusieurs onglets de feuilles dans Google Sheets et offre les fonctionnalités suivantes :
Ajout vertical et séquentiel : La formule ajoute les données verticalement et de manière séquentielle, créant ainsi un tableau plus grand. En d’autres termes, les données du deuxième onglet seront situées en dessous du premier onglet, les données du troisième onglet en dessous du deuxième onglet, et ainsi de suite.
Conservation des types de données : La fonction COPY_TO_MASTER_SHEET ainsi que la formule n’utilisent pas la fonction SPLIT, ce qui permet de conserver les types de données dans le résultat.
Recommandation pour les plages de données fermées : Je vous suggère d’utiliser des plages de données fermées dans la formule. Par exemple, utilisez A1:C1000, et non A1:C, car la combinaison des données verticalement peut entraîner plusieurs lignes vides sous les données de chaque onglet. Si vous préférez une plage ouverte, enveloppez ma fonction nommée ou ma formule avec QUERY ou FILTER pour filtrer les lignes vides.
Prérequis
Supposons que vous souhaitiez combiner dynamiquement les données de feuille dans Onglet1, Onglet2 et Onglet3 dans un quatrième onglet nommé « Master ». La formule nécessite que tous les noms d’onglets soient répertoriés dans l’onglet « Master ».
Vous pouvez saisir manuellement les noms d’onglets dans la colonne A de l’onglet « Master » ou utiliser un script Google Apps, que vous pouvez obtenir sur le site webapps.stackexchange.com.
Pour ajouter le script, suivez ces étapes :
- Allez dans le menu « Extensions » de votre Google Sheets et sélectionnez « Apps Script ».
- Supprimez le texte par défaut. Copiez-collez le script Apps Script à partir de la page mentionnée ci-dessus et enregistrez le projet en sélectionnant l’icône de sauvegarde.
Note : Vous pouvez également l’obtenir en créant une copie de ma feuille d’exemple fournie quelques paragraphes plus bas.
Utilisez la formule suivante dans la cellule A1 de l’onglet « Master » pour obtenir tous les noms d’onglets :
=sheetnames()
Lorsque vous utilisez ma fonction COPY_TO_MASTER_SHEET ou une formule pour combiner les données des onglets « Onglet1 », « Onglet2 » et « Onglet3 » dans l’onglet « Master », ne spécifiez pas A1:A. Spécifiez uniquement A2:A.
Cela est dû au fait que A1 contiendra le nom « Master », que nous ne voulons pas inclure dans la combinaison.
Combinez les données dynamiquement dans plusieurs onglets verticalement : Formule
Nous allons fusionner verticalement les données des onglets Onglet1, Onglet2 et Onglet3 dans la plage A2:C5 dans l’onglet « Master ».
Voici le résultat attendu après la fusion verticale des onglets 1 à 3 de manière dynamique.
Pour les données d’exemple, la fonction nommée et la formule, cliquez sur le bouton ci-dessous pour copier la feuille.
Dans l’exemple ci-dessus (veuillez vous référer à la capture d’écran ci-dessus), j’ai utilisé la formule suivante dans la cellule B2 :
=REDUCE( TOCOL(, 1), TOCOL(A2:A, 1), LAMBDA(a, v, IFERROR(VSTACK(a, INDIRECT(v&"!"&"A2:C5")))) )
Où :
- A2:A est la liste des noms d’onglets.
- « A2:C5 » est la plage dans chaque onglet à fusionner verticalement.
Maintenant, comprenons comment la formule combine dynamiquement plusieurs onglets de feuilles verticalement dans Google Sheets. Voici l’explication de la formule.
Note : Lorsque vous utilisez des plages ouvertes dans la formule, par exemple « A2:C » au lieu de « A2:C5 », enveloppez la formule avec QUERY, comme suit :
=QUERY(..., "SELECT * WHERE Col1 IS NOT NULL", 0)
Explication de la formule
La formule utilise la fonction REDUCE pour combiner dynamiquement plusieurs onglets de feuilles verticalement. Voici la syntaxe :
Syntaxe :
REDUCE(initial_value, array_or_range, lambda)
Dans la formule :
- initial_value :
TOCOL(, 1)– La fonction TOCOL renvoie une cellule vide si elle est spécifiée sous la forme TOCOL(, 0). En changeant 0 en 1, elle élimine cette cellule vide, remplissant ainsi la condition initial_value requise pour REDUCE. Elle ne renvoie essentiellement rien. - array_or_range :
TOCOL(A2:A, 1)– Elle renvoie la liste des noms d’onglets, en supprimant les cellules vides.
Voici la partie Lambda de la formule :
- a : nom de l’accumulateur.
- v : chaque valeur (nom d’onglet) provenant de array_or_range.
La fonction REDUCE itère sur chaque nom d’onglet dans array_or_range, en utilisant la fonction INDIRECT(v& »!A2:C5″). À chaque itération, v représente Onglet1, Onglet2 et Onglet3.
Le résultat à chaque étape est stocké dans l’accumulateur, qui est ensuite empilé verticalement à l’aide de VSTACK en tant que VSTACK(a, …). Cela définit la logique REDUCE derrière la combinaison dynamique de plusieurs onglets verticalement.
Combinez les données dynamiquement dans plusieurs onglets verticalement : Fonction nommée
Ma formule ci-dessus est déjà prête à être utilisée en tant que fonction personnalisée.
Il vous suffit de la copier-coller dans la partie de définition de formule du menu Données -> Fonctions nommées -> Ajouter de nouvelles fonctions. Remplacez les références de plage de cellules par des noms et ajoutez ces noms dans les espaces réservés des arguments.
Je l’ai fait pour vous. Il vous suffit de l’importer dans votre feuille pour l’utiliser.
Syntaxe :
COPY_TO_MASTER_SHEET(list, array)
Arguments :
- list : La cellule ou la plage de cellules qui contient la liste des onglets à combiner.
- array : La plage de données à combiner est spécifiée entre guillemets.
Exemple :
Nous pouvons remplacer notre formule précédente dans Master!B2 par ma fonction nommée COPY_TO_MASTER_SHEET de la manière suivante :
=COPY_TO_MASTER_SHEET(A2:A,"A2:C5")
Lorsque vous souhaitez combiner plusieurs feuilles dynamiquement avec des plages ouvertes, utilisez la formule suivante incluant QUERY :
=QUERY(COPY_TO_MASTER_SHEET(A2:A,"A2:C"), "SELECT * WHERE Col1 IS NOT NULL", 0)
Grâce à cette fonction nommée, tout le monde peut rapidement combiner des données de plusieurs onglets de feuilles dans un onglet de feuille principale dans Google Sheets.
Comment copier la fonction nommée COPY_TO_MASTER_SHEET
Voici les étapes rapides :
- Pour utiliser une fonction nommée, importez-la dans la feuille où vous souhaitez qu’elle fonctionne.
- Tout d’abord, faites une copie de ma feuille d’exemple ci-dessus.
- Allez dans Fichier -> Paramètres et sous « Lieu », sélectionnez le même pays que la feuille dans laquelle vous souhaitez utiliser ma fonction.
- Ensuite, ouvrez la feuille dans laquelle vous souhaitez utiliser ma fonction COPY_TO_MASTER_SHEET.
- Allez dans le menu Données de cette feuille. Sélectionnez Fonctions nommées -> Importer une fonction.
- Sélectionnez la feuille copiée (vous pouvez rechercher par nom de feuille) et sélectionnez Insérer. Suivez les instructions à l’écran qui suivent.
Vous trouverez le code à importer.
Ressources
Ce tutoriel a couvert une formule ainsi qu’une fonction nommée pour combiner dynamiquement les données verticalement à partir de plusieurs onglets de feuilles. Vous êtes intéressé par l’empilement horizontal ? Vous trouverez le tutoriel correspondant et quelques astuces supplémentaires dans les ressources ci-dessous.
- Consolidation des données de plusieurs feuilles à l’aide d’une formule dans Google Sheets
- Consolidation uniquement de la dernière ligne dans plusieurs feuilles dans Google Sheets
- Comment combiner plusieurs feuilles dans Importrange et les contrôler via un menu déroulant
- Empiler horizontalement plusieurs feuilles dans Google Sheets de manière dynamique
- SOMME.SI sur plusieurs feuilles dans Google Sheets
- RECHERCHEV sur plusieurs feuilles dans Google Sheets
- Comment inclure les futures feuilles dans les formules Google Sheets
C’est tout pour aujourd’hui ! J’espère que vous avez trouvé ces astuces utiles pour combiner les données dynamiquement dans plusieurs onglets verticalement dans Google Sheets. Si vous avez des questions, n’hésitez pas à les poser. Et n’oubliez pas de visiter Crawlan.com pour plus de conseils et de tutoriels Google Sheets.
