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...

Selengkapnya »

2 Comments

SD PANCASILA 45 Belajar Excel

Membuat Aplikasi Pengolahan Nilai Sederhana Kurikulum 2013 dengan Excel Part 2

Bismillahirrohmaanirrohiim

Menggunakan Fungsi IF

Fungsi IF adalah fungsi logika dalam Microsoft Excel yang sering digunakan karena kemudahan penggunaannya untuk menyelesaikan pernyataan yang berhubungan dengan Logika, untuk mengembalikan satu nilai jika kondisi true dan nilai lain jika kondisi false. Untuk memahami tentang fungsi logika IF, maka akan saya jelaskan secara sederhana.

Sintaks:  IF(logical_test, value_if_true, [value_if_false])

Nama argumen	Deskripsi	Contoh
logical_test     (diperlukan)	Kondisi yang ingin Anda uji.	Sakit
value_if_true  (diperlukan)	Nilai yang ingin Anda kembalikan jika hasil logical_test adalah TRUE	Berobat
value_if_false  (opsional)	Nilai yang ingin Anda kembalikan jika hasil logical_test adalah FALSE	Tidak berobat

Misalnya: =IF(E5="";"";1)

Maksudnya: Jika cell E5 sama dengan kosong (""), maka kosong ("") dan jika tidak, maka mempunyai nilai 1.

Sekarang buka sheet "siswa". Pada cell B5 masukkan rumus =IF(E5="";"";1). Hasilnya adalah angka 1. Coba hapuslah teks yang ada pada cell E5, maka pada cell B5 akan kosong.

Menggunakan Fungsi COUNT

Fungsi COUNT menghitung jumlah sel yang berisi angka, dan menghitung angka dalam daftar argumen. Gunakan fungsi COUNT untuk mendapatkan jumlah entri dalam bidang angka yang berada dalam rentang atau array angka. Misalnya, Anda dapat memasukkan rumus berikut untuk menghitung angka dalam rentang A1:A20, =COUNT(A1:A20). Dalam contoh ini, jika lima dari sel-sel dalam rentang tersebut berisi angka, hasilnya adalah 5.

Sintaks: COUNT(value1, [value2], ...)

Sintaks fungsi COUNT memiliki argumen berikut:

value1 : Diperlukan. Item pertama, referensi sel, atau rentang yang ingin Anda hitung angkanya.

value2, ... : Opsional. Hingga 255 item tambahan, referensi sel, atau rentang yang ingin Anda hitung angkanya.

CATATAN  Argumen dapat berisi atau merujuk ke beragam tipe data berbeda, tetapi hanya angka yang dihitung.

Sekarang masukkkan rumus =COUNT($B$5:B5) pada cell B6, maka akan menghasilkan angka 1.
Maksudnya: Menghitung jumlah cell yang berisi angka dalam rentang mulai B5 hingga B5 (B5:B5).

Selanjutnya copy rumus pada dari cell B6 hingga B14 dengan cara arahkan kursor mouse ke sudut kanan bawah dari cell B6 hingga muncul tanda plus (+). Kemudian klik kiri tahan. Selanjutnya tarik ke bawah sampai dengan cell B14. 

Hasilnya kita dapatkan rumus sebagaimana tabel berikut:

Hasil dari penggunaan rumus tersebut secara berurutan mulai dari B6 sampai B14 adalah angka 1, 2, 3, 4, 5, 6, 7, 8, 9.
Karena yang kita harapkan pada kolom tersebut adalah nomor urut 1 sampai dengan 10, maka rumusnya harus kita ubah:

=COUNT($B$5:B5) diubah menjadi =COUNT($B$5:B5)+1

maka akan kita dapatkan nomor urut mulai 1 hingga 10 pada cell B5 hingga B14.

Menggabungkan Fungsi IF dan COUNT dalam satu rumus

Kita sudah belajar tentang fungsi IF dan COUNT. Bagaimana jadinya, jika kedua fungsi tersebut digabungkan menjadi satu dalam sebuah rumus.

=IF(E5="";"";1) digabungkan dengan =COUNT($B$5:B5)+1

Karena yang kita jadikan target dari rumus adalah cell B6 maka E5 pada rumus pertama diubah menjadi E6 ----> hasilnya: 

=IF(E6="";"";COUNT($B$5:B5)+1)

"Jika E6 sama dengan kosong (""), maka E6 kosong (""). Dan jika tidak kosong, maka menghitung jumlah cell yang berisi angka dalam rentang mulai B5 hingga B5 (B5:B5) ditambah (+) 1" 

Copy rumus tersebut dan tempel (paste) pada cell B6 kemudian copy hingga B14 seperti cara yang sudah kita bahas diatas. Lihatlah hasilnya! Luar biasa!!!

Semoga bermanfaat!!!

Bersambung.....

Selengkapnya »

Komentari

SD PANCASILA 45 Belajar Excel