Office-Blog

Excel hat eine weitere Neuerung: Diagramme, die sich dynamisch an eine wechselnde Anzahl von Datenpunkten anpassen, die per Arrayfunktionen erzeugt werden. Microsoft verkündete dies bei den Excel-Neuerungen für September.

Natürlich war ich neugierig und habe das gleich am Beispiel der Funktion FILTER getestet (siehe auch diesen Beitrag). Es klappte auf Anhieb. Zum perfekten Bereitstellen der Daten für das Diagramm habe ich zusätzlich die Funktion SPALTENWAHL eingebaut.

Nur die gefilterten Daten im Diagramm anzeigen

Die folgende Abbildung zeigt die Datenbasis und eine Vorschau auf das fertige Diagramm:

• Die Spalten B bis E enthalten die Projektdaten für vier Standorte.
• In Zelle H2 wird per Dropdown einer der Standorte ausgewählt.
• Daraufhin listet die Funktion FILTER die Daten zu dem ausgewählten Standort in Spalte I und J auf.
• Die gefilterte Liste wird mit der Funktion SPALTENWAHL auf zwei Spalten eingegrenzt – hier auf Projekt und Beteiligte.
• Die in den Spalten I und J generierten Daten sind die Basis für das Säulendiagramm rechts. Es zeigt für den gewählten Standort, wie viele Personen an jedem der Projekte beteiligt sind.

Die Herausforderung: Egal, ob die zugrundeliegende Datenbasis in Spalte I und J aus drei, vier, fünf oder mehr Projekten besteht, soll im Diagramm die Anzahl der Säulen automatisch angepasst werden. Das kürzliche Update in Excel 365 macht das nun möglich.

Links die Projekttastelle ich zunächst belle, oben das Dropdown zur Projektauswahl und rechts die Daten für das Diagramm

Die Datenauswahl per Dropdown in Zelle G2 einrichten

Damit in G2 per Dropdown der gewünschte Standort ausgewählt werden kann, stelle ich zunächst eine sortierte Liste der möglichen Standorte zusammen. Das erledige ich in Spalte R mit den Funktionen EINDEUTIG und SORTIEREN.

Ich markiere Zelle R4 und geben folgende Formel ein:
=SORTIEREN(EINDEUTIG(tbl_Daten[Standort])).

Hinweis: In diesem Blogbeitrag gibt es eine detaillierte Beschreibung zum Anlegen einer dynamischen Dropdownliste mit EINDEUTIG und SORTIEREN).

Mit EINDEUTIG und SORTIEREN die Daten für das Dropdownfeld vorbereiten

Nach dieser Vorbereitung richte ich das Dropdown in Zelle G2 wie folgt ein:

• Ich markiere G2 und wechsle zur Registerkarte Daten.
• Dort klicke ich auf den Befehl Datenüberprüfung.
• Die folgende Abbildung zeigt die erforderlichen Einstellungen in dem nun eingeblendeten Dialogfeld.

Wichtig beim Konfigurieren des Dropdownfelds ist das #-Zeichen nach $R$4

Über die gefilterte Liste in den Spalten I und J das dynamische Diagramm erstellen

Nach Auswahl des Standorts über das Dropdown in Zelle G2 sollen in den Spalten I und J die Projekte und die Anzahl der Beteiligten zum gewählten Standort angezeigt werden.

• Die Formel =FILTER(tbl_Daten;tbl_Daten[Standort]=G2) erzeugt eine gefilterte Liste mit allen vier Spalten.
• Da aber nur die Spalten Projekt und Beteiligte (Spalte 2 und Spalte 3 der Datentabelle) gebraucht werden, grenze ich mit der Funktion SPALTENWAHL die Anzahl der Spalten auf zwei ein.
• Dazu gebe ich in Zelle I4 folgende Formel ein:
  =SPALTENWAHL(FILTER(tbl_Daten;tbl_Daten[Standort]=G2);2;3).
  Hinweis: Die 2 und die 3 am Ende der Formel stehen für das Argument Spaltennummer der Funktion SPALTENWAHL. Sie geben an, das nur Spalte 2 und Spalte 3 in der gefilterten Liste erscheinen.
• Dann klicke ich in den Bereich der gefilterten Liste in Spalte I und J und erzeuge mit Alt+F1 das in der Abbildung rechts gezeigte Säulendiagramm.

MIT FILTER und SPALTENWAHL die Daten für das Diagramm aufbereiten

So teste ich die Lösung

• Ändere ich den Standort über das Dropdown in G2, werden nun in I und J die Daten zum gewählten Standort angezeigt.
• Auch das Diagramm ändert sich automatisch: Höhe und Anzahl der Säulen passen sich sofort an. Die folgenden beiden Abbildungen illustrieren dies.

Die Daten und das Diagramm für Berlin …

… und die Daten sowie das Diagramm für Hamburg

Interesse an weiteren dynamischen Arrayfunktionen?

Hier im Blog gibt es bereits mehrere Beiträge zu den neuen Arrayfunktionen. Wer sich mit allen Neuerungen in Excel 365 vertraut machen will, merkt sich den Termin der Excel-Kompetenztage vor: 22. bis 24. Mai 2023.