Summenprodukt in VBA mit Vergleich klappt nicht
Wolfgang Habernoll
über die tollen Möglichkeiten von Summenprodukt wurde hier schon viel
berichtet. Ich bekomme es in der Tabelle auch hin z.B.
wenn wie hier in Spate A=Datum, B=Der Preis und in C=Der Artikel steht
funktioniert diese Formel.
=SUMMENPRODUKT((MONAT(A2:A11)=10)*(C2:C11="Lenkrad")*B2:B11)
Nun möchte ich das in VBA mit der entsprechenden
"WorksheetFunction.SumProduct" machen, bekomme aber den Vergleich nicht hin.
Hier ein Beispiel:
Set r1 = Worksheets("Liste").Range("B2:B11") ' Preis Numerisch
Set r2 = Worksheets("Liste").Range("C2:C11") 'Art String
Set r3 = Worksheets("Liste").Range("F2:F11") 'Test Numerisch
Debug.Print WorksheetFunction.SumProduct((r1,r3) ' klappt
Nun mit vergleichen eine von *vielen* ausprobierten ....
Debug.Print WorksheetFunction.SumProduct((r2="Lenkrad")*r1) klappt nicht,
ich geb's auf.
Hat jemand eine Idee was ich falsch mache ?
mfG
Wolfgang
Melanie Breden
"Wolfgang Habernoll" <wolfgang....@t-online.de> schrieb im Newsbeitrag
news:apbufm$39s$04$1...@news.t-online.com...
vielleicht hilft dir dieses Alternative?
Sub Summenprodukt()
Dim r2 As Range, Zelle As Range
Set r2 = Worksheets("Liste").Range("C2:C11")
Dim Anzahl As Integer
Dim Artikel As String
Artikel = InputBox("wähle einen Artikel")
If Artikel = "" Then Exit Sub
For Each Zelle In r2
If Zelle = Artikel And _
Zelle.Offset(0, -1) <> "" And _
Zelle.Offset(0, 3) <> "" Then
Anzahl = Anzahl + 1
End If
Next
MsgBox "Artikel " & Artikel & " steht " & Anzahl & " in der Liste"
End Sub
mit Gruß
Melanie
jensF
SUMMENPRODUKT macht in diesem Fall ja nichts anderes als
die Arrayformel
{=SUMME((MONAT(A2:A11)=10)*(C2:C11="Lenkrad")*B2:B11)}
nur ohne geschweifte Klammern.
Die Synthax von Arrayformeln klappt aber nicht so ohne
weiteres in VBA.
Mit EVALUATE kannst Du aber auch ArrayFormeln in VBA
benutzen:
MsgBox Evaluate("=SUMPRODUCT(Month(A2:A11=10)*
(C2:A11=""Lenkrad"")*B2:B11)")
bzw.
MsgBox Evaluate("=SUM(Month(A2:A11=10)*(C2:A11=""Lenkrad"")
*B2:B11)")
langt auch.
Oder Du gehst den Bereich mit einer "normalen" VBA-
Schleife durch.
Grüße
Jens
www.excelformeln.de
JensF
meine Antwort ist wohl nicht angekommen, oder dauert das
nur sehr lange hier ?
Also Arrayformeln wie
=SUMMENPRODUKT((MONAT(A2:A11)=10)*(C2:C11="Lenkrad")
*B2:B11)
bzw.
{=SUMME((MONAT(A2:A11)=10)*(C2:C11="Lenkrad")*B2:B11)}
kann man nur mit Hilfe von EVALUATE in VBA benutzen:
MsgBox Evaluate("=SUMPRODUCT(Month(A2:A11=10)*
(C2:A11=""Lenkrad"")*B2:B11)")
oder
MsgBox Evaluate("=SUM(Month(A2:A11=10)*(C2:A11=""Lenkrad"")
*B2:B11)")
Gruß
Jens
www.excelformeln.de
Wolfgang Habernoll
für eure Antworten. Urprünglich dachte ich ja auch an die "normale VBA
Schleife" war aber von der mächtigen SumProduct Formel, die das ganze in
einer Codeile erledigt, so begeistert das ich es unbedingt hinbekommen
wollte... *es muss* doch gehen,wo ist der Syntaxfehler ?
dank Jens weis ich es jetzt ja
>SUMMENPRODUKT macht in diesem Fall ja nichts anderes als die
>Arrayformel{=SUMME((MONAT(A2:A11)=10)*(C2:C11="Lenkrad")*B2:B11)}
>nur ohne geschweifte Klammern.
>Die Synthax von Arrayformeln klappt aber nicht so ohne weiteres in VBA.
da kann ich ja lange suchen ...
>Mit EVALUATE kannst Du aber auch ArrayFormeln in VBA benutzen:
>MsgBox
Evaluate("=SUMPRODUCT(Month(A2:A11=10)*(C2:A11=""Lenkrad"")*B2:B11)")
Das klappt ausgezeichnet, so sieht es jetzt aus
Debug.Print
Evaluate("SUMPRODUCT((Month(A2:A65000)=10)*(C2:C65000=""Lenkrad"")*B2:B65000
)")
(es fehlten um die Month = noch 2 Klammern) . Ich habe nun beide Methoden
(Fomel+Schleife) mal mit 65000 Zeilen getestet : Die Formel benötigt 0.5 Sek
die Schleife 20 Sek 40*schneller!!
Nun interessiert mich doch noch was *gegen* den Einsatz von EVALUATE
spricht, da ich nur Vorteile sehe.
mfG
Wolfgang
Wolfgang Habernoll
eine starke Sache diese Methode. Ich denke, da ja nichts gegen sie spricht,
sollte man öfters mal prüfen ob man sie einsetzen kann.
mfG
Wolfgang
"Frank Arendt-Theilen" <Thei...@t-online.de> schrieb im Newsbeitrag
news:1vdlruotnt0eue5ui...@4ax.com...
> Hallo Wolfgang,
> absolut nichts!
> Das gestellte Problem konnte mit der Evaluate-Methode komplett gelöst
> werden. Dies war Ziel der "Übung" :-))
> Die Evaluate-Methode ist ein in XL-integriertes Feature und muss daher
> für die Anwendung nicht erneut über den Basic-Interpreter in
> ausführbaren Code übersetzt werden, daher die Geschwindigkeit.
>
> MfG Frank
> _____________________________________________________
> Frank Arendt-Theilen, Microsoft MVP für Excel, Hameln
> eMail: Thei...@t-online.de, Homepage: http://www.xl-faq.de
>
JensF
zu diesem Thema kannst Du übroigens auch hier:
http://www.excelformeln.de/formeln.html?welcher=264
nachlesen.
Dort Weise ich auch auf den Geschwindigkeitsvorteil hin.
Es gibt aber auch einen nicht unerheblichen Nachteil, der
Formelausdruck ist ja ein String, willst Du die einzelnen
Bestandteile variabel halten, also die Bereiche oder
Suchkriterien, muß Du den String zusammensetzen etwa
Bereich1 = "A1:A65000"
Bereich2 = "B1:B65000
Kriterium = "Lenkrad"
Debug.Print
Evaluate("SUMPRODUCT((Month(" & Bereich1 & ")=10)*
(C2:C65000="& Kriterium &")*" & Bereich2 & ")")
das ist natürlich nicht gerade übersichtlich und schwer zu
warten. Also wenn da ein Fehler drin ist, brauche ich 3
mal so lang um dahinter zu kommen woran es liegt, als mit
einer "sauberen" VBA-Programmierung. Muß man halt abwägen.
Gruß
Jens
Frank Arendt-Theilen
JensF
einen Nachteil gibt es schon finde ich, Übersichtlichkeit
und debugging, siehe meinen Beitrag unten drunter.
Viele Grüße
Jens
www.excelformeln.de