Sortieren und Filtern in ExcelMit ABSCHNBEREICH() Datenbereiche in Excel anpassen

Warum feste Zellbereiche Pflege brauchen

Wer regelmäßig Auftragslisten übernimmt, hat selten zweimal hintereinander gleich viele Datensätze. Mit ABSCHNBEREICH() geben Sie einen großzügigen Eingangsbereich vor und erhalten daraus nur den Teil, der tatsächlich belegt ist.

Angenommen, Ihr Vertrieb liefert im September 20 Aufträge und im Oktober 27. Zeigt Ihre Auswertung fest auf die ersten 20 Datensätze, fehlen im Oktober sieben Aufträge, solange Sie den Bezug nicht von Hand anpassen.

Reservieren Sie stattdessen vorsichtshalber 1.000 Zeilen, rechnet jede Formel, die den ganzen Bereich verarbeitet, auch mit den fast tausend leeren Zeilen.

Die Excel-Funktion ABSCHNBEREICH() verbindet beides:

• Sie geben einen Bereich mit Reserve vor und
• die Funktion liefert daraus nur den belegten Ausschnitt.

Die Reserve bleibt allerdings endlich. Ein Bezug bis Zeile 1000 sieht keine Eingabe in Zeile 1001.

Was die Funktion ABSCHNBEREICH() bewirkt

ABSCHNBEREICH() entfernt vollständig leere Zeilen und Spalten an den äußeren Rändern eines Bereichs oder Arrays. Die Funktion prüft von jedem Rand aus nach innen und stoppt an der ersten Zeile oder Spalte mit Inhalt. Leerzeilen mitten in den Daten bleiben erhalten. 

Die Quelldaten bleiben unverändert. Das Ergebnis erscheint dort, wo die Formel steht. Da es meist mehrere Zeilen und Spalten umfasst, verteilt Excel es auf die benachbarten Zellen. Dieses Verhalten heißt Überlaufen.

Syntax und Argumente

Neben dem Bereich kennt die Funktion zwei optionale Angaben. Sie legen damit fest, an welchen Rändern gekürzt wird: 

=ABSCHNBEREICH(Bereich; [Zeilenmodus]; [Spaltenmodus])

Die eckigen Klammern markieren optionale Argumente und werden nicht eingetippt. Die Namen „Zeilenmodus“ und „Spaltenmodus“ sind hier zur besseren Lesbarkeit gewählt.

Für die optionalen Argumente sind folgende Angaben möglich:

Lassen Sie beide Angaben weg, gilt jeweils der Wert 3. Excel kürzt dann an allen vier Rändern.

Hinweis zum Funktionsnamen: In der deutschen Excel-Version heißt die Funktion ABSCHNBEREICH, im englischen Original TRIMRANGE.

Beispiel: Wie Sie eine Auftragsliste Schritt für Schritt zuschneiden

Schritt 1: Die Arbeitsblätter vorbereiten

Annahme: Die Daten befinden sich in Ihrer Excel-Arbeitsmappe im Tabellenblatt Import. Für die weitere Auswertung und Bearbeitung der Daten wollen Sie die Funktion ABSCHNBEREICH() nutzen. Dazu legen Sie ein Tabellenblatt Auswertung an.

Arbeiten Sie für dieses Beispiel mit normalen Zellbereichen, nicht mit einer formatierten Excel-Tabelle.

Im Blatt Auswertung braucht das Ergebnis rechts und unterhalb der Zelle A1 freien Platz. In diese Zelle tragen Sie die Funktion ABSCHNBEREICH() ein.

Im Blatt Import ist A1:H1000 für die Datenübernahme reserviert. Die Überschriften stehen in B3:F3. Die Zeilen 1 und 2 sowie die Spalten A, G und H bleiben leer, auch Hinweise oder Summen gehören dort nicht hin. So liegt um die Daten ein leerer Rand, den die Funktion später abschneidet.

Schritt 2: Den Bereich ausgeben

Wechseln Sie ins Blatt Auswertung, markieren Sie Zelle A und geben Sie die folgende Formel ein:

=ABSCHNBEREICH(Import!A1:H1000)

Beenden Sie die Eingabe mit der Eingabetaste.

Excel schneidet die leeren Zeilen 1 und 2, die Spalten A, G und H sowie alle Zeilen ab Zeile 24 ab.

Übrig bleibt B3:F23. Das Ergebnis läuft nach A1:E21 über: eine Überschriftenzeile und 20 Aufträge in fünf Spalten. Vergleichen Sie zur Kontrolle die erste und die letzte Auftragsnummer mit dem Blatt Import.

Meist wissen Sie genau, in welchen Spalten und ab welcher Zeile Ihre Daten stehen. Offen ist nur, wo die Liste endet. Dann genügt es, am unteren Rand zu kürzen.

Ersetzen Sie die Formel in A1 durch:

=ABSCHNBEREICH(Import!B3:F1000; 2; 0)

Der Bezug beginnt jetzt direkt bei der Überschrift in Zelle B3 und endet in Spalte F. Zeilenmodus 2 entfernt nur die leeren Zeilen unterhalb der Aufträge. Spaltenmodus 0 lässt die fünf Spalten unangetastet, selbst wenn eine davon einmal leer bleibt.

Das Ergebnis ist dasselbe wie vorher, die Formel zeigt aber auf einen Blick, wo die Daten erwartet werden.

Schritt 3: Einen neuen Auftrag ergänzen

Tragen Sie im Blatt Import in B24:F24 einen weiteren Auftrag ein, ändert sich der angezeigte Abschnitt im Blatt Ausgabe automatisch. Die Ausgabe reicht dann bis Zeile 22.

Beachten Sie: Eine Falle lauert beim Austausch der kompletten Liste. Kopieren Sie eine kürzere neue Liste über eine längere alte, bleiben die überzähligen alten Aufträge darunter stehen und landen weiter in der Ausgabe. Löschen Sie die alten Inhalte deshalb vor jedem Import.

Variante Ganze Spalten statt fester Reserve

Wer die Grenze bei Zeile 1000 nicht ziehen möchte, übergibt ganze Spalten. Bezüge wie B:F galten lange als Bremse, weil manche Funktionen dann über eine Million Zeilen verarbeiten. Microsoft nennt genau dieses Problem als einen Grund für ABSCHNBEREICH.

Die Funktion schneidet die ungenutzten Zeilen ab, sodass nur der belegte Teil weiterverarbeitet wird. Sie können also auch eingeben:

=ABSCHNBEREICH(Import!B:F)

Der Preis dafür: Alles, was in den Spalten B bis F steht, gehört zum Eingang, auch eine Summe oder Notiz weit unterhalb der Aufträge. Für sehr große Arbeitsblätter werden daher trotzdem begrenzte Bereiche empfohlen.

Fehler und unerwartete Ergebnisse überprüfen

Stimmt das Ergebnis nicht, liegt die Ursache meist an einer von zwei Stellen:

• Der Eingangsbereich enthält nicht genau die Daten, die Sie erwarten, oder
• dem Ergebnis fehlt der Platz zum Überlaufen.

Weitere mögliche Fehler oder Probleme:

Das passende Werkzeug wählen

ABSCHNBEREICH ist nicht das einzige Werkzeug für wechselnde Datenmengen. Welches passt, hängt davon ab, was Sie mit den Daten vorhaben: 

Die Werkzeuge schließen sich nicht aus. Wollen Sie im Beispiel nur die Aufträge der Region Nord sehen, hilft das Zuschneiden allein nicht weiter. Dafür braucht es zusätzlich eine Bedingung, etwa mit FILTER.