Komplexe Formel in VBA übertragen möglich?

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
Bild

Betrifft: Komplexe Formel in VBA übertragen möglich?
von: erichm
Geschrieben am: 18.04.2015 20:19:57

Hallo,
wir haben kürzlich für unser Projekt hier im Forum eine Formellösung erhalten, die perfekt funktioniert:
https://www.herber.de/forum/archiv/1416to1420/t1419215.htm
diese Formel:
=SUMMENPRODUKT(ZÄHLENWENN(Tabelle2!$B$100:$J$100;Tabelle3!$B$2:$AZ$4*(Tabelle3!$A$2:$A99=B$1)))
Projektbedingt mussten wir eine Umstellung bei den Tabellen vornehmen, siehe unten!
Das hat uns jetzt auf die Idee gebracht, dass wir das Projekt erheblich erweitern und modifizieren können. Allerdings stoßen wir mit der Formel auf ein Ressourcen- und Berechnungszeitproblem in EXCEL:
Wir würden die Formel gerne bei
ca. 65.000 Zeilen und
ca. 6.000 Spalten einsetzen
(In dem obigen Archivthread sind ja nur zwei Zeilen und zwei Spalten dargestellt.)
Jetzt sind wir am diskutieren, ob wir da auf ACCESS wechseln müssten (kennen wir uns aber kaum aus) oder ob evtl. eine VBA-Lösung diesen massiven Zahlenvergleich in einem überschaubaren Zeitraum bewältigen könnte.
Die Tabellen würden wir dann so aufbauen:
Tabelle1

 ABCD
1Nummer 12
2 Datum01.01.201502.01.2015
3101.01.201542
4202.01.201515

Formeln der Tabelle
ZelleFormel
C3=SUMMENPRODUKT(ZÄHLENWENN(Tabelle2!$A3:$G3;Tabelle3!$B$3:$AE$5*(Tabelle3!$A$3:$A100=C$2)))
D3=SUMMENPRODUKT(ZÄHLENWENN(Tabelle2!$A3:$G3;Tabelle3!$B$3:$AE$5*(Tabelle3!$A$3:$A100=D$2)))
C4=SUMMENPRODUKT(ZÄHLENWENN(Tabelle2!$A4:$G4;Tabelle3!$B$3:$AE$5*(Tabelle3!$A$3:$A101=C$2)))
D4=SUMMENPRODUKT(ZÄHLENWENN(Tabelle2!$A4:$G4;Tabelle3!$B$3:$AE$5*(Tabelle3!$A$3:$A101=D$2)))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Tabelle2

 ABCDEFG
1       
2       
301.01.201512343132
402.01.201516233343536


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Tabelle3

 ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1                               
2                               
301.01.2015123456789101112131415161718192021222324252627282930
402.01.201513233343536373839404142434445464748495051525354555657585960


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Ich hoffe, das ist alles verständlich soweit.
Besten Dank für eine Hilfe oder Empfehlung!
mfg

Bild

Betrifft: Besser Pivot?
von: lupo1
Geschrieben am: 19.04.2015 09:48:11
Eine SUMMENPRODUKT-Formel ist für ernsthafte Projekte möglicherweise nicht die Wahl, falls es schon jetzt ein Performanceproblem gibt.
Dann solltest Du für die Ausgangsdaten eine flexible Hilfsspalte vorsehen und diese per Pivot auswerten.
Pivot macht aus 10.000 Daten 10.000 Zuordnungen (bei egal wie vielen Klassen oder Ausprägungen, nehmen wir mal 200).
200 SUMMENPRODUKTe benötigen hingegen schon 2.000.000 Überprüfungen (also: "Zuordnungsversuche"); bei mehreren Bereichen in der Formel sind es auch 4.000.000 oder 6.000.000.

Bild

Betrifft: AW: Besser Pivot?
von: erichm
Geschrieben am: 19.04.2015 16:12:27
DANKE für die den Hinweis und die Idee.
Ich kenne zwar Pivottabellen, aber für einen Zahlenvergleich habe ich die noch nie benutzt und habe jetzt auch nicht entdeckt, wie ich aus zwei Ausgangstabellen (siehe oben Tabelle2 und Tabelle3) eine Hilfsspalte für die Daten zur Übertragung in eine Pivottabelle bringen kann.
Besten Danl für eine weitere Hilfe.
mfg


Bild

Betrifft: AW: Besser Pivot?
von: Wolfgang
Geschrieben am: 20.04.2015 17:33:16
Ich sehe da wirklich ein Problem, da Eure Zahlen anscheinend ungeordnet daherkommen und alle Bereiche komplett durchlaufen werden müssen, um das Ergebnis zu erzielen. Und bei 65.000 x 6.000 haben wir 390 Mio. Zellen, die zu beackern sind! Es wäre also dringend nötig, die Aufgabenstellung einzudampfen oder die Ausgangsdaten sortiert vorzulegen.
Ich persönlich würde sicherlich eine VBA-Lösung versuchen. Dann könnte man auch überlegen, ob man nur einen Tag zur Zeit auswertet und so den Rechenaufwand begrenzt. Oder dass man die Ergebnisse speichert und nur neu hinzukommende Daten rechnen lässt.
Ich lasse die Frage offen.
Gruß, Wolfgang

Bild

Betrifft: AW: Besser Pivot?
von: erichm
Geschrieben am: 20.04.2015 22:24:23
Hallo Wolfgang,
danke für die Hinweise.
Also wir sind ja noch am Anfang unserer Überlegungen, da wir noch nicht wissen ob wir die Erweiterung so umsetzen können. Allerdings wäre es unproblematisch, das Ganze in Etappen umzusetzen: wenn ein Teilbereich berechnet ist, wird sich dieser nicht mehr ändern und es könnte dann mit dem "nächsten Paket" weitergerechnet werden.
Dabei ist jedoch zwingend erforderlich:
In einen Berechnungsvorgang müssten immer die ca. 6.000 Spalten einbezogen werden. Also könnte man z.B. 5.000 Zeilen mit 6.000 Spalten berechnen und dann wiederum 5.0000...... usw. Beim Umfang bzw. bei der Anzahl der Zeilen sind wir flexibel, das heißt es könnten mehr oder weniger für einen Berechnungsvorgang sein.
Also wäre die Frage ob VBA ca. 5.000 Zeilen mit ca. 6.000 Spalten in einem überschaubaren Zeitraum berechnen kann.
Anmerkung:
Mir ist bei der Beschreibung jetzt aufgefallen, dass ich immer von ca. 6.000 Spalten schreibe. Da sind die ca. 6.000 Zeilen im ersten Beitrag in der Tabelle 2 gemeint. Der Spaltenbezug ergibt sich aus der ersten Tabelle mit den Formeln. Und die 65.000 Zeilen sind in der Tabelle3 enthalten. Das kam aus dem ersten Beitrag nicht so ganz eindeutig heraus.
Besten Dank nochmal!
mfg

Bild

Betrifft: Per UserForm starten!
von: erichm
Geschrieben am: 21.04.2015 06:32:24
Mir ist noch was eingefallen. Wir könnten die etappenweise Bearbeitung / Berechnung dadurch einbauen, dass wir eine UserForm vorschalten, mit der wir immer genau auswählen, welche Bereiche berechnet und wo die Ergebnisse eingetragen werden sollen.
Aus einem anderen Projekt habe ich so eine UserForm mal grob abgeleitet:
Userbild
Den Code dafür müssten wir dann noch entwickeln / anpassen.
Aber über diesen Weg wäre das eine komfortable Lösung.
Insofern müsste dann bei "Berechnung starten" die VBA-Formel eingebaut werden.
Besten Dank für eine weitere Hilfe!
mfg

Bild

