Astuce Excel : Formules transposées

Publié le 18 mars 2013
par Sophie Marchand M.Sc., CPA, CGA, MVP

Astuce Excel : Formules transposées

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

.

Tranposer les données de colonnes à lignes

(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 les données de colonnes à lignes

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

.

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

.

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.

.

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

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 de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top