Wie kann man innerhalb eines Excel-Modells dynamische Datenquellen als Basis für Diagramme oder Tabellen erstellen? Früher war dafür in meinem Portfolio die Pivot-Tabelle gesetzt, später Power Query. Dann habe ich die Funktion SUMMEWENNS() „neu“ entdeckt, als Excel365 die Dynamischen Arrays sowie die Funktionen EINDEUTIG(), SORTIEREN() eingeführt hat. Diese Kombination empfinde ich gerade beim Aufbau von Dashboards.
Dynamische Arrays mit Mustererkennung und Überlauf
Ein Array (engl. für Bereich, Matrix) ist im mathematischen Sinn eine Matrix. Excel meint damit einen konstanten Tabellenbereich, der aus einer bestimmten Anzahl von Zeilen und Spalten besteht. Dafür geben sogenannte Array-Formeln mehrere Ergebnisse oder ein einzelnes Ergebnis zurück. Dabei warden die mathematischen Prinzipien der Vektoren- und Matrizenrechnung angewendet. Arrayformeln erfordern daher das Markieren des gesamten Ausgabebereichs und das Abschließen der Formel mit der Tastenkombination <Ctrl> + <Shift> + <Enter>. Sie werden daher auch als CSE -Formeln (<CONTROL>+<SHIFT>+<ENTER>) oder als Array-Konstante bezeichnet.
Dynamische Arrays entstehen dann, wenn Sie in eine Zelle eine Formel/Funktion eingeben, diese Eingabe mit <Enter> bestätigen und diese Funktion automatisch in einen Zellbereich übertragen wird (Überlauf). Dabei orientiert sich das Array an den vorhandenen Spalten und Zeilenbeschriftungen in Ihrem Zielbereich und erkennt Muster. Dadurch werden Ergebnisse automatisch entweder nach unten oder über die benachbarten Zellen „übertragen“. Vorteilhaft dafür ist nach Beobachtung des Autors die Verwendung von „Intelligenten Tabellen“ sowie von Namen für Zellen/Zellbereiche in Ihrem Excel-Modell. Klassische Zellbezüge, relativ oder absolut helfen hier nicht immer weiter. So kann zum Beispiel mit der Funktion SUMMEWENNS eine Kreuztabelle erstellt werden, in dem ähnlich einer Pivot-Tabelle passend zur Zeilen- und Spaltenbeschriftung der Umsatz aus einer Tabelle addiert wird. Dazu müssen die Spalten – und Zeilenbeschriftungen mit den Zeichenfolgen (Strings) in der Datenquelle übereinstimmen. Der sogenannte Überlauf sorgt dafür, dass die Funktion SUMMEWENNS nur in eine Zelle eingegeben werden muss und in allen Zellen des Bereich automatisch ergänzt wird.
Das Video zeigt das Prinzip, mit dem aus einem Download dynamisch eine Kreuztabelle entsteht und dabei die Daten verdichtet werden.
Ältere Matrixformeln (bekannt als Legacy-Array-FormelnI , die über STRG + UMSCHALT + EINGABETASTE eingegeben werden, werden aus Gründen der Abwärtskompatibilität weiterhin unterstützt, sollten aber laut Microsoft nicht mehr verwendet werden.
Implizite Schnittmengen
Durch den Überlauf der Array-Formeln kann es zu Fehlern kommen, wenn die implizite Schnittmengenlogik unklar ist. Damit werden viele Werte auf einen einzelnen Wert reduziert. Wenn eine Funktion einen einzelnen Wert zurückgab, hat die implizite Schnittmenge nichts getan (obwohl sie technisch gesehen im Hintergrund ausgeführt wurde). Die Logik funktioniert folgendermaßen:
- Wenn es sich bei einem Wert um ein einzelnes Element handelt, wird das Element zurückgegeben.
- Wenn es sich bei dem Wert um einen Bereich handelt, wird der Wert aus der Zelle in derselben Zeile oder Spalte wie die Formel zurückgegeben.
- Wenn es sich bei dem Wert um ein Array handelt, wird der Wert oben links ausgewählt.
Impliziter Schnittmengenoperator: @
Der Operator für implizite Schnittmengen wurde in die Excel-Formelsprache eingeführt, um dynamische Arrays zu unterstützen. Wo eine alte Formel implizite Schnittmengen im Hintergrund auslösen könnte, zeigt Excel an, wo sie mit dem @-Zeichen aufgetreten wäre. Das @-Symbol wird bereits in der Intelligenten Tabelle für Tabellenverweisen verwendet, um implizite Schnittmengen anzugeben. Beachten Sie die folgende Formel in einer Tabelle =[@Spalte1]. Hier gibt das @-Zeichen an, dass die Formel die implizite Schnittmenge verwenden soll, um den Wert aus derselben Zeile aus [Spalte1] abzurufen.
