Generische Formel
=OFFSET($B$5,(ROW(A1)*n)-1,0)
Zusammenfassung
Um Werte zu kopieren oder Referenzen mit einem Muster wie jeder 3. Zeile, jeder 7. Zeile usw. zu generieren, können Sie eine Formel verwenden, die auf den Funktionen OFFSET und ROW basiert. In dem gezeigten Beispiel lautet die Formel in D5:
=OFFSET($B$5,(ROW(D1)*3)-1,0)
Was in Spalte D kopiert werden kann, um n-te Werte aus Spalte B aufzunehmen.
Erläuterung
In Excel können Sie Formeln, die Zeilen oder Spalten nach einem bestimmten Muster überspringen müssen, nicht einfach kopieren, da sich die Verweise in der Formel automatisch ändern, wenn die Beziehung zwischen der ursprünglichen Quellzelle und der neuen Zielzelle besteht. Mit ein wenig Arbeit ist es jedoch möglich, Formelreferenzen zu erstellen, die bestimmten Mustern folgen.
In diesem Fall verwenden wir die OFFSET-Funktion, mit der Verweise auf andere Zellen oder Zellbereiche basierend auf einem bestimmten Startpunkt oder Ursprung erstellt werden.
In dem gezeigten Beispiel ist die Startzelle B5, die OFFSET als Referenzargument zur Verfügung gestellt und als absolute Referenz festgelegt wurde, damit sie sich beim Kopieren der Formel nicht ändert:
=OFFSET($B$5
Um den korrekten Wert für das Zeilenargument zu berechnen, verwenden wir eine Unterformel, die auf der ROW-Funktion basiert:
(ROW(D1)*3)-1
Die Zeile erhält die erste Zelle in derselben Spalte und gibt 1 zurück. Dieses Ergebnis wird mit n multipliziert, was in diesem Beispiel 3 ist, um 3 zu erhalten.
Während die Formel in die Spalte kopiert wird, erhöht sich der von ROW zurückgegebene Wert um 1, wodurch das "n-te Muster" erstellt wird.
Der Grund, warum wir jeweils 1 subtrahieren, ist, dass die OFFSET-Funktion die Referenzzelle nicht enthält, wenn das Zeilenargument angewendet wird. Mit anderen Worten, das Versetzen um eine Zeile von A1 gibt A2 zurück:
=OFFSET(A1,1,0) // returns A2
Das Subtrahieren von 1 berücksichtigt dieses Verhalten.
Ab 1
Wenn Sie in der ersten Zeile mit dem Kopieren beginnen möchten und dann dem n-ten Muster folgen, können Sie die Formel folgendermaßen anpassen:
=OFFSET($B$5,(ROW(A1)-1)*n,0)
