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 dans Excel, jumelée avec 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 dans Excel
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
Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière. |
|---|
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.
Numéro de colonne à imbriquer dans la fonction Indirect
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 en lien avec la fonction Indirect dans Excel
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 modifications 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 :
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é.
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
Bonjour Mathieu,
Merci pour votre commentaire.
Effectivement, une telle fonction existe. J’en ferai l’objet d’un billet à venir.
Au plaisir,
Sophie Marchand
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
Bonjour Marc,
Merci pour votre question. Pourriez-vous préciser ce que vous entendez par “utilisation de la consolidation (soit par position ou par catégorie)”?
Au plaisir,
Sophie
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
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
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
Bonjour Marc,
Vous pouvez bien sûr me faire parvenir le tout à marchandsophie@hotmail.com.
Sinon, je vous offre la possibilité d’écrire un billet sur le sujet, que je pourrais publier sur Mon Cher Watson. Est-ce que cette opportunité vous intéresserait?
Au plaisir,
Sophie
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
Parfait!
C’est envoyé
Merci
Marc
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 ?
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
Ping : EXCEL: CONSOLIDEZ VOS DONNÉES (3 de 3) | Mon Cher Watson
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
Bonjour,
Cà marche pas votre truc.
et on fait comment pour vous envoyer la copie d’écran ?
fronck
çà fonctionne, mais en français:
=INDIRECT(ADRESSE(4;COLONNE();1;1;$B4))
Bonjour,
Effectivement. Si vous utilisez une version en français d’Excel, vous devez insérer la formule en français.
Merci.
Sophie