WorksheetFunction.SumProduct (VBA)

[Kotov]

Bonsoir à tous,

Je prépare actuellement une macro qui sort plus de 500 données issues d'une base et je coince bêtement sur WorksheetFunction.SumProduct.

J'utilise régulièrement sans problème SOMMEPROD en formules avec de multiples conditions mais je bloque sur la même fonction en VBA.

Vous trouverez ci-après une macro expurgée posant le problème sachant que
- Plage contient les valeurs "Oui" ; "Non" et ""
- Plage1 contient des données numériques négatives (ex : -1234,56)
et que bien évidemment je cherche à connaître le montant global des valeurs numériques se référant à la plage contenant les valeurs "Oui"

Sub StatsGlob()
Dim r&
Dim ShR As Worksheet
Set ShR = ActiveWorkbook.Sheets("R")
Dim ShS As Worksheet
Set ShS = ActiveWorkbook.Sheets("STATS")
Dim Plage As Range
Dim Plage1 As Range

ShR.Activate
'*****************  Calculs avec SUMPRODUCT  ************************
With ShR
r = ShR.Cells(65536, 1).End(3).Row

Set Plage = ShR.Range(Cells(2, 69), Cells(r, 69))
Set Plage1 = ShR.Range(Cells(2, 68), Cells(r, 68))
  
 
[color=blue] '3 tentatives sans succès : [/color]

[B]ShS.Cells(9, 8) = Application.WorksheetFunction.SumProduct(Plage.Value = "Oui" * 1, Plage1.Value)[/B] 'j'obtiens "erreur 13"

[B]ShS.Cells(10, 8) = Application.WorksheetFunction.SumProduct(Plage, Plage1)[/b] 'Résultat=0, si je fais SumProduct(Plage,1, Plage1) j'obtiens "erreur 13"

[B]ShS.Cells(11, 8) = Application.WorksheetFunction.SumProduct(Plage.Value = "Oui" ,1, Plage1.Value)[/B] 'j'obtiens "erreur 13"


'******* .... Autres calculs (sans intérêt pour le problème posé)  ****************
End With
'*****************  Libération de mémoire  ************************
Set ShR = Nothing
Set ShR = Nothing
Set Plage = Nothing
Set Plage1 = Nothing
End Sub

Pour ce soir j'ai le cerveau en surchauffe, alors peut être que quelqu'un verra au premier coup d'oeil sur quoi je bute depuis une heure.

Bonne soirée
Kotov


EDIT : bien sûr, je peux obtenir le résultat avec une boucle qui additionne les montants quand la valeur est "Oui", mais j'aimerais l'obtenir avec WorksheetFunction.SumProduct

 

[Kotov]

Re : WorksheetFunction.SumProdruct (VBA)

Bonsoir le forum,

Bien vu Spit pour ton astucieux détournement de problème.
Quand Ninbihan a conclu que la fonction marchait uniquement avec des valeurs numériques, j'avais envisagé de transformer les "Oui" en 1 et les "Non" en 0. A la différence de ta méthode qui fait le remplacement, et remet tout en l'état après les calculs, j'étais parti sur un changement définitif à l'ouverture du fichier.

J'avais finalement renoncé pour 2 raisons :
1. je craignais que le fichier soit moins "parlant" pour les utilisateurs. C'est là que ton idée est très utile : en remettant le fichier en l'état, il reste tout à fait lisible.

2. dans l'exemple fourni, j'avais 3 options : "Oui", "Non" et "". C'était le cas le plus simple. En réalité, je comptais utiliser SumProduct sur 5 à 6 colonnes avec des options beaucoup plus nombreuses (plus d'une cinquantaine de critères différents).
J'imaginais alors qu'avec l'aide d'une boucle je pourrais aller chercher chaque critère

Un truc dans ce genre : (! attention cette macro est incomplète et donc non fonctionnelle, c'est juste pour présenter l'idée de départ)

Dim r&, c&, i&, y& '(r= Row, c= Column, i= variable boucle référencant toutes les options)
Dim Critère as Variant

With ShR
r = ShR.Cells(65536, 1).End(3).Row

Set Plage = ShR.Range(Cells(2, 2), Cells(r, 2))
Set Plage1 = ShR.Range(Cells(2, 1), Cells(r, 1))

' maintenant, imaginons une "base" de 50 critères (ShS.Cells(1,1) --> ShS.Cells(50,1)

For i = 1 to 50
Critère = Shs.Cells(i,1) 'A chaque fois le critère change
ShS.Cells(i, 2) = Application.WorksheetFunction.SumProduct(Plage.Value = Critère * 1, Plage1.Value)
next i

Comme il était nécessaire de tout transformer en numérique, j'avais envisagé d'attribuer un critère numérique à chaque option (option1 =1, Option 2 = 2 ..), à utiliser le N° d'option comme coef multiplicateur (Plage.Value = Critère * Coef) puis à diviser le résultat obtenu par le coef pour revenir au multiplicateur 1.
Mais c'est trop alambiqué et j'ai obtenu un bon résultat avec une boucle et Select Case


Cela dit, Spit, j'utiliserai ton idée pour les cas avec 2 options.
Merci pour ta contribution et ton obstination à trouver la solution.

Bonne soirée
Kotov

 

[bqtr]

Re : WorksheetFunction.SumProduct (VBA)

Bonjour Kotov, le fil

As tu essayé avec la fonction SumIF.

Dans l'exemple joint je passe par un tableau pour lister les différents critères, puis par une boucle pour les calculs avec Sumif. L'avantage, c'est que tu n'es pas obligé de tout transformer en numérique.

Sub Sum_If()

Dim r&, i&, k&
Dim ShR As Worksheet
Set ShR = ActiveWorkbook.Sheets("R")
Dim ShS As Worksheet
Set ShS = ActiveWorkbook.Sheets("STATS")
Dim Plage As Range
Dim Plage1 As Range
Dim tablo()

For i = 1 To 4 ' 4, nombre de critères
    ReDim Preserve tablo(i)
    tablo(i) = ShS.Cells(i, 5).Value
Next

With ShR
r = ShR.Cells(65536, 1).End(3).Row

Set Plage = ShR.Range(.Cells(2, 2), .Cells(r, 2))
Set Plage1 = ShR.Range(.Cells(2, 1), .Cells(r, 1))

For k = 1 To UBound(tablo)
ShS.Cells(k + 5, 2) = Application.WorksheetFunction.SumIf(Plage, tablo(k), Plage1)
Next
End With
'*****************  Libération de mémoire  ************************
Set ShR = Nothing
Set ShS = Nothing
Set Plage = Nothing
Set Plage1 = Nothing

End Sub

Si ca peut te donner des idées...

Bonne nuit

 

[Kotov]

Re : WorksheetFunction.SumProduct (VBA)

Bonsoir Pierre et merci pour ta proposition,

Ton idée me plait beaucoup et c'est également une méthode que j'utiliserai à l'avenir.

Finalement à partir d'une déception (la fonction SumProduct "bridée" en VBA), on arrive à trouver des solutions alternatives simples et interessantes.

Merci à tous pour vos contributions.
Bonne soirée

Kotov

 

[Spitnolan08]

Re : WorksheetFunction.SumProduct (VBA)

Re,

je croyais que tu ne voulais pas de boucle... Parce que sinon, tu peux aussi boucler sur les valeurs de ta colonne "oui/non" et charger 2 tableaux ou collections avec les éléments correspondant aux oui et multiplier les 2 matrices obtenues (enfin je crois car j'y avais pensé mais ne l'ai pas testé à cause de la boucle...) Une autre idée...

Bonne soirée
Cordialement

 

[Bebere]

Re : WorksheetFunction.SumProduct (VBA)

bonjour Spitnolan,Kotoc,Bqrt
une réponse avec sumproduct

Sub EssaiSumProduct()
Dim I&
Dim Result As Variant
Dim tablo() As String

Dim SheetName As String
Dim NameAddress As String

'noms définis ColA,ColB
With Sheets("R")
SheetName = "=" & .Name & "!"
I = .Range("A65535").End(xlUp).Row
NameAddress = .Range("A2:A" & I).Address
ActiveWorkbook.Names.Add Name:="ColA", RefersTo:=SheetName & NameAddress
NameAddress = .Range("B2:B" & I).Address
ActiveWorkbook.Names.Add Name:="ColB", RefersTo:=SheetName & NameAddress
End With
'critères
For I = 1 To 4
ReDim Preserve tablo(I)
tablo(I) = Sheets("STATS").Cells(I, 5).Value
Next
'résultats
ReDim Result(1 To UBound(tablo), 1 To 1)

For I = 1 To UBound(tablo, 1)
Result(I, 1) = Evaluate("SUMPRODUCT((ColB=""" & tablo(I) & """)*ColA)")
Next I

With Sheets("STATS")
.Range("B1").Resize(UBound(Result, 1), UBound(Result, 2)) = Result
End With

End Sub

à bientôt

 

[Kotov]

Re : WorksheetFunction.SumProduct (VBA)

Bonjour à tous,


Spitnolan,

Effectivement je cherchais d’éviter, si possible, la programmation par boucle.
Je t’explique le cas :
Récemment nommé à un poste pour limiter le risque financier, j’ai dans un premier temps, répertorié tous les cas de perte financière :
Dans une base, j’ai listé sur plus de 80 colonnes et quelques centaines de lignes, tous les éléments à ma disposition. Certains éléments sont binaires « Oui-Non ; Vrai-Faux », d’autres sont multiples « zone géographique, ancienneté de relation … », certains sont chiffrés, d’autres non.

But du « jeu » : dégager des profils à risque en croisant les données, les hiérarchiser pour engager des actions adaptées au risque encouru.

Mon idée de départ :
- définir une plage par colonne
- utiliser les fonctions d’Excel pour les calculs comme on le fait habituellement par le biais des formules

Ayant plusieurs centaines de croisements de données à faire, j’espérais faire une ligne pour chaque calcul et éviter de passer par une boucle.

Exemple :

ShS.Cells(2, 2) = Application.WorksheetFunction.Sum(Plage)
ShS.Cells(8, 8) = Application.WorksheetFunction.SumProduct(Plage.Value = "Oui" * 1, (Plage2.Value = "45" * 1, Plage1.Value)

Malheureusement, SumProduct n’est pas aussi performante que SOMPROD et nécessite les quelques aménagements que les amis du forum ont proposé.

Pressé par le temps, j’ai utilisé une boucle couplée à SELECT CASE, IF, NBVAL ... et le résultat obtenu est nickel : traitement de l’intégralité de la base en quelques secondes, avec pages de données, graphiques, synthèse et cibles prioritaires.
Une seule déception : ma macro n’est pas aussi épurée que je l’espérais au départ.
Mais l’essentiel c’est l’efficacité, quitte à passer par une boucle.
Merci pour tes idées. A+


Berbère.
Merci pour ta proposition, également très interessante. Nommer les plages, puis appliquer Evaluate à SumProduct.

Ce n’est pas une méthode que j’utilise habituellement, d’autant que je préfère .Cells(1,1) à .Range("A1").
Mais la confrontation d’idée fait progresser et je te remercie.

Je conserve toutes ces méthodes, elles me seront probablement utiles un jour.

Bonne journée à tous
Kotov

 

[Gael]

Re : WorksheetFunction.SumProduct (VBA)

Bonjour à tous,

A partir d'une astuce trouvée sur le net, les instructions jointes donnent un résultat correct:

Set Plage1 = ShR.Range("F1:F" & r) pour la définition de la plage

ShS.Cells(10, 8) = Application.WorksheetFunction.SumProduct([0+(G1:G33="Oui")], Plage1)

remarques:
Si on enlève le 0+, cela ne marche plus
Si Plage1 est défini avec Range(Cells(),cells()), cela ne marche pas non plus.
Je n'ai pas réussi à remplacer G1:G33 par une variable, ce qui rend bien sûr ce code peu intéressant mais peut-être cela vous donnera-t-il une autre piste de recherche?

@+

Gael

 

[Bebere]

Re : WorksheetFunction.SumProduct (VBA)

bonsoir le fil

l'idée de Gael complétée

Sub essai()
Dim Plage1 As Range,I as Integer
Dim ShR As Worksheet
Dim ShS As Worksheet

Set ShR = ActiveWorkbook.Sheets("R")
Set ShS = ActiveWorkbook.Sheets("STATS")
I = Sheets("R").Range("A65535").End(xlUp).Row
ActiveWorkbook.Names.Add Name:="ColB", RefersTo:="=" & ShR.Name & "!" & ShR.Range("B2:B" & I).Address


Set Plage1 = ShR.Range("A2:A" & I) ' pour la définition de la plage
'critères
For I = 1 To 4
ReDim Preserve Tablo(I)
Tablo(I) = ShS.Cells(I, 5).Value
Next
For I = 1 To 4
ActiveWorkbook.Names.Add Name:="Choix", RefersTo:="=" & ShS.Name & "!" & ShS.Range("E" & I).Address
ShS.Cells(I, 2) = Application.WorksheetFunction.SumProduct([0+(ColB=Choix)], Plage1)
Next I

End Sub

à bientôt

 

[Kotov]

Re : WorksheetFunction.SumProduct (VBA)

Bonjour à tous,

Merci Gaël et Berbère pour cette nouvelle façon de procéder.
J'ai testé, c'est nickel.

Quelle est la signification de [0+ dans la fonction ??

Par ailleurs, dans la mesure où ce fil me parait le plus complet pour contourner les "lacunes" de SumProduct, je propose la note de 5 étoiles de manière à attirer l'attention des personnes confrontées au même problème.

Un grand merci à tous les contributeurs.
Bonne journée
Kotov