Cara menggunakan tabel bantu di Excel

Microsoft Excel adalah program perangkat lunak multifungsi. Program ini memungkinkan pengguna untuk menyimpan, mengatur, menghitung, dan menganalisis data menggunakan rumus menggunakan sistem spreadsheet yang dibagi menjadi baris dan kolom.

Rumus Excel, juga disebut rumus, digunakan untuk melakukan penghitungan, memproses informasi, menganalisis konten, pengujian kondisi, dan banyak lagi. Ini membuat segalanya lebih mudah bagi pengguna, terutama dalam hal data dan statistik.

VLOOKUP dan HLOOKUP adalah fungsi Excel yang dapat dikategorikan sebagai Lookup dan Reference. Fitur ini bisa dibilang basic, jadi jangan heran jika kamu melamar pekerjaan yang berhubungan dengan data. Beberapa perusahaan memastikan bahwa kamu dapat menguasai fitur ini.

Rumus VLOOKUP adalah rumus Excel yang digunakan untuk mencari nilai tertentu dalam kolom atau sumber data lain menggunakan kata kunci vertikal tertentu. HLOOKUP, di sisi lain, digunakan untuk mencari nilai di kolom horizontal atau sumber data lainnya.

Cara Menggunakan Rumus VLOOKUP dan HLOOKUP

Fungsi VLOOKUP digunakan untuk menampilkan data dari sebuah tabel yang disusun dalam format vertikal. Bentuk penulisan fungsinya =VLOOKUP(lookup_value, tabel_array, col_index_num).

Sedangkan fungsi HLOOKUP digunakan untuk menampilkan data dari sebuah tabel yang disusun dalam format horizontal. Bentuk penulisan fungsinya =HLOOKUP(lookup_value,tabel_array, row_index_num).

Contoh Pengaplikasian VLOOKUP dan HLOOKUP

Melihat contoh soal di atas, yang harus anda isikan adalah menghitung gaji pokok, tunjangan,transportasi, total gaji, pajak, gaji bersih dan total.

• Menghitung Gaji pokok

Untuk menghitung gaji pokok gunakan fungsi vlookup di sel D5. Setelah mengisi formula disel D5, copy formula atau drag hingga sel D9. Bentuk penulisan fungsinya sama dengan yang telah dijelaskan sebelumnya, yaitu, =VLOOKUP(lookup_value, table_array, col_index_num).

Yang perlu kamu ketahui lLookup_value, diisi dengan nilai sebagai dasar (nilai kunci) pembacaan tabel data. Table_array merupakan range data yang disusun tegak atau vertikal yang berfungsi sebagai tabel bantu yang akan dibaca. Col_index_num, nomor urut kolom untuk pembacaan tabel yang dimulai dari kolom paling kiri.

Catatan: karena posisi “tabel gaji” yang akan dibaca diposisinya tetap, untuk menghindari kesalahan pada saat copy formula atau drag tambahkan $, untuk mengabsolutkan sel/range. Proses pengerjaannya bisa anda lihat seperti gambar di bawah ini.

• Menghitung Tunjangan

Untuk menghitung tunjangan gunakan fungsi VLOOKUP di sel E5. Setelah mengisi formula di sel E5, copy formula atau drag hingga sel E9. Seperti gambar di bawah ini:

• Menghitung Total Gaji

Untuk menghitung total gaji, gunakan fungsi SUM di sel G5 dan tulis rumusnya =SUM(D5:F5).
Setelah memasukkan rumus di sel G5, salin atau seret rumus ke sel G9. Atau, kamu dapat menambahkannya ke sel G5 seperti ini: =D5+E5+F5, lalu salin ke bawah.

• Menghitung Pajak

Untuk menghitung pajak gunakan fungsi HLOOKUP di sel H5, copy formula atau drag hingga sel H9.
Seperti gambar di bawah ini.

• Menghitung Gaji Bersih

Gunakan fungsi SUM di sel I5 seperti ini =SUM(G5-H5), copy formula atau drag hingga sel I9. Atau kamu juga bisa gunakan perintah =G5-H5, kemudian copy atau drag ke bawah sampai sel I9.

Perintah terakhir adalah menghitung “total” masing-masing bagian yang telah kamu kerjakan sebelumnya, dengan menggunakan fungsi SUM di sel D10, copy formula atau drag ke samping kanan sampai ke I10. Hasil akhir semua pengerjaan di atas, bisa di lihat seperti gambar di bawah ini.

Dapatkan Berita dan Artikel lain di Google News

PENGGUNAAN VLOOKUP DAN HLOOKUP

Fungsi VLOOKUP

Fungsi VLOOKUP digunakan untuk mencari nilai berdasarkan pembacaan pada tabel referensi (tabel bantu) yang ditentukan berdasarkan nomor kolom (data tersusun secara vertical). Perhatikan tabel di bawah ini.

Pada tabel jenis barang :

Offset untuk kolom Kode Barang adalah 1

Offset untuk kolom Nama Barang adalah 2

Offset untuk kolom Harga Satuan adalah 3

Offset untuk kolom Jumlah adalah 4

Bentuk umum dari fungsi VLOOKUP adalah :

=VLOOKUP(Sel;NamaTabelBantu;OffsetKolom,False)

Untuk lebih memahami, kita akan coba dengan contoh berikut.

1. Buatlah tabel utama seperti pada gambar di bawah ini.

1. Pada Sheet2, Buatlah tabel bantu seperti pada gambar di bawah ini. Untuk memudahkan kita, sebaiknya tiap-tiap sheet kita beri nama sesuai data yang ada di dalamnya.

1. Langkah selanjutnya adalah mengubah nama tabel bantu dengan cara memblok seluruh tabel bantu dan mengubah namanya menjadi apa saja sesuai kehendak kita yang terletak di pojok kiri atas. Fungsinya adalah untuk memudahkan dalam pengerjaan sheet LAPORAN. Pada contoh ini nama tabel bantu saya ubah dari B4 menjadi TBV, perhatikan gambar.

1. Selanjutnya kita akan mengerjakan sheet LAPORAN. Pada kolom "Nama Barang", kita isi berdasarkan pembacaan tabel bantu kolom kedua berdasarkan kolom Kode Barang pada tabel Laporan. Aktifkan sel D4, lalu masukkan rumus =VLOOKUP(C4;TBV;2;0) dan diakhiri dengan menekan tombol "Enter".  Untuk mengisi data di bawahnya kita dapat mengcopy rumus tersebut dengan cara menempatkan kursor pada pojok kanan bawah sel D4 sampai kursor berbentuk + dan menariknya ke bawah.

1. Untuk kolom "Harga Satuan", rumusnya hampir sama dengan kolom "Nama Barang" hanya berbeda pada kolom pembacaan tabel bantunya, yaitu kolom ke 3. Rumusnya yaitu =VLOOKUP(C4;TBV;3;0) dan diakhiri menekan tombol "Enter" lalu copy untuk mengisi data di bawahnya.
2. Kolom "Jumlah" didapat dari pembacaan Tabel Bantu kolom ke 4 yaitu dengan rumus =VLOOKUP(C4;TBV;4;0) dan diakhiri dengan menekan tombol "Enter" lalu copy untuk data di bawahnya.
3. Kolom "Total Bayar" didapat dari perkalian dari Harga Satuan dengan Jumlah yaitu dengan rumus =E4*F4 dan diakhiri dengan menekan tombol "Enter" lalu copy untuk data di bawahnya. Hasil akhirnya seperti pada gambar berikut.

Fungsi HLOOKUP

Fungsi HLOOKUP digunakan untuk mencari nilai berdasarkan pembacaan pada tabel referensi (tabel bantu) yang ditentukan berdasarkan nomor baris (data tersusun secara horizontal). Pada Microsoft Excel, offset baris dimulai dengan angka 1 untuk baris. Perhatikan tabel di bawah ini :

Pada tabel Staf Pemasaran :

Offset untuk baris "Kode" adalah 1

Offset untuk baris "Nama Sales" adalah 2

Bentuk umum fungsi HLOOKUP adalah.

=HLOOKUP(Sel;NamaTabelBantu;OffsetBaris;False)

Contoh penggunaan fungsi HLOOKUP.

1. Buatlah tabel utama pada sheet1, seperti gambar di bawah ini.

1. Pada sheet2, buatlah tabel bantu seperti gambar di bawah ini. Kemudian beri nama yang berbeda masing-masing tabel bantu. Misalkan tabel Staf Pemasaran kita beri nama "TBHP" dan tabel Jenis Barang adalah "TBHK".

1. Pada tabel utama, kolom "Nama Sales" didapat dari pembacaan tabel bantu  Staf Pemasaran baris kedua berdasarkan tabel utama kolom kode sales. Aktifkan sel E4, kemudian masukkan rumus =HLOOKUP(D4;TBHP;2;0) dan diakhiri menekan tombol "Enter".

1. Kolom "Nama Barang" berdasarkan pembacaan tabel bantu Jenis Barang baris kedua dengan rumus =HLOOKUP(C4;TBHK;2;0) dan diakhiri menekan tombol "Enter".
2. Kolom "Harga Satuan" dengan rumus =HLOOKUP(C4;TBHK;3;0).
3. Kolom "Jumlah" dengan rumus =HLOOKUP(C4;TBHK;4;0).
4. Kolom "Total Bayar" didapat perkalian antara Harga Satuan dengan Jumlah dengan rumus =G4*H4.
5. Hasil akhirnya seperti pada gambar berikut ini.

Fungsi Gabungan LOOKUP

Fungsi ini digunakan untuk mencari nilai berdasarkan pembacaa tabel referensi (tabel bantu) dengan menggabungkan beberapa fungsi secara bersamaan.

Bentuk umum dari fungsi ini adalah :

=VLOOKUP(LEFT(Sel;n);NamaTabelBantu;OffsetKolom;False)

=HLOOKUP(LEFT(Sel;n);NamaTabelBantu;OffsetBaris;False)

Contoh penggunaan fungsi ini.

1. Buatlah tabel Laporan seperti pada gambar di bawah ini.

1. Pada sheet yang berbeda buat tabel bantu seperti pada gambar di bawah. Kemudian beri nama masing-masing tabel bantu, pada contoh ini Tabel Pelanggan saya beri nama “TBHP” dan Tabel Jenis Barang adalah “TBHJB”.

1. Pada tabel Laporan, kolom Nama Pelanggan kita isi berdasarkan tabel bantu Staf Pemasaran baris kedua dengan mengambil dua digit akhir kolom Kode Barang. Rumus yang kita gunakan adalah =HLOOKUP(RIGHT(B4;2);TBHP;2;0) dan diakhiri menekan tombol Enter.
2. Kolom Nama Barang berdasarkan tabel bantu Jenis Barang kolom kedua dengan mengambil dua digit dari kolom Kode Barang. Rumusnya adalah =VLOOKUP(LEFT(B4;2);TBHJB;2;0) dan diakhiri menekan tombol Enter.
3.  
4. Kolom Harga Satuan dengan rumus =VLOOKUP(LEFT(B4;2);TBHJB;3;0) dan diakhiri menekan tombol Enter.
5. Kolom Jumlah dengan rums =VLOOKUP(LEFT(B4;2);TBHJB;4;0) dan diakhiri menekan tombol Enter.
6. Kolom Total Bayar dengan rumus =E4*F4 dan diakhiri menekan tombol Enter. Hasil akhirnya seperti pada gambar di bawah ini.

Bagaimana cara menggunakan VLOOKUP?

Rumus VLOOKUP digunakan untuk apa?

Apa itu VLOOKUP dan hlookup pada Excel?

Langkah langkah membuat tabel di ms Excel?