SOLUSI EXCEL : JURNAL PENDAFTARAN SISWA (update) – Belajar Microsoft Excel : Tips, Tricks & Tutorial

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

sheet "daftar"
sheet “daftar”

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

sheet "jurnal1"
sheet “jurnal1”

mari kita coba bersama cari solusinya…..

  1. pertama kasi warna kuning (tergantung preferensi anda) pada cell A11 di sheet “daftar
  2. pindah ke sheet “jurnal1”
  3. ketikkan formula berikut pada cell E3 =IF(Daftar!A2=””;””;Daftar!A2) , kebawah  kopikan sampai E11
  4. ketikkan formula berikut pada cell A3 =IF(E3=””;””;MATCH(E3;Daftar!$F$2:$F$11;0)) , kebawah  kopikan sampai A11
  5. ketikkan formula berikut pada cell B3 =IF(E3=””;””;VLOOKUP(A3;Daftar!$A$2:$E$11;2;FALSE)) , kebawah  kopikan sampai B11
  6. ketikkan formula berikut pada cell C3 =IF(E3=””;””;VLOOKUP(A3;Daftar!$A$2:$E$11;3;FALSE)) , kebawah  kopikan sampai C11
  7. 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

sheet "jurnal" telah urut ranking
sheet “jurnal” telah urut ranking

lalu bagai mana untuk menambah jumlah calon siswa pada sheet “daftar”

  1. silahkan taruh kursor pada cell A11 yang berwarna kuning
  2. blok kebawah sejumlah data yang akan ditambah, misal 4 data baru maka range yang diblok A11:A14
  3. klik kanan >>> insert >>>entire row…anda akan mempunyai 4 baris kosong baru
  4. tambahkan data calon siswa baru seperti contoh (cell warna hijau)
  5. kopikan cell F10 ke 4 baris di bawahnya

hasilnya akan seperti ini

sheet "daftar" tambah data
sheet “daftar” tambah data

setelah itu aktifkan sheet “jurnal1”, blok range A11:E11, kopikan kebawah sejumlah yang diinginkan (tidak tergantung jumlah data), hasilnya akan seperti ini

sheet "jurnal" data tambahan otomatis urut ranking
sheet “jurnal” data tambahan otomatis urut ranking

sebenarnya format sheet “jurnal1” dapat disederhanakan dengan menghilangkan kolom no urut, dan tabel jurnal akan lebih sederhana seperti gambar berikut

alternatif tabel jurnal
alternatif tabel jurnal

Keterangan :

  1. kolom Rank diisi nomor urut seperti biasa
  2. formula B3 =IF(A3=””;””;VLOOKUP(MATCH(A3;Daftar!$F$2:$F$15;0);Daftar!$A$2:$E$15;2;FALSE))
  3. formula  C3  =IF(A3=””;””;VLOOKUP(MATCH(A3;Daftar!$F$2:$F$15;0);Daftar!$A$2:$E$15;3;FALSE))
  4. 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

30 Comments

Jika sOdara menemukan sesuatu yang bermanpaat di marih .... silahkan kasih komennya