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 buka ( digunakan untuk mengawali dari sekelompok rumus
10. Simbol kurung tutup ) digunakan untuk mengakhiri dari sekelompok rumus
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 buka ( digunakan untuk mengawali dari sekelompok rumus
10. Simbol kurung tutup ) digunakan 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 :
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)
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
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 :
