Excel for Beginners - VLOOKUP (Part 4)

Masih ingin mendalami Excel? Mari kita lanjutkan. Posting saya kali ini adalah mengenai pemakaian fungsi VLOOKUP. Fungsi VLOOKUP ini digunakan untuk mencuplik/mengambil data dari tabel referensi (acuan).
Sebagai informasi awal selain fungsi VLOOKUP, terdapat juga fungsi yang kegunaannya hampir sama yaitu HLOOKUP. Perbedaan penggunaannya terletak pada bentuk tabel referensi. Jika tabel referensi disusun secara vertikal, digunakan fungsi VLOOKUP sementara jika tabel referensi disusun secara horizontal digunakan fungsi HLOOKUP.

Sintax: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Perhatikanlah contoh berikut ini:

Untuk mendapatkan Gaji maka pada sel D9 ketikkan rumus:

=VLOOKUP(C9,$F$2:$G$6,2)

Selanjutnya copy sel D9 dan Paste ke sel D10 sampai D18.

Keterangan:

• C9 adalah lookup value  yang dicocokkan dengan isi tabel referensi.
• $F$2:$G$6 adalah alamat tabel referensi yang dinyatakan dalam bentuk alamat absolut.
• 2 adalah index kolom pada tabel referensi (kolom golongan, indeks =1 dan kolom gaji, indeks =2)
• Dalam hal ini Golongan pada tabel referensi harus diurutkan secara urut Naik (Ascending)

Bagaimana jika tabel referensi tersebut diatas datanya tidak dalam kondisi terurut? Saatnya kita menerapkan range_lookup.

Perlu diketahui range_lookup hanya memiliki 2 opsi yaitu, TRUE dan FALSE.

Contoh:

Perhatikan data Kode Pegawai pada tabel referensi dalam kondisi tidak terurut.

Untuk mendapatkan Gaji, pada sel D10 ketikkan:
=VLOOKUP(C10,$B$2:$D$7,2,FALSE)
Selanjutnya copy sel D10 dan Paste ke sel D11 sampai D19.

Untuk mendapatkan Tunjangan, pada sel E10 ketikkan:
=VLOOKUP(C10,$B$2:$D$7,3,FALSE)
Selanjutnya copy sel E10 dan Paste ke sel E11 sampai E19.

Excel for Beginners- Alamat Absolut (Part 3)

Kita lanjut lagi menggali apa yang ada di Microsoft Excel. Perlahan-lahan namun pasti kita akan coba membahas penerapan alamat Absolute. Alamat absolut ini digunakan untuk pemakaian khusus, tergantung peletakan data pada sel.
Harus ada pemahaman yang cukup jika ingin menggunakan alamat absolut dalam pembuatan rumus. Jika tidak maka pemahaman yang keliru akan menghasilkan kebingungan.

Penulisan rumus yang menerapkan alamat absolut dapat dilihat dengan  adanya tanda $ (simbol mata uang dollar).

Perhatikan contoh sederhana berikut ini:
Contoh 1.

Jika peletakan data terlihat seperti gambar diatas, maka dengan mudah dapat kita masukkan rumus pada sel E3 yaitu, =C2*D2

Selanjutnya copy sel E3 dan Paste ke sel E3 sampai E6.

Penempatan data seperti diatas biasanya dihindari oleh pengguna Excel, alasannya adalah tarif per jam untuk setiap baris adalah sama. Biasanya peletakan data diubah menjadi seperti gambar berikut ini.

Data terlihat lebih simple dan kerja pun menjadi lebih ringan. Jika peletakan data seperti gambar diatas, saatnya kita menerapkan alamat absolut pada rumusnya.

Pada sel D4 ketikkan =C4*C$1

Selanjutnya copy sel D4 dan Paste ke sel D5 sampai D8.

Keterangan:

A5: alamat relatif (baik kolom maupun baris)

$A5: alamat absolut kolom relatif baris

A$5: alamat relatif kolom absolut baris

$A$5: alamat absolut (baik kolom maupun baris)

Untuk mengubah alamat relatif (default) menjadi alamat absolut (baik kolom, baris atau keduanya) dapat
menggunakan tombol keyboard F4.

Contoh:

• misalnya ketikkan rumus =A3
• tekan F4 (beberapa kali sampai anda melihat jenis alamat yg anda inginkan)
• lanjutkan penulisan rumus sampai lengkap

Untuk menambah pemahaman anda akan alamat absolut perhatikanlah contoh berikut ini.

Excel For Beginners - IF Function (Part 2)

Posting kali ini membahas penggunaan fungsi build-in yang terdapat pada Excel, yaitu fungsi IF. Fungsi IF sangat umum digunakan untuk membuat keputusan (decision). Misalnya seorang guru atau pendidik akan sangat mudah menentukan siswa yang Lulus atau Gagal dalam suatu mata pelajaran dengan mengacu pada standar nilai minimal (KKM = Kriteria Ketuntasan Minimal).

Penerapan lain tentu sangat banyak, tergantung kearifan anda dalam melihat suatu masalah dan mencoba menerapkan fungsi IF tersebut agar "kesulitan" anda teratasi.

Bentuk umum fungsi IF:
=IF(logical_test,value_if_true,value_if_false)
Pada bagian logical_test sebuah sel dibandingkan dengan "sesuatu" menggunakan Operator Pembanding. Sesuatu disini bisa berupa sebuah alamat sel atau sebuah nilai baik angka maupun teks. Dapat juga berupa rumus yang sederhana maupun rumit.

Contoh logical test.
A5>A7
B2=15000
B2<>"T"
C5="Pontianak"
A3>=SUM(B2:B7)

Operator Pembanding (Comparison Operator) yang dapat digunakan pada Excel dalam kaitannya dengan penerapan fungsi IF diantaranya adalah:

• = (sama dengan)
• >< (tidak sama dengan)
• > (lebih besar)
• < (lebih kecil)
• >= (lebih besar sama dengan)
• <=(lebih kecil sama dengan)

Sementara value_if_true berisi "nilai" jika logical test tercapai/terpenuhi dan value_if_false berisi "nilai" jika logical test tidak tercapai/terpenuhi.

Nilai yang dimaksud diatas dapat berupa angka atau teks atau berupa rumus.

Dalam menggunakan fungsi IF, kita harus berpedoman kepada ketentuan yang diberlakukan/ditetapkan. 

Untuk memahaminya cobalah perhatikan contoh sederhana berikut ini.

Contoh 1.
Siswa dinyatakan LULUS jika Nilainya mencapai 70. Jika tidak maka dinyatakan GAGAL.

Untuk mendapatkan siapa siswa yang LULUS atau GAGAL maka pada sel D6 ketikkan
=IF(E6>=70,"LULUS","GAGAL") lalu tekan Enter.
Selanjutnya copy sel D6 dan Paste ke sel D7 sampai D10.

Jika setting Regional and Language Option pada komputer anda pada saat ini adalah Indonesia, maka ganti rumus tersebut menjadi
=IF(E6>=70;"LULUS";"GAGAL") lalu tekan Enter.

Perhatikan contoh lanjutan dibawah ini.
Misalnya berlaku ketentuan sebagai berikut:
Jika Kode Ruang = T maka disebut Ruang Teori dan jika Kode Ruang = P disebut Ruang Praktek.

Pada sel D59 ketikkan: =IF(C59="T","Ruang Teori","Ruang Praktek")
atau dapat juga anda berikan rumus seperti berikut:
=IF(C59="P","Ruang Praktek","Ruang Teori")

Contoh 2.
Penerapan Nested IF (IF bersarang)

Untuk mendapatkan Kategori pada sel D9 ketikkan:

=IF(C9>=80,"Baik",IF(C9>=60,"Cukup","Kurang"))

Selanjutnya copy sel D9 dan Paste ke sel D10 sampai D13.

Ingat, banyaknya kurung buka harus sama dengan banyaknya kurung tutup.

Contoh 3
Menghitung Pajak Penghasilan.

Misalnya berlaku ketentuan, setiap pegawai yang gaji kotor-nya mencapai 3500000 dikenai pajak sebesar 15%.

 

Pada sel D45 ketikkan: =IF(C45>=3500000,"15%*C45,0)
Pada sel E45 ketikkan: =C45-D45

Contoh 4.
Menghitung Diskon dan Total Pembayaran.

Sebuah Mini Market membuat ketentuan sebagai berikut:
Jika total belanja mencapai 100000, diberikan diskon sebesar 10%.

 

Pada sel E18 ketikkan: =C18*D18

Selanjutnya copy rumus tersebut untuk mendapatkan Bayar pada barang lainnya.

Pada sel E23 ketikkan: =SUM(E18:E22)

Pada sel E24 ketikkan: =IF(E23>=100000,10%*E23,0)

Pada sel E25 ketikkan: =E23-E24

Excel For Beginners - SUM and AVERAGE (Part 1)

Excel merupakan salah satu software spreadsheet terkemuka pada saat ini dan paling banyak digunakan pengguna komputer untuk menyelesaikan perhitungan baik yang sangat sederhana maupun dalam tingkat rumit. Excel menyediakan fungsi yang memudahkan pengguna komputer untuk melaksanakan proses kalkulasi yang rumit sekalipun. 

Untuk pemula, materi kali ini akan sangat membantu anda dan mudah-mudahan dapat menggerakkan anda untuk mendalami Excel lebih lanjut.

Contoh 1.

Menghitung Jumlah dan Rata-rata.

Rumus:
Untuk mendapatkan Jumlah, pada sel F2 ketikkan =SUM(C2:E2) lalu tekan Enter. Selanjutnya copy sel F2 dan kemudian Paste ke sel F3 sampai F6.

Untuk mendapatkan Rata-rata, pada sel G2 ketikkan =AVERAGE(C2:E2) lalu tekan Enter. Selanjutnya copy sel G2 dan kemudian Paste ke sel G3 sampai G6.

Contoh 2.

Rumus:
Untuk mendapatkan Total, pada sel E3 ketikkan =C3*D3 lalu tekan Enter.
Selanjutnya copy sel E3 dan Paste ke sel E4 sampai F7.

Untuk mendapatkan Sub Total, pada sel E8 ketikkan =SUM(E3:E7) lalu tekan Enter.

 Operator Aritmatik yang digunakan untuk perhitungan sederhana.

• + (tambah)
• - (kurang)
• * (kali)
• / (bagi)