Matched Content

Tuesday, May 8, 2012

Fungsi VLOOKUP Pada Microsoft Excel Dan Contohnya


Banyak pembaca yang ingin tahu tentang rumus VLOOKUP pada Program Inventori Kontrol. Karena ingin menyesuaikan program tersebut agar lebih cocok dengan kebutuhan mereka. Banyak halaman di internet membahas rumus pada software Excel ini, tapi hanya beberapa yang memberikan contoh penerapan.

Rumus VLOOKUP berfungsi untuk mencari suatu referensi atau acuan tertentu pada data di kolom pertama tabel, lalu mengambil data lain di baris yang sama dengan baris data referensi tapi pada kolom berbeda berdasar nomor index kolom.

Sebagai contoh: ada suatu tabel dimana terdapat nomor telpon, nama dan alamat, sebagaimana gambar dibawah. Nomor telpon digunakan sebagai patokan atau referensi. Jika pada sel kuning (B2) diinput nomor telpon, maka pada sel C2 akan muncul nama, dan pada sel D2 akan muncul alamat.


Pada sel C2 terlihat rumus yang digunakan yaitu =VLOOKUP(B2,B4:D7,2,0). Bagian-bagian rumus tersebut dijelaskan berikut ini dengan ditandai huruf merah:

=VLOOKUP(B2,B4:D7,2,0).
Referensi pada rumus itu adalah sel B2, sel kuning, yang berisi nomor telpon 08111011. Nomor telpon tersebut akan dicari siapa pemiliknya, dan dimana alamatnya.

=VLOOKUP(B2,B4:D7,2,0).
Rumus akan mencari data yang sama dengan sel B2 pada kolom B dari tabel yang tertulis pada daerah atau range (B4:D7).

=VLOOKUP(B2,B4:D7,2,0).
Rumus menemukan data 08111011 pada sel B6, lalu mengambil data di kolom kedua (2) dari tabel, maka akan didapat data Alex.

=VLOOKUP(B2,B4:D7,2,0).
Angka 0 pada bagian akhir rumus dinamakan range_lookup yang menentukan tingkat akurasi, jika ditulis satu (1) maka rumus akan mencari yang mirip dengan data referensi 08111011, jika ditulis 0 maka rumus hanya mencari data yang sama dengan data referensi 08111011.


Terlihat di bawah adalah rumus dengan range_lookup menggunakan 1 menggantikan 0. Tampak data referensi adalah nomor telpon 08111015, yang sebenarnya tidak ada di tabel nomor telpon berekor 5. Tapi rumus mencari yang mirip yaitu 08111012, dan itu adalah nomor telpon Tina. Sedangkan rumus VLOOKUP pada alamat (Address) tidak menggunakan 1 pada range_lookup tapi menggunakan nol (0), sehingga rumus tidak menemukan data yang cocok (#N/A).


Sebagaimana rumus pada software Excel lainnya, rumus ini dapat tulis langsung pada sel. Tapi agar lebih jelas, berikut adalah langkah-langkah yang diperlukan untuk mengaplikasikan rumus VLOOKUP dengan menggunakan prosedur normal. Tampak pada gambar dibawah mouse (panah merah) menunjuk pada tombol fx (Insert Function) dari Formula Bar. Akan dicantumkan rumus VLOOKUP pada sel C2.


Setelah tombol fx ditekan maka akan muncul tampilan Insert Function sebagaimana gambar dibawah.



Jika rumus tidak terlihat di jendela Select a function, ketik vlookup pada jendela Search for a function lalu tekan Go. Setelah muncul rumus VLOOKUP pada jendela Select a function, terlihat diatas berlatar biru, tekan tombol OK.

Selanjutnya akan muncul jendela Function Arguments, seperti pada gambar dibawah. Sebagai Lookup_value adalah sel B2, yaitu nilai yang akan dicari pada tabel atau bisa disebut nilai referensi.



Table_array adalah tabel yang akan diambil datanya, yaitu B4:D7, judul tabel boleh tidak dimasukkan.

Col_index_num adalah posisi kolom dimana data akan diambil, yaitu kolom ke dua dari tabel, maka pada rumus ditulis 2.

Range_lookup adalah 0 atau FALSE, sehingga rumus hanya akan mencari data yang benar-benar sama dengan data referensi.

Selanjutnya tekan tombol OK maka rumus akan tertulis dan berfungsi pada sel C2. Lakukan hal yang sama untuk sel D2 agar rumus mencari alamat (Address) dari nomor telpon referensi.

Perlu diperhatikan bahwa nomor telpon diawali dengan angka 0 untuk kode area atau nomor handphone / cellular phone / ponsel . Pastikan kolom atau sel yang tertulis nomor telpon diformat dalam bentuk Text. Jika diformat dalam sebagai General, maka akan dikenali sebagai angka, sehingga tidak bisa menuliskan angka 0 di depan.




Program Inventori Kontrol

Pada lembar dari software Excel untuk Program Inventori Kontrol, rumus VLOOKUP digunakan untuk mengambil data Deskripsi dan Lokasi, dengan Part Number sebagai referensi atau acuan.



Terlihat di Formula Bar rumus VLOOKUP menggunakan referensi sel B4 atau Part Number, membaca data pada tabel di sheet ‘register’ di kolom D sampai kolom G yang mana tertulis pada rumus sebagai register!D:G.

Di sheet register, kolom yang diambil datanya adalah kolom ke dua yaitu kolom E, terlihat pada rumus tertulis 2.

Dengan akurasi data yang harus benar-benar sama dengan data referensi maka pada rumus Range_lookup tertulis 0.

Program ini dapat digunakan untuk keperluan bisnis seperti memantau stok di gudang, toko, bisnis online, check harga barang dan lain-lain. Dengan sedikit modifikasi maka dapat digunakan untuk mencatat data karyawan, data siswa, dengan nomor induk sebagai referensinya.



Absensi Karyawan

Bagi anda yang menjalankan bisnis atau bagian personalia yang perlu memantau kehadiran karyawan, program berikut dapat membantu kerja anda. Pada lembar Excel ini, terdapat contoh rumus VLOOKUP yang digunakan untuk membaca nomor identitas karyawan (ID), lalu rumus menemukan dan menampilkan nama karyawan yang sesuai dengan nomor identitas tersebut. Dengan menggunakan nomor identitas maka pencatatan jam kerja karyawan akan jauh lebih mudah dan cepat. Tidak perlu menulis setiap nama terus menerus, yang dapat menyebabkan terjadinya salah ketik. Sel-sel yang mengandung rumus diberi warna biru.


Terlihat digambar atas formula VLOOKUP pada sel C13, yang membaca ID karyawan di sel B13 dan menulis pada sel C13 sebagai Margareth Isabella Taylor. ID dan nama karyawan sudah didaftarkan pada tabel REGISTER dipojok kiri atas. Tabel REGISTER inilah yang dilihat oleh rumus VLOOKUP untuk mendapatkan nama karyawan berdasarkan ID.

Input data pada tabel absensi adalah: tanggal, ID karyawan, jam masuk kerja, jam keluar untuk makan siang, jam masuk setelah makan siang, dan jam pulang kerja. Keterangan dapat ditambahkan jika ada data yang tidak normal.

Pada tabel jam kerja sudah diberi Conditional Formatting, yang akan merubah warna huruf dan angka jika sesuai kondisi tertentu secara otomatis. Sehingga jam masuk (IN) akan merah jika lebih dari jam 8:00. Jam keluar makan siang (OUT for lunch) akan merah jika kurang dari jam 12:00. Jam masuk setelah makan siang (IN after lunch) akan merah jika lebih dari jam 13:00. Dan jam pulang (OUT) akan merah jika kurang dari jam 17:00. Waktu total kerja (TOTAL) akan merah jika kurang dari 8:00.


Pada gambar diatas, terlihat panah biru pada sel I4 dimana tertulis rumus VLOOKUP dan digunakan untuk mencheck nama seorang karyawan dengan nomor K0004, dan rumus menemukan nama Michael Shoemaker. Lalu pada sel J4 terdapat rumus SUMIF yang menjumlah total jam kerja untuk Michael Shoemaker yaitu 17:09.

Pada gambar diatas juga terlihat panah kuning yang menunjukkan TOTAL dengan memakai rumus SUBTOTAL. Nilai ini adalah jumlah dari nilai di tabel pada kolom TOTAL dan akan berubah bergantung pada filter yang terapkan. Karena filter hanya menampilkan K0004, maka nilai pada sel L12 adalah 17:09 yaitu total untuk Michael Shoemaker.

Contoh sederhana dari rumus VLOOKUP yang digunakan untuk daftar nomor telpon sebagaimana dijelaskan diawal, juga tercantum pada buku dari software Excel tersebut pada sheet simple.


Klasemen Kompetisi
Pada kompetisi seperti olahraga sepak bola, futsal, bulutangkis, voli, basket, maupun kompetisi lainya. Untuk membuat tabel peringkat peserta hasil kompetisi tersebut dapat dibaca di "Belajar Rumus Rank Untuk Kompetisi Olahraga". Tabel yang dicontohkan juga menggunakan rumus VLOOKUP.

19 comments:

  1. Infonya bermanfaat sekali, tq..

    ReplyDelete
    Replies
    1. trimakasih kembali...ini sbenarnya cuma sebagian kecil dr kemampuan excel yg sangat berguna dan mudah diaplikasikan

      Delete
  2. mas, kalo bikin klasemen sepakbola rumus nya gimana ya? mungkin bisa di share kalo mas tau :)

    ReplyDelete
    Replies
    1. Bisa diperjelas format data yang dibutuhkan?...Supaya nanti kalau ada waktu saya buatkan rumusnya

      Delete
    2. Untuk perhitungan klasemen olahraga (sepakbola, futsal, badminton, voli, pingpong, dll) juga dapat diterapkan untuk kompetisi lainnya, dengan rumus RANK dan VLOOKUP sudah ada diartikel:

      http://maruzar.blogspot.com/2013/04/belajar-rumus-rank-untuk-kompetisi.html

      Delete
  3. makasih :)
    http://pendidikantech.blogspot.com

    ReplyDelete
  4. siang gan,
    gan mw nanya, q kan pke rumus hlookup/vlookup akan tetapi kodenya campuran karakter dan angka/numerik. sprt ini :

    kode
    123/A/C
    321/B/B
    213/C/A

    kalo utk kode A atau C (huruf) sy bisa kerjainnya, rumusnya seperti ini ( =HLOOKUP(MID(A5;5;1);F19:I20;2) )atau ( =HLOOKUP(RIGHT(A5;1);K19:N20;2) ). tapi ketika saya ambil kode yg angka sy gunakan rumus diatas tdk mau y gan, knp y? klo emang menggunakan ‘char’ keterangan rumus lengkapnya gmn y? tks gan
    Reply

    ReplyDelete
    Replies
    1. Hai Irul,
      Sepertinya tulisan rumus salah, tanda titik koma harus ganti jadi koma

      Data yang didapat dari rumus MID akan dikenal sebagai text, walau sebenarnya angka. Jika patokan (Lookup_value) yang dicari HLOOKUP adalah angka, maka tidak akan ketemu. Mengubah format sel tidak akan membantu. Hal ini juga menjelaskan kenapa untuk kode A atau C (huruf) tidak error.

      Ada 2 solusi:
      1. membuat rumus MID pada sel tersendiri, tidak digabung HLOOKUP. Pada sel tersebut angka yang didapat rumus MID dikalikan 1, agar dikenal sebagai angka bukan text. Baru kemudian menggunakan HLOOKUP pada sel lain, dengan berpatokan sel yang sudah berisi angka (sudah dikali 1) dari rumus MID tersebut.

      Bisa saja rumus MID tetap digabung dalam HLOOKUP dan langsung dikalikan 1. Tapi untuk mencegah timbulnya masalah jika database dibuka di Excel versi berbeda di komputer lain, maka sebaiknya rumus MID dipisah dari HLOOKUP. Memisahkan rumus MID pada sel tersendiri akan memudahkan troubleshooting.

      2. mengetik angka pada tabel (Table_array) yang dicari persamaanya oleh rumus HLOOKUP, dengan menambahkan tanda petik (') sebelum angka di tabel tersebut. Sehingga angka pada tabel juga akan dikenal sebagai text, maka HLOOKUP melihatnya sama dengan hasil dari rumus MID. Tapi hal ini pada prakteknya sulit karena tanda petik tidak terlihat ditampilan Excel sehingga dapat membingungkan orang lain yang menggunakan database.

      Smoga membantu

      Delete
  5. makasih yach sob , super bermanfaat bgt..

    ReplyDelete
  6. trims ilmunya, bermanfaat buat saya yg lg belajar excel

    ReplyDelete
  7. sangat bermanfaat. salam kenal bang.

    ReplyDelete
  8. Mas gimana solusinya
    Saya coba ketik kode barang, muncul nama barang + harganya.
    Tapi ada satu kode barang yg saya ketik, yg muncul hanya nama barang, harga barangnya gak muncul.
    Itu gimana ya ?
    Terima kasih

    ReplyDelete
    Replies
    1. coba check format cell harga barang yang tidak muncul, mungkin formatnya berbeda sendiri, harus disamakan dengan yang lain, yang bisa muncul harganya..

      Delete
  9. Artikel yg sangat bermanpaat ,makasih telah berbagi semoga berkah,

    ReplyDelete
  10. saya coba bikin persediaan pakai rumus vlookup berdasarkan kode barang, tapi kenapa ya ada yang keluar sesuai barangnya tapi banyak yang keluar tidak sesuai barangnya.. makasih

    ReplyDelete
    Replies
    1. Mungkin karena Range_lookup tidak 0 atau FALSE, jika 0 maka rumus hanya akan mencari data yang benar-benar sama dengan data referensi.fornat pada kode barang harus text, format general biasanya membingungkan hasilnya.

      Delete

Your positive comment will be highly appreciated to improve this site