Penggabungan Beberapa Rumus Dasar Excel

Dalam post ini aku mau menjelaskan soal penggabungan beberapa rumus dasar Excel; HLOOKUP, VLOOKUP, IF, LEFT, MID, dan RIGHT. Langsung aja cekidot ;;)

Sebelumnya, beberapa hari yang lalu aku mendapat sebuah tugas dari guru TIK. Tugasnya adalah penerapan rumus-rumus Microsoft Office Excel 2007. Pertama-tama kita disuruh bikin tabel dengan beberapa kolom yang udah diisi dengan data yang telah ditentukan, tapi banyak kolom yang belum diisi. Nah kolom-kolom yang kosong itu harus diisi menggunakan rumus Excel, dan bukan diisi manual. Kaya gini nih kertas tugasnya:

  Terus ini bentuknya setelah aku contoh di MS Office Excel:

Tabel laporan

Tabel bantu (di Sheet 2)

Awalnya aku sempat bingung gimana caranya menerapkan rumus VLOOKUP supaya nggak perlu nulis rumus manual satu-satu setiap pegawai, tapi setelah aku tanya teman dan cari di Google akhirnya aku menemukan jawabannya :)) gini nih penyelesaiannya untuk seluruh tabel:

1. Kolom "Jabatan" diisi dengan rumus:
   =VLOOKUP(LEFT(C7,2),Sheet2!$B$2:$D$7,2,0)
2. Kolom "Keterangan" diisi dengan rumus:
   =VLOOKUP(RIGHT(C7,3),Sheet2!$K$2:$L$8,2,0)
3. Kolom "Tanggal Masuk Kerja" diisi dengan rumus:
   =DATE(MID(C7,4,2),MID(C7,6,2),MID(C7,8,2))
4. Kolom "Masa Kerja" diisi dengan rumus:
   =YEAR($D$3)-YEAR(F7)
5. Kolom "Gaji Pokok" diisi dengan rumus:
   =VLOOKUP(LEFT(C7,2),Sheet2!$B$2:$D$7,3,0)
6. Kolom "Bonus" diisi dengan rumus:
   =IF(H7<=$D$4,(VLOOKUP(D7,Sheet2!$F$2:$I$8,2,0)*Sheet1!I7),0)
7. Kolom "Potongan" diisi dengan rumus:
   =IF(H7>$D$4,(MINUTE(H7)*10000),0)
8. Kolom "Gaji Bersih" diisi dengan rumus:
   =I7+J7-K7
9. Untuk baris-baris di bawahnya, tinggal drag rumusnya ke bawah :))

---------------------------------------------------------------------------------------------------------
Penjelasan:
Pertama-tama perlu diketahui bahwa rumus dasar VLOOKUP adalah:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Di mana
*lookup_value: data sumber atau data yang diketahui
*table_array: tabel yang memuat data-data lainnya alias tabel bantu
*col_index_num: kolom pada tabel bantu yang akan digunakan untuk mencari data
*range_lookup: diisi dengan "0" (nol)

1. Kolom Jabatan
=VLOOKUP(LEFT(C7,2),Sheet2!$B$2:$D$7,2,0)

• "=VLOOKUP" adalah rumus yang menunjukkan bahwa kita menggunakan fungsi VLOOKUP alias fungsi pembacaan tabel secara vertikal.
• "LEFT(C7,2)" adalah data sumber atau data yang diketahui. Maksudnya, patokan kita untuk mencari data pada tabel bantu adalah 2 karakter pertama dari kiri pada cell C7.
• "Sheet2!" adalah karena tabel bantu terdapat pada Sheet 2.
• "B2:D7" adalah range tabel bantu yang kita gunakan. Kenapa dibuat absolut (menggunakan tanda $)? Karena pada kolom jabatan semua data-nya diambil dari tabel B2:D7.
• "2" menunjukkan bahwa kita mengambil data dari kolom ke-2 pada tabel B2:D7
• "0" adalah range_lookup.

2. Kolom Keterangan
=VLOOKUP(RIGHT(C7,3),Sheet2!$K$2:$L$8,2,0)

•  "=VLOOKUP" adalah rumus yang menunjukkan bahwa kita menggunakan fungsi VLOOKUP alias fungsi pembacaan tabel secara vertikal.
• "RIGHT(C7,3) adalah data sumber atau data yang diketahui. Maksudnya, patokan kita untuk mencari data pada tabel bantu adalah 3 karakter pertama dari kanan pada cell C7.
• "Sheet2!" adalah karena tabel bantu terdapat pada Sheet 2.
• "K2:L8" adalah range tabel bantu yang kita gunakan. Kenapa dibuat absolut (menggunakan tanda $)? Karena pada kolom jabatan semua data-nya diambil dari tabel K2:L8.
• "2" menunjukkan bahwa kita mengambil data dari kolom ke-2 pada tabel B2:D7
• "0" adalah range_lookup.

3. Kolom Tanggal Masuk Kerja
=DATE(MID(C7,4,2),MID(C7,6,2),MID(C7,8,2))

• "=DATE" adalah rumus yang digunakan supaya data pada cell akan otomatis berformat menjadi sebuah tanggal.
• Rumus dasar tanggal adalah
  =DATE(year,month,day)
• "MID(C7,4,2)" menunjukkan bahwa tahunnya terdapat mulai karakter ke 4 pada cell C7 sepanjang 2 huruf berikutnya (karena digit ke-4 s.d. ke-9 pada nomor induk pegawai menunjukkan tanggal masuk kerja-nya).
• "MID(C7,6,2) menunjukkan bahwa bulannya terdapat mulai karakter ke 6 pada cell C7 sepanjang 2 huruf berikutnya (karena digit ke-4 s.d. ke-9 pada nomor induk pegawai menunjukkan tanggal masuk kerja-nya).
• "MID(C7,8,2) menunjukkan bahwa tanggalnya terdapat mulai karakter ke 8 pada cell C7 sepanjang 2 huruf berikutnya (karena digit ke-4 s.d. ke-9 pada nomor induk pegawai menunjukkan tanggal masuk kerja-nya).

4. Kolom Masa Kerja
=YEAR($D$3)-YEAR(F7)

Sebenernya aku krang paham maksud dari masa kerja, tapi aku menyimpulkan bahwa masa kerja adalah jarak tahun masuk kerja menuju sekarang.

• "=YEAR($D$3)" adalah rumus supaya dia hanya menampilkan tahun dari cell D3 --> 2013
• "=YEAR(F7)" adalah rumus supaya dia hanya menampilkan tahun dari cell F7 --> 1989
• "-" di tengah artinya sebagai pengurang; 2013-1989

5. Kolom Gaji Pokok
=VLOOKUP(LEFT(C7,2),Sheet2!$B$2:$D$7,3,0)

•   "=VLOOKUP" adalah rumus yang menunjukkan bahwa kita menggunakan fungsi VLOOKUP alias fungsi pembacaan tabel secara vertikal.
•  "LEFT(C7,2)" adalah data sumber atau data yang diketahui. Maksudnya, patokan kita untuk mencari data pada tabel bantu adalah 2 karakter pertama dari kiri pada cell C7.
•  "Sheet2!" adalah karena tabel bantu terdapat pada Sheet 2.
•  "B2:D7" adalah range tabel bantu yang kita gunakan. Kenapa dibuat absolut (menggunakan tanda $)? Karena pada kolom jabatan semua data-nya diambil dari tabel B2:D7.
• "3" menunjukkan bahwa kita mengambil data dari kolom ke-3 pada tabel B2:D7.
• "0" adalah range_lookup.

6. Kolom Bonus
=IF(H7<=$D$4,(VLOOKUP(D7,Sheet2!$F$2:$I$8,2,0)*Sheet1!I7),0)

"=IF" merupakan rumus fungsi logika. Perlu diketahui bahwa rumus dasar =IF adalah:
=IF(logical_test, [value_if_true], [velue_if_false])

Di mana
*logical_test: cell serta syarat yang menentukan apakah cell tersebut akan menghasilkan true atau false.
*value_if_true: hasil yang akan ditampilkan jika cell patokan memenuhi syarat.
*value_if_false: hasil yang akan ditampilkan jika cell patokan tidak memenuhi syarat.

Jika diterjemahkan dengan bahasa manusia, rumus pada Kolom Bonus di atas akan berbunyi seperti ini:

 Jika cell H7 lebih kecil/sama dengan cell D4, maka H7 akan berisi besarnya persentase bonus dikalikan gaji pokok. Jika tidak, maka H7 adalah nol.

• "Jika cell H7 lebih kecil/sama dengan cell D4," merupakan terjemahan dari rumus bagian
  =IF(H7<=$D$4,
• "maka H7 akan berisi besarnya persentase bonus dikalikan gaji pokok." merupakan terjemahan dari rumus bagian
  (VLOOKUP(D7,Sheet2!$F$2:$I$8,2,0)*Sheet1!I7),
• "Jika tidak, maka H7 adalah nol." merupakan terjemahan dari rumus bagian
  ,0)

Bisa dipahami? ;;)

7. Kolom Potongan
=IF(H7>$D$4,(MINUTE(H7)*10000),0)

Ini juga pake rumus fungsi logika nih.. Kalo diterjemahkan dengan bahasa manusia, rumus pada kolom Potongan di atas akan berbunyi seperti ini:

 Jika cell H7 lebih besar daripada cell D4, maka H7 akan berisi jarak menit pada cell H7 dikalikan dengan Rp10.000. Jika tidak, maka H7 adalah nol.

• "Jika cell H7 lebih besar daripada cell D4," merupakan terjemahan dari rumus bagian
  =IF(H7>$D$4,
• "maka H7 akan berisi jarak menit pada cell H7 dikalikan dengan Rp10.000." merupakan terjemahan dari bagian rumus
  (MINUTE(H7)*10000)
• "Jika tidak, maka H7 adalah nol." merupakan terjemahan dari rumus bagian
  ,0)

8. Kolom Gaji Bersih
=I7+J7-K7

•  Kolom gaji bersih berisi penjumlahan biasa, yaitu gaji pokok ditambah dengan bonus dikurangi dengan potongan.

Setelah terisi semua, bentuknya bakal kaya gini nih

Catatan:
Jika dengan rumus-rumus di atas terdapat kesalahan formula, coba ganti semua tanda koma (,) dengan tanda titik koma (;). Tidak semua MS Office Excel 2007 di setiap PC meminta koma (,) sebagai pemisah. Coba dicek di format formulanya ketika mengetikkan formula ;;)

Yang butuh file Excelnya untuk latihan bisa didownload di sini:
Download - lpk dinamicom.zip

Sekian dan semoga bermanfaat ;)