Formula umum
=OFFSET($B$5,(ROW(A1)*n)-1,0)
Ringkasan
Untuk menyalin nilai atau menghasilkan referensi dengan pola seperti setiap baris ke-3, setiap baris ke-7, dll. Anda dapat menggunakan rumus berdasarkan fungsi OFFSET dan ROW. Dalam contoh yang ditampilkan, rumus di D5 adalah:
=OFFSET($B$5,(ROW(D1)*3)-1,0)
Yang dapat disalin ke bawah kolom D untuk mengambil nilai n dari kolom B.
Penjelasan
Di Excel, Anda tidak dapat dengan mudah menyalin rumus yang perlu melewati baris atau kolom mengikuti pola tertentu, karena referensi dalam rumus akan otomatis berubah mengikuti hubungan antara sel sumber asli dan sel target baru. Namun, dengan sedikit usaha, Anda dapat membuat referensi rumus yang mengikuti pola tertentu.
Dalam kasus ini, kami menggunakan fungsi OFFSET, yang dirancang untuk membangun referensi ke sel lain, atau rentang sel, berdasarkan titik awal atau asal tertentu.
Dalam contoh yang diperlihatkan, sel awal adalah B5, diberikan ke OFFSET sebagai argumen referensi, dan diperbaiki sebagai referensi absolut sehingga tidak akan berubah saat rumus disalin:
=OFFSET($B$5
Untuk menghitung nilai yang benar untuk argumen baris, kami menggunakan sub rumus berdasarkan fungsi ROW:
(ROW(D1)*3)-1
Baris diberi sel pertama di kolom yang sama, dan mengembalikan 1. Hasil ini dikalikan dengan n, yaitu 3 dalam contoh ini untuk mendapatkan 3.
Saat rumus disalin ke bawah kolom, nilai yang dikembalikan oleh ROW bertambah sebesar 1, yang menciptakan "pola ke-n".
Alasan kami mengurangi 1 dalam setiap kasus adalah karena fungsi OFFSET tidak menyertakan sel referensi saat argumen baris diterapkan. Dengan kata lain, mengimbangi satu baris dari A1 mengembalikan A2:
=OFFSET(A1,1,0) // returns A2
Pengurangan 1 memperhitungkan perilaku ini.
Mulai dari 1
Jika Anda ingin mulai menyalin pada baris pertama, kemudian mengikuti pola setiap n, Anda dapat menyesuaikan rumus seperti ini:
=OFFSET($B$5,(ROW(A1)-1)*n,0)
