tips microsoft excel

16
ips Microsoft Excel : Menampilkan Nama Siswa Dan Nilai Hasil Ujian (Cara I) 8 Juni 2010 omiyan Tinggalkan komentar Go to comments Rate This Postingan ini merupakan jawaban atas pertanyaan Pak Budi, tentang bagaimana memunculkan nama siswa dan nama wali kelas jika kita mengetikkan nama kelasnya. Pertanyaan yang sangat sulit tapi semoga saja jawaban yang saya buat setidaknya mampu memuaskan pertanyaan tersebut. Sebetulnya banyak cara yang bisa dilakukan dan saya berharap ada jawaban atau komentar dari sahabat lainnya yang bisa membantu sebagai cara lain dalam penyelesaiannya, yang saya uraikan disini adalah cara yang saya temukan tentunya versi saya dan semoga aja bisa jadi bahan referensi nantinya. Kenapa judul diatas ada akhiran cara I, karena cara ini akan bisa dikerjakan dengan 2 (dua) cara (versi saya) tergantung dari Model Data Base yang tersedia nantinya, jadi saya coba dulu cara pertama dengan begitu ada beberapa alternatif dalam menyelesaikannya. Seandainya data yang tersedia sebelumnya seperti contoh dibawah ini (buat di Sheet 1): Cel l A B C D E F G H 1 No NIS Nama Kela s IPA IPS MATEMATIKA BAHASA 2 1 10112 Aang A3 8.00 8.00 7.00 8.00 3 2 10113 Adinda A3 8.00 8.00 6.00 9.00 4 3 10114 Agung A3 7.50 8.00 6.50 7.00 5 4 10115 Agus Subagja A3 8.50 8.00 9.00 6.00 6 5 10116 Anita A3 9.00 9.00 9.50 8.00 7 6 10117 Aryuni A3 9.30 9.00 9.50 8.00 8 7 10118 Bembi A3 6.00 7.50 8.00 9.00

Upload: rizky-afandi

Post on 03-Jul-2015

170 views

Category:

Documents


3 download

TRANSCRIPT

Page 1: Tips Microsoft Excel

ips Microsoft Excel : Menampilkan Nama Siswa Dan Nilai Hasil Ujian (Cara I)

8 Juni 2010 omiyan Tinggalkan komentar Go to comments Rate This

Postingan ini merupakan jawaban atas pertanyaan Pak Budi, tentang bagaimana memunculkan nama siswa dan nama wali kelas jika kita mengetikkan nama kelasnya.

Pertanyaan yang sangat sulit tapi semoga saja jawaban yang saya buat setidaknya mampu memuaskan pertanyaan tersebut.

Sebetulnya banyak cara yang bisa dilakukan dan saya berharap ada jawaban atau komentar dari sahabat lainnya yang bisa membantu sebagai cara lain dalam penyelesaiannya, yang saya uraikan disini adalah cara yang saya temukan tentunya versi saya dan semoga aja bisa jadi bahan referensi nantinya.

Kenapa judul diatas ada akhiran cara I, karena cara ini akan bisa dikerjakan dengan 2 (dua) cara (versi saya) tergantung dari Model Data Base yang tersedia nantinya, jadi saya coba dulu cara pertama dengan begitu ada beberapa alternatif dalam menyelesaikannya.

Seandainya data yang tersedia sebelumnya seperti contoh dibawah ini (buat di Sheet 1):

Cell A B C D E F G H1 No NIS Nama Kelas IPA IPS MATEMATIKA BAHASA2 1 10112 Aang A3 8.00 8.00 7.00 8.003 2 10113 Adinda A3 8.00 8.00 6.00 9.004 3 10114 Agung A3 7.50 8.00 6.50 7.005 4 10115 Agus Subagja A3 8.50 8.00 9.00 6.006 5 10116 Anita A3 9.00 9.00 9.50 8.007 6 10117 Aryuni A3 9.30 9.00 9.50 8.008 7 10118 Bembi A3 6.00 7.50 8.00 9.009 8 10119 Budi rahardi A3 6.30 7.00 7.50 9.0010 9 10120 Cecep A3 6.50 7.00 7.90 9.0011 10 10121 Chintya A3 7.00 9.50 9.00 9.0012 11 10122 Deden B3 7.00 9.00 8.50 8.0013 12 10123 Dodo B3 7.50 7.50 8.60 8.0014 13 10124 Farrel B3 7.80 8.00 8.80 8.0015 14 10125 Fenia B3 8.10 8.50 9.00 9.0016 15 10126 Karim B3 8.30 9.00 9.30 9.0017 16 10127 Luna B3 8.00 8.50 9.00 9.0018 17 10128 Maya B3 8.50 8.50 7.00 8.00

