Comment fusionner des tableaux, les trier et supprimer les doubles avec une seule formule Excel ? (ASSEMB.V et ASSEMB.H)

Dans ce tutoriel, je vais vous montrer comment utiliser la fonction ASSEMB.V sur Excel pour empiler vos données de manière simple et efficace. Que vous soyez débutant ou utilisateur expérimenté, cette fonction vous permettra de gagner du temps et d'organiser vos tableaux de données de manière claire et précise.

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. Introduction à la fonction ASSEMB.V

La fonction ASSEMB.V est une fonction puissante disponible dans Excel 365. Elle permet de combiner plusieurs plages ou tableaux de données verticalement en un seul grand tableau. Lorsque nous utilisons la fonction ASSEMB.V, chaque tableau suivant est ajouté au bas du tableau précédent, créant ainsi une pile verticale de données.

Cette fonction va donc nous permettre de consolider des données provenant de différentes sources ou fragmentées sur plusieurs feuilles de calcul. Elle simplifie le processus d'organisation et de gestion de nos données, tout en maintenant leur structure et leur lisibilité.

Pour illustrer ce tutoriel, nous allons utiliser l’exemple suivant, dans lequel nous retrouvons les ventes réalisées par une entreprise au cours de l’année 2022.

Ces ventes sont décomposées par trimestres, chaque produit étant présenté dans un tableau propre.

2. Utilisation basique de la fonction ASSEMB.V

Tout d’abord, et comme nous venons de le voir, l’utilisation de la fonction ASSEMB.V nécessite Excel 365.

Outre le fait que vous ne pourrez pas l’utiliser si vous n’avez pas souscrit à l’offre 365, cela veut également dire qu’il faut faire attention avant de partager vos fichiers, de vous assurer que les destinataires utilisent également cette version d’Excel, sinon ces derniers auront des erreurs à la place du résultat souhaité.

La syntaxe de la fonction ASSEMB.V est la suivante :

 =ASSEMB.V(matrice1;[matrice2];...) 

Elle attend donc en argument une ou plusieurs matrices à ajouter que nous allons souhaiter combiner en tant que résultat.

Il faut savoir que la fonction ASSEMB.V retourne un tableau en ajoutant chaque argument les uns à la suite des autres.

Ce tableau aura donc un nombre de lignes correspondant à la somme de toutes les lignes de chaque argument du tableau et le nombre de colonnes est égal au nombre maximal de colonnes parmi tous les arguments du tableau.

Ainsi, si un tableau a moins de colonnes que la largeur maximale des tableaux sélectionnés, Excel renvoie une erreur #N/A dans les colonnes supplémentaires. Nous verrons un peu plus tard qu’il est possible d’utiliser la fonction ASSEMB.V à l'intérieur de la fonction SIERREUR ou SI.NON.DISP pour remplacer #N/A par la valeur de votre choix.

Note : Il existe également la fonction ASSEMB.H qui permet d'ajouter des tableaux horizontalement de manière similaire à la fonction ASSEMB.V.

3. Assembler les ventes trimestrielles

Pour combiner rapidement les ventes totales de l’entreprise, nous pourrons donc utiliser la fonction ASSEMB.V.

Pour cela, nous nous plaçons sur la cellule dans laquelle nous souhaitons insérer le tableau de synthèse (ici, la cellule A14), puis nous y insérons la formule suivante, dans laquelle nous saisissons les références aux données du tableau en tant qu’argument :

 =ASSEMB.V(A9:C12;E9:G12;I9:K12) 

Lorsque nous validons la formule en appuyant sur la touche [Entrée], et Excel va bien nous présenter les données présentes dans les trois tableaux, empilées verticalement dans la cellule sélectionnée.

Le résultat de cette fonction n’est donc pas retourné classiquement en tant que résultat de la cellule de destination, mais bien saisi dans un ensemble de plusieurs cellules, de manière dynamique et intelligente.

Il faut en effet savoir que la fonction ASSEMB.V, tout comme les fonctions UNIQUE, FILTRE, SEQUENCE, TRIER,… font partie des « fonctions matricielles dynamiques » d’Excel 365, qui permettent de renvoyer des résultats sur plusieurs cellules sans nécessiter la présélection de toutes les cellules de destination. Ces fonctions simplifient la création de tableaux de bord élaborés en évitant les contraintes liées à la sélection manuelle des cellules de sortie.

4. Ajouter des en-têtes à vos données empilées

Maintenant que nous avons vu comment récupérer rapidement les données de plusieurs tableaux, voyons comment ajouter des en-têtes à vos données empilées.

En effet, au-delà des données en elles-mêmes, les en-têtes de colonnes sont essentiels pour identifier les données et les rendre plus lisibles.

Pour ajouter rapidement les en-têtes de colonne en haut, du tableau, il existe plusieurs méthodes.

La première d’entre elles consiste tout simplement à inclure les cellules de l’en-tête en tant que premier argument de la fonction ASSEMB.V :

 =ASSEMB.V(A8:C8;A9:C12;E9:G12;I9:K12) 

Sinon, si vous préférez conserver le même nombre d’arguments que le nombre de tableaux empilés, il suffit d’intégrer l’en-tête sur la première plage de cellules utilisée en argument :

 =ASSEMB.V(A8:C12;E9:G12;I9:K12) 

Les résultats sont alors identiques.

Ensuite, la troisième méthode que nous allons découvrir ici va consister à saisir directement ces en-têtes en tant que matrice, manuellement.

Pour cela, les en-têtes sont saisis entre accolades, et chaque terme entre guillemets et séparé par un point :

 =ASSEMB.V({"Produit"."Période"."Quantités  vendues"};A9:C12;E9:G12;I9:K12) 

Cette méthode est un peu plus longue à mettre en place, mais elle a pour avantage de permettre de personnaliser ces en-têtes.

5. Gérer les erreurs avec la fonction ASSEMB.V

Maintenant, comme nous l’avons vu un peu tôt, lorsque le nombre de colonnes n’est pas le même sur tous les tableaux, la fonction ASSEMB.V pourra nous renvoyer une erreur #N/A.

Pour ce nouvel exemple, nous allons souhaiter récupérer le montant du CA correspond à ces ventes dans le tableau de synthèse, mais comme vous pouvez le constater, cette information n’est disponible que pour le produit C :

Nous allons donc appeler la fonction ASSEMB.V comme nous l’avons vu un peu plus tôt :

 =ASSEMB.V(A9:C12;E9:G12;I9:L12) 

Ici, lorsque l’information est manquante, Excel nous affiche une erreur.

Pour gérer ces erreurs, nous pouvons utiliser la fonction SIERREUR ou SI.NON.DISP pour remplacer les valeurs manquantes par un texte ou une valeur spécifique.

Si nous souhaitons obtenir une cellule vide :

 =SIERREUR(ASSEMB.V(A9:C12;E9:G12;I9:L12);"")  

Pour afficher le texte « Non disponible » :

 =SIERREUR(ASSEMB.V(A9:C12;E9:G12;I9:L12);"Non  disponible") 

6. Trier les données

Si nous le souhaitons, il est également possible de trier les données présentées dans le tableau de synthèse.

Pour cela, il suffit d’encapsuler la fonction ASSEMB.V en tant qu’argument de TRIER :

 =TRIER(SIERREUR(ASSEMB.V(A9:C12;E9:G12;I9:L12);"Non  disponible");2) 

Ici, en tant que deuxième argument de la fonction TRIER, nous spécifions la position de la colonne que nous souhaitons utiliser pour effectuer le filtre :

Les données sont donc classées par trimestre, c’est-à-dire par rapport à la deuxième colonne :

Nous pouvons également les classeur en fonction du nombre de ventes (troisième colonne) :

 =TRIER(SIERREUR(ASSEMB.V(A9:C12;E9:G12;I9:L12);"Non  disponible");3) 

Par défaut, le tri s’effectue par ordre croissant, pour modifier cette règle, nous utilisons le troisième argument « ordre_tri », auquel nous donnons la valeur « -1 » :

7. Supprimer les doublons

Pour finir, voyons comment supprimer les doublons éventuellement présents dans une table retournée par la fonction ASSEMB.V.

Pour cela, nous avons un nouvel exemple, qui reprend trois listes de prénoms :

Compte tenu de ce que nous avons juste avant, il est très simple de combiner ces prénoms dans une liste unique en utilisant la fonction ASSEMB.V :

Seulement, dans cette liste, se trouvent maintenant des doublons.

Pour nous en rendre compte facilement, il suffit de la trier :

Pour supprimer les doublons automatiquement, il va maintenant nous rester à encapsuler cette formule en tant qu’argument de la fonction dynamique « UNIQUE » :

 =UNIQUE(TRIER(ASSEMB.V(A7:A13;C7:C10;E7:E12)))  

Et voilà, nous nous retrouvons maintenant avec une liste reprenant les prénoms des trois listes précédentes, triée par ordre alphabétique, et sans doublon !