Ce tutoriel explique comment convertir une ligne en colonne dans Excel à l'aide de la fonction Collage spécial, de la fonction TRANSPOSE et des formules INDIRECT et ADRESSE.
La transposition de données dans Excel est une tâche familière à de nombreux utilisateurs. Très souvent, vous construisez un tableau complexe uniquement pour vous rendre compte qu'il est parfaitement logique de le faire pivoter pour une meilleure analyse ou présentation des données dans des graphiques.
Dans ce tutoriel, vous trouverez plusieurs façons de convertir des lignes en colonnes. Ces solutions fonctionnent dans toutes les versions d'Excel 2016, 2013, 2010, 2007 et antérieures, couvrent de nombreux scénarios possibles et expliquent les erreurs les plus courantes.
Supposons que vous disposiez d'un ensemble de données similaire à ce que vous voyez dans la partie supérieure des graphiques ci-dessous. Les noms de pays sont organisés en colonnes, mais la liste des pays est trop longue. Nous ferions donc mieux de changer les colonnes en lignes pour que la table tienne dans l'écran :
Pour basculer des lignes en colonnes, procédez comme suit :
Veillez à sélectionner une cellule située en dehors de la plage contenant vos données d'origine afin que les zones de copie et les zones de collage ne se chevauchent pas. Par exemple, si vous avez actuellement 4 colonnes et 10 lignes, la table convertie aura 10 colonnes et 4 lignes.
Remarque : Si vos données source contiennent des formules, veillez à utiliser correctement les références relatives et absolues selon qu’elles doivent être ajustées ou rester verrouillées sur certaines cellules.
Comme vous venez de le voir, la fonctionnalité Collage spécial permet d'effectuer des transformations de rangée à colonne (ou de colonne à ligne) en quelques secondes. Cette méthode copie également le formatage de vos données d'origine, ce qui ajoute un argument supplémentaire en sa faveur.
Cependant, cette approche présente deux inconvénients qui l’empêchent d’être qualifiée de solution idéale pour la transposition de données dans Excel :
Voyons comment changer de ligne en colonne en utilisant la technique classique de collage, mais en connectant la table résultante au jeu de données d'origine. La meilleure chose à propos de cette approche est que chaque fois que vous modifiez les données de la table source, la table retournée reflète les modifications et se met à jour en conséquence.
Vous obtiendrez un résultat similaire à celui-ci :
Cela transformera votre table en quelque chose d'un peu effrayant, mais ne vous affolez pas, juste 2 étapes supplémentaires, et vous obtiendrez le résultat souhaité.
C'est une solution un peu longue mais élégante, n'est-ce pas ? Le seul inconvénient de cette approche est que le formatage d'origine est perdu au cours du processus et que vous devrez le restaurer manuellement.
Un moyen plus rapide de convertir dynamiquement des colonnes en lignes dans Excel consiste à utiliser la formule TRANSPOSE ou INDIRECT / ADRESSE. Comme dans l'exemple précédent, ces formules conservent également les connexions aux données d'origine mais fonctionnent un peu différemment.
Comme son nom l'indique, la fonction TRANSPOSE est spécialement conçue pour transposer des données dans Excel :
=TRANSPOSE(tableau)
Dans cet exemple, nous allons convertir un autre tableau qui répertorie les résultats d’un teste de maitrise pour un ensemble d’élèves par matière :
Par exemple, notre exemple de table comporte 7 colonnes et 6 lignes, y compris les en-têtes. La fonction TRANSPOSE transformant les colonnes en lignes, nous sélectionnons une plage de 6 colonnes et 7 lignes.
Voilà, les colonnes sont converties en lignes, exactement comme nous le souhaitions :
Et le résultat est comme ceci :
Le principal avantage de la fonction TRANSPOSE est que la table pivotée conserve la connexion à la table source et chaque fois que vous modifiez les données source, la table transposée change en conséquence.
En résumé, bien que la fonction TRANSPOSE soit simple et facile à utiliser, elle manque assurément de souplesse et n’est donc peut-être pas la meilleure solution dans de nombreuses situations.
Dans cet exemple, nous utiliserons une combinaison de deux fonctions, ce qui est un peu délicat. Faisons donc tourner une petite table pour pouvoir mieux nous concentrer sur la formule.
Supposons que vous ayez des données dans 4 colonnes (A - D) et 5 lignes (1 - 5) :
Pour convertir les colonnes en lignes, procédez comme suit :
=INDIRECT(ADRESSE(COLONNE(A1);LIGNE(A1)))
C'est tout ! Dans votre table nouvellement créée, toutes les colonnes sont remplacées par des lignes.
Si vos données commencent dans une ligne autre que 1 et une colonne autre que A, vous devrez utiliser une formule un peu plus complexe :
=INDIRECT(ADRESSE(COLONNE(A1) - COLONNE($A$1) + LIGNE($A$1);LIGNE(A1) - LIGNE($A$1) + COLONNE($A$1)))
Où A1 est la cellule la plus en haut à gauche de votre table source. Veillez également à l’utilisation de références de cellules absolues et relatives.
Cependant, les cellules transposées ont l’air très terne, comparées aux données originales :
Mais ne soyez pas déçu, ce problème peut être facilement résolu. Pour restaurer le formatage d'origine, procédez comme suit :
Avantages : Cette formule offre un moyen plus flexible de transformer des lignes en colonnes dans Excel. Cela permet d'apporter des modifications à la table transposée, car vous utilisez une formule régulière, pas une formule matricielle.
Lacunes : je ne peux en voir qu’une - le formatage des données ordinales est perdu. Cependant, vous pouvez le restaurer rapidement, comme indiqué ci-dessus.
Maintenant que vous savez utiliser la combinaison INDIRECT / ADRESSE, vous voudrez peut-être avoir un aperçu de ce que la formule fait réellement.
Comme son nom l'indique, la fonction INDIRECT sert à référencer indirectement une cellule. Mais le vrai pouvoir d'INDIRECT est qu'il peut transformer n'importe quelle chaîne en référence, y compris une chaîne que vous construisez à l'aide d'autres fonctions et des valeurs d'autres cellules. Et c'est exactement ce que nous allons faire. Si vous suivez cela, vous comprendrez tout le reste avec facilité.
Comme vous vous en souvenez, nous avons utilisé 3 fonctions supplémentaires dans la formule - ADDRESS, COLONNE et LIGNE.
La fonction ADRESSE obtient l'adresse de la cellule par les numéros de ligne et de colonne que vous spécifiez, respectivement. S'il vous plaît rappelez-vous l'ordre : premièrement la rangée et après la colonne.
Dans notre formule, nous fournissons les coordonnées dans l'ordre inverse, et c'est ce qui fait le tour ! En d’autres termes, cette partie de la formule ADRESSE(COLONNE(A1);LIGNE(A1)) permute les lignes en colonnes, c’est-à-dire prend un numéro de colonne et le remplace par un numéro de ligne, puis prend un numéro de ligne et le convertit en numéro de colonne.
Enfin, la fonction INDIRECT sort les données pivotées. Rien d'épouvantable, n'est-ce pas?