In Excel gibt es ja die sehr praktische Möglichkeit, für einen Zellbereich einen Namen zu vergeben, den man später in seinen Funktionen verwenden kann. Gerade komplexere oder verschachtelte Funktionen werden leichter lesbar, wenn man anstelle von Zellkoordinaten einfach einen aussagefähigen Namen verwendet.
Was macht man aber, wenn sich der Bereich ständig ändert, auf den sich ein Name beziehen soll? Weil man z.B. am unteren Ende laufend neue Datensätze anfügt.
Mit einem sich dynamisch anpassenden Bereichsnamen ist das kein Problem!
Und so geht’s:
Die Ausgangslage
Nehmen wir an, du hast in Excel ein kleines Haushaltsbuch erstellt, in der du deine Ausgaben in einer Tabelle erfasst (die Musterdatei kannst du dir hier herunterladen). Es gibt Spalten für Datum, Kategorie, Bemerkung und natürlich für den Betrag. Außerdem möchte ich in einer kleinen Übersicht sehen, wieviel Geld ich in jeder Kategorie ausgegeben habe:
Eingabeliste und Auswertung
Dazu habe ich für den Bereich B4:B12 den Namen „Kategorie“ vergeben und für den Bereich D4:D12 den Namen „Ausgaben“:
Ein statischer Namensbereich
Diese Namen verwende ich in den Zellen G4:G10 in einer einfachen SUMMEWENN-Funktion, um meine kleine Auswertung zu bekommen.
Hier wird aber schon das Problem deutlich: Sobald ich in Zeile 13 neue Daten ergänze, stimmt der angegebene Bereich nicht mehr und die Auswertung ist falsch:
Das Problem mit statischen Namen
Jetzt kommt die Dynamik ins Spiel:
Anstatt einen fixen Zellenbereich für unsere beiden Namen zu definieren, vergeben wir mit Hilfe von ein paar kleinen Excel-Funktionen dynamische Bereichsnamen. Öffnen wir also den Namens-Manager und verändern den Bezug für den Namen „Kategorie“ im Feld „Bezieht sich auf“ wie folgt:
=$B$4:BEREICH.VERSCHIEBEN($B$4;VERGLEICH("";$B:$B;-1)-4;0)
Dynamische Namen
Dann wiederholen wir das Ganze auch für den Namen „Ausgaben“ und geben diese Formel ein:
=$D$4:BEREICH.VERSCHIEBEN($D$4;VERGLEICH("";$B:$B;-1)-4;0)
… und noch einmal.
Und schon passt unsere Auswertung wieder, da sich die benannten Bereiche „Kategorien“ und „Ausgaben“ automatisch angepasst haben und den zuletzt hinzugefügten Datensatz einschließen:
Korrekte Auswertung
Zur Erläuterung
Wir geben statt des statischen einen dynamischen Bezug ein. Dabei bleibt Anfangsbezug immer noch gleich, nämlich $B$4.
Der Endbezug wird über die BEREICH.VERSCHIEBEN-Funktion ermittelt. Diese Funktion habe ich hier und hier schon ausführlich erläutert. Hier nur noch mal die Kurzfassung:
Wir verschieben einen Zellbereich, ausgehend von B4, um eine bestimmte Anzahl von Zeilen nach unten. Um wieviele genau, ermitteln wir mit der VERGLEICH-Funktion. Mit dieser suchen wir nämlich nach der letzten leeren Zelle in Spalte B. Diesen Trick habe ich in diesem Artikel beschrieben. Die Funktion liefert die Zeilennummer der letzten gefüllten Zeile wieder. Von der so ermittelten Zeilennummer ziehe ich in meinem Beispiel noch 4 ab, da meine Datentabelle ja erst ab der vierten Zeile beginnt.
Dynamische Namen werden im Namensfeld leider nicht angezeigt, ebensowenig im Gehezu-Fenster, das man über die Funktionstaste F5 aufrufen kann. Wenn man in diesem Fenster aber im Verweis-Feld den Namen eintippt, wird trotzdem der korrekte Bereich markiert:
Manuelle Eingabe des Namens
Korrekter Bereich wird markiert
Du kannst nun weitere Datensätze eingeben und wie eben gezeigt mit F5 überprüfen, dass tatsächlich der korrekte Bereich für den Namen ermittelt wurde.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
das war wieder ein interessanter Beitrag zum Thema Dynamische Bereichsnamen. Dazu kam mir gleich ein fortführender Gedanke: Mal angenommen, im Bereich Kategorie taucht ein neuer Begriff auf, z.B. Darlehen. Dieser Begriff steht noch nicht in der Zusammenfassung rechts daneben. Man könnte ihn nun einfach dort eintragen und die Formel SUMMEWENN runter ziehen, die einfachste Lösung. Hat man aber sehr viele Datensätze einzutragen mit vielen neuen Begriffen, kann es leicht unübersichtlich werden. Gibt es möglicherweise eine automatische Lösung (ohne VBA), die neuen Begriffe einzutragen?
Mit dem Spezialfilter kann man etwas hin bekommen, macht aber mehr Arbeit als der manuelle Eintrag.
Viele Grüße
Gerhard
Hallo Gerhard,
eine „Quick-and-dirty“-Lösung, um neue Kategorien mehr oder weniger automatisch zu ergänzen, könnte sein, die Kategorienliste als eine Pivot-Tabelle anzulegen und daneben die SUMMEWENN-Funktionen stellen. Durch die Neukalkulation tauchen neue Kategorien automatisch Liste auf und man müsste nur noch die SUMMEWENN-Formel nach unten ziehen.
Schöne Grüße,
Martin
Ich habe den Beitrag zu dynamischen Beeichnamen ausprobiert. Es klappt prima. Ein neuer Datensatz wird am unteren Ende der Tabelle eingefügt. Wie ist es aber bei Tabellen, die vorn in der Spalte unterschiedliche Karegorien haben? Als Beispiel eine Adressendatei, bei der erst alle privaten Adressen kommen (Suchkriterium „Privat“), dann Adressen zum Thema Gesundheit (Suchkriterium „Medizin“) u.s.w.
Ich habe versucht, im dynamischen Bereichnamen hinteren Teil ( …Vergleich(„“; …) die Leerstelle durch das Suchkriterium z.B. „Privat“ zu ersetzen. Das klappt nicht. Hast Du vielleicht eine passende Idee ?
Ich muss noch erwähnen, dass ich das Ganze im Rahmen einer VBA-Prozedur einsetzen will.Neue Adressen sollen einfach am Tabellenschluss eingetragen werden, dann wird nach Kategorien neu sortiert und die Bereiche sollten damit aktuell sein.
Hallo Manfred,
wenn Du ein zusätzliches Kriterium in der Auswertung berücksichtigen möchtest, musst Du nur einen weiteren Namen definieren. Dieser muss sich dann nur auf die entsprechende Spalte beziehen, der Rest der Formel bleibt gleich. Das Leerzeichen in der VERGLEICH-Funktion dient nur dazu, die erste leere Zelle am Ende der Tabelle zu finden. Also angenommen, Du hast eine zweite Kategorie in Spalte A, dann lautet die Formel für den dynamischen Namen:
=Tabelle1!$A$4:BEREICH.VERSCHIEBEN(Tabelle1!$A$4;VERGLEICH(„“;Tabelle1!$A:$A;-1)-4;0)
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für Deine tollen Erklärungen. Ich verstehe nur noch nicht ganz den Sinn der „-4“, mit der Du bewirkst, dass Du erst ab Zeile 4 die Zeilen zählst. Ist die wirklich notwendig? Hast Du das nicht eh schon durch $D$4 festgelegt?
Gruß,
Jake
Hallo Jake,
mit $D$4 wird nur der Anfangspunkt festgelegt. Nun geht es ja darum, wieviele Zeilen man von dort aus braucht. Die VERGLEICH-Funktion liefert die Zeilennummer der letzten gefüllten Zeile, also z.B. Zeile 13. Der dynamische Bereich umfasst aber nicht 13 Zeilen, da die eigentliche Tabelle erst ab Zeile 4 beginnt. Und deshalb muss der Wert 4 noch abgezogen werden. Ich weiß, ist am Anfang etwas verwirrend.
Schöne Grüße,
Martin
Sehr geehrter Heer Martin Weiß,
die Anwendung der dynamischen Bereichsnamen ist für die dargestellten Beispiel gut brauchbar.
Ist es möglich, eine Tabelle mit dynamischen Bereichsnamen zu entwerfen, die für jeden Monat tägliche Tätigkeiten beinhaltet, die einem bestimten Wochentag zugeordnet sind. Beim Wechsel des Monats sollten die Tätigkeiten dem gleichen Wochentag zugeordnet bleiben.
Dabei gibt es Tätigkeiten, die sich am gleichen Wochentag wiederholen wie auch Tätigkeiten, die neu aufgenommen werden.
Der aktuelle Zustand ist, dass beim Wechsel des Monats sich auch der Wochentag zum Monatsbeginn ändert, so dass die geplanten Tätigkeiten sich an einem falschen Tag befinden.
Für diese Änderung könnte die Anwendung des dynamischen Bereichsnamens nutzbar sein, sonst müsste für jeden Monatstag die Tätigkeiten neu eingetragen werden.
Die o,g, Erweiterung ist für ein Programm gedacht, das unbegrenzt Jahre, inclusive Schaltjahr, Monate sowie Wochentage berücksichtigt.
Mit freundlichen Grüßen
Hubert Schostek
Sehr geehrter Herr Weiß,
wie kann die dynamisch anpasssender Bereichsnamen für Arbeitskalender anwenden.
Der Arbeitskalender enthält monatliche Tätigkeiten, dabei einige den bestimmten Wochentagen zugeordnet sind.
Bei Monatswechsel ändern sich die Wochentage die Tabelle bleibt unverändert übernommen und dadurch die bestimmte Tätigkeiten in den falschen Wochentag geraten.
Das Programm berücksichtigt angegebenen Jahr inclusive Schaltjahr, Monat und Wochentag.
Mit freundlichen Grüßen
Hubert Schostek
Hallo Herr Schostek,
ein Tipp ist hier etwas schwierig, da ich den genauen Aufbau und die Funktionsweise Ihres Arbeitskalenders nicht kenne. Ich fasse kurz zusammen, wie ich Sie verstanden habe:
– Bestimmte Tätigkeiten sollen immer einem bestimmten Wochentag zugeordnet werden.
– Da der Aufbau des Kalenders statisch ist, stehen nach einem Monatswechsel die Wochentage nicht immer an der gleichen Stelle. Wenn die Tage spaltenweise angeordnet sind, wandert der Montag in eine andere Spalte, bei zeilenweiser Anordnung wandert der Montag in eine andere Zeile. Richtig?
In diesem Fall könnten Sie also per WOCHENTAG-Funktion prüfen, wie der Wochentag in der jeweiligen Zelle lautet und davon abhängig z.B. mit einer WENN-Funktion die fix zugeordneten Tätigkeiten eintragen lassen. Also ungefähr so:
=WENN(WOCHENTAG(A2;2)=1;“Montagsaktivität;WENN(WOCHENTAG(A2;2)=2;“Dienstagsaktivität“;““))
Dynamische Bereichsnamen sind hier nicht zwingend notwendig.
Schöne Grüße,
Martin
Guten Tag Martin,
Erstmal herzlichen Dank für die wertvollen Inputs hier. Weiter so!
Das klappt ganz gut, was ich als störend empfinde ist der Umstand, jedes Mal bei Einfügen einer neuen Zeile im Namensmanager die Formel anpassen zu müssen, da der Bezug zur ersten Zeile nicht mehr stimmt. Gibt es da eine Umgehungsmöglichkeit?
Beste Grüsse,
Dominik
Hallo Dominik,
die Idee bei den dynamischen Bereich ist es ja gerade, dass die Formel nicht ständig angepasst werden muss. Im beschriebenen Beispiel ist das auch der Fall: Wenn neue Zeilen in der Ausgabenliste dazukommen, passen die Formeln trotzdem. Hast du die Bezüge im Namensmanager genauso eingegeben, wie im Beispiel?
Schöne Grüße,
Martin
Hallo,
danke für den Tipp und die vielen, vielen anderen, durch die ich mich momentan nach und nach hindurchlese. Superinteressante Sachen dabei!!
Zum hiesigen Artikel: Ich habe eine ähnliche Ausgabendatei und habe die Bereiche einfach dummplump bis zur Zeile 1000 hinunter fix benannt; das ist ungefähr die Zahl meiner Ausgaben pro Jahr. Ich nehme aus verschiedenen Gründen (ich teile u.a. die Summen auch noch nach Monaten auf) nicht ZÄHLENWENN, sondern SUMMENPRODUKT für die Auswertung, und diese Funktion stört sich überhaupt nicht an den vielen leeren Zeilen.
Gibt es im gegebenen Beispiel Gründe mit dem dynamischen Bereich zu arbeiten (statt mit: Bereich „Ausgaben“ erstreckt sich von $D$4 bis $D$1000) außer den Grund, meinem armen Computer unnötige Arbeit zu ersparen?
Lieben Gruß
Michael
Hallo Michael,
man kann natürlich immer einen bestimmten größeren statischen Bereich in seinen Formeln berücksichtigen (z.B. bis Zeile 1000). Aber irgendwann wird vielleicht auch diese Zeile überschritten und dann stimmen die Formeln einfach nicht mehr. In kleinen und überschaubaren Kalkulationen mag das noch auffallen, aber in komplexeren Anwendungen kann eine statische Angabe fatal, da man die fehlerhaften Ergebnisse oftmals nicht oder viel zu spät erkennt.
Schöne Grüße,
Martin
Hallo.
Die Website ist super hilfreich 🙂 den dynamischen Bereichsnamen könnte ich sehr gut gebrauchen nur leider tu ich mich sehr schwer die Formel für mich richtig zu definieren. Meine Tabelle hat eine feste Spaltenanzahl nur die Zeilen werden hinzugefügt/erweitert.
Meine Tabelle geht also z.B. Von A2:E15. Wie gestalte ich die Formen um die Tabelle dynamisch auf A2:E16 anzupassen?
in Ihrer Beschreibung ist der Bereich ja nur über eine Spalte definiert. Können Sie mir die Vorgehensweise beschreiben?
Sie haben den Namen für den Bereich D4:D12 dynamisch umgewandelt zu
=$D$4:BEREICH.VERSCHIEBEN($D$4;VERGLEICH(„“;$B:$B;-1)-4;0)
Können Sie mir die Formel für meine Tabelle B7:C12 dynamisch umwandeln, sodass die 13te Teile automatisch mit eingeschlossen wird?
ich möchte aus den Daten eine Pivot Tabelle in einer anderen Datei erstellen und möchte den dynamischen Bereichsnamen verwenden um die Pivot Tabelle automatisch mit den neu hinzugefügten Zeilen zu füttern.
Hallo Anna,
im Zusammenhang mit Pivot-Tabellen empfehle ich anstelle von dynamischen Bereichsnamen per Formel stattdessen formatierte Tabellen zu verwenden (Menü Start | Als Tabelle formatieren). Neben vieler anderer praktischer Eigenschaften ist eine solche Tabelle von Haus aus dynamisch angelegt. Wenn dann daraus eine Pivot-Tabelle erstellt wird, greift diese immer auf den jeweils aktuellen Tabellenbereich zu.
Schöne Grüße,
Martin
Hallo,
erstmal ein sehr schöner Beitrag=)
Ich habe allerdings noch eine Frage. Kann man Zellnamen so einstellen, dass wenn ein (bestimmter) Inhalt in der Zelle steht, dass dann die Zelle einen bestimmten Namen bekommt? Am besten wäre es, wenn das ganze kopierbar wäre. Ich brauche das für einen Kalender in dem bei nicht-Schul-Wochen und an Montagen nichts stehen soll.
Gruß Carsten
Hallo Carsten,
dankeschön. Die Zellnamen lassen sich leider nicht dynamisch ändern, eine Dynamik ist nur für den Zellbezug möglich.
Schöne Grüße,
Martin
Hallo Martin,
ich habe 2 als Tabellen formatierte Tabellen mit den Namen tab1 (=1. Tabelle) und tab2 (=2. Tabelle).
In einer weiteren Zelle habe ich ein Dropdown, in dem ich den Tabellennamen auswählen kann.
Nun möchte ich eine Pivottabelle erstellen, die als Datenquelle entweder tab1 oder tab2 hat, je nachdem, welchen Namen ich in dem Dropdownfeld auswähle.
Weißt du, wie so etwas geht? Eine Lösung dazu würde mir wirklich sehr helfen.
Viele Grüße
Nadine
Hallo Nadine,
Du musst einen Namen definieren (Formeln | Namen definieren). Nenne ihn beispielsweise „Datenquelle“. Angenommen, dein Dropdownfeld steht in Tabelle1!L1, dann gibst du im Namensmanager im Feld „Bezieht sich auf“ folgende Formel ein:
=INDIREKT(Tabelle1!$L$1)
Jetzt erstellst du eine neue Pivot-Tabelle (oder nimmst die vorhandene) und gibst als Datenquelle den eben definierten Namen „Datenquelle“. Du musst nur nach jeder Änderung im Dropdown-Feld auch die Pivot-Tabelle aktualisieren.
Das sollte es gewesen sein.
Schöne Grüße,
Martin
Hallo Martin,
super, vielen lieben Dank für deine Hilfe!!!
Es funktioniert prima 🙂
Liebe Grüße
Nadine
Hallo,
ich bin mir nicht ganz sicher ob ich damit hier richtig bin.
Stehe vor folgendem Problem. Ich habe eine Tabelle wo ich laufend Materialausgänge verbuche nach Datum (können auch mehrere Einträge pro Tag sein). Am Ende der Woche sollte dann die Summe dieser Woche automatisch erstellt werden (idealerweise in der Spalte wo ich über Dropdownliste „Füllung“ auswähle). Jetzt habe ich das Problem, dass ich nicht klar komme, wie ich den Summenbereich so variabel gestalten kann, dass Excel von alleine versteht, wann die letzte „Füllung“ war. Dann sollte von (exklusive) letzter Füllung bis zur Spalte des letzten Eintrags für einen Warenausgang automatisch die Summe gebildet werden. Somit hätte ich dann die Liste der notwendigen Artikel, welche ich nachfüllen muss.
Wäre sehr froh über Hilfe
Danke und Gruß
Rene
Hallo Rene,
ich fürchte, dieses Problem wirst du mit einer Formel nicht lösen können. Jedenfalls fällt mir keine passende Lösung dazu ein. Zumal ja erschwerende hinzukommt, dass du offensichtlich ja auch noch das jeweilige Ende der Woche irgendwie berücksichtigen willst.
Daher vermute ich, dass du um ein paar Hilfsspalten nicht herumkommen wirst, in denen die die laufenden Abgänge summierst, abhängig davon, ob in der Zelle daneben eben „Füllung“ steht oder nicht. Aber das ist jetzt ziemlich ins Blaue gesprochen. Vielleicht hat ja ein anderer Leser eine Lösung parat.
Schöne Grüße,
Martin
Hallo Martin,
dein Artikel bzw. Beschreibung Dynamische Bereichsnamen fand ich interessant und anregend. Für Deine Beispieltabelle habe ich noch eine andere Lösung.
1. ich Füge eine Tabelle (über Einfügen Tabelle, mit der Auswahl Tabelle hat Überschrift) im Bereich A3:D12 ein.
2. im weiteren lege ich eine Kleine Tabelle im Bereich F2:G3 an mit
3. In F4 lege über Datenüberprüfung eine Liste mit den Bereich B4:B12 an.
4. In G4 trage ich die Formel =SUMMEWENN(B4:B10;F4;D4:D10) ein
5. Ab jetzt kann ich die Tabelle mit den Kategorien fortlaufend ausfüllen und über die Liste das Ergebnis mir anzeigen lassen.
Was hältst du von dieser Lösung?
LG Gerold
Hallo Gerold,
guter Vorschlag! Formatierte Tabellen sind immer eine gute Lösung, den sie bringen die hier benötigte Dynamik bereits mit.
Schöne Grüße,
Martin
Hallo,
ich möcht eine Monats-Statistik erstellen über ein Jahr.
Nun gibt es Monat Feb aber zwei möglichkeiten 28 oder 29 Tage.
Ausgang:
In Zeile 4 stehen die Tage des ganzen Jahres als Datum, also vom 01.01.Jahr bis 31.13.Jahr ab der Spalte B
In der Spalte A stehen ZimmerNr
Ab B5 werden für die FarbNr(einstellig)&VerwendungsNr(zweistellig)&BuchungsNr eingetragen zB.: 24455
die 2 steht für die Farbanzeige auf den Plan, die 44 ist die Verwendung der Buchung und die 55 ist die BuchungsNr
Jetzt mein Problem: die Gesamtsumme der Verwendung kann ich mit =ZählenWenns( BJahr;“?“&WERT(A5)&“*“) auslesen pro Verwendung. BJahr steht für das ganz Jahr&Zimmer Also von B5 bis NA106
Ich wollte jetzt =ZählenWenns(BJahr;“?“&WERT(A5)&“*“;BJahr;MonatsZahl) auslesen – Ergebnis: #WERT
Wie kann ich vielleicht variable MonatsBereiche festlegen? oder anders
Ich würde mich sehr über eine Lösung ohne Makro freuen.
Hallo Michael,
ich habe gerade versucht, dein Beispiel in einer Tabelle nachzubauen. Aber mir erschließt sich noch nicht, wie du jetzt schon die Gesamtsumme der Verwendung berechnest. Du schreibst, in jeder der Zellen in B5:NA106 steht die 5stellige Nummer (also z.B. 24455) und in Spalte A nur eine Zimmernummer (das Format hast du nicht beschrieben, also z.B. 101, 102, 103 usw.?). Dann verstehe ich den Teil „?“&WERT(A5)&“*“ in der Formel nicht.
Aber das nur am Rande.
Einen dynamischen Monatsbereich wirst du in einer Tabelle mit diesem Aufbau vermutlich nicht hinbekommen. Zumindest fällt mir keine Lösung ein. Ich würde stattdessen diese Matrix, die ja jetzt eine Kreuztabelle ist, in eine flache Tabelle transformieren (relativ einfach mit Power Query). Also mit einem Datensatz je Zeile bestehend aus Tag, Zimmer, und dem 5stelligen Schlüssel. Und dann kannst du mit ZÄHLENWENNS etc. direkt die Tage und damit auch die Monate abgreifen.
Aber vielleicht hat ja ein anderer Leser eine bessere Idee.
Schöne Grüße,
Martin
Hallo Martin,
ich habe Deine hier beschriebene Formel verwendet, um den Druckbereich automatisch anzupassen. Das funktioniert auch insoweit, dass der Druckbereich sichtbar an die Anzahl der verwendeten erweitert bzw. verringert wird.
Leider ist Excel das beim Ausdruck selbst aber egal. Hier möchte Excel dann dennoch alle Zeilen ausdrucken – egal ob diese Werte enthalten oder nicht, also leer sind.
Gibt es eine Möglichkeit, Excel zu zwingen, den eigentlich korrekt festgelegten Druckbereich auch tatsächlich zu verwenden und keine leeren Blätter auszudrucken?
Viele Grüße
Tino
Moin Tino,
in der Regel kann man in den Druckereinstellung „leere Seiten überspringen“ o.ä. einstellen.
Best Regards
Hallo Tino,
ich habe das bei mir gerade nochmal getestet und es klappt einwandfrei mit dem Druckbereich. Und bei dir wird in der Druckvorschau auch wirklich nur der relevante Bereich angezeigt? Oder sind dort auch schon zusätzliche Seiten zu sehen? Falls nein, dann tippe ich eher auf irgendein Problem mit den Bezügen in der Formel.
Schöne Grüße,
Martin
war ein Denkfehler meinerseits.
Ich hatte in der Arbeitsmappe zwei Blätter und habe vergessen, den Blattnamen vor dem Namen mit anzugeben.
Excel wußte somit wohl nicht, auf welches Blatt sich der Druckbereich beziehen sollte.
Nachdem ich die Formel um den Blattnamen ergänz habe, funktioniert es auch bei mir einwandfrei.
=Tabelle1!$A$1:BEREICH.VERSCHIEBEN(Tabelle1!$H$1;VERGLEICH(„“;Tabelle1!$B:$B;-1)-1;0)
Liebe Grüße