Membuat Aplikasi Pengolahan Nilai Sederhana Kurikulum 2013 dengan Excel Part 3

بسم الله الرحمن الرحيم

Alhamdulillah, pada kesempatan kali ini saya akan melanjutkan tulisan saya mengenai cara membuat Aplikasi Pengolahan Nilai Sederhana Kurikulum 2013 dengan Excel. Tentunya para pembaca sudah memahami tulisan saya sebelumnya, yaitu pada Part 1 dan Part 2.

Sheet "tema1"

Sebagaimana pada alur algoritma aplikasi telah digambarkan bahwa sheet "tema1" disamping sebagai INPUT, sekaligus juga sebagai PROSES. Penjelasan lebih detailnya lihat diagram di bawah ini:

1. Mengambil beberapa data dari sheet "data"

Langsung saja dibuka sheet "tema1". Masukkan formula (rumus) berikut pada cell yang telah ditentukan:

D3    ---> =": "&db_skh

D4    ---> =": "&db_kls

W3   ---> =": "&db_tp

W4   ---> =": "&db_smt

B25  ---> =db_ks

R25  ---> =db_gk

Lihatlah hasil dari pekerjaan Anda! Sangat mudah!

2. Mengambil nama siswa dari sheet "siswa" dengan menggunakan fungsi VLOOKUP

VLOOKUP merupakan salah satu fungsi pencarian dan referensi, saat Anda perlu menemukan berbagai hal dalam tabel atau rentang menurut baris. Misalnya, mencari nama siswa menurut nomor urutnya, atau menemukan Nomor Induk Siswa dengan mencari nomor urutnya.

Rahasia untuk VLOOKUP adalah untuk menata data Anda sehingga nilai yang Anda cari (nomor urutnya) berada di sebelah kiri dari nilai yang dikembalikan yang ingin Anda temukan (nama siswa).

Sintaks: VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Misalnya: =VLOOKUP(B8;tbl_siswa;4;FALSE)

Nama argumen	Deskripsi
lookup_value    (diperlukan)	Nilai yang ingin Anda cari. Nilai yang ingin Anda cari harus berada di kolom pertama rentang cell yang Anda tentukan di table-array . Misalnya, jika table-array membentang di cell sheet "siswa" B5:H14, maka lookup_value harus berada di kolom B. Lookup_value bisa berupa nilai atau referensi ke cell. Pada contoh di atas adalah B8.
table_array    (diperlukan)	Rentang cell yang akan di cari VLOOKUP untuk lookup_value dan nilai yang dikembalikan. Kolom pertama rentang cell harus memuat lookup_value (misalnya, B5:H14 atau define name tbl_siswa.) Rentang cell juga harus menyertakan nilai yang dikembalikan (misalnya, Nama Siswa) yang ingin Anda temukan. Pada contoh di atas adalah tbl_siswa.
col_index_num    (diperlukan)	Kolom angka (mulai dengan 1 untuk kolom table-array paling kiri) yang memuat nilai yang dikembalikan. Pada contoh di atas adalah kolom 4.
range_lookup   (opsional)	Nilai logika yang menetapkan apakah Anda ingin VLOOKUP menemukan hasil yang persis sama atau mendekati: TRUE mengasumsikan kolom pertama dalam tabel diurutkan secara numerik atau abjad, dan kemudian akan mencari nilai terdekat. Ini adalah metode default jika Anda tidak menentukannya. FALSE mencari nilai persis dalam kolom pertama. Pada contoh di atas digunakan FALSE.

Sebelum kita menggunakan fungsi VLOOKUP, maka lebih dulu kita membuat define name pada sheet "siswa". 
-. Buka sheet "siswa"
-. Blok cell mulai dari B5 sampai dengan H14.
-. Klik Tab Formula -------> Define Name
-. Pada kotak Name, beri nama tbl_siswa

Jika sudah selesai:
-. buka sheet "tema1"
-. pada cell C8 masukkan formula =VLOOKUP(B8;tbl_siswa;4;FALSE)
-. copy rumus hingga cell C17

-. Hasilnya lihat gambar berikut:

3. Proses pengolahan nilai menjadi "Nilai Rerata"

Kita asumsikan bahwa kita akan menginput nilai mata pelajaran aspek Pengetahuan (KI3) untuk beberapa KD yang terdapat pada Subtema 1, Subtema 2, dan Subtema 3. Masukkan nomor KD pada kolom KD dengan asumsi bahwa pada:
- Subtema 1 terdapat KD 3.1, 3.2, 3.3, 3.4
- Subtema 2 terdapat KD 3.2, 3.3, 3.4, 3.5
- Subtema 3 terdapat KD 3.1, 3.3, 3.4, 3.5
- Nilai Rerata masukkan KD 3.1, 3.2, 3.3, 3.4, 3.5
- Predikat/Huruf masukkan KD 3.1, 3.2, 3.3, 3.4, 3.5

Lihat gambar berikut:

Selanjutnya kita akan menghitung nilai rerata KD 3.1:
Pada tabel terdapat 2 KD 3.1 yaitu pada subtema 1 dan subtema 3. Sehingga untuk menghitung rerata KD 3.1 adalah:

Rerata 3.1 = 3.1 + 3.1 

                            2

Jika dibuat formula:

P8 ===> =SUM(D8;L8)/2

Maksudnya: Menjumlahkan cell D8 dan L8 dibagi 2

Karena dalam tabel terlalu banyak nilai rerata yang akan dihitung maka akan butuh sekian banyak model formula untuk setiap cell. Untuk menghindari kesalahan dalam membuat formula dan mengurangi banyaknya model formula, maka kita dapat membuat formula sebagai pengganti formula =SUM(D8;L8)/2 dengan menggunakan fungsi SUMIF (menjumlahkan dengan kriteria tertentu) dan COUNTIF (menghitung jumlah cell yang memenuhi kriteria).

SUM(D8;L8) diganti menjadi =SUMIF($D$7:$O$17;P$7;$D8:$O8)

Menjumlah nilai di rentang (range) D8:O8, di mana cell yang terkait di rentang (range) D7:O17 jika sama dengan kriteria P7

Pembagi 2 diganti menjadi  =COUNTIF($D$7:$O$7;P$7)

Menghitung jumlah cell pada rentang (range) D7:O17 jika memenuhi kriteria P7

Maka formula baku pada cell P8 adalah:

P8 ===> =SUMIF($D$7:$O$17;P$7;$D8:$O8)/COUNTIF($D$7:$O$7;P$7)

Untuk menghindari jika terjadi error pada penggunaan formula tersebut, maka kita gunakan fungsi IFERROR, sehingga menjadi....

P8 ===> =IFERROR(SUMIF($D$7:$O$17;P$7;$D8:$O8)/COUNTIF($D$7:$O$7;P$7);0)

Menjumlah nilai di range D8:O8 yang terkait dengan range D7:O17, jika sesuai dengan kriteria P7, dibagi dengan hitungan jumlah cell pada range D7:O17, jika sesuai dengan kriteria P7. Dan jika error maka bernilai 0

Walhasil, formula pada cell P8:

=SUM(D8;L8)/2 sama dengan
=SUMIF($D$7:$O$17;P$7;$D8:$O8)/COUNTIF($D$7:$O$7;P$7) sama dengan
=IFERROR(SUMIF($D$7:$O$17;P$7;$D8:$O8)/COUNTIF($D$7:$O$7;P$7);0)

Pada kesempatan kali ini kita gunakan formula ketiga pada cell P8.
Selanjutnya copy (sebagaimana cara mengcopy pada Part 2) formula pada P8 kearah kanan hingga T8. kemudian copy ke bawah hingga T17.

4. Proses pengolahan nilai menjadi "Predikat/Huruf"

Kita tentunya sudah memahami bahwa kurikulum 2013 memiliki konversi nilai dari angka ke huruf sebagai berikut:

Dari tabel konversi tersebut kita buat deret angka dan huruf sebagai berikut:

Angka	1	1,18	1,51	1,85	2,18	2,51	2,85	3,18	3,51	3,85
Huruf	D	D+	C-	C	C+	B-	B	B+	A-	A

Selanjutnya kita akan membuat formula dengan menggunakan fungsi LOOKUP (Vektor) dikombinasikan dengan IFERROR

U8 ===> =IFERROR(LOOKUP(P8;{1\1,18\1,51\1,85\2,18\2,51\2,85\3,18\3,51\3,85};
                 {"D"\"D+"\"C-"\"C"\"C+"\"B-"\"B"\"B+"\"A-"\"A"});"")


Copy U8 ke kanan hingga Y8, kemudian copy ke bawah hingga Y17.
Hasilnya sebagaimana gambar berikut:

Disamping menggunakan fungsi LOOKUP (Vektor) kita juga bisa menggunakan fungsi VLOOKUP seperti pada Part 2.
Alhamdulillah selesai untuk Part 3... 
Jika masih bingung dengan formula yang ada atau penjelasan bisa konfirmasi via komentar.
Selamat mencoba hingga berhasil!!!

Bersambung...

Anda baru saja membaca artikel yang berkategori Belajar Excel dengan judul Membuat Aplikasi Pengolahan Nilai Sederhana Kurikulum 2013 dengan Excel Part 3. Anda bisa bookmark halaman ini dengan URL http://educreativities.blogspot.com/2015/09/membuat-aplikasi-pengolahan-nilai_21.html. Terima kasih!

Ditulis oleh: SD PANCASILA 45 - Rabu, 02 September 2015