Office: SUMMENPRODUKT über VBA -> Performanz verbessern

Moin allerseits,

hab mich mal mit der Funktion Summenprodukt unter VBA auseinandergesetzt ... ist ja man gar nicht so einfach dieses Thema *boah

Ziel war es, Stunden aus Spalte H (im Vergleich von Spalte A (Nr.) und Spalte F (KW)) zu summieren … mit folgender Formel ja auch kein Problem: Code:

Mein Problem war die Performanz dieser Formel, denn wenn man diese auf einen größeren Bereich anwenden (runter ziehen) will, stürzt Excel ohne zu zögern gerne mal ab. *frown.gif*

Also musste eine Lauffähige-VBA-Lösung her ... mit nachstehendem CODE habe ich die Performanz (bei über 20.000 Zeilen) auf unter 15 Sekunden gedrückt! *dance
Code:

Meine Frage an die VBA-Meister, könnte es noch schneller gehen? ... oder gibt es eine Alternative zu Evaluate("=SUMPRODUCT(....)?
Zum besseren Verständnis häng ich mal eine Beispielmappe an.

Schon mal vielen Dank für Deine Zeit!

:)

 

Hallo Dirk
Bin zwar kein VBA-Meister aber:

Bestimmt. ;-)
Versuch mal selber.
Public Sub test()
Dim arr
Dim L As Long
Dim objDic As Object
Dim strtmp As String
Dim Start As Double
Start = Timer
Set objDic = CreateObject("Scripting.Dictionary")
arr = Range("A1").CurrentRegion
Redim out(1 To UBound(arr), 1 To 1)
For L = LBound(arr) To UBound(arr)
strtmp = arr(L, 1) & "DUMMY" & arr(L, 6)
objDic(strtmp) = objDic(strtmp) + arr(L, 8)
out(L, 1) = objDic(strtmp)
Next
Range("I1").Resize(UBound(out)) = out
Debug.Print Timer - Start
End Sub

Glaube nicht da da jetzt noch viel drin ist...
ransi

 

Hi Dirk,

sorry bin auch kein VBA-Meister, aber hier auch mein bescheidener Versuch:

Code:

 

Hallo ransi,

oh doch ... du bist ein VBA-Meister ... oder sollte ich lieber Meister der Array´s sagen *wink.gif*

Vielen Dank für Deine schnelle und perfekte Unterstützung.

Dein CODE läuft mit SCHALLGESCHWINDIGKEIT und ist in < 0,5 Sekunden fertig! ... dann werd ich mich mal ans verstehen begeben.

Vielen Dank ransi *hands

 

Hallo Jürden,

du Meister der Bescheidenheit *wink.gif*

Vielen Dank für Deinen "Versuch"! Laufzeit < 5 Sekunden, aber an die Schallgeschwindigkeit von ransi kommt er nicht ran.
Trotzdem vielen Dank Jürgen, so habe ich jetzt mehrere Varianten die ich in aller Ruhe vergleichen und verinnerlichen kann!

 

Hi Dirk,

gegen das Dictionary ist einfach kein Kraut gewachsen! *wink.gif* *Smilie

 

Hallo zusammen,

nein, viel geht da nicht mehr!

Eine kleine Änderung vielleicht noch:
Anstelle LateBinding...
Code:

...EarlyBinding (mit Verweis auf Microsoft Scripting Runtime) verwenden
Code:

Testtabelle mit rund 60k Daten:
0,4063 zu 0,3215 Sekunden, also rund 20% Zeitersparnis.
(Wenn's denn sein muss *wink.gif*)

Am Ende aber bitte noch ein
Code:

hinzufügen.
(Das muss/sollte sein *wink.gif*)

Was mir vorher nicht so klar war und sehr gut gefällt:
Das vorherige Zusammensetzen des Strings für den Zugriff auf das Dictionary
Code:

gibt nochmal einen guten Temposchub, wenn der String mehrfach verwendet wird, wie im Beispiel.
Das merk ich mir! *idee

@Dirk
Das wäre übrigens auch hier eine Methode, um noch etwas Zeit rauszuholen:
VBA - MATRIX-Formel über VBA -> zu langsam

Grüße
EarlFred

 

Hallo Zusammen
Erstemal, schoener Thread zum lernen. Danke!

Zeitemal, da faellt mir noch was ein:

*wink.gif*
gruss hary

 

Moin EarlFred,

hätte nicht gedacht, das die Zeit von ransi noch unterboten werden kann … Respekt!
Bei der Datenmenge >20.000 habe ich Bestzeiten (ransi 0,314 Sek. / EarlFred 0,265 Sek.) gemessen, die weit unter meiner Vorstellung des Machbaren liegen … das sollte für alle weiteren Erweiterungen der Mappe ausreichend sein *wink.gif* ... ich will ja zwischendurch auch noch (m)einen Kaffee genießen *biggrin.gif*

Nochmals vielen Dank an alle Beteiligten, ich könnt EUCH *knuddel

 

Hallo zusammen,

bin neu hier und muss mal gleich tief in die Kiste greifen und einen alten Thread herausholen, aber das Ding ist einfach zu gut. *mrcool

Ich bin auch bisher mit Summeprodukt unterwegs gewesen, muss aber jetzt auf Grund der Datenvolumina umsteigen.

@ransi: Ich habe ein Frage. Dein Code gibt ja immer den bis dahin aufsummierten Wert für die entsprechende Stringkombination mit und schreibt jedes mal einen neuen Datensatz wenn ein weiterer Wert hinzukommt.

Kann man auch ohne große Umstände nur den Endwert ausgeben lassen? Ich meine den letzten Wert, der für eine Stringkombination nach Durchlauf aller Datensätze erzeugt wurde. Also am Beispiel der ersten beiden Datensätze von Dirks Tabelle:

Jahr__KW__Std___Ergebnis
2002__1____8_____8
2002__1____8_____16

Nehmen wir an, dass wäre die ganze Tabelle ... so erzeugt das Dictionary ja zwei Datensätze. Für jede Zeile einen. Mich interessiert aber kein Zwischenergebnis ( hier die "8") sondern nur das Endergebnis (die "16").

Gibt es dafür eine schlanke Lösung? Oder muss ich mir mit einem Kunstgriff im Nachgang die entsprechenden Zeilen raussuchen?

Vielen Dank vorab?

VG, HeGlu

 

HAllo

Ne, der Code überschreibt einfach in dem vorhandenem Datensatz die vorhandene Summe mit der neuen Summe.

Schau es dir mal an:
Test

	A	B	C	D	E	F	G	H
1	Nr.	Überschrift 2	Überschrift 3	Überschrift 4	Überschrift 5	KW	Überschrift 6	Stunden
2	2002					1		8,00
3	2002					1		8,00
4	2002					1		8,00
5	2002					1		8,00
6	2002					1		8,00
7	2002					1		8,00

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Public Sub test()
Dim arr
Dim L As Long
Dim objDic As Object
Dim strtmp As String
Set objDic = CreateObject("Scripting.Dictionary")
arr = Range("A2:H7")
'ReDim out(1 To UBound(arr), 1 To 1)
For L = LBound(arr) To UBound(arr)
strtmp = arr(L, 1) & "DUMMY" & arr(L, 6)
objDic(strtmp) = arr(L, 8)
MsgBox "arr(" & L & ", 1)" & vbCrLf & "objDic.Count: " & objDic.Count & vbCrLf & strtmp & "-->" & objDic(strtmp)
Next
'Range("I1").Resize(UBound(out)) = out
'Debug.Print Timer - Start
End Sub

ransi

 

Hallo Ransi,

danke für die schnelle Antwort. Hast mich auf jeden Fall einen großen Schritt weitergebracht. Bleiben wir mal bei der Variante mit der Summenbildung:

Das Scripting Dictionary enthält also tatsächlich alle Daten die ich brauche. Nämlich die summierten Werte für alle eingelesenen Stringkombinationen. Das habe ich soweit begriffen. Aber wie komme ich an die Daten ran. Anders gefragt, gibt es eine Möglichkeit den Inhalt von "objDic" auszulesen ohne ihm die Stringkombination als Kriterium angeben zu müssen? Im Sinne von "Gib mir alles was du hast!".

Falls nicht müsste ich ja erst alle angefallenen Stringkombinationen in einem Array "protokollieren", dann sortieren, die Duplikate entfernen und dann aus dem "objDic" abfragen. Richtig? Oder gibt es einen eleganteren Weg?

Danke und Gruß,
HeGlu

 

Hallo

In dem Dictionary wirst du keine Duplikate mehr finden.
DAS ist ja der Clou des Dictionarys.

Auslesen aller Unikate geht ganz einfach:
Diese Daten:
Test

	A	B	C	D	E	F	G	H
1	Nr.	Überschrift 2	Überschrift 3	Überschrift 4	Überschrift 5	KW	Überschrift 6	Stunden
2	2002					1		8,00
3	2002					1		8,00
4	2002					2		8,00
5	2004					1		8,00
6	2004					1		8,00
7	2004					2		8,00
8	2006					1		8,00
9	2006					2		8,00
10	2006					3		8,00
11	2006					4		8,00
12	2009					2		8,00
13	2009					2		8,00
14	2009					2		8,00
15	2009					2		8,00
16	2010					1		8,00
17	2010					1		8,00
18	2010					2		8,00
19	2010					3		8,00
20	2010					4		8,00

Excel Tabellen im Web darstellen >>

 

\@ransi: Super, danke für die Infos. Mit dem Dictionary waren wir (ein Arbeitskollege und ich) in der Lage die Berechnung einer wichtigen Auswertung von 2 Stunden (mit Summenprodukt) auf 200 Sekunden zu drücken!!! *grins Perfekt

 

Grüezi HeGlu

Hmmm, wann immer ich von grossen Datenmengen und Auswertungen mit SUMMENPRODUKT() höre/lese fällt mir spontan die Pivot-Tabelle ein.

Habt ich schon damit versucht eure Auswertung zu erstellen?