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 dans Excel 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:
- Fonction Excel: Index/Equiv (Index/Match)
- Excel: Vous pensez tout connaître de la fonction INDEX?
- Excel: Effectuer un Index/Match (Index/Equiv) sur plusieurs plages de données
Données de départ pour notre exemple de recherches approximatives dans Excel
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.
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 ne s’agit donc pas ici d’une recherche approximative dans Excel mais bien d’une recherche exacte. On peut alors simplement utiliser la fonction suivante:
=INDEX(Coûts,EQUIV(NbrePages,Pages,0),EQUIV(NbreCopies,Copies,0))
où
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. Nous sommes ici dans une situation de recherches approximatives dans Excel.
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$.
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 à ces fonctions de recherches approximatives dans Excel
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 :
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é.
