Si vous avez un jeu de données et que vous souhaitez le transposer dans Excel (Cela signifie convertir les lignes en colonnes et les colonnes en lignes), l'exécution manuelle est totalement impossible !
Table des matières
Transposer les données à l'aide du collage spécial
Collage spécial peut faire beaucoup de choses étonnantes, dont l'une consiste à transposer des données dans Excel.
Supposons que vous ayez un ensemble de données qui ressemble à ceci :
Les données ont des régions en colonnes et des trimestres en lignes.
Maintenant, pour une raison quelconque, si vous avez besoin de transposer ces données, vous pouvez utiliser le collage spécial de la manière suivante :
Cela copiera et collera les données immédiatement, mais d'une manière qui a été transposée.Vous trouverez ci-dessous une démo montrant l'ensemble du processus.
Les étapes ci-dessus copient les valeurs, les formules (le cas échéant) et la mise en forme.Si vous souhaitez simplement copier la valeur, sélectionnez "Valeur" dans la boîte de dialogue de collage spéciale.
Notez que les données copiées sont statiques et que si vous apportez des modifications à l'ensemble de données d'origine, ces modifications ne seront pas reflétées dans les données transposées.
Si vous souhaitez lier ces cellules transposées aux cellules d'origine, vous pouvez combiner la puissance de Rechercher et remplacer avec Collage spécial.
Transposer les données en utilisant le collage spécial et rechercher et remplacer
Collage spécial seul vous donne des données statiques.Cela signifie que si vos données d'origine changent et que les données que vous souhaitez transposer sont également mises à jour, vous devez à nouveau utiliser le collage spécial pour les transposer.
C'est une astuce sympa que vous pouvez utiliser pour transposer les données et toujours les lier aux cellules d'origine.
Supposons que vous ayez un ensemble de données qui ressemble à ceci :
Voici les étapes pour transposer les données mais garder le lien intact :
- Sélectionnez le jeu de données (A1:E5).
- Dupliquez-le (Ctrl + C, ou faites un clic droit et choisissez Dupliquer).
- Vous pouvez maintenant coller les données transposées au nouvel emplacement.Dans cet exemple, je veux copier dans G1: K5, donc faites un clic droit sur la cellule G1 et choisissez Collage spécial.
- Dans la boîte de dialogue Collage spécial, cliquez sur le bouton Coller le lien.Cela vous donnera 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, G2 est lié à A2, etc.).
- Avec ces données nouvellement copiées sélectionnées, appuyez sur Ctrl + H (ou allez dans Accueil -> Modifier -> Rechercher et sélectionner -> Remplacer).Cela ouvrira la boîte de dialogue Rechercher et remplacer.
- Dans la boîte de dialogue Rechercher et remplacer, utilisez les commandes suivantes :
- Dans Trouver quoi :=
- Remplacez par : !@# (notez que j'ai utilisé !@# car il s'agit d'une combinaison unique de caractères et il est peu probable qu'elle fasse partie de vos données. Vous pouvez utiliser n'importe quel jeu de caractères unique).
- Cliquez sur Remplacer tout.Cela remplacera les égaux dans la formule et vous aurez !@# suivi de la référence de cellule dans chaque cellule.
- Faites un clic droit et copiez le jeu de données (ou utilisez Control+C).
- Sélectionnez un nouvel emplacement, cliquez avec le bouton droit et sélectionnez Collage 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.
- Copiez-collez ces données transposées nouvellement créées là où elles ont été créées.
- Ouvrez à nouveau la boîte de dialogue Rechercher et remplacer et remplacez !@# par =.
Cela vous donnera les données liées transposées.Les données transposées seront automatiquement mises à jour si vous apportez des modifications à l'ensemble de données d'origine.
Remarque : Étant donné que A1 est vide dans nos données d'origine, vous devez supprimer manuellement le 1 dans G0.Le 0 apparaît lorsque nous collons le lien, car un lien vers une cellule vide renvoie toujours 0.De plus, vous devez formater le nouveau jeu de données (vous pouvez simplement copier-coller le formatage du jeu de données d'origine).
Transposer les données à l'aide de la fonction Excel TRANSPOSE
La fonction Excel TRANSPOSE - comme son nom l'indique - permet de transposer des données dans Excel.
Supposons que vous ayez un ensemble de données qui ressemble à ceci :
Voici les étapes pour le transposer :
- Sélectionnez les cellules dans lesquelles vous souhaitez transposer le jeu de données.Notez que vous devez sélectionner le nombre exact de cellules en tant que données brutes.Ainsi, par exemple, si vous avez 2 lignes et 3 colonnes, vous devez sélectionner les 3 lignes et 2 colonnes de cellules où vous souhaitez transposer les données.Dans ce cas, puisqu'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 (devrait être la cellule en haut à gauche sélectionnée et appuyez sur Control Shift Enter.
Cela transposera le jeu de données.
Voici ce que vous devez savoir sur la fonction TRANSPOSE :
- C'est une fonction de tableau, vous devez donc utiliser Control-Shift-Enter au lieu de simplement Enter.
- Vous ne pouvez pas supprimer une partie du résultat.Vous devez supprimer tout le tableau de valeurs renvoyé par la fonction TRANSPOSE.
- La fonction de transposition copie uniquement la valeur, pas le format.
Transposer les données avec Power Query
Power Query est un outil puissant qui vous permet de transposer rapidement des données dans Excel.
Requête de puissance OuiExcel 2016("Get and Transform" dans l'onglet "Data"), mais si vous utilisezExcel 2013 ou 2010, vous devez l'installer en tant que module complémentaire.
Supposons que vous ayez un ensemble de données qui ressemble à ceci :
Voici les étapes pour transposer ces données à l'aide de Power Query :
dans Excel 2016
Notez que puisque la cellule en haut à gauche de notre ensemble de données est vide, elle reçoit un nom générique Column1 (illustré ci-dessous).Vous pouvez supprimer cette cellule des données transposées.
Sous Excel 2013/2010
Dans Excel 2013/10, vous devez installer Power Query en tant que complément.
Cliquez iciTéléchargez le plugin et obtenez les instructions d'installation.
Après avoir installé Power Query, accédez à Power Query -> Excel Data -> From Table.
Cela ouvrira la boîte de dialogue Créer une table.Suivez maintenant les mêmes étapes que celles indiquées dans Excel 2016.
Vous pourriez également aimer les didacticiels Excel suivants :
- Multipliez dans Excel en utilisant Collage spécial.
- Concaténer des plages Excel (avec et sans délimiteurs).
