La fonction Equiv()
Le moins cher
Valeur en fonction de 2 critères
Positionnement sur valeur inférieure ou supérieure
Recherche suivant plusieurs critères dans une
BD
Jokers * et ? dans Equiv()
Recherche sur une partie de code
Recherche 2 critères sur table en colonne
Liste des produits d'une catégorie
Fonction perso EquivCouleurFond
Recherche dernier
Dernier dernière date
Equiv(ValeurCherchée,champ;0 ou 1ou -1)
donne la position de la valeur cherchée dans le
champ (1,2,3,..)
-Avec 1, on se positionne sur la valeur inférieure si la valeur
cherchée n'existe pas dans la table qui doit
être en ordre croissant
-Avec 0, on se positionne sur la valeur exacte. La table n'a pas besoin
d'être en ordre croissant.
Retourne #N/A si pas trouvé.
-Avec -1,on se positionne sur la valeur supérieure si la valeur
cherchée n'existe pas dans la table qui
doit être en ordre décroissant.
-Avec 1, (la table est TRIEE) la recherche est faite par DICHOTOMIE et
peut être x100 + RAPIDE puisqu'il suffit de quelques accés
pour retrouver un code. C'est TRES IMPORTANT lorsque la table est de taille
importante et que
la formule Equiv() est recopiée x1000 fois (Avec 0 , Excel consulte
la table SEQUENTIELLEMENT).
Sur l'exemple on cherche le nom du vendeur qui a réalisé le meilleur CA:
Noms de champ
Ca $C$2:$C$7
Noms $B$2:$B$7
En F1, =Max(ca) donne le meilleur CA
En F3, =EQUIV(F1;ca;0) donne la position du meilleur
CA dans le champ C2:C7 (3)
En F5, =INDEX(Noms;F3) donne la 3eme cellule du champ
(B2:B7)
Avec une seule formule: =INDEX(Noms;EQUIV(MAX(ca);ca;0))
Remarque:
Sur cet exemple, on ne peut pas utiliser RechercheV().
Avec RechercheV(), la recherche doit se faire
dans la première colonne du tableau.
Le moins cher
Sur l'exemple, on recherche, pour chaque produit, le nom du fournisseur le moins cher:
=EQUIV(MIN(B3:E3);B3:E3;0) donne la position
du prix minimum dans B3:E3 (3)
=INDEX(Fournisseur;3) donne la 3eme cellule du champ
B2:E2 (Fournisseur)
Valeur en fonction de 2 critères
On recherche un prix en fonction d'une désignation de produit et d'un mode de conditionnement.
EquivIndexProduits
index Equiv
Prix
En C3, =EQUIV(A4;Designation;0) donne
la position du produit dans la table Désignation
En C7, =EQUIV(A7;Conditionnement;0) donne la
position du conditionnement dans la table Conditionnement
Le prix de la table Prix s'obtient par : =INDEX(Prix;C3;C7)
Avec une seule formule
=INDEX(Prix;EQUIV(A3;Designation;0);EQUIV(A7;Conditionnement;0))
Positionnement sur valeur inférieure ou supérieure
-Les points sont en ordre croisssant et
on veut se positionner sur la valeur inférieure (1500
pour la valeur cherchée=1505).
=INDEX(val;EQUIV(B13;Points;1);EQUIV(B12;an;0))
-Les points sont en ordre croisssant et on veut se positionner sur la valeur supérieure (1600 pour la valeur cherchée=1505).
=INDEX(val;EQUIV(VRAI;Points>=B13;0);EQUIV(B12;an;0))
Valider avec maj+ctrl+entrée
-Les points sont en ordre décroisssant et on veut se positionner sur la valeur supérieure (1600 pour la valeur cherchée=1505).
=INDEX(val;EQUIV(B13;points;-1);EQUIV(B12;an;0))
-Les points sont en ordre décroisssant et on veut se positionner sur la valeur inférieure (1500 pour la valeur cherchée=1505).
=INDEX(val;EQUIV(VRAI;points<=B13;0);EQUIV(B12;an;0))
Valider avec maj+ctrl+entrée
Position du dernier nombre dans un vecteur ligne ou colonne
=EQUIV(9^9;Champ)
Position de la dernière chaîne dans un vecteur ligne ou colonne
=EQUIV("zz";champ)
Position du premier libellé dans une table
=Equiv("*";champ;0) donne la position du premier libellé d'une colonne.
On cherche la position du nombre immédiatement supérieur
La liste est triée.
=EQUIV(VRAI;A7:A11>=D7;0)
Valider avec Maj+Ctrl+Entrée
Recherche de la valeur supérieure dans une table non triée
=MIN(SI(B4:B7>=B2;B4:B7))
Valider avec Maj+ctrl+entrée
La valeur la plus proche (inférieure ou supérieure)
=MIN(SI(ABS(Champ-valeur)=MIN(ABS(Champ-valeur));Champ))
Valider avec Maj+ctrl+entrée
Autre exemple
On veut le prix pour la date la plus proche d'une date
Valeur pour la date plus proche supérieure
=INDEX(prix;EQUIV(VRAI;date>=D2;0))
Valider avec maj+ctrl+entrée
Valeur pour la date plus proche
=MIN(SI(ABS(date-D2)=MIN(ABS(date-D2));prix))
Valider avec maj+ctrl+entrée
Recherche le dernier
=INDEX(Prix;MAX(SI((produit=A2);LIGNE(INDIRECT("1:"&LIGNES(produit))))))
Valider avec maj+ctrl+entrée
Si la table est triée par produit
=INDEX(Prix;EQUIV(A2;produit;0)+NB.SI(produit;A2)-1)
ou
=RECHERCHEV(A2;Tbl;2;VRAI)
On veut le dernier prix d'achat (dernière date)pour un produit
Les dates d'achat pour chaque produit sont dans l'ordre croissant:
Date dernier achat: =SI(NB.SI(produit;A2);MAX(SI((produit=A2);date));"")
Valider avec Maj+ctrl+entrée
Prix: =SI(NB.SI(produit;A2);INDEX(Prix;MAX(SI(produit=A2;LIGNE(INDIRECT("1:"&LIGNES(produit)));0)));"")
Les dates pour chaque produit ne sont pas dans l'ordre croissant et Il n'y a qu'un prix par jour pour chaque produit
Date dernier achat : =SI(NB.SI(produit;A2);MAX(SI((produit=A2);date));"")
Prix: =SI(NB.SI(produit;A2);INDEX(Prix;EQUIV(1;(MAX((produit=A2)*date)=date)*(produit=A2);0));"")
Recherche avec 2 critères sur table en colonne (prix produit fournisseur )
On recherche le prix d'un produit chez un fournisseur.
Le même produit peut exister chez plusieurs fournisseurs.
Lorsque le fournisseur est trouvé, il faut limiter la recherche
du produit aux produits de ce fournisseur.
=INDEX(prix;EQUIV(F2;DECALER(produit;EQUIV(E2;fourn;0)-1;;SI(EQUIV("zzz";fourn)=EQUIV(E2;fourn;0);LIGNES(produit)-EQUIV(E2;fourn;0)+1;EQUIV("*";DECALER(fourn;EQUIV(E2;fourn;0););0)));0)+EQUIV(E2;fourn;0)-1)
Autre exemple
EquivDecaler
EquivIndexDecaler
=INDEX(prix;EQUIV(F2;DECALER(tarif;EQUIV(E2;date)-1;;
SI(E2<MAX(date);MIN(SI(date>E2;LIGNE(tarif)))-LIGNE(tarif)+1;LIGNES(tarif)+1)-EQUIV(E2;date));)+EQUIV(E2;date)-1)
Valider avec maj+ctrl+entrée
Sous total
=SI(A2<>"";SOMME(DECALER(B2;;;SI(EQUIV(A2;nom;0)<>EQUIV("zzz";nom);EQUIV("*";A3:A$12;0);LIGNES(nom)-EQUIV(A2;nom;0)+1));0);"")
Recherche suivant plusieurs critères dans une BD
Dans une BD, on cherche pour une personne et un examen la date la plus récente ainsi que le statut.
En G2:
=SI(MAX((Noms=$F2)*(Examen=G$1)*Date)>0;
INDEX(Statut;EQUIV(1;(MAX((Noms=$F2)*(Examen=G$1)*Date)=Date)*(Noms=$F2);0));"")
Valider avec maj+ctrl+entrée
En H2:
=SI(MAX((Noms=$F2)*(Examen=G$1)*Date)>0;MAX((Noms=$F2)*(Examen=G$1)*Date);"")
Valider avec maj+ctrl+entrée
Jokers * et ? dans Equiv()
Les jokers * et ? peuvent être utilisés dans une recherche avec Equiv()
=INDEX(Age;EQUIV(E2;Nom;0))
Si le champ de recherche comporte des caractères spéciaux *,? , les faire précéder du caractère ~:
=INDEX(Prix;EQUIV(SUBSTITUE(E2;"*";"~*");Code;0))
Recherche sur des chaînes>255 caractères
Equiv() ne fonctionne pas pour des chaînes >255 caractères. On peut utiliser la formule ci dessous pour trouver la position de la cellule qui contient engin de manutention
=MAX(SI(ESTNUM(CHERCHE(C2;champ));LIGNE(INDIRECT("1:"&LIGNES(champ)))))
Valider avec maj+ctrl+entrée
Sur cet exemple, on recherche un code associé à une valeur
=INDEX(Codes;EQUIV("*;"&A2&";*";";"&Valeurs&";";0))
On recherche un code dans un texte
Il faut retrouver le code présent dans le texte (ASA,APA,DPD)
=INDEX(Codes;EQUIV(1;EQUIV("*"&Codes&"*";A2;0);0))
Valider avec maj+ctrl+entrée
ou =INDEX({"ASA";"APA";"DPD"};EQUIV(1;EQUIV("*"&{"ASA";"APA";"DPD"}&"*";A2;0);0))
Si le nombre de caractères dans une cellule dépasse 255 caractères
=trouvecode(A2;Valeurs;Codes)
Function TrouveCode(v, valeurs, codes)
TrouveCode = "Inconnu"
temp = ";" & v & ";"
For i = 1 To valeurs.Count
If InStr(";" & valeurs(i) &
";", temp) > 0 Then
TrouveCode = codes(i)
End If
Next i
End Function
Recherche sur une partie de code
-La table contient des codes aa,bb,cc,... Le code cherché est bb rouge
Pour obtenir le prix
=INDEX(prix;EQUIV(1;NB.SI(A2;code&"*");0)))
valider avec maj+ctrl+entrée
ou
=INDEX(prix;EQUIV(1;EQUIV(code&"*";A2;0);0))
valider avec maj+ctrl+entrée
ou
=SI(SOMME(NB.SI(A2;"*"&Code))=0;"Inconnu";INDEX(prix;EQUIV(1;NB.SI(A2;code&"*");0))))
valider avec maj+ctrl+entrée
RechPartieCode
RechPartieCode2
Autre exemple
On veut extraire Schmidtt de location B. Schmitt du 31/05 au 04/06/10
=INDEX(ref;EQUIV(1;NB.SI(A2;"*"&ref&"*");0))
Autre exemple
Pour FIDJI BLACK/RED VENERE T.48, on recherche la référence associée à BLACK/RED
En B2: =INDEX(ref;EQUIV(1;NB.SI(A2;"*"&couleur&"*");0))
On recherche un code dans un texte
Il faut retrouver le code présent dans le texte (ASA,APA,DPD)
=INDEX(Codes;EQUIV(1;EQUIV("*"&Codes&"*";A2;0);0))
Valider avec maj+ctrl+entrée
ou =INDEX({"ASA";"APA";"DPD"};EQUIV(1;EQUIV("*"&{"ASA";"APA";"DPD"}&"*";A2;0);0))
Convertit les marques en majuscules
ConvertitMajuscules
En B2:
=SUBSTITUE(A2;INDEX(ref;EQUIV(1;NB.SI(A2;"*"&ref&"*");0));MAJUSCULE(INDEX(ref;EQUIV(1;NB.SI(A2;"*"&ref&"*");0))))
Valider avec maj+ctrl+entrée
On extrait un mois dans un texte
Il faut extraire le mois présent dans le texte en A1 (mars).
Le 23 mars 1995
=INDEX(TEXTE(DATE(;LIGNE(1:12);1);"mmmm");EQUIV(1;NB.SI(A1;"*"&TEXTE(DATE(;LIGNE(1:12);1);"mmmm")&"*");0))
Valider avec Maj+Ctrl+entrée
On extrait un jour dans un texte
Il faut extraire le jour présent dans le texte en A1 (jeudi).
Le jeudi 14 mars 2012
=INDEX(TEXTE(DATE(;1;LIGNE(1:7));"jjjj");EQUIV(1;NB.SI(A1;"*"&TEXTE(DATE(;1;LIGNE(1:7));"jjjj")&"*");0))
Valider avec Maj+Ctrl+entrée
On recherche les produits d'une catégorie
=INDEX(produits;LIGNES($1:1);EQUIV($A$2;Catégorie;0))
En matriciel
-Sélectionner E2:E8
=INDEX(produits;;EQUIV(A2;Catégorie;0))
-Valider avec maj+ctrl+entrée
Autre exemple
On recherche un coefficient Mini en fonction d'un grade (B2) et d'un échelon (C2).
- =EQUIV($B2;$G$1:$G$30;0) donne la ligne
du grade dans le tableau G1:G30
- C2+1 donne la colonne dans le tableau G1:M30
- =INDEX($G$1:$M$30;EQUIV($B2;$G$1:$G$30;0)+2;$C2+1)
donne la valeur
à l'intersection de la ligne et de colonne du
tableau G1:M30.
- IndexEquiv -
Variante
Si l'échelon n'existe pas, on se positionne sur l'échelon
inférieur.
=INDEX($G$1:$M$30;EQUIV($B2;$G$1:$G$30;0)+2;
EQUIV($C2;DECALER($H$1;EQUIV($B2;$G$1:$G$30;0);0;1;6);1)+1)
Fonction perso EquivCoulFond(coul As Range, champ As Range)
Donne la position d'une couleur dans un champ colonne ou ligne.
Function equivCoulFond(coul As Range, champ As Range)
Application.Volatile
equivCoulFond = 0
For i = 1 To champ.Count
If champ(i).Interior.ColorIndex = coul.Interior.ColorIndex
Then equivCoulFond = i
Next i
End Function
Recherche avec des caractères accentués
Le nom cherché dans une table peut être frappé avec ou sans accent.
=INDEX(Villes;EQUIV(sansaccentm(A2);sansaccentm(Noms);0))
Exemples |
| index Equiv Prix |
| Equiv 2 conditions |