anaksmanda.com - Di artikel-artikel sebelumnya, kalian sudah belajar rumus satu per satu: VLOOKUP, nilai berbobot, IF, ranking, sampai COUNTIF. Sekarang saatnya menggabungkan semuanya menjadi satu file rekap nilai yang bekerja otomatis. Cukup ketik NIS dan nilai, lalu nama, nilai rapor, predikat, status, ranking, dan rekap kelas akan terisi sendiri.
Rangkai semua rumus dalam seri ini menjadi satu file nilai yang rapi, mudah diubah, dan aman dipakai bersama.
Sebelum mulai
Artikel ini merangkum materi seri Belajar Excel. Jika ada rumus yang belum dipahami, ikuti tautan ke artikel pembahasannya. Semua rumus memakai pemisah titik koma (;), dan angka bobot, KKM, serta batas predikat hanya contoh.
1. Rancangan File: 4 Sheet yang Saling Terhubung
Agar mudah dirawat, pisahkan data, pengaturan, dan hasil ke dalam sheet berbeda. Buat empat sheet dengan nama berikut, tanpa spasi agar rumus lebih mudah ditulis:
Berisi bobot nilai, KKM, batas kehadiran, dan batas predikat. Ubah angka di sini, seluruh hasil ikut menyesuaikan.
Tabel induk berisi NIS, nama, kelas, dan jenis kelamin. Diketik sekali, dipakai berulang.
Tempat guru mengetik NIS dan nilai. Kolom lainnya diisi oleh rumus.
Ringkasan kelas: jumlah lulus, remedial, ketuntasan, rata-rata, sebaran predikat, dan juara kelas.
2. Sheet Pengaturan
Tulis keterangan di kolom A dan nilainya di kolom B. Semua rumus di sheet lain akan merujuk ke sel-sel di kolom B ini.
| Sel | Keterangan (kolom A) | Isi (kolom B) |
|---|---|---|
| B2 | Bobot rata-rata tugas | 40% |
| B3 | Bobot PTS | 30% |
| B4 | Bobot PAS | 30% |
| B5 | KKM | 75 |
| B6 | Batas kehadiran | 80% |
| B7 | Batas predikat A | 90 |
| B8 | Batas predikat B | 80 |
| B9 | Batas predikat C | 70 |
| B10 | Cek total bobot | =IF(SUM(B2:B4)=100%;"OK";"Periksa bobot") |
3. Sheet DataSiswa
Isi tabel induk mulai baris 2: NIS di kolom A, nama di kolom B, kelas di kolom C, dan jenis kelamin di kolom D. Pastikan setiap NIS hanya muncul sekali dan formatnya seragam.
| A: NIS | B: Nama | C: Kelas | D: L/P |
|---|---|---|---|
| 2324001 | Andi Pratama | XI-1 | L |
| 2324002 | Budi Santoso | XI-1 | L |
| 2324003 | Citra Lestari | XI-1 | P |
4. Sheet Nilai: Kolom Input dan Kolom Rumus
Inilah inti file. Guru hanya mengisi kolom input, sedangkan kolom rumus terisi otomatis. Berikan warna latar berbeda pada kolom input, misalnya kuning muda, agar pengisi tahu bagian mana yang boleh diketik.
| Kolom | Isi | Jenis |
|---|---|---|
| A | NIS | Input |
| B | Nama siswa | Rumus |
| C – F | Tugas 1 sampai Tugas 4 | Input |
| G | Rata-rata tugas | Rumus |
| H – I | PTS dan PAS | Input |
| J | Kehadiran (dalam persen) | Input |
| K | Nilai rapor | Rumus |
| L | Predikat | Rumus |
| M | Status | Rumus |
| N | Ranking | Rumus |
Tulis rumus berikut di baris 2, lalu tarik ke bawah sampai baris terakhir siswa (contoh ini memakai baris 2 sampai 31 untuk 30 siswa).
Kolom B: Nama siswa dari NIS
IFERROR membuat sel tetap kosong saat NIS belum diisi. Pembahasan lengkap VLOOKUP.
Kolom G: Rata-rata tugas
Kolom K: Nilai rapor berbobot
Kolom L: Predikat
Pembahasan lengkap rumus IF predikat.
Kolom M: Status lulus atau remedial
Pembahasan lengkap rumus IF AND.
Kolom N: Ranking dengan penentu nilai PAS
Jika nilai rapor sama, siswa dengan nilai PAS lebih tinggi mendapat peringkat lebih baik. Pembahasan lengkap rumus ranking.
Pola IF(A2="";"";...)
Hampir semua rumus diawali pemeriksaan sel kosong. Dengan begitu, baris siswa yang belum diisi tetap bersih, tidak dipenuhi angka 0 atau tulisan "Remedial" yang menyesatkan.
| Nama | Rata tugas | PTS | PAS | Hadir | Rapor | Predikat | Status | Rank |
|---|---|---|---|---|---|---|---|---|
| Andi Pratama | 85 | 78 | 82 | 95% | 82 | B | Lulus | 2 |
| Budi Santoso | 75 | 80 | 76 | 70% | 77 | C | Remedial | 3 |
| Citra Lestari | 90 | 88 | 94 | 90% | 91 | A | Lulus | 1 |
Budi berstatus Remedial meskipun nilai rapornya 77 (di atas KKM), karena kehadirannya hanya 70%, di bawah batas 80%.
5. Sheet Rekap Kelas
Sheet Rekap merangkum seluruh kelas. Tulis keterangan di kolom A dan rumusnya di kolom B.
| Keterangan | Rumus |
|---|---|
| Jumlah siswa | =COUNTA(Nilai!$A$2:$A$31) |
| Siswa lulus | =COUNTIF(Nilai!$M$2:$M$31;"Lulus") |
| Siswa remedial | =COUNTIF(Nilai!$M$2:$M$31;"Remedial") |
| Ketuntasan kelas | =B3/B2 (format persen) |
| Rata-rata kelas | =ROUND(AVERAGE(Nilai!$K$2:$K$31);1) |
| Nilai tertinggi | =MAX(Nilai!$K$2:$K$31) |
| Nilai terendah | =MIN(Nilai!$K$2:$K$31) |
| Jumlah predikat A | =COUNTIF(Nilai!$L$2:$L$31;"A") |
| Juara kelas | =INDEX(Nilai!$B$2:$B$31;MATCH(1;Nilai!$N$2:$N$31;0)) |
Rumus juara kelas mencari baris dengan ranking 1 memakai MATCH, lalu mengambil nama di baris tersebut memakai INDEX. Ganti angka 1 menjadi 2 atau 3 untuk menampilkan juara berikutnya.
BACA JUGARumus COUNTIF untuk Menghitung Jumlah Siswa Lulus dan Remedial6. Mengunci Rumus dan Uji Coba
Setelah semua rumus terpasang, lindungi file agar rumus tidak terhapus saat dipakai bersama. Kunci sel rumus di sheet Nilai dan Rekap dengan Protect Sheet, lalu lindungi susunan sheet dengan Protect Workbook. Langkah lengkapnya ada di sini.
Sebelum file dipakai, lakukan uji coba berikut:
Cocokkan nilai rapor satu siswa dengan kalkulator untuk memastikan bobotnya benar.
Masukkan nilai tepat 70, 80, 90, dan KKM untuk memastikan predikat dan status sudah tepat.
Ganti bobot atau KKM di sheet Pengaturan, lalu pastikan semua hasil ikut berubah.
Ketik NIS yang tidak terdaftar. Kolom nama harus tetap kosong, bukan menampilkan error.
7. Pertanyaan yang Sering Diajukan
Bagaimana jika jumlah siswa lebih dari 30?
Tarik rumus sampai baris terakhir siswa, lalu ubah rentang $2:$31 di rumus ranking dan rekap menjadi jumlah baris yang sesuai, misalnya $2:$41 untuk 40 siswa.
Apakah file ini bisa dipakai untuk beberapa mata pelajaran?
Bisa. Salin sheet Nilai untuk setiap mata pelajaran, misalnya Nilai_MTK dan Nilai_IPA, lalu sesuaikan rujukan sheet di rumus rekap. Jika bobot atau KKM berbeda, buat kolom pengaturan terpisah untuk setiap mata pelajaran.
Kenapa rata-rata kelas tidak terpengaruh baris kosong?
Karena rumus nilai rapor menghasilkan teks kosong ("") untuk baris yang belum diisi, dan AVERAGE mengabaikan sel berisi teks. Itulah salah satu manfaat pola IF(A2="";"";...).
Apakah file ini bisa dibuka di Google Sheets?
Bisa. Semua fungsi yang dipakai, yaitu VLOOKUP, IFERROR, AVERAGE, ROUND, IF, AND, RANK.EQ, COUNTIF, COUNTIFS, INDEX, dan MATCH, tersedia di Google Sheets. Fitur proteksinya diganti dengan Data > Lindungi sheet dan rentang.
Bagaimana jika ada siswa yang belum ikut PAS?
Kosongkan dulu nilainya dan beri tanda pada siswa tersebut. Nilai rapornya baru dihitung setelah ujian susulan, agar siswa tidak tercatat remedial hanya karena nilainya belum masuk.
8. Kesimpulan
Rekap nilai otomatis dibangun dari empat sheet: Pengaturan untuk aturan penilaian, DataSiswa untuk data induk, Nilai untuk input dan perhitungan, serta Rekap untuk ringkasan kelas. Setiap rumus dalam seri Belajar Excel punya peran di file ini.
Simpan semua angka aturan di sheet Pengaturan, gunakan pola pemeriksaan sel kosong, kunci rumus sebelum file dipakai bersama, dan lakukan uji coba di nilai-nilai perbatasan. File yang sama bisa dipakai lagi di semester berikutnya hanya dengan mengganti data.
- Rumus Excel pakai koma atau titik koma
- Fungsi tanda $ di Excel
- Rumus IF predikat nilai A, B, C, D
- Rumus IF AND: lulus dengan KKM dan kehadiran
- Rumus ranking siswa, termasuk jika nilainya sama
- Cara menghitung nilai rapor dengan bobot
- Rumus COUNTIF untuk menghitung siswa lulus dan remedial
- VLOOKUP untuk menampilkan nama siswa dari NIS
- Rumus menghitung umur per tanggal tertentu
- Cara mengunci rumus agar tidak terhapus
- Studi kasus: membuat rekap nilai otomatis satu kelas (sedang dibaca)
Baca Juga
Sumber dan Kredit Gambar
- Microsoft Support — VLOOKUP function
- Microsoft Support — RANK.EQ function
- Microsoft Support — Protect a worksheet
- Foto laptop dan spreadsheet — Lukas Blazek, Unsplash License
- Foto apel dan buku — Element5 Digital, Unsplash License
- Foto pengajar di depan papan tulis — ThisisEngineering, Unsplash License
Komentar
Memuat komentar...
Tulis Komentar
Silakan bertanya atau berbagi pengalaman. Komentar berisi tautan aktif atau promosi akan dihapus.