Excel: Effectuer un Index/Match (Index/Equiv) sur plusieurs plages de données

Publié le 02 février 2015
par Sophie Marchand M.Sc., CPA, CGA, MVP
Index match/equiv 4 paramètres

Excel: Effectuer un Index/Match (Index/Equiv) sur plusieurs plages de données

Je vous ai déjà parlé sur ce blogue de la façon d’effectuer un Recherchev (Vlookup) sur deux tables ou deux plages de données (ou plus!). Aujourd’hui, nous allons voir comment effectuer un Index/Equiv (Index/Match) sur plusieurs tables ou plages de données. Pour ce faire, nous allons utiliser la fonction Index/Equiv (Index/Match) avec 4 paramètres.

 

Deux types de fonctions Index

Si vous insérez une fonction Index dans votre barre de formules, vous verrez qu’Excel vous proposera 2 types de fonctions Index. Nous avons déjà discuté de la première, soit celle à 3 paramètres, dans l’article: Fonction Excel: Index/Equiv (Index/Match). Aujourd’hui, nous allons plutôt nous pencher sur la deuxième, soit celle à 4 paramètres.

Index/Match 4 paramètresVous noterez que le premier paramètre est passé de “Array” (Plage de données) à “Reference” (Zone de référence). Nous allons donc étudier maintenant la différence entre ces deux fonctions.
 
 

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.

 

Fonction à 3 paramètres

Dans l’exemple ci-bas, j’ai 4 plages de données.

  • Plage 1: Produit 1 – Quantité mensuelle (Qté) selon 3 scénarios possibles
  • Plage 2: Produit 2 – Quantité mensuelle (Qté) selon 3 scénarios possibles
  • Plage 3: Produit 1 – Prix de vente mensuel (PV) selon 3 scénarios possibles
  • Plage 4: Produit 2 – Prix de vente mensuel (PV) selon 3 scénarios possibles

Dans le bas de cette feuille, je souhaite obtenir la Qté et le PV du produit x, au mois y, selon le scénario z, afin d’en connaître les revenus. Notons que les variables x, y, z sont déterminées par l’utilisateur à l’aide de menus déroulants.
 
Index match/equiv 4 paramètres
 
 
Vous noterez qu’en utilisant la première fonction Index, soit celle à 3 paramètres, je ne peux pas inclure le paramètre de produit. Cela signifie donc qu’un usager qui choisira le produit 2 dans la liste, n’obtiendra pas le bon résultat avec cette fonction, à moins d’y insérer d’abord une fonction Si (If), ce qui risque d’alourdir inutilement la formule.

 

Pour trouver le prix de vente avec la fonction Index à 3 paramètres, vous procéderiez de la même façon et vous rencontreriez la même limite.
 
Index match/equiv 4 paramètres
 
 

Fonction Index à 4 paramètres

Le 1er et le 4ième paramètres de la fonction Index à 4 paramètres, permettent de résoudre notre problématique. D’abord, dans notre premier paramètre, nous allons insérer une zone de référence, i.e. toute la zone de données où il est possible d’aller chercher l’information, plutôt qu’insérer une seule plage de données. Dans notre exemple, vous pouvez observer que nous allons donc chercher la plages de données “Qté pour le produit 1” et la plage de données de “Qté pour le produit 2”. Cela constitue notre zone de référence. Au besoin, vous pouvez cliquez sur l’image pour l’agrandir.
 
Index match/equiv 4 paramètres

Pour ce qui est du 4ième paramètre (le 2ième et le 3ième n’ayant pas changé), il faut indiquer à Excel dans laquelle des plages de données de notre zone de référence il doit aller chercher les données. Dans notre exemple, ce dernier paramètre est lié au numéro de produit, qui lui, est déterminé par l’utilisateur. À noter que le 4ième paramètre est un chiffre, qui représente la position de la plage de données dans la zone de référence. Pour déterminer ce chiffre, nous pourrions donc également utiliser une troisième fonction Equiv (Match).

 

Nous procédons ensuite de la même façon pour déterminer le PV.
 
Index match/equiv 4 paramètres
 
 

Comparaisons des deux fonctions Index

On peut donc constater, en jetant un coup d’oeil au résultat ci-bas, que lorsque l’utilisateur choisit un scénario différent et un mois différent, les deux fonctions s’ajustent correctement, mais lorsqu’il choisit en plus, un produit différent, seule la deuxième fonction s’ajuste correctement et va lire dans la bonne plage de données.
 
Index match/equiv 4 paramètres
 
 


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

Pour une introduction aux principes de base et aux meilleures pratiques d’affaires en modélisation financière dans Excel et vous familiariser avec les fonctions et les fonctionnalités qui permettent de faire des prévisions financières, des analyses de sensibilité et des analyses de scénarios, suivez notre formation Excel – Modélisation financière niveau 1.
 

Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Modélisation financière niveau 1 :

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

 

1 réflexion sur “Excel: Effectuer un Index/Match (Index/Equiv) sur plusieurs plages de données”

Laisser un commentaire

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

Scroll to Top