Page 2: Tips Microsoft Excel

19 18 10129 Misca B3 8.60 9.00 8.20 8.5020 19 10130 Nazwan B3 9.00 9.50 9.00 9.0021 20 10131 Nunung B3 9.00 9.60 9.50 9.0022 21 10132 Nur C3 9.00 9.00 8.80 9.0023 22 10133 Nurohma C3 9.50 9.00 9.00 9.0024 23 10134 Oding C3 9.70 9.00 9.40 9.0025 24 10135 Opick C3 8.00 8.50 7.50 8.0026 25 10136 Pardiyanto C3 8.30 8.50 7.60 8.0027 26 10137 Risma C3 8.80 9.00 7.80 8.0028 27 10138 Rustam C3 6.75 7.90 6.00 7.8029 28 10139 Santika C3 7.25 8.40 6.00 7.9030 29 10140 Vito C3 8.30 9.00 6.50 7.5031 30 10141 Yanti C3 9.00 9.50 7.00 9.00

Data diatas kita andaikan jumlah siswa tiap kelas adalah sama (contoh disini 10 siswa per kelas) Kemudian kita buka Sheet 2 dan buatlah data seperti berikut ini :

Cell A B1 Kelas Wali Kelas2 A3 Bapak Effendi Rahman3 B3 Ibu Ratnaningsih

4 C3 Bapak Abdullah Hadi

Jika sudah buka lagi Sheet 3 dan buat data persis dibawah ini :

Cell A B C D E F G H I1 Nama Kelas

2Nama Wali Kelas

345

No NIS Nama SiswaNilai Hasil Ujian RATA-

RATAKETERANGA

N6 IPA IPS

MATEMATIKA

BAHASA

7 18 29 3

10 411 512 613 714 815 916 10

Page 3: Tips Microsoft Excel

17 Nilai Rata-rata

Mohon diperhatikan Cell baik kolom maupun baris, ini penting untuk mengingat rumus nantinya.

Disini yang kita inginkan adalah setiap kita mengetikkan nama kelas misalkan A3 atau B3 atau C3 maka data yang diharapkan munculs ecara otomatis adalah

1. Nama Wali Kelas2. Nomor Induk Siswa3. Nama Siswa4. Nilai Hasil Ujian Setiap Mata Pelajaran5. Nilai Rata-Rata6. Dan Keterangan Hasil Ujian tersebut.

Yuk kita bahas satu persatu-satu (ingat kita sekarang bekerja di Sheet 3!)

1. Memunculkan Nama Wali kelas (Cell D2)

Rumus yang dihadirkan adalah :

=VLOOKUP($D$1,Sheet2!$A$2:$B$4,2,FALSE)

Artinya dengan kita mengetikkan Nama Kelas (misalkan A3, B3 atau C3) di Cell D1, maka kita melihat (membandingkan) data yang sama (nama kelas) yang berada di Sheet 2 dan data yang diharapkan muncul adalah nama wali kelas (perhatikan range pengambilan data di Sheet 2).

2. Nomor Induk Siswa Muncul Otomatis (Cell B7)

Rumus yang dihadirkan adalah :

=IF($D$1=”A3“,Sheet1!B2,IF($D$1=”B3“,Sheet1!B12,IF($D$1=”C3“,Sheet1!B22)))

Artinya, ketika kita mengetikkan kelas A3 maka NIS yang muncul di Cell B7 adalah NIS yang ada di Sheet1 dimulai Cell B2 tapi ketika kita mengetikan kelas B3 maka NIS yang muncul adalah NIS yang ada di Sheet1 dimulai Cell B12 tapi ketika kita mengetikan kelas C3 maka NIS yang muncul adalah NIS yang ada di Sheet1 dimulai Cell B22.

Untuk Cell B8 kita tinggal ketikkan rumus =Cell B7+1, copy atau sorot sampai Cell B16

3. Nama Siswa Muncul Otomatis (Cell C7)

Pekerjaan ini menjadi mudah karena data yang ada di Cell B7 sudah terbuka, disini cara yang kita lakukan adalah dengan membandingkan data yang muncul di Cell B7 dengan

Page 4: Tips Microsoft Excel

melihat data yang ada di Sheet 1 atau dengan menggunakan Fungsi Vlookup, jadi rumusnya adalah :

=VLOOKUP(B7,Sheet1!$B$2:$C$31,2,FALSE)

Sisanya untuk Cell C8 sampai Cell C16 tinggal copy saja rumusnya.

4. Nilai hasil Ujian setiap Pelajaran

Asumsi atau anggapan yang kita harapkan disini adalah seandainya mata pelajaran itu IPA, berapa nilai dari Siswa dimaksud nah disini mata pelajaran tersebut yaitu IPA terletak di Cell D6, IPS ada di Cell E6, Matematika ada di Cell F6, dan mata pelajaran Bahasa ada di Cell G6, maka rumus yang kita dapatkan adalah :

Cell D7

=IF($D$6=”IPA”,VLOOKUP($B7,Sheet1!$B$2:$H$31,4,FALSE))

Cell E7

=IF($E$6=”IPS”,VLOOKUP($B7,Sheet1!$B$2:$H$31,5,FALSE))

Cell F7

=IF($F$6=”MATEMATIKA”,VLOOKUP($B7,Sheet1!$B$2:$H$31,6,FALSE))

Cell G7

=IF($G$6=”BAHASA”,VLOOKUP($B7,Sheet1!$B$2:$H$31,7,FALSE))

Kelemahan rumus diatas yaitu tidak bersifat dinamis karena ketika nama mata pelajarannya kita rubah atau posisinya berubah satu sama lain maka sudah pasti akan menjadi error atau data menjadi Blank (#NA) maka mau tidak mau kita harus merubah rumusnya menyesuaikan perubahan yang ada, maka untuk mengakali hal tersebut terjadi maka keempat rumus tersebut kita gabung menjadi satu yaitu :

=IF(D$6=”IPA”,VLOOKUP(Sheet3!$B7,Sheet1!$B$2:$H$31,4,FALSE),

IF(D$6=”IPS”,VLOOKUP(Sheet3!$B7,Sheet1!$B$2:$H$31,5,FALSE),

IF(D$6=”MATEMATIKA”,VLOOKUP(Sheet3!$B7,Sheet1!$B$2:$H$31,6,FALSE),

IF(D$6=”BAHASA”,VLOOKUP(Sheet3!$B7,Sheet1!$B$2:$H$31,7,FALSE)))))

Kelebihan penggabungan rumus diatas adalah ketika kita mengubah nama mata pelajarannya yang tadinya misalkan di Cell D6 adalah IPA kita gantikan dengan IPS atau

Page 5: Tips Microsoft Excel

Matematika atau Bahasa maka secara otomatis nilai di Cell D7 tersebut akan juga berubah atau Dinamis.

5. Nilai Rata-Rata

Disini cukup mudah kita tinggal menjumlahkan nilai-nilai tersebut kemudian kita bagi dengan jumlah mata pelajaran yang ada, jadi rumusnya di Cell H7 adalah :

=SUM(D7:G7)/4

Sisanya untuk Cell H8 sampai dengan Cell H16 tinggal anda copy saja.

6. Keterangan

Rasanya ketika kita melihat sebuah data yang berisi sebuah hasil dari suatu ujian atau berisi tentang nilai rasanya hambar atau kurang afdol jika tidak disertai dengan sebuah keterangan yang bertujuan untuk lebih menjelaskan kondisi dari hasil nilai tersebut.

Disini kita harus mempunyai argumen tentang batasan dari suatu nilai yang diraih, dan argumen tersebut adalah :

Jika nilai tersebut kurang dari atau sama dengan 5 maka keterangan yang muncul adalah Kurang Sekali

Jika nilai tersebut kurang dari atau sama dengan 6 maka keterangan yang muncul adalah Kurang

Jika nilai tersebut kurang dari atau sama dengan 7 maka keterangan yang muncul adalah Cukup

Jika nilai tersebut kurang dari atau sama dengan 8.5 maka keterangan yang muncul adalah Baik

Jika nilai tersebut Lebih dari  8.5 maka keterangan yang muncul adalah Baik Sekali

Nah maka rumus yang kita buat di Cell I7 adalah :

=IF(H7<=5,”Kurang Sekali“,IF(H7<=6,”Kurang“,IF(H7<=7,”Cukup“,IF(H7<=8.5,”Baik“,IF(H7>8.5,”Baik Sekali“)))))

Sekarang coba dan praktekkan semoga berhasil…

Dan sebagai bonus, rasanya sebuah laporan tak pantas atau tak lengkap tanpa tanda tangan dari masing-masing wali kelas dan caranya adalah coba ketikkan rumus dibawah ini di Cell G20 :

=”Serang,”&” “&TEXT(TODAY(),”dd-mmm-yyy”)

Page 6: Tips Microsoft Excel

Kemudian di Cell G21 coba anda ketikkan :

=”Wali Kelas”&”(spasi)“&D1

Dan di Cell G25 coba anda ketikkan :

=D2

Dan hasilnya …… Cobain aja sendiri dijamin penasaran heheheh dan penasaran bentuknya gimana silahkan unduh aplikasinya disini Via rapidshare

Salam

——————————————-00000000000——————————————-

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

——————————————-00000000000——————————————-

Page 7: Tips Microsoft Excel

Tips Microsoft Excel : Menampilkan Nama Siswa Dan Nilai Hasil Ujian (Cara II)

18 Juni 2010 omiyan Tinggalkan komentar Go to comments Rate This

Setelah sukses memposting Tips Microsoft Excel (busyeettt deh hehehehehe boong itu mah boong ….) dengan judul Microsoft Excel : Menampilkan Nama Siswa Dan Nilai Hasil Ujian (Cara   I) , maka kali ini merupakan cara kedua dalam menampilkan data dimaksud.

Cara kedua ini saya buat seandainya Data Base yang ada berbeda dengan Cara I, setidaknya bisa menjadi alternatif dalam pengerjaan nantinya

Page 8: Tips Microsoft Excel

Ok tanpa menunggu kalimat panjang yuk kita mulai. Eiiittt jangan lupa makan dan minum ya hehehe.

Sekarang Buka Worksheet Excel dan di Sheet 1 buatlah data-data seperti ini :

Cell A1 —–> No Cell B1 —–> NIS Cell C1 —–> Nama Cell D1 —–> Kelas Cell E1 —–> Pelajaran Cell F1 —–> Nilai

Sekarang masukkan data-data tentang siswa disesuaikan dengan Cell masing-masing ( Isi Data Base sama dengan Cara I), tapi disini perbedaannya adalah NIS, Nama Siswa, Kelas akan muncul berulang-ulang dikarenakan mata pelajaran yang sebelumnya dalam Cara I berada di Kolom yang berbeda sekarang menjadi satu kolom (urut kebawah) dalam kolom Pelajaran (Cell E1) begitupun dengan nilainya disesuaikan dengan mata pelajarannya (Mohon dipahami dengan benar-benar).

Saya anggap semua sudah ngerti dengan yang saya maksud atau jika masih bingung saya kasih ilustrasinya (Gambar 1) dibawah ini.

Gambar 1

Page 9: Tips Microsoft Excel

Bisa dibayangkan maksud saya, nah Sheet 1 ini kita rename menjadi DBHasil Ujian dan sekarang buka Sheet 2 dan buatlah Data yang berisi nama siswa kelas A3, caranya yaitu seperti ini :

Gambar 2

Jangan lupa rename menjadi Kelas A3, sekarang buka sheet 3 dan buat data persis Gambar 2 yang berisi siswa Kelas B3, yaitu :

Gambar 3

Dan jangan lupa juga rename Sheet 3 menjadi Kelas B3, selanjutnya kita buat daftar siswa kelas C3 di Sheet 4 (rename menjadi Kelas C3).

Page 10: Tips Microsoft Excel

Gambar 4

Selanjutnya kita buat Data yang berisi nama-nama wali kelas, buat di Sheet 5 yang kemudian kita rename menjadi Wali Kelas, lihat gambar 5 dibawah ini:

Gambar 5

Dan terakhir di Sheet 6 (selanjutnya kita rename menjadi Aplikasi Data Ujian), caranya sama dengan Cara I atau saya ingatkan kembali bentuknya yaitu seperti (gambar 6) dibawah ini :

Page 11: Tips Microsoft Excel

Gambar 6

Ok Beres deh …. makasih ya hehehehee…   nah kita sudah setengah jalan so tanggung kan kalau ga diberesin, tapi saran saya pahami dulu baik-baik atau ga buka lagi deh tulisan saya sebelumnya yang berhubungan dengan cara II ini yaitu Microsoft Excel : Menampilkan Nama Siswa Dan Nilai Hasil Ujian (Cara   I).

Ok kita mulai memasukkan rumus-rumusnya :

Untuk Memunculkan Nama Wali Kelas sama persis dengan Cara I.

Kita berlanjut ke cara menampilkan Nama Induk Siswa atau NIS, disini kita memanfaatkan NO persis yang berada dibilah kiri NIS, Fungsi yang kita gunakan adalah Fungsi Vlookup karena disini kita akan melihat data yang sama yang berada di No (nomor 1,2,…..) dengan No yang berada dimasing-masing Kelas dengan tujuan memunculkan NIS yang diinginkan, dan rumusnya adalah :

=IF($D$1=”A3“,VLOOKUP($A7,’Kelas A3‘!A2:C11,2,FALSE),IF($D$1=”B3“,VLOOKUP($A7,’Kelas B3‘!$A$2:$C$11,2,FALSE),IF($D$1=”C3“,VLOOKUP($A7,’Kelas C3‘!$A$2:$C$12,2,FALSE))))

Artinya jika di Cell D1 atau Nama Kelas kita mengetikkan salah satu kelas yang dimaksud baik A3 atau B3 ataupun C3 maka data yang disandingkan adalah A7 atau no 1 dengan data di kelas masing-masing dengan data yang sama (no 1 juga) dengan range 2 walaupun data yang disorot sampai di Cell C12 dimaksudkan memunculkan NIS.

Sisanya tinggal ada copy paste Cell B8 s.d Cell B16.

Paham …. kalau ga paham … sama hahahahaha.

Page 12: Tips Microsoft Excel

Sip jika sudah paham kita berlanjut ke menampilkan Nama Siswa, rumusnya sama persis dengan cara diatas yang kita rubah adalah Range data yang diambil dimana yang sebelumnya bernilai 2 sekarang coba ganti dengan 3, perhatikan rumusnya :

=IF($D$1=”A3“,VLOOKUP($A7,’Kelas A3‘!A2:C11,3,FALSE),IF($D$1=”B3“,VLOOKUP($A7,’Kelas B3‘!$A$2:$C$11,3,FALSE),IF($D$1=”C3“,VLOOKUP($A7,’Kelas C3‘!$A$2:$C$12,3,FALSE))))

Sama tapi karena Range yang diambil adalah 3 maka yang dimunculkan adalah Nama Siswa dimaksud sesuai dengan NIS nya.

Sisanya tinggal Copy paste di Cell C8 s.d Cell C16

Yang berbeda antara Cara I dengan cara kedua adalah rumus yang digunakan dalam mengambil nilai yang dimaksud dalam masing-masing pelajaran, jika dalam cara sebelumnya kita menggunakan Fungsi IF dan Fungsi VLOOKUP maka dicara kedua ini kita menggunakan Fungsi SUMIFS.

Fungsi SUMIFS adalah sebuah fungsi yang digunakan untuk menjumlahkan data atau nilai numerik yang memenuhi lebih dari satu kriteria (perhatikan akhiran S dibelakang F).

Data yang akan kita (isitilah saya) diadukan atau diuji persamaannya adalah NIS, Nama Mata Pelajaran dan Nama Kelas yang tertera di  Aplikasi ini dengan NIS, Nama Mata Pelajaran dan Nama Kelas yang tertera di DBHasil Ujian…..pahami dulu argumen ini.

Dan rumusnya adalah

=SUMIFS(‘DBHasil Ujian’!$F$2:$F$121,’DBHasil Ujian’!$B$2:$B$121,’Aplikasi Data Ujian’!$B7,’DBHasil Ujian’!$E$2:$E$121,’Aplikasi Data Ujian’!D$6,’DBHasil Ujian’!$D$2:$D$121,’Aplikasi Data Ujian’!$D$1)

Dalam menggunakan Fungsi SUMIFS nilai yang dimaksud atau menjadi tujuan utama harus muncul terlebih dahulu dalam hal ini adalah nilai ujian dari siswa-siwa tersebut (perhatikan DBHasil Ujian’!$F$2:$F$121), kemudian kita sorot NIS di DBHasil Ujian (perhatikan DBHasil Ujian’!$B$2:$B$121) karena akan dibuktikan ada atau tidaknya NIS yang sama di Aplikasi Data ujian (perhatikan Aplikasi Data Ujian’!$B7) …. jika ini sudah paham saya yakin yang lainnya akan paham …..

Jika rumus diatas benar maka bisa coba test dengan mengetikkan kelas yang diinginkan dan saya yakin Nilai siswa tersebut sudah pasti akan muncul.

Dan sisanya tinggal Copy paste aja s.d Cell G16.

Untuk memunculkan Nilai Rata-rata di Cell H7 rumusnya sama dengan Cara I yaitu :

Page 13: Tips Microsoft Excel

=SUM(D7:G7)/4

Sisanya tinggal di Copy paste.

Untuk memunculkan Nilai Rata-rata di Cell D17 rumusnya adalah :

=SUM(D7:D16)/10

Sisanya tinggal di Copy paste.

Untuk memunculkan Keterangan dan tanggal beserta tanda tangal wali kelas sama dengan Cara I.

Beres deh …..

Jika suka dengan tulisan saya, maka sekarang saya sudah nyiapin file tulisan ini dalam format PDF dan bisa diunduh disini kalau mau lihat aplikasi diatas bisa diunduh disini juga…semua gratissssss.

Salam

——————————————-00000000000——————————————-

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

——————————————-00000000000——————————————-