=OFFSET(A1,3,2) mengembalikan sel yang berjarak 3 baris ke bawah dan 2 kolom ke kanan dari A1, yaitu C4. Kalau kamu juga memberinya tinggi dan lebar, rumus ini mengembalikan seluruh rentang, dan untuk itulah OFFSET paling sering dipakai: total dan rata-rata dari rentang yang bergeser atau bertambah.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Rows | Cols | Result | |
| 2 | Apple | Fruit | $1.20 | 3 | 2 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | ||||
| 4 | Carrot | Vegetable | $0.80 | ||||
| 5 | Bread | Bakery | $2.40 | ||||
| 6 | Milk | Dairy | $1.10 |
3 baris ke bawah dan 2 kolom ke kanan dari A1 sampai di C4, harga Carrot, $0.80. Isi Cols dengan 0 untuk mendapat nama Carrot, atau Rows dengan 5 untuk baris Milk. Baris dan kolom bisa negatif untuk bergerak ke atas atau mundur, dan bergerak melewati batas atas atau tepi sheet menghasilkan #REF!.
Sintaks OFFSET
=OFFSET(reference, rows, cols, [height], [width])
reference: sel (atau rentang) awal.rows,cols: seberapa jauh bergerak. 0 berarti tetap di tempat.height,width: ukuran rentang yang dikembalikan, dihitung dari sel tujuan. Kalau dikosongkan, ukurannya sama denganreference.
Kalau ditulis sendirian di sebuah sel, OFFSET yang mengembalikan beberapa sel akan spill di Excel 365; versi lama biasanya menampilkan #VALUE!. Di dalam SUM, AVERAGE, COUNT atau MAX, OFFSET bekerja sebagai rentang.
Jumlahkan N baris terakhir
Tugas klasik OFFSET: total yang selalu mencakup baris-baris terbaru, berapa pun baris yang sudah ditambahkan. COUNT mencari berapa banyak nilainya, OFFSET turun ke nilai pertama dari N nilai terakhir, dan tingginya mengambil N baris.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Last N | Total | ||
| 2 | Jan | 4,200 | 3 | 14,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
Ada 7 nilai, jadi OFFSET mulai 7-3+1, 5 baris di bawah B1, yaitu di B6, lalu mengambil 3 baris: Mei sampai Juli, 14,900. Ketik 4900 di B9 (Agustus), maka totalnya bergeser ke Juni, Juli dan Agustus, karena COUNT sekarang menemukan 8. Rentang B2:B13 menyisakan tempat untuk sisa tahunnya. Kolomnya tidak boleh punya sel kosong di tengah, atau COUNT menghitung terlalu sedikit dan jendelanya jatuh di tempat yang salah.
Rata-rata bergerak
Kalau disalin ke bawah sebuah kolom, OFFSET dengan pergeseran baris negatif memberi setiap baris jendela berisi baris-baris di atasnya: di sini rata-rata bulan ini dan dua bulan sebelumnya.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | 3-month average |
| 2 | Jan | 4,200 | |
| 3 | Feb | 3,900 | |
| 4 | Mar | 4,800 | 4,300 |
| 5 | Apr | 5,100 | 4,600 |
| 6 | May | 4,600 | 4,833 |
| 7 | Jun | 5,300 | 5,000 |
| 8 | Jul | 5,000 | 4,967 |
C4 merata-ratakan B2:B4 (Januari sampai Maret), 4,300. Setiap baris di bawahnya menggeser jendela satu baris ke bawah. Ubah 3 menjadi 6 dan -2 menjadi -5 untuk rata-rata enam bulan (mulai rumusnya di baris 7). Kasus yang satu ini sebenarnya tidak butuh OFFSET sama sekali: =AVERAGE(B2:B4) yang disalin ke bawah dari C4 melakukan hal yang sama, karena referensi relatif memang sudah bergeser. OFFSET baru berguna ketika ukuran jendelanya diambil dari sebuah sel.
Kenapa INDEX sering jadi pilihan yang lebih baik
OFFSET bersifat volatile: Excel menghitung ulang setiap OFFSET setelah ada perubahan di mana pun di workbook, karena tidak bisa tahu lebih dulu sel mana yang akan dirujuknya. Sheet dengan ribuan OFFSET jadi lambat. INDEX juga mengembalikan referensi, dan rentang yang ditulis sebagai start:INDEX(...) bertambah dengan cara yang sama tanpa bersifat volatile:
=SUM(OFFSET(B2, 0, 0, E2, 1)) first E2 rows, volatile
=SUM(B2:INDEX(B2:B13, E2)) same rows, not volatile
Keduanya membaca sebanyak E2 baris pertama dari kolom itu. OFFSET juga lebih sulit diperiksa: Trace Precedents dan garis berwarna yang digambar Excel saat kamu mengedit rumus menunjukkan sel awal dan argumennya, bukan rentang yang akhirnya dikembalikan OFFSET. Pakai OFFSET untuk model cepat atau rentang grafik; utamakan INDEX di workbook besar. INDEX membahas lebih lanjut cara mengembalikan rentang, dan INDIRECT adalah fungsi referensi volatile yang lain.
Latihan: total N bulan pertama
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | First N | Total | ||
| 2 | Jan | 4,200 | 4 | |||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
Giliranmu: Di F2, pakai OFFSET di dalam SUM untuk menjumlahkan N bulan pertama, dengan N ada di E2.
Pertanyaan yang sering diajukan
Apa fungsi OFFSET di Excel?
OFFSET mengembalikan referensi yang berjarak sejumlah baris dan kolom dari sel awal, dan ukurannya bisa diubah. =OFFSET(A1,3,2) adalah sel 3 baris ke bawah dan 2 kolom ke kanan dari A1, yaitu C4.
Bagaimana cara menjumlahkan N baris terakhir di Excel?
Mulai dari judul kolom lalu turun ke nilai pertama dari N nilai terakhir: =SUM(OFFSET(B1,COUNT(B2:B100)-N+1,0,N,1)). COUNT mencari berapa banyak nilainya, dan tinggi N mengambil sebanyak itu baris. Cara ini hanya berfungsi kalau kolomnya tidak punya sel kosong di tengah.
Kenapa OFFSET bersifat volatile?
Excel menghitung ulang setiap OFFSET setelah ada perubahan apa pun di workbook, karena sel yang dirujuknya baru diketahui setelah rumus itu dijalankan. Di workbook besar, ini memperlambat kerja. Rentang yang dibuat dengan INDEX, seperti B2:INDEX(B2:B100,N), melakukan hal yang sama tanpa bersifat volatile.
Apa saja argumen OFFSET?
OFFSET(reference, rows, cols, [height], [width]): sel awal, berapa baris ke bawah (negatif berarti ke atas), berapa kolom ke kanan (negatif berarti mundur), dan secara opsional ukuran rentang yang dikembalikan.
