5 façons de transposer des données dans Excel (tutoriel pas à pas)
Transposer des données signifie changer l'orientation d'un tableau en convertissant ses lignes en colonnes ou vice versa. Dans ce didacticiel, nous partagerons avec vous cinq méthodes différentes pour changer l'orientation d'un tableau et obtenir le résultat souhaité.
Vidéo : Transposer des données dans Excel
Basculer les colonnes en lignes avec Collage spécial
Étape 1 : Copiez la plage à transposer
Sélectionnez la plage de cellules où vous souhaitez permuter les lignes et les colonnes, puis appuyez sur Ctrl + C pour copier la plage.
Étape 2 : sélectionnez l'option Coller la transposition
Cliquez avec le bouton droit sur la première cellule de la plage de destination, puis cliquez sur le Transposer icône (sur le bord gauche de l'écran) (Sous Options de collage) dans le menu contextuel.
Résultat
Voila ! La gamme est transposée en un rien de temps !
(AD) Transposez facilement les dimensions du tableau avec Kutools
La conversion d'un tableau croisé (tableau bidimensionnel) en une liste plate (liste unidimensionnelle) ou vice versa dans Excel prend toujours beaucoup de temps et d'efforts. Cependant, avec Kutools for Excel installé, son Transposer les dimensions de la table outil vous aidera à faire la conversion rapidement et facilement.
Kutools for Excel - Comprend plus de 300 fonctions pratiques, ce qui rend votre travail beaucoup plus facile. Obtenez un essai gratuit complet de 30 jours maintenant !
Transposer et lier les données à la source
Pour changer dynamiquement l'orientation d'une plage (changer les colonnes en lignes et vice versa) et connecter le résultat commuté à l'ensemble de données d'origine, 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 changements dans les résultats)
- Collage spécial et Rechercher et remplacer (Complexe mais facile à comprendre)
- Power Query (Facile avec des résultats formatés dans un tableau)
Changer les lignes en colonnes à l'aide de la fonction TRANSPOSE
Pour transformer les lignes en colonnes et vice versa avec le TRANSPOSER fonction, 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
Conseil: Ignorez cette étape si vous utilisez Excel 365 ou Excel 2021.
Disons que votre table d'origine est à portée A1: C4, ce qui signifie que le tableau comporte 4 lignes et 3 colonnes. Ainsi, le tableau transposé aura 3 lignes et 4 colonnes. Ce qui signifie que vous devez 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 + Maj + Entrée pour obtenir le résultat.
Conseil: Actualités Entrer si vous êtes des utilisateurs d'Excel 365 ou d'Excel 2021.
=TRANSPOSE(A1:C4)
- Tu devrais changer A1: C4 à votre plage source réelle que vous souhaitez transposer.
- S'il y a des cellules vides dans la plage d'origine, le TRANSPOSER convertirait les cellules vides en 0 (zéros). Afin de se débarrasser des résultats nuls et de conserver les cellules vides lors de la transposition, vous devez profiter de la IF fonction:
-
=TRANSPOSE(IF(A1:C4="","",A1:C4))
Résultat
Les lignes sont transformées en colonnes et les colonnes sont converties en lignes.
Faire pivoter les données à l'aide des fonctions INDIRECT, ADDRESS, COLUMN et ROW
Bien que la formule ci-dessus soit assez facile à comprendre et à utiliser, l'inconvénient est que vous ne pouvez pas modifier ou supprimer de cellules dans le tableau pivoté. Donc, je vais introduire une formule utilisant INDIRECT, ADRESSE, COLONNE et RANGÉE les fonctions. Disons que votre table d'origine est à portée A1: C4, pour effectuer une transformation de colonne en ligne et conserver les données pivotées connectées à l'ensemble de données source, veuillez suivre les étapes ci-dessous.
Étape 1 : Saisissez la formule
Entrez la formule ci-dessous dans la cellule la plus à gauche de la plage de destination (A6 dans notre cas), et appuyez sur Entrer:
=INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))
- Tu devrais changer A1 à la cellule la plus à gauche de votre plage source réelle que vous allez transposer, et conservez les signes dollar tels qu'ils sont. Le signe dollar ($) avant la lettre de colonne et le numéro de ligne indique une référence absolue, qui conserve la lettre de colonne et le numéro de ligne inchangés lorsque vous déplacez ou copiez la formule dans d'autres cellules.
- S'il y a des cellules vides dans la plage d'origine, la formule convertira les cellules vides en 0 (zéros). Afin de se débarrasser des résultats nuls et de conserver les cellules vides lors de la transposition, vous devez profiter de la IF fonction:
-
=IF(INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))=0,"",INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1))))
Étape 2 : Copiez la formule dans les cellules de droite et en dessous
Sélectionnez la cellule de formule et faites glisser sa poignée de remplissage (le petit carré vert dans le coin inférieur droit de la cellule) vers le bas, puis vers la droite jusqu'à autant de lignes et de colonnes que nécessaire.
Résultat
Les colonnes et les lignes sont immédiatement inversées.
Convertissez les colonnes en lignes avec Collage spécial et Rechercher et remplacer
Quelques étapes supplémentaires avec la méthode Collage spécial ainsi que la fonction Rechercher et remplacer vous permettent de transposer les données tout en liant les cellules source aux cellules transposées.
Étape 1 : Copiez 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 Coller le lien
-
Faites un clic droit sur une cellule vide, puis cliquez sur Collage spécial.
-
Cliquez sur Coller avec liaison.
Vous obtiendrez le résultat comme indiqué ci-dessous :
Étape 3 : recherchez et remplacez les signes égal (=) à partir du résultat Coller le lien
-
Sélectionnez la plage de résultats (A6: C9) et appuyez sur Ctrl + H, puis remplacez = avec @EO (ou tout caractère qui n'existe pas dans la plage sélectionnée) dans le Rechercher et remplacer dialogue.
-
Cliquez sur Remplacer tout, puis fermez la boîte de dialogue. Ce qui suit montre à quoi ressembleront les données.
Étape 4 : Transposer le résultat Coller le lien remplacé
Sélectionnez la plage (A6: C9) et appuyez sur Ctrl + C pour le copier. Faites un clic droit sur une cellule vide (ici j'ai sélectionné A11) et sélectionnez le Transposer icône (sur le bord gauche de l'écran) du Options de collage pour coller le résultat transposé.
Étape 5 : Récupérez les signes égal (=) pour lier le résultat transposé aux données d'origine
-
Conserver les données de résultat transposées A11: D13 sélectionné, puis appuyez sur Ctrl + Het remplacez @EO avec = (l'opposé de étape 3).
-
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 effectuer le travail en suivant les étapes ci-dessous :
Étape 1 : Sélectionnez la plage à transposer et ouvrez le Power Query Éditeur
Sélectionnez la plage de données à transposer. Et puis, sur le Données onglet, dans le Obtenir et transformer des données groupe, cliquez sur À partir du tableau/plage.
- Si la plage de données sélectionnée n'est pas dans un tableau, un Créer un tableau la boîte de dialogue apparaîtra ; Cliquez sur OK pour lui créer un tableau.
- Si vous utilisez Excel 2013 ou 2010 et que vous ne trouvez pas À partir du tableau/plage sur le Données , vous devrez le télécharger et l'installer à partir de l'onglet Microsoft Power Query pour la page Excel. Une fois installé, rendez-vous sur Power Query onglet, cliquez sur De la table dans l' Données Excel groupe.
Étape 2 : Convertissez les colonnes en lignes avec Power Query
-
Allez à Transformer languette. dans le Utiliser la première ligne comme en-tête menu déroulant, sélectionnez Utiliser les en-têtes comme première ligne.
-
Cliquez sur Transposer.
Étape 3 : Enregistrer les données transposées dans une feuille
Sur le Déposez votre dernière attestation onglet, cliquez sur Fermer et charger pour fermer la fenêtre Power Editor 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 tableau dans une feuille de calcul nouvellement créée.
- Des en-têtes de colonne supplémentaires sont générés dans la première ligne, comme indiqué ci-dessus. Pour promouvoir la première ligne sous les en-têtes en en-têtes de colonne, sélectionnez une cellule dans les données et cliquez sur Question > Modifier. Ensuite, sélectionnez Transformer > Utiliser la première ligne comme en-tête. Enfin, sélectionnez Accueil > Fermer et charger.
- Si des modifications sont apportées à l'ensemble de données d'origine, vous pouvez mettre à jour les données transposées ci-dessus avec ces modifications en cliquant sur le Refresh icône (sur le bord gauche de l'écran) à côté de la table dans le Requêtes et connexions volet, ou en cliquant sur le Refresh bouton sur la Question languette.
(AD) Transformer la gamme avec Kutools en quelques clics
La Transformer la gamme L'utilitaire de Kutools for Excel peut vous aider à transformer (convertir) facilement une colonne verticale en plusieurs colonnes ou inversement, ou à convertir une ligne en plusieurs lignes ou vice versa.
Kutools for Excel - Comprend plus de 300 fonctions pratiques, ce qui rend votre travail beaucoup plus facile. Obtenez un essai gratuit complet de 30 jours maintenant !
Articles connexes
- Comment transposer rapidement des plages et ignorer les cellules vides dans Excel?
- Lorsque nous collons une plage transposée, les cellules vides seront également collées. Cependant, parfois, nous voulons simplement transposer les plages en ignorant les cellules vides comme illustré ci-dessous. Existe-t-il des moyens de transposer rapidement les plages et d'ignorer les blancs dans Excel ?
- Comment transposer toutes les 5 ou n lignes d'une colonne à plusieurs colonnes?
- Supposons que vous ayez des données longues dans la colonne A et que vous souhaitiez maintenant transposer toutes les 5 lignes de la colonne A vers plusieurs colonnes, par exemple transposer A1: A5 en C6: G6, A6: A10 en C7: G7, etc. capture d'écran suivante illustrée. Comment pouvez-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, vous rencontrez parfois ce problème : comment pourriez-vous convertir ou transposer une plage de données en une seule colonne ? (Voir les captures d'écran suivantes :) 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 longue ligne ? Peut-être que cela vous semble facile, car vous pouvez les copier un par un et les joindre manuellement dans une rangée. Cependant, cela peut prendre du temps et être fastidieux s'il y a des centaines de lignes et de colonnes. Ici, je vais parler de quelques astuces rapides pour le résoudre.
Meilleurs outils de productivité bureautique
Améliorez vos compétences Excel avec Kutools for Excel et faites l'expérience d'une efficacité comme jamais auparavant. Kutools for Excel offre plus de 300 fonctionnalités avancées pour augmenter la productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et facilite grandement votre travail
- Activer l'édition 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é de 50% et réduit des centaines de clics de souris chaque jour!
Table des matières
- Vidéo : Transposer des données dans Excel
- Basculer les colonnes en lignes avec Collage spécial
- Transposer et lier les données à la source
- Avec la fonction TRANSPOSER
- Avec les fonctions INDIRECT, ADDRESS, COLUMN et ROW
- Avec Collage spécial et Rechercher et remplacer
- Avec Power Query
- Articles connexes
- Les meilleurs outils de productivité de bureau
- Commentaires