Sabtu, 10 Desember 2011

Fungsi OFFSET sebagai Alternatif Pengganti VLOOKUP

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.


Artikel Terkait:

5 komentar:

Isti mengatakan...

terima kasih. sangat bermanfaat

ariemedia mengatakan...

maaf mas, tp perbedaannya antara vlookup/hlookup dengan offset jauh kok.
menurut saya vlookup itu kegunaanya mencari data yg kita panggil berdasarkan tabel.
sedangkan offset itu berbeda, coba saja pada tabel yg di artikel di atas, bila urutan nomornya kita acak ( tdk terurut pd gmbar diatas ) maka hasilnya psti berbeda.

Nancy mengatakan...

Saya setuju dengan Mas Ariemedia, offset memang sangat bagus untuk mencari data berdasarkan letaknya (row dan column), tetapi apabila datanya tidak berurutan (misalnya Ismail Marzuki kodenya 3 bila diganti menjadi 8, maka rumus offset akan mencari row ke 8 dan hasilnya jadi salah.
Memang lebih bagus menggunakan vlookup, tapi vlookup juga ada kekurangan yang mana jika data kuncinya (kolom pertamanya) tidak berurutan, maka kebanyakan hasilnya akan salah atau #NA.
Menurut saya rumus pencarian data paling akurat dan paling bagus adalah kombinasi index dan match. (Tolong dikasih masukan kalo ada yang lebih bagus dari index dan match). But btw penjelasan untuk offset di sini sangat bagus dan mudah dimengerti.
Saya tunggu karya Pak Heru selanjutnya.

excell mengatakan...

Betul sekali bahwa OFFSET tidaklah bisa menggantikan VLOOKUP. Sehingga di judul di atas, saya menggunakan istilah "alternatif pengganti". Fungsi OFFSET akan gagal jika no key-nya tidak berurutan. Tapi, karena sebagian besar tabel seringkali dimulai dengan kolom "No Urut", maka fungsi OFFSET bisa digunakan untuk menggantikannya.
Fungsi OFFSET jika digabung dengan fungsi MATCH, maka saya rasa setara dengan kombinasi INDEX-MATCH (walaupun tidak sama benar). Artinya untuk data kunci yang tidak berurutan, kita bisa menggunakan kombinasi fungsi OFFSET-MATCH.
Saya sendiri, sejak mengenal fungsi OFFSET ini, tidak pernah lagi menggunakan fungsi VLOOKUP untuk menyelesaikan berbagai macam permasalahan di Excel.
Intinya, setiap fungsi memmpunyai karakteristik tersendiri. Penggunaannya akan sangat tergantung pada kondisi dan masalah yang dihadapi, serta kecenderungan user itu sendiri.

Sandra Sasi Kirana mengatakan...

Alhamdulillah, terima kasih infonya... Berguna sekali :)