Excel: Bereich verschieben - mit Funktion ganz einfach

Johannes Kneussel

Mit der Excel-Funktion BEREICH.VERSCHIEBEN können Berechnungsbereiche ganz einfach verschoben werden oder auf andere Art und Weise angepasst werden. Wir zeigen euch anhand von einfachen Beispielen, wie es funktioniert.

Excel: Bereich verschieben - mit Funktion ganz einfach

Excel: Bereich verschieben – Funktion der Funktion

BEREICH.VERSCHIEBEN ist eine Funktion, die in Excel nicht unbedingt häufig benutzt wird. Dabei ist sie recht mächtig, kann aber auch auf den ersten Blick ziemlich komplex wirken. Die Funktionsformel sieht folgendermaßen aus:

  • BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten;;)
  • Die ersten drei Argumente müssen dabei immer angegeben werden, zu den letzten beiden kommen wir weiter unten.

Ein Beispiel: 

  • Wir haben eine Spalte mit Zahlenwerten und eine weitere Zelle mit der BEREICH.VERSCHIEBEN-Funktion.
  • In dieser geben wir jetzt als Bezug E4 an, was in unserem Beispiel dem Wert 1 entspricht.
  • Hier kann alternativ auch ein ganzer Zellbereich ausgewählt werden. 
    excel-bereich-verschieben
  • Die Zahl 5, die auch durch eine Zelle mit einem bestimmten Wert ersetzt werden kann, besagt, dass der anzuzeigende bzw. zu berechnende Wert um fünf Zeilen nach unten verschoben ist, also nicht mehr E4, sondern E9, in unserem Fall wäre das 6.
  • Mit dem dritten Argument können nun noch die Spalten verschoben werden. Bei einer 0 wird die abgefragte Spalte nicht verschoben.

BEREICH.VERSCHIEBEN

Noch ein Beispiel: 

Nehmen wir mal an, unsere Mietnebenkosten variieren jeden Monat und wir wollen nun, wenn wir die Zahl des jeweiligen Monats angeben, die Summe für die Gesamtkosten der Nebenkosten berechnen. Oder wie viel Stromkosten beispielsweise im Jahr anfallen. Auch das können wir mit BEREICH.VERSCHIEBEN erledigen.

excel-bereich-verschieben

  • Ihr seht zunächst unsere Tabelle für die Mietnebenkosten für die Monate Januar bis März und die einzelnen Kostenposten.
  • Darunter kann man einmal angeben, welcher Posten von 1 bis 6 (Strom bis Telefon) berechnet werden soll, rechts davon wird die Summe für diesen Posten für diese drei Monate angezeigt: =SUMME(BEREICH.VERSCHIEBEN(F7:H7;E14-1;0))
    excel-bereich-verschieben2

  • Wiederum darunter könnt ihr einen Monat auswählen und euch dann rechts davon die Gesamtkosten für diesen Monat berechnen lassen: =SUMME(BEREICH.VERSCHIEBEN(F7:F12;0;E16-1))
    excel-bereich-verschieben3
  • Schauen wir uns die erste Funktion noch einmal genauer an. Zunächst geben wir den Bereich an, der summiert werden soll, also F7:H7, was den Stromausgaben von Januar bis März entspricht.
  • Danach folgt die Angabe der jeweiligen Zeile, die man berechnen möchte, diese geben wir unter E14 an. -1 deswegen, weil sonst bei der Angabe von 1 in E14 tatsächlich Zeile E15 berechnet werden würde (E14+1), das wäre dann Wasser, was aber in unserer Tabelle Zeile 2 ist. Es folgt die 0, weil wir die Spalte nicht anpassen wollen.
  • Ähnlich erfolgt die Berechnung der Gesamtnebenkosten für den jeweiligen Monat. Diesmal passen wir allerdings den Bezug der Spalte an, den wir in E16 eintragen können, auch hier folgt wieder -1, damit wir nicht in der falschen Spalte landen.

Höhe und Breite eines Bereiches ändern

Das Ganze ist bereits schon recht kompliziert, man kann aber noch zwei weitere, optionale Argumente an unsere Formel hängen. Da wäre einmal die Höhe und einmal die Breite.

excel-bereich-verschieben4

  • Wollen wir mehr als eine Zeile für unsere drei Monate berechnen, beispielsweise Strom und Wasser zusammen, können wir die Formel wie folgt erweitern: =SUMME(BEREICH.VERSCHIEBEN(F7:H7;E14-1;0;2))
  • Herauskommen tut der Wert 9698 für Strom und Wasser von Januar bis März.
  • Das liegt daran, dass wir der Formel das Argument 2 angehängt haben, welches die Höhe auf 2 Zeilen erhöht.
  • Ähnlich funktioniert es auch mit der Breite: =SUMME(BEREICH.VERSCHIEBEN(F7:F12;0;E16-1;;2))
  • Damit können wir die Gesamtausgaben für zwei Monate berechnen, in unserem Fall wären es Januar und Februar.
  • Beachtet bitte die zwei Semikolons, dazwischen würde ein Wert für die Höhe stehen. Da wir diese aber nicht verändern möchten, bleibt der Bereich leer, es folgt ein zweites Semikolon und daraufhin die Angabe der Breite.

Weitere Excel-Tutorials auf GIGA

Weitere Themen: Microsoft Office (für iPad), Microsoft Office Compatibility Pack, Microsoft Office 2007, Microsoft Office 2016 Mac, Microsoft Office 365 Home, Microsoft Excel 2013, Microsoft Excel für iPad, Microsoft Office Mobile für Android, Maxdome, Microsoft

Neue Artikel von GIGA WINDOWS

  • Adobe Acrobat Reader DC

    Adobe Acrobat Reader DC

    Mit dem Adobe Acrobat Reader DC Download holt ihr euch das Standardprogramm zum Anzeigen, Drucken und Kommentieren von PDF-Dokumenten auf den Rechner,... mehr

  • NoScript für Firefox

    NoScript für Firefox

    NoScript ist eine nützliche Erweiterung für den Firefox, die Javascript, Flash, Silverlight und Java auf Knopfdruck ab- und anschaltet, wobei ihr auch... mehr

  • Q-Dir Portable

    Q-Dir Portable

    Der Q-Dir Portable Download verschafft euch die komfortable Windows Explorer-Alternative in der portablen Ausgabe. mehr