Excel: La fonction INDEX pour des recherches approximatives

Publié le 29 mai 2019
par Sophie Marchand M.Sc., CPA, CGA, MVP
Index avec Equiv approximatif

Excel: La fonction INDEX pour des recherches approximatives

Dans la formation Excel – Mise à niveau, j’enseigne l’usage de la fonction INDEX et de la fonction EQUIV. Dans la formation Excel – Modélisation financière (niveau 1), je pousse un peu plus loin les cas d’usage. Toutefois, il y a un cas que je ne couvre pas et la question m’est parfois posée en classe. J’ai donc décidé d’y répondre aujourd’hui, par le biais d’un article de blogue. Cette question concerne les recherches approximatives et elle est expliquée en détail ci-dessous.

 

Si vous n’êtes pas habitué à utiliser les fonctions INDEX et EQUIV, vous pouvez consulter les articles suivants:

 

Données de départ

Pour illustrer les différents scénarios, nous utiliserons les données ci-dessous. À noter que nous avons nommé plusieurs champs, qui seront définis plus loin. En somme, la matrice ci-dessous représente les coûts d’impression d’un magazine, en fonction de son nombre de pages et du nombre de copies à imprimer.
 
Matrice coûts d'impression
 
 

Scénario 1 : Nombre de pages et nombre de copies exacts

Dans ce scénario, l’usager fournit un nombre de pages et un nombre de copies qui existent dans les entêtes de lignes et les entêtes de colonnes. Il s’agit donc d’une recherche exacte. On peut alors simplement utiliser la fonction suivante:

=INDEX(Coûts,EQUIV(NbrePages,Pages,0),EQUIV(NbreCopies,Copies,0))

Coûts : Matrice de coûts d’impression (sans les entêtes de lignes et de colonnes)

NbrePages : Nombre de pages sélectionné par l’usager

Pages: Entêtes de lignes de la matrice (possibilités de nombres de pages)

NbreCopies: Nombre de copies sélectionné par l’usager

Copies : Entêtes de colonnes de la matrice (possibilités de nombre de copies)

 

Celle-ci retournera un coût d’impression de 4,895$.
 
 

Vous aimeriez être un Ninja Excel et devenir la référence ultime de votre bureau, ou tout le moins ne plus avoir l’impression de stagner ? Développez des solutions innovatrices en explorant des fonctions et fonctionnalités souvent méconnues mais fort utiles avec nos formations pour devenir Ninja d’Excel.

 

Scénario 2 : Nombre de pages et nombre de copies approximatifs

Dans ce scénario, l’usager fournit un nombre de pages et un nombre de copies dont la valeur exacte n’est pas présentée dans les entêtes de lignes et de colonnes. On doit donc utiliser la recherche approximative. Par exemple, ci-dessous, l’usager cherche le coût pour un magazine de 57 pages et de 18,000 copies. Il faudra donc d’abord se demander si le fournisseur demandera le taux de la borne inférieure, de la borne supérieure.

 

Scénario 1 A – Référence aux bornes inférieures

Ici, on utilise le chiffre 1 au niveau du dernier paramètre de la fonction EQUIV, autant au niveau du numéro de ligne que du numéro de colonnes. Ainsi, Excel retournera la valeur la plus prochaine mais inférieure à celle spécifiée par l’usager. À noter que les données doivent être présentées en ordre croissant. Dans ce cas-ci, Excel cherchera donc 57 pages, ne trouvant pas 57, il s’arrêtera sur la valeur la plus proche mais inférieure, 55. De même, il cherchera 18,000 copies, ne trouvant pas 18,000, il s’arrêtera sur la valeur la plus proche mais inférieure, 15,000. On reviendra donc au coût de 4,875$.
 
Index avec Equiv approximatif
 
 

Scénario 2 B – Références aux bornes supérieures

En supposant que le fournisseur soit plutôt d’avis qu’à 57 pages et 18,000 copies, il devrait plutôt charger l’équivalent d’un 60 pages à 20,000 copies, on ajustera la formule en conséquence comme suit:

=INDEX(Coûts,EQUIV(NbrePages,Pages,1)+1,EQUIV(NbreCopies,Copies,1)+1)

Et on obtiendra donc 6,192$.

 

Problématique liée au scénario 2B

Dans le scénario 2A, si l’usager choisit finalement 55 pages et 15,000 copies, il obtiendra un coût de 4,895$. C’est parfait. Par contre, dans le scénario 2B, si l’usager choisit finalement 55 pages et 15,000 copies, il obtiendra un coût de 6,192$, ce qui n’est pas ce que l’on souhaite. Étant donné qu’il est fort à parier que le fournisseur choisira de se faire rémunérer selon les bornes supérieures, et non selon les bornes inférieures, il faudra adapter cette formule convenablement pour qu’elle puisse retourner le coût souhaité, que l’usager choisisse un nombre de pages et/ou un nombre de copies exact ou approximatif.

 

Modifications proposées

Nous pourrions donc imbriquer deux fonctions SI dans notre fonction INDEX, comme suit:

=INDEX(Coûts,EQUIV(NbrePages,Pages,1)+SI(ESTNUM(EQUIV(NbrePages,Pages,0)),0,1),EQUIV(NbreCopies,Copies,1)+SI(ESTNUM(EQUIV(NbreCopies,Copies,0)),0,1))

Ce faisant, si le nombre de pages et/ou le nombre de copie est exact, on n’ajoutera pas de 1 à la suite du EQUIV mais dans les autres cas, on ajoutera un 1 pour aller chercher le prochain nombre de pages et/ou nombre de copies exact dans la matrice.

 


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 apprendre des façons créatives d’utiliser Excel pour créer des solutions surprenantes et régler des problématiques qui semblent parfois sans solution, suivez la formation Excel – Trucs de pro.
 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

 

Le CFO masqué - Commentaires formation - Excel – Trucs de pro

CFO-Masque_Formations-en-ligne_FBLa 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é.

Laisser un commentaire

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

Scroll to Top