Selasa, 05 Maret 2013

FUNGSI Rumus VLOOKUP dan HLOOKUP


Rumus fungsi ini digunakan untuk mencari nilai berdasarkan tabel ketentuan.

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
clip_image002

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


clip_image004

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
clip_image006

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
clip_image008

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

Penggunaan Fungsi IF dalam Microsoft Excel

 
Fungsi IF merupakan fungsi logika, yang artinya suatu nilai mempunyai syarat dan dapat bernilai benar jika syarat terpenuhi serta bernilai salah jika syarat tidak terpenuhi. Secara umum rumus fungsi IF dalam Microsoft Excel adalah sebagai berikut :
         IF(Logical_Test;Value_if_True;Value_if_false) atau
         IF(Syarat;Hasil_Jika_Syarat_Terpenuhi;Hasil_Jika_Syarat_Tidak_Terpenuhi)
Dimana:
            logical_test merupakan syarat dari percabangan.
             value_if_true merupakan nilai jika syarat percabangan terpenuhi.
             value_if_false merupakan nilai jika syarat percabangan tidak terpenuhi.
Langkah-langkah untuk menyelesaikannya melalui function wizard adalah sebagai berikut :
  • Klik pada sel D3.
  • Klik dari menu Klik Insert - > Function , kemudian muncul window seperti ini , pilih Fungsi IF, klik OK

  • Ubah setting pada window fungsi IF seperti berikut :

Pada Logical Test ditulis C3 > 50 adalah karena di sel C3 lah letak dari nilai yang akan dilakukan penyeleksian. Ketikkan syaratnya pada isian logical_test, misalnya C3>50, yang artinya jika data di cell C3 lebih besar atau sama dengan 50 maka bernilai benar dan jika kurang dari 50 maka bernilai salah. Ketikkan teks “Lulus” pada isian value_if_true, yang artinya jika pada logical_test bernilai benar maka teks ini yang akan dihasilkan/dikeluarkan. Ketikkan teks “Tidak Lulus” pada isian value_if_false, yang artinya jika pada logical_test bernilai salah maka teks ini yang akan dihasilkan/dikeluarkan.
  • Klik OK. Copy-kan formula ke sel dibawahnya.

Pemberian tanda “ ” merupakan tambahan jika ingin menambahkan statement berupa
kalimat atau string.
Didapatkan hasil akhir seperti gambar berikut :
Dari gambar diatas kita dapat menggunakan fungsi IF untuk memberikan hasil pada keterangan berdasarkan nilai yang didapat. Coba buat tabel seperti pada gambar diatas, dengan mengosongkan data keterangan karena akan diisi menggunakan rumus fungsi IF. Setelah selesai coba masukan rumus berikut ini pada sel D3.
=IF(C3<=50;”Tidak Lulus”;”Lulus”)
Kemudian tekan enter untuk melihat hasilnya, setelah terlihat hasilnya coba copy kan rumus tersebut untuk sel D4 sampai dengan D7.
Fungsi IF diatas adalah fungsi IF sederhana,  kita bisa menggabungkan beberapa fungsi bersamaan dengan Fungsi IF.

Fungsi IF : Menentukan suatu tes logika untuk dikerjakan, dan  mempunyai bentuk:
=IF(tes logika, nilai jika benar, nilai jika salah)

Fungsi AND, OR dan NOT :  Merupakan fungsi tambahan untuk mengembangkan tes  kondisi. Fungsi AND dan OR maksinal berisi 30 argumen  logika, sedangkan NOT hanya mempunyai satu argumen  logika, mempunyai bentuk:
  • AND(logika1,logika2,………,logika30)
  • OR(logika1,logika2,………,logika30)
  • NOT(logika)


