i8

SELAMAT DATANG DIBLOG SAYA

Minggu, 17 Maret 2013

microsoft excel


rumus dasar excel
1. Simbol sama dengan [=digunakan untuk mengawali sebelum melakukan perhitungan
2. Simbol perpangkatan [^] digunakan untuk melakukan hitungan perpangkatan
3. Simbol pembagian [/] digunakan untuk melakukan hitungan pembagian
4. Simbol pengurangan [-] digunakan untuk melakukan hitungan pengurangan
5. Simbol perkalian [*] digunakan untuk melakukan hitungan perkalian
6. Simbol penjumlahan [+] digunakan untuk melakukan perhitungan penjumlahan
7. Simbol titik koma [;] digunkan untuk melakukan pembatas antar rumus
8. Simbol koma [,] digunakan untuk melakukan pembatas antar rumus
9. Simbol kurung bukadigunakan untuk mengawali dari sekelompok rumus
10. Simbol kurung tutupdigunakan untuk mengakhiri dari sekelompok rumus
membuat rumus sederhana

Nah sekarang anggaplah aplikasi yang kita buat adalah seperti gambar dibawah ini :

Yuk kita mulai membuat rumusnya :
  • Untuk memunculkan Nama Warga (Cell D4) saya yakin yang biasa berkunjung kesini sudah paham, yaitu dengan menggunanakan Fungsi VLOOKUP, dan rumusnya adalah :
=IF(ISBLANK(D3),”",VLOOKUP(D3,Sheet2!$A$6:$B$8,2,FALSE))
Kali ini kita menambah Fungsi IF dan ISBLANK dengan tujuan ketika data yang tertera di Nomor Penduduk (Cell D3) kosong dalam arti tidak ada hurup atau angka yang diketik maka di Nama Warga (Cell D4) tidak akan muncul #N/ A.
  • Karena kita ingin menampilkan profil warga tersebut di Keterangan (Cell D5) maka rumus yang kita bangun adalah sebagai berikut :
=D4&” adalah warga kami yang sekarang tinggal dan berkedudukan di “&IF(ISBLANK(D3),”",VLOOKUP(D3,Sheet2!$A$6:$G$8,4,FALSE)& dimana sebelumnya adalah warga pindahan dari &IF(ISBLANK(D3),”",VLOOKUP(D3,Sheet2!$A$6:$G$8,3,FALSE)& adapun tempat dan tanggal lahirnya yaitu &IF(ISBLANK(D3),”",VLOOKUP(D3,Sheet2!$A$6:$G$8,5,FALSE)&”, “&IF(ISBLANK(D3),”",VLOOKUP(D3,Sheet2!$A$6:$G$8,6,FALSE)& dengan status perkawinan sekarang adalah &IF(ISBLANK(D3),”",VLOOKUP(D3,Sheet2!$A$6:$G$8,7,FALSE))))))
Perhatikan baik-baik jangan dulu pusing ya, bahwa ada beberapa kalimat yang merupakan penjelasan dari rumus yang dibuat yang bertujuan menjelaskan kondisi warga tersebut dan untuk yang lainnya saya rasa cukup jelas.
Nah sekarang coba masukan salah satu nomor penduduk, misalkan A121, maka yang muncul adalah seperti gambar dibawah ini : 

Klik lambang  Microsoft dipojok kiri atas kemudian pilih Excel Options.
  • Kemudian pilih Customize dan akan muncul berbagai pilihan Commands dan cari Command Form.
  • Setelah itu tekan  OK
Command Form tersebut akan muncul di sebelah kanan lambang Microsoft tersebut dan lambang Form tersebut adalah seperti ini :
http://bungaliani.files.wordpress.com/2010/09/form.jpg?w=750
Langkah selanjutnya yaitu kita membuat table, perhatikan contoh dibawah ini :

Setelah itu arahkan cursor di Cell A1 atau tepatnya di judul kolom NO, kemudian klik lambang form yang sudah kita add sebelumnya maka akan muncul sebuah form dengan tulisan seperti ini :
Microsoft Office Excel cannot determine which………
Kemudian tekan OK dan hasilnya akan muncul FORM seperti gambar dibawah ini 

Berarti kita sukses Membuat FORM tersebut dan tinggal mengisi datanya.
Sebagai latihan coba isikan data seperti ini di FORM yang sudah muncul tadi:
No                          1
Nama                    Iyan
NIS                         1234
Kelas                     A1
Jenis Kelamin    L
Jika sudah coba sekarang keluar dari FORM tersebut, maka ditable akan berisi data seperti ini


Cell
A
B
C
D
E
F
1
NIS
Nama
Jenis
Kelamin
Bulan
Ujian
Ke
Nilai
Ujian
2
A10001
Andi
L
Januari
1
8.5
3
A10002
Omiyan
L
Januari
1
7.6
4
A10003
Agung
L
Januari
1
7.7
5
A10004
Yanti
P
Januari
1
6.3
6
A10005
Lisa
P
Januari
1
9.2
7
A10001
Andi
L
Januari
2
8.0
8
A10002
Omiyan
L
Januari
2
7.2
9
A10003
Agung
L
Januari
2
9.3
10
A10004
Yanti
P
Januari
2
5.8
11
A10005
Lisa
P
Januari
2
9.5
Tabel.1 (Sheet1) atau DB

Jika sudah selesai, disini kita ingin membuat suatu cara bisa mengetahui berapa nilai ujian anak yang dimaksud dengan cukup mengetikkan Nomor Induk Siswa (NIS) tersebut, sekarang buka Worksheet Excel Sheet2, dan buatlah sebuah Table seperti Table.2 dibawah ini  :
Cell
A
B
C
D
E
F
1
NIS





2
Nama





3
Jenis Kelamin





4






5
Bulan
Ujian Ke
Jumlah
Nilai Rata-Rata
Keterangan
6

1
2



7
Januari





8






Tabel.2 (Sheet2)
Oke kalau sudah siap semua, coba siapkan Teh manis hangat plus Sepotong roti atau makanan ringan tapi ingat jangan merokok ya hehehehhe……
Coba ikuti langkah-langkah sebagai berikut :
1. Perhatikan Tabel.2, di Cell B2 tepat disamping Nama, coba ketikkan rumus dibawah ini :
=VLOOKUP(B1,DB!$A$2:$B$11,2,FALSE)
Artinya kita ingin melihat sebuah nilai yang sama dari Cell B1 yaitu NIS dengan muncul sebuah nama di Cell B2 yaitu Nama (dalam hal ini nama dari NIS dimaksud) dengan melihat data di DB diantara Cell A1 sampai dengan Cell B11, adapun sebuah muncul angka 2 maksudnya kolom yang menjadi patokan pencarian data atau mengadu data atau mencari persamaan hanya 2 kolom sedangkan False adalah sebuah Nilai dari range yang kita lihat benar atau salah.
2. Perhatikan Tabel.2, di Cell B3 tepat disamping Jenis Kelamin, coba ketikkan rumus dibawah ini :
=VLOOKUP(B1,DB!$A$2:$C$11,3,FALSE)
Artinya kita ingin melihat sebuah nilai yang sama dari Cell B1 yaitu NIS dengan muncul sebuah nama (kata) di Cell B3 yaitu Jenis Kelamin (dalam hal ini Jenis Kelamin dari NIS dimaksud) dengan melihat data di DB diantara Cell A1 sampai dengan Cell C11, adapun sebuah muncul angka 3 maksudnya kolom yang menjadi patokan pencarian data atau mengadu data atau mencari persamaan hanya 3 kolom sedangkan False adalah sebuah Nilai dari range yang kita lihat benar atau salah.
Ok Cukup dulu untuk pembuktian coba sekarang ketikkan salah satu NIS di Sheet 2 Cell B2, misalkan A10001 maka hasilnya akan muncul :
Cell
A
B
1
NIS
A10001
2
Nama
Andi
3
Jenis Kelamin
L
Jika muncul seperti diatas berarti rumusnya benar sekali, sekarang kita lanjut tapi ingat Teh manis hangatnya diminum dulu ntar keburu dingin lagi hehehehe.
Masih di Sheet2, pindahkan kursor ke Cell B7, sekarang kita ingin mencari Berapa Sih Nilai Ujian Siwa Tersebut Pada Ujian 1, uuntuk itu coba masukkan rumus sebagai berikut :
=SUMIFS(DB!$F$2:$F$11,DB!$A$2:$A$11,Sheet2!$B$1,DB!$D$2:$D$11,
Sheet2!$A7,DB!$E$2:$E$11,Sheet2!B$6)
Artinya kita menggunakan rumus SUMIFS karena kita akan mengadu dataatau mencari persamaan lebih dari satu data, disini data yang akan kita adu atau cari persamaannya adalah :
1.      Nilai ujian (F2:F11) dan NIS (A2:A11) yang ada di DB dengan data NIS di Sheet2 (B1).
2.      Nama Bulan (D2:D11) di DB dengan nama Bulan di Sheet 2 (A7).
3.      Ujian Ke (E2:E11) di DB dengan angka dibawah baris Ujian ke di Sheet 2 (B6).
4.      Tanda $ apabila berada sebelum dan sesudah Cell berarti mengunci data tersebut baik dari sisi kolom maupun baris.
5.      Tanda $ Cell berarti kita mengunci data tersebut dari sisi kolom.
6.      Tanda $ Cell berarti kita mengunci data tersebut dari sisi baris.
Jika sudah seperti pembuktian pertama coba ketikkan  salah satu NIS di Sheet 2 Cell B2, misalkan A10001 maka hasilnya akan muncul :
Cell
A
B
C
D
E
F
1
NIS
A10001




2
Nama
Andi




3
Jenis Kelamin
L




4






5
Bulan
Ujian Ke
Jumlah
Nilai Rata-Rata
Keterangan
6

1
2



7
Januari
8.5




8






Untuk melihat Nilai hasil Ujian ke 2 (Cell C7) cukup mencopy rumus sebelumnya. Kemudian untuk mencari total dari hasil 2 kali ujian (Cell D7) saya yakin semua sudah pada tahu cukup ketikkan rumus :
=SUM(B7:C7)
Untuk mencari Nilai Rata-Rata (Cell E7) saya yakin banget semua sudah pada tahu tinggak ketik rumus :
=D7/2
Untuk membuat keterangan bisa manual dengan memperhatikan beberapa kriteria misalkan jika nilainya dibawah 7 maka dianggap Kurang kalau diatas 7 berarti dan seterusnya. Nah disini kita bisa menggunakan Vlookup atau rumus IF, disini saya menggunakan rumus IF, coba masukkan rumus sebagai berikut :
=IF(E7<7,”Kurang”,IF(E7<8,”Cukup”,IF(E7<9,”Baik”,IF(E7=>9,”Baik Sekali”))))
Artinya Jika nilai Rata-rata siswa tersebut kurang dari 7 maka keterangan yang akan muncul adalah Kurang, jika lebih dari 7 tapi kurang dari 8 adalah Cukup, jika lebih dari 8 tapi kurang dari 9 adalah Baik, jika sama dengan dan atau lebih dari 9 maka hasilnya adalah Baik Sekali.
h  sekarang buka worksheet excel dan ikuti seperti contoh dibawah ini 

Mohon ditulis persis seperti contoh diatas (Cell penulisan akan berpengaruh terhadap rumus)
Oke mungkin saya kasih keterangan terlebih dahulu agar lebih mudah dimengerti :
  • Jika Penghasilan Kena Pajak sampai dengan 50 juta maka tarif yang dikenakan adalah 5%
  • Jika Penghasilan Kena Pajak sampai dengan 250 juta maka tarif yang dikenakan adalah 5% (untuk 50 juta) dan 15% (untuk sisanya), contoh misalkan Penghasilan Kena Pajak tersebut berjumlah 250 juta maka tarif yang diitung adalah 50 juta x 5% dan sisanya 200 juta x 15%.
  • Jika Penghasilan Kena Pajak sampai dengan 500 juta maka tarif yang dikenakan adalah 5% (untuk 50 juta), 15% (untuk 200 juta) dan 25% (untuk sisanya).
  • Jika Penghasilan Kena Pajak diatas 500 juta maka tarif yang dikenakan adalah 5% (untuk 50 juta), 15% (untuk 200 juta), 25% (untuk 250 juta) dan 30% (untuk sisanya).
Sudah mengerti kan penjelasannya, jika sudah mengerti ini berarti kita bisa melangkah kedalam contoh soal dibawah ini, jika belum pahami dulu saja penjelasan diatas.
Ok sekarang perhatiin contoh soal dibawah ini :
Misalkan Penghasilan Kena Pajak atas nama Tuan A berjumlah Rp. 550.000.000 (tulis nilai uang tersebut di Cell E19), nah yang jadi pertanyaannya berapa Penghasilan Yang Terutang Pajaknya atau istilahnya PPh Terutangnya.
Jika kita gunakan cara manual maka hasilnya adalah sebagai berikut :
  • 50.000.000 x 5% = 2.500.000 (Cell B22)
  • 200.000.000 x 15% = 30.000.000 (Cell B23)
  • 250.000.000 x 25% = 62.500.000 (Cell B24)
  • 50.000.000 x 30% = 15.000.000 (Cell B25)
Hasilnya (Cell E26) adalah 110.000.000 (seratus sepuluh juta rupiah).
Nah sekarang kita ingin penghitungan tersebut dilakukan secara otomatis dengan menggunakan rumus, angapalah kita ingin menulis hasilnya di Cell E8 maka rumusnya adalah :
=IF(E19<B6,(E19*D5),IF(AND(E19>C5,E19<B7),(E5)+(E19-C5)*D6,IF(AND(E19>C6,E19<B8),(E5+E6)+(E19-C6)*D7,IF(E19>C7,(E5+E6+E7)+(E19-C7)*D8))))
Keterangan :
·         Ketika kondisi nilai yang muncul di Cell E19 kurang dari B6 (50.000.001) maka akan dikalikan 5%
·         Jika kondisi nilai yang muncul di Cell E19 lebih dari C5 (50.000.000) dan kurang dari Cell B7 (250.000.001) maka hasilnya yang pertama adalah hasil pasti Cell E5 (2.500.000) ditambah dengan nilai di Cell E19 dikurangi dengan batasan yang dikenakan Cell C5 (50.000.000) dikali 15%
·         dst.
Jika sudah dan benar maka hasil yang muncul adalah sama yaitu 110.000.000.
Tapi bisa juga kamu membuat rumus dengan cara seperti ini, hampir sama tapi perhatiin Cell yang diambil :
=IF(AND(E19>1,E19<B6),(E19*D5),IF(AND(E19>C5,E19<B7),(C5*D5)+(E19-C5)*D7,IF(AND(E19>C6,E19<B8),(C5*D5)+(C6-C5)*D6+(E19-C6)*D7,IF(E19>C7,(C5*D5)+(C6-C5)*D6+(C7-C6)*D7+(E19-C7)*D8))))
Dan hasilnya sama saja tetap 110.000.000, eiittt coba perhatiin dengan seksama pasti ada perbedaan dalam mengambil Cell antara rumus pertama dan yang kedua tersebut.
Mungkin hasilnya pada gambar seperti pada gambar dibawah ini :


Tidak ada komentar:

Posting Komentar