Microsoft 365 Recherche de prix en fonction de dates

Juan

XLDnaute Junior
Bonjour à tous,

Après mes recherches infructueuses, je me tourne vers votre aide.

Je m'explique :
  • J'ai besoin d'aller récupérer le prix d'une référence en fonction de la date de vente
  • Pour cela, j'ai donc ma table de réf / prix / date de modification
  • en revanche, mes formules index ou recherche n'ont pas l'air de bien marcher avec la notion de date
  • Vous trouverez donc un échantillon en PJ. Pour info, ma table de résultats fera entre 200k et 300k lignes (ca peut peut-être vous orienter dans vos solutions)
Merci beaucoup pour votre aide !
Jean
 

Pièces jointes

  • aide excel.xlsx
    12 KB · Affichages: 13

JHA

XLDnaute Barbatruc
Bonjour à tous,

Tu peux essayer cette formule matricielle
VB:
=SIERREUR(SI(MAX.SI.ENS(_!E:E;_!D:D;I4;E:E;">0";E:E;"<="&J4)=0;INDEX(F:F;EQUIV(I4&MIN.SI.ENS(_!E:E;_!D:D;I4;E:E;">="&J4);D:D&E:E;0))*1;INDEX(G:G;EQUIV(I4&MAX.SI.ENS(_!E:E;_!D:D;I4;E:E;">0";E:E;"<="&J4);D:D&E:E;0))*1);"")

JHA
 

Pièces jointes

  • aide excel.xlsx
    13.2 KB · Affichages: 0

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour Juan,
Joli casse tête.
Par formules j'ai séché ( piteusement ), alors en PJ un essai en VBA en espérant avoir couvert toutes les configurations.
Mais avec autant de lignes cela risque de ramer. Quoique qu'avec des formules cela ne doit pas non plus être rapide.
 

Pièces jointes

  • aide excel (1).xlsm
    19.6 KB · Affichages: 3

Juan

XLDnaute Junior
Bonjour Juan,
Joli casse tête.
Par formules j'ai séché ( piteusement ), alors en PJ un essai en VBA en espérant avoir couvert toutes les configurations.
Mais avec autant de lignes cela risque de ramer. Quoique qu'avec des formules cela ne doit pas non plus être rapide.
Salut Sylvanu,

Mais t'es trop fort !! Ca a l'air de pas mal marcher :)

J'ai l'impression que ca ne devrait pas trop de prendre de temps mais le fichier que j'ai joint était un exemple. dans mon fichier d'origine, j'utilise un "tableau" dans excel.

Penses-tu pouvoir me faire la même chose dans le nouveau fichier joint ?

Merci beaucoup :)
 

Pièces jointes

  • DB 2022 SALES ORDER AND RETURNS WITH CM.xlsb
    407.2 KB · Affichages: 5

sylvanu

XLDnaute Barbatruc
Supporter XLD
Re, Bonjour Dudu,
Dans la macro de Dudu le tableau s'appelle TableauPrix, dans votre fichier Pricing, mais le principe reste le même.
Juste une question: dans votre matrice la colonne H semble être "TTC" les colonnes E et F en "HT".
Si c'est vrai et qu'on a une date inférieure à la date minimum dans la matrice, il faut remonter dans ce cas la OldValue, mais ici on n'a que la valeur HT et pas la valeur TTC. Ou est ce une configuration qui n'est pas possible ?
 

Juan

XLDnaute Junior
Re @sylvanu ,

Pas sûr de comprendre de quel fichier tu parles.

Je vais répondre à ta (bonne) question en utilisant le fichier que je joint dans ma réponse (qui est un tout petit peu différent sur la colonne AX).

- Dans l'onglet "_", là où il y a les prix de vente, en effet, les prix sont en TTC
- Dans l'onglet "DATABASE", colonne AX "Price TTC", je vais donc chercher l'info dans l'onglet "_" (je le convertirai en HT dans une autre colonne)

- Si, comme tu l'écris, on a, dans "DATABASE", une date inférieur à la date minimum de la matrice de l'onglet "_", il faut bien remonter OldValue
- Egalement, si une référence de "DATABASE" n'est pas présente dans la matrice de l'onglet "_", on va récupérer la colonne AG de l'onglet "DATABASE"

J'ai tenté la formule de @Dudu2 avec 200k lignes, mais ca met une plombe...

Ton code permettrait-il d'accélérer la chose ?

Merci encore !!
 

Pièces jointes

  • DB 2022 SALES ORDER AND RETURNS WITH CM..xlsb
    366.7 KB · Affichages: 0

Dudu2

XLDnaute Barbatruc
Avec 200.000 lignes, certes c'est long.
Un Macro globale ira évidemment plus vite à condition de charger tout en table mémoire.
J'ai transformé la fonction personnalisée en fonction générique.
Et passé le tableau résultat en tableau structuré pour simplifier l'adressage.
 

Dudu2

XLDnaute Barbatruc
Petite modif pour initialiser à vide les prix au cas où ils seraient non valides.
Les noms de tableaux structurés et colonnes utilisés sont toujours paramétrés en constantes.
 

Pièces jointes

  • aide excel fonction complète.xlsm
    25.8 KB · Affichages: 1
Dernière édition:

Juan

XLDnaute Junior
Petite modif pour initialiser à vide les prix au cas où ils seraient non valides.
Les noms de tableaux structurés et colonnes utilisés sont toujours paramétrés en constantes.
Merci Dudu2 c'est top.

En faisant cela, j'écrase les formules des autres colonnes. Serait-il possible de ne copier que la colonne new price ? et donc de laisser les formules (s'il y en a) dans les autres colonnes ?

Merci beaucoup !
On est sur la fin :)
 

Dudu2

XLDnaute Barbatruc
Ah oui ok je vois. Désolé, c'est ce que j'aurais du faire dès le départ.

Il n'y a que cette partie finale qui change (si tu as déjà fait ta transposition sur ton tableau)
VB:
    'Copie des valeurs NewPrice de la Table Résultat sur les cellules du TableauRésultat
    For iRésultat = 1 To UBound(TabRésultat, 1)
        TabRésultat(iRésultat, 1) = TabRésultat(iRésultat, TableauRésultat_NoColonneNewPrice)
    Next iRésultat
    ReDim Preserve TabRésultat(1 To UBound(TabRésultat, 1), 1 To 1)
   
    Range(NomTableauRésultat).ListObject.ListColumns(TableauRésultat_NoColonneNewPrice).DataBodyRange.Value = TabRésultat
 

Pièces jointes

  • aide excel fonction complète.xlsm
    25.5 KB · Affichages: 1

Discussions similaires