Download - Modul Latihan KKPI Ms Excel
Penggunaan Operator Arithmatik
Arzaki Arif Nc., S.T. – KKPI (Microsoft Office Excel) - SMK Negeri 3 Pacitan
KE T EN T UA N P EN GI S I A N :
JUMLAH = Jumlah dari Nilai (Normatif, Adaptif, Produktif)
RATA-RATA = Rata-Rata dari Nilai (Normatif, Adaptif, Produktif)
HASIL KALI = Jumlah dikali Rata-Rata
HASIL BAGI = Jumlah dibagi Rata-Rata
HASIL KURANG = Hasil Kali dikurangi Hasil Bagi
HASIL PANGKAT = Rata-Rata dipangkatkan 2
HASIL PERSEN = Hasil Pangkat dikalikan 2%
Penerapan Fungsi SUM, MAX, MIN
Arzaki Arif Nc., S.T. – KKPI (Microsoft Office Excel) - SMK Negeri 3 Pacitan
KE T EN T U A N PEN GI S I A N :Lakukan Format – Cell (accounting) pada kolom HARGA SATUAN, JUMLAH HARGA, DISKON dan TOTAL PEMBAYARANuntuk memberi tanda “Rp” dan “nol dibelakang koma”
Jumlah Harga = Nilai Uang yang dibayarkan (Jumlah Barang dikalikan Harga Satuan)Diskon = Nilai Uang setelah Diskon 5% dari Jumlah HargaTotal Pembayaran = Nilai Uang yang harus dibayarkan setelah DiskonJumlah Total Pembayaran = Jumlah Nilai Uang Total PembayaranHarga Tertinggi dari Total Pembayaran = Nilai Uang Tertinggi pada Total PembayaranHarga Terendah dari Total Pembayaran = Nilai Uang Terendah pada Total Pembayaran
Penerapan Fungsi TEXT (LEFT,RIGHT, MID, UPPER, LOWER, PROPER, LEN, CONCATENATE)
Arzaki Arif Nc., S.T. – KKPI (Microsoft Office Excel) - SMK Negeri 3 Pacitan
KE T EN T U A N PEN GI S I A N :Gabung Nama : Gabungkan nama depan dan nama belakang (tambahkan spasi sebagai pemisah nama depan dan nama belakang) Jumlah Karakter Nama Siswa : Hitung jumlah karakter nama siswa (termasuk spasi)Huruf Kecil : Ubah Nama Siswa menjadi huruf kecil semua
Huruf Kapital : Ubah Nama Siswa menjadi huruf kapital semuaHuruf Kapital Setiap Awal Kata Nama : Ubah Nama Siswa menjadi huruf kapital pada setiap awal kata namaPengambilan Bagian Nama Depan : Ambil dari depan 2 karakter nama siswaPengambilan Bagian Nama Tengah : Ambil 2 karakter nama siswa dimulai dari karakter ke-7Pengambilan Bagian Nama Belakang : Ambil dari belakang 2 karakter nama siswa
Penerapan Fungsi COUNT, COUNTA, COUNTBLANK dan COUNTIF
Arzaki Arif Nc., S.T. – KKPI (Microsoft Office Excel) - SMK Negeri 3 Pacitan
Gunakan Formula yang sesuai dengan pertanyaannya !
Penerapan Fungsi SUMIF
Arzaki Arif Nc., S.T. – KKPI (Microsoft Office Excel) - SMK Negeri 3 Pacitan
Tidak semua pertanyaan menggunakan formula SUMIF. Sesuaikan dengan uraian yang ditanyakan ! Ingat Latihan sebelumnya !
Penerapan Fungsi IF (sampai dengan 3 kondisi)
Arzaki Arif Nc., S.T. – KKPI (Microsoft Office Excel) - SMK Negeri 3 Pacitan
KETEN TU AN PENGI SI AN :
STATUS PEMINJAM :Bila Kode Peminjam L, maka “PEMINJAM LAMA”Bila Kode Peminjam B, maka “PEMINJAM BARU”
JENIS BUKU :Jika Kode Buku MTK, maka “MATEMATIKA”
Jika Kode Buku BIG, maka “BAHASA INGGRIS”
Jika Kode Buku BIN, maka “BAHASA INDONESIA”
LAMA PINJAM : Jumlah lama hari peminjaman
DENDA : Akan dikenakan denda Rp. 1.000/hari, bagi yang Lama Pinjamnya l e b i h d a ri 7 h a ri
Penerapan Fungsi Kombinasi IF (5 kondisi) dan TEXT
Arzaki Arif Nc., S.T. – KKPI (Microsoft Office Excel) - SMK Negeri 3 Pacitan
KE T EN T U A N PEN GI S I A N :
Jenis Kendaraan :Jika Kode Kendaraannya DH maka DAIHATSU Jika Kode Kendaraannya TY maka TOYOTA Jika Kode Kendaraannya IZ maka ISUZUJika Kode Kendaraannya SZ maka SUZUKI
Tahun KendaraanJika Kode Kendaraannya 20 maka 2000Jika Kode Kendaraannya 21 maka 2001Jika Kode Kendaraannya 22 maka 2002Jika Kode Kendaraannya 23 maka 2003Jika Kode Kendaraannya 24 maka 2004
Harga PokokJika Jenis Kendaraanya SUZUKI maka Harganya Rp. 35.000.000,00Jika Jenis Kendaraanya DAIHATSU maka Harganya Rp. 45.000.000,00Jika Jenis Kendaraanya ISUZU maka Harganya Rp. 50.000.000,00Jika Jenis Kendaraanya TOYOTA maka Harganya Rp. 60.000.000,00
DiskonDiskon sebesar 5% dari Harga Pokok akan diberikan apabila JenisKendaraannya TOYOTA
Total Harga : Nilai Uang yang harus dibayarkan setelah mendapatkan diskon.
Penerapan Fungsi IF(AND) dan IF(OR)
KETENTU AN PE NGIS I AN :
LAMA INAP = Jumlah hari lamanya menginap.
BIAYA/MALAM :Rp. 200.000 untuk kelas VIP Rp. 150.000 untuk kelas IA Rp. 125.000 untuk kelas IB Rp. 100.000 untuk kelas IIA Rp. 75.000 untuk kelas IIB
DISCOUNT = akan mendapatkan Discount 15% dari Total Biaya, apabila menginap di KelasVIP da n Lama Inap 10 hari atau lebih
BONUS = Akan diberikan bonus berupa VOUCHER apabila menginap dikelas VIP atauLama Inap lebih dari 7 hari
KETERANGAN = Berikan Keterangan PRIORITAS bila menginap dikelas VIP
Penerapan Fungsi TIME dan ABSOLUTE CELL
PETU N J UK :Lama Rental = Lamanya penggunaan komputer di rental internet
Biaya = Uang yang harus dibayar oleh pengguna internet.
Total Pendapatan = Jumlah uang yang dihasilkan
Penerapan Fungsi VLOOKUP dan HLOOKUP (dikombinasi fungsi pemenggalan text)
KODE SU JS TSJENIS BARANG SEPATU JEANS T-SHIRTDISC.KHUSUS 5 % 10 % 15 %
No. Kode MerkBarang
NamaBarang
JumlahTerjual
HargaSatuan
HargaTotal
DiscountKhusus
TotalBayar Bonus
1 SU-NKE 182 JS-WCT 193 TS-HMR 224 SU-NKE 1235 TS-HMR 346 SU-ADS 787 JS-WCT 498 SU-NKE 969 JS-HMR 10010 TS-WCT 123
Jumlah total Sepatu yang terjual Jumlah Harga Total jenis SepatuJumlah total Jeans yang terjual Jumlah Harga Total jenis JeansJumlah total T-Shirt yang terjual Jumlah Harga Total jenis T-ShirtJumlah total jenis NIKE yang terjual Jumlah Harga Total jenis NIKEJumlah total jenis ADIDAS yang terjual Jumlah Harga Total jenis ADIDASJumlah total jenis DIADORA yang terjual Jumlah Harga Total jenis DIADORAJumlah total jenis HAMMER yang terjual Jumlah Harga Total jenis HAMMERJumlah total jenis WATCH-OUT yang terjual Jumlah Harga Total jenis WATCH-OUT
Database :
KODE MERK BARANG HARGANKE NIKE 250.000ADS ADIDAS 200.000DDA DIADORA 120.000HMR HAMMER 100.000WCT WATCH-OUT 95.000
Akan mendapat Bonus berupa TOPI, untuk pembelian jenis barang SEPATU dan Nama Barang ADIDAS
Konversi dari Angka ke Huruf(kombinasi VLOOKUP, LEFT, MID, RIGHT, CONCATENATE)
Tabel Data diketik di sheet2
Arzaki Arif N. – KKPI SMKN 3 Pacitan – Microsoft Office Excel
Se bagia n Formul a Mic rosoft Exce l (un tu k d ipe laja ri seb aga i bah an Ujia n Su ma tif Se me ste r Gan jil ) :
=SUM(Range) : untuk m en jum l ah k a n data yang terdapat dalam range.
=MAX(Range) : untuk menentukan nilai tertinggi dari data yang terdapat dalam range
=MIN(Range) : untuk menentukan nilai terendah dari data yang terdapat dalam range
=AVERAGE(Range) : untuk menentukan nilai rata-rata dari data yang terdapat dalam range (hanya untuk data numerik/angka)
=AVERAGEA(Range) : untuk menentukan nilai rata-rata dari data yang terdapat dalam range (untuk data numerik/angka maupun data string/non angka)
Bandingkan hasilnya, jika menggunakan AVERAGE !
=LEFT(Cell;Jumlah karakter) : untuk mengambil sebagian karakter dalam Cell penghitungan dari sebelah kiri
=RIGHT(Cell;Jumlah karakter) : untuk mengambil sebagian karakter dalam Cell penghitungan dari sebelah kanan
=MID(Cell;No.Urut Karakter;Jumlah Karakter) : untuk mengambil sebagian karakter dalam Cell yang berada diantara teks (penghitungan no. urut karakter dari sebelah kiri)
=UPPER(Cell) : untuk mengubah teks dalam cell ke bentuk teks huruf kapital semua
=LOWER(Cell) : untuk mengubah teks dalam cell ke bentuk teks huruf kecil semua
=PROPER(Cell) : untuk mengubah teks dalam cell ke bentuk teks huruf kapital hanya pada huruf pertama masing-masing kata
=LEN(Cell) : untuk menghitung jumlah karakter dalam Cell
Arzaki Arif N. – KKPI SMKN 3 Pacitan – Microsoft Office Excel
=CONCATENATE(Cell1;Cell2;….;dst) : untuk menggabungkan isi dalam Cell
Jika ingin memberikan jarak antar kata ditambahkan“s pa s i “ diantara cell !
=COUNT(Range) : untuk m en g h it u n g banyak data yang terdapat dalam range(hanya untuk data numerik/angka)
Range yang digunakan adalah kolom Nil a i , karena berupa data numerik !
=COUNTA(Range) : untuk m en g h it u n g banyak data yang terdapat dalam range(untuk data numerik/angka maupun data string/non angka)
Bisa menggunakan Range kolom N a ma S i s w a , karena bisa membaca data string/non numerik !
=COUNTBLANK(Range) : untuk m en g h it u n g banyak data kosong yang terdapat dalam range
Kosong ≠ Nol (0)
=COUNTIF(Range;”Kriteria”) : untuk untuk me ngh i t un g banyak data yang terdapat dalam range tertentu berdasarkan kriteria/kondisi tertentu.
=SUMIF(Range;”Kriteria”;Range Dijumlah) : untuk m en jum l ah k a n seluruh data numerik/angka yang sesuai kriteria/kondisi tertentu
Arzaki Arif N. – KKPI SMKN 3 Pacitan – Microsoft Office Excel
Nilai Nilai Huruf Predikat85 – 100 A Sangat Memuaskan75 – 84 B Memuaskan60 – 74 C Cukup
<60 D Mengulang
Se bagia n Formul a Mic rosoft Exce l (un tu k d ipe laja ri seb aga i bah an Ujia n Su ma tif Se me ste r Ge nap ) :
BENTUK UMUM FU NGS I “IF” (2 k ondis i)
=IF(logika1;kondisi1;kondisi2)
maksudnya apabila log i k a 1 s e s uai , maka perintah pada k on d i s i 1 yang akan dimunculkan dalam Cell dan jika logi k a 1 ti d a k s e s u a i , maka perintah pada k o n di s i2 yang akan dimunculkan dalam Cell.
Untuk mengisi Kolom Keterangan (cell C2), Jika Nilai lebih dari atau sama dengan 60, maka LULUS, dan jika kurang dari 60 maka GAGAL
= IF(B2>=60;"LULUS";"GAGAL")atau := IF(B2<60;"GAGAL";"LULUS")
Catatan: K on d isi yang berupa data n u mer i k jangan menggunakan tanda kutip dan sebaliknya apabila kond i si yang berupa
data st r ing/n o n -nu m erik harus selalu berada dalam tanda kutip.
IF BERCABA NG (4 kondisi)
=IF(logika1;kondisi1;IF(logika2;kondisi2;IF(logika3;kondisi3;kondisi4)))
Formula untuk mengisi Kolom N ilai H u r uf (cell D2):=IF(B2<60;"D";IF(B2<=74;"C";IF(B2<=84;"B";"A")))
Untuk Mengisi Kolom Nilai Huruf dan PredikatBerdasar tabel berikut :
Formula untuk mengisi Kolom Pr edikat (cell E2):=IF(D2="D";"Mengulang";IF(D2="C";"Cukup";IF(D2="B";"Memuaskan";"Sangat Memuaskan")))
IF K OM B I NAS I F UN G S I T EX T (lef t ,mid, r igh t )
Untuk Mengisi Kolom MERK dan Processor dan Produk,dengan ketentuan :MERK
Jika Kode AC, maka ACER Jika Kode AX, maka AXIOO Jika Kode TO, maka TOSHIBA
PROCESSORJika Kode DC, maka Dual CoreJika Kode C2, maka Core2Duo
PRODUKJika Kode I, maka INTELJika Kode A, maka AMD
Maka,Formula untuk mengisi Kolom Merk (cell C3):=IF(LEFT(B3;2)="AC";"Acer";IF(LEFT(B3;2)="AX";"Axioo";" Toshiba "))
Formula untuk mengisi Kolom Processor (cell D3):=IF(MID(B3;4;2)="DC";"Dual Core";"Core2Duo")
Formula untuk mengisi Kolom Produk (cell E3):=IF(RIGHT(B3;1)="I";"Intel";"AMD")
Arzaki Arif N. – KKPI SMKN 3 Pacitan – Microsoft Office Excel
IF DENGAN 2 LOG IKA
IF(OR) : jika salah satu logika saja sesuai, maka perintah pada k o n di s i1 akan dimunculkan dalam Cell.
=IF(OR(logika1;logika 2);kondisi1;kondisi2)
Untuk mengisi kolom Discount (cell E2) :akan mendapat discount 10%, jika Merk Adidas atau Jenisnya Topi.
=IF(OR(B2="Adidas";C2="Topi");10%*D2;0)
IF(AND) : jika semua logika sesuai, maka perintah pada k on d i s i1 yang akan dimunculkan dalam Cell.
=IF(AND(logika1;logika 2);kondisi1;kondisi2)
Untuk mengisi kolom Discount (cell E2):akan mendapat discount 10%, jika Merk Adidas dan Jenisnya Topi.
=IF(AND(B2="Adidas";C2="Topi");10%*D2;0)
H LO O KU P dan V L O O KU P (kombinasi Fungsi TEXT)
HLOOKUP : formula ini digunakan untuk pembacaan tabel data yang disusun secara Horizontal
=HLOOKUP(cell kunci;range tabel data;nomer urut posisi baris)
VLOOKUP : formula ini digunakan untuk pembacaan tabel data yang disusun secara Vertikal
=VLOOKUP(cell kunci;range tabel data;nomer urut posisi kolom)
Untuk “r a n g e tab e l d a t a ” harus d i - a bso l ut - k a n (pemberian tanda $), cara yang paling sederhana dengan menekan tombol F4, setelah menge-blok range tabel data.
Formula untuk mengisi Kolom Kode Wilayah (cell C3):=HLOOKUP(LEFT(B3;4);$C$11:$F$12;1)
Formula untuk mengisi Kolom Nama Wilayah (cell D3):=HLOOKUP(LEFT(B3;4);$C$11:$F$12;2)
Jika tabel data disusun secara Horizontalseperti ini, maka menggunakan HLOOKUP
Arzaki Arif N. – KKPI SMKN 3 Pacitan – Microsoft Office Excel
Jika tabel data disusun secara Vertikal seperti ini, maka menggunakan VLOOKUP
Formula untuk mengisi Kolom Kode Wilayah (cell C3):=VLOOKUP(LEFT(B3;4);$F$3:$G$6;1)
Formula untuk mengisi Kolom Nama Wilayah (cell D3):=VLOOKUP(LEFT(B3;4);$F$3:$G$6;2)
kutipan kata-kata bijak