PEMANFAATAN FEATURE MS.EXCEL
DALAM MEMBUAT JADWAL PELAJARAN
Berbagai teknis yang
dilakukan dalam membuat jadwal pelajaran, ada yang dibuat secara manual
dengan menggunakan sejumlah warna yang berkorespondensi satu-satu dengan
nama pengajar/mata pelajaran , hingga penggunaan program aplikasi
pembuat jadwal pelajaran seperti aSc Timestable yang cukup lengkap
hingga pengaturan siswa maupun ruangan telah disediakan. Namun demikian
program ini pun pada prinsipnya sama menggunakan pewarnaan yang berbeda
untuk setiap nama pengajar atau mata pelajaran dengan maksud sebagai
indikator pembeda kondisi jadwal yang dibuat dengan bantuan computer.
Program aplikasi aSc
Timestable tidak gratis (not free) alias harus beli, kalau yang bisa
kita downloadpun merupakan versi trial, lumayan sekedar mengenal
penggunaan program aplikasi tersebut. Penulis telah mencoba mengunduh
program aSc Times Table tersebut dan menggunakannya namun masih
terdapat error , akhirnya kembali lagi menggunakan MS. Excel walaupun
entry datanya dan penataannya manual satu persatu , namun cukup
membantu dengan cepat dalam membuat jadwal pelajaran dan lebih familiar
bagi kita yang sering menggunakannya.
Sepuluh tahun yang lalu lampu
rambu perempatan jalan telah menginspirasi penulis. Saat kendaraan yang
saya tunggangi di malam hari berhenti di perempatan dan Traffic Lights
yang berwarna merah, kuning, dan hijau jadi perhatian saya. Saat itu
terlintas dalam benak saya sepertinya dalam menyusun jadwal mengajar
dapat menggunakan warna-warna tersebut. Setibanya di rumah tanpa
menunggu lama saya nyalakan PC jadul dan saya buka MS.Excel 97 lalu
mencari pemformatan cells dengan membuka menu format saya temukan
submenu conditional format yang hanya dapat berisi 3 kondisi saja,
artinya MS. Excel 97 menuntut kita untuk terampil menggkombinasikan
fungsi-fungsi yang ada. Walau harus sedikit merumuskan beberapa cell
untuk kondisi jadwal akhirnya membuat jadwal mengajar dengan formatting
cells dapat saya buat.
Dalam MS. Excel 97 hanya berisi 3
kondisi , berbeda dengan MS.Office Excel 2007 yang berisi feature
conditional formatting hingga 64 kondisi dan beberapa feature lainnya
berbentuk icons yang tidak ada dalam MS. Excel 97 dan Traffic Lights
yang telah menginspirasi penulis dijadikan sebagai salah satu icon sets formatting cells, sehingga dalam pemformatan cells lebih variatif,mudah dan simple.
Dalam membuat jadwal mengajar dengan formatting cells , warna merah saya gunakan sebagai indicator untuk kondisi jadwal Rangkap
artinya satu mata pelajaran yang diampu oleh seorang guru terdapat di
beberapa ruang kelas yang berbeda pada saat yang bersamaan. Warna hijau sebagai indicator jumlah jampel per minggu yang dialokasikan masih kurang dalam satu kelas, dan warna kuning sebagai indikator jumlah jampel berlebih
dalam satu kelas dari yang dialokasikan sesuai muatan kurikulum. Hal
ini sangat membantu dalam entry kode pengajar atau mengedit jadwal, saat
mengganti kode pengajar si A dengan si B terkadang kita lupa siapa yang
diganti atau dihapus , mata pelajaran apa yang kurang atau berlebih,
jadi kita tak akan dibebani dengan hal tersebut.
Untuk pemula yang belum
mengenal feature-feature MS. Excel 2007 lainnya, berikut penulis sajikan
langkah-langkah membuat jadwal mengajar atau jadwal pelajaran dengan
formatting cells:
- Buka MS. Excel, satu workshett kosong terbuka secara otomatis
- Pada Sheet 1, buatlah dan tentukan rancangan bentuk jadwal yang diinginkan, bentuk vertical atau horizontal sekaligus distribusikan jumlah jampel setiap hari dari Senin s.d. Sabtu sehingga jumlah jampel per Minggu sesuai dalam muatan kurikulum sekolah.
Berikut contoh blanko jadwal yang berbentuk vertical.
- Pada Sheet yang sama di area lain yang tidak sebaris dengan jadwal, buatlah tabel data yang memuat kode pengajar/mata pelajaran dan alokasi jumlah jampel setiap kelas dalam satu Minggu.
( Jadwal dengan tabel data harus
dibuat dalam satu Sheet yang sama , karena Conditional Formatting tidak
dapat bekerja pada Sheet yang berbeda jika menggunakan fungsi vlookup
reference, kecuali kita menggunakan Excel 2010. Dalam MS.Excel 2010
Conditional Formating dapat menggunakan referensi pada sheet yang sama
atau sheets lain dalam satu workbooks tetapi tidak bisa bekerja dengan
menggunakan workbooks lain).
- Langkah selanjutnya, pemformatan setiap Cell pada jadwal yang telah dibuat.
- Pilih range cell D8:T8 – Klik Tab Home – Klik Conditional Formatting – klik Highlight Cells - klik duplicate values….
Maka muncul kotak dialog Duplicate values seperti berikut:
Jika anda tidak menginginkan warna baku yang disediakan, anda bisa klik menu dropdown value with lalu klik custom format…, maka terbuka kotak dialog Format Cells:
Saya memilih Tab Fill untuk mewarnai cells dengan warna merah lalu OK.
(pilih Tab Font jika anda mau memilih Hurufnya saja yang diwarnai ).
Selanjutnya periksa Cells D8 s.d. T8 yang sudah kita format dengan duplikasi tadi.
Klik Conditional Formatting – klik Manage Rules … , maka tampak pada kotak dialog berikut :
Pemformatan untuk Duplikcate values diterapkan pada range D8:T8. Klik Apply lalu OK.
- Format Cell D8 dengan menggunakan formula (funggsi yang digunakan COUNTIF dan VLOOKUP)
- Klik Conditional Formatting – klik New Rule… , maka terbuka kotak dialog berikut:
- Klik Use a formula to determine which cells to format – pada kotak tab Format value where this formula is true ketikkan rumus berikut:
=COUNTIF(D$8:D$59,D8)<VLOOKUP(D8,$AO$72:$BE$96,3,FALSE) , lalu
Klik button Format – klik warna sesuai selera anda lalu OK .
Penjelasan :
Fungsi COUNTIF : untuk menghitung konten cell D8 dalam range cells D8:D59
(Range D8:D59 berisi 38 Cell yang mewakili jumlah 38 jampel per minggu untuk kelas 7A)
Fungsi VLOOKUP : untuk mencari konten D8
dalam table array (AO72:BE96) yang merupakan jumlah jampel untuk kelas
7A mata pelajaran dengan kode pengajar yang berkorespondensi dengan
konten pada cell D8.
3 adalah nomor indeks kolom untuk alokasi jumlah jampel kelas 7A.
False : nilai range_lookup agar fungsi VLOOKUP mencari secara tepat tanpa harus menyusun data tabel secara terurut.
Dengan langkah yang sama seperti untuk kondisi 2
- Format Cell D8 , klik cell D8
- Klik Conditional Formatting – klik New Rule… , maka terbuka kotak dialog berikut:
- Klik Use a formula to determine which cells to format – pada kotak tab Format value where this formula is true ketikkan rumus berikut:
=COUNTIF(D$8:D$59,D8)>VLOOKUP(D8,$AO$72:$BE$96,3,FALSE) , lalu
Klik button Format – klik warna sesuai selera anda lalu OK – OK.
Formatting cell D8 dengan 3 kondisi selesai, untuk melihat pemformatan tadi
- Klik Conditional Formatting – klik Manage Rules … , maka tampak pada kotak dialog berikut :
Dalam kotak dialog ini anda dapat menghapus, mengedit formula, menata urutan kondisi formatting.
- Langkah selanjutnya Formatting cell E8, F8, s.d. T8 (sesuai jumlah kolom pada jadwal yang anda buat).
Untuk pemformatan cell E8, F8, s.d. T8
tak perlu merumuskan seperti langkah diatas, kita gunakan Format Painter
pada group Clipboard sub tab Home.
Klik cell D8 – klik Format Painter – klik cell E8.
Untuk melihat hasil pemformatan pada cell E8 ,
Klik cell E8 – klik Conditional Formatting – klik Manage Rules … , maka tampak pada kotak dialog berikut :
Untuk melihat sepintas formula, gerakkan
pointer mouse hingga di atas formula, untuk mengedit klik kondisi yang
akan diedit – klik tab Edit Rule…
Dengan cara yang sama, format untuk cell F8, G8 , dst..
- Langkah selanjutnya Formatting range cell D9:T9 (sesuai jumlah kolom pada jadwal yang anda buat)
Pilih range cell D8:T8 – klik Format Painter – klik cell D9
Maka range cell D9:T9 telah terformat.
Periksa cell D9 : Klik cell D9 – klik Conditional Formatting – klik Manage Rules … , maka tampak pada kotak dialog berikut :
Tampak untuk Duplicate value perlu diedit, klik Duplicate value – tulis pada kotak Applies to :
=$D$9:$T$9.
Periksa lagi untuk cell E9 , dst.
Ulangi langkah pemformatan untuk baris berikutnya hingga tuntas.
Setelah semua cells sudah terformat
dengan 3 kondisi tersebut, maka blanko jadwal siap diisi dan perhatikan
hasil pemformatannya .
Berikut contoh 3 kondisi yang tampak, kondisi rangkap(merah), kurang jam (hijau), dan lebih jam (kuning).
Apabila kondisi jadwal terlihat berwarna putih seperti warna background, maka kondisi jadwal telah benar.
WARNING:
Setiap cell anda harus mengetikkan
satu persatu, jangan lakukan Copy- Paste untuk konten yang sama, karena
setiap cell telah diformat dengan format tertentu sehingga akan
mengubah formatting cell yang telah dibuat!!!.
- LANJUTAN :
Tampak Kode mapel IS belum diisikan pada kelas 8A sejumlah 2 jampel.
- Formula untuk Cell AQ104 : =Countif(range kolom kelas 7A pada jadwal,criteria)
= Countif($D$8:$D$59,AO104)
Formulakan untuk Cell AR104, dst , untuk baris selanjutnya anda bisa Copy-Paste.
Untuk Kondisi jumlah jampel, prinsifnya
membandingkan antara jumlah jam hasil perhitungan fungsi Countif dengan
jumlah seharusnya yang ada pada Tabel data.
Fungsi IF yang saya gunakan untuk formulanya tampak pada Formula Bar .
=IF(BF104=BF72,”TEPAT”,IF(BF104<BF72,”KURANG”&” “& (BF72-BF104),”LEBIH”&” “&(BF104-BF72)))
- Untuk memeriksa jumlah jam setiap pengajar per hari dalam
satu minggu, anda dapat membuat tabel pemeriksa lagi, seperti berikut:
Tampak Kode BS pada hari jum’at tidak ada jam mengajar. Ini berguna untuk memeriksa permintaan jam kosong mengajar.
Tampak pada formula bar Fungsi Countif(range cells hari senin pada jadwal,criteria).
- Untuk mencegah salah pengisian kode pengajar pada masing-masing kelas, anda dapat menggunakan Data Validation.
Klik cell D8 – Klik tab Data – Klik Data Validation – klik Data Validation … , maka tampak kota dialog berikut :
Pada tab Allow: pilih list , lalu ketikkan pada tab Source: Kode nama pengajar yang mengajar di kelas 7A dan pisahkan dengan “,” (koma)
Tampak In-cell dropdown di ceklis berarti daftar kode pengajar pada cell D8 ditampilkan dalam menu dropdown.
Untuk peringatan salah entry data, anda dapat klik tab Error Alert seperti kotak dialog berikut:
Isi dengan teks yg diperlukan lalu OK.
Hasil validasi data pada cell D8, jika klik cell D8 tampak pada sebagai berikut:
Icon small triangle pada cell D8 merupakan menu dropdown jika anda klik berisi daftar kode pengajar yang tadi dibuat.
Jadi anda dapat mengkliknya tanpa harus mengetikan lagi.
Icon small triangle tidak akan tercetak saat printing, jika settingan baku tidak dirubah.
Jika anda mengetikkan kode nama pengajar
selain yang terdaftar tadi, maka muncul peringatan kesalahan yang tadi
telah dibuat. Ini kegunaannya data validasi sebagai pembatasan.
Selanjutnya anda dapat melakukan validasi data yang sama untuk cell lainnya jika anda mau !
Klik cell d8 – klik Data Validation – Data Validation … , lalu OK.
Klik atau pilih Cell lainnya lalu tekan F4 .
Selamat mencoba dan semoga bermanfaat.
10 November 2013 pukul 16.52
saya sudah mencoba sampai kondisi 3, namujn setelah saya coba entri data ternyata semua efek conditional tersebut hanya untuk kolom aq sedangkan AR dan seterusnya tidak ngefek , maksudnya jika seorang guru pada kela 7a tidak mengajar maka untuk kelas lainnya dianggap tidak mengajar juga padahal ada 2 jam