Comment convertir un tableau en style matriciel en trois colonnes dans Excel ?
Lorsque vous travaillez avec des données dans Excel, vous pouvez souvent rencontrer des tableaux en style matriciel qui présentent des informations sous forme de grille, où les lignes et les colonnes servent d'en-têtes. Bien que ce format soit visuellement utile pour certaines analyses, vous devrez peut-être transformer cette matrice en une « liste » ou en un tableau à trois colonnes pour des tâches telles que l'importation de bases de données, la normalisation des données, la création de graphiques ou des analyses avancées. La conversion de la matrice en une liste à trois colonnes (parfois appelée données « dépivotées ») permet un filtrage, une agrégation et une intégration plus faciles avec d'autres outils de données. Consultez l'exemple ci-dessous illustrant la transformation :
➤ Convertir un tableau en style matriciel en liste avec un Tableau Croisé Dynamique
➤ Convertir un tableau en style matriciel en liste avec du code VBA
➤ Convertir un tableau en style matriciel en liste avec Kutools pour Excel
➤ Convertir un tableau en style matriciel en liste avec une formule Excel
Convertir un tableau en style matriciel en liste avec un Tableau Croisé Dynamique
Dans Excel, il n'y a pas de commande intégrée directe pour convertir un tableau en style matriciel en une liste à trois colonnes. Cependant, en utilisant l'assistant du Tableau Croisé Dynamique, vous pouvez transformer efficacement une matrice en données tabulaires plates adaptées à une analyse plus approfondie. Cette approche est idéale pour les petits et moyens ensembles de données et est particulièrement utile lorsque vous souhaitez aplatir une structure de rapport complexe. Elle convient moins aux grands jeux de données ou aux utilisateurs peu familiers des opérations des Tableaux Croisés Dynamiques.
1. Ouvrez la feuille de calcul contenant votre matrice. Appuyez sur Alt + D, puis P pour ouvrir l'assistant Tableau Croisé Dynamique et Graphique Croisé Dynamique. Dans l'assistant :
- Sous Où se trouvent les données que vous voulez analyser, choisissez Plages de consolidation multiples.
- Sous Quel type de rapport voulez-vous créer, sélectionnez Tableau Croisé Dynamique.
2. Cliquez sur Suivant. Dans l'étape 2a de 3 de la boîte de dialogue, sélectionnez Je vais créer les champs de page :
3. Cliquez Suivant. Dans Étape 2b de 3, cliquez sur le 
bouton et sélectionnez toute la plage de données de la matrice, y compris les en-têtes de ligne et de colonne. Cliquez Ajouter pour insérer la plage dans la Toutes les plages liste. Confirmez que votre sélection de plage couvre toute la matrice.
4. Cliquez sur Suivant. À l’étape 3 de 3, choisissez si vous voulez placer le Tableau Croisé Dynamique dans une nouvelle feuille de calcul ou dans une cellule spécifique :
5. Cliquez sur Terminer. Excel génère un Tableau Croisé Dynamique résumant la matrice. Par défaut, il affiche les totaux agrégés aux intersections. Vous n’avez pas besoin d’ajuster la structure du Tableau Croisé Dynamique pour cette tâche – passez simplement à l'étape suivante :
6. Double-cliquez sur la cellule où se croisent les totaux généraux des lignes et des colonnes (par exemple, la cellule F22). Excel créera une nouvelle feuille contenant une liste à trois colonnes, où chaque ligne montre une combinaison unique d’en-tête de ligne et d’en-tête de colonne avec la valeur correspondante.
7. Pour finaliser, sélectionnez la nouvelle table, faites un clic droit, puis choisissez Tableau > Convertir en plage. Cela supprime la mise en forme du tableau, laissant une liste simple et modifiable :
Astuce : Si votre matrice change fréquemment, vous devrez répéter ce processus pour rafraîchir la liste à trois colonnes. Cette méthode convient mieux aux données statiques. De plus, si votre matrice contient des cellules vides ou fusionnées, un nettoyage peut être nécessaire avant d’utiliser cette technique.
Convertir un tableau en style matriciel en liste avec du code VBA
Si vous préférez l'automatisation ou souhaitez appliquer cette transformation de manière répétée, utiliser une macro VBA peut rapidement convertir n'importe quel tableau en style matriciel en une liste structurée à trois colonnes. Cette méthode est particulièrement efficace pour les grands ensembles de données ou dispositions diverses et élimine le besoin de formatage manuel. Elle est idéale pour les utilisateurs familiers avec l'exécution de scripts VBA.
1. Appuyez sur Alt + F11 pour ouvrir l'éditeur Microsoft Visual Basic for Applications.
2. Dans l'éditeur, cliquez sur Insertion > Module pour créer un nouveau module. Collez ensuite le code suivant dans la fenêtre du module :
📜 Code VBA : Convertir une matrice en liste
Sub ConvertTable()
' Updated by Extendoffice
Dim Rng As Range
Dim cRng As Range
Dim rRng As Range
Dim xOutRng As Range
xTitleId = "KutoolsforExcel"
Set cRng = Application.InputBox("Select your Column labels", xTitleId, Type:=8)
Set rRng = Application.InputBox("Select Your Row Labels", xTitleId, Type:=8)
Set Rng = Application.InputBox("Select your data", xTitleId, Type:=8)
Set outRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set xWs = Rng.Worksheet
k = 1
xColumns = rRng.Column
xRow = cRng.Row
For i = Rng.Rows(1).Row To Rng.Rows(1).Row + Rng.Rows.Count - 1
For j = Rng.Columns(1).Column To Rng.Columns(1).Column + Rng.Columns.Count - 1
outRng.Cells(k, 1) = xWs.Cells(i, xColumns)
outRng.Cells(k, 2) = xWs.Cells(xRow, j)
outRng.Cells(k, 3) = xWs.Cells(i, j)
k = k + 1
Next j
Next i
End Sub3. Appuyez sur F5 ou cliquez sur Exécuter pour exécuter la macro. Une série de messages guidera vos sélections nécessaires :
Étape 1 : Sélectionnez les étiquettes de colonnes (généralement la première ligne de votre matrice) :
Étape 2 : Sélectionnez les étiquettes de lignes (généralement la première colonne de votre matrice) :
Étape 3 : Sélectionnez la plage de données réelle de la matrice (excluant les en-têtes de ligne et de colonne) :
Étape 4 : Choisissez la cellule de sortie où la liste convertie à trois colonnes doit commencer. Il est recommandé d'utiliser une cellule vide ou une nouvelle feuille de calcul :
Étape 5 : Cliquez sur OK. Votre matrice sera maintenant convertie en une liste plate à trois colonnes.
⚠️ Notes et conseils :
• Assurez-vous de ne pas inclure les en-têtes de colonnes ou de lignes dans la plage de données de la matrice.
• Si votre matrice contient des cellules fusionnées, désactivez-les avant d'exécuter la macro pour éviter les erreurs.
• Si vous rencontrez des erreurs, revérifiez les plages que vous avez sélectionnées et assurez-vous qu'elles sont correctement alignées.
Convertir un tableau en style matriciel en liste avec Kutools pour Excel
Bien que les méthodes ci-dessus soient efficaces, elles peuvent sembler fastidieuses ou intimidantes pour les utilisateurs moins expérimentés. Si vous recherchez une solution rapide et conviviale, Kutools pour Excel propose un utilitaire dédié appelé Convertir la dimension du tableau, spécialement conçu pour cet objectif.
Cet outil est idéal pour les utilisateurs qui convertissent fréquemment des tableaux en style matriciel ou ont besoin d'effectuer un traitement par lots. Il peut préserver la mise en forme d'origine - comme la police, la couleur de remplissage et les formules - si nécessaire. Un inconvénient potentiel est que Kutools est un add-in tiers qui doit être installé, mais c'est une option puissante pour quiconque redimensionne régulièrement des données dans Excel.
Étapes :
1. Après avoir installé Kutools, accédez à l'onglet Kutools, cliquez sur Plage, puis sélectionnez Convertir la dimension du tableau :
2. Dans la boîte de dialogue Convertir la dimension du tableau :
- (1) Choisissez Convertir tableau bidimensionnel en liste sous la section Type de conversion.
- (2) Cliquez sur le
bouton à côté de Plage source pour sélectionner votre tableau matriciel. - (3) Cliquez sur le bouton à côté de Plage des résultats pour spécifier où placer la sortie.
Assurez-vous de sélectionner toute la matrice (y compris les en-têtes et les données) pour éviter une conversion partielle ou des résultats incorrects.
3. Cliquez sur OK. La matrice sera instantanément convertie en une liste à trois colonnes, en préservant autant que possible la mise en forme des cellules d'origine :
Astuce : Cette fonctionnalité prend également en charge l'opération inverse - convertir une liste plate en une matrice bidimensionnelle. C'est utile pour reconstruire des rapports ou préparer des données pour une analyse croisée. Voir : comment convertir une liste en tableau croisé 2D.
➤ En savoir plus sur la fonctionnalité Convertir la dimension du tableau
⏬ Téléchargez et essayez Kutools pour Excel gratuitement dès maintenant !
Convertir un tableau en style matriciel en liste avec une formule Excel
Si vous préférez une approche basée sur des formules - particulièrement utile si vous souhaitez que votre liste à trois colonnes se mette à jour dynamiquement lorsque la matrice change - vous pouvez utiliser une combinaison des fonctions INDEX, LIGNE, COLONNE et NBVAL pour dépivoter manuellement les données. Cette solution ne nécessite ni VBA ni add-ins et est idéale pour ceux qui souhaitent éviter d'utiliser des macros ou des outils externes. Cependant, elle nécessite une attention minutieuse aux références de formules, car les formules doivent généralement être remplies en tant que tableau ou systématiquement glissées vers le bas/vers la droite. Elle est la plus pratique pour des matrices de taille modérée et des situations où la liste doit rester active et réactive aux changements des données sources.
Supposons que votre plage de données soit la suivante :
- Les étiquettes de ligne sont dans les cellules A2:A10.
- Les étiquettes de colonne sont dans les cellules B1:J1.
- Les valeurs de la matrice sont dans les cellules B2:J10.
1. Créez une nouvelle feuille de calcul ou commencez dans une zone vide de votre feuille existante. Dans la cellule L2, entrez la formule suivante pour extraire l'étiquette de ligne :
=INDEX($A$2:$A$10,INT((ROW(A1)-1)/COUNTA($B$1:$J$1))+1)2. Dans la cellule M2, entrez cette formule pour extraire l'étiquette de colonne correspondante :
=INDEX($B$1:$J$1,MOD(ROW(A1)-1,COUNTA($B$1:$J$1))+1)3. Dans la cellule N2, extrayez la valeur de la matrice avec :
=INDEX($B$2:$J$10,INT((ROW(A1)-1)/COUNTA($B$1:$J$1))+1,MOD(ROW(A1)-1,COUNTA($B$1:$J$1))+1)4. Sélectionnez les cellules L2:N2 et faites glisser la poignée de remplissage vers le bas. Le point d'arrêt doit être le nombre de lignes × nombre de colonnes (dans cet exemple, c’est 9 lignes × 9 colonnes = 81 lignes au total).
✅ Conseils :
- Ajustez toutes les plages pour qu'elles correspondent à votre structure de données réelle.
- Utilisez
SIetESTVIDEpour filtrer les lignes vides si nécessaire. - Pour un comportement d'expansion automatique, envisagez d'utiliser
DECALERou des plages nommées dynamiques. - Cette méthode est la plus pratique pour une taille de matrice statique et un volume de données modéré.
ℹ️ Notes supplémentaires :
- Avantages : Les formules restent actives et reflètent automatiquement les changements dans la matrice. Pas de VBA ou d'add-ins requis.
- Inconvénients : Peut ralentir les performances du classeur pour les grandes matrices. Nécessite une configuration minutieuse.
Démo : Convertir un tableau en style matriciel en liste avec Kutools pour Excel
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels