Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1428to1432
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

Starker Ressourcenverbrauch durch neue Formeln?

Starker Ressourcenverbrauch durch neue Formeln?
03.06.2015 18:54:49
erichm
Hallo,
ich habe eine EXCEL-Datei (.xlsm) mit ca. 40 MB. Es bestehen ca. 20 Tabellen, davon drei Tabellen mit ca. 40.000 Zeilen, die anderen deutlich weniger Zeilen (bis 1.000); diverse kleinere Makros, ansonsten eine Vielzahl von Formeln, teilweise auf andere Tabellen zugreifend – alles „ganz normal“ und im grünen Bereich wenn die Datei neu rechnen muss (dauert ein bisschen…).
Jetzt hatte ich die Datei erweitert mit folgenden Formeln:
=WENN(ISTFEHLER(SVERWEIS(U2&V2;Tabelle1$A:$E;5;0));0;SVERWEIS(U2&V2;Tabelle1!$A:$E;5;0))
Die Formel steht in 12 Spalten; pro Spalte ca. 40.000 Zeilen.
Matrixformel:
{=MAX(WENN(Tabelle2!$O:$O=$B3;Tabelle2!FJ:FJ))}
Diese Formel steht in zwei Spalten mit jeweils ca. 600 Zeilen.
Nach dem einfügen und kopieren der Formeln erhielt ich ständig Fehlermeldungen:
„Für EXCEL waren beim Berechnen einer oder mehrerer Formeln nicht genügend Ressourcen vorhanden. Daher können diese Formeln nicht ausgewertet werden.“

Ich habe jetzt schon alles Mögliche probiert, Umfang der Spalten reduziert; jeweils die Formeln abwechselnd komplett entnommen. Grundsätzlich habe ich darauf geachtet, dass sonst keine anderen Anwendungen geöffnet sind (außer Firefox-Browser). Aber ohne Erfolg – einmal gings mit der ersten kompletten Formel – dann wiederum nicht.
Jetzt bin ich genervt und mit dem Latein am Ende. Dabei habe ich andere EXCEL-Dateien mit ca. 70 MB, die ohne Probleme (abgesehen von etwas längerer Rechenzeit) funktionieren.
Technische Daten:
- MS Office Professional Plus 2013
- Desktop-PC HP p6-2433eg
- Windows 8.1
- Intel® Core™ i5-3350P CPU @ 3.10 GHz
- RAM: 12,0 GB
- 64-Bit-Betriebssystem, x64-basierter Prozessor
Besten Dank wenn mir jemand einen Tipp hat; gibt’s vielleicht andere Formeln mit dem gleichen Ergebnis?
Mit freundlichen Grüßen

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Matrix-Formel ganze Spalte?
03.06.2015 19:06:00
RPP63
Hallo!
1. Statt WENN(ISTFEHLER()) WENNFEHLER() opt. halbe Berechnungsdauer.
2. und viel wichtiger: Matrixformel begrenzen!
Deine orgelt > 1.000.000 Zeilen durch, egal, ob nur 600 gefüllt sind!
Mit Excel > 2003 dürfte aber noch einiges an Potential brachliegen.
Gruß Ralf

AW: Matrix-Formel ganze Spalte?
03.06.2015 19:35:35
Daniel
und noch ne Optimierung für die Berechnungsdauer:
wenn die Datenquelle (Tabelle1$A:$E) für den SVerweis viele Daten enthält, dann
1. Tabelle1$A:$E nach Spalte A aufsteigend sortieren
2. folgenden SVerweis verwenden:
=WENN(SVERWEIS(U2&V2;Tabelle1$A:$A;1;1)=U2&V2;SVERWEIS(U2&V2;Tabelle1!$A:$E;5;1);0)
das von Ralf vorgeschlagene
=WennFehler(SVERWEIS(U2&V2;Tabelle1!$A:$E;5;0);0)
nimmst du, wenn die Tabelle nicht sortiert werden kann und du mit dem SVeweis mit 4. Parameter = 0 arbeiten musst.
Gruß Daniel

Anzeige
AW: Matrix-Formel ganze Spalte?
03.06.2015 21:31:56
erichm
Danke für die Rückmeldungen:
1. Sortieren der Spalte A gemäß Vorschlag Daniel leider nicht möglich.
2. Vorschläge von Ralf (WENNFEHLER + Matrixformel auf die betroffenen Zeilen eingeschränkt) umgesetzt:
EXCEL hat zumindest bei Berechnungsbeginn keinen Fehler angezeigt; rechnet aber schon eine ganze Weile, aktuell bei 3 % angekommen.
Mal sehen was daraus wird; melde mich natürlich nochmals!
mfg

AW: Matrix-Formel ganze Spalte?
04.06.2015 09:08:29
erichm
Leider haben die beiden vorgenommenen Änderungen noch zu keinem Erfolg geführt; nach mehreren Stunden habe ich die Berechnung abgebrochen.
Ich lasse das Thema mal offen in der Hoffnung auf den Hinweis von Ralf:
Mit Excel größer 2003 dürfte aber noch einiges an Potential brachliegen.

Vielleicht gibt es ja noch weitere Ansätze - Besten Dank!
mfg

Anzeige
Das hängt doch hoffentlich nicht ...
04.06.2015 09:53:21
Luc:-?
hiermit zusammen, Erich?!
Morrn, Luc :-?

AW: Das hängt doch hoffentlich nicht ...
04.06.2015 10:26:16
erichm
NEIN - das ist eine ganz andere Baustelle (siehe jeweils betroffene Anfragen bzw. Formeln). Habe momentan mit meinen EXCEL-Lösungen kein richtiges Glück :)
Zu dem anderen Thema komme ich im Laufe des Tages noch.
mfg

brachliegendes Potential? ...
04.06.2015 12:51:18
der
Hallo Erich,
... gibt es immer und überall, man muss es halt nur finden ;-)
Und finden kann man es vielleicht eher, wenn man mehr weiß und kennt. Kannst Du z.B. mal eine kleine Beispielarbeitsmappe (natürlich nur mit max 3 Tabellen und stark reduzierten Daten) hier einstellen und daran Deine grundsätzliche Zielstellung verbal erläutern?
Zunächst aber noch folgende Fragen:
- Excel größer 2003? Soll die Lösung auch für XL2007 noch gelten obwohl Du als Version 2013 vorgibst?
- Gibt es zusätzlich bedingte Formatierungen in Deinen Tabellen?
- Hast Du schon mal eine Untersuchung der Formeln auf Deinen großen Datentabellen auch ohne sonstiges "Begleitwerk" (andere Tabellenblätter; VBA) in einem gänzlich neuen Arbeitsmappe vorgenommen?
Noch ein kleines Detail.
Deine angegebene Matrixformel ließe sich reduzieren auf: {=MAX((Tabelle2!O:O=B3)*Tabelle2!F:F)}
Teste dies auch mal mit =MAX((Tabelle2!O1:O40000=B3)*Tabelle2!F1:F40000) oder ab XL2010 mit
=AGGREGAT(14;6;(Tabelle2!O:O=B3)*Tabelle2!F:F;1) (ohne {}!) und auch hier analog vorigem auch nur für den max. notwendigen Bereich.
Gruß Werner
.. , - ...

Anzeige
AW: brachliegendes Potential? ...
04.06.2015 16:28:58
erichm
WAHNSINN!!
Bereits durch Entnahme der Matrixformel und Verwendung der AGGREGAT-Formel ist meine vollständige Datei wieder komplett einsatzbereit (ca. 3 – 4 Minuten Berechnungsdauer); habe es bereits mehrmals mit Änderungen probiert : Alles im grünen Bereich – BESTEN DANK Werner!
Aber der Reihe nach:
- Die diversen Anregungen sind jetzt natürlich Ansporn, die Datei weiter zu optimieren; da habe ich bisher nicht so darauf geachtet!
- z.B. habe ich relativ viele SVERWEIS bzw. SUMMMEWENN-Formeln und dabei meistens keine Einschränkung im Zeilenbereich in den gesuchten Werten, sondern fast immer die gesamten Spalten benannt.
- Es gibt mittlerweile einige bedingte Formatierungen die eher dem Komfort als der Zweckmäßigkeit geschuldet sind, da kann ich reduzieren (kann ich später bei Bedarf ja immer wieder einbauen).
- Es bestehen WENN(ISTFEHLER() die ich auf WENNFEHLER() umstellen kann.
Ich werde das mal sukzessive abarbeiten und dann je nach Ergebnis überlegen, ob eine verkürzte Beispieldatei noch sinnvoll / notwendig ist. Da müsste ich evtl. zwei Dateien bereitstellen, da ein paar Formeln auf eine separate Datei auf dem PC zugreifen (dürften aber keine Ressourcenfresser sein).
Auf alle Fälle ist mein Problem schon mal gelöst und ich sage nochmals DANKE für die Hilfe!
Noch zu den Fragen von Werner:
- Excel größer 2003? Soll die Lösung auch für XL2007 noch gelten obwohl Du als Version 2013 vorgibst?
NEIN, EXCEL2013 reicht; habe nur den Hinweis von Ralf übernommen.
- Gibt es zusätzlich bedingte Formatierungen in Deinen Tabellen? JA, siehe Antwort soeben
- Hast Du schon mal eine Untersuchung der Formeln auf Deinen großen Datentabellen auch ohne sonstiges "Begleitwerk" (andere Tabellenblätter; VBA) in einem gänzlich neuen Arbeitsmappe vorgenommen?
NEIN, evtl. nicht mehr nötig :)
mfg

Anzeige
freut mich auch! Ergänzend, ...
04.06.2015 16:55:19
der
Hallo Erich,
... schau Dir auf jeden Fall nochmal die bedingte Formatierungen und da insbesondere auch deeren Bereichsdefinitionen an. Ich weiß nicht, ob in XL2013 die mir aus in XL2010 (und nur diese Version habe ich) bekannten Problem nicht mehr auftreten können. Z.B. durch Zellen- und/oder Zeilen- und/oder Spaltenverschiebungen/bzw. auch Kopien werden diese Bereichsdefinitionen gesplittet (meist unnötig) und damit sehr groß und damit sehr Ressourcen hungrig. Des-weiteren spielen die Anzahl der verwendeten Grafikobjekte und die Anzahl der Schriftfonts diesbzgl eine große Rolle.
Gruß Werner
.. , - ...

Anzeige
AW: freut mich auch! Ergänzend, ...
04.06.2015 19:30:44
erichm
Danke für die Ergänzungen;
Grafikobjekte habe ich keine, aber bei Schriftfonts kann ich sicherlich noch was berücksichtigen zur Optimierung. Und die Bedingten Formatierungen muss ich mir anschauen.
Eine Matrixformel habe ich noch die sehr oft vorkommt:
{=SUMME(ZÄHLENWENN($A$1:$L$1;Tabelle3!A8:L8))}
Bisher keine Verbesserungsmöglichkeit gefunden.
mfg

vielleicht eine Möglichkeit SUMMENPRDUKT() ...
05.06.2015 08:01:27
der
Hallo Erich,
... so: =SUMMENPRODUKT(ZÄHLENWENN($A$1:$L$1;Tabelle3!A8:L8)) wobei der Zeitgewinn nur minimal sein dürfte.
Gruß Werner
.. , - ...

AW: Starker Ressourcenverbrauch durch neue Formeln?
04.06.2015 14:55:22
KlausF
Hallo,
weis nicht, ob das weiter hilft, aber
unter dem Mac hat bei mir der SVERWEIS bei mehr als 6400 Zeilen immer angefangen rumzuspinnen.
Evtl. gibt es da eine (nicht dokumentierte) Limitierung(?). Habe allerdings bisher nichts dergleichen
finden können. Ich benutze seitdem, wenn möglich, INDEX.
Gruß
Klaus
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige