SumProduct (utilisation en VBA)

Version imprimable

26/06/2013, 23h15
Invité

SumProduct (utilisation en VBA)

Bonjours,

Je n'arrive pas à me servir de la fonction Sumproduct en VBA qui pourtant marche bien en Excel. J'aurai aimer savoir comment on peut introduire des constante dans une fonction Sumproduct.

Imaginons les valeurs suivantes:
A_________B____C_____D_______ Résultat (Excel)____Résultat (VBA)
2.5_______ 2____25____32_______8.967539539______
1_________3____32____12_______-1.727402497______
2_________7____66____24_______-2.173019259______
3_________9____55____78_______1.029013732_______
4_____________________________14.18208467_______
5_____________________________-4.731036159______
6_____________________________-7.243988707______

En Excel, le Résultat (Excel) s'écrit:

Code:

=SOMMEPROD($C$2:$C$5; SIN(($D$2:$D$5 * $A2) + RADIANS($E$2:$E$5)))

http://img11.hostingpics.net/thumbs/...nsousExcel.png http://img11.hostingpics.net/thumbs/...sousExcel2.png

Cela fonctionne bien. Le résultat est bon. La constante introduit (A2) dans la formule ne pose pas de problème. Excel la prends comme valeur unique. En faisant glisser la formule dans la feuille, la valeur A2 change et passe en A3, puis A4 etc...

En VBA, cela ne marche pas dutout, pourquoi ? Même en utilisant une boucle for pour la constante.

Quel code puis-je appliquer pour avoir le même résultat qu'en Excel ?

Merci.

Bonjour,

Essayez avec cette procédure:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
Sub MyCalculous()
    Dim mywb As Workbook
    Dim myws As Worksheet
    Dim myConst_B As Range, myConst_C As Range, myConst_D As Range
    Dim myDriver As Range, outPutrange As Range
    Dim myFormuleMagic As String
 
    Set mywb = ThisWorkbook
    Set myws = mywb.Worksheets(1)
    myws.Range("H2:H8").ClearContents
    Set myDriver = myws.Range("A2")
    Set outPutrange = myws.Range("H2")
    Set myConst_B = myws.Range("C2:C5")
    Set myConst_C = myws.Range("D2:D5")
    Set myConst_D = myws.Range("E2:E5")
 
    Do Until myDriver = ""
        myFormuleMagic = "=SUMPRODUCT(" & CStr(myConst_B.AddressLocal) & "," & "SIN((" & CStr(myConst_C.AddressLocal) & "*" & _
        CStr(myDriver.AddressLocal) & ")" & "+RADIANS(" & CStr(myConst_D.AddressLocal) & ")))"
        Debug.Print myFormuleMagic
        myws.Range(outPutrange.AddressLocal).Formula = CStr(myFormuleMagic)
        Set myDriver = myDriver.Offset(1)
        Set outPutrange = outPutrange.Offset(1)
    Loop
 
    Set mywb = Nothing
    Set myws = Nothing
    Set myDriver = Nothing
    Set outPutrange = Nothing
    Set myConst_B = Nothing
    Set myConst_C = Nothing
    Set myConst_D = Nothing
 
End Sub

Bonjour, et merci beaucoup pour cette réponse.

Aïe, aïe, aïe, j'ai failli dire (vu la beauté de la Macro) que c'était impressionnant et que vous déteniez " la " solution :mrgreen: ! ... mais non, fausse joie que j'ai pas eu en voyant qu'il s'agissait d'une formule !!!

Je banie les formule en VBA pour ne laisser place qu'au .Value

J'aurai enfaite des milliers et des milliers de formule (en .value enfaite) à utiliser parceque ça rame de partout avec des formules.

Enfaite, je voudrai utiliser des matrices avec beaucoup de cellules et ça prends trop de mémoire avec les formula, alors je suis limité.
Au chargement, ça peux me prendre 5 Go de RAM ou plus:
http://img4.hostingpics.net/pics/763468MmoireExcel.png
Avec des .value, j'aurai un plus grand contrôle et je pourrai aller plus loin dans les calculs.

Les formules sont bien pour le calcul en continue ou pour la mise à jour auto de données en direct mais ne permettent pas ou permettent très mal les calculs de longues durées.

Avec des .Formula, je peux y arriver simplement (on peut se débrouiller avec les . formula de la forme (= "="):
Code:

1 2 3 4 5 6 7 8 Sub form() Dim atext As String atext = "$A2" [I2].Formula = "=" & "SUMPRODUCT($C$2:$C$5" & "," & " SIN(($D$2:$D$5 * " & atext & ") + RADIANS($E$2:$E$5)))" [I2].AutoFill Destination:=[I2:I8], Type:=xlFillCopy End Sub
La même en .Value ? :mrgreen:

P.S.: En tout cas, c'est sympa d'avoir d'essayé. Ça a dû prendre du temps en plus vu la longueur de la Macro :ccool: .

Peut-être que cette forme te conviendra.

La premiere valeur attendue est un scalaire, les trois autres sont des vecteurs (de même longueur):

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
 
Public Function MyCalculous2(myDriver As Range, myConst_B As Range, myConst_C As Range, myConst_D As Range) As Double
    Dim i As Integer
    Dim Dbl_outPutrange As Double
    Dim pi As Double
 
    pi = 3.1416
    Dbl_outPutrange = 0
    For i = 1 To myConst_B.Count
        Dbl_outPutrange = Dbl_outPutrange + myConst_B(i) * Math.Sin(myConst_C(i) * myDriver.Value + myConst_D(i) * pi / 180)
    Next i
    MyCalculous2 = Dbl_outPutrange
End Function

J'ai enfin trouvé une solution !!!

Petite mise à jour, j'ai trouvé une méthode, certainement peu efficace, mais je verrai par la suite si ça vaut le coup de continuer ou pas. Si c'est trop lent, l'intérêt de la fonction SOMMEPROD se perd.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Option Explicit Sub form() Dim atext As String Dim ctext As String, dtext As String, etext As String Dim itext1 As String, itext2 As String Dim ModeCalc As Integer ModeCalc = Application.Calculation Application.Calculation = xlCalculationManual atext = "$A2" ' A écrire sur la Feuille Excel en $K$20 :=> $K$20 = $A2 'Sans les guillemets ctext = "$C$2:$C$5" ' A écrire sur la Feuille Excel en $K$21 :=> $K$21 = $C$2:$C$5 'Sans les guillemets 'ADRESSE par calcul dans feuille dtext = "$D$2:$D$5" 'idem etext = "$E$2:$E$5" 'idem itext = "$I$2:$I$8" 'idem itext1 = "$I$2" 'idem itext2 = "$I$8" 'idem With Range(itext1) .Formula = "=" & "SUMPRODUCT(" & ctext & "," & " SIN((" & dtext & " * " & atext & ") + RADIANS(" & etext & ")))" .AutoFill Destination:=Range(itext), Type:=xlFillDefault End With ActiveSheet.Range(itext).Calculate 'Obligatoire : Pour les cas où le calcule auto n'est pas actif, avec Nom classeur actif With Range(itext) .Value = .Value 'Transformation de Formule en Valeur End With Application.Calculation = ModeCalc End Sub
Nos chemins se croisent !

Merci, je vais essayer ta solution pour voir.

EDIT: Ah, elle n'utilise pas de SOMMEPROD hélas.
C'est sympa de ta part de m'avoir fait une petite fonction, mais je ne pense pas que j'utiliserai de fonction perso pour l'instant. Mon but est surtout d'essayer d'apprendre le VBA enfaite et bien sûr pouvoir faire des trucs avec comme ici. Mais je commence à connaitre de mieux en mieux le VBA (création de boucle avec pause/marche/arrêt, création de tableau, et même un tout petit peu d'OpenGL pour les graphiques etc...) et j'arrive bien à utiliser la plupart des fonctions VBA sauf justement les fonctions interne à Excel.

Bonjour,

La même en .Value ? :D

Code:

1
2
3
4
5
6
7
Sub form()
Dim atext As String
    atext = "$A2"
    [I2].Formula = "=" & "SUMPRODUCT($C$2:$C$5" & "," & " SIN(($D$2:$D$5 * " & atext & ") + RADIANS($E$2:$E$5)))"
    [I2].AutoFill Destination:=[I2:I8], Type:=xlFillCopy
    [I2:I8] = [I2:I8].Value
End Sub

Cordialement.

Ah oué, carrément 8O , pourtant j'avais essayé il me semble et ça marchait pas au tout début :mrgreen:
Puis je suis parti dans des modife pour tout gérer hors VBA (plage de cellule avec le texte en cellule pour éviter d'ouvrir l'éditeur VBA histoire d'être totalement tranquille) et puis ça à marché :mrgreen: !

EDIT: Mais attention, il manque quand même la petite sécurité au niveau de l'option de calcul qu'il faut insérer entre :

Code:

[I2].AutoFill Destination:=[I2:I8], Type:=xlFillDefault

et

Code:

[I2:I8] = [I2:I8].Value

Sinon, ça pourrai ne pas marcher :mrgreen:

En tout cas, merci bien :ccool:

A+ et encore merci !

C'est assez incroyable, mais je n'ai vu aucune formule sur le net utilisant des constantes à l'intérieur des fonctions sumprod, ni même de fonction autre que des plages avec un signe égale après les plages. Tous les sites que j'ai vu parlaient toujours de plages 100% identiques en dimensions et sans fonctions spécifique à l'intérieur des plages.

Ça mériterai bien une contribution sur l'utilisation des sumproduct qui ont l'air difficile en VBA. Mais je ne l'a ferai pas, si quelqu'un veux en faire une, ça pourrai aider je pense, parce que ce genre de fonction est super galère en VBA. Les fonctions sinus par exemple ne prennent pas en charge les plages de données en VBA alors qu'il n'y a aucun problème en Excel.

EDIT:
Citation:
Envoyé par SMBeccaria
Peut-être que cette forme te conviendra.

La premiere valeur attendue est un scalaire, les trois autres sont des vecteurs (de même longueur):
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 Public Function MyCalculous2(myDriver As Range, myConst_B As Range, myConst_C As Range, myConst_D As Range) As Double Dim i As Integer Dim Dbl_outPutrange As Double Dim pi As Double pi = 3.1416 Dbl_outPutrange = 0 For i = 1 To myConst_B.Count Dbl_outPutrange = Dbl_outPutrange + myConst_B(i) * Math.Sin(myConst_C(i) * myDriver.Value + myConst_D(i) * pi / 180) Next i MyCalculous2 = Dbl_outPutrange End Function
Je viens de tester la fonction, et nickel, elle fonction bien. Mais elle est forcément limité avec un Sin et un radians, ce qui empêcherai de l'utiliser avec d'autres fonctions (Signe, Cos ou sans Radians par exemple). Mais elle fonctionne rapidement en tout cas. A mettre de coté donc.

RePS:
J'ai testé la Fonction SOMMEPROD avec le timer:

Test n°1
2000 valeurs de A
1000 valeurs de C,D,E
Timer = 1,08s ; 1,23 s ; 1,77 s ; 1,47 s pour les 4 essais

Test n°2
400 000 valeurs de A
8000 valeurs de C, D, E
Timer = 33,125 s

Questions:
Ça nous donne combien de multiplications et combien d'additions tous ça en excluant les fonctions RAD et SIN ?

(1+2) x 8000 x 400 000 = 9 600 000 000 Multiplications en 33s ? Ou je me trompe complètement ? Ça parait gros.

Comparaison temps de calcul: SOMMEPROD vs. Fonction personnelle

Bonjours,

Le sujet est résolu, j'ajoute simplement ici une comparaison du temps de calcul entre la fonction perso de SMBeccaria et la fonction Excel SOMMEPROD pour montrer le gain qu'il y a entre des boucles et les fonctions en Excel.

Test n°1: Fonction SOMMPROD // Fonction perso
A = 320 // 320

B = 8000 // 8000
C = 8000 // 8000
D = 8000 // 8000

Temps de calcul: 1,15 s à 1,8s // 33 s

------------------------------------------------

Test n°2: Fonction SOMMPROD
A = 6400 // 6400

B = 8000 // 8000
C = 8000 // 8000
D = 8000 // 8000

Temps de calcul: 24 s en .Value et 33 s en .Formula // 672 s

-----------------------------------------------

Le temps de calcul est divisé par 28 en utilisant un SOMMEPROD plutôt qu'en utilisant des boucles avec la fonction perso. Utiliser des tableau (travaille en mémoire vive) ne semble pas changer la donne pour la fonction perso.
Le temps de calcul en supprimant les lignes du code:
Code:

1 2 3 With Range(itext) .Value = .Value 'Transformation de Formule en Valeur End With
présant dans ce code:
Code:

1 2 3 4 5 6 7 8 With Range(itext1) .Formula = "=" & "SUMPRODUCT(" & ctext & "," & " SIN((" & dtext & " * " & atext & ") + RADIANS(" & etext & ")))" .AutoFill Destination:=Range(itext), Type:=xlFillDefault End With ActiveSheet.Range(itext).Calculate 'Obligatoire : Pour les cas où le calcule auto n'est pas actif, avec Nom classeur actif With Range(itext) .Value = .Value 'Transformation de Formule en Valeur End With
augmente très bizarrement le temps de calcul !!!!
C'est très bizarre, et le " ActiveSheet.Range(itext).Calculate " doit y être pour quelquechose. Pourtant, il est placé avant le " Range(itext).Value = .Value "

Donc, je pense qu'on peut dire que la fonction "AutoFill" associé à un ".Value= .Value" est très efficace.

Voilà.

-------------------------------- Mise à jour --------------------------------------

EDIT: J'arrive enfaite à 11,8 s en remplaçant:

Code:

ActiveSheet.Range(itext).Calculate

par:

Code:

Calculate

Donc, le ratio passe à 672/11,8 = 57

L'application du "Autofill" couplé à "Calculate" et à ".Value = .Value" est 57 fois plus rapide !!! Et maintenant, la différence entre .Formula et .Formula + .Value est négligeable. Le .Value = .Value ne prends pas de temps de calcul.