Membuat Daftar Hadir Otomatis dengan Microsoft Excel

Pada beberapa unit kerja baik itu swasta maupun pemerintah, daftar hadir merupakan suatu dokumen wajib sebagai bahan pertanggungjawaban maupun evaluasi dari unit kerja tersebut. Banyak cara yang dilakukan kantor untuk membuat daftar hadir, baik itu dengan sistem manual (paraf/tanda tangan) atau dengan tekhnologi canggih seperti FingerPrint, CheckLock, dll. Namun pada tutorial kali ini kita hanya akan mencoba membuat daftar hadir dengan menggunakan Microsoft Excel. Kelebihan dari Daftar Hadir ini adalah kita tinggal memasukkan (angka) bulan berjalan, tekan enter dan langsung print. Sangat berbeda jika harus mengetik satu persatu keadaan yang berlaku pada bulan tersebut. Daftar hadir di bawah ini merupakan contoh daftar hadir yang digunakan pada kantor saya, dan anda bebas mengembangkan / memodifikasi formatnya sesuai dengan kebutuhan anda. Untuk lebih jelasnya, kita mulai saja tutorial ini.

Langkah-langkahnya adalah :

1. Buka Excel untuk membuat file baru.
2. Buatlah format daftar hadir yang anda inginkan.

• Format di bawah ini hanya contoh, anda bebas menyesuaikan sesuai dengan kebutuhan anda. Namun agar tidak bingung dalam latihan ini, maka samakan saja dengan tutorial ini.

3. Pada format di atas, ada beberapa field/kolom/cell yang akan kita otomatisasi (tanda panah) sesuai dengan keadaan bulan berjalan, diantaranya :

• Bulan (1)
• Nama hari (2)
• Tanda “X” pada hari libur (3)
• Tanggal 1 s/d terakhir bulan berjalan (4), dan
• Tanggal terakhir hari kerja pada bulan berjalan (5)

Selain cell yang disebutkan diatas, cell yang lainnya merupakan cell yang berisi data statis atau tidak berubah-ubah. Antara lain : Nama Pegawai, Jabatan, tanggal , dan nama/jabatan pimpinan.

4. Tahap pemberian fungsi pada cell-cell segera dimulai. Sebelumnya siapkan sebuah cell diluar format untuk menginput angka bulan (contoh : Juni = 6), serta sebuah tabel bulan yang akan digunakan sebagai tabel pencarian nantinya. Pada contoh ini, saya menempatkan cell inputnya pada cell AI1 dan tabel bulan di bawahnya seperti gambar di bawah :

Jika anda bertanya-tanya tentang garis putus-putus di sekeliling format di atas maka akan saya terangkan di akhir tutorial ini.

5. Kita melangkah pada pemberian fungsi / rumus pada cell-cell yang akan terisi otomatis, dimulai pada nama bulan (1). Ketikkan =”: “&UPPER(VLOOKUP(AI1;AL10:AN23;2)) pada cell C5 lalu tekan ENTER (bukan tanda panah atau klik).

Setelah di enter dan hasilnya adalah : JUNI maka rumusnya bekerja dan berhasil.

ð  Penjelasan :

• =”: “&UPPER(VLOOKUP(AI1;AL10:AN23;2)) adalah untuk membuat tanda titik dua di awal kata.
• =”: “&UPPER(VLOOKUP(AI1;AL10:AN23;2)) untuk menambah fungsi selanjutnya.
• =”: “&UPPER(VLOOKUP(AI1;AL10:AN23;2)) untuk menjadikan huruf kapital pada fungsi yang dihasilkan dari rumus yang ada di dalam tanda kurung.
• =”: “&UPPER(VLOOKUP(AI1;AL10:AN23;2)) untuk melakukan pencarian nama bulan dari angka yang diketikkan di (AI1), dan mencocokkan nama bulan yang sesuai pada tabel bulan (AL10 s/d AN23) pada kolom nama bulan (kolom ke-2)

6. Selanjutnya untuk fungsi tanggal (3), ketikkan angka satu pada cell D10. Untuk tanggal 2 dan seterusnya ketikkan rumus berikut pada cell E10 :

=IF(OR($AI$1=1;$AI$1=2;$AI$1=3;$AI$1=4;$AI$1=5;$AI$1=6;$AI$1=7;$AI$1=8;$AI$1=9;$AI$1=10;$AI$1=11;$AI$1=12);D10+1;””).

Fungsi dari rumus ini adalah untuk menyesuaikan jumlah hari dengan bulan berjalan (Contoh : bulan Januari = 31, Februari = 28/29, Juli= 31, dst).

