Astuce Excel : Formules transposées

Publié le 18 mars 2013
par Sophie Marchand M.Sc.

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

 

Transposée Paste Special

 

Vos données en colonne, apparaîtront maintenant en ligne.

 

Transposée Paste Special 2

 

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.

 

Transposée Paste Special 3

 

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.

 

Transposée Claude

 

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.

 

Transposée Annie

 

Vous aimeriez être un Ninja Excel et devenir la référence ultime de votre bureau, ou tout le moins ne plus avoir l’impression de stagner ? Développez des solutions innovatrices en explorant des fonctions et fonctionnalités souvent méconnues mais fort utiles avec nos formations pour devenir Ninja d’Excel.

 

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 :

Le CFO masqué - Commentaires d'apprenants ayant suivi la formation : Excel - Traitement, manipulation et analyse de données

CFO-Masque_Formations-en-ligne_FBLa 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é.

7 réflexions sur “Astuce Excel : Formules transposées”

  1. En complément, il y a également une formule matricielle “transpose”. Elle me semble particulièrement adaptée à la problématique.

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

  2. 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)

  3. Martin Thériault

    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!

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

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut