Microsoft 365 Recherche de prix en fonction de dates

J

JHA

XLDnaute Barbatruc

• 20 Avril 2022

• #2

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

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 20 Avril 2022

• #3

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

Vote positif 0 Vote négatif

J

Juan

XLDnaute Junior

• 20 Avril 2022

• #4

sylvanu à dit:

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.

Cliquez pour agrandir...

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

Vote positif 0 Vote négatif

Dudu2

XLDnaute Barbatruc

• 20 Avril 2022

• #5

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.

 

Pièces jointes

• aide excel.xlsm

  20.4 KB · Affichages: 6

Vote positif 0 Vote négatif

Dudu2

XLDnaute Barbatruc

• 20 Avril 2022

• #6

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 .

 

Vote positif 0 Vote négatif

J

Juan

XLDnaute Junior

• 20 Avril 2022

• #7

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

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 20 Avril 2022

• #8

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 ?

 

Vote positif 0 Vote négatif

J

Juan

XLDnaute Junior

• 20 Avril 2022

• #9

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

Vote positif 0 Vote négatif

Dudu2

XLDnaute Barbatruc

• 20 Avril 2022

• #10

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.

 

Vote positif 0 Vote négatif

Dudu2

XLDnaute Barbatruc

• 20 Avril 2022

• #11

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: 20 Avril 2022

Vote positif 0 Vote négatif

J

Juan

XLDnaute Junior

• 20 Avril 2022

• #12

Dudu2 à dit:

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.

Cliquez pour agrandir...

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

 

Vote positif 0 Vote négatif

Dudu2

XLDnaute Barbatruc

• 20 Avril 2022

• #13

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

Vote positif 0 Vote négatif

Dudu2

XLDnaute Barbatruc

• 20 Avril 2022

• #14

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

 

Vote positif 0 Vote négatif

J

Juan

XLDnaute Junior

• 20 Avril 2022

• #15

Alors, ca marche super bien !

Je dirais 1 min environ. Ce qui est parfait !

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

A+

 

Vote positif 0 Vote négatif