Daftar Nilai Pelajaran

Dah lama ga sharing serunya excel, selamat berjumpa kembali.
Kali ini kami coba hadirkan project sample yang cukup menarik yaitu menggabungkan beberapa fungsi/ formula atau rumus menjadi sebuah rumus yang berkumpul dan panjang (*keliatannya). Megaformula namanya jika meminjam istilah bang Abimono pengasuh xl-mania di yahoogroups.

Sample ini adalah banyak dicari para pengajar untuk mempermudah kalkulasi dalam penilaian ulangan, ya ... tulisan ini sekaligus menjawab pertanyaan dari Mas Novis seorang pengajar SMP Negeri 244 Jakut. Selamat malam Pak Novis!

Kasus:

Case diatas adalah range input data dengan kolom-kolom diantaranya nilai hasil ulangan, nilai remedial pertama dan remedial kedua. Kolom terakhir adalah nilai yang dicatat dengan beberapa ketentuan-ketentuan sbb:

Diketahui beberapa ketentuan sbb:

1. Nilai minimun (tidak perlu remedial) adalah 75
2. Remedial pertama (Rm1) untuk nilai ulangan (UH) dibawah 75
3. Remedial kedua (Rm2) untuk nilai Rm1 dibawah 75
4. Hasil remedial diatas 75 (contoh: 85) pada kolom T akan di tulis tetap 75
5. Jika pada contoh Nani dan Boi diatas dimana semua hasil nilai baik UH, Rm1 dan Rm2 sama sama dibawah 75, maka pada kolom T akan memilih nilai yang paling tinggi diantara ketiga nilai tersebut.

Jawab:

Untuk menjawab tantangan ini diperlukan beberapa jurus (baca: formula) dasar yaitu diantaranya: IF, COUNT, AND dan MAX.

Catatan: ohya ... tidak ada keterikatan dalam penentuan jurus karena tentunya beda buku referensi akan beda rumus dan beda guru akan beda pula jurus jurusnya. Tapi jurus dari kami untuk solusi case ini adalah jurus dasar yang tidak mematikan .... :P

Singkatnya: formula untuk cell I11 dimana mulai nilai untuk nama pertama adalah:

=IF(AND(COUNT(F11:H11)=3,H11>=75),75,IF(AND(COUNT(F11:H11)=2,G11>=75),75,IF(COUNT(F11:H11)>=1,MAX(F11:H11,F11))))

jika dipisahkan satu-satu menjadi

IF(AND(COUNT(F11:H11)=3,H11>=75),75,

IF(AND(COUNT(F11:H11)=2,G11>=75),75,

IF(COUNT(F11:H11)>=1,

MAX(F11:H11,F11))))

Jika diterjemahkan satu persatu

IF pertama:

=IF(AND(COUNT(F11:H11)=3,H11>=75),75  ... yaitu jika jumlah cell yang mengandung angka pada range F11:H11 adalah sebanyak 3 cell dan jika H11 bernilai lebih dari atau sama dengan 75 maka nilai untuk cell tersebut adalah 75

woles, ulangi baca terjemahannya kembali kemudian hubungkan dengan ketentuan diatas, untuk ketentuan yang mana if pertama ini? Yaa benar ...  formula ini untuk ketentuan 3 dan 4 diatas.

Atau jika diterjemahkan kembali lebih pas adalah begini:

"Jika ketiga 3 tahap ulangan dilakukan (UH, Rm1 dan Rm2) dan jika Rm2 bernilai lebih dari atau sama dengan 75 maka nilai untuk kolom T tersebut adalah 75"

contoh case: nilai untuk Astian

IF kedua:

IF(AND(COUNT(F11:H11)=2,G11>=75),75 ... hampir sama seperti terjemahan diatas.

Yaa, formula ini ditujukan untuk ketentuan 2 dan 4. Dimana formula ini bagi kolom yang terisi nilai hanya pada kolom UH dan Rm1, sedangkan IF pertama untuk mengeksekusi jika ketiga kolom UH, Rm1 dan Rm2 berisikan nilai atau angka. Betul ... angka 75 diakhir adalah sebagai eksekutor jika kedua argument "COUNT(F11:H11)=2" dan "G11>=75" adalah benar.

contoh case: nilai untuk Novella

IF ketiga:

IF(COUNT(F11:H11)>=1 ... ini untuk ketentuan 1 dan 4 dimana Rm1 dan Rm2 tidak dilakukan karena UH sudah cukup 75 (atau diatasnya)

contoh case: nilai untuk Alfian, Ratih dan Marsha

Terakhir:

MAX(F11:H11,F11) ... ini adalah untuk ketentuan ke 5 dimana tidak terpenuhinya ketentuan 1, 2, 3 dan 4 atau formula terakhir untuk eksekusi jika evaluasi ketiga IF sebelumnya tidak ditemukan nilai 75 atau diatasnya pada range F11:H11.

Mudah bukan?

Jika sudah sreg dengan formula tersebut, copy dan paste untuk cell-cell dibawahnya. Setelah itu coba simulasi dengan mengisi acak nilai-nilai pada kolom nilai UH, Rm1 dan Rm2.

Selamat mencoba dan gutnite!