MATERI & TUGAS EXCEL PERTEMUAN 2
Operator Aritmatika, SUM, AVERAGE, dan IF
Nama: ..............................................
Kelas: ..............................................
A. Materi Dasar
1. Operator Aritmatika
- + (Penjumlahan)
- - (Pengurangan)
- * (Perkalian)
- / (Pembagian)
2. Fungsi SUM dan AVERAGE
- SUM = Menjumlahkan data → =SUM(A1:A10)
- AVERAGE = Rata-rata → =AVERAGE(A1:A10)
3. Fungsi IF
=IF(kondisi, nilai_jika_benar, nilai_jika_salah)
Contoh: =IF(E2>=20000, E2*10%, E2*5%)
B. Contoh
| Harga | Jumlah | Total |
|---|---|---|
| 5000 | 4 | =5000*4 |
C. Tugas
Isi NIS / Absen di Cell B2 (di Excel)
| No | Nama Barang | Harga | Jumlah | Total Harga | Diskon | Total Bayar |
|---|---|---|---|---|---|---|
| 1 | Buku | =5000+($B$2*10) | 4 | ? | ? | ? |
| 2 | Pensil | =2000+($B$2*5) | 6 | ? | ? | ? |
| 3 | Penghapus | =1500+($B$2*3) | 3 | ? | ? | ? |
| 4 | Bolpoin | =3000+($B$2*7) | 5 | ? | ? | ? |
| 5 | Spidol | =7000+($B$2*2) | 2 | ? | ? | ? |
| 6 | Buku Tulis | =4000+($B$2*6) | 7 | ? | ? | ? |
| 7 | Penggaris | =2500+($B$2*4) | 4 | ? | ? | ? |
| 8 | Tipe-X | =6000+($B$2*8) | 3 | ? | ? | ? |
| 9 | Map | =3500+($B$2*5) | 6 | ? | ? | ? |
| 10 | Kertas HVS | =50000+($B$2*20) | 1 | ? | ? | ? |
| 11 | Stapler | =8000+($B$2*9) | 2 | ? | ? | ? |
| 12 | Isi Stapler | =3000+($B$2*4) | 5 | ? | ? | ? |
| 13 | Binder | =12000+($B$2*6) | 3 | ? | ? | ? |
| 14 | Sticky Notes | =4500+($B$2*3) | 4 | ? | ? | ? |
| 15 | Flashdisk | =60000+($B$2*15) | 1 | ? | ? | ? |
| 16 | Kabel Data | =25000+($B$2*10) | 2 | ? | ? | ? |
| 17 | Mouse | =75000+($B$2*12) | 1 | ? | ? | ? |
| 18 | Keyboard | =120000+($B$2*18) | 1 | ? | ? | ? |
| 19 | Headset | =90000+($B$2*14) | 1 | ? | ? | ? |
| 20 | Speaker | =150000+($B$2*16) | 1 | ? | ? | ? |
| Total Keseluruhan | ? | |||||
| Rata-rata | ? | |||||
D. Petunjuk Pengerjaan
- Total Harga = Harga * Jumlah
- Diskon menggunakan IF:
- Jika Total ≥ 20000 → 10%
- Jika < 20000 → 5%
- Total Bayar = Total Harga - Diskon
- Gunakan SUM dan AVERAGE
E. Pengumpulan
- File Excel saja
- Nama file: Nama_Kelas.xlsx
- Wajib ada identitas di dalam file
- Isi NIS/Absen agar data berbeda