Cara Praktis Membuat Absensi Siswa Otomatis di Excel untuk Bulan Berjalan

oleh | 27 Apr 2024 | Excel Advanced, Excel Elementary, Tips Excel | 0 Komentar

Absensi siswa merupakan komponen kunci dari manajemen kelas yang efektif, memberikan pemahaman yang mendalam tentang kehadiran siswa dan pola absensi mereka. Dengan memanfaatkan alat seperti Excel, Anda dapat membangun template absensi Excel yang terorganisir dengan baik dan mudah diakses. Excel menawarkan berbagai fitur yang memungkinkan Anda membuat struktur absensi yang sesuai dengan kebutuhan Anda, mulai dari pelacakan kehadiran harian hingga analisis jangka panjang tentang kehadiran siswa secara keseluruhan.

Langkah-langkah sederhana dapat membantu Anda membuat template absensi Excel yang efisien. Salah satu cara untuk melakukannya adalah dengan menggunakan rumus INDEX yang mudah dipahami dan sangat berguna. Dengan rumus INDEX, Anda dapat mengambil data dari berbagai area dalam spreadsheet Anda dengan cara yang sistematis, memastikan bahwa informasi kehadiran siswa terorganisir dengan baik dan mudah diakses.

Seiring dengan menggunakan rumus INDEX, pastikan juga untuk memanfaatkan fitur-fitur Excel lainnya, seperti format kondisional untuk membedakan antara siswa yang hadir, izin, sakit, atau alpa. Selain itu, tambahkan fungsionalitas filter dan sortir agar Anda dapat dengan cepat menemukan dan menganalisis data absensi berdasarkan kriteria tertentu. Dengan kombinasi ini, Anda dapat menciptakan template absensi Excel yang tidak hanya terstruktur, tetapi juga memberikan wawasan yang berharga tentang kehadiran siswa di kelas Anda:

Siapkan Data Siswa

Langkah pertama adalah menyiapkan data siswa. Buatlah daftar siswa dengan kolom-kolom seperti Nomor Urut, Nomor Induk Siswa, Nama Siswa, Jenis Kelamin dan kelas, dan informasi tambahan lainnya yang diperlukan. Pastikan untuk menyimpan data ini dalam format yang mudah diakses dan dikelola.

Berikut adalah contoh tabel data siswa yang disimpan dalam sheet “data”:

data absensi siswa

Buat Tabel Absensi Siswa

Langkah berikutnya adalah merekap data hari libur diluar hari minggu seperti hari libur nasional atau hari libur semester. Data ini biasanya bisa kita dapat dari kalender pendidikan yang disediakan oleh Dinas Pendidikan di awal ajaran baru.

Rekap dan simpan tabel libur ini di sheet “Linas” dengan format tabel seperti berikut:

data libur nasional

Buat Tabel Absensi Siswa

Selanjutnya, buatlah tabel absensi siswa dengan kolom-kolom untuk tanggal absen dan status absensi (hadir, izin, sakit, atau alpa). Pastikan untuk menyertakan kolom-kolom ini di sebelah data siswa agar dapat menghubungkan absensi dengan informasi siswa yang bersangkutan.

Tabel inilah yang nantinya akan kita jadikan sebagai template absensi excel untuk merekap dan absensi siswa. Dan berikut template absensi excel ini:

template absensi excel

Buat Tanggal dan Hari Otomatis

Berikutnya adalah membuat rumus Excel yang secara otomatis menampilkan tanggal dan hari dengan menggunakan fungsi built-in seperti DATE, YEAR, MONTH dan DAY untuk tanggal dan vlookup untuk mengambil hari libur.

Contohnya, rumus DATE, YEAR, MONTH dan DAY akan menampilkan tanggal pada bulan yang dipilih, sementara rumus VLOOKUP akan menampilkan hari dalam format teks, seperti “Sen” atau “Sel” untuk hari dan “LB” untuk hari libur di luar hari Minggu. Dengan menggunakan rumus-rumus ini, Anda dapat dengan mudah mengintegrasikan informasi tanggal dan hari ke dalam spreadsheet Anda secara otomatis, memudahkan pemantauan waktu dan perencanaan kegiatan berdasarkan tanggal dan hari yang tepat.

