So erstellen Sie einen dynamischen Diagrammbereich in Excel

In diesem Tutorial wird gezeigt, wie Sie in allen Excel-Versionen einen dynamischen Diagrammbereich erstellen: 2007, 2010, 2013, 2016 und 2022.

Wenn Sie einen Datensatz zum Plotten eines Diagramms in Excel erweitern oder verkleinern, müssen standardmäßig auch die zugrunde liegenden Quelldaten manuell angepasst werden.

Durch das Erstellen dynamischer Diagrammbereiche können Sie diesen Aufwand jedoch vermeiden.

Dynamische Diagrammbereiche ermöglichen es Ihnen, die Quelldaten jedes Mal automatisch zu aktualisieren, wenn Sie Werte aus dem Datenbereich hinzufügen oder daraus entfernen, was viel Zeit und Mühe spart.

In diesem Tutorial erfahren Sie alles, was Sie wissen müssen, um die Kraft von zu entfesseln Dynamische Diagrammbereiche.

Dynamische Diagrammbereiche - Einführung

Betrachten Sie den folgenden Beispieldatensatz zur Analyse von Gewinnmargenschwankungen:

Grundsätzlich gibt es zwei Möglichkeiten, einen dynamischen Diagrammbereich einzurichten:

1. Konvertieren des Datenbereichs in eine Tabelle
2. Verwenden dynamischer benannter Bereiche als Quelldaten des Diagramms.

Beide Methoden haben ihre Vor- und Nachteile, daher werden wir auf jede von ihnen genauer eingehen, damit Sie feststellen können, welche für Sie am besten geeignet ist.

Fangen wir ohne weiteres an.

Die Tabellenmethode

Lassen Sie mich damit beginnen, Ihnen den schnellsten und einfachsten Weg zu zeigen, um die anstehende Aufgabe zu erledigen. Also, hier ist die Übung: Verwandeln Sie den Datenbereich in eine Tabelle, und Sie sind golden-einfacher als Erbsen zu schälen.

Auf diese Weise wird alles, was Sie in die Zellen am Ende dieser Tabelle eingeben, automatisch in die Quelldaten des Diagramms aufgenommen.

Hier erfahren Sie, wie Sie dies in nur zwei einfachen Schritten erreichen können.

Schritt 1: Konvertieren Sie den Datenbereich in eine Tabelle.

Verwandeln Sie den Zellbereich, der Ihre Diagrammdaten enthält, gleich zu Beginn in eine Tabelle.

1. Markieren Sie den gesamten Datenbereich (A1: B6).
2. Drücke den Einfügung Tab.
3. Schlagen Sie die „Tisch" Taste.

In dem Tabelle erstellen Dialogfeld, gehen Sie wie folgt vor:

1. Vergewissern Sie sich, dass der markierte Zellbereich mit der gesamten Datentabelle übereinstimmt.
2. Wenn Ihre Tabelle keine Kopfzeile enthält, deaktivieren Sie das „Meine Tabelle hat Überschriften" Kasten.
3. Klicken "OK.“

Als Ergebnis sollten Sie diese Tabelle erhalten:

Schritt 2: Erstellen Sie ein Diagramm basierend auf der Tabelle.

Der Grundstein ist gelegt, so dass Sie nun anhand der Tabelle ein Diagramm erstellen können.

1. Markieren Sie die gesamte Tabelle (A1: B6).
2. Navigieren Sie zum Einfügung Tab.
3. Erstellen Sie ein beliebiges 2D-Diagramm. Zur Veranschaulichung erstellen wir ein einfaches Säulendiagramm (Säulen- oder Balkendiagramm einfügen > Gruppierte Säule).

Das ist es! Um die Technik zu testen, versuchen Sie, neue Datenpunkte hinzuzufügen am Ende der Tabelle um sie automatisch auf dem Plot anzuzeigen. Wie viel einfacher kann es sein?

HINWEIS: Bei diesem Ansatz sollte der Datensatz noch nie Leere Zellen enthalten, die das Diagramm ruinieren.

Die dynamische Named-Range-Methode

Obwohl einfach anzuwenden, ist das zuvor gezeigte, Tabellenmethode hat einige gravierende nachteile. Zum Beispiel wird das Diagramm durcheinander gebracht, wenn der neue Datensatz kleiner als die ursprüngliche Datentabelle ist - und manchmal möchten Sie einfach nicht, dass der Datenbereich in eine Tabelle umgewandelt wird.

Die Entscheidung für benannte Bereiche kann etwas mehr Zeit und Mühe kosten, aber die Technik negiert die Nachteile der Tabellenmethode und macht den Dynamikbereich auf lange Sicht viel komfortabler.

Schritt 1: Erstellen Sie die dynamischen benannten Bereiche.

Richten Sie zunächst die benannten Bereiche ein, die schließlich als Quelldaten für Ihr zukünftiges Diagramm verwendet werden.

