A.
Mengenal Microsoft Excel 2016
Microsoft Excel merupakan program aplikasi lembar kerja
(spreadsheet) yang yang biasa digunakan untuk mengolah data
secara otomatis. Data yang diolah berupa perhitungan dasar,
rumus, pemakaian fungsi atau formula, pembuatan table dan
grafik, serta manajemen data. Microsoft
Excel bekerja menggunakan workbook yang di dalamnya terdapat worksheet
yang terdiri dari kolom dan baris.
Gambar 1. Jendela Kerja Microsoft Excel 2016
B. Komponen Microsoft Excel 2016
1. Title Bar
Merupakan baris judul yang menunjukan nama dari file. Nama ini kita
berikan ketika menyimpan file tersebut di komputer (Save As).
2. Menu Bar
Menu Bar berisi beberapa menu utama dimana menu-menu ini
membawahi banyak menu lain yang dilengkapi dengan icon-icon untuk
memudahkan penggunaan.
3. Ribbon Menu
Ribbon Menu merupakan fasilitas yang terdapat di dalam Menu Bar yang
berisi tools yang dikelompokan berdasarkan fungsi-fungsinya.
4. Name Box
Name Box atau Nama Kotak. Dalam lembar kerja pada Excel terdiri dari
kotak-kotak atau blok (Cell) dan masing-masing kotak tersebut
mempunyai nama sesuai posisinya.
Posisi koordinat pada masing-masing kotak terdiri antara Nama Kolom
dan Nama Baris. Misal kita sorot Cell G12 yang artinya kotak tersebut
berada pada Kolom G dan Baris 12. Cell atau sel sendiri berarti titik
pertemuan antara Baris dan Kolom
5. Formula Bar
Kotak persegi panjang yang berfungsi untuk menampilkan dan mengedit
isi dari sebuah sel yang sedang aktif. bagian ini juga difungsikan sebagai
tempat memasukkan rumus serta untuk mengedit dan memperbaiki
rumus pada excel.
6. Column Name (Nama Kolom)
Kolom merupakan bagian yang melintang vertikal ke atas dan ditandai
dengan huruf A, B, C dan seterusnya sampai XFD. Jumlah dari Kolom
adalah 16.384 Kolom
7. Row Name (Nama Baris)
Row atau Baris adalah bagian dari excel yang melintang horisontal ke
samping dan ditandai dengan nomor angka 1, 2, 3 sampai 1.048.576
8. Jenis-Jenis Pointer dalam Ms. Excel 2016
Pointer ini dipergunakan untuk
menyorot/mengeblok cell Pointer ini
dipergunakan untuk memindahkan isi cell
Pointer ini dipergunakan u resize lebar ataupun tinggi
suatu cell Pointer ini dipergunakan untuk mengcopy
nilai di suatu cell
9. Menggabungkan Cell
Blok cell-cell yang ingin digabungkan (cth : cell B2 s/d H2)
Pilih merge dan center
10. Penggunaan Format Cell
Blok cell yang ingin di format
Klik kanan -> Format cell atau pilih
11. Fungsi Statistik
Berikut ini Berikut ini beberapa fungsi statistik yang disediakan oleh Excel
2016.
=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)
=Sumif (Range) Menghitung jumlah nilai pada range dengan kriteria tertentu
(cth:
menghitung total nilai harga dari seluruh harga baju batik
saja)
dll
C. Latihan Soal
1. Latihan Pertama
Ketentuan:
a. Hitung Jumlah bayar, dimana 1 lusin adalah 12 pcs
harga (pcs) X jumlah pengiriman X 12
b. Hitung sub total dengan
= SUM ( range ) = SUM(H10 :H19)
Trik Untuk lebih memudahkan serta menghindari kesalahan, kita dapat
menyorot (memblok cells) yang ingin di jumlahkan dalam soal
ini adalah H10 s/d H19
c. Hitung Nilai PPN sebesar 2% dari sub total ( =2%* H20 )
d. Hitung Total Bayar, yang didapat dari Sub Total – PPN (=H20 + H21)
2. Latihan Kedua
Tambahkan 1 kolom disebelah kolom Nama Barang, dan beri Tittle Jenis
Pakaian
Blok seluruh kolom pada kolom F (dalam hal ini harga)
Klik kanan , pilih Insert
Kemudian rapihkan seperti gambar :
3. Latihan Ketiga
Isikan kolom Jenis barang berdasarkan 1 karakter pertama dari kode
barang (pergunakan fungsi String)
Ada 3 jenis fungsi String
Jenis
Keterangan Rumus
String
LEFT Pengambilan String/ karakter
dari =Left (text;[num_chars])
sebelah kiri
MID Pengambilan String/ karakter
dari = Mid (text; [start_num];
[num_chars]
tengah, sebanyak n karakter
RIGHT Pengambilan String/ karakter
dari =Right (text;[num_chars])
sebelah kanan
Karena pengisian pada kolom Jenis pakaian akan diisikan dengan 1
karakter pertama yang diambil dari kode barang (A,B,K & M), maka
fungsi yang digunakan adalah Left
4. Latihan Keempat
Tambahkan table berikut ini, dan hitunglah harga termurah, harga
termahal dan rata-rata harga pakaian. Serta hitung pula jumlah
masing-masing jenis baju.
Ketentuan:
Harga termurah = MIN(G10:G19)
Harga termahal = MAX(G10:G19)
Rata-rata harga = AVERAGE(G10:G19)
Jumlah baju batik = COUNTIF(F10:F19;"B"), lakukkan hal yang sama pada jenis
pakaian yang lain.
5. Latihan Kelima
Tambahkan table seperti gambar dibawah ini
Kemudian isikan kolom jenis pakaian dengan ketentuan :
Jika kode Barang A, Maka Jenis pakaian adalah Pakaian Anak
Jika kode Barang B, Maka Jenis pakaian adalah Pakaian Batik
Jika kode Barang K, Maka Jenis pakaian adalah Kerudung
Jika kode Barang M, maka Jenis pakaian adalah Pakaian Muslim
Ketentuan:
Untuk soal yang menggunakan kondisi, kita dapat menggunakan fungsi IF
IF tunggal, dengan bentuk umum
=IF (logical_test;[value_if_true];[value_if_false])
Cth : Jika keterangan lulus maka dapat sertifikat, jika tidak maka
tidak dpt sertifikat
=IF(cell_kondisi=”lulus”;”dapat setifikat”;”tidak dapat sertifikat”)
IF majemuk, dengan bentuk umum
=IF(logical_test_1;[value_if_true]; IF(logical_test_n;[value_if_true]…
[value_if_false]))
Karena kondisi pd soal diatas ada 4 kondisi (Jika A ,Jika B, Jika K & Jika
M), maka IF yang digunakan adalah sebanyak 3 (Jumlah IF = Jumlah
kondisi – 1)
=IF(D28="A";"Anak";IF(D28="B";"Batik";IF(D28="K";"Kerudung";"Muslim")))
6. Latihan Keenam (Fungsi LookUp)
Fungsi VlookUp merupakan, fungsi yang digunakan untuk membaca data
yang berada pada suatu tabel yang disusun secara vertikal
Bentuk umum
= Vlookup(LookUp_value;table_array;col_index_num;[range_LookUp])
Atau dapat juga seperti ini :
=Vlookup(Nilai_Kunci;Range_table;index_kolom;range_lookup_value)
7. Latihan Ketujuh (Fungsi HlookUP)
Fungsi HlookUp merupakan, fungsi yang digunakan untuk membaca data
yang berada pada suatu tabel yang disusun secaraHorizontal
=Hlookup(Nilai_Kunci;Range_table;index_Baris;range_lookup_value)
Buatlah tampilan sbb:
Untuk pengisian kolom Judul Novel dan Harga dapat kita gunakan fungsi
Vlookup
=VLOOKUP(B3;$B$13:$D$17;2;FALSE)
B3 Nilai Kunci, Cell yang menjadi acuan pencarian/pencocokkan.
$B $ 13 : $ D $ 17 range kunci/ tabel induk yang akan diambil nilainya,
dalam hal ini, seluruh tabel induk (mulai dari cell B13
s/d D17) kemudian di absolutkan (ditandai dengan
simbol $) dengan cara menekan tombol F4 setelah
tabel induk tsb di blok.
2 Nilai kolom yang akan diambil, judul novel merupakan kolom kedua
yang diblok.
False merupakan value lookupnya. False dikarenakan data tdk terurut.
8. Pembuatan Chart / Grafik
Dengan menggunakan [Link] kita bisa membuat grafik atau chart
erdasarkan data tertentu.
Contoh, kita ingin membuat grafik dari data pengunjung dari table diatas.
Dengan cara memblok data yang kita akan grafikkan kemudian pilih
charts pada bagian toolbar dibagian menu insert.
Setelah dipiliih bentuk chart yang kita inginkan, maka akan tampil chart
sbb:
Tugas 1 :
Ketentuan Soal :
Kerjakan soal diatas dengan menggunakan rumus yang sesuai dengan kolom-
kolom.
Tugas 2 :
Nilai Ujian
No Nama Siswa Rata-rata
Matematika Fisika Kimia Biologi
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
Ketentuan Soal :
Kerjakan soal diatas dengan menggunakan rumus yang sesuai dengan kolom-
kolom
Tugas 3 :
PT. ARSEL SEJAHTERA
ABADI DAFTAR UPAH
KARYAWAN BULAN APRIL
2022
UPAH TOTAL
JAM JAM UPAH TOTAL
NO NAMA KERJA PAJAK UPAH
KERJA LEMBUR LEMBUR UPAH
(KOTOR) (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
25000 Upah Lembur = Jam Lembur
x 30000
Total Upah = Upah Kerja + Upah
Lembur Pajak = Total Upah x 5%
Total Upah (Netto) = Total Upah – Pajak
Hitung :
1. Upah Kerja (Kotor)
2. Upah Lembur
3. Total Upah
4. Pajak
5. Total Upah
6. Total Upah Seluruh Karyawan
7. Rata-rata Upah Seluruh Karyawan
8. Upah Tertinggi Karyawan
9. Upah Terendah Karyawan
Tugas 4 :
FUNGSI IF TUNGGAL DAN IF MAJEMUK
No Nama Keahlian Nilai Grade Status
1 Jack William Software Enggineering 60
2 Billy Darthmounth Requirement Engineering 90
3 Mcfaden Franklin Multivariate Calculus 34
4 Steven Shwimmer Software Architecture 96
5 Ruby Jason Relational DBMS 70
6 Mark Dyne PHP Develoment 34
7 Philip Namdaf Microsoft Dot Net Platform 78
8 Erik Bawn HTML & Scripting 87
9 Ricky Ben Data Communication 78
10 Miecky Esmeralda Computer Network 89
Ketentuan Soal :
Grade A = Nilai 90 – 100
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 Complete
Jika Grade < 75, maka status Failed
Tugas 5 :
MENGHITUNG HARGA JUAL BUKU
Harga Harga
No Judul Buku Kategori Buku Diskon Jual
1 Tips dan Trik Photoshop CS6 A 275000
2 Manajemen Website dan Web Server C 220000
3 Hacker dan Keamanan A 310000
4 Desain Web Praktis dengan Css A 400000
5 Animasi Iklan Flash untuk Pemula B 180000
6 Magic of Adobe After Effect C 150000
Kategori Diskon
A 25%
B 50%
C 75%
Ketentuan Soal :
Menggunakan VLOOKUP
Tugas 6 :
DATA GAJI PEGAWAI PT. ARSEL AFAREZEL
Bagian Operator
Gaji
Total Gaji
No Gol Nama Pegawai Gaji Pajak
Tunjangan Transportasi Gaji Bersih
Pokok
1 1C Albert Einstein
2 1B Aiko Fauta
3 1B Indrasta
4 1C Ivan Budiawan
5 1A Reveul
Total
Tabel Gaji Tabel Pajak
Gol Gaji Pokok Tunjangan Transportasi 1A 1B 1C
1A 600000 50000 100000 2% 3% 4%
1B 800000 70000 100000
1C 1000000 120000 100000
Ketentuan Soal :
1. Untuk gaji sesuai dengan golongan berdasarkan table gaji
2. Total Gaji = Gaji Pokok + Tunjangan + Transportasi
3. Pajak = Total Gaji x Pajak
4. Gaji Bersih = Total Gaji – Pajak
Tugas 7 :
LAPORAN NILAI MAHASISWA
SEMESTER GANJIL TAHUN AJARAN 2024/2025
Program Nama Mata Nilai
No Nim Nama Mahasiswa Kode Grade
Studi Kuliah Absen Tugas UTS UAS Akhir
1 19190067Anastasia Aku 120 80 85 80 85
2 19190061Anggello Yosua Gracio 738 100 80 90 95
3 19200781Arabbi Tuakia 745 90 90 95 90
4 19200219Aripin 120 85 95 60 86
5 19201039Arizal Apriliawan Putra 150 100 80 70 70
6 19200949Arlyn Arta Pradipa 745 75 85 80 75
7 19190057Asta Pratiwi 738 80 80 90 80
8 19200776Brinda Angela 120 90 90 60 85
9 19190080David Tamu 150 95 75 90 90
10 19200175Denys Setyawan 738 90 85 80 85
Rata-rata
Tertinggi
Resume Jumlah
Terendah
A
B
C
D
E
Ketentuan :
1. Jurusan mahasiswa didapat dari 2 karakter di awal
Jika 2 karakter diawal 11, maka jurusan KA
Jika 2 karakter diawal 12, maka jurusan MI
Jika 2 karakter diawal 13, maka jurusan TK
2. Nama Matakuliah didapat dari kode
Jika kode 738, nama matakuliah Visual Basic
Jika kode 120, nama matakuliah PPN I
Jika kode 745, nama matakuliah Foxpro
Jika kode 150, nama matakuliah Arsitektur Komputer
3. Nilai Akhir didapat dari = (10%*Absen)+(20%*Tugas)+(30%*uts)+(40%*uas)
4. 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
Jika besar sama dengan 65, maka grade C
Jika besar sama dengan 55, maka grade D
Selain itu grade E
5. Hitung pula jumlah mahasiswa yang bergrade A,B,C,D dan E
6. Hitunglah nilai terendah, tertinggi dan nilai rata-rata
Tugas 8 :
DAFTAR
PENGUNJUNG
CHAMP KARAOKE
[Link] NAMA JENIS LAMA HARGA JUMLAH SUB
KEL LA POTON
AN PENYEWA PENGUNJUNG SEWA SEWA/JAM BAYAR TOTAL
AS NT GAN
SEW
AI
A
VIP-02- UCUP 2
AT Jam
EXC-02- BEJO 3
BS Jam
VIP-01- UCRIT 1
BS Jam
EXC-01- NANO 1
AT Jam
VIP-03- NINA 2
BS Jam
EXC-01- YANTI 2
BS Jam
EXC-01- BAMB 1
AT ANG Jam
VIP-03- BARA 3
AT Jam
VIP-02- UDIN 4
BS Jam
EXC-03- SANTO 2
AT SO Jam
Kelas Harga Sewa/Jam
Kode Lantai Kelas
VIP EXC 01 02 03
01 Raja Executive I VIP 150000 120000 110000
02 Ratu Executive II EXC 100000 110000 115000
03 Mahkota Executive
Ketentuan soal :
Gunakan Fungsi VlookUp & HlookUP
Tugas 9 :
TABEL PENJUALAN BARANG
PT. ARSEL ALFAREZEL
HARGA HARGA JENIS NILAI
KODE BARANG JUMLAH JUAL NAMA BARANG SATUAN TOTAL BAYAR DISKON BERSIH BONUS
A001 5 Tunai
A002 10 Kredit
A003 2 Tunai
A004 18 Tunai
A005 16 Tunai
A002 15 Kredit
A005 12 Kredit
A003 6 Tunai
Nilai Bersih Tertinggi
Total Nilai Bersih Yang Jumlah Jualnya > 10
TABEL BARANG
KODE BARANG 001 002 003 004 005
NAMA BARANG FLASDISK MOUSE KEYBOARD HEADSET SPEAKER
HARGA SATUAN Rp75.000 Rp89.000 Rp150.000 Rp125.000 35000
Ketentuan Soal ;
1. Simpan Di folder nim masing-masing dengan nama Quiz A
2. Nama barang dan harga satuan ditentukan dari berdasarkan kode barang yang ada di tabel kode barang
3. Harga total diisi dengan hasil perkalian jumlah unit dan harga satuan
4. Discount diberikan :
jika jumlah jual lebih dari 5 dan jenis bayar Tunai maka discount 5 % dari
harga total jika jumlah jual lebih dari 10 dan jenis bayar Tunai maka
discount 10 % dari harga total jika jumlah jual lebih dari 15 dan jenis bayar
Tunai maka discount 15 % dari harga total
5. Nilai Bersih diisi dengan harga total dikurang discount
6. Bonus di berikan berupa Jam dinding jika jenis bayar Tunai
Tugas 10 :
TABEL PENJUALAN BUKU
TOKO BUKU ARSEL SENTOSA
KODE NAMA HARGA JUMLAH JUMLAH TOTAL
JENIS BUKU JUDUL BUKU DISKON BONUS
BUKU PENGARANG SATUAN BELI HARGA HARGA
BK01
BK02
BK03
BK01
BK01
BK03
BK05
BK04
JUMLAH
JUMLAH TOTAL HARGA YANG JUMLAH BELINYA > 10
KODE NAMA HARGA
JENIS JUDUL BUKU
BUKU PENGARANG SATUAN
BUKU
01 AGAMA Kisah Sufi Agung Faridudin Rp55.0
Aththar 00
02 HUMOR Humor Sufi Nasrudin Rp15.0
Affandi 00
03 NOVEL Air Mata dan Kahli Gibran Rp67.0
Senyuman 00
04 CERITA Loving U Merit Yuk O. Solihin Rp45.0
00
05 KESEHAT Pengobatan Islami H.M. Hembing Rp48.0
AN 00
Ketentuan Soal :
1. Simpan Di folder nim masing-masing dengan nama QuizB
2. Jenis Buku, Judul Buku Nama Pengarang dan Harga Satuan diambil dari Tabel Bantu dengan menggunakan
penggabungan fungsi lookup dan fungsi text.
3. Jumlah harga = harga satuan dikalikan banyak
4. Diskon
Jika jumlah beli lebih besar dari 5 maka diskon 5% dari jumlah harga
Jika jumlah beli lebih besar dari 10 maka diskon 10% dari jumlah harga
Jika jumlah beli lebih besar dari 12 maka diskon 15% dari jumlah harga
Selain itu tidak dapat diskon
5. Total harga = jumlah harga dikurang diskon
6. Bonus
Jika total harga lebih dari 750000 maka bonus Payung
Jika total harga lebih dari 600000 maka bonus Kaos
Jika total harga lebih dari 500000 maka bonus Kalender
Jika total harga lebih dari 250000 maka bonus PIN
Selain itu tidak dapat bonus