FUNGSI VLOOKUP & HLOOKUP
Menggunakan VLOOKUP untuk tabel yang beda sheet di excel 2003
Fungsi
Vlookup sangat berguna jika anda mempunyai tabel referensi (lihat
sheet2)yang sering digunakan pada tabel kerja (lihat sheet1). Jika anda
mengubah nilai pada tabel referensi maka anda tidak perlu mengubah nilai
pada tabel kerja, karena otomatis akan berubah sendiri mengacu pada
tabel referensinya. Hal ini sangat efektif jika anda mempunyai banyak
baris (lebih dari puluhan baris).
Berikut ini prosedur membuat formula Vlookup di excel 2003:
1. Buat tabel referensi
2. Buat tabel kerja
Pada sel B3 ketik rumus
=VLOOKUP(A3,Sheet2!$A$3:$C$6,2,0)
Pada sel C3 ketik rumus
=VLOOKUP(A3,Sheet2!$A$3:$C$6,3,0)
Jika data lebih dari baris ke 14, copy formula pada kolom B3 dan C3 sesuai kebutuhan.
Menggunakan VLOOKUP jika tabel dalam sheet yang sama di excel 2003
Fungsi
Vlookup sangat berguna jika anda mempunyai tabel referensi yang sering
digunakan pada tabel kerja . Jika anda mengubah nilai pada tabel
referensi maka anda tidak perlu mengubah nilai pada tabel kerja, karena
otomatis akan berubah sendiri mengacu pada tabel referensinya. Hal ini
sangat efektif jika anda mempunyai banyak baris (lebih dari puluhan
baris).
Berikut ini prosedur membuat formula Vlookup jika tabel referensi dan tabel kerja dalam sheet yang sama:
1. Membuat tabel referensi ( background biru)
2. Membuat tabel kerja (background coklat)
Formula yang digunakan :
a. pada sel B3, ketik:
=VLOOKUP(A3,$E$3:$G$6,2,0)
b. pada sel C3, ketik:
=VLOOKUP(A3,$E$3:$G$6,3,0)
Copy formula B3 dan C3 sampai B14 dan C14 (atau sesuai kebutuhan datanya)
Fungsi Hlookup sederhana
Fungsi Hlookup hampir sama dengan vlooukup, tetapi tabel referensinya disusun secara horisontal.
Berikut contoh penggunaan hlookup
1. Buat tabel kerja dan tabel referensi
2. Masukkan formula berikut:
a. Pada sel B5 ketik:
=HLOOKUP(A5,$F$4:$I$6,2,0)
b. Pada sel C5 ketik:
=HLOOKUP(A5,$F$4:$I$6,3,0)
c. Copy formula dari baris 5 ke 16
3. Hasilnya seperti gambar di bawah
TIK FUNGSI STATISTIK, IF, OR NOT AND, ABSOLUT
1. Fungsi statistika
Dalam Microsoft Excel terdapat Fungsi yang sering digunakan antara lain :
1.
SUM : digunakan untuk mencari jumlah isi data pada range
tertentu Syntax: =SUM
(bilangan1,bilangan2,…).
2. AVERAGE : digunakan untuk mencari
nilai rata-rata dari suatu range
Syntax: =AVERAGE(bilangan1,bilangan2,…).
3. MAX :
digunakan untuk mencari nilai tertinggi dari suatu
range Syntax:
=MAX(bilangan1,bilangan2,…).
4. MIN : digunakan untuk
mencari nilai terendah dari suatu
range Syntax:
=MIN(bilangan1,bilangan2,…).
5. COUNT : digunakan untuk
mencara banyaknya data dari satu
range Syntax:
=COUNT(nilai1,nilai2,…).
perhatikan contoh penggunaan fungsi-fungsi diatas :
1. buat tabel seperti dibawah ini:
Cara pengerjaan :
• Jumlah Nilai pada cell F6 adalah “=Sum(C6:E6)” atau “+C6+D6+E6”
• Total Nilai Kelas pada cell C16 adalah “=Sum(C6:C15)”
• Rata-rata Nilai Kelas pada cell C17 adalah “=Average(C6:C15)”
• Nilai Terendah pada cell C18 adalah “+Min(C6:C15)”
• Nilai Terbesar pada cell C19 adalah “+Max(C6:C15)”
• Jumlah Data pada cell C20 adalah “+Count(C6:C15)”
Untuk
nilai TAS dan nilai Praktikum, dapat dilakukan penggandaan rumus
formula / melakukan copy rumus hanya dengan menggerakkan kursor ┼ pada
sebelah kanan bawah cell yang akan di-copy lalu ke daerah yang masih
belum ada rumus formulanya.
2. Fungsi IF
Fungsi : Menguji kebenaran suatu kondisi.
Bentuk Penulisan Fungsi : =IF(X,Y,Z)
Contoh Kasus :
Peserta ujian dinyatakan lulus jika nilai rata-rata harus di atas 60,
jika tidak maka dinyatakan tidak lulus. Perhatikan tabel di bawah ini :
Penulisan fungsi:
• Pada sel E3 ketik =IF(D3<60,”TIDAK LULUS”,”LULUS”)
• Pada sel E4 ketik =IF(D4<60,”TIDAK LULUS”,”LULUS”)
• Pada sel E5 ketik =IF(D5<60,”TIDAK LULUS”,”LULUS”)
• Pada sel E6 ketik =IF(D6<60,”TIDAK LULUS”,”LULUS”)
Latihan :
Buatlah tabel seperti di atas, untuk kolom HASIL isi rumusnya seperti
pada kolom PENULISAN FUNGSI. Hasilnya akan terlihat pada kolom HASIL
3. Fungsi OR, NOT, AND
a. Fungsi OR
Fungsi : Menghasilkan TRUE jika beberapa argumen bernilai benar dan FALSE jika argumen bernilai salah
Bentuk Penulisan : =OR(X1,X2,X3,...)
Contoh Kasus :
Jika pada kolom NILAI-1 dan NILAI-2 lebih besar dari 60 maka pada kolom
HASIL akan dinyatakan LULUS, tetapi jika salah satu nilainya kurang
dari 60 maka pada kolom HASIL akan dinyatakan HER.
Perhatikan tabel di bawah ini :
Penulisan fungsi:
• Pada sel D3 ketik =OR(B3<60,C3<60,D3<60,”HER”,”LULUS”)
• Pada sel D4 ketik =OR(B4<60,C4<60,D4<60,”HER”,”LULUS”)
• Pada sel D5 ketik =OR(B5<60,C5<60,D5<60,”HER”,”LULUS”)
• Pada sel D6 ketik =OR(B6<60,C6<60,D6<60,”HER”,”LULUS”)
Latihan :
Buatlah tabel seperti di atas, untuk kolom HASIL isi rumusnya seperti pada kolom PENULISAN FUNGSI
b. Fungsi NOT
Fungsi : Menghasilkan TRUE jika kondisi yang diuji salah dan menghasilkan FALSE jika kondisi yang diuji benar.
Bentuk Penulisan Fungsi : =NOT()
Contoh :
Perhatikan tabel di bawah ini
Penulisan fungsi:
• Pada sel E3 ketik =NOT(D3<60,”LULUS”,”GAGAL”)
• Pada sel E4 ketik =NOT(D4<60,”LULUS”,”GAGAL”)
• Pada sel E5 ketik =NOT(D5<60,”LULUS”,”GAGAL”)
• Pada sel E6 ketik =NOT(D6<60,”LULUS”,”GAGAL”)
Latihan :
Buatlah tabel seperti di atas, untuk kolom HASIL isi rumusnya seperti pada kolom PENULISAN FUNGSI
c. Fungsi AND
Fungsi : Menghasilkan nilai TRUE jika semua argumen yang diuji bernilai
benar dan FALSE jika ada satu atau lebih argumen yang bernilai salah.
Bentuk Penulisan Fungsi : =AND(X1,X2,X3,...)
Contoh Kasus :
Peserta ujian dinyatakan lulus jika nilai ujian teori dan praktik
masing-masing harus di atas 7, jika kurang dari 7 maka dinyatakan gagal.
Perhatikan tabel di bawah ini :
Pengunaan fungsi:
• Pada sel E2 ketik =AND(C2>7,D2>7,”LULUS”,”GAGAL”)
• Pada sel E3 ketik =AND(C3>7,D3>7,”LULUS”,”GAGAL”)
• Pada sel E4 ketik =AND(C4>7,D4>7,”LULUS”,”GAGAL”)
• Pada sel E5 ketik =AND(C5>7,D5>7,”LULUS”,”GAGAL”)
• Pada sel E6 ketik =AND(C6>7,D6>7,”LULUS”,”GAGAL”)
• Pada sel E7 ketik =AND(C7>7,D7>7,”LULUS”,”GAGAL”)
• Pada sel E8 ketik =AND(C8>7,D8>7,”LULUS”,”GAGAL”)
• Pada sel E9 ketik =AND(C9>7,D9>7,”LULUS”,”GAGAL”)
• Pada sel E10 ketik =AND(C10>7,D10>7,”LULUS”,”GAGAL”)
• Pada sel E11 ketik =AND(C11>7,D11>7,”LULUS”,”GAGAL”)
•
Latihan :
Buatlah tabel seperti di atas, untuk kolom KETERANGAN isi rumusnya
seperti pada kolom PENULISAN FUNGSI. Hasilnya akan terlihat pada kolom
KETERANGAN
