Wenn Sie ein Diagramm in Excel erstellen und sich die Quelldaten ändern, müssen Sie die Datenquelle des Diagramms aktualisieren, um sicherzustellen, dass es die neuen Daten wiedergibt.
Wenn Sie mit Diagrammen arbeiten, die häufig aktualisiert werden, ist es besser, einen dynamischen Diagrammbereich zu erstellen.
Dieses Tutorial behandelt:
Was ist ein dynamischer Diagrammbereich?
Ein dynamischer Diagrammbereich ist ein Datenbereich, der automatisch aktualisiert wird, wenn Sie die Datenquelle ändern.
Dieser dynamische Bereich wird dann als Quelldaten in einem Diagramm verwendet. Wenn sich die Daten ändern, wird der dynamische Bereich sofort aktualisiert, was zu einer Aktualisierung des Diagramms führt.
Unten sehen Sie ein Beispiel für ein Diagramm, das einen dynamischen Diagrammbereich verwendet.
Beachten Sie, dass das Diagramm mit den neuen Datenpunkten für Mai und Juni aktualisiert wird, sobald die Daten eingegeben werden.
Wie erstellt man einen dynamischen Diagrammbereich in Excel?
Es gibt zwei Möglichkeiten, einen dynamischen Diagrammbereich in Excel zu erstellen:
- Verwendung von Excel-Tabellen
- Verwendung von Formeln
In den meisten Fällen ist die Verwendung von Excel-Tabellen der beste Weg, um dynamische Bereiche in Excel zu erstellen.
Lassen Sie uns sehen, wie jede dieser Methoden funktioniert.
Klicken Sie hier, um die Beispielsdatei herunterzuladen.
Verwendung von Excel-Tabellen
Die Verwendung von Excel-Tabellen ist der beste Weg, um dynamische Bereiche zu erstellen, da sie automatisch aktualisiert werden, wenn ein neuer Datenpunkt hinzugefügt wird.
Die Funktion Excel-Tabellen wurde in der Windows-Version von Excel 2007 eingeführt, und wenn Sie eine frühere Version verwenden, können Sie sie nicht nutzen (siehe den nächsten Abschnitt über das Erstellen dynamischer Diagrammbereiche mithilfe von Formeln).
Im folgenden Beispiel können Sie sehen, dass sich die Excel-Tabelle erweitert, sobald ich neue Daten hinzufüge, um diese Daten als Teil der Tabelle einzuschließen (beachten Sie, dass sich der Rahmen und die Formatierung erweitern, um sie in die Tabelle einzuschließen).
Nun müssen wir diese Excel-Tabelle beim Erstellen der Diagramme verwenden.
Hier sind die genauen Schritte zum Erstellen eines dynamischen Liniendiagramms unter Verwendung der Excel-Tabelle:
- Markieren Sie die gesamte Excel-Tabelle.
- Gehen Sie zur Registerkarte „Einfügen“.
- Wählen Sie in der Gruppe „Diagramme“ das Diagramm „Linie mit Markierungen“.
Das war’s!
Mit den obigen Schritten wird ein Liniendiagramm eingefügt, das automatisch aktualisiert wird, wenn Sie der Excel-Tabelle weitere Daten hinzufügen.
Beachten Sie, dass das Diagramm zwar durch das Hinzufügen neuer Daten automatisch aktualisiert wird, dass aber beim Löschen von Daten die Datenpunkte nicht vollständig entfernt werden. Wenn Sie z. B. 2 Datenpunkte entfernen, wird im Diagramm rechts ein leerer Bereich angezeigt. Um dies zu korrigieren, ziehen Sie die blaue Markierung unten rechts in der Excel-Tabelle, um die gelöschten Datenpunkte aus der Tabelle zu entfernen (wie unten gezeigt).
Während ich das Beispiel eines Liniendiagramms genommen habe, können Sie mit dieser Technik auch andere Diagrammtypen wie Säulen-/Balkendiagramme erstellen.
Excel-Formeln verwenden
Wie bereits erwähnt, ist die Verwendung von Excel-Tabellen der beste Weg, um dynamische Diagrammbereiche zu erstellen.
Wenn Sie jedoch aus irgendeinem Grund keine Excel-Tabelle verwenden können (möglicherweise, wenn Sie Excel 2003 verwenden), gibt es eine andere (etwas komplizierte) Möglichkeit, dynamische Diagrammbereiche mithilfe von Excel-Formeln und benannten Bereichen zu erstellen.
Angenommen, Sie haben den unten gezeigten Datensatz:
Um einen dynamischen Diagrammbereich aus diesen Daten zu erstellen, müssen wir:
- Zwei dynamische benannte Bereiche mit der Formel OFFSET erstellen (jeweils einen für die Spalten „Werte“ und „Monate“). Das Hinzufügen/Löschen eines Datenpunkts würde diese benannten Bereiche automatisch aktualisieren.
- Fügen Sie ein Diagramm ein, das die benannten Bereiche als Datenquelle verwendet.
Lassen Sie mich nun jeden Schritt im Detail erklären.
Schritt 1 – Erstellen von dynamischen benannten Bereichen
Nachfolgend finden Sie die Schritte zum Erstellen von dynamischen benannten Bereichen:
- Gehen Sie zur Registerkarte „Formeln“.
- Klicken Sie auf ‚Namensmanager‘.
- Geben Sie im Dialogfeld „Namensmanager“ den Namen „ChartValues“ an und geben Sie die folgende Formel in den Verweis auf den Teil ein: =OFFSET(Formula!$B$2,,,COUNTIF(Formula!$B$2:$B$100,“<>“))
- Klicken Sie auf OK.
- Klicken Sie im Dialogfeld „Name Manager“ auf Neu.
- Geben Sie im Dialogfeld „Namensmanager“ den Namen „ChartMonths“ an und geben Sie die folgende Formel in den Referenzteil ein: =OFFSET(Formel!$A$2,,,COUNTIF(Formel!$A$2:$A$100,“<>“))
- Klicken Sie auf OK.
- Klicken Sie auf Schließen.
Die obigen Schritte haben zwei benannte Bereiche in der Arbeitsmappe erstellt – ChartValue und ChartMonth (diese beziehen sich auf den Werte- bzw. Monatsbereich im Datensatz).
Wenn Sie die Wertespalte aktualisieren, indem Sie einen weiteren Datenpunkt hinzufügen, wird der benannte Bereich „ChartValue“ nun automatisch aktualisiert, um den zusätzlichen Datenpunkt darin anzuzeigen.
Die Magie wird hier durch die OFFSET-Funktion bewirkt.
In der Formel für den benannten Bereich „ChartValue“ haben wir B2 als Bezugspunkt angegeben. Die OFFSET-Formel beginnt dort und erstreckt sich auf alle gefüllten Zellen in der Spalte.
Die gleiche Logik funktioniert auch in der Formel für den benannten Bereich „ChartMonth“.
Schritt 2 – Erstellen eines Diagramms mit diesen benannten Bereichen
Jetzt müssen Sie nur noch ein Diagramm einfügen, das die benannten Bereiche als Datenquelle verwendet.
So fügen Sie ein Diagramm ein und verwenden dynamische Diagrammbereiche:
- Gehen Sie zur Registerkarte Einfügen.
- Klicken Sie auf „Linien- oder Flächendiagramm einfügen“ und fügen Sie das Diagramm „Linie mit Markierungen“ ein. Dadurch wird das Diagramm in das Arbeitsblatt eingefügt.
- Gehen Sie bei ausgewähltem Diagramm zur Registerkarte „Entwurf“.
- Klicken Sie auf „Daten auswählen“.
- Klicken Sie im Dialogfeld „Datenquelle auswählen“ auf die Schaltfläche „Hinzufügen“ unter „Legendeneinträge (Serien)“.
- Geben Sie in das Feld „Serienwert“ =Formel!ChartValues ein (beachten Sie, dass Sie den Namen des Arbeitsblatts vor dem benannten Bereich angeben müssen, damit dies funktioniert).
- Klicken Sie auf OK.
- Klicken Sie auf die Schaltfläche „Bearbeiten“ im Feld „Horizontale (Kategorie) Achsenbeschriftungen“.
- In das Dialogfeld „Achsenbeschriftungen“ geben Sie =Formula!ChartMonths
- Klicken Sie auf OK.
Das war’s! Jetzt verwendet Ihr Diagramm einen dynamischen Bereich und wird aktualisiert, wenn Sie Datenpunkte im Diagramm hinzufügen/löschen.
Ein paar wichtige Dinge sind zu beachten, wenn Sie benannte Bereiche mit Diagrammen verwenden:
- Es sollten keine leeren Zellen in den Diagrammdaten vorhanden sein. Wenn es leere Zellen gibt, würde sich der benannte Bereich nicht auf den richtigen Datensatz beziehen (da die Gesamtzahl dazu führen würde, dass er sich auf eine geringere Anzahl von Zellen bezieht).
- Sie müssen die Benennungskonvention einhalten, wenn Sie den Blattnamen in der Diagrammquelle verwenden. Wenn der Blattname beispielsweise aus einem einzigen Wort besteht, wie z. B. Formula, können Sie =Formula!ChartValue verwenden. Wenn der Blattname jedoch aus mehr als einem Wort besteht, wie z. B. Formula Chart, müssen Sie =’Formula Chart‘!ChartValue verwenden.
Die folgenden Excel-Tutorials könnten Ihnen auch gefallen:
- Wie man ein Thermometerdiagramm in Excel erstellt.
- Erstellen einer Glockenkurve in Excel.
- Erstellen eines Stufendiagramms in Excel.
- Erstellen eines Pareto-Diagramms in Excel.
- Erstellen eines Histogramms in Excel
- Hinzufügen von Fehlerbalken in Excel (horizontal/vertikal/angepasst)