Dynamische Diagramme mit Dropdown-Listen in Excel erstellen

Microsoft
2024-04-22T15:01:14Z

Wenn Sie Ihre Freunde und Kollegen beeindrucken wollen, können Sie ein Excel-Diagramm auf der Grundlage eines Dropdown-Feldes, das Sie Ihrem Blatt hinzugefügt haben, ändern lassen. Es gibt verschiedene Möglichkeiten, dies zu erreichen, aber wir bevorzugen die folgende Methode, weil es einfacher ist, Probleme zu finden, wenn etwas schief geht.

Wir verwenden eine Datentabelle mit den Namen von fünf Fußballspielern und deren Spielbewertung für fünf Spiele, um Ihnen den Prozess zu erläutern.

Schritt 1: Erstellen einer Dropdown-Liste

Der erste Schritt besteht darin, die Dropdown-Liste zu erstellen, die Ihr Diagramm dynamisch machen wird. Beginnen Sie, indem Sie ein Aktionswort in die Zelle neben der Dropdown-Liste eingeben. In unserem Beispiel soll das Einblendmenü in Zelle B9 platziert werden, also geben wir unser Aktionswort in Zelle A9 ein. Klicken Sie dann auf die Zelle, in die das Einblendmenü eingefügt werden soll, wechseln Sie zur Registerkarte Daten im Menüband und klicken Sie in der Gruppe Datentools auf "Datenüberprüfung".

An Excel worksheet. 'Choose' is typed into cell A9, cell B9 is selected, and the 'Data Validation' function is highlighted.

Wählen Sie im Fenster "Datenüberprüfung" unter "Zulassen" die Option "Liste". Klicken Sie dann in das Feld "Quelle" und markieren Sie die Spaltenüberschriften in Ihrer Tabelle (die Daten, die in der von Ihnen erstellten Dropdown-Liste angezeigt werden sollen). In unserem Beispiel möchten wir die Spielnummern auswählen, da wir in der Tabelle, die wir später erstellen werden, die Bewertungen der Spieler für jedes Spiel sehen möchten. Klicken Sie dann auf "OK"

An Excel spreadsheet with the data validation window open. The Allow field contains the

Wenn Sie auf den Pfeil klicken, wird eine Dropdown-Liste mit den von Ihnen ausgewählten Daten angezeigt. Außerdem haben wir die Zelle "Auswählen" so formatiert, dass sie hervorsticht.

An Excel spreadsheet containing a table and a drop-down list with the options showing as the column headers in the table.

Schritt 2: Erstellen einer Datenabruftabelle

Wir müssen nun eine zweite Tabelle erstellen, die von unseren Rohdaten getrennt ist, aber Informationen daraus abruft, wenn wir das von uns erstellte Dropdown-Menü anpassen. Das Diagramm, das wir später erstellen, wird anhand der Informationen in dieser Datenabruftabelle erstellt.

Von nun an nennen wir die Hauptdatentabelle Tabelle 1 und die Datenabruftabelle Tabelle 2.

Markieren Sie die Zeilentitel in Tabelle 1, drücken Sie Strg C, und verwenden Sie Strg V, um die Liste an die Stelle zu kopieren, an der Sie Tabelle 2 haben möchten.

An Excel sheet with a table on the left, and the first column copied and pasted to the right.

Wählen Sie die eingefügten Daten aus, klicken Sie mit der rechten Maustaste darauf, bewegen Sie den Mauszeiger über "Sortieren" und klicken Sie auf "Von A bis Z sortieren"

An Excel spreadsheet with data in Table 2 selected, and the Sort A to Z option highlighted.

Um mit der Erstellung von Tabelle 2 fortzufahren, müssen Sie der nächsten Spalte eine Überschrift geben. Welche Daten in Tabelle 2 erscheinen, hängt davon ab, was Sie in Ihrem Dropdown-Feld auswählen. In unserem Fall soll die Überschrift in Zelle I2 stehen. In diese Zelle geben wir = ein und klicken auf Zelle B9.

An Excel spreadsheet with a column in Table 2 referencing a drop-down cell.

Drücken Sie die Eingabetaste, um zu sehen, dass die Spaltenüberschrift in Tabelle 2 die Dropdown-Auswahl übernimmt. Versuchen Sie, die Dropdown-Auswahl zu ändern, um zu sehen, wie sich die Spaltenüberschrift in Tabelle 2 ändert.

Schritt 3: Daten aus Tabelle 1 in Tabelle 2 extrahieren

Nun müssen wir die relevanten Daten mit INDEX und MATCH aus Tabelle 1 in Tabelle 2 extrahieren. Dies ist der komplexeste Teil des Prozesses, aber wir werden ihn aufschlüsseln, damit Sie den Prozess leichter durcharbeiten können. Hier ist die Synax:

=INDEX(a,MATCH(b,c,0),MATCH(d,e,0))

wobei

  • a sind alle Daten in Tabelle 1 (mit Ausnahme der Zeilen- und Spaltenüberschriften),
    • b
  • ist der erste Wert, den wir in Tabelle 2 nachschlagen,
  • c ist der Wertebereich in der ersten Spalte in Tabelle 1,
  • d ist die Spaltenüberschrift in Tabelle 2, und
  • e steht für die Datenspaltenüberschriften in Tabelle 1
    • .

Vergewissern Sie sich, dass die Zellbezüge a, c, d und e absolute Bezüge sind, indem Sie F4 auf Ihrer Tastatur drücken, nachdem Sie jeden Bezug ausgewählt haben. Andernfalls wird Ihre Formel nicht funktionieren, wenn Sie den Rest der Daten in Tabelle 2 ergänzen.

Schauen wir uns das in Aktion an.

In Zelle I3 möchten wir Excel anweisen, in Tabelle 1 zu suchen und einen Wert über die Funktion INDEX in Tabelle 2 zu ziehen.

=INDEX

Wir müssen nun die Klammern öffnen und Excel mitteilen, wo sich die Daten in Tabelle 1 befinden. Markieren Sie dazu alle Daten in Tabelle 1, aber nicht die Spalten- und Zeilenüberschriften. Drücken Sie F4, um einen absoluten Bezug herzustellen, und fügen Sie ein Komma hinzu.

=INDEX($B$3:$F$7,

Der nächste Schritt besteht darin, Excel anzuweisen, die Daten in Tabelle 2 mit den Daten in Tabelle 1 abzugleichen. Geben Sie MATCH ein, öffnen Sie eine neue Klammer, und klicken Sie auf den ersten Eintrag in der ersten Spalte von Tabelle 2. In unserem Fall ist das Davies in Zelle H3. Fügen Sie ein Komma hinzu.

=INDEX($B$3:$F$7,MATCH(H3,

Als Nächstes muss Excel wissen, was es als Referenz zum Nachschlagen in Tabelle 1 verwenden soll. In unserem Fall sind es die Daten von Zelle A3 bis Zelle A7 (die Namen der Spieler), also markieren Sie diesen Bereich, drücken Sie F4 und fügen Sie ein Komma ein. Geben Sie dann 0 (Null) ein, um Excel mitzuteilen, dass wir nach einer exakten Übereinstimmung suchen, und schließen Sie die Klammern. Fügen Sie ein weiteres Komma hinzu.

=INDEX($B$3:$F$7,MATCH(H3,$A$3:$A$7,0),

Nun müssen wir den MATCH-Vorgang für die Daten wiederholen, die sich ändern, wenn wir eine andere Option in der Dropdown-Liste auswählen. In unserem Fall ist das die Spielnummer. Nachdem wir also ein zweites Mal MATCH in unsere Formel eingegeben haben, klicken wir auf I2, drücken F4 und fügen ein Komma ein.

=INDEX($B$3:$F$7,MATCH(H3,$A$3:$A$7,0),MATCH($I$2,

Und wieder müssen wir Excel mitteilen, wo die Daten in Tabelle 1 zu finden sind. In unserem Beispiel sind das die Zellen B2 bis F4. Vergessen Sie nicht, F4 zu drücken, nachdem Sie auf diese Zellen verwiesen haben. Fügen Sie dann ein Komma und eine 0 ein, und schließen Sie die beiden Klammern zusammen. Drücken Sie abschließend die Eingabetaste.

=INDEX($B$3:$F$7,MATCH(H3,$A$3:$A$7,0),MATCH($I$2,$B$2:$F$2,0))

Klicken und ziehen Sie nun den Griff in der rechten unteren Ecke der Zelle, in die Sie gerade Ihre Formel eingegeben haben, um die restlichen Daten in Tabelle 2 automatisch auszufüllen.

A table in Excel being AutoFilled.

Versuchen Sie, Ihre Dropdown-Option auf einen anderen Wert zu ändern, um zu sehen, wie sich die Daten in Tabelle 2 ändern, und überprüfen Sie, ob sie mit den Informationen in Tabelle 1 übereinstimmen. Denken Sie daran, dass wir die erste Spalte in Tabelle 2 alphabetisch sortiert haben. Prüfen Sie also sorgfältig, denn die erste Spalte in Tabelle 1 wird in einer anderen Reihenfolge sein!

An Excel sheet with a drop-down selection changed to 'Game 3,' and Table 2 showing the data for this selection.

Da sich die Tabelle 2 nun erfolgreich in Abhängigkeit von unserer Dropdown-Auswahl ändert, können wir nun das Diagramm erstellen.

Schritt 4: Einfügen und Formatieren des Diagramms

Markieren Sie alle Daten in Tabelle 2 (einschließlich der Spalten- und Zeilenköpfe), öffnen Sie die Registerkarte "Einfügen" und wählen Sie ein Diagramm aus, das Ihnen zusagt.

An Excel spreadsheet with Table 2 selected and the charts highlighted in the Insert tab.

Wir haben uns für ein einfaches 2D-Säulendiagramm entschieden.

An Excel chart with the data chosen by a drop-down box, which uses Table 2 to retrieve data from Table 1.

Formatieren Sie das Diagramm so, wie Sie es wünschen, und spielen Sie mit der Dropdown-Liste, um zu sehen, wie sich die Tabelle 2 und damit auch das Diagramm an Ihre Auswahl anpasst. Auch der Titel Ihres Diagramms ändert sich.

Schritt 5: Fügen Sie eine Durchschnittslinie zu Ihrem Diagramm hinzu

Um dem Diagramm eine Durchschnittslinie hinzuzufügen, müssen Sie der Tabelle 2 weitere Daten hinzufügen. Fügen Sie eine weitere Spaltenüberschrift zu Tabelle 2 hinzu und nennen Sie sie Durchschnitt.

An Excel spreadsheet with two tables and a chart. Table 2 has a new column, headed 'Average.'

Verwenden Sie nun die Funktion MITTELWERT, um den Durchschnitt der ausgewählten Daten zu erfassen. In unserem Fall haben wir das Spiel 4 ausgewählt. Wir möchten nun die durchschnittliche Bewertung aller fünf Spieler für dieses Spiel ermitteln. Geben Sie dazu ein

=DURCHSCHNITT

in die erste Zelle dieser neuen Spalte ein, und wählen Sie dann die Daten in der vorherigen Spalte aus. Drücken Sie F4, um einen absoluten Bezug herzustellen, und schließen Sie dann die Klammern und drücken Sie Enter.

An Excel table with the average of the first data column being calculated in the second data column.

Klicken Sie dann auf den AutoAusfüllen-Griff und ziehen Sie die Formel an den unteren Rand der Spalte.

Jetzt wollen wir die Durchschnittsdaten zu unserem Diagramm hinzufügen.

Klicken Sie auf eine beliebige Stelle am Rand des Diagramms und klicken Sie im Menüband auf "Diagrammdesign". Gehen Sie dann zur Gruppe Daten und klicken Sie auf "Daten auswählen"

An Excel chart selected with the 'Select Data' icon in the Chart Design group selected.

Klicken Sie im Fenster "Datenquelle auswählen" auf "Hinzufügen"

Excel's Select Data Source window with 'Add' highlighted.

Deaktivieren Sie dann im Fenster "Reihe bearbeiten" das Feld "Reihenwerte", und wählen Sie die Daten in der Spalte "Durchschnitt" in Tabelle 2 aus. Klicken Sie auf "OK", und der Durchschnitt wird als zusätzlicher Balken in Ihrem Diagramm angezeigt.

An Excel sheet showing the Edit Series dialog box, the data in 'Series Values' is taken from Table 2, and the chart shows the average value as a column.

Um diese Durchschnittsdaten in eine Linie über den vorhandenen Balken zu ändern, klicken Sie mit der rechten Maustaste auf einen der neuen Balken und dann auf "Seriendiagrammtyp ändern"

An Excel chart with an average column selected through a right-click, and 'Change Series Chart Type' selected.

Öffnen Sie die Registerkarte "Alle Diagramme", klicken Sie im Menü auf der linken Seite auf "Combo" und ändern Sie die Durchschnittsdatenreihe in "Linie"

The Change Chart Type window in Excel, with the 'All Charts' tab opened, the 'Combo' menu selected, and Series 2 changed to 'Line.'

Klicken Sie auf "OK", um das Ergebnis zu sehen, und ändern Sie die Dropdown-Auswahl, um zu sehen, wie sich Ihr Diagramm dynamisch verändert!

An Excel chart reflecting the drop-down choice and with an average line included as an additional data set.

Anschließend können Sie Ihre Linie so formatieren, dass sie so hervorsticht, wie Sie es wünschen.

Nachdem Sie nun dieses beeindruckende, dynamische Diagramm erstellt haben, sollten Sie sich einige Möglichkeiten ansehen, wie Sie es noch besser zur Geltung bringen können.