Membuat Blanko Daftar Nilai Dengan Menampilkan Nama Siswa Secara Otomatis

Kali ini saya akan berbagi untuk membuat blanko daftar nilai dimana guru cukup memilih kelas maka nama siswa yang pada kelas yang bersangkutan tampil. Jadi cukup tampil dalam satu sheet Microsoft Excel dan wali kelas tinggal copy-paste nilai ke dalam blanko raport.

Tujuan dari pembuatan file ini adalah agar satu file bisa digunakan untuk semua kelas, sehingga blanko nilai tidak perlu dibuat dalam banyak sheet untuk blanko nilai masing-masing sheet.

Baik lanjut saja ya..

Kita memerlukan 2 (dua) sheet, pertama sheet untuk data siswa , kemudian kedua sheet untuk blanko nilai.

Pada Sheet1 ganti nama menjadi DATA SISWA dan Sheet2 diganti menjadi NILAI

MEMBUAT DATA SISWA

Pada sheet DATA SISWA buat kolom sebagai berikut:

Pada Kolom A (NO) ,B (NAMA SISWA) ,C (L/P) ,D (NIS) dan E (KELAS) diisi data siswa sesuai kolomnya

Sedangkan untuk kolom F, G, H, I, J, dan K dibuatkan formula untuk mempfilter data yang dibutuhkan.

Untuk penulisan formula saya berasumsi settingan Region Number Format adalah Indonesian, artinya penulisan sparator formula menggunakan titik koma (;).

Pada sel F2 (kolom NO2) buat formula sebagai berikut;

=IF(G2="";"";COUNT(F$1:F1)+1)

Pada sel G2 (kolom Kelas2) buat formula

=IF(E2="";"";IF(E2=E1;"";E2))

Pada sel H2 (kolom NO3) buat formula

=IF(I2="";"";1)

Pada sel I2 (kolom NAMA SISWA2) buat formula
=IF(NILAI!$C$4=$E2;B2;"")

Pada sel J2 (kolom L/P2)
=IF(NILAI!$C$4=$E2;C2;"")

Pada sel K2 (kolom L/P2)
=IF(NILAI!$C$4=$E2;D2;"")

Berikutnya copy range F2:K2 kemudian paste pada baris di bawahnya sebanyak data siswa . Misalkan jumlah siswa seluruhnya dari kelas 10 hingga kelas 12 ada 30 rombel dan rata-rata siswa per rombel 36 siswa maka baris yang dibutuhkan sebanyak 1.080 baris, jadi formula dicopy bisa sampai baris 1.100

MEMBUAT BLANKO NILAI

Sekarang kita akan membuat blanko nilai, pada sheet NILAI Buat tabel persis seperti di bawah ini termasuk posisi baris dan kolomnya.

Membuat formula data siswa
Pada sel B9 (kolom NAMA SISWA) buat formula
=IF($C$4="";"";IF($A9="";"";VLOOKUP($A9;'DATA SISWA'!$H$2:$K$753;2;0)))

Pada sel C9 (kolom L/P) buat formula
=IF($C$4="";"";IF($A9="";"";VLOOKUP($A9;'DATA SISWA'!$H$2:$K$753;3;0)))

Pada sel D9 (kolo NO INDUK) buat formula
=IF($C$4="";"";IF($A9="";"";VLOOKUP($A9;'DATA SISWA'!$H$2:$K$753;4;0)))

Copy range B9:D9 kemudian paste pada baris dibawahnya sampai dengan baris 56

Membuat formula untukdata nilai yang dientri
Sel E57 buat formula
=IF(SUM(E9:E56)>0;SUM(E9:E56);"")
selanjutnya copy formula tersebut dan paste pada range F57 sampai dengan K57
selanjutnya paste juga untuk range M57 sampai dengan S57

Sel U57 buat formula
=IF(COUNT(E57:K57)=0;1;COUNT(E57:K57))

Sel Y57 buat formula
=IF(COUNT(M57:S57)=0;1;COUNT(M57:S57))

Membuat Formula Rata-rata nilai pengetahuan dan keterampilan
Sel U9 buat formula
=IF($D9="";"";ROUND(SUM(E9:K9)/$U$57;0))
selanjutnya copy formula tersebut untuk baris di bawahnya sampai dengan baris 56

Sel Y9 buat formula
=IF($D9="";"";ROUND(SUM(M9:S9)/$Y$57;0))
selanjutnya copy formula tersebut untuk baris di bawahnya sampai dengan baris 56

Formula ini tidak menggunakan =average() , karena jika ada siswa yang tidak memiliki nilai pada kompetensi tertentu maka pembaginya adalah jumlah kompetensi yang dinilai, sehingga nilainya akan lebih rendah dari siswa yang lengkap nilainya.
Contoh:
siswa A nilainya 74, 75, 77, 74 maka rata-ratanya adalah 300 dibagi 4 yaitu 75
siswa B nilainya 75, , 76, 74 maka rata-ratanya adalah 225 dibagi 4 yaitu 56,25

Kalau menggunakan formula =average() , maka berapapun jumlah nilai dari kompetensi maka nilainya akan dirata-rata.
Contoh:
siswa A nilainya 74, 75, 77, 74 maka rata-ratanya adalah 300 dibagi 4 yaitu 75
siswa B nilainya 75, , 76, 74 maka rata-ratanya adalah 225 dibagi 3 yaitu 75

Membuat Nilai Pengetahuan Akhir
Nilai akhir merupakan gabungan dari nilai Harian, Nilai Ujian Tengah Semester (UTS) dan Nilai Ujian Akhir Semester (UAS) berdasarkan bobot yang ditetapkan sekolah.
Caranya:
Pada sel X9 buat formula
=IFERROR(ROUND(U9*$V$2/100+V9*$V$3/100+W9*$V$4/100;0);"")

copy sel X9 kemudian paste pada baris di bawahnya dari sel X10 sampai dengan sel X56

Membuat Kesimpulan Nilai
Sel E59 buat formula
=IFERROR(AVERAGE(E$9:E$56);0)

Sel E60 buat formula
=IFERROR(MAX(E$9:E$56);"")

Sel E61 buat formula
=IFERROR(MIN(E$9:E$56);"")

Copy Range E59:E61 kemudian paste untuk range F59:K61 , range M59:S61, serta range U59:Y61

Membuat List Kelas

Buatlah urutan angka dari 1 sampai 30 ( jumlah rombel) mulai sel AA9 ke bawah

Pada sel AB9 buat formula
=IFERROR(VLOOKUP(AA9;'DATA SISWA'!$F$2:$G$753;2;0);"")

copy formula AB9 untuk baris di bawahnya sampai dengan baris nomor terakhir (AB10 : AB38)

Langkah berikutnya :

Letakkan kursor pada sel C4 kemudian
klik Menu Data,
pilih Data Validation
Pada tampilan Allow pilih List
Letakkan kursor pada kolom Sourch , kemudian pilih/blok range AB9:AB38
Klik OK

Fungsi langka ini adalah memberikan daftar list kelas, nanati jika diklik menu list maka akan tampil pilihan nama kelas yang tercantum pada range AB9:AB38

FINALISASI
Untuk pembuatan formula sudah selesai sekarang tinggal finalisasi :
1. Sembunyikan (Hide) kolom AA dan Kolom AB, dengan cara :

Klik status Kolom AA dan AB
Klik Kanan, kemudian pilih Hide

Sembunyikan juga Baris 57 dengan cara klik status baris 57, kemudian klik kanan, pilih Hide

2. Protect Sel
Supaya formula-formula yang dibuat tidak rusak/kacau maka diberikan protect, hanya sel-sel tertentu yang akan diisi tidak diprotect.

Caranya:

blok/pilih semua halaman sheet dengan cara klik sel di pojok kiri atas sheet
Klik Menu Home,
pilih Format pada Toolbar,
kemudian pilih Format Cells
Pada tampilan Costum List centang pada Locked dan Hiden
OK

Berikutnya kita akan membuka beberapa sel sebagai data isian yang tidak diprotect caranya:
pilih/blok area/range yang akan dilepaskan protectnya

Pada Menu Home pilih Format
Kemudian pilih Format Cells
Pada tampilan Costum List hilangkan centangan pada Locked dan Hiden
OK

Area yang perlu dilepas protecnya adalah
Kolom Mata Pelajaran
Kolom Kelas
Kolom Semester
Kolom Nama Guru
Kolom Bobot
Range nilai Pengetahuan
Range Nilai Ketrampilan
Range Nilai UTS, Nilai UAS

3. Protect Password

Jika sudah menentukan area yang diprotect dan tidak maka langkah berikutnya memberikan password. Caranya

Pada Menu Home pilih Format
Kemudian pilih Protect Sheet
Isi password pada kolom protect, klik OK
Isi sekali lagi password yang sama, kemudian OK

Pastikan anda mencatat passwordnya, karena jika lupa maka protect tidak bisa dibuka.

Nah selesailah sudah file daftar nilai anda, silahkan simpan file kemudian silahkan dicoba pilih salah satu kelas, maka nama siswa akan muncul secara otomatis.

Jika tidak tampil berarti ada beberapa kesalahan penulisan formula, silahkan diteliti ulang.

Download

Panduan dalam bentuk PDF

Contoh Blanko Nilai Final (siap pakai)