Microsoft 365 Calculer et classer les factures en fonction de leur importance

[Ines99]

Bonjour le forum,

Je vous sollicite pour trouver une façon de représenter "type Pareto" le pourcentage de factures en fonction de leur importance.
J'aimerais pouvoir distinguer un pourcentage de facture.
Par exemple, si je souhaite traiter que 30% de factures, alors me les colorier ou les mettre sur une autre feuille ?
Ci-joint mon fichier exemple

Merci pour votre aide

Ines

 

[AtTheOne]

Bonjour à toutes & à tous, bonjour @Ines99
J'ai transformé ta liste des factures en Tableau Structuré ("TS_Factures")
Le Pourcentage recherché est dans la cellule nommée "Poids"
Une proposition avec une formule matricielle dynamique (et Lambda récursive)
La formule pour extraire les factures dont le montant cumulé est inférieur ou égal à un pourcentage du total des facture :

=LET(Tbt;TRIER(Tableau1;6;-1);
          tb;SI(Tbt="";"";Tbt);
          M;INDEX(Tbt;;6);
          nb;LIGNES(M);
          Plafond;SOMME(M)*Poids;
          Lbda;LAMBDA(me;i;Cumul;SI(i>nb;Cumul;me(me;i+1;SI(i=1;INDEX(M;1);ASSEMB.V(Cumul;INDEX(Cumul;i-1)+INDEX(M;i))))));
          Lbdb;LAMBDA(me;i;Cumulp;SI(i>nb;Cumulp;me(me;i+1;SI(i=1;INDEX(tb;1;7);ASSEMB.V(Cumulp;INDEX(Cumulp;i-1)+INDEX(tb;i;7))))));
          r;ASSEMB.H(tb;Lbdb(Lbdb;1;0));
FILTRE(r;Lbda(Lbda;1;0)<=Plafond))

