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

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

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

 

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 :

Excel formation - 035 2d to 1d - 01

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 :

Excel formation - 035 2d to 1d - 02

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

Excel formation - 035 2d to 1d - 03

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

Excel formation - 035 2d to 1d - 04

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

Excel formation - 035 2d to 1d - 05

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 :

Excel formation - 035 2d to 1d - 06

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 :

Excel formation - 035 2d to 1d - 07

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

Excel formation - 035 2d to 1d - 08

Pour finir, nous modifions le texte du bouton :

Excel formation - 035 2d to 1d - 09

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
  •  

Excel formation - 035 2d to 1d - 10

 

   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 :

Excel formation - 035 2d to 1d - 11

 

   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 :

Excel formation - 3d to 2d - 01

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 

Excel formation - 3d to 2d - 02

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) 

Excel formation - 3d to 2d - 03

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

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

Excel formation - 3d to 2d - 04

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

Excel formation - 3d to 2d - 05

 

 

   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 :

Excel formation - 3d to 2d - 06

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 

Excel formation - 3d to 2d - 07

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) 

Excel formation - 3d to 2d - 08

 

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

Excel formation - 3d to 2d - 09

 

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



Articles qui pourraient vous intéresser

Comment identifier les cellules identiques en 1 clic sur Excel (couleur selon valeur) ?
Comment créer un tableau de bord qui s’actualise et identifie automatiquement les meilleures performances sur Excel !
L’erreur cachée qui plombe vos tableaux Excel (et comment l’éviter)
Comment entourer automatiquement cellules non conformes Excel sans MF conditionnelle ni formule ?
Comment créer un tableau de bord commercial intelligent avec Excel ?
Comment créer un graphique dessiné à la main avec Excel ?
Comment créer un graphique de distribution en 5 minutes chrono ?
L’astuce Excel pour imprimer le tableau que vous voulez en un clic !
Comment gérer ses comptes sur Excel avec le « Suivi du budget familial »
Comment créer facilement des QR codes sans VBA avec Excel ?
Transformez vos données en graphique tornade interactif facilement sur Excel !
Comment créer un graphique Iceberg dans 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.