BEREICH.VERSCHIEBEN

"Überlauf" - Formel-Ausgabe mehrerer Werte

Roman Formeln , , , , ,

In früheren Excel-Versionen hat man teilweise trotz korrekter Eingabe einer Formel die Fehlermeldung "#WERT!" erhalten. Dies war beispielsweise bei "=BEREICH.VERSCHIEBEN" der Fall, wenn das Ergebnis der Formel mehrere Werte beinhaltete. Dabei kommt es zu einem "Überlauf", da innerhalb einer Zelle nur ein Wert dargestellt werden kann. Angewendet hat man die erwähnte Formel unter anderem, um dynamische Bereiche zu definieren - damit Pivot-Tabellen oder Drop-Down-Listen (allenfalls nach dem Aktualisieren) die angepassten Datenquellen berücksichtigen. Dabei wird mit dem Namensmanager gearbeitet, wie bereits auf Excelblog.ch im Beitrag "Pivot-Datenquelle automatisch erweitern" sowie "Bereich eines Namens automatisch erweitern" erläutert wurde.

In diesem Beitrag gehen wir erneut auf die Formel "BEREICH.VERSCHIEBEN" ein und stellen damit innerhalb einer Excel-Tabelle eine dynamische Liste erneut dar - ohne den Namensmanager zu verwenden. Dabei simulieren wir erneut den angesprochenen "Überlauf" und zeigen, wie der Ergebnisbereich neuerdings in Excel dargestellt wird. Um dieses Beispiel zu erläutern, verwenden wir nachfolgende Länder-Liste auf dem Tabellenblatt "Parameter":

Laenderliste_Ausgangslage

Eine Drop-Down-Liste könnte ganz einfach auf diesen Bereich verweisen und nur diese Werte wären zulässig. Falls man jedoch weitere Länder in die Liste eintragen sollte, bleibt der Bezug der Drop-Down-Liste statisch. Weitere Erläuterungen zu diesem dynamischen Bereich findest Du im bereits verlinkten Beitrag "Bereich eines Namens automatisch erweitern". Die Formel, die wir verwenden, um den Bereich in unserem Beispiel dynamisch zu gestalten, lautet wie folgt:

=BEREICH.VERSCHIEBEN(Parameter!$A$2;;;ANZAHL2(Parameter!$A:$A)-1;)

Ausgehend von der Zelle "A2" (erstes Land) wird gemessen, wie viele Einträge in der Spalte A vorhanden sind (abzüglich 1 für die Überschrift). Dies ergibt unseren dynamischen Bereich, welchen wir in einer Drop-Down-Liste verwenden können:

Laenderliste_DropDown

Falls man obige Formel jedoch nicht im Namen eingibt, sondern wie ansonsten üblich in einer einfachen Zelle (im Screenshot unten Zelle "B2"), dann erhalten wir folgendes Resultat:

Laenderliste_BereichVerschieben

Die Liste wird exakt gleich dargestellt und die Werte werden in den Zellen unterhalb von B2 weitergeführt - die Zelle B2 ist "überlaufen". Wenn Du in die Zelle B2 klickst, siehst Du die Formel in der Bearbeitungsleiste normal angezeigt. Klickst Du jedoch beispielsweise in die Zelle "B3", dann ist die Formel zwar angezeigt, aber ausgegraut. Du kannst sie da auch nicht bearbeiten - nur eben in Zelle "B2". Falls Du einen Wert in den "Überlaufbereich" einfügst (z.B. in Zelle "B3"), dann erhältst Du neu die Fehlermeldung "#ÜBERLAUF!". Dies ist ein Hinweis, dass der Überlaufbereich nicht leer ist. Über die Fehlermeldung kann man auch direkt in die "blockierende Zelle" navigieren - dies kann vor allem bei grösseren Listen hilfreich sein:

Laenderliste_Ueberlauf

=SORTIEREN - ein weiterer Anwendungsfall

Eine weitere Formel, die nun ein interessantes Ergebnis mit Überlauf liefert, ist "=SORTIEREN". Dies kann erneut mit der obigen Länder-Liste illustriert werden. Angenommen die Länder sind einfach über die Zeit in die Liste eingetragen worden, ohne einer Reihenfolge oder Sortierung zu unterliegen. Mit der Formel "=SORTIEREN" kann einfach die gesamte Liste markiert werden, um die Länder im Zielbereich alphabetisch zu sortieren - dafür benötigt es in der Formel keine weiteren Angabe, der Bereich (z.B. "A2:A11") genügt! Die Formel wird in einer Zelle eingetragen, sämtliche Länder werden mithilfe des Überlaufs als Bereich ausgegeben. Dies ist das Resultat, nebeneinander dargestellt:

Laenderliste_SORTIEREN

Pivot-Datenquelle automatisch erweitern

Roman Formeln, Pivot , , , , , , , , , , ,

Pivot-Tabellen aggregieren innert kürzester Zeit immense Datenmengen. Sobald man der Datenquelle weitere Informationen anfügt, wird dies standardmässig nicht direkt erkannt - der neue Bereich ist jeweils manuell anzupassen:

pivotdatenquelleaendern

Es besteht jedoch die Möglichkeit, die Datenquelle automatisch erweitern zu lassen. Dies funktioniert im Grunde genommen, wie wir das bereits in diesem Beitrag hier mittels Namensgebung für Bereiche behandelt haben. Unserer Pivot-Datenquelle teilen wir auch hier zuerst einen Namen zu: Markiere die gesamte Datenquelle und schreibe ins Namensfeld (links neben der Bearbeitungsleiste) Deinen gewünschten Namen, bspw. "Datenbasis":

pivotdatenquellenamensfeld

Gehst Du nun via "Einfügen" → "PivotTable" kannst Du direkt bei "Tabelle/Bereich:" den definierten Namen ("Datenbasis") eingeben:

(Beachte: Unsere Pivot wird in einem neuen Tabellenblatt sein, nicht im selben wie die Datenbasis.)

Nun ist aber noch nicht konfiguriert, dass sich der Bereich automatisch erweitern soll, sobald weitere Daten angefügt werden (weitere Zeilen, weitere Spalten). Hier kommt die Formel "BEREICH.VERSCHIEBEN" zum Einsatz. Gehe via "Formeln" auf "Namensmanager" und bearbeite Deinen bereits angelegten Namen. In der Maske gibst Du bei "Bezieht sich auf:" die nachfolgende Formel ein:

=BEREICH.VERSCHIEBEN(Datenbasis!$A$1;;;ANZAHL2(Datenbasis!$A:$A);ANZAHL2(Datenbasis!$1:$1))

Auf dem Tabellenblatt mit ebenfalls dem Namen "Datenbasis", beginnend bei Zelle "A1" wird einerseits nach unten (Spalte A:A) gezählt, wie viele Einträge vorhanden sind. Dasselbe passiert auf der Zeile 1.

pivotdatenquellenamensmanager

Der Bereich wird entsprechend daraufhin erweitert, sobald neue Einträge angefügt werden. (Beachte, dass die Pivot-Tabelle noch via Rechtsklick → "Aktualisieren" aufzufrischen ist.)

Bereich eines Namens automatisch erweitern

Roman Formeln , , , , , , , , ,

Wir haben uns bereits den Namensmanager und die Dropdown-Listen angeschaut. Wenn Du zu der Kriterienliste weitere Begriffe hinzufügst, wird der Bereich Deines Namens allerdings nicht automatisch erweitert - Du musst via Namensmanager die letzte Zelle manuell angeben, respektive den effektiven Bereich neu selektieren. Mit der Formel "BEREICH.VERSCHIEBEN" kann dieser Schritt automatisiert werden. Excel sagt uns folgendes über diese Formel:

Gibt einen Bezug zurück, der gegenüber dem angegebenen Bezug versetzt ist.

Man kann damit nicht nur den Bereich "A1:A3" nach "A3:A5" verschieben, sondern auch auf "A1:A5" erweitern - und genau das wollen wir in diesem Beitrag thematisieren. Schauen wir uns einmal die Syntax der Formel an:

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

Wir beginnen also mit einem Bezug - einer einzelnen Zelle (A1). "Zeilen" und "Spalten" stehen jeweils für eine Verschiebung um X Zellen nach unten respektive nach rechts. "[Höhe]" und "[Breite]" hingegen lässt uns einen Bereich nach unten oder nach rechts erweitern. Mithilfe der Formel "ANZAHL2" als "Höhe" kannst Du zählen, wie viele Argumente in einer Spalte stehen und so die Anzahl Zellen Deines Bereiches ermitteln. Die Formel lautet demnach:

=BEREICH.VERSCHIEBEN($A$1;;;ANZAHL2(A:A);)

Wir setzen A1 absolut ($), weil diese Formel als Namen definiert werden soll. Der Name ist in einem Tabellenblatt oder einer gesamten Excel-Arbeitsmappe vorhanden. Ohne die Dollarzeichen würde sich deshalb in anderen Zellen die Referenz automatisch verschoben werden und nicht an A1 festhalten. Beachte anschliessend die nacheinanderfolgenden Semikolon (;) - wir geben weder bei "Zeilen" noch bei "Spalten" etwas ein - die ANZAHL2-Formel ermittelt die Höhe unseres Bereiches anhand sämtlicher nichtleerer Zellen in Spalte A.

Die obengenannte Formel musst Du bei Deinem Namen als Bezug eingeben, um die Kriterien nachher in Deiner Dropdown-Liste aufzuführen (siehe "Namensmanager" und "Dropdown-Liste"). Klicke hierfür auf den Menüpunkt "Formeln" → "Namen definieren". Der Bezug "$A$1" wird Dir automatisch mit dem Namen des entsprechenden Tabellenblattes ergänzt (bspw. Tabelle1!$A$1). Bitte beachte deshalb folgendes, wenn Du auf andere Tabellenblätter Bezug nehmen musst: Falls Du Deine Kriterien zum Beispiel im separaten Tabellenblatt "Parameter" hast, heissen Deine Bezüge "Parameter!$A$1" und "Parameter!A:A".

Wenn wir nun ein neues Kriterium zu unserer Liste hinzufügen, wird dies automatisch zum Namen und folglich in die Dropdown-Liste mitaufgenommen.