SOLUSI EXCEL : JURNAL PENDAFTARAN SISWA (update)
barusan terima e-mail dari seorang pembaca yang nyasar ke blog ini, berikut sebagian petikan dari email beliau :
“Bagaimana ya caranya membuat aplikasi utk pendaftaran siswa baru menggunakan Ms. Excel ?
Contoh kasus: Sheet 1 untuk input data siswa lengkap dg alamat, nila dll. Sheet2 berisi jurnal.
Lah yg saya tanyakan pada Sheet 2 itu kita ngga input apa2 tapi secara otomatis akan mengurutkan data yg teratas nilai tertinggi.”
dalam email tersebut dilampirkan sebuah file excel latihan.xls yang berisi dua sheet seperti gambar berikut
kolom rank di isi dengan formula berikut :
pada cell F2 =RANK($E$2:$E$11;$E$2:$E$11)
kopikan ke bawah sampai cell F10
mari kita coba bersama cari solusinya…..
- pertama kasi warna kuning (tergantung preferensi anda) pada cell A11 di sheet “daftar“
- pindah ke sheet “jurnal1”
- ketikkan formula berikut pada cell E3 =IF(Daftar!A2=””;””;Daftar!A2) , kebawah kopikan sampai E11
- ketikkan formula berikut pada cell A3 =IF(E3=””;””;MATCH(E3;Daftar!$F$2:$F$11;0)) , kebawah kopikan sampai A11
- ketikkan formula berikut pada cell B3 =IF(E3=””;””;VLOOKUP(A3;Daftar!$A$2:$E$11;2;FALSE)) , kebawah kopikan sampai B11
- ketikkan formula berikut pada cell C3 =IF(E3=””;””;VLOOKUP(A3;Daftar!$A$2:$E$11;3;FALSE)) , kebawah kopikan sampai C11
- ketikkan formula berikut pada cell D3 =IF(E3=””;””;VLOOKUP(A3;Daftar!$A$2:$E$11;5;FALSE)) , kebawah kopikan sampai D11
jika berhasil seharusnya sheet “jurnal1” menjadi seperti ini
lalu bagai mana untuk menambah jumlah calon siswa pada sheet “daftar”
- silahkan taruh kursor pada cell A11 yang berwarna kuning
- blok kebawah sejumlah data yang akan ditambah, misal 4 data baru maka range yang diblok A11:A14
- klik kanan >>> insert >>>entire row…anda akan mempunyai 4 baris kosong baru
- tambahkan data calon siswa baru seperti contoh (cell warna hijau)
- kopikan cell F10 ke 4 baris di bawahnya
hasilnya akan seperti ini
setelah itu aktifkan sheet “jurnal1”, blok range A11:E11, kopikan kebawah sejumlah yang diinginkan (tidak tergantung jumlah data), hasilnya akan seperti ini
sebenarnya format sheet “jurnal1” dapat disederhanakan dengan menghilangkan kolom no urut, dan tabel jurnal akan lebih sederhana seperti gambar berikut
Keterangan :
- kolom Rank diisi nomor urut seperti biasa
- formula B3 =IF(A3=””;””;VLOOKUP(MATCH(A3;Daftar!$F$2:$F$15;0);Daftar!$A$2:$E$15;2;FALSE))
- formula C3 =IF(A3=””;””;VLOOKUP(MATCH(A3;Daftar!$F$2:$F$15;0);Daftar!$A$2:$E$15;3;FALSE))
- formula D3 =IF(A3=””;””;VLOOKUP(MATCH(A3;Daftar!$F$2:$F$15;0);Daftar!$A$2:$E$15;5;FALSE))
Kopikan kebawah dan Selesai………….
materi file jurnal_pendaftaran.xls dapat di download dari download box disamping
===========================================================
UPDATE 23 AGUSTUS 2010
===========================================================
Maaf gan, jurnal yg kemarin setelah aku coba apabila ada nilai yg sama hasilnya jadi #N/A pada kolom No. Nama. Nilai. Gemana ya solusinya?
error ini terjadi karena untuk fungsi RANK hanya akan menampilkan peringkat tertinggi dari data2 yang nilainya sama, misalnya ada data dengan nilai sama seharusnya menempati peringkat 7 dan 8, maka peringkat yang akan ditampilkan adalah 7 untuk kedua data tersebut dan peringkat 8 tidak digunakan. untuk mengatasi masalah data kembar/sama pada kolom yang akan dicari peringkatnya dapat ditambah dengan formula pada sheet “daftar” range F2 =RANK($E$2:$E$15;$E$2:$E$15)+COUNTIF($E$2:$E2;E2)-1
dengan penambahan formula tersebut data2 yang nilainya sama akan diurutkan berdasarkan posisi barisnya, dimana baris paling atas akan menempati peringkat yang lebih awal daripada baris2 dibawahnya
bingung cara downloadnya ??Baca aja halaman download dengan klik icon di samping |
|
feed , email and my social media        |
Makasih mas ….. atas penjelasan dari pertanyaan saya. Alhamdulilah saya nyasar di Blog yang “Amanah”, semoga Amal baik mas Sukron mendapat Ridlo dari Allah SWT.. Amin.
amiiiiiiiinnnnnnnn………..
Maaf gan, jurnal yg kemarin setelah aku coba apabila ada nilai yg sama hasilnya jadi #N/A pada kolom No. Nama. Nilai. Gemana ya solusinya?
sebelum saya jawab saya ada pertanyaan :
kalo nilai sama kira2 variabel apa yang digunakan untuk menentukan peringkat???
misal A dan B nilai sama, bagaimana cara menentukan siapa peringkat yang lebih tinggi dan siapa yang lebih rendah????
Variabelnya Nilai masing2 Mapel UN,
sudag saya update pak…silahkan dibaca bagian bawah artikelnya
Makasih gan… dah langsung aku coba n sukses. Blog ini bener2 bertujuan tuk membantu orang yang membutuhkan dan tanpa pamrih lagi. Walaupun tanpa iklan semoga mas selalu mendapat limpahan rizki yang halal dan berkah. Amin
bisanya cuman gini pak…semoga bisa manfaat
Bagi saya hal gini sulit banget…. waktu saya sekolah blum ada komputer. Palagi saya cma gr olahraga. Saya blajar dari internet termasuk dr blog panjenengan.
Alhamdulilah mas…. jurnal pendaftaran dah klar yah mendekati 99%. Saya mo minta bantuan lagi boleh kan..? Apabila kita inputnya menggunakan Form yg pake Macro Excel gmana ya?
sudah pakai form standar excel pak??? coba klik menu excel anda DATA >> FORM, kalo pakai form standar excel sudah bisa memenuhi kebutuhan bapak ngapain repot2 pakai macro.
kalo pengen belajar macro coba aja rekam2 dulu terus dipelajari hasilnya, saya sendiri belum tahu banyak maklum otodidak pak.
Saya belum pernah malah blum tau ada Form di Excel. Yah mau tak coba. Trim’s
Terimakasih… sudah berkunjung di blog kami dan memberikan tips dan triknya… bagus..
thanks… 😉
Artikel yang cukup menarik dan tentunya sangant berguna. Kami udah coba dan hasilnya OK. Kemudian bagaimana agar tabel border pada sheet jurnal dapat mengikuti secara otomatis dari sheet daftar. Terima kasih dan mohon solusi.
1. ilangin bordernya dulu semua
2. pada sheet “jurnal1” pilih range A3:E3
3. lakuan conditional format dengan aturan :
Formula is >>> =INT($A3)>0
format bodernya pada empat sisinya
SALAM HORMAT!!
SAYA HANYA BISA MENDOAKAN SEMOGA MAS M. SUKRON FATAH SELALU MENDAPAT RAHMAT DARI ALLOH SWT. APA YANG TELAH MAS BERIKAN KE SAYA (KHUSUSNYA TENTANG JURNAL PPDB) ALHAMDULILLAH TELAH SAYA PRAKTEKAN DAN BERJALAN DENGAN BAIK. SEMOGA AMAL BAIK PANJENENGAN MENDAPAT BALASAN DARI ALLOH SWT. AMIEEN
Amiieeen….
assalamualaikum
terima kasih mas aplikasinya sudah di coba dan sangat membantu untuk program psb di sekolah. tapi ketika saya coba untuk kasus siswa yang mencabut berkas/ mengundurkan diri dari pendaftaran dan saya delete maka siswa yang daftar di bawahnya menjadi tidak muncul. contoh sudah saya kirim via email. mohon solusinya mas terima kasih banyak atas bantuannya
cek inbox
kalau cuma untuk menghitung rangking kenapa harus muter-muter dah benar tuh yang revisi gunakan aja formula range, jadi lebih simple
betul … banyak jalan menuju roma … bisa lewat jalan tol bisa lewat gang dan lorong sempit … 😉
maaf mas bagaimana membuat jurnal yang menggunakan rentang untuk datanya mungkin sama dengan yang d atas sheet1daftar dan sheet2 jurnal. contoh rentang 30.00-31.00 5 siswa, 32.00-33.00 10 siswa dll…. bagaimana mas buatnya…?? mohon d konfirm ke email saya galihnirmala@gmail.com
silahkan pelajari fungsi DSUM … cari di help Excel sOdara
mas minta tolong kalau buat jurnal yang ada rentangnya misal nilai UN 30.00-32.99 caranya gimana…?? datanya sama persis dengan pendaftaran siswa kaya diatas…
Mohon bantuannya, saya pingin buat tabel gaji perminggu dan di mulai hari senin sampai minggu menggunakan Mc Office 2010. dengan perincian sbb:
1. Jam masuk kerja 7.00 Wib (hari biasa 8 jam kerja – 1 jam istirahat. Jadi total nya 7 jam kerja) jadi jam pulang 15.00 Wib.
2. Jam lembur hari biasa ( Senin – jumat ) 1 jam pertama terhitung 1,5 jam. dan jam selanjutnya terhitung 2 jam.
3. Untuk hari Sabtu masuk kerja jam 7.00 Wib dan pulang jam 12.00 Wib ( Terhitung 5 Jam : Seminggu 40 jam). Dan Jam lembur seperti hari biasa.
4. Untuk hari Minggu, lembur mulai dari awal kerja, 1 jam terhitung 2 jam.
Saya ucapkan terima kasih atas bantuannya.
waduh … lumayan panjang nih jawabnya … butuh waktu untuk bikin tabelnya … mohon maaf tidak bisa memberi jawaban saat ini
terima kasih banyak!!!!!!!!!!!!
Saya pernah, dapat file excel, ketka input data masuk, maka data itu lansung secara otomatis urut sediri pada sheet yang sama. Bagaimana ya caranya/