Le fichier utilisé dans ces notes est disponible au format XLS (Transpose.xls 160K).

1. Il arrive parfois de devoir transformer des données afin de pouvoir les exploiter sous forme de liste Excel. C'est le cas notamment de données présentées sur différentes lignes, qu'il faut transposer pour les présenter en colonne.

La solution proposée ici est basée sur les fonctions TRANSPOSE et INDIRECT.



La fonction TRANSPOSE renvoie une plage verticale de cellules sous forme de plage horizontale, ou vice versa. La fonction TRANSPOSE doit être entrée sous forme de formule matricielle dans une plage dont le nombre de lignes et de colonnes est égal au nombre de lignes et de colonnes du tableau, respectivement. Cette fonction réalise le même traitement que le "Collage spécial" avec "Transposition".

Pour obtenir le même résultat avec le "Collage spécial", il faut, pour chaque groupe de 6 lignes, procéder de la façon suivante :

• Sélectionner puis copier les 6 lignes d'un groupe (par exemple : C1:C6)
• Sélectionner la première cellule libre de la feuille "Résultats"
• Utiliser "Collage spécial" avec la case "Transposé" cochée.


Pour obtenir le même résultat avec la fonction TRANSPOSE, il faut, pour chaque groupe de 6 lignes, procéder de la façon suivante :

• Sélectionner 6 colonnes dans la feuille "Résultats"
• Utiliser la formule =TRANSPOSE(Source!C1:C6) en terminant la saisie par Ctrl+MAj+ Enter car TRANSPOSE est une fonction matricielle.

On peut bien sûr améliorer cette dernière procédure. Pour cela, on va calculer chaque référence à transposer à l'aide de la fonction INDIRECT.

La fonction INDIRECT renvoie la référence spécifiée par une chaîne de caractères. Les références sont immédiatement évaluées afin d'afficher leur contenu. On utilise la fonction INDIRECT lorsqu'on veut modifier la référence à une cellule à l'intérieur d'une formule sans modifier la formule à proprement parler. Ainsi, si dans une cellule quelconque, on indique :
=INDIRECT("Source!C1"), on obtient : ATHENEE ROYAL PRINCE BAUDOUIN et
la formule utilisée plus haut (=TRANSPOSE(Source!C1:C6)) peut s'écrire :
=TRANSPOSE(INDIRECT("Source!C1:C6")) puis
=TRANSPOSE(INDIRECT("Source!C7:C12")), … .

Dans notre exemple, on va générer automatiquement les numéros de lignes qu'il faut utiliser. Dans la feuille "Résultats", on indique :

• en B3, le nom de la feuille de données (ici " Source "),
• en C3 le nom de la colonne à transposer (ici " C "),
• en B6 le numéro de la première ligne (ici " 1 "),
• en C6 la formule =B6+5,
• en B7 la formule =C6+1,
• on génère les cellules suivantes en recopiant de B8 à B71 la formule en B7 et de C7 à C71, la formule en C6.



On peut maintenant utiliser la fonction TRANSPOSE :

• Sélectionner les cellules E6:J6 de la feuille " Résultats "
• Utiliser la formule =TRANSPOSE(INDIRECT($B$3&"!"&$C$3&B6&":"&$C$3&C6)) en terminant la saisie par Ctrl+MAj+Enter car TRANSPOSE est une fonction matricielle.



• Sélectionner à nouveau les cellules E6:J6
• Recopier cette sélection jusqu'à la ligne 71.



2. On peut bien sûr réaliser la manipulation inverse, c'est-à-dire transformer des données en colonne pour les présenter en ligne. Voir un exemple en choisisant l'onglet Inverse dans le fichier d'accompagnement.
3. Pour une bonne utilisation des résultats, il faut copier les données (E5:J71 de la feuille Résultats ou C22:C417 de la feuille Inverse) et les coller sur une autre feuille (ou un autre classeur) en utilisant le "Collage spécial" avec "Valeurs".