Excel : convertir une ligne en colonne (transposer un tableau)

Participez au vote ☆☆☆☆☆★★★★★

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.

Table des matières

Convertir des lignes en colonnes dans Excel avec le Collage Spécial

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 :

  1. Sélectionnez les données d'origine. Pour sélectionner rapidement l’ensemble du tableau, c’est-à-dire toutes les cellules contenant des données dans une feuille de calcul, appuyez sur Ctrl + Home, puis sur Ctrl + Shift + End.
  2. Copiez les cellules sélectionnées en cliquant avec le bouton droit de la souris sur la sélection et en choisissant Copier dans le menu contextuel ou en appuyant sur Ctrl + C.
  3. Sélectionnez la première cellule de la plage de destination.

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.

  1. Cliquez avec le bouton droit sur la cellule de destination et choisissez Collage spécial dans le menu contextuel, puis sélectionnez Transposé.

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 :

  • Il n'est pas bien adapté à la rotation de tableaux Excel entièrement fonctionnels. Si vous copiez la table entière puis ouvrez la boîte de dialogue Collage spécial, vous constaterez que l'option Transposer est désactivée. Dans ce cas, vous devez soit copier la table sans en-têtes de colonne, soit la convertir au préalable en plage.
  • Collage spécial > Transposé ne lie pas la nouvelle table aux données d'origine. Elle ne convient donc que pour les conversions uniques. Chaque fois que les données source changent, vous devez répéter le processus et faire une nouvelle rotation du tableau. Personne ne voudrait perdre son temps à convertir sans cesse les mêmes lignes et colonnes, non?

Comment transposer une table et la lier aux données d'origine

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.

  1. Copiez les lignes que vous souhaitez convertir en colonnes (ou les colonnes à modifier en lignes).
  2. Sélectionnez une cellule vide dans la même ou une autre feuille de calcul.
  3. Ouvrez la boîte de dialogue Collage spécial, comme expliqué dans l'exemple précédent, puis cliquez sur Coller avec liaison dans le coin inférieur gauche :

Vous obtiendrez un résultat similaire à celui-ci :

  1. Sélectionnez le nouveau tableau et ouvrez la boîte de dialogue Rechercher et remplacer d'Excel (ou appuyez sur Ctrl + H pour accéder à l'onglet Remplacer immédiatement).
  2. Remplacez tous les caractères "=" par "xxx" ou par tout autre caractère qui n'existe nulle part dans vos données réelles.

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é.

  1. Copiez le tableau avec les valeurs "xxx", puis utilisez Collage spécial > Transposé pour retourner les colonnes en lignes.
  2. Enfin, ouvrez une nouvelle fois la boîte de dialogue Rechercher et remplacer pour inverser la modification, c’est-à-dire remplacer tout "xxx" par "=" pour restaurer les liens vers les cellules d’origine.

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.

Comment transposer dans Excel à l'aide de formules

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.

Modifier les lignes en colonnes dans Excel à l'aide de la fonction TRANSPOSE

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 :

  1. Comptez le nombre de lignes et de colonnes dans votre table d'origine et sélectionnez le même nombre de cellules vides, mais dans l'autre sens.

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.

  1. Avec les cellules vides sélectionnées, tapez cette formule : =TRANSPOSE(A1:G6)
  2. Puisque notre formule doit être appliquée à plusieurs cellules, appuyez sur Ctrl + Shift + Entrée pour en faire une formule matricielle.

Voilà, les colonnes sont converties en lignes, exactement comme nous le souhaitions :

Et le résultat est comme ceci :

Avantages de la fonction TRANSPOSE :

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.

Faiblesses de la fonction TRANSPOSE :

  • La mise en forme de la table d'origine n'est pas enregistrée dans la table convertie, comme le montre la capture d'écran ci-dessus.
  • S'il y a des cellules vides dans la table d'origine, les cellules transposées contiendront 0 à la place. Pour résoudre ce problème, utilisez TRANSPOSE en combinaison avec la fonction SI.
  • Vous ne pouvez éditer aucune cellule dans la table pivotée car celle-ci dépend beaucoup des données source. Si vous essayez de modifier une valeur de cellule, vous obtiendrez l'erreur "Vous ne pouvez pas modifier une partie d'un tableau".

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.

Convertir une ligne en colonne avec les fonctions INDIRECT et ADRESSE

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 :

  1. Entrez la formule ci-dessous dans la cellule la plus à gauche de la plage de destination, par exemple A7, puis appuyez sur la touche Entrée :

=INDIRECT(ADRESSE(COLONNE(A1);LIGNE(A1)))

  1. Copiez la formule vers la droite et vers le bas dans autant de lignes et de colonnes que nécessaire en faisant glisser une petite croix noire dans le coin inférieur droit des cellules sélectionnées :

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 :

  1. Copiez la table d'origine.
  2. Sélectionnez la table résultante.
  3. Cliquez avec le bouton droit sur la table résultante et choisissez Option de collage > Mise en forme.

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.

Comment fonctionne cette formule

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?

Contenu publié le 21 Juillet 2019 et rédigé par Hanane Mouqqadim