Excel-TippDie geheimnisvolle Funktion BEREICH.VERSCHIEBEN()

Die Excel-Funktion BEREICH.VERSCHIEBEN() ist ein leistungsstarkes Werkzeug zur dynamischen Manipulation und Verarbeitung von Datenbereichen in Excel. Sie ermöglicht es, einen bestimmten Zellbereich basierend auf einer angegebenen Verschiebungsanzahl und Verschiebungsrichtung zu ändern.

Diese Funktion ist besonders nützlich, wenn Sie Datenanalysen, Berichterstattung oder Dashboards erstellen, bei denen sich der Datenbereich aufgrund von Aktualisierungen oder neuen Datenquellen ändert.

Die Funktion besitzt die folgende Syntax:

BEREICH.VERSCHIEBEN(Bezug;Zeilenverschiebung;Spaltenverschiebung;[Höhe];[Breite])

• Bezug: Der Ausgangspunkt, also der Bezug oder Bereich, den Sie verschieben möchten.
• Zeilenverschiebung: die Anzahl der Zeilen, um die der Bezug verschoben werden soll. Positive Werte verschieben nach unten, negative Werte nach oben.
• Spaltenverschiebung: die Anzahl der Spalten, um die der Bezug verschoben werden soll. Positive Werte verschieben nach rechts, negative Werte nach links.
• Höhe (optional): die Anzahl der Zeilen im neuen Bereich. Wenn dieser Wert nicht angegeben wird, behält der neue Bereich die gleiche Höhe wie der ursprüngliche Bereich.
• Breite (optional): die Anzahl der Spalten im neuen Bereich. Wenn dieser Wert nicht angegeben wird, behält der neue Bereich die gleiche Breite wie der ursprüngliche Bereich.

Was diese Einstellungen bedeuten und bewirken, lässt sich an einem Beispiel zeigen.

Angenommen, Sie haben eine Tabelle mit Umsatzdaten, aufgegliedert nach Kalenderjahr und Quartalen.

Sie wollen nun die Summe eines bestimmten Kalenderjahres berechnen, indem Sie die entsprechenden Quartale des betreffenden Jahres summieren. Dabei wollen Sie variabel angeben, für welches Jahr die entsprechenden Quartalsumsätze summiert werden sollen.

Die Funktion BEREICH.VERSCHIEBEN() ermöglicht es Ihnen, den Datenbereich für die Funktion SUMME() automatisch anzupassen, sodass Sie die Summe des entsprechenden Kalenderjahres berechnen können.

Schauen Sie sich das Beispiel in der folgenden Abbildung an.

In der Zelle A2 wird hier das Kalenderjahr erfasst, für das die Summe der Quartale berechnet werden soll.

Die SUMMEN-Formel steht hierbei in der Zelle C2:

=SUMME(BEREICH.VERSCHIEBEN(A5;A2-A5;1;1;4))

Die Funktion BEREICH.VERSCHIEBEN() teilt der Funktion SUMME() hierbei den Bereich dynamisch mit, der summiert werden soll. Schauen wir uns die Funktionsweise an diesem Beispiel genauer an:

• Bezug: der Ausgangspunkt, also der Zellbereich, den Sie verschieben möchten. Das ist im Beispiel die Zelle A5. Also die erste Jahreszahl der Umsatzaufstellung. Von diesem Bezug aus wird nun verschoben.
• Zeilenverschiebung: die Anzahl der Zeilen, um die der Bezug oder Ausgangspunkt (A5) verschoben werden soll. Im Beispiel steht hier die Formel: A2-A5.
  In der Zelle A2 steht die Jahreszahl, für die der Umsatz summiert werden soll (2010). In A5 steht die erste Jahreszahl der Aufstellung. Die Formel A2-A5 bewirkt die Rechenoperation 2010-2000=10. Dies bedeutet, dass der Startpunkt A5 um 10 Zeilen nach unten verschoben wird; also zur Zelle A15.
• Spaltenverschiebung: Die Anzahl der Spalten, um die der Bezug verschoben werden soll. Hier steht der Wert 1. Der Bezug A15 verschiebt sich jetzt zur Zelle B15.
• Höhe (optional): die Anzahl der Zeilen im neuen Bereich. Der Eintrag 1 bewirkt, dass die Anzahl der Zeilen bei einer Zeile bleibt. Es ist daher weiterhin die Zelle B15 das aktuelle Ergebnis des Bereiches.
• Breite (optional): die Anzahl der Spalten im neuen Bereich. Hier ist der Wert 4 erfasst. Dies bedeutet, dass der Bereich B15:B15 um 4 Spalten nach rechts erweitert wird. Das Ergebnis der Formel BEREICH.VERSCHIEBEN() lautet nun B15:E15.
• Dieser Bereich wird jetzt an die Funktion SUMME() übergeben, die dann die Werte dieser vier Zellen (die vier Quartale des Jahres 2010) summiert.

Die Funktion BEREICH.VERSCHIEBEN() bietet Flexibilität und Präzision bei der Verarbeitung von Datenbereichen in Excel und ist ein unverzichtbares Werkzeug für die Erstellung von dynamischen Berichten und Analysen.

Die Möglichkeiten für die Anpassung von Datenbereichen sind nahezu unbegrenzt. Sie müssen nur die Funktion entsprechend Ihren Anforderungen anpassen. Entscheidend dabei ist zu ermitteln,

• wie weit der Bereich verschoben werden soll; die Werte für die Zeilen- und Spaltenverschiebung
• wie groß der (neue) Bereich sein soll; die Höhe und Breite

Wollen Sie das Beispiel erweitern, indem Sie neben der Jahreszahl auch die Quartalszahl erfassen und so den Umsatz eines bestimmten Quartals aus einem Kalenderjahr zurückgeben, dann müssen Sie die obige Formel wie folgt anpassen. Dabei steht der gewünschte Quartalswert in Zelle B2.

=SUMME(BEREICH.VERSCHIEBEN(A5;A2-A5;B2;1;1))

Das Ergebnis zeigt die folgende Abbildung.