Dynamische Diagramme mit der Funktion OFFSET und Bereichsnamen von Excel ändern ihre Größe, wenn Daten hinzugefügt oder gelöscht werden. Im Gegensatz zur Verwendung von Tabellen für dynamische Diagramme ist diese Methode rückwärtskompatibel mit früheren Versionen von Excel.
Verwenden Sie die Excel-Funktionen OFFSET und COUNTA, um dynamische Bereichsnamen zu erstellen, die sich automatisch anpassen, wenn Daten hinzugefügt oder gelöscht werden.
Klicken Sie, um die Tutorial-Beispieldatei zur dynamischen Größenänderung von Diagrammen mit OFFSET und Bereichsnamen herunterzuladen. Das Beispiel enthält sowohl horizontale als auch vertikale Daten.
Die OFFSET- und Bereichsnamen-Methode erfordert mehr Arbeit als die zwei Klicks, die für eine Tabelle verwendet werden, aber sie gibt Ihnen die Kontrolle über alle Daten des Diagramms, wie mein Buch „Balanced Scorecards and Operational Dashboards with Microsoft Excel“ zeigt. Auf dieser Website, CriticalToSuccess.com, finden Sie Beispiele für andere Verwendungsmöglichkeiten von OFFSET, z. B. die Erstellung eines Rollendiagramms, die Steuerung von Diagrammen mit Dropdown-Menüs und vieles mehr. Suchen Sie auf CriticalToSuccess.com nach dem Begriff „OFFSET“ oder „dynamischer Bereich“, um Beispiele zu sehen.
Erstellen eines statischen Diagramms
Beginnen Sie Ihr dynamisches Diagramm, indem Sie zunächst ein statisches Diagramm wie folgt erstellen,
1. Klicken Sie in den Bereich B7:D12, damit Excel weiß, wo sich die Diagrammdaten befinden.
2. Wählen Sie auf der Registerkarte Einfügen in der Gruppe Diagramme ein einfaches Diagramm aus, z. B. ein Säulen-, Balken- oder Liniendiagramm.
Verschieben Sie dieses Diagramm so, dass Sie Platz haben, um Daten unter dem Datenbereich hinzuzufügen.
Erstellen von dynamischen Bereichsnamen mit OFFSET
Die verschiedenen Versionen von Excel haben unterschiedliche Methoden zum Erstellen und Bearbeiten von Bereichsnamensformeln. Die folgenden Anweisungen gelten für Excel 2007, 2010 und 2013.
1. Klicken Sie auf der Registerkarte Formeln in der Gruppe Definierter Name auf Name definieren. Das Feld Name definieren wird angezeigt.
Erstellen Sie Bereichsnamen im Feld Name definieren.
2. Geben Sie in das Textfeld Name rngVDate ein. Die Namenskonventionen lauten:
rng Bereichsname
V Vertikale Ausrichtung der Daten in B7:B16
Datum Die Art der Daten im Bereich
3. Schreiben Sie einen Kommentar in das Textfeld Kommentar, der Ihnen oder anderen hilft, zu wissen, wofür dieser Bereichsname steht.
4. Geben Sie in das Feld Bezieht sich auf die OFFSET-Formel ein, die den Datumsdatenbereich berechnet.
=OFFSET(wrksht!$B$7,0,0,COUNTA(wrksht!$B$7:$B$16),1)
5. Klicken Sie auf Ok.
Mit dieser OFFSET-Formel können Sie zusätzliche Daten an beliebiger Stelle im Bereich B7:B16 eingeben, solange sich die neuen Daten am unteren Ende der alten Datumsliste befinden und keine leeren Zellen zwischen den Daten vorhanden sind.
Wiederholen Sie nun den gleichen Vorgang, um Bereichsnamen für die Daten von Sales und Units zu erstellen.
Die Namen und Formeln, die Sie hinzufügen, lauten:
Datenbereichsname bezieht sich auf
Umsätze rngVSales =OFFSET(wrksht!$C$7,0,0,COUNTA(wrksht!$C$7:$C$16),1)
Einheiten rngVEinheiten =OFFSET(wrksht!$D$7,0,0,COUNTA(wrksht!$D$7:$D$16),1)
Hier erfahren Sie, wie OFFSET die Größe des dynamischen Bereichsnamens berechnet. Dieser Artikel gibt Ihnen auch Tipps zur Überprüfung von dynamischen Bereichsnamen.
Klicken Sie hier, um zu erfahren, welchen Fehler Sie bei OFFSET niemals machen sollten. Machen Sie diesen Fehler?
Klicken Sie hier, um die Tutorial-Beispielsdatei zur dynamischen Größenänderung von Diagrammen mit OFFSET und Bereichsnamen herunterzuladen. Das Beispiel enthält sowohl horizontale als auch vertikale Daten.
Einfügen eines Bereichsnamens in die Datumsformel des Diagramms
Jetzt können Sie Ihr Diagramm dynamisch gestalten, indem Sie die Bereichsnamen, die Sie erstellt haben, in die Formeln einfügen, die das Diagramm zur Referenzierung der Daten verwendet. Machen Sie sich keine Sorgen. Das ist ganz einfach. Wenn Sie wissen, wie das geht, erhalten Sie mystische Fähigkeiten und die Macht, jedes Diagramm nach Ihrem Willen zu formen. (Außerdem können Sie damit Diagramme korrigieren, die durcheinander geraten sind.)
Oben haben Sie drei dynamische Bereichsnamen erstellt, die auf Daten verweisen, die vom Diagramm verwendet werden sollen,
rngVDate
rngVSales
rngVUnits
Im Folgenden werden die vom Diagramm verwendeten Zellverweise durch die von Ihnen erstellten dynamischen Bereichsnamen ersetzt,
1. Wählen Sie das Diagramm aus, indem Sie auf seinen Rand klicken.
2. Klicken Sie auf der Registerkarte Diagrammwerkzeuge in der Gruppe Daten auf das Werkzeug Daten auswählen, um das Dialogfeld Datenquelle auswählen anzuzeigen.
Das Dialogfeld Datenquelle auswählen gibt Ihnen eine magische Kontrolle über die Daten, die ein Diagramm verwendet.
3. Klicken Sie unter den Achsenbeschriftungen der horizontalen (Kategorie) auf Bearbeiten, um das Feld Achsenbeschriftungen anzuzeigen. Da die Formel in diesem Dialogfeld schwer zu erkennen ist, klicken Sie auf das Auswahlsymbol rechts neben dem Zellbezug. Dadurch wird ein breiteres Feld Achsenbeschriftungen angezeigt, in dem Sie die Formel referenzieren können. Seien Sie VORSICHTIG, bevor Sie die Formel bearbeiten!
Das Feld Achsenbeschriftungen zeigt die Referenz für die horizontale Achse an.
4. Seien Sie VORSICHTIG! Bevor Sie versuchen, die Formel zu bearbeiten, klicken Sie in die Formel und drücken Sie die Taste Bearbeiten (F2), damit Sie die Formel bearbeiten können. Drücken Sie die Endetaste, um an das Ende der Formel zu gelangen. Ersetzen Sie die Zellverweise nach dem !-Zeichen durch den Bereichsnamen, wie in dieser Abbildung gezeigt.
Ihre Formel sollte jetzt wie folgt aussehen,
=’Dynamically Resize Charts to Data.xlsx‘!rngVDate
Das Feld Achsenbeschriftungen sieht so aus, nachdem Sie F2 gedrückt und die Formel so bearbeitet haben, dass sie den Namen des Datumsbereichs enthält.
TIPP: Wenn Sie beim Bearbeiten der Formel einen Fehler machen, drücken Sie Strg+Z (Rückgängig), um zurückzugehen.
5. Klicken Sie auf Ok, um zum Dialogfeld „Datenquelle auswählen“ zurückzukehren, damit Sie die Namen für „Umsatz“ und „Einheiten“ hinzufügen können.
Einfügen eines Bereichsnamens in die Formel für „Umsatz“ und „Einheiten“ des Diagramms
Ersetzen Sie nun die Formeln für die Achsen „Umsatz“ und „Einheiten“ durch Bereichsnamen, indem Sie fast das gleiche Verfahren anwenden.
1. Klicken Sie im Dialogfeld „Datenquelle auswählen“ auf „Umsatz“ und die Schaltfläche „Bearbeiten“, um das Dialogfeld „Serie bearbeiten“ anzuzeigen.
Ersetzen Sie den Zellverweis „Umsatzdaten“ durch den Bereichsnamen.
2. Klicken Sie auf das Auswahlsymbol rechts neben der Formel „Serienwert“, die auf die Serie „Umsatz“ verweist.
3. Klicken Sie in die Formel, drücken Sie F2 zum Bearbeiten, und ändern Sie die Zellverweise für die Umsatzdaten in den Bereichsnamen rngVSales.
Das Diagramm verwendet jetzt den dynamischen Bereichsnamen, um auf die Umsatzdaten zu verweisen.