Cara Menggunakan Rumus HLOOKUP, VLOOKUP dan IF pada Microsoft Excel 2010

Sebelum menginjak ke pembahasan soal mari kita ketahui terlebih dahulu fungsi dari rumus-rumus Microsoft Excel terutama pada rumus HLOOKUP, VLOOKUP, dan IF. Rumus HLOOKUP digunakan untuk menampilkan data dari sebuah tabel yang disusun dalam format horizontal. Bentuk penulisan rumusnya yaitu =HLOOKUP (lookup_value; table_value; row_index_num; [range_lookup]) atau bisa juga dibaca =HLOOKUP (nilai kunci; acuan tabel; baris ke (n); false). Untuk VLOOKUP sama dengan HLOOKUP, namun dalam format yang berbeda, jika  HLOOKUP format tabelnya berbentuk horizontal dan jika VLOOKUP format tabel berbentuk vertikal. Dalam penulisan fungsinya =VLOOKUP (lookup_value; table_value; col_index_num; [range_lookup]).

Fungsi IF memungkinkan Anda untuk membuat perbandingan logis antara nilai dan apa yang diharapkan dengan menguji kondisi dan mengembalikan hasil jika True atau False. Sehingga fungsi IF dapat memiliki dua hasil. Bentuk penulisan fungsinya yaitu =IF(logical_test; [value_if_true]; [value_if_false]) atau bisa dibaca =IF(ketentuan yang diinginkan; jika benar; jika salah).

Untuk dapat memahami penjelasan di atas pastinya kita harus mempunyai contoh soal Ms. Excel atau data yang ingin kita kerjakan. Sebagai contoh saya mempunyai tampilan soal/data seperti berikut dan menggunakan Ms. Excel 2010.

dengan ketentuan...

Cara Menggunakan Rumus HLOOKUP, VLOOKUP, dan IF pada Microsoft Excel 2010

Dari soal di atas, yang perlu diisi datanya adalah Fasilitas, Biaya Periksa, Total Biaya, Pengurangan Biaya dan Cek Lab. Langkah-langkah untuk mengerjakan soal di atas adalah sebagai berikut:

1. Gunakan rumus HLOOKUP untuk menghitung Fasilitas.

    Sehingga akan muncul seperti di bawah ini.

Dalam fungsi HLOOKUP terdapat lookup_value atau bagian cell yang akan kita gunakan, table_array atau acuan tabel yang akan kita gunakan, dan row_index_num adalah baris ke (n) yang akan kita tampilkan, serta terdapat range_lookup yang berisi TRUE dan FALSE.

Untuk lebih jelasnya, mari kita lihat terlebih dahulu soal dan kemudian mari kita coba isikan pada tabel fasilitas seperti di bawah ini.

   
Langkah-langkah:

a.      Tulis =HLOOKUP( pada cell F6.

Menggunakan rumus HLOOKUP, karena format tabel tersebut berbentuk horizontal.

b.      Pada lookup_value kita isi dengan rumus MID, karena dalam soal fasilitas ditentukan oleh karakter ke 6 dari nomor kartu. Sehingga seperti pada gambar berikut.

      

B6 adalah cell yang ditunjuk, 6 adalah karakter ke 6, dan 1 adalah jumlah yang diambil dari karakter tersebut.

c.      Untuk table_array kita isi dengan cara blok tabel yang akan kita gunakan pada gambar di bawah tabel berada pada cell N5 sampai dengan Q6.
      Akan muncul fungsi N5;Q6, setelah itu kunci fungsi tersebut dengan tekan tombol F4 pada keyboard. Sehingga akan muncul seperti gambar berikut.

d.      Pada row_index_num kita akan mengisinya dengan angka 2. Mengapa? Karena baris kedua dari tabel tersebut yang akan kita tampilkan.

      

e.      Dalam range lookup terdapat TRUE dan FALSE. Untuk rumus ini kita menggunakan FALSE dengan mengklik 2 kali pada kata FALSE seperti pada gambar di bawah. Mengapa FALSE? Inilah yang masih menjadi misteri sampai saat ini.  So, jangan dibuat pusing yaa!

Setelah itu jangan lupa tutup kurung yaa. Kemudian enter dan ketemu jawabannya seperti di bawah ini. Horeee.

Coba kita lihat karakter ke 6 dari nomor kartu cell B6, karakter ke 6 yaitu huruf T dan pada tabel, jenis fasilitas huruf T adalah Tanpa Asuransi.

 2. Gunakan rumus VLOOKUP untuk menghitung Biaya Periksa.

Sehingga akan muncul seperti di bawah ini.

Dalam fungsi VLOOKUP terdapat lookup_value atau bagian cell yang akan kita gunakan, table_array atau acuan tabel yang akan kita gunakan, dan col_index_num adalah baris ke (n) yang akan kita tampilkan, serta terdapat range_lookup yang berisi TRUE dan FALSE.

Untuk lebih jelasnya, mari kita lihat terlebih dahulu soal dan kemudian mari kita isi pada tabel biaya periksa seperti di bawah ini:

    
Langkah-langkah:

a.       Sebelum kita menggunakan rumus VLOOKUP, terlebih dahulu kita akan mengkalikan jumlah periksa dengan rumus VLOOKUP. Sehingga  =G6*VLOOKUP( pada cell H6.

Mengapa VLOOKUP? Karena format tabel yang diminta dalam bentuk vertikal.

b.      Pada lookup_value kita isi dengan rumus MID, karena dalam soal Biaya Periksa ditentukan oleh karakter ke 3 dan 4 dari nomor kartu. Sehingga seperti pada gambar berikut.

Pada rumus MID B6 adalah cell yang ditunjuk, 3 adalah karakter ke 3, dan 2 adalah jumlah yang diambil dari karakter tersebut.

c.       Untuk table_array kita isi dengan cara blok tabel pada cell N8 sampai O13.

      Akan muncul fungsi N8;O13, setelah itu kunci fungsi tersebut dengan tekan tombol F4 pada keyboard. Sehingga akan muncul seperti gambar berikut.

d.       Pada col_index_num kita akan mengisinya dengan angka 2. Mengapa? Karena kolom kedua dari tabel tersebut yang akan kita tampilkan.

e.       Range_lookup. Dalam range lookup terdapat TRUE dan FALSE. Untuk rumus ini kita menggunakan FALSE dengan mengklik 2 kali pada kata FALSE. Mengapa FALSE? Sepertinya penjelasan di atas alasan mengapa kita menggunakan FALSE. Jangan lupa untuk tutup kurungnya!!! Dan enter.

Coba cek sendiri benar tidak hasilnya. Pasti benar dong!☺☺☺

  3. Gunakan simbol plus (+) untuk menghitung Total Biaya.

Perhatikan soal untuk nomor 3 di bawah ini.

Dalam rumus penjumlahan kita biasanya menggunakan rumus SUM, jika dalam jumlah cell yang banyak. Namun, yang diminta disini hanya penjumlahan dari dua cell. Sehingga, kita cukup menggunakan simbol plus (+) seperti di bawah ini.

H6 adalah Biaya Periksa yang akan dijumlah dengan I6 adalah Biaya Obat.

Tekan tombol Enter, dan hasilnya sebagai berikut. So simple kan?

  4. Gunakan rumus IF untuk menghitung Pengurangan Biaya.

Sehingga akan muncul seperti berikut ini.

Dalam fungsi IF terdapat logical_test yang biasa diisi dengan ketentuan yang diinginkan atau yang diminta sesuai dengan soal, value_if_true diisi dengan pernyataan jika benar, dan value_if_false diisi dengan pernyataan jika salah.

Untuk lebih jelasnya, mari kita lihat terlebih dahulu soal dan kemudian mari kita isi pada tabel pengembalian biaya seperti di bawah ini:

Langkah-langkah:

a.       Sebelum memasukkan rumus, terlebih dahulu pahami soal yang telah ditentukan. Dalam tabel tersebut dapat disimpulkan bahwa jika total biaya kurang dari sama dengan Rp 250.000 maka akan dikalikan dengan 10%, jika total biaya kurang dari sama dengan Rp 400.000 maka akan dikalikan dengan 20%, jika total biaya lebih dari Rp 400.000 maka akan dikalikan dengan 30%.

b.       Dari pembahasan di atas maka kita akan menggunakan rumus IF bertingkat. Seperti pada gambar di bawah ini. Pada gambar di bawah terdapat 3 rumus IF, karena terdapat 3 ketentuan.

Ø  Dalam rumus IF terdapat logical_test, dari gambar di atas yang dimaksud logical test adalah J6<=250000, J6<=400000, dan J6>400000. Logical_test di atas menunjukkan ketentuan dari soal yang diminta. J6 adalah total biaya. Mengapa total biaya? Karena pengembalian biaya ditentukan dari total biaya yang termasuk kategori dalam soal.

Ø  Untuk value_if_true kita mengisinya dengan mengkalikan total biaya dan persentase yang telah ditentukan sesuai dengan logical_test yang telah diisi sebelumnya,  atau bisa disebut pernyataan yang benar. Dari gambar di atas yang termasuk value_if_true adalah J6*10%, J6*20%, dan J6*30%.

Ø  Pada value_if_false kita mengisinya dengan angka nol (0) seperti pada gambar di atas. Mengapa nol? Karena tidak ada pernyataan yang salah.

c.       Untuk langkah terakhir jangan lupa diberi tanda kurung sampai warna hitam yaa!! Dan enter.

  5. Gunakan rumus IF untuk menghitung Cek Lab.

Sehingga akan muncul seperti berikut.

Seperti pada soal nomor 4. Dalam soal nomor 5 ini kita akan menyelesaikan soal cek lab dengan menggunakan rumus IF. Untuk lebih jelasnya, mari kita lihat terlebih dahulu soal dan kemudian mari kita isi pada tabel cek lab seperti di bawah ini.

Langkah-langkah:

a.      Pamahi soal terlebih dahulu. Di dalam soal cek lab ditentukan jika umur pasien adalah lebih dari sama dengan 35 tahun dan jumlah periksa lebih dari 2, maka untuk jenis fasilitas Tanpa Asuransi cek labnya di Lab. Medika, untuk jenis fasilitas BPJS cek labnya di Lab. Melati, dan untuk Asuransi Mandiri cek labnya di Lap. Parasanti. Namun, jika tidak memenuhi syarat yang telah digaris bawah maka Tidak Ada Cek Lab.

b.      Masukkan rumus IF pada cell L6.

c.       Untuk logical_test kita mengisinya seperti gambar berikut.

Kita akan memasukkan terlebih dahulu syarat pertama yaitu umur pasien lebih dari sama dengan 35 tahun. E6 adalah umur yang akan kita gunakan dalam perbandingan ini.

d.      Untuk syarat yang kedua, kita membutuhkan fungsi IF kembali.

Syarat yang kedua yaitu jumlah periksa lebih dari 2. Sehingga nampak pada gambar di atas.

e.       Setelah kedua syarat tersebut telah diisi, langkah selanjutnya yaitu dengan mengisi value_if_true. Seperti gambar berikut.

Kita kembali menggunakan rumus HLOOKUP karena tabel pada soal nomor 5 dalam bentuk horizontal. Dalam tabel tersebut telah diketahui jenis fasilitas, sehingga untuk lookup_valuenya kita menggunakan fasilitas sehingga muncul F6, untuk table_array kita blok tabel pada cell N1 sampai dengan Q2 dan jangan lupa untuk dikunci dengan tekan tombol F4. Sehingga akan muncul $N$1;SQ$2, dilanjutkan dengan raw_index yang diisi dengan angka 2 dan FALSE jangan lupa tutup kurung yaa.

f.        Langkah selanjutnya dengan mengisi value_if_false atau jika ketentuan salah. Dalam soal ini jika ketentuan tidak sesuai dengan dua syarat tersebut di atas, maka “Tidak Ada Cek Lab”. Sehingga seperti gambar berikut ini.

Untuk menulis tidak ada cek lab jangan lupa diberi tanda petik dua(“) di awal dan di akhir, setelah itu beri tanda kurung sampai berwarna hitam. Dan enter. Selesaiii.

Setelah menyelesaikan 5 soal, selesai sudah untuk pengerjaan soal di atas. Demikian sedikit informasi mengenai Cara Menggunakan Rumus HLOOKUP, VLOOKUP, dan IF pada Microsoft Excel 2010. Semoga bermanfaat dan terima kasih. ☺☺☺