KEGIATAN PEMBELAJARAN 4
RUMUS DALAM MICROSOFT EXCEL
A. Formula (Rumus)
Formula adalah mersamaan dalam Microsoft Excel yang dapat digunakan untuk perhitungan, menghasilkan informasi, mengedit isi sel, menguji kondisi ataupun yang lainnya yang bersangkutan dengan pengolahan data. Untuk menjalankan formula harus dimulai dengan simbol Sama Dengan ( = ).
Suatu rumus dapat bersangkutan dengan elemen berikut:
ü Nilai angka secara langsung
ü Operator perhitungan
ü Referensi sel
ü Fungsi microsoft excel
ü Nama sel atau range
1. Rumus Yang Berisi Nilai Angka Langsung
Kita bisa melakukan perhitunga dengan angka yang langsung kita ketik. Misalnya 100+500. Maka kita bisa langsung mengetiknya dengan =100+500 maka setelah kita klik akan nampak hasinya adalah 600.
2. Rumus Yang Berisikan Operator Perhitungan
Operator merupakan simbol yang digunakan Excel untuk melakukan operasi terhadap data.Operator ini melambangkan satu operasi tertentu, misalnya operator + untuk operasi penjumlahan dan operator - untuk operasi pengurangan.
a. Operator Aritmatika
|
No |
Simbol |
Keterangan |
Penulisan |
Hasil |
|
1 |
+ |
Penjumlahan |
=20+10 |
30 |
|
2 |
- |
Pengurangan |
=30-10 |
20 |
|
3 |
/ |
Pembagian |
=40/4 |
10 |
|
4 |
* |
Perkalian |
=6*10 |
60 |
|
5 |
^ |
Perpangkatan |
=10^2 |
100 |
b. Operator Perbandingan
|
No |
Simbol |
Keterangan |
Penulisan |
Hasil |
|
1 |
= |
Sama dengan |
=20=20 |
True |
|
2 |
> |
Lebih dari |
=30>10 |
True |
|
3 |
< |
Kurang dari |
=40<10 |
False |
|
4 |
>= |
Lebih besar atau sama dengan |
=6>=10 |
False |
|
5 |
<= |
Kurang dari atau sama dengan |
=10<=2 |
False |
|
6 |
<> |
Tidak sama dengan |
=2<>3 |
True |
*True menunjukan jawaban benar dan False menunjukan jawaban salah
c. Operator Teks &
Data penggabungan teks tidak akan bisa berjalan menggunakan fungsi matematika dan ada simbol khusus yaitu dengan menggunakan fungsi teks &.
=A1+A2, menghasilkan error (#VALUE)
=A1&A2, menghasilkan sayaasli (tanpa spasi)
=A1&" "&A2, menghasilkan saya asli (ada spasi antara saya dan asli)
=A1&" "&A2&" "&A3, menghasilkan saya asli jogja (spasi spasi antara saya, asli, dan jogja).
Untuk memberikan spasi antara saya, asli, dan jogja dengan menggunakan spasi yang diletakkan di antara tanda petik ganda (“), seperti ini: “ “.
Data dapat diambil dari referensi sel lain yang mengandung nilai yang akan kita proses. Referensi ini bisa dari sel lain maupun dari sheet lain.
Function (Fungsi) adalah rumus-rumus siap pakai yang sudah terdapat pada aplikasi MS Excel,yang dapat digunakan melakukan perhitungan dan berbagai fungsi ketika bekerja dengan MS Excel.
Fungsi berguna untuk menyederhanakan perhitungan yang panjang dan rumit, misal:
Formula =B10+B11+B12+B13+B14+B15+B16+B17+B18+B19+B20, bila ditulis dengan menggunakan fungsi cukup ditulis menjadi =SUM(B10:B20). Fungsi juga disediakan oleh MS Excel untuk melakukan perhitungan yang sulit jika dibuat dengan formula (rumus) sendiri.
Rumus-Rumus dalam Microsoft Excel
a. Rumus Statistik
|
=Sum(Range) |
Menghitung jumlah nilai pada data yang terdapat pada range |
|
=Average(Range) |
Menghitung nilai rata-rata pada range |
|
=Min(Range) |
Mencetak nilai minimum pada range |
|
=Max(Range) |
Mencetak nilai maximum pada range |
|
=Count(Range) |
Menghitung jumlah data pada range |
|
=Countif(Range) |
Menghitung jumlah data pada range dengan kriteria tertentu (cth: hanya mencari jumlah/banyaknya baju batik saja) |
|
=Counta(Range) |
Menghitung banyaknya cell nonblank (cell yg tidak kosong) |
|
=Concatenate(Range) |
Menggabungka 2 sel |
|
=Sumif (Range) |
Menghitung jumlah nilai pada range dengan kriteria tertentu (cth: menghitung total nilai harga dari seluruh harga baju batik saja) |
Contoh Penulisan:
ü Contoh Rumus Sum
ü Contoh Rumus Average
b. Rumus Text
|
Fungsi |
Keterangan |
Contoh Penggunaan Rumus |
|
LEFT |
Untuk mengambil beberapa Karakter dari kiri |
= LEFT (Text;Num_Chars) |
|
MID |
Untuk mengambil beberapa karakter dari tengah |
= MID (Text;Start_Num;Num_Chars) |
|
RIGHT |
Untuk mengambil beberapa karakter dari kanan |
= RIGHT(Text; Num_Chars) |
ü Menggunakan Fungsi LEFT
Pada cell B2 masukkan rumus =LEFT(A2,3) Rumus tersebut digunakan untuk mengambil tiga digit dari kiri.
Keterangan:
A2 merupakan cell kunci
3 merupakan jumlah karakter yang diambil
ü Menggunakan Fungsi MID
Pada cell C2 masukkan rumus =MID(A2,3) Rumus tersebut digunakan untuk mengambil tiga digit dari tengah.
Keterangan:
A2 merupakan cell kunci
5 Merupakan karakter pertama dari karakter yang akan diambil
6 Jumlah karakter yang diambil
ü Menggunakan Fungsi RIGHT
Pada cell D2 masukkan rumus =RIGHT(A2,5,6) Rumus tersebut digunakan untuk mengambil tiga digit dari kanan.
c. Rumus Logika
ü Single IF
Fungsi logika IF digunakan untuk menguji suatu isi sel apakah berisikan nilai yang memenuhi kriteria (syarat) yang telah ditentukan dan akan diberikan suatu nilai atataukah tidak, sehingga akan diberikan nilai yang sama.
= IF ( Logical Test; Value IF True; Value IF False )
Contoh Penggunaan
Keterangan Rumus:
Jika Nilai Excel pada cell C2 lebih besar atau sama dengan 75, maka keterangan LULUS, selain itu dianggap TIDAK LULUS. Yang perlu diperhatikan pada penulisan rumus, jika keterangan menggunakan huruf maka diberi tanda kutip, contoh “LULUS” atau “TIDAK LULUS”, Tapi jika keterangan menggunakan angka tidak diberi tanda kutip.
ü Multi IF
Merupakan rumus yang biasa dipakai dimana logika yang digunakan lebih dari 2.
Contoh Penggunaan:
Keterangan:
Jika Nilai pada cell C2 lebih besar atau sama dengan 90, maka pada kolom D2 sama dengan A, Jika Nilai pada cell C2 lebih besar atau sama dengan 76, maka pada kolom D2 sama dengan B, Jika Nilai pada cell C2 lebih besar atau sama dengan 60, maka pada kolom D2 sama dengan C, selain itu diberi nilai D.
Rumusnya:
|
=IF(C2>=90,”A”,IF(C2>=76,”B”,”IF(C2>=60,”C”,”D”))) |
Perhatikan!
Jika kurung buka ada tiga maka kurung tutup juga ada tiga.
ü Fungsi IF dan AND
Merupakan fungsi multi logika dimana membutuhkan 2 syarat atau lebih dan semuanya harus memenuhi syarat. Apabila semua kriteria memenuhi syarat maka menghasilkan nilai TRUE dan menghasilkan nilai FALSE jika ada satu atau lebih argument yang bernilai salah.
Contoh penggunaan rumus IF AND
|
Pada cell F3, masukkan rumus: =IF(AND(C3>70;D3>70);”Kompeten”;”Tidak Kompeten”) |
Rumus IF AND diatas digunakan untuk menyeleksi apakah nilai ujian siswa pada kolom C (teori) dan kolom D (praktik) bernilai lebih besar dari 70, jika nilai pada kedua kolom tersebut bernilai lebih dari 70, maka dinyatakan pada kolom E adalah Kompeten. Tapi, jika ada nilai dibawah 70 pada salah satu kolom C atau D, maka dinyatakan pada kolom E adalah Tidak Kompeten.
Hasil akhir dari penggunaan rumus IF AND diatas, bisa dilihat pada tampilan table dibawah ini:
ü Fungsi IF dan OR
Merupakan fungsi multi logika dimana membutuhkan 2 syarat atau lebih dan syarat ini hanya membutuhkan salahsatu syarat yang harus memenuhi. Apabila salah satu minimal satu kriteria memenuhi syarat maka menghasilkan nilai TRUE dan menghasilkan nilai FALSE jika semua kriteria tidak ada yang memenuhi.
Contoh kasus:
Seorang HRD sedang menyeleksi calon karyawan baru, calon karyawan akan diterima apabila nilai Tes Potensi lebih dari 85, dan hasil wawancara bagus. Atau nilai Tes Potensi kurang dari 85, tetapi hasil wawancara bagus. Selain itu tidak diterima.
Perhatikan cell E2!
Pada cell E2 hasilnya DITERIMA,
padahal nilai Tes Potensi kurang dari 85, tetapi hasil
wawancara bagus. Tapi pada cell E5
hasilnya DITOLAK, sebab nilai Tes Potensi kurang dari 85, dan hasil wawancara
kurang.
B. Mengetahui Kesalahan Ketika Menulis Rumus / Formula
|
PESAN KESALAHAN |
PENYEBAB |
|
#DIV/0! |
Formula yang dibuat ada pembagian dengan nol (0) |
|
#N/A |
Ada argumen yang tidak tertulis |
|
#NAME? |
Dalam formula ada teks yang tidak dikenal oleh MS Excel |
|
#NULL! |
Ada kesalahan membuat range selnya, yang seharusnya mengandung interseksi, tetapi tidak mengandung interseksi. Misal formula =SUM(A1:B3 C1:D5) tidak memiliki interseksi (perpotongan) |
|
#REF |
Referensi sebagai argumen tidak ada |
|
#VALUE |
Yang seharusnya argumennya adalah teks, tetapi yang dimasukkan bukan teks |
Link Dokumen Materi: https://drive.google.com/file/d/1hh6QecVrmPF9Oil-v5B8awfpczk-rtCV/view?usp=sharing
LEMBAR KERJA MURID
1. Kasus 1 (Mencocokan)
|
Istilah |
Penjelasan |
|
Jawaban |
|
|
Istilah |
Penjelasan |
|||
|
1. SUM |
A. Fungsi yang mencari nilai tertinggi dalam rentang sel |
1 |
|
|
|
2. AVERAGE |
B. Fungsi yang menggabungkan teks dari beberapa sel |
2 |
|
|
|
3. IF |
C. Fungsi logika yang memberikan hasil berbeda berdasarkan kondisi |
3 |
|
|
|
4. VLOOKUP |
D. Referensi sel absolut yang tidak berubah saat disalin |
4 |
|
|
|
5. COUNTIF |
E. Fungsi yang menghitung jumlah sel yang berisi angka |
5 |
|
|
|
6. MAX |
F. Fungsi yang menampilkan tanggal hari ini |
6 |
|
|
|
7. MIN |
G. Fungsi yang membulatkan angka ke digit tertentu |
7 |
|
|
|
8. CONCATENATE |
H. Fungsi yang mengekstrak sejumlah karakter dari kiri teks |
8 |
|
|
|
9. TODAY |
I. Fungsi yang mencari nilai di kolom pertama tabel dan mengembalikan nilai dari kolom lain |
9 |
|
|
|
10. ROUND |
J. Fungsi yang menghitung jumlah dengan beberapa kriteria |
10 |
|
|
|
11. $A$1 |
K. Fungsi yang mencari nilai terendah dalam rentang sel |
11 |
|
|
|
12. COUNT |
L. Fungsi yang menghitung rata-rata dari sekumpulan angka |
12 |
|
|
|
13. SUMIFS |
M. Fungsi yang menghitung selisih antara dua tanggal |
13 |
|
|
|
14. LEFT |
N. Fungsi yang menghitung jumlah dari sekumpulan angka |
14 |
|
|
|
15. DATEDIF |
O. Fungsi yang menghitung jumlah sel yang memenuhi kriteria tunggal |
15 |
|
|
2. Kasus 2 (Pengerjaan Projek)
|
No |
Nama Siswa |
Nilai Ujian |
Rata-rata |
|||
|
Matematika |
PAI |
Ekonomi |
Akuntansi |
|||
|
1 |
Usro |
6.5 |
8.5 |
5.0 |
7.5 |
|
|
2 |
Unyil |
4.5 |
7.5 |
5.5 |
7.5 |
|
|
3 |
Otong |
5.0 |
6.0 |
4.5 |
7.0 |
|
|
4 |
Eneng |
7.0 |
7.0 |
6.0 |
8.0 |
|
|
5 |
Ucup |
8.5 |
8.0 |
7.0 |
8.5 |
|
|
6 |
Endo |
7.5 |
6.5 |
6.0 |
6.5 |
|
|
7 |
Syarah |
8.0 |
7.0 |
5.0 |
7.5 |
|
|
8 |
Friska |
5.5 |
8.0 |
5.0 |
9.0 |
|
|
SUM |
|
|
|
|
|
|
|
MIN |
|
|
|
|
|
|
|
MAX |
|
|
|
|
|
|
|
NO |
NAMA |
JAM KERJA |
JAM LEMBUR |
UPAH KERJA (KOTOR) |
UPAH LEMBUR |
TOTAL UPAH |
PAJAK |
TOTAL UPAH (NETTO) |
|
1 |
Arsel |
45 |
15 |
|
|
|
|
|
|
2 |
Maher |
48 |
16 |
|
|
|
|
|
|
3 |
Shaka |
47 |
10 |
|
|
|
|
|
|
4 |
Zianka |
50 |
13 |
|
|
|
|
|
|
5 |
Rezki |
45 |
17 |
|
|
|
|
|
|
6 |
Queen |
44 |
12 |
|
|
|
|
|
|
7 |
Naswa |
50 |
14 |
|
|
|
|
|
|
8 |
Raisa |
39 |
10 |
|
|
|
|
|
|
9 |
Abang |
41 |
12 |
|
|
|
|
|
|
10 |
Raymon |
45 |
17 |
|
|
|
|
|
|
Total Upah Seluruh Karyawan |
|
|
|
|
|
|||
|
Rata-rata Upah Seluruh Karyawan |
|
|
|
|
|
|||
|
Upah Tertinggi Karyawan |
|
|
|
|
|
|||
|
Upah Terendah Karyawan |
|
|
|
|
|
|||
Ketentuan Soal :
Upah Kerja (Kotor) = Jam Kerja x Rp. 25.000 Upah Lembur = Jam Lembur x Rp. 30.000
Total Upah = Upah Kerja + Upah Lembur Pajak = Total Upah x 5%
Total Upah (Netto) = Total Upah – Pajak
|
No |
Nama |
Nilai |
Grade |
Status |
|
1 |
Dahlan |
60 |
|
|
|
2 |
Usin |
90 |
|
|
|
3 |
Jaka |
34 |
|
|
|
4 |
Saman |
96 |
|
|
|
5 |
Riki |
70 |
|
|
|
6 |
Mahli |
34 |
|
|
|
7 |
Popon |
78 |
|
|
|
8 |
Erik |
87 |
|
|
|
9 |
Rikja |
78 |
|
|
|
10 |
Maman |
89 |
|
|
Ketentuan Soal :
Grade A = Nilai 90 – 10, Grade B = Nilai 80 – 89, Grade C = Nilai 70 – 79, Grade D = Nilai 60 – 69 ,Grade E = Nilai < 60
Status :
Jika Grade > 75, maka status Lulus, Jika Grade < 75, maka status Tidak Lulus
|
No |
Nama Mahasiswa |
Kode |
Nama Mata Kuliah |
Nilai |
Grade |
|||||||||||||
|
Absen |
Tugas |
UTS |
UAS |
Akhir |
||||||||||||||
|
1 |
Anastasia Aku |
120 |
|
80 |
85 |
80 |
85 |
|
|
|||||||||
|
2 |
Anggello Yosua Gracio |
738 |
|
100 |
80 |
90 |
95 |
|
|
|||||||||
|
3 |
Arabbi Tuakia |
745 |
|
90 |
90 |
95 |
90 |
|
|
|||||||||
|
4 |
Aripin |
120 |
|
85 |
95 |
60 |
86 |
|
|
|||||||||
|
5 |
Arizal Apriliawan Putra |
150 |
|
100 |
80 |
70 |
70 |
|
|
|||||||||
|
6 |
Arlyn Arta Pradipa |
745 |
|
75 |
85 |
80 |
75 |
|
|
|||||||||
|
|
Rata-rata |
|
|
|
|
|
|
|
||||||||||
|
Tertinggi |
|
|
|
|
|
|
|
|||||||||||
|
Terendah |
|
|
|
|
|
|
|
|||||||||||
Keterangan:
1. Nilai Akhir didapat dari = (10%*Absen)+(20%*Tugas)+(30%*uts)+(40%*uas)
2. Untuk Grade di dapatkan dari ( pergunakan operator perbandingan spt: >, <,>=, <=)
Jika besar sama dengan 85, maka grade A
Jika besar sama dengan 75, maka grade B
Selain itu grade C
0 Komentar
Terimakasih telah mampir yah..