7. Masih pada fungsi tanggal. Setelah di enter, pastikan hasilnya adalah angka 2. Setelah itu kembalikan kursor pada cell E10 lalu klik dan tarik sudut kanan bawah cell E10 (tanda +) sampai pada ujung kolom tanggal (AH10), dan lihat hasilnya. Semestinya akan tercipta deretan angka 2 s/d 30 sementara cell terakhir akan dikosongkan karena bulan juni hanya sampai tanggal 30.

8. Rumus untuk tanggal selesai, kita beralih pada fungsi nama hari. Pada awal cell nama hari (D9) Ketikkan rumus berikut :

=IF(AND(D10>0;D10<32);TEXT(D10&”/”&$AI$1&”/2012″;”dddd”);””) lalu tekan ENTER dan hasilnya akan muncul teks Jumat sesuai dengan awal hari pada tanggal 01 Juni 2012. Seperti pada langkah ke-7, klik dan tarik cell D9 hingga ke ujung kolom nama hari dan hasilnya akan tercipta deretan nama hari hari yang sesuai dengan tanggal yang ada di bawahnya. Kolom terakhir juga akan dikosongkan karena isian tanggal dibawahnya juga kosong, jadi menyesuaikan dengan tanggal di bawahnya.

9. Langkah selanjutnya adalah mengisi rumus untuk mengarsir (dengan tanda “X”) pada hari sabtu, minggu dan hari libur (jika ada). Ketikkan rumus berikut pada cell D11

=IF(OR(D$10=$D$23;D$10=$D$24;D$10=$D$25;D$10=$D$26);”XXX”;IF(OR(D$9=”SABTU”;D$9=”MINGGU”);”XXX”;””)) lalu tekan ENTER dan hasilnya masih kosong. Kembalikan kursor pada cell D11 lalu klik dan tarik (seperti langkah-langkah sebelumnya) hingga ke ujung dan biarkan tetap ter-blok, klik kembali tanda “+” pada sudut kanan bawah cell yang terblok lalu tarik ke bawah sejajar dengan nama pegawai yang terakhir. Hasilnya, semua hari sabtu dan minggu akan terdapat tanda “X”.

cell D11 merupakan gabungan antara baris D11 dan D12 dengan cara merge cell, demikian pula untuk cell-cell yang selanjutnya.

10. Fungsi terakhir adalah memberi tanggal sesuai hari kerja terakhir pada bulan berjalan (5).

Ketikkan rumus berikut :

=”Benteng, “&MAX(D21:AH21)&” “&VLOOKUP(AI1;AL10:AN23;2)&” 2012″ lalu tekan ENTER. Hasilnya, cell akan terisi dengan nama tempat dan tanggal hari kerja terakhir pada bulan tersebut yaitu pada hari Jumat tanggal 29.

11. Pada format daftar hadir ini terdapat pula isian untuk hari libur nasional, untuk menggunakannya anda tinggal memasukkan tanggal hari libur dimaksud pada lajur kolom D dan keterangannya di sebelah kanan tanggal. Contoh, anggaplah hari libur pada hari Kamis tanggal 14 Juni 2012. Maka pengisiannya cukup masukkan tanggal 14 pada kolom D dan keterangannya pada kolom selanjutnya. Perhatikan perubahannya.

12. Penjelasan untuk garis putus-putus di sekeliling form.

Di luar form daftar hadir di atas terdapat cell inputan angka bulan dan tabel nama bulan. Nah, agar cell-cell tersebut tidak ikut tercetak sewaktu di print maka area pencetakan dibatasi hanya pada format daftar hadir saja dengan cara, blok semua form daftar hadir dari cell A1 sampai cell AH27, pilih tab Page Layout >> Print Area >> dan Set Print Area.

Hasilnya akan tercipta garis putus-putus di sekeliling form yang menandakan batas pencetakan halaman sehinggan area diluar garis putus-putus tidak akan tercetak.

Demikian turorial ini, semoga bermanfaat bagi anda semua.

Jika ada yang kurang dimengerti silahkan ajukan pertanyaan, saya akan berusaha meluangkan waktu untuk menjawabnya sesuai dengan kemampuan yang saya miliki. Jika ada kekurangan mohon dikritik dan dimaklumi karena saya adalah manusia biasa yang sudah barang tentu tidak luput dari kesalahan.

Wassalam…

UPDATE 2 JUNI 2016

Berhubung banyaknya permintaan file excelnya, maka saya dengan senang hati membagikan untuk anda semua. Silahkan download filenya disini.