Fungsi Vlookup Dan Hlookup

Cara Menggunakan Rumus Vlookup Dan Hlookup Pada Ms Excel

Pengertian dan fungsi Vlookup dan Hlookup

Fungsi VLOOKUP dan HLOOKUP merupakan fungsi referensi ke tabel lain. Dimana nilai dihasilkan berdasarkan key/range tertentu yang sesuai dengan tabel referensi.

Fungsi VLOOKUP

Fungsi ini digunakan untuk mencari nilai di kolom paling kiri tabel refrensi, dan kemudian mengembalikan sebuah nilai dalam baris yang sama dari sebuah kolom yang di tetapkan dalam tabel refrensi. Gunakan fungsi VLOOKUP (bukan HLOOKUP) jika nilai-nilai perbandingan berada di kolom sebelah kiri data yang ingin ditemukan.

Lookup_value, adalah nilai yang dapat ditemukan dalam kolom pertama dari tabel referensi. Lookup_value dapat berupa sebuah nilai, referensi, atau string teks.
Table_array adalah tabel informasi/referensi, tabel ini dapat berupa range, atau nama range.
col_index_num adalah nomor kolom pada tabel refeensi yang akan dihasilkan/atau dikembalikan. Jika col_index_num bernilai 1, maka nilai-nilai yang dihasilakn/dikembalika berada pada kolom pertama pada tabel referensi; sJika col_index_num bernilai 1, maka nilai-nilai yang dihasilakn/dikembalika berada pada kolom keduapada tabel referensi; demikian seterusnya. Jika col_index_num bernilai kurang dari 1, funsi VLOOKUP akan mengembalikan nilai kesalahan berupa # VALUE!, jika col_index_num lebih besar daripada jumlah kolom tabel referensi, maka fungsi VLOOKUP mengembalikan nilai kesalahan berupa # REF! .

range_lookup dapat bernilai TRUE ata FALSE, parameter ini bersifat opsional. Jika range_lookup berniali TRUE, maka nilai pada kolom pertama pada tabel referensi harus ditempatkan dalam urutan menaik: …, -2, -1, 0, 1, 2, …, AZ, FALSE, TRUE; jika tidak, maka kemungkinan fungsi VLOOKUP tidak akan memberikan nilai yang benar. Jika range_lookup bernilai FALSE, maka tabel tidak perlu disortir. Range_lookup adalah berupa nilai logika yang menentukan apakah fungsi VLOOKUP digunakan untuk pencarian sama persis atau berupa rentang. Jika range_lookup bernilai TRUE atau tidak ditulis, maka nilai yang dikembalikan berupa rentang, Dengan kata lain, bila pencocokan sama persis tidak ditemukan, nilai terbesar berikutnya yang lebih kecil dari lookup_value yang dikembalikan. Jika paramaeter ini bernilai FALSE, maka fungsi VLOOKUP akan melakukan pencarian sama persis. Jika data tidak ditemukan, maka fungsi ini menhasilkan nilai kesalahan # N / A

Contoh: Pada contoh kasus ini nilai kolom pertama pada tabel referensi, merupakan nilai rentang. Dimana nilai yang yang tercantum merupakan nilai batas bawah dari rentang. Angka 0 pada baris pertama dan angka 20 pada baris kedua, mengandung pengertian bahwa rentangnya adalah 0 hingga kurang dari 20. Angka 20 pada baris kedua dan angka 45 pada baris ketiga, mengandung pengertian bahwa rentangnya adalah 20 hingga kurang dari 45, demikian seterusnya. Rumus untuk yang digunakan untuk kolom Abjad adalah sebagai berikut: =VLOOKUP(C4;$C$12:$D$16;2;TRUE)

Parameter pertama bernilai C4, menyatakan nilai/sel yang dijadikan dasar pencarian
Parameter kedua bernilai $C$12:$D$16, adalah alamat absolut C12:D16 yang menyatakan lokasi dari tabel referensi. Untuk membuat alamat absolut Anda dapat menekan tombol F4 setelah memblok tabel refrensi.
Parameter ketiga bernilai 2, menyatakan nomor kolom tabel referensi yang menampung nilai-nilai yang dihasilkan oleh fungsi VLOOKUP
Parameter keempat diberi nilai TRUE karena kolom pertama dari tabel referensi merupakan rentang.

Fungsi Vlookup dan Hlookup Excel digunakan untuk membaca suatu tabel. Menggunakan rumus vlookup untuk membaca tabel secara vertikal ( atas – bawah ). Sedang rumus hlookup digunakan untuk membaca tabel secara horisontal ( kanan – kiri ). Berikut ini tutorial mengenal dan menggunakan rumus vlookup dan hlookup Excel, baik Office 2003 maupun 2007.

Sebelum dilanjutkan, belajar Excel prinsipnya agar cepat bisa memahami struktur rumus-rumus harus latihan langsung, mencoba dan terus mencoba. Karena kalau hanya mengetahui pengertian saja itu belum cukup, pokonya harus praktik langsung. Maka pada artikel ini, saya hanya memberi contoh dasar-dasarnya, supaya mudah dipahami bagi yang baru mulai belajar Excel.

VLOOKUP : mencari nilai, dimana tabel ketentuannya beberbentuk vertical (datanya tersusun kebawah) HLOOKUP : mencari nilai, dimana tabel ketentuannya beberbentuk horizontal (datanya tersusun mendatar). Rumus Vlookup

= VLOOKUP(sel yang diuji, tabel ketentuan, nomor index kolom)

Contoh Soal 1

Langkah pengisian Nama Barang 1. Tempatkan penunjuk sel pada sel C6 2. Ketikkan rumusnya : =VLOOKUP(B6,$B$16:$C$18,2) 3. Copy hasil tersebut sampai data terakhir

Contoh Soal 2 Langkah pengisian Harga Satuan 1. Tempatkan penunjuk sel pada sel D6 2. Ketikkan rumusnya : =VLOOKUP(B6,$B$16:$D$18,3) 3. Copy hasil tersebut sampai data terakhir Rumus Hlookup

= HLOOKUP(sel yang diuji, tabel ketentuan, nomor index baris)

Contoh Soal 1 Langkah pengisian Nama Barang 1. Tempatkan penunjuk sel pada sel C6 2. Ketikkan rumusnya : =HLOOKUP(B6,$B$15:$E$16,2) 3. Copy hasil tersebut sampai data terakhir

Contoh Soal 2

Langkah pengisian Harga Satuan 1. Tempatkan penunjuk sel pada sel D6 2. Ketikkan rumusnya : =HLOOKUP(B6,$B$15:$E$17,3) 3. Copy hasil tersebut sampai data terakhir

CONTOH APLIKASI PENGGUNAAN TABEL PIVOT

Pivot table digunakan untuk melakukan rekapan perhitungan berdasar data yang ada.

Contoh Soal

Silahkan anda mengetikkan data berikut

Kalau anda ditanya berapa pengeluaran jumlah pengeluaran tiap bulan bagaimana anda menyelesaikannya? Contoh lain berapa pengeluaran per jenis aktivitas selama satu tahun, berapa beban kerja penanggung jawab per bulan dan lain lain. Nah disinilah fungsi pivot table akan digunakan. Apalagi kalau jumlah datanya sangat banyak, ribuan data, maka fungsi pivot tabel akan sangat berguna bagi anda.

Penggunaan Pivot Table

pivot table seperti pada gambar berikut

Untuk menggunakan pivot table caranya silahkan klik menu insert Setelah anda mengklik pivot tabel akan muncul tampilan

Klik Select a table or range, klik dibawah kanannya, kemudian sorot data yang ingin dibuat pivot table seperti pada gambar berikut

Kemudian pilih lokasi hasil pivot table di existing worksheet seperti pada gambar berikut

Lalu klik OK. Kalau tahap anda benar, maka akan muncul menu pivot tabel seperti pada gambar berikut

Untuk membuat pengeluaran per bulan, anda cukup memasukkan bulan di row label dan nilai di value,

Perhatikan, pivot tabel sudah menghitungkan untuk anda jumlah pengeluaran tiap bulan Kalau kombinasi misalnya pengeluaran per bulan per jenis aktivitas, maka anda tinggal memasukkan jenis aktivitas di column labels seperti pada gambar berikut

Untuk merapikan hasil, gunakan format cell. Berikut contoh hasil perhitungan yang sudah dirapikan