Sortieren und Filtern in ExcelTrim-Bezüge in Excel-Formeln verwenden

Was sind Trim-Bezüge?

Beispiel: Eine monatliche Aufstellung enthält einmal 18, im nächsten Monat 25 Datensätze. Die Auswertung soll in beiden Fällen genau die vorhandenen Daten erfassen, ohne dass jemand die Formel jeden Monat anpassen muss.

Dafür eignen sich Trim-Bezüge: Ein zusätzlicher Punkt am Doppelpunkt eines Bereichsbezugs legt fest, welche leeren Ränder Excel abschneidet. Die Quelldaten bleiben dabei unverändert.

Wenn ein Trim-Bezug eingesetzt wird, erkennen Sie dies an der Schreibweise der Bereichsangabe. Sie sieht beispielsweise so aus: A2:.C26. Achten Sie auf den Punkt nach dem Doppelpunkt.

Dieselbe Aufgabe erfüllt die Funktion ABSCHNBEREICH, im englischen Excel TRIMRANGE. Sie erhält den Bereich als erstes Argument und legt über zwei weitere Argumente fest, ob führende, nachfolgende oder beide leeren Ränder entfernt werden, und zwar getrennt für Zeilen und Spalten.

Trim-Bezüge sind die Kurzschreibweise dafür: Der Punkt steht direkt im Bezug, eine eigene Funktion ist nicht nötig. Dafür behandeln sie Zeilen und Spalten immer gleich.

Die drei Schreibweisen für Trim-Bezüge

Ein gewöhnlicher Bereichsbezug enthält einen Doppelpunkt. Bei einem Trim-Bezug kommt davor, danach oder auf beiden Seiten ein Punkt hinzu. Jede Variante entspricht einer ABSCHNBEREICH-Formel.

Der Punkt steht also auf der Seite, an der gekürzt wird. Das gilt für Zeilen und Spalten gleichermaßen: A1:.E100 heißt nicht nur „bis zur letzten gefüllten Zeile“, sondern entfernt auch leere Spalten am rechten Rand. Die Schreibweise funktioniert ebenso mit ganzen Spalten oder Zeilen, etwa A:.A.

Lücken innerhalb des verbleibenden Bereichs bleiben erhalten. Eine leere Zeile zwischen zwei Aufträgen oder eine fehlende Angabe in einer Datenzeile entfernt ein Trim-Bezug nicht.

Die folgende Abbildung zeigt die Wirkung an einem Bereich A1:E10, in dem nur B3:D7 Daten enthält.

Beachten Sie: Über die angegebene Grenze hinaus wächst der Bezug nicht: Ein Bezug bis Zeile 1000 erfasst keinen Auftrag in Zeile 1001. Die Reserve sollte deshalb großzügig ausfallen, alternativ lassen sich ganze Spalten verwenden.

Warum feste Formelbereiche an Grenzen stoßen

Ein Bezug wie A1:E10 umfasst genau diesen Bereich. Kommen darunter weitere Daten hinzu, bleiben sie unberücksichtigt.

Wer vorsorglich einen größeren Bereich wie A1:E1000 angibt, erfasst zwar neue Einträge bis zu dieser Grenze, holt sich aber zunächst viele leere Zellen in die Formel.

Diese Reservezeilen stören, sobald der Bereich ausgegeben oder weiterverarbeitet wird. Führt man etwa mehrere Listen samt Reserve mit VSTAPELN zusammen, erscheinen die leeren Zeilen im Ergebnis als Nullwerte.

Bisher half man sich mit benannten Bereichen, die über BEREICH.VERSCHIEBEN oder INDEX mitwachsen. Das ist umständlich, und BEREICH.VERSCHIEBEN ist zudem volatil, wird also bei jeder Änderung in der Mappe neu berechnet. In großen Arbeitsmappen kann das spürbar bremsen.

Ein Trim-Bezug erledigt die Aufgabe direkt in der Formel. Der Ausgangsbereich darf großzügig bemessen sein, Excel schneidet die leeren Ränder selbst ab.

Grenzen und typische Fehler beim Einsatz von Trim-Bezügen

Scheinbar leere Zellen

Für Excel ist ein Leerzeichen Inhalt. Schon ein einziges unsichtbares Leerzeichen weit unterhalb der Liste vergrößert den gekürzten Bereich.

Steht etwa in Zelle C100 ein Leerzeichen, reicht der Bereich bis Zeile 100. Die sortierte Ausgabe umfasst 99 Zeilen, davon 91 ohne Daten. Dasselbe gilt für Formeln, die einen Leerstring zurückgeben: Die Zelle sieht leer aus, enthält aber eine Formel und bleibt deshalb im Bereich.

Lücken und leere Listen

Fehlt in einer Auftragszeile im Beispiel der Abbildung der Kundenname, bleibt die Zeile trotzdem in der Ausgabe, denn die Lücke liegt im Inneren des Bereichs. Sollen nur Aufträge mit Kundennamen erscheinen, braucht es zusätzlich die Funktion FILTER mit einem passenden Kriterium.

Ist der gesamte Ausgangsbereich leer, bleibt nach dem Kürzen nichts übrig, und der Trim-Bezug liefert den Fehler #BEZUG!. Ein leerer Export ist deshalb als eigener Fall einzuplanen.

Mögliche Fehler, Ursachen und Vorgehen 

Die folgende Übersicht fasst die typischen Fehlerbilder zusammen: