Combiner plusieurs tableaux Excel avec une formule : la méthode complète pour consolider vos données automatiquement

Dans ce tutoriel, je vais vous montrer comment produire un rapport de ventes complet, par responsable et par produit, en combinant automatiquement plusieurs tableaux Excel — avec une seule formule, sans copier-coller, et sans jamais toucher à vos données sources.

Concrètement, imaginez deux équipes commerciales qui saisissent leurs ventes dans deux tableaux complètement différents, avec des colonnes qui ne se ressemblent pas. Jusqu'ici, votre seule option était de tout copier-coller à la main, de croiser les doigts pour ne pas faire d'erreur, et de recommencer à zéro à chaque mise à jour.

Eh bien, c'est terminé.

Nous allons voir ensemble comment utiliser GROUPER.PAR d'une façon que la plupart des utilisateurs d'Excel ne connaissent pas encore : en le combinant avec d’autres fonctions Excel surpuissantes pour créer un rapport qui se met à jour tout seul, dès que l'une des sources change.

Restez bien jusqu’à la fin pour découvrir une formule complète, prête à l'emploi, que vous pouvez adapter à n'importe quelle situation.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons utiliser le tableau suivant dans lequel nous retrouvons les données de vente d'une chaîne de magasins de sport, réparties dans deux tableaux distincts selon les équipes commerciales, ainsi qu'un troisième tableau listant les responsables par région.

La première chose à faire pour exploiter ces tableaux de la meilleur des manières est de les transformer en tableau structuré.

Pour cela, nous cliquons dans l'une des cellules du premier bloc, puis nous appuyons sur [Ctrl]+[L].

Dans la fenêtre qui s'ouvre, nous vérifions que la plage est correcte, et que l’option « Mon tableau comporte des en-têtes » est bien cochée, puis nous cliquons sur « OK ».

Nous nous rendons ensuite dans l'onglet « Création de tableau » et nous donnons au tableau le nom « VentesNord ».

Nous répétons l'opération pour les deux autres tableaux, en leur donnant respectivement les noms « VentesSud » et « Responsables ».

Ce nommage va rendre nos formules bien plus lisibles, en reprenant le nom de chaque tableau est des colonnes utilisées, plutôt qu'une plage de cellules classique comme « $C$7:$C$18 », et surtout il rend la formule dynamique — si nous ajoutons une ligne dans un tableau, tout se recalcule automatiquement.

2. Exemple de base — GROUPER.PAR sur deux tableaux avec ASSEMB.V

   2.1 Le problème : GROUPER.PAR ne voit qu'un seul tableau à la fois

Pour regrouper et agréger des données, nous pouvons utiliser la fonction Excel GROUPER.PAR.

Sauf que sa seule véritable limite est qu’elle ne travaille nativement que sur une seule source de données.

Si nos ventes sont réparties dans VentesNord et VentesSud, il faut d'abord les empiler en une seule colonne, avant de les regrouper.

C'est exactement le rôle d’une autre fonction Excel très puissance : la fonction ASSEMB.V.

   2.2 Empiler les régions avec ASSEMB.V

ASSEMB.V (« Assembler Verticalement ») prend plusieurs plages et les colle les unes sous les autres, comme si on empilait des feuilles de papier.

Sa syntaxe est la suivante :

  =ASSEMB.V(tableau1; tableau2; ...) 

Dans notre cas, nous voulons créer une liste unifiée de toutes les régions, toutes sources confondues.

Nous nous plaçons dans une cellule vide de notre feuille de calcul — par exemple la cellule N8 — et nous saisissons :

  =ASSEMB.V(VentesNord[Région]; VentesSud[Région]) 

Quand nous appuyons sur [Entrée], Excel affiche automatiquement une liste de 20 régions : les 12 de VentesNord suivies des 8 de VentesSud.

C'est ce qu'on appelle le déversement : Excel étend le résultat sur autant de lignes que nécessaire, sans que nous ayons à sélectionner la plage au préalable.

   2.3 Retrouver le responsable de chaque ligne avec RECHERCHEX

Maintenant que nous avons notre liste de régions, ce que nous voulons c’est afficher dans notre rapport le nom du responsable associé à chaque région.

Pour cela, nous allons envelopper notre ASSEMB.V dans une RECHERCHEX.

En effet, RECHERCHEX cherche une valeur dans une colonne, et retourne la valeur correspondante dans une autre colonne.

Ici, nous cherchons chaque région dans la colonne Responsables[Région], et nous voulons récupérer la colonne Responsables[Responsable].

Ce qui donne :

  =RECHERCHEX( ASSEMB.V(VentesNord[Région]; VentesSud[Région]);  Responsables[Région]; Responsables[Responsable] ) 

Le résultat est une colonne de 20 noms de responsables, un pour chaque ligne de vente.

C'est exactement ce qu'il nous faut pour alimenter GROUPER.PAR.

   2.4 Le GROUPER.PAR final

Nous avons maintenant tous les ingrédients.

Nous enveloppons le tout dans GROUPER.PAR pour obtenir le total des ventes par responsable :

  =GROUPER.PAR( RECHERCHEX( ASSEMB.V(VentesNord[Région]; VentesSud[Région]);  Responsables[Région]; Responsables[Responsable] );  ASSEMB.V(VentesNord[Montant]; VentesSud[Montant]); SOMME ) 

GROUPER.PAR attend trois arguments :

• ce par quoi nous regroupons (les noms des responsables),
• ce que nous calculons (les montants),
• et la fonction à appliquer (SOMME).

La règle fondamentale est que ces deux premières colonnes doivent contenir le même nombre de lignes — ici, 20 dans les deux cas.

Le résultat s'affiche automatiquement : chaque responsable apparaît avec son total de ventes, toutes sources confondues.

Et si nous ajoutons une ligne dans l'un des deux tableaux, le rapport se met à jour sans aucune intervention supplémentaire.

3. Exemple avancé — LET + GROUPER.PAR + ASSEMB.H + ETENDRE

   3.1 Un rapport à trois dimensions

Nous voulons maintenant aller plus loin : obtenir un rapport qui détaille les ventes par Produit, par Responsable, et par Source (c'est-à-dire indiquer si la vente vient de VentesNord ou VentesSud).

Le problème est que cette colonne « Source » n'existe nulle part dans nos données — nous allons devoir la créer de toutes pièces, directement dans la formule.

Pour éviter d'écrire une formule illisible, nous allons utiliser la fonction LET, qui nous permet de nommer chaque calcul intermédiaire et de le réutiliser ensuite.

C'est comme écrire un brouillon structuré à l'intérieur même de la formule.

   3.2 Construction étape par étape avec LET

Nous nous plaçons dans une cellule vide — par exemple M6 d'une nouvelle feuille — et nous commençons à saisir notre formule.

La structure de LET est la suivante : on donne un nom, puis une valeur, puis un autre nom, puis une valeur, et ainsi de suite, et tout à la fin on indique ce que l'on veut afficher.

Nous déclarons d'abord regionField, qui empile les régions des deux tableaux :

  =LET(
    regionField; ASSEMB.V(VentesNord[Région]; VentesSud[Région]); 

Ensuite, nous déclarons colProduit pour les produits :

   colProduit;  ASSEMB.V(VentesNord[Produit]; VentesSud[Produit]); 

Puis colResponsable, en réutilisant regionField que nous venons de définir — c'est tout l'avantage de LET, nous n'avons pas besoin de répéter le ASSEMB.V :

   colResponsable;  RECHERCHEX(regionField; Responsables[Région]; Responsables[Responsable]); 

   3.3 Créer la colonne Source avec ETENDRE

Nous arrivons à la partie la plus originale.

Pour créer une colonne qui contient « Région Nord » sur 12 lignes, puis « Région Sud » sur 8 lignes, nous utilisons la fonction ETENDRE.

Elle prend une valeur de départ et la répète sur le nombre de lignes souhaité :

  =ETENDRE("Région Nord"; LIGNES(VentesNord); 1; "Région  Nord") 

LIGNES(VentesNord) retourne automatiquement le nombre de lignes du tableau, soit 12.

Le 1 indique que nous voulons une seule colonne.

Le dernier argument — encore "Région Nord" — est la valeur de remplissage pour toutes les lignes.

Nous empilons ensuite les deux blocs avec ASSEMB.V pour former colSource :

   colSource;  ASSEMB.V(
       ETENDRE("Région Nord"; LIGNES(VentesNord); 1; "Région Nord");
       ETENDRE("Région Sud"; LIGNES(VentesSud); 1; "Région Sud")
   ); 

   3.4 Assembler les colonnes avec ASSEMB.H

Nous avons maintenant trois colonnes : colProduit, colResponsable, colSource.

Nous les assemblons côte à côte avec ASSEMB.H (« Assembler Horizontalement ») pour former notre tableau de regroupement :

donnees; ASSEMB.H(colProduit; colResponsable; colSource);

valeurs; ASSEMB.V(VentesNord[Montant]; VentesSud[Montant]);

tableau; GROUPER.PAR(donnees; valeurs; SOMME);

tableau

)

   3.5 La formule complète

Voici la formule dans son intégralité, prête à copier-coller :

  =LET( regionField; ASSEMB.V(VentesNord[Région]; VentesSud[Région]); colProduit;  ASSEMB.V(VentesNord[Produit]; VentesSud[Produit]); colResponsable;  RECHERCHEX(regionField; Responsables[Région]; Responsables[Responsable]); colSource;  ASSEMB.V(ETENDRE(B3; LIGNES(VentesNord); 1; B3); ETENDRE(E1; LIGNES(VentesSud);  1; E1)); donnees; ASSEMB.H(colProduit; colResponsable; colSource); valeurs;  ASSEMB.V(VentesNord[Montant]; VentesSud[Montant]); résultat; GROUPER.PAR(donnees;  valeurs; SOMME); résultat ) 

Le résultat affiché est un tableau structuré avec quatre colonnes : Produit, Responsable, Source et Total des ventes.

Chaque combinaison apparaît sur sa propre ligne, et le tout se recalcule instantanément si nous modifions l'une des données sources.

4. Règles à respecter et pièges à éviter

La règle la plus importante avec GROUPER.PAR est que row_fields et values doivent toujours contenir le même nombre de lignes.

Si ce n'est pas le cas, Excel retourne une erreur #VALEUR!.

Pour vérifier rapidement, nous pouvons saisir en parallèle :

  =LIGNES(ASSEMB.V(VentesNord[Région]; VentesSud[Région])) 

et comparer avec :

  =LIGNES(ASSEMB.V(VentesNord[Montant]; VentesSud[Montant])) 

Les deux doivent retourner la même valeur.

Enfin, je vous rappelle que ces fonctions — GROUPER.PAR, ASSEMB.V, ASSEMB.H, ETENDRE et LET — sont disponibles uniquement dans Excel 365. Elles ne fonctionnent pas dans Excel 2019 ou les versions antérieures.

Nous avons vu dans ce tutoriel comment dépasser les limites habituelles de GROUPER.PAR en le combinant avec ASSEMB.V pour empiler les tableaux, RECHERCHEX pour enrichir les données à partir d'une table externe, ETENDRE pour générer des colonnes qui n'existent pas dans les sources, ASSEMB.H pour les assembler côte à côte, et LET pour organiser tout cela de façon lisible et maintenable.

La prochaine fois que vous recevez deux exports de deux équipes différentes, vous saurez exactement quoi faire — une formule, et le rapport est prêt.