Vlookup
Fungsi vlookup merupakan sebuah fungsi Ms.
Excel yang kurang lebih berfungsi untuk mencari atau melihat sebuah data “look”
dari suatu table secara vertical. Lihat contoh gambar table dibawah ini :
Jika terdapat daftar penjualan barang dalam
sebulan dengan menggunakan kode kode diatas, dan kita harus mendapatkan total
pemasukan dari penjualannya, hal termudah adalah menggunakan fungsi Vlookup
adapun syntaknya sebagai berikut :
=VLOOKUP(lookup_value,table_array,column_index_number,[range_lookup])
Penyelesaian
Terlihat dalam gambar fungsi vlookup sebagai
berikut =VLOOKUP(G2,$B$2:$E$7,3,0)
• G2 ; merupakan letak cell kode yang akan
dicari (lookup value)
• $B$2:$E$7 ; merupakan lokasi ataupun daerah
cell table keterangan dari kode tersebut (Table array), perhatikan tanda $,
tanda tersebut merupakan pengunci cell agar saat formula di copy cell tidak
bergeser
• 3 ; merupakan nomor kolom yang akan
ditampilkan (column index number), kolom 3 merupakan kolom harga, kolom 2 yaitu
kolom jenis, kolom 4 adalah kolom kualitas, sedangkan kolom pertama yaitu kode
itu sendiri
• 0 ; merupakan range lookup, dalam point ini
range lookup dapat anda isikan true “benar” kode dalam table urut atau sesuai
abjad, jika tidak hasil lookup akan terdapat #N/A atau tidak sesuai, jika range
lookup anda isikan false “salah” hasilnya akan sesuai meskipun table dalam
kondisi tidak urut, untuk angka 0 dapat diartikan “false”
Satu contoh kasus yang lebih kompleks, jika
terdapat sebuah kode, dimana kode tersebut merupakan gabungan beberapa data yang
sengaja disingkat agar lebih simple, Misalkan terdapat sebuah kode di pabrik
konveksi, kita disuruh menentukan harga penjualan sebuah kain berdasarkan waktu
produksi, kualitas, dan warna kain :
0610AR, “0610” merupakan bulan dan tahun produksi, “A” kualitas
kain, dan “H” warna kain
Dapat dipastikan kita akan memiliki beberapa tabel harga ataupun potongan
menurut
1. Waktu produksi kain
2. Kualitas kain
3. Warna Kain
Untuk
penyelesaian menggunakan fungsi vlookup dalam Ms. Excel sebagai berikut :
- Pisahkan kode tersebut menjadi tiga kode
dalam cell yang berbeda menggunakan fungsi Right, Mid dan Left.
- Jika bentuk kode Tanggal pada table sebagai berikut
Maka kode yang kita ambil hanya pada bulannya saja “0610” menjadi “6” dengan
cara mengganti formula Left menjadi seperti gambar dibawah,
(Value adalah converter dari text menjadi angka)
- Langkah berikutnya adalah menamai ketiga table tersebut pada “name box”,
Kegunaan menamai table tersebut agar dalam penulisan formula kita tidak susah
payah untuk blog “table_array” atau lokasi table, untuk lebih detailnya lihat
gambar
Lakukan seperti hal tersebut pada table kualitas dan harga dengan nama “name
box” yang berbeda.
- Cari hasil lookup dari ketiga kode tersebut sehingga mendapatkan hasil table
sesuai kode
Hasil daru formula diatas adalah 0.12 kenapa bukan 12%? Saya katakana sama saja,
12% adalah persentase dari pecahan 12/100 yang merupakan decimal dari angka 0.12
“mungkin anda sudah mengerti karena di SD kelas 3 sudah di bahas dalam satu
semester”, lakukan pada kedua kode selanjutnya
Coba ubah Kode pada cell B3, contoh dengan kode 1110CB atau yang lainnya, cek
hasil lookup dengan table apakah sesuai atau tidak, jika tidak sesuai maka akan
tertulis #N/A , untuk menghindarinya dapat anda gunakan fungsi “Iferror”.
- Langkah selanjutnya tinggal mencari harga dari kode tersebut yaitu sebagai
berikut,
Didalam pelajaran sekolah mungkin kita telah
mempelajari matematika dengan bab rugi laba ataupun bunga dan potongan harga,
jadi dari contoh soal diatas dapat kita ambiil syntak harga sebagai berikut
:
Ha = Hs – { Hs x ( Pk + Pp )}
Ha : Harga setelah diskon
Hs :
Harga sebelum diskon
Pk : Diskon menurut Kualitas barang
Pp : Diskon
menurut Bulan Produksi
Dalam penghitungan di Ms. Excel dapat dituliskan
sebagai berikut :
Jika anda sudah biasa menggunakan Excel, mungkin formula ini lebih cocok untuk
anda, yaitu formula dimana pembahasan panjang lebar diatas hanya disingkat
kedalam satu formula saja.
Hlookup
Hlookup adalah sebuah fungsi yang sama dengan
vlookup hanya saja terdapat perbedaan pada lookup data dimana vlookup secara
vertical sedangkan hlookup secara horizontal, untuk lebih jelasnya saya sertakan
sampel agar lebih dapat dipahami. Dalam kasus soal diatas pada penghitungan
vlookup terdapat hasil harga berdasarkan kode yang tersedia, jika terdapat table
potongan harga menurut banyaknya penjualan dimana table tersebut disajikan
secara horizontal seperti gambar dibawah ini,
Pada gambar diatas terllihat table dimana pada
kilogram hanya terdapat angka kelipatan 10, bagaimana jika pembelian pada angka
45? Bagaimana penghitungan dalam formula excel untuk mendapatkan persentase pada
angka tersebut jika pada lookup value tidak ada angka 45?
Kita gunakan pembulatan kebawah, 45 kita
jadikan 40, 56 jadikan 50, 99 jadikan 90 walaupun angka tersebut dekat dengan
angka 100. Adapun caranya menggunakan fungsi “INT”
Int merupakan fungsi Excel yang berguna untuk
membuat pecahan ataupun decimal ke dalam bilangan bulat dengan cara pembulatan
kebawah, /10 saya gunakan agar 45 menjadi bilangan decimal yaitu 4,5 setelah itu
akan di bulatkan menjadi 4 oleh fungsi INTdan akhirnya saya kalikan 10 kembali
sehingga menjadi angka 40, tapi itu tak penting yang penting anda tau maksud
dari penggunaan fungsi hlookup yang akan saya bahas di bawah ini
Penggunaan Hlookup pada kasus
di atas agar mendapatkan potongan harga sebagai berikut,
- $C$4:$M$5 = Adalah table_array atau lokasi table yang sebenarnya penulisannya
sebagai berikut C4:M5, akan tetapi terdapat tanda dolar $, tanda tersebut
berfungsi untuk mengunci cell, untuk lebih jelasnya lihat “Penggunaan fungsi Ms.
Excel”, sebenarnya anda dapat menggunakan cara seperti pembahasan Vlookup yaitu
menamai table pada name box, tetapi akan lebih baiik jika anda memiliki
pengetahuan lebih
- Lihat lingkaran merah di “row_index_num”
berbeda pada formula Vlookup dimana tertulis “col_index_num”, disitulah
perbedaannya jika penyajian table secara vertical ataupun kolom (column) maka
kita menggunakan fungsi vlookup, jika penyajian table secara horizontal, baris
atau sering disebut (row) maka kita menggunakan fungsi Hlookup
- =HLOOKUP(lookup_value,table_array,row_index_number,[range_lookup])
- Lihat lingkaran kedua, #N/A berarti dalam table tidak ada angka tersebut,
bukannya 230 merupakan >100? Benar, akan tetapi dalam table tidak terdapat
angka 230 hanya >100. Untuk mengatasi hal tersebut maka kita perlu
menggunakan fungsi “If” atau “Iferror”
Penggunaan if
Hlookup(“>100”,$C$4:$M$5,2,0) dapat langsung anda ganti “0.5” yang merupakan
hasil dari fungsi tersebut
Penggunaan iferror
Setelah saya perhatikan ternyata ada beberapa
kesalahan seperti kolom potongan saya isi dengan hasil pembulatan kebawah dan
kolom pembayaran terisi oleh potongan harga, nah dibawah ini gambar secara
lengkap yang telah direvisi serta pemadatan formula agar simple.
Dan akhirnya selesailah pembahasan tentang
fungsi Hlookup dan Vlookup pada Ms. Excel, Jika ada pertanyaan dengan senang
hati saya akan menjawabnya jika didalam penjelasan ini terdapan penulisan yang
kurang jelas, dan jika ada masukan, saran ataupun kritik dengan lapang dada saya
akan menerimanya dan memebenahinya. Semoga berguna. :)
