Formula generik
=OFFSET($B$5,(ROW(A1)*n)-1,0)
Ringkasan
Untuk menyalin nilai atau menghasilkan rujukan dengan corak seperti setiap baris ke-3, setiap baris ke-7, dan lain-lain, anda boleh menggunakan formula berdasarkan fungsi OFFSET dan ROW. Dalam contoh yang ditunjukkan, formula dalam D5 adalah:
=OFFSET($B$5,(ROW(D1)*3)-1,0)
Yang boleh disalin turun lajur D untuk mengambil nilai n dari lajur B.
Penjelasan
Di Excel, anda tidak dapat dengan mudah menyalin formula yang perlu melangkau baris atau lajur mengikuti corak tertentu, kerana rujukan dalam formula akan berubah secara automatik berikutan hubungan antara sel sumber asal dan sel sasaran baru. Walau bagaimanapun, dengan sedikit kerja mungkin untuk membina rujukan formula yang mengikut corak tertentu.
Dalam kes ini, kami menggunakan fungsi OFFSET, yang dirancang untuk membina rujukan ke sel lain, atau julat sel, berdasarkan titik awal, atau asal yang ditentukan.
Dalam contoh yang ditunjukkan, sel permulaan adalah B5, diberikan kepada OFFSET sebagai argumen rujukan, dan diperbaiki sebagai rujukan mutlak sehingga tidak akan berubah ketika formula disalin:
=OFFSET($B$5
Untuk mengira nilai yang betul untuk argumen baris, kami menggunakan sub formula berdasarkan fungsi ROW:
(ROW(D1)*3)-1
Baris diberi sel pertama dalam lajur yang sama, dan mengembalikan 1. Hasil ini dikalikan dengan n, yang merupakan 3 dalam contoh ini untuk mendapatkan 3.
Oleh kerana formula disalin ke bawah lajur, nilai yang dikembalikan oleh ROW bertambah sebanyak 1, itulah yang membuat "corak nth".
Sebab kita mengurangkan 1 dalam setiap kes adalah kerana fungsi OFFSET tidak termasuk sel rujukan ketika argumen baris diterapkan. Dengan kata lain, mengimbangi satu baris dari A1 mengembalikan A2:
=OFFSET(A1,1,0) // returns A2
Menolak 1 mengambil kira tingkah laku ini.
Bermula pukul 1
Sekiranya anda ingin mula menyalin pada baris pertama, kemudian ikuti setiap corak ke-9, anda boleh menyesuaikan formula seperti ini:
=OFFSET($B$5,(ROW(A1)-1)*n,0)
