Lompat ke konten Lompat ke sidebar Lompat ke footer
Beranda / Excel / Template

Cara Membuat Absensi Siswa Otomatis di Excel (Rekap Sakit, Izin, Alpa per Bulan)

Ditulis oleh Terbit
Absensi siswa otomatis di Excel dengan kode H, S, I, A dan COUNTIF

Absensi siswa otomatis di Excel cukup diisi dengan huruf H, S, I, atau A per tanggal. Jumlahnya dihitung dengan =COUNTIF(D6:AH6,"S"), dan persentase kehadiran dihitung dari jumlah hadir dibagi semua hari efektif. Kalau tanggal di judul kolom dibuat dengan rumus DATE, satu sheet yang sama bisa dipakai untuk bulan apa saja. Cukup ganti angka bulannya, nama hari dan jumlah tanggalnya ikut menyesuaikan.

Dicek terakhir: 9 November 2026 oleh Arfan Putra. Semua rumus di artikel ini dan di template sudah dihitung ulang dan dicocokkan dengan hitungan manual untuk 30 siswa selama 6 bulan.

Unduh template gratis: absensi siswa 1 semester (Excel), berisi 6 sheet bulan (Juli–Desember 2026) dan rekap semester untuk 30 siswa. Nama dan NISN di dalamnya fiktif.

Catatan penulisan rumus: rumus di artikel ini memakai koma (,) sebagai pemisah. Kalau Excel menolak rumusnya, ganti koma dengan titik koma (;) sesuai pengaturan wilayah di Windows.

Susunan Tabel

BagianLetak di template
Tahun dan bulanC2 (tahun) dan C3 (bulan dalam angka, misalnya 11)
Nama hari dan tanggalBaris 4 dan 5, kolom D (tanggal 1) sampai AH (tanggal 31)
Data siswaBaris 6 sampai 35: nomor, NISN, nama, lalu kode kehadiran per tanggal
Jumlah H, S, I, AKolom AI, AJ, AK, AL
Persentase hadirKolom AM

Langkah 1: Tanggal dan Nama Hari Otomatis

Di sel tanggal 1 (D5), tulis rumus:

=IF(MONTH(DATE($C$2,$C$3,1))=$C$3,DATE($C$2,$C$3,1),"")

Untuk tanggal 2 di E5, ganti angka 1 terakhir di dalam DATE menjadi 2, dan seterusnya sampai 31 di AH5. Bagian MONTH(...)=$C$3 mencegah tanggal yang tidak ada. Contohnya 31 November: Excel mengubahnya menjadi 1 Desember, bulannya tidak sama dengan 11, sehingga selnya dikosongkan.

Sel ini berisi tanggal lengkap, jadi tampilannya diatur lewat format, bukan rumus:

  • Baris 5 (tanggal): klik kanan → Format Cells → Custom, isi d. Yang tampil hanya angka tanggalnya.
  • Baris 4 (nama hari): rumusnya sama, formatnya ddd. Excel menampilkan singkatan nama hari sesuai pengaturan wilayah Windows, misalnya Sen, Sel, Rab kalau formatnya Indonesia.

Dengan cara ini, sheet bulan Agustus cukup dibuat dengan menyalin sheet Juli lalu mengganti angka di C3 menjadi 8.

Langkah 2: Batasi Isian Hanya H, S, I, A

  1. Blok area isian kehadiran, di template D6:AH35.
  2. Pilih Data → Data Validation. Di bagian Allow pilih List, lalu isi Source dengan H,S,I,A.
  3. Klik OK. Setiap sel sekarang punya daftar pilihan, dan Excel menolak isian selain empat huruf itu.

Hari Minggu dan hari libur dikosongkan, bukan diisi H. Sel kosong tidak ikut dihitung, jadi persentase kehadiran hanya dihitung dari hari efektif.

Langkah 3: Hitung Jumlah dan Persentase

=COUNTIF($D6:$AH6,"H")

Rumus ini menghitung berapa kali huruf H muncul di baris siswa itu. Salin ke kolom sebelahnya, lalu ganti "H" menjadi "S", "I", dan "A". Tanda $ di depan huruf kolom menjaga rentangnya tetap D sampai AH saat rumus disalin ke samping.

Persentase kehadiran:

=IF(SUM(AI6:AL6)=0,"",AI6/SUM(AI6:AL6))

Hasilnya angka desimal. Ubah formatnya ke Percentage lewat tombol % di tab Home. Pengecekan SUM(...)=0 mencegah error #DIV/0! untuk siswa yang barisnya belum diisi sama sekali.

Langkah 4: Warna Otomatis untuk S, I, A, dan Hari Minggu

  1. Blok D6:AH35, lalu pilih Home → Conditional Formatting → Highlight Cells Rules → Equal To. Isi S dan pilih warna kuning. Ulangi untuk I (biru) dan A (merah).
  2. Untuk kolom Minggu, pilih Conditional Formatting → New Rule → Use a formula to determine which cells to format, lalu isi rumus =AND(D$5<>"",WEEKDAY(D$5)=1) dan pilih warna abu-abu.

WEEKDAY tanpa argumen kedua menghasilkan 1 untuk hari Minggu. Tanda $ di depan angka 5 membuat semua baris di kolom itu mengacu ke tanggal di baris 5. Hasilnya, satu kolom Minggu berwarna abu-abu dari atas sampai bawah.

Langkah 5: Rekap Satu Semester

Di sheet Rekap Semester, jumlah sakit siswa pertama selama Juli–Desember dihitung dengan menjumlahkan sel yang sama di setiap sheet bulan:

='Jul'!AJ6+'Agu'!AJ6+'Sep'!AJ6+'Okt'!AJ6+'Nov'!AJ6+'Des'!AJ6

Versi pendeknya memakai referensi 3D:

=SUM(Jul:Des!AJ6)

Rumus ini menjumlahkan AJ6 di semua sheet yang letaknya dari Jul sampai Des. Hati-hati: kalau ada sheet lain yang dipindah ke antara Jul dan Des, sheet itu ikut terhitung. Template memakai versi panjang supaya tidak terpengaruh urutan sheet.

Kolom keterangan menandai siswa yang alpanya sudah mencapai batas di sel L4 (contoh 3 hari):

=IF(G5>=$L$4,"Perlu dipanggil wali kelas","")

Angka sakit, izin, dan alpa dari rekap ini bisa langsung disalin ke rapor bayangan atau ke e-Rapor.

Kalau Hasilnya Error

  • Judul tanggal tampil angka seperti 46327. Itu nomor seri tanggal di Excel. Formatnya belum diatur. Ubah ke Custom d atau ddd.
  • Nama hari tampil dalam bahasa Inggris. Excel mengikuti pengaturan wilayah Windows. Kalau ingin tetap bahasa Indonesia di semua komputer, pakai format Custom [$-421]ddd.
  • Jumlah S, I, atau A selalu 0, padahal sudah diisi. Ada spasi yang ikut terketik, misalnya "S ". Hapus spasinya, atau pasang Data Validation agar isian selain H, S, I, A ditolak.
  • Muncul #DIV/0! di persentase. Baris siswa itu masih kosong. Pakai rumus persentase dengan pengecekan SUM(...)=0 di atas.
  • Kolom tanggal 29–31 tetap muncul di bulan Februari. Rumus tanggal belum memakai pengecekan MONTH(...)=$C$3.
  • Rekap semester tidak berubah setelah sheet bulan diisi. Perhitungan Excel di-set manual. Pilih Formulas → Calculation Options → Automatic.

Sumber