EQUIV / EQUIVX : Exemples de recherches complexes

Les fonctions EQUIV et EQUIVX permettent de trouver la position d'une valeur dans une liste de valeurs (plage de cellules, matrice, constante matricielle). Elle est souvent intégré à d'autre fonction comme la fonction INDEX.

Par exemple, on peut trouver la position de la référence 40 mais comment faire pour trouver la position de la 1^ère valeur dont le prix est supérieur à 100 ou bien celle correspondante à plusieurs critère (par exemple comme un type ET une couleur spécifique) ?

(Pour plus d'information sur l'utilisation "classique" des fonctions EQUIV et EQUIVX, voir Fonction EQUIV ou fonction EQUIVX ? et Les fonctions de recherche de valeur).

J'ai présenté mes formules en les basant sur la fonction EQUIVX. Pour les utiliser avec la fonction EQUIV, il suffit de faire cette manipulation complexe : remplacer EQUIVX par EQUIV 😁.

Les utilisateurs d'anciennes versions d'Excel devront valider les fonctions EQUIV en mode matricielle, c'est à dire par la combinaison de touches clavier CTRL + MAJ + ENTRER.

Tableau de départ

Tableau structuré "TabProduits".

Cas classique : Position de la référence 40 (pour rappel)

=EQUIVX(40;TabProduits[Ref];0)

=> Résultat = 4

=EQUIVX(40;TabProduits[Ref];0)

=> Résultat = 4

Cas complexe 1 : 1^ère valeur dont le Prix est supérieur à 100

=EQUIVX(VRAI;TabProduits[Prix]>100;0)

=> Résultat = 5

=EQUIVX(VRAI;TabProduits[Prix]>100;0)

=> Résultat = 5

Principe de fonctionnement :

• L'expression "TabProduits[Prix]>100" va générer une matrice de valeurs booléennes VRAI et FAUX dans laquelle EQUIV/EQUIVX va chercher la 1^ère valeur à VRAI.

Cas complexe 2 : 1^ère valeur de Type "Chaise" ET de Matière "Plastique"

Solution 1 : On génère une valeur combinée à rechercher

La rechercher se fera évidement sur une colonne combinée elle aussi.

=EQUIVX("ChaisePlastique";TabProduits[Type]&TabProduits[Matière];0)

=> Résultat = 2

=EQUIVX("ChaisePlastique";TabProduits[Type]&TabProduits[Matière];0)

=> Résultat = 2

Ou cette version plus "sécurisé" (évite des faux positif comme "Chat" & "on" vs "Cha" & "ton" pour "Chaton").

=EQUIVX("Chaise@Plastique";TabProduits[Type]&"@"&TabProduits[Matière];0)

=EQUIVX("Chaise@Plastique";TabProduits[Type]&"@"&TabProduits[Matière];0)

Le caractère "@" est utilisé ici comme séparateur. Utiliser tous caractère n'étant jamais présent dans les valeurs cherchées.

Solution 2 :

Variante du cas complexe 1.

=EQUIVX(1;(TabProduits[Type]="Chaise")*(TabProduits[Matière]="Plastique");0)

=> Résultat = 2

=EQUIVX(1;(TabProduits[Type]="Chaise")*(TabProduits[Matière]="Plastique");0)

=> Résultat = 2

• Pour Excel VRAI correspond à 1 et FAUX à 0 donc :
  • VRAI * VRAI = 1
  • FAUX * VRAI = 0 et VRAI * FAUX = 0
  • FAUX * FAUX = 0
• Ainsi la matrice résultante de (TabProduits[Type]="Chaise")*(TabProduits[Matière]="Plastique") est une matrice de 0 et de 1 donc la valeur cherchée n'est plus la valeur VRAI mais la valeur 1.

Remarque : Un OU logique peut aussi être obtenu ainsi

Cas : 1^ère valeur de Type "Chaise" ET de Matière "Plastique" OU "Fer".

=EQUIVX(1;(TabProduits[Type]="Chaise")*(((TabProduits[Matière]="fer")+(TabProduits[Matière]="Plastique"))>0);0)

=EQUIVX(1;(TabProduits[Type]="Chaise")*(((TabProduits[Matière]="fer")+(TabProduits[Matière]="Plastique"))>0);0)

• Ici les OU sont générés par des additions. La somme des comparaisons sera égale au nombre de comparaisons vérifiées (à VRAI).

Cas complexe 3 : Recherche sensible à la casse

Même logique que le cas complexe 1.

Par défaut, la recherche de "Chaise", "CHAISE" ou "chaise" donnera le même résultat, voici la solution pour faire une recherche sensible à la casse.

=EQUIVX(VRAI;EXACT("CHAISE";TabProduits[Type]);0)

=> Résultat = 6

=EQUIVX(VRAI;EXACT("CHAISE";TabProduits[Type]);0)

=> Résultat = 6

• La fonction EXACT permet de comparer "strictement" les valeurs.

Cas bonus pour faire comme EQUIVX avec la fonction EQUIV

Ces cas sont nativement gérés via les arguments de la fonction EQUIVX mais voici des syntaxes pour pouvoir utiliser la fonction EQUIV. Ceci peut se présenter si la fonction EQUIVX ne peut être utilisée pour cause de compatibilité avec d'ancienne version d'Excel par exemple.

Cas complexe 4 : 1^ère lettre du nom du fournisseur est "D"

Même logique que le cas complexe 1.

=EQUIV(VRAI;GAUCHE(TabProduits[Fournisseur];1)="d";0)

=> Résultat = 2

=EQUIV(VRAI;GAUCHE(TabProduits[Fournisseur];1)="d";0)

=> Résultat = 2

Cas complexe 5 : Dernière occurrence

Il est possible de se passe de la fonction EQUIV dans ce cas

Position du fournisseur "Dupont" en recherchant à partir du bas du tableau.

=MAX((TabProduits[Fournisseur]="Dupont")*(LIGNE(TabProduits[Fournisseur])-LIGNE(TabProduits[[#En-têtes];[Fournisseur]])))

=> Résultat = 6

=MAX((TabProduits[Fournisseur]="Dupont")*(LIGNE(TabProduits[Fournisseur])-LIGNE(TabProduits[[#En-têtes];[Fournisseur]])))

=> Résultat = 6

• LIGNE(TabProduits[Fournisseur])-LIGNE(TabProduits[[#En-têtes];[Fournisseur]]) permet de générer une numérotation à partir de 1. L'utilisation d'une colonne avec la numérotation des lignes serait plus simple et plus rapide à l'exécution.

=MAX((TabProduits[Fournisseur]="Dupont")*TabProduits[NL])

=MAX((TabProduits[Fournisseur]="Dupont")*TabProduits[NL])

Remarque : Formules correspondantes au cas complexes précédent mais en mode dernière occurrence.

Cas complexe 1 : =MAX((TabProduits[Prix]>100)*TabProduits[NL]) ==> 5 
Cas complexe 2 : =MAX(((TabProduits[Type]="Chaise")*(TabProduits[Matière]="Plastique"))*TabProduits[NL]) ==> 6

Cas complexe 1 : =MAX((TabProduits[Prix]>100)*TabProduits[NL]) ==> 5 
Cas complexe 2 : =MAX(((TabProduits[Type]="Chaise")*(TabProduits[Matière]="Plastique"))*TabProduits[NL]) ==> 6

Merci pour votre attention bienveillante.