(j'y ai ajouter le pourcentage cumulé la formule se trouve dans la cellule nommée "Extrait")

La formule de la mise en forme conditionnelle du TS :

=ESTNUM(EQUIV($D8&"|"&$I8&"|"&$J8;INDEX(Extrait#;;1)&"|"&INDEX(Extrait#;;6)&"|"&INDEX(Extrait#;;7);0))

Voir le fichier joint

À bientôt

 

[chris]

Bonjour,
@chris
Dans le fichier du #3, j'ai remarqué un truc bizarre, que je n'avais encore jamais vu...
...

Oui quand j'ai rouvert le fichier g vu qu'il y avait un problème

Mais néanmoins le 1ère méthode prend du temps pour le cumul ligne à ligne d'où ma recherche d'une autre méthode

 

[chris]

Bonjour à toutes & à tous, bonjour @Ines99
J'ai transformé ta liste des factures en Tableau Structuré ("TS_Factures")
Le Pourcentage recherché est dans la cellule nommée "Poids"
Une proposition avec une formule matricielle dynamique (et Lambda récursive)

Superbe formule.

Cependant je n'ai pas réussi à avoir un résultat avec 4000 lignes

 

[AtTheOne]

Re, Bonjour @chris

Cependant je n'ai pas réussi à avoir un résultat avec 4000 lignes

Je vais essayé chez moi ...
À bientôt

 

[chris]

Re,
Ou encore en VBA.
Dans cette PJ il suffit de sélectionner la feuille Pareto pour obtenir le résultat.
Sur mon PC avec XL2007 et 5000 lignes cela met 0.11 seconde.

Par curiosité j'ai comparé sur mon PC avec 365 après avoir copié les mêmes données dans les 2 classeurs (4248 lignes) :

• à l'ouverture du fichier comme au changement du choix du %, tu as le même temps de 0,063
• avec PQ j'ai 2,625 à l'ouverture mais seulement 0,019 au changement de choix

J'avais déjà remarqué que la 1ère exécution d'une requête est plus longue que ses ré-exécutions.

J'ai aussi testé en doublant le nombre lignes (quasi 8500), les re-exécutions PQ changent à peine de durée, VBA double approximativement

Mais cela reste une belle performance pour les 2 options ;-)

 

[sylvanu]

Bonjour Chris,

à l'ouverture du fichier comme au changement du choix du %, tu as le même temps de 0,063

Le VBA ne pèse rien, et n'est pas pris en compte par XL, donc normal qu'il n'y ait pas de différence.

avec PQ j'ai 2,625 à l'ouverture mais seulement 0,019 au changement de choix

Je pense que comme PQ est intégré à XL, à l'ouverture il prépare PQ pour être exécuté sur demande d'autant qu'il est très optimisé.
Ensuite évidemment en étant natif il est plus rapide que du VBA.

 

[Ines99]

Bonjour,
@chris
Dans le fichier du #3, j'ai remarqué un truc bizarre, que je n'avais encore jamais vu...
Lorsqu'on clique dans le résultat de la requête (onglet "Feuil3"), on devrait avoir le ruban supplémentaire "Requête". Or il n'apparait pas...
Si on affiche le volet "Données/Requêtes et connexions", et que l'on clique sur "Factures", c'est le tableau "Source" qui est sélectionné, et non le résultat...
Regarde la pièce jointe 1221879
Si on renomme la requête, on obtient un bug.
Dans ta version V2, le comportement de PQ est conforme.
Un peu bizarre, non? (peut-être l'origine du pb?)
Bon dimanche

Bonjour Cousinhub,
Merci pour cette remarque.
Effectivement, je ne comprennais pas quand j'ai lancé l'editeur Power Query
Je regarde le retour de Chris
Merci le forum

 

[Ines99]

Re,
Ou encore en VBA.
Dans cette PJ il suffit de sélectionner la feuille Pareto pour obtenir le résultat.
Sur mon PC avec XL2007 et 5000 lignes cela met 0.11 seconde.
( Pensez à valider les macros si ce n'est déjà fait )

Sylvanu,
Merci, je vous fais un retour
Iners

 

[AtTheOne]

Re,
Ouh la la ça a bougé depuis mon dernier message !
Je poste ce que j'ai fait et je regarde après chaque message...
Chez moi aussi, impossible d'avoir un résultat avec la formule du post#16, dès que le nombre de lignes devient conséquent, mon PC manque de ressource, les formules récursives c'est pratique mais gourmand !

Donc je suis passé par le vba avec quelques noms définis :
le code :

Sub Pareto()

     With sh_Factures
          PoidsF = .[Poids]
          TbBrut = .[Ts_Factures]
          Clef1 = .[TS_Factures[Montant Facture]]
          Clef2 = .[TS_Factures[Date]]
     End With
    
     CumulPc = 0
     tb = WorksheetFunction.SortBy(TbBrut, Clef1, -1, Clef2, 1)
     For i = 1 To UBound(tb)
          CumulPc = CumulPc + tb(i, 7)
          J = i
          If CumulPc > PoidsF Then Exit For
     Next i
    
     CumulPc = 0
     ReDim TbPareto(1 To J, 1 To 8)
     For i = 1 To J
          TbPareto(i, 1) = CDate(tb(i, 1))
          For k = 2 To 7
               TbPareto(i, k) = tb(i, k)
          Next k
          CumulPc = CumulPc + TbPareto(i, 7)
          TbPareto(i, 8) = CumulPc
     Next i
     With Sh_Pareto.[TS_Pareto]
          .ClearContents
          .ListObject.Resize .Offset(-1).Resize(2)
          .ListObject.Resize .Offset(-1).Resize(J + 1)
     End With
     sh_Factures.[TS_Pareto].Value = TbPareto
    
End Sub

il est appeler par l'événement WorksheetChange de la feuille "Factures" :

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Address = Me.[Poids].Address Or _
        Not Intersect(Target, Me.[Ts_Factures]) Is Nothing Then
          Pareto
     End If
End Sub

Et la formule de la mise en forme conditionnelle :

=ESTNUM(EQUIV($D8&"|"&$I8&"|"&$J8;P_Date&"|"&P_Montant&"|"&P_PourCent;0))*(INDEX(P_Cumul;EQUIV($D8&"|"&$I8&"|"&$J8;P_Date&"|"&P_Montant&"|"&P_PourCent;0))<Poids)

Avec 12000 ligne cela reste rapide
Lorsque le % atteint est inférieur au % demandé, dans la feuille "Pareto" j'affiche la ligne suivante.
La mise en forme conditionnelle, elle, respecte strictement la règle (Cumul % inférieur ou égal au % demandé).

Voir le fichier joint

 

[Ines99]

Bonjour AtTheOne,

Merci à vous.
Cependant, il y'a des lenteurs et 1 bug
Ines

 

[Ines99]

Bonjour Chris,
Suite au test à l'instant
Ines

 

[Ines99]

Bonjour AtTheOne,

Voici après le test avec ma data
Ines

 

[AtTheOne]

re

il y'a des lenteurs et 1 bug

Peux-tu aller dans le débogueur et me joindre la capture d'écran de la ligne incriminée ?
En attendant une nouvelle version avec le graphique Pareto inclus.

Pour les lenteurs, c'est surtout quand il y a beaucoup de lignes filtrées, avec les données du fichier et un poids de 33% cela reste raisonnable. mais c'est plus lent que la version de @sylvanu (sans pourcentage cumulé, mais ce ne serait pas bien long à ajouter).
Une autre raison, c'est qu'en filtrant avec la WorksheetFunction.SortBy les dates sont converties, je ne sais pas pourquoi, en texte et que je suis obligé des les reconvertir en dates.

Combien as-tu de lignes dans ton fichier réel ?

JE VIENS DE VOIR TON POST#27 :
Il faut transformer ta plage de données en tableau structuré et le nommé "TS_Factures"

Voir le fichier joint

 

[Ines99]

Bonjour Sylvanu

Voici le retour suite à mon test, il y'a aussi un bug pour depassement capacité
Ines

 

[AtTheOne]

Re,
@Ines99,
Peux-tu nous envoyer tes data avec seulement les dates et les montants que l'on comprenne d'où viennent ces bugs sur les trois solutions différentes ?
À bientôt