Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1572to1576
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

SUMMEWENNS sehr langsam

SUMMEWENNS sehr langsam
07.08.2017 09:01:07
cH_rI_sI
Guten Morgen!
Ich verwende eine SUMMEWENNS-Formel welche ich über ca. 85.000 Zeilen anwende - die Berechnung läuft ca. 20 min. - gibt es hier eventuell eine schnellere Alternative? Matrixformel?
Bitte um Eure Vorschläge - Danke!
Lg,
Chrisi

29
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA
07.08.2017 09:05:43
Fennek
Hallo,
wenn man VBA etwas geschickt programmiert, ist es wesentlich schneller. D.h. die Werte in Arrays zu übergeben und dort alle Berechnungen durchführen.
Vermutung: ca 10 Sekunden.
mfg
AW: VBA
07.08.2017 09:06:41
cH_rI_sI
Aber ohne VBA geht hier nichts oder?
AW: VBA
07.08.2017 09:07:50
SF
Hola,
ohne irgendwas zur Datei/Formel zu wissen:
ja, nein, vielleicht
Gruß,
steve1da
AW: VBA
07.08.2017 10:15:28
cH_rI_sI
Die Formel sieht so aus:

=SUMMEWENNS('PPM Import Database - pre-month'!G:G;'PPM Import Database - pre-month'!K:K;K5; 'PPM Import Database - pre-month'!D:D;D5;'PPM Import Database - pre-month'!N:N;N5; 'PPM Import Database - pre-month'!L:L;L5;'PPM Import Database - pre-month'!C:C;C5)) 

Anzeige
Du bist ungenau, denn ...
07.08.2017 10:00:42
lupo1
... Du verwendest die Formel nicht 1x, sondern z.B. 10.000x, mit demnach 850.000.000 Auswertungsvergleichen, multipliziert mit der Anzahl der Kriterien.
SUMMEWENNS lässt sich - aufgrund seiner Einschränkungen - fast immer durch eine Pivot-Tabelle ersetzen. Und da werden die 85.000 Datensätze nur einmal aufgeteilt.
Nachbemerkung: Da SUMMENPRODUKT (langsamer) alles das kann, was SUMMEWENNS unmöglich ist, kann man dessen darüber hinaus möglichen Verwendungen NICHT durch Pivot ersetzen.
AW: SUMMEWENNS sehr langsam
07.08.2017 10:25:17
cH_rI_sI
@ all: Anbei eine Beispieldatei mit 200 anstatt 85.000 Zeilen
https://www.herber.de/bbs/user/115296.xlsx
Danke im Voraus für Eure Hilfe!!!
Lg,
Chrisi
Anzeige
AW: SUMMEWENNS sehr langsam
07.08.2017 11:01:52
Luschi
Hallo cH_rI_sI,
was erwartest Du jetzt für Wunder. Deine massenhaften Daten erwarten auch eine vernünftige Planung und bessere Technologien als:
- 'PPM Import Database - pre-month'!G:G
Diese planlose Übergabe von Bereichsparametern über ganze maximale Gesamtbereiche ist genau so als ob jemand die Besucher der Innenstadt von London zählen soll, ohne ihn zu verraten, das z.Z. gerade die Leichtathletik-WM dort stattfindet.
Gestern beim Marathon-Lauf hätte man da schon seine Probleme beim Ausführen der Aufgabe gehabt.
Außerdem gibt es auch für Excel Besserers als die stupiden internen Funktionen wie:
- Summe(), SummeWenn(), SummenProdukt() u.u.u
Da wären z.B.
- PowerQuery bzw. PowerPivot
Aber davon will ja die uninteressierte Gemeinde nichts wissen, wenn es um Massendaten sich handelt;
das ist wie beim Billigeinkauf in den tollen Supermärkten:
- Eierskandal
- Rinderwahnsinn
- Schweinepest, Karninchenfäule
- u.u.u.
Luschi aus klein-Paris
PS: es würde mich nicht wundern, wenn Du dann das Alles auch noch auf einem IPad realisieren willst!
Anzeige
AW: SUMMEWENNS sehr langsam
07.08.2017 11:09:27
cH_rI_sI
Hallo Luschi,
ich dachte eigentlich, hier wird einem geholfen - aber eigentlich werde ich nur blöd angemacht...
Eigentlich solltest Du wissen, dass Excel 2007 kein Powerquery unterstützt...
Wenn Du mir doch noch helfen möchtest - gerne.
Lg,
Chrisi
Fragen
07.08.2017 11:06:07
lupo1
1. Sind beide Tabellen im Original ca. 85.000 Sätze lang? Dann landest Du - bei 1 Summenfeld und 4 Kriterien - sogar bei 36 Mrd. Berechnungen (wozu auch Vergleiche gehören).
2. Bezüge statt G:G ändert man besser auf G$1:G$85000. Aber auch das wird hier nicht viel bringen, da Excel das eigentlich selbst erkennt. Eigentlich!
3. Falls Du beide Tabellen sortierst, so dass die Summen nacheinander folgen, kannst Du mit intelligentem VERGLEICH arbeiten (braucht eine Hilfsspalte). Die Rechenzeit geht dann auf ca. 3 Sekunden.
Anzeige
AW: Fragen
07.08.2017 11:14:23
cH_rI_sI
Hallo Lupo,
danke für deine Rückmeldung!
Zu 1: Ja - beide Tabellen sind fast gleich lang (Unterschied sind vielleicht 5.000 Einträge)
Zu 2: ist nicht unbedingt erforderlich oder? Weil über das Jahr gesehen wächst die Tabelle auf unbestimmte Größe an - daher kann ich nicht sagen, ob eine Limitierung auf 100.000 schon reicht...
Zu 3: Was meinst Du mit Sortierst? Ich setze nachher lediglich einen Filter... Kannst Du mir eventuell ein Beispiel in meiner Beispieldatei geben, wie das mit derm VERGLEICH funktioniert? Bitte, Danke!!!
Lg,
Chrisi
Fragen (Fortsetzung 1)
07.08.2017 11:09:56
lupo1
4. Falls Du im Pre-Month Daten hast, die es im lfd. Mo. nicht gibt, fallen die bei der Kontrollsumme unter den Tisch! Willst Du das?
Anzeige
AW: Fragen (Fortsetzung 1)
07.08.2017 11:15:53
cH_rI_sI
Die Pre-Month hat immer weniger Daten als die andere Tabelle - daher sollte es keine Probleme geben...
angemacht?
07.08.2017 11:12:28
lupo1
Ich wollte gerade eine Formel liefern, aber das unterbleibt jetzt.
AW: angemacht?
07.08.2017 11:18:16
cH_rI_sI
War ja nicht auf Dich bezogen sondern auf Luschi... Der hat ja nur rumgemault, aber keinen konstruktiven Vorschläge gemacht...
Wäre Dir sehr dankbar wenn Du mir helfen würdest.
Lg,
Chrisi
mit dieser Matrixformel?
07.08.2017 12:55:03
cH_rI_sI
Meintest Du so in etwa?
=INDEX('PPM Import Database - pre-month'!$A$5:$O$100000;VERGLEICH(C5&D5&K5&L5&N5; 'PPM Import Database - pre-month'!$C$5:$C$100000&'PPM Import Database - pre-month'!$D$5:$D$100000&'PPM Import Database - pre-month'!$K$5:$K$100000&'PPM Import Database - pre-month'!$L$5:$L$100000&'PPM Import Database - pre-month'!$N$5:$N$100000; 0);7) 
Berechnung dauert aber ebenfalls lange...
Anzeige
AW: SUMMEWENNS sehr langsam
07.08.2017 13:35:16
cH_rI_sI
Sollte ich jemanden beleidigt haben, möchte ich mich hiermit entschuldigen!
AW: VBA Dictionary
07.08.2017 14:36:28
Fennek
Hallo,
anbei ein VBA-Code: im Bsp-Datensatz scheint es zu funktionieren, im Originalsatz müßte "application.transpose" ersetz werden.
Beide Sheets werden jeweils in ein Array geladen und 1-Mal durchlaufen. Die Verknüpfung erfolgt im Dictionary.
Bitte testen!

