SPREADSHEET

Tugas Praktikum 4 (Pengantar Aplikasi Komputer)

LATIHAN 1 (SHEET 1 )

Cara Mencari Total, Rata-rata, Max, Min dan Jumlah jurusan menggunakan rumus sebagai berikut:

1. Rumus Mencari Total
    =SUM(C5:C11)---->[ Sumatera ]
    =SUM(D5:D11)---->[ Jawa ]
    =SUM(E5:E11)----> [ Kalimantan ]
    =SUM(F5:F11)----> [ Daerah Lainnya]
    =SUM(G5:G11)---->[ Jumlah ]
2. Rumus Mencari Rata-rata
    =AVERAGE(C5:C11)---->[ Sumatera ]

    =AVERAGE (D5:D11)---->[ Jawa ]
    =AVERAGE(E5:E11)----> [ Kalimantan ]
    =AVERAGE(F5:F11)----> [ Daerah Lainnya]
    =AVERAGE(G5:G11)----> [ Jumlah]
3. Rumus Mencari Max dan Min
    =MAX(C5:C11)----> [Sumatera ]
    =MIN(C5:C11) ----> [Sumatera ]
    Seterusnya sama seperti contoh mencari rata-rata dan total hanya diganti dengan Max dan Min
4. Rumus Jumlah Jurusan
    =SUBTOTAL(103,B5:B11)----> [Sumatera]
    =SUBTOTAL(103,D5:D11)----> [Jawa]
    =SUBTOTAL(103,E5:E11)----> [Kalimantan]
    =SUBTOTAL(103,F5:F11)----> [Daerah Lainnya]
    =SUBTOTAL(103,G5:G11)----> [Jumlah]
5. Rumus Mencari Jumlah
    =SUM(C5:F5)--->[Jumlah mahasiswa Teknologi dari pulau sumatera, jawa, kalimantan, dst]
    Seterusnya memakai SUM hanya diganti angka kolomnya saja

LATIHAN 2 ( SHEET 2 )

Cara Mengambil Huruf Awal,tengah dan akhir pada suatu kata

1. Rumus Cara Mengambil Huruf Awal

    =LEFT(B4,1)---->[Jeruk]

    =LEFT(B5,1)---->[Manggis]

    =LEFT(B6,1)---->[Lemon]

    =LEFT(B7,1)---->[Salak]

2. Rumus Cara Mengambil Huruf Tengah
    =MID(B4,3,1) ---->[Jeruk]
    =MID(B5,3,1) ---->[Manggis]
    =MID(B6,3,1) ---->[Lemon]
    =MID(B7,3,1) ---->[Salak]
3. Rumus Cara Mengambil Huruf Akhir
    =RIGHT(B4,2)---->[Jeruk]
    =RIGHT(B5,2)---->[Manggis]
    =RIGHT(B6,2)---->[Lemon]
    =RIGHT(B7,2)---->[Salak]

LATIHAN 3 (SHEET 3 )

 Cara mencari V-Look Up dan H-Look Up

1. Rumus Cara Mencari V-Look Up
    =VLOOKUP(A9,$A$3:$C$6,3,0)---->[ Alamat Badu ]
    =VLOOKUP(A10,$A$3:$C$6,3,0)---->[ Alamat Abdul ]
    =VLOOKUP(A11,$A$3:$C$6,3,0)---->[ Alamat Christian ]
    =VLOOKUP(A12,$A$3:$C$6,3,0)---->[ Alamat Deby ]
    =VLOOKUP(A13,$A$3:$C$6,3,0)---->[ Alamat Abdul ]
2. Rumus Cara Mencari H-Look Up
    =HLOOKUP(F9,$G$2:$J$4,3,0)---->[ Alamat Badu]
    =HLOOKUP(F10,$G$2:$J$4,3,0)---->[ Alamat Abdul ]
    =HLOOKUP(F11,$G$2:$J$4,3,0)---->[ Alamat Christian]
    =HLOOKUP(F12,$G$2:$J$4,3,0)---->[ Alamat Deby]
    =HLOOKUP(F10,$G$2:$J$4,3,0) ---->[ Alamat Abdul ]

LATIHAN 4 (SHEET 4)

Cara Mencari Kode Jurusan, Nama Jurusan, Kriteria Nilai, Biaya Kuliah, Potongan Biaya, Total Bayar, Keterangan (Lulus atau Tidak Lulus) .

1. Rumus Mencari Kode Jurusan
    =MID(A3,3,3)---->[ Aa Badrul ]
    =MID(A4,3,3)---->[ Bejo Michel ]
    =MID(A5,3,3)---->[ Cecev Gorbacev ]
    =MID(A6,3,3)---->[ I gede a kecil ]
2. Rumus Mencari Nama Jurusan
    =HLOOKUP(311,$C$9:$D$10,2,0)---->[ Adm. Bisnis (Aa Badrul) ]
    =HLOOKUP(312,$C$9:$D$10,2,0)---->[ Akutansi (Bejo Michael)]
    =HLOOKUP(312,$C$9:$D$10,2,0)---->[ Akuntasni (Cecev Gorbacev ]
    =HLOOKUP(313,$C$9:$D$10,2,0)---->[ Desain Visual ( I gede a kecil ]
3. Rumus Mencari Kriteria Nilai
    =VLOOKUP(G3,$A$9:$B$13,2,0)---->[ A= Sangat Baik ]
    =VLOOKUP(G4,$A$9:$B$13,2,0)---->[ B= Baik ]
    =VLOOKUP(G5,$A$9:$B$13,2,0)---->[ D= Kurang ]
    =VLOOKUP(G6,$A$9:$B$13,2,0)---->[ C= Cukup ]
4. Rumus Mencari Biaya Kuliah
    =IF(C3=2001,30000,IF(C3=2002,40000,50000))*F3 ----> [ Rp 6.600.000 ]
    =IF(C4=2001,30000,IF(C4=2002,40000,50000))*F4 ----> [ Rp 5.400.000 ]
    =IF(C5=2001,30000,IF(C5=2002,40000,50000))*F5 ----> [ Rp 10.000.000]
    =IF(C6=2001,30000,IF(C6=2002,40000,50000))*F6 ----> [ Rp 10.500.000]
5. Rumus Mencari Potongan Biaya
    =IF(G3="A","20%",IF(G3="B","10%",IF(G3="C","5%","0%")))---->[20%]
    =IF(G4="A","20%",IF(G4="B","10%",IF(G4="C","5%","0%")))---->[10%]
    =IF(G5="A","20%",IF(G5="B","10%",IF(G5="C","5%","0%")))---->[0%]
    =IF(G6="A","20%",IF(G6="B","10%",IF(G6="C","5%","0%")))---->[5%]

6. Rumus Mencari Total Bayar 
    =I3-(I3*J3)---->[Rp 5.280.000 (Aa Badrul)
    =I4-(I4*J4)---->[Rp 4.860.000 (Bejo Mivhael)
    =I5-(I5*J5)---->[Rp 10.000.000 (Cecev Gorbacev)
    =I6-(I6*J6)---->[Rp 9.975.000 (I gede a kecil)

7. Rumus Mencari Keterangan (Lulus/Tidak Lulus)
    =IF(G3="A","LULUS",IF(G3="B","LULUS",IF(G3="C","LULUS","TIDAK LULUS")))
    =IF(G4="A","LULUS",IF(G4="B","LULUS",IF(G4="C","LULUS","TIDAK LULUS")))
    =IF(G5="A","LULUS",IF(G5="B","LULUS",IF(G5="C","LULUS","TIDAK LULUS")))
    =IF(G6="A","LULUS",IF(G6="B","LULUS",IF(G6="C","LULUS","TIDAK LULUS")))