Fungsi VLOOKUP adalah salah satu fungsi yang sangat powerfull di Excel, dan diajarkan serta ditulis dalam buku-buku Excel standar serta sering digunakan oleh para pengguna Excel dalam me-LookUp data.
Namun, sebenarnya Vlookup bukan satu-satunya fungsi untuk tujuan me-LookUp data. Karena sebenarnya masih banyak fungsi lain yang bisa kita gunakan sebagai Variasi dari VLOOKUP dan saudaranya: HLOOKUP.
Kali ini pembahasan kita adalah tentang fungsi OFFSET.
Menurut Help-nya, Excel mendefinisikan fungsi OFFSET sebagai suatu fungsi untuk : Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. (Mengembalikan referensi ke range yang ditentukan dengan jumlah tertentu dari baris dan kolom dari sel atau range sel).
Bahasa sederhananya, Mengubah rujukan sel sesuai dengan jumlah baris dan kolom yang ditentukan.
Syntaxnya:
=OFFSET(reference, rows, cols, [height], [width])
reference: alamat sel sebagai pedoman arah perubahan atau pergeseran.
rows: Pindahnya berapa baris?
cols : Pindahnya berapa kolom?
height: Tingginya berapa baris (bersifat optional, artinya boleh ditulis, boleh dikosongkan)
width: Lebarnya berapa kolom (bersifat optional, artinya boleh ditulis, boleh dikosongkan)
Contoh Penggunaan
Jika hanya melihat penjelasannya, memang agak sulit dipahami, sehingga kita akan langsung melihat contohnya di TKP.
Pada gambar di atas, kita punya tabel Data di sel B3 sampai F7. Kemudian kita punya isian Nama, Alamat, dan Nomor Telepon di sel E11 sampai E13. Serta Nomor urut di sel E9. Nah tugas kita adalah mengisi isian Nama di sel E11 sesuai dengan nomor urut di sel E9.
Rumus yang digunakan menggunakan fungsi OFFSET seperti berikut:
=OFFSET(C2,E9,0)
Bahasa manusianya: Ubah atau geser data di sel C2, sebanyak nilai E9 (4) baris, dan 0 kolom (tidak bergeser kolomnya).
C2 : pedoman atau TITIK PIJAK awal pergeseran sel.
E9 : bergeser sebanyak 4 Baris
0 : bergeser sebanyak 0 kolom alias tetap.
Maka hasilnya : adalah Nama di nomor 4 yakni, Pangeran Mangkubumi.
Kalau nilai di sel E9 kita ubah menjadi 3, maka hasilnya pun akan berubah menjadi Nama di nomor urut 3.
Keterangan tentang Rows dan Cols
Jika nilai dari row POSITIF, maka pergeserannya ke bawah dari sel TITIK PIJAK. Jika negatif, maka pergeserannya ke ATAS dari TITIK PIJAK.
Jika nilai dari Col POSITIF, maka pergeserannya ke KANAN dari sel TITIK PIJAK-nya. Jika NEGATIF, maka bergeser ke KIRI.
Bagaimana dengan isian Alamat dan Nomor Telepon?
Ayo silahkan dicoba sendiri........!
Bandingkan dengan:
Alamat : =OFFSET(C2,E9,2) atau =OFFSET(E3,E9,0) atau =OFFSET(F3,E9,-1) atau =OFFSET(D2,E9,1)
No Telp: =OFFSET(C2,E9,3) atau =OFFSET(E3,E9,1) atau =OFFSET(F3,E9,0) atau =OFFSET(D2,E9,2)
Keuntungan menggunakan fungsi OFFSET dibandingkan dengan VLOOKUP adalah semakin kecilnya kemungkinan hasil #NA seperti yang sering terjadi pada fungsi VLOOKUP jika data yang rujuk keluar dari batas range yang ditentukan.
Untuk penggunaan lebih lanjut, Fungsi OFFSET bisa digandengkan dengan fungsi-fungsi lain seperti SUM, AVERAGE, MATCH, dan lain-lain sesuai keperluan. Contoh penggunaan OFFSET lebih lanjut bisa dilihat dalam membuat Defined Name Dinamis pada posting sebelumnya.
Demikian, semoga bermanfaat.
