Laden Sie die Beispielarbeitsmappe herunter
In diesem Tutorial wird gezeigt, wie Sie Zellbezüge mithilfe der OFFSET-Funktion in Excel und Google Sheets erhöhen.
Zellenreferenz erhöhen
Wenn Sie Formeln über Zeilen oder Spalten hinweg kopieren und Ihre Zellbezüge nicht gesperrt sind, werden sie proportional verschoben. Wenn Sie Zellreferenzen mit unterschiedlichen Inkrementen erhöhen möchten, können Sie die OFFSET-Funktion verwenden.
Zellenreferenz in einer Spalte erhöhen
Schauen wir uns zunächst ein Beispiel an. In diesem Beispiel werden Zellenreferenzen mit den Funktionen OFFSET und ROW um jeweils 4 Zeilen erhöht:
| 1 | =OFFSET($B$2,(ZEILE(B2)-1)*4,0) |
Sehen wir uns an, wie diese Formel genau funktioniert.
ROW-Funktion
Wenn Sie nur nützliche Daten in jedem n . habenNS Zeile müssen Sie den Zellbezug um n erhöhen. Wir können die ROW-Funktion verwenden, um die relevanten Zeilennummern zu finden.
| 1 | =(REIHE(B2)-1)*4 |
Im obigen Beispiel benötigen wir nur Daten von allen 4NS Reihe (n = 4). Beginnen Sie mit der Kopfzeile Ihres Datensatzes (hier Zelle B2) und verwenden Sie die obige Formel, um die Zeilennummern für alle 4 . zu findenNS Reihe.
OFFSET-Funktion
Die OFFSET-Funktion gibt den Wert der Zelle zurück, der eine angegebene Anzahl von Zeilen und Spalten aus der angegebenen Zelle ist.
| 1 | =OFFSET($B$2,D3,0) |
Hier beginnen wir beim Dataset-Header (Zelle B2) und erhöhen die Zeile um 4 (Zelle D3). Da wir in der gleichen Spalte bleiben, wird das Argument cols dieser Funktion auf 0 gesetzt. Das $s in „$B$2“ sperrt die Zellreferenz, sodass die Zelle E3 bei der Berechnung des Offsets ebenfalls am Datenkopf beginnt.
Die Kombination dieser Funktionen ergibt unsere ursprüngliche Formel:
| 1 | =OFFSET($B$2,(ZEILE(B2)-1)*4,0) |
Zellenreferenz in einer Zeile erhöhen
Wenn Sie beim Füllen von Formeln über eine Zeile die Zellbezüge um mehr als 1 erhöhen möchten, können Sie die OFFSET-Funktion mit der COLUMN-Funktion kombinieren.
| 1 | =OFFSET($B$2,0,(SPALTE(B2)-1)*4) |
Diese Formel funktioniert genauso wie die obige zum Erhöhen des Zellbezugs in einer Spalte. Der einzige Unterschied besteht darin, dass das Argument rows der Funktion auf Null gesetzt und das Argument cols um 4 erhöht wird.
Zellenreferenz in Google Tabellen erhöhen
Diese Formeln funktionieren in Google Sheets genauso wie in Excel.
