Microsoft 365 Trouver une valeur en fonction de plusieurs critères de recherche dont certains sont approximatifs

[sgas]

Bonjour,
Voici mon besoin,
J'ai un 1er tableau avec 4 colonnes : Clé1, Clé2, Bornes, Valeur
J'ai autant de valeur que de bornes pour les valeurs Clé1 et Clé2

Clé1	Clé2	Bornes	Valeur
01	PBLE	10	2
01	PBLE	20	3
02	PBLE	25	5
02	PAV	2,3	22
02	PAV	2,5	40

Dans un 2nd tableau, j'ai les valeur Clé1 et Clé2, ainsi qu'une 3ème donnée.
Dans ce tableau, je veux récupérer dans une 4ème colonne la Valeur indiquée dans le 1er tableau pour les Clés (1&2 qu'éventuellement je concatène), et la 3ème donnée égale à la borne ou comprise entre 2 bornes, dans ce cas, la valeur attendue est celle de la borne inférieure.
J'espère que je suis claire, je mets quelques exemples ci-dessous.

Clé1	Clé2	Pivot (3ème donnée)	? Valeur à retrouver
01	PBLE	20	3
02	PBLE	20	#N/A
02	PAV	2,4	22

Merci de votre aide.

J'ai essayé avec des formules INDEX & EQUIV, ça fonctionne avec mon tableau simple, mais dès que mon 1er tableau se complexifie, ça ne fonctionne plus ;( (voir en L20 sur ma feuille)
Y'a-t-il un moyen d'ignorer le résultat "vide" et dans ce cas décaler la colonne de recherche vers la gauche ? jusqu'à ce qu'il trouve une valeur non vide, dans mon cas la valeur 2.

 

[vgendron]

Bonjour

Il serait plus pratique que tu postes ton fichier qui sert d'exemple

 

[sgas]

J'ai modifié mon msg précédent et ajouter le fichier.

 

[JHA]

Bonjour à tous,

Un début en formule matricielle.


JHA

 

[job75]

Bonjour sgas, vgendron, JHA,

Voyez le fichier .xlsm joint et cette fonction VBA :

Function Resu(Cle1Cle2 As String, Pivot As Double, ConcatCle As Range, Bornes As Range, Valeur As Range)
Dim i&, n&, a()
For i = 1 To ConcatCle.Count
    If ConcatCle(i) = Cle1Cle2 Then
        If Bornes(i) <= Pivot Then
            n = n + 1
            ReDim Preserve a(1 To 2, 1 To n)
            a(1, n) = Bornes(i)
            a(2, n) = Valeur(i)
        End If
    End If
Next
If n Then Resu = Application.HLookup(Application.Max(Application.Index(a, 1, 0)), a, 2, 0) Else Resu = [#N/A]
End Function

Le code doit être placé impérativement dans un module standard.

Formule en L4 à propager vers le bas :

=Resu(I4&J4;K4;A$4:A$6000;D$4:D$6000;E$4:E$6000)

A+

 

[job75]

Avec la fonction précédente le recalcul des 9 formules s'effectue en 0,14 seconde.

Avec celle-ci le recalcul s'effectue en 0,04 seconde :

Function Resu(Cle1Cle2 As String, Pivot As Double, ConcatCle, Bornes, Valeur)
Dim i&, maxi#
ConcatCle = ConcatCle.Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
Bornes = Bornes.Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
Valeur = Valeur.Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
For i = 1 To UBound(ConcatCle)
    If ConcatCle(i, 1) = Cle1Cle2 Then _
        If Bornes(i, 1) <= Pivot Then _
            If Bornes(i, 1) > maxi Then maxi = Bornes(i, 1): Resu = Valeur(i, 1)
Next
End Function

 

[job75]

Bon avec cette formule Excel matricielle le recalcul se fait en 1,6 centième de seconde :

=INDEX(E$4:E$6000;EQUIV(MAX(SI(A$4:A$6000=I4&J4;SI(D$4:D$6000<=K4;D$4:D$6000)));SI(A$4:A$6000=I4&J4;SI(D$4:D$6000<=K4;D$4:D$6000));0))

A+

 

[sgas]

Bon avec cette formule Excel matricielle le recalcul se fait en 1,6 centième de seconde :

=INDEX(E$4:E$6000;EQUIV(MAX(SI(A$4:A$6000=I4&J4;SI(D$4:D$6000<=K4;D$4:D$6000)));SI(A$4:A$6000=I4&J4;SI(D$4:D$6000<=K4;D$4:D$6000));0))

A+

Super, merci, ça fonctionne.

 

[sgas]

Super, merci, ça fonctionne.

Bonjour @job75 , je me permets de te solliciter, je voudrai ajouter dans mon tableau de recherche un nouveau critère.
Il faudrait ajouter un critère de recherche de type date, c'est à dire, chaque ligne de mon barème s'applique entre 2 dates seulement, il y a donc 2 nouvelles colonnes dans mon Barème (colonnes A à G) et une nouvelle colonne dans ma "Base à compléter" (colonne N), je veux retrouver dans cette base la valeur liée aux critères Clé1, Clé2, Pivot et Date dans le barème. Je joins une nouvelle PJ pour être plus claire.

 

[job75]

Bonjour sgas,

Formule matricielle en O4 :

=INDEX(E$4:E$6000;EQUIV(MAX(SI((A$4:A$6000=K4&L4)*(N4>=F$4:F$6000)*(N4<=G$4:G$6000);SI(D$4:D$6000<=M4;D$4:D$6000)));SI((A$4:A$6000=K4&L4)*(N4>=F$4:F$6000)*(N4<=G$4:G$6000);SI(D$4:D$6000<=M4;D$4:D$6000));0))

A+

 

[sgas]

Bonjour sgas,

Formule matricielle en O4 :

=INDEX(E$4:E$6000;EQUIV(MAX(SI((A$4:A$6000=K4&L4)*(N4>=F$4:F$6000)*(N4<=G$4:G$6000);SI(D$4:D$6000<=M4;D$4:D$6000)));SI((A$4:A$6000=K4&L4)*(N4>=F$4:F$6000)*(N4<=G$4:G$6000);SI(D$4:D$6000<=M4;D$4:D$6000));0))

A+

Super, ça à l'air de fonctionner, merci.