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

Chris401

XLDnaute Accro

• 25 Octobre 2021

• #2

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 ?

 

Dernière édition: 25 Octobre 2021

Vote positif 0 Vote négatif

grotsblues

XLDnaute Occasionnel

• 25 Octobre 2021

• #3

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

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 25 Octobre 2021

• #4

Bonsoir grotsblues, Chris401,

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

Code:

=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+

 

Pièces jointes

• VALEUR PROCHE(1).xlsx

  13.6 KB · Affichages: 13

Vote positif 0 Vote négatif

grotsblues

XLDnaute Occasionnel

• 25 Octobre 2021

• #5

Bonsoir job75 et merci je teste et vous tiens au courant

 

Vote positif 0 Vote négatif

Chris401

XLDnaute Accro

• 26 Octobre 2021

• #6

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

 

Pièces jointes

• VALEUR PROCHE_v01.xlsx

  13.4 KB · Affichages: 5

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 26 Octobre 2021

• #7

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+

 

Vote positif 0 Vote négatif

Chris401

XLDnaute Accro

• 26 Octobre 2021

• #8

Re
@job75 exact !

 

Vote positif 0 Vote négatif

grotsblues

XLDnaute Occasionnel

• 26 Octobre 2021

• #9

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

 

Vote positif 0 Vote négatif

grotsblues

XLDnaute Occasionnel

• 26 Octobre 2021

• #10

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é

 

Vote positif 0 Vote négatif

grotsblues

XLDnaute Occasionnel

• 26 Octobre 2021

• #11

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.

 

Pièces jointes

• VALEUR PROCHE(1) (1).xlsx

  12.3 KB · Affichages: 2

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 26 Octobre 2021

• #12

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.

 

Pièces jointes

• VALEUR PROCHE(2).xlsx

  13.7 KB · Affichages: 8

Vote positif 0 Vote négatif

grotsblues

XLDnaute Occasionnel

• 26 Octobre 2021

• #13

Merci Job75 je teste demain et vous redis
Bonne nuit

 

Vote positif 0 Vote négatif

grotsblues

XLDnaute Occasionnel

• 27 Octobre 2021

• #14

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

 

Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc

• 28 Octobre 2021

• #15

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 :

VB:

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+

 

Pièces jointes

• VBA VALEUR PROCHE(1).xlsm

  21 KB · Affichages: 1

Dernière édition: 28 Octobre 2021

Vote positif 0 Vote négatif