Fonction Excel de recherche valeur proche

Fermé
Jeyjeyz - 14 janv. 2011 à 11:35
 Jeyjeyz - 4 févr. 2011 à 19:51
Bonjour,

Voici mon problème...en clair (?):

Colone A = valeurs X (ou -X) ..."Force"
Colone B = valeurs Y (ou -Y), liées a X(-X), ..."Raideur"
Cellule c = valeur Z (-Z)... "Fx_Mesurée"

Ce que je veux faire :
dans une Cellule d, "résultat": récupérer Y "raideur", suivant X "force" la plus proche de Z "force_mesurée"

J'ai essayé avec rechercheV, mais les valeurs recherché et de référence ne correspondent pas.

Quelqu'un a-t-il déjà résolu ce problème ? J'ai bien trouvé quelques réponses qui se rapprochent à mon problème... mais comme "rechercheV", j'ai du mal à retranscrire...

Merci à ceux qui se pencheraient sur ce soucis.

16 réponses

Vaucluse
Messages postés
26495
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
8 avril 2022
6 253
Modifié par Vaucluse le 14/01/2011 à 12:39
Bonjour
peut être une solution avec une colonne supplémentaires
à adapter:
de ligne 1 à ligne 100
A > Force
B > Raideur
C > mesure
D < =ABS(C1-A1) à tirer de 1 à 100

dans la cellule d"'édition la formule:

=INDEX(B1:B100;EQUIV(MIN(D1:D100);D1:D100;0);1)

crdlmnt




--
Demandons nous si nous ne sommes pas seuls à comprendre ce que l'on explique?
0
Merci Vaucluse.
Je me rends compte que je n'ai pas été assez explcite dans ma demande.
Car en fait, la Cellule C est différente sur +6000 lignes. Seul CA et CB ne changent pas.
Pour le coup, je ne peux pas faire tout le calcul. Peut-etre en incorporant la fonction MIN ?

pour résumer, la colone A me donne une fourchette de "Forces" et je veux que ma foncion situe Z ("Force mesurée") dans une fourchette et en retienne la "Raideur" liée.

Cordialement
0
Vaucluse
Messages postés
26495
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
8 avril 2022
6 253
14 janv. 2011 à 14:22
Alors c'est moi qui ne comprend pas:

dans une Cellule d, "résultat": récupérer Y "raideur", suivant X "force" la plus proche de Z "force_mesurée"

et que sont CA et CB ??

X la plus proche de Z est bien celle pour laquelle on obtient le plus petit écart en valeur absolue entre X et Z?

