Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1332to1336
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

Welche Formel verbraucht wie viel Rechenpower

Welche Formel verbraucht wie viel Rechenpower
22.10.2013 17:55:57
nontacky
Hallo Allerseits,
ich habe mal wieder eine recht umfassende Excel Datei - mit aktuell 6.000 Einträgen, und jedes Jahr kommen ein paar tausend dazu. Im besten Fall will ich diese ganzen Daten aber in der Datei behalten und auch weiterhin zu all diesen Datensätzen Berechnungen anstellen (für Statistiken, etc.). Immer mehr wird diese Datei aber quälend langsam, und ich möchte mich jetzt mal dran machen die Formeln zu optimieren. Deswegen die Frage: kann ich irgendwo sehen welche Formeln bei der Berechnung immer am längsten brauchen? Ich habe SVerweis, Summewenn & Summewenns, Anzahl, Index, Matrixformeln, Vergleich. Teilweise gekoppelt mit ISTNVs
Wenn ich jetzt auf den Punkt sehen könnte welche Formeln besonders viel Rechenleistung fordern, könnte ich mir die direkt vorknöpfen.
Vielen Dank,
Martin

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Welche Formel verbraucht wie viel Rechenpower
22.10.2013 18:03:50
Uduuh
Hallo,
Matrixformeln brauchen viel Dampf.
Evtl. ist es sinnvoll auf Hilfsspalten auszuweichen.
Gruß aus’m Pott
Udo

AW: Welche Formel verbraucht wie viel Rechenpower
22.10.2013 18:11:30
nontacky
Hallo Udo,
ok - vielen Dank. Die Matrixformel kann ich auch umgehen indem ich sie nur einschalte wenn ich sie wirklich brauche (sonst nur mit Werten arbeite). Das wäre einmal im Monat, damit komme ich klar - das mache ich dann mal. Ist dann immer noch langsam, deswegen ist die Frage ob man irgendwie kategorisieren / prüfen kann, was jetzt am meisten Power braucht. Ein Hilfe in Excel gibt es dazu nicht?
VG

Anzeige
AW: Welche Formel verbraucht wie viel Rechenpower
22.10.2013 18:28:58
Matze
Hallo nontacky,
eventuell solltest du eine Musterdatei einstellen die deinem Aufbau entspricht mit ein paar Zeilen Daten. Die Profis wissen dann schon was zu tun ist. Wir wissen ja nicht wie komplex deine Mappe ist. Du solltest vor allem "volatile" Funktionen vermeiden zumindest wenn diese auch noch mehrfach vorkommen.
Matze

nicht wirklichzufriedenstellend zu beantworten ...
22.10.2013 18:20:23
neopa
Hallo Martin,
.. zunächst 600 Einträge; meinst Du damit Zellenwerte? oder Anzahl Zeilen?. Selbst 6000 Formeln sind an sich für einen Standard-PC im Normalfall noch keine wirkliche Hürde. Natürlich kosten Matrixformeln auch etwas mehr Ressourcen als Standardfunktionen. Doch viel entscheidender ist, ob Du die Matrixformeln z.B. über den gesamten Tabellenbereich (Zeilen oder Spalten) auswerten lässt oder nur auf wirklich max. notwendig auszuwertenden Zellbereich. Das gilt übrigens auch für die Matrix-Funktionen wie z.B. SUMMEWENNS()
Problemtisch können des weiteren auch nicht mehr existierende (intern wie extern) Bezüge und vor allem auch bedingte Formatierungen (gerade in XL2007) sein. Natürlich auch Grafiken und Diagramme.
Möglich wäre auch, das Du die Option "iterative Berechnung" aktiviert hast und Zirkelbezüge vorhanden sind. Und ...
Zusammenfassend: Ohne genauere Kenntnis Deiner Arbeitsmappenstruktur und Deiner Formeln kann wahrscheinlich nur der Blick in die Glaskugel Klarheit versprechen.
Gruß Werner
.. , - ...

Anzeige
AW: nicht wirklichzufriedenstellend zu beantworten ...
22.10.2013 18:36:14
nontacky
Hallo Werner,
vielen Dank für Deine Antwort. Ich eine 6000 Zeilen an Datensatz. Dazu gesellen sich dann ca. 8 Spalten die mit komplexen Formeln diese 6000 Zeilen an Datensatz auswerten. In der Tat mache ich die Formeln immer so, dass sie möglichst die gesamte Spalte (A:A) durchrechnen, das war mir nicht klar, dass das wirklich einen Unterschied macht, ich dachte Excel rechnet sowieso nur die Zeilen in denen was steht.
Ich gebe mal ein paar Beispiele von Formeln:
Einfacher SVerweis der aus dem Blatt "Kunden" den Kundennamen passend zu einer Email Adresse im Datensatz zieht:
=WENN(ISTNV(SVERWEIS(C9;Kunden!$D$7:$S$5000;16;FALSCH))=WAHR;"KUNDE FEHLT!";SVERWEIS(C9; Kunden!$D$7:$S$5000;16;FALSCH))
Diese Formel prüft ob eine Bestellnummer (Spalte G) in mehreren Zeilen vorkommt oder nicht und teilt dann den einzelnen Zeilen der Bestellung einen Prozentsatz in Höhe Ihres anteiligen Wertes der Gesamtbestellung zu:
=WENN(J11+P11=0;1;(J11+P11)/(SUMMEWENN(G:G;G11;P:P)+(SUMMEWENN(G:G;G11;J:J))))
Diese Formel prüft ob in einem der Kontoauszüge der vier Konten (MB, Giro, CC, Paypal) die es gibt, die Bestellnummer des Datensatzes vorkommt und zieht sich dann das Datum der Bestellung (mit noch ein wenig Extras)
=WENN(ISTFEHLER(WENN(AS120;AS12;WENN(S12="MB";SUMMEWENN(MB!F:F;G12;MB!I:I)/ZÄHLENWENN(MB!F:F;G12); WENN(S12="CC";SUMMEWENN(CC!F:F;G12;CC!L:L)/ZÄHLENWENN(CC!F:F;G12);WENN(S12="Paypal"; SUMMEWENN(Paypal!F:F;G12;Paypal!G:G)/ZÄHLENWENN(Paypal!F:F;G12);WENN(S12="Giro";SUMMEWENN(Giro!F:F; G12;Giro!I:I)/ZÄHLENWENN(Giro!F:F;G12);"KEIN EINTRAG"))))))=WAHR;E12;WENN(AS120;AS12;WENN(S12="MB"; SUMMEWENN(MB!F:F;G12;MB!I:I)/ZÄHLENWENN(MB!F:F;G12);WENN(S12="CC";SUMMEWENN(CC!F:F;G12;CC!L:L) /ZÄHLENWENN(CC!F:F;G12);WENN(S12="Paypal";SUMMEWENN(Paypal!F:F;G12;Paypal!G:G)/ZÄHLENWENN(Paypal!F:F; G12);WENN(S12="Giro";SUMMEWENN(Giro!F:F;G12;Giro!I:I)/ZÄHLENWENN(Giro!F:F;G12);"KEIN EINTRAG"))))))
Diese Formel checkt wiederum in welcher Zeile in einem der Kontoauszüge eine Bestellnummer vorkommt (damit man leichter abgleichen kann, wenn es mal einen Fehler gibt):
=WENN(WENN(ISTNV(VERGLEICH(F9;Giro!F:F;0));0;VERGLEICH(F9;Giro!F:F;0))+WENN(ISTNV(VERGLEICH(F9; Paypal!F:F;0));0;VERGLEICH(F9;Paypal!F:F;0))+WENN(ISTNV(VERGLEICH(F9;MB!F:F;0));0;VERGLEICH(F9; MB!F:F;0))+WENN(ISTNV(VERGLEICH(F9;CC!F:F;0));0;VERGLEICH(F9;CC!F:F;0))=0;"BELEG FEHLT!"; WENN(ISTNV(VERGLEICH(F9;Giro!F:F;0));0;VERGLEICH(F9;Giro!F:F;0))+WENN(ISTNV(VERGLEICH(F9;Paypal!F:F; 0));0;VERGLEICH(F9;Paypal!F:F;0))+WENN(ISTNV(VERGLEICH(F9;MB!F:F;0));0;VERGLEICH(F9;MB!F:F;0)) +WENN(ISTNV(VERGLEICH(F9;CC!F:F;0));0;VERGLEICH(F9;CC!F:F;0)))
Und so weiter und so fort :)
Welche von denen ist jetzt besonders heftig, wenn man das überhaupt so sagen kann?
VG

Anzeige
AW: nicht wirklichzufriedenstellend zu beantworten ...
22.10.2013 23:46:48
Daniel
Hi
heftig sind die alle, vorallem Zählenwenn und SummeWenn.
SVerweis und Vergleich sind auch heftig, aber hier besteht ne Chance, das zu optimieren.
hier mal am ersten Codebeispiel:
=WENN(ISTNV(SVERWEIS(C9;Kunden!$D$7:$S$5000;16;FALSCH))=WAHR;"KUNDE FEHLT!";SVERWEIS(C9;  Kunden!$D$7:$S$5000;16;FALSCH))  
die Optimierung würde so aussehen:
1. die Tabelle Kunden!D7:S5000 muss nach der Spalte D aufsteigend sortiert werden.
2. die Formel wird so abgeändert:
=Wenn(SVerweis(C9;Kunden!$D$7:$S$5000;1;WAHR)=C9;SVerweis(C9;Kunden!$D$7:$S$5000;16;WAHR); "Kunde fehlt") 

hier nutzt man aus, daß der SVerweis in sortierten Tabellen wesentlich schneller suchen kann, als in unsortierten.
das selbe Prinzip gilt auch für den Vergleich.
Gruß Daniel

Anzeige
für mich ist noch einiges offen ...
23.10.2013 09:06:20
neopa
Hallo nontacky,
... aus Deinen Formeln ist ersichtlich, dass Du Auswertungen von verschiedene Tabellenblattdaten vornimmst, es aber nicht eindeutig klar ist wieviel Formeln im Einsatz sind. Wenn ich Deine neuen Aussagen richtig interpretieren, hast Du im Tabellenblatt ca. (6.000x 8) 48.000 Formeln im Einsatz? Das wäre nicht wenig aber auch noch nicht allzuviel. Gibt es auch in den anderen Tabellenblätter weitere Formeln? Wieviel Formeln sind derartige Matrixformeln? Jedenfalls könntest Du schon etwas Luft bekommen, wenn Du wie bereits von mir geschrieben, dort nicht den gesamten Zeilenbereich sondern den notwendig auswerten lässt. (Dies gilt übrigens nicht für eigenständige einfache Funktionsformeln wie SUMME() oder z.B. RANG() etc.) Wieviel Datensätze stehen denn in der gesamten Arbeitsmappe, wenn bereits in KUNDEN schon 500 auszuwerten sind?
Zu meinen anderen Fragen/Hinweisen von gestern gibt es keine Aussagen?
Gruß Werner
.. , - ...

Anzeige
AW: für mich ist noch einiges offen ...
23.10.2013 09:36:09
nontacky
Hallo Werner, Matthias und Daniel,
vielen Dank für eure Antworten. Ich werde mich mal dran machen eine Musterdatei vorzubereiten. In der Tat ist die Sache doch etwas komplexer, es gibt doch mehr als nur einen Datensatz (da es ja noch die Kundenliste und die Konten gibt) und es gibt auch noch mehr Auswertungen.
Lösungen wie "aufsteigend sortieren" sind dabei leider nicht so richtig praktikabel, weil die Liste teilweise von Mitarbeitern gebraucht wird die komplett fast 0 Ahnung von Excel haben :). Denen dann zusätzlich zu der an sich eher komplexen Tabelle erklären zu müssen, wann sie wie und was aufsteigend sortieren müssen, wird wahrscheinlich in einer hohen Fehlerquote münden. Ich würde also zunächst am liebsten alle anderen Optimierungen machen und dann schauen wie die Performance ist. Erst dann könnte ich ja noch mal ausprobieren wie es mit solchen Anpassungen ausschaut die zusätzliche "Arbeit" der Tabellennutzer erfordert.

Anzeige
AW: Welche Formel verbraucht wie viel Rechenpower
23.10.2013 14:14:07
nontacky
Hallo Allerseits,
so - ich habe mal eine Muster Datei erstellt. Mit ein paar Datensätzen je für die Konten (Giro, Paypal & MB) + Kunden (11 Einträge) + Bestellungen (11 Bestellungen, teilweise mehrere Produkte pro Bestellung).
Ich weiß nicht ob das jetzt so direkt nach zu voll ziehen ist, wer mag kann sich das ja einfach mal anschauen. Die heftigen Formeln stehen größtenteils in der Tabelle "Bestellungen" (Spalten: D, X, Y, AB, AH-AN, AR, AU, AW) und "BuHa" (Spalten: B, C, D, E, M, N, R). Als allgemeine Hilfe: die Spalten die hellblau eingefärbt sind, sind zur Eingabe da und enthalten keine Formeln.
Die Datei konnte ich nicht direkt hochladen, weil sie deutlich größer ist als 300KB. Deswegen könnt ihr euch die hier runterladen:
www.dynamedion.com/download/Abrechnung_BOOM_Muster.zip
Vielen Dank und viele Grüße,
Martin

Anzeige
zunächst ändere die Formeln wie beschrieben ...
23.10.2013 20:26:40
neopa
Hallo Martin,
... ersetzte doch erst einmal wie bereits geschrieben in Deiner Originaldatei in Spalte AB die Bereichsangaben über die ganze Tabelle durch entsprechend begrenzte Datenbereiche. Analog in den anderen Spalten, wo dies ähnlich noch vorhanden sein sollte. Ergibt sich da bereits ein merklicher Effekt?
Und im Weiteren beschränke ebenso die bedingte Formatierung auf die erforderlichen Bereiche.
Gruß Werner
.. , - ...

Formeln geändert ..
24.10.2013 18:53:03
nontacky
Hallo Werner,
ich habe jetzt mal alle Formeln so weit es ging eingeschränkt auf deutlich kleinere Bereiche (entweder 5000 Zeilen oder 10000 Zeilen). Bedingte Formatierung gibt es nur in einer Spalte, dazu noch in einer anderen Spalte Dropdown Auswahl (keine Ahnung ob das auch was frisst).
Leider hat sich nicht wirklich was verändert :) .. Gefühlt einen Tick schneller, aber so richtig heilsbringend ist das noch nicht. Weiß denn jemand eine Reihenfolge - welche Funktionen am schlimmsten sind? Man kann ja schon einiges machen indem man es durch ein Makro ersetzt (= also eine Spalte per Knopfdruck ausrechnen lassen indem man die entsprechenden Funktionen reinkopiert, ausrechnen lässt und dann durch Werte wieder ersetzt), aber ich möchte diese Art der manuellen Einflussnahme natürlich möglichst begrenzen.
Viele Grüße,
Martin

Anzeige
hmmm ...
25.10.2013 16:56:38
neopa
Hallo Martin,
... dann bringt eine zwar mögliche Formeloptimierung wahrscheinlich auch nicht mehr allzuviel. Wenn Du willst und auchdarfst, kannst Du mir ja mal Deine Arbeitsmappe mailen. Ich kann und werde nichts versprechen, außer dass ich Deine Datei nicht eigene Zwecke verwende oder dritten weiterleite. Ich könnte es mir am Sonntag vielleicht mal anschauen. Wenn, dann an "neopa at email Punkt de". Vielleicht ...
Gruß Werner
.. , - ...

AW: hmmm ...
28.10.2013 17:09:43
nontacky
hallo Werner,
danke für Deine nette Antwort. Ich habe Dir mal eine Datei geschickt, leider bin ich vorher nicht dazu gekommen, weil ich übers WE weg war.
Viele Grüße,
Martin

Anzeige
Mail ist angekommen ...
28.10.2013 17:45:24
neopa
Hallo Martin,
... allerdings komme ich wahrscheinlich nicht vor Freitag dazu, wirklich reinzuschauen und wie bereits geschrieben Erfolg kann und werd ich nicht versprechen.
Gruß Werner
.. , - ...

AW: Mail ist angekommen ...
29.10.2013 08:42:31
nontacky
Hallo Werner,
vielen Dank, kein Problem - Du kannst Dir so viel Zeit lassen wie Du möchtest, ist ja total nett, dass Du das überhaupt auf Dich nimmst. Ich versuche hier im Rahmen meiner Möglichkeiten weiterhin ein wenig zu optimieren.
Gruß, M.
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge