Peoblème sumproduct en vba

[KIM]

Bonjour le forum, bonjour les ami(e)s,
J'ai besoin de votre aide pour finaliser la génération automatique d'un tableau de bord.
Je créé ce tableau de bord avec des formules sommeprod sans problème.
Avec du vba, je n'ai pas réussi à retrouver les bons résultats vec la formule sumproduct.

La même formule fonctionne manuellement (voir fichier joint, onglet Recap, col K) par contre sous vba elle ne fonctionne pas.
J'ai essayé plusieurs codes sans résultat.

Merci d'avance pour votre aide.
KIM

 

[Dranreb]

Re : Peoblème sumproduct en vba

Bonjour.

Il ne faut pas de SsGroup pour des données qui ne font pas l'objet d'un For Each, et un .Id ne peut s'obtenir qu'après celui ci.
Si ce sont des informations à rapatrier de la dernière ligne d'un Id (en supposant qu'elles y soient identiques au autres précédentes dans ce même Id), je pense qu'elles sont encore disponible après le Next Détail dans le tableau contenu dans ce Variant Détail.
Il faudrait donc à la fin des affectation sur le modèle: Tbl(L, 2) = Détail(2)

 

[Dranreb]

Re : Peoblème sumproduct en vba

Apparamment je me suis trompé, Détail n'est pas gardé après exécution du dernier Next Détail.
Bon ben alors, qu'à cela ne tienne, et si le premier trouvé vous va, faisons comme ça :

Public Sub GrouperELEC3()
Dim PlgSrc As Range, AnMin&, AnMax&, Tbl(), N&, L&, _
   Dpt As SsGroup, Id As SsGroup, An As SsGroup, Som#, Détail, LePremId()
   
Set PlgSrc = PlgUti(Feuil1.Rows(5))
AnMin = WorksheetFunction.Min(PlgSrc.Columns(15))
AnMax = WorksheetFunction.Max(PlgSrc.Columns(15))
ReDim Tbl(1 To 1000, 1 To AnMax - AnMin + 7)
Tbl(1, 1) = "DPT": Tbl(1, 2) = "SIT": Tbl(1, 3) = "ID": Tbl(1, 4) = "PER": Tbl(1, 5) = "LIB": Tbl(1, 6) = "SRC"
For N = AnMin To AnMax: Tbl(1, N - AnMin + 7) = N: Next N
L = 1
For Each Dpt In GroupOrg(PlgSrc, 1, 3, 15)
   For Each Id In Dpt.Contenu
      LePremId = Id.Contenu(1).Contenu(1)
      L = L + 1
      Tbl(L, 1) = Dpt.Id
      Tbl(L, 2) = LePremId(2)
      Tbl(L, 3) = Id.Id
      Tbl(L, 4) = LePremId(4)
      Tbl(L, 5) = LePremId(5)
      Tbl(L, 6) = LePremId(10)
      For Each An In Id.Contenu
         Som = 0
         For Each Détail In An.Contenu
            Som = Som + Détail(14): Next Détail
         Tbl(L, An.Id - AnMin + 7) = Som: Next An
      Next Id, Dpt
Feuil2.[A4].Resize(500, 20).ClearContents
Feuil2.[A4].Resize(UBound(Tbl, 1), UBound(Tbl, 2)) = Tbl
End Sub

 

[KIM]

Re : Peoblème sumproduct en vba

Re bonjour le fil,
@Dranreb,
Merci de ta contribution et de ton aide. Après un test rapide, je pense qu'il manque un niveau de regroupement qui est le service SRC (col 10).
Les dépenses doivent être calculées par DPT, ensuite SRC (col J) et en finale par ID.
Et peut être pour une meilleure lisibilité du groupement, mettre ces 3 colonnes en premier dans la feuille Recap.

Merci d'avance
KIM

 

[Dranreb]

Re : Peoblème sumproduct en vba

Ah, bien sûr, il faut un SsGroup pour toute donnée devant donner lieu à un regroupement, et il faut un For Each correspondant au bon endroit. Donc d'après ce que vous dites, Src As SsGroup, 10 ajouté après 1 au GroupOrg, For Each Src In Dpt.Contenu après le For Each Dpt, et pour Id ça deviente For Each Id In Src.contenu. Plus loin alors: Tbl(L, 6) = Src.Id peut être utilisé à la place de LePremId(10) pour plus de clarté.

Alors. Tant qu'à mettre en tête les Id, au lieu de les mettre en colonnes il serait tout à fait possible de créér juste des lignes spéciales après chaque For Each et ne plus les répéter ensuite à chaque ligne :

Public Sub GrouperELEC3()
Dim PlgSrc As Range, AnMin&, AnMax&, Tbl(), N&, L&, _
   Dpt As SsGroup, Src As SsGroup, Id As SsGroup, An As SsGroup, Som#, Détail, LePremId()
   
Set PlgSrc = PlgUti(Feuil1.Rows(5))
AnMin = WorksheetFunction.Min(PlgSrc.Columns(15))
AnMax = WorksheetFunction.Max(PlgSrc.Columns(15))
ReDim Tbl(1 To 1000, 1 To AnMax - AnMin + 7)
Tbl(1, 1) = "DPT": Tbl(1, 2) = "SRC": Tbl(1, 3) = "ID": Tbl(1, 4) = "SIT": Tbl(1, 5) = "PER": Tbl(1, 6) = "LIB"
For N = AnMin To AnMax: Tbl(1, N - AnMin + 7) = N: Next N
L = 1
For Each Dpt In GroupOrg(PlgSrc, 1, 10, 3, 15)
   L = L + 1: Tbl(L, 1) = "Département " & Dpt.Id
   For Each Src In Dpt.Contenu
      L = L + 1: Tbl(L, 2) = "Source " & Src.Id
      For Each Id In Src.Contenu
         LePremId = Id.Contenu(1).Contenu(1)
         L = L + 1
'         Tbl(L, 1) = Dpt.Id
'         Tbl(L, 2) = Src.Id
         Tbl(L, 3) = Id.Id
         Tbl(L, 4) = LePremId(2)
         Tbl(L, 5) = LePremId(4)
         Tbl(L, 6) = LePremId(5)
         For Each An In Id.Contenu
            Som = 0
            For Each Détail In An.Contenu
               Som = Som + Détail(14): Next Détail
            Tbl(L, An.Id - AnMin + 7) = Som: Next An
         Next Id, Src, Dpt
Feuil2.[A4].Resize(1000, 20).ClearContents
Feuil2.[A4].Resize(L, UBound(Tbl, 2)) = Tbl
End Sub

Ce serait plus lisible, non ?
Voulez pas des totaux et sous totaux aussi, par hasard ? Faciiile…

 

[KIM]

Re : Peoblème sumproduct en vba

Bonsoir Dranreb et le fil,
Merci encore. Je regarderai demain matin.
Ta proposition pour les totaux m'a fait réfléchir sur la structure de l'onglet Recap.
Remarques importantes : Les SRC (col 10) sont complètement indépendant les uns des autres. C'est la source de financement. On ne peut faire des sommes que pour un même SRC. Chaque SRC est répartie sur des DPT. Pour chaque DPT il y a 1 ou plusieurs ID.
Pour faciliter les totaux et les sous-totaux, je pense qu'il est nécessaire que les colonnes soient dans l'ordre suivant :
SRC, DPT, ID, les autres données SIT, PER, LIB et ensuite les années.
Actuellement nous avons des sous-totaux par année pour chaque.
Est-il possible d'avoir pour chaque SRC des sous-totaux par DPT et par année et des totaux par SRC et par année ?
Ces totaux et sous-totaux peuvent-ils être dans un autre tableau recap ?
Merci pour ta compréhension et ton aide.
Bonne soirée
KIM

 

[Dranreb]

Re : Peoblème sumproduct en vba

Je pense que vous devriez maintenant comprendre comment on doit explorer une collection rendue par la fonction GroupOrg convenablement paramétrée en conséquence, et qu'il ne vous reste donc qu'à adapter tout ça à vos vrais besoin.
Pour calculer des sous totaux il suffit de prévoir des tableaux pour les accueillir, le remettre à 0 avant un For Each, y additionner les valeurs trouvées avant le Next qui lui correspond, puis, derrière, les affecter aux postes correspondants d'une nouvelle ligne du tableau de sortie.
Vous pouvez toujours encore me consulter si vous avez de légères difficultés de mise au point.
Oui, il serait possible de remplir en même temps un 2ième tableau de sortie. Il lui faudrait un autre nom que le premier, évidemment, et un autre L pour suivre son remplissage (Lr par exemple comme Ligne récap.)

 

[KIM]

Re : Peoblème sumproduct en vba

Re-Bonsoir Dranreb & le fil,
J'essaye de comprendre le code pour l'adapter à mon besoin.
Pouvez-vous m'expliquer le rôle de la variable LePremId() et son utilisation ?
Que veut dire : LePremId = Id.Contenu(1).Contenu(1) ?
Pourquoi 2 fois seulement Contenu(1) ?

A quoi correspond An, Id.contenu et An.contenu dans :
For Each An In Id.Contenu
Som = 0
For Each Détail In An.Contenu

Merci d'avance
Bonne soirée
KIM

 

[Dranreb]

Re : Peoblème sumproduct en vba

