Berbagi Ilmu

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 :

Tips Sebelumnya :

About these ads

About omiyan

Cuman seorang newbie didunia maya, selalu merasa jika berbicara lewat tulisan itu lebih baik daripada kita berbicara tanpa ada jejak dalam sebuah tulisan.

Diskusi

23 thoughts on “Tips Microsoft Excel : Tips Membuat Keterangan “Hasil Ujian”

  1. Om.. ajari gambar dunk… *nyodorin krayon*

    *dilempar buku excel ama si Om*

    [maap pagi2 berkunjung dng comment OOT]

    Posted by Inge | 8 Maret 2010, 01:43
  2. ok om… oya tu bikin bullet numbering di wp bs ya? kl di blogspot tau ga?

    Posted by Erikson | 8 Maret 2010, 02:21
  3. Makasih Om…
    Cara I itu memang panjang, tapi komentar yg dihasilkan kebih luwes dan terkesan manusiawi.
    Benar juga, makin banyak ulangan, variasinya makin banyak pula.
    Kalau nggak salah ada 2^n dengan n = jumlah ulangan.
    Cara itulah yang saya gunakan selama ini.
    Saya juga nambah range tersendiri yg berisi semua komentar yg mungkin.
    Jadi semua gabungan cara itu saya pakai.
    Pada kolom yg isinya Y dan T itu saya isi angka 0 dan 1
    Jadi kalau ulangan 1 lulus sedang ulangan 2 dan 3 tidak, akan muncul kode 100
    Jika ulangan 1 & 3 lulus sedang yg 2 tidak, munculnya 101
    Kalau hanya 3 saja yg lulus munculnya 001.
    3 digit inilah yg nantinya jadi kunci komentar.
    Jadi, pada range kunci yg saya maksudkan tadi saya buat:
    000 ulangan 1, 2 dan 3 tidak lulus
    001 ulangan 1 dan 2 tidak lulus, ulangan 3 lulus
    010 ulangan 1 dan 3 tidak lulus, ulangan 2 lulus
    dst.
    Makasih responnya
    Saya masih akan selalu konsultasi terus menerus, semoga Omiyan nggak bosan
    Sambil terus berpikir tentang foto siswa yg muncul otomatis… :D

    Omiyan : Semoga saja bisa sedikit memuaskan hahahah

    Posted by marsudiyanto | 8 Maret 2010, 05:17
  4. Saya sangat mengerti betapa panjang waktu yg dibutuhkan untuk memposting ini, mana rumusnya pakai warna-warni lagi…
    Semua demi memuaskan pembaca.
    Sekali lagi saya salut pada Omiyan.

    Posted by marsudiyanto | 8 Maret 2010, 05:20
  5. Saat kebanyakan orang meremehkan Excel…
    Saat orang lain menganggap remeh kalau ada orang yg bisanya cuman Excel,
    Saat itulah ketauan siapa orang itu…
    Orang itu pasti nggak bisa Excel.

    Dan menurut saya…
    Sulitnya ngeblog, sulitnya optimasi SEO…
    Itu cuman 1% dibanding kerumitan Excel.

    APalagi Facebook.
    Tanpa meremehkan FB, rumitnya FB malah cuman 0,00001% dibanding Excel

    Posted by marsudiyanto | 8 Maret 2010, 05:24
  6. Tapi emang enak kok dibaca kalo rumusnya pake warna warni.
    puas deh…

    Posted by Decal sticker | 8 Maret 2010, 13:20
  7. wao …. akan kucoba rumusan ini!

    Posted by nuansa pena | 9 Maret 2010, 04:08
  8. Sebagai guru, isteriku sangat membutuhkan sekali! Trims telah berbagi rumusan ini!

    Posted by nuances pen | 9 Maret 2010, 04:09
  9. Untuk Office 2007 yaa ?

    bagaimana caranya merubah warna pada textnya,
    dengan 2 kondisi pda Office 2003

    Cobain aja dulu mas berhasil atau tidak, perhatiin tab antar office 2007 dan 2003, jika sama berarti rumus bisa dipakai

    Posted by DikMa | 5 Mei 2010, 01:26
  10. thanks moga warga indonesia sdm nya bertambah good

    Posted by ary pranata | 3 Juni 2010, 22:29
  11. saya mw nanya bagaimana cara mencari rumus if untuk menggabungkan satu kata dari sheet 1 ke sheet yang lain?

    Omiyan : ga pake Fungsi IF mas, tapi coba dengan contoh seperti ini : =Cell (kata pertama)&” “&Cell (Kata kedua)

    semoga sesuai yang diharapkan

    Posted by muhammad faisal | 11 Juni 2010, 02:41
  12. omiyan….salut deh atas bagi ilmu excelnya…terima kasih bro…semua sudah saya aplikasikan sendiri dengan merubah beberapa data inputnya….dan rata2 semua berhasil…yg tidak berhasil mgkn karena fungsi yg saya ketik ada yg berbeda…tapi ndak masalah yg jelas saya sudah mengerti maksudnya…
    Bonusnya ok…seperti yg saya cari selama ini…..
    Buat Omiyan dan Mas Marsudiyant….kalo ada info tentang cara masukin foto siswa otomatis…kabari saya ya di rrisnandar@yahoo.com

    Posted by RAHMAD | 17 Juni 2010, 07:30
  13. RAHMAD :

    Sama-sama pak, Alhamdulillah saya juga minta sarannya kalau terjadi salah dalam memasukkan rumus, kalau mau tahu buat postingan saya bisa nge-klik tab dibawah OnLIne By Email

    Posted by omiyan | 17 Juni 2010, 07:57
  14. thanks ilmunya.
    semoga menjadi berkat untuk anda dan kita semua. amin

    Posted by KENKEINA | 23 September 2010, 14:30
  15. matur nuwun yo Omiyan aku malah dadi bingung…..maklum wong ra donk…! ^_^

    Posted by jagaresa | 24 Desember 2010, 04:03
  16. siiiiiiiiipppppp deh…. salut buat om iyan, yg begini nih yg kucari, mudah diikuti dan difahami, langkah2nya step by step shg yg awam kayak aq bisa ngikutin… thx berat, moga Allah mbalas dg kebaikan yg berlimpah dan rejeki yang barokah…….

    Posted by ufay | 14 Maret 2011, 03:40
  17. Mas, gimana cara menampilkan nama-nama siswa yang tidak lulus ujian (berdasarkan data, baik di nilai ataupun dari keterangan LULUS/GAGAL)? tolong pencerahan mengenai rumus logika if-nya excel. Hatur nuhuun. Ditunggu di e-mailku

    Posted by ricki | 26 Maret 2011, 14:59
  18. exel susah tapi cm sekali j susahnyaaa,yang penting hasil akhir dan seeterusssnya,tinggal copy pasteeee

    Posted by caturdonig | 1 Desember 2011, 11:49
  19. hihihihi semua aplikasi EXCEL, ane cobain di OPENCALC … heheheh mantab bro keep,… thanks yah emang excel tuh sangat dasyat buat database … hanya orang orang yang ngga ngerti excel yang berani komentar “bisa nya cuma excel” ….

    Posted by aldo desatura | 15 Februari 2012, 10:39
  20. perasaan ane kok jai pusing ya mas liat nya, :D

    Posted by hasankomputer | 1 April 2013, 07:58
  21. KEREN NIH JDI NGERTI ARTI =IF(LOGICAL,TRUE,FALSE)

    Posted by ULANGAN UMUM | 7 Mei 2013, 23:14

Trackbacks/Pingbacks

  1. Ping-balik: Microsoft Excel : Memecah Suatu (Angka) Bilangan « Blognya Omiyan - 25 Maret 2010

Berikan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Logout / Ubah )

Twitter picture

You are commenting using your Twitter account. Logout / Ubah )

Facebook photo

You are commenting using your Facebook account. Logout / Ubah )

Google+ photo

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

twitter

  • 1,829,587 Sejak 17 Nov 2008

Kategori

Yang Lagi OL

Langganan via Email

Bergabunglah dengan 778 pengikut lainnya.

smadav antivirus indonesia

Ikuti

Get every new post delivered to your Inbox.

Bergabunglah dengan 778 pengikut lainnya.

%d blogger menyukai ini: