Cara Membuat Rekap Absensi Serba Otomatis di Excel

Pada artikel kali ini saya akan memberikan sebuah Tutorial bagaimana Cara Membuat Rekap Absensi Serba Otomatis di Excel. Sebelum kita ke tutorialnya kita cari dulu apa itu pengertian dari absensi.

Kalau kita sekilas mendengar kata absensi yang terbayang pasti absen sekolah dan absen kerja.

Absen merupakan daftar kehadiran. Adapun fungsi dari absen sangat banyak diantaranya pendataan kehadiran karyawan atau siswa, untuk menghitung Gaji dan lain-lain.

Pada daftar absen yang saya buat pada artikel ini adalah absensi untuk karyawan.

Absensi ini bersifat serba otomatis, dantaranya :

• Bulan, Tanggal dan Tahun otomatis
• Menandai hari libur nasianal secara otomatis dengan tanda warna
• Menandai hari Minggu secara otomatis dengan warna merah
• Mengitung rekapan kehadiran otomatis
• Menandai tanggal yang aktif sekarang

Kelebihan Absen Otomatis :

ketika kita sudah merekap kehadiran satu bulan penuh kita tidak perlu lagi mengganti tabel secara manual apalagi sampai edit sana edit sini. Cukup dengan mengklik sebuah combobox untuk beralih ke bulan berikutnya, secara otomatis tanggal, bulan, hari libur nasional dan hari minggu akan berubah sesuai kalender saat ini dan menandainya dengan warna masing-masing.

Untuk mengisi absen ini anda cukup mendelet absen kehadiran bekas bulan lalu dan mengisi ulang dengan bulan yang sekarang.

Seandainya anda membutuhkan data absensi bulan lalu pastikan sebelumnya Anda men Save As-nya dan bila nanti suatu saat kita membutuhkannya.

Berikut adalah langkah yang harus Anda lakuan untuk membuat Cara Membuat Rekap Absensi Serba Otomatis di Excel dibawah ini,

Cara Membuat Rekap Absensi Serba Otomatis di Excel

Gambar 1: Hasil Tabel Absensi

Sebelum kita mulai menggunakan rumusnya sebaiknya kita buat desain terlebih dahulu tabelnya dengan cara :

• Buka Excel Anda kemudian buat 2 buah Sheet, yaitu Sheet1 dan Sheet2
• Klik Sheet2 buatlah tabel seperti dibawah ini di Sheet2

Gambar 2 : Tabel Sumber

Pada Gambar diatas pada Sheet2 terdapat 2 tabel yaitu Tabel Bulan dan Tabel Tgl Libur.

Untuk Tabel Tanggal Libur diambil dari kalender tahun 2022 dengan cara catat dan masukan tanggal libur ke dalam tabel.

Setelah Anda mempunyai tabel seperti diatas langkah selanjutnya adalah membuat sumber data yang akan kita masukan nantinya kedalam absensi dengan cara :

Pada Tabel Bulan (Sheet2) :

• Klik cell A3, tahan dan drag ke cell A14 yaitu dari bulan Januari sampai Desember lalu klik Formula >> Difine Name maka akan muncul kotak dialog New Name pada kotak Name isikan bulan dan Ok

Pada Tabel Tgl Libur(Sheet2) :

• Klik cell E3, tahan dan drag ke E18 lalu klik Formula >>Difine Name >>, maka akan muncul kotak dialog New Name pada kotak Name isikan libur dan OK.

Sekarang tahap pertama Anda sudah selesaikan untuk membuat tabel sumber

Tahap selanjutnya kita akan membuat Tabel Rekapan Absensi pada Sheet1 dan buatlah tabel seperti dibawah ini pada Sheet1 :

Gambar 3 : Tabel Rekapan Absensi

Setelah tabel diatas telah terbentuk langkah selanjutnya kita akan membuat rumus Nama Bulan caranya:

Mencari  Nama Bulan

• Terlebih dahulu gabungjan kolom B3 dan C3 dengan cara klik Merge & Center maka kolom B3 dan C3 menjadi 1 kolom 
• Lalu langsung ketik rumusnya untuk mencari Nama Bulan yaitu dengan cara :
• Klik Tab Data >> Data Validation >>Pada Tab setting >> Allow : List, Source: =bulan lalu OK maka akan muncul sebuah Combobox seperti gambar dibawah ini :

Nama Bulan

Pada Gambar diatas nama bulan diambil dari Sheet2 dengan Define Name (=bulan)

Langkah selanjutnya kita mencari Tanggal Awal, caranya :

• Terlebih dahulu gabungkan cel B4 dan C4 dan pilih Merge & Center maka kolom tersebut akan menyatu menjadi satu.
• Lalu langsung ketik rumusnya =DATEVALUE("1"&B3) (B3 adalah cell Nama Bulan) lalu Enter dan akan muncul Tanggal Awal pada bulan sekarang dengan format 6 digit angka, untuk merubah menjadi tanggal caranya Klik kanan Cell tersebut >> Format Cells >>Custom pada Type ketik DD-MM-YYYY lalu OK maka format akan menjadi Tanggal Awal sekarang misalnya seperti 01-11-2022

Langkah berikutnya kita mencari Tanggal Akhir, caranya :

• Sama dengan langkah diatas, terlebih dahulu gabungkan cell, 
• Lalu ketik rumusnya =EOMONTH(B4;0) (B4 adalah cell Tanggal Awal) lalu tekan Enter, maka akan muncul 6 digit angka Format Tanggal. Lalu ubah formatnya caranya Klik kanan Cell tersebut >> Format Cells >> Custom pada Type ketik DD-MM-YYYY lalu OK maka format akan berubah menjadi tanggal akhir sekarang misalnya seperti 30-11-2022

Setelah kita menentukan Bulan, Tanggal Awal dan Tanggal Akhir sekarang coba Anda klik pada Combobox nama bulan, lalu pilih nama bulan misalnya "Agustus" maka secara otomatis Tanggal Awal dan Tanggal Akhir akan berubah sesuai kalender saat ini.

Langkah selanjutnya kita membuat Urutan Tanggal, caranya :

• Klik pada D9 ketik rumus berikut =B4, B4 adalah Tanggal Awal, lalu ubah formatnya klik kanan Cell >>Format Cell >> Custom >>Type "dd" lalu Ok.
• Setelah itu urutkan tanggalnya sampai akhir bulan caranya Klik pada cell E9 ketik rumusnya =IF(D9<$E$4;D9+1;"") lalu copy rumusnya dari cell E9 sampai AH9 atau dari tanggal 1 sampai 31

Langkah selanjutnya kita akan mencari Nama Hari, caranya :

Ketik pada cell D8, =TEXT(D9;"ddd") tekan Enter, lalu copy rumusnya dari cell D8 sampai AH8 atau sampai tanggal 31

Sekarang kita akan menandai Libur Nasional dengan Warna, caranya :

• Blok cell D8 sampai AH29 >> Klik Tab Home >> Conditional Formating >> New Rules... >> Use a formula to determine which cells to format, lihat pada gambar dibawah ini :

Format Rule

Pada Format Values where this formula is true, isikan rumus =COUNTIF(libur;D$9) kemudian klik Format >>Fill >> tentukan warnanya, libur nasional misalnya ditandai warna kuning tekan Ok.

Berikutnya kita akan mendai Hari Minggu dengan Warna, caranya:

Langkahnya sama dengan langkah diatas dengan cara blok dari D8 sampai AH29,hanya berbeda pada penulisan rumus Pada Format Values where this formula is true. Untuk menandai hari minggu gunakan rumus =OR(D$8="Mgg"), kemudian klik Format >>Fill >> tentukan warnanya. Untuk Hari Minggu tandai dengan pilih warna merah dan Ok.

Langkah selanjutnya kita akan menghitung Total Hadir per Hari atau per Tanggal, caranya :

Tempatkan pada cell D30, ketik rumusnya =COUNTIF(D10:D29;"H") lalu copy rumusnya dari cell D30 samapai AH30 .

Menghitung Total Alpa per Hari atau per Tanggal, caranya :

Tempatkan pada cell D31, ketik rumusnya =COUNTIF(D10:D29;"A") lalu copy rumusnya dari cell D31 samapai AH31 .

Menghitung Rekapan Total Kehadiran (H) per Bulan, caranya;

Tempatkan pada AJ10, ketik rumusnya =COUNTIF(D10:AH10;"H"), lalu Copy rumusnya ke bagian cell bawahnya samapi ke cell AJ29

Menghitung Rekapan Total Alpa (A) per Bulan, caranya;

Tempatkan pada AK10, ketik rumusnya =COUNTIF(D10:AH10;"A"), lalu Copy rumusnya ke bagian cell bawahnya samapi ke cell AK29

Menghitung Total Cuti (C) per bulan, caranya :

Tempatkan pada AL10, ketik rumusnya =COUNTIF(D10:AH10;"C"), lalu Copy rumusnya ke bagian cell bawahnya samapi ke cell AL29

Menghitung Total Cuti Khusus (CK) per bulan, caranya :

Tempatkan pada AM10, ketik rumusnya =COUNTIF(D10:AH10;"CK"), lalu Copy rumusnya ke bagian cell bawahnya samapi ke cell AM29

Jika anda sudah mengikuti langkah-langkah dari awal sampai akhir dengan benar maka hasil tabelnya akan tampak pada Gambar 1 seperti diatas artikel ini.

Catatan :

Anda bisa memodifikasi Tabel Absensi buatan Anda sendiri sesuai selera Anda

Demikinalah artikel tentang Cara Membuat Rekap Absensi Serba Otomatis di Excel, semoga bermanfaat buat kita semua. Amin.

Jika ada masalah dan keluhan ketika anda membuat tutorial ini silahkan tulis dalam komentar.Jangan lupa share dan bagkan ke temen anda semua pada tombol dibawah ini