Wert aus jeder n-ten Zeile kopieren

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. Im gezeigten Beispiel lautet die Formel in D5:

= OFFSET ($B,( ROW (A1)*n)-1,0)

Welche kann nach unten in Spalte D kopiert werden, um n-te Werte aus Spalte B aufzunehmen.

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 entsprechend der Beziehung zwischen der ursprünglichen Quellzelle und der neuen Zielzelle ändern. Mit etwas Arbeit ist es jedoch möglich, Formelreferenzen zu konstruieren, die bestimmten Mustern folgen.

In diesem Fall verwenden wir die OFFSET-Funktion, mit der Referenzen auf andere Zellen oder Zellbereiche basierend auf einem bestimmten Startpunkt oder Ursprung erstellt werden.

Im gezeigten Beispiel ist die Startzelle B5, die als Referenzargument für OFFSET bereitgestellt und als absolute Referenz festgelegt wurde, damit sie sich beim Kopieren der Formel nicht ändert:

Berechnen Sie das Alter anhand des Geburtsdatums

 
= OFFSET ($B,( ROW (D1)*3)-1,0)

Um den richtigen Wert für das Argument rows zu berechnen, verwenden wir eine Unterformel basierend auf der ROW-Funktion:

 
= OFFSET ($B

Row 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.

Wenn die Formel in die Spalte kopiert wird, wird der von ROW zurückgegebene Wert um 1 erhöht, wodurch das 'n-te Muster' erstellt wird.

Der Grund, warum wir in jedem Fall 1 subtrahieren, liegt darin, dass die OFFSET-Funktion die Referenzzelle nicht einschließt, wenn das Zeilenargument angewendet wird. Mit anderen Worten, das Versetzen um eine Zeile von A1 gibt A2 zurück:

 
( ROW (D1)*3)-1

Die Subtraktion von 1 berücksichtigt dieses Verhalten.

Ab 1

Wenn Sie mit dem Kopieren in der ersten Reihe beginnen möchten, dann folgen Sie dem jedes n-ten Muster, Sie können die Formel wie folgt anpassen:

 
= OFFSET (A1,1,0) // returns A2