5 façons de transposer des données dans Excel (Tutoriel étape par étape)

La transposition de données consiste à changer l'orientation d'un tableau en convertissant ses lignes en colonnes ou vice versa. Dans ce tutoriel, nous partagerons avec vous cinq méthodes différentes pour modifier l'orientation d'un tableau et obtenir le résultat souhaité.

Transformer les colonnes en lignes avec Collage spécial

Transposer et lier les données à la source

• Utiliser la fonction TRANSPOSE
• Utiliser les fonctions INDIRECT, ADRESSE, COLONNE et LIGNE
• Utiliser le Collage spécial et Rechercher & Remplacer
• Utiliser la fonction Power Query

Télécharger des exemples de transposition de données (.xlsx)

Vidéo : Transposer des données dans Excel

Transformer les colonnes en lignes avec Collage spécial

Étape 1 : Copier la plage à transposer

Sélectionnez la plage de cellules où vous souhaitez échanger les lignes et les colonnes, puis appuyez sur "Ctrl" + "C" pour copier la plage.

Étape 2 : Sélectionner l'option Coller en transposant

Faites un clic droit sur la première cellule de la plage de destination, puis cliquez sur l'icône "Transposer" (sous "Options de collage") dans le menu contextuel.

Résultat

Voilà ! La plage est transposée en un rien de temps !

Transposer et lier les données à la source

Pour basculer dynamiquement l'orientation d'une plage (transformer les colonnes en lignes et vice versa) et connecter le résultat transformé au jeu de données original, vous pouvez utiliser l'une des approches ci-dessous :

• "Fonction TRANSPOSE" (Facile à utiliser mais les résultats ne sont pas modifiables)
• "Fonctions INDIRECT, ADRESSE, COLONNE et LIGNE" (Grande formule mais permet de faire des modifications dans les résultats)
• "Collage spécial et Rechercher & Remplacer" (Complexe mais facile à comprendre)
• "Power Query" (Facile avec des résultats formatés dans un tableau)

Changer les lignes en colonnes en utilisant la fonction TRANSPOSE

Pour transformer les lignes en colonnes et vice versa avec la fonction "TRANSPOSE", procédez comme suit.

Étape 1 : Sélectionnez le même nombre de cellules vides que l'ensemble de cellules d'origine mais dans une autre direction

Astuce : Ignorez cette étape si vous utilisez Excel 365 ou Excel 2021.

Disons que votre tableau d'origine se trouve dans la plage A1:C4, ce qui signifie que le tableau a 4 lignes et 3 colonnes. Donc, le tableau transposé aura 3 lignes et 4 colonnes. Cela signifie que vous devriez sélectionner 3 lignes et 4 colonnes de cellules vides.

Étape 2 : Entrez la formule TRANSPOSE

Tapez la formule ci-dessous dans la barre de formule, puis appuyez sur "Ctrl" + "Shift" + "Entrée" pour obtenir le résultat.

Astuce : Appuyez sur "Entrée" si vous êtes utilisateur d'Excel 365 ou Excel 2021.

=TRANSPOSE(A1:C4)

Résultat

Les lignes sont transformées en colonnes, et les colonnes sont converties en lignes.

Pivoter des données en utilisant les fonctions INDIRECT, ADRESSE, COLONNE et LIGNE

Bien que la formule ci-dessus soit assez facile à comprendre et à utiliser, son inconvénient est que vous ne pouvez pas modifier ou supprimer des cellules dans le tableau pivoté. Alors, je vais introduire une formule utilisant les fonctions "INDIRECT", "ADRESSE", "COLONNE" et "LIGNE". Disons que votre tableau d'origine est dans la plage A1:C4, pour effectuer une transformation de colonne en ligne et garder les données pivotées connectées au jeu de données source, suivez les étapes ci-dessous.

Étape 1 : Entrer la formule

Entrez la formule ci-dessous dans la cellule en haut à gauche de la plage de destination (A6 dans notre cas), puis appuyez sur "Entrée" :

=INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))

Étape 2 : Copier la formule vers les cellules à droite et en dessous

Sélectionnez la cellule contenant la formule, puis faites glisser sa poignée de recopie (le petit carré vert dans le coin inférieur droit de la cellule) vers le bas, puis vers la droite, sur autant de lignes et de colonnes que nécessaire.

Résultat

Les colonnes et les lignes sont immédiatement échangées.

Convertir des colonnes en lignes avec Collage spécial et Rechercher & Remplacer

Quelques étapes supplémentaires avec la méthode Collage spécial associée à la fonctionnalité Rechercher et Remplacer vous permettent de transposer des données tout en reliant les cellules sources aux cellules transposées.

Étape 1 : Copier la plage à transposer

Sélectionnez la plage de cellules que vous souhaitez transposer, puis appuyez sur "Ctrl" + "C" pour copier la plage.

Étape 2 : Appliquer l'option Collage avec lien

1. Faites un clic droit sur une cellule vide, puis cliquez sur "Collage spécial".

   

2. Cliquez sur le bouton "Collage avec lien".

   

Vous obtiendrez le résultat comme indiqué ci-dessous :

 Étape 3 : Rechercher et remplacer les signes égal (=) dans le résultat du Collage avec lien

1. Sélectionnez la plage de résultats (A6:C9) et appuyez sur "Ctrl" + "H", puis remplacez "=" par "@EO" (ou tout caractère(s) qui n'existe(nt) pas dans la plage sélectionnée) dans la boîte de dialogue Rechercher et remplacer.

   

2. Cliquez sur "Remplacer tout", puis fermez la boîte de dialogue. Voici à quoi ressembleront les données.

   

Étape 4 : Transposer le résultat du Collage avec lien remplacé

Sélectionnez la plage (A6:C9) et appuyez sur "Ctrl" + "C" pour la copier. Faites un clic droit sur une cellule vide (ici j'ai sélectionné A11) et choisissez l'icône "Transposer" dans les options de collage pour coller le résultat transposé.

Étape 5 : Rétablir les signes égal (=) pour lier le résultat transposé aux données d'origine

1. Gardez les données résultantes transposées A11:D13 sélectionnées, puis appuyez sur "Ctrl" + "H", et remplacez "@EO" par "=" (l'inverse de l'étape 3).

   

2. Cliquez sur "Remplacer tout", puis fermez la boîte de dialogue.

Résultat

Les données sont transposées et liées aux cellules d'origine.

Transposer et lier les données à la source avec Power Query

Power Query est un puissant outil d'automatisation des données qui vous permet de transposer facilement des données dans Excel. Vous pouvez accomplir cette tâche en suivant les étapes ci-dessous :

Étape 1 : Sélectionnez la plage à transposer et ouvrez l'éditeur Power Query

Sélectionnez la plage de données à transposer. Ensuite, dans l'onglet "Données", dans le groupe "Obtenir & Transformer les données", cliquez sur "À partir d'un tableau/plage".

Étape 2 : Convertir les colonnes en lignes avec Power Query

1. Allez dans l'onglet "Transformer". Dans le menu déroulant "Utiliser la première ligne comme en-têtes", sélectionnez "Utiliser les en-têtes comme première ligne".

   

2. Cliquez sur "Transposer".

   

Étape 3 : Enregistrer les données transposées dans une feuille

Dans l'onglet "Fichier", cliquez sur "Fermer et charger" pour fermer la fenêtre "Éditeur Power" et créer une nouvelle feuille de calcul pour charger les données transposées.

Résultat

Les données transposées sont converties en un tableau dans une nouvelle feuille de calcul créée.

Articles connexes

• Comment transposer rapidement des plages et ignorer les cellules vides dans Excel ?
• Lorsque nous collons une plage en transposant, les cellules vides seront également collées. Cependant, parfois, nous voulons simplement transposer les plages en ignorant les cellules vides comme le montre la capture d'écran ci-dessous. Existe-t-il des moyens de transposer rapidement des plages et ignorer les cellules vides dans Excel ?
• Comment transposer toutes les 5 ou n lignes d'une colonne en plusieurs colonnes ?
• Supposons que vous ayez des données longues dans la colonne A, et maintenant, vous voulez transposer toutes les 5 lignes de la colonne A en plusieurs colonnes, comme transposer A1:A5 en C6:G6, A6:A10 en C7:G7, et ainsi de suite comme le montre la capture d'écran suivante. Comment pourriez-vous gérer cette tâche sans copier et coller à plusieurs reprises dans Excel ?
• Comment transposer / convertir des colonnes et des lignes en une seule colonne ?
• Lorsque vous utilisez une feuille de calcul Excel, parfois, vous rencontrerez ce problème : comment pourriez-vous convertir ou transposer une plage de données en une seule colonne ? (Voir les captures d'écran ci-dessous :) Maintenant, je vous présente trois astuces rapides pour résoudre ce problème.
• Comment transposer / convertir des colonnes et des lignes en une seule ligne ?
• Comment joindre plusieurs lignes et colonnes en une seule ligne longue ? Peut-être que cela semble facile pour vous, car vous pouvez les copier un par un et les joindre en une ligne manuellement. Cependant, cela peut être très chronophage et fastidieux s'il y a des centaines de lignes et de colonnes. Ici, je vais parler de quelques astuces rapides pour résoudre cela.