Membuat Nomor Urut Otomatis

Tutorial: Trik di Ms Excel

Trik-trik yang saya tulis di bawah ini saya dapet selama bekerja dengan excel untuk keperluan administrasi kelas selama saya mengajar.

• Nomor Urut Otomatis
• Konversi Bilangan ke Huruf
• Membuat rangking
• Rumus Kelulusan UN 2008
• Rumus Kelulusan UN 2011

Jadi bukan mau ngajarin tapi sekedar share aja, siapa tahu bermanfaat buat yg lain juga.
Buat yang punya trik-trik baru silahkan posting juga di sini, biar ilmunya bermanfaat

Nomor urut otomatis

Biasanya nomor urut otomatis di excel kita buat dengan memasukkan angka “1″ pada sel di baris pertama, lalu angka “2″ dibaris berikutnya, kemudian dicopy (drag) dengan mouse.
Pembuatan nomor urut pada excel bisa juga menggunakan cara lain yaitu dengan memasukkan angka “1″ pada baris pertama, lalu di baris berikutnya menggunakan formula “=<alamat sel yg berisi angka 1>+1″.
Misalnya angka “1″ ada pada sel A4, maka pada sel A5 masukkan : =A4+1, lalu sel yang berisi rumus tadi (A5) di copy ke bawah.
Keuntungannya, jika kita ingin mereset urutan nomor, dapat dilakukan dengan menyisipkan angka 1 kembali di tengah urutan.

Nomor urut otomatis berulang

Nomor urut berulang, buat pada sel A5 masukkan rumus =if(A4>=n,1,A4+1)
n=nilaiterbesar dari urutan nilai, misalnya kita ingin nomor urut 1 s/d 10, lalu kembali ke 1. maka n diganti 10.

Konversi bilangan ke huruf

Nilai puluhan dapat di konversi ke huruf dengan sedikit rumus. Misalnya dengan menggunakan fungsi vlookup.
A. buat tabel acuan

Angka di kolom A diberi awalan single apostrop (‘) agar tersimpan pada sel dengan notasi string.
B. Buat rumus vlookup
=CONCATENATE(VLOOKUP(LEFT(E2,1),$A$2:$C$11,2),” “,VLOOKUP(RIGHT(E2,1),$A$2:$C$11,3))
Rumus vlookup sebenarnya dapat di letakkan di sheet lain agar tidak mengganggu tabel, penambahan ‘$’ agar alamat tabel aray menjadi absolut.

Membuat rangking berdasarkan nilai

Untuk mengetahui rangking berdasarkan nilai, Ms Excel telah menyediakan sebuah fungsi khusus yaitu :
=rank(nilai,daftar nilai,order)
nilai= adalah sebuah bilangan yang ingin diketahui rangking-nya
daftar nilai = adalah range yang berisi nilai-nilai yang akan diranking
order = diisi 0 untuk descending (urutan menurun, atau selain 0 untuk pengurutan menaik)

Rumus kelulusan UN SMP 2008

Peningkatan standar kelulusan UN sudah ditetapkan, lepas dari pro dan kontra seputar pelaksanaan Ujian nasional beberapa rekan menanyakan cara membuat rumus kelulusan UN dengan Ms Excel. Setelah membaca kriteria kelulusan barangkali rumus ini bisa digunakan nanti. Tapi harus diuji coba dulu (mumpung masih banyak waktu ). Jadi kalau masih belum benar ya silahkan dikoreksi kalau dianggap benar, hati-hati siapa tau kebijakan berubah .
Standar nilai kelulusan untuk setiap jenjang mulai SMA, MA, SMK, SMP hingga SD semua sama, yaitu rata-rata minimal 5,25 dengan tidak ada nilai di bawah 4,25 (ini kriteria pertama). Khusus untuk siswa SMK nilai mata pelajaran kompetensi keahlian minimal 7,00 dan digunakan untuk menghitung nilai rata-rata UN SMK tersebut.
Apabila kriteria di atas tidak tercapai, maka ada kriteria kedua yang mensyaratkan: boleh terdapat nilai 4,00 hanya pada satu mata pelajaran yang di-UN-kan, dan lima mata pelajaran lainnya harus mencapai nilai sekurang-kurangnya 6,00 dan mencapai nilai rata-rata minimal 5,25.
Contoh yang saya buat untuk rumus kelulusan tingkat SMP, jadi untuk jenjang lain silahkan lakukan perubahan seperlunya.
Karena ada dua skenario lulus, berarti rumus dimulai dengan operator logika “OR” dst.
Kemudia pada Cel “i5″ dimasukkan sebuah kriteria untuk fungsi COUNTIFdengan isi “>=6″. nila-nilai minimal dapat dimasukkan dalam fungsi atau di dalam sel sebagai referal seperti contoh disini.
Rumus untuk keterangan “Lulus” dan “Tidak Lulus” adalah sbb :

=IF(OR(AND(F2>=I$2,MIN(B2:E2)>=I$3),AND(MIN(B2:E2)>=I$4,

COUNTIF(B2:E2,I$5)=3,F2>=5.25)),”LULUS”,”TIDAK LULUS”)

Kalau gambarnya kurang jelas silahkan download file contoh di :

file contoh rumus un2008.xls