Zellenbezug inkrementieren – Excel & Google Sheets

In diesem Tutorial zeigen wir Ihnen, wie Sie Zellenbezüge mit der Funktion BEREICH.VERSCHIEBEN in Excel und Google Sheets inkrementiert können.

Zellenbezug inkrementieren

Wenn Sie Formeln zeilen- oder spaltenübergreifend kopieren und Ihre Zellenbezüge nicht gesperrt sind, werden sie proportional verschoben. Wenn Sie außerdem Zeilen oder Spalten einfügen, bezieht sich der Zellenbezug auf die ursprüngliche Zeile oder Spalte. Nicht auf die neue angrenzende Zeile/Spalte.

Wenn Sie Zellenbezüge mit einer narrensicheren Methode inkrementieren möchten, können Sie die Funktion BEREICH.VERSCHIEBEN verwenden.

Inkrementieren von Zellenbezügen in einer Spalte

Schauen wir uns zunächst ein Beispiel an. In diesem Beispiel werden die Zellenbezüge mit Hilfe der Funktionen BEREICH.VERSCHIEBEN und ZEILE um jeweils 4 Zeilen inkrementiert:

=BEREICH.VERSCHIEBEN($B$2;(ZEILEN(B2)-1)*4;0)

Schauen wir uns an, wie diese Formel genau funktioniert.

Die ROW-Funktion

Wenn Sie nur in jeder n-ten Zeile nützliche Daten haben, müssen Sie den Zellenbezug um n erhöhen. Wir können die ZEILE-Funktion verwenden, um die relevanten Zeilennummern zu finden.

=(ZEILE(B2)-1)*4

Im obigen Beispiel benötigen wir nur Daten aus jeder 4. Zeile (n = 4). Beginnen Sie am Kopf Ihres Datensatzes (hier Zelle B2) und verwenden Sie die obige Formel, um die Zeilennummern für jede vierte Zeile zu ermitteln.

Die Funktion BEREICH.VERSCHIEBEN

Die Funktion BEREICH.VERSCHIEBEN gibt den Wert der Zelle zurück, die eine bestimmte Anzahl von Zeilen und Spalten von der angegebenen Zelle entfernt ist.

=BEREICH.VERSCHIEBEN($B$2;D3;0)

Hier beginnen wir am Kopf des Datensatzes (Zelle B2) und erhöhen die Zeile um 4 (Zelle D3). Da wir in derselben Spalte bleiben, wird das Argument Spalten dieser Funktion auf 0 gesetzt. Die $-Zeichen in „$B$2“ fixiert den Zellenbezug, so dass die Zelle E3 bei der Berechnung des Versatzes ebenfalls an der Datenkopfzeile beginnt.

Durch die Kombination dieser Funktionen erhalten wir unsere ursprüngliche Formel:

=BEREICH.VERSCHIEBEN($B$2;(ZEILE(B2)-1)*4;0)

Erhöhen der Zellenbezüge in einer Zeile

Wenn Sie beim Ausfüllen von Formeln in einer Zeile die Zellenbezüge um mehr als 1 erhöhen möchten, können Sie die Funktion BEREICH.VERSCHIEBEN mit der Funktion SPALTE kombinieren.

=BEREICH.VERSCHIEBEN($B$2;0;(SPALTE(B2)-1)*4)

Diese Formel funktioniert genauso wie die obige beim Erhöhen des Zellenbezugs in einer Spalte. Der einzige Unterschied besteht darin, dass das Argument Zeilen der Funktion auf Null gesetzt wird und das Argument Spalten um 4 erhöht wird.

Zellenbezug in Google Sheets inkrementieren

Diese Formeln funktionieren in Google Sheets genau so wie in Excel.