Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 15:03:35
Unterschiedliche Dateien miteinander abgleichen lassen
25.04.2024 14:05:48
Makro in aktueller Zelle ausführen mit Doppelklick
25.04.2024 12:56:25
Werte von Kunden und Artikel stehen untereinander
25.04.2024 12:55:42
Funktionsparameter auslesen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1420to1424
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1420to1424
1420to1424
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Komplexe Formel in VBA übertragen möglich?

Komplexe Formel in VBA übertragen möglich?
18.04.2015 20:19:57
erichm
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

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Besser Pivot?
19.04.2015 09:48:11
lupo1
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.

AW: Besser Pivot?
19.04.2015 16:12:27
erichm
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

Anzeige
AW: Besser Pivot?
20.04.2015 17:33:16
Wolfgang
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

Anzeige
AW: Besser Pivot?
20.04.2015 22:24:23
erichm
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

Anzeige
Per UserForm starten!
21.04.2015 06:32:24
erichm
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

Anzeige
AW: Per UserForm starten!
21.04.2015 14:26:24
fcs
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

Anzeige
AW: Per UserForm starten!
22.04.2015 07:36:57
erichm
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

AW: Per UserForm starten!
22.04.2015 17:30:40
fcs
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

Anzeige
AW: Per UserForm starten!
23.04.2015 07:23:24
erichm
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

AW: Per UserForm starten!
23.04.2015 19:27:10
erichm
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
Anzeige

313 Forumthreads zu ähnlichen Themen

Mit VBA In Zelle Excelformel schreiben
Gho-Reg

Hi Leute
Ich habe hier diesen netten VBA Code geschrieben und leider wie solls auch anders sein kommt dauernd eine Fehlermeldung :)
CODE:
rowbeforeinteger = 2
Dim text As String
text = "=IF(" & epsoldcolumn & rowbeforeinteger & "=""x"";1;0)+IF(" & emailsoldco...
25.08.2008 11:08:33
Thread-ID: 1004403
6 Beiträge
Anzeige
Formel in Zelle per VBA einfügen
Werner

Wie kann ich Statistikformeln (Mittelwert, Maximum, Minimum, Standardabweichung) per VBA in eine Zelle einfügen?
Der zu berechnende Bereich geht von "$J$1" bis "$J$n" wobei n mit ActiveCell.Row-2 zuvor berechnet wird.
Gruß Werner.
10.09.2008 14:17:46
Thread-ID: 1008801
5 Beiträge
Formel in VBA
steffen

Hallo Leuts,
ich bekomme meine Formel, die in ein Tabellenblatt eingetragen werden soll, einfach nich hin:
ActiveSheet.Range("A4").Formula = "=Büromaterial!R?x?C?"
Wie stelle ich es jetzt an, dass R mit einer Zählvariablen x verbunden wird, sozusagen als Zähler, der bei jedem Durch...
17.09.2008 11:35:00
Thread-ID: 1010306
4 Beiträge
Anzeige
Kopieren VBA anstatt einer Formellösung
Bernhard

Hallo liebe Excel-Freunde,
wer kann mir helfen, eine Aufgabenstellung, die ich zwar mit Formeln gelöst habe mit einem VBA Makro zu lösen. Wie kopiere und paste ich Werte aus einer Tabelle1 in eine Tabelle2, abhängig davon ob ein Wert in einer bestimmten Spalte ist. Das Beispiel habe ich zum...
24.09.2008 17:28:00
Thread-ID: 1012129
9 Beiträge
Formel in VBA
balta

Hallo,
habe ein Problem mit meinem Code
Sub Testi() Dim lngZeile As Long For lngZeile = 2 To 100 Worksheets("Daten").Cells(lngZeile, 6).Formula = "=WENN($M2" & Chr(34) & ";ANZAHL2($M$ _ 2:$M2);"")" Next lngZeile End Sub
das Code soll Indexzahl ausgeben, also wenn Zelle M...
28.09.2008 00:15:02
Thread-ID: 1012872
5 Beiträge
Formel in VBA
steffen

Hallo,
ich komme mal wieder nicht mit meiner Formel in VBA zurecht.
Activesheet.Range("A4").Formula = "=INDIRECT(""Tabelle1!B"" &" & x + 1 & ")"; Formel gehört zu einem anderen Tabellenblatt
Das Problem besteht im Namen "Tabelle1". Da dieser Blattname steht's anders laut...
06.10.2008 11:46:00
Thread-ID: 1014389
9 Beiträge
TextBox Werte in Spalte Q ersetzen ?
Selma

Hallo Leute,
ich habe eine Frage zu UserForm. Beim Klick auf CommandButton1 sollen die Zahlen aus TextBox1 mit Werten in Spalte Q ab Zeile 12 ersetzt werden. Dies soll nur dann erfolgen, wenn in TextBox1 die Zahlen stehen bzw. der TextBox gefüllt ist.
Ersetzt sollen nur die vorhandene W...
13.10.2008 15:42:00
Thread-ID: 1016083
3 Beiträge
Array-Formel in VBA-Code umwandeln?
Robert

Hallo,
ich bräuchte bitte eure Hilfe.
Hab folgende Formel...
Selection.FormulaArray = _
=IF(ROW(R[-107]C[-3])>COUNTA('1'!R5C[4]:R200C[4]),"""",INDEX('1'!R5C4:R200C4,SMALL(IF('1'!R5C[4]:R200C[4]<>"""",ROW(R1:R196),""""),ROW(R[-107]C[-3]))))"
Mein Problem ist, dass ic...
26.10.2008 10:56:28
Thread-ID: 1019326
13 Beiträge
Anzeige

Links zu Excel-Dialogen

UserForm

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
5
11
4
3
2
8
4
8
2
12
4
2
6
1
3
14
5
5
8
4
8
4
6
8
4