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

drenek35

XLDnaute Nouveau
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.
 

Pièces jointes

  • Evolution prix.xlsx
    11.6 KB · Affichages: 16

drenek35

XLDnaute Nouveau
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,
 

job75

XLDnaute Barbatruc
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 :
Code:
=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+
 

Pièces jointes

  • Evolution prix(1).xlsx
    311.1 KB · Affichages: 8

job75

XLDnaute Barbatruc
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

XLDnaute Nouveau
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.

1634904921304.png
 

Pièces jointes

  • Evolution_prix3_PQ.xlsx
    123.5 KB · Affichages: 2
  • Source_Evolution_prix.xlsx
    73.4 KB · Affichages: 3

drenek35

XLDnaute Nouveau
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 :
Code:
=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

XLDnaute Barbatruc
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
 
Dernière édition:

drenek35

XLDnaute Nouveau
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.
 

Discussions similaires

Réponses
6
Affichages
109

Statistiques des forums

Discussions
312 195
Messages
2 086 077
Membres
103 111
dernier inscrit
Eric68350