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 | H |
| 1 | No | NIS | Nama | Kelas | 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 |
| 9 | 8 | 10119 | Budi rahardi | A3 | 6.30 | 7.00 | 7.50 | 9.00 |
| 10 | 9 | 10120 | Cecep | A3 | 6.50 | 7.00 | 7.90 | 9.00 |
| 11 | 10 | 10121 | Chintya | A3 | 7.00 | 9.50 | 9.00 | 9.00 |
| 12 | 11 | 10122 | Deden | B3 | 7.00 | 9.00 | 8.50 | 8.00 |
| 13 | 12 | 10123 | Dodo | B3 | 7.50 | 7.50 | 8.60 | 8.00 |
| 14 | 13 | 10124 | Farrel | B3 | 7.80 | 8.00 | 8.80 | 8.00 |
| 15 | 14 | 10125 | Fenia | B3 | 8.10 | 8.50 | 9.00 | 9.00 |
| 16 | 15 | 10126 | Karim | B3 | 8.30 | 9.00 | 9.30 | 9.00 |
| 17 | 16 | 10127 | Luna | B3 | 8.00 | 8.50 | 9.00 | 9.00 |
| 18 | 17 | 10128 | Maya | B3 | 8.50 | 8.50 | 7.00 | 8.00 |
| 19 | 18 | 10129 | Misca | B3 | 8.60 | 9.00 | 8.20 | 8.50 |
| 20 | 19 | 10130 | Nazwan | B3 | 9.00 | 9.50 | 9.00 | 9.00 |
| 21 | 20 | 10131 | Nunung | B3 | 9.00 | 9.60 | 9.50 | 9.00 |
| 22 | 21 | 10132 | Nur | C3 | 9.00 | 9.00 | 8.80 | 9.00 |
| 23 | 22 | 10133 | Nurohma | C3 | 9.50 | 9.00 | 9.00 | 9.00 |
| 24 | 23 | 10134 | Oding | C3 | 9.70 | 9.00 | 9.40 | 9.00 |
| 25 | 24 | 10135 | Opick | C3 | 8.00 | 8.50 | 7.50 | 8.00 |
| 26 | 25 | 10136 | Pardiyanto | C3 | 8.30 | 8.50 | 7.60 | 8.00 |
| 27 | 26 | 10137 | Risma | C3 | 8.80 | 9.00 | 7.80 | 8.00 |
| 28 | 27 | 10138 | Rustam | C3 | 6.75 | 7.90 | 6.00 | 7.80 |
| 29 | 28 | 10139 | Santika | C3 | 7.25 | 8.40 | 6.00 | 7.90 |
| 30 | 29 | 10140 | Vito | C3 | 8.30 | 9.00 | 6.50 | 7.50 |
| 31 | 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 | B |
| 1 | Kelas | Wali Kelas |
| 2 | A3 | Bapak Effendi Rahman |
| 3 | 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 | I |
| 1 | Nama Kelas | ||||||||
| 2 | Nama Wali Kelas | ||||||||
| 3 | |||||||||
| 4 | |||||||||
| 5 | No | NIS | Nama Siswa | Nilai Hasil Ujian | RATA-RATA | KETERANGAN | |||
| 6 | IPA | IPS | MATEMATIKA | BAHASA | |||||
| 7 | 1 | ||||||||
| 8 | 2 | ||||||||
| 9 | 3 | ||||||||
| 10 | 4 | ||||||||
| 11 | 5 | ||||||||
| 12 | 6 | ||||||||
| 13 | 7 | ||||||||
| 14 | 8 | ||||||||
| 15 | 9 | ||||||||
| 16 | 10 | ||||||||
| 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
- Nama Wali Kelas
- Nomor Induk Siswa
- Nama Siswa
- Nilai Hasil Ujian Setiap Mata Pelajaran
- Nilai Rata-Rata
- 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 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 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”)
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——————————————-
Tips Sebelumnya :
- Microsoft Excel : Merubah Background Grafik Dengan IWS “Image Wanita Sexi” (Jika Mau..)
- Microsoft Excel : Membuat Aplikasi Mendeteksi Variasi Nomor Sebuah Kartu
- Microsoft Excel : Membuat Grafik
- Microsoft Excel : Memecah Suatu (Angka) Bilangan
- Microsoft Excel : Tips Membuat Keterangan “Hasil Ujian”
- 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
——————————————-00000000000——————————————-
Untuk saran dan kritik bisa dilayangkan ke :
bunga.liani@yahoo.co.id atau ke blognyaomiyan@yahoo.co.id (email ini lupa password hehe)
——————————————-00000000000——————————————-
Mohon masukan :
Setujukah jika Tips atau aplikasi yang ada di Blognya Omiyan dibuat sebuah buku ..?
Alhamdulillah bisa posting juga setelah lemootttt setengah mati ……
kalau ada kesalahan tulis tolong dikonfirmasi ….
Posted by omiyan | 8 Juni 2010, 06:13Masih menunggu yang conditional format nama
Posted by marsudiyanto | 8 Juni 2010, 06:52(maaf) izin mengamankan KETIGA dulu. Boleh kan?!
Wah, panjang juga musti di save as dulu, ntar habis bw baru dipelajari dengan seksama.
Posted by alamendah | 8 Juni 2010, 13:47postingan yg panjang pak guru,, makasih infonya btw..
Posted by Mvstova | 8 Juni 2010, 14:49wah perlu dicoba nanti om, izin mempromosikan ke rakan-rekan guru ya om
Posted by Budisastro | 8 Juni 2010, 22:48dah saya coba dan berhasil om,
kalau data pada sheet 1 tidak tersusun secara urut/terkelompok per kelas, berarti rumusnya harus beda lagi ya om
mohon tutorial berikutnya
mantab om
Posted by budies | 9 Juni 2010, 00:57Thanx infonya, yg udah terlupakan jd teringat lagi kekekek
Posted by archer | 9 Juni 2010, 03:36Betul ….. cara pertama ini ada sedikit resiko jika kelas yang ada berbeda jumlah siswanya kemungkinan siswa lainnya akan muncul sehingga resiko kita bekerja dua kali untuk menghapusnya… nanti akan ada cara kedua dan barusan dapet ilham munkin cara III juga bisa..semoga bisa secepatnya
Posted by omiyan | 9 Juni 2010, 05:28wah, minta iji mau kopi ya, buat dipelajari …
Posted by joe | 9 Juni 2010, 06:15sudah lama sekali gak maenan MS excel pak. hehe, maklum tugas gak ada yang make excel… hehe.
Posted by hanifilham | 10 Juni 2010, 00:48ijin baca dua kali.,.,
masih bingung nih.,.
saya sudah lupa semua rumus2 exel…
Posted by aziz | 10 Juni 2010, 01:57..
Pass..
Saya lewat dulu Om, nggak ngerti..
He..he..
Salam
..
Posted by septarius | 10 Juni 2010, 02:35wow.,.keren…
bisa di contek untuk perhitungan nilai mahasiswaku nih
Posted by Ria | 10 Juni 2010, 02:50Musti print out deh keknya
Posted by anny | 10 Juni 2010, 02:59keren
Posted by eric | 10 Juni 2010, 11:25Bisa ngga ya, pada penggunaan VLOOKUP ato HLOOKUP, kriteria memuat Klausa “LIKE” seperti pada SQL ?
Omiyan : Hhhhmm untuk yang satu ini saya musti mengekerutkan dahi saya jujur masih mudeng untuk SQL ditunggu aja masukannya mas
Posted by Budhi | 11 Juni 2010, 11:10keren2 ^___^..slm kenal y mas/mbak..
mo nanya boleh ya..klo misalnya kelasnya ga di short berurutan gmn?
trus klo vlookup range nya itu kan maju terus ya, klo misalnya kolomnya di ambil mundur(kolom sebelumnya) bisa g? maaf ya byk nanya,msh agak bingung..tengkyu2..;))
Omiyan :
- nanti akan ada cara dua dan hal ini dikarenakan jika terjadi hal yang mas tanyakan
- Vlookup biasanya dari kiri ke kanan begitupun Hlookup dari atas ke bawah, pernah saya coba Data jadi tidak dikenal mungkin ada cara lain tapi untuk saat ini belum saya temukan (ini lagi ngetes juga mas heheheh)
Posted by sari | 15 Juni 2010, 08:26hmm gitu ya..ok2
ditunggu cara kedua nya ya..
btw saya mbak bkn mas..heheh..;D
Omiyan : heheheheh iya mba, saya huga baru ngeh, duh hapunteunya
Posted by sari | 15 Juni 2010, 08:41oya satu lagi..vlookup tu memang selalu pake kata ‘FALSE’ d belakangnya ya??
maaaaaffff saya nanya mulu…T_T
Posted by sari | 15 Juni 2010, 08:43tidak harus mba, disesuaikan dengan kondisi data (disini saya jadi lieur sendiri ngejelasinnya heheheheh )
Posted by omiyan | 15 Juni 2010, 08:48mgkin mas omiyan bisa ngasih contoh yg pake ‘TRUE’ ato ga pake ‘TRUE’ or ‘FALSE’..
barusan saya liat d excel ada yg ga pake true false, cm smpe range doang lgs tutup kurung..makasih2…
Posted by sari | 15 Juni 2010, 08:56Barusan ngetes mungkin ini kesimpulan versi saya, semoga aja ada yang bisa bantuin hihihihihi :
- TRUE dipakai jika data yang menjadi kata kunci pembanding jika dibandingkan dengan data lainnya tidak terdapat kesamaan atau masih bersifat perkiraan sehinggga jika ada dua angka yang sama dipastikan yang satu tidak akan dikenal, misalkan :
- Cell A1 kita ketik 2
- Cell D2 ketik 23, Cell E2 ketik Agus
- Cell D3 ketik 2, Cell E3 ketik Andi
Coba di Cell B1 ketik rumus ini : =VLOOKUP($A$1,$D$1:$E$2,2,TRUE)
dan satu lagi di Cell B2 ketik rumus : =VLOOKUP($A$1,$D$1:$E$2,2,FALSE)
pastinya di Cell B1 akan muncul #N/A tapi di Cell B2 akan muncul andi
tapi jika data di Cell D2 angka 23 kita rubah dengan 1, maka ketika kita mengetikkan angka 1 di Cell A1 hasilnya di Cell B1 dan Cell B2 adalah agus
- FALSE dipakai jika data yang menjadi kata kunci pembanding jika dibandingkan dengan data lainnya sudah bersifat PASTI walaupun data pembanding ada yang sama angkanya terbukti di Cell B2
Posted by omiyan | 15 Juni 2010, 09:35ok deh makasih ya mas..saya pelajari dulu..hehe..maksih bgt ya ud jwb pertanyaan saya..jgn bosen klo besok2 saya nanya lagi..heheu..;D
Posted by sari | 15 Juni 2010, 09:43sama-sama mba, setidaknya apa yang say posting bisa bermanfaat buat yang lainnya … saya justru suka jika bisa berkomunikasi seperti ini
salam
Posted by omiyan | 15 Juni 2010, 09:53tangk dari kayu pak… alias tanks u… sangat bermanfaat sekali… saya tunggu yang lainnya….
Posted by budi | 28 Juli 2010, 03:21menurut saya lebih mudahnya, kita buat nama untuk masing-masing kelas. Contoh untuk data kelas A3 di Sheet 1, kita blok A2:H11, lalu define name “KELAS_A3″. Demikian untuk kelas yang lain.
Nah, untuk kolom nilai di Sheet3, biar rumusnya tidak terlalu panjang; untuk sel E7 (IPA), maka bisa kita gunakan rumus:
=INDEX(INDIRECT(“KELAS_”&$D$1);$B7;MATCH(E$6;$E$6:$H$6;0)+4)
kopikan untuk sel yang lain.
Posted by haeruddin | 26 Februari 2011, 15:00mantaf2 idenya dan kalau ada kasi dong penghitungan umur untuk penerimaan siswa baru khusus sd
Posted by maul | 26 April 2011, 02:57untuk mencari umur seseorang, bisa pake rumus DATEDIF. Silahkan cari di Google fungsi DATEDIF. Saran saya, cari webnya maseko di mana di web tersebut ada tutorial tentang DADEDIF yang sudah berbahasa indonesia.
Ketikkan kata berikut di Google: DATEDIF maseko.
Semoga bermanfaat
Posted by haeruddin | 26 April 2011, 07:19Link-nya broken yaw sist,, mohon pencerahannya,,, atau kalau ada waktu mohon dikirim ke email saya yaw,,,
thx before,,,
Posted by Miko | 2 Desember 2011, 16:58