Fungsi VLOOKUP
Fungsi VLOOKUP digunakan untuk mencari nilai di kolom yang
terletak paling kiri dari sebuah range, kemudian mengembalikan suatu nilai di
kolom sebelah kanan yang sudah ditentukan dan dalam baris yang sama.
Syntax : VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Ø
Lookup_value :
nilai yang akan dicari dan terletak di kolom paling kiri dari tabel array
Ø
Table_array :
range yang berisi beberapa kolom dan kolom yang terletak di paling kiri yang
akan digunakan sebagai acuan data
Ø
Col_index_num :
menentukan kolom yang akan digunakan untuk mengembalikan nilai jika nilai
lookup sesuai dengan paling kiri table array
Ø
Range_lookup bernilai TRUE atau FALSE, jika TRUE
maka excel akan mencari data yang sama atau mendekati sama dengan lookup_value,
jika FALSE maka excel akan mencari data yang benar – benar sama, jika tidak
ditemukan maka hasilnya bernilai #N/A, namun jika diabaikan akan bernilai TRUE
(bersifat optional)
Misal ada data seperti di bawah ini.
Ada data daftar gaji pegawai, tabel gaji serta tabel
potongan pajak. Sekarang kita akan
mengisi kolom gaji pokok, tunjangan dan transportasi pada data “Daftar Gaji
Pegawai” berdasarkan data pada “Tabel Gaji”, untuk menyelesaikannya kita akan
menggunakan fungsi hlookup.
- Lookup_value adalah kolom Golongan yang ada di data “Daftar Gaji Pegawai” kolom B
- Table_array adalah range yang berisi data gaji, terdapat di Tabel Gaji (range B15:E18)
- Col_index adalah informasi Gaji dari tiap – tiap Golongan di Tabel Gaji (kolom C, D dan E)
- Range_lookup isi dengan FALSE karena data tiap golongan harus benar – benar sama
Letakkan kursor di Cell D5 kemudian ketikkan formula berikut
:
=VLOOKUP(B5:B11;$B$15:$E$18;2;FALSE)
- Cell B5 adalah cell yang berisi data golongan di daftar gaji pegawai
- Range $B$15:$E$18 adalah data tabel gaji tempat informasi gaji tersimpan
- 2 adalah posisi kolom data gaji pokok di tabel gaji (kolom ke 2)
- FALSE, karena data harus benar – benar sama
Copy cell D5, kemudian paste ke range D6:D11 maka hasilnya
akan tampak seperti pada gambar, kolom Gaji Pokok sudah terisi.
Dengan cara yang sama, kita akan mengisi Kolom Tunjangan dan
Transportasi, yakni meletakkan kursor pada cell E5, kemudian ketikkan formula =VLOOKUP(B5;$B$15:$E$18;3;FALSE).
Copy cell D5, kemudian paste ke range D6:D11. Untuk mengisi kolom Transportasi
formulanya sama dengan kolom Tunjangan dan Gaji Pokok, perbedaannya terletak
pada Col_index, untuk data transportasi yang ada di Tabel Gaji terletak di
kolom ke – 4 sehingga kita isi col_index dengan angka 4, sehingga formula untuk
mengisi kolom transportasi adalah =VLOOKUP(B5;$B$15:$E$18;4;FALSE). Berikut
adalah gambar bahwa kolom Tunjangan dan Transportasi sudah terisi.
Untuk menghitung total gaji silahkan menggunakan formula
sederhana, yakni dengan rumus Gaji Pokok + Tunjangan + Transportasi.
Fungsi HLOOKUP
Fungsi HLOOKUP mirip dengan fungsi VLOOKUP, perbedaannya
hanya terletak pada orientasi data, jika fungsi VLOOKUP mencari data secara
vertikal (berdasarkan kolom), sedangkan HLOOKUP mencari data secara horizontal.
Dengan data yang sama dengan contoh sebelumnya, berikut
contoh penggunaan HLOOKUP dimana kita akan mencari total pajak yang harus
dibayar tiap pegawai berdasarkan data prosentasi yang terdapat pada Tabel
Potongan Pajak.
Letakkan kursor pada cell H5, kemudian ketikkan formula =HLOOKUP(B5;$G$14:$J$15;2;FALSE)*G5
- Cell B5 adalah cell yang berisi data golongan di daftar gaji pegawai
- Range $G$14:$J$15 adalah data tabel potongan pajak
- 2 adalah posisi baris data potongan pajak (baris ke 2)
- FALSE, karena data harus benar – benar sama
- *G5 = nilai yang dikembalikan oleh fungsi HLOOKUP dikalikan dengan nilai dari cell G5 yang berisikan total gaji, karena rumus dari pajak yang dibayar = prosentase pajak * total gaji
Copykan cell H5 kemudian paste di range H6:H11, maka
hasilnya seperti pada gambar.
No comments:
Write comments