Inhalt
- Erstellen benannter Bereiche in Excel 2007 und 2010
- Erstellen eines dynamischen Diagramms in Excel 2007 und 2010
- Fazit
Robbie schreibt hauptsächlich über Skyrim, beleuchtet aber gelegentlich auch die Kuriositäten von Microsoft-Anwendungen wie Excel und Outlook.
Hallo und willkommen zu meinem Artikel über die Verwendung der OFFSET Funktion. In dem heutigen Artikel werde ich untersuchen, wie dynamische Diagramme in Excel mithilfe von erstellt werden OFFSET ebenso gut wie Definierte Namen um benannte Bereiche zu erstellen.
Auf diese Weise können Sie aus Ihren Daten Diagramme erstellen, die beim Hinzufügen oder Entfernen von Daten automatisch aktualisiert werden. Bei einem statischen Diagramm müssen Sie beim Hinzufügen von Daten Ihr Diagramm manuell aktualisieren, damit die neuen Daten angezeigt werden. Ein dynamisches Diagramm erledigt dies automatisch für Sie.
In meinem Vorheriger Artikel auf der OFFSET Funktion habe ich untersucht, wie man es mit dem verwendet COUNTA Funktion zum Erstellen dynamischer Datenbereiche, mit denen Sie sie beispielsweise verwenden können SUMME um Rechnungen, Verkaufs- oder Webverkehrsdaten zu addieren. Wenn Sie Daten löschen oder zum Dynamikbereich hinzufügen, wird Ihre Formel automatisch neu berechnet, um die neuen Daten widerzuspiegeln.
Erstellen benannter Bereiche in Excel 2007 und 2010
Der erste Schritt besteht darin, benannte Bereiche aus unseren Daten zu definieren.
Die benannten Bereiche werden dann verwendet, um die Daten zu definieren, die wir zum Erstellen der dynamischen Diagramme verwenden.
Wir müssen zwei benannte Bereiche erstellen, einen für die Daten selbst und einen für die Diagrammbeschriftungen. Zunächst erstellen wir den Bereich für die Daten, die in unserem Diagramm angezeigt werden.
- Wähle aus Name definieren Taste auf der Formeln Registerkarte in der Definierte Namen Gruppe.
- Definieren Sie a Name (ohne Leerzeichen) können Sie sehen. Ich habe DailyVisits verwendet.
- In dem Bezieht sich auf Geben Sie im Feld die folgende OFFSET-Formel ein:
= OFFSET (DynamicCharts! $ B $ 2,1,0, COUNTA (DynamicCharts! $ B: $ B), 1)
Die obige Formel erstellt einen Dynamikbereich ab Zelle B2. Es zählt die gleiche Anzahl von Zeilen herunter wie die Anzahl der Zeilen, von denen festgestellt wurde, dass sie Daten enthalten COUNTA.
Also, wenn COUNTA Wenn 23 Zellen Daten enthalten, beginnt der Bereich bei B2 und endet bei B25.
COUNTA ist dynamisch. Wenn Daten hinzugefügt oder entfernt werden, werden sie automatisch aktualisiert, wodurch sich der Bereich ändert. Das OFFSET Die Funktion wird in meinem ersten Hub näher erläutert OFFSET (Der Link befindet sich in der Einleitung).
Hinweis:Wenn Sie Änderungen an der vornehmen Bezieht sich auf Verwenden Sie die Pfeiltasten nicht, um sich in diesem Dialogfeld zu bewegen, da sonst der Formel Zellreferenzen hinzugefügt werden. Bewegen Sie sich stattdessen mit der Maus.
Hinweis:Verwenden Sie die, um Ihre benannten Bereiche nach ihrer Erstellung zu bearbeiten Namensmanager Schaltfläche links neben der Schaltfläche Name definieren, mit der Sie sie erstellt haben.
- Als nächstes müssen wir den obigen Vorgang wiederholen, diesmal mit Label für die Name und die Formel unten in der Bezieht sich auf Feld, um unseren Dynamikbereich für die Diagrammbeschriftungen zu erstellen.
= OFFSET (DynamicCharts! $ A $ 2,1,0, COUNTA (DynamicCharts! A $ 3: $ A $ 33), 1
Erstellen eines dynamischen Diagramms in Excel 2007 und 2010
Jetzt haben wir die benannten Bereiche, wir müssen das Diagramm erstellen:
- Wähle aus Säule Schaltfläche in der Diagramme Gruppe auf der Einfügen Tab
Dadurch wird ein vollständig leeres Diagramm erstellt.
- Klicken Sie bei ausgewähltem Diagramm auf Wählen Sie Daten Schaltfläche in der Daten Gruppe auf der Design Registerkarte unter Diagrammwerkzeuge.
- Ändern Sie den Diagrammdatenbereich so, dass der Name, den Sie oben für die Daten angegeben haben (ich habe DailyVisits verwendet), nach dem Namen der Registerkarte eingegeben wird, wie unten gezeigt.
In meinem Beispiel habe ich Folgendes geändert:
= DynamicCharts! $ F $ 7
zu
= DynamicCharts! DailyVisits
Das Diagramm sieht nun so aus.
So fügen Sie unsere dynamischen Labels hinzu:
- Wählen Sie das Diagramm erneut aus.
- Klick auf das Wählen Sie Daten Schaltfläche in der Daten Gruppe auf der Design Registerkarte unter Diagrammwerkzeuge.
- Unter Horizontale (Kategorie) Achsenbeschriftungen, klicken Bearbeiten.
- In dem Achsenbeschriftung Bereich, geben Sie den Namen Ihres benannten Bereichs für Ihre Etiketten ein (ich habe Etiketten für meine ausgewählt).
- Klicken OK zweimal und Excel hat jetzt Ihre Beschriftungen zum Diagramm hinzugefügt.
Schließlich müssen wir sicherstellen, dass das Diagramm die genannten Bereiche verwendet. Um dies zu tun:
- Stellen Sie sicher, dass das Diagramm erneut ausgewählt ist.
- Klicken Sie auf die Datenreihe.
- In der Formelleiste sehen Sie eine Formel, die mit = SERIES beginnt.
Die Formel in meinem Diagramm lautet:
= SERIE (, 'Analytics Overview.xlsm'! Labels, DynamicCharts! $ B $ 3: $ B $ 39,1)
Bevor wir diese Formel bearbeiten, schauen wir uns ihre Syntax an.
Die Formel besteht aus vier Teilen.
- Der Titel des Diagramms (dies ist optional)
- Die Position der Beschriftungen für die horizontale Achse
- Der Speicherort der Daten für die Serie
- Die Reihenfolge, in der die Serie in der Tabelle angezeigt wird (1 für die erste, 2 für die zweite usw.)
Es gibt drei Teile der Formel, die Sie bearbeiten sollten.
- Der erste besteht darin, den Diagrammtitel aus dem Datenkopf hinzuzufügen, wenn Sie dies wünschen (wenn Sie links neben dem ersten Komma in der Formel klicken und dann die Zelle auswählen, die Ihren Datenkopf enthält (in meinem Beispiel B2), wird Excel dies tun füge es automatisch für dich hinzu), so dass der erste Teil der Formel lautet:
= SERIE (DynamicCharts! $ B $ 2,
- Der zweite Teil ist der Teil, der die dynamischen Beschriftungen enthält. Diese werden automatisch aktualisiert, wenn sich die Beschriftungen in Ihrer Tabelle ändern.
'Analytics Overview.xlsm'! Labels,
- Der dritte Teil besteht darin, sicherzustellen, dass das Diagramm aktualisiert wird, wenn Sie Daten dynamisch hinzufügen oder entfernen. Um dies zu tun:
Ändern Sie den letzten Teil der Formel $ B $ 3: $ B $ 39 in Ihren benannten Datenbereich für die Daten (meiner ist DailyVisits).
DynamicCharts! $ B $ 3: $ B $ 39,
Die Formel lautet:
= SERIE (DynamicCharts! $ B $ 2, 'Analytics Overview.xlsm'! Labels, DynamicCharts! DailyVisits, 1)
Jetzt haben wir das Diagramm fertiggestellt, eine letzte Sache, die Sie bei der Eingabe von Daten beachten müssen. Wenn Sie Daten ohne die entsprechenden Beschriftungen in der angrenzenden Zeile eingeben, fügt Excel sie Ihrem Diagramm nicht hinzu. Die neuen Daten werden erst angezeigt, wenn die Beschriftungen hinzugefügt wurden. Wenn Sie eine Reihe von Beschriftungen ohne Daten hinzufügen, zeigt Excel die erste Beschriftung ohne Daten an, fügt dem Diagramm jedoch keine weiteren Beschriftungen ohne Daten hinzu.
Fazit
Da unser Leben immer geschäftiger wird und die Menge an Informationen, mit denen wir arbeiten, zunimmt, ist es sowohl unter dem Gesichtspunkt der Zeitersparnis als auch unter dem Gesichtspunkt der Fehlerreduzierung immer sehr nützlich, so viel wie möglich zu automatisieren OFFSET Die Funktion ist in der Tat sehr nützlich, um sowohl Formeln als auch Diagramme in Excel zu automatisieren. Insbesondere für Tabellenkalkulationen, die häufig aktualisiert werden, ist die Automatisierung der Aktualisierung Ihrer Diagramme in der Tat sehr praktisch.
Das OFFSET Mit der Funktion in Excel können Sie dynamische Bereiche und dynamische Diagramme erstellen, die automatisch aktualisiert werden, wenn Daten innerhalb des Bereichs hinzugefügt oder entfernt werden. In diesem Artikel haben wir benannte Bereiche erstellt und diese mit dem verwendet OFFSET Funktion zum Erstellen dynamischer Diagramme.
Vielen Dank fürs Lesen und ich hoffe, dass Sie jetzt Spaß daran haben, Ihre Diagramme dynamisch zu aktualisieren und Ihre neue Freizeit für etwas angenehmeres zu verbringen. Bitte hinterlassen Sie Ihre Kommentare unten.
Dieser Artikel ist genau und nach bestem Wissen des Autors. Der Inhalt dient nur zu Informations- oder Unterhaltungszwecken und ersetzt nicht die persönliche Beratung oder professionelle Beratung in geschäftlichen, finanziellen, rechtlichen oder technischen Angelegenheiten.