Betrifft: AW: Per UserForm starten!
von: fcs
Geschrieben am: 21.04.2015 14:26:24
Hallo Erwin,
im Prinzip sind Standard-Funktionen in Excel schneller als entsprechende Nachbauten unter VBA.
Es gibt aber durchaus Ausnahmen bei großflächigen, komplexen Matrixfunktionen, da Excel dann gezwungen ist Daten temporär auszulagern.
Dann lohnt es sich ggf. die relevanten Daten per VBA in Datenarrays zu übernehmen und die komplette Auswertung im Arbeitsspeicher zu machen und dann das Ergebnis zurückzuschreiben.
Aber bevor man hier irgendetwas in dieser Richtung in Angriff nimmt solltest du eine kleine Excel-Beispieldatei erstellen (5 Zeilen/5 Spalten in Tabelle2, 20 Zeilen in Tabelle3, Formeln mit Auswertung der Daten in Tabelle1) und hier hochladen.
Außerdem wäre es interessant, mal zu erfahren wie die Quelle dieser Riesen-Datenmenge aussieht, evtl. ist ja viel sinnvoller dort anzusezen für eine Auswertung.
Gruß
Franz

Bild

Betrifft: AW: Per UserForm starten!
von: erichm
Geschrieben am: 22.04.2015 07:36:57
Hallo Franz,
danke für die interessanten Infos!
Zunächst die Beispieldatei:
https://www.herber.de/bbs/user/97217.xlsm
(die Formeln in der Ergebnistabelle habe ich an die aktuelle Musterdatei angepasst)
Die Quelle der Datenmengen kommen quasi aus mehreren unterschiedlichen (Teil-)Projekten und müssen zusammengeführt werden. Dies läuft quasi so ab, dass die Daten dann in die Tabellen 2 und 3 als "Zwischenergebnis" eingespielt werden.
mfg


Bild

Betrifft: AW: Per UserForm starten!
von: fcs
Geschrieben am: 22.04.2015 17:30:40
Hallo Erwin,
ich hab mal etwas gebastelt.
Es ist deutlich schneller als die Formeln.
Bei 6000 Spalten in Tabelle2 bzw. der Ergebnistabelle schafft das Makro jedoch auch "nur" ca. 100 Zeilen pro Minute.
Ich hab die Formeln nochmals etwas angepasst. Nach meinem Verständnis darf die Datumsspalte bei der Ermittlung der Anzahl Übereinstimmungen nicht mitgezählt werden.
Schau mal, ob du damit etwas anfangen kannst.
Grundsätzlich kann man so auch deine Idee umsetzen, ggf. per Userform den auszuwertenden Zeilenbereich vorzugeben. Da müßte dann noch ein wenig Feinschliff untergebracht werden.
Gruß
Franz
https://www.herber.de/bbs/user/97248.xlsm

Bild

Betrifft: AW: Per UserForm starten!
von: erichm
Geschrieben am: 23.04.2015 07:23:24
Hallo Franz,
DANKE!
Das sieht schon ganz gut aus (mit dem Code muss ich mich noch auseinandersetzen). Ich versuche heute abend oder morgen früh ein umfassendes Feedback zu geben - muss jetzt los :)
mfg

Bild

Betrifft: AW: Per UserForm starten!
von: erichm
Geschrieben am: 23.04.2015 19:27:10
Hallo Franz,
BIN BEGEISTERT!
Also ich habe die Daten jetzt mal auf 100 Zeilen und 1.000 Spalten ausgeweitet: in 4 Sekunden berechnet - KLASSE!!
Die Anpassung der Formel in der Tabelle "Formel" konnte ich nicht erkennen - oder war da nur die Anpassung in VBA gemeint?
Den Code selber verstehe ich leider nur zum Teil (und zu wenig). Es wird ja immer die Ergebnistabelle geleert. Wenn ich aber etappenweise rechnen lasse muss das bisher errechnete stehen bleiben und es müssen in der nächsten Zeile bzw. Spalte die VBA-Ergebnisse eingetragen werden.
Da blicke ich jetzt leider gar nicht durch, wie das funktionieren kann oder soll?
Beispiel:
bisher in Tabelle Ergebnis eingetragen:
100 Zeilen
1.000 Spalten
nächste Etappe müsste jetzt
ab Zeile 101 und
Spalte 1.001 rechnen und eintragen
Oder ist das zu kompliziert und man müsste immer die 6.000 Spalten mit 100 Zeilen und dann die nächsten 100 Zeilen mit 6.000 Spalten rechnen lassen?
Besten Dank nochmal!
mfg

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Komplexe Formel in VBA übertragen möglich?"