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

Cara Membuat Jadwal Pelajaran di Excel dengan Cek Bentrok Jam Guru Otomatis

Ditulis oleh Terbit
Ilustrasi jadwal pelajaran di Excel berisi kode guru per kelas dengan sel bentrok berwarna merah

Jadwal pelajaran di Excel paling mudah dibuat dengan mengisi kode guru di setiap sel kelas per jam, bukan nama mapel. Dengan kode guru, Excel bisa langsung memeriksa bentrok: kalau kode yang sama muncul dua kali dalam satu baris jam, artinya satu guru dijadwalkan di dua kelas sekaligus. Rumus COUNTIF menandai bentrok itu, dan jam mulai serta selesai dihitung otomatis dari jam masuk dan lama satu JP.

Dicek terakhir: 10 November 2026 oleh Arfan Putra. Semua rumus di template sudah dihitung ulang dengan mesin rumus dan dicocokkan dengan hitungan manual: jam pelajaran, jumlah JP 13 guru, lembar cetak per kelas, dan uji bentrok yang sengaja dibuat. Screenshot dari Excel asli menyusul.

Unduh template gratis: jadwal pelajaran dengan cek bentrok (Excel), berisi sheet Guru, Jadwal (6 kelas, Senin–Jumat, 8 JP per hari), dan Cetak Kelas. Nama guru di dalamnya fiktif, dan pembagian JP per mapel hanya contoh, bukan struktur kurikulum resmi.

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 Template

SheetIsi
GuruKode (A), nama guru (B), mapel (C), target JP per minggu (D), JP yang sudah terjadwal (E), keterangan Pas/Kurang/Lebih (F). Data mulai baris 4.
JadwalPengaturan jam di C2:C4. Baris 7–51: hari, jam ke, jam mulai, jam selesai, lalu kolom E sampai J untuk 6 kelas. Kolom K = cek bentrok.
Cetak KelasPilih kelas di C2, jadwal kelas itu tampil lengkap dengan nama mapel dan guru, siap dicetak atau ditempel di kelas.

Langkah 1: Jam Mulai dan Selesai Otomatis

Isi jam masuk di C2 (contoh 07:30), lama satu JP dalam menit di C3 (contoh 40), dan lama istirahat di C4 (contoh 15). Sesuaikan C3 dengan aturan jenjang dan sekolah Anda.

Jam mulai jam pertama tiap hari cukup =$C$2. Jam selesainya:

=C7+$C$3/1440

Excel menyimpan waktu sebagai pecahan hari, dan satu hari = 1440 menit. Jadi 40 menit ditulis 40/1440. Jam mulai baris berikutnya cukup mengambil jam selesai baris sebelumnya, misalnya =D7 di C8. Untuk baris istirahat, ganti $C$3 dengan $C$4. Atur format sel C dan D ke Custom hh:mm.

Hasilnya dengan pengaturan contoh: jam ke-1 07:30–08:10, jam ke-4 09:30–10:10, istirahat 10:10–10:25, dan jam ke-8 selesai 13:05. Kalau jam masuk diubah, semua jam di bawahnya ikut berubah.

Langkah 2: Isi Sel Kelas dengan Kode Guru

  1. Di sheet Guru, beri setiap guru kode singkat yang unik, misalnya G01, G02. Satu guru yang mengajar dua mapel boleh diberi dua kode, asal di cek bentrok nanti diingat bahwa keduanya orang yang sama.
  2. Blok sel jadwal (di template E7:J51), lalu pilih Data → Data Validation. Di Allow pilih List, dan di Source isi =Guru!$A$4:$A$23.
  3. Sekarang setiap sel punya daftar pilihan kode guru, dan kode yang salah ketik ditolak.

Langkah 3: Rumus Cek Bentrok per Jam

Di kolom K baris 7, tulis:

=IF(SUMPRODUCT((E7:J7<>"")*(COUNTIF(E7:J7,E7:J7)>1))>0,"BENTROK","")

Cara bacanya:

  • COUNTIF(E7:J7,E7:J7) menghitung, untuk setiap sel di baris itu, berapa kali isinya muncul di baris yang sama. Kalau G05 muncul di dua kelas, kedua sel itu bernilai 2.
  • >1 menandai sel yang kodenya muncul lebih dari sekali.
  • (E7:J7<>"") membuang sel kosong, supaya jam kosong dan baris istirahat tidak dianggap bentrok.
  • SUMPRODUCT menjumlahkan hasilnya. Kalau lebih dari 0, ada bentrok.

Salin rumus ke bawah sampai baris terakhir. SUMPRODUCT mengolah rentang sebagai array dengan sendirinya, jadi rumus ini cukup diakhiri Enter biasa.

Langkah 4: Warnai Sel yang Bentrok

  1. Blok E7:J51, lalu pilih Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
  2. Isi rumus =AND(E7<>"",COUNTIF($E7:$J7,E7)>1), klik Format, pilih warna isi merah muda, lalu OK.

Tanda $ di depan E dan J membuat rentang pengecekan tetap satu baris penuh, sementara sel yang diwarnai bergeser sesuai posisinya. Jadi yang berwarna adalah kedua sel yang bentrok, dan Anda langsung tahu kelas mana yang harus digeser.

Langkah 5: Hitung Total JP Tiap Guru

Di sheet Guru kolom E:

=COUNTIF(Jadwal!$E$7:$J$51,A4)

Rumus ini menghitung berapa kali kode guru muncul di seluruh jadwal dalam seminggu. Bandingkan dengan target di kolom D:

=IF(E4=D4,"Pas",IF(E4<D4,"Kurang "&D4-E4&" JP","Lebih "&E4-D4&" JP"))

Sebelum jadwal dibagikan, pastikan semua guru berstatus Pas dan kolom Cek Bentrok kosong semua.

Langkah 6: Lembar Cetak per Kelas

Di sheet Cetak Kelas, kode guru diubah menjadi nama mapel dengan INDEX dan MATCH:

=INDEX(Guru!$C$4:$C$23,MATCH(INDEX(Jadwal!$E7:$J7,1,MATCH($C$2,Jadwal!$E$6:$J$6,0)),Guru!$A$4:$A$23,0))

MATCH($C$2,Jadwal!$E$6:$J$6,0) mencari kolom kelas yang dipilih di C2. INDEX bagian dalam mengambil kode guru di jam itu, lalu INDEX-MATCH bagian luar mencari mapelnya di sheet Guru. Template menambahkan pengecekan sel kosong dan IFERROR, sehingga jam kosong tetap kosong dan kode yang tidak terdaftar tampil "kode tidak ada". Ganti kelas di C2, dan seluruh lembar berubah ke jadwal kelas itu.

Kalau Hasilnya Error

  • Jam tampil seperti 0,3125. Itu nilai waktu yang belum diformat. Ubah format sel ke Custom hh:mm.
  • Bentrok tidak terdeteksi padahal guru sama. Kodenya diketik berbeda, misalnya "G05" dan "G05 " (ada spasi) atau "G5". Pakai daftar pilihan dari Data Validation supaya kode selalu sama.
  • Baris bertanda BENTROK padahal gurunya berbeda semua. Rentang rumus melebar saat disalin, misalnya menjadi E7:J8, sehingga guru jam berikutnya ikut terhitung. Pastikan rumus di tiap baris hanya mengacu ke baris itu sendiri.
  • Lembar cetak menampilkan #N/A. Nama kelas di C2 tidak sama persis dengan judul kolom di sheet Jadwal (beda spasi atau tanda hubung). Pilih kelas dari daftar di C2, jangan diketik.
  • JP guru tidak bertambah setelah jadwal diisi. Kolom kelas baru di luar rentang E7:J51. Perluas rentang di rumus COUNTIF, Data Validation, dan Conditional Formatting setiap kali menambah kelas.

Sumber