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])
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
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, Untuk
penjelasan ketiga fungsi tersebut baca posting tentang “Fungsi Left, Mid dan
Right pada Ms. Excel”
• 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 *Penjelasan Fungsi Value dapat anda baca pada “Fungsi Value pada Ms. Excel”
• 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,
• Pertanyaan :
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?
• Jawaban :
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” untuk penjelasan fungsi
tersebut silahkan cari di kategori Excel pada blog ini
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
Untuk penjelasan fungsi IF dan IFERROR silahkan lihat pada kategori Excel di blog ini
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
1 komentar:
1xbet korean | Live Online Betting | Sports Betting | Betting
Onexbet korean is the sports скачать 1xbet betting app for South Korea. It is licensed by the Kahnawake Gaming Commission.
Posting Komentar