Index-Equiv : Astuces de pro sur Excel

Dans ce tutoriel, je vais vous montrer comment utiliser une astuce incroyable pour extraire des données précises dans Excel sans tracas. Pour cela, nous utiliser toute la puissance du combo INDEX+EQUIV.

Nous verrons ainsi comment ces fonctions permettent de réaliser simplement des recherches multicritères ou encore des recherches 3D (en fonction d’une ligne et d’une colonne).

1. Introduction

Pour illustrer ce tutoriel, imaginons que nous gérions un petit magasin de livres et que nous souhaitions analyser les ventes de différents genres de livres au cours de différents mois. Voici un tableau de données que nous allons utiliser

Nous voulons trouver les ventes d’un livre spécifique en fonction du genre et du mois.

2. Introduction aux fonctions INDEX et EQUIV

Tout d’abord, la fonction “INDEX” permet de renvoyer la valeur d’une cellule spécifique dans une plage de cellules. Cela est particulièrement utile lorsque nous travaillons avec de grandes quantités de données et que nous avons besoin d’extraire des informations précises.

La syntaxe de la fonction “INDEX” est la suivante :

 =INDEX(plage_de_cellules; numéro_de_ligne;  [numéro_de_colonne]) 

Où :

• “plage_de_cellules” : Correspond à la plage de cellules dans laquelle nous souhaitons rechercher une valeur.
• “numéro_de_ligne” : Correspond au numéro de la ligne de la valeur que nous souhaitons renvoyer.
• “numéro_de_colonne” : Cet argument facultatif correspond Le numéro de la colonne de la valeur que nous souhaitons renvoyer.

Ainsi, si nous souhaitons obtenir la valeur des ventes pour la 3e ligne de la colonne des ventes, nous utiliserons la formule suivante :

 =INDEX(D7:D16;3) 

Cette formule renvoie la valeur “150”, qui correspond aux ventes de “Le Seigneur des Anneaux” en mars.

Ensuite, la fonction “EQUIV” est utilisée pour rechercher une valeur spécifique dans une plage de cellules et renvoyer la position relative de cette valeur.

Cette fonction est très utile lorsqu’il s’agit de trouver l’emplacement d’une donnée dans une liste.

La syntaxe de la fonction “EQUIV” est la suivante :

 =EQUIV(valeur_cherchée; plage_de_recherche;  [type_de_correspondance]) 

Où :

• “valeur_cherchée” : La valeur que nous souhaitons trouver.
• “plage_de_recherche” : La plage de cellules dans laquelle nous souhaitons effectuer la recherche.
• “type_de_correspondance” : (facultatif) Le type de correspondance (0 pour une correspondance exacte, 1 pour une correspondance approximative).

Pour illustrer cela, supposons que nous voulons maintenant trouver la position de “Mars” dans la colonne des mois :

 =EQUIV(“Mars”;C7:C16;0) 

Cette formule renvoie la valeur “3”, car “Mars” est la 3e valeur dans la plage C7:C16.

3. Utilisation conjointe des fonctions INDEX et EQUIV

Maintenant que nous connaissons et savons utiliser les fonctions “INDEX” et “EQUIV”, nous allons les combiner pour extraire des valeurs spécifiques.

Par exemple, nous souhaitons trouver les ventes de “Le Seigneur des Anneaux” en mars. Nous devons d’abord trouver la position de “Le Seigneur des Anneaux” dans la colonne des titres, puis utiliser cette position pour obtenir la valeur correspondante dans la colonne des ventes.

La formule sera ainsi :

 =INDEX(D7:D16;EQUIV((“Le Seigneur des  Anneaux”;A7:A16;0)) 

Explications :

• La fonction “EQUIV(“Le Seigneur des Anneaux”; B2:B11;0)” recherche “Le Seigneur des Anneaux” dans la colonne B et renvoie sa position relative, qui est “3”.
• La fonction “INDEX(D2:D11; 3)” utilise cette position pour renvoyer la valeur correspondante dans la colonne des ventes, soit “150”.

Nous pouvons également affiner notre recherche en utilisant plusieurs critères.

Par exemple, nous souhaitons trouver les ventes d’un livre en fonction du genre et du mois.

Pour ce faire, nous allons utiliser une formule matricielle combinant “INDEX” et “EQUIV” avec des critères multiples.

Prenons un exemple pratique. Supposons que nous voulons trouver les ventes de livres de “Fiction” pour le mois d'”Avril”. La formule sera :

 =INDEX(D7:D16;EQUIV(1;(B7:B16=”Fiction”)*(C7:C16=”Avril”);0))  

Explications :

• “( B7:B16=”Fiction”)” crée un tableau de valeurs logiques (VRAI ou FAUX) indiquant si chaque cellule de la colonne B correspond au critère “Fiction”.
• “(C7:C16=”Avril”)” crée un tableau de valeurs logiques indiquant si chaque cellule de la colonne C correspond au critère “Avril”.
• “( B7:B16=”Fiction”)*(C7:C16=”Avril”)” multiplie ces deux tableaux pour obtenir un tableau de 1 (VRAI) et 0 (FAUX) où les deux conditions sont remplies.
• “EQUIV(1; (B7:B16=”Fiction”)*(C7:C16=”Avril”);0)” trouve la position du premier 1 dans ce tableau, correspondant à la ligne où les deux conditions sont remplies.

4. Recherche 3D avec INDEX-EQUIV

Nous allons maintenant explorer une astuce avancée : la recherche 3D avec les fonctions “INDEX” et “EQUIV”.

Cela nous permet de rechercher des valeurs dans un tableau à l’aide de plusieurs critères.

Pour cela, nous saisissons la formule suivante dans la cellule G14 :

 =INDEX(B7:D16;EQUIV(G12;A7:A16;0);EQUIV(G13;B6:D6;0))  

Contrairement à ce que nous avons vu juste avant, le premier argument de la fonction INDEX ne correspond pas à la référence d’une simple colonne, mais d’une plage de plusieurs lignes et colonnes dans lesquelles se trouvent les données à récupérer.

Puis, pour déterminer la ligne et la colonne dans laquelle se trouve l’information à récupérer dans cette plage de cellules.

La première fonction “EQUIV” est utilisée chercher la position de la valeur dans la cellule “G12” (qui contient “Harry Potter”) dans la plage “A7:A16” (la colonne des titres des livres).

Cela renvoie la position relative de “Harry Potter” dans la colonne des titres, qui est 5.

La seconde fonction “EQUIV” est utilisée pour trouver la position de la valeur dans la cellule “G13” (qui contient “Avril”) dans la plage “B6:D6” (la ligne des mois).

Dans notre cas, “valeur_cherchée” est “G13”, “plage_de_recherche” est “B6:D6”, et “type_de_correspondance” est “0” (pour une correspondance exacte). Cela renvoie la position relative d'”Avril” dans la ligne des mois, qui est 4.

Cela signifie que nous cherchons la valeur dans la 5ème ligne et la 4ème colonne de la plage “D7:D16”. La valeur renvoyée est “300”, qui correspond aux ventes de “Harry Potter” en avril.

5. Utilisation avancée d’INDEX et EQUIV avec des plages dynamiques

Une autre astuce de pro consiste à utiliser des plages dynamiques avec les fonctions “INDEX” et “EQUIV”. Cela permet de rendre vos formules plus flexibles et adaptables aux changements dans vos données, comme l’ajout ou la suppression de lignes.

Pour illustrer cette technique, nous allons créer des plages nommées dynamiques qui s’ajustent automatiquement lorsque de nouvelles données sont ajoutées.

Pour créer une plage nommée pour les mois, rendez-vous dans l’onglet « Formules », puis « Gestionnaire de noms ».

Sur la boîte de dialogue qui s’affiche à l’écran, nous cliquons sur “Nouveau” et nous nommons la plage “Plage_Mois”.

Ensuite, dans le champ “Fait référence à”, nous saisissons la formule suivante :

 =DECALER($C$7;0;0;NBVAL($C:$C)-1;1) 

Cette formule utilise “DECALER” pour créer une plage dynamique en fonction du nombre de valeurs non vides dans la colonne C.

Ensuite, nous répétons les mêmes étapes pour créer la plage “Plage_Genres” en utilisant la formule suivante :

 =DECALER($B$7;0;0;NBVAL($B:$B)-1;1) 

Et la plage “Plage_Ventes” en utilisant la formule suivante :

 =DECALER($D$7;0;0;NBVAL($D:$D)-1;1) 

Ensuite, pour obtenir les ventes de livres de “Fiction” en “Avril” dans la cellule G9, nous pouvons utiliser la formule :

 =INDEX(plage_ventes;EQUIV(1;(plage_genres=”Fiction”)*(plage_mois=”Avril”);0))  

Maintenant, nous pouvons ajouter une nouvelle ligne à la suite de la table

Celle-ci sera maintenant incluse automatiquement dans la formule :

Ainsi, la formule suivante nous renverra 400 :

 =INDEX(plage_ventes;EQUIV(1;(plage_genres=”Fiction”)*(plage_mois=”Novembre”);0))  

Je vous souhaite de passer une bonne journée !

Et à très bientôt pour de nouvelles astuces !

cfr Alexandre, www.excelformation.fr

About The Author