1. Gehe zum Formeln Tab.
2. Klicken "Namensmanager.”
3. In dem Namensmanager Dialogfeld, das angezeigt wird, wählen Sie „Neu.”

In dem Neuer Name Erstellen Sie im Dialogfeld einen brandneuen benannten Bereich:

1. Typ "Quartal" neben dem "Name" Gebiet. Passen Sie der Einfachheit halber den Namen des dynamischen Bereichs an die entsprechende Kopfzeilenzelle von . an Spalte A (A1).
2. In dem "Umfang” Feld, wählen Sie das aktuelle Arbeitsblatt aus. In unserem Fall ist das Blatt1.
3. Geben Sie die folgende Formel in das „Bezieht sich auf" Gebiet: =OFFSET(Blatt1!$A$2,0,0,COUNTA(Blatt1!$A:$A)-1,1)

Im Klartext gibt die OFFSET-Funktion jedes Mal, wenn Sie eine Zelle im Arbeitsblatt ändern, nur die tatsächlichen Werte in . zurück Spalte A, ohne die Kopfzeilenzelle (A1), während die COUNTA-Funktion die Anzahl der Werte in der Spalte jedes Mal neu berechnet, wenn das Arbeitsblatt aktualisiert wird – und damit effektiv die ganze schmutzige Arbeit für Sie erledigt.

Lassen Sie uns die Formel genauer aufschlüsseln, damit Sie verstehen, wie sie funktioniert:

HINWEIS: Der Name eines benannten Bereichs muss mit einem Buchstaben oder Unterstrich beginnen und darf keine Leerzeichen enthalten.

Richten Sie ebenfalls einen anderen benannten Bereich basierend auf ein Spalte Gewinnspanne (Spalte B) mit Hilfe dieser Formel und beschrifte sie mit „Gewinnspanne”:

1	=OFFSET(Blatt1!$B$2,0,0,COUNTA(Blatt1!$B:$B)-1,1)

Wiederholen Sie den gleichen Vorgang, wenn Ihre Datentabelle mehrere Spalten mit tatsächlichen Werten enthält. In unserem Fall sollten Sie daher zwei benannte Bereiche einsatzbereit haben:

Schritt #2: Erstellen Sie ein leeres Diagramm.

Wir haben es durch den schwierigsten Teil geschafft. Jetzt ist es an der Zeit, ein leeres Diagramm einzurichten, damit Sie die dynamischen benannten Bereiche manuell einfügen können.

1. Wählen Sie eine leere Zelle im aktuellen Arbeitsblatt aus (Blatt1).
2. Geh zurück zum Einfügung Tab.
3. Richten Sie ein beliebiges 2D-Diagramm ein. Für unser Beispiel erstellen wir ein Säulendiagramm (Säulen- oder Balkendiagramm einfügen > Gruppierte Säule).

Schritt 3: Fügen Sie den/die benannten Bereich/Bereiche hinzu, die die tatsächlichen Werte enthalten.

Fügen Sie zuerst den benannten Bereich (Gewinnspanne) verknüpft mit den Istwerten (Spalte B) in das Diagramm.

Klicken Sie mit der rechten Maustaste auf das leere Diagramm und wählen Sie „Daten auswählen“ aus dem Kontextmenü.

In dem Datenquelle auswählen Dialogfenster klicken Sie auf „Hinzufügen.”

In dem Serie bearbeiten erstellen Sie eine neue Datenreihe:

1. Unter "Serienname”, markieren Sie die entsprechende Kopfzeilenzelle (B1).
2. Unter "Serienwerte”, geben Sie den benannten Bereich an, der im Diagramm gezeichnet werden soll, indem Sie Folgendes eingeben:=Blatt1!Profit_Margin.” Die Referenz besteht aus zwei Teilen: den Namen des aktuellen Arbeitsblatts (=Blatt1) und der jeweilige dynamische benannte Bereich (Gewinnspanne). Das Ausrufezeichen wird verwendet, um die beiden Variablen miteinander zu verbinden.
3. Auswählen "OK.”

Dort angekommen, wird Excel automatisch die Werte grafisch darstellen:

Schritt 4: Fügen Sie den benannten Bereich mit den Achsenbeschriftungen ein.

Ersetzen Sie schließlich die standardmäßigen Kategorieachsenbeschriftungen durch den benannten Bereich, der aus . besteht Spalte A (Quartal).

In dem Datenquelle auswählen Dialogfeld unter „Horizontale (Kategorie) Achsenbeschriftungen," wähle aus "Bearbeiten" Taste.

Fügen Sie dann den benannten Bereich in das Diagramm ein, indem Sie die folgende Referenz unter „Achsenbeschriftungsbereich:”

1	=Blatt1!Quartal

Schließlich ist das Säulendiagramm basierend auf dem dynamischen Diagrammbereich fertig:

Sehen Sie sich das an: Das Diagramm wird automatisch aktualisiert, wenn Sie Daten im dynamischen Bereich hinzufügen oder entfernen.