Auswertung extrem langer Datenliste

Question

Hallo,

ich stehe öfters vor dem Problem, dass mir unendlich lange Listen vorliegen und ich eine Auswertung daraus mittels zahlreicher Spalten mit Formel erstelle. Alles im Excel natürlich, da kenne ich mich mittlerweile dementsprechend auch aus.

In den Auswertespalten sind Formeln mit SVERWEIS, MIN, MAX, EINDEUTIG, ZÄHLENWENN, SUMMEWENN, KKLEINSTE, ... enthalten.

Das ganze ist aber ab einer gewissen Anzahl an Datenzeilen nicht mehr lustig, denn die Berechnungen sind der pure Wahnsinn.

Wenn man z.B. 100k Zeilen hat und 15 Spalten mit Formeln, dann wird es schon mühsam.

Ich helfe mir, indem ich die Formeln nach Berechnung kopiere und als Werte einfüge, so dass die Berechnung jeweils wegfällt.

Auch habe ich schon probiert mittels Makro die Formeln zu kopieren, ist logischweise auch das gleiche Problem.

Wie stellt man solche "Auswertungen" effektiver an?

Könnt ihr mir hier weiterhelfen?

Danke

liebe Grüße

Manfred

Answer 1

Hallo Manfred,

das Problem kenne ich nur zu gut. Schon ab ca. 30.000 Datenzeilen wird Excel gerne etwas langsam. Bei >100.000 Zeilen steht es dann gern mal kurz vorm Kollaps. Da bleibt nur Warten bis die Berechnungen durchgeführt sind.

Ideen:

- Schalte im Ribbon "Formeln" unter Berechnungsoptionen auf manuelle Berechnung um. Dann kannst du zumindest mit den Daten arbeiten und die Formeln eingeben und runterziehen. Deren Berechnung musst du dann mit F9 selbst anstoßen. Die Wartezeit kannst du dann aber leider nicht umgehen.

- Anstelle der Formeln kannst du die Daten auch markieren und über Ribbon "Einfügen" eine Pivot-Tabelle erstellen. Die deckt in den meisten Fällen schon so ziemlich alles ab, was man wissen will und geht viel schneller als der Aufbau von Formeln und ist übersichtlich angeordnet. Beschäftige dich mal damit. Hilft vielleicht schon weiter.

- Wenn alles nichts nützt kannst du die Berechnungen auch per Makro in einer Schleife machen. Damit meine ich nicht die Formeln per Makro in die Zelle zu schreiben, sondern über Application.WorksheetFunctions in eine Variable zurückzugeben und deren Wert dann als Fixwert in die Zelle schreiben. Das sollte viel schneller gehen, weil nicht ständig die langsame Berechnung von Excel dazwischenfunkt. Zumindest sofern keine anderen Formeln vorhanden sind.

- Achte auch auf die Dateigröße. Bei 100.000 Datensätzen sollte sie so etwa um die 15 MB sein. Ist sie wesentlich höher hast du wahrscheinlich noch Leerzeilen gespeichert. Stichwort: UsedRange! Den kannst du mit den Tasten STRG+Ende ermitteln. Lösche Überflüssige Zeilen am Ende der Tabelle. Das mach Excel dann auch wieder ein Stück schneller.

Gruß Mr. K.

Answer 2

Hallo Community

Formeln nur im genutzten Bereich einfügen!

Volatile Formeln ersetzen

Matrixformeln ersetzen

oder auch Volatile und Matrixformeln weglassen und nur diese durch ein Makro berechnen lassen!

Zum besseren Verständnis einer Volatilen eigenschaft

Ohne Zellveränderung passiert dieses!

Schreibe dir mal eine Volatile Function mit einer MSG Box ,die MSG Box wird deinen Bildschirm in Sekundenbruchteilen zupflastern!

Denke jetzt aber nicht das du Fehler gemacht hast!

Verursacher ist in erster Linie MS

weitere verursacher die sogenannten Profis die Volatile und Matrixformeln vorschlagen!

Gruß Nighty

Offtopc Satire

Wenn ich Bettler wäre und jemand spendet mir 1 Eur ,dann wünschte ich mir das der Spender Volatile wäre und ich bin dann der reichste Mann der Welt!

Answer 3

Moin Moin Manfred,
wahrscheinlich wäre eine Datenbank wie beispielsweise MS_Access eine Überlegung wert. Mit einer halbwegs aktuellen Version des Excel kannst du aber auch ehebliche Geschwindigkeitsgewinne erzielen; vielleicht findest du xxxxxxxxxx (eventuell den Blog-Betreiber anschreiben).

Günther

*Admininfo: Dauer-Eigenwerbung gelöscht.

Answer 4

*Admininfo: Dauer-Eigenwerbung gelöscht.

Okay, wenn der (zielführende) Link auf (m)einen nicht-kommerziellen Blog nicht erlaubt / erwünscht ist, dann habe ich keine Lust mehr, mein Wissen in diesem Forum weiter zu geben. Das hat nämlich nichts mit Eigenwerbung zu tun! Schade…

Aber okay, der Hausherr bestimmt die Regeln und ich habe mich als Gast danach zu richten oder bleibe draußen. 

Günther

Answer 5

Hallo Günther

Wo bleibt deine Altersweisheit!

Ich möchte mich bei dir bedanken für deine langjährige Hilfe in beliebigen Foren ,wie viele andere sicherlich auch!

Ein kleiner Vers von Hermann Hesse sollte die Wogen glätten!

https://www.youtube.com/watch?v=IwqsrNSZECM

Gruß Nighty