Sub Fen2()
Anf = Timer
Dim Res()
Dim WSF As WorksheetFunction: Set WSF = Application.WorksheetFunction
PreM = Sheets("PPM Import Database - pre-month").Cells(1).CurrentRegion
ActM = Sheets("PPM Import Database").Cells(1).CurrentRegion
With CreateObject("Scripting.Dictionary")
For i = 2 To UBound(ActM)
iStr = Join(Array(ActM(i, 11), ActM(i, 4), ActM(i, 14), ActM(i, 12), ActM(i, 3)), "|")
y = .Item(iStr)
Next i
'summieren
For i = 2 To UBound(PreM)
iStr = Join(Array(ActM(i, 11), ActM(i, 4), ActM(i, 14), ActM(i, 12), ActM(i, 3)), "|")
If .exists(iStr) Then .Item(iStr) = .Item(iStr) + PreM(i, 7)
Next i
Sheets("PPM Import Database").Cells(2, "Q").Resize(.Count + 1) = Application.Transpose(. _
items)
End With
Debug.Print Timer - Anf
End Sub
mfg
(Bitte an einem Datensatz mit ca 30.000 Zeilen testen)
Anzeige
AW: VBA Dictionary
07.08.2017 15:17:03
cH_rI_sI
Hallo Fennek,
vielen lieben Dank für dein Coding - läuft in der Beispieldatei einwandfrei!!!
Ich werde dies heute Abend an dem kompletten Datensatz probieren...
Gibt es deiner Meinung nach eine brauchbare Formel-Alternative, welche eine vergleichbare Performance hat?
Lg,
Chrisi
AW: mit Formel
07.08.2017 15:50:38
Daniel
Hi
mit Formeln wird's da etwas aufwendig bei dieser Datenmenge.
für eine schnelle Berechnung per Formel müsste man bei dieser Datenmenge so vorgehen:
in der Datenquelle:
1. fasse in einer zusätzlichen Spalte alle benötigten Kriterien zu einem Gesamttext zusammen (mit & verketten) und füge als letzen Buchstaben noch ein "x" hinzu (ist egal was, aber kein "a")
2. sortiere die Datenquelle nach dieser Hilfsspalte aufsteigend
in der Zieltabelle
1. fasse in einer Hilfsspalte die benötigten Kriterien zu einem Gesamttext zusammen, gleiche Reihenfolge und gleiches Trennzeichen wie in der Datenquelle verwenden.
hier ohne zusätzliches Zeichen am ende
2. ermittle die erste Zeile in der Quelle mit der Formel
=Vergleich(Gesamttext & "a"; Hilfsspalte in Quelle; 1)+1
3. ermittle die letzte Zeile in der Quelle mit dieser Formel:
=Vergleich(Gesamttext & "z"; Hilfsspalte in Quelle; 1)
4. Bilde jetzt die gewünschte Summe über die Index-Funktion:
=Summe(Index(WerteSpalte_Quelle;ersteZeile):Index(WerteSpalte_Quelle_letzteZeile))
Wenn es in der Zieldatei mehrere Zeilen mit den gleichen Kriteren gibt, kannst du auch die Zieltabelle nach dem Gesamttext sortieren und die Berechnung dann nur in der ersten Zeile ausführen (Wenn Gesamttext Gesamttext Zeile drüber) und ansonsten einfach den Werte aus der Zeile drüber übernehmen.
Wenn du die Listen wieder in eine andere Reihenfolge zurücksortieren musst, musst du die Formeln kopieren und als Wert einfügen.
Gruß Daniel
Anzeige
AW: VBA Dictionary
07.08.2017 15:33:49
cH_rI_sI
Eine Frage habe ich noch: Beim Originalfile beginnen die Daten erst bei Zeile 5 - ich habe _ daher

For i = 2 To UBound(ActM)
auf

For i = 5 To UBound(ActM)
und

For i = 2 To UBound(PreM)
auf

For i = 5 To UBound(PreM)
geändert.
Aber nun bekomme ich den Fehler "Typen unverträglich" - aber warum?
Anbei nochmal das angepasste Beispielfile:
https://www.herber.de/bbs/user/115312.xlsm
Danke schonmal für deine / eure erneute Hilfe!!!
Lg,
Chrisi
Anzeige
AW: VBA Dictionary
07.08.2017 16:07:02
Daniel
Hi
das musst tu hier anpassen:
PreM = Sheets("PPM Import Database - pre-month").Cells(4, 1).CurrentRegion
ActM = Sheets("PPM Import Database").Cells(4, 1).CurrentRegion
wenn oberhalb der Tabelle noch was steht, muss die Zeile direkt über der Überschrift (Zeile 3) komplett leer sein, damit das .Currentregion die Tabelle korrekt ermitteln kann.
der Startpunkt für die Schleifen bleibt bei 2.
Gruß Daniel
AW: VBA Dictionary angepasst
07.08.2017 16:19:12
Fennek
Hallo,
so klappt es auch ab Zeile 5 mit beliebig vielen Zeilen.

