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.

 

Téléchargement

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

 

 

Tutoriel Vidéo

 

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



Articles qui pourraient vous intéresser

Comment contrôler les données saisies dans un tableau sans macro VBA sur Excel

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

Comment récupérer des informations depuis internet sur Excel et sans macro VBA (et créer un comparateur de prix)

Comment créer un sommaire automatique et généré en 1 clic sur Excel

Commentaires

Il n'y a aucun commentaire...

Poster un commentaire

Pseudo
Commentaire

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - 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.