Mengenal Vlookup , Hlookup, If dan menghapus #N/A

Tutorial ini saya kutip dari http://nanda.web.id/

Beberapa hari terakhir ini, saya sibuk dengan hobi baru. Membuat formula untuk excel. Hmm, semacam low level programming ?. Ternyata mengasyikan , saya terlarut cukup lama dengan ini. Konsepnya, semua hal pasti bisa dilakukan asal bisa dibayangkan. Dan efeknya, saya penuh imajinasi, pasti bisa kalo di-begini-kan, pasti bisa kalau di-begini-in. Suka. Meski, yah kadang-kadang idenya terlalu ekstrim dan ternyata tidak bisa di-cover oleh excelnya sendiri, he he.

Dan berikut beberapa hasil oprekan formula yang saya buat. oia, untuk contoh saya tampilkan data-data motor Honda karena kebetulan saya bekerja disana, jadi tutorial ini emang sengaja saya tulis sambil bekerja, gak masalah kan . Nah, sebelum memulai, perlu anda perhatikan, sering error yang terjadi adalah kesalahan penggunaan karakter ; (titik koma) dan , (koma) untuk memisahkan antarperintah dalam formula. Ini bergantung pada setting regional komputer anda. Karakter ; (titik koma) digunakan pada regional Indonesia. Sedangkan karakter , (koma) digunakan pada regional English (United States). Jadi, kalo formula anda tidak berfungsi, jangan dulu panik, coba deh cek untuk hal ini.

Fungsi VLOOKUP

Idenya, saya punya database berisi nilai-nilai dan keterangannya. Berdasarkan database tersebut, saya tampilkan keterangan saat formula menemukan nilai yang sama dengan database tadi. Fungsi yang saya gunakan adalah VLOOKUP.


=VLOOKUP(k3,master_plafond!$B:$C,2,0), dimana


VLOOKUP : Nama fungsi yang akan digunakan. awalan v menunjukkan data yang akan diambil berada dalam kolom vertikal. Kalau datanya ingin diambil dari kolom horizontal ? ya, namanya jadi hlookup dunks . Perlu diperhatikan, setiap fungsi pada excel membutuhkan karakter () yang mengapit formula bersangkutan.
K3 : Kolom berisi patokan nilai yang akan dicari datanya dari database.
master_plafond!$B:$C : Database dengan kolom berisi nilai yang sesuai dengan patokan nilai yang akan dicari. Apabila diperhatikan, penulisan master_plafond! terjadi pada saat nilai yang diambil tidak berada dalam worksheet yang sama dengan kolom dimana formula ini dituliskan.
2 : Posisi kolom ke- dari range kolom yang di-sort dimana berisi data yang akan ditampilkan manakala ditemui nilai yang sama dengan patokan K3.
0 : Nah, saya belum ngedapetin penjelasan teknis tentang angka 0. Mungkin semacam angka default yang harus ada. nanti saya cari deh, sebenernya angka ini teh representasi apa .

Masih bingung ? Berikut saya kasih ilustrasinya. Jadi kondisinya begini, saya ingin saat saya tulis nama motor di kolom Nama_Motor pada worksheet Olahan_Data, secara otomatis kolom Kode_Tipe akan menampilkan kode motor tersebut yang databasenya diambil dari worksheet Master_Plafond. Dan berikut capture screen-nya.

Fungsi_VLOOKUP #1

Fungsi_VLOOKUP #2

Fungsi_VLOOKUP #3

Fungsi IF

Idenya, saya berada dalam 2 kondisi. Pada saat formula menemukan kondisi 1, saya ingin menampilkan data UVW dari worksheet S kolom D. Sementara itu, apabila formula menemukan kondisi 2, saya ingin menampilkan data XYZ dari worksheet S kolom F. Dan fungsi yang saya gunakan adalah IF.


=IF(K3=”-”;”silakan isi nama motor”;VLOOKUP(K3;Master_Plafond!$B:$C;2;0)), dimana

IF : Nama fungsi yang digunakan pada kondisi ini.
K3=”-” : Kondisi yang menunjukkan situasi pada saat pada kolom K3 memiliki nilai -. Kondisi ini juga dijadikan dasar pengambilan keputusan.
“silakan isi nama motor” : Statement ini akan muncul pada saat kolom K3 memiliki nilai -. Atau dapat juga diartikan, manakala kondisi terpenuhi, maka eksekusi dengan pilihan ini akan dilakukan.
VLOOKUP(K3;Master_Plafond!$B:$C;2;0) : Apabila kolom K3 memiliki nilai dan sesuai dengan database pada master_plafond, maka data yang sesuai akan ditampilkan. Apabila kolom terisi, tapi nilainya tidak sesuai dengan database, maka statement #N/A akan ditampilkan. Perlu diketahui juga, section ini dapat diganti dengan statement “nama motor tidak sesuai dgn master”, misalnya. Dan secara otomatis, statement atau hasil eksekusi fungsi VLOOKUP tersebut akan ditampilkan manakala kondisi tidak terpenuhi.

Masih bingung juga ? Jadi untuk fungsi ini, begini ilustrasinya. Saya ingin, pada saat saya mengisi kolom K3 dengan -, maka kolom O3 akan menampilkan tulisan silakan isi nama motor. Apabila, nama motor terisi dengan kalimat selain -, maka kolom O3 akan menampilkan hasil VLOOKUP. Dan apabila dari hasil VLOOKUP, tidak ditemui kesamaan, kolom O3 akan menampilkan kalimat #N/A. Kalau kolom K3 tidak diisi samasekali, gimana ? Yaa, hasil VLOOKUP juga pasti gagal dan nilai yang akan ditampilkan #N/A juga.
Nah, berikut capture screen-nya,

Fungsi_IF

Menghilangkan #N/A

Idenya, saya agak terganggu dengan tulisan #N/A saat formula yang saya gunakan tidak menemui hasil. Kenapa ? Sederhana ajah sih, karena kata-katanya ga user friendly, he he he. Lalu, bisa ga kalo #N/A ini diganti dengan kalimat lain ? Ooohh, tentu bisaa . Dan yang saya lakukan adalah menyisipkan statement ISNA dalam formula saya.


=IF(ISNA(VLOOKUP(K3;Master_Plafond!$B:$C;2;0));”tipe blm ada di master”;VLOOKUP(K3;Master_Plafond!$B:$C;2;0)), dimana
ISNA(VLOOKUP(K3;Master_Plafond!$B:$C;2;0)) : Kalimat ISNA ditempatkan sebagai suatu fungsi dan diletakkan didepan apitan formula yang ga saya ingin apabila ia gagal lalu menampilkan #N/A.
“tipe blm ada di master” : Statement ini akan menggantikan kalimat #N/A pada saat formula gagal memperoleh data.
VLOOKUP(K3;Master_Plafond!$B:$C;2;0) : Formula yang dimanfaatkan untuk mengambil data.

Nah, sekarang saya tampilkan screenshoot-nya yah…

Menghilangkan #N/A

Sumber : http://nanda.web.id/2011/04/27/fungsi-vlookup-if-dan-menghilangkan-na-pada-microsoft-excel/