Excel performance steigern

[Binary91]

Hallo zusammen,

ich habe eine Exceltabelle mit 10.000 potentiellen Datensätzen, die vom Anwender ausgefüllt werden und von Excel validiert werden können.
Diese Datensätze existieren in 5 Tabellen pro Arbeitsmappe. Das heisst also 5x10.000 Formeln, die im Hintergrund arbeiten und auch noch tabellenübergreifend auf eine Setup-Tabelle zugreifen, teilweise mit INDIREKT-Verweisen.

Das sorgt dafür, dass bereits beim Laden der Arbeitsmappe viel Zeit verstreicht. Ganz zu schweigen von den unangenehmen Verzögerungen beim Befüllen der Datensätze, man kann nicht mehr von flüssigem Workflow sprechen.

Daher meine Frage: Gibt es eine Möglichkeit, Formeln nur berechnen zu lassen wenn eine bestimmte Bedingung zutrifft. Ich würde quasi die Formel zuerst checken lassen, ob der jeweilige Datensatz überhaupt gefüllt ist oder ob nur leere Zellen vorhanden sind und falls ja, sollte die eigentliche Formelauswertung mit den INDIREKT-Verweisen usw. gar nicht erst stattfinden.

Ist so etwas möglich?

Weiterhin frage ich mich, ob es grosse Performance-Einbussen gibt bei Abfragen tabellenübergreifend und ob es vielleicht Rechenzeit ersparen würde, das Setup nicht auszulagern, sondern in jedem Tabellenblatt stehen zu haben. Das macht zwar die Wartung komplizierter, aber für die Performance würde ich es machen.

Vielen Dank bereits vorab für Eure Beiträge.

LG Binary

[Rabe]

Hi,

evtl. solltet ihr überlegen, auf ein Datenbank-Programm wie Access umzusteigen. Excel ist für solche Datenmengen nicht geeignet!

[snb]

Man kann auf andere Daten verlinken mittels ein Querytable.

Die Formel können dann Werten finden im Querytable statt in eine externe Datei.

Eine Beispieldatei könnte mal helfen deine Frage richtig zu beantworten.

[LCohen]

Man kann auch alle - bis auf die erste Formel - plattmachen (=Werte überkopieren) und diese Formel nur bei Bedarf - also wenn Änderungen vorliegen - wieder ausfüllen.

Dafür braucht man auch kein Wissen außerhalb von Tabellenkalkulation.

[Binary91]

Hallo,

danke schon einmal für die Antworten.


@Rabe:
Hätte ich sehr gerne verwendet, leider können die Anwender nur mit Excel umgehen..

@LCohen:
Verstehe ich nicht ganz. Wer füllt dann die Formelzelle später aus? Der Nutzer soll nicht scripten müssen, und VBA ist keine Option. Wie meinst Du das genau?

Viele Grüsse
Binary91

[Der Steuerfuzzi]

Hallo,

da wir die Formeln nicht kennen, können wir Dir leider auch keine Verbesserungen vorschlagen.

Es gibt viele Gründe für langsame Berechnungen, z.B. wenn bei jeder Berechnung die gleiche Zeilenzahl und Spaltenzahl berechnet werden. Oder wenn Datumsbereiche durchsucht werden, die nicht relevant sind, etc. Das ganze kann man in der Performance verbessern, indem man z.B. Werte einmal berechnet und alle Formeln darauf Bezug nehmen. oder man den Datumsbereich vorab eingrenzt.

INDIREKT ist grundsätzlich bei Performance-Problemen nicht die beste Wahl (da volatil, das bedeutet, es wird häufiger eine Berechnung angestoßen, als bei einer nicht volatilen Funktion).

[Binary91]

Danke für die schnelle Rückmeldung.
Man muss es sich so vorstellen:
Die Datensätze bestehen aus Spalte A-J (10 Spalten), und rechts daneben im ausgeblendeten Bereich stehen pro Datensatz in den Spalten K-T je eine Formel, die die Gültigkeit der korrespondierenden editierbaren Zelle prüfen. Beispiel:
In Spalte A kann der Anwender das Datum des Datensatzes eintragen. Korrespondierende Prüfspalte K überprüft nun, ob ein gültiges Datum eingegeben wurde. Je nach Gültigkeit gibt es dann Wert 0 (Fehler, bspw. Datum ausserhalb eines definierten Bereichs in der Setup-Tabelle), Wert 1 (valide) und Wert 2 (Warnung, bspw. Datum überschreitet einen Grenzwert).
Spalte B ist eine vorgegebene DropDownListe mit Werten, die per Datenüberprüfung aus einer Spalte der Setup-Tabelle gezogen werden.
Da es leider meines Wissens keine Funktion gibt, die das Überschreiben von Zellformaten per Strg-C verhindert, muss ich leider trotz vorhandener DDL mit einer separaten Formel prüfen, ob der Zellinhalt auch in der Setup-Tabelle zu finden ist. Da die Setup-Tabelle jedoch erweiterbar ist, muss dies über einen INDIREKT-Befehl geschehen.

So zieht sich die Datenprüfung über die zehn Spalten und jede Zelle prüft immer nur den korrespondierenden Wert des editierbaren Bereichs. Meines Erachtens werden keine Werte doppelt berechnet.

Ich kann die Formeln alle posten, aber das ist ne ganze Menge..

Könnt ihr damit etwas anfangen?

LG Binary91

[Der Steuerfuzzi]

Man muss es sich so vorstellen: [...] Ich kann die Formeln alle posten, aber das ist ne ganze Menge..

Nein, Excel-Dateien mit den Formeln und ein paar wenigen aussagekräfigten und anonymisierten Daten reicht und ist besser als jede Erläuterung.

Da die Setup-Tabelle jedoch erweiterbar ist, muss dies über einen INDIREKT-Befehl geschehen.

Diese Aussage kann ich jetzt nicht nachvollziehen. Was heißt erweiterbar? Warum geht das nur per INDIREKT?

[snb]

In Spalte A kann der Anwender das Datum des Datensatzes eintragen. Korrespondierende Prüfspalte K überprüft nun, ob ein gültiges Datum eingegeben wurde.

Kennst du dich aus mit 'Validation' ?

[LCohen]

@LCohen: Verstehe ich nicht ganz. Wer füllt dann die Formelzelle später aus? Der Nutzer soll nicht scripten müssen, und VBA ist keine Option. Wie meinst Du das genau?

Ach so, dann dürfen bei Dir die User die Formeln gar nicht selbst erweitern. Also maximale Anfänger.

"Plattmachen" geht so: Angenommen, die Formeln stehen in F2:K9999, untereinander immer die gleichen. Dann markierst Du F3:K9999 (also ohne die erste Zeile) und fügst die Werte auf sich selbst ein.

Muss nun mal wieder alles gerechnet werden, markierst Du wieder F2:K9999 und führst Strg-U aus.

Eine weitere Möglichkeit ohne dieses Prozedere wäre die Aufteilung auf zwei Dateien A und B. B (mit Formeln) holt sich die Eingaben über Formelbezüge aus A (dort keine Formeln). B ist normalerweise geschlossen und rechnet daher dann auch nur, wenn es geöffnet wird.

Das ergibt aber nur Sinn, wenn man die Einstellung "manuelle Berechnung" hasst.