XL 2010 Récupérer la valeur la plus proche après une recherchev EXCEL 2007

[grotsblues]

Bonsoir le forum

Je souhaiterai savoir si on peut récupérer une valeur la plus proche après une recherchev.

exemple feuille 1
19583 toto 165 29360.71 19583165
exemple feuille 2
19583 toto 165 29441.28 19583165
Je souhaiterai qu'elle récupère les 29441.28.

Je joint un fichier pour la compréhension
Merci pour votre retour

 

[Chris401]

Bonsoir
Quand tu dis "Récupérer la valeur la plus proche", ce n'est pas suffisant.
En effet, pour le deuxième TOTO, la valeur la plus proche de 51 620.64 est 29 441.28 et tu veux "Inconnu".
Donc plus proche de x en plus ou x en moins ?
Ou simplement supérieure ?

 

[grotsblues]

Bonsoir merci pour votre réponse, de faite je n'ai pas mentionné supérieur ou inferieur à 100 euro

 

[job75]

Bonsoir grotsblues, Chris401,

Voyez le fichier joint, en Feuil1!F2 la formule matricielle est impressionnante :

=SIERREUR(INDEX(Tableau1[MONTANT];EQUIV(PETITE.VALEUR(SI((Tableau1[CONCAT]=E2)*(ABS(Tableau1[MONTANT]-D2)<100);ABS(Tableau1[MONTANT]-D2));1);SI(Tableau1[CONCAT]=E2;ABS(Tableau1[MONTANT]-D2);0)));"INCONNU")

En Feuil2 le tableau source est structuré (menu Insertion => Tableau).

A+

 

[grotsblues]

Bonsoir job75 et merci je teste et vous tiens au courant

 

[Chris401]

Bonjour @grotsblues , @job75
Autre formule et Format Personnalisé

 

[job75]

Bonjour Chri401, le forum,

Votre formule n'est pas au point, pour tester entrez 29400 en D4 de Feuil2.

F2 de Feuil1 affiche "Inconnu" alors que le résultat 29 441,28 doit rester inchangé.

A+

 

[Chris401]

Re
@job75 exact !

 

[grotsblues]

Bonsoir le forum et merci job 75
Je viens de tester la formule sur mon fichier de travail, j'ai redimensionner le tableau1 car j ai 10040 lignes sur la feuille1 et 86040 lignes sur la feuille2. Et descendu la formule, le souci est
-le résultat obtenu est un montant qui n'existe pas dans la feuille 2
-elle répète ce résultat plusieurs fois
-et la mise à jour est un peu longues
Est ce dû au nombre de lignes
J'espère être clair

Merci pour votre aide

 

[grotsblues]

Rectification le montant de 5952.96 existe bien sur la feuille2, cette valeur se trouve sur les 10 dernières lignes du tableau1 soit 86030 à 86040 lignes
Désolé

 

[grotsblues]

Du coup j'ai modifie le fichier excel de job75 et le résultat n'est plus du tout le même. Je ne comprends plus rien.

 

[job75]

Bonsoir grotsblues,

Oui il y avait une parenthèse fermante du dernier SI mal positionnée à la fin de la formule en F2.

C'et corrigé dans ce fichier (2).

Merci d'avoir testé, on fait facilement des erreurs avec des formules aussi longues.

Bonne nuit.

 

[grotsblues]

Merci Job75 je teste demain et vous redis
Bonne nuit

 

[grotsblues]

Bonsoir job75 cela fonctionne très bien, juste que le calcul est un peu long du faite que mon fichier contient 89000 lignes.
Je ne sais pas si en mettant la formule dans du vba cela pourrait peut être aller plus vite.
Merci beaucoup

 

[job75]

Bonjour grotsblues, le forum,

Le VBA ne sera pas plus rapide que la formule matricielle du post #12.

Voyez le fichier .xlsm joint et cette fonction VBA :

Function PlusProche(montant#, concat$, Pmontant As Range, Pconcat As Range)
Dim Tmontant, Tconcat, mini#, i&, dif#
Tmontant = Pmontant.Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
Tconcat = Pconcat.Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
mini = 1E+308
For i = 1 To UBound(Tmontant)
    If Tconcat(i, 1) = concat Then
        dif = Abs(Tmontant(i, 1) - montant)
        If dif < 100 Then If dif < mini Then mini = dif: PlusProche = Tmontant(i, 1)
     End If
Next
If mini = 1E+308 Then PlusProche = "Inconnu"
End Function

Le recalcul des 5 formules se fait chez moi en 1,8 millième de seconde ici comme au post #12.

Vérifiez sur votre fichier de 89000 lignes.

A+