trouver une valeur proche dans une colonne

[Yann]

Bonjour,

voici mon problème : j'ai une serie de chiffres - no classée (et je ne peux le classer par ordre croissant/decroisant) dans une colonne. j'ai un chiffre que je dois trouver - le plus proche - dans cette colonne. Comment puis-je faire ?
(la fonction rechercheV ne fonctionne qu'avec des chiffres classés par ordre croissant)
Avec mes remerciements pour votre aide,

Yann

 

[Jean-Marie]

Bonjour

Une petite précision, suivant ton exemple, tu recherches 20,00%, dans ta liste de valeurs, la formule doit te retourner 20,30% ou 19,43%.

La valeur la proche et qu'elle soit toujours inférieure, ou la plus proche de la valeur cherchée qu'elle soit supérieure ou inférieure pas d'importance. ?

@+Jean-Marie

 

[Lord Nelson]

Salut Yann et Jean-Marie,

A supposer que tu cherches la valeur proche inférieure ou égale, voici une fonction qui pourras t'aider :

Function Valeur_Proche(VTest, Plage As Range) As Double
Dim CL As Range
Dim VP As Double, Ecart As Double, EcartRef As Double
EcartRef = 2 * 10 ^ 9
For Each CL In Plage
If CL.Value <= VTest Then
Ecart = VTest - CL.Value
If Ecart < EcartRef Then
EcartRef = Ecart
VP = CL.Value
End If
End If
Next
Valeur_Proche = VP
End Function

Bonne année 2005 à tous !
Horatio

 

[Jean-Marie]

Re...

Formule en feuille de calcul.

Pour une valeur toujours inférieure
=PETITE.VALEUR(B1:B19;NB.SI(B1:B19;"<"&B25))
Pour une valeur toujours supérieure
=PETITE.VALEUR(B1:B19;NB.SI(B1:B19;"<="&B25)+1)

Pour une valeur inférieure ou égale
=PETITE.VALEUR(B1:B19;NB.SI(B1:B19;"<="&B25))

Pour une valeur supérieure ou égale
=PETITE.VALEUR(B1:B19;NB.SI(B1:B19;"<="&B25)+1+(NB.SI(B1:B19;"="&B25)*1))
ou
=GRANDE.VALEUR(B1:B19;NB.SI(B1:B19;">="&B25))

Pour une valeur la plus proche inférieure ou supérieure
=B25+SI(ABS(PETITE.VALEUR(B1:B19;NB.SI(B1:B19;"<="&B25))-B25)>PETITE.VALEUR(B1:B19;NB.SI(B1:B19;"<"&B25)+1)-B25;PETITE.VALEUR(B1:B19;NB.SI(B1:B19;"<"&B25)+1)-B25;PETITE.VALEUR(B1:B19;NB.SI(B1:B19;"<="&B25))-B25)

D'autres variantes sont possibles avec les mêmes fonctions.

@+Jean-Marie

 

[Yann]

Avec tous mes remerciements,

Bonne année à tous,

Yann

 

[Yann]

En fait, je pensai reussir la suite de mes opérations et ... c'est un echec ! Donc nous avons réussit à trouver la valeur proche ... ET je dois trouver - sur la même ligne - le valeur correspondante à la dernière colonne. Dans l'exemple joint, la valeur à proche à chercher est 36, la valeur la plus proche est 35.13 donc la valeur finale chercher est 132.35 ! Ourggh !
Merci de votre aide !

Yann

 

[Jean-Marie]

Bonsoir Yann

La fonction RECHERCHEV possède un 4ème argument optionnel certe, mais d'une grande importance, qui spécifie le type de valeur recherchée proche ou identique.

Voici la formule modifiée.
=RECHERCHEV(B26;B2😀19;3;FAUX)

@+Jean-Marie

 

[Yann]

merci Jean-marie !

 

[williamss]

Re : Re: trouver une valeur proche dans une colonne

Re...

Formule en feuille de calcul.

Pour une valeur toujours inférieure
=PETITE.VALEUR(B1:B19;NB.SI(B1:B19;"<"&B25))
Pour une valeur toujours supérieure
=PETITE.VALEUR(B1:B19;NB.SI(B1:B19;"<="&B25)+1)

Pour une valeur inférieure ou égale
=PETITE.VALEUR(B1:B19;NB.SI(B1:B19;"<="&B25))

Pour une valeur supérieure ou égale
=PETITE.VALEUR(B1:B19;NB.SI(B1:B19;"<="&B25)+1+(NB.SI(B1:B19;"="&B25)*1))
ou
=GRANDE.VALEUR(B1:B19;NB.SI(B1:B19;">="&B25))

Pour une valeur la plus proche inférieure ou supérieure
=B25+SI(ABS(PETITE.VALEUR(B1:B19;NB.SI(B1:B19;"<="&B25))-B25)>PETITE.VALEUR(B1:B19;NB.SI(B1:B19;"<"&B25)+1)-B25;PETITE.VALEUR(B1:B19;NB.SI(B1:B19;"<"&B25)+1)-B25;PETITE.VALEUR(B1:B19;NB.SI(B1:B19;"<="&B25))-B25)

D'autres variantes sont possibles avec les mêmes fonctions.

@+Jean-Marie

Bonjour,

La dernière formule citée ci-dessus m'intéresse, mais par surprise quand je l'utilise pour voir entre 2 valeurs très proche laquelle est plus proche d'une autre voilà que parfois cela me dit : #NOMBRE!

=E52+SI(ABS(PETITE.VALEUR(D54😀55;NB.SI(D54😀55;"<=" &E52))-E52)>PETITE.VALEUR(D54😀55;NB.SI(D54😀55;"<"&E52)+1)-E52;PETITE.VALEUR(D54😀55;NB.SI(D54😀55;"<"&E52)+1)-E52;PETITE.VALEUR(D54😀55;NB.SI(D54😀55;"<="&E52))-E52)

Par exemple si J'ai dans E52 : -4,50

puis dans D54 : -4,50191795758293

et dans D55 : -4,5013325332986

alors la formule me donne comme résultat #NOMBRE! pourtant la valeur de D55 est la plus proche de E52 par rapport à D54.

Pourquoi avec certaine valeur cela fonctionne est pas avec d'autre comme ici ?? Et donc que faut-il faire pour que cela fonctionne toujours ??

Merci

Williams

 

[Victor21]

Re : trouver une valeur proche dans une colonne

Bonsoir, williamss.

En l'absence de fichier, j'ai du mal à rechercher l'erreur.
Mais l'important c'est que vous gardiez le sourire 🙂
Pour l'effacer, désactivez les smileys 🙂

Ps : si vous ajoutez une 3° valeur (ex : D54 😀56) aux deux testées actuellement, cela fonctionne-t'il mieux ?

 

[Victor21]

Re : Re: trouver une valeur proche dans une colonne

Re,

Avec :

=PETITE.VALEUR(D54😀55;NB.SI(D54😀55;"<"&B25)+1)-E52

vous tentez d'extraire la 3° plus petite valeur d'une plage qui n'en contient que 2.
Si une des deux valeurs est > et l'autre < à la valeur de comparaison, aucun message d'erreur.

C'est "l'évaluation de formule", onglet "Formules", groupe "Audit de formule" qui me l'a soufflé.

 

[Modeste geedee]

Re : Re: trouver une valeur proche dans une colonne

Bonsour®

=INDEX(PlageD1Dxx;EQUIV(MIN(ABS(PlageD1Dxx-E52));ABS(PlageD1Dxx-E52);0))

à valider par Ctrl+Maj+Entrée

 

[williamss]

Re : trouver une valeur proche dans une colonne

OK merci.

Avec vos formules j'ai maintenant tenté de voir en mettant dans la cellule F3 (cellule où j'analyse les données du 1er jour) la formule pour savoir sur une plage de 240 données par jours (données!AG2:AG242 données correspondant au premier jours) quelle est la valeur la plus proche de celle que j'écris en $B$10 . Et ceci pour les 365 jours de l'année donc il faudrait qu'en tirant la cellule F3 un décalage de 240 cellules pour la plage.

