Ö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.

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!

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?