Excel: La fonction INDIRECT pour créer des sommaires exécutifs en un clic de souris

Publié le 05 juillet 2011
par Sophie Marchand M.Sc., CPA, CGA, MVP

Excel: La fonction INDIRECT pour créer des sommaires exécutifs en un clic de souris

Souvent, on travaille avec un nombre considérable d’onglets identiques dans Excel, qu’il s’agisse de données par produit, par semaine, par employé. Pour toutes sortes de raisons, on peut vouloir traiter les données séparément par onglet mais on peut également vouloir avoir une vue d’ensemble des données du fichier. Pour créer un tel sommaire exécutif en un clin d’oeil, vous devez utiliser la fonction INDIRECT, la fonction ADDRESS (adresse en français) et la fonction COLUMN (colonne en français), imbriquées les unes dans les autres.

 

S’assurer que tous les onglets sont identiques

Vous devez d’abord vous assurer que tous vos onglets sont identiques au niveau de leur structure et de leur position dans le chiffrier (mêmes coordonnées). Dans l’exemple ci-bas, les onglets A, B, C, D, etc. sont tous structurés de la même façon. Les premières données apparaissent dans la cellule C4 et les dernières données dans la cellule I4.
.

.

.

 

Utiliser la fonction ADDRESS imbriquée dans la fonction INDIRECT 

Pour créer votre onglet sommaire, vous devez reprendre les titres de colonnes de vos onglets et mettre en colonne tous les noms de vos onglets. Par la suite, vous utiliserez la fonction suivante:

=INDIRECT(ADDRESS(numéro de ligne, numéro de colonne, référence absolue (voir tableau ci-bas), A1 vs R1C1 (voir explications ci-bas)

Référence absolue (type de valeur retournée):

1 – Type $A$1
2 – Type A$1
3 – Type $A1
4 – A1

 

A1 vs R1C1

Dans Excel, on peut travailler selon deux mode qui sont les modes A1 et R1C1. La très grande majorité des utilisateurs d’Excel travaillent en A1. Le mode R1C1 est davantage utilisé lors de l’usage de macros (nous traiterons de ce mode des articles à venir).

 

Numéro de colonne

Pour le numéro de colonne, placez votre tableau sommaire dans la même colonne que vos données par onglet (dans l’exemple ci-dessous la colonne C) et utilisez la fonction COLUMN().

.

 

En utilisant la fonction COLUMN(), vous pouvez simplement copier votre formule à l’ensemble du tableau pour obtenir votre sommaire. Si vous aviez plutôt utiliser un chiffre fixe, vous auriez dû avoir 7 formules différentes, soit une par colonne.

 

Précautions

Enfin, si vous changez le nom d’un onglet, assurez-vous de le changer également dans votre tableau sommaire.
.

.

Lorsque vous souhaitez faire des modifications à un onglet, assurez-vous d’appliquer ces modifications à tous les onglets identiques à la fois. Pour ce faire, cliquez sur votre premier onglet (dans notre exemple A ou Albert), cliquez ensuite sur SHIFT et cliquez finalement sur votre dernier onglet (dans notre exemple Z). Vous verrez que tous les onglets intermédiaires sont pâlis. Effectuez ensuite les modifidations dans votre premier onglet. Celles-ci s’appliqueront automatiquement à tous vos onglets. Pour sortir de ce mode de modifications simultanées, cliquer sur un onglet au hasard. Vous verrez que la couleur des onglets redeviendra plus foncée. Vous saurez alors que vous n’êtes plus dans un mode de modifications simultanées des onglets.

 


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

 

Formation complémentaire

Afin d’approfondir vos connaissances de ce type de fonctions, nous vous recommandons notre formation Excel – Modélisation financière (niveau 1) .

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Jean-Philippe Provost
Écrit il y a 1 mois
Super formation !

Me permettra sans aucun doute de relevé la qualité de mes modélisations financières même si je modélise depuis plus de 10 ans.

Antoine Champagne
Écrit il y a 6 mois
Excellents outils

Des outils excellents pour bien organiser et rendre clair nos fichiers Excel. Je travaille à tous les jours avec excel et c'est bon de pouvoir bonifier et améliorer mon travail. Vraiment, Mme Marchand, vous êtes excellente; j'ai hâte à la formation suivante. Ça fait plaisir d'encourager une entreprise québécoise qui "excel" dans son domaine.

Paul Chainé
Écrit il y a 1 an
Vraiment très agréable à suivre

La narration et les explications se font avec un débit qui laisse le temps de voir et comprendre ce qui est expliqué, dans un style amical et décontracté, la simplicité des exercices est très appréciée ce qui fait qu'on les fait et c'est justement ce qui nous permet d'assimiler la matière.

Mélanie Hébert
Écrit il y a 2 ans
Très utile

J'aime que nous avons des excel que nous pouvons revoir par la suite j'aime que la formation soit faite avec des sections définies et claires et que chaque sujet ait son propre document de présentation ce qui permet de le consulter facilement au besoin

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

 

18 réflexions sur “Excel: La fonction INDIRECT pour créer des sommaires exécutifs en un clic de souris”

  1. Wow… et moi qui recréait tout à la main depuis le début… avez-vous d’autres trucs similaires qui aideraient à créer des sommaires exécutifs, comme par exemple, la somme de tous les onglets dans l’onglet sommaire?

    Merci,

    Mathieu

  2. Bonjour Madame Marchand
    Merci pour ce billet. J’aurais par contre une question pour vous. Qu’elle est la différence (avantages et inconvénient) entre utiliser la fonction indirect et l’utilisation de la consolidation (soit par position ou par catégorie).
    Merci de me donner votre opinion à ce sujet.
    Marc

  3. Bonjour Madame Marchand
    Dans Excel 2010 (et je crois que c’est au même endroit en 2007) si vous aller dans l’onglet Données, groupe outils de données, bouton Consolider. Cette option fait le même travail, mais en plus, il permet de calculer des tableaux qui ne seraient pas placés au même endroit dans chacune des feuilles. Si par exemple, j’ai un tableau avec 12 feuilles (12 mois de l’année) et que sur la feuille Janvier, dans la colonne A, j’ai Nord, Sud, Est, Ouest, mais que sur la feuille Février, j’ai Sud, Est, Nord, Ouest, ma feuille sommaire sera en mesure de repérer les étiquettes de colonne sans avoir à lui dire ou sont les les données pour le Nord, de chacune des feuilles. C’est ce que l’on appelle la consolidation, soit par position (si toutes les étiquettes sont placées au même endroit sur chacune des feuilles) ou par catégorie (si les étiquettes ne sont pas placées au même endroit). Alors ma question était qu’elle est la différence (avantages et inconvénient) entre la fonction indirect (dans l’exemple données dans votre tableau), et la consolidation?.
    Merci
    Marc

    1. Bonjour Marc,

      Pour être bien franche avec vous, je n’avais jamais exploré cette fonctionnalité!

      Suite à votre remarque, j’ai donc été faire le test. Je n’ai pas passé beaucoup de temps à la tester mais tout de suite, je n’ai pas aimé le fait de devoir sélectionner chaque zone à consolider. Si le fichier est tout petit, ça va, mais dès qu’il y a plusieurs onglets, ça devient laborieux.

      Je dirais donc que la fonction Indirect, permet de contourner ce problème. Elle m’apparaît un moyen plus rapide d’arriver au même résultat.

      Mais je vais explorer cette fonctionnalité davantage et je vais tenter ensuite de vous communiquer ce que j’en pense plus clairement à travers un billet de blogue.

      Au plaisir,

      Sophie

  4. Bonjour Mme Marchand
    Lors que le document est plus gros, il est possible d’utiliser la même option que dans une formule 3D, soit de tenir le bouton SHIFT pour dire de qu’elle feuille à qu’elle feuille la zone est à consolider.
    Si vous désirez, donner moi une adresse courriel pour que je puisse vous envoyer un document plus explicatif, si vous le désirez.
    Merci et bonne journée
    Marc

  5. Bonjour Mme Marchand
    Ce que je ferai, c’est que je vous enverrai au début de la semaine prochaine, des fichiers avec des explications comment fonctionne la consolidation.
    Si vous jugez que cela pourrait être intéressant, complémentaire avec Mon cher Watson et pourrait être intéressant avec les gens qui vous lisent, alors j’accepterai volontier votre offre. Est-ce que cela vous conviendrait?
    Merci et bonne fin de journée
    Marc

  6. Bonjour,

    J’ai beaucoup apprécié vos tutos ! celui-ci m’interesse particulièrement … mais là où je ne comprend pas c’est l’interet d’avoir une ligne par onglet ?? Mon but personnel étant de consolider les infos présentes sur 4 onglets en un seul (ligne par ligne), et dans votre exemple … les titres des colonnes doivent être identiques… mais les lignes aussi … ça implique que les lignes soient indentifiées et qu’elles ne changent pas …. ai-je bien compri ?

    1. Bonjour Rida,

      En fait, mon exemple ne comporte qu’une ligne par onglet mais vous pouvez en mettre plus qu’une…

      La fonction ne tient pas compte des titres de colonnes comme tels mais plutôt de la structure de chaque onglet, qui doit être identique.

      Par exemple, si on veut faire le sommaire des ventes de mai 2012 dans l’onglet sommaire et que chaque onglet comprend les ventes de 2012 d’un produit différent, il faudrait placer les chiffres de ventes de mai 2012 dans la même cellule de chaque onglet (par exemple D4).

      Selon ce que vous me dites et selon vos besoins, peut-être serez-vous plus à l’aise avec la fonction “Consolider” juste ici http://lecfomasque.com/2012/10/17/excel-consolidez-vos-donnees-1-de-3/

      En espérant avoir répondu à votre question.

      Au plaisir,

      Sophie

  7. Ping : EXCEL: CONSOLIDEZ VOS DONNÉES (3 de 3) | Mon Cher Watson

  8. Merci beaucoup Sophie car ce billet m’a fait gagner considérablement de temps et amélioré mon tableau de contrôle (du calcul annuel des charges de tous les bulletins de paie de mes clients) et m’a enrichi intellectuellement. Merci encore, Très Bonne Année à Vous et à bientôt. Daniel

    1. Sophie Marchand

      Bonjour,

      Effectivement. Si vous utilisez une version en français d’Excel, vous devez insérer la formule en français.

      Merci.

      Sophie

Laisser un commentaire

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

Scroll to Top