Wenn Sie ein Diagramm in Excel erstellen und sich die Quelldaten ändern, müssen Sie die Datenquelle des Diagramms aktualisieren, um sicherzustellen, dass sie die neuen Daten widerspiegelt.
Wenn Sie mit Diagrammen arbeiten, die häufig aktualisiert werden, ist es besser, einen dynamischen Diagrammbereich zu erstellen.
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 Dynamikbereich 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 wurden.
Wie erstelle ich einen dynamischen Diagrammbereich in Excel?
Es gibt zwei Möglichkeiten, einen dynamischen Diagrammbereich in Excel zu erstellen:
- Excel-Tabelle verwenden
- Verwenden von Formeln
In den meisten Fällen ist die Verwendung von Excel Table der beste Weg, um dynamische Bereiche in Excel zu erstellen.
Sehen wir uns an, wie jede dieser Methoden funktioniert.
Klicken Sie hier, um die Beispieldatei herunterzuladen.
Excel-Tabelle verwenden
Die Verwendung der Excel-Tabelle ist die beste Möglichkeit, dynamische Bereiche zu erstellen, da sie automatisch aktualisiert wird, wenn ein neuer Datenpunkt hinzugefügt wird.
Die Excel-Tabellenfunktion wurde in der Excel 2007-Version von Windows eingeführt, und wenn Sie Versionen davor haben, können Sie sie nicht verwenden (siehe den nächsten Abschnitt zum Erstellen dynamischer Diagrammbereiche mithilfe von Formeln).
Profi-Tipp: Um einen Zellbereich in eine Excel-Tabelle zu konvertieren, wählen Sie die Zellen aus und verwenden Sie die Tastenkombination - Strg + T (halten Sie die Strg-Taste gedrückt und drücken Sie die T-Taste).Im folgenden Beispiel sehen Sie, dass die Excel-Tabelle, sobald ich neue Daten hinzufüge, erweitert wird, um diese Daten als Teil der Tabelle einzuschließen (beachten Sie, dass der Rahmen und die Formatierung erweitert werden, um sie in die Tabelle aufzunehmen).
Jetzt müssen wir diese Excel-Tabelle beim Erstellen der Diagramme verwenden.
Hier sind die genauen Schritte zum Erstellen eines dynamischen Liniendiagramms mithilfe der Excel-Tabelle:
- Wählen Sie die gesamte Excel-Tabelle aus.
- Wechseln Sie zur Registerkarte Einfügen.
- Wählen Sie in der Gruppe Diagramme das Diagramm „Linie mit Markierungen“.
Das ist es!
Die obigen Schritte würden ein Liniendiagramm einfügen, das automatisch aktualisiert wird, wenn Sie der Excel-Tabelle weitere Daten hinzufügen.
Beachten Sie, dass beim Hinzufügen neuer Daten das Diagramm automatisch aktualisiert wird, beim Löschen von Daten die Datenpunkte jedoch nicht vollständig entfernt werden. Wenn Sie beispielsweise 2 Datenpunkte entfernen, zeigt das Diagramm rechts eine leere Stelle. 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.
Verwenden von Excel-Formeln
Wie bereits erwähnt, ist die Verwendung einer Excel-Tabelle 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 aus diesen Daten einen dynamischen Diagrammbereich zu erstellen, müssen wir:
- Erstellen Sie mit der OFFSET-Formel zwei dynamische benannte Bereiche (jeweils einen für die Spalte „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 jetzt jeden Schritt im Detail erklären.
Schritt 1 - Dynamische benannte Bereiche erstellen
Im Folgenden sind die Schritte zum Erstellen dynamischer benannter Bereiche aufgeführt:
- Gehen Sie zum Reiter „Formeln“.
- Klicken Sie auf „Namensmanager“.
- Geben Sie im Dialogfeld Name Manager den Namen als ChartWerte und geben Sie in Bezieht sich auf Teil die folgende Formel ein: =OFFSET(Formula!$B$2,,,COUNTIF(Formula!$B$2:$B$100””))
- OK klicken.
- Klicken Sie im Dialogfeld Namensmanager auf Neu.
- Geben Sie im Dialogfeld Name Manager den Namen als DiagrammMonate und geben Sie in Bezieht sich auf Teil die folgende Formel ein: =OFFSET(Formula!$A$2,,,COUNTIF(Formula!$A$2:$A$100””))
- OK klicken.
- Klicken Sie auf Schließen.
Die obigen Schritte haben zwei benannte Bereiche in der Arbeitsmappe erstellt - ChartValue und ChartMonth (diese beziehen sich auf die Werte bzw. den Monatsbereich im Datensatz).
Wenn Sie die Wertespalte aktualisieren, indem Sie einen weiteren Datenpunkt hinzufügen, wird der benannte Bereich von ChartValue jetzt automatisch aktualisiert, um den zusätzlichen Datenpunkt darin anzuzeigen.
Die Magie wird hier von der OFFSET-Funktion ausgeführt.
In der „ChartValue“ benannten Bereichsformel haben wir B2 als Bezugspunkt angegeben. Die OFFSET-Formel beginnt dort und erstreckt sich über alle gefüllten Zellen in der Spalte.
Die gleiche Logik funktioniert auch in der benannten Bereichsformel von ChartMonth.
Schritt 2 - Erstellen Sie ein Diagramm 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:
- Wechseln 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.
- Wechseln Sie bei ausgewähltem Diagramm zur Registerkarte Design.
- Klicken Sie auf Daten auswählen.
- Klicken Sie im Dialogfeld „Datenquelle auswählen“ unter „Legendeneinträge (Serien)“ auf die Schaltfläche Hinzufügen.
- Geben Sie im Feld Reihenwert =Formula!ChartValues ein (beachten Sie, dass Sie den Arbeitsblattnamen vor dem benannten Bereich angeben müssen, damit dies funktioniert).
- OK klicken.
- Klicken Sie in den „Horizontalen (Kategorie) Achsenbeschriftungen“ auf die Schaltfläche Bearbeiten.
- Geben Sie im Dialogfeld „Achsenbeschriftungen“ =Formula!ChartMonths . ein
- OK klicken.
Das ist es! Ihr Diagramm verwendet jetzt einen dynamischen Bereich und wird aktualisiert, wenn Sie Datenpunkte im Diagramm hinzufügen/löschen.
Einige wichtige Dinge, die Sie wissen sollten, wenn Sie benannte Bereiche mit Diagrammen verwenden:
- Die Diagrammdaten sollten keine leeren Zellen enthalten. Wenn ein Leerzeichen vorhanden ist, 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 Namenskonvention befolgen, wenn Sie den Blattnamen in der Diagrammquelle verwenden. Wenn der Blattname beispielsweise ein einzelnes Wort ist, z. B. Formel, können Sie =Formula!ChartValue verwenden. Wenn es jedoch mehr als ein Wort gibt, z. B. Formula Chart, müssen Sie =’Formula Chart’!ChartValue verwenden.
