Array-Formeln in Excel sind ein sehr mächtiges Werkzeug. Allerdings leider auch ein recht schwieriges. Wir zeigen, wie eine Excel-Array-Formel funktioniert und was Sie dabei beachten sollten.
Eine Excel-Array-Formel (auch als Matrixformel bezeichnet) kann mehrere Berechnungen auf einmal durchführen und somit im Extremfall tausende der üblichen Formeln ersetzen. Dennoch haben Schätzungen zufolge gut 90 Prozent der Benutzerinnen und Nutzer noch nie Array-Funktionen in ihren Arbeitsblättern verwendet.
Was ist ein Array in Excel?
Ein Array ist im Grunde genommen nichts weiter als eine Sammlung von Elementen. Die deutsche Übersetzung “Feld” trifft es auch einigermaßen, besser ist aber die Übersetzung “Reihe” – ein Array ist vergleichbar mit einer simplen Reihe von Daten.
Die Daten können dabei Texte oder Zahlen sein, bei Excel sind es in der Regel Zahlen.
Arrays schreibt Excel (wie viele Programmiersprachen auch) mit einem Komma als Trennzeichen und von Klammern umgeben.
Wenn Sie also zum Beispiel die Einkaufsliste in ein Excel-Array-Format übertragen, könnte dies so aussehen: {“Milch”, “Eier”, “Butter”, “Cornflakes”}
Was ist eine Array-Formel in Excel?
Der Unterschied zwischen einer Array-Formel und einer normalen Formel besteht darin, dass eine Array-Formel mehrere Werte statt nur eines Wertes verarbeitet. Statt also beispielsweise Zelle A1 mit B1 zu verrechnen, kann eine Array-Formel den kompletten Bereich von beispielsweise A1:A100 mit B1:B100 verrechnen.
Klassische Arrayformeln (bis Excel 2021) geben hierbei immer genau ein Eregbnis zurück – und zwar in der Zelle, in der diese eingebeben wurde. Mittlerweile kann Excel aber auch ganze Zellbereiche als Ergebnis dynamisch zurückgeben, dazu später mehr.
Wichtig:
Um eine Array-Formel zu erzeugen, müssen Sie die Formel-Eingabe immer mit STRG + SHIFT + ENTER abschließen. Die Eingabetaste alleine genügt nicht!
Beispiel
Angenommen, Sie haben einige Produkte in Spalte B und deren Preise in Spalte C. Nun möchten Sie die Gesamtsumme aller Verkäufe berechnen.
Natürlich hindert Sie nichts daran, zunächst die Zwischensummen in jeder Zeile etwa mit =B2*C2 zu berechnen und diese Werte dann zu summieren.
Eine Excel-Array-Formel ermöglicht es in diesem Fall aber, das Ergebnis kompakt in nur eine Zelle zu schreiben. Statt also eine neue Spalte D mit den Ergebnissen einfügen zu müssen, lässt sich das Endergebnis direkt ausgeben. Vor allem in komplexen Tabellen spart man so Platz und gewinnt Übersicht.
Markieren Sie eine leere Zelle und geben Sie dort die folgende Formel ein:
=SUMME(B2:B6*C2:C6)Drücken Sie die Tastenkombination STRG + SHIFT + ENTER, um die Array-Formel abzuschließen. Sobald Sie dies getan haben, umgibt Microsoft Excel die Formel mit geschweiften Klammern { }. Damit zeigt Excel an, dass es sich um eine Array-Formel handelt.
Die Formel multipliziert die Werte in jeder einzelnen Zeile des Arrays (Zellen B2 bis C6), addiert die Zwischensummen und gibt die Gesamtsumme aus:
Dieses einfache Beispiel zeigt, wie leistungsstark eine Array-Formel sein kann. Schon eine einzige kann buchstäblich hunderte von normalen Formeln ersetzen.
Neue Array-Formeln seit Excel 2021
Eine Excel-Array-Formel kann aber nicht nur mit mehreren Werten gleichzeitig arbeiten, sondern seit einiger Zeit auch mehrere Werte gleichzeitig zurückgeben. Das von einer Array-Formel zurückgegebene Ergebnis ist also auch ein Array (oder zu deutsch: eine Reihe bzw. ein “Feld”). Das ist neu und war vorher so nicht möglich.
Microsoft selbst nennt diese Art Funktion auch dynamische Arrayformel. Ein gutes Beispiel für ein solche Funktion ist die Funktion =EINDEUTIG(Matrix). Diese filtert alle vorkommenden Werte eines Bereiches aus und listet diese im Zielbereich genau einmal auf. So kann man beispielsweise schnell sehen, welche Kunden in einem Zeitraum aktiv waren, auch wenn diese mehrfach bestellt haben.
Das Ergebnis-Array kann dabei logischerweise unterschiedlich groß sein – eben je nachdem, wie viele Einträge es einmalig darin gibt. Passt das Eregbnis-Array nicht in das Excel-Blatt, meldet Excel den Fehler #ÜBERLAUF.
Tipps und Hinweise zu Array-Formeln
- Sobald Sie die Formel fertig eingegeben und gleichzeitig die Tasten STRG + SHIFT+ ENTER gedrückt haben, schließt Excel diese automatisch zwischen {geschweifte Klammern} ein. Wenn Sie eine solche Zelle(n) auswählen, sehen Sie die geschweiften Klammern in der Formelleiste, was Ihnen einen Hinweis darauf gibt, dass sich dort eine Array-Formel befindet.
- Das manuelle Eingeben der geschweiften Klammern um eine Formel funktioniert nicht! Sie müssen die Tastenkombination Strg + Umschalt + Eingabe drücken, um eine Array-Formel abzuschließen.
- Jedes Mal, wenn Sie eine Array-Formel bearbeiten, verschwinden die Klammern und Sie müssen erneut Strg + Umschalt + Eingabe drücken, um die Änderungen zu speichern.
- Wenn Sie vergessen, Strg + Umschalt + Eingabe zu drücken, meldet Excel entweder einen Fehler oder die Formel verhält sich wie eine gewöhnliche Formel – und verwendet nur den/die ersten Wert(e) in dem/den angegebenen Array(s).
