Sedikit tentang Penggunaan Fungsi/Formula/Rumus dari Ms. Excel

Fungsi Pembacaan Tabel :

VLOOKUP = membaca tabel lain secara vertikal sesuai dengan kunci yang dimilikinya
HLOOKUP = membaca tabel lain secara horizontal sesuai dengan kunci yang dimilikinya

=VLOOKUP(Lookup Value,Table Array, Column Index)
=HLOOKUP(Looukup Value, Table Array, Column Index)

Fungsi Statistika

Average = mencari nilai rata-rata
Max = mencari nilai maksimum
Min = mencari nilai minimum
STDEV = mencari nilai standar deviasi
Count = mencari jumlah data


Fungsi Matematika
Round = membulatkan suatu data
Sum = fungsi penjumlahan data
Int = membulatkan Nilai kebawah
Mod = mencari nilai sisa dari pembagian
Fact = mencari nilai faktorial

LKS 19

 

Filter Data tersebut dengan cara mengklik Sort and Filter. Lalu jika telah terfilter baru dipilih SMU, SARJANA S1, dan D3. Filter lagi Gaji Pokoknya.

LKS 18

Merek Televisi
=CONCATENATE(VLOOKUP(VALUE(LEFT(C8,1)),TBLMERKGARANSI,2,0)," ",VLOOKUP(MID(C8,2,2),TBLUKRNHARGA,2,0))

Cara beli
=IF(RIGHT(C8,1)="K","KREDIT","CASH")

Harga Satuan
=VLOOKUP(MID(C8,2,2),TBLUKRNHARGA,IF(LEFT(C8,1)="1",3,IF(LEFT(C8,1)="2",4,5)),0)

Biaya garansi
=VLOOKUP(VALUE(LEFT(C8,1)),TBLMERKGARANSI,3,0)*G8

Discount
=G8*D8*IF(AND(RIGHT(C8,1)="C",D8>=10,MID(C8,2,1)="C"),8%,IF(AND(RIGHT(C8,1)="C",D8>=10,MID(C8,2,1)="W"),15%,0))


Jumlah bayar
=((G8*D8)+H8)-I8

LKS 17

Departemen
=IF(LEFT(B5,2)="D1","DEP1",IF(LEFT(B5,2)="D2","DEP2",IF(LEFT(B5,2)="D3","DEP3")))

Bagian
=IF(LEFT(B5,2)="D1","PROCESSOR",IF(LEFT(B5,2)="D2","PACKING",IF(LEFT(B5,2)="D3","MARKETING")))

Tahun Masuk
=1900+VALUE(MID(B5,4,2))

Lama Kerja
=2011-F5

Gaji Pokok
=IF(LEFT(B5,2)="D1",9000000,IF(LEFT(B5,2)="D2",1200000,IF(LEFT(B5,2)="D3",1300000)))

Tunjangan jabatan
 =IF(G5<=9,SUM(H5)*15,IF(G5>10,SUM(H5)*20%))

Total Pendapatan
=H5+I5

LKS 16

Jam tiba didapatkan dengan fungsi
=SUM(D7,IF(LEFT(C7)=1,4,IF(LEFT(C7)=2,7,IF(LEFT(C7)=3,9))))

Nama kereta menggunakan fungsi VLOOKUP
=VLOOKUP(VALUE(LEFT(C7,1)),$B$26:$E$28,IF(MID(C7,2,1)="A",2,IF(MID(C7,2,1)="B",3,4)),0)

Jurusan
=CONCATENATE(VLOOKUP(MID(C7,2,1),$L$25:$M$27,2,0),"-",(VLOOKUP(RIGHT(C7),$L$25:$M$27,2,0)))

Harga Tiket Dewasa
=H7*HLOOKUP(J7,$H$25:$J$27,3,0)

Harga Tiket Anak-anak
=I7*HLOOKUP(J7,$H$25:$J$27,2,0)


Jumlah bayar didapat dari
harga Tiket Dewasa + Harga Tiket Anak
=K7+L7

LKS 15

Jenis dan nama film diperoleh dengan menggunakan fungsi
=CONCATENATE(IF(MID(A6,1,1)="A","ACTION",IF(MID(A6,1,1)="C","CARTOON",IF(MID(A6,1,1)="K","KOMEDI",IF(MID(A6,1,1)="M","MUSIC",IF(MID(A6,1,1)="R","ROMANCE"))))),"/",VLOOKUP(VALUE(MID(A6,5,1)),$H$7:$I$15,2,0))

Discount gunakan fungsi
=IF(MID(A6,4,1)="B",IF(MID(A6,2,2)>90,SUM(VLOOKUP(VALUE(MID(A6,5,1)),$H$7:$K$15,4,0))*15%,0)*IF(MID(A6,4,1)="B",IF(MID(A6,2,2)<=90,SUM(VLOOKUP(VALUE(MID(A6,5,1)),$H$7:$K$15,4,0))*25%)))+REPLACE(FALSE,1,5,0)

Jumlah gunakan fungsi
=VLOOKUP(VALUE(MID(A6,5,1)),H$7:K$15,4,0)-D6

Untuk mengetahui tentang keterangan gunakan Fungsi
=CONCATENATE(IF(MID(A6,4,1)="S","SEWA","BELI"),"(",IF(MID(A6,6,1)="A","2 HARI",IF(MID(A6,6,1)="B","3 HARI",IF(MID(A6,6,1)="C","5 HARI"))),")")

LKS 14

Nama barang menggunakan fungsi membaca tabel secara vertikal
=IF(ISNA(VLOOKUP(B9,THD,2,0)),VLOOKUP(B9,THC,2,0),VLOOKUP(B9,THD,2,0))

Harga satuan juga diperoleh dari Tabel Yang ada
=IF(ISNA(VLOOKUP(B9,THD,3,0)),VLOOKUP(B9,THC,3,0),VLOOKUP(B9,THD,3,0))

Kualitas
=IF(ISNA(VLOOKUP(B9,THD,4,0)),VLOOKUP(B9,THC,4,0),VLOOKUP(B9,THD,4,0))

Total merupakan hasil kali antara harga satuan dengan jumlah barang
=D9*F9

Discount
i. Disket Fuji 15%
ii. Disket Maxcel 12,5%
iii. Disket 3M 7%
iv. Disket Sony 10%

Fungsi Discount
=VLOOKUP(E9,DISCOUNT,2,0)*G9

Bayar diperoleh dari Total dikurangi Discount
=G9-H9

LKS 13

Untuk mengetahui Nama Barang gunakan formula =CONCATENATE(VLOOKUP(LEFT(B5,3),N19:O21,2,0),"  ",VLOOKUP(MID(B5,5,3),$J$19:$K$23,2,0))

Untuk mengetahui Harga Satuan =VLOOKUP(MID(B5,5,3),$J$19:$L$23,3,0)

Jumlah barang =MID(B5,9,2)

Total Harga diketahui dari Harga Satuan * Jumlah Barang =D5*E5

Discount didapat dari fungsi IF
=IF(AND(E5>10,OR(VLOOKUP(MID(B5,5,3),$J$19:$K$23,2,0)="ADIDAS",VLOOKUP(MID(B5,5,3),$J$19:$K$23,2,0)="HAMMER")),SUM(F5)*10%,0)

Harga yang dibayar didapat dari Total Harga dikurangi Discount =F5-G5

LKS 12

Jenis Apartemen =VLOOKUP(VALUE(MID(B7,2,1)),$A$31:$C$33,2,0)
Developer =HLOOKUP(LEFT(B7,1),$G$30:$I$31,2,0)
Jumlah Penjualan =VALUE(MID(B7,2,2))
Harga Apartemen =VLOOKUP(VALUE(MID(B7,2,1)),$A$31:$C$33,3,0)
Bonus =IF(F7>25,6%*G7*F7,IF(F7<12,3.5,IF(F7>=12,3.5%*G7*F7)))
Komisi Tetap =VALUE(G7/5000000)*HLOOKUP(E7,$G$31:$I$32,2,0)
Jumlah Pendapaatan =SUM(H7:I7)

LKS 11

 

Jenis Buku =IF(D9="AG","Agama",IF(D9="FS","Fiksi",IF(D9="IL","Ilmiah",IF(D9="ST","Sastra"))))

Harus Kembali =F9+E5

Terlambat =E4-G9

Denda =IF(D9="IL",250,IF(D9="FS",200,IF(D9="ST",175,IF(D9="AG",750))))*H9

LKS 10

 

Lewat hari menggunakan formula =A3-A9
Denda bunga =B9*A4*C9
Saldo piutang =B9+D9
Keterangan =IF(C9>90,"SEGERA TAGIH",IF(C9>=30,"TAGIH","_"))

LKS 9

 

Pada LKS 9 ini yang diminta adalah grafiknyak maka Klik insert, chart, pilih grafik contohnya Pie masukan data yang diblok (C6:H10) lalu pilih format data labels dan pilih percentage untuk menggunakan persen dalam grafik tersebut.

LKS 8

a.       jika jenis kamar VIP dan jumlah tarif>= 3500000 , maka besar discount 10 % dari jumlah tarif
b.      jika jenis kamar kelas I dan jumlah tarif >=2500000 , maka besar discount 6 % dari jumlah tarif 
c.       jika jenis kamar kelas II dan jumlah tarif 1500000 , maka besar discount 4 % dari jumlah tarif 
d.      Selain diatas , besar discount =2 % dari jumlah tarif
e.      Kolom tagihan diisi dengan jumlah tarif dikurangi dengan beasr discount

Jenis Kamar diperoleh menggunakan fungsi =VLOOKUP(C5,tarif,2)
Lama inap yaitu Tanggal keluar - tanggal masuk
Tarif Malam =VLOOKUP(C5,tarif,3)
Jumlah Tarif =H5*G5
Discount gunakan fungsi  =IF(AND(D5="VIP",I5>=3500000),10%*I5,IF(AND(D5="kelas I",I5>=2500000),6%*I5,IF(AND(D5="kelas II",I5=1500000),4%*I5,2%*I5)))
Tagihan gunakan formula Jumlah tarif - Discount

LKS 7

 

Nama depan gunakan rumus =LEFT(C6,FIND(" ",C6)) lalu enter dan blok hingga kebawah.

Angkatan diperoleh dari Nomor Induk Mahasiswa =1900+value(mid(cell,nomor induk))

atau fungsinya yaitu =1900+VALUE(MID(B6,3,2))

Jurusan juga diperoleh dari Nomor Induk mahasiswa. Gunakan rumus =VLOOKUP(LEFT(B6,2),jurusan,2,0)

Jumlah  SKS diketahui dari Jurusan. Gunakan fungsi =IF(G6<2,16,IF(G6<3,20,24))

Uang Kuliah menggunakan fungsi =IF(LEFT(B6,2)="EK",2000000,IF(LEFT(B6,2)="HK",2100000,IF(LEFT(B6,2)="SP",1800000,IF(LEFT(B6,2)="TS",2500000,2800000))))

Terakhir Biaya seluruhnya diketahui dari =I6+(VLOOKUP(LEFT(B6,2),jurusan,3,0)*H6)

VLOOKUP berfungsi membaca data yang ada pada tabel dibawah itu.

LKS 6

Kamar kelas gunakan fungsi if. Rumusnya adalah =IF(C7="V","VIP",IF(C7="U","UTAMA","EKONOMI")) lalu enter dan blok hingga ke bawah.
Lama Tinggal diperoleh dari keluar - tanggal masuk   =F7-E7
Tarif kamar diperoleh dari fungsi =IF(C8="V",150000,IF(C8="U",120000,75000))
Discount gunakan fungsi =IF(G7>=5,15%,5%)*(H7*G7)
Biaya dibayar gunakan fungsi =H7*G7-I7 (tarif kamar * lama tinggal - discount)

LKS 5

 

Untuk mengisi kolom jumlah, masih dengan fungsi yang sama yaitu fungsi Autosum.

Untuk mengisi Rata-rata gunakan fungsi Average  =AVERAGE(sel berapa:sel berapa)

Untuk mengisi Maximum gunakan fungsi MAX =MAX(sel berapa:sel berapa)

Untuk mengisi Minimum gunakan fungsi MIN =MIN(sel berapa:sel berapa)

 

LKS 4

Kolom gaji diisi dengan fungsi pembulatan (ROUND) jadi rumusnya adalah =ROUND(B8,-2) lalu enter dan blok hingga ke bawah.
Sedangkan untuk mencari jumlah lembar uang digunakan rumus seperti di bawah ini
i. Untuk kolom lembar Rp 10.000 dengan rumus =Int(sel gaji,10000)
ii. Untuk kolom lembar Rp 1000 dengan rumus =Int(mod(sel gaji,10000)/1000) 
iii. Untuk kolom lembar Rp 100 dengan rumus =Int(mod(sel gaji,1000)/100) 


Untuk jumlah gunakan fungsi Autosum.

LKS 3

Tunjangan diisi dengan menggunakan rumus =B10*10%       lalu enter dan blok hingga ke bawah
Gaji Kotor diisi dengan rumus =B10+C10 lalu enter dan blok hingga ke bawah
Potongan merupakan 6% dari Gaji pokok =B10*6%      lalu enter dan blok hingga ke bawah
Gaji Bersih merupakan Gaji kotor - potongan  =D10-E10      lalu enter dan blok hingga ke bawah
Untuk Jumlah blok sesuai dengan baris lalu klik Autosum.

LKS 2

 

 Pembahasan LKS 2 hampir sama dengan LKS 1 dengan menggunakan rumus Autosum.

Jumlah dicari dengan ikon sigma. Blok nilai yang tertera dari ...6 sampai ....6  (.... adalah kolom) lalu klik ikon sigma. Lakukan berulang sampai semua jumlah sesuai dengan kode mobil terisi. Untuk total juga menggunakan rumus =SUM (autosum). Caranya blok nilai penjualan mobil sesuai dengan barisnya.

Buat Grafik sesuai dengan data diatas. Pertama, blok data tersebut dari C6 sampai E12. Lalu, klik Insert -> Chart -> Pilih Grafik contohnya Pie -> Format Data Labels -> Label Options  -> Klik percentage lalu CLOSE.

LKS 1

 

Pembahasan LKS 1 :

Mencari total  gunakan rumus Autosum   atau klik tanda sigma dan copy ke bawah sesuai bulan penjualan.

Rumusnya :

1. Bulan Januari  =SUM(C6:C12)

2. Bulan Februari =SUM(D6:D12)

3. Bulan Maret =SUM(E6:E12)

4. Bulan April =SUM(F6:F12)