Excel - Dynamische Bereichsnamen :: Office-Hilfe.com - Wir lösen Ihre Probleme mit Microsoft Office

Einloggen, um private Nachrichten zu lesen

Downloads

Excel - Dynamische Bereichsnamen

Office-Hilfe.com - Wir lösen Ihre Probleme mit Microsoft Office Foren-Übersicht » Microsoft Excel Tutorials

Autor

Nachricht

schatzi
Moderator

Anmeldedatum: 09.12.2006
Beiträge: 6790

Verfasst am: 18.08.2007, 12:43

Hallo!

Manchmal ergibt es sich in Excel, dass sich die Bereiche, in denen sich die auszuwertenden Daten befinden, (un-)regelmäßig erweitern. Dies führt speziell bei der Erstellung von Diagrammen und auch Pivot-Tabellen häufig zu Problemen.

Beispiel (siehe auch angehängte Datei):
Ihr wollt eine monatliche Umsatz-Übersicht des laufenden Jahres erfassen und daraus ein Diagramm erstellen. Woher aber sollt ihr im Mai bereits die Umsätze des Septembers kennen? Diese Werte können ja erst später bekannt sein und sollen auch erst dann in die Auswertung einfließen. Die Bereiche sollen sich also dynamisch an die bereits ausgefüllten Zellen anpassen.

Nun könnt ihr natürlich am Ende eines jeden Monats den auszuwertenden Bereich manuell um eine Zeile erweitern (und in meinem einfachen Beispiel hält sich der Aufwand dafür auch in Grenzen), aber schöner wäre es natürlich, wenn Excel automatisch erkennen könnte, bis wohin ein Bereich aktuell ausgefüllt ist. Für diese Zwecke (und natürlich auch für andere) stellt Excel die Funktion BEREICH.VERSCHIEBEN zur Verfügung, die in Verbindung mit einer Namensdefinition gar wundervolle Dinge vollbringen kann. Leider ist der Funktionsname in der deutschen Excel-Version sehr lang; in der englischen Version heißt sie kurz und bündig OFFSET.

Mit der Funktion BEREICH.VERSCHIEBEN kann man Bereiche auf eine eigene Weise definieren. Die Syntax dieser Funktion lautet wie folgt:

=BEREICH.VERSCHIEBEN(AusgangsBereich;ZeilenVerschub;SpaltenVerschub;HöheZeilen;BreiteSpalten)

Klingt erstmal kompliziert, ist es aber eigentlich gar nicht. Ein Beispiel (hat nichts mit der angehängten Datei zu tun):

=BEREICH.VERSCHIEBEN(A1;2;3;4;5)

Welcher Bereich wird hier definiert?
Der AusgangsBereich ist A1.
Dieser wird um zwei Zeilen verschoben, ergibt also A3. (Eine negative Zahl bewirkt einen Verschub nach oben. Wird das Argument mit der Zahl Null oder auch gar nicht besetzt, wird kein ZeilenVerschub vorgenommen.)
Dieser wird um drei Spalten verschoben, ergibt also D3. (Eine negative Zahl bewirkt einen Verschub nach links. Wird das Argument mit der Zahl Null oder auch gar nicht besetzt, wird kein SpaltenVerschub vorgenommen.)
Der Bereich hat eine Höhe von vier Zeilen, ergibt also D3:D6. (Wird das Argument nicht besetzt, ist die Höhe gleich der Höhe des AusgangsBereichs.)
Der Bereich hat eine Breite von fünf Spalten, ergibt also D3:H6. (Wird das Argument nicht besetzt, ist die Breite gleich der Breite des AusgangsBereichs.)

Die Formel „=BEREICH.VERSCHIEBEN(A1;2;3;4;5)“ definiert also den Bereich D3:H6. Also sind z.B. die folgenden beiden Formeln identisch in ihrer Funktionsweise:

=SUMME(D3:H6)
und
=SUMME(BEREICH.VERSCHIEBEN(A1;2;3;4;5))