Sub vergleich()
Anf = Timer
Dim Res(), Rs()
Dim WSF As WorksheetFunction: Set WSF = Application.WorksheetFunction
PreM = Sheets("PPM Import Database - pre-month").Cells(4, 1).CurrentRegion
ActM = Sheets("PPM Import Database").Cells(4, 1).CurrentRegion
With CreateObject("Scripting.Dictionary")
For i = 2 To UBound(ActM)
iStr = Join(Array(ActM(i, 11), ActM(i, 4), ActM(i, 14), ActM(i, 12), ActM(i, 3)), "|")
y = .Item(iStr)
Next i
'summieren
For i = 2 To UBound(PreM)
iStr = Join(Array(ActM(i, 11), ActM(i, 4), ActM(i, 14), ActM(i, 12), ActM(i, 3)), "|")
If .exists(iStr) Then .Item(iStr) = .Item(iStr) + PreM(i, 7)
Next i
'Ausgabe
Res = .items
ReDim Rs(UBound(ActM) + 3, 0)
For i = 0 To .Count - 1
Rs(i, 0) = Res(i)
Next i
Sheets("PPM Import Database").Cells(5, "Q").Resize(.Count) = Rs
End With
Debug.Print Timer - Anf
End Sub
mfg
AW: VBA Dictionary angepasst
08.08.2017 06:44:02
cH_rI_sI
Guten Morgen Fennek!
Vielen Dank für den angepassten Code - das Ergebnis wird auch ab Zeile 5 geschrieben, jedoch ist das Ganze um 1 Zeile verschoben:
Userbild
Wenn ich

Sheets("PPM Import Database").Cells(5, "Q").Resize(.Count) = Rs
auf

Sheets("PPM Import Database").Cells(6, "Q").Resize(.Count) = Rs
ändere, dann passt nun die Position, jedoch fehlt in Zeile 5 der Wert.
Vielleicht magst nochmal drüber schauen - vielen Dank!
Lg,
Chrisi
AW: VBA Dictionary angepasst
08.08.2017 08:10:05
cH_rI_sI
Hallo Fennek,
bei genauerer Betrachtung habe ich festgestellt, dass z.B. bei Zeile 74903 der Wert um 48 Zeilen verschoben ist?!
D.h. die erste Annahme, dass alles um lediglich eine Zeile verschoben ist, stimmt nicht.
Danke für deine Unterstützung!
Lg,
Chrisi
AW: VBA Dictionary angepasst
08.08.2017 09:39:41
cH_rI_sI
Hallo nochmal,
ich wollte jetzt eine neue Beispieldatei posten, aber wenn ich die Einträge auf ~ 2500 reduziere und das Makro nochmal laufen lasse, dann stimmt alles - vorher waren aber schon zu Beginn alles um 1 Zeile verschoben bzw. noch mehr...
Ich verstehe das Ganze nicht...
Lg,
Chrisi
AW: VBA Dictionary angepasst
08.08.2017 10:38:42
cH_rI_sI
So jetzt weiß ich was nicht stimmt - wenn die Sortierung beider Tabellen nicht gleich ist, funktioniert das Ganze nicht mehr - aber warum? Der Vergleich sollte immer funktionieren, egal wie die Sortierung ist...
Anbei das Beispielfile, wo ich die Sortierung geändert habe und somit die Werte nicht stimmen:
https://www.herber.de/bbs/user/115331.xlsm
Bitte um Eure Hilfe - Danke!
Lg,
Chrisi
AW: VBA Dictionary angepasst
08.08.2017 23:39:51
Christian
Hey, ich mag mich täuschen, aber ein ganz klassischer copy paste bug....
so sollte es funktionieren:
Sub Vergleichen()
Anf = Timer
Dim Res(), Rs()
Dim WSF As WorksheetFunction: Set WSF = Application.WorksheetFunction
PreM = Sheets("PPM Import Database - pre-month").Cells(4, 1).CurrentRegion
ActM = Sheets("PPM Import Database").Cells(4, 1).CurrentRegion
With CreateObject("Scripting.Dictionary")
For i = 2 To UBound(ActM)
iStr = Join(Array(ActM(i, 11), ActM(i, 4), ActM(i, 14), ActM(i, 12), ActM(i, 3)), "|" _
)
y = .Item(iStr)
Next i
'summieren
For i = 2 To UBound(PreM)
iStr = Join(Array(PreM(i, 11), PreM(i, 4), PreM(i, 14), PreM(i, 12), PreM(i, 3)), "|" _
)
If .exists(iStr) Then .Item(iStr) = .Item(iStr) + PreM(i, 7)
Next i
'Ausgabe
Res = .items
ReDim Rs(UBound(ActM) + 3, 0)
For i = 0 To .Count - 1
Rs(i, 0) = Res(i)
Next i
Sheets("PPM Import Database").Cells(5, "Q").Resize(.Count) = Rs
End With
Debug.Print Timer - Anf
End Sub
Problem: In der Schleife des 'Summieren-Blocks wurde das falsche (gleiche) Array angesprochen....
Daher die merkwürdig Sortierung :-)
VG,Ch.
Danke Christian für deine Hilfe!!!
09.08.2017 09:57:40
cH_rI_sI
CLOSED
Danke Christian für deine Hilfe!!!
09.08.2017 09:57:41
cH_rI_sI
CLOSED
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge