Voir la vidéo – Comment transposer des données dans Excel
Si vous avez un ensemble de données et que vous voulez le transposer dans Excel (ce qui signifie convertir des lignes en colonnes et des colonnes en lignes), le faire manuellement est un NON complet !
Ce tutoriel couvre :
Transposer des données à l’aide de Paste Special
Paste Special peut faire beaucoup de choses étonnantes, et l’une de ces choses est de transposer des données dans Excel.
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous :
Ces données ont les régions dans une colonne et les quartiers dans une ligne.
Maintenant, pour une raison quelconque, si vous devez transposer ces données, voici comment vous pouvez le faire en utilisant le collage spécial:
- Sélectionnez l’ensemble de données (dans ce cas A1:E5).
- Copier l’ensemble de données (Control + C) ou faire un clic droit et sélectionner copier.
- Maintenant vous pouvez coller les données transposées dans un nouvel emplacement. Dans cet exemple, je veux copier dans G1:K5, alors faites un clic droit sur la cellule G1 et sélectionnez coller spécial.
- Dans la boîte de dialogue coller spécial, cochez l’option transposer en bas à droite.
- Cliquez sur OK.
Cela permettrait de copier et de coller instantanément les données mais de telle sorte qu’elles aient été transposées. Vous trouverez ci-dessous une démo montrant l’ensemble du processus.
Les étapes présentées ci-dessus copient la valeur, la formule (le cas échéant), ainsi que le format. Si vous voulez seulement copier la valeur, sélectionnez ‘valeur’ dans la boîte de dialogue de collage spécial.
Notez que les données copiées sont statiques, et si vous apportez des modifications à l’ensemble de données d’origine, ces modifications ne seraient pas reflétées dans les données transposées.
Si vous voulez que ces cellules transposées soient liées aux cellules d’origine, vous pouvez combiner la puissance de Find & Replace avec Paste Special.
Transposer des données en utilisant Paste Special et Find & Replace
L’utilisation de Paste Special seul vous donne des données statiques. Cela signifie que si vos données d’origine changent et que vous voulez que les données transposées soient également mises à jour, vous devez alors utiliser à nouveau le Paste Special pour les transposer.
Voici une astuce sympa que vous pouvez utiliser pour transposer les données tout en les liant aux cellules d’origine.
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous:
Voici les étapes pour transposer les données mais garder les liens intacts:
- Sélectionner l’ensemble de données (A1:E5).
- Copiez-le (Control + C, ou cliquez avec le bouton droit de la souris et sélectionnez copier).
- Maintenant vous pouvez coller les données transposées dans un nouvel emplacement. Dans cet exemple, je veux copier dans G1:K5, donc cliquez avec le bouton droit de la souris sur la cellule G1 et sélectionnez coller spécial.
- Dans la boîte de dialogue Coller spécial, cliquez sur le bouton Coller lien. Vous obtiendrez ainsi le même ensemble de données, mais ici les cellules sont liées à l’ensemble de données d’origine (par exemple, G1 est lié à A1, et G2 est lié à A2, et ainsi de suite).
- Avec ces nouvelles données copiées sélectionnées, Appuyez sur Control + H (ou allez dans Home -> Editing -> Find & Select -> Replace). Cela ouvrira la boîte de dialogue Rechercher & Remplacer.
- Dans la boîte de dialogue Rechercher & Remplacer, utilisez les éléments suivants :
- Dans Rechercher quoi : =
- Dans Remplacer par : !@# (notez que j’utilise !@# car c’est une combinaison unique de caractères qui a peu de chances de faire partie de vos données. Vous pouvez utiliser n’importe quel ensemble unique de caractères de ce type).
- Cliquez sur Remplacer tout. Cela remplacera l’égal à de la formule et vous aurez !@# suivi de la référence de la cellule dans chaque cellule.
- Cliquez avec le bouton droit de la souris et copiez l’ensemble des données (ou utilisez Control + C).
- Sélectionnez un nouvel emplacement, faites un clic droit et sélectionnez coller spécial. Dans cet exemple, je le colle dans la cellule G7. Vous pouvez le coller où vous voulez.
- Dans la boîte de dialogue Collage spécial, sélectionnez Transposer et cliquez sur OK.
- Copier coller cette donnée transposée nouvellement créée vers celle à partir de laquelle elle est créée.
- Maintenant, ouvrez à nouveau la boîte de dialogue Rechercher et remplacer et remplacez !@# par =.
Cela vous donnera une donnée liée qui a été transposée. Si vous apportez des modifications à l’ensemble des données d’origine, les données transposées se mettraient automatiquement à jour.
Note : Comme nos données d’origine ont A1 comme vide, vous devriez supprimer manuellement le 0 dans G1. Le 0 apparaît lorsque nous collons des liens, car un lien vers une cellule vide renverrait toujours un 0. En outre, vous auriez besoin de formater le nouvel ensemble de données (vous pouvez simplement copier-coller le format uniquement à partir de l’ensemble de données original).
Transposer des données à l’aide de la fonction TRANSPOSE d’Excel
La fonction TRANSPOSE d’Excel – comme son nom l’indique – peut être utilisée pour transposer des données dans Excel.
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous :
Voici les étapes pour le transposer :
- Sélectionnez les cellules où vous voulez transposer l’ensemble de données. Notez que vous devez sélectionner le nombre exact de cellules comme les données d’origine. Donc par exemple, si vous avez 2 lignes et 3 colonnes, vous devez sélectionner 3 lignes et 2 colonnes de cellules où vous voulez les données transposées. Dans ce cas, comme il y a 5 lignes et 5 colonnes, vous devez sélectionner 5 lignes et 5 colonnes.
- Entrez =TRANSPOSE(A1:E5) dans la cellule active (qui doit être la cellule supérieure gauche de la sélection et appuyez sur Control Shift Enter.
Ceci transposerait l’ensemble de données.
Voici quelques éléments à connaître sur la fonction TRANSPOSE :
- C’est une fonction tableau, vous devez donc utiliser Control-Shift-Enter et pas seulement Enter.
- Vous ne pouvez pas supprimer une partie du résultat. Vous devez supprimer l’ensemble du tableau de valeurs que la fonction TRANSPOSER renvoie.
- La fonction TRANSPOSER ne copie que les valeurs, pas le formatage.
Transposer des données à l’aide de Power Query
Power Query est un outil puissant qui vous permet de transposer rapidement des données dans Excel.
Power Query fait partie d’Excel 2016 (obtenez & Transformer dans l’onglet Données) mais si vous utilisez Excel 2013 ou 2010, alors vous devez l’installer en tant que module complémentaire.
Supposons que vous ayez l’ensemble de données présenté ci-dessous :
Voici les étapes pour transposer ces données à l’aide de Power Query :
Dans Excel 2016
- Sélectionner les données et aller dans Données -> Obtenir & Transformer -> De la table.
- Dans la boîte de dialogue Créer un tableau, assurez-vous que la plage est correcte et cliquez sur OK. Cela ouvrira la boîte de dialogue de l’éditeur de requêtes.
- Dans la boîte de dialogue de l’éditeur de requêtes, sélectionnez l’onglet ‘Transformer’.
- Dans l’onglet Transformer, allez dans Table -> Utiliser la première ligne comme en-têtes -> Utiliser les en-têtes comme première ligne. Cela garantit que la première ligne (qui contient les en-têtes : Q1, Q2, Q3 et Q4) est également traitée comme des données et transposée.
- Cliquez sur Transposer. Cela transposerait instantanément les données.
- Cliquez sur Utiliser la première ligne comme en-tête. Cela rendrait maintenant la première ligne des données transposées comme en-têtes.
- Allez dans le fichier -> Fermer et charger. Cela fermera la fenêtre de Power Editor et créera une nouvelle feuille qui contient les données transposées.
Notez que puisque la cellule supérieure gauche de notre ensemble de données était vide, elle obtient un nom générique Column1 (comme indiqué ci-dessous). Vous pouvez supprimer cette cellule des données transposées.
En Excel 2013/2010
En Excel 2013/10, vous devez installer Power Query en tant que module complémentaire.
Cliquez ici pour télécharger le module complémentaire et obtenir les instructions d’installation.
Une fois que vous avez installé Power Query, allez dans Power Query -> Excel Data -> From Table.
Cela ouvrira la boîte de dialogue create table. Maintenant, suivez les mêmes étapes que celles indiquées pour Excel 2016.
Vous pouvez également aimer les tutoriels Excel suivants :
- Multiplier dans Excel en utilisant le Paste Special.
- CONCATENER une plage Excel (avec et sans séparateur).
- Nettoyer les données dans les feuilles de calcul Excel.
- Comparer des colonnes dans Excel
- Texte Excel vers les colonnes
- Fonction TRIM Excel
- Fusionner des tableaux dans Excel
.