Rabu, 12 Februari 2014

VLOOKUP

Artikel ini membahas lookup value, table array, dan range lookup yang merupakan argumen dari sintaks untuk VLOOKUP dan HLOOKUP.
Perbedaan penggunaan fungsi VLOOKUP dan HLOOKUP hanya pada susunan tabel. Bila tabel tersusun secara horizontal (kolom), maka kita menggunakan fungsi HLOOKUP. Dan bila tabel tersusun secara vertikal (baris), kita menggunakan fungsi VLOOKUP.
Cara Penulisan Sintaks:
  • =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
  • =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
Catatan:
Pemisah argumen pada tutorial ini menggunakan tanda titik koma (;). Pemisah argumen juga bisa menggunakan tanda koma (,). Ini tergantung pada pengaturan di komputer.

Contoh:
  • Menggunakan tanda titik koma: =VLOOKUP(B12;$A$3:$B$6;2;FALSE)
  • Menggunakan tanda koma: =VLOOKUP(B12,$A$3:$B$6,2,FALSE)
Berikut penjelasan detilnya.
Tentang Lookup_value

  • Lookup_value adalah sel referensi (misalnya sel A1) atau nilai yang dijadikan kunci dalam pencarian data. Nilai bisa berupa teks atau angka.

  • Lookup_value harus berada pada baris atau kolom pertama tabel penyimpan data.


 


  • Tidak berlaku case sensitive untuk lookup_value. Teks Jan, JaN, atau JAN dianggap sama oleh Excel.
  •  Lookup_value berupa teks harus diberi tanda petik dua (""). 
Contoh: =HLOOKUP("feb";$A$6:$J$7;2;FALSE).
Contoh berikut menggunakan lookup_value sel referensi A10. Keuntungan menggunakan sel referensi adalah apabila isi sel berubah, kita tidak perlu mengubah sintaksnya.
Contoh: Rate untuk Jan adalah 2,5. Bila kita ingin mendapatkan Rate untuk Feb, isi sel A10 cukup diganti ke Feb dan Rate pada sel B10 akan otomatis berubah menjadi 3 tanpa harus mengedit sintaks. 
 

Tentang Table_array
Table_array adalah tabel atau range yang menyimpan data yang ingin dicari.
Table_array bisa ditulis seperti berikut:
  1. =VLOOKUP(B12;A3:B6;2;FALSE)
    Table_array: A3:B6. Jika formula di-copy paste ke baris berikutnya, hasilnya bisa error. Untuk itu sebaiknya gunakan cara  b dan c.
  1. =VLOOKUP(B12;$A$3:$B$6;2;FALSE)
    Table_array $A$3:$B$6 menggunakan absolute reference (simbol $).
  1. =VLOOKUP(B12;Tabel_Komisi;2;FALSE)
    Table_array menggunakan nama (Tabel_Komisi).
    Cara memberi nama: pilih range (kumpulan sel) dan klik kanan. Pilih Name a Range dan ketik nama tabel di kotak Name. Klik OK.

Tentang Range_lookup
  1. Range_lookup adalah nilai logika TRUE atau FALSE, dimana kita ingin fungsi VLOOKUP atau HLOOKUP mengembalikan nilai dengan metode kira-kira (TRUE) atau mengembalikan nilai secara tepat (FALSE).
  2. Range_lookup tidak wajib ditulis. Bila dikosongkan, maka range_lookup menggunakan metode kira-kira (TRUE).
  3. Logika TRUE juga bisa ditulis dengan angka 1 dan FALSE ditulis dengan angka 0 (nol).
  4. Untuk penggunaan logika TRUE, kolom atau baris pertama tabel yang menyimpan data yang dicari (lookup_value) harus diurutkan secara ascending (dari nilai terendah ke nilai tertinggi).

Contoh penggunaan range_lookup logika TRUE


Penjelasan Tabel
  • Kolom Komisi pada Tabel Perhitungan Komisi akan diisi berdasarkan data di Tabel Komisi.Dimana untuk Jumlah Penjualan 1.000.000 - 4.999.999 mendapatkan komisi 3%, 5.000.000 - 9.999.999 mendapatkan komisi 5%, dan seterusnya.
  • Sintaks untuk mengisi sel C13: =VLOOKUP(B13;$A$3:$B$7;2;TRUE)
    • Lookup_value: nilai sel B13, yaitu 3.000.000.
    • Table_array: range $A$3:$B$7 pada Tabel Komisi.
    • Col_index_num: mengambil nilai dari kolom 2 (kolom Komisi - Tabel Komisi).
    • Range_lookup: menggunakan metode kira-kira (TRUE). Dimana bila tidak menemukan lookup_value dengan nilai yang sama (3.000.000), maka cari nilai terdekat yang lebih kecil dari lookup_value. Nilai yang lebih kecil adalah 1.000.000, dengan begitu nilai yang dikembalikan adalah 3%.
Selanjutnya formula disalin ke sel berikutnya (C14:C17). Hasilnya adalah seperti tabel berikut ini.


  • Metode kira-kira (TRUE) mengharuskan data pada kolom atau baris pertama yang berisi lookup_value pada table_array diurutkan dari nilai terkecil ke nilai terbesar. Bila tidak, akan mendapatkan nilai error seperti contoh berikut.
 





Contoh penggunaan range_lookup logika FALSE



Penjelasan Tabel

  • Kolom Harga pada Tabel Penjualan akan diisi berdasarkan data dari Tabel Size.
  • Sintaks untuk mengisi sel C12: =VLOOKUP(B12;$A$3:$B$6;2;FALSE)
    • Lookup_value: nilai sel B12, yaitu M.
    • Table_array: range $A$3:$B$6 pada Tabel Size.
    • Col_index_num: mengambil nilai dari kolom 2 (kolom Harga - Tabel Size).
    • Range_lookup: menggunakan metode pengembalian nilai secara tepat (FALSE). Dimana bila tidak menemukan lookup_value dengan nilai yang sama (M), maka kembalikan nilai error.
Selanjutnya formula disalin ke sel berikutnya (C13:C16). Hasilnya adalah seperti tabel berikut ini.







  • Tabel berikut merupakan contoh pengembalian nilai error (#N/A), karena XXL tidak terdapat pada Tabel Size. Untuk mencegah terjadi salah ketik, gunakan validasi data.

 


Kadang mungkin kita ingin membuat aturan pada data yang diketikkan pada lembar kerja. Misalnya, pada range tertentu hanya boleh diisi dengan angka saja, kemudian untuk sel yang lain hanya boleh diisi dengan teks saja. Hal ini dapat dilakukan dengan menggunakan fitur validasi data (Data Validation) di Excel 2007.
Validasi data membantu mengurangi kesalahan saat input data pada lembar kerja. Fitur ini sangat bermanfaat terutama bila file Excel dikerjakan oleh lebih dari satu orang.


Misalnya: Pada range tertentu (B1:F1) hanya boleh diisi dengan angka 100 – 9999 saja.



Bila diisi dengan data lain akan diberikan peringatan seperti contoh berikut.




Cara membuatnya cukup mudah. Berikut akan disajikan cara validasi input untuk angka, teks, dan tanggal.

Cara Validasi Input untuk Angka


  • Pilih kumpulan sel (range).  
  • Pada Data tab, Data Tools group, klik Data Validation, dan pilih Data Validation. 




  • Pada Setting tab, di bagian Allow, pilih Whole number.
  • Di bagian Data, pilih between.
  • Di kotak Minimum, isi angka terendah, misalnya 100.
  • Di kotak Maximum, isi angka tertinggi, misalnya 9999. 
  • Untuk menambahkan input message lihat di sini atau klik OK untuk menutup kotak dialog.


Cara Validasi Input untuk Text


  • Pilih kumpulan sel (range).  
  • Pada Data tab, Data Tools group, klik Data Validation, dan pilih Data Validation.  
  • Pada Setting tab, di bagian Allow, pilih Custom. 
  • Di kotak Formula, isi dengan formula: =IsText (B2), sel B2 merupakan sel pertama pada range. 
  • Untuk menambahkan input message lihat di sini atau klik OK untuk menutup kotak dialog. 



 Cara Validasi Input untuk Tanggal



  1. Pilih kumpulan sel (range).
  2. Pada Data tab, Data Tools group, klik Data Validation, dan pilih Data Validation.
  3. Pada Setting tab, di bagian Allow, pilih Date.
  4. Di bagian Data, pilih between.
  5. Di kotak Start date, isi dengan tanggal awal, misalnya 3/1/2010.
  6. Di kotak End date, isi dengan tanggal akhir, misalnya 3/31/2010.  
  7. Untuk menambahkan input message lihat di sini atau klik OK untuk menutup kotak dialog.
 






Diposting oleh di Tidak ada komentar:
Langganan: Postingan (Atom)