Dan rumus Excel yang bisa digunakan untuk menampilkan tanggal di sel E6 yang merefer pada bulan yang kita pilih dari sel AN5:

=DATE(YEAR(AN5);MONTH(AN5);DAY(AN5-(AN5-1)))

Penjelasan Rumus:
Rumus ini digunakan untuk menghasilkan tanggal pertama dari bulan yang sama dengan tanggal yang diberikan dalam sel AN5. Mari kita jelaskan setiap bagian dari rumus ini:

  • YEAR(AN5): Bagian ini mengambil tahun dari tanggal yang terdapat di sel AN5.
  • MONTH(AN5): Bagian ini mengambil bulan dari tanggal yang terdapat di sel AN5.
  • DAY(AN5-(AN5-1)): Bagian ini menghitung hari pertama dari bulan tersebut dengan mengurangi tanggal dalam sel AN5 dengan satu hari kurang dari tanggal tersebut. Ini menghasilkan hari pertama dari bulan yang sama dengan tanggal yang ada di sel AN5.
Baca Juga  Fungsi IFERROR dan ISERROR Excel: Perbedaan dan Persamaannya

Misalnya, jika AN5 berisi tanggal 27 April 2024, maka rumus ini akan menghasilkan tanggal 01 April 2024. Hal ini karena YEAR(AN5) akan menghasilkan tahun 2024, MONTH(AN5) akan menghasilkan bulan April, dan DAY(AN5-(AN5-1)) akan menghasilkan 01 sebagai hari pertama dari bulan tersebut.

Sementara untuk sel di sampingnya dari sel F6 sampai AF6 bisa diteruskan dengan rumus sederhana: =E6+1. Seret rumusnya hingga sel AF6. Sementara mulai sel AG6 bisa gunakan rumus berikut agar ketika bulan Februari tanggalnya berhenti di tanggal 28 atau 29 (kabisat) atau 30 untuk bulan-bulan tertentu seperti April:

=IFERROR(IF(DAYS360(TEXT($AN$5;"DD");TEXT(AF6+1;"DD");1)=0;"";AF6+1);"")

Penjelasan Rumus:
Rumus Excel ini memiliki beberapa bagian yang dapat dijelaskan:

  • IFERROR: Ini adalah fungsi Excel yang digunakan untuk menangani kesalahan dalam rumus. Jika rumus di dalamnya menghasilkan kesalahan, IFERROR akan mengembalikan nilai yang Anda tentukan sebagai alternatif.
  • IF: Fungsi IF digunakan untuk mengevaluasi suatu kondisi. Jika kondisi yang ditentukan benar, maka fungsi ini akan mengembalikan nilai yang Anda tentukan; jika salah, maka akan mengembalikan nilai yang lain.
  • DAYS360: Ini adalah fungsi Excel yang menghitung jumlah hari antara dua tanggal berdasarkan metode 360 hari per tahun. Dalam rumus ini, DAYS360 digunakan untuk menghitung jumlah hari antara dua tanggal yang diberikan.
  • TEXT: Fungsi TEXT digunakan untuk mengonversi nilai menjadi teks dengan format tertentu. Dalam rumus ini, TEXT digunakan untuk mengubah nilai tanggal menjadi teks dengan format “DD” (hari).

Jadi, jika kita memecah rumus tersebut:

  1. TEXT($AN$5,”DD”): Ini mengonversi nilai yang ada di sel AN5 menjadi format teks dengan hanya menampilkan hari (hari dalam format “DD”).
  2. TEXT(AF6+1,”DD”): Ini mengonversi nilai tanggal yang ada di sel AF6 ditambah 1 hari menjadi format teks dengan hanya menampilkan hari (hari dalam format “DD”).
  3. DAYS360(…): Ini menghitung jumlah hari antara dua tanggal yang sudah dikonversi menjadi format teks dengan format “DD”.

Selanjutnya, rumus menggunakan fungsi IFERROR untuk mengevaluasi hasil DAYS360. Jika hasil DAYS360 adalah 0 (artinya tanggal yang dihitung adalah hari yang sama), maka rumus ini akan mengembalikan nilai kosong (“”). Jika hasil DAYS360 bukan 0, maka rumus akan mengembalikan nilai tanggal yang dihitung ditambah 1 hari (AF6+1).

Jadi, rumus pada absen siswa Excel ini digunakan untuk menghitung tanggal berikutnya dari tanggal yang ada di sel AF6, dengan catatan jika tanggal berikutnya adalah hari yang sama, maka rumus akan mengembalikan nilai kosong.

Berikutnya kita buat rumus untuk memastikan apakah tanggal di sel E6 merupakan hari libur atau bukan. Untuk keperluan tersebut di sel E6 kita tulis:

=IF(IF(ISNA(VLOOKUP(E6;Linas!$A$2:$C$27;3;0));"";VLOOKUP(E6;Linas!$A$2:$C$27;3;0))="Libur";"LB";TEXT(E6;"[$-421]ddd;@"))

Penjelasan Rumus:
Rumus Excel ini memiliki beberapa bagian yang dapat dijelaskan:

  1. IF(ISNA(VLOOKUP(…))): Ini adalah bagian dari rumus yang menggunakan fungsi VLOOKUP untuk mencari nilai tertentu (dalam hal ini, nilai di sel E6) di dalam rentang sel Linas!$A$2:$C$27. Fungsi ISNA digunakan untuk mengecek apakah hasil pencarian VLOOKUP menghasilkan kesalahan #N/A (tidak ada nilai yang cocok). Jika tidak ada kesalahan, IF(ISNA(VLOOKUP(…))) akan mengembalikan nilai FALSE; jika ada kesalahan, akan mengembalikan nilai TRUE.
  2. IF(…=”Libur”;”LB”;TEXT(E6,”[$-421]ddd;@”)): Bagian ini adalah bagian utama dari rumus yang mengevaluasi kondisi. Jika hasil pencarian VLOOKUP adalah “Libur”, maka rumus ini akan mengembalikan nilai “LB”. Jika bukan “Libur”, maka rumus akan menampilkan teks hari dari tanggal yang ada di sel E6 dengan format tertentu menggunakan fungsi TEXT.
Baca Juga  Cara Membuat Pembukuan Keuangan Pribadi di Microsoft Excel dengan Rumus Sederhana

Jadi, secara keseluruhan, rumus ini digunakan untuk menampilkan “LB” jika nilai yang dicari di dalam rentang sel Linas!$A$2:$C$27 adalah “Libur”, dan jika bukan “Libur”, maka rumus akan menampilkan hari dari tanggal yang ada di sel E6 dengan format khusus yang ditentukan dalam fungsi TEXT.

Jika sudah selesai dibuat, agar tidak mengganggu estetika, hasil dari rumus tersebut bisa disamarkan dengan memberi warna font yang senada dengan warna selnya yang dalam hal ini adalah warna putih. Beriku hasilnya:

tanggal absensi siswa

Rumus untuk Menampilkan Data Siswa di Kelas Tertentu

Sebelum ke rumus untuk menampilkan data siswa berdasarkan kelasnya, kita akan membuat nomor urut otomatis terlebih dahulu. Dan cara membuat nomor urut otomatis di sel A7 ini bisa menggunakan rumus Excel berikut:

=IF(B7="";"";ROW(A1))

Copy dan paste rumus di atas ke sel-sel yang ada di bawahnya untuk menampilkan nomor urut.

Penjelasan Rumus:
Rumus Excel ini menggunakan fungsi IF untuk mengecek apakah nilai di sel B7 kosong. Jika nilai di B7 kosong, rumus akan mengembalikan nilai kosong (“”). Jika nilai di B7 tidak kosong, maka rumus akan mengembalikan nomor baris dari ROW A1 yang dalam hal ini akan menampilkan angka 1.

Jadi, rumus ini digunakan untuk memberikan nilai berdasarkan kondisi apakah sel B7 kosong atau tidak. Jika kosong, nilainya juga kosong; jika tidak kosong, nilainya adalah nomor baris dari baris A1.

Baru kemudian kita bisa mengambil dan menampilkan data siswa berdasarkan kelasnya. Untuk keperluan ini kita akan menggunakan array formula yang ditulis sekaligus di banyak kolom. Caranya adalah blok range B7:B28, kemudian ketik rumus berikut di formula bar:

=IFERROR(INDEX(data!B3:B117;SMALL(IF(data!$E$3:$E$117=$C$2;ROW($1:$115));ROW($1:$115)));"")

Dan karena ini formula array maka akhiri penulisan rumusnya dengan menekan Ctrl + Shift dan Enter. Jika benar, akan ada tanda kurung kurawal yang mengapit rumus di atas.

Jika sudah, copi dan paste rumus di atas ke kolom “Nama Siswa” di range C7:C28 dan “JK” di range D7:D28. Berikut adalah hasil jadinya:

Penjelasan Rumus:
Formula array Excel di atas adalah rumus yang kompleks namun sangat berguna. Mari kita jabarkan bagian-bagian utamanya:

  1. IFERROR: Fungsi ini digunakan untuk menangani kesalahan dalam rumus. Jika rumus di dalamnya menghasilkan kesalahan, IFERROR akan mengembalikan nilai yang Anda tentukan sebagai alternatif.
  2. INDEX: Fungsi INDEX digunakan untuk mengembalikan nilai dari sel atau rentang sel tertentu dalam sebuah array atau daftar.
  3. SMALL: Fungsi SMALL digunakan untuk mengembalikan nilai ke-n terkecil dari kumpulan nilai. Dalam rumus ini, SMALL digunakan untuk mengambil nilai ke-n terkecil dari baris-baris yang memenuhi kondisi tertentu.
  4. IF: Fungsi IF digunakan untuk mengevaluasi suatu kondisi. Jika kondisi yang ditentukan benar, maka fungsi ini akan mengembalikan nilai yang Anda tentukan; jika salah, maka akan mengembalikan nilai yang lain.
  5. ROW: Fungsi ROW digunakan untuk mengembalikan nomor baris dari sel tertentu.

Dalam formula array ini, rumus berfungsi untuk mengambil nilai dari kolom B di sheet “data” berdasarkan kondisi yang diberikan: jika nilai di kolom E (sheet “data”) sama dengan nilai yang ada di sel C2, maka nilai dari kolom B (sheet “data”) pada baris tersebut akan ditampilkan. Jika tidak ada nilai yang memenuhi kondisi, maka formula akan mengembalikan nilai kosong (“”).

Baca Juga  Cara Membuat Mesin Pencarian di Excel Menggunakan VBA Macro Sederhana

Perlu dicatat bahwa formula ini merupakan formula array Excel, yang berarti Anda harus menekan Ctrl+Shift+Enter setelah mengetik formula ini untuk mengonfirmasi bahwa ini adalah formula array. Excel akan menunjukkan formula ini di dalam kurung kurawal {} setelah dikonfirmasi sebagai formula array.

Gunakan Rumus untuk Menghitung Kehadiran

Dalam tabel absensi, gunakan rumus Excel seperti COUNTIF untuk menghitung jumlah kehadiran siswa dalam satu periode waktu tertentu. Rumus yang bisa digunakan di sel AJ7 adalah sebagai berikut:

=COUNTIF($E$7:$AI$7;AJ6)

Copy dan paste rumus di atas ke sel yang ada di sebelah dan bawahnya. Dan berikut adalah hasilnya:

Penjelasan Rumus:
Rumus Excel =COUNTIF($E$7:$AI$7;AJ6) digunakan untuk menghitung berapa kali nilai yang sama dengan nilai di sel AJ6 muncul dalam rentang sel $E$7:$AI$7. Mari kita jabarkan bagian-bagian utamanya:

  • COUNTIF: Ini adalah fungsi Excel yang digunakan untuk menghitung berapa kali nilai tertentu muncul dalam rentang sel tertentu dengan syarat tertentu.
  • $E$7:$AI$7: Ini adalah rentang sel di mana kita ingin mencari nilai yang sama dengan nilai di sel AJ6.
  • AJ6: Ini adalah nilai yang ingin kita hitung berapa kali muncul dalam rentang sel $E$7:$AI$7.

Jadi, rumus ini akan menghitung berapa kali nilai yang ada di sel AJ6 muncul dalam rentang sel $E$7:$AI$7. Hasilnya akan menjadi jumlah kemunculan nilai tersebut dalam rentang sel yang telah ditentukan.

Gunakan Format Kondisional untuk Visualisasi Data

Tambahkan format kondisional pada tabel absensi untuk memvisualisasikan data dengan lebih jelas. Misalnya, Anda dapat memberi warna berbeda pada sel-sel yang menunjukkan hari libur, hadir, sakit, izin, sakit, atau alpa untuk memudahkan pemahaman dan analisis data.

Sebagai contoh, berikut adalah contoh Conditional Formatting yang bisa digunakan untuk menandai hari libur di absensi Excel download ini:

Blok range E6:AI28 kemudian pergi ke Home >> Conditional Formatting dan gunakan setting berikut:

setting conditional formatting

Dan berikut hasilnya:

hasil jadi template absensi excel

Buat Laporan Absensi

Terakhir, buatlah laporan absensi siswa yang rapi dan informatif. Gunakan grafik atau tabel pivot untuk menyajikan data absensi secara lebih terperinci dan mudah dipahami oleh pihak terkait, seperti orang tua siswa atau pihak sekolah.

Dengan mengikuti langkah-langkah di atas, Anda dapat membuat sistem absensi siswa yang efisien dan mudah dikelola menggunakan Excel. Jangan lupa untuk selalu memperbarui data absensi secara berkala dan melakukan analisis untuk meningkatkan efektivitas pengelolaan absensi di sekolah atau lembaga pendidikan Anda.

Kesimpulan

Membuat absensi siswa dengan Excel dapat menjadi langkah yang sangat efisien dan efektif dalam manajemen kelas. Dengan memanfaatkan fitur-fitur Excel seperti rumus INDEX, format kondisional, dan rumus-rumus lainnya, Anda dapat membuat template absensi Excel yang terstruktur, mudah diakses, dan memberikan wawasan yang mendalam tentang kehadiran siswa serta pola absensi mereka.

Langkah-langkah yang dijelaskan dalam membuat template absensi Excel, mulai dari persiapan data siswa hingga pembuatan rumus-rumus Excel untuk menghitung kehadiran, memberikan panduan yang komprehensif untuk menciptakan sistem absensi yang baik dan informatif. Dengan demikian, Anda dapat mengoptimalkan manajemen kehadiran siswa di kelas dan meningkatkan efektivitas pengelolaan absensi secara keseluruhan.

Pranala Luar

File Excel Cara Praktis Membuat Absensi Siswa Otomatis di Excel untuk Bulan Berjalan ini bisa Anda download jika memang memerlukannya dengan cara klik tautan absensi Excel download di bawah ini:

Atau juga jika ingin mempelajari langkah demi langkah membuat template absensi Excel ini secara visual, bisa kunjungi Youtube Channel Depot Excel dengan judul video: Membuat Absensi Siswa Otomatis di Microsoft Excel. Di sana Anda akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!

0 Komentar

Kirim Komentar

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *

Join Our Newsletter