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

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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.

Excel formation - vstack - 01

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) 

Excel formation - vstack - 02

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 :

Excel formation - vstack - 03

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) 

Excel formation - vstack - 04

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") 

Excel formation - vstack - 05

 

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 :

Excel formation - vstack - 06

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) 

Excel formation - vstack - 07

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 » :

Excel formation - vstack - 08

Excel formation - vstack - 09

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 :

Excel formation - vstack - 10

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 :

Excel formation - vstack - 11

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

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

Excel formation - vstack - 12

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)))  

Excel formation - vstack - 13

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 !

 

 

 

 



Articles qui pourraient vous intéresser

Comment effectuer des remplacements multiples avec une seule formule magique d'Excel que personne ne vous a jamais montrée !
Les erreurs de concaténation qui vous font perdre du temps sur Excel
Découvrez la nouvelle fonction Excel que 90% des experts ne connaissent pas !
Boostez vos investissements : maîtrisez le calcul du taux de rentabilité avec Excel en 10 minutes !
Les 8 (+1 incroyable) fonctions Excel à maîtriser absolument en 2024
Percez les secrets de la fonction SOMME (SOMME.SI/SOMMEPROD) et des ces déclinaisons pour boostez votre productivité sur Excel ?
TOP3 des méthodes pour extraire des données par date dans Excel
Comment identifier la date la plus ancienne ou la plus récente en fonction de critères sur Excel ?
Comment calculer une moyenne sans tenir compte des zéros sur Excel ?
Comment fusionner des tableaux, les trier et supprimer les doubles avec une seule formule Excel ? (ASSEMB.V et ASSEMB.H)
Arrêtez d'imbriquer des fonctions SI() sur Excel ! Découvrez SI.MULTIPLE() et SI.CONDITIONS()
Comment obtenir efficacement le numéro de semaine d’une date donnée sur Excel ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.