Est-ce qu’il vous est déjà arrivé de travailler avec des données en colonne et de vouloir plutôt les utiliser en ligne (ou l’inverse)? Bien sûr, vous pouvez faire une manipulation simple avec le “Collage Spécial” (“Paste Special”) mais si vous souhaitez que vos données en colonne demeurent liées à vos données en ligne, comment procéder? Cet article vous propose d’utiliser la fonction “Indirect” pour bien transposer vos formules dans Excel.
Tranposer des données de colonnes à lignes dans Excel
(sans lier les données entre elles)
1 – Sélectionnez d’abord vos données en colonne et appuyez sur CTRL+C (pour copier).
2 – Placez le curseur à l’endroit désiré dans votre page et rendez-vous dans le menu “Collage Spécial” (“Paste Special”) et choisissez l’option “Transpose”.
Vos données en colonne, apparaîtront maintenant en ligne.
Ces données ne seront toutefois pas liées entre elles. Si vous changez le nom de Claude par Annie dans votre colonne de données, le changement ne sera pas réflété dans votre ligne.
Tranposer des formules de colonnes à lignes dans Excel
(lier les données entre elles)
Pour lier vos données en colonne avec vos données en ligne, vous devez utiliser la fonction “Indirect”, ainsi que la fonction “Colonne” (“Column”), tel qu’illustré dans l’image ci-bas. En utilisant cette technique, on ne va pas simplement transposer des données, on va concrètement transposer des formules.
De cette façon, lorsque vous changerez le nom d’Annie dans vos données en colonne, celui-ci changera également dans vos données en ligne.
Décortiquons maintenant la formule :
La fonction “Indirect” ramène la valeur de la colonne “C” et tout le reste de la formule sert à trouver la bonne ligne.
En E4 (Annie), voici comment on doit décomposer la formule:
Column() = 5
5-1 = 4
De sorte qu’en E4, on va chercher la valeur de la cellule C4.
De même, en F4, on obtient:
Column()=6
6-1=5
De sorte qu’en F4, on va chercher la valeur de la cellule C5.
Évidemment, si vous changez la disposition de votre ligne, vous devrez ajuster votre formule en conséquence.
Formation complémentaire
Développez vos compétences en analyse de données et exploitez les fonctions d’Excel qui permettent de manipuler, gérer et transformer des données qui proviennent de diverses sources et différents formats, sans faire intervenir Power Query ou le VBA, en suivant la formation Excel – Traitement, manipulation et analyse de données.
Voici quelques commentaires d’apprenants ayant suivi cette formation :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
merci pour l’astuce!
En complément, il y a également une formule matricielle “transpose”. Elle me semble particulièrement adaptée à la problématique.
Bonjour Asnor,
En effet, la fonction matricielle est intéressante, sauf si on travaille avec un modèle avec lequel les données en colonnes vont évoluer avec le temps. Lorsque la liste va s’agrandir, la fonction transpose sera à refaire tandis que la fonction indirect pourra être inscrite à l’avance.
Au plaisir,
Sophie
Cela ne fonctionne pas pour moi, qu’est-ce que je fais de pas correct pour que la réponse me donne: #NOM? Est-ce bien écrit de cette façon: =INDIRECT(“E”&column(11)-1)
Bonjour Dominique,
C’est probablement que votre Excel est en français. Si c’est le cas, remplacez column par colonne.
Au plaisir,
Sophie
Bon petit truc. J’aimerais bien que tu fasses un billet un jour sur les “array formula” (celles qu’il faut confirmer en faisant ctrl+shift+enter). C’est peut-être ça à quoi vous faites référence en parlant de formules matricielles. Ça semble assez puissant, mais j’ai beau lire sur le sujet, ce n’est jamais aussi clair que des explications de MonCherWatson. Quelle est leur utilité? Sont-elles trop compliquées pour rien?
Merci!
Bonjour Martin,
Merci pour votre commentaire. Je prends bonne note de votre demande concernant les “array formulas”.
En fait, celles-ci ne sont pas “trop compliquées”. Au contraire, elles sont très puissantes. Et dans certaines situations, il sera impossible de s’en sortir autrement.
Par contre, je ne les utilise pas souvent puisque je travaille surtout dans des modèles évolutifs, qui doivent donc s’ajuster automatiquement lorsqu’on insère de nouvelles données (y compris de nouvelles lignes et colonnes). Avec les “array formulas”, cet aspect est plus difficile à gérer.
Mais cela dit, je vais tâcher de faire quelques exercices sur ces fonctions dans un proche avenir.
Merci pour la suggestion.
Au plaisir,
Sophie