C'est à dire que pour une des 2 formule ci-dessous permettant de savoir quelle est la valeur la plus proche de celle que j'écris en $B$10

=$B$10+SI(ABS(PETITE.VALEUR(données!AG2:AG242;NB.SI(données!AG2:AG242;"<=" &$B$10))-$B$10)>PETITE.VALEUR(données!AG2:AG242;NB.SI(données!AG2:AG242;"<"&$B$10)+1)-$B$10;PETITE.VALEUR(données!AG2:AG242;NB.SI(données!AG2:AG242;"<"&$B$10)+1)-$B$10;PETITE.VALEUR(données!AG2:AG242;NB.SI(données!AG2:AG242;"<="&$B$10))-$B$10)

ou

={INDEX(données!AG2:AG242;EQUIV(MIN(ABS(données!AG2:AG242-$B$10));ABS(données!AG2:AG242-$B$10);0))}

il faudrait que la plage de valeurs de cette formule ( données!AG2:AG242 ) soit à chaque fois décalée de 240 lignes, de ligne en ligne.

C'est a dire qu'à la première cellule (F3) où est est mise cette formule c'est dans la plage AG2:AG241 qu'est recherchée la valeur la plus proche à celle mise dans la cellule $B$10 puis après à la cellule de dessous (donc F4) avec un décalage de 240 cellules pour la plage de recherche c'est donc dans la plage AG242:AG481 puis après pour F5 c'est dans la recherche s'effectue dans la plage 481:721, etc en se basant toujours la même valeur rentrée ($B$10).

Avec DECALER je n'arrive pas à faire ceci sur une de ces 2 formules comme je ne suis pas si fort sous Excel. Donc voici le fichier Document Cjoint avec dans la page données les valeurs que sur 4 jours au lieu de 365 ou 366 jours question du poids du fichier.

Comment peut t'on faire pour que cette formule puisse avoir sa plage de données décalées de 240 lignes quand on tire vers le bas la cellule où il y a la formule ??

Merci

Williams

 

[Jocelyn]

Re : trouver une valeur proche dans une colonne

Bonjour le Forum,
Bonjour a tout le monde

Un essai en fichier avec la fonction decaler a tester en F3 de la feuille calcul et a étirer vers le bas

=INDEX(données!AG:AG;PETITE.VALEUR(SI(ABS(DECALER(données!$AG$2;(LIGNES($1:1)-1)*240;;240)-$B$10)=MIN(ABS(DECALER(données!$AG$2;(LIGNES($1:1)-1)*240;;240)-$B$10));LIGNE(DECALER(données!$AG$2;(LIGNES($1:1)-1)*240;;240));9^9);1))

Cordialement

 

[williamss]

Re : trouver une valeur proche dans une colonne

Bonjour Jocelyn,

Ok, et merci bp car ce n'est pas facile de trouver certaines formules suivant ce qu'on recherche parfois.

😉

Williams