Percabangan tidak hanya pemisahan menjadi dua kemungkinan saja, namun juga bisa menjadi banyak kemungkinan. Untuk percabangan yang memisahkan ke banyak kemungkinan harus menggunakan IF secara bertingkat.
Percabangan beberapa Tingkat
Pembahasan fungsi IF di atas dengan Tes Logika Tunggal, Tes Logika dapat dikembangkan dengan tambahan salah satu fungsi AND , OR atau NOT. Bentuk fungsi IF dengan tes logika yang dikembangkan adalah sebagai berikut :
= IF (OR(Tes Logika1;Tes Logika2);Nilai jika benar;Nilai jika salah)
Studi kasus : sebuah perusahaan akan merekrut tenaga satuan pengaman ( satpam ) dengan ketentuan :pengalaman kerja minimal empat tahun dan usia maksimal 35 tahun. Perusahaan melakukan seleksi administrasi dengan kriteria tersebut , pelamar yang memenuhi syarat akan mengikuti syarat selanjutnya , sedangkan yang tidak memnuhi syarat dinyatakan gugur. Kasus tersebut dapat diterjemahkan ke dalafungsi seperti berikut ini :
= IF(AND(Kerja>=4;Usia<=35); Wawancara; Gugur)
Fungsi tambahan adalah AND karena kedua tes logika merupakan kriteria yang harus terpenuhi, perhatikan penerapan fungsi tersebut dalam baris rumus worksheet.
Istilah fungsi IF bercabang adalah kasus yang mempunyai banyak tingkat pengujian tes logika yang diselesaikan dengan fungsi IF. Sebagai contoh sebuah lembar kerja berisi data hasil ujian statistik , berdasarkan nilai ujian akan dikonversikan dalam bentuk huruf dengan ketentuan sebagai berikut :
             Nilai Ujian Huruf
0 – 59    :               E
60 – 74  :               D
75 – 84  :               C
85 – 94  :               B
95 – 100                :               A
Perhatikan penyelesaian dengan fungsi IF dalam lembar kerja seperti pada gambar berikut :
Contoh Fungsi IF berrcabang Sel E5 diisi dengan rumus :
=IF(D5<60;"E";IF(D5<75;"D";IF(D5<85;"C";IF(D5<95;"B";"A"))))
Mencari Jumlah
Kadangkala diperlukan sebuah informasi untuk menmapilkan berapa jumlah data yang memenuhi kriteria tertentu, misalnya dalam sebuah daftar nilai ingin diketahui berapa orang yang mendapat nilai “A”. Untuk itu telah disediakan sebuah fungsi yaitu :
=COUNTIF(range,criteria)
dimana pada area yang sidebutkan di range akan dicari berapa jumlah sel yang sesusai dengan criteria. Contoh =COUNTIF(B2:B57,”A”) artinya dicari berapa jumlah sel yang berisi “A” pada range B2 sampai B57.
Pada contoh sebelumnya , dikembangkan untuk mencari Jumlah Lulus dan Tidak Lulus , sehingga nantinya akan menjadi seperti dibawah ini :
Untuk dapat menambahkan hasil tersebut , lakukan penambahan fungsi COUNTIF pada C9 sebagai berikut melalui function wizard:
Sedangkan untuk mendapatkan julah yang tidak lulus, lakukan penambahan fungsi COUNTIF pada C10 sebagai berikut melalui function wizard:
Nilai yang kita olah melalui Excel sebenarnya dapat dibagi menjadi dua bagian , yaitu nilai formula dan nilai acuan. Yang selama ini dijelaskan pada bab-bab sebelumnya , adalah nilai formula , dimana semua nilai yang diolah menjadi satu dengan formula yang dihitung , misal =A1 * 20. Angka 20 merupakan nilai formula. Sedangkan pada beberapa keadaan dimana nilai tersebut sering berubah , bisa kita gunakan nilai acuan agar tidak perlu merubah melalui formula. Untuk memudahkan menggunakan nilai acuan , Excel menyediakan fasilitas Fungsi Lookup , fungsi ini akan melihat nilai pada tabel yang lain apakah nilai yang di cocokan ada pada tabel tersebut , untuk kemudian diambil nilainya.