Share via

Mengenal Formula pada Excel - Part 29: Contoh Penerapan (id-ID)

  • Article

Untuk lebih memahami bagaimana formula diterapkan dalam aplikasi Excel juga sebagai gambaran bagi Anda yang mungkin baru saja mempelajari tentang formula pada Excel, berikut ini contoh beberapa kasus yang mudah-mudahan bisa digunakan sebagai bahan pembelajaran.

Contoh 1: Laporan Persentase Komisi Sales

Pada tabel berikut ini Anda diminta untuk mengisi kolom Persentase Komisi serta Jumlah Komisi, dimana jumlah komisi tersebut dihitung berdasarkan Masa Kerja dan Nilai Penjualan. Tabel Data Perhitungan Komisi Sales digunakan sebagai acuan atau referensi untuk menyelesaikan perhitungan.

http://arhiez.net/images/technet/formula-excel/Part29-01a.jpg

Solusinya, karena ada tabel lain sebagai acuan maka formula jelas akan melibatkan fungsi VLOOKUP. Namun pada tabel referensi tersebut ada 2 kondisi untuk masa kerja hingga fungsi IF harus digunakan juga. Dengan demikian formula yang digunakan adalah:

D4=VLOOKUP(C4;$A$12:$C$16;IF(B4<=3;2;3);TRUE)

E4=D4*C4

Contoh hasil akhirnya bisa dilihat pada gambar berikut ini.

http://arhiez.net/images/technet/formula-excel/Part29-01b.jpg

Contoh 2: Menghitung Total Upah Karyawan

Pada tabel berikut ini Anda diminta untuk menghitung Waktu Kerja dan Total Upah karyawan, dengan aturan jam kerja standar adalah 9 jam dengan upah Rp. 10.000 / jam. Untuk karyawan yang jam kerjanya melebihi jam kerja standar (lembur), akan mendapat upah Rp. 2.500 / jam

http://arhiez.net/images/technet/formula-excel/Part29-02a.jpg

Solusi untuk kasus ini adalah menggunakan fungsi IF. Alasannya karena ada karyawan yang mendapat upah standar saja dan ada karyawan yang mendapat upah standar plus upah lembur juga. Selain fungsi IF Anda cukup menggunakan operasi dasar matematika biasa. Kemudian mengingat perhitungan matematika yang digunakan cukup kompleks, Anda boleh menggunakan fungsi ROUNDDOWN agar nilai-nilai yang dihasilkan bulat. Dengan demikian formula yang digunakan adalah:

D9=ROUNDDOWN((C9-B9)*24;2)

E9=IF(D9<=9;D9*$C$5;(9*$C$5)+(D9-9)*$C$6)

Contoh hasil akhirnya bisa dilihat pada gambar berikut ini.

http://arhiez.net/images/technet/formula-excel/Part29-02b.jpg

Contoh 3: Mengurai Struktur NIM (Nomor Induk Mahasiswa)

Pada tabel berikut ini Anda diminta untuk mengisi kolom Angkatan, Jurusan dan Fakultas dengan fungsi yang sesuai. Namun pengisian kolom-kolom ini bersandar pada struktur penulisan NIM yang jumlahnya 8 digit, yaitu:

  • Digit pertama menunjukan Fakultas
  • Digit ke-2 dan ke-3 menunjukan Jurusan
  • Digit ke-4 dan ke-5 menunjukan angkatan
  • Tiga digit terakhir menunjukan nomor urut pendaftaran

Khusus untuk mengisi Fakultas dan Jurusan, disediakan referensi dalam 2 tabel terpisah, yaitu tabel Kode Fakultas dan tabel Kode Jurusan.

http://arhiez.net/images/technet/formula-excel/Part29-03a.jpg

Solusinya, karena semua pengisian kolom bersandar pada struktur NIM dan tidak semua nilai NIM tersebut digunakan melainkan hanya digit-digit tertentu saja, maka Anda bisa menggunakan fungsi teks untuk mengambil beberapa karakter tertentu dari NIM tersebut.

Untuk mengisi kolom Angkatan pada sel C4, maka Anda harus menggunakan fungsi MID karena berdasarkan struktur NIM, angkatan ini merupakan digit yang berada di tengah yaitu digit ke-4 dan ke-5.

Untuk mengisi kolom Fakultas pada sel D4 serta kolom Jurusan pada sel E4, maka Anda harus menggunakan fungsi VLOOKUP karena ada tabel referensi yang disediakan, namun nilai kunci yang digunakan pada fungsi VLOOKUP tersebut menggunakan fungsi lain yaitu fungsi LEFT untuk Fakultas dan dungsi MID untuk Jurusan.

Dengan demikian formula yang digunakan adalah:

C4=MID(A4;4;2)

D4=VLOOKUP(LEFT(A4;1);$A$11:$B$13;2;FALSE)

E4=VLOOKUP(MID(A4;2;2);$D$11:$E$16;2;FALSE)

Contoh hasil akhirnya bisa dilihat pada gambar berikut ini.

http://arhiez.net/images/technet/formula-excel/Part29-03b.jpg

Contoh 4: Discount Penjualan Rokok

Pada tabel berikut ini Anda diminta untuk mengisi kolom Jumlah Harga, Discount, dan Total Harga. Namun ada aturan yang harus dipenuhi yaitu untuk merk rokok Ardath, Gudang Garam dan Jarum mendapat discount 5%. Sedangkan untuk merk-merk lainnya tidak mendapat discount.

http://arhiez.net/images/technet/formula-excel/Part29-04a.jpg

Solusinya, untuk kolom Jumlah Harga pada sel E4 dan Total Harga pada sel G4 hanya merupakan operasi perkalian dan pengurangan biasa. Barulah pada kolom Discount Anda harus menggunakan fungsi yaitu fungsi IF-OR. Alasannya karena perhitungan ini melibatkan 3 syarat atau kondisi. Dengan demikian formula yang digunakan adalah:

E4=D4*C4

F4=IF(OR(B4="ardath";B4="gudang garam";B4="jarum");5%;0)*E4

G4=E4-F4

Contoh hasil akhirnya bisa dilihat pada gambar berikut ini.

http://arhiez.net/images/technet/formula-excel/Part29-04b.jpg

Contoh 5: Biaya Paket Wisata Harian

Pada tabel berikut ini Anda diminta untuk mengisi kolom Biaya Tambahan per Hari, Tujuan Wisata, Harga Paket, Biaya Tambahan, serta kolom Jumlah Dibayar.

http://arhiez.net/images/technet/formula-excel/Part29-05a.jpg

Biaya Tambahan per Hari pada sel G11 hanya merupakan operasi pembagian biasa dimana Anda tinggal membagi Harga Paket dengan Lama Wisata.

G11=F11/E11

Lalu Tujuan Wisata pada sel D4 dan Harga Paket pada sel F4 bisa Anda kerjakan dengan mudah menggunakan fungsi VLOOKUP dimana nilai kuncinya bersandar pada kolom Kode.

D4=VLOOKUP(C5;$C$11:$G$15;2;FALSE)

F4=VLOOKUP(C4;$C$11:$G$15;4;FALSE)

Formula yang cukup kompleks justru terdapat pada kolom Biaya Tambahan di sel G4. Disini ada kondisi yang harus ditetapkan yaitu jika peserta berwisata dengan lama hari sesuai dengan lama wisata paket atau dibawahnya maka peserta tidak dikenakan biaya tambahan. Namun jika peserta berwisata dengan lama hari diatas lama wisata paket maka peserta akan dikenakan biaya tambahan per hari. Biaya tambahan ini tentunya disesuaikan dengan kelebihan hari yang diambil peserta tersebut.

Karena ada 2 buah kondisi maka fungsi utama yang digunakan jelas fungsi IF, namun tiap-tiap argumen pada fungsi IF tersebut harus diurai dengan fungsi VLOOKUP mengingat tujuan wisata yang bervariasi. Dengan demikian formula yang digunakan adalah:

G4=IF(E4>VLOOKUP(C4;$C$11:$G$15;3;FALSE);(E4-VLOOKUP(C4;$C$11:$G$15;3;FALSE))*VLOOKUP(C4;$C$11:$G$15;5;FALSE);0)

Dan yang terakhir adalah kolom Jumlah Dibayar pada sel H4. Formula yang digunakan disini hanya merupakan operasi penjumlahan biasa dimana Anda tinggal menambahkan Harga Paket dengan Biaya Tambahan.

H4=F4+G4

Contoh hasil akhirnya bisa dilihat pada gambar berikut ini.

http://arhiez.net/images/technet/formula-excel/Part29-05b.jpg

Demikian beberapa contoh kasus terkait dengan penerapan formula pada aplikasi Excel yang mungkin saja Anda temukan di lingkungan kerja Anda. Dan seperti Anda lihat bahwa formula ini bisa menyelesaikan beragam perhitungan secara semi otomatis pada tabel-tabel data yang sederhana hingga yang kompleks.

Catatan:

Penerapan formula pada artikel ini dibahas secara garis besar dan tidak terlalu detail. Tujuannya agar Anda bisa melakukan analisa sendiri terkait dengan formula dan fungsi yang digunakan tersebut.

Formula yang dituliskan pada artikel ini juga hanya formula untuk sel-sel pada baris teratas saja karena untuk mengisi sel-sel berikutnya seperti biasa Anda dapat menggunakan proses Auto Fill serta bantuan Sel Absolut.

Jika Anda ingin mencoba sendiri contoh-contoh kasus pada artikel ini, Anda dapat mendownload file-nya di tautan berikut ini:
http://sdrv.ms/10xFnvw

 

Mengenal Formula pada Excel
Daftar Artikel: