Matrix- oder Arrayfunktionen in Excel sind auch für viele versierte Excel-Anwender immer noch ein Mysterium. Dabei sind Matrixfunktionen äußerst mächtig, da man mit ihrer Hilfe im Prinzip mehrere Rechenoperationen in einem Schritt durchführen kann. Somit lassen sich auch komplexe Aufgabenstellungen mit einer einzigen Formel lösen.

Matrix- bzw. Arrayfunktionen entmystifiziert

In diesem Tutorial sowie in der zugehörigen Excel-Datei werden grundlegende Matrixfunktionen vorgestellt. Dabei soll aber nicht auf komplizierte, theoretische Anwendungsfälle aus der Matrizenrechnung eingegangen werden, sondern der Fokus auf ausgewählte hilfreiche Anwendung für das Financial Modelling z.B. im Rahmen internationaler Projektfinanzierungen liegen.

Im Gegensatz zu normalen Formeln können Matrixformeln eine ganze Gruppe von Zellen gleichzeitig bearbeiten und dabei mehrere Berechnungen durchführen. Aus einer Gesamtmenge von Werten wird ein Teil der Daten mittels vorgegebenen Bedingungen daraus ausgewertet. Anstatt zeitraubend mehrere unterschiedlich zu definierende Einzelformeln zu erstellen, kann oftmals eine einzige Matrixformel Berechnungen erheblich vereinfachen.
Prinzipiell werden Matrixfunktionen in Excel genauso in Berechnungen verwendet wie „normale“ Funktionen. Es bestehen allerdings die folgenden Unterschiede:

1. Die meisten Matrixfunktionen liefern mehrere Ergebniswerte. Dazu müssen auch mehrere Tabellenzellen ausgewählt werden. Um wie viele Tabellenzellen es sich dabei genau handelt und ob dabei Tabellenzellen in einer Zeile oder Spalte (oder beides) markiert werden müssen, hängt von der jeweiligen Matrixfunktion und den Ausgangsdaten ab.
2. Wie fast alle Funktionen, benötigen auch die Matrixfunktionen Angaben in Form von Funktionsargumenten. Bei diesen Funktionsargumenten handelt es sich in den meisten Fällen um Matrizen, was in Excel identisch ist mit Zellbereichen (deshalb auch der Name „Arrayfunktionen“).
3. Wird eine Matrixfunktion nicht über den Funktionsassistenten eingefügt, sondern direkt in die Tabellenzelle eingetragen, ist zum Abschluss der Eingabe die Tastenkombination „Shift + Strg + Return“ zu verwenden. Anschließend signalisieren zwei geschweifte Klammern {  } zu Beginn und am Ende der Formel, dass es sich um eine Matrixfunktion handelt.

Es sind bereits zahlreiche Matrixfunktionen in Excel integriert, die aber im Prinzip wie normale Funktionen verwendet werden können, d.h. ohne Eingabe der geschweiften Klammern. Dazu zählen u.a. die bekannten Funktionen:

• SVERWEIS
• WVERWEIS
• MTRANS
• VERGLEICH
• GEOMITTEL

Ferner lassen sich Summen mit mehreren Bedingungen mit Hilfe von Arrayformeln berechnen. Ab Excel 2007 sollten Sie stattdessen aber nach Möglichkeit z.B. die Funktionen SUMMEWENN oder ZÄHLENWENN einsetzen, da die Verwendung nicht nur einfacher und weniger fehleranfällig ist, sondern deren Berechnung auch wesentlich schneller als die von Arrayformeln erfolgt.

Typische Anwendungen im Financial Modelling

Grundlegende Arrayformeln

Im ersten praxisorientierten Beispiel (siehe auch zugehörige Excel-Datei) geht es um die Umsatzermittlung mittels Matrixformel. Bei der Eingabe ist dabei folgendes Vorgehen zu beachten:

1. Bereich G10:G14 auswählen
2. Formel =E10:E14*F10:F14 in die Bearbeitungsleiste eingeben
3. Formel mit  „Shift + Strg + Return“ abschließen

Die Formel wird anschließend in geschweiften Klammern dargestellt { } und in jede Zelle eingefügt. Bennent man die beiden Bereiche E10:E14 mit „anzahl“ und F10:F14 mit „preis“, dann kann alternativ auch die Formel {=anzahl*preis} verwendet werden.

Die Gesamtsumme kann auch in einer einzigen Formel mittels Matrixfunktion berechnet werden (siehe Screenshot oben, Zelle G15).

Im unteren Teil des Beispiels wird zusätzlich gezeigt, wie man mittels Matrixformeln in Kombination mit der Funktion MTRANS vertikale aufgeführte Bezeichnungen/Werte einfach und schnell in horizontale Ausrichtung umwandeln kann, eine häufige Anwendung im Alltag des Financial Modelling.

Fortgeschrittene Arrayformeln

Häufig sollen Berechnungen durchgeführt werden, obwohl Fehlerwerte oder Text (z.B. „n.a.“) in einigen Zellen enthalten sind. Ferner ist es oft nicht erwünscht, bestimmte Nullwerte in eine Kalkulation einzubeziehen. In diesen Fällen hilft es, die Berechnungen innerhalb der Matrixformel mit einer entsprechenden WENN-Funktion einzugrenzen (siehe Beispiele in Bild unten).

Abweichungen bzw. unterschiedliche Werte lassen sich schnell identifizieren bzw. zählen durch mittels der Formel   {=SUMME(WENN(Bereich 1 =Bereich 2;0;1))}. Dabei müssen die beiden Bereiche 1 und 2 die gleiche Größe und Dimension aufweisen (siehe Bild oben).
Schließlich gelingt auch die Berechnung von Summen, für deren Summanden bestimmte Bedingungen zutreffen sollen mittels Arrayformeln (siehe Screenshot und zugehörige Excel-Datei). Alternativ lassen sich derartige bedingte Summen häufig auch mit der Funktion SUMMENPRODUKT (kein Abschluss mit „Shift + Strg + Return“ nötig) berechnen.

Abschließende Bemerkung

Matrixfunktionen sind in vielen Fällen „normalen“ Funktionen überlegen. Die selektive Verwendung im Rahmen des Financial Modelling wird daher empfohlen. Trotz der zahlreichen Vorteile sollte man sich aber auch der möglichen Nachteile bei der Verwendung von Arrayformeln bewusst sein.
Vorteile

• Hohe Konsistenz und Genauigkeit, da in jeder Zelle des entsprechenden Bereichs die „gleiche“ Formel zu finden ist.
• Sicherheit, da Teile einer Matrixfunktion die einen Zellbereich (nicht eine einzelne Zelle) beinhalten nicht überschrieben werden können.
• Zeitgewinn, da häufig mehrere lange Einzelformeln durch eine einzige Matrixformel ersetzt werden können.
• Effizient, da bei großen Dateien die Dateigröße durch Verwendung von Matrixformeln verringert werden kann.

Nachteile

• Das Vergessen der Tastenkombination „Shift + Strg + Return“ führt schnell zu Fehlern, die nicht immer auf den ersten Blick erkannt werden.
• Die Berechnung von Arrayformeln erfordert teilweise mehr Zeit und Rechenleistung als die Verwendung ähnlicher Alternativfunktionen (z.B. SUMMEWENN etc.).
• Matrixformeln sind vielen Anwendern häufig nicht ausreichend bekannt (und auch wenig dokumentiert), so dass die Verwendung möglicherweise nicht verstanden und kritisch gesehen wird.