Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Matrixformeln machen Excel langsam

Forumthread: Matrixformeln machen Excel langsam

Matrixformeln machen Excel langsam
21.02.2020 11:18:03
VoBiA
Hallo Zusammen,
ich habe hier ein Tabellenblatt mit über tausend Matrixformeln die Excel extrem ausbremsen, hier ein Beispiel dieser Matrix-Formel:
{=WENNFEHLER(INDEX(Measures!$B:$B;KKLEINSTE(WENN(Measures!$A$2:$A$9999""; WENN(Measures!$Q$2:$Q$9999="Nein";WENN(Measures!$G$2:$G$9999=$A$2;WENN(Measures!$F$2:$F$9999=A$1; ZEILE(INDIREKT("$2:$9999"))))));ZEILE($A1)));"") }

Meine Frage ist nun, lässt sich das beschleunigen?
Vielen Dank im Voraus
Grüße
VoBiA
Anzeige

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Matrixformeln machen Excel langsam
21.02.2020 11:22:39
onur
Bestimmt- aber ohne zu wissen, was du mit der Formel erreichen willst .....
AW: Matrixformeln machen Excel langsam
21.02.2020 12:04:04
VoBiA
Ok hast Recht ;)
Ich habe ein Tabellenblatt "Measures" mit einem Haufen Daten die aus einer Datenbank gefüllt ist.
In einem extra Tabellenblatt "Übersicht" versuche ich nun die relevanten Daten sortiert darzustellen.
Die Matrix-Formeln stehen alle im Tabellenblatt "Übersicht" ab Zeile 3.
Die Zeile 1 ist in vier Spaltenüberschriften aufgeteilt (A+B= Neue Beanstandung, C+D= Vorklärung, E+F= Report erstellen, G+H= Wiederverwerten.
In den Zellen A2, A20, A40, A60, A90, A120 und A130 stehen jeweils Typen Namen.
In A3:H19, A:21:A39 und so weiter, sollen jetzt die Daten entsprechend der Matrixformeln aufgelistet werden.
Zuerst sehe ich ob in der Spalte A von "Measures" Daten stehen, dann suche ich nach dem Wert "Nein" in Spalte Q von "Measures", denn nur diese Zeilen sollen ausgelesen werden.
Dann vergleiche ich die Typen aus Spalte G von "Measures" mit den Typen in A2 von "Übersicht", ebenso vergleiche ich die Spalte F von "Measures" mit der Spaltenüberschrift vom Tabellenblatt "Übersicht".
Das ganze funktioniert soweit ganz gut ist aber ewig langsam.
Jetzt soll ich hier noch weitere Werte in weitere Spalten aufnehmen, was Excel wahrscheinlich ganz einfrieren lässt.
Anzeige
AW: Matrixformeln machen Excel langsam
21.02.2020 15:12:27
VoBiA
Okay, habe jetzt eine stark gekürzte Excel Datei als Beispiel hochgeladen:
https://www.herber.de/bbs/user/135359.xlsm
Damit könnt ihr bestimmt mehr anfangen als mit meinem Erklärungsversuch. ;-)
Grüße
VoBiA
AW: Matrixformeln machen Excel langsam
21.02.2020 15:20:50
onur
Muss die Ausgabe alphab. sortiert sein oder ist das nur ein Nebeneffekt?
Anzeige
AW: Matrixformeln machen Excel langsam
21.02.2020 15:34:08
VoBiA
Muss sie eigentlich nicht, ist sie doch auch gar nicht, oder verstehe ich was falsch?
AW: Matrixformeln machen Excel langsam
25.02.2020 11:51:12
VoBiA
Hallo onur,
zuerst hatte ich ein paar Probleme, aber jetzt wo ich die Matrixformeln entfernt und mir deinen Code angeschaut habe, sehe ich erst wie genial er ist.
Ich verstehe zwar noch nicht alles aber echt genial, 1000 Dank dafür.
Grüße
VoBiA
Anzeige
AW: Matrixformeln machen Excel langsam
21.02.2020 16:12:50
Daniel
HI
1. passe die Zellbezüge der Matrixformeln so gut wie möglich an die vorhandene Datenmenge an.
Matrixformeln rechnen immer alle angegebenen Zellen durch, daher kann man so aufwand minimieren
2. vermeide es, Matrixformeln mit volatilen Funktionen (Indirekt, Bereich.Verschieben) zu kombinieren.
diese Funktionen sorgen dafür, dass deine Formeln unnötig oft neu berechnet werden.
3. überlege dir, ob du durch Sortierung oder geschickte Hilfsspalten das Formelwerk und den Berechnungsaufwand verringern kannst.
Gruß Daniel
Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Matrixformeln effizient nutzen und Excel beschleunigen


Schritt-für-Schritt-Anleitung

  1. Zellbezüge optimieren: Überprüfe die Zellbezüge deiner Matrixformeln und passe sie an die tatsächlich verwendeten Daten an. So kannst du die Berechnungsgeschwindigkeit verbessern, da Excel nicht unnötig viele Zellen durchlaufen muss.

  2. Volatile Funktionen vermeiden: Vermeide den Einsatz von volatilen Funktionen wie INDIREKT oder BEREICH.VERSCHIEBEN in deinen Matrixformeln. Diese Funktionen sorgen dafür, dass deine Formeln häufig neu berechnet werden, was die Excel-Berechnung verlangsamt.

  3. Hilfsspalten nutzen: Überlege, ob Du Hilfsspalten einführen kannst, um die Daten vorab zu filtern oder zu sortieren. So kannst du den Aufwand der Matrixformeln reduzieren.

  4. Matrix fixieren: Wenn du mit großen Datenmengen arbeitest, kann es hilfreich sein, die Matrixformeln in den Zellen zu fixieren. Das kannst du erreichen, indem du die Formeln in Werte umwandelst, wenn die Berechnung abgeschlossen ist.

  5. Excel 8 Threads nutzen: Stelle sicher, dass deine Excel-Version die Berechnung über mehrere Threads unterstützt. In den Excel-Optionen kannst du die Anzahl der Threads anpassen, um die Excel-Berechnung zu beschleunigen.


Häufige Fehler und Lösungen

  • Formeln berechnen nicht korrekt: Überprüfe, ob alle Zellbezüge richtig gesetzt sind und ob du keine unnötigen leeren Zellen in deinen Bereichen hast.

  • Excel friert ein: Wenn Excel häufig einfriert, kann das an einer hohen Anzahl an Matrixformeln liegen. Überlege, ob du die Struktur deiner Daten anpassen oder die Anzahl der Formeln reduzieren kannst.

  • Langsame Berechnung: Wenn die Excel-Berechnung zu lange dauert, teste die oben genannten Schritte zur Optimierung der Zellbezüge und vermeide volatile Funktionen.


Alternative Methoden

  • Pivot-Tabellen nutzen: Statt aufwendiger Matrixformeln kannst du auch Pivot-Tabellen verwenden, um Daten effizient zu aggregieren und zu analysieren. Dies kann die Berechnung erheblich beschleunigen.

  • Power Query: Eine weitere Alternative zur Verarbeitung großer Datenmengen ist Power Query. Damit kannst du Daten aus verschiedenen Quellen abrufen und transformieren, ohne auf komplexe Matrixformeln zurückgreifen zu müssen.


Praktische Beispiele

Ein Beispiel für eine Matrixformel, die die oben genannten Prinzipien beachtet:

{=WENNFEHLER(INDEX(Measures!$B$2:$B$9999;KKLEINSTE(WENN(Measures!$A$2:$A$9999<>"";WENN(Measures!$Q$2:$Q$9999="Nein";WENN(Measures!$G$2:$G$9999=A$2;ZEILE(INDIREKT("$2:$9999")))));ZEILE($A1)));"")}

In diesem Beispiel wird sichergestellt, dass die Matrixformel nur die relevanten Zeilen und Spalten durchläuft, um die Berechnung zu beschleunigen.


Tipps für Profis

  • Datenbankfunktionen: Nutze Excel-Datenbankfunktionen wie DBSUMME oder DBANZAHL, wenn du mit großen Datenmengen arbeitest. Diese Funktionen sind oft schneller als Matrixformeln.

  • Daten sortieren: Sortiere deine Daten in der "Measures"-Tabelle, bevor du sie in den Matrixformeln verwendest. Dies kann die Effizienz der Berechnung erheblich steigern.

  • Regelmäßige Berechnung: Setze die Berechnung von Excel auf manuell, wenn du umfangreiche Änderungen vornimmst. So kannst du die Berechnung nur dann ausführen, wenn du bereit bist, und die Leistung zwischen den Änderungen erhöhen.


FAQ: Häufige Fragen

1. Wie kann ich meine Excel-Berechnung beschleunigen?
Optimiere Zellbezüge, vermeide volatile Funktionen und nutze Hilfsspalten, um die Anzahl der zu berechnenden Zellen zu reduzieren.

2. Was sind die besten Alternativen zu Matrixformeln?
Pivot-Tabellen und Power Query sind hervorragende Alternativen, die eine schnellere Verarbeitung großer Datenmengen ermöglichen.

3. Warum wird Excel bei der Verwendung von Matrixformeln langsam?
Matrixformeln rechnen oft mit vielen Zellen, was zu einer hohen Belastung der Berechnung führt. Optimierungen und die Verwendung effizienterer Methoden können helfen.

4. Wie kann ich Matrixformeln fixieren?
Du kannst die Ergebnisse von Matrixformeln in Werte umwandeln, um die Berechnung zu fixieren und Excel zu entlasten.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige