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 :

 

Mélissa Paquin
Écrit il y a 1 mois
Certains sujets aideront à améliorer les rapports actuels

Formation intéressante. Certains sujets aideront à améliorer les rapports actuels que j'utilise en tant qu'analyste financier.

Roger Martin Nguyen
Écrit il y a 5 mois
Exemples bien expliquées

Exemples bien expliquées et surtout, les exercices sont détaillées avec la démarche à suivre pour arriver au résultat.

Nathalie Tardif
Écrit il y a 5 mois
J'ai été cherché de nouvelles façons de faire qui vont optimiser mon travail.

Excellent! J'ai été cherché de nouvelles façons de faire qui vont optimiser mon travail. Merci beaucoup!

Lucie Dupuis
Écrit il y a 5 mois
La formation en ligne est idéale

Excellente formation qui donne des trucs pour des situations que nous pouvons et/ou avons rencontré dans notre travail. La formation en ligne est idéale car ça nous permet de réécouter/revoir les explications que nous n'avons pas bien saisi. La durée des modules n'est jamais trop longue.


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

 

Laisser un commentaire

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

Scroll to Top