modul excel 2007

Jl. Mayjend Sutoyo Kios Garnizun No. 3 PurworejoHP : 081 328 145 156

Olahdata – Kursus Privat – Penjualan – Rental – Scan -Service – dll

PENGOLAH ANGKAMICROSOFT EXCEL

Modul Microsoft Office Excel 2

Modul Microsoft Office Excel 3

MODUL 1PENGENALAN EXCEL

1.1. PENGERTIANMicrosoft Excel merupakan program dari Microsoft Office yang dikhususkan untuk pengolahanlembar kerja (worksheet) atau biasa dikenal dengan istilah spreadsheet program. Excel biasaidentik dengan pengolahan angka, dengan menggunakan Excel kita dapat membuat proposal biaya,rencana bisnis, form aplikasi, buku kerja akuntansi, dan masih banyak jenis dokumen lain yangmemerlukan perhitungan angka. Selain itu lembar kerja Excel juga dapat menampilkan data dalambentuk grafik dan gambar. Pengolahan database sederhana juga dimungkinkan dalam aplikasiMicrosoft Excel.Microsoft Excel 2007 memiliki tampilan antarmuka berbeda dari versi – versi sebelumnya. Daftarperintah yang biasa dikelompokkan dalam menu, kali ini dikelompokkan dalam beberapa tab yaitu: Home, Insert, Page Layout, Formulas, Data, Review, dan View. Masing-masing kategori memilikitoolbar yang dikelompokkan sesuai dengan kriterianya dan dapat diakses secara cepat dan mudah.

1.2 MEMANGGIL EXCEL

1.3. BAGIAN LAYAR EXCEL

- Office Button- Quick Access Toolbar- Title Bar- Tabmenu Ribbon- Ribbon- Formula Bar- Status Bar- Horizontal Scroll Bar- Vertical Scroll Bar

Modul Microsoft Office Excel 4

1.4. Membuat Tabel Sederhana

SOAL 01 :Buatlah tabel dibawah ini :

Kemudian berikan perintah sebagai berikut :1. Memberikan border dengan menggunakan langkah sebagai berikut :

- Blok A1:E11- Klik tab home, pilih Border

- Pilih All Border

2. Memberikan Pattern- Blok A1:E1, kemudian pilih tab Home pilih Fill color

- Tentukan salah satu warna yang diinginkan

Modul Microsoft Office Excel 5

3. Menghitung harga total, klik pada sel E2 ketik =C2*D2, kemudian berikan perintah Ctrl + C(mengcopy), kemudian sorot E3:E11 tekan CtrlV.

4. Untuk memberikan tanda Rp. Gunakan perintah blok D2:E11, klik kanan pilih format cell

Setelah itu pilih Accounting, pada decimal place diisi dengan 0 dan pada symbol carilah Rp.Indonesian, klik OK. Hasilnya adalah sebagai berikut :

Modul Microsoft Office Excel 6

5. Menyimpan dokumen, dengan menekan toolbar save pada Quick toolbar atau denganmenekan tombol Ctrl S.

Pada file name tentukan nama file : ”LATIHAN 1”, kemudian klik saveSOAL 02 :Buatlah tabel dibawah ini dan simpan hasilnya dengan nama ”DAFTAR GAJI”

Tunjangan = 10 % * Gaji PokokTotal Gaji adalah Gaji Pokok ditambah tunjangan

Modul Microsoft Office Excel 7

MODUL 2FUNGSI DATABASE

SUM, MAX, MIN, AVERAGE, DLL)

SOAL 03 :

Buatlah tabel dibawah ini dan simpan namanya dengan nama ”GEGAP GEMPITA”

Langkah pengerjaan :- Pada sel D17 berikan rumus =SUM(D7:D16)- Pada sel D18 berikan rumus =AVERAGE(D7:D16)- Pada sel D19 berikan rumus =MAX(D7:D16)- Pada sel D20 berikan rumus = MIN(D7:D16)

Kemudian hasil tersebut copykan ke sel diinginkan sehingga hasilnya menjadi sebagai berikut :DAFTAR GAJI PT GEGAP GEMPITA

Jl. Mawar 12 Bandung

No Nama Jabatan Gaji Pokok Tunjangan Potongan Gaji Bersih(10%) (5%)

1 Andi Direktur 1,500,000.00Rp 150,000.00Rp 75,000.00Rp 1,575,000.00Rp2 Hasan Staf 450,000.00Rp 45,000.00Rp 22,500.00Rp 472,500.00Rp3 Evi Staf 600,000.00Rp 60,000.00Rp 30,000.00Rp 630,000.00Rp4 Natang Finishing 850,000.00Rp 85,000.00Rp 42,500.00Rp 892,500.00Rp5 Annisa Finishing 750,000.00Rp 75,000.00Rp 37,500.00Rp 787,500.00Rp6 Retno Staf 562,000.00Rp 56,200.00Rp 28,100.00Rp 590,100.00Rp7 Wulan Staf 675,000.00Rp 67,500.00Rp 33,750.00Rp 708,750.00Rp8 Dari Administrasi 850,000.00Rp 85,000.00Rp 42,500.00Rp 892,500.00Rp9 Budi Cleaning Service 450,000.00Rp 45,000.00Rp 22,500.00Rp 472,500.00Rp

10 Pandu Cleaning Service 500,000.00Rp 50,000.00Rp 25,000.00Rp 525,000.00RpJumlah 7,187,000.00Rp 718,700.00Rp 359,350.00Rp 7,546,350.00RpRata-rata 718,700.00Rp 71,870.00Rp 35,935.00Rp 754,635.00RpTertinggi 1,500,000.00Rp 150,000.00Rp 75,000.00Rp 1,575,000.00RpTerendah 450,000.00Rp 45,000.00Rp 22,500.00Rp 472,500.00Rp

Modul Microsoft Office Excel 8

SOAL 04 :

Latihan :

DAFTAR NILAI SISWASMA 77 JAKARTA

No Nama Nilai 1 Nilai 2 Nilai 3 NR1 Andi 86 79 702 Yoha 63 77 733 Yaho 79 91 624 Natang 84 92 745 Samba 75 70 876 Widi 68 74 867 Firman 60 80 958 Ndari 70 84 979 Wulan 62 62 79

10 Retno 67 89 74

JumlahTertinggiTerendahRata-rataStandar deviasi

Keterangan :- NR adalah (Nilai 1 + Nilai 2 + Nilai 3) /3- Mencari standar deviasi dengan menggunakan rumus STDEV- Atur hasil penilaian dengan menggunakan 2 angka desimal dengan menggunakan perintah

Format cell – Number

Hasilnya adalah sebagai berikut :

DAFTAR NILAI SISWASMA 77 JAKARTA

No Nama Nilai 1 Nilai 2 Nilai 3 NR1 Andi 86.00 79.00 70.00 78.332 Yoha 63.00 77.00 73.00 71.003 Yaho 79.00 91.00 62.00 77.334 Natang 84.00 92.00 74.00 83.335 Samba 75.00 70.00 87.00 77.336 Widi 68.00 74.00 86.00 76.007 Firman 60.00 80.00 95.00 78.338 Ndari 70.00 84.00 97.00 83.679 Wulan 62.00 62.00 79.00 67.67

10 Retno 67.00 89.00 74.00 76.67

Jumlah 714.00 798.00 797.00 769.67Tertinggi 86.00 92.00 97.00 83.67Terendah 60.00 62.00 62.00 67.67Rata-rata 71.40 79.80 79.70 76.97Standar deviasi 9.22 9.61 11.27 4.86

Modul Microsoft Office Excel 9

MODUL 3FUNGSI LOGIKA IF

SOAL 05 :

Buatlah tabel dibawah ini dan simpan hasilnya dengan nama FUNGSI IF

Ketentuan : Gaji pokok diisi berdasarkan golongan jika 1 = 500000, 2 : 750000, 3 : 1.000.0000 Tunjangan status diisi hanya untuk yang statusnya kawin yaitu 10% dikalikan Gaji Pokok Tunjangan anak sebesar 2 % dari Gaji Pokok per anak. Anak yang mendapatkan tunjangan

maksimal 3 Potongan = 2 % * Gaji Pokok Gaji bersih = Gaji Pokok + Tunjangan – Potongan

1. Formula yang digunakan :

Rumus Gaji Pokok (F5)=IF(C5=1,500000,IF(C5=2,750000,1000000))

Rumus Tunjangan Status (G5)=IF(E5="Kawin",10%*F5,0)

Rumus Tunjangan Anak (H5)=IF(D5>=3,3*2%*F5,D5*2%*F5)

Rumus Potongan (I5)=2%*F5

Modul Microsoft Office Excel 10

Rumus Gaji Bersih (J5)=SUM(F5:H5)-I5

2. Mengatur judul tabelo Blok A2:J2o Berikan perintah Format Cell – Aligmento Pada isian horizontal pilih center across selection

3. Mengatur judul kolomo Blok A4:J4o Berikan perintah Format Cell – Aligmento Klik Ok

Hasilnya adalah sebagai berikut :

Modul Microsoft Office Excel 11

SOAL 06 :Ketikan tabel berikut dan simpan hasilnya dengan nama MUJUR SELALU

* Masukkan data Nama Pegawai, Gol, Sex(Jenis Kelamin), : P=Pria, W=Wanita, Marital StatusS=Single, M=Married, Jumlah Anak

* Kolom Gaji Pokok diisi dengan ketentuan sebagai berikut :~ Untuk Gol.=I, Gaji Pokok=150000~ Untuk Gol.=II. Gaji Pokok=250000~ Untuk Gol.=III, Gaji Pokok=350000

* Tunjangan Istri diberikan hanya untuk pegawai yang berjenis kelamin "PRIA" dan "SUDAHMENIKAH", yang besarnya sebagai berikut :

~ Untuk Gol. I, Tunjangan Istri=5%*Gaji Pokok~ Untuk Gol. II, Tunjangan Istri=10%*Gaji Pokok~ Untuk Gol. III, Tunjangan Istri=15%*Gaji Pokok

* Tunjangan Anak hanya diberikan untuk pegawai yang berjenis kelamin "PRIA" yang "SUDAHMENIKAH", yang besarnya 5% dari Gaji Pokok

* Kolom Total Gaji diisi dengan Gaji Pokok ditambah Tunjangan Istri ditambah Tunjangan Anak

Rumus yang dipakai :Gaji Pokok =IF(B6="I",150000,IF(B6="II",250000,350000))Tunjangan Istri =IF(AND(C6="P",D6="M"),IF(B6="I",5%,IF(B6="II",10%,15%)),0)*F6Tunjangan Anak =IF(AND(C6="P",D6="M"),IF(E6>3,3,E6)*5%,0)*F6Total Gaji =F6+G6+H6

Modul Microsoft Office Excel 12

SOAL 07 :

Keterangan* Ketiklah Nomor urut, Nama Pelanggan, Gol. Tarif dan Jumlah Pemakaian* Kolom Biaya/Bulan diisi sebagai berikut :

~ Jika Gol. Tarif="R-1", Biaya Beban/Bulan=3620~ Jika Gol. Tarif="R-2", Biaya Beban/Bulan=3660~ Jika Gol. Tarif="R-3", Biaya Beban/Bulan=7560

* Kolom Jumlah Pembayaran diisi dengan ketentuan sebagai berikut :~ Untuk Pemakaian 0-50, Biayanya Rp. 75/kWh~ Untuk Pemakaian 51-75, Biayanya Rp. 100/kWh~ Untuk Pemakaian >=76, Biayanya Rp. 150/kWh

* Kolom jumlah Tagihan diisi dengan total Jumlah Pembayaran ditambah dengan Biaya Beban per BulanRumus yang dipakai :Biaya (Jumlah Pembayaran)Biaya 0 – 50 =IF(E6>=50,50,E6)*75Biaya 51 – 75 =IF(E6>75,25,IF(E6<=50,0,E6-50))*100Biaya >=76 =IF(E6>=76,E6-75,0)*150

Modul Microsoft Office Excel 13

MODUL 4FUNGSI TANGGAL

SOAL 08 :Ketik tabel berikut dan simpan hasilnya dengan nama ”Dedy Hotel”

I. Keterangan cara kerja : Untuk menulis Judul dipakai text book dan Elipse dan dipakai warna serta sesuaikan huruf serta

blank untuk kotaknya Untuk gambil gambar pakai Insert, Object, Clipt art galery cari gambar yang sesuai Untuk menulis teks dalam gambar pakai text book tulis dan sesuai huruf Untuk penulisan tanggal diambil dari Format, Cells, Number,pilih Custom dan atur misal : dd-

"Desember", yy dan ditulis bulan, tanggal, tahunMisal untuk tanggal : 15 Desember 1998 ditulis : 12/15/98

5. Untuk penulisan Tarif kamar diambil dari Format, Cells, Number, pilih Custom dan atur misal:Rp 0 "/hari"

6. Untuk yang ada Rupiah diambil dari Format, Cells,Number, pilih custom dan cari yang adaformat Rp

II. Keterangan untuk rumus :1. Untuk mencari kelas dengan rumus If :

Jika kode kamar = E, maka kelas = "EKONOMI"Jika kode kamar = U, maka kelas = "UTAMA"Jika tidak, maka kelas = "VIP"

2. Untuk mencari lama tinggal pakai rumus = tanggal masuk - tanggal keluar3. Untuk mencari Tarif kamar pakai rumus If :

Jika kelas = Ekonomi, maka Tarif kamar = 57500Jika kelas = Utama, maka Tarif kamar = 85000Jika tidak, maka Tarif kamar = 95000

4. Untuk mencari Discount pakai rumus If :Jika Kelas =VIP atau lama tinggal lebih 5 hari, maka discount = 10% dari lama tinggal kali Tarifkamar Jika tidak maka Discount = 0

5. Biaya dibayar = Lama tinggal kali tarif kamar - Discount

Rumus yang dipakai :Lama Tinggal =G13-F13Tarif kamar =IF(E13="Ekonomi",57500,IF(E13="Utama",85000,95000))Diskont =IF(OR(E13="VIP",H13>5),10%*H13*I13,0)Biaya dibayar =H13*I13-J13

Modul Microsoft Office Excel 14

Modul Microsoft Office Excel 15

MODUL 5FUNGSI WAKTU

SOAL 09 :

Modul Microsoft Office Excel 16

Keterangan :

1. Untuk judul dibuat ditengah dipergunakan Merge and Center dan pergunakan huruf yang sesusai

2. Untuk jam kerja normal dipergunakan Kolom tersendiri dan diset dengan Format, cells, number,custom misal : 0 jam sehari

3. Untuk jam masuk dan jam keluar dipergunakan dengan set Format, cells, number, time dan sesuaikan dengan hasil(tulis biasa) misal : 6:45 atau 18:25

4. Lama kerja diperoleh dengan jam keluar - jam masuk dengan mempergunakan Set format, cells, number, custom baru sesuaikan misal : h "jam lebih" mm " menit"

5. Untuk mencari Lama Lembur dengan rumus Lama kerja - absolut jam kerja normal dibagi 24 dan diset format sesuai dengan nomor 4

6. Upah kerja dengan rumus Upah per jam dikali absolut jam kerja normal

7. Upah lembur diperoleh dari Lama lembur dikalikan 1.5 dari upah per jam dikalikan 24

8. Total upah dengan rumus Upah kerja + Upah lembur

Rumus yang digunakan :

Lama Kerja = E10-D10Lama Lembur = F10-$E$6/24Upah Kerja = H10*$E$6Upah Lembur = G10*H10*1.5*24Total Upah = I10+J10

Modul Microsoft Office Excel 17

MODUL 6FUNGSI LOOKUP

SOAL 10 :

Rumus yang digunakanType kamar =HLOOKUP(B6,TABEL1,2)Nama Kamar =VLOOKUP(C6,TABEL2,2)Tarif Kamar =VLOOKUP(C6,TABEL2,IF(B6="F",3,IF(B6="D",4,5)))Total Pembayaran =F6*G6

Modul Microsoft Office Excel 18

SOAL 11 :

Modul Microsoft Office Excel 19

PROSES :

1. Nomor Tiket terdiri atas 3 karakter (X,Y,Z); dimana X:Kode Kereta, Y:Kode Nama Kereta/Kode Kota Asal dan Z:Kode Kota Tujuan.

2. Nama Kereta diambil berdasarkan Kode Kereta dan Nama Kereta dari Tabel (Gunakan Value)

3. Jam Tiba didapat dari Jam Berangkat ditambah dengan Lama Perjalanan. Dimana untuk Kode 1 lamanya 4 Jam. Kode 2 lamanya 7 Jam dan Kode 3 lamanya 9 Jam.

(Gunakan TimeValue)

4. Jurusan didapat dari Tabel berdasarkan Kode Asal dan Kode Kota Tujuan (Gunakan Fungsi CONCATENATE).

5. harga Tiket didapat dari perkalian antara Jumlah Tiket masing-masing dengan Harga Tiket Satuan.

6. Jumlah Bayar merupakan Penjumlahan antara Harga Tiket Dewasa dengan Harga Tiket Anak-Anak.

7. Cari Sub Total dan Rekapitulasi Data (Gunakan Fungsi Database dan Data Fiil).

Rumus :

Jam tiba :

IF(LEFT(D7,1)="1",F7+TIMEVALUE("4:00"),IF(LEFT(D7,1)="2",F7+TIMEVALUE("7:00"),F7+TIMEVALUE("9:00")))

Jurusan :

=CONCATENATE(VLOOKUP(MID(D7,2,1),KOTA,2,FALSE)," * ",VLOOKUP(RIGHT(D7,1),KOTA,2,FALSE))

Harga Dewasa

=HLOOKUP(K7,HARGA,2,FALSE)*I7

Tiket Anak-anak

=HLOOKUP(K7,HARGA,3,FALSE)*J7

Jumlah bayar

=L7+M7

Modul Microsoft Office Excel 20

MODUL 7MEMBUAT GRAFIK

Soal 12 :

Buatlah tabel dibawah ini dan simpan hasilnya dengan nama ”GRAFIK”

Langkah membuat grafik :

1. Blok A4: E8

2. Pilih tab Insert – pilih group Chart, Pilih Column

3. Pilih salah satu jenis grafik, contoh hasilnya adalah sebaga berikut :

0

20

40

60

80

100

120

140

EXCEL WORD PASCAL JUMLAH

2005

2007

2007

2008

Modul Microsoft Office Excel 21

4. Perintah-perintah tambahan :

Tab Layout pada Chart

Chart title digunakan untuk menambahkan judul grafik Axis title digunakan menambahkan judul sumbu X dan Y Legend digunakan untuk menambahkan legend atau keterangan Data label digunakan untuk menuliskan data ke dalam grafik Data table digunakan untuk menampilkan tabel data pada grafik Axes digunakan mengatur tampilan sumbu axes grafik

Tab Design Switch Row / Column --> Data Y grafik akan dipindah sebagai sumbu X dan sebaliknya Select Data --> Merubah range data yang akan dibuat grafik Chart Layout --> Memilih layout grafik yang disediakan excel Chart Style --> Merubah model warna grafik Move Chart --> Memindahkan grafik ke lain sheet

Dari data tersebut cobalah buat grafik berikut ini :Grafik 1 :

0

10

20

30

40

50

60

70

2005 2006 2007 2008

jJum

lah

Sisw

a

Tahun

GRAFIK JUMLAH SISWA

EXCEL

WORD

PASCAL

Grafik 2 :

200523%

200624%

200723%

200830%

JUMLAH SISWA

Modul Microsoft Office Excel 22

MODUL 8PENYARINGAN DAN PENGURUTAN DATA

Soal 13 :

Buatlah tabel dibawah ini dan simpan hasilnya dengan nama ”SORTFILTER”

Langkah mengurutkan data

1. Blok sel A3:E18

2. Klik tab data, pada grup sort filter pilih Sort

3. Pada sort by pilih ”Nama” dan pada order pilih “A to Z”

4. Klik OK

5. Hasilnya data akan diurutkan berdasarkan nama

Modul Microsoft Office Excel 23

NO NAMA JURUSAN NILAI PRAKTIK NILAI TEORI2 AGUS EXCEL 55 501 ANDI WORD 96 87

14 DEWA EXCEL 64 898 JAWAWI OFFICE 84 70

15 KARYA OFFICE 97 654 MANTO EXCEL 58 573 MENTUS WORD 77 67

10 PRASTIYANI OFFICE 72 606 RETNO WORD 60 719 SAREKO EXCEL 75 94

11 SRINTIL OFFICE 95 6313 SUKMA WORD 79 585 SUSI OFFICE 89 60

12 WIDI WORD 79 627 WULAN OFFICE 92 89

Mengurutkan data berdasarkan lebih dari field

Misalkan kita akan mengurutkan berdasarkan JURUSAN dan NILAI PRAKTIK, maka pada saatpengisian kotak dialog sort tentukan seperti berikut ini :1. Tentukan Jurusan sebagai field kunci pengurutan pertama, pengisian adalah sebaga berikut

2. Klik Add Level, tentukan kotak dialog menjadi sebagai berikut

3. Klik OK, hasilnya adalah :NO NAMA JURUSAN NILAI PRAKTIK NILAI TEORI

2 AGUS EXCEL 58 934 MANTO EXCEL 98 66

14 DEWA EXCEL 87 519 SAREKO EXCEL 59 56

10 PRASTIYANI OFFICE 87 568 JAWAWI OFFICE 91 605 SUSI OFFICE 58 757 WULAN OFFICE 78 61

11 SRINTIL OFFICE 52 7715 KARYA OFFICE 67 576 RETNO WORD 94 963 MENTUS WORD 71 85

13 SUKMA WORD 93 72

Modul Microsoft Office Excel 24

12 WIDI WORD 93 621 ANDI WORD 93 82

Menfilter data, menampilkan data yang memenuhi kriteriaUntuk memfilter atau menyaring data dapat dilakukan dengan menggunakan perintahAutofilter dan Advanced filterAutofilter

1. Klik salah satu judul kolom pada tabel tersebut

2. Klik tab data – pilih Filter, hasilnya adalah sebagai berikut

3. Klik tanda panah pada jurusan, pilih WORD maka hasilnya adalah

4. Klik OK, hasilnya adalahNO NAMA JURUSAN NILAI PRAKTIK NILAI TEORI

1 ANDI WORD 78 743 MENTUS WORD 92 776 RETNO WORD 68 72

12 WIDI WORD 78 7313 SUKMA WORD 75 57

Menampilkan yang nilai praktiknya lebih dari > 75- Pada kolom nilai praktik, klik panah – pilih Numbered filter

Modul Microsoft Office Excel 25

- Klik OK, hasilnya adalah sebagai berikut :

NO NAMA JURUSAN NILAI PRAKTIK NILAI TEORI6 RETNO WORD 63 65

13 SUKMA WORD 52 59