بسم الله الرحمن الرحيم
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).
Misalnya: =VLOOKUP(B8;tbl_siswa;4;FALSE)
Sintaks: VLOOKUP
(lookup_value, table_array, col_index_num, [range_lookup])
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:
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:
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...
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
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...
SD PANCASILA 45
Belajar Excel
