Latihan Vlookup,Count If & If
Klinik Persalinan "Mawar"
TanggalNama Pasien Kelas Penanganan MasukRahayu A Dokter 11-Jan-08Santi C Dokter 11-Jan-08Putri B Bidan 11-Jan-08Eka A Bidan 11-Jan-08Icha B Bidan 11-Jan-08
Total Bayar 8,720,000 Total Biaya Tertinggi 2,200,000 Total Biaya Terendah 1,450,000 Jumlah Kelas A 2Jumlah Kelas B 2Jumlah Kelas C 1
Klinik Persalinan "Mawar"
Tanggal lama BiayaKeluar Inap Inap Layanan Persalinan
14-Jan-08 3 900,000 300,000 1,000,000 14-Jan-08 3 300,000 150,000 1,000,000 14-Jan-08 3 600,000 210,000 750,000 14-Jan-08 3 900,000 300,000 750,000 14-Jan-08 3 600,000 210,000 750,000
Biaya PerhariBiaya Inap Layanan
A 300000 100000B 200000 70000C 100000 50000
Biaya PersalinanDokter 1000000Bidan 750000
Total Bayar
2,200,000 1,450,000 1,560,000 1,950,000 1,560,000
Latihan Vlook Up dengan Mid
No NIM Nama1 0011500100 Humisar2 0122500101 Endarto3 0211500109 Hendri4 0233500102 Sovan5 0222500106 Sri6 0311500107 Lihin7 0344500103 Satrio 8 0422500104 Rizky 9 0444500108 Ainur
10 0511500105 Safrina
Tabel Tahun MasukKode Tahun00 200001 200102 200203 200304 200405 2005
Tabel JurusanKode Jurusan
11 Teknik Informatika22 Sistem Informasi33 Sistem Informatika44 Komputerisasi Akuntansi
Kode Jurusan Jurusan Tahun11 Teknik Informatika 200022 Sistem Informasi 200111 Teknik Informatika 200233 Sistem Informatika 200222 Sistem Informasi 200211 Teknik Informatika 200344 Komputerisasi Akuntansi 200322 Sistem Informasi 200444 Komputerisasi Akuntansi 200411 Teknik Informatika 2005
Latihan Vlookup
Kode Gaji Pokok Uang Transport (Perhari)1 Rp500,000 Rp3,0002 Rp350,000 Rp2,5003 Rp200,000 Rp2,000
Laporan Upah Karyawan (Salesman)Harga Barang: Rp75,000Jumlah Komisi: 10%
Nama Karyawan Kode Hari KerjaYohanes 1 24Umar 1 25Mamat 1 20Budiman 1 25Ratna 1 26Charlie 1 25Tanto 2 26Danie 2 24Gugun 2 27Susi 2 20Abdullah 2 22Ferri 3 25Asusanto 3 21Eni 3 20Ermanto 3 23
Jumlah MaksimumJumlah Minimum
Jumlah Rata - Rata
Uang Makan(Per Hari) Keterangan:Rp5,000 1. Gaji Pokok Adalah Gaji Sesuai Kode Karyawan Masing-Masing Rp4,000 2. Uang Transport adalah Uang Transport perhari x hari kerja sesuai kode karyawanRp3,000 3. Uang Makan Adalah Uang Makan Perhari x Hari Kerja sesuai kode karyawan
4. Komisi adalah Jumlah Barang Yang Terjual x Harga Barang dan x Jumlah Komisi5. Pendapatan Bersih didapat Dari gaji pokok + Uang Transport + Uanga Makan + Komisi -Pinjaman6. Jangan lupa Isikan Masing-Masing Jumlah Max,Min dan Rata2
Jumlah Barang Gaji Pokok Uang Transport Uang Makan147 Rp500,000 Rp72,000 Rp120,000130 Rp500,000 Rp75,000 Rp125,000178 Rp500,000 Rp60,000 Rp100,000
99 Rp500,000 Rp75,000 Rp125,000120 Rp500,000 Rp78,000 Rp130,000
80 Rp500,000 Rp75,000 Rp125,000185 Rp350,000 Rp65,000 Rp104,000175 Rp350,000 Rp60,000 Rp96,000105 Rp350,000 Rp67,500 Rp108,000
85 Rp350,000 Rp50,000 Rp80,000100 Rp350,000 Rp55,000 Rp88,000
75 Rp200,000 Rp50,000 Rp75,000145 Rp200,000 Rp42,000 Rp63,000
97 Rp200,000 Rp40,000 Rp60,00086 Rp200,000 Rp46,000 Rp69,000
185 Rp500,000 Rp78,000 Rp130,00075 Rp200,000 Rp40,000 Rp60,000
120 Rp370,000 Rp60,700 Rp97,867
1. Gaji Pokok Adalah Gaji Sesuai Kode Karyawan Masing-Masing 2. Uang Transport adalah Uang Transport perhari x hari kerja sesuai kode karyawan3. Uang Makan Adalah Uang Makan Perhari x Hari Kerja sesuai kode karyawan4. Komisi adalah Jumlah Barang Yang Terjual x Harga Barang dan x Jumlah Komisi5. Pendapatan Bersih didapat Dari gaji pokok + Uang Transport + Uanga Makan + Komisi -Pinjaman6. Jangan lupa Isikan Masing-Masing Jumlah Max,Min dan Rata2
Komisi Pinjaman Pendapatan BersihRp1,102,500 0 Rp1,794,500
Rp975,000 7000 Rp1,668,000Rp1,335,000 0 Rp1,995,000
Rp742,500 1300 Rp1,441,200Rp900,000 15000 Rp1,593,000Rp600,000 0 Rp1,300,000
Rp1,387,500 23700 Rp1,882,800Rp1,312,500 46000 Rp1,772,500
Rp787,500 0 Rp1,313,000Rp637,500 0 Rp1,117,500Rp750,000 13000 Rp1,230,000Rp562,500 0 Rp887,500
Rp1,087,500 12000 Rp1,380,500Rp727,500 0 Rp1,027,500Rp645,000 0 Rp960,000
Rp1,387,500 46000 Rp1,995,000Rp562,500 0 Rp887,500Rp933,750 Rp7,867 Rp1,424,200
Latihan Chart
Tabel APertumbuhan PendudukNilai X Nilai Y
1 5 Tahun2 7 20063 9 20074 11 2008
2009
Pengeluaran 1 bulanPerbandingan Jumlah Hutang Dan PendapatanMhs A Mhs B
1 5 Tahun 3 9 20064 11 20072 7 2008
2009
1. Buatlah 2 grafik lingkaran berdasar “Tabel A”. grafik 1 menunjukan distribusi X,tabel 2 menunjukan distribusi y2. Buatlah grafik garis pengeluaran mahasiswa 1 bulan berdasarkan tabel“pengeluaran 1 bulan”3. Buatlah grafik batang berdasarkan tabel “pertumbuhan penduduk”menggunakan grafik tipe “Bar”4. Buatlah grafik batang perbandingan jumlah hutang dan pendapatanmenggunakan grafik tipe “column”
1 2 3 40
5
10
15
20
Mhs B
1
2
3
4
0 2000 4000 6000 8000 10000 12000 14000
Jumlah
1 2 3 40
2000
4000
6000
8000
10000
12000
Tahun Pendapatan Hutang
1 2 3 40
2000
4000
6000
8000
10000
12000
Tahun Pendapatan Hutang
Pertumbuhan Penduduk
Jumlah50009000
110007000
Perbandingan Jumlah Hutang Dan Pendapatan
Pendapatan Hutang10000 9000
5000 60009000 7000
12000 4000
1. Buatlah 2 grafik lingkaran berdasar “Tabel A”. grafik 1 menunjukan distribusi X,
2. Buatlah grafik garis pengeluaran mahasiswa 1 bulan berdasarkan tabel
3. Buatlah grafik batang berdasarkan tabel “pertumbuhan penduduk”
4. Buatlah grafik batang perbandingan jumlah hutang dan pendapatan
10%
20%
30%
40%
Nilai X1 2 3 4
16%
22%
28%
34%
Nilai Y1 2 3 4
1
2
3
4
0 2000 4000 6000 8000 10000 12000 14000
Jumlah
10%
20%
30%
40%
Nilai X1 2 3 4
16%
22%
28%
34%
Nilai Y1 2 3 4
Pivot table dan pivot chat
Bulan Nama Barang HargaJanuari Motherboard ASUS $250,00Januari Processor Quad Core $500,00Maret Keyboard $100,00April Mouse $120,00Januari Monitor LG $300,00April HDD 200 Gb $650,00Juli Graphic Card ATI Rade$300,00September Sound Card Audigy $400,00September LAN Card $20,00Oktober Printer $400,00November Head Sink $10,00Desember Speaker 5‐1 $400,00 $400,00
1. Salin tabel di atas pada program MS Excel 20072. Ubahlah tabel tersebut menggunakan pivot table dengan mengisi field columnlabels dengan bulan, raw labels dengan nama barang, dan values dengan harga3. Buatlah pivot chart pada dengan mengisi field column labels dengan bulan, rawlabels dengan nama barang, dan values dengan harga
2. Ubahlah tabel tersebut menggunakan pivot table dengan mengisi field columnlabels dengan bulan, raw labels dengan nama barang, dan values dengan harga3. Buatlah pivot chart pada dengan mengisi field column labels dengan bulan, raw
Rumus Excel Sederhana
No No_Mhs Nama_Mhs Angkatan1 01311114 MUHAMMAD MA'RUF 012 01312160 WAHYU WIJOSENO AJI 013 02312039 A DODI KURNIAWAN 024 03313224 LINDA MERIANA 035 04312005 DIAH KUSUMA WARDANI 046 03312008 AJENG WIDHA IRFANA 037 01312012 WULAN ARI PUJI LESTARI 018 04311013 DHANAR SEPTI SETYANI 049 04312021 PUTRI PRIMA S 04
10 04313039 SIDIK SAPUTRA 0411 05313047 NOVITA NUR RAHMAWATI 0512 04312048 SEKAR PHALEFI IRAWATI 0413 03313051 ARDHITA RATRIE FEBRIANI 0314 04312073 KUNTO RIADHA 0415 04311083 LUTHFI ARSYAD PRAMONO 04
Tabel Kriteria Nilai Kriteria
A Sangat Baik B Baik C CukupD KurangE Sangat Kurang
1. Angkatan diperoleh dari dua angka awal no mahasiswa (Gunakan LEFT)2. Nomor urut diperoleh dari dua angka akhir dari no mahasiswa (Gunakan RIGHT)3. Kode jurusan diperoleh dari tiga angka setelah tahun (Gunakan MID)4. Kriteria diambil dari kriteria pada tabel kriteria (Gunakan VLOOKUP)5. Biaya kuliah diperoleh dari jumlah SKS dikali biaya per SKS6. Potongan Biaya diberikan pada mahasiswa yang memperoleh nilai A=20%,B=10%, dan C=5% (Gunakan IF)7. Total biaya diperoleh dari biaya kuliah dikurangi dengan potongan biaya kuliah8. Keterangan “LULUS” atau “TIDAK LULUS”. Mahasiswa yang memperoleh nilai A,B atau C diyatakan lulus(Gunakan IF)9. No 9,10,11,dan 12 diisi dengan total, rata-rata, terendah dan tertinggi dari biaya total kuliah.
No Urut MHS Kode Jurusan Jmlh SKS Nilai Rata‐Rata Biaya Kuliah14 11114 22 A Rp770,00060 12160 18 B Rp630,00039 12039 25 D Rp875,00024 13224 21 A Rp735,00005 12005 19 B Rp665,00008 12008 20 C Rp700,00012 12012 23 E Rp805,00013 11013 24 C Rp840,00021 12021 22 B Rp770,00039 13039 18 A Rp630,00047 13047 15 C Rp525,00048 12048 22 D Rp770,00051 13051 21 A Rp735,00073 12073 23 E Rp805,00083 11083 19 C Rp665,000
Biaya per SKS: Rp 35,000
A Sangat BaikB BaikC CukupD KurangE Sangat Kurang
1. Angkatan diperoleh dari dua angka awal no mahasiswa (Gunakan LEFT)2. Nomor urut diperoleh dari dua angka akhir dari no mahasiswa (Gunakan RIGHT)3. Kode jurusan diperoleh dari tiga angka setelah tahun (Gunakan MID)4. Kriteria diambil dari kriteria pada tabel kriteria (Gunakan VLOOKUP)5. Biaya kuliah diperoleh dari jumlah SKS dikali biaya per SKS6. Potongan Biaya diberikan pada mahasiswa yang memperoleh nilai A=20%,B=10%, dan C=5% (Gunakan IF)7. Total biaya diperoleh dari biaya kuliah dikurangi dengan potongan biaya kuliah8. Keterangan “LULUS” atau “TIDAK LULUS”. Mahasiswa yang memperoleh nilai A,B atau C diyatakan lulus
9. No 9,10,11,dan 12 diisi dengan total, rata-rata, terendah dan tertinggi dari biaya total kuliah.
Potongan B. Kuliah Total Bayar Ket. Kriteria154000 Rp616,000 LULUS Sangat Baik
63000 Rp567,000 LULUS Baik0 Rp875,000 TIDAK LULUS Kurang
147000 Rp588,000 LULUS Sangat Baik66500 Rp598,500 LULUS Baik35000 Rp665,000 LULUS Cukup
0 Rp805,000 TIDAK LULUS Sangat Kurang42000 Rp798,000 LULUS Cukup77000 Rp693,000 LULUS Baik
126000 Rp504,000 LULUS Sangat Baik26250 Rp498,750 LULUS Cukup
0 Rp770,000 TIDAK LULUS Kurang147000 Rp588,000 LULUS Sangat Baik
0 Rp805,000 TIDAK LULUS Sangat Kurang33250 Rp631,750 LULUS Cukup
Total Rp10,003,000Rata‐rata Rp666,867terendah Rp498,750Tertinggi Rp875,000
Rumus excel sederhana 2
ID Nama Barang Jumlah barangA/1/2007 Meja komputer 20A/2/2007 Pensil 5A/3/2007 Papan tulis 1B/4/2007 Keyboard 21B/5/2007 Processor 21A/6/2007 Kursi 20B/7/2007 Monitor 21B/8/2007 Speaker 2C/9/2007 Pohon natal 5C/10/2007 Lampu natal 5
dikode Jenis barangA ATKB CompC Lain-lain
1. Isikan jenis barang berdasarkan tabel dan kolom ID. Gunakan fungsi VLOOKUP.2. Isilah diskon dengan ketentuan jika jenis barang ATK mendapat diskon 2%, jika bukan tidak ada diskon3. Subtotal diperoleh dari jumlah barang dikali hargabarang4. Total diperoleh dari subtotal dikurangi diskon5. Total keseluruhan diperoleh dari jumlah seluruh total.
harga barang Jenis barang sub total diskon 500,000.00 ATK 10,000,000.00 200,000.00 1,500.00 ATK 7,500.00 150.00 200,000.00 ATK 200,000.00 4,000.00 150,000.00 Comp 3,150,000.00 - 2,500,000.00 Comp 52,500,000.00 - 600,000.00 ATK 12,000,000.00 240,000.00 2,000,000.00 Comp 42,000,000.00 - 350,000.00 Comp 700,000.00 - 500,000.00 Lain-lain 2,500,000.00 - 100,000.00 Lain-lain 500,000.00 -
total keseluruhan
1. Isikan jenis barang berdasarkan tabel dan kolom ID. Gunakan fungsi VLOOKUP.2. Isilah diskon dengan ketentuan jika jenis barang ATK mendapat diskon 2%, jika bukan tidak ada diskon
total 9,800,000.00 7,350.00 196,000.00 3,150,000.00 52,500,000.00 11,760,000.00 42,000,000.00 700,000.00 2,500,000.00 500,000.00 123,113,350.00
Gabungan Rumus Excel
NO Nama Karyawan Gol. Status1 Amir 2B Belum2 Caleb 2A Nikah3 Yosua 2D Belum4 Anastasia 2B Nikah5 Desiana 2C Nikah6 Roy 2C Belum7 Ichsan 2A Belum8 Theo 2B Nikah9 Hudson 2D Belum
10 Grant 2D BelumTotal
GOL 2A 2B 2CJML ORG 2 3 2GAJI BERSIH 2,380,000 5,030,000 4,220,000
TABEL GOLONGAN GAJIGOL 2A 2B 2CGAPOK 1,000,000.00 1,500,000.00 2,000,000.00
TABEL TUNJANGAN
GOLONGAN Status Nikah Belum
2A 300000 2000002B 350000 2500002C 400000 3000002D 450000 350000
1. Gaji pokok diberikan berdasarkan golongan, besarnya gaji pokok diperoleh dari tabel golongan gaji(gabungkan fungsi IF dan HLOOKUP)2. Tunjangan diberikan berdasarkan golongan dan status, besarnya tunjangan dilihat dari tabeltunjangan. (gabungkan fungsi IF, AND dan VLOOKUP)3. Besarnya pajak ditentukan berdasarkan golongan. Golongan A dikenai pajak sebesar 5%, golongan Bsebesar 8%, golongan C sebesar 11 % dan golongan D sebesar 15% (gabungkan fungsi IF dan RIGHT)4. Potongan sosial sebesar Rp 20.000,00 dikenakan pada pegawai yang mempunyai gaji pokok lebih dariRp 1.000.000,00 atau mempunyai tunjangan lebih besar dari Rp 200.000,00, tetapi pegawai yangdikenai pajak lebih dari 11% berapapun besar gaji dan tunjangan yang diterimanya hanya akan dikenaipotongan sosial sebesar Rp 5000,00. (gabungkan fungsi IF, OR, dan NOT)5. Gaji bersih diperoleh dari jumlah gaji pokok dan tunjangan dikurangi pajak dan sosial.6. Hitung jumlah orang sesuai golongan (gunakan CountIF)
7. Hitung total gaji sesuai golongan. (gunakan SumIF)
Gaji Pokok Tunjangan Pajak 1,500,000.00 250,000.00 8% 120,000.00 1,000,000.00 300,000.00 5% 50,000.00 2,500,000.00 350,000.00 15% 375,000.00 1,500,000.00 350,000.00 8% 120,000.00 2,000,000.00 400,000.00 11% 220,000.00 2,000,000.00 300,000.00 11% 220,000.00 1,000,000.00 200,000.00 5% 50,000.00 1,500,000.00 350,000.00 8% 120,000.00 2,500,000.00 350,000.00 15% 375,000.00 2,500,000.00 350,000.00 15% 375,000.00 18,000,000.00 3,200,000.00 101.00% 2,025,000.00
2D3
7,410,000
2D 2,500,000.00
1. Gaji pokok diberikan berdasarkan golongan, besarnya gaji pokok diperoleh dari tabel golongan gaji
2. Tunjangan diberikan berdasarkan golongan dan status, besarnya tunjangan dilihat dari tabel
3. Besarnya pajak ditentukan berdasarkan golongan. Golongan A dikenai pajak sebesar 5%, golongan Bsebesar 8%, golongan C sebesar 11 % dan golongan D sebesar 15% (gabungkan fungsi IF dan RIGHT)4. Potongan sosial sebesar Rp 20.000,00 dikenakan pada pegawai yang mempunyai gaji pokok lebih dariRp 1.000.000,00 atau mempunyai tunjangan lebih besar dari Rp 200.000,00, tetapi pegawai yangdikenai pajak lebih dari 11% berapapun besar gaji dan tunjangan yang diterimanya hanya akan dikenai
5. Gaji bersih diperoleh dari jumlah gaji pokok dan tunjangan dikurangi pajak dan sosial.
Pajak dihitung dgn gaji pokok
Sosial Gaji Bersih20000 1,610,000.00 20000 1,230,000.00
5000 2,470,000.00 20000 1,710,000.00 20000 2,160,000.00 20000 2,060,000.00
0 1,150,000.00 20000 1,710,000.00
5000 2,470,000.00 5000 2,470,000.00
15000 19,040,000.00
Latihan Hlookup
ParahyanganNo Nama Pemesan Kode
1 Titik EXD2 Kheiza EKA3 Rizal EKL4 Gatut BIA5 Yati EXA6 Anya BIL7 Ian BID8 Dara EKD
Kode-1 BI EKKelas Bisnis Ekonomi
Kode-2 Ket BIA Anak 30,000.00 D Dewasa 60,000.00 L Lansia 45,000.00
Kelas Status Harga Jml Tiket Jml HargaExecutive Dewasa 100,000.00 2 200,000.00 Ekonomi Anak 20,000.00 4 80,000.00 Ekonomi Lansia 30,000.00 3 90,000.00 Bisnis Anak 30,000.00 4 120,000.00 Executive Anak 50,000.00 1 50,000.00 Bisnis Lansia 45,000.00 2 90,000.00 Bisnis Dewasa 60,000.00 4 240,000.00 Ekonomi Dewasa 50,000.00 2 100,000.00
EXExecutive
EK EX 20,000.00 50,000.00 50,000.00 100,000.00 30,000.00 75,000.00
