Penjelasan
Mengenai Fungsi VLOOKUP dan HLOOKUP disertai Gambar
Dengan
banyaknya pertanyaan masuk di kolom komentar mengenai fungsi vlookup dan
hlookup, pada artikel cara menggunakan rumus fungsi
vlookup dan hlookup pada microsoft excel. Akhirnya, kesempatan kali
ini saya akan coba membahas kembali materi vlookup hlookup tersebut. Tulisan
pada artikel kali ini, saya ambil dari contoh soal sebelumnya dengan penjelasan
yang saya harapkan lebih komprehensif (lengkap) dengan disertai gambar
tentunya.
Mengenal Fungsi Lookup
Vlookup
dan Hlookup merupakan bagian dari fungsi Lookup. Lookup digunakan untuk mencari
nilai pada baris atau kolom Tertentu.
Vlookup
digunakan untuk mencari nilai dalam bentuk vertikal, sesuai dengan awalan huruf
V yang merupakan singkatan dari Vertikal/tegak Lurus. Sedangkan Hlookup yang
berawal Huruf H digunakan untuk mencari nilai dalam bentuk baris /horizontal.
Pembahasan VLOOKUP
Penggunan
istilah vertikal pada VLOOKUP digunakan untuk merujuk pada bentuk tabel
referensi yang digunakan, dimana judul kolomnya terletak dibagian atas dan
data-datanya tersusun ke bawah secara vertikal. Lihat gambar dibawah ini:
Untuk
penulisan Fungsi VLOOKUP, Formula bakunya adalah:
=VLOOKUP(lookup_value,table_array,
col_index_num,[range_lookup])
Atau
bisa juga dibaca seperti ini:
=VLOOKUP
(nilai_kunci, tabel_data, nomor_kolom,tipe_data)
Penjelasan
dari fungsi VLOOKUP diatas
·
Nilai_kunci: merupakan nilai yang diajukan untuk
membaca tabel referensi. Nilai kunci harus ada pada tabel yang akan disi maupun
pada tabel referensi.
·
Tabel_data: merupkan range data yang disusun tegak
atau vertikal yang berfungsi sebagai tabel bantu/referensi yang akan dibaca.
range pada tabel data berisikan data-data yang digunakan untuk mengisi
hasil yang diharapkan. Saat anda mengambil tabel_data, pastkan anda tidak
menyertakan judul kolomnya.
·
Nomor_kolom: merupakan nomor urut kolom untuk
pembacaan tabel referensi, dimulai dari kolom paling kiri. Dimulai dengan index
ke satu atau nomor 1 , dan seterusnya.
·
Tipe data: untuk tipe data ada 2 Jenis, yaitu:
True digunakan jika nilai datanya tidak pasti atau berada range tertentu
dan tipe data False digunakan jika nilai datanya itu pasti.
Tipe data TRUE, bisa
juga digantikan dengan angka 1, Sedangkan angka 0 untuk tipe data FALSE. Bila
penulisan tipe_data ini tidak anda gunakan/hilangkan. Maka otomatis fungsi
Vlookup yang anda gunakan mengacu pada tipe Data True.
Saran..
Gunakan tipe data, dalam penulisan formula VLOOKUP.
Oke,
setelah pemahaman diatas sudah anda pahami, Lanjut ke pengerjaan soal excel
menggunakan FUNGSI VLOOKUP. Untuk memudahkan pengerjaan, silahkan ketikkan
soalnya seperti gambar dibawah ini:
Pada
contoh soal gambar di atas, ada 3 buah tabel yang bisa anda lihat, dimana tabel
bagian operator merupakan tempat dimana anda harus menyelesaikan atau
mengisi soal. Lalu ada tabel referensi untuk membantu anda
menyelesaikan soal tersebut, yaitu tabel gaji dan tabel
potongan pajak.
#1.
Menghitung Gaji Pokok
Untuk
menghitung gaji pokok pada tabel bagian operator sesuai soal di atas, anda
memerlukan fungsi VLOOKUP untuk mengambil data yang berbentuk vertikal pada
tabel gaji.
Silahkan
letakkan kursor di Cell D5 dengan menuliskan formula VLOOKUP
Ingat
penulisan fungsi VLOOKUP
D5=VLOOKUP
(nilai_kunci, tabel_data, nomor_kolom,tipe_data)
Nilai
kunci yang digunakan untuk pengisian gaji pokok adalah data pada kolom gol dalam
tabel bagian operator, yaitu sel B5. Mengapa harus menjadikan
kolom gol sebagai nilai kunci? Ya, karena kolom gaji pokok
akan bisa terisi dengan bersandar pada data dalam kolom gol tersebut.
Intinya
: Nilia kunci ini harus ada, diantara tabel yang akan diisi dan tabel
referensi, lihat gambar berikut:
http://umardanny.com/penjelasan-mengenai-fungsi-vlookup-dan-hlookup-disertai-gambar/
Sehingga
penulisan formulanya menjadi seperti ini:
D5=VLOOKUP
(B5, tabel_data, nomor_kolom,tipe_data)
Lanjut
ke pengisian tabel_data yang datanya diambil dari tabel referensi, yaitu tabel
gaji yang terletak pada range B14:E16 (dibaca: B14 sampai E16).
Sesuai
dari pengambilan tabel_data gambar di atas, dengan memblok area B14 sampai E16,
maka penulisan formulanya akan menjadi seperti ini:
D5=VLOOKUP
(B5, B14:E16, nomor_kolom,tipe_data)
Jangan
lupa menambahkan tanda $ pada sel/range untuk mengabsolutkan atau mengunci.
Tujuannya apa? Agar saat di drag/copy ke bawah untuk mengisi jawaban kolom gaji
pokok di bawahnya, hasilnya tetap muncul, tidak menjadi #N/A.
Atau
langkah mudahnya menekan tombol F4 pada keyboard, setelah mengisi
B14:E16. Setelah ditambahkan tanda dollar ($), hasilnya akan tampak
seperti dibawah ini:
D5=VLOOKUP
(B5, $B$14:$E$16, nomor_kolom,tipe_data)
Oke,
lanjut ke pengisian nomor_kolom..
Untuk
mengisikan nomor_kolom, anda harus melihat data_tabel yang ada pada tabel
referensi yaitu: data_tabel pada tabel gaji. Silahkan anda lihat data untuk
gaji pokok terletak pada kolom keberapa pada tabel_data referensi..
Pada
gambar di atas, terlihat bahwa data yang akan anda ambil berada pada kolom ke
2, maka isilah nomor_kolom dengan angka 2. Sampai sejauh ini, penulisan VLOOKUP
nya menjadi seperti ini:
D5=VLOOKUP
(B5, $B$14:$E$16, 2,tipe_data)
Sekarang,
tinggal tipe_data yang belum anda isikan..
Untuk
tipe_data, gunakanlah FALSE. Mengapa harus FALSE? Karena datanya sudah pasti,
dimana Gol 1A gaji pokoknya sudah pasti 60,000, 1B gaji pokoknya sudah pasti
800,000 dan 1C gaji pokoknya sudah pasti 1,000,000.
Sampai
sejauh ini, proses pengerjaan untuk mengisi kolom gaji pokok di cell D5 telah
selesai, dengan penulisannya menjadi seperti ini:
D5=VLOOKUP
(B5,$B$14:$E$16, 2,FALSE)
Seperti
gambar dibawah ini:
http://umardanny.com/penjelasan-mengenai-fungsi-vlookup-dan-hlookup-disertai-gambar/
Untuk
mengisi kolom gaji pokok dibawahnya, anda tinggal copy/drag ke bawah, sehingga
hasil kolom gaji pokok keseluruhannya, seperti dibawah ini:
#2.
Menghitung Tunjangan
Untuk
menghitung kolom tunjangan pada cell E5 sampai E9, penulisan fungsinya sama
dengan pengerjaan menghitung gaji pokok, hanya saja nomor_kolom mesti anda
isikan dengan angka 3. Loh kok dengan angka 3 atau index ke 3? Ya, karena kalau
anda lihat pada tabel referensinya (tabel gaji pokok), tunjangan itu berada
pada kolom ke 3.
Penulisan
akhir pada kolom tunjangan akan menjadi seperti ini:
E5=VLOOKUP(B5,$B$14:$E$16,3,FALSE)
#3.
Menghitung Transportasi
Untuk
mengisi kolom transportasi dari cell F5 sampai F9, tidak perlu dijelaskan lagi
ya? Karena penjelasannya sama seperti sebelumnya, yang membedakan hanya
nomor_kolomnya saja, yaitu: 4
Hasil
pengerjaannya:
F5=
VLOOKUP(B5,$B$14:$E$16,4,FALSE)
#4.
Mengisi total Gaji
Untuk
mengisi kolom total Gaji dari cell G5 smpai G9, anda bisa menggunakan fungsi SUM.
Fungsi SUM digunakan untuk menjumlahkan data dari beberapa sel.
Untuk
kolom total gaji yang harus anda jumlahkan adalah gaji
pokok+tunjangan+transportasi.
Letakkan kursor di Cell G5, setelah itu ketik =SUM(selawal:sampaiakhir)
G5=SUM(D5:F5)
Artikel
lainnya: panduan belajar microsoft office
lengkap
Pembahasan HLOOKUP
Fungsi
HLOOKUP digunakan untuk mengisi data berdasarkan tabel referensi yang berbentuk
baris/horizontal.
Tentu
berbeda dengan penggunaan VLOOKUP yang telah kita bahas diatas, dimana HLOOKUP
judul kolomnya terletak dibagian kiri dan data-datanya tersusun ke kanan dalam
bentuk horizontal. Lihat gambar dibawah ini:
Pada
contoh soal pengerjaan ini, HLOOKUP digunakan untuk mengisi kolom pajak pada
tabel bagian operator dengan tabel referensinya terletak pada tabel potongan
pajak.
Aturan
penulisan HLOOKUP hampir sama dengan VLOOKUP, hanya berbeda pada bagian
nomor-indeksnya saja, kalau VLOOKUP menggunakan nomor indeks berdasarkan data
berbentuk kolom, HLOOKUP menggunakan nomor indeks berdasarkan data yang
berbentuk baris..
Lihat
gambar data yang berbentuk baris/horizontal dibawah ini:
Penulisan
fungsi HLOOKUP
=HLOOKUP(lookup_value,table_array,
row_index_num,[range_lookup])
Atau
bisa juga dibaca seperti ini:
=HLOOKUP
(nilai_kunci, tabel_data, nomor_baris, tipe_data)
Keterangan
penulisan HLOOKUP, diatas:
·
nilai_kunci : merupakan nilai yang dijadikan
acuan untuk membaca tabel referensi. Nilai ini harus pada tabel yang akan
diisi/diselesaikan maupun pada tabel referensi.
·
tabel_data : merupakan range dari tabel yang
berisikan data referensi untuk membantu mengisikan hasil yang diharapkan.
·
nomor_baris : merupakan nomor urut
data dalam tabel referensi yang akan dituliskan hasilnya. Dimulai dari baris
paling atas pada tabel referensi tersebut dengan nomor index 1 atau angka 1,
dan seterusnya.
·
tipe_data : ada 2 Jenis Tipe data yang bisa
anda digunakan, TRUE digunakan jika nilai datanya tidak pasti/berada pada range
tertentu dan tipe data FALSE untuk nilai data yang sudah pasti.
Oke,
lanjut ke pembahasan soal menggunakan HLOOKUP..
#5.
Menghitung Pajak
Untuk
menghitung pajak, letakkan kursor di Cell H5 pada tabel bagian operator, lalu
ketikan fungsi HLOOKUP…
H5=HLOOKUP(nilai_kunci,
tabel_data,nomor_baris,tipe_data)
Mengisi
nilai_kunci:
Proses
pengerjaannya hampir sama seperti anda mengerjakan soal yang berbentuk VLOOKUP
sebelumnya..karena yang dijadikan nilai_kunci adalah golongan… yang membedakan
adalah tabelnya. Dimana pada pengerjaan mengisi kolom pajak, anda menggunakan
tabel potongan pajak sebagai tabel referensinya.
Maka
penulisannya menjadi
H5=HLOOKUP(B5,tabel_data,nomor_baris,tipe_data)
Mengisi
tabel_data:
Untuk
tabel_data, bisa anda lihat pada gambar diatas, range tabel potongan pajak yang
harus anda anda blok areanya dari cell G13 sampai I14. Lalu jangan lupa
tambahkan tanda dollar/$ setelah anda memblok area G13:I14 atau tekan F4 pada
keyboard. Penulisan selanjutnya menjadi seperti ini:
H5=HLOOKUP(B5,$G$13:$I$14,nomor_baris,tipe_data)
Mengisi
nomor_baris:
Untuk
nomor baris dapat terlihat pada gambar di atas, bahwa potongan pajak terletak
pada pada baris kedua atau indeks yang kedua, silahkan anda isikan nomor_baris
dengan angka 2.
H5=HLOOKUP(B5,$G$13:$I$14,2,tipe_data)
Mengisi
tipe_data:
Untuk
tipe_data karena datanya sudah pasti maka silahkan anda gunakan tipe data
FALSE.
Sehingga
hasil penulisan fungsi HLOOKUP menjadi seperti ini:
H5=HLOOKUP(B5,$G$13:$I$4,2,FALSE)
Jangan
lupa menambahkan operator perkalian untuk mengetahui potongan pajak yang harus
dikeluarkan. dengan cara mengalikan formula di atas dan gaji pokok pada cell D5
Penulisannya:
H5=HLOOKUP(B5,$G$13:$I$4,2,FALSE)*D5
Tinggal
anda drag/copy ke bawah,dan hasil pengerjaan pengerjaan menghitung pajak
seperti gambar dibawah ini:
http://umardanny.com/penjelasan-mengenai-fungsi-vlookup-dan-hlookup-disertai-gambar/
#6.
Menghitung Gaji Bersih
Untuk
mengitung gaji bersih, gunakan operator pengurangan di Cell I5, dengan cara
total gaji dikurangi pajak pada tabel bagian operator.
Penulisannya
I5=G5-H5
Hasilnya,
tampak seperti dibawah ini:
7#.
Menghitung Total
Bagian
terakhir, adalah menghitung total dari semua proses pengerjaan yang tadi telah
Anda kerjakan dengan menggunakan fungsi SUM.
Letakkan
kursor di Cell D10, lalu isikan =sum(D5:D9), lihat hasilnya:
Selanjutnya tinggal
anda drag/copy ke arah samping untuk mengisi total yang lainnya seperti ini:
http://umardanny.com/penjelasan-mengenai-fungsi-vlookup-dan-hlookup-disertai-gambar/
Hasil
keseluruhan dari proses pengerjaan menggunakan fungsi VLOOKUP dan HLOOKUP
seperti tampak seperti gambar di atas..