Parce que Id.Contenu(1) ce n'est que le premier élément de la collection Contenu du SsGroup Id en cours.
Comme ce n'est pas le dernier critère il s'agit lui même d'un SsGroup (comme ceux qu'on explore ensuite à l'intérieur avec An)
C'est ce dernier là seulement (il n'y a plus d'autre argument de regroupement derrière, voilà pourquoi il n'en faut pas davantage) qui contient en guise de Contenu une collection de tableaux des lignes de détails, dont je veux aussi la 1ère. Est-ce clair ? En somme c'est la 1ère ligne détail du 1er An de l'Id.
On récupère ainsi la même chose que dans Détail la 1ère fois qu'on passera plus loin dans le For Each Détail. On aurait tout aussi bien pu d'ailleurs le récupérer à l'occasion de ce passage, mais ça aurait été plus compliqué de prévoir de quoi savoir qu'on y passait pour la 1ère fois de tout le Id (le SsGroup, pas sa propriété Id. N'était-ce pas un choix de nom regrettable ?)
Son utilisation, vous voyez bien, c'est pour garnir les colonne 4 à 6 de la table de sortie.
Si ça vous parait plus clair, vous pouvez décomposer :

Set An = Id.Contenu(1): LePremId = An.Contenu(1)

"LePremId" n'est peut être pas bien choisi ? "PremDétail" aurait peut être été mieux. En tout cas c'est le premier détail de l'Id.

Il y a aussi des commentaires d'explication dans la fonction GroupOrg du module MClassement et aussi dans le module de classe SsGroup.

 

[KIM]

Re : Problème sumproduct en vba

Bonsoir Dranreb et le fil,
Je pense que maintenant j'arrive à adapter la fonction GroupOrg à mes besoins. J'ai remplacé l'intitulé de la col ID par CPT, J'ai intégré un niveau suppémentaire de regroupement et modifier la hiérarchie du regroupement : SRC / DPT/ SITE/CPT.
Pour avoir des totaux par SITE ou DPT ou SRC, je peux utiliser pour chaque niveau la même procédure avec peu de modification.
Par contre j'ai essayé d'intégrer le calcul des sous-totaux par DPT et par SRC dans la même procédure sans résultat.

J'ai créé 2 tableaux pour recevoir les totaux TblDPT(), TblSRC(), les variables, TotSRC et TotDPT mais je n'ai pas su les calculer et remplir les tableaux. Ces 2 tableaux sont à créer à la suite du tableau initial Tbl.
Ci-joint le tableau avec mes modifications
Merci de ton aide.
KIM

 

[Dranreb]

Re : Peoblème sumproduct en vba

Bonjour.

Oui, ben ça m'a quand même pris un peu de temps.
Il aurait fallu des tables pour les totaux pas des variables, puisqu'il en faut un pour chaque année.
Mais j'ai pensé que puisqu'on à les tables de sortie pour les 2 récapitulatifs, elles peuvent les supporter.
Il y a juste le niveau Site, qui n'est pas récapitulé, qui a besoin d'un tableau TotSit
Le principe, n'est ce pas, c'est d’additionner, tout à la fin mais avant de quitter un niveau, ses totaux à ceux du niveau précédent.
J'ai enlever les ligne vides qui ne servent à rien, nuisent à la visibilité de l'indentation, et ne laissent embrasser du regard qu'une plus petite partie du code et donc de la structure de l'algorithme.
Ou alors, si vous y tenez parce que pour vous c'est plus clair, mettez obligatoirement un commentaire après une ligne vide, genre : Rem. —— Début source

 

[KIM]

Re : Peoblème sumproduct en vba

Bonsoir Dranreb et le fil,
Merci beaucoup. Je vais regarder et reviens vers vous lundi.
Merci encore et bon we.
KIM

 

[KIM]

Re : Peoblème sumproduct en vba

Bonjour Dranreb et le fil,
Merci beaucoup. Je n'aurai jamais réussi tout seul. La répartition par année est gérée sur un certain nombre de tableaux de bord.
Les colonnes des années et leur nombre ont été calculées dans la procédure Grouper322.
J'ai compris maintenant comment adapter la fonction GroupOrg à d'autres tableaux de bord dans lesquels les dépenses sont réparties selon les même critères (par SRC, SRC et DPT, et SRC, DPT et SITE) mais calculées par Catégories (Col P) et non plus par année.
La liste des catégories n'est pas figée. Elle est extraite par ListCAT (liste sans doublons). Je n'ai pas réussi à l'intégrer dans la procédure Grpuper331pour remplir le titre des tableaux automatiquement comme pour les années voir fichier ci-joint onglet Recap3.
Avez vous une fonction dans vos modules qui liste et trie des données uniques d'une colonne (liste unique sans doublons) et intégrable dans GroupOrg ?
Merci pour votre aide
KIM

 

[Dranreb]

Re : Peoblème sumproduct en vba

Bonjour

Curieusement je n'ai pas de fonction toute faite qui ne fait que ça.
Mais vous pourriez l'obtenir facilement avec un GroupOrg préalable uniquement basé sur cette colonne, en ne mettant que les .Id dans un Dictionary avec pour Item le numéro de colonne où ça doit aller.
Je vous recommande de cocher la référence "Microsoft Scripting Runtime". Elle est nécessaire pour pouvoir déclarer des variables As Dictionary.
À part ça, si ça vous intéresse j'ai un module MDictionary qui utilise les modules de service que vous avez déjà et qui sait fabriquer des dictionnaires arborescents.

 

[KIM]

Re : Peoblème sumproduct en vba

Re,
1- Oui je suis intéressé par votre module MDictionary qui utilise vos modules de service, etc.
2- J'ai déclaré Dliste As New Collection et TabListe As Variant et cocher la référence "Microsoft Scripting Runtime".
J'ai redéfini CMax = Dliste.Count + 7
Je bloque sur
For C = 7 To CMax: TblSrc(1, C) = Dliste(C - 7): TblDpt(1, C) = Dliste(C - 7): Tbl(1, C) = Dliste(C - 7): Next C

ensuite par quoi faut remplacer An.Id - AnMin + 7 dans : Tbl(L, An.Id - AnMin + 7) = Som: Next An pour calculer la somme pour chaque CAT ?

Ci-joint le fichier xls.
Merci d'avance pour votre aide.
KIM

 

[gosselien]

Re : Peoblème sumproduct en vba

Bonjour,

et un TCD ne peut pas convenir à partir du tableau "BASE" ?

P.