Fonction Excel : INDEX/EQUIV (INDEX/MATCH)

Avant de vous familiariser avec la fonction Index/Equiv,  ê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 RECHERCHEV (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$.

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.

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.

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…

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

Au final, ce qu’on se trouve réellement à demander à Excel en utilisant la fonction Index/Equiv (Index/Match), 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.

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.

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.

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.

Exemple : produire les prévisions financières d’un magazine

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
• 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. Aussi, ils n’auront pas à reprogrammer chaque fois les coûts d’impression du magazine.

Voyez la fonction Index/Equiv en action avec ce tutoriel

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 :

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