Macro ou formule pour trouver la valeur la plus proche

Bonjour,

J'ai deux onglets => le 1er avec Ref - Prix et Lancement en fonction du prix (ci-dessous). Je voudrais trouver avec une formule ou macro, la qté de lct idéal en fonction du prix le plus proche (2ème tableau ci-dessous). Par exemple pour bananes, ça doit être 2400. Merci de votre aide.

Références	Prix	Lct
Pommes	1,8	200
Pommes	1,52	300
Pommes	1,68	400
Pommes	1,54	500
Pommes	1,75	600
Pommes	1,2	700
Pommes	1,36	800
Pommes	2,54	900
Poires	1,96	1000
Poires	1,24	1100
Poires	1,65	1200
Poires	1,66	1300
Poires	2,59	1400
Poires	3,65	1500
Bananes	3,52	1600
Bananes	1,2	1700
Bananes	1,11	1800
Bananes	0,98	1900
Bananes	5,41	2000
Bananes	2,32	2100
Bananes	2,75	2200
Bananes	2,85	2300
Bananes	2,69	2400

Références	Prix	Lct
Pommes	1,42
Poires	1,33
Bananes	2,71

Bonjour

Vous ne précisez pas "le plus proche" = dans l'ordre croissant, décroissant ou en valeur absolue
vous ne précisez pas non plus votre version d'Excel (en particulier 365 ou antérieur)

Pour la valeur absolue, je vous propose une solution sans macro VBA ni formule mais avec une requête Power Query

Vous chargez vos deux tables que j'ai nommées "Table_Source" et "Table_Recherche"
Vous les fusionnez sur la référence, vous développez le prix cible de la "Table_Recherche"
Vous ajoutez une colonne pour calculer l'écart entre les deux prix puis la valeur absolue
Vous regroupez par référence en calculant l'écart minimum et en récupérant "toutes les lignes"
puis développez les colonnes et filtrer sur l'écart = l'écart mini (il peut y avoir plusieurs réponses)

environ 2 mn pour créer la requête soit bien moins de temps que pour rédiger cette réponse.
le temps de calcul est immédiat et sûrement très rapide même avec une grosse volumétrie de références.

Code:

---

1 2 3 4 5 6 7 8 9 10 11

let Source = Table.NestedJoin(Table_Source, {"Références"}, Table_Recherche, {"Références"}, "Table_Recherche", JoinKind.LeftOuter), #"Table_Recherche développé" = Table.ExpandTableColumn(Source, "Table_Recherche", {"Prix"}, {"Prix.1"}), #"Soustraction insérée" = Table.AddColumn(#"Table_Recherche développé", "Soustraction", each [Prix] - [Prix.1], type number), #"Valeur absolue calculée" = Table.TransformColumns(#"Soustraction insérée",{{"Soustraction", Number.Abs, type number}}), #"Lignes groupées" = Table.Group(#"Valeur absolue calculée", {"Références"}, {{"Min", each List.Min([Soustraction]), type number}, {"Données", each _, type table [Références=nullable text, Prix=nullable number, Lct=nullable number, Prix.1=nullable number, Soustraction=number]}}), #"Données développé" = Table.ExpandTableColumn(#"Lignes groupées", "Données", {"Références", "Prix", "Lct", "Soustraction"}, {"Références.1", "Prix", "Lct", "Soustraction"}), #"Lignes filtrées" = Table.SelectRows(#"Données développé", each [Références.1] = [Références] and [Soustraction] = [Min]), #"Autres colonnes supprimées" = Table.SelectColumns(#"Lignes filtrées",{"Références", "Prix", "Lct"}) in #"Autres colonnes supprimées"

---

Stéphane

Merci pour ta réponse.

N'ayant jamais utilisé Power Query, je te mets le fichier excel. Pour le plus proche, peu importe la solution, il y a des milliers de lignes donc qu'on soit en valeur absolue, croissant ou décroissant, c'est pas bien grave.

Citation:

---

Envoyé par Roran46

N'ayant jamais utilisé Power Query,

---

Eh bien il faut un début à tout, c'est très utile, le gain de temps est vraiment important quand on manipule des données

Citation:

---

Envoyé par Roran46

je te mets le fichier excel.

---

Si j'insère la requête tu n'auras rien appris, ça ne sert à rien
je vais également dire que l'utilisation des tableaux structurés est obligatoire dans tous fichiers Excel

Citation:

---

Envoyé par Roran46

Pour le plus proche, peu importe la solution, il y a des milliers de lignes donc qu'on soit en valeur absolue, croissant ou décroissant, c'est pas bien grave.

---

Etonnant car avec les quelques données d'exemple les réponses sont bien différentes, mais bon...


Sinon avec Excel 365 les fonctions EQUIX, FILTRE, TRI.... seraient bien pratique.
quelle est la version d'excel ?

cette question pourrait passer pour un cas d'école. Je peux voir plusieurs possibilités de réponses

1 avec des formules plus ou moins simples, l'inconvénient, il faut ajouter des colonnes et réajuster les lignes...
Ca pourrait être un recherchev qui renvoi la valeur et effectue le calcul de la distance pour chaque valeur renvoyée puis utilisation de max ou min. Bref, combinaison de plusieurs formules, fastidieux mais ça fait le job

2 en sql, quelque chose comme select abs min dans une sous requête. Sql query ou définir la requête sql dans vba

3 power query ou power pivot, je ne connais pas bien ces modules mais c'est une option supplémentaire

4 vba, en une seule boucle ou en 2 boucles avec une fonction (mémoire pile ou stack) pour optimiser le traitement sur des millions de lignes

En ce qui me concerne, ca serait fait en vba en 20-30 mn juste pour le traitement sans effet esthétique. Après une fois que le code est fiable et optimisé, ca sera automatique (à l'ouverture du classeur avec un bouton...ou automatiquement sans bouton avec un évènement ouverture du classeur ou feuille active...)

Salut.

Je tombe sur ce poste avec l'envie d'y répondre...

Un INDEX-EQUIV couplé à un MAX fonctionne pas trop mal, je crois (A valider en matricielle pour les versions autres que Excel 365)

Pièce jointe 607462


PS: N'oublie pas de mentionner ta version d'Excel car les solutions peuvent être différentes en fonction de la version.