Contact: 514-605-7112 / info@lecfomasque.com

Astuce Excel : Formules transposées

Publié le : 18 mars 2013

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.

.


NOTRE CATALOGUE DE FORMATIONS


Catalogue de formations

Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

Pour info: 514-605-7112 ou info@lecfomasque.com

Téléchargez notre catalogue

Sophie Marchand, M.Sc., CPA, CGA, MVP

Instigatrice du CFO masqué, Sophie Marchand est détentrice d’une M.Sc. en finance corporative, d’un titre comptable CPA, CGA et d’un titre MVP (Most valuable professional) Excel de Microsoft, et cumule plus de 12 années d’expérience dans le milieu des affaires. Elle se spécialise particulièrement en modélisation financière et en intelligence d’affaires. À ce titre, elle développe des modèles financiers rigoureux, des tableaux de bord sophistiqués et des outils de gestion performants. Elle offre ses services en tant que consultante, formatrice et conférencière.

  • répondre LORY ,

    merci pour l’astuce!

    • répondre Asnor ,

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

      • répondre moncherwatson ,

        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

      • répondre Dominique ,

        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)

        • répondre moncherwatson ,

          Bonjour Dominique,

          C’est probablement que votre Excel est en français. Si c’est le cas, remplacez column par colonne.

          Au plaisir,

          Sophie

        • répondre 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!

          • répondre moncherwatson ,

            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

          Écrire un commentaire


          • *