Fonction Excel: Index/Equiv (Index/Match)

Publié le 28 avril 2011
par Sophie Marchand M.Sc., CPA, CGA, MVP
Fonction INDEX

Fonction Excel: Index/Equiv (Index/Match)

Êtes-vous des utilisateurs des fonctions Recherchev (Vlookup) et Rechercheh (Hlookup)?

 

Vous avez répondu non…

Si vous travaillez avec des bases de données Excel et que vous n’utilisez pas encore les fonctions Rechechev (Vlookup) et Rechercheh (Hlookup), il est grand temps d’apprendre à le faire puisque la technique est simple à utiliser et les résultats très efficaces. Pour voir des exemples de l’utilisation des fonctions Recherchev et rechercheh, cliquez ici. Une fois que vous aurez maîtrisé ces fonctions, revenez consulter ce billet pour voir comment la fonction Index/Equiv (Index/Match) pourra vous permettre d’obtenir des résultats encore plus intéressants!

 

Vous avez répondu oui…

Saviez-vous que vous pouvez à la fois exécuter un Recherchev (Vlookup) et un Rechercheh (Hlookup) dans une base de données? Ceci pourrait être nécessaire dans le cas où vous devez fournir une donnée tirée d’une table à deux dimensions. Vous pourriez alors le faire en utilisant la fonction Index, suivi de la fonction Equiv (ou Match en anglais).

 

Comment interpréter la fonction INDEX?

La fonction INDEX de base a 3 paramètres. On recherche d’abord dans une matrice (en bleu), une donnée qui se trouve à l’intersection d’une ligne (désignée par la première fonction EQUIV imbriquée) et d’une colonne (désignée par la deuxième fonction EQUIV). Par exemple, ci-dessous, on cherche, dans la matrice en bleu, la valeur sur la ligne qui correspond au nombre de pages 55 dans la colonne en mauve et dans la colonne qui correspond au nombre de copies 15,000 dans la ligne en rose. On obtient ainsi 4,895$.

 

Fonction INDEX

 

Le deuxième paramètre sert donc à spécifier un numéro de ligne. Mais pour éviter d’entrer une valeur fixe, qui ferait en sorte de mettre à risque la formule par rapport à l’ajout d’une nouvelle ligne dans le tableau, on utilise la fonction EQUIV. La fonction EQUIV ci-dessous permet de retourner la position du nombre 55 dans la liste spécifiant les nombres de pages, ceci retourne la valeur 11.

 

EQUIV pour numéro de ligne

 

Le troisième paramètre sert à spécifier le numéro de colonne, par le biais de la fonction EQUIV, pour protéger la formule contre un ajout de colonnes. Elle cherche ainsi la position du nombre 15,000 dans la ligne spécifiant les quantités de copies, ceci retourne la valeur 3.

 

EQUIV pour trouver numéro colonne

 

Autrement dit, le premier EQUIV cherche la ligne qui correspond au nombre de pages spécifié par l’usager, par le biais de la fonction EQUIV…

 

Trouver no ligne

 

… et le deuxième EQUIV cherche la colonne qui correspond au nombre de copies spécifié par l’usager, par le biais d’une deuxième fonction EQUIV.

 

Trouver no colonne

 

Au final, ce qu’on se trouve réellement à demander à Excel, c’est de retourner la valeur à la croisée des de la ligne 11 et de la colonne 3, sauf qu’au lieu d’utiliser des valeurs fixes, on imbrique des fonctions EQUIV pour se protéger contre l’ajout éventuel de lignes ou de colonnes dans le tableau.

 

Ligne 11 colonne 3

 

Pourquoi la fonction Index/Equiv (Index/Match) est-elle si utile?

Cette fonction vous permet d’automatiser votre recherche de données dans une table  deux dimensions. Vous économisez ainsi beaucoup de temps et vous évitez de faire des erreurs. Notez que si vous ajoutez des lignes et/ou des colonnes à votre tableau en deux dimensions, la fonction s’ajustera d’elle-même. Vous n’aurez donc pas à faire d’ajustements manuels.

 

La fonction Index/Equiv (Index/Match) en modélisation financière

Cette fonction devient encore plus intéressante lorsqu’elle est rattachée à un modèle excel. Prenons l’exemple d’un modèle excel qui serait utilisé pour produire les prévisions financières d’un magazine. Les gestionnaires du magazine voudront sans doute tester divers scénarios sans avoir à refaire chaque fois une nouvelle analyse. Ils voudront ainsi utiliser un modèle flexible qui leur permettra de jouer avec les paramètres du modèle pour générer les résultats de divers scénarios.

 

Dans ce modèle interactif, il y aura sans doute un paramètre concernant le nombre d’annonces et le nombre d’articles à paraître dans chaque magazine. Ces paramètres permettront au modèle de calculer automatiquement le nombre de pages à imprimer.

 

Parallèlement, des prévisions seront faites concernant la couverture du territoire où seront distribués les magazines. Par exemple, les gestionnaires pourraient baser cette estimation sur la population du territoire desservi ou encore sur le nombre de présentoirs disponibles et /ou de distributeurs associés. Il pourrait également s’agir d’un mixte de tout cela. Ces paramètres influenceront directement le nombre de copies à imprimer et seront donc liés à la cellule sous-jacente dans le modèle excel.

 

En somme, les gestionnaires pourront raffiner leurs hypothèses et tester l’impact de divers scénarios sur leurs résultats sans avoir à reprogrammer chaque fois les coûts d’impression du magazine.
.

 


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 avec la fonction INDEX et d’autres fonctions Excel utiles en modélisation financière et en élaboration de fichiers Excel dignes des meilleures pratiques d’affaires, nous vous recommandons de suivre notre formation Excel – Modélisation financière (niveau 1) .

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Mathieu Poliquin
Écrit il y a 1 semaine
Bonnes pratiques et des méthodes qui me permettront de voir des gains de performance

Super pertinent. Malgré que je suis déjà de l'expérience en modélisation financière dans Excel, j'arrive tout de même à prendre des bonnes pratiques et des méthodes qui me permettront de voir des gains de performance concret et applicabe rapidement. Merci beaucoup

Jean-Philippe Provost
Écrit il y a 2 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.

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

 

3 réflexions sur “Fonction Excel: Index/Equiv (Index/Match)”

  1. Frédéric Pelletier

    Avec cette fonction, est-il obligatoire d’avoir des chiffres dans la colonne et la ligne de référence ou peut-elle utiliser du texte? Lorsque j’essaie par exemple de croiser “Salaire” (en A2) et “Août” (en I6), la résultante ne renvoie pas valeur que je suis supposé atteindre.

    Merci à l’avance!
    Frédéric

    1. Bonjour Frédéric,

      C’est probablement parce qu’il y a un écart d’épellation dans le mot de référence et les mots de la plage de référence… Si vous le souhaitez, vous pouvez me faire parvenir votre fichier Excel (ou un extrait) à marchandsophie@hotmail.com et je pourrai mieux comprendre le problème et surtout, le résoudre!

      Au plaisir

      Sophie

    2. Bonjour Frédéric,

      Il y avait une petite erreur dans vos formules de “Match”, au niveau du troisième élément de la formule, soit le “match type”.

      Lorsque vous mettez le “match type” à zéro, tout fonctionne bien.

      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