Manchmal möchten wir abwechselnd Zellen aus einem Bereich oder jeden dritten Wert oder jeden vierten Wert usw. kopieren. In all diesen Fällen ist das manuelle Kopieren eine anstrengende Aufgabe. Was ist also die Lösung? Wie kopiert man den Wert aus jeder n-ten Zeile? Gibt es dafür eine Formel?
Ja da ist. Wir können die OFFSET-Funktion von Excel verwenden, um Werte aus n-ten Zeilen zu erhalten. Wie? Mal sehen.
=OFFSET(First_Cell_in_List,(REIHE(A1)*n)-1,0)
First_Cell_in_Range: Dies ist die absolute Referenz der ersten Zelle im Listenbereich, aus der Sie kopieren möchten. Wenn Ihre Liste beispielsweise in A5:A100 ist, dann First_Cell_in_Range ist $ 5 A$. Fügen Sie keine Spaltenüberschrift hinzu.
n: Es ist eine Zahl, die Sie als Vielfaches erhalten. Wenn Sie beispielsweise jeden zweiten Wert aus der Liste abrufen möchten, ist N gleich 2. Für jeden dritten Wert ist es 3 und so weiter.
REIHE(A1): es, um ein Vielfaches von N zu erhalten.
Sehen wir uns nun ein Beispiel an, und dann werde ich erklären, wie es funktioniert.
Beispiel: Jeden zweiten Wert aus einer Liste in Excel kopieren
Hier haben wir eine Liste mit Artikeln. Wir müssen jeden zweiten Wert aus der Liste in einen neuen Bereich kopieren.
Also, wie machen wir das?
Da wir 11 Elemente auf der Liste haben, erhalten wir fünf alternative Elemente (2, 4, 5, 6, 10).
Schreiben Sie die folgende Formel in E4 und kopieren Sie die Zellen nach unten.
=OFFSET($B$4,(ZEILE(A1)*2)-1,0)
Hier ist $B$4 die erste Zelle im Listenbereich, und da wir jeden zweiten (alternativen) Wert aus der Liste kopieren möchten, haben wir unser N als 2.
Wenn Sie jedes dritte Element kopieren möchten, verwenden Sie diese Formel.
=OFFSET($B$4,(ZEILE(A1)*3)-1,0)
Für jeden 4.:
=OFFSET($B$4,(ZEILE(A1)*4)-1,0)
Und so weiter. Sie können jeden N-ten Wert aus a kopieren, indem Sie diese Excel-Formel verwenden.
Wie es funktioniert
Die Formel erfüllt diese Aufgabe mit zwei Excel-Funktionen, OFFSET und ROW.
Die Funktion OFFSET gibt den Wert des Zeilen- und Spalten-Offsets einer bestimmten Zelle zurück.
Die Funktion ROW gibt die Zeilennummer der angegebenen Referenz zurück.
Von innen beginnend, gibt ROW(A1) 1 zurück. Da A1 zur 1. Reihe gehört.
(REIHE(A1)*2 es wird 2.
(REIHE(A1)*2)-1, es wird (1*2)-1=1.
Wenn wir die Formel nach unten kopieren, ändert sich die Referenz in ROW in A2, A3, A4 usw. Für A2 erhalten wir den Endwert (2*2)-1=3. Für A3 erhalten wir (3*3)-1=5 und so weiter.
Nun haben wir unsere Formel in E4 zu =OFFSET($B$4,1,0). Dadurch wird der Wert 1 Zeile darunter von B4 zurückgegeben, der der zweite Wert von B4 ist.
In E4 lautet die Formel =OFFSET($B$4,3,0), was der vierte Wert von B4 ist. Ebenso erhalten wir alle Wechselkosten.
Jetzt wissen Sie, wie Sie jeden N-ten Wert aus der Liste in Excel kopieren. Das ist die Technik, die ich kenne. Wenn Sie einen anderen Trick kennen, um die n-te Zelle aus einem Bereich zu kopieren, können Sie dies gerne im Kommentarbereich unten teilen. Wenn Sie Fragen zu dieser oder einer anderen Funktion von Excel 2019, 2016, 2013 und 2010 haben, verwenden Sie den Kommentarbereich.
So rufen Sie den neuesten Preis in Excel ab
Abrufen des ersten Werts in einer Liste, der größer/kleiner als ein angegebener Wert ist
So rufen Sie die gesamte Zeile eines übereinstimmenden Werts ab
Populäre Artikel:
Die SVERWEIS-Funktion in Excel
ZÄHLENWENN in Excel 2016
So verwenden Sie die SUMIF-Funktion in Excel