Oubliez RECHERCHEV() ! La fonction INDEX-EQUIV pour réaliser des recherches complexes sur Excel

Aujourd’hui je vous propose de découvrir comment effectuer des recherches complexes dans Excel en utilisant les fonctions dédiées proposées par le tableur. Nous commencerons donc par découvrir les deux fonctions historiques dédiées à cet usage, c’est-à-dire les fonctions RECHERCHEV() et RECHERCHEH(). Nous verrons alors très rapidement que ces fonctions sont très limitées, et qu’il faut mieux leur préférer la fonction INDEX-EQUIV(), bien plus souple et puissante !

 

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

Tutoriel Vidéo

 

1. Présentation

Dans un précédent tutoriel, nous avons déjà eu l'occasion de découvrir comment effectuer des recherches complexes au sein d'une base de données en utilisant les fonctions dédiées : RECHERCHEV() et RECHERCHEH().

Nous avions également vu dans ce tutoriel qu'il existe une autre possibilité qui permet d’effectuer des recherches, qui est encore plus puissante et qui consiste à utiliser un mélange de deux fonctions : les fonctions INDEX() et EQUIV().

Dans ce nouveau tutoriel nous allons revenir sur ces notions en redécouvrant rapidement le fonctionnement des fonction RECHERCHEV et RECHERCHEH, puis en nous attardant davantage sur la puissance de ce que nous pourrions appeler la fonction INDEX-EQUIV.

 

2. Effectuer une recherche horizontale : la fonction RECHERCHEV()

Comme son nom l’indique, la fonction RECHERCHEV permet d’effectuer une recherche dite verticale au sein d’une base de données.

Comprenez par-là que partant d’un tableau, nous allons chercher à effectuer une recherche sur la première colonne afin d’en extraire la donnée qui correspond et qui est donc située sur la même ligne, mais sur une colonne différente.

Pour bien comprendre cette notion, partons de l’exemple suivant :

Excel formation - la fonction index-equiv - 01

Nous avons ici le tableau de détermination de l’EBE mensuel d’une entreprise, et souhaitons dans un premier temps connaitre le montant de cet EBE qui correspond au mois de mars.

Pour cela nous allons donc pouvoir utiliser la fonction RECHERCHEV :

 =RECHERCHEV(K10;A9:G20;7) 

Nous commençons par saisir la valeur qui servira de clé de recherche (le mois de mars, situé en cellule K10).

Ensuite, nous venons saisir les coordonnées de l’ensemble des cellules du tableau d’analyse (les cellules A9 à G20).

Et pour finir, il ne reste plus qu’à renseigner le numéro de la colonne dans laquelle se trouve l’information à extraire de la base (le calcul de l’EBE se trouve dans la septième colonne du tableau).

 

3. Effectuer une recherche horizontale : la fonction RECHERCHEH()

L’équivalent de la fonction que nous venons de découvrir et qui permet d’effectuer des recherches horizontales est la fonction RECHERCHEH().

Celle-ci fonctionne exactement de la même manière.

Par contre, nous ne pouvons pas utiliser cette fonction avec notre tableau, étant donné que les en-têtes de colonnes ne sont pas classés suivant un ordre alphabétique…

Et nous en arrivons donc aux limites des fonctions de recherches traditionnelles que nous allons maintenant détailler.

 

4. Les limites de ces fonctions

En effet, comme nous venons de le voir les fonctions RECHERCHEV() et RECHERCHEH() connaissent un certain de limitations qu’il est important de garder en tête :

  • La première limite, et surement la plus contraignante, c’est que la colonne sur laquelle nous allons mettre en place la recherche doit OBLIGATOIREMENT être classée par ordre alphabétique ! C’est la raison pour laquelle nous ne pouvons pas simplement saisir les noms de mois dans la colonne mois et que nous avons dû contourner le problème en saisissant tout d’abord le numéro du mois,
  • La seconde limite, également très contraignante, c’est que la colonne de recherche doit impérativement être la première colonne de la plage des cellules utilisées dans le second argument de la fonction ! Impossible donc d’extraire une information située sur la gauche de celle-ci
  • Et enfin la troisième limite c’est que le numéro de la colonne sur laquelle nous souhaitons extraire l’information doit obligatoirement être renseigné en tant que nombre. Même s’il est possible de la récupérer dynamiquement, cette solution est toujours plus complexe à mettre en place que d’utiliser un index logique, tel que le nom de la colonne. De plus, étant donné qu’il ne s’agit que d’une valeur numérique, et non pas d’une référence à une plage de cellules, le fait de modifier la structure du tableau en ajoutant ou supprimant des colonnes va fausser le résultat retourné par la fonction…

Heureusement, les fonctions que nous venons de (re)voir ici connaissent des alternatives très intéressantes et tout aussi simples à mettre en place.

La première d’entre elles est la toute jeune fonction RECHERCHEX() introduite avec Excel 365, et qui n’est malheureusement pas accessible au plus grand nombre.

La seconde alternative consiste à utiliser un mélange de deux fonctions : INDEX() et EQUIV(), et c’est que nous allons pouvoir découvrir maintenant.

 

5. La fonction INDEX-EQUIV

Comme nous venons de le voir, la fonction INDEX-EQUIV() est en fait une combinaison de deux fonctions.

 

   5.1. La fonction INDEX()

 

Tout d’abord la fonction INDEX() qui permet d’afficher la valeur de la cellule située à une position données d’une plage de cellules.

Dans sa version classique (nous découvrirons la version évoluée plus tard dans la vidéo), la fonction INDEX() attend deux arguments :

= INDEX(matrice;no_ligne) 

Où :

  • Matrice : correspond à la plage des cellules dans laquelle se trouve l’information à extraire,
  • No_ligne : correspond à la place dans laquelle se trouve l’information à extraire au sein de matrice. Dans le cas présent, la valeur à donner à l’argument no_ligne est déterminée par la formule EQUIV(),

 

   5.2. La fonction EQUIV()

 

Ensuite, la formule EQUIV() permet de connaître la position de la cellule que nous recherchons au sein de la plage des cellules correspondante.

 =EQUIV(valeur_cherchée;tableau_recherche;[type])  

Où :

  • Valeur_cherchée est la valeur de recherche,
  • Tableau_recherche est la plage des cellules dans laquelle nous souhaitons effectuer la recherche.
  • Le paramètre optionnel [type] permet de spécifier si l’on souhaite effectuer une recherche exacte (dans ce cas type vaut 0), ou alors la valeur la plus élevée qui est inférieure ou égale à celle de valeur_cherchée (1), ou enfin la plus petite valeur qui est supérieure ou égale à celle à valeur_cherchée (-1).

 

   5.3. La combinaison des fonctions INDEX() et EQUIV()

 

Dans cette combinaison de fonctions, EQUIV() est à utiliser en tant qu’argument no_ligne de la fonction principale INDEX() et retournera alors la position de l’élément à récupérer au sein de la plage des cellules matrice.

 =INDEX(plage_retour;EQUIV(valeur_cherchée;plage_recherche;[type]))  

Où :

  • plage_retour: il s’agit de la plage des cellules (lignes ou colonne) dans laquelle se trouve la valeur à extraire,
  • valeur_cherchée : valeur à retrouver dans le plage_recherche,
  • plage_recherche : plage des cellules dans laquelle nous souhaitons effectuer la recherche
  • [type] : défini le type de recherche, généralement, nous souhaiterons effectuer une recherche exacte, et donc nous lui affecterons la valeur 0

Reprenons l’exemple vu un peu plus tôt, dans lequel nous souhaitons connaître le montant de l’EBE du mois de mars :

 =INDEX(G9:G20;EQUIV(K10;A9:A20;0)) 

Excel formation - la fonction index-equiv - 02

