Microsoft 365 Recherche de prix en fonction de dates

[Juan]

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

 

[JHA]

Bonjour à tous,

Tu peux essayer cette formule matricielle

=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

 

[sylvanu]

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.

 

[Juan]

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

 

[Dudu2]

Bonjour à tous,

Je confirme le résultat de @sylvanu avec un code différent sur une fonction personnalisée.
Le tableau des prix passé en tableau structuré pour simplifier.

 

[Dudu2]

Je confirme qu'en formule il faut être Einstein. En VBA ce n'est pas non plus trivial.
Je laisse @sylvanu travailler sur le nouveau tableau que je ne comprends pas en première lecture ce qui encourage ma paresse notoire à passer à autre chose .

 

[Juan]

Salut à vous deux !
Déjà merci beaucoup
Je suis en train de dupliquer ta formule @Dudu2.
Je vous tiens au courant. mais j'ai bonne espoir !
Si j'avais vos skills !! lol

 

[sylvanu]

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]

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 !!

 

[Dudu2]

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]

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.

 

[Juan]

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]

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)

    '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

 

[Dudu2]

Juste pour info... Combien de temps pour les 200.000 lignes ?

 

[Juan]

Alors, ca marche super bien !

Je dirais 1 min environ. Ce qui est parfait !

Merci beaucoup à tous les deux ! Vous êtes des monstres !!

A+