Lompat ke konten Lompat ke sidebar Lompat ke footer
Beranda / Excel / Template

Cara Membuat Rapor Bayangan Otomatis di Excel Sebelum Input e-Rapor

Ditulis oleh Terbit
Rapor bayangan otomatis di Excel dengan INDEX dan MATCH

Rapor bayangan adalah draf rapor di Excel untuk mengecek nilai sebelum diinput ke e-Rapor. Caranya: kumpulkan nilai akhir semua mata pelajaran dalam satu sheet, lalu buat satu sheet berbentuk rapor yang mengambil data siswa dengan INDEX dan MATCH berdasarkan nomor urut. Cukup ganti nomor urutnya, dan rapor siswa berikutnya langsung tampil, siap dicek atau dicetak.

Dicek terakhir: 9 November 2026 oleh Arfan Putra. Semua rumus di artikel ini dan di template sudah dihitung ulang untuk beberapa siswa contoh, termasuk siswa yang nilainya belum lengkap.

Unduh template gratis: rapor bayangan otomatis (Excel), berisi sheet Pengaturan, Nilai, dan Rapor untuk 30 siswa dan 8 mata pelajaran. Nama dan NISN di dalamnya fiktif.

Rapor bayangan bukan pengganti rapor resmi. Rapor yang dibagikan tetap dicetak dari e-Rapor atau aplikasi yang ditetapkan sekolah. Gunanya untuk menemukan nilai yang belum masuk, salah ketik, atau siswa yang belum tuntas sebelum semuanya diinput.

Catatan penulisan rumus: rumus di artikel ini memakai koma (,) sebagai pemisah. Kalau Excel menolak rumusnya, ganti koma dengan titik koma (;) sesuai pengaturan wilayah di Windows.

Susunan File: Tiga Sheet

SheetIsi
PengaturanIdentitas sekolah, wali kelas, kepala sekolah, tanggal rapor, nama mata pelajaran (A17:A24), dan materi yang disebut di kalimat capaian (B17:B24)
NilaiSatu baris per siswa (baris 5–34): nomor urut di kolom A, nama di C, nilai akhir 8 mapel di D sampai K, lalu jumlah, rata-rata, ranking, serta sakit, izin, dan alpa
RaporBentuk rapor satu siswa. Nomor urut siswa diketik di sel C3, sisanya terisi otomatis

Nilai akhir per mapel bisa diambil dari tabel rekap guru mapel. Kalau belum punya, cara menghitungnya ada di artikel rumus Excel nilai rapor.

Langkah 1: Isi Sheet Nilai

  1. Isi nomor urut, NISN, dan nama siswa. Kolom NISN sebaiknya berformat Text supaya nol di depan tidak hilang.
  2. Judul kolom mapel di baris 4 tidak perlu diketik, karena sudah dirumuskan =Pengaturan!A17, =Pengaturan!A18, dan seterusnya. Nama mapel cukup diubah di sheet Pengaturan.
  3. Salin nilai akhir tiap mapel ke kolom D sampai K, lalu isi jumlah sakit, izin, dan alpa. Kalau absensi direkap di Excel, angkanya bisa diambil dari rekap itu.

Kolom jumlah memakai pengecekan supaya siswa yang nilainya belum lengkap langsung kelihatan:

=IF(COUNT(D5:K5)<8,"",SUM(D5:K5))

Kalau ada satu saja mapel yang kosong, jumlahnya ikut kosong. Rata-ratanya dibagi 8, jadi tidak mungkin salah karena ada sel yang terlewat. Kolom ranking memakai RANK.EQ dari jumlah nilai. Sekolah yang tidak memakai peringkat bisa mengabaikan kolom ini. Kalau memakai peringkat dan ingin nilai yang sama tidak menghasilkan ranking kembar, lihat rumus ranking Excel agar tidak ganda.

Langkah 2: Ambil Data Siswa dengan INDEX dan MATCH

Di sheet Rapor, sel C3 berisi nomor urut siswa. Semua rumus di sheet ini mencari baris siswa itu di sheet Nilai. Contohnya rumus nama:

=INDEX(Nilai!$C$5:$C$34,MATCH($C$3,Nilai!$A$5:$A$34,0))

MATCH mencari nomor urut di kolom A sheet Nilai dan menghasilkan posisinya, misalnya baris ke-5 dari tabel. INDEX lalu mengambil isi kolom nama di posisi yang sama. Angka 0 di MATCH berarti harus sama persis.

Untuk nilai mapel, rentangnya diperlebar ke seluruh kolom nilai, dan nomor kolomnya ditambahkan:

=INDEX(Nilai!$D$5:$K$34,MATCH($C$3,Nilai!$A$5:$A$34,0),1)

Angka 1 di akhir berarti kolom pertama rentang itu, yaitu mapel pertama. Mapel kedua memakai angka 2, dan seterusnya sampai 8. Di template, rumus ini dibungkus pengecekan sel kosong, karena INDEX yang mengambil sel kosong menghasilkan 0. Tanpa pengecekan itu, nilai yang belum masuk tampil sebagai 0 di rapor.

Kenapa dicari berdasarkan nomor urut, bukan nama? Nomor urut tidak mungkin salah ketik atau dobel, sedangkan nama bisa berbeda satu spasi atau huruf. Kalau ingin mencari berdasarkan NISN, ganti rentang kolom A dengan kolom B dan ketik NISN di C3.

Langkah 3: Kalimat Capaian Otomatis (Sebagai Draf)

Kolom capaian kompetensi di template berisi kalimat draf yang disusun dari nilai dan materi di sheet Pengaturan:

=IF(C14="","(nilai belum diisi)",IF(C14>=Pengaturan!$B$13,"Menunjukkan penguasaan yang sangat baik dalam "&Pengaturan!$B$17&".",IF(C14>=Pengaturan!$B$14,"Menunjukkan penguasaan yang baik dalam "&Pengaturan!$B$17&".","Perlu bimbingan dalam "&Pengaturan!$B$17&".")))

Sel B13 berisi batas "sangat baik" (contoh 85), dan B14 berisi batas tuntas (contoh 70). Tanda & menyambung teks dengan isi sel, jadi kalimatnya ikut berubah kalau materi di Pengaturan diubah. Logika IF bertingkatnya sama dengan di artikel rumus IF untuk predikat.

Kalimat ini hanya kerangka. Di Kurikulum Merdeka, deskripsi capaian sebaiknya menyebut tujuan pembelajaran yang benar-benar sudah dan belum dikuasai tiap siswa, jadi tetap perlu diperiksa dan diperbaiki guru sebelum diinput.

Langkah 4: Cek, Lalu Cetak Satu per Satu

  1. Ketik nomor urut di sel C3, misalnya 1. Cek nama, nilai, dan jumlah ketidakhadirannya.
  2. Perhatikan baris Mapel di bawah batas tuntas. Rumusnya =COUNTIF(C14:C21,"<"&Pengaturan!B14), menghitung mapel yang nilainya di bawah batas tuntas.
  3. Untuk mencetak, tekan Ctrl+P. Area cetak di template sudah diatur ke bentuk rapor saja dan dipaskan ke satu halaman A4. Kalau membuat sendiri, blok area rapornya, pilih Page Layout → Print Area → Set Print Area, lalu di pengaturan cetak pilih Fit Sheet on One Page.
  4. Ganti nomor urut ke siswa berikutnya, lalu ulangi.

Kalau ingin mencetak rapor bayangan seluruh kelas sekaligus tanpa mengganti nomor satu per satu, sheet Nilai bisa dijadikan sumber data mail merge di Word. Caranya sama dengan membuat kartu ujian atau draf blanko ijazah dengan mail merge.

Kalau Hasilnya Error

  • Muncul #N/A di semua isian. Nomor urut di C3 tidak ada di kolom A sheet Nilai, misalnya diketik 31 padahal siswanya hanya 30. Bisa juga nomor di kolom A tersimpan sebagai teks. Ketik ulang nomornya sebagai angka.
  • Nilai mapel tampil 0, padahal belum diisi. Rumus INDEX belum dibungkus pengecekan sel kosong. Pakai =IF(INDEX(...)="","",INDEX(...)) seperti di template.
  • Nilai yang muncul milik mapel lain. Nomor kolom di akhir INDEX salah, atau urutan mapel di sheet Pengaturan berbeda dengan urutan kolom di sheet Nilai. Samakan urutannya.
  • Jumlah dan rata-rata kosong. Masih ada mapel yang nilainya belum diisi. Ini disengaja, supaya rapor dengan nilai yang belum lengkap tidak terlanjur dicek.
  • Kalimat capaian terpotong saat dicetak. Aktifkan Wrap Text di kolom capaian dan perbesar tinggi barisnya.
  • Rapor tercetak dua halaman. Area cetak belum diatur, atau pilihan Fit Sheet on One Page belum dipilih.

Sumber