Was haben wir nun bisher gewonnen?
Zugegebenermaßen noch gar nichts, außer dass die Formel länger geworden ist...
Interessant wird die Funktion BEREICH.VERSCHIEBEN dadurch, dass die Parameter natürlich auch dynamisch gesetzt werden können, d.h. man kann weitere Excel-Funktionen hier einfließen lassen.
Ein weiteres Beispiel hierzu:

=BEREICH.VERSCHIEBEN($A$1;;;ANZAHL($E:$E))

Was passiert denn nun hier?
Der AusgangsBereich ist A1.
Die Argumente für den Zeilen- und SpaltenVerschub wurden nicht besetzt, also findet kein Verschub statt, also befinden wir uns immer noch in A1.
Nun wird die Höhe des Bereichs definiert durch ANZAHL2($E:$E). Es werden also alle Zellen der Spalte E gezählt, die eine Zahl beinhalten und die Anzahl dieser Zellen ergibt die Höhe des Bereichs. (Näheres zur Funktion ANZAHL gibt es in der Excel-Hilfe.)
Haben also z.B. 17 Zellen in der Spalte E einen Inhalt und sind alle anderen Zellen der Spalte E leer, dann ergibt ANZAHL($E:$E) den Wert 17. Also hat unser Bereich eine Höhe von 17 Zeilen, ergibt also A1:A17. Wird in Spalte E ein weiterer Eintrag vorgenommen, erweitert sich also der Bereich automatisch auf A1:A18.
Das letzte Argument „BreiteSpalten“ wurde auch nicht besetzt, daher bleibt die Breite gleich der Breite des AusgangsBereichs, also eine Spalte.

Soweit alles klar? Hoffentlich...

Ihre ganze Genialität kann die Funktion BEREICH.VERSCHIEBEN dann ausspielen, wenn man sie mit einem Namen verbindet. Wie ihr vielleicht wisst, kann man in Excel einem Bereich einen Namen zuweisen. Dies geschieht über Menü Bearbeiten -> Namen -> Definieren. (In Excel2007 findet ihr diesen Punkt im Ribbon „Formeln“ unter „Namen definieren“.)
Gebt hier im Feld „Name“ einen aussagekräftigen Namen eurer Wahl ein, z.B. „MeinName“ oder „Liste“ oder „Umsatz“ oder, oder, oder... (Seid hier bitte vorsichtig bei der Verwendung von Sonderzeichen; insbesondere Plus- und Minuszeichen führen zu Problemen.)
Im Feld „Bezieht sich auf“ könnt ihr einen Bereich angeben, z.B. =A1:C50.
Nun bewirken die folgenden beiden Formeln genau das Gleiche:

=SUMME(A1:C50)
und
=SUMME(MeinName)

Und hier schließt sich jetzt der Kreis:
Dadurch, dass ihr gelernt habt, dass man Bereiche nicht nur über die direkte Eingabe von Zellbezügen definieren kann, sondern auch über die Funktion BEREICH.VERSCHIEBEN, lassen sich die tollsten Dinge bewerkstelligen; und jetzt komme ich auch endlich auf das Beispiel in der angehängten Datei zu sprechen...

Um ein dynamisches Diagramm zu erstellen, definiert ihr zunächst zwei Namen nach der obigen Erklärung:

Name: xWerte
Bezieht sich auf: =BEREICH.VERSCHIEBEN($A$2;;;ANZAHL($B:$B))

Name: yWerte
Bezieht sich auf: =BEREICH.VERSCHIEBEN($B$2;;;ANZAHL($B:$B))

Nun erstellt ihr zunächst ein Diagramm auf die (zumindest für mich) gewohnte Weise, nämlich über den Diagramm-Assistenten:

Markiert den Bereich A1:B13 und erstellt mithilfe des Assistenten ein Diagramm eurer Wahl.
Ergebnis: Das Diagramm umfasst das ganze Jahr, obwohl noch lange nicht alle Daten verfügbar sind. Wünschenswert wäre ein Diagramm, welches nur die bisher erfassten Monate anzeigt.

Dazu klickt ihr einfach einen beliebigen Punkt/Balken/Säule/Tortenstück in eurem Diagramm an und ihr seht in der Bearbeitungsleiste eine Formel, die in etwa so aussieht:

=DATENREIHE(Tabelle1!$B$1;Tabelle1!$A$2:$A$13;Tabelle1!$B$2:$B$13;1)

Dies bedeutet in etwa dies:

=DATENREIHE(TitelDesDiagramms;DatenbereichX;DatenbereichY;NummerDerDatenreihe)

Ändert nun manuell in der Bearbeitungsleiste die Einträge in

=DATENREIHE(Tabelle1!$B$1;Tabelle1!xWerte;Tabelle1!yWerte;1)

Jetzt habt ihr ein dynamisches Diagramm erstellt!
Probiert es aus, indem ihr in B7 einen Umsatz-Wert für Juni, Juli, usw. eintragt. Das Diagramm erweitert sich automatisch um den jeweiligen Monat.
Bitte versucht, in diesem Beispiel Leerzeilen zu vermeiden; also z.B. die Umsätze von August und September freizulassen, aber bereits einen Umsatz für Oktober einzutragen. Dafür ist die von mir verwendete Formel nicht geeignet; aber auch dafür gibt es Lösungsmöglichkeiten, auf die ich jedoch hier aus Gründen der Übersichtlichkeit nicht weiter eingehen möchte.

Dieser Beitrag ist für weitere Antworten gesperrt!
Falls ihr weitere Fragen zu diesem Thema habt, so stellt diese bitte im Excel-Forum mit einem Hinweis auf dieses Tutorial.

In diesem Sinne: Bleibt dynamisch!

Dynamisches Diagramm.xls

Beschreibung:

Download

Dateiname:

Dynamisches Diagramm.xls

Dateigröße:

14.5 KB

Heruntergeladen:

1705 mal

_________________

Viele Grüße vom Schatzi
------------------------
Dir wurde geholfen? Dann gib doch bitte ein Feedback! Du hilfst damit automatisch anderen Usern, die ein ähnliches Problem haben!

Ich arbeite mit: Excel 2010 beta, Windows Vista
Ein Tool, um Tabellen(ausschnitte) posten zu können, findet ihr z.B. hier.

Office-Hilfe.com - Wir lösen Ihre Probleme mit Microsoft Office Foren-Übersicht » Microsoft Excel Tutorials

Thema		Autor	Forum	Antworten	Verfasst am
Ähnliche Beiträge
	Excel Solver funktioniert nicht - neu...	VM	Microsoft Excel Hilfe	0	09.03.2010, 14:50
	(Excel 2003) Zellen in Spalte automat...	Pali	Microsoft Excel Hilfe	0	08.03.2010, 13:17
	Wie brechnet Excel Zahlenwerte (Komma...	Tauchmann	Microsoft Excel Hilfe	3	03.03.2010, 11:21
	Diagramm mit "Unterdiagramm"...	Hossy81	Microsoft Excel Hilfe	1	02.03.2010, 23:52
	Problem bei Excel Werte aus Datenblat...	gio2000	Microsoft Excel Hilfe	4	26.02.2010, 18:09

Gehe zu:

Du kannst keine Beiträge in dieses Forum schreiben.
Du kannst auf Beiträge in diesem Forum nicht antworten.
Du kannst deine Beiträge in diesem Forum nicht bearbeiten.
Du kannst deine Beiträge in diesem Forum nicht löschen.
Du kannst an Umfragen in diesem Forum nicht mitmachen.
Du kannst Dateien in diesem Forum posten
Du kannst Dateien in diesem Forum herunterladen

Haftungsausschluss/Disclaimer

SMS kostenlos versenden | Tuning Forum | krankenversicherungsvergleich | Wii Modchip Xbox 360 Konsole

Powered by phpBB © 2001, 2002 phpBB Group :: FI Theme :: Alle Zeiten sind GMT + 1 Stunde
Deutsche Übersetzung von phpBB.de