TL;DR
Analitik absensi di Google Sheets dimulai dari tiga kolom mentah: nama karyawan, tanggal, dan jam masuk. Dari situ kamu bisa hitung persentase keterlambatan per orang, per hari dalam seminggu, dan per shift pakai COUNTIFS plus fungsi WEEKDAY. Angka yang paling berguna biasanya bukan siapa yang paling sering telat, tapi hari dan jam apa keterlambatan menumpuk, karena itu nunjuk ke masalah jadwal, bukan masalah orang.
Data absensi di kebanyakan perusahaan cuma dipakai buat satu hal: motong tunjangan kehadiran.
Padahal file yang sama bisa nunjukin hari apa orang paling sering telat, shift mana yang paling berat, dan divisi mana yang lembur diam-diam. Semua itu bisa kamu kerjain di Google Sheets tanpa tools tambahan.
Aku pakai data absensi 42 karyawan sebuah UMKM distribusi selama 3 bulan sebagai contoh. Nama dan angkanya udah disamarkan, tapi polanya asli.
Minimal tiga kolom: nama atau ID karyawan, tanggal, dan jam masuk. Dengan tiga kolom itu kamu udah bisa hitung persentase keterlambatan, pola per hari, dan tren per bulan. Kolom jam pulang, kode shift, dan divisi bikin analisanya jauh lebih tajam, tapi bukan syarat mulai.
Bentuk data yang benar itu satu baris per karyawan per hari. Bukan satu baris per karyawan dengan 30 kolom tanggal.
Kalau mesin fingerprint kamu ekspor dalam format lebar (tanggal jadi kolom), ubah dulu jadi format panjang. Di Google Sheets kamu bisa pakai FLATTEN, dan di Excel modern ada TOCOL. Cara mengubah bentuk data ini ada di tutorial FLATTEN Google Sheets.
Empat tab. Jangan lebih dulu.
| Tab | Isi | Sumber |
|---|---|---|
absensi_mentah | id_karyawan, tanggal, jam_masuk, jam_pulang, kode_shift | Ekspor mesin absensi |
karyawan | id_karyawan, nama, divisi, tanggal_masuk_kerja | Data HR |
shift | kode_shift, jam_mulai, jam_selesai, toleransi_menit | Kamu isi manual |
hitung | Semua kolom turunan dan rekap | Rumus |
Aturan yang aku pegang: tab absensi_mentah nggak boleh disentuh rumus sama sekali. Semua perhitungan pindah ke tab hitung. Jadi waktu bulan depan kamu tempel data baru, nggak ada rumus yang rusak.
Mulai dari kolom penanda telat. Ini pondasi semua hitungan lain.
=IF(D2=""; ""; IF(D2 > VLOOKUP(F2; shift!$A:$B; 2; FALSE) + shift_toleransi; 1; 0))Versi yang lebih rapi pakai XLOOKUP dan langsung ngambil toleransi dari tabel shift:
=IF(D2=""; "";
IF(D2 > XLOOKUP(F2; shift!$A:$A; shift!$B:$B) + XLOOKUP(F2; shift!$A:$A; shift!$D:$D)/1440;
1; 0))Angka 1440 itu jumlah menit dalam sehari. Toleransi 10 menit dibagi 1440 jadi nilai waktu yang bisa dijumlah ke jam mulai shift. Argumen lengkapnya ada di halaman XLOOKUP.
Setelah kolom penanda ini jadi, sisanya tinggal ngitung. Persentase telat per karyawan:
=COUNTIFS($A:$A; $H2; $G:$G; 1) / COUNTIFS($A:$A; $H2; $G:$G; "<>")Kolom G itu penanda telat, kolom H daftar id karyawan unik. Penjelasan lengkap argumen COUNTIFS ada di halaman fungsinya.
Buat durasi kerja per hari:
=IF(OR(D2=""; E2=""); ""; (E2 - D2) * 24)Dikali 24 biar hasilnya jam desimal, gampang dirata-rata. Hasil 8,5 artinya 8 jam 30 menit.
Daftar lengkap fungsi yang tersedia ada di daftar fungsi resmi Google Sheets.
Bukan ranking karyawan paling telat. Itu yang paling gampang bikin salah keputusan.
Mulai dari tiga potongan ini:
Buat poin pertama, rumus hari-nya begini:
=TEXT(B2; "dddd")Detail kode format teks lainnya ada di halaman TEXT.
Data mentahnya 2.520 baris, hasil dari 42 karyawan dikali 60 hari kerja. Batas masuk jam 08:00 dengan toleransi 10 menit.
Angka besarnya: tingkat keterlambatan keseluruhan 14,2 persen. Artinya sekitar 1 dari 7 kehadiran kena telat.
Kalau berhenti di sini, keputusan yang muncul biasanya "perketat sanksi". Tapi pecahan per hari nunjukin hal lain:
| Hari | Tingkat telat | Rata-rata menit telat |
|---|---|---|
| Senin | 26,8% | 18 menit |
| Selasa | 11,3% | 9 menit |
| Rabu | 10,1% | 8 menit |
| Kamis | 9,7% | 11 menit |
| Jumat | 13,2% | 12 menit |
Senin tingkat telatnya 2,6 kali lipat hari lain. Dan bukan cuma lebih sering, tapi juga lebih lama.
Waktu ditelusuri, penyebabnya rapat koordinasi mingguan yang dipindah dari Jumat sore ke Senin pagi jam 08:30 delapan bulan sebelumnya. Karyawan yang tau rapat baru mulai 08:30 mulai santai berangkat.
Temuan kedua ada di sebaran menit. Dari 358 kejadian telat, 211 di antaranya (59 persen) cuma telat di bawah 15 menit. Yang telat di atas 45 menit cuma 24 kejadian, dan 19 dari 24 itu datang dari 3 orang yang sama.
Jadi masalahnya kepecah dua: masalah jadwal yang kena hampir semua orang, dan masalah individu yang kena 3 orang. Dua-duanya butuh penanganan beda, dan angka rata-rata 14,2 persen tadi nutupin keduanya.
Cara nyari kelompok yang perilakunya beda kayak gini masuk ke ranah segmentation.
Absensi ngukur kehadiran, bukan hasil. Supaya berguna, sandingin sama satu angka output yang beneran kamu catat.
Di kasus UMKM distribusi tadi, output-nya jumlah surat jalan yang diproses per hari. Aku bikin tabel per tanggal dengan dua kolom: jumlah karyawan hadir tepat waktu dan jumlah surat jalan selesai.
Hasilnya nggak seperti dugaan. Korelasi antara kehadiran tepat waktu dan jumlah surat jalan cuma 0,21. Lemah.
Yang korelasinya jauh lebih kuat itu jumlah armada tersedia, di angka 0,68. Artinya bottleneck-nya di kendaraan, bukan di jam masuk orang.
Temuan ini yang bikin manajemen batal nambah sanksi keterlambatan. Cara ngitung angka korelasinya ada di tutorial CORREL.
Ingat, korelasi bukan sebab akibat. Angka 0,68 cuma ngasih arahan mau cek apa duluan.
Bikin ranking karyawan paling telat lalu tempel di grup. Ini rusakin kepercayaan dan bikin orang mulai titip absen. Data kamu jadi lebih jelek dari sebelumnya.
Ngitung rata-rata jam kerja tanpa buang hari libur. Baris kosong ikut kehitung, rata-ratanya jadi turun palsu. Filter dulu baris yang jam masuknya kosong.
Ngeliat persentase tanpa ngeliat jumlah. Karyawan baru yang masuk 5 hari dan telat 2 kali punya angka 40 persen. Kelihatan paling parah, padahal datanya belum cukup. Kasih batas minimal 20 hari kehadiran sebelum masuk ranking.
Lupa shift malam yang lewat tengah malam. Jam pulang 01:00 dikurangi jam masuk 22:00 ngasih hasil negatif. Tambahin 1 hari ke jam pulang kalau lebih kecil dari jam masuk.
Nyimpen file absensi di sheet yang aksesnya terbuka. Data kehadiran itu data karyawan. Batasi yang bisa buka, dan pisahin sheet rekap dari sheet mentah kalau mau dibagikan ke atasan.
Nggak ngecek data ganda. Mesin fingerprint kadang nyatet dua kali dalam satu menit. Cek dulu pakai cara hapus duplikat di Google Sheets sebelum ngitung apa pun. Ini bagian dari kerja data quality yang sering dilewat.
Pakai perbandingan waktu langsung. Kalau jam masuk ada di kolom C dan batas masuk jam 08:00, rumusnya =IF(C2>TIMEVALUE("08:00");1;0). Hasilnya 1 kalau telat, 0 kalau nggak. Setelah kolom penanda ini jadi, semua hitungan lain tinggal SUMIFS atau COUNTIFS di atasnya. Jangan hitung telat langsung di pivot, bikin kolom penanda dulu.
Bikin tabel referensi shift dengan kolom kode shift dan jam mulai. Tarik jam mulai ke tabel absensi pakai XLOOKUP berdasarkan kode shift, baru bandingin sama jam masuk aktual. Cara ini juga bikin kamu gampang ubah jadwal tanpa nyentuh rumus. Kalau shift malam lewat tengah malam, tambahin 1 hari ke jam pulang sebelum hitung durasi.
Nggak langsung. Absensi cuma ngukur kehadiran, bukan hasil kerja. Yang bisa kamu lakukan itu nyandingin data absensi sama output nyata, misal jumlah pesanan diproses per hari, lalu lihat apakah ada kaitan. Kalau ternyata nggak ada kaitan, itu temuan berguna juga: berarti masalah produktivitas kamu bukan di jam hadir.
Buat keperluan analisa pola, 6 sampai 12 bulan cukup karena kamu butuh lihat musim ramai dan sepi. Buat keperluan administrasi kepegawaian, ikuti kebijakan HR dan aturan ketenagakerjaan yang berlaku di perusahaan kamu. Data absensi termasuk data karyawan, jadi batasi aksesnya dan jangan sebar file mentahnya ke sheet yang bisa dibuka semua orang.
Kuat sampai puluhan ribu baris kalau rumusnya rapi. Yang bikin lemot itu rumus array di seluruh kolom dan referensi ke kolom penuh kayak A:A yang dipakai berulang. Batasi rentang ke jumlah baris yang beneran ada, ganti hitungan berulang jadi satu kolom bantu, dan pakai QUERY buat agregasi ketimbang puluhan COUNTIFS.
Dua hal yang aku ambil dari kasus 42 karyawan tadi. Angka rata-rata sering nutupin dua masalah berbeda yang butuh solusi berbeda. Dan pola per hari biasanya nunjuk ke jadwal, bukan ke orang.
Coba mulai dari yang paling murah. Ambil data absensi 3 bulan terakhir, bikin satu kolom penanda telat, lalu pecah per hari dalam seminggu. Lima menit kerja, dan biasanya langsung kelihatan ada satu hari yang menonjol.
Kalau kamu belum punya format pencatatannya, mulai dari template Excel absensi biar struktur kolomnya udah rapi dari awal.
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
Punya ratusan angka dan mau tau sebarannya masuk ke rentang mana aja? FREQUENCY ngitung distribusi frekuensi sekali jalan, tanpa nulis COUNTIFS berkali-kali.
SUMIFS ribet kalau kriterianya banyak dan sering ganti. Rumus database Excel kayak DSUM baca kriteria dari sel terpisah, jadi kamu tinggal ubah tabel kecil tanpa nyentuh rumus.
DATEDIF ngasih umur bulat, tapi kadang kamu butuh angka desimal kayak 27,6 tahun buat hitung masa kerja atau bunga. YEARFRAC ngasih pecahan tahun dengan presisi.