FUNGSI 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
Langkah pengisian Nama Barang
1. Tempatkan penunjuk sel pada sel C6
2. Ketikkan rumusnya :
=VLOOKUP(B6,$B$16:$C$18,2)
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)
Fungsi
IF adalah penerapan Fungsi logika untuk mendapatkan beberapa
kemungkinan dengan mengacu pada kondisi Objek. Fungsi AND adalah Fungsi
yang akan menyeleksi Dua Atau lebih kondisi yang benar artinya jika dua
atau lebih kondisinya Benar maka hasilnya Benar, namun jika Salah satu
kondisi salah mak hasilnya akan salah.
Fungsi OR adalah fungsi yang mempunyai hasil Benar jika salah satu dari dua tau lebih acuhan dalam kondisi benar, akan memiliki hasil salah jika semua acuhan tidak memiliki kondisi Benar, Ribet ya ???????????????? OK agar lebih jelas, mari kita perhatikan Pembelajaran berikut. Mohon permisi buat para mastah Excel, ini sesuai dengan tema Blog " Pembelajaran Dasar ilmu komputer "
Fungsi OR adalah fungsi yang mempunyai hasil Benar jika salah satu dari dua tau lebih acuhan dalam kondisi benar, akan memiliki hasil salah jika semua acuhan tidak memiliki kondisi Benar, Ribet ya ???????????????? OK agar lebih jelas, mari kita perhatikan Pembelajaran berikut. Mohon permisi buat para mastah Excel, ini sesuai dengan tema Blog " Pembelajaran Dasar ilmu komputer "
89
PENGUNAAN FUNGSI IF,
COUNT IF, DAN LOOKUP
Dalam melakukan perhitungan, seringkali ditemukan adanya beberapa pilihan yang harus
ditentukan. Sebagai contoh, dari nilai mahasiswa akan ditentukan apakah mahasiswa
tersebut lulus atau tidak, dan jika lulus apakah predikat dari nilainya tersebut. Dalam
menangani hal ini telah disediakan fungsi untuk percabangan, yaitu dengan mengunakan
fungsi IF. Sebagai contoh dalam menampilkan status kelulusan mahasiswa dengan melihat
nilai ujian yang sudah dijalaninya sebagai berikut :
Gambar 7.1 Data awal untuk mencari status kelulusan
Untuk mendapatkan status “LULUS” mahasiswa harus mempunyai nilia lebih besar dari 50 ,
sehingga jika nilainya kurang dari 50 maka akan diberi status “TIDAK LULUS”
7.1 Penggunaan Fungsi IF
Untuk dapat menyelesaikannya dibutuhkan fungsi Logika yaitu fungsi IF, untuk lebih
jelasnya berikut beberapa paparan tentang penggunaan fungsi IF.
Fungsi IF dengan format lengkap adalah sebagai berikut :
IF(logical_test;value_if_true;value_if_false)
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 :
1. Klik pada sel D3.
2. Klik dari menu Klik Insert - > Function , kemudian muncul window seperti ini , pilih
Fungsi IF, klik OK.
Pelatihan Aplikasi Software Perkantoran
90
Gambar 7. 2 pemilihan Fungsi IF melalui Category Logical.
3. Ubah setting pada window fungsi IF seperti berikut :
Gambar 7.3. Setting melalui function wizard
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.
4. 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 :
Microsoft Excel 2007
91
Gambar 7.4. Hasil Akhir pemberian status kelulusan
Tabel 7.1. Fungsi Logika
Fungsi Keterangan
IF Menentukan suatu tes logika untuk dikerjakan, dan
mempunyai bentuk:
=IF(tes logika, nilai jika benar, nilai jika salah)
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.
7.2 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 syrat
dinyatakan gugur. Kasus tersebut dapat diterjemahkan ke dalam fungsi IF 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.
Pelatihan Aplikasi Software Perkantoran
92
Gambar 7.5. Fungsi IF dengan 2 tes logika
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 :
Gambar 7.6. 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"))))
7.3 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 :
Gambar 7.7 Hasil Akhir penambahan fungsi COUNTIF
Microsoft Excel 2007
93
Untuk dapat menambahkan hasil tersebut , lakukan penambahan fungsi COUNTIF pada C9
sebagai berikut melalui function wizard:
Gambar 7.8. Pengubahan setting fungsi COUNTIF untuk sel C9
Sedangkan untuk mendapatkan julah yang tidak lulus, lakukan penambahan fungsi
COUNTIF pada C10 sebagai berikut melalui function wizard:
Gambar 7.9. Pengubahan setting fungsi COUNTIF untuk sel C10
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.
7.4. Fungsi Lookup Reference
Seringkali kita menghadapi permasalahan seperti pada gambar 7.11 :
Pelatihan Aplikasi Software Perkantoran
94
Gambar 7.11 Contoh Penggunaan fungsi Lookup
Permasalahan yang akan diselesaikan adalah mengisi Gaji Pokok berdasarkan data yang
ada di atasnya. Hal ini sebenarnya dapat diselesaikan dengan menggunakan percabangan
IF, misalnya untu mengisi sel D8 dapat digunakan rumus
=IF(C3=1,$B$2,IF(C3=2,$B$3,IF(C3=3,$B$4,$B$5))).
Penyelesaian tersebut dapat digunakan jika jumlah golongan yang ada hanya sedikit dan
tidak akan berubah, bagaimana jika jumlah golongan mencapai 100 atau lebih ?.
Untuk menyelesaikan permasalahan di atas telah disediakan fungsi VLOOKUP dan HLOOKUP
dengan bentuk fungsinya adalah
VLOOKUP(lookup value,table_array,col_index_num[,range_lookup])
dan
HLOOKUP(lookup value,table_array,col_index_num[,range_lookup]) , dimana :
• lookup_value adalah data yang akan dicocokkan.
• table_array adalah tempat pencarian data.
• col_index_num adalah data pada kolom keberapa yang hendak diambil.
• range_lookup (optional) adalah nilai logika yang dimasukkan, jika diisi TRUE maka
akan dicari sampai data terdekat, sedang jika diisi FALSE maka akan dicari data
yang persis sama.
Pemakaian VLOOKUP untuk kasus di atas adalah pada sel D8 akan kita masukkan rumus
=VLOOKUP(C8,$A$2:$B$5,2), dimana C8 adalah data kunci yang akan dicocokkan,
$A$2:$B$5 adalah area pencarian data termasuk kunci pencarian dan data yang akan
diambil, dan 2 adalah menunjukkan kolom ke-2 dari range tersebut adalah data yang
diambil.
Atau dengan cara lain dengan menggunakan function wizard sebagai berikut :
1. Letakkan kursor pada sel C8
2. Klik Insert -> Function
3. Pilih kategori Lookup & Reference
Microsoft Excel 2007
95
Gambar 7.12 Fungsi Vlookup ada di kategori Lookup & Referance
4. Pilih Menu VLOOKUP. Klik OK.
5. Pada menu VLOOKUP Wizard pilih atau ketik sel C8.
Gambar 7.13 Memasukkan sel sebagai lookup value
6. Klik tombol Browse pada Cell Range , Blok A1 hingga B5 , judul kolom tidak usah
dipilih.
Gambar 7.14 Memilih Range sel sebagai Table Array
7. Tambahkan tanda $ untuk Range, sehingga menjadi $A$2:$B$5 , tanda $ ini untuk
menjadikan sel absolut agar jika di copy ke sel dibawahnya tidak berubah
referensinya.
8. Ketik 2 dimana akan mengembalikan ke 2 pada Col_index_num, untuk lebih jelasnya
bisa dilihat ilustrasi pada gambar 6.5 :
Pelatihan Aplikasi Software Perkantoran
96
Gambar 7.15 Indeks Kolom pada Table Array
Gambar 7.16 Perubahan pada isian VLOOKUP melalui wizard
9. Klik OK, kemudian Copykan ke sel dibawahnya. Hasil akhirnya seperti di gambar
7.17 :
Gambar 7.17 Hasil akhir dan contoh pengkodean
Sebagai pedoman dalam pemakaian VLOOKUP ini adalah kunci pencarian harus berada di
kolom paling kiri dari table_array dan kunci pencarian tersebut hars dalam keadaan sudah
terurut.
Pemakaian HLOOKUP sama dengan VLOOKUP, perbedaannya hanya dalam hal penyusunan
datanya, yaitu kalau VLOOKUP datanya disusun secara vertikal sedangkan kalau HLOOKUP
datanya disusun secara horisontal.
Microsoft Excel 2007
97
7.4 Latihan dan Soal :
1. Data Karyawan Berdasarkan Jenjang Pendidikan
Buatlah tabel yang berisi data karyawan berdasarkan jenjang pendidikan dimana
tabel tersebut berisi Nama, Pendidikan, Divisi.
Hitunglah berapa jumlah karyawan yang tingkat pendidikannya setara Diploma dan
berapa yang tingkat pendidikannya setara sarjana
Hitunglah berapa jumlah karyawan yang bekerja untuk masing-masing Divisi baik
Divisi Akuntansi, Personalia ataupun Divisi Produksi
2. Hitunglah Harga Jual Buku dengan tabel diskon yang ada di tabel bantu bawah.
3. Menghitung Tarif Kereta Api
Buatlah tabel Tarif Kereta Api, yang berisi Nomor, Nama Pemesan, kode, kelas,
Status, Harga, Jumlah Tiket, Jumlah Harga. Buatlah juga tabel pembantu yang berisi
kode, kelas, dan taripnya. Tugas Anda adalah :
a. Isilah Kolom Nomor, Nama Pemesan, dan Kode, Kelas, Status, Jumlah Tiket
b. Isilah Kolom Harga berdasarkan tabel pembantu sesuai dengan kode masingmasing
tiket
c. Isilah kolom Jumlah Harga berdasarkan Harga dan Jumlah tiket.
Pelatihan Aplikasi Software Perkantoran
98
Menerapkan Fungsi IF
Untuk memulai silahkan anda Buka Program Microsoft Excel 2003 / 2007, terserah
pilih Start Menu - All Program - Microsoft Office - pilih Microsoft Excel
Pertama :
Fungsi IF Tunggal
Adalah Fungsi IF yang hanya memiliki 2 kemungkinan nilai yang akan muncul
sebagai contoh silahkan anda buat Tabel dengan Kepala kolom ( coloumn Head ) Nilai dan Keterangan, Lihat gambar 1
klik pada kolom B2 tepat di bawah cell B1, ketikkan rumus seperti di bawah ini :
Untuk memulai silahkan anda Buka Program Microsoft Excel 2003 / 2007, terserah
pilih Start Menu - All Program - Microsoft Office - pilih Microsoft Excel
Pertama :
Fungsi IF Tunggal
Adalah Fungsi IF yang hanya memiliki 2 kemungkinan nilai yang akan muncul
sebagai contoh silahkan anda buat Tabel dengan Kepala kolom ( coloumn Head ) Nilai dan Keterangan, Lihat gambar 1
klik pada kolom B2 tepat di bawah cell B1, ketikkan rumus seperti di bawah ini :
=IF(A2>=60,"LULUS","REMIDI")
Rumus diatas artinya, jika pada cell A2 diisikan angka 60 / lebi besar maka akan muncul keterangan LULUS pada cell B2, sebaliknya jika pada cell A2 di isikan nilai angka kurang dari 60, maka akan muncul keterangan REMIDI pada Cell B2. lihat gambar 2
Gampang kan ! sekarang mari kita tingkatkan
Kedua :
Fungsi IF Bertingkat
adalah Fungsi IF yang memiliki lebih dari 2 kemungkinan nilai yang akan muncul
contoh : dari tabel di atas tambahkan kolom pada cell C1 berikan coloumn Head Keterangan ke2
klik pada cell C2 tepat dibawah cell C1 dan ketikkan rumus di bawah ini :
=IF(A2>=90,"SANGAT BAIK",IF(A2>=70,"BAIK",IF(A2>=60,"CUKUP",IF(A2>=50,"KURANG","SANGAT KURANG"))))
- jika nilai pada cell A2 lebih besar/sama dengan 90 maka akan muncul keterangan SANGAT BAIK pada Cell C2
- jika nilai pada cell A2 lebih besar/sama dengan 70 maka akan muncul keterangan BAIK pada Cell C2
- jika nilai pada cell A2 lebih besar/sama dengan 60 maka akan muncul keterangan CUKUP pada Cell C2
- jika nilai pada cell A2 lebih besar/sama dengan 50 maka akan muncul keterangan KURANG pada Cell C2
- jika nilai pada cell A2 lebih kecil dari 50 maka akan muncul keterangan SANGAT KURANG pada Cell C2
saat nilai pada Cell A2 kita ganti maka akan terjadi perubahan pada kolom keterangan sesuai dengan rumus yang kita berikan
untuk isi dari keterangan yang ingin di munculkan tergantung pada kebutuhan, artinya dapat anda ubah sesuai yang anda inginkan. Untuk mengembangkan Fungsi IF dapat di kompbinasi dengan Fungsi AND / OR, Pembelajaran berikutnya akan kita bahas Fungsi AND dan OR pada Excel, serta kombinasi kedua fungsi tersebut dengan fungsi IF.
Semoga bermanfaat
untuk isi dari keterangan yang ingin di munculkan tergantung pada kebutuhan, artinya dapat anda ubah sesuai yang anda inginkan. Untuk mengembangkan Fungsi IF dapat di kompbinasi dengan Fungsi AND / OR, Pembelajaran berikutnya akan kita bahas Fungsi AND dan OR pada Excel, serta kombinasi kedua fungsi tersebut dengan fungsi IF.
Semoga bermanfaat
sumber : -Posted by Dimas Rachmanto
-
http://www.google.co.id/url?sa=t&rct=j&q=fungsiif+or+if+and+if+not&source=web&cd=2&cad=rja&ved=0CCcQFjAB&url=http%3A%2F%2Fwww.deptan.go.id%2Fdistanbun-sijunjung%2Fadmin%2Frb%2Fbab-7-microsoft-excel.pdf&ei=UkSiULnVDIWzrAe4tYG4Bw&usg=AFQjCNFHuZs6qYtdRXZBSmdVarrV98AiBQ
Tidak ada komentar:
Posting Komentar