Les fonctions Index et Equiv sous excel

I. Introduction

INDEX et EQUIV sont deux fonctions de la catégorie "Recherche".

INDEX renvoie une valeur ou la référence de la cellule au croisement d'une ligne et d'une colonne dans une plage de données.
EQUIV renvoie la position de la valeur cherchée dans le tableau.

Contrairement aux fonctions RECHERCHEV et RECHERCHEH qui ne renvoient que la première donnée trouvée, les fonctions INDEX et EQUIV, utilisées ensemble, permettent d'extraire plusieurs éléments répondant à la recherche dans un tableau.

Tous les exemples présentés dans le tutoriel sont disponibles dans le classeur téléchargeable en bas de cette page.

II. La fonction Index

II-A. Description

La fonction INDEX peut renvoyer une valeur (formule matricielle) ou la référence de la cellule (formule référentielle) à l'intersection d'une ligne et d'une colonne dans une plage de cellules.

Définition des plages et constantes matricielles :
     Une plage matricielle partage une même formule.
     Par exemple, dans une nouvelle feuille de calcul, sélectionnez la cellule A1.
     Collez cette formule
     =LIGNE(Feuil1!$B$2:$B$20)-LIGNE(Feuil1!$B$2)
     Sélectionnez la plage A1:A19
     Appuyez sur la touche clavier F2
     Pour valider la formule matricielle, appuyez sur CTRL+MAJ+ENTRÉE.

     Une constante matricielle est un groupe de constantes qui sert d'argument dans la formule.
     Dans l'exemple ci-dessous, la constante matricielle {"A";"B";"C";"D";"E";"F"} affiche une lettre aléatoire entre A et F :
     =INDEX({"A";"B";"C";"D";"E";"F"};(ENT(ALEA()*6+1)))

La fonction INDEX affiche la valeur d'erreur #REF! si les arguments no_lig et no_col font référence à une cellule située en dehors du tableau.

II-A-1. La forme matricielle

Renvoie une donnée d'un tableau ou d'une matrice à partir des numéros de ligne et de colonne indiqués.

La syntaxe est
INDEX(tableau;no_ligne;no_col)

tableau définit la plage de données où va être effectuée la recherche
no_ligne définit la ligne du tableau
no_col définit la colonne du tableau

Par exemple :
En spécifiant un numéro de ligne et de colonnes d'un tableau, vous pouvez récupérer le contenu de la cellule à cette intersection.




Au moins un des deux arguments no_ligne ou no_col doit être spécifié.

Si les arguments no_lig et no_col sont tous les deux utilisés, la fonction INDEX renvoie la valeur de la cellule située à l'intersection des numéros de ligne et de colonne.

Si l'argument tableau contient une seule ligne ou colonne, l'argument no_lig ou no_col correspondant est facultatif.

Si vous spécifiez la valeur 0 pour l'argument no_lig ou no_col, la fonction INDEX renvoie respectivement la matrice des valeurs de la colonne ou de la ligne entière.

II-A-2. La forme référentielle

Dans ce cas, la fonction INDEX renvoie la référence de la cellule située à l'intersection d'une ligne et d'une colonne déterminées.
Cette référence est utilisable dans d'autres formules.
L'exemple suivant renvoie l'adresse de la cellule à l'intersection de la 5ième ligne et de la 2ième colonne dans la plage A1:B10 (soit $B$5).
=CELLULE("adresse";INDEX(A1:B10;5;2))

La syntaxe est
INDEX(réf;no_lig;no_col;no_zone)

réf est une référence à une ou plusieurs plages de cellules.
Placez l'argument réf entre parenthèses lorsque vous utilisez des plages multiples. Vous pouvez choisir la sélection sur laquelle la fonction doit être exécutée grâce à l'argument no_zone.
Cet exemple renvoie la référence à l'intersection de la 5ième ligne et de la 1ière colonne dans la 3ième plage de cellules (G1:H10) :
=INDEX((A1:B10;D1:E10;G1:H10);5;1;3)

no_zone définit la plage de l'argument réf pour laquelle l'intersection de no_col et no_lig doit être renvoyée.
La première zone sélectionnée ou entrée porte le numéro 1, la deuxième, le numéro 2 et ainsi de suite.
Si l'argument no_zone est omis, la fonction INDEX utilise la zone numéro 1.

Par exemple, si l'argument réf décrit les cellules (A1:B10;D1:E10;G1:H10), l'argument no_zone 1 correspond à la plage A1:B10, l'argument no_zone 2, à la plage D1:E10 et l'argument no_zone 3, à la plage G1:H10.

Si chaque zone de l'argument réf contient une seule ligne ou colonne, l'argument no_lig ou no_col, respectivement, devient facultatif. Par exemple, dans le cas d'un argument référence à une seule ligne, utilisez la fonction INDEX(réf;;no_col).

no_lig est le numéro de la ligne de référence à partir de laquelle une référence doit être renvoyée.

no_col est le numéro de la colonne de réf à partir de laquelle une référence doit être renvoyée.

Si vous spécifiez la valeur 0 (zéro) pour l'argument no_lig ou no_col, la fonction INDEX renvoie respectivement la référence de la colonne ou de la ligne entière.

Si les arguments no_lig et no_col sont omis, la fonction INDEX renvoie la zone de l'argument référence définie par l'argument no_zone.

II-B. Exemples

II-B-1. Afficher une donnée aléatoire

Cet exemple affiche une donnée aléatoire parmi les cellules de la plage A10:A15.

Formule

=INDEX(A10:A15;(ENT(ALEA()*6+1)))

Appuyez sur la touche F9 pour relancer le calcul.

II-B-2. Renvoyer la dernière donnée saisie dans la colonne A

La formule fonctionne également s'il y a des cellules vides entre les données. Cet exemple suppose qu'il y a moins de 1000 données dans la colonne A.
(formule matricielle à valider par Ctrl+Maj+Entree).

Formule

=INDEX(A1:A1000;MAX(NON(ESTVIDE(A1:A1000))*LIGNE(A1:A1000));1)

II-B-3. Renvoyer la dernière donnée saisie dans la ligne 1

Cette formule renvoie la donnée la plus proche, à gauche de la cellule AA1
(formule matricielle à valider par Ctrl+Maj+Entrée)

Formule

=INDEX(A1:Z1;MAX(COLONNE(A1:Z1)*(A1:Z1<>"")))

II-B-4. Extraire une donnée sur 5 dans la plage A1:A100

Saisissez la formule dans la première ligne d'une autre colonne puis utilisez la poignée de recopie vers le bas.

Formule

=SI(LIGNE()=1;A1;INDEX($A$2:$A$100;(LIGNE()-2)*5+5))

II-B-5. Extraire les données d'une liste sans doublons

Si par exemple votre liste initiale est dans la plage A1:A15,
En B1, saisissez :

Formule

=A1

En B2, saisissez cette formule matricielle (à valider par Ctrl+Maj+Entree).

Formule

=SI(B1="";"";INDEX(A2:A$16;MIN(SI(NB.SI(B$1:B1;A2:A$15);LIGNE(A$16)-LIGNE()+1;LIGNE(A2:A$15)-LIGNE()+1))))

Puis étirez la formule vers le bas.

III. La fonction Equiv

III-A. Description

La fonction EQUIV recherche une valeur dans une colonne d'un tableau et renvoie sa position verticale (numéro de ligne).

La syntaxe est :
Equiv(valeur_cherchée;tableau_recherche;type)

valeur_cherchée est la donnée recherchée dans le tableau.
La donnée peut être de tout type: texte, numérique ou logique.

tableau_recherche est le tableau de données.

type spécifie la recherche à effectuer.
Si la valeur de l'argument type est -1 (valeur par défaut), la fonction EQUIV trouve la plus petite valeur qui est supérieure ou égale à celle de l'argument valeur_cherchée. Les valeurs de l'argument matrice_recherche doivent être placées en ordre décroissant.

Si la valeur de l'argument type est 0, la fonction EQUIV trouve la première valeur exactement équivalente à celle de l'argument valeur_cherchée. Les valeurs de l'argument matrice_recherche peuvent être placées dans un ordre quelconque.
L'application vérifie tous les éléments de l'argument tableau_recherche les uns après les autres, jusqu'à ce qu'il trouve une donnée égale à la valeur recherchée.
Si l'argument valeur_cherchée est une donnée texte, les caractères génériques peuvent être utilisés. L'astérisque (*) correspond à une séquence de caractères. Le point d'interrogation (?) correspond à un caractère particulier. Si vous voulez rechercher un véritable point d'interrogation ou astérisque, tapez un tilde (~) avant ce caractère.

Si la valeur de l'argument type est 1, la fonction EQUIV trouve la valeur la plus élevée qui est inférieure ou égale à celle de l'argument valeur_cherchée. Les valeurs de l'argument tableau_recherche doivent être triées en ordre croissant.

La fonction EQUIV n'est pas sensible à la casse.

Une valeur d'erreur #N/A est renvoyée si la donnée n'est pas trouvée dans le tableau.

III-B. Exemples

III-B-1. Retrouver la position d'une donnée dans une plage de cellules

Cet exemple affiche la position de la donnée "dvp" dans la plage cible A1:A10

Formule

=EQUIV("dvp";A1:A10;0)

