Office: Excel - Dynamische Bereichsnamen

Helfe beim Thema Excel - Dynamische Bereichsnamen in Microsoft Excel Tutorials um das Problem gemeinsam zu lösen; Hallo! Manchmal ergibt es sich in Excel, dass sich die Bereiche, in denen sich die auszuwertenden Daten befinden, (un-)regelmäßig erweitern. Dies... Dieses Thema im Forum "Microsoft Excel Tutorials" wurde erstellt von schatzi, 18. August 2007.

  1. schatzi Super-Moderator

    Excel - Dynamische Bereichsnamen


    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!
     
    Zuletzt von einem Moderator bearbeitet: 9. Dezember 2020
    schatzi, 18. August 2007
    #1
Thema:

Excel - Dynamische Bereichsnamen

Die Seite wird geladen...
  1. Excel - Dynamische Bereichsnamen - Similar Threads - Excel Dynamische Bereichsnamen

  2. Dynamische Tabellen mit automatischer Aktualisierung

    in Microsoft Excel Hilfe
    Dynamische Tabellen mit automatischer Aktualisierung: Hallo in die Runde! Vorab schon mal vielen Dank für alle Mühen und die Hilfe! Ich habe folgendes Anliegen: Ich habe eine Geräteliste als Excel Datei mit einigen verschiedenen Tabellenblättern...
  3. (Excel) Dynamische Tabelle, VBA, fehlende Formatierung?

    in Microsoft Excel Hilfe
    (Excel) Dynamische Tabelle, VBA, fehlende Formatierung?: Liebe Experten, Gefilterte Daten sollen in ein Listobject übertragen werden. Für das Listobject habe ich ein Template angelegt, dass alle Formatierungen (u.a. bedingte Formatierung etc.) enthält....
  4. "dynamisches Bild" in Excel

    in Microsoft Excel Hilfe
    "dynamisches Bild" in Excel: Hallo Leute, ich verzweifele bald, ich versuche seit heute morgen es hinzubekommen (hab eine Beispieltabelle erzeugt auf die ich mich jetzt direkt beziehe), dass ich in der A2 Zelle per Dropdown...
  5. Tabellenblätter aus Vorlagen-Tabellenblatt generieren mit dynamischen Anpassungen durch Vorgaben

    in Microsoft Excel Hilfe
    Tabellenblätter aus Vorlagen-Tabellenblatt generieren mit dynamischen Anpassungen durch Vorgaben: Hallo, ich suche gerade eine Möglichkeit mittels VBA mehrere Tabellenblätter auf einmal zu generieren (ca. 40), die auf einem Vorlage-Tabellenblatt (TB2) basieren. Das kopieren/ Vervielfachen an...
  6. Dynamische Excel-Tabelle nach Word übertragen mit VBA

    in Microsoft Excel Hilfe
    Dynamische Excel-Tabelle nach Word übertragen mit VBA: Hallo Zusammen, ich bin neu hier und habe folgende Problemstellung: Ich bekomme quartalsweise eine (dynamische) EXCEL-datei mit folgendem Aufbau: Überschriften: Name Vorname PersNr spalte4...
  7. Dynamische Dropdownliste mit Bedingungen/Filter in Excel

    in Microsoft Excel Hilfe
    Dynamische Dropdownliste mit Bedingungen/Filter in Excel: Hallo zusammen, ich bin am verzweifeln und bräuchte dringend Hilfe bei einer benötigten Dropdownliste. Es gibt eine Liste (Front Data) die Informationen anzeigen soll und eine Quelldatei (Source...
  8. Dynamisches Tabellenblatt in Funktion

    in Microsoft Excel Hilfe
    Dynamisches Tabellenblatt in Funktion: Hallo zusammen, seit 3 Stunden verzweifel ich an einem eigentlich simplen Problem. Ich habe ein Übersichtsblatt und möchte die Ergebnisse aus einzelnen Tabellenblättern zusammenführen. Die...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden