Utiliser la fonction EQUIV dans Excel

Vous souhaitez utiliser la fonction EQUIV dans Excel ? Cet article vous présentera son utilisation par le biais d’exemples. Ainsi, vous saurez comment elle fonctionne.

La vidéo qui se trouve ci-dessous montre les trois exemples qui seront présentés par la suite. Dans celle-ci, et dans la suite de l’article, vous trouverez les trois modes de fonctionnement de cette fonction. Le premier est le mode exact, le second le mode inférieur et enfin le mode supérieur.

Pourquoi utiliser la fonction EQUIV dans Excel ? En effet, si vous avez déjà consulté la vidéo ou l’article sur la RECHERCHEV, vous verrez que les deux sont similaires dans l‘idée. L’objectif est de faire ressortir une donnée. Il y a cependant une différence majeure. EQUIV n’affiche pas directement le résultat, mais elle renvoie la position de celui-ci dans la liste.

Avec la fonction EQUIV, il est possible de faire des recherches dans une liste. La recherche peut-être lancée à partir d’une donnée qui sera indiquée par l’utilisateur. Vous pourrez donc créer une interaction entre vous et le logiciel. Cette fonction peut être mise en œuvre dans de nombreuses situations. Dans cet article, nous verrons deux situations qui permettront de mettre en valeur les trois modes de la fonction.

Le premier mode que nous allons étudier permettra de lancer une recherche avec un identifiant. Les deux modes suivants effectueront une recherche par tranche. Rassurez-vous, nous reviendrons plus en détail sur ces éléments dans les prochaines étapes. A la fin de l’article, vous devriez avoir un bon aperçu des possibilités d’utilisations de la fonction.

1. Comment utiliser la fonction EQUIV dans Excel ?

Il s’agit probablement du mode le plus utilisé pour ma part. En effet, le mode Exact de la fonction EQUIV  est très utile pour lancer des recherches selon un identifiant dans un tableau déterminé. Le type d’identifiant peut-être varié, les plus classiques étant ceux de clients, des noms, des numéros de produit… Tout dépend la liste sur laquelle vous allez appliquer la recherche.

Présentons le cas que nous allons étudier. Il s’agira d’un tableau constitué d’une première colonne avec un identifiant client (« Client 1 », « Client 2 »…). C’est sur cette colonne que sera appliquée la fonction EQUIV. On utilisera cette dernière comme liste (la colonne clients). Si notre recherche aboutie, nous pourrons afficher la position du client dans cette fameuse liste.

Le reste du tableau contient les chiffres d’affaires mensuelles pour chaque client durant les six premiers mois de l’année. Enfin, la dernière colonne correspond au montant cumulé des six premiers mois de chiffre d’affaires. Mais nous n’aurons pas besoin de ces dernières.

TELECHARGER TABLEAU FONCTION EQUIV

Maintenant que notre tableau est présenté, nous pouvons utiliser la fonction EQUIV pour afficher la position d’un client dans une cellule créée spécialement à cet effet.  Nous placerons cette dernière sous le tableau, nous allons créer un petit espace dans lequel on insérera une cellule pour indiquer l’identifiant du client recherché. C’est très utile pour faire ressortir une donnée lorsqu’on a de nombreux identifiants dans notre liste. On va également créer une cellule pour l’affichage du résultat, c’est dans celle-ci qu’on va utiliser la fonction EQUIV.

1.1. Mode Exact de la fonction EQUIV

La démonstration ci-dessous va donc faire ressortir la position dans notre première colonne de notre client. Il s’agira du « client 2 ». Notre formule va indiquer la position que l’on pourrait très bien réutiliser dans une autre formule. Pour cela : présentons les paramètres de la fonction EQUIV.

=EQUIV(valeur_cherchée;tableau;type)

Nous avons trois paramètres à indiquer dans la fonction. Le premier paramètre qu’il faut indiquer est l’identifiant recherché ou la cellule dans laquelle se trouve celui-ci.

Le second, c’est la liste à sélectionner pour effectuer la recherche de l’identifiant. Pour le troisième et dernier paramètre, c’est le type de mode utilisé pour la fonction. Il sera représenté par trois possibilités qui sont 0 pour le mode exact, -1 pour le mode supérieur et 1 pour le mode inférieur. Nous verrons les deux derniers par la suite.

En croisant les informations précédentes et notre tableau avec la fonction EQUIV, cela donne les paramètres ci-dessous :

=EQUIV(B11;A2:A6;0)

Dans notre cellule, cela affiche bien la position de notre client 2 dans la liste. Si on souhaite changer la donnée « Client 2 » par « Client 3 », on constate que c’est désormais la position de celui-ci qui apparaît.

Si par erreur vous renseignez un identifiant d’un client dans la cellule qui n’a pas de correspondance dans la liste, vous verrez alors la valeur « N/A »  apparaître en résultat. Cela aura pour signification que vous devez modifier l’identifiant indiqué pour trouver une correspondance.

2. Les modes supérieur et inférieur avec EQUIV

Nous pouvons maintenant passer aux autres modes de la fonction EQUIV. Il s’agit des modes supérieur et inférieur. On utilise ces derniers lorsqu’on recherche une valeur dans une liste qui ne possède pas de correspondance, mais que cette dernière peut se trouver dans une tranche. Si nous utilisons le mode exact, nous obtiendrons un message « #N/A ».

Les modes supérieur et inférieur vont nous permettre d’avoir une approche différente de notre liste. Pour donner un exemple concret de ce mode, nous allons utiliser un tableau de remise de prix. Ce dernier possède deux colonnes, la première donne des tranches de chiffre d’affaires. La seconde propose des pourcentages qui seront appliqués en fonction de la tranche de chiffre d’affaires atteinte.

TELECHARGER TABLEAU FONCTION EQUIV

Nous allons utiliser la fonction EQUIV avec les modes supérieur et inférieur pour faire ressortir la position de la tranche dans laquelle se trouve le chiffre d’affaires. Pour cela, on va créer une cellule sous le tableau dans laquelle on va indiquer le chiffre d’affaires à tester.  La fonction EQUIV sera appliquée dans une autre cellule.

2.1. Utiliser le mode inférieur de la fonction EQUIV

Comment fonctionnent les modes inférieur et supérieur ? Pour le mode inférieur, il faut utiliser le type « 1 ». On l’utilise lorsque la liste des tranches est dans un ordre croissant. Le mode va récupérer la position de la tranche concernant la valeur immédiatement inférieure.

Par exemple, si on indique un chiffre d’affaires de 35 000 €, on constate que la valeur n’existe pas dans la liste. La valeur qui est immédiatement inférieure à celle-ci dans le tableau c’est 30 000 €. La fonction va donc afficher la position correspondant à 30 000 € dans la liste. Ici, ce sera donc 2.

Dans la cellule affichant le résultat la fonction EQUIV, on trouvera les paramètres suivants :

=EQUIV(B27;A20:A24;1)

Par rapport au mode EXACT, la différence se trouve donc au niveau du dernier paramètre. Dans celui-ci, nous allons indiquer «1», ce qui va nous permettre de faire fonctionner le mode inférieur.

2.2. Utiliser le mode supérieur de la fonction EQUIV

Voyons maintenant l’autre possibilité du mode approximatif, c’est le supérieur. Il fonctionne à l’inverse du mode précédent. Autrement dit, le mode inférieur doit être utilisé lorsque les tranches sont croissantes, alors que le mode supérieur est utilisé lorsque celles-ci sont décroissantes. On va donc utiliser une formule légèrement différente.

=EQUIV(B27;D20:D24;-1)

Comme on peut le constater, c’est le dernier paramètre qui est modifié. Ce dernier est « -1 » pour utiliser le mode approximatif supérieur. Comme vous pouvez le voir sur la capture ci-dessous, la fonction EQUIV va donc nous renvoyer la position de notre chiffre d’affaires parmi les tranches. On adaptera seulement le dernier paramètre selon que nos tranches soient croissantes ou décroissantes.

Dans l’exemple, nous gardons le chiffre d’affaires de 35 000 €. Celui-ci se trouve entre nos tranches de 50 000 € et 30 000 €. Avec le mode supérieur, c’est donc 50 000 € qui va être utilisé. Celui-ci se trouve en position « 3 » dans le tableau. C’est donc la position affichée dans la cellule indiquant le résultat de la fonction.

3. Utiliser la fonction EQUIV + INDEX

Comme vous avez pu le remarquer dans cette présentation, la fonction EQUIV en elle-même n’a pas de grand intérêt. Elle devient intéressante lorsqu’on peut la coupler avec une autre fonction permettant de récupérer une donnée associée. Dans ce cas on peut utiliser une fonction RECHERCHEV, ou l’association de la fonction EQUIV avec la fonction INDEX.

L’avantage de la fonction EQUIV, c’est qu’elle traite les tranches en mode décroissante. Ce que la fonction RECHERCHEV ne fait pas. Vous pouvez retrouver un article sur l’utilisation de la fonction EQUIV avec la fonction INDEX.