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

 

[ROGER2327]

Re : Peoblème sumproduct en vba

Bonjour KIM.

(...)
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.
(...)

Hélas, la formule dans la procédure n'est pas la même que celle de la feuille Excel.

Essayez ceci :​

        Tbl(L, 3) = Evaluate("sumproduct((ColA = """ & Tbl(L, 1) & """)*(ColC = " & Tbl(L, 2) & ")*(ColO = " & Tbl(1, 3) & ")*ColN)")
        Tbl(L, 4) = Evaluate("sumproduct((ColA = """ & Tbl(L, 1) & """)*(ColC = " & Tbl(L, 2) & ")*(ColO = " & Tbl(1, 4) & ")*ColN)")
        Tbl(L, 5) = Evaluate("sumproduct((ColA = """ & Tbl(L, 1) & """)*(ColC = " & Tbl(L, 2) & ")*(ColO = " & Tbl(1, 5) & ")*ColN)")
        Tbl(L, 6) = Evaluate("sumproduct((ColA = """ & Tbl(L, 1) & """)*(ColC = " & Tbl(L, 2) & ")*(ColO = " & Tbl(1, 6) & ")*ColN)")
        Tbl(L, 7) = Evaluate("sumproduct((ColA = """ & Tbl(L, 1) & """)*(ColC = " & Tbl(L, 2) & ")*(ColO = " & Tbl(1, 7) & ")*ColN)")

Bonne journée.


ℝOGER2327
#7781

Mardi 3 Pédale 142 (Saint Ellen, hile - fête Suprême Quarte)
7 Ventôse An CCXXIII, 4,5284h - alaterne
2015-W09-3T10:52:06Z

 

[KIM]

Re : Peoblème sumproduct en vba

Re-Bonjour le forum,
@Roger2327, merci bien, votre formule fonctionne très bien.

Bonne journée
KIM

 

[Dranreb]

Re : Peoblème sumproduct en vba

Bonjour.

Je pense que pour être tout à fait exact la fonction SOMMEPROD fait la même chose que la WorksheetFunction.Sumproduct dans la mesure où ce n'est pas du tout elle qui s'occupe de pré-traiter les expressions matricielles dans ses paramètres pour en faire des tableaux de variant, qui ne lui sont soumis que tout à la fin. Je vois beaucoup de SOMMEPROD où, en dehors des parenthèses inutiles aussi, en passant, les "*" sont systématiquement utilisés à la place des ";". C'est la détourner de sa vocation: elle ne fait plus de produit vu qu'elle ne reçoit au final qu'un seul paramètre, c'est le prétraitement matriciel qui fait tout. Si on tent à l'écrire comme ça un SOMME validé en matriciel fait la même chose.

 

[KIM]

Re : Peoblème sumproduct en vba

Re, le fil, bonjour Dranreb,
Merci pour les explications. J'utilise plus facilement SOMMEPROD que SOMME quand j'ai des conditions multiples.
Dans le cas actuel, j'ai 3 critères sur les données en ligne et un critère selon le titre de la colonne pour l'année.
Je ne sais pas comment résoudre mon cas avec SOMME.
Sûrement je peux trouver des cas complexes résolus par SOMME sur ce FORUM. Je regarderai.

Merci et bonne journée
KIM

 

[Dranreb]

Re : Peoblème sumproduct en vba

Bien sûr que SOMMEPROD est plus indiqué que SOMME si on veut calculer la somme des produits de colonnes spécifiées séparées par des point virgules et non des astérisques. On peut tolérer celles ci pour un produit de conditions spécifiées entre parenthèse, mais après, pour qu'il reste au SOMMEPROD un travail qui ne relève pas d'un simple SOMME validé en matriciel, la quantité à sommer quand les conditions sont réunies doit être spécifiée derrière un ";" et non encore multiplié aux conditions par un "*".

 

[KIM]

Re : Peoblème sumproduct en vba

Merci, cela me rassure, en effet j'utilise SOMMEPROD pour les cas complexes et je n'ai jamais utilisé des astérisques.

Bonne journée
KIM

 

[ROGER2327]

Re : Peoblème sumproduct en vba

Bonjour Dranreb, KIM.

D'accord avec Dranreb sur l'usage fait à tort et à travers de la fonction SOMMEPROD.
Ma réponse se borne à constater que la fonction qui est dans la feuille (voir plage K5:O8)​

=SOMMEPROD((ColA=$A5)*(ColC=$B5)*(ColO=C$4)*ColN)

n'est pas la même que celle qui figure dans le code. Cette dernière équivaut à​

=SOMMEPROD((ColA=""&$A5&"")*(ColC=""&$B5&"")*(ColO=""&C$4&"")*ColN)

dans la feuille (voir plage K11:O14).
Si on veut effectivement considérer $B5 et C$4 comme du texte, on peut utiliser​

=SOMMEPROD((ColA=""&$A5&"")*(""&ColC&""=""&$B5&"")*(""&ColO&""=""&C$4&"")*ColN)

(voir plage K17:O20).
Le problème ne se pose pas pour ColA et $A5 parce que ces plages contiennent des données qu'Excel et VB interprètent comme du texte.
​

Bonne journée.


ℝOGER2327
#7782

Mardi 3 Pédale 142 (Saint Ellen, hile - fête Suprême Quarte)
7 Ventôse An CCXXIII, 5,5365h - alaterne
2015-W09-3T13:17:15Z

 

[KIM]

Re : Peoblème sumproduct en vba

Re le fil,
@Roger2327,
En effet suite à un contrôle avec des sommes et des filtres, je ne retrouve pas le même total avec les formules du code vba.
Les colonnes A et B ainsi que les titres de colonnes sont du texte. La somme se fait avec la col N de l'onglet Base.

Comment est-il possible d'améliorer le vba pour que les données colonne par colonne soient homogènes.

Merci d'avance
KIM

 

[Dranreb]

Re : Peoblème sumproduct en vba

Il y avait un problème du fait que des nombres étaient donnés entre double guillemets dans l'expression à évaluer.
Les 2 formules testées ci après trouvent bien 823

Sub test()
Dim T(), Z$
Z = "SUMPRODUCT((ColA=$A5)*(ColC=$B5)*(ColO=$F$4),ColN)"
MsgBox Z & vbLf & " Evaluate ==> " & Evaluate(Z)
T = ActiveSheet.[A1:G8].Value
Z = "SUMPRODUCT((ColA=" & Vf(T(5, 1)) & ")*(ColC=" & Vf(T(5, 2)) & ")*(ColO=" & Vf(T(4, 6)) & "),ColN)"
MsgBox Z & vbLf & " Evaluate ==> " & Evaluate(Z)
End Sub
Function Vf(Valeur) As String
If VarType(Valeur) = vbString Then Vf = """" & Replace(Valeur, _
   """", """""") & """" Else Vf = Trim$(Str$(Valeur))
End Function

… et de grâce ne mettez plus "*ColN)" à la fin mais le séparateur de paramètres.
En théorie il faudrait même:

=SOMMEPROD(N(ColA=$A8);N(ColC=$B8);N(ColO=$F$4);ColN)

Si ont veut vraiment que ce soit SOMMEPROD et pas un autre dispositif qui fasse tout le boulot des multiplications.

Cela dit, je crois que je ferais cette procédure très très différemment.

 

[KIM]

Re : Peoblème sumproduct en vba

Re-bonjour le fil
@Dranreb,
Merci pour ces recommandations. J'ai essayé d'intégrer ton code test et ta fonction dans mon code Sub GrouperDranreb. Elle me génère des erreurs de type #Valeurs!
Ci-joint le fichier

Merci d'avance de ton aide
KIM

 

[Dranreb]

Re : Peoblème sumproduct en vba

Il ne faut plus mettre les double guillemets, c'est ma fonction Vf qui les met, seulement quand il faut. Mais laissez tomber j'ai une autre proposition.
C'est le deuxième GrpOrgKIM que je vous fournis. Tâchez à l'avenir de penser de vous même aux simplifications possibles grâce à la fonction GroupOrg.

 

[ROGER2327]

Re : Peoblème sumproduct en vba

Suite...

(...)
Cela dit, je crois que je ferais cette procédure très très différemment.

Moi pareil...​

Bonne soirée.


ℝOGER2327
#7783

Mardi 3 Pédale 142 (Saint Ellen, hile - fête Suprême Quarte)
7 Ventôse An CCXXIII, 7,4918h - alaterne
2015-W09-3T17:58:49Z

 

[KIM]

Re : Peoblème sumproduct en vba

Bonjour Dranreb, Roger2327 & le forum,

Désolé je n'y ai pas pensé. Je vais regarder, l'appliquer à mon fichier général et vous faire un retour.

Merci à vous deux et Bonne journée
KIM

 

[KIM]

Re : Peoblème sumproduct en vba

Re bonjour le fil,

@Dranreb,
J'ai essayé d'appliquer ta méthode à mon fichier source. Les colonnes sont enrichies.
Les dépenses doivent être calculées par DPT, ensuite SRC (col J) et en finale par ID.
Dans la Feuil Recap, je complète pour chaque ID son SIT, PER, LIB et SRC.
Les données dans l'onglet Base commencent à la ligne 4.
ci-joint le fichier
Merci d'avance pour ton aide et tes conseils.
KIM