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.
Wenn Ihnen dieser Beitrag gefallen hat, dann teilen Sie ihn gerne. Falls Sie Anmerkungen haben, schreiben Sie bitte einen Kommentar, oder senden Sie mir eine Mail an info@prt.de.
