Tips Microsoft Excel : Tips Membuat Keterangan “Hasil Ujian”

marsudiyanto :

Saya punya program analisis nilai buat siswa. Kapan2 mau saya sharingkan pada Omiyan. Saya masih gunakan rumus bertele2, karena cuma seperti itu yg saya pahami. Gambarannya begini Om:

Ulangan 1 misalnya materi Logaritma, dengan batas lulus 65 Ulangan 2 tentang Persamaan, dengan batas lulus 70 Ulangan 3 tentang Grafik, dengan batas lulus 65.

…. Anak I Nilainya 70, 70 dan 60. Saya berharap ada komentar otomatis untuk Anak I sbb: Logaritma dan Persamaan lulus, Grafik tidak lulus”

Anak II Nilainya 90, 80, 80 lalu muncul komentar “Logaritma, Persamaan dan Grafik lulus”

Anak ke III nilainya 64, 70, 70 lalu muncul “Logaritma tidak lulus, Persamaan dan Grafik lulus”

Anak ke IV nilainya 80, 45, 90, komentarnya “Logaritma dan Grafik lulus, Persamaan tidak lulus”.

Saya sudah berhasil membuat formulanya, tapi puanjang………..

Komentarnya ada disini.

Bismillah…..

Saya mencoba menjawab permintaan tersebut diatas saya akuin sulit tapi ketika saya mencobanya saya mendapatkan 3 cara dimana satu cara persis yang diminta Pak Marsudi dan 2 cara dengan bentuk hampir sama tapi ada perbedaan dalam “nama” keterangan yang muncul nantinya.

Nah sekarang saya pake Cara I terlebih dahulu :

Cara ini akan menghasilkan jawaban yang hampir sama dengan yang diminta kalaupun ada perbedaan hanya di penempatan rumus untuk membacanya jadi rumus itu nantinya bisa didepan atau dibelakang.

Tapi kelemahannya rumus ini adalah terlalu panjang dan butuh kesabaran hehehe kalau salah dikit bisa bikin gregetan tapi disisi lain kita bisa menampilkan sebuah keterangan semau kita yang ingin ditampilkan.

Sekarang anggaplah yang data yang dimaksud tersebut seperti gambar dibawah ini :

Saran saya ikutin penempatan Cellnya seperti diatas.

Jika sudah sekarang yang kita butuhkan adalah bagaimana caranya agar kita mengetahui dari tiap anak tersebut mana pelajaran yang Lulus dan yang Tidak Lulus, dan rumusnya adalah sebagai berikut :

=IF(AND(B4>=65,C4>=70,D4>65),($B$3&”, “&$C$3&” dan “&$D$3&” Lulus”),IF(AND(B4>=65,C4>=70,D4<=65),($B$3&” dan “&$C$3&” Lulus, “&$D$3&” Tidak Lulus”),IF(AND(B4<=65,C4>=70,D4>=65),($B$3&” Tidak Lulus, “&$C$3&” dan “&$D$3&” Lulus”),IF(AND(C4<70,B4>=65,D4>=65),($B$3&” dan “&$D$3&” Lulus, “&$C$3&” Tidak Lulus”),IF(AND(B4<65,C4<70,D4<65),($B$3&”, “&$C$3&” dan “&$D$3&” Tidak Lulus”),IF(AND(B4>=65,C4<70,D4>=65),($B$3&” Lulus, “&$C$3&” dan “&$D$3&” Tidak Lulus”),IF(AND(B4<65,C4>=70,D4<65),($B$3&” dan “&$D$3&” Tidak Lulus, “&$C$3&” Lulus”),IF(AND(B4<65,C4<70,D4>=65),($B$3&” dan “&$C$3&” Tidak Lulus, “&$D$3&” Lulus”)))))))))

Rumus ini harus mengikuti setiap perubahan yang terjadi dengan begitu rumus yang kita tulis menjadi panjang, untuk sisanya tinggal kita Copy Paste saja. Dan jika benar dalam penulisannya maka hasilnya adalah akan seperti ini :

Sekali lagi rumus ini akan panjang lagi jika bertambahnya mata pelajaran yang diujikan.

Oke sekarang kita masuk ke Cara II, dengan soal yang sama dan akan muncul sebuah keterangan tentang hasil ujian yang Lulus dan Tidak Lulus.

Cara ini rumusnya akan lebih pendek dari rumus Cara pertama tapi akan terkesan kaku karena keterangan yang dihasilkan memunculkan lebih dari satu kata yang sama dalam satu kalimat yaitu kata Lulus dan kata Tidak Lulus.

Dan rumusnya yang kita ketikk di Cell E4 adalah seperti ini :

=IF(AND(B13>=65),($B$3&” Lulus”&”, “),($B$3&” Tidak Lulus”&”, “))&IF(AND(C13>=70),($C$3&” Lulus”&”, “),($C$3&” Tidak Lulus”&”, “))&IF(AND(D13>=65),($D$3&” Lulus”),($D$3&” Tidak Lulus”))

Dalam rumus ini untuk spasi kita tempatin sebelum pernyataan Lulus dan Tidak Lulus, coba perhatiin diantara tanda kutip yaitu ” Lulus” ingat bukan “Lulus” (semoga cukup jelas) dan untuk sisanya tinggal kita Copy Paste saja. Jika benar maka hasilnya akan seperti ini :

Jika diperhatikan setiap pelajaran yang lulus akan selalu muncul kata lulus berbeda dengan Cara I untuk yang lulus maka kata Lulus-nya hanya muncul sekali, tapi hal ini tidak kita permasalahkan
karena yang kita butuhkan adalah informasi pelajaran mana yang Lulus dan Tidak.

Jika sudah paham maka saya kita membuat Cara III, cara ini sedikit menambah tabel sebelumnya tidak seperti Cara I dan Cara II yang cukup dengan dengan Tabel Keterangan Lulus, di sini kita menambah 3 tabel persis dibawah Tabel Keterangan Lulus, yaitu dengan mengetikkan nama pelajaran yang diujikan yaitu Logaritma (Cell E3), Persamaan (Cell F3) dan Grafik (Cell G3) ditambah tabel berikutnya di Cell H2 kita membuat Tabel Jumlah
Pelajaran dan persis dibawahnya yaitu Cell H3 kita ketik Lulus dan Cell I3 kita ketik Tidak.

Rumus yang kita ketik untuk mata pelajaran Logaritma yaitu tepatnya di Cell E4 yaitu :

=IF(E4<65,”T”,IF(E4>=65,”Y”))

Artinya jika nilai yang ada di Cell B22 tersebut kurang dari 65 maka hasilnya adalah Tidak atau tanda T, dan jika lebih maka hasilnya Lulus atau tanda Y, dan sisanya kebawah tinggal anda Copy Paste saja.

Rumus selanjutnya yang kita ketik untuk mata pelajaran Persamaan yaitu tepatnya di Cell F4 yaitu :

=IF(F4<70,”T”,IF(F4>=70,”Y”))

Untuk sisanya kebawah tinggal anda Copy Paste saja.

Rumus selanjutnya yang kita ketik untuk mata pelajaran Grafik yaitu tepatnya di Cell G4 yaitu :

=IF(G4<65,”T”,IF(G4>=65,”Y”))

Untuk sisanya kebawah tinggal anda Copy Paste saja.

Jika ketiga rumus tersebut sudah anda ketik dan Copy kebawahnya maka hasilnya akan muncul mana pelajaran yang Lulus dan Tidak Lulus, tapi jika muncu tulisan FALSE berarti ada yang salah atau kurang.

Sekarang kita masuk ke Tabel Jumlah Pelajaran, Tabel ini saya buat yang berfungsi untuk mengetahui secara matematis berapa total pelajaran dari tiap siswa yang berhasil lulus dan tidak dengan kata lain Tabel ini berfungsi untuk mengsikronkan dengan Tabel sebelumnya yaitu Tabel Keterangan Lulus, anda akan melihat jika dari 3 pelajaran yang diikuti seorang siswa berhasil semua artinya tiap pelajaran muncul kode Y maka jumlahnya pasti 3.

Dan ini adalah rumusnya yang anda ketik di Cell H4, yaitu

=COUNTIF($E4:$G4,”Y”)

Artinya kita mencari dan menjumlahkan dari Cell E4 sampai dengan G4 yang berkode Y, adapun tanda dollar ($) didepan E dan G adalah untuk mengunci agar rumus tersebut nantinya ketika kita Copy Paste terutama kesamping tidak terjadi pergeseran atau perubahan data Cell yang diambil.

Jadi rumus tersebut tinggal anda Copy paste tepat dibawah Tidak atau Cell I4.

Jika kita benar maka sudah dipastikan akan muncul total angka untuk setiap kode dari hasil pelajaran yang diujikan. Dan itu akan persis seperti gambar berikut ini :

Nah mungkin dalam benak kita bertanya terus di Cell B9 kok ada tulisan Lihat Data dan fungsinya buat apa …….???????

Anggaplah ini bonus buat anda untuk tips kali ini hehehehehe, ini nantinya berfungsi untuk mengetahui Pelajaran mana saja ynag lulus dan tidak dengan kita berikan tanda tertentu bisa fontnya menjadi besar atau dicetak tebal atau tanda lainnya. dan caranya adalah sebagai berikut :

1. Kita blok area yang berisi kode Y dan T tersebut yaitu Cell E4 sampai dengan G7, kemudian Klik Tab Conditional Formatting lihat (gambar 1)
2. Setelah itu kita pilih New Rule kemudian pilih Type Rule : Format only cells that contant dan jangan lupa perintah Between kita ganti dengan Equal to dan ditanda kota untuk browse anda pilih Cell C9, karena Cell ini nantinya untuk mengetikkan kode Y dan T.
3. Untuk membedakan hasil yang kita maksud sekarang kita klik Format disini kita tinggal milih nanti hasilnya seperti apa, apakah tebal atau diberi warna dasar hitam, adapun cara yang saya lakukan adalah dengan memilih type Font atau Font Style yaitu Bold serta Color Font nya yaitu merah serta BG atau Background color warna hitam,dan jika sudah kita tinggal tekan OK.

Sekarang tinggal kita praktekin jika setiap kita memasukkan kode Y di Cell C9, maka daerah Cell E4 sampai dengan G7 khususnya hasil yang berkode Y akan berubah dengan warna dasar Hitam dengan font yang tercetak tebal dengan warna merah.

Semoga berhasil.

Jika masih bingung ya udah nih tinggal download caranya ada disini.

Salam

Sumber bacaan :

• Mengungkap Kedahsyatan Fungsi IF karangan Johar Arifin. PT. Elex Media Komputindo
• 25 Aplikasi Bisni Excel 2007 . Maxikom
• Mempermudah dan Mempercepat Pekerjaan Menggunakan Formula Excel 2007. Media Kita
• Microsoft Excel 2007 Membangun Rumus dan Fungsi. andipublisher

Tips Sebelumnya :

• Microsoft Excel : Tips Menghitung “PPh Terutang” Dalam SPT Tahunan
• Microsoft Excel : Membuat Aplikasi Sederhana “Pengeluaran Per Bulan”
• Microsoft Excel : Membuat Aplikasi Sederhana “Kapan Kamu Mengenal Cinta”
• Microsoft Excel : Tips Membaca Angka Dalam Bentuk Teks (Kalimat)
• Mengenal Mutasi Habis SPPT PBB
• Microsoft Excel : Membuat Data Base Sederhana Dan Tabel Pencarian Data
• Microsoft Excel : Menggabungkan Data Kota Dan Tanggal Lahir
• Microsoft Excel : Tips Menggabungkan Dua Kata Dari Dua Kolom
• Microsoft Excel : Tips Menambah dan Menghilangkan Tanda Baca