Mengenal Fungsi pada Excel

Fungsi VLOOKUP

VLOOKUP termasuk kedalam salah satu fungsi yang paling banyak digunakan dalam

aplikasi Excel. Sesuai dengan nama dasarnya yaitu lookup yang bisa diartikan melihat atau

mencari, maka fungsi ini akan menghasilkan suatu formula untuk mengisi data pada tabel

berdasarkan data pada tabel lainnya atau tabel referensi tertentu dengan menggunakan suatu

nilai kunci yang spesifik. Awalan huruf V didepan kata lookup merupakan singkatan dari kata

Vertical. Istilah vertical ini merujuk pada bentuk tabel referensi yang digunakan, dimana judul

kolomnya terletak dibagian atas dan data-datanya tersusun kebawah secara vertikal. Contoh

sebuah tabel vertical ini bisa dilihat pada gambar berikut ini.

Aturan penulisan VLOOKUP mungkin bisa dibilang agak rumit bagi yang baru saja

mempelajari penggunaan formula pada Excel. Namun jika sudah terbiasa, aturan ini

sebetulnya bisa dibilang sederhana, yaitu:

=VLOOKUP(nilai_kunci;range_tabel_referensi;no_index_kolom;tipe_data)

Penjelasan aturan penulisan tersebut sebagai berikut:

nilai_kunci: Adalah nilai yang dijadikan acuan untuk membaca tabel referensi. Nilai ini harus ada baik

pada tabel yang akan diisi maupun pada tabel referensi.

range_tabel_referensi: Adalah range dari tabel yang berisikan data referensi untuk mengisi hasil yang

diharapkan. Pastikan bahwa range yang Anda pilih tidak menyertakan judul kolomnya.

no_index-kolom: Adalah nomor urut data dalam tabel referensi yang akan dituliskan hasilnya. Dimulai

dari kolom paling kiri pada tabel referensi tersebut dengan nomor index 1, dan seterusnya.

tipe_data: Ada 2 jenis tipe data yaitu TRUE dan FALSE. Nilai TRUE Anda gunakan jika nilai datanya tidak pasti atau berada pada range tertentu dan nilai FALSE Anda gunakan jika nilai data

berharga pasti.

Agar lebih jelas bagaimana cara menggunakan fungsi VLOOKUP ini, perhatikan contoh

berikut ini.

Pada contoh tersebut terdapat 2 buah tabel. Data pada tabel Laporan Penjualan jelas akan

selalu bertambah tiap harinya. Setiap kali ada pembeli maka Anda tinggal mengisikan data

tanggal, nama pembeli serta kode voucher-nya. Sementara data Voucher serta Harga akan

terisi secara otomatis tiap kali Anda mengisikan data pada kolom Kode. Pengisian data secara

otomatis ini bisa dicapai dengan melihat tabel referensi yaitu tabel Stok Gudang dan tentunya

dengan menggunakan formula VLOOKUP.

Langkah pertama akan diisi dahulu data pada tabel Voucher yaitu data pada sel E12. Untuk

mempermudah pembahasan, aturan penulisan VLOOKUP kembali dituliskan sebagai berikut:

E12=VLOOKUP(nilai_kunci;range_tabel_referensi;no_index_kolom;tipe_data)

nilai_kunci yang digunakan adalah data pada kolom Kode dalam tabel Laporan Penjualan,

yaitu sel D12. Alasannya karena Voucher akan bisa terisi dengan bersandar pada data dalam

kolom Kode tersebut.

Dengan demikian formulanya menjadi:

E12=VLOOKUP(D12;range_tabel_referensi;no_index_kolom;tipe_data)

Kemudian range_tabel_referensi jelas adalah range data pada tabel Stok Gudang yaitu

B4:D7.

Dengan demikian formulanya menjadi:

E12=VLOOKUP(D12;B4:D7;no_index_kolom;tipe_data)

no_index_kolom Anda tentukan dengan melihat didalam range_tabel_referensi. Dalam hal

ini Anda akan mengisi data pada kolom Voucher maka Anda lihat data untuk Voucher

tersebut didalam range_tabel_referensi berada pada kolom keberapa?

Dari gambar diatas dapat dilihat bahwa data yang akan diambil berada pada kolom ke-2, maka

no_index_kolom yang digunakan adalah 2. Dengan demikian formulanya menjadi:

E12=VLOOKUP(D12;B4:D7;2;tipe_data)

Yang terakhir adalah tipe_data. Disini yang akan digunakan adalah FALSE. Penjelasannya

adalah karena data tersebut bersifat pasti, misalnya kode S sudah pasti untuk Simpati, kode E

sudah pasti untuk Esia, dan demikian seterusnya. Dengan demikian formula lengkap untuk

mengisi data pada kolom Voucher tersebut adalah:

E12=VLOOKUP(D12;B4:D7;2;FALSE)

Untuk mengisi data pada sel selanjutnya yaitu sel E13 dan E14 seperti biasanya Anda dapat

menggunakan Auto Fill. Namun perhatikan bahwa tabel referensi yang menjadi acuan

memiliki range alamat sel yang statis alias tetap. Oleh karenanya, sebelum Anda menjalankan

proses Auto Fill maka Anda harus mengunci terlebih dahulu range tabel referensi tersebut

agar menjadi sel yang absolut. Hingga formulanya akan menjadi:

E12=VLOOKUP(D12;$B$4:$D$7;2;FALSE)

Dan proses Auto Fill kini dapat dijalankan untuk mengisi data pada sel-sel berikutnya.

Berikutnya, bagaimana mengisi data pada kolom Harga yaitu sel F12 hingga F14? Formula

yang digunakan tentunya tetap sama hanya saja no_index_kolom yang berbeda yaitu 3

karena data yang akan diisikan diambil dari kolom ke-3 pada tabel referensi.

Dan berikut ini formula yang digunakan setelah menggunakan sel absolut untuk range tabel

referensinya.

F12=VLOOKUP(D12;$B$4:$D$7;3;FALSE)

Pada contoh diatas Anda telah melihat penggunaan tipe data FALSE yaitu tipe data yang

pasti. Untuk tipe data TRUE aturan penulisan formula-nya tetap sama, hanya saja data yang

akan diisikan adalah data yang tidak pasti atau berada pada range tertentu. Contoh untuk

penggunaan tipe data TRUE ini bisa dilihat pada tabel berikut ini.

Anda harus mengisikan formula VLOOKUP pada kolom Nilai dalam tabel Daftar Nilai

Siswa, yaitu sel E13 hingga sel E15. Nilai yang akan diisikan tersebut mengacu pada range

tertentu dalam tabel referensi, yaitu:

Nilai E: Score 0 s/d 39

Nilai D: Score 40 s/d 59

Nilai C: Score 60 s/d 79

Nilai B: Score 80 s/d 89

Nilai A: Score 90 s/d 59

Kondisi ini jelas menggambarkan sebuah data yang tidak pasti oleh karenanya tipe data yang

digunakan adalah TRUE. Dan formula akhir yang digunakan adalah sebagai berikut.

=VLOOKUP(D13;$B$4:$C$8;2;TRUE)

Yang harus Anda perhatikan untuk tipe data TRUE ini adalah urutan data pada tabel referensi

harus menaik atau tersusun dari data terkecil hingga data terbesar

Fungsi HLOOKUP

HLOOKUP adalah varian lain dari fungsi VLOOKUP yang telah dibahas pada artikel

sebelumnya. Kegunaannya juga sama yaitu untuk mengisi data pada tabel berdasarkan data

pada tabel lainnya atau tabel referensi tertentu dengan menggunakan suatu nilai kunci yang

spesifik. Perbedaannya dengan VLOOKUP ditunjukan oleh awalan huruf H yang berarti

Horizontal, artinya tabel referensi yang digunakan berbentuk horisontal, dimana judul

kolomnya terletak dibagian kiri dan data-datanya tersusun kekanan dalam arah horisontal.

Contoh sebuah tabel horisontal bisa dilihat pada gambar berikut ini.

Aturan penulisan HLOOKUP juga sama dengan VLOOKUP, namun ada sedikit perbedaan

yaitu pada no index-nya. Jika pada VLOOKUP no index mengacu kepada kolom

(no_index_kolom) maka pada HLOOKUP mengacu pada baris (no_index_baris), hal ini

dikarenakan datanya yang memang tersusun dalam suatu baris.

Berikut ini aturan penulisannya:

=HLOOKUP(nilai_kunci;range_tabel_referensi;no_index_baris;tipe_data)

Penjelasan aturan penulisan tersebut sebagai berikut:

nilai_kunci: Adalah nilai yang dijadikan acuan untuk membaca tabel referensi. Nilai ini harus ada baik

pada tabel yang akan diisi maupun pada tabel referensi.

range_tabel_referensi: Adalah range dari tabel yang berisikan data referensi untuk mengisi hasil yang

diharapkan. Pastikan bahwa range yang Anda pilih tidak menyertakan judul barisnya.

no_index_baris: Adalah nomor urut data dalam tabel referensi yang akan dituliskan hasilnya. Dimulai

dari baris paling atas pada tabel referensi tersebut dengan nomor index 1, dan seterusnya.

tipe_data: Ada 2 jenis tipe data yaitu TRUE dan FALSE. Nilai TRUE Anda gunakan jika nilai datanya tidak pasti atau berada pada range tertentu dan nilai FALSE Anda gunakan jika nilai data berharga

pasti.

Sebagai contoh untuk penggunaan HLOOKUP ini, perhatikan tabel berikut.

Pada sel C8 dalam tabel Cek Stok, digunakan formula HLOOKUP agar jika Anda ketikan

nama Produk pada sel B8 maka otomatis ditampilkan jumlah Stok yang tersedia dengan

mengacu pada tabel referensi Stok Gudang. Dengan demikian maka formula yang digunakan

adalah sebagai berikut.

C8=HLOOKUP(B8;C3:E4;2;FALSE)

Penjelasan dari formula tersebut adalah:

B8 adalah nilai_kunci yang digunakan karena jumlah Stok akan ditampilkan jika nama Produk dalam

sel B8 tersebut diisi.

C3:E4 adalah range_tabel_referensi yang digunakan sebagai acuan pengisian jumlah Stok pada tabel

Cek Stok.

no_index_baris adalah 2 karena yang akan dituliskan pada tabel Cek Stok diambil dari baris kedua

pada range_tabel_referensi.

Tipe data yang digunakan adalah FALSE karena datanya bersifat pasti.

Dalam contoh ini tidak digunakan Sel Absolut karena data-nya hanya satu hingga tidak perlu

melakukan proses Auto Fill.

Contoh Penerapan

Untuk lebih memahami bagaimana formula diterapkan dalam aplikasi Excel juga sebagai

gambaran bagi Anda yang mungkin baru saja mempelajari tentang formula pada Excel,

berikut ini contoh beberapa kasus yang mudah-mudahan bisa digunakan sebagai bahan

pembelajaran.

Contoh 1: Laporan Persentase Komisi Sales

Pada tabel berikut ini Anda diminta untuk mengisi kolom Persentase Komisi serta Jumlah

Komisi, dimana jumlah komisi tersebut dihitung berdasarkan Masa Kerja dan Nilai

Penjualan. Tabel Data Perhitungan Komisi Sales digunakan sebagai acuan atau referensi

untuk menyelesaikan perhitungan.

Solusinya, karena ada tabel lain sebagai acuan maka formula jelas akan melibatkan fungsi

VLOOKUP. Namun pada tabel referensi tersebut ada 2 kondisi untuk masa kerja hingga

fungsi IF harus digunakan juga. Dengan demikian formula yang digunakan adalah:

D4=VLOOKUP(C4;$A$12:$C$16;IF(B4<=3;2;3);TRUE)

E4=D4*C4

Contoh hasil akhirnya bisa dilihat pada gambar berikut ini.

Contoh 2: Menghitung Total Upah Karyawan

Pada tabel berikut ini Anda diminta untuk menghitung Waktu Kerja dan Total Upah

karyawan, dengan aturan jam kerja standar adalah 9 jam dengan upah Rp. 10.000 / jam.

Untuk karyawan yang jam kerjanya melebihi jam kerja standar (lembur), akan mendapat upah

Rp. 2.500 / jam

Solusi untuk kasus ini adalah menggunakan fungsi IF. Alasannya karena ada karyawan yang

mendapat upah standar saja dan ada karyawan yang mendapat upah standar plus upah lembur

juga. Selain fungsi IF Anda cukup menggunakan oparasi dasar matematika biasa. Kemudian

mengingat perhitungan matematika yang digunakan cukup kompleks, Anda boleh

menggunakan fungsi ROUNDDOWN agar nilai-nilai yang dihasilkan bulat. Dengan demikian

formula yang digunakan adalah:

D9=ROUNDDOWN((C9-B9)*24;2)

E9=IF(D9<=9;D9*$C$5;(9*$C$5)+(D9-9)*$C$6)

Contoh hasil akhirnya bisa dilihat pada gambar berikut ini.

Contoh 3: Mengurai Struktur NIM (Nomor Induk

Mahasiswa)

Pada tabel berikut ini Anda diminta untuk mengisi kolom Angkatan, Jurusan dan Fakultas

dengan fungsi yang sesuai. Namun pengisian kolom-kolom ini bersandar pada struktur

penulisan NIM yang jumlahnya 8 digit, yaitu:

Digit pertama menunjukan Fakultas

Digit ke-2 dan ke-3 menunjukan Jurusan

Digit ke-4 dan ke-5 menunjukan angkatan

Tiga digit terakhir menunjukan nomor urut pendaftaran

Khusus untuk mengisi Fakultas dan Jurusan, disediakan referensi dalam 2 tabel terpisah,

yaitu tabel Kode Fakultas dan tabel Kode Jurusan.

Solusinya, karena semua pengisian kolom bersandar pada struktur NIM dan tidak semua nilai

NIM tersebut digunakan melainkan hanya digit-digit tertentu saja, maka Anda bisa

menggunakan fungsi teks untuk mengambil beberapa karakter tertentu dari NIM tersebut.

Untuk mengisi kolom Angkatan pada sel C4, maka Anda harus menggunakan fungsi MID

karena berdasarkan struktur NIM, angkatan ini merupakan digit yang berada di tengah yaitu

digit ke-4 dan ke-5.

Untuk mengisi kolom Fakultas pada sel D4 serta kolom Jurusan pada sel E4, maka Anda

harus menggunakan fungsi VLOOKUP karena ada tabel referensi yang disediakan, namun

nilai kunci yang digunakan pada fungsi VLOOKUP tersebut menggunakan fungsi lain yaitu

fungsi LEFT untuk Fakultas dan dungsi MID untuk Jurusan.

Dengan demikian formula yang digunakan adalah:

C4=MID(A4;4;2)

D4=VLOOKUP(LEFT(A4;1);$A$11:$B$13;2;FALSE)

E4=VLOOKUP(MID(A4;2;2);$D$11:$E$16;2;FALSE)

Contoh hasil akhirnya bisa dilihat pada gambar berikut ini.

Contoh 4: Discount Penjualan Rokok

Pada tabel berikut ini Anda diminta untuk mengisi kolom Jumlah Harga, Discount, dan

Total Harga. Namun ada aturan yang harus dipenuhi yaitu untuk merk rokok Ardath,

Gudang Garam dan Jarum mendapat discount 5%. Sedangkan untuk merk-merk lainnya

tidak mendapat discount.

Solusinya, untuk kolom Jumlah Harga pada sel E4 dan Total Harga pada sel G4 hanya

merupakan operasi perkalian dan pengurangan biasa. Barulah pada kolom Discount Anda

harus menggunakan fungsi yaitu fungsi IF-OR. Alasannya karena perhitungan ini melibatkan

3 syarat atau kondisi. Dengan demikian formula yang digunakan adalah:

E4=D4*C4

F4=IF(OR(B4="ardath";B4="gudang garam";B4="jarum");5%;0)*E4

G4=E4-F4

Contoh hasil akhirnya bisa dilihat pada gambar berikut ini.

Contoh 5: Biaya Paket Wisata Harian

Pada tabel berikut ini Anda diminta untuk mengisi kolom Biaya Tambahan per Hari,

Tujuan Wisata, Harga Paket, Biaya Tambahan, serta kolom Jumlah Dibayar.

Biaya Tambahan per Hari pada sel G11 hanya merupakan operasi pembagian biasa dimana

Anda tinggal membagi Harga Paket dengan Lama Wisata.

G11=F11/E11

Lalu Tujuan Wisata pada sel D4 dan Harga Paket pada sel F4 bisa Anda kerjakan dengan

mudah menggunakan fungsi VLOOKUP dimana nilai kuncinya bersandar pada kolom Kode.

D4=VLOOKUP(C5;$C$11:$G$15;2;FALSE)

F4=VLOOKUP(C4;$C$11:$G$15;4;FALSE)

Formula yang cukup kompleks justru terdapat pada kolom Biaya Tambahan di sel G4.

Disini ada kondisi yang harus ditetapkan yaitu jika peserta berwisata dengan lama hari sesuai

dengan lama wisata paket atau dibawahnya maka peserta tidak dikenakan biaya tambahan.

Namun jika peserta berwisata dengan lama hari diatas lama wisata paket maka peserta akan

dikenakan biaya tambahan per hari. Biaya tambahan ini tentunya disesuaikan dengan

kelebihan hari yang diambil peserta tersebut.

Karena ada 2 buah kondisi maka fungsi utama yang digunakan jelas fungsi IF, namun tiap-

tiap argumen pada fungsi IF tersebut harus diurai dengan fungsi VLOOKUP mengingat

tujuan wisata yang bervariasi. Dengan demikian formula yang digunakan adalah:

G4=IF(E4>VLOOKUP(C4;$C$11:$G$15;3;FALSE);(E4-

VLOOKUP(C4;$C$11:$G$15;3;FALSE))*VLOOKUP(C4;$C$11:$G$15;5;FALSE);0)

Dan yang terakhir adalah kolom Jumlah Dibayar pada sel H4. Formula yang digunakan

disini hanya merupakan operasi penjumlahan biasa dimana Anda tinggal menambahkan

Harga Paket dengan Biaya Tambahan.

H4=F4+G4

Contoh hasil akhirnya bisa dilihat pada gambar berikut ini.

Demikian beberapa contoh kasus terkait dengan penerapan formula pada aplikasi Excel yang

mungkin saja Anda temukan di lingkungan kerja Anda. Dan seperti Anda lihat bahwa formula

ini bisa menyelesaikan beragam perhitungan secara semi otomatis pada tabel-tabel data yang

sederhana hingga yang kompleks.

Catatan:

Penerapan formula pada artikel ini dibahas secara garis besar dan tidak terlalu detail.

Tujuannya agar Anda bisa melakukan analisa sendiri terkait dengan formula dan fungsi yang

digunakan tersebut.

Formula yang dituliskan pada artikel ini juga hanya formula untuk sel-sel pada baris teratas

saja karena untuk mengisi sel-sel berikutnya seperti biasa Anda dapat menggunakan proses

Auto Fill serta bantuan Sel Absolut.

Daftar Pustaka

Aris Lesmana, 2013, Mengenal Formula pada Excel - Part 24: Fungsi VLOOKUP (id-ID),

http://social.technet.microsoft.com/wiki/contents/articles/16485.mengenal-formula-

pada-excel-part-24-fungsi-vlookup-id-id.aspx diakses tanggal 12-11-2014 jam

14:15)

Aris Lesmana, 2013, Mengenal Formula pada Excel - Part 25: Fungsi VLOOKUP (id-ID),

http://social.technet.microsoft.com/wiki/contents/articles/16560.mengenal-formula-

pada-excel-part-25-fungsi-hlookup-id-id.aspx diakses tanggal 12-11-2014 jam

14:15)