trouver la valeur superieure la plus procheRésolu

Question

Bonjour,

J'ai une colonne de valeur numeriques decimales. Je souhaite faire un vlookup pour chaque valeur dans un tableau contenant 2 colonnes. 
Or, le vlookup classique me retourne la valeur associee a la valeur immediatement inferieure.
Je souhaite rechercher la valeur superieure la plus proche, et retourner la valeur associee.

petit exemple pour etre plus clair :

je recherche la valeur 24.4532 issue de ma colonne initiale, dans ce tableau :

8.32                1
12.4532        2
19.335          3
23.34             4
26.6789        5
34.6754        6

je souhaite que excel me retourne 5. or le vlookup va trouver 23.34 dans ce tableau, et me retourner 4.

De plus, je souhaiterais le faire avec une simple fonction excel, sans code vba (pas que je connais pas le vba, mais c'est plus adapte dans mon cas)

Merci d'avance !

NHenry · Accepted Answer

Bonjour

en utilisant mon code, une petite correction (par rapport à ton dernier post) : 
Remplacer 
if lRange.Value>pValue then 'Si on a trouver le premier nombre supérieur à celui recherché
par
if lRange.Value=>pValue then 'Si on a trouver le premier nombre supérieur à celui recherché

Dans Word, j'Excel. (juste pour la citation)
VB (6, .NET1&2), C++, C#.Net1

jrivet · Answer

Salut,
Tu ne te serais pas trompé dans les nombres dans ton exemple??? du coup je ne comprends plus rien ... :) pas réveillé certes, mais bon....

@+: Ju£i?n
Pensez: Réponse acceptée

outofbusiness · Answer

Salut Julien !
je te l'accorde, je ne suis peut etre pas assez clair. mais il n'y a pas d'erreur.
en fait je dois me referer a ce petit tableau de 2 colonnes. les valeurs decimales sont des valeurs de reference.
du coup, pour reprendre l'exemple cite, si dans ma colonne ou je fais a la base mon vlookup, je recherche 24.4532, je dois retourner la valeur associee a la valeur de reference superieure dans le tableau. soit ici : la valeur la plus proche superieure est : 26.6789, et excel doit me retourner 5.
mon vlookup actuel fait : vlookup(ma_valeur,mon_tableau,2,TRUE)
ceci me retourne 4, soit se refere a la valeur la plus proche inferieure.

As tu des idees ? ou au moins une disons ??

Merci !

NHenry · Answer

Bonjour

Tu peux recoder la fonction (dsl, mais les fonction Excel c'est pas ma plus grande connaissance) : 

Dans un module : 

public function MaVLookUp(byval pValue as variant,byval pCells as Range, byval pCol as long) as long
    dim lRange as range 'Déclaration
    
    for lRange in pCells 'Parcours de la liste
        if lRange.Value>pValue then 'Si on a trouver le premier nombre supérieur à celui recherché
            MaVlookUp=lRange.offset(0,pCol-lRange.Column).value 'On retourne sa valeur
            Exit function 'Et on sort
        end if
    Next lRange
    
    MaVlookUp="Non trouvé" 'Sinon on affiche "Non trouvé"
end function

Ensuite dans ta cellule : 
=MaVlookUp(24.4532;A2:A25;2)

n.b. : Code tapé directement, non exclu d'erreur

Dans Word, j'Excel. (juste pour la citation)
VB (6, .NET1&2), C++, C#.Net1

bigfish_le vrai · Answer

Salut,

supposont que ton tableau soit construit comme suit :

        A                 B                  C                  D
1    8.32                1                                 24.4532
2    12.4532        2
3    19.335          3
4    23.34             4
5    26.6789        5
6    34.6754        6

=INDIRECT("B" & (SOMME_SI(A1:A6,"<"&D1)+1))

cette formule suppose les valeurs en colonne sont classées en ordre croissant et que la premiere valeur ce trouve en ligne 1. Si par exemple la premiere valeur est en ligne 3 remplace +1 par +3. Comment ca marche ?

En faite cette formule compte le nombre de valeur inferieur a la valeur recherche puis on  ajoute 1 le (+1 dans la formule) car la valeur recherchée est la premiere plus grande valeur, ce qui dans ton exemple donne 5. Donc le 
SOMME_SI(A1:A6,"<"&D1)+1 aurait pu suffire mais ici je te donne une solution qui fonctionne meme si les valeur en colonne B ne sont pas une suite de valeurs en ordre croissant mais par exemple une suite de mots ou de jours etc...
essaye ceci :

       A                 B                  C                  D

1    8.32           Lundi                                24.4532

2    12.4532   Mardi

3    19.335      Mercredi

4    23.34         Jeudi

5    26.6789   Vendredi

6    34.6754   Samedi

ici la formule renvoi Vendredi

Vala, maintenant a toi de comprendre 

A+

bigfish_le vrai · Answer

... oups

il fallait lire :

cette formule suppose que les valeurs en colonne A sont classées en ordre croissant

A+

bigfish_le vrai · Answer

Salut,

une derniere chose :

pouquoi ta formule vlookup(ma_valeur,mon_tableau,2,TRUE) avec +1 a la fin n'est pas acceptable ?

tel que :

vlookup(ma_valeur,mon_tableau,2,TRUE)+1

A+

outofbusiness · Answer

bigfishlevrai> parce que si la valeur que je cherche coincide precisement avec une valeur de reference du tableau, je veux qu'excel renvoie la valeur correspondante et pas incrementee de 1.
si ma valeur recherchee est 26.6789 au lieu de 24.4532, je dois obtenir 5 et non 6...
c'est la que ca se complique un peu....

bigfish_le vrai · Answer

...

come on ! 

Tu mets le +1 dans une condition Si ! Si valeur recherchée = valeur trouvée alors 0 sinon +1

J'y crois pas 

A+

outofbusiness · Answer

merci pour la soluce !!
la solution de bigfish_levrai est pas mal (je la retiens ;-)), mais je dirais que j'ai une preference pour la solution de NHenry.
j'avais envie de coder un peu pour pas perdre la main (j'ai pas l'occasion de coder tous les jours), et puis une fonction personnalisee comme ca, ca epate les collegues qui ne connaissent pas trop le code ;-)

Trouver la valeur superieure la plus proche

10 réponses

Votre réponse

Discussions similaires