Formuła ogólna
=OFFSET($B$5,(ROW(A1)*n)-1,0)
Podsumowanie
Aby skopiować wartości lub wygenerować odniesienia ze wzorcem, takim jak co trzeci wiersz, co siódmy wiersz, itp., Możesz użyć formuły opartej na funkcjach OFFSET i ROW. W przedstawionym przykładzie formuła w D5 to:
=OFFSET($B$5,(ROW(D1)*3)-1,0)
Które można skopiować w dół kolumny D, aby pobrać n-te wartości z kolumny B.
Wyjaśnienie
W programie Excel nie można łatwo kopiować formuł, które muszą pomijać wiersze lub kolumny według określonego wzorca, ponieważ odwołania w formule zostaną automatycznie zmienione zgodnie z relacją między pierwotną komórką źródłową a nową komórką docelową. Jednak przy odrobinie pracy można skonstruować odwołania do formuł, które będą zgodne z określonymi wzorami.
W tym przypadku używamy funkcji PRZESUNIĘCIE, która służy do tworzenia odwołań do innych komórek lub zakresów komórek na podstawie danego punktu początkowego lub źródła.
W pokazanym przykładzie komórką początkową jest B5, przekazana do OFFSET jako argument odniesienia i ustalona jako odniesienie bezwzględne, więc nie zmieni się podczas kopiowania formuły:
=OFFSET($B$5
Aby obliczyć poprawną wartość argumentu wierszy, używamy formuły podrzędnej opartej na funkcji WIERSZ:
(ROW(D1)*3)-1
Wiersz otrzymuje pierwszą komórkę w tej samej kolumnie i zwraca 1. Ten wynik jest mnożony przez n, które w tym przykładzie wynosi 3, aby uzyskać 3.
Gdy formuła jest kopiowana w dół kolumny, wartość zwracana przez WIERSZ zwiększa się o 1, co tworzy „n-ty wzór”.
Powodem, dla którego odejmujemy 1 w każdym przypadku, jest to, że funkcja PRZESUNIĘCIE nie obejmuje komórki odniesienia, gdy stosowany jest argument wierszy. Innymi słowy, przesunięcie o jeden wiersz z A1 zwraca A2:
=OFFSET(A1,1,0) // returns A2
Odejmowanie 1 uwzględnia to zachowanie.
Począwszy od 1
Jeśli chcesz rozpocząć kopiowanie od pierwszego wiersza, a następnie postępować zgodnie z co n-tym wzorem, możesz dostosować formułę w następujący sposób:
=OFFSET($B$5,(ROW(A1)-1)*n,0)
