Übersichtstabellen mit dynamischen Arrays

Eine Zusammenfassungstabelle fasst eine große Liste mit Rohdatenzeilen zu einem kleinen, übersichtlichen Bereich zusammen: eine Zeile pro Kategorie mit den jeweiligen Summen daneben. Stellen Sie sich ein Verkaufsprotokoll mit Hunderten von Zeilen vor, das in eine übersichtliche Tabelle mit jeder Region und ihrem Gesamtumsatz umgewandelt wird.

Früher verwendete man dafür eine manuelle PivotTable, die Sie aktualisieren mussten. Heute nutzt man Formeln mit dynamischen Arrays, die sich sofort selbst aktualisieren, sobald sich Ihre Daten ändern. Keine Schaltflächen, keine Aktualisierung.

In dieser Lektion kombinieren Sie drei leistungsstarke Werkzeuge: UNIQUE, um die Kategorien aufzulisten, SUMIFS, um die Summe jeder Kategorie zu bilden, und FILTER, um passende Zeilen abzurufen. Zusammen erstellen sie eine aktuelle Zusammenfassung.

Stellen Sie sich ein Tabellenblatt namens Sales mit drei Spalten vor: Region in Spalte A, Produkt in Spalte B und Betrag in Spalte C, jeweils in den Zeilen 2 bis 200.

Unser Ziel ist eine Zusammenfassung, in der jede eindeutige Region und ihr Gesamtumsatz angezeigt werden. Die erste Herausforderung besteht darin, eine übersichtliche Liste der Regionen zu erstellen, ohne sie von Hand einzugeben, da später neue Regionen hinzukommen können.

• A2:A200 enthält viele wiederholte Regionsnamen wie East, West, East, North.
• Wir möchten nur: East, West, North, jeweils einmal aufgeführt.

Diese Liste der eindeutigen Werte bildet das Fundament der gesamten Zusammenfassung.

Die Funktion UNIQUE nimmt einen Bereich und gibt jeden Wert nur einmal zurück. Das Ergebnis läuft über, das heißt, eine Formel füllt so viele Zellen, wie eindeutige Werte vorhanden sind.

Geben Sie dies in Zelle E2 ein, und die Regionsliste erscheint automatisch darunter:

Wenn später eine neue Region zu den Daten hinzugefügt wird, wächst die übergelaufene Liste selbstständig. Sie müssen die Formel nie bearbeiten.

Nun benötigen wir den Gesamtbetrag für jede Region in Spalte E. SUMIFS addiert Werte aus einem Bereich nur dann, wenn ein anderer Bereich einer Bedingung entspricht.

Die Struktur lautet SUMIFS(sum_range, criteria_range, criteria). Platzieren Sie dies in F2 neben der ersten Region:

Der Bezug E2# ist der entscheidende Kniff. Das Zeichen # bedeutet, dass der gesamte Überlaufbereich ab E2 gemeint ist. Mit dieser einzigen Formel werden daher die Summen für alle von UNIQUE erzeugten Regionen berechnet.

Der Überlaufbezug E2# verweist immer auf den vollständigen Bereich, den eine Formel erzeugt hat, unabhängig davon, wie groß dieser wird. Dadurch bleibt die Zusammenfassung dynamisch.

Wenn UNIQUE drei Regionen findet, ist E2# drei Zellen hoch und SUMIFS gibt drei Summen zurück. Wenn die Daten auf fünf Regionen anwachsen, erweitern sich beide Bereiche gemeinsam, ohne dass Sie etwas bearbeiten müssen.

• E2 = nur die einzelne oberste Zelle.
• E2# = das gesamte übergelaufene Array ab E2.

Machen Sie sich mit dem Zeichen # vertraut; es bildet das Herzstück von Dashboard-Formeln.

Eine Zusammenfassung lässt sich besser lesen, wenn die Summen geordnet sind. Schachteln Sie die Regionsliste in SORT, damit die Kategorien alphabetisch erscheinen, oder sortieren Sie die gesamte Tabelle nach der Summe.

Um die Regionen in E2 alphabetisch aufzulisten:

Da die Summen in F weiterhin auf E2# verweisen, werden die Summen beim Sortieren der Regionen automatisch neu zugeordnet. Die beiden Spalten bleiben synchron.

Manchmal benötigen Sie die zugrunde liegenden Zeilen für eine Kategorie und nicht nur eine Summe. FILTER gibt jede Zeile zurück, die eine Bedingung erfüllt, und lässt die Ergebnisse überlaufen.

Um alle Verkaufszeilen anzuzeigen, in denen die Region dem Wert in Zelle H1 entspricht:

Enthält H1 den Wert East, erhalten Sie jede Zeile für East. Ändern Sie H1 in West, wird der Bereich sofort neu erstellt. Das ist die Grundlage einer Drill-down-Ansicht in einem Dashboard.

FILTER gibt den Fehler #CALC! zurück, wenn keine Übereinstimmung gefunden wird. Damit das Ergebnis übersichtlich bleibt, geben Sie das optionale dritte Argument als Ersatztext an.

Das dritte Argument wird angezeigt, wenn es keine Übereinstimmungen gibt:

Eine Region ohne Verkäufe zeigt nun einen verständlichen Hinweis statt eines Fehlers an. Fügen Sie in Dashboards immer diesen Ersatztext hinzu, damit eine ungültige Auswahl das Layout nicht beeinträchtigt.

In einer Zusammenfassung wird häufig angezeigt, wie viele Bestellungen jede Region hatte, und nicht nur der Umsatz. COUNTIFS zählt Zeilen, die eine Bedingung erfüllen, ähnlich wie SUMIFS, jedoch ohne Summenbereich.

Platzieren Sie dies in Spalte G neben den Summen:

Ihre dreispaltige Zusammenfassung enthält nun Region, Gesamtumsatz und Bestellanzahl, die alle von der einzigen übergelaufenen Regionsliste in E2# gesteuert werden. Alles wird gemeinsam aktualisiert.

Hier ist die vollständige Lösung nebeneinander:

• E2: =SORT(UNIQUE(Sales!A2:A200)) listet die Regionen auf.
• F2: =SUMIFS(Sales!C2:C200, Sales!A2:A200, E2#) bildet die Summe jeder Region.
• G2: =COUNTIFS(Sales!A2:A200, E2#) zählt jede Region.

Nur die Formel in E2 wird über die Zeilen eingegeben; F und G laufen ausgehend vom #-Bezug über. Fügen Sie irgendwo in Sales einen neuen Verkauf hinzu, und alle drei Spalten werden ohne Klicks aktualisiert.

Eine formelgesteuerte Zusammenfassung bietet gegenüber der manuellen Eingabe von Werten oder der Aktualisierung einer PivotTable entscheidende Vorteile:

• Aktuell: Sie wird sofort neu berechnet, sobald sich die Daten ändern.
• Passt sich selbst an: Neue Kategorien erscheinen dank UNIQUE und des #-Bezugs automatisch.
• Transparent: Jede Person kann die Logik in der Zelle nachvollziehen.

Der Nachteil ist, dass Überlaufbereiche freien Platz benötigen, in den sie sich ausdehnen können. Blockierte Überläufe behandeln wir in einer späteren Lektion. Lassen Sie vorerst unter Ihren Formeln genügend Platz.

Sie haben eine Zusammenfassungstabelle erstellt, die sich selbst aktuell hält:

• UNIQUE listet jede Kategorie einmal auf und lässt das Ergebnis überlaufen.
• SORT ordnet diese Liste übersichtlich.
• SUMIFS und COUNTIFS bilden mithilfe des Überlaufbezugs E2# die Summe und Anzahl jeder Kategorie.
• FILTER ruft für einen Drill-down die passenden Zeilen ab und zeigt bei fehlenden Übereinstimmungen einen Ersatztext an.

Da jede Formel auf der übergelaufenen Liste basiert, aktualisiert das Hinzufügen neuer Daten die gesamte Zusammenfassung ohne manuelle Schritte. Als Nächstes erstellen Sie vollständige Pivot-ähnliche Berichte ausschließlich mit Formeln.