Rumus IF Untuk Menghitung Jam Lembur

Sering Bekerja Lembur Hampir Setiap hari ? yuk kita otak-atik rumus excel untuk menghitung jam lembur dan upahnya. Dengan sedikit logika dan penggunaan rumus IF beberapa kondisi, dibantu fungsi MAX dan MIN, kita bisa menghitung berapa sih jam lembur yang harus dibayar, yang merupakan hak pekerja atas jerih payah dan keringatnya yang tidak mengenal lelah untuk perusahaan tercinta.

Contoh Soal:

Si Badu Bekerja sebagai karyawan di sebuah perusahaan yang mempekerjakan karyawannya 6 hari dalam seminggu. Ia merupakan karyawan tetap dan dibayar secara bulanan dengan total upah tetap (pokok + tunjangan) senilai Rp. 3.460.000. Dikarenakan perusahaan tempat ia bekerja sangat dinamis, menuntut karyawannya untuk siap bekerja kapan saja tanpa mengenal waktu, Si Badu pun bekerja selalu bekerja melebihi jam dinas. Bahkan pada hari libur resmi dan hari minggu pun, terkadang si Badu harus masuk kantor untuk menyelesaikan tugas dengan loyalitas tiada banding.

Sebagai kompensasi atas kerja kerasnya, Si Badu menerima upah kerja over time yang entah kenapa ia merasa nilainya tidak sebanding dengan jerih payah dan waktu yang ia korbankan. Merasa ada yang tidak beres, maka si Badu meminta tolong anda untuk menghitungkan upah lembur dia yang seharusnya sesuai ketentuan pemerintah, sebagai data pendukung untuk komplain ke atasannya.

Berikut data jam masuk dan jam pulang Si Badu dalam waktu Seminggu.

Sebelum membahas rumus excel untuk menghitung jam dan upah lembur, alangkah baiknya terlebih dahulu kita mengetahui ketentuan perhitungan upah lembur yang berlaku. Perhitungan lembur dalam pembahasan contoh rumus excel kali ini berdasarkan pada  KEPMEN NO. 102 TH 2004.

Untuk pekerja yang upahnya dibayar secara bulanan, maka nilai upah sejam = upah sebulan dibagi dengan 173. Si Badu bekerja dengan mendapatkan total upah tetap bulanan senilai Rp. 3.460.000, sehingga upah sejam = Rp 3.460.000/173 = Rp. 20.000/jam.

Si Badu bekerja di perusahaan yang memperkerjakan karyawannya 6 hari dalam seminggu, sehingga kita gunakan perhitungan sesuai pasal 11 point a. dan b. 

Berdasarkan ketentuan tersebut, ada 3 skenario perhitungan lembur yang berbeda yaitu.

1. Lembur pada hari kerja
2. Lembur pada hari libur istirahat mingguan atau pada hari libur resmi yang jatuh pada hari biasa
3. Lembur pada hari libur resmi yang jatuh pada hari kerja terpendek. Anggaplah hari Sabtu sebagai hari kerja terpendek.

Ketiga kondisi tersebut dapat digabungkan menggunakan rumus IF. Namun karena rumus cukup panjang, alangkah baiknya kita bahas terlebih dahulu masing-masing kondisi supaya memudahkan pemahaman.

Menghitung Jam Lembur Karyawan Pada Hari Kerja.

Dengan mengacu ke ketentuan pemerintah, upah lembur pada hari kerja dihitung sebagai berikut:

• Jam kerja lembur satu jam pertama harus dibayar sebesar 1,5 kali upah sejam
• Jam kerja lembur selanjutnya harus dibayar sebesar 2 kali upah sejam

Ketentuan untuk menghitung jam lembur dibayar pada hari kerja tersebut dapat dituliskan dalam bentuk formula excel sebagai berikut:

=MIN(1,jamLembur)*1.5+MAX(0,jamLembur-1)*2

• Bagian rumus MIN(1,jamLembur)*1.5 digunakan untuk menghitung jumlah jam lembur di bayar untuk kerja lembur pada jam pertama.
• Jika lembur lebih dari 1 jam  ⇒ Jam lembur dibayar pada 1 jam pertama = 1 x 1,5 = 1,5 jam.
• Jika lembur dari 1 jam ⇒ Jam Lembur dibayar pada 1 jam pertama = jamLembur x 1,5 jam.

• Bagian Rumus MAX(0,jamLembur-1)*2 digunakan untuk menghitung jam lembur di bayar untuk kerja lembur pada jam ke-2 dan seterusnya
• Jika lembur kurang dari 1 jam ⇒  jam lembur dibayar pada jam ke-2 dan seterusnya = 0
• Jika lembur lebih dari 1 jam ⇒  jam lembur dibayar pada jam ke-2 dan seterusnya = (jamLembur-1) x 2 jam.

Untuk lebih jelasnya, perhatikan gambar berikut.

Penjelasan rumus:

Rumus nomor 1: =IF(D2="",E2-C2,E2-D2)

Digunakan untuk menghitung selisih antara jam pulang lembur dengan jam pulang normal  pada hari kerja.  Fungsi IF digunakan untuk mengecek sel D2 (jam pulang normal) apakah memiliki konten atau sebaliknya (blank). Jika blank maka akan dianggap sebagai hari libur, dan jam kerja lembur dihitung sebagai selisih jam pulang dengan jam masuk. Hasil rumus ini sebenarnya berupa bilangan desimal yang diformat hh:mm untuk menampilkan jam dan menit.

Rumus nomor 2:  =F2*24

Formula tersebut digunakan untuk mengkonversi nilai waktu (hasilrumus nomor 1) menjadi bilangan jam. Caranya dengan mengalikan nilai waktu dengan 24.

Rumus nomor 3:  =MIN(1,G2)*1.5+MAX(0,G2-1)*2

Formula ini digunakan untuk menghitung faktor pengali atau jumlah jam lembur dibayar (hasil nomor rumus nomor 2) pada hari kerja dengan mengacu pada KEPMEN NO. 102 TH 2004. Detail cara kerja rumus sudah dijelaskan di atas.

Rumus nomor 4:  =H2*20000

Rumus yang terakhir ini digunakan untuk mendapatkan nilai upah lembur dalam sehari pada hari kerja. Karena upah sejam si Badu adalah Rp 20.000, maka upah yang harus dibayar = jumlah jam lembur dibayar x Rp 20.000

Menghitung Jam Lembur Karyawan Pada Hari Minggu dan Libur Resmi Yang Jatuh pada hari biasa.

Sesuai ketentuan : 

Apabila lembur kerja dilakukan pada hari istirahat mingguan dan / atau hari libur resmi untuk waktu hari kerja 40 (empat puluh) jam seminggu maka:

• Perhitungan upah kerja lembur pada 7 (tujuh) jam pertama dibayar 2 (dua) kali upah sejam, dan jam kedelapan dibayar 3 (tiga) kali upah sejam dan jam lembur kesembilan dan kesepuluh dibayar 4 (empat) kali upah sejam.

Ketentuan di atas dapat diterjemahkan ke dalam rumus excel sebagai berikut:

=MIN(7,jamLembur)*2+MIN(MAX(0,jamLembur-7),1)*3+MAX(0,jamLembur-8)*4

• Bagian rumus MIN(7,jamLembur)*2 digunakan untuk menghitung jam lembur dibayar pada 7 jam pertama.
• Jika lembur kurang dari 7 jam ⇒ jam lembur dibayar = jamLembur x 2 jam.
• Jika lembur lebih dari 7 jam ⇒ jam lembur dibayar = 7 x 2 jam
• Bagian rumus MIN(MAX(0,jamLembur-7),1)*3 digunakan untuk menghitung jam lembur dibayar pada jam ke-8.
• Jika lembur kurang dari 7 jam ⇒ jam lembur dibayar pada jam ke-8 = 0
• Jika lembur lebih dari 7 jam ⇒ jam lembur dibayar pada jam ke-8 = maksimum 1 x 3 jam.
• Bagian rumus MAX(0,jamLembur-8)*4 digunakan untuk menghitung jumlah jam lembur dibayar pada jam ke-9 dan seterusnya.
• Jika lembur kurang dari 8 jam ⇒ jam lembur dibayar pada jam ke-9 dan seterusnya = 0
• Jika lembur lebih dari 8 jam ⇒ jam lembur dibayar pada jam ke-9 dan seterusnya = (jamLembur – 8) x 4 jam.

Penerapannya dalam excel akan seperti ini.

 .

Menghitung Jam Lembur Karyawan Pada Hari Libur Resmi Yang Jatuh Pada Hari Kerja Terpendek.

Sesuai ketentuan pemerintah: 

Apabila hari libur resmi jatuh pada hari kerja terpendek, perhitungan upah lembur 5 (jam) pertama dibayar 2 (dua) kali upah sejam, jam keenam 3 (tiga) kali upah sejam dan jam lembur ketujuh dan kedelapan 4 (empat) kali upah sejam,

Ketentuan pehitungan lembur pada hari libur yang jatuh pada hari kerja terpendek tersebut dapat dirumuskan sebagai berikut:

=MIN(5,jamLembur)*2+MIN(MAX(0,jamLembur-5),1)*3+MAX(0,jamLembur-6)*4

• Bagian rumus MIN(5,jamLembur)*2 digunakan untuk menghitung jam lembur dibayar pada 5 jam pertama.
• Jika lembur kurang dari 5 jam ⇒ jam lembur dibayar pada 5 jam pertama = jamLembur x 2 jam.
• Jika lembur lebih dari 5 jam ⇒ jam lembur dibayar pada 5 jam pertama = 5 x 2 jam

• Bagian rumus MIN(MAX(0,jamLembur-5),1)*3 digunakan untuk menghitung jam lembur dibayar pada jam ke-6.
• Jika lembur kurang dari 5 jam ⇒ jam lembur dibayar pada jam ke-6 = 0
• Jika lembur lebih dari 5 jam ⇒ jam lembur dibayar pada jam ke-6 = maksimum 1 x 3 jam.
• Bagian rumus MAX(0,jamLembur-6)*4 digunakan untuk menghitung jumlah jam lembur dibayar pada jam ke-7, ke-8 dan seterusnya.
• Jika lembur kurang dari 6 jam ⇒ jam lembur dibayar pada jam ke-7 dan seterusnya = 0
• Jika lembur lebih dari 6 jam ⇒ jam lembur dibayar pada jam ke-7 dan seterusnya = (jamLembur – 6) x 4 jam.

Penerapannya dalam excel akan seperti ini.

Mengkombinasikan rumus perhitungan jam lembur pada hari kerja dan hari libur.

Seperti sudah dipaparkan di bagian awal, type perhitungan faktor pengali  jumlah jam lembur dibayar dipengaruhi oleh 3 kondisi yaitu:

• Kondisi 1 = Hari Kerja
• Kondisi 2 = Hari libur istirahat mingguan atah hari libur resmi yang jatuh pada hari kerja biasa
• Kondisi 3 = Hari libur resmi yang jatuh pada hari kerja terpendek.

Masing-masing rumus untuk masing-masing kondisi sudah kita bahas di atas, dan penerapannya di excel dengan menggunakan contoh tabel jam masuk dan pulan kerja si Badu, kita dapat meringkas rumus excel penghitung jam lembur dibayar si badu pada sel H2 sebagai berikut:

• Kondisi 1             B2=1
• Kondisi 2             B2=2
• Kondisi 3             B2=3
• Rumus Kondisi 1  =MIN(1,G2)*1.5+MAX(0,G2-1)*2
• Rumus Kondisi 2  =MIN(7,G2)*2+MIN(MAX(0,G2-7),1)*3+MAX(0,G2-8)*4
• Rumus Kondisi 3  =MIN(5,G2)*2+MIN(MAX(0,G2-5),1)*3+MAX(0,G2-6)*4

Kemudian kita bisa mengkombinasikan ketiga rumus tersebut menggunakan bantuan fungsi IF. Secara umum rumusnya dapat dituliskan sebagai berikut:

=IF(kondisi1,rumus_kondisi1,IF(konsisi2,rumus_kondisi2,rumus_kondisi3))

Selanjutnya masing-masing parameter dalam fungsi IF bertingkat di atas, maka kita bisa menggantinya dengan rumus per masing-masing kondisi, sehingga rumus akhir dapat dituliskan sebagai berikut.

=IF(B2=1,MIN(1,G2)*1.5+MAX(0,G2-1)*2,IF(B2=2,MIN(7,G2)*2+MIN(MAX(0,G2-7),1)*3+MAX(0,G2-8)*4,MIN(5,G2)*2+MIN(MAX(0,G2-5),1)*3+MAX(0,G2-6)*4))

Dengan contoh rumus di atas, kita bisa menghitung berapa faktor pengali jumlah jam lembur dibayar. Selanjutnya hasil dari rumus tersbut dapat dikalikan dengan upah sejam untuk menghasilkan nilai jumlah upah lemburnya.

Berikut screenshot penerapan rumus akhir beserta hasilnya:

Catatan: Dalam pembahasan ini dititik beratkan pada pembahasan rumus No 3 (pada kolom H) karena saya fikir merupakan formula yang memerlukan kerutan kening lebih kuat dibandingkan ketiga rumus lainnya.

Demikian pembahasan mengenai cara menghitung jam lembur dibayar dengan bantuan fungsi IF, MIN dan MAX. Semoga mudah memahaminya dan semoga bermanfaat.

Artikel terkait:

• Menghitung Jam Kerja Karyawan
• Rumus IF untuk Range Nilai Ujian
• Rumus IF Bertingkat Untuk Penilaian Prestasi Kerja
• Rumus IF untuk Keterangan Lulus Tidak Lulus

Mencari Nilai Tertinggi dan Terendah

Mencari nilai tertinggi dan terendah di excel ternyata dapat dilakukan dengan sangat mudah. Salah satu caranya adalah dengan menggunakan rumus excel.  Penulisan formula untuk mencari nilai terbesar dan terkecil juga sangat ringkas dan sederhana. Cukup menggunakan satu fungsi, maka nilai paling tinggi dan paling rendah pun dapat segera diketahui. Ada beberapa fungsi yang dapat dimanfaatkan. Untuk mencari nilai tertinggi, kita bisa menggunakan salah satu dari fungsi MAX atau fungsi LARGE. Sedangkan untuk mencari nilai paling rendah kita bisa menggunakan fungsi MIN atau fungsi SMALL. 

Contoh Soal.

Misalnya sudah tersedia tabel berisi data nilai ujian siswa. Tabel terdiri atas 2 kolom. Kolom pertama (kolom A) berisi nama siswa, sedangkan kolom ke-2 (Kolom B) berisi nilai ujian. Selanjutnya anda ditugaskan untuk mencari :

• Nilai paling tinggi (maksimum)
• Nilai ke-2 tertinggi
• Nilai paling rendah (minimum)
• Nilai ke-3 terendah.

Kira-kira apa yang anda lakukan?

Untuk contoh tabel di atas sih gampang. Hanya dengan melototin data sebentar juga paling ketemu mana nilai yang paling besar dan mana yang paling kecil. Ya, memang untuk contoh kita gunakan data yang kecil dan sederhana. Manfaat sebenarnya akan terasa ketika kita bekerja dengan data yang banyak dan pekerjaan dilakukan secara berulang-ulang. Alih-alih mata perih memandangi data dan mencari nilai secara manual, lebih baik kita manfaatkan saja rumus excel.

Contoh Rumus Excel Untuk Menghitung Nilai Tertinggi.

Untuk mencari nilai terbesar, kita bisa menggunakan salah satu dari fungsi MAX atau fungsi LARGE.

Mencari Nilai Paling Tinggi Dengan Fungsi MAX

Fungsi MAX digunakan untuk mencari nilai maksimum atau nilai paling besar diantara elemen-elemen sebuah array atau diantara nilai sel-sel dalam range. Fungsi ini dapat dituliskan dengan syntax:

=MAX(bil1,bil2,bil3,…)

Dimana bil1,bil2, bil3 dan seterusnya merupakan bilangan-bilangan yang akan dicek mana yang merupakan nilai maksimum.

Misalnya: rumus =MAX(4,6,10,2,11) akan menghasilkan nilai 11 karena 11 merupakan nilai maksimum di antara bilangan yang dicek oleh funngsi MAX

Jika bilangan berada dalam sebuah range excel, maka kita bisa menggunakan satu range saja mewakili semua bilangan yang ada dalam range tersebut sebagai parameter fungsi MAX. Formula-nya dapat dituliskan sebagai berikut:

=MAX(range)

Dengan menggunakan referensi tabel contoh, maka parameter range dapat kita ganti menjadi A2:A9, sehingga rumus dapat dituliskan sebagai berikut: 

=MAX(B2:B9)

Dan hasilnya adalah 95, karena memang bilangan ini merupakan bilangan maksimum pada range A2:A9.

Tips: Untuk melihat elemen bilangan dalam range B2:B9, kita bisa merubah range tersebut menjadi array dengan cara letakan kursor pada formula bar dan seleksi bagian rumus B2:B9, kemudian tekan F9, sehingga rumus akan berubah menjadi:  =MAX({93;80;85;76;77;95;87;88}). 

Mencari Nilai ke-2 Tertinggi Menggunakan Fungsi LARGE

Fungsi LARGE berfungsi untuk mencari nilai ke-n tertinggi dari sebuah array atau range. Fungsi ini dapat dituliskan =LARGE(range, n)  dimana range adalah referensi range dimana kumpulan nilai berada, sedangkan n adalah nomor urutan tertinggi keberapa yang kita ingin cari.

Dengan pengertian di atas mak kita bisa menggantikan fungsi MAX dengan fungsi LARGE untuk mencari nilai tertinggi. Nilai tertinggi berarti nilai ke-1 tertinggi, maka kita bisa menuliskannya:

=LARGE(B2:B9,1)

Untuk mencari nilai ke-2 tertinggi maka kita cukup mengganti bilangan 1 menjadi 2 sehingga rumusnya menjadi:

=LARGE(B2:B9,2)

Dan hasilnya adalah 93.

Silahkan dicoba untuk menentukan rangking ke-3, ke-4 dan seterusnya.

Cukup mudah dan sederhana buka?

Contoh Rumus Excel Untuk Mencari nilai Paling Rendah.

Untuk mencari nilai Terendah, kita bisa menggunakan salah satu fungsi MIN atau SMALL

Mencari nilai terendah meggunakan fungsi MIN

Fungsi MIN bekerja sebaliknya dari fungsi MAX. fungsi ini berguna untuk mencari nilai minimum atau nilai paling rendah dari sekumpulan bilangan. Fungsi ini dapat dituliskan sebagai berikut:

=MIN(bil1,bil2,bil3,…)

Dimana bil1,bil2,bil3 dan seterusnya merupakan bilangan yang akan dicek oleh fungsi MIN. jika kumpulan bilangan tersebut terdapat dalam sebuah range excel, maka kita dapat menuliskan rumus MIN sebagai berikut:

=MIN(range)

Selanjutnya mari kita ganti parameter range tersebut menjadi referensi range B2:B9 sesuai tabel contoh.

=MIN(B2:B9)

Maka hasilnya adalah 76 yang merupakan nilai terendah diantara bilangan lainnya dalam range B2:B9.

Mencari nilai terendah ke-3 menggunakan fungsi SMALL.

Fungsi SMALL merupakan kebalikan dari fungsi LARGE. Fungsi ini berguna untuk mencari nilai ke-n terendah diantara bilangan dalam sebuah range.

Fungsi ini dapat dituliskan =SMALL(range,n). 

Sehingga untuk mencari nilai minimum (terkecil ke-1) dari bilangan dalam range B2:B9, kita bisa menuliskan rumusnya seperti ini:

=SMALL(B2:B9,1)  ⇒ hasilnya 76

Rumus tersebut bisa menggantikan rumus sebelumnya =MIN(B2:B9)

Sedangkan untuk mencari nilai ke-3 terbesar, maka rumusnya dapat dituliskan

=SMALL(B2:B9,3) ⇒ hasilnya 80

Rangkuman:

Sebagai ringkasan dari pembahasan diatas, maka untuk menjawab soal nilai tertinggi dan terendah diantara nilai ujian siswa (data pada range B2:B9) adalah sebagai berikut:

Rumus untuk Mencari Nilai Paling Tinggi:

=MAX(B2:B9)  atau  =LARGE(B2:B9,1)

Rumus untuk Mencari Nilai ke-2 Tertinggi

=LARGE(B2:B9,2)

Rumus untuk Mencari Nilai Paling Rendah:

=MIN(B2:B9)  atau  =SMALL(B2:B9,1)

Rumus untuk Mencari Nilai ke-3 Terendah

SMALL(B2:B9,2)

Penerapan rumus – rumus tersebut dapat dilihat dalam screenshot di bawah ini.

Demikian pembahasan ringkas mengenai contoh rumus untuk mencari nilai tertinggi (paling besar) dan terendah (paling kecil) diantara bilangan menggunakan fungsi MAX, LARGE, MIN dan SMALL.

Semoga bemanfaat.

Artikel terkait:

• Menjumlahkan nilai terendah di Excel
• Menjumlahkan nilai tertinggi di Excel
• Menjumlahkan nilai tertinggi dengan kriteria

Membuat Nomor Urut Dengan Mudah

Semua user kemungkinan besar pernah membuat nomor urut di excel. Ada beberapa cara yang dapat dilakukan, entah itu dengan mengetik manual, menggunakan auto fill, flash fill, maupun menggunakan rumus excel.  Nah dalam artikel ini kita akan membahas tehnik pembuatan nomor urut dengan mudah menggunakan rumus excel, mencakup nomor urut semua data tanpa mempedulikan sel kosong maupun nomor urut untuk sel berisi data saja. Ada beberapa formula sederhana dan fungsi yang dapat dimanfaatkan, diantaranya: Operasi Matematika penjumlahan, fungsi ROWS, dan fungsi IF COUNTA. 

Contoh Soal.

Telah tersedia sebuah tabel terdiri atas 2 kolom. Kolom pertama berisi nomor urut, sedangkan kolom ke-2 berisi data nama barang. Kolom pertama belum diisi dengan bilangan nomor urut sebagaimana mestinya. Selanjutnya tugas kita untuk membuat nomor urut tersebut menggunakan rumus excel.

Sesuai gambar di atas, ada dua asumsi yang akan berpengaruh pada rumus mana yang harus digunakan.

• Asumsi 1: Membuat nomor urut tanpa mempedulikan keberadaan sel kosong
• Asumsi 2: Membuat nomor urut agar sel kosong tidak dihitung dan tidak perlu ada nomor urutnya.

Baiklah kita lanjutkan ke pembahasan contoh rumus excel:

Rumus Nomor Urut Tanpa Mempedulikan Adanya Sel Kosong.

Contoh 1 : Menggunakan Operasi Matematika Penambahan

Rumus ini merupakan yang paling umum digunakan pengguna excel untuk membuat nomor urut. Formulanya sangat sederhana, cukup menggunakan operasi matematika penambahan dan tanpa perlu menggunakan fungsi excel.

Caranya: Pada baris pertama data: ketik angka 1  dan pada baris berikutnya merupakan rumus penambahan bilangan baris di atasnya dengan 1, begitu seterusnya.

Dengan menggunakan tabel sesuai contoh soal:

• Pada sel A2, ketik angka 1
• Pada sel A3, ketik rumus =A2+1
• Copy sel A3 ke sel-sel dibawahnya seterusnya sampai baris akhir data, seperti diperlihatkan hasilnya pada screenshot di bawah ini.

Contoh 2: Menggunakan Fungsi ROWS

Selain menggunakan operasi penambahan, kita juga bisa memanfaatkan fungsi ROWS untuk membuat nomor urut tanpa mempedulikan keberadaan sel kosong.

Sedikit mengulas tentang fungsi ROWS. Fungsi ini digunakan untuk menghitung jumlah baris range yang menjadi parameter fungsi. Misalnya kalau kita menuliskan =ROWS(A2:A3) maka rumus tersebut akan menghasilkan bilangan 2. Angka tersebut merupakan jumlah baris yang terdapat dalam range A2:A3.

Dengan menggunakan contoh tabel sebelumnya, Fungsi ROWS dapat diterapkan untuk membuat nomor urut dengan cara sebagai berikut:

• Di dalam sel A2, ketikan rumus  =ROWS($B$2:B2)
• Copy sel A2 tersebut ke range di bawahnya sampai baris akhir data.
• Hasilnya: semua baris data dapat terisi nomor urut, seperti terlihat dalam gambar berikut:

Cara kerja rumus ROWS.

Perhatikan penggunaan tanda dolar yang disematkan pada sel B2 pertama. Tanda dolar ini berguna untuk mengunci supaya pada saat rumus dicopy ke sel dibawahnya, referensi sel B2 pertama tidak berubah. Hal ini menyebabkan jumlah baris dalam referensi range fungsi ROWS akan semakin meningkat dan bertambah 1 setiap kali rumus di copy ke sel di bawahnya.

Sel A2  =ROWS($B$2:B2) ⇒  jumlah baris pada range $B$2:B2 = 1

Sel A3  =ROWS($B$2:B3) ⇒  jumlah baris pada range $B$2:B3  = 2

Sel A4  =ROWS($B$2:B4) ⇒  jumlah baris pada range $B$2:B4  = 3

Dan begitu seterusnya…

Rumus Nomor Urut Agar Sel Kosong Tidak Terhitung.

Supaya sel kosong tidak ada nomor urutnya, maka kita bisa memanfaatkan fungsi IF dan COUNTA 

Fungsi IF berguna untuk mengecek referensi sel apakah merupakan sel kosong atau sel berisi data. Jika sel kosong, maka jangan berikan penomoran. Sebaliknya maka lakukan penomoran.

Secara umum dapat dituliskan seperti ini:

=IF(sel_kosong, jangan_dinomori, nomori)

Selanjutnya Fungsi COUNTA berfungsi untuk menghitung jumlah sel yang tidak kosong.  Kegunaan ini dapat dimanfaatkan untuk membuat nomor urut. 

=IF(sel_kosong, jangan_dinomori, jalankan_COUNTA)

Untuk memudahkan pemahaman, mari kita praktekan menggunakan tabel contoh:

• Di dalam sel A2 ketik formula  =IF(B2<>"",COUNTA($B$2:B2),"")
• Kemudian copy rumus tersebut ke range di bawahnya sampai baris akhir data. Maka hasilnya akan nampak seperti gambah di bawah ini:

Dari gambar di atas, dapat kita lihat bahwa nomor urut hanya terdapat disamping sel yang berisi data.

Catatan Penting: Sama halnya dengan penggunaan fungsi ROWS, penggunaan fungsi COUNTA untuk membuat nomor urut juga harus menggunakan tanda dolar $ untuk referensi sel B1 pertama. Ini dimaksudkan supaya sel B1 pertama bersifat abolute sehingga jika rumus dicopy ke sel di bawahnya, referensi sel tidak berubah. Jika ini tidak dilakukan maka semua rumus hanya akan menghasilkan bilangan 1 atau kosong saja.

----

Demikian pembahasan singkat mengenai contoh rumus untuk membuat nomor urut di excel. Pembahasan mencakup nomor urut semua data tanpa mempedulikan sel kosong dan nomor urut hanya untuk sel yang berisi data saja.

Semoga bermanfaat

Contoh Rumus Excel Lainnya:

• Menghitung Jam Kerja Karyawan
• Menghitung Sel Kosong dan Tidak Kosong
• Komversi Text Menjadi Bilangan
• Rumus Lolos Gagal Tanpa Fungsi IF

Menghitung Jam Kerja Karyawan

Menghitung jam kerja karyawan harian sebenarnya dapat dilakukan secara mudah menggunakan microsoft excel. Namun dalam prakteknya masih dijumpai penggunaan excel yang tidak tepat dalam menghitung jam kerja karyawan. Kesalahan paling utama adalah kekurangtahuan dalam cara penulisan format waktu di excel. Dan yang kedua adalah ketidaktahuan akan kegunaan fungsi TIME. Pembahasan  kali ini mencakup dasar-dasar penulisan format waktu di excel dan dilanjutkan dengan cara menghitung jumlah jam kerja harian karyawan.

Contoh Soal:

Anggaplah kita sudah memiliki data masuk dan pulang kerja karyawan dalam bentuk tabel excel. Kolom A berisi data Jam masuk, kolom B  berisi data jam pulang. Data jam masuk dan jam pulang ditulis dalam format Jam:menit atau hh:mm. Kemudian kita ditugaskan untuk menghitung berapa jam dan berapa menit waktu kerja efektif karyawan. Waktu kerja dihitung sebagai selisih antara jam keluar (pulang) dengan jam masuk kemudian dikurangi waktu istirahat.

  

Perhatikan gambar di atas.

Berapa jam dan berapa menit waktu kerja karyawan dengan asumsi:

1. Waktu istirahat = 1 jam
2. Waktu itirahat = 45 menit

Untuk menjawab pertanyaan di atas, terlebih dahulu kita harus memahami konsep penulisan waktu di excel.

Waktu di excel sebenarnya berupa bilangan pecahan 0 s/d 1 yang diformat sedemikian rupa sehingga tampilan dalam sel menunjukan minimal salah satu dari data jam (h), menit (m) dan detik (s)

Berikut contoh value waktu di excel yang ditampilkan dalam beberapa format number.

Tabel di atas, perbarisnya menunjukan nilai bilangan yang sama namun dalam bentuk format yang berbeda. Waktu sebernarnya merupakan bentuk pengulangan dari 0 s.d 1 setiap hari.  Mungkin yang sedikit membingungkan adalah waktu jam 00:00 dengan 24:00, karena sebenarnya sama. Angka yang lebih tinggi menunjukan sudah adanya pergantian hari. 

Baiklah untuk pembahasan detail mengenai konsep waktu di excel, insya Alloh akan sy persiapkan dalam kesempatan lain.

Selanjutnya mari kita lanjutkan pembahasan utama yaitu bagaimana menghitung waktu kerja karyawan.

Rumus Umum.

Untuk menghitung jam kerja karyawan dengan adanya jam istirahat, secara umum rumusnya dapat dituliskan sebagai berikut:

waktu_kerja = jam_pulang – jam_masuk – waktu_istirahat.

Data jam masuk dan jam keluar sudah tersedia dalam tabel (kolom A dan kolom B) dan dapat langsung digunakan sebagai referensi rumus. 

Bagaimana dengan waktu istirahat?

Nah, untuk waktu istirahat kita bisa memanfaatkan fungsi TIME. Fungsi ini secara sederhana dapat dituliskan dengan syntax sebagai berikut:

=TIME(jam,menit,detik)

Saya kira tidak perlu penjelasan lebih jauh mengenai parameter-parameter fungsi IF, karena pembaca pasti sudah sangat familiar dengan istilah waktu jam, menit, maupun detik.

Langsung saja kita lanjutkan ke contoh fungsi TIME dan pengertian hasilnya:

=TIME(1,0,0)   = 1 Jam atau 01:00:00

=TIME(0,45,0)  = 45 Menit atau 00:45:00

=TIME(1,40,59) = 1 Jam 40 Menit 59 Detik atau 01:40:59

Dengan mengambil contoh di atas, maka waktu istirahat selama 1 jam dapat kita tuliskan dengan rumus  =TIME(1,0,0). Sehingga dengan menggunakan contoh tabel dalam soal yang sudah ditunjukan di atas, kita dapat menuliskan rumus untuk menghitung jumlah waktu kerja karyawan sebagai berikut:

=B2-A2-TIME(1,0,0)

Penerapannya dapat dilihat dalam screenshot di bawah ini.

Sedangkan untuk menghitung jumlah jam kerja efektif karyawan dengan asumsi waktu istirahat 45 menit, maka kita hanya tinggal mengganti rumus TIME(1,0,0) menjadi TIME(0,45,0). Sehingga rumusnya menjadi:

=B2-A2-TIME(0,45,0)

Dan berikut penampakannya dalam lembar kerja excel:

Catatan: 

Penting untuk diperhatikan ketika kita mengetik tanggal. Sebaiknya gunakan tanda titik 2 ( : ) sebagai pemisah antara jam, menit dan detik. Jika tidak, maka kemungkinan excel tidak akan mengenali data yang kita input sebagai waktu. Kalaupun dikenali maka kemungkinan besar akan menampilkan waktu yang salah.

Misalnya: jika pada sebuah sel yang masih berformat default, diketikan 07.45 maka excel akan membacanya sebagai bilangan desimal 7.45 (jika setting tanda titik sebagai pemisah desimal). Jika 07.45 diketik pada sel yang format number nya hh:mm, maka yang akan muncul adalah 10:48.

Demikian pembahasan singkat mengenai contoh rumus excel untuk menghitung waktu jam kerja karyawan. Bahasan ini juga sedikit mengulas bagaimana mengetik waktu di excel.  

Semoga bermanfaat.

Artikel terkait:

• Rumus IF untuk Menghitung Jam Lembur Karyawan
• Rumus IF Bertingkat Untuk Penilaian Prestasi Kerja
• Menghitung Data Antara Dua Tanggal
• Rumus Lolos Gagal Tanpa Fungsi IF

Menghitung Cell Kosong dan Berisi Data

Untuk menghitung jumlah cell kosong dan  berisi data (tidak kosong) di excel dapat dilakukan dengan mudah menggunakan rumus.  Fungsi yang dapat digunakan untuk menghitung jumlah sel kosong adalah fungsi COUNTBLANK. Sedangkan untuk menghitung cell yang berisi data atau tidak kosong, kita bisa menggunakan fungsi COUNTA. Selain itu kita juga bisa memanfaatkan fungsi lainnya seperti COUNTIF, COUNTIFS dan SUMPRODUCT+LEN, baik untuk menghitung banyaknya sel kosong maupun sel yang berisi data.

Contoh Soal:

Misalnya kita ambil range A1:A8 dalam sebuah sheet, dimana range tersebut berisi data jenis buah-buahan, atau apalah yang anda inginkan. Namun sebagian sel dalam range A1:A8 tersebut dikosongkan karena kita ingin menguji bagaimana rumus penghitung sel kosong dan terisi dapat bekerja dengan benar.

Gambaran contoh soal dapat dilihat dalam screenshot di bawah ini.

Contoh Rumus Untuk Menghitung Sel Kosong

Untuk menghitung sel kosong kita bisa menggunakan fungsi COUNTBLANK. Fungsi ini dapat dituliskan dengan syntax yang sangat sederhana yaitu:

=COUNTBLANK(range)

Jika range kita ganti dengan referensi yang kita gunakan sebagai contoh yaitu A1:A8 maka rumus COUNTBLANK dapat kita tuliskan seperti di bawah ini:

=COUNTBLANK(A1:A8)

Karena dalam range A1:A8 ada 2 sel kosong yaitu sel A4 dan sel A7, maka rumus akan memberikan hasil informasi bilangan 2. 

Dari gambar diatas, rumus COUNTBLANK sudah berhasil menghitung jumlah sel kosong pada range A1:A8.  Gambaran dalam contoh mungkin nampak spele, karena data yang kita gunakan sangat sedikit. Manfaat rumus ini akan terasa ketika kita bekerja dengan data yang banyak.

Contoh Rumus untuk Menghitung Sel Tidak Kosong

Untuk menghitung sel yang berisi data , alias tidak kosong bin tidak blank, kita bisa menggunakan fungsi COUNTA. Sama hal nya dengan fungsi COUNTBLANK, fungsi COUNTA juga memiliki syntax yang sangat sederhana dengan cukup hanya satu argumen fungsi saja. Fungsi ini dapat dituliskan sebagai berikut:

=COUNTA(range)

Dimana range adalah range data yang ingin kita hitung jumlah sel yang berisi data dalam range tersebut. Selanjutnya jika referensi range contoh A1:A8 kita masukan sebagai argumen fungsi COUNTA,   maka kita bisa menuliskannya sebagai berikut:

=COUNTA(A1:A8)

Gambar di atas memperlihatkan bagaimana rumus excel COUNTA berhasil menghitung jumlah sel berisi data pada range A1:A8

Menghitung sel kosong dan tidak kosong menggunakan fungsi COUNTIF

Selain fungsi COUNTA dan COUNTBLANK, excel juga menyediakan fungsi lainnya yang dapat digunakan untuk menghitung sel kosong dan tidak kosong. Salah satunya adalah fungsi COUNTIF.

Fungsi COUNTIF dapat dituliskan dengan syntax:

=COUNTIF(range, criteria)

Dimana range adalah referensi range cell yang akan dicek, sedangkan criteria adalah string yang menunjukan syarat perhitungan. Kita dapat menuliskan criteria sebagai berikut:

Menghitung cell kosong: criteria   "<>*"

Menghitung cell berisi data: criteria "*"

Sehingga dengan menggunakan contoh range sebelumnya (range A1:A8) maka kita dapat menuliskan rumus sebagai berikut:

Menghitung cell Kosong:

=COUNTIF(A1:A8,"<>*")

Menghitng cell berisi data (tidak kosong)

=COUNTIF(A1:A8,"=*")

Dari gambar diatas kita bisa mengetahui bahwa fungsi COUNTIF dapat digunakan secara lebih luas. Fungsi ini mampu menghitung jumlah cell kosong maupun tidak kosong. Kuncinya adalah parameter criteria yang digunakan.

Selain menggunakan fungsi COUNTIFS sebenarnya masih ada fungsi lainnya yang dapat digunakan untuk menghitung sel kosong dan tidak kosong, misalnya fungsi COUNTIFS dan SUMPRODUCT.

Alternative Rumus Menghitung Sel Kosong  dan Tidak Kosong.

Menghitung Cell Kosong:

=COUNTIFS(A1:A8,"<>*")

=SUMPRODUCT(--(LEN(A1:A8)=0))

Menghitung Cell Tidak Kosong

=COUNTIFS(A1:A8,"=*")

=SUMPRODUCT(--(LEN(A1:A8)>0))

Catatan penting: 

• Fungsi COUNTA, COUNTBLANK, COUNTIF, COUNTIFS dan SUMPRODUCT&LEN akan menghitung sel yang berisi karakter yang tidak nampak sebagai sel yang berisi data atau tidak kosong. Keberadaan spasi dalam sel yang seperti kosong, sering menyebabkan hasil rumus tersebut tidak valid.

• Kecuali COUNTBLANK, semua fungsi yang dicontohkan diatas juga akan menghitung sel berisi data dengan jumlah karakter Nol yang merupakan hasil dari formula lain sebagai sel yang kosong, padahal ada formula di dalamnya. Misal sel berisi formula =""

Demikan pembahasan mengenai beberapa alternative rumus untuk menghitung banyaknya sel kosong dan sel berisi data (tidak kosong).

Semoga bermanfaat.

Artikel terkait: 

• Menghitung Jumlah Kata Tertentu dalam Range
• Menghitung Jumlah Data Unik dengan SUMPRODUCT
• Menghitung Data antara Dua Tanggal
• Menghitung Data antara Dua Bilangan

Konversi Text Menjadi Bilangan

Konversi text menjadi bilangan (numerik) cukup penting untuk diketahui karena ada beberapa kasus rumus yang bisa menghasilkan nilai error gara-gara masalah type data ini. Sebagai contoh: rumus VLOOKUP akan menghasilkan nilai error jika digunakan untuk mencari bilangan dalam sebuah kolom  berisi text yang nampak seperti bilangan. Untuk mengatasi problem ini, maka mau tidak mau kita harus mengkonversi terlebih dahulu text menjadi bilangan.

Mengenali Data Type Text dan Numerik (Bilangan)

Sebelum melangkah lebih jauh, alangkah baiknya kita memahami terlebih dahulu cara membedakan mana data type text dan mana data type numerik.

Yang dimaksud text disini adalah text yang menyerupai numerik karena penulisan bilangan didahului tanda petik 1 ( ‘ ) sehingga excel membacanya sebagai text. Kita sebut saja “text bilangan”. Aplikasi excel pun sebenarnya sudah menyediakan cara yang sangat mudah untuk mengidentifikasi data mana yang berupa text dan mana yang merupakan bilangan, seperti terlihat dalam screenshot di bawah ini:

Dari gambar di atas, type data text dapat diketahui dari 2 cara.

• Pertama : data text bilangan memiliki tanda segitiga kecil warna hijau pada pojok kiri atas sel.
• Kedua : Pada kondisi format default data text akan rata kiri, sedangkan data bilangan rata kanan.

Selain menggunakan cara visual seperti di atas, kita juga bisa mengenali data text dengan menggunakan bantuan fungsi ISTEXT. Fungsi ini akan memberikan nilai TRUE, jika parameter yang di-cek memang berupa text.

Fungsi ISTEXT dapat dituliskan dengan syntax sebagai berikut:

=ISTEXT(value)

Dimana value adalah data yang dicek, dalam hal ini bisa berupa referensi sebuah sel.

Misalnya: untuk mengecek data yang ada di sel A2 apakah merupakan text atau tidak, maka kita bisa menuliskan rumus seperti di bawah ini:

=ISTEXT(A1)

Perhatikan gambar diatas. Rumus =ISTEXT(A1) menghasilkan nilai FALSE, karen sel A1 berisi data type text, sedangkan pada baris ke-2, rumus =ISTEXT(A2) menghasilkan nilai TRUE karena sel A2 berisi data type numerik.

Setelah kita bisa membedakan mana text bilangan dan mana yang benar-benar bilangan, mari kita lanjut ke pembahasan utama yaitu contoh rumus untuk konversi text bilangan menjadi bilangan.

Contoh Rumus Konversi Text Menyerupai Bilangan Menjadi Numerik

Ada beberapa rumus yang dapat digunakan untuk mengkonversi text menjadi bilangan, diantaranya :

• Mengalikan text bilangan dengan Satu.
• Menambahkan text bilangan dengan Nol
• Menggunakan double unary ( -- )
• Menggunakan fungsi VALUE

Anggaplah kita ingin mengkonversi sebuah text bilangan yang terletak di sel A2. 

Contoh Rumus 1: Kita bisa membuat rumus perkalian sederhana  dengan referensi sel A2 dikalikan dengan angka 1.

=A2*1

Dari hasil perkalian tersebut, nampak hasilnya adalah data yang benar-benar bilangan yang ditandai format alignment rata kanan pada kondisi default.

Contoh Rumus 2:  Dengan cara menambahkan text bilangan dengan angka Nol

=A2*1

Contoh Rumus 3: Dengan menggunakan tanda minus ganda atau double unary (--)

tanda minus dua atau double unary nampaknya bekerja dengan mengalikan data terhadap -1 sejumlah 2 kali. Secara matematis, negatif dikali negatif samadengan positif.

=--A2

Contoh Rumus 4: Menggunakan fungsi VALUE

Fungsi VALUE dapat dituliskan dengan syntax =VALUE(text), jika text kita ganti dengan referensi  A1 maka rumus dapat dituliskan:

=VALUE(A2)

Selain menggunakan fungsi VALUE, kita juga bisa mencobanya dengan fungsi lainnya. Jika output yang dihasilkan berupa bilangan yang ditandai dengan format alignment rata kanan secara default, maka berarti fungsi tersebut berhasil mengkonversi text bilangan menjadi bilangan.

Dari contoh-contoh yang sudah diperlihatkan di atas dapat disimpulkan bahwa untuk mengkonversi text bilangan menjadi benar-benar bilangan (numerik) hanya diperlukan rumus yang sangat sederhana. Namun meskipun sangat sederhana, pemahaman mengenai hal sangat penting karena excel akan memperlakukan data text dan bilangan / numerik secara berbeda, dan hal ini bisa berpengaruh terhadap hasil kalkulasi data.

Demikian pembahasan singkat mengenai cara mengkonversi text bilangan menjadi bilangan / numerik. Semoga bermanfaat.

Artikel terkait:

• Menghilangkan Spasi Berlebih.
• Menghitung Jumlah Kata Tertentu Pada Sel dan Range.
• Memisahkan Nama Depan dan Belakang.
• Menghitung Total Jumlah Kata