Comment transformer un tableau en 2 dimensions (double entrée) et un tableau en 1 dimension (liste) sur Excel

Dans ce tutoriel, nous allons voir comment transformer un tableau de données classique à double entrées, en une liste de données.

Cette liste pourra ensuite être utilisée par exemple en tant que source de données pour alimenter un tableau croisé dynamique.

Nous verrons tout d'abord une première méthode reposant sur la création d'une macro commande en VBA, qui permettra d'effectuer la transformation en 1 clic.

Puis, nous verrons une seconde méthode pour atteindre un résultat similaire en utilisant cette fois-ci des formules Excel.

Téléchargement

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

Tutoriel Vidéo

Partie 1 : 

Partie 2 : 

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

1. Présentation

Pour illustrer ce tutoriel, nous allons partir du document suivant :

Dans celui-ci, nous retrouvons le montant des ventes réalisées par les commerciaux d’une entreprise, réparties en fonction des pays dans lesquelles les affaires ont été réalisées.

Pour pouvoir traiter simplement ces informations, nous souhaitons alors transformer ces données en liste, ce qui nous permettra par exemple par la suite de créer un tableau croisé dynamique.

Pour cela, nous allons devoir créer une macro commande en VBA.

Pas de panique, nous allons voir chaque point de développement dans le détail, mais si vous souhaitez maîtriser le langage VBA, je vous invite à suivre la formation GRATUITE, offerte sur excelformation.fr en cliquant ici.

2. Création du projet

Pour créer notre macro, nous commençons par lancer l’éditeur de VBA, en cliquant sur le bouton Visual Basic du menu Développeur :

Si le menu Développeur n’apparaît dans votre Menu Ruban, Rendez-vous dans Fichier > Option > Personnaliser le Ruban, cochez la case Développeur, puis confirmez en appuyant sur le bouton [OK] :

Après avoir cliqué sur le bouton Visual Basic, vous devriez vous retrouver dans l’outil de développement de VBA :

À partir de là, nous allons pouvoir créer un nouveau module afin de saisir notre macro VBA (menu Insertion > Module) :

Puis nous pouvons créer une nouvelle procédure que nous allons appeler convertirTableauEnListe (que nous faisons précéder du mot-clé Sub qui permet de créer une procédure) :

 Sub convertirTableauEnListe()
 
End Sub 

Pour le moment, nous ne saisissons rien, nous allons tout d’abord créer un bouton sur la feuille de calcul qui va se charger de lancer cette macro.

Revenons donc sur la feuille de calcul (pour passer d’Excel à VBA, nous pouvons utiliser le raccourci clavier [ALT]+[F11], ou cliquer sur le bouton Excel du menu :

Puis nous allons revenir dans le menu Développeur, choisir le sous-menu Insérer et cliquer sur le bouton qui porte justement le nom de Bouton :

De cette manière, nous pouvons simplement ajouter le bouton sur la feuille de calcul puis dans la fenêtre qui va alors s’afficher, nous pouvons simplement sélectionner notre macro convertirTableauEnListe et valider l’affectation en appuyant sur le bouton [OK] :

Pour finir, nous modifions le texte du bouton :

Et voilà, le travail préparatoire est maintenant terminé, nous pouvons à présent passer au développement de la macro à proprement parler.

3. Convertir un tableau en liste

Pour faire fonctionner notre macro commande, nous allons fonctionner de la manière suivante :

• L’utilisateur doit avoir sélectionné une des cellules du tableau AVANT de lancer la macro,
• À partir de cette cellule, nous allons pouvoir récupérer les coordonnées de la plage des cellules complète dans laquelle celle-ci se trouve,
• Puis, nous allons construire une nouvelle liste qui sera composée de trois colonnes : en première colonne, nous retrouverons les noms des commerciaux, dans la seconde colonne les noms de pays, et bien évidemment, les montants de ventes seront insérés dans la troisième et dernière colonne
•  

   3.1. Vérification de la cellule sélectionnée

Comme nous venons de le voir à l’instant, l’utilisateur doit au préalable avoir sélectionné une des cellules du tableau.

Pour nous en assurer, nous allons simplement lui envoyer une demande de confirmation via une boîte de dialogue dans laquelle celui-ci pourra confirmer ou non la bonne sélection de cellule.

Lorsque la réponse sera négative, alors nous quitterons simplement la procédure :

    ' 1 - Contrôle de la cellule sélectionnée
    If MsgBox("Avez-vous sélectionné une cellule du tableau ?", vbYesNo) = vbNo Then
        MsgBox "Veuillez sélectionner une cellule du tableau, puis relancer la macro commande"
        Exit Sub
    End If 

Nous présentons ici une boîte de dialogue, avec le message « Avez-vous sélectionné une cellule du tableau ».

Nous affectons la valeur vbYesNo au second paramètre de l’instruction Msgbox. Cela permet d’afficher deux boutons « Oui » et « Non ».

Grâce à l’instruction If, nous analysons le bouton sur lequel l’utilisateur a cliqué, et lorsque celui-ci répond par la négative, alors nous lui affichons un second message pour lui demander de sélectionner des cellules du tableau, puis nous quittons la procédure.

Nous arrivons maintenant au second point de la procédure avec l’assurance que la cellule sélectionnée appartient bien au tableau.

   3.2. Récupération de la plage des cellules du tableau

À présent, nous pouvons récupérer l’ensemble des cellules qui compose le tableau, grâce à l’instruction CurrentRegion sur la cellule sélectionnée (ActiveCell), que nous allons stocker dans une variable de type Range (c’est-à-dire une plage des cellules) que nous allons appeler pTableau (petit « p » pour « plage de cellules ») :

    ' 2 - Récupération de la plage complète du tableau
    Dim pTableau As Range
    Set pTableau = ActiveCell.CurrentRegion 

Attention, une variable Range est un Objet en VBA, il est donc nécessaire d’utiliser l’instruction Set pour lui affecter une plage de cellules.

Pour tester notre code, nous pouvons ajouter une ligne en dessous pour afficher l’adresse dans une fenêtre :

    ' 2 - Récupération de la plage complète du tableau
    Dim pTableau As Range
    Set pTableau = ActiveCell.CurrentRegion
    MsgBox pTableau.Address 

Puis de retour dans la feuille de calcul, nous allons maintenant cliquer sur le bouton pour lancer la macro commande :

   3.3. Création de la liste

Entrons maintenant dans le vif du sujet, il va ici s’agir de passer en revue chacune des cellules du tableau, puis lorsque celle-ci n’est pas nulle, nous allons la récupérer (ainsi que les titres de ligne et de colonne correspondants) pour les insérer dans la liste.

Pour commencer, nous allons créer un certain nombre de variables :

• « ligneTitre » permet de stocker le numéro de la ligne dans laquelle se trouvent les noms de pays,
• « ligneColonne » permet de stocker le numéro de la colonne dans laquelle se trouvent les noms des commerciaux,
• « ligne » permet de passer en revue chacune des lignes du tableau,
• « colonne » permet de passer en revue chacune des colonnes du tableau,
• « cellule » il va s’agit de la cellule située sur la feuille de calcul « Liste » au sein de laquelle nous allons saisir la valeur de la cellule analysée

    ' 3 - Création de la liste
    Dim ligneTitre As Integer, colonneTitre As Integer
    Dim ligne As Integer, colonne As Integer
    Dim cellule As Range 

Puis nous récupérons les numéros de ligne et de colonne des titres, en analysant la toute première cellule du tableau :

    ' stockage des lignes de début et de fin
    ligneTitre = pTableau.Cells(1).Row
    colonneTitre = pTableau.Cells(1).Column 

Nous affectons la première cellule de la feuille Liste à la variable cellule et nous supprimons toutes les données qui pourraient éventuellement se trouver saisies dans la feuille :

    ' cellule de saisie
    Set cellule = Sheets("Liste").[a1]
    Sheets("Liste").Cells.ClearContents 

À présent, pour passer en revue chacune des lignes du tableau, nous allons imbriquer une boucle for dans une seconde boucle for :

    ' Création de la liste
    For ligne = ligneTitre + 1 To pTableau.Cells(pTableau.Rows.Count, 1).Row
        For colonne = colonneTitre + 1 To pTableau.Cells(pTableau.Columns.Count, 1).Row
        Next
    Next 

Le principe d’une boucle For each est de répéter une opération tant qu’une variable répond à une condition, tout en incrémentant la valeur de cette variable lors de chaque passage de la boucle.

Note : Si vous avez des difficultés à comprendre l’utilisation du la boucle For que nous utilisons ici, je vous invite à consulter la vidéo située tout en haut de ce tutoriel dans laquelle nous utilisons une autre option pour parcourir les cellules contenues dans une plage de cellule.

Pour prenons donc notre variable qui porte le nom de ligne, à laquelle nous affectons la valeur déjà stockée dans la variable ligneTitre à laquelle nous ajoutons un afin de ne pas tenir compte des ligne.

Puis grâce au mot clé To de l’instruction For, nous demandons à VBA de répéter l’opération jusqu’à ce que ligne atteigne pour valeur le numéro de la dernière ligne du tableau, que nous obtenons par pTableau.Cells(pTableau.Rows.Count, 1).Row.

Cela étant fait, nous faisons de même avec les colonnes.

Ainsi, lorsque nous nous trouvons à l’intérieur de ces deux boucles, nous avons à disposition deux variables qui nous indiquent respectivement les numéros de ligne et de colonne de la cellule étudiée.

Nous pouvons alors contrôler que celle-ci n’est pas vide, puis insérer les valeurs dans la feuille liste :

    ' Création de la liste
    For ligne = ligneTitre + 1 To pTableau.Cells(pTableau.Rows.Count, 1).Row
        For colonne = colonneTitre + 1 To pTableau.Cells(pTableau.Columns.Count, 1).Row
            If Cells(ligne, colonne) <> "" Then
                cellule = Cells(ligne, colonneTitre)
                cellule.Offset(0, 1) = Cells(ligneTitre, colonne)
                cellule.Offset(0, 2) = Cells(ligne, colonne)
                Set cellule = cellule.Offset(1, 0)
            End If
        Next
    Next 

La liste étant composée de trois colonnes, nous alimentons celle-ci de la manière suivante :

• Dans la cellule cellule, nous insérons la valeur contenu dans le titre de ligne (les noms de commerciaux),
• Dans la seconde cellule, située juste à droite (obtenu par l’instruction Offset(0, 1) qui permet de décaler une cellule), nous insérons la valeur contenue dans le titre de colonne (nom du pays),
• Et enfin dans la troisième et dernière colonne de la liste, nous insérons la valeur contenu dans la cellule en cours

Puis nous passons à la ligne suivante en affectant à la variable cellule la cellule située juste en dessous (Set cellule = cellule.Offset(1, 0)).

Enfin pour tester, nous lançons la commande en appuyant sur le bouton nouvellement créé (attention avant cela d’avoir bien créé une feuille ayant pour nom « Liste »).

4. Transformer des données tabulaires en liste avec une formule Excel

Dans cette partie du tutoriel consacré à la transformation, d'un tableau de données classique à double entrées, en une liste de données., nous allons voir comment atteindre un résultat similaire, avec des formules Excel.

Ces formules reposeront sur des fonctions que nous avions déjà eu l'occasion de découvrir lors de tutoriels précédents, il s'agit des fonctions INDEX, MOD, LIGNE, COLONNE et DECALER que nous reverrons ici dans le détail.

Cette méthode est utile pour transformer rapidement des tableaux de données en listes, qui peuvent ensuite être utilisées pour alimenter des tableaux croisés dynamiques ou d'autres analyses de données.

Dans un premier temps, nous verrons comme effectuer cette opération sur un exemple très simple, puis nous verrons dans un second temps comment dupliquer automatiquement les formules sur un exemple plus complexe en quelques secondes seulement.

Pour illustrer ce tutoriel, nous allons utiliser les données suivantes qui représentent les notes obtenues par les élèves d'une classe dans trois matières :

Pour pouvoir traiter simplement ces informations, nous souhaitons alors transformer ces données en liste, ce qui nous permettra par exemple par la suite de créer un tableau croisé dynamique.

   4.1. Création de la liste des lignes

Pour créer la liste, nous allons utiliser les fonctions Excel "DECALER" et "INDEX".

Tout d’abord, la fonction DECALER permet de décaler une plage de cellules selon un nombre de lignes ou de colonnes données.

Elle est souvent utilisée dans des formules pour référencer une plage de cellules dynamique qui évolue en fonction des paramètres donnés.

La syntaxe de la fonction DECALER() est la suivante :

 =DECALER(Référence; Ligne; Colonne; [Hauteur];  [Largeur]) 

Où :

• Référence est la cellule de référence à partir de laquelle la plage de cellules sera décalée,
• Ligne est le nombre de lignes de décalage par rapport à la référence,
• Colonne est le nombre de colonnes de décalage par rapport à la référence,
• Hauteur est le nombre de lignes de la plage de cellules à renvoyer (optionnel),
• Largeur est le nombre de colonnes de la plage de cellules à renvoyer (optionnel).

Ainsi, pour décaler une plage de cellules de 2 lignes vers le bas et de 1 colonne vers la droite à partir de la cellule A1, on peut utiliser la formule suivante :

 =DECALER(A1; 2; 1; 2; 2) 

La cellule A1 sera donc ignorée, et la plage renvoyée par la formule sera de 2 lignes (puisque Hauteur est défini à 2) et de 2 colonnes (puisque Largeur est défini à 2).

Nous aurons également besoin de maîtriser la fonction INDEX.

Cette fonction INDEX() d'Excel en effet permet de retourner la valeur d'une cellule dans une plage de cellules à partir de sa position relative.

La syntaxe de la fonction INDEX() est la suivante :

 =INDEX(plage; num_ligne; [num_colonne]) 

Où :

• plage : la plage de cellules dans laquelle la fonction doit chercher la valeur
• num_ligne : le numéro de ligne de la cellule à retourner
• num_colonne (facultatif) : le numéro de colonne de la cellule à retourner. Si cet argument est omis, la fonction renvoie la valeur de la cellule correspondant à la ligne spécifiée et à la première colonne de la plage.

Ainsi, pour obtenir le prénom du troisième élève de la liste, nous utiliserons la formule suivante :

 =INDEX(A2:A6;3) 

Maintenant que nous avons vu comment utiliser ces fonctions, nous allons pouvoir créer la première colonne de la liste, qui correspondra au nom des élèves, nous allons utiliser la fonction "DECALER" avec la formule suivante dans la cellule F2 :

Pour commencer, nous allons souhaiter partir du numéro de la ligne dans laquelle est insérée la formule, que nous pouvons obtenir facilement en utilisant la fonction LIGNE, sans argument :

 LIGNE()-1 

Cela nous permet donc d’obtenir un décompte dans les cellules.

Ensuite, nous allons diviser ce résultat par le nombre de matières que nous obtenons maintenant avec la fonction NBVAL :

 =(LIGNE()-1)/NBVAL($B$1:$D$1) 

Ensuite, nous allons arrondir ces résultats à l'entier supérieur :

 ARRONDI.SUP((LIGNE()-1)/NBVAL($B$1:$D$1);0) 

Comme vous pouvez le constater, cela va donc nous permettre d’obtenir une série de nombre de 1 jusqu’au nombre d’élèves dans la classe, en répétant chacun de ces nombres autant de fois qu’il y a de matières.

Il ne restera maintenant plus qu’à inclure cette fonction en tant qu’argument de la fonction INDEX pour obtenir le nombre de l’élève correspondant, comme nous l’avons vu dans l’exemple précédent :

  =INDEX($A$2:$A$6;ARRONDI.SUP((LIGNE()-1)/NBVAL($B$1:$D$1);0)) 

   4.2. Création de la liste des colonnes

Pour obtenir la liste des matières, nous allons devoir adapter la formule que nous venons de créer pour afficher la série des trois matières les unes à la suite des autres.

Pour cela, nous allons faire reposer l’analyse sur la fonction MOD.

En effet, cette fonction MOD() permet de calculer le reste de la division euclidienne d'un nombre par un autre nombre.

La syntaxe de la fonction MOD() est la suivante :

 =MOD(nombre; diviseur) 

Où :

• Nombre est le nombre à diviser,
• Diviseur est le nombre par lequel le nombre est divisé.

Nous pourrons donc l’utiliser pour générer une suite de nombres de 1, 2 et 3 en utilisant comme argument « nombre » le numéro de la ligne dans laquelle la formule est insérée, et en tant que diviseur le chiffre 3 :

Comme vous pouvez le constater, cette formule nous retourne en réalité des nombres de 0 à 2.

Il suffit donc d’ajouter « +1 » :

 =MOD(LIGNE();3)+1 

Ici, l’ordre chronologique n’est pas respecté, mais ce n’est pas important.

Maintenant, nous pouvons la rendre dynamique en utilisant la fonction NBVAL pour déterminer le nombre de matières comme nous l’avons fait dans la partie précédente :

 =MOD(LIGNE();NBVAL($B$1:$D$1))+1 

Les résultats seront ici strictement identiques.

Pour finir, nous allons encapsuler ce résultat comme argument de la fonction INDEX pour piocher le nom des matières dans les cellule B1 à D1 :

 =INDEX($B$1:$D$1;MOD(LIGNE();NBVAL($B$1:$D$1))+1) 

   4.3. Récupération des notes

Maintenant que nous avons créé la liste des données, nous allons pouvoir récupérer les notes correspondantes facilement en utilisant les fonctions INDEX et EQUIV afin de générer une recherche en 3D :

  =INDEX($B$2:$D$6;EQUIV(F2;$A$2:$A$6;0);EQUIV(G2;$B$1:$D$1;0)) 

Cette formule utilise en effet les fonctions INDEX et EQUIV pour rechercher des données dans la table d’origine :

• Pour commencer, nous appelons la fonction INDEX(), sauf qu’ici nous ne sélectionnons pas une seule colonne, mais bien la plage qui correspond à toutes les données de la table, il s'agit de la plage $B$2:$D$6.
• Ensuite, pour déterminer quelle cellule nous allons piocher dans cette plage, nous allons appeler deux fois la fonction EQUIV(), afin de déterminer tout d’abord la ligne dans laquelle se trouve cette cellule, puis la colonne correspondante :

Sur le premier appel de la fonction EQUIV(), nous commençons par préciser la valeur à rechercher dans la première colonne de la table à deux dimensions, c’est -à-dire la cellule F2, et pour le second argument, nous renseignons la plage de la colonne dans laquelle se trouvent les noms des étudiants : il s'agit de la plage $A$2:$A$6.

Nous allons ensuite procéder de la même manière pour identifier la position de la matière rechercher dans la plage $B$1:$D$1.

La fonction INDEX() renvoie ainsi la valeur qui se trouve à l'intersection de la ligne et de la colonne correspondant aux arguments des fonctions EQUIV().

Article publié le 09/10/2019 et mis à jour le 20/04/2023.