So automatisierst du Berichte mit dynamischen Arrays und Tabellen

FAQ — Welche Fragen werden in diesem Artikel beantwortet?

Q: Was bedeutet „dynamisch” im Zusammenhang mit einem Excel-Bericht?

A: Ein dynamischer Bericht wird automatisch aktualisiert, sobald neue Daten hinzugefügt werden. Wenn du beispielsweise eine Zeile zu deiner Datentabelle hinzufügst, spiegeln alle Filterergebnisse, Summen und Diagramme im Bericht diese Änderung automatisch wider. In diesem Artikel zeigen wir dir, wie du einen dynamischen Bericht von Grund auf erstellst.

Q: Brauche ich VBA oder Makros, um einen Bericht zu automatisieren?

A: Nein, die Techniken in diesem Artikel verwenden nur Standard-Excel-Formeln, insbesondere Excel-Tabellen in Kombination mit den dynamischen Array-Funktionen FILTER, SORTIEREN und EINDEUTIG. Es sind keine Makros, kein Code und keine Add-Ins erforderlich.

Q: Was ist ein Überlaufbereich (spill range) und wie verweise ich darauf?

A: Wenn eine dynamische Array-Formel mehrere Ergebnisse liefert, „überlaufen“ (spill) diese automatisch in die umgebenden Zellen. Du kannst auf den gesamten Überlaufbereich verweisen, indem du ein # hinter die oberste Zelle setzt. Zum Beispiel summiert die Formel =SUMME(C2#) alles, was die Formel in C2 ergibt.

Q: Wie kann ich den Bericht so einrichten, dass die Ergebnisse nach einer Auswahl aus einer Dropdown-Liste gefiltert werden?

A: Erstelle mithilfe der Datenüberprüfung eine Dropdown-Liste in einer Zelle und verweise deine FILTER-Formel dann auf diese Zelle. Wenn du eine andere Option in der Dropdown-Liste auswählst, wird der gesamte Bericht sofort aktualisiert.

Q: Welche Excel-Version brauche ich?

A: Dynamische Array-Funktionen (FILTER, SORTIEREN, EINDEUTIG und SEQUENZ) erfordern entweder Excel 365 oder Excel 2021, da diese Funktionen in Excel 2019 oder früheren Versionen nicht verfügbar sind.

Das Problem mit manuellen Berichten

Die meisten Excel-Berichte folgen einem ähnlichen Ablauf: Daten exportieren, in Excel einfügen, sortieren, die Zeilen des letzten Monats löschen, die Formatierung neu anwenden, den Diagrammbereich anpassen und die Summen aktualisieren. Im nächsten Monat musst du das Ganze dann wieder von vorne machen.

Das kostet Zeit. Außerdem entstehen dabei Fehler. Sobald du die Datei speicherst, ist sie bereits veraltet.

Es gibt jedoch einen besseren Weg, der anfangs etwas mehr Überlegung erfordert, danach aber fast keinen monatlichen Aufwand mehr verursacht. Die Grundlage bilden zwei Excel-365-Technologien: Excel-Tabellen und dynamische Array-Formeln.

Schritt 1: Trage deine Daten in eine Excel-Tabelle ein.

Bevor du auch nur eine einzige Formel eingibst, wandle deine Daten in eine Excel-Tabelle um. Wähle dazu eine beliebige Zelle in deinen Daten aus und drücke Strg+T. Klicke auf „OK“. Das war’s schon!

Eine Tabelle erfüllt drei wesentliche Automatisierungsfunktionen:

Automatische Erweiterung: Wenn du eine neue Zeile direkt unterhalb der letzten Zeile der Tabelle eingibst, erweitert Excel die Tabelle automatisch, um diese aufzunehmen. Jede Formel, die auf die Tabelle verweist, erkennt die neue Zeile automatisch, sodass du nichts ändern musst.

Strukturierte Verweise: Anstatt die Zellbereiche E2:E500 einzugeben und zu hoffen, dass genügend Zeilen vorhanden sind, gibst du Sales[Amount] ein, also die Spalte anhand ihres Namens. Wenn die Tabelle wächst, wächst der Verweis mit.

Ein Name, den du selbst festlegst: Wechsle zur Registerkarte Tabellendesign und gib der Tabelle einen aussagekräftigen Namen, zum Beispiel Sales. Deine Formeln lauten dann =FILTER(Sales, ...), was viel übersichtlicher ist als =FILTER(A2:E500, ...).

💡 Tipp: Speichere die Tabelle mit den Rohdaten auf einem eigenen Blatt. Nenne es DATA. Deine Berichtsformeln befinden sich auf separaten Blättern und beziehen ihre Daten aus dieser Tabelle. Diese Trennung erleichtert die Pflege.