Maintenant que nous utilisons cette fonction, nous pouvons sans problème renommer les cellules dans lesquelles se trouvent les mois de l’année :

Excel formation - la fonction index-equiv - 03

Le résultat retourné sera alors toujours correct, même si les cellules ne suivent pas l’ordre alphabétique, ce que la fonction RECHERCHEV() ne permettrait pas.

L’autre aspect très intéressant de la formule INDEX-EQUIV() est que l’ordre des colonnes n’a aucune importance. En effet, dans la formule INDEX-EQUIV(), il n’y a pas de connexion directe entre la colonne de recherche et celle qui contient le résultat, aussi leur ordre n’a aucune importance.

Pour illustrer cette notion, nous allons construire la fonction inverse à celle de l’exemple précédent : c’est-à-dire que nous allons chercher à savoir pour quel mois de l’année l’entreprise a dégagée un EBE correspondant au montant obtenu, c’est -à-dire 37757€ :

 =INDEX(A9:A20;EQUIV(K11;G9:G20;)) 

Excel formation - la fonction index-equiv - 04

Une autre force de la formule INDEX-EQUIV(), c’est qu’en plus des recherches verticales classiques, nous pouvons également effectuer une recherche horizontale avec exactement la même fonction :

 =INDEX(B11:G11;EQUIV(K9;B8:G8;)) 

Excel formation - la fonction index-equiv - 05

Nous pouvons également effectuer des recherches 3D, c’est-à-dire en utilisant un paramètre horizontal et un autre vertical !

Pour cela, pour allons simplement utiliser l’argument facultatif no_col de la fonction INDEX.

Nous allons tout d’abord devoir saisir le numéro de ligne en utilisant la fonction EQUIV, puis dans un second temps le numéro de la ligne de la même manière :

Excel formation - la fonction index-equiv - 06

 =INDEX(B9:G20;EQUIV(K10;A9:A20;);EQUIV(K9;B8:G8;))  

Excel formation - la fonction index-equiv - 07

Bien entendu, à ce moment-là la plage des cellules dans laquelle se trouve le résultat à extraire doit être constituée d’un nombre de lignes et de colonnes équivalent aux plages appelées par la suite.

Et plus de cela, nous pouvons modifier la structure du tableau en ajoutant ou supprimant des lignes ou colonnes, le résultat sera toujours correct !

En conclusion, en plus d’être très puissance, la formule INDEX-EQUIV, telle que nous venons de la découvrir permet également de comprendre que parfois nous pouvons détourner Excel de l’usage prévu à l’origine par le développeur pour arriver à nos fins !

 



Articles qui pourraient vous intéresser

Comment effectuer un test sur Excel : la fonction SI() (ainsi que ET() et OU())
Comment convertir des unités de mesure sur Excel ? La fonction CONVERT()
Comment modifier le résultat d’une formule renvoyant une erreur : la fonction SIERREUR() d’Excel ?
Comment obtenir des informations sur le classeur (nom et répertoire d’enregistrement) et sur les cellules (nom de l’onglet,…) sur Excel ?
Comment effectuer un test logique sur Excel : découvrir les fonctions ESTERREUR, ESTERR, ESTNA, ESTREF, ESTNUM, ESTTEXTE, ESTNONTEXTE,…
Comment classer simplement des données avec la fonction RANG (avec ou sans ex æquo) Excel ?
Oubliez RECHERCHEV() ! La fonction INDEX-EQUIV pour réaliser des recherches complexes sur Excel
Comment savoir si une cellule est un nombre sur Excel ?
Découvrir la fonction AGREGAT d’Excel pour effectuer des calculs en tenant compte des filtres en place sur Excel
[VIDEO] Assembler des cellules avec la fonction CONCATENER()
Comment calculer une moyenne arithmétique ou pondérée sur Excel
Comment modifier ou supprimer des caractères dans un texte avec la fonction SUBSTITUE d'Excel

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.