Si le mot recherché est dans la cellule A3, le résultat renvoyé sera 3.
La valeur recherchée peut être numérique ou du texte.
La fonction EQUIV n'est pas sensible aux majuscules ou minuscules pour les recherches de texte.

Si vous ne connaissez qu'une partie du mot recherché, il est possible d'insérer des valeurs génériques.
L'astérisque est équivalent à une séquence de caractères,
Le point d'interrogation est équivalent à un caractère unique.

Par exemple :

Formule

=EQUIV("d*";A1:A10;0)

III-B-2. Extraire le dernier mot d'un texte saisi dans la cellule A1

Formule

=DROITE(A1;EQUIV(" ";STXT(A1;NBCAR(A1)-LIGNE(INDIRECT("1:"&NBCAR(A1)));1);0))

A valider par Ctrl+Maj+Entree

III-B-3. Appliquer une formule conditionnelle sur une plage de cellule

Cet exemple renvoie les valeurs max, min et la moyenne de B3:B10, pour les dates comprises entre F2 et G2.
     Les dates sont dans la plage A3:A10
     Les données à calculer sont en B3:B10
     La date de début est saisie en F2
     La date de fin est saisie en G2

La valeur maxi :

Formule

=MAX(INDIRECT(ADRESSE((EQUIV($F$2;A3:A10)+2);2)&":"&ADRESSE((EQUIV($G$2;A3:A10)+2);2)))

La valeur mini :

Formule

=MIN(INDIRECT(ADRESSE((EQUIV($F$2;A3:A10)+2);2)&":"&ADRESSE((EQUIV($G$2;A3:A10)+2);2)))

La moyenne

Formule

=MOYENNE(INDIRECT(ADRESSE((EQUIV($F$2;A3:A10)+2);2)&":"&ADRESSE((EQUIV($G$2;A3:A10)+2);2)))

III-B-4. Faire une recherche verticale RECHERCHEV sur plusieurs feuilles

La fonction suivante recherche le contenu de la cellule FeuilResult!A2.
Les données sont dans la plage C3:F20 de chaque feuille (Feuil1 à Feuil4):

Formule

=RECHERCHEV(FeuilResult!A2;INDIRECT("Feuil"&NB(EQUIV(FeuilResult!A2;Feuil1!C3:C20;0))+ NB(EQUIV(FeuilResult!A2;Feuil2!C3:C20;0))*2+NB(EQUIV(FeuilResult!A2;Feuil3!C3:C20;0))*3+ NB(EQUIV(FeuilResult!A2;Feuil4!C3:C20;0))*4&"!C3:F20");4;FAUX)

Si la donnée saisie (FeuilResult!A2) est trouvée, la fonction renvoie la valeur de la colonne F.
La formule suppose que la donnée recherchée est unique dans le colonne C de chaque plage (Feuil1!C3:C20 à Feuil4!C3:C20).


La partie :

Formule

INDIRECT("Feuil"&NB(EQUIV(FeuilResult!A2;Feuil1!C3:C20;0))+ NB(EQUIV(FeuilResult!A2;Feuil2!C3:C20;0))*2+NB(EQUIV(FeuilResult!A2;Feuil3!C3:C20;0))*3+ NB(EQUIV(FeuilResult!A2;Feuil4!C3:C20;0))*4

Permet de définir dans quelles feuilles faire la recherche.

III-B-5. Extraire les valeurs impaires contenues dans la plage A1:A5

En B1, saisissez:

Formule

=(A1=IMPAIR(A1))*1

Etirez la formule vers le bas.
La fonction renvoie 1 si la donnée est impaire, et 0 dans le cas contraire.

En C1, saisissez:

Formule

=EQUIV(1;$B$1:$B$5;0)

En C2, saisissez:

Formule

=EQUIV(1;INDIRECT("B"&(SOMME($C$1:C1)+1)&":B5");0)

Puis étirez la formule vers le bas.
Vous obtenez la position relative des lignes impaires dans le tableau.

En D1, saisissez:

Formule

=SI(ESTERREUR(DECALER($A$1;SOMME($C$1:C1)-1;0));"";DECALER($A$1;SOMME($C$1:C1)-1;0))

Puis, étirez la formule vers le bas.

Les colonnes B et C peuvent ensuite être masquées si besoin.

IV. L'association Index Equiv

IV-A. Description

L'association INDEX / EQUIV est une solution plus complexe à mettre en œuvre que la classique fonction RECHERCHEV mais moins limitative.
Elle permet de :
     Gérer des plages multiples non adjacentes et sans restriction d'ordre.
     D'effectuer une recherche multicritères dans la matrice.

Vous pouvez par exemple afficher le prix d'une référence saisie en E6 en fonction du nombre de pièces saisi en E7.
Formule à valider par Ctrl+Maj+Entrée

Formule

=INDEX(C9:C20;EQUIV(1;(A9:A20=E6)*(B9:B20=E7);0))

A9:A20 contient les références
B9:B20 contient le nombre de pièces
C9:C20 contient les prix

IV-B. Exemples

IV-B-1. Afficher la donnée (texte ou numérique) qui apparait le plus souvent dans la plage A1:A10

Formule

=INDEX(A1:A10;EQUIV(MAX(NB.SI(A1:A10;A1:A10));NB.SI(A1:A10;A1:A10);0))

Formule matricielle à valider par Ctrl+Maj+Entrée

IV-B-2. Créer une relation entre plusieurs tableaux

Lorsque vous stockez vos données dans plusieurs tables (bien que ce soit fortement déconseillé sous Excel), vous aurez peut être besoin ensuite d'une liaison entre les informations contenues dans les bases.

Par exemple:
Vous disposez de deux tableaux contenant chacun un type de données (Le nom et le code produit dans un premier tableau, le nom et le prix unitaire dans un deuxième tableau).
Vous souhaitez afficher une synthèse qui renvoie le prix unitaire par code produit.
Plutôt que de reconstituer une base de données commune par de fastidieux copier/coller, et sans avoir besoin de trier et reclasser les informations, vous pouvez créer une relation simple entre des tableaux, grâce aux fonctions Index/Equiv.

Formule

=SI(ESTNA(INDEX($E$20:$E$25;EQUIV(INDEX($A$20:$A$24;EQUIV(G20;$B$20:$B$24;0));$D$20:$D$25;0))); "";INDEX($E$20:$E$25;EQUIV(INDEX($A$20:$A$24;EQUIV(G20;$B$20:$B$24;0));$D$20:$D$25;0)))

Remarque:
Il s'agit ici d'un simple exemple pour montrer une capacité des fonctions Index/Equiv. Cette solution a ses limites, notamment si un élément apparait plusieurs fois dans une des tables car seule la première donnée trouvée sera prise en compte.
Sous Excel, privilégiez toujours des bases des données uniques, sinon utilisez Access qui permet de gérer facilement les bases relationnelles.

IV-B-3. Lister toutes les occurrences contenant une chaîne particulière

L'exemple liste toutes les cellules de la plage A11:A20 qui contiennent le mot "DVP".

En C14, vous allez saisir la chaîne de caractères à rechercher.
Dans la cellule E14, saisissez cette formule:

Formule

=EQUIV("*"&C$14&"*";DECALER(A$11:A$20;SOMME(E$13:E13);0);0)

Puis utilisez la poignée de recopie vers le bas.

Dans la cellule F14, saisissez:

Formule

=SI(ESTNA(INDEX(A$11:A$20;SOMME(E$14:E14)));"";INDEX(A$11:A$20;SOMME(E$14:E14)))

Puis utilisez les poignées de recopie vers le bas.
Vous pouvez si vous le souhaitez ensuite, masquer les colonnes intermédiaires et les valeurs #N/A.

IV-B-4. Rechercher entre bornes sur plusieurs colonnes

La fonction retrouve dans quelle ligne est située la valeur de la cellule E11 en fonction des données Mini et Maxi indiquées dans les colonnes A et B. Le contenu de la colonne C est renvoyé en résultat.
Si vous recherchez 13 (13 étant compris entre 12 et 14), la formule affichera "B".

Formule

=INDEX(C10:C14;EQUIV(1;(A10:A14=MAX((A10:A14<=E11)*A10:A14))*(B10:B14=MIN(SI(B10:B14>=E11;B10:B14)));0))

Formule matricielle à valider par Ctrl+Maj+Entrée

مواضيع من منتدى نقاش المغرب  العربي

حركات دلع ورومنسية للزوجات
نقطة مهمة يجب على الزوج عدم إهمالها
زواج الرجل من المرأة السمينة يمنحه السعادة الأبدية
للمرأة الثلاثينية طعم خاص
**افهمــــــي زوجــــك ..واعطيه فرصــــة ليفهمــــك **
اخدمي زوجك تملكي قلبه
مرحلة السكتة الزوجية وطرق علاجها
الحب بين الزوجين استثمار عاطفي مدى الحياة
خبانة الزوجة
الأْنْثَى وَردةٌ حَمراء ... لا تقطفها أيها الرجل
الكذبة البيضاء.. "شماعة" أخطاء الأزواج العرب! هل تنقذ الحياة الزوجية؟
سحر حلال اسحري به حماتك
كيف تقولين لزوجك " أحبك " دون أن تنطقي بها !!
أسرارا التعرف على النساء الجميلات