Schritt 2: Das Spilling (überlaufen) verstehen

Bevor du dich mit den Berichtsformeln befasst, solltest du dich mit dem Konzept des Spillings vertraut machen.

In herkömmlichen Excel-Versionen lieferte jede Formel nur ein einziges Ergebnis. Dynamische Array-Formeln brechen diese Regel jedoch, da sie Dutzende oder sogar Hunderte von Ergebnissen liefern können. Excel platziert diese Ergebnisse dann automatisch in den Zellen darunter (oder rechts davon). Dieser automatische Überlauf wird als Spilling bezeichnet.

Du musst die Formel nur einmal in eine einzelne Zelle eingeben. Die Ergebnisse erscheinen in so vielen Zellen wie nötig und der Spill-Bereich vergrößert oder verkleinert sich automatisch, wenn sich die Daten ändern. Du musst eine Formel nie nach unten ziehen.

Der Operator

Um einen gesamten Ausgabebereich zu referenzieren, füge ein # hinter die Adresse der obersten Zelle ein. Befindet sich deine FILTER-Formel beispielsweise in Zelle C3, gib die Formel =SUMME(C3#) ein, um alle von der Formel erzeugten Zeilen zu summieren, auch wenn du nicht weißt, wie viele Zeilen das sein werden.

So kannst du Summen, Diagramme und andere Formeln mit dem Spill-Bereich verknüpfen, ohne dich auf eine feste Zeilenanzahl festlegen zu müssen.

Schritt 3: FILTER, SORTIEREN und EINDEUTIG

Diese drei Funktionen sind das Herzstück des automatisierten Berichts.

FILTER: Zeige nur die gewünschten Zeilen an

Die Funktion FILTER durchsucht die Tabelle und gibt nur die Zeilen zurück, bei denen die Bedingung erfüllt ist. Das Filterergebnis ändert sich jedes Mal, wenn sich die Tabelle ändert.

=FILTER(Sales, Sales[Region]="North", "Keine Daten gefunden")

• Sales — die zu durchsuchende Tabelle
• Sales[Region]="North" — die Bedingung, dass nur Zeilen zurückgegeben werden, bei denen Region gleich North ist.
• "keien Daten gefunden" — was angezeigt werden soll, wenn keine Zeilen übereinstimmen (immer angeben)

UND-Bedingungen: Beide müssen wahr sein, multipliziere sie miteinander:

=FILTER(Sales, (Sales[Region]="North") * (Sales[Amount]>1000))

ODER Bedingungen: Beides kann zutreffen, addiere sie:

=FILTER(Sales, (Sales[Region]="North") + (Sales[Region]="South"))

SORTIEREN: liefert Ergebnisse in geordneter Reihenfolge

Wenn du SORTIEREN um FILTER herum einsetzt, erhältst du ein sortiertes Ergebnis. Das zweite Argument gibt die Spaltennummer an, nach der sortiert werden soll (gezählt vom linken Rand des Arrays), und das dritte Argument legt fest, ob in aufsteigender Reihenfolge 1 oder absteigender Reihenfolge -1 sortiert werden soll.

Die folgende Formel sortiert die Zeilen der Region „Nord“ in absteigender Reihenfolge nach der fünften Spalte (Betrag):

=SORTIEREN(FILTER(Sales, Sales[Region]="North"), 5, -1)

Dies filtert die Zeilen der Region „North“ heraus und sortiert nach Spalte 5 (Betrag), vom größten zum kleinsten Wert.

Mit der Funktion EINDEUTIG kannst du eine sich selbst aktualisierende Liste erstellen.

EINDEUTIG entfernt Duplikate und gibt jeden Wert nur einmal zurück. Damit eignet es sich perfekt, um aus Live-Daten eine eindeutige Liste von Regionen, Produkten oder Vertriebsmitarbeitern zu erstellen, ohne dass eine Pivot-Tabelle erforderlich ist.

=SORTIEREN(EINDEUTIG(Sales[Region]))

Dadurch entsteht eine alphabetische Liste aller Regionen, die in der Tabelle vorkommen. Füge eine neue Region zur Tabelle hinzu und sie erscheint automatisch in der Liste. Lösche alle Zeilen für eine Region, und sie verschwindet aus der Liste.

SUMMEWENSS für einen übergelaufenen Bereich

Hier kommt die wahre Stärke zum Tragen. Angenommen, die Liste der Regionen wurde von der EINDEUTIG-Funktion in Zelle A2 deines Übersichtsblatts überlaufen. Wenn du den Gesamtumsatz pro Region berechnen möchtest, kannst du eine einzige Formel für die gesamte Spalte verwenden:

=SUMMEWENNS(Sales[Amount], Sales[Region], A2#)

Das A2# am Ende weist Excel an, für jeden Eintrag im übergelaufenen Bereich eine SUMMEWENNS-Funktion auszuführen. Die Ergebnisse werden als eine Zahl pro Region nach unten übertragen. Wenn du den Daten eine neue Region hinzufügst, werden die EINDEUTIG-Liste und die SUMMENWENNS-Summen automatisch um eine Zeile erweitert.

Schritt 4: Füge ein interaktives Dropdown-Menü hinzu.

So kannst du zwischen verschiedenen Ansichten des Berichts wechseln, ohne Formeln bearbeiten zu müssen.

1. Stelle auf deinem Arbeitsblatt „Zusammenfassung“ zunächst sicher, dass die Formel =SORTIEREN(EINDEUTIG(Sales[Region]) eine Liste der Regionen in Zelle A2 ausgibt.
2. Wähle im Blatt „Report“ die Zelle B1 aus.
3. Gehe zu Daten → Datenüberprüfung → Zulassen: Liste.
4. Gib im Feld Quelle =Zusammenfassung!A2# ein. Dies verweist auf den Ausgabebereich der eindeutigen Regionen.
5. Klicke auf OK. Die Zelle B1 verfügt nun über ein Dropdown-Menü, das alle Regionen auflistet.

Aktualisiere anschließend deine FILTER-Formel so, dass sie auf B1 statt auf einen festen Textwert verweist.

=SORTIEREN(FILTER(Sales, Sales[Region]=B1, "keine Daten gefunden"), 5, -1)

Wähle im Dropdown-Menü die Option „South“ aus, um die gesamte gefilterte Tabelle mit den Daten für den Süden anzuzeigen. Wähle „North“, um zurückzuwechseln. Dabei werden die Zeilenanzahl, die Gesamtsumme und alle Diagramme, die C3# verwenden, gleichzeitig aktualisiert.

Schritt 5: Vollständiges Arbeitsmappenlayout

Eine übersichtliche, automatisierte Berichtsarbeitsmappe besteht aus drei Blättern:

DATA: Enthält nur die Excel-Tabelle. Sonst nichts. Dies ist die einzige verlässliche Datenquelle. Hier kannst du neue Exporte einfügen oder eine Verbindung zu Power Query herstellen.

BERICHT: Dies ist der Bericht, den deine Kollegen tatsächlich lesen. B1 ist die Dropdown-Filterzelle. In C3 befindet sich die Formel FILTER + SORTIEREN. Eine Summenzeile darunter verwendet die Formel =SUMME(G3#). Für die Zeilenzählung wird die Formel =ZEILEN(C3#) verwendet. Diagramme verwenden C3# als Datenquelle.

ZUSAMMENFASSUNG: Die Übersicht. In Spalte A wird die Formel =SORTIEREN(EINDEUTIG(Sales[Region]) verwendet. In Spalte B wird die SUMMEWENNS-Summe pro Region berechnet. Dieses Blatt ist auch die Quelle für die Dropdown-Liste.

Was du vermeiden solltest

Blockierung des Ausbreitungsbereichs
Wenn eine Zelle im Bereich, in den die Formel ausgebreitet werden soll, einen Wert, eine Formel oder ein Leerzeichen enthält, zeigt Excel die Fehlermeldung #ÜBERLAUF! an. Wähle die Formelzelle aus, um genau zu sehen, welche Zellen blockiert sind. Diese sind durch einen blauen Rahmen gekennzeichnet. Lösche sie.

Zusammengeführte Zellen im Ausbreitungsbereich verhindern:
Führe niemals Zellen im oder in der Nähe eines Ausbreitungsbereichs zusammen.

Fest codierte Zeilenbereiche
Wenn du C3:C50 statt C3# eingibst, bricht die Automatisierung in dem Moment ab, in dem die Ausbreitung über die Zeile 50 hinausgeht oder unter diese fällt.

Vermischung von Daten und Formeln auf demselben Blatt
Behalte die Rohdatentabelle auf einem eigenen Blatt. Das Vermischen von Daten und Berichtsformeln im selben Bereich sorgt für Verwirrung. Es verursacht außerdem Probleme bei der Pflege.Blockierung des Ausbreitungsbereichs.

Zusammenfassung

Erstelle das einmal. Danach musst du für die Pflege des Berichts lediglich Zeilen zum Blatt DATA hinzufügen. Alle Filter, Summen, Zusammenfassungen und Diagramme werden dann automatisch aktualisiert.