Dynamische Bereiche mit Namens-Manager

 

Öfters werden wir in bestehenden Berichten mit Daten konfrontiert die sich in Ihrer Struktur verändern.

Es kommt ein neuer Monat zum Resultat hinzu. Die Anzahl Zeilen mit den Kundenaufträgen ist mal kürzer, mal länger. Das Management hat beschlossen die Verkaufsgebiete zu erweitern oder reduzieren, oder die Anzahl Produktkategorien zu vergrößern oder verringern. Verkäufer Müller geht, Verkäufer Meier kommt. Das Risk Management erhält eine neue Kategorie. Etc etc etc.

Das ganze Zahlengerüst muss dann um diese neuen Rubriken erweitert werden.

Dynamische Bereiche können uns helfen diese (langweilige und deswegen auch oft fehlerbehaftete) Arbeit zu eliminieren!

Zwei Methoden um dynamische Bereiche zu erstellen

Namens-Manager

In diesem Artikel konzentrieren wir uns auf diese Methode.

Der Namens-Manager erlaubt es dass man anstelle eines Bereiches, eine Formel eingeben kann. Dies ist weniger intuitiv und bedarf deshalb ein wenig Erklärung.

Excel Tabellen Werkzeug

Grundsätzlich gibt es seit Excel 2007 die sehr hilfreichen Tabellen welche einem in vielen Fällen entgegenkommen bei dieser Problematik. Das Tool ist aber, wie der Namen schon sagt, für Listen und Tabellen gedacht und geeignet. Falls Ihr es noch nicht kennt, solltet Ihr unbedingt den Artikel lesen.

Beispiel: Dynamischer Bereich im Namens-Manager

Hier die Datei mit der Ihr es in Excel selber anschauen könnt: Dynamische Bereiche

Im Namens-Manager habe ich zwei Formeln erstellt: “Monate” und “Sales”. Beide Namen enthalten praktisch identische Formeln (bis auf die Bereiche natürlich) und sind genau gleich strukturiert, weshalb wir nur eine der beiden Namen anschauen werden.

SNAGHTML1e4d552

Die Syntax der Formel in “Sales”

=BEREICH.VERSCHIEBEN(‘Dynamische Bereich mit Diagramm’!$I$3;0;0;ANZAHL2(‘Dynamische Bereich mit Diagramm’!$I$3:$I$14);1)

Die ANZAHL2 Formel prüft ob der angegebene Bereich einen Wert enthaltet. Es werden sowohl Numerische wie auch Text Werte gezählt. Die Säulengrafik zeigt aber nur die Numerischen Werte. Wenn wir also einen neuen Wert eingeben in diesem Bereich so geht der Zähler bei dieser Formel um eins rauf.

Die ANZAHL2 Formel ist in der BEREICH.VERSCHIEBEN eingebettet.

Die Syntax der BEREICH.VERSCHIEBEN Formel ist:

BEREICH.VERSCHIEBEN(Bezug; Zeilen; Spalten; [Höhe]; [Breite])

Mit dem 4. Argument können wir die höhe neu definieren des Bereiches. Das ist genau das was wir wollen! Hier platzieren wir deshalb ANZAHL2 welche uns ja bekanntlich die Anzahl Zellen mit einem Wert zurückgibt.

Somit haben wir unsere ganze Formel welche im Namens-Manager eingegeben wird erstellt.

Säulengrafik

Die Säulengrafik wird zuerst ganz normal über das Einfügen Menü erstellt. Nachher kann man unter Diagrammtools>Entwurf>Daten auswählen>Bearbeiten auswählen.

Hier geben wir als Reihenname die Zelle I2 an.

Bei den Reihenwerten wird unsere Dynamische Formel im Namens-Manager eingegeben:

=’Dynamische Bereiche.xlsx’!Sales

ODER

=’Dynamische Bereich mit Diagramm’!Sales

ACHTUNG: Es ist wichtig dass man den ganzen Pfad angibt, also entweder den Dateinamen, oder das Tabellenblatt, denn sonst will Excel die Referenz nicht annehmen und bringt eine Fehlermeldung!

SNAGHTML1ed4755

Genau gleich wird die Eingabe der dynamischen Formel für die horizontale Achsenbeschriftung eingegeben. Eigentlich benötigt man für die Achsenbeschriftung keine Dynamische Bereichs Formel, denn die diese werden nur eingeblendet wenn Daten vorhanden sind. Ich mache es aber trotzdem da so die Bereiche gleich gross bleiben und die Anzahl Werte nicht über die Anzahl möglichen Achsenbeschriftungen hinauswächst, was dazu führen kann dass die Beschriftung im Diagramm plötzlich fehlt.

Wo setzt Ihr Dynamische Bereichs Formeln ein?

Comments

  • Hi,

    der Beitrag ist toll und sehr verständlich, danke dafür!
    Eine Frage bleibt bei mir jedoch offen:
    Wie kann ich den Dateinamen auch noch dynamisch gestallten? Bei meinen Dateien kommt immer ein Datum vor den Dateinamen, so dass ich es beim Revisionieren einfacher habe und gleichzeitig eine Historie aufbau.

    Wäre toll, wenn es dazu auch noch eine Lösung gäbe.
    Vielen Dank schon Mal im Voraus,
    Daniel

    DanielJanuary 13, 2015
  • Hi Daniel,
    den Dateinamen dynamisch generieren wirst Du über Formeln nicht hinbekommen. Da muss man VBA einsetzen. Dazu habe ich jetzt noch kein Beitrag geschrieben, aber evtl. tue ich das… interessanter Gedanke! Danke für den Kommentar.

    Lukas RohrJanuary 13, 2015
  • Hallo Lukas,
    da bin ich mal auf die Lösung gespannt :) mit VBA.
    Würde nämlich gerne den Bereich “per Variablen” (Anfangszeile, Endzeile) verschieben können. Hoffentlich wird der Link hier auch gepostet.
    Beste Grüße

    --Fragezeichen--May 21, 2015
  • Hallo Herr Rohr,

    wie könnte man die Werte im dynamischen Wertebereich jetzt so filtern, dass man zB. die Werte von Jan bis Jul in einer gestapelten Säule und die Werte von Aug, Okt, Dez in die darüber liegende Säule rausgibt?

    Viele Grüße

    Mathieu

    mathieuSeptember 10, 2015
  • January 18, 2016

    […] Bereich gespeichert ist (in unserem Beispiel heisst der Bereich ). Dieser ist idealerweise dynamisch oder zumindest so erstellt dass wenn neue Zeilen eingeschoben werden der Bereich […]

  • Hallo Lukas,

    gibt es zudem auch eine Möglichkeit (zusätzlich) auch den Anfangsbereich dynamisch zu gestalten?

    Gruß Marc

    MarcFebruary 15, 2016
  • “Hi Daniel,
    den Dateinamen dynamisch generieren wirst Du über Formeln nicht hinbekommen. Da muss man VBA einsetzen. Dazu habe ich jetzt noch kein Beitrag geschrieben, aber evtl. tue ich das… interessanter Gedanke! Danke für den Kommentar.”

    Hallo Lukas,
    ich bin da etwas anderer Meinung.
    Über INDIREKT kann man ja auch den Dateinamen angeben…

    Im Namensmanager kann man z.B. mit
    =INDIREKT(“‘”&E1&”‘!$A$1”) auf $A$1 in einer Datei verweisen deren Name in E1 steht…
    Natürlich beliebig erweiterbar…

    Lars SchiffmannJune 21, 2016
  • Hallo Marc,
    den Anfangbereich kannst du dynamisch adressieren indem du die “0” passend ersetzt.

    “=BEREICH.VERSCHIEBEN(‘Dynamische Bereich mit Diagramm’!$I$3;_0_;_0_;ANZAHL2(‘Dynamische Bereich mit Diagramm’!$I$3:$I$14);1)”

    Die erste “0” gibt die Anzahl Zeilen nach unten an
    Die zweite gibt die Anzahl Spalten nach rechts an.

    Lars SchiffmannJune 21, 2016

Leave a Reply