Diberdayakan oleh Blogger.
RSS
You can replace this text by going to "Layout" and then "Page Elements" section. Edit " About "

FUNGSI HLOOKUP DAN VLOOKUP

Diposting oleh Unknown |


Fungsi HLOOKUP
Hlookupdigunakan untuk pembacaan table data yang di susun secara horizontal (mengambil data secara horizontal dari data induk).Horizontal disini maksudnya adalah kita mengambil data berdasarkan nilai baris dalam Microsoft Excel.
Rumus fungsi Hlookup :
=HLOOKUP(lookup_value;table-array;row_index_num; range_lookup)
                            Atau diterjemahkan sebagai berikut :
=HLOOKUP(data yang dicari;tabel;baris_yang_diingikan;range)
Keterangan:
-Lookup_value…nilai sebagai dasar pembacaan table
-Table_array…range table data yang dibaca
-Row_index_num…nomor urut yang menyatakan posisi baris dalam suatu table (dimulai dari1)
-Range_lookup…berisi true (apabila table data diurutkan secara menaik) dan false (apabila table data tidak diurutkan
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.
Fungsi VLOOKUP
Vlookup digunakan untuk pembacaan table data yang di susun secara vertikal.
Rumus fungsi Vlookup :
=VLOOKUP(lookup_value;table_array; col_index_num;range_lookup)
Dimana:
  • lookup_value: nilai atau sel referensi yang dijadikan kunci dalam pencarian data.
  • table_array: tabel atau range yang menyimpan data yang ingin dicari. Range untuk contoh tabel di atas adalah: A2:C4 (tabel pertama - VLOOKUP) dan B1:D3 (tabel dua - HLOOKUP).
  • col_index_num: nomor kolom yang ingin diambil nilainya untuk fungsi VLOOKUP. Untuk tabel pertama (VLOOKUP): nomor kolom adalah 2, bila ingin mengambil nilai pada kolom Name. Nomor kolom adalah 3, bila ingin mengambil nilai pada kolom Price.
  • range_lookup: Nilai logika TRUE atau FALSE, dimana Anda ingin fungsi VLOOKUP atau HLOOKUP mengembalikan nilai dengan metode kira-kira (TRUE) atau mengembalikan nilai secara tepat (FALSE).

Contoh Vlookup :

Penjelasan tabel:
  • Tabel 1 (A1:C4), merupakan tabel yang akan kita ambil datanya.
  • Tabel 2 (A9:D12) memiliki tiga kolom (Customer, Unit, dan Code) yang sudah berisi data. Sedangkan kolom Total akan diisi dengan menggunakan data dari Tabel 1.
  • Kunci (lookup_value) yang digunakan adalah nilai pada kolom Code, yaitu 1002, 1003.

Cara membaca:
  1. =VLOOKUP(1002,$A$2:$C$4,3,FALSE) akan menghasilkan 68,
    yaitu: =VLOOKUP(temukan 1002 yang di C10,pada range A2:C4 di tabel 1, kemudian kembalikan nilai pada kolom 3 baris yang sama, dan kembalikan nilai hanya apabila menemukan 1002 pada tabel 1)
  2. =VLOOKUP(1003,$A$2:$C$4,2,FALSE) akan menghasilkan GHI,
    yaitu: =VLOOKUP(temukan 1003 yang di C11, pada range A2:C4 di tabel 1, kemudian kembalikan nilai pada kolom 2 baris yang sama, dan kembalikan nilai hanya apabila menemukan 1003 pada tabel 1)

Contoh VLOOKUP:
Tiga formula berikut digunakan untuk mengisi sel D10, D11, dan D12 pada kolom Total.
  1. =B10*VLOOKUP(C10,$A$2:$C$4,3,FALSE) akan menghasilkan 340.
    Nilai 340 diperoleh dari 5 x 68. Dimana: B10 = 5, dan fungsi =VLOOKUP(C10,$A$2:$C$4,3,FALSE) yang mengembalikan nilai 68.
  2. =B11*VLOOKUP(C11,$A$2:$C$4,3,FALSE) akan menghasilkan 320.
    Nilai 320 diperoleh dari 10 x 32. Dimana: B11 = 10, dan fungsi =VLOOKUP(C11,$A$2:$C$4,3,FALSE) yang mengembalikan nilai 32.
  3. =B12*VLOOKUP(C12,$A$2:$C$4,3,FALSE) akan menghasilkan 544.
    Nilai 544 diperoleh dari 8 x 68. Dimana: B12 = 8, dan fungsi =VLOOKUP(C12,$A$2:$C$4,3,FALSE) yang mengembalikan nilai 68.

Berikut penjelasan detilnya.

Tentang Lookup_value
  1. Lookup_value adalah sel referensi (misalnya sel A1) atau nilai yang dijadikan kunci dalam pencarian data. Nilai bisa berupa teks atau angka.
  2. Lookup_value harus berada pada baris atau kolom pertama tabel penyimpan data.
  3. Tidak berlaku case sensitive untuk lookup_value. Teks Jan, JaN, atau JAN dianggap sama oleh Excel.
  4. Lookup_value berupa teks harus diberi tanda petik dua ("").
    Contoh: =HLOOKUP("feb";$A$6:$J$7;2;FALSE).
  5. 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 seperti pada tutorial ini: Cara Validasi Data yang Diinput pada Lembar Kerja Excel.


  • Digg
  • Del.icio.us
  • StumbleUpon
  • Reddit
  • RSS

0 komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)