Offset Function, Formula Excel Yang Kerap Terlupakan

Ada sebuah formula di Microsoft Excel yang jarang sekali kita gunakan dalam pekerjaan sehari-hari, padahal menyimpan banyak sekali manfaat, khususnya bagi mereka yang sudah mengetahui fungsi ini sebelumnya (i.e OFFSET Function). Formula ini bisa kita temukan dengan mudah di fasilitas help pada bab; “Lookup and reference functions (reference)”. Cukup dengan menekan tombol F1 di computer anda dan mengetikkan kata “Offset” pada search engine di help, maka anda dapat mempelajarinya lebih dalam. Detailnya sebagai berikut;

OFFSET function

Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.

Penjelasan: Saya coba tafsirkan kalimat diatas dengan gaya bahasa saya sendiri, CMIIW. Hasil akhir dari fungsi Offset adalah kembali kepada specific range, yakni perpotongan baris (row) dan kolom (column) yang berupa cell tertentu e.g A1 atau B1 misalnya. Meskipun single, secara teknis A1 atau B1 tetap disebut sebagai range. Atau bisa juga sekumpulan cell yang membentuk array e.g A1:A12 misalnya. Untuk lebih jelasnya akan kita praktekan nanti.

Syntax

OFFSET(reference,rows,cols,height,width)

Reference is the reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.

Penjelasan: Pada syntax diatas terdapat reference yang merujuk kepada sebuah cell yang akan kita jadikan base atau titik acuan. Analogi sederhananya begini, kita memiliki rumah tinggal dan hampir setiap hari kita memulai rutinitas/aktivitas dari tempat tersebut. Berangkat ke kantor misalnya, (umumnya) diawali dari rumah kemudian berkendara menuju kantor. Rumah itulah yang kita sebut sebagai base. Jika reference kita isi dengan array, maka akan menghasilkan info “#VALUE!”.

Rows is the number of rows, up or down, that you want the upper-left cell to refer to. Using 5 as the rows argument specifies that the upper-left cell in the reference is five rows below reference. Rows can be positive (which means below the starting reference) or negative (which means above the starting reference).

Penjelasan: Rows berupa angka numeric, yakni sejumlah angka yang menunjukan pergeseran posisi cell dari titik acuan semula, ke atas atau ke bawah. Jika angka tersebut positif, maka posisi cell aktif akan bergeser ke bawah, jika angkanya negative maka posisi cell aktif akan bergeser ke atas. Rows = 5, artinya cell aktif bergeser ke bawah sejumlah 5 baris dari titik acuan semula, terhitung dari baris pertama setelah reference cell. Contoh reference = A1, Rows = 5, maka posisi cell aktif adalah A6 (dengan catatan, Cols = 0, red).

Cols is the number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Using 5 as the cols argument specifies that the upper-left cell in the reference is five columns to the right of reference. Cols can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference).

Penjelasan: Sebagaimana Rows, Cols atau Columns juga berupa angka numeric yang bisa kita isi dengan bilangan bulat, baik positif maupun negative. Namun pergeserannya tidak ke bawah atau ke atas, melainkan ke kanan atau ke kiri mengikuti kolom-kolomnya. Columns positif artinya cell aktif bergeser ke arah kanan sedangkan columns negative artinya cell aktif bergeser ke arah kiri. Cols = 5 artinya cell aktif bergeser ke kanan sejumlah 5 baris dari titik acuan semula, terhitung dari kolom pertama setelah reference cell. Contoh reference = A1, Cols = 5, maka posisi cell aktif adalah F1 (dengan catatan, Rows = 0, red).

Height is the height, in number of rows, that you want the returned reference to be. Height must be a positive number.

Width is the width, in number of columns, that you want the returned reference to be. Width must be a positive number.

Penjelasan: Height dan Width merupakan angka numeric yang merujuk kepada sejumlah row dan column yang akan kita treatment. Hasil akhirnya bisa berupa cell, atau kumpulan cell (array). Treatment diawali dari cell aktif hasil perpotongan Rows & Cols. Contoh; cell aktif berada pada cell A1, height = 2 dan width = 2, maka hasil akhirnya berupa block range A1:B2. (Catatan; height dan width harus bernilai positif)

Dan berikut adalah contoh penerapan syntaxnya.

Perbedaan gambar yang pertama dan kedua terletak pada nilai height-nya (lihat gambar diatas, red). Hasil akhirnya akan menunjukkan nilai yang berbeda. C1 = base/reference, raws=2, cols=1, height=1 or 2 dan width=1. Artinya posisi cell aktif berada di baris ke-2 di bawah C1 i.e baris 3 dan 1 kolom di kanan C1 i.e kolom D. Artinya pula bahwa perpotongan keduanya berada pada cell D3. Kunci treatmentnya sendiri terletak pada nilai height dan width. Nilai (1,1) pada gambar yang pertama menunjukkan bahwa system melakukan blocking hanya pada satu cell saja, yakni D3 sehingga hasil akhirnya menjadi “A”. Sedangkan nilai (2,1) pada gambar dibawahnya menunjukkan bahwa system melakukan blocking pada range D3:D4 sehingga hasil akhirnya menjadi #VALUE. Mengapa? Jika fungsi Offset tidak dikombinasikan dengan fungsi lain seperti SUM, SUMIF, SubTotal, CountA, CountIF dan sebagainya, maka nilai #VALUE akan tetap muncul selama treatment Offset berupa range, dan bukan cell. Tapi jika kita kombinasikan dengan fungsi lain e.g SUM atau CountA misalnya, maka akan menghasilkan nilai tertentu. Contoh; ketiklah formula berikut, SUM(OFFSET(C1,2,1,2,1)), maka hasil akhirnya menunjukkan nilai “0” atau ketik COUNTIF(OFFSET(C1,2,1,2,1),”A”), dimana hasil akhirnya akan menunjukkan nilai “1”.

Masih banyak manfaat lain dari fungsi Offset yang bisa kita pelajari dan kupas lebih dalam, khususnya yang berkaitan dengan pengolahan database yang selalu berubah secara dinamis, atau pembuatan grafik secara otomatis yang dipadukan dengan bahasa VBA sebagaimana pembahasan kita terdahulu. InsyaAllah pada kesempatan lain akan saya share dan tambahkan kembali…