soit pour chaque ligne =(ABS(valeurZ - valeurX) donc =ABS(C1-A1)
qui peut sans trop charge le fichier,se placer sur 6000 lignes

la formule INDEX EQUIV va bien ensuite ressortir la valeur de Y en ligne avec ce mini trouvé dans la colonne B

Si cela ne va pas, mettez à disposition un tableau de votre problème sur:
http://www.cijoint.fr
et revenez placer ici le lien donné par le site.

A vous lire

Crdlmnt

0
merci de perdre du temps pour moi,
CA et CB sont en fait ColoneA et ColoneB.
Mais voici le tableau :
http://www.cijoint.fr/cjlink.php?file=cj201101/cijFVteoJ5.xls

Cordialement
0

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question
Vaucluse
Messages postés
26495
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
8 avril 2022
6 253
14 janv. 2011 à 14:54
On va d'abord essayez de comprendre

vos messages parlent:

d'une valeur C vide dans votre modèle?

d'un résultat en colonne D alors qu'elle est déjà remplie par les valeurs mesurées

quelle est la correspondance entre le tableau d'étalonnage qui s'arrête à, ligne 102 et le tableau de mesure qui va jusqu'à 5003

en fait, donc, sur quelles données se base le calcul, qui doit donner l'écart mini entre colonne A et colonne C ou D?????

*et quelle est la colonne ou se situe la valeur à ressortir




A vous lire

Crdlmnt


0
J'ai modifié le tableau pour séparer les efforts mesurées à l'étallonage de ceux mesurés sur site.
la colone 4 correspond aux valeurs "mesure" (Z) à comparer avec la colone 1 (X) "etallonage"
je dois trouver dans la colone 1 la valeur X la plus proche de Z.
pour ensuite extraire la valeur Y sur la même ligne de la valeur X choisie.
0
Vaucluse
Messages postés
26495
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
8 avril 2022
6 253
14 janv. 2011 à 18:22
Re
seconde tentative, (j'ai retiré la première qui était à coté de la plaque)
en voila une seconde dont je ne sais toujours pas si elle correspond à ce que vous attendez
Mais elle n'est pas très élégante, passant par deux colonnes de renvoi.

http://www.cijoint.fr/cjlink.php?file=cj201101/cijdWikZZd.xls

crdlmnt


0
Bonjour,
Désolé pour cette abs, j'ai eu un soucis d'accès internet.

Merci pour la réponse c'est ce que je cherchais.
0
tontong
Messages postés
2512
Date d'inscription
mercredi 3 février 2010
Statut
Membre
Dernière intervention
17 mai 2022
1 023
18 janv. 2011 à 15:08
Bonjour,
Je sais que Vaucluse ne me tiendra pas rigueur de dénoncer un petit bug dans son fichier. En effet une partie de la formule...EQUIV(MIN(G3:G5003);G3:G5003;0) pointe toujours vers la même cellule.
Dans le fichier ci-dessous j'ai délayé un peu en ajoutant 4 colonnes ce qui rend les formules plus lisibles.
https://www.cjoint.com/?0bso9DeqobP
0
Vaucluse
Messages postés
26495
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
8 avril 2022
6 253
18 janv. 2011 à 15:33
Bonjour tontong

je ne t'en voudrais surement pas, mais je ne comprend pas toute ta remarque...!
1°)je ne vois pas pourquoi la formule devrait changer de cellule puisqu'il s'agit de trouver la quelle, dans la liste Y correspond au minimum d'écart entre les valeurs de la liste A et les valeurs de la liste D
il n'y a donc pas lieu de cherche un tableau de la valeur cherché, une seule cellule suffit et renverra toujours la même chose
La valeur cherchée dans ton modèle étant celle de B pour la quelle A -D est minimum.
Il n'y en a qu'une

2°) par contre, où tu as raison, c'est dans la recherche de la valeur la plus proche en travaillant sur les valeurs encadrant la valeur cherchée car effectivement, une seule fonction RECHERCHE renvoi la valeur directement inférieure, alors qu'il est possible que la supérieure soit plus proche.

Si la colonne G chez toi renvoie la même valeur , c'est parce que la valeur cherchée est en F est toujours la même, mais ça, ça vient du relevé, pas des formules

Bien cordialement



0
tontong
Messages postés
2512
Date d'inscription
mercredi 3 février 2010
Statut
Membre
Dernière intervention
17 mai 2022
1 023
18 janv. 2011 à 16:48
Bonjour Vaucluse,
Je vais tenter d'expliquer ce j'ai vu sur ton fichier, je ne suis pas sûr d'y parvenir.
Le mini des écarts sur G3:G5003 est -4,7184785 en ligne 4336 et correspond à la valeur proche -9,86168785 qui est très loin de l'effort mesuré -39,0771
La valeur trouvée de 13,93605639, située en regard de -9,86168785 dans le tableau d'étalonnage, n'est donc pas la bonne.
L'essentiel reste la satisfaction du demandeur ;-)
0
Vaucluse
Messages postés
26495
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
8 avril 2022
6 253
18 janv. 2011 à 17:02
Ok tontong, la formule à perdu quelque chose en route que j'avais signalé, mais probablement oublié en cours de route, ou alors ce n'est pas le bon fichier, car j'étais convaincu d'avoir traité le sujet
Telle quelle est elle s'adresse en fait au minimum incluant les valeurs négatives

la formule est matricielle et c'est:
=RECHERCHEV(INDEX(F3:F5003;EQUIV(MIN(ABS(G3:G5003));ABS(G3:G5003);0);1);$A$3:$B$102;2;0)
a entrer avec ctrl+shift+enter

Il n'en reste pas moins que tu es plus près en traitant les valeurs encadrant la valeur cherchée.

crdlmnt

0
Bonjour,

Merci à vous deux... juste une petite question en passant : au final, entre vos données quelle est(ou sont) la(les) formule(s) à utiliser et dans quelle cellule ?

ne stressez pas, je vais essayer un peu tout ça et voir. C'est juste que ça serait plus clair dans mon petit crâne.

Cordialement
0
tontong
Messages postés
2512
Date d'inscription
mercredi 3 février 2010
Statut
Membre
Dernière intervention
17 mai 2022
1 023
19 janv. 2011 à 14:12
Bonjour,
En prenant comme base le fichier joint au post 10:
La colonne F donne la valeur inférieure à l'effort mesuré de la colonne D =RECHERCHEV(D3;$A$3:$A$102;1)
La colonne G donne la valeur supérieure à l'effort mesuré de la colonne D =INDEX($A$3:$A$102;EQUIV(RECHERCHEV(D3;$A$3:$A$102;1);$A$3:$A$102;1)+1;1)
La colonne H donne la valeur la plus proche de l'effort mesuré de la colonne D. En fait la colonne H choisit la valeur la plus proche entre les bornes déterminée en F et G. =SI(ABS(D3-F3)<=ABS(G3-D3);F3;G3)

La colonne I donne le résultat qui est la raideur en regard de l'effort le plus proche dans le tableau d'étalonnage. =INDEX($A$3:$B$102;EQUIV(H3;$A$3:$A$102;1);2)

Si un point reste obscur n'hésitez pas à poser des questions.
0
Vaucluse
Messages postés
26495
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
8 avril 2022
6 253
Modifié par Vaucluse le 19/01/2011 à 19:21
Bonsoir
tous
pour ma part, j'avoue avoir beaucoup de mal à traiter le problème des écarts négatifs et positif
Dans le fichier joint, deux façons de traiter le problème qui donnent deux résultats différents et je reste benêt sur laquelle choisir?
A moins qu'il n'y en ai aucune
le principe:
1° sélectionner la valeur la plus proche en dessous et en dessus de la valeur D
2° faire la différence des valeurs absolue en choisissant la plus proche de D
3° établir en F le résultat de cet écart
et c'est là que je butte
la différence est celle des valeurs absolue en colonne F ce qui donne aussi parfois des valeurs négatives qui pourrissent à mon avis le choix de l'écart mini (plus la valeur absolue du négatif est grande, plus il est mini)
j'ai donc bâti une seconde solution qui travaille avec la valeur absolue des différence des valeurs absolues...!

Une fois que la bonne colonne est choisie, les formules du petit tableau, recherche sur le même principe les valeurs proches sup et inf de D dans colonne A , choisit la plus proche de D est renvoi la valeur correspondante de B;

Mais à part ça, je ne sais plus quoi dire!!!!!
Tontong va surement jeter un oeil



http://www.cijoint.fr/cjlink.php?file=cj201101/cijlpglRWe.xls

Crdlmnt

Demandons nous si nous ne sommes pas seuls à comprendre ce que l'on explique?
0
tontong
Messages postés
2512
Date d'inscription
mercredi 3 février 2010
Statut
Membre
Dernière intervention
17 mai 2022
1 023
19 janv. 2011 à 20:43
Bonsoir Vaucluse,
J'ai à peu près identifié le malaise, je détaillerai demain.
En gros il ne faut pas travailler sur les écarts.
À bientôt.
0
Vaucluse
Messages postés
26495
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
8 avril 2022
6 253
20 janv. 2011 à 06:39
Bonjour

la nuit portant conseil, je pense que celui ci est correct
http://www.cijoint.fr/cjlink.php?file=cj201101/cij0zcWvDl.xls
en fait, il ne faut pas travailler sur les valeurs absolues, mais sur l'écart le plus proche de 0
Ca devrait marcher cette fois.

Cdlmnt



0
tontong
Messages postés
2512
Date d'inscription
mercredi 3 février 2010
Statut
Membre
Dernière intervention
17 mai 2022
1 023
20 janv. 2011 à 14:40
Bonjour à tous,
À Vaucluse:
C'était une nuit de pleine lune donc pleine de lumière(s).
Le fichier joint au #18 fonctionne bien et constitue une belle démonstration d'écriture de formules.
Hélas il ne donne qu'une valeur de la raideur qui correspond à la valeur mesurée présentant l'écart le plus faible par rapport à une valeur d'effort d'étalonnage.
La demande initiale réclamait une raideur en face de chacun des efforts mesurés.
Sauf si j'ai mal interprété:
Ce que je veux faire :
dans une Cellule d, "résultat": récupérer Y "raideur", suivant X "force" la plus proche de Z "force_mesurée"

Il me semble que la confusion est la suivante:
on cherche l'écart mini entre un effort mesuré et les 2 efforts d'étalonnages qui l'encadrent et non pas la valeur mini des écarts (sur toute la colonne) entre chaque effort mesuré et l'effort d'étalonnage proche.
Ouf je suis arrivé au bout ma phrase.mdr
J'espère que mon verbiage est compréhensible.
À bientôt.
0
Vaucluse
Messages postés
26495
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
8 avril 2022
6 253
Modifié par Vaucluse le 20/01/2011 à 15:42
Bonjour tontong
alors si c'est ce que tu dis, c'est plus facile, du moins je crois, il suffit de faire un recherchev sur chaque ligne avec la valeur la plus proche sup ou inf, détectée par l'écart en valeur absolue.

http://www.cijoint.fr/cjlink.php?file=cj201101/cijqRpDPoB.xls

Je pense que le demandeur doit un peu se prendre la tête avec tout ça, on ne le revoit plus.
Crdlmnt

Ps pour ma part j'avais compris qu'il s'agissait de trouver la ligne de la colonne D où l'écart était minimum par rapport à n'importe quelle valeur de A et de renvoyer la valeur de Y correspondante
0
tontong
Messages postés
2512
Date d'inscription
mercredi 3 février 2010
Statut
Membre
Dernière intervention
17 mai 2022
1 023
20 janv. 2011 à 16:27
Là nous sommes presque d'accord, il reste juste un petit bug:
Pour une valeur mesurée de -38,5022 la valeur d'étalonnage la plus proche est encore -38,51357863 et non pas -35,30746129.La raideur devrait encore être 12,828429.
La bascule doit intervenir pour la valeur moyenne -36,9 environ.

Il y effectivement longtemps que le demandeur a succombé à une overdose de "paracétamol"
0
Vaucluse
Messages postés
26495
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
8 avril 2022
6 253
Modifié par Vaucluse le 20/01/2011 à 16:44
pour ma part, ,j'en reste là en attendant qu'il se remette.
Bonne soirée, mon ami
0
Vaucluse
Messages postés
26495
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
8 avril 2022
6 253
20 janv. 2011 à 16:46
.... suite
après le dernier tube d'aspirine,correction de la formule qui comporte une grossière erreur (à force de brasser)
=RECHERCHEV(SI(ABS(RECHERCHEV($D3;VE;1)-D3)>ABS(INDEX(TE;EQUIV($D3;VE)+1;1)-D3);INDEX(TE;EQUIV($D3;VE)+1;1);RECHERCHEV($D3;VE;1));TE;2;0)
Manquait deux fois -D3 dans les deux premiers items!!!! :-(
cette fois, je ferme.
crdlmnt
0
Merci de votre aide ! Et désolé du temps passé avant de vous remercier... j'étais parti récolter d'autres forces mesurées :)
0