Office: SUMMENPRODUKT über VBA -> Performanz verbessern

Hallo,

ich habe mich heute hier angemeldet, weilich gerade ein bisschen verzweifle.

Es geht um die Anwendung von der Summenproduktformel via Makro, da insgesamt mehrere tausend (ca. 50.000 Zeilen x 20 Spalten) ausgefüllten werden müssen. Die normale Summenproduktformel ist dafür zu ineffizient.

Ich habe versucht die Formel von mücke aus Beitrag 1 auf mein Problem anzuwenden. Es klappt allerdings nicht wie erhofft.

Weiterhin muss ich es wohl mit einer/zwei Schleifen verbinden.

In Excel lautet die Summenformel für die Zelle H4 in Sheet3 wie folgt:

=SUMMENPRODUKT((Sheet1!$A$2:$A$50000=Sheet3!$A4)*(Sheet1!$E$2:$E$50000=Sheet3!H$ 3)*(Sheet1!$T$2:$T$50000))

Die Ergebnisse der Summenproduktformeln sollen jeweils in den Zellen H4 bis Spalte DC50000 geschrieben werden.

"Erschwerend" (zumindest für mich) kommt hinzu, dass ich mit 2 unterschiedelichn Arbeitsblättern arbeiten muss.

Ich hoffe jemand kann mir einen tip geben, wie ich den Code umgestalten muss, damit es funktioniert. Versuche mit Evaluate haben leider auch nix gebracht, wobei ich nun auch kein VBA Crack bin.


Excel Jeanie HTMLSheet3

	A	H	I	J
3	Unique Key	12640	14481	12410
4	25002132126104	880	0	0
5	25002132126105	7040	0	0
6	250021321261010	0	0	0
7	250021321261011	0	0	0
8	25006288144812	0	0,31	0
9	25011170124101	0	0	15087,5
10	25011170124102	0	0	14492,5
11	25011170124103	0	0	15997

Zelle	Formel
H4	=SUMMENPRODUKT((Sheet1!$A:$A000=Sheet3!$A4)*(Sheet1!$E:$E000=Sheet3!H)*(Sheet1!$T:$T000))
I4	=SUMMENPRODUKT((Sheet1!$A:$A000=Sheet3!$A4)*(Sheet1!$E:$E000=Sheet3!I)*(Sheet1!$T:$T000))
J4	=SUMMENPRODUKT((Sheet1!$A:$A000=Sheet3!$A4)*(Sheet1!$E:$E000=Sheet3!J)*(Sheet1!$T:$T000))

Excel Tabellen im Web darstellen >>

 

Hallo zusammen,

ich bin auf diesen Thread gestoßen und war eigentlich sehr glücklich weil ich ein ähnliches Problem habe.
Nur leider funktioniert die formel bei mir genauso langsam als wenn ich es excel berechnen lasse...

Zum Problem:
ich möchte mir Durchschnittswerte für bestimmte Kunden anzeigen lassen. Dazu lasse ich eine Liste auslesen die folgendermaßen aussieht:
Spalte I : Kundenname
Spalte AB : Kalkulierte Stunden
Spalte AA : Geleistete Stunden
Spalte AL : Zeitraum
Spalte AM : Effizienz = Kalkulierte Stunden / geleistete Stunden


Das ist mein Code:

pintZaehler : Allgemeiner Zaehler als Integer
cintZeile_Start : Konstante Integer
pintAnzahl_Zeilen : Allgemeine Variable mit der Anzahl der Zeilen die ich vorher auslese

Code:

Jemand ne Idee was ich falsch mache? Mit dem Dictionary hab ich es nicht hinbekommen weil ich nicht verstehe wie man da die Vergleichsoperatoren einbringt.

Vielen Dank im Voraus!!!

Gruß
Sebastian

 

Grüezi Sebastian

Die 'Arbeit' der Formel ist ja dieselbe, egal ob in Excel oder in VBA.


...aber warum verwendest Du nicht eine Pivot-Tabelle, ganz ohne VBA und Programmierung?

 

Hallo Thomas,

hatte irgendwie gehofft, dass das über VBA schneller geht.

Pivot kann ich nicht nehmen, da ich diese Werte darauf dann mit einer Pivot Tabelle verarbeite :-)

Vielleicht kann mir mal einer helfen, dass mittels dieser dictionary methode zu vereinfachen? Das scheint ja der deutlich schnellere Weg zu sein.

Momentan braucht er nämlich für meine Operation mit 10xxx Zeilen ca. 4 Minuten!!!

 

Würde es vielleicht noch was bringen wenn ich mir meine Ergebnisse erst in ein array schreibe und dann am Ende alles gleichzeitig nach Excel übertrage anstatt wie jetzt jeden Wert einzeln zu schreiben?

EDIT:
Selber beantwortet. Lasse ich das schreiben nach Excel weg ändert sich nicht viel. Es ist und bleibt die Sumproduct formel...

 

Hallo Sebastian,

mit Vba würde ich Folgendes versuchen:
- die Ausgangstabelle in ein ListObject' wandeln (als Tabelle formatieren)
- per Vba in der Auswertetabelle je Zeile/Zelle den Filter auf das ListObject setzen und das Ergebnis aus der Ergebniszeile dieses Objektes in die jeweilige Zeilenwerte übertragen

Andere Möglichkeiten einer Tempobeschleunigung sehe ich nicht; denn Vba ist immer langsamer als die Excel-Formellösungen.
Wenn Du eine Ausgangstabelle mit ein paar Testdaten, die Deinem Tabellenaufbau entsprechen, bereitstellst, mache ich mich mal ans Werk.-
Bitte die Datei als gezippte Datei hier hochladen, da ich hinter einer meterdicken Firewall sitze, die xlsx, xlsm, txt und xls von dieser Webseite blockiert!

Gruß von Luschi
aus klein-Paris

 

Hallo Luschi,

kann da leider auch nicht viel machen weil ein Teil meiner Liste eine Liste aus unserem Intranet ist :-)

Ist alles eine ganz schön verstrickte Sache...
Bin aber gerade an einer Lösung dran die ich dann gerne hier poste.

Das Stichwort ist Sumproduct durch Sumif zu ersetzen und dabei auf eine Hilfspalte zurückzugreifen.

 

so, es funktioniert und das mit 1:43 min anstatt 4:00 min

Das ist die Lösung:
man muss sich eine Zusatzspalte bauen und dann mit Sumif arbeiten.

Code: