Cara Membuat Kartu Ujian dengan vlookup

Halo sobat semua. Kembali lagi bersama BelajarOffice.com sudah lama tidak update baru kali bisa update lagi. Pada artikel kali ini admin akan berbagi contoh cara membuat Format Cetak Kartu Ujian Semester Siswa Rumus Excel lengkap dengan jadwal UAS (Ujian Akhir Semester). Format Kartu Ujian yang akan admin buat ini menggunakan rumus Excel yang dilengkapi dengan tombol Print Otomatis menggunakan koding Excel vba macro yang mudah-mudahan bisa bermanfaat bagi guru disekolah dan sobat semua sebagai bahan latihan dalam menguasai dan mendalami rumus-rumus Excel.

Ok langsung saja berikut ini contoh tampilannya yang saya dapatkan dari format foto copy siswa salah satu sekolah SMP, admin lihat penulisan no Induk, Nama, Kelas, Nomer Tes ditulis secara manual, kemudian Admin punya ide bagaimana kalau dibuat supaya bisa mencetak secara otomatis dengan sistem kerjanya mirip Mail Marge pada Ms Word sehingga lebih praktis.

Formatnya Kartu Ujian Semester ini dibuat dengan sederhana hanya berupa lembaran berisi data siswa dan jadwal ujian pelajarannya. Berikut ini contoh gambar formatnya

Format Cetak Kartu Ujian Siswa (UAS)

Format Cetak Kartu Ujian Siswa Rumus Excel admin buat menjadi 2 sheet yaitu sebagai berikut :

Sheet1 diberinama “DataSiswa”

Sheet “DataSiswa” digunakan untuk menginput data-data siswa berkaitan dengan ujian meliputi : No Induk, Nama, Kelas, No Test, Ruang Ujian.

Silahkan disesuaikan dengan kebutuhan masing-masing.

Sheet DataSiswa

Selanjutnya Sheet2 kita berinama “KartuUjian”

Sheet ini digunakan untuk mencetak kartu ujian semester siswa lengkap dengan jadwal ujiannya formatnya seperti gambar dibawah ini

Format Cetak Kartu Ujian Siswa (UAS)

Data-datanya meliputi jadwal ujian hari, tanggal, waktu, mata pelajaran yang diujikan, paraf, kemudian terdapat juga yang nanti perlu dibuat rumus yaitu pada format ini, supaya saat mencetak/print No Induk, Nama, Kelas , No Test dan Ruang berubah otomatis, sedangkan untuk jadwalnya tetap karena datanya sama untuk satu kelas.

Pada contoh file ini juga admin buat satu lembar cukup untuk 3 lembar format sehingga lebih hemat kertas dengan menggunakan ukuran kertas legal atau bisa juga F4 supaya muat.

Selanjutnya akan admin jelaskan cara membuat rumusnya, seperti biasa rumus yang digunakan rumus Excel Vlookup

Admin tidak bahas lagi, untuk lebih mendalami rumus Excel Vlookup bisa baca pada link artikel dibawah ini

Baca juga : Rumus Excel Vlookup

Lembar Kartu Ujian No 1

Nama

Pada cell D10 ketikan rumusnnya :

=VLOOKUP($D$9;DataSiswa;2)

Kelas

Pada cell D10 ketikan rumusnnya :

=VLOOKUP($D$9;DataSiswa;3)

No Test

Pada cell F9 ketikan rumusnnya :

=VLOOKUP($D$9;DataSiswa;4)

Ruang

Pada cell F1o ketikan rumusnnya :

=VLOOKUP($D$9;DataSiswa;5)

Lembar Kartu Ujian No 2

No Induk

Pada cell K9 ketikan rumusnnya :

=IF(D9=””;””;INDEX(DataSiswa!B6:B25;MATCH(D9;DataSiswa!B6:B25;1)+1))

Nama

Pada cell K10 ketikan rumusnnya :

=VLOOKUP($K$9;DataSiswa;2)

Kelas

Pada cell K11 ketikan rumusnnya :

=VLOOKUP($K$9;DataSiswa;3)

No Test

Pada cell M9 ketikan rumusnnya :

=VLOOKUP($K$9;DataSiswa;4)

Ruang

Pada cell M1o ketikan rumusnnya :

=VLOOKUP($K$9;DataSiswa;5)

Lembar Kartu Ujian No 3

No Induk

Pada cell R9 ketikan rumusnnya :

=IF(D9=””;””;INDEX(DataSiswa!B6:B25;MATCH(D9;DataSiswa!B6:B25;1)+1))

Nama

Pada cell R10 ketikan rumusnnya :

=VLOOKUP($R$9;DataSiswa;2)

Kelas

Pada cell R11 ketikan rumusnnya :

=VLOOKUP($R$9;DataSiswa;3)

No Test

Pada cell T9 ketikan rumusnnya :

=VLOOKUP($K$9;DataSiswa;4)

Ruang

Pada cell T1o ketikan rumusnnya :

=VLOOKUP($K$9;DataSiswa;5)

Selanjutnya dibuat tombol print otomatis mengggunakan rumus Excel VBA

Tombol Print Otomatis Excel VBA

Siapkan terlebih dahulu kolom untuk pengisian data-data pengaturan no yang akan diprint sebagai berikut :

Dari No pada cell Y6 (untuk input nomer urutan yang akan di print mulai dari …)
Sampai No pada cell Y7 (untuk input nomer urutan yang akan di print sampai no …)
Jml Copy pada cell Y8 (untuk input rangkap kartu ujian yang akan diprint)

Kemudian kita tambahkan dua buah command button

Klik dari tab menu DEVELOPER >>> klik Insert >> Command Button

Selanjutnya kita tambahkan 2 buah tombol seperti pada gambar diatas dengan nama CommandButton1 dan CommandButton1 kemudian kita atur propertiesnya, atur captionnya atau nama tampilannya menjadi :

CommandButton1 = Print
CommandButton2 = Preview

Kemudian klik kanan pada tombol Print/CommandButton1 selanjutnya ketikan koding dibawah ini

Dim i As Integer
Private Sub CommandButton1_Click()

mulai = Range(“Y6”).Value
Sampai = Range(“Y7”).Value
kali = Range(“Y8”).Value
i = mulai

Do While i <= Sampai

Worksheets(“KartuUjian”).Range(“D9”).Value = Worksheets(“DataSiswa”).Cells(5 + i, 2).Value

Worksheets(“DataSiswa”).PrintOut Copies:=kali

i = i + 3

Loop
End Sub

Kemudian klik kanan pada tombol Preview/CommandButton2 selanjutnya ketikan koding dibawah ini

Private Sub CommandButton2_Click()
Worksheets(“KartuUjian”).PrintPreview
End Sub

Selesai

Contoh file yang sudah jadi bisa sobat downloa pada link dibawah ini

Format Cetak Kartu Ujian Semester Siswa Rumus Excel

NB : Pada file tersebut sudah dibuat menggunakan named range dengan nama “DataSiswa”. Name Range yang bisa membantu ketika user ingin menambah atau edit range data sehingga cukup edit named range tersebut tanpa harus mengubah rumusnya satu-satu sehingga lebih praktis.

contohnya :

=VLOOKUP($D$9;DataSiswa;2)

pada Named range tersebut admin ambil range untuk cetak kartu ujian 50 siswa jika jml siswa/siswanya maka harus di edit/tambah Refers to nya , Untuk lebih jelasnya bisa baca pada artikel berikut ini