Microsoft 365 Affichage valeur en fonction de la date la plus proche

[drenek35]

Bonjour,
Je souhaite faire une analyse sur des augmentations de tarif.
J'ai à ma disposition un ensemble d'articles avec des prix et des dates de mise à jour
Je souhaite obtenir pour un article le prix à la date la plus proche de la date de référence.

Vous trouverez ci-joint un fichier Excel pour étayer ma demande avec le résultat attendu.

En espérant que vous pourrez m'aider.
Je vous en remercie par avance,
Christian.

 

[chris]

Bonjour

Je ne récupère qu'une famille dans le cache du TCD...

 

[drenek35]

Chris,
Oui c'est normal car dans mon fichier de base, je n'ai qu'une seule famille.
Cela marchera également si j'ai plus d'une famille ?
Merci pour le travail accompli.
Je vais me pencher sur ce que tu as fait pour essayer de comprendre le fonctionnement pour pouvoir le reproduire.
Amicalement,

 

[chris]

RE

Je n'ai pas ta source donc j'ai seulement agi sur le TCD

 

[job75]

Bonsoir drenek35, chris,

Sur Excel 2019 je n'ai pas les fonctions UNIQUE et FILTRE.

Donc voyez le fichier joint et les formules matricielles en B3 C3 D3 E3.

La formule en B3 :

=INDEX(Tarifs[PA];EQUIV(MAX(SI(($A3=Tarifs[Ref])*(--Tarifs[date]<=B$1);--Tarifs[date]))&$A3;--Tarifs[date]&Tarifs[Ref];0))

Avec -- pas de souci pour les dates...

On obtient pas mal de #N/A, on peut bien sûr les éviter avec des SIERREUR..

A+

 

[job75]

Bonjour drenek35, chris,

Avec le fichier précédent le recalcul des formules des 4 colonnes B C D E prend 17 secondes.

Mais dans la 1ère feuille transformez les dates (textes) en vraies dates (nombres) en utilisant la commande Remplacer / => /.

On peut alors supprimer les -- qui faisaient la conversion et le recalcul des formules se fait en 3,7 secondes.

La validation d'une cellule comme B1 prend 4 fois moins de temps soit 0,9 seconde.

A+

 

[drenek35]

RE

Je n'ai pas ta source donc j'ai seulement agi sur le TCD

Bonjour Chris,
Le fichier est fonctionnel. Cela fonctionne avec plusieurs famille.

Saurais-tu me dire pourquoi les moyennes ne sont pas correctes dans le TCD ?
Je te remets en PJ le fichier ainsi que le fichier source m'ayant permis d'arriver à ce résultat.
Merci pour tes explications.

 

[drenek35]

Bonsoir drenek35, chris,

Sur Excel 2019 je n'ai pas les fonctions UNIQUE et FILTRE.

Donc voyez le fichier joint et les formules matricielles en B3 C3 D3 E3.

La formule en B3 :

=INDEX(Tarifs[PA];EQUIV(MAX(SI(($A3=Tarifs[Ref])*(--Tarifs[date]<=B$1);--Tarifs[date]))&$A3;--Tarifs[date]&Tarifs[Ref];0))

Avec -- pas de souci pour les dates...

On obtient pas mal de #N/A, on peut bien sûr les éviter avec des SIERREUR..

A+

Bonjour Job75
Merci pour votre aide. Cependant J'ai bien avancé déjà dans mon projet en incorporant le travail accompli par Chris.
Ce qui est pratique c'est de n'avoir rien à remodifier. La mise à jour de ma requête source avec de nouvelles valeurs sont pris en compte automatiquement dans le TCD via Powerquery.
Je vous remercie malgré tout pour le temps passé sur mon problème.

C'est grâce à des personnes comme vous, membres actifs d'Excel-Download, que nous progressons tous les jours un peu plus 🙂
Amicalement,

 

[chris]

Bonjour

Cela vient d'anomalies dans le fichier source : exemples
LUBE10356 : il y a en date du 17/08/2021 2 lignes de tarif
LUBE10423 : idem en date du 24/05/2019

24 codes en tout

c_art
LUBE10356
LUBE10423
LUBEDUBGREASEMO-0.4
LUBEGENITIUMMD1-30
LUBEG-SYNT-10 2T-1
LUBEISEAOUTBOARD4T10W30-1
LUBEJARDI4T10W-40-5
LUBEJARDI4TSAE30-2
LUBEJARDI4TSAE30-5
LUBEKAJOMHPSPECIALBRHG22
LUBEKITOPEVIDANGE
LUBEMULTI10W40-20
LUBEPOWERHP20W50-25
LUBEPPOMPEALEVIERSHUTTLE
LUBERACING5W40-20
LUBERACING5W40-5
LUBERACINGC15W30-220
LUBERACINGF5W30-VRAC
LUBERACINGP0W30-5
LUBERACINGR5W30-1
LUBERACINGV5W30-5
LUBETRANSFLUID-5
LUBETRANSLS75W90-5
LUBETRANSTO450W-20

Comme les tarifs sont différents, on ne peut éliminer de doublons et de ce fait ils subsistent dans la base PowerQuery.
La moyenne du TCD tient compte de ces n tarifs dans son calcul d’où l'écart avec une moyenne calculée en dehors sur 1 unique exemplaire.

Il faut donc que tu analyses ces cas afin de voir quelles règles appliquer pour éliminer ce qui ne va pas

 

[drenek35]

Bonjour

Cela vient d'anomalies dans le fichier source : exemples
LUBE10356 : il y a en date du 17/08/2021 2 lignes de tarif
LUBE10423 : idem en date du 24/05/2019

24 codes en tout

c_art
LUBE10356
LUBE10423
LUBEDUBGREASEMO-0.4
LUBEGENITIUMMD1-30
LUBEG-SYNT-10 2T-1
LUBEISEAOUTBOARD4T10W30-1
LUBEJARDI4T10W-40-5
LUBEJARDI4TSAE30-2
LUBEJARDI4TSAE30-5
LUBEKAJOMHPSPECIALBRHG22
LUBEKITOPEVIDANGE
LUBEMULTI10W40-20
LUBEPOWERHP20W50-25
LUBEPPOMPEALEVIERSHUTTLE
LUBERACING5W40-20
LUBERACING5W40-5
LUBERACINGC15W30-220
LUBERACINGF5W30-VRAC
LUBERACINGP0W30-5
LUBERACINGR5W30-1
LUBERACINGV5W30-5
LUBETRANSFLUID-5
LUBETRANSLS75W90-5
LUBETRANSTO450W-20

Comme les tarifs sont différents, on ne peut éliminer de doublons et de ce fait ils subsistent dans la base PowerQuery.
La moyenne du TCD tient compte de ces n tarifs dans son calcul d’où l'écart avec avec une moyenne calculée en dehors sur 1 unique exemplaire.

Il faut donc que tu analyses ces cas afin de voir quelques règles appliquer pour éliminer ce qui ne va pas

Bonjour Chris,
Merci pour ce retour, j'analyserais le fichier source lundi. Je te tiens au courant.
Bon week end.