berlatih microsoft excel 2010

RADDINI GUSTI RAHAYU [email protected]

Mari Berlatih Microsoft Excel 2010 | Daftar Isi 2

Daftar Isi

Daftar Isi ......................................................................................................................... 2

1. Pendahuluan ............................................................................................................ 4

2. Tampilan Workbook ................................................................................................. 5

Soal 1 ................................................................................................................................. 5

Soal 2 ................................................................................................................................. 7

3. Menyisipkan dan Format Grafik ................................................................................ 9

Soal 3 ................................................................................................................................. 9

Soal 4 ............................................................................................................................... 11

Soal 5 ............................................................................................................................... 12

Soal 6 ............................................................................................................................... 14

Soal 7 ............................................................................................................................... 16

Soal 8 ............................................................................................................................... 18

Soal 9 ............................................................................................................................... 19

4. Proteksi Data .......................................................................................................... 21

Soal 10 ............................................................................................................................. 21

Soal 11 ............................................................................................................................. 24

Soal 12 ............................................................................................................................. 25

5. Manajemen Data .................................................................................................... 27

Soal 13 ............................................................................................................................. 27

Soal 14 ............................................................................................................................. 30

Soal 15 ............................................................................................................................. 32

Soal 16 ............................................................................................................................. 34

Soal 17 ............................................................................................................................. 36

Soal 18 ............................................................................................................................. 38

Soal 19 ............................................................................................................................. 41

Soal 20 ............................................................................................................................. 42

Mari Berlatih Microsoft Excel 2010 | Daftar Isi 3

Soal 21 ............................................................................................................................. 43

Soal 22 ............................................................................................................................. 46

Soal 23 ............................................................................................................................. 49

6. Mencetak Data ....................................................................................................... 51

Soal 24 ............................................................................................................................. 51

Soal 25 ............................................................................................................................. 53

7. Menggunakan Fungsi Excel ..................................................................................... 55

Soal 26 ............................................................................................................................. 55

Soal 27 ............................................................................................................................. 58

Soal 28 ............................................................................................................................. 59

Soal 29 ............................................................................................................................. 60

Soal 30 ............................................................................................................................. 61

Soal 31 ............................................................................................................................. 62

Soal 32 ............................................................................................................................. 63

Soal 33 ............................................................................................................................. 64

Soal 34 ............................................................................................................................. 65

Soal 35 ............................................................................................................................. 67

Soal 36 ............................................................................................................................. 68

Soal 37 ............................................................................................................................. 69

Soal 38 ............................................................................................................................. 70

Soal 39 ............................................................................................................................. 72

Soal 40 ............................................................................................................................. 74

Soal 41 ............................................................................................................................. 76

Soal 42 ............................................................................................................................. 77

Soal 43 ............................................................................................................................. 78

Mari Berlatih Microsoft Excel 2010 | Pendahuluan 4

1. Pendahuluan

Microsoft Office Excel 2010 merupakan program aplikasi lembar kerja (spreadsheet)

yang memiliki fitur berbagai jenis kalkulasi dan pembuatan grafik. Selain itu, program

ini juga mampu mengolah berbagai bentuk pengolahan angka yang lain, seperti

penyusunan data, memproyeksikan, menganalisa serta mempresentasikan data dalam

bentuk ilustrasi yang professional dan menawan.

Ebook ini berisi latihan-latihan yang mengoptimalkan fitur dan Fungsi Excel untuk

memudahkan berbagai jenis pekerjaan. Setiap soal latihan disertakan keterangan

gambar sebelum dan sesudah pengerjaan lengkap dengan langkah-langkah

mengerjakannya.

Ebook ini dapat digunakan oleh siapa saja, jika Anda belum begitu paham mengenai

materi tentang Excel, Anda dapat membaca ebook sebelumnya yaitu Working with

Microsoft Excel 2007. Walau ebook tersebut menggunakan versi 2007 namun secara

keseluruhan yang dibahas tidak ada perbedaan yang signifikan.

Mari Berlatih Microsoft Excel 2010 | Tampilan Workbook 5

2. Tampilan Workbook

Soal 1

Pak Beni sedang melihat laporan penjualan laptop buatan karyawannya. Tabel nya

begitu panjang dan lebar sehingga dia kesulitan membaca laporan itu dengan baik.

Ketika dia menggeser tampilan ke kanan kolom Merk Barang pun ikut tergeser,

sedangkan ketika menggeser tampilan ke bawah judul kolom ikut tergeser ke atas.

Bantulah Pak Beni untuk membekukan judul kolom dan kolom Merk Barang supaya

tidak ikut tergeser.

Jawab:

1. Buka file TAMPILAN.xlsx dan buka sheet Tampilan1.

2. Tempatkan penunjuk cell di B4 lalu pada tab View kategori Window klik Freeze

Panes lalu pilih Freeze Panes.


3. Untuk menonaktifkan Freeze Panes, pilih tab View kategori Window > Freeze

Panes > Unfreeze Panes.


Soal 2

Desita baru saja selesai membuat laporan keuangan, namun dia tidak yakin hasil

perhitungannya itu betul atau tidak. Untuk itu dia ingin membandingkan nilai di

laporan yang dia buat dengan nilai yang ada di Neraca Lajur. Tapi dia bingung karena

kedua sheet tersebut terdapat di workbook yang sama. Bantulah Desita untuk

menampilkan kedua sheet tersebut seperti di bawah ini:

Jawab:

1. Buka file TAMPILAN.xlsx dan buka sheet Tampilan2.

2. Pada tab View kategori Window, klik New Window. Sehingga tampilan window

workbook menjadi dua. Ciri bahwa window tersebut merupakan hasil duplikasi,

terdapat di keterangan nama windownya yang ditunjukkan pada gambar berikut:


3. Pada tab View kategori Window klik View Side by Side. Jika workbook atau

tampilan window yang aktif lebih dari dua maka akan muncul kotak dialog

Compare Side by Side, yang mana meminta anda untuk memilih dengan

workbook mana akan ditampilkan secara side by side.

4. Pilih TAMPILAN.xlsx:2 lalu klik OK.

5. Fitur ini sudah aktif dan sekarang cobalah geser tampilan ke bawah atau ke kanan,

maka tampilan window akan ikut bergeser secara bersamaan.

Mari Berlatih Microsoft Excel 2010 | Menyisipkan dan Format Grafik 9

3. Menyisipkan dan Format Grafik

Soal 3

Data berikut ini menggambarkan data penjualan beberapa merek komputer dari bulan

Januari sampai dengan Desember. Data yang disajikan sudah cukup informatif, namun

akan lebih mudah membacanya jika disajikan dalam bentuk grafik. Oleh karena itu,

buatlah grafik konvensional atau grafik standar dengan tipe Column.

Jawab:

Berikut langkah-langkah membuat grafik column:

1. Aktifkan file GRAFIK.xlsx dan buka sheet Grafik1.

2. Tempatkan penunjuk cell di area table atau sorot data range B5:F17.

3. Pada tab Insert kategori Charts klik Column sehingga muncul beberapa pilihan

grafik Column. Anda dapat memilih grafik tipe 2 dimensi, 3 dimensi, tabung,

kerucut dan sebagainya.


4. Anda juga dapat memilih semua tipe grafik dengan mengklik All Chart

Types,sehingga muncul kotak dialog Insert Chart. Disini Anda dapat memilih tipe

grafik yang Anda inginkan.

5. Pilih tipe grafik yang Anda inginkan, an akhiri dengan mengklik tombol OK

sehingga hasilnya menjadi seperti berikut:


Soal 4

Sebuah perusahaan memiliki data perbandingan penjualan semangka dan melon

seperti gambar berikut. Anda diminta untuk membuat grafik konvensional tipe Line

dengan sub-tipe Stacked Line with Markers.

Jawab:


2. Sorot cell B4:D16.

3. Pada tab Insert kategori Charts pilih tipe Line dan Sub-tipe Stacked Line with

Markers.


Soal 5

Data berikut ini menampilkan tabel yang berisi informasi mengenai tingkat kelulusan

ujian Komprehensif siswa dari berbagai sekolah. Berdasarkan data tersebut buatlah

grafik kombinasi tipe Line dengan tipe Column.

Jawab:


2. Sorot cell B5:E11.

3. Pada tab Insert kategori Charts pilih tipe Column dan sub-tipe Clustered Column.

Sehingga tampilan awal grafik seperti berikut:

4. Klik kanan data series Tidak Lulus lalu pilih Change Series Chart Type.


5. Ketika kotak dialog Chage Chart Type muncul, pilih type Line dan sub-tipe Line with

Markers, sehingga tampilan grafik menjadi seperti berikut:


Soal 6

Data berikut ini adalah mengenai hasil panen beberapa jenis buah selama satu tahun.

Dari data ini Anda diminta membuat grafik artistik dengan tipe Column seperti gambar

berikut:

Jawab:


2. Sorot cell B6:C9.

3. Pada tab Insert kategori Charts pilih tipe Column dan sub-tipe 3-D Clustered

Column. Sehingga tampilan awal grafik seperti berikut:


4. Untuk menambahkan judul grafik, Anda dapat melakukannya pada tab Charts

Tools Layout kategori Labels lalu pilih Chart Title > Above Chart, lalu ketikkan

judulnya Hasil Panen Tahun 2011.

5. Untuk menampilkan tabel data di bawah grafik, Anda dapat melakukannya pada

tab Charts Tools Layout kategori Labels lalu pilih Data Table > Show Data Table

with Legend Keys.

6. Klik kanan data series Pisang lalu klik Format Data Series sehingga muncul kotak

dialog Format Data Point.

7. Klik tab Fill lalu aktifkan pilihan Picture or texture fill. Untuk memasukkan gambar

klik tombol File lalu pilih lokasi gambar pisang yang telah disediakan.

8. Klik tombol Insert dan Close. Ulangi langkah-langkah di atas dengan memilih

gambar nanas untuk data series nanas, dan seterusnya.


Soal 7

Tabel berikut ini adalah data mengenai jumlah penumpang Pesawat dan Bus dari

bulan Januari sampai dengan bulan Desember. Dari tabel ini, buatlah grafik artistik

yang memberikan informasi mengenai perbandingan penumpang. Anda diminta

melengkapinya dengan gambar pesawat dan Bus pada grafik tersebut.

Jawab:



3. Pada tab Insert kategori Charts pilih tipe Line dan Sub-tipe Line with Markers.


4. Secara default, marker yang ditampilkan adalah tanda kotak. Untuk

mengubahnya klik data series Pesawat.

5. Klik kanan marker pada data series Pesawat untuk bulan Desember lalu klik

Format Data Point.

6. Pada kotak dialog Format Data Point pilih tab Fill lalu aktifkan Picture or texture

fill.


7. Klik tombol File lalu masukkan gambar pesawat setelah itu klik Insert.

8. Pilih tab Marker Options lalu aktifkan pilihan Built-in. Atur ukuran gambar

pesawat pada pilihan size, misalnya 30.

9. Klik tab Marker Line Color, aktifkan No Line untuk menghilangkan border pada

marker, lalu klik Close sehingga tampilannya menjadi seperti berikut:


Soal 8

Berikut ini merupakan data hasil perkebunan stroberi selama satu semester kedua.

Berdasarkan data ini, Anda diminta membuat grafik artistik yang dilengkapi dengan

background gambar Stroberi seperti tampilan berikut:

Jawab:


2. Sorot cell B4:C10.

3. Pada tab Insert kategori Charts pilih tipe Line dan Sub-tipe Stacked Line with

Markers.

4. Klik kanan area grafik lalu pilih Format Chart Area sehingga muncul kotak dialog

Format Chart Area.

5. Pada tab fill, pilih Picture or texture fill. Klik tombol File lalu pilih gambar stroberi

yang Anda punya.

6. Klik insert dan close.


Soal 9

Data berikut ini menampilkan tabel yang berisi informasi mengenai tingkat kelulusan

ujian Komprehensif siswa dari berbagai sekolah. Berdasarkan data tersebut buatlah

grafik kombinasi tipe Line dengan tipe Column.

Jawab:



3. Pada tab Insert kategori Charts pilih tipe Column dan sub-tipe Clustered Column.


4. Ubahlah posisi kolom dan baris pada tab Charts Tools Design > Switch

Row/Column sehingga hasilnya seperti berikut:


Jika Anda perhatikan, posisi sumbu pada grafik khususnya sumbu vertikal, terletak

di sebelah kiri. Anda dapat mengubah posisi sumbu agar berada di tengah-tengah,

caranya adalah sebagai berikut:

5. Klik kanan sumbu kategori atau sumbu horisontal lalu klik Format Axis sehingga

muncul kotak dialog Format Axis.

6. Pada tab Axis Options kategori Vertical Axis Crosses > At category Number,

masukkan angka 2 lalu klik tombol Close. Hasilnya terlihat seperti gambar berikut:

Mari Berlatih Microsoft Excel 2010 | Proteksi Data 21

4. Proteksi Data

Soal 10

Pak Ahmad adalah seorang karyawan di salah satu toko elektonik. Dia memiliki data

tentang daftar harga barang-barang yang dijual di toko tersebut, dia tidak ingin orang

lain dapat mengganti isi atau mengetahui formula yang ada pada data tersebut. Tugas

Anda adalah membantu Pak Ahmad untuk memproteksi seluruh datanya supaya

hanya bisa dilihat tetapi tidak bisa diubah-ubah.

Jawab:

1. Buka file PROTECT.xlsx dan buka sheet Protect1.

2. Sorot seluruh cell pada worksheet tersebut atau dengan menekan tombol Sellect

All pada bagian kiri atas worksheet.

3. Pada tab Home kategori Cells, pilih Format lalu pastikan tombol toggle Lock Cell

sedang dalam keadaan aktif.


4. Pada tab Home kategori Cells, pilih Format lalu klik Protect Sheet sehingga

muncul kotak dialog Protect Sheet.

5. Pada kotak dialog Protect Sheet, ceklis check box Protect Worksheet and

contents of locked cells jika ingin menggunakan password untuk membuka

proteksi lalu masukkan passwordnya di kotak isian Password to unprotect sheet.

6. Pada check box list Allow all users of this worksheet to: ceklis lah check box

Select unlocked cells. Jika sudah klik tombol OK.

7. Excel akan meminta Anda memasukkan password satu kali lahgi pada kotak dialog

Confirm Password, hal ini dilakukan untuk memastikan Anda tidak melakukan

kesalahan dalam pengisian password. Lalu klik OK.


8. Untuk membuka proteksinya, pada tab Home kategori Cells, pilih Format lalu klik

Unprotect Sheet dan Anda diminta memasukkan password (jika Anda

menggunakan password).


Soal 11

Berikut ini adalah form registrasi nasabah pada Bank Berlian. Pak Andi yang membuat

Form ini merasa kesulitan dan kebingungan ketika menggunakannya. Ketika dia

pertama memasukkan data di Tgl Pengajuan (cell D7) lalu menekan tombol Enter, cell

berpindah ke cell D8. Dia menginginkan ketika menekan tombol Enter, cell berpindah

ke cell-cell yang brwarna putih (cell D9, D11, D15 dan seterusnya). Tugas Anda adalah

membantu Pak Andi untuk mewujudkan keinginannya. Gunakan fitur Protect sheet

untuk menyelesaikan soal ini.

Jawab:

1. Buka file PROTECT.xlsx dan buka sheet Protect2.

2. Pastikan seluruh cell dalam keadaan Locked, caranya pada tab Home kategori

Cells pilih Format lalu pastikan tombol toogle Lock Cell dalam keadaan aktif.

3. Sorot cell-cell yang berwarna putih atau yang merupakan cell isian Form.

4. Pada tab Home kategori Cells klik Format lalu nonaktifkan Lock Cell.

5. Pada tab Home kategori Cells klik Format lalu klik Protect Sheet sehingga muncul

kotak dialog Protect Sheet.

6. Masukkan password jika Anda menginginkannya lalu ceklis check box Select

Unlock Cell lalu klik tombol OK.


Soal 12

Pada soal proteksi sebelumnya, Pak Ahmad telah terbantu dengan menggunakan

protect sheet. Tetapi dia kebingungan bagaimana caranya supaya tidak semua orang

dapat membuka file daftar harga tersebut. Bantulah Pak Ahmad membuat proteksi

lebih pada file daftar harganya dengan menggunakan Enkripsi.

Jawab:

1. Buka file PROTECT.xlsx dan buka sheet file Protect1.

2. Pada tab File lalu klik Save As sehingga muncul kotak dialog Save As.

3. Pada sebelah kiri tombol Save, klik tombol Tools lalu pilih General Options

sehingga muncul kotak dialog General Options.


4. Ada dua pilihan password, yaitu password untuk membuka file dan untuk

memodifikasi isi file. Anda dapat menggunakan salah satu atau seluruh password

tersebut. Jika sudah klik OK.

5. Excel akan meminta Anda memasukkan password satu kali lagi pada kotak dialog

Confirm Password, hal ini dilakukan untuk memastikan Anda tidak melakukan

kesalahan dalam pengisian password. Lalu klik OK.

Mari Berlatih Microsoft Excel 2010 | Manajemen Data 27

5. Manajemen Data

Soal 13

Data berikut adalah daftar nama siswa kelas 10-A SMA Budi Pekerti. Data ini dibuat

berdasarkan urutan registrasi siswa. Urutkanlah data tersebut berdasarkan Nama

secara Ascending supaya lebih mudah ketika membuat daftar absensi siswa.

Jawab:

1. Buka file MANDATA.xlsx dan buka sheet Sort1.

2. Tempatkan penunjuk cell di area tabel.

3. Pada tab Data kategori Sort & Filter klik Sort. Sehingga muncul kotak dialog Sort.


4. Pada kotak pilihan Sort By, pilhlah:

Column : Nama

Sort On : Value

Order : A to Z

5. Setelah itu Klik OK sehingga tampilan datanya menjadi seperti berikut:

Pengurutan di atas adalah sort satu level. Anda bisa menggunakan lebih dari satu

level untuk melakukan sorting data. Sebagai contoh, urutkanlah tabel di atas

berdasarkan Asal Sekolah dan Tanggal Lahir.




Column : Asal Sekolah

Sort On : Value

Order : A to Z


4. Klik tombol Add Level, lalu pada kotak pilihan Then By, pilihlah:

Column : Tanggal Lahir

Sort On : Value

Order : Oldest to Newest



Soal 14

Data pada gambar berikut ini menampilkan daftar tunggakan mahasiswa di Lembaga

Pendidikan Pinter Terus. Anda diminta mengurutkan data tersebut dengan kunci atau

kriteria pengurutan 3 level, yaitu Nama, Jurusan dan Tunggakan.

Jawab:

1. Buka file MANDATA.xlsx dan buka sheet Sort2.




Column : Nama

Sort On : Value

Order : A to Z


Column : Jurusan

Sort On : Value

Order : A to Z


Column : Tunggakan

Sort On : Value

Order : Smallest to Largest



Soal 15

Data pada soal 14, tampilkanlah data mahasiswa dari jurusan Akuntansi saja, selain

jurusaN tersebut tidak ditampilkan. Gunakan fasilitas Filter untuk menyelesaikan soal

ini.

Jawab:

1. Buka file MANDATA.xlsx dan buka sheet Filter1.

2. Tempatkan penunjuk cell pada salah satu judul kolom, misalnya kolom NIM (B4).

3. Pada tab Data kategori Sort & Filter klik Filter sehingga setiap judul kolom pada

tabel tersebut muncul icon panah bawah yang menandakan tabel tersebut

menggunakan fitur filter.

4. Pada tampilan pilihan filter di atas, Anda terlebih dulu harus menonaktifkan

pilihan Select All agar semua tanda ceklis pada seluruh pilihan dihilangkan, baru

setelah itu ceklis check box Akuntansi.


5. Setelah itu klik OK sehingga tampilan datanya menjadi seperti berikut:

6. Perhatikan pada judul kolom Jurusan, terdapat perbedaan icon yaitu icon panah

bawah dan saringan. Ini menandakan bahwa kolom tersebut sedang dalam mode

filter.


Soal 16

Data yang digunakan masih menggunakan data pada soal sebelumnya. Anda diminta

membuat sebuah tabel yang hanya menampilkan data jurusan Informatika dan Bisnis

yang tunggakannya lebih dari 300 ribu.

Jawab:

1. Buka file MANDATA.xlsx dan buka sheet Filter2.

2. Tempatkan penunjuk cell pada salah satu judul kolom, misalnya kolom Nama (B4).

3. Pada tab Data kategori Sort & Filter klik Filter sehingga setiap judul kolom pada

tabel tersebut muncul icon panah bawah yang menandakan tabel tersebut

menggunakan fitur filter.

4. Tentukan pilihan filter untuk jurusan Informatika dan Bisnis saja dengan langkah-

langkah seperti pada jawaban soal sebelumnya.

5. Tentukan pilihan filter untuk Tunggakan yang lebih dari 300 ribu dengan cara

seperti terlihat pada gambar berikut:


6. Klik OK pada kotak dialog Custom Autofilter, sehingga tampilan datanya menjadi

seperti berikut:


Soal 17

Berikut ini adalah data pengumuman score Lomba Cerdas Cermat SD Mulyasari. Dalam

pengisian scorenya minimal harus bernilai 350. Buatlah validasinya dengan

menggunakan Data Validation.

Jawab:

1. Buka file MANDATA.xlsx dan buka sheet Validation1.

2. Sorot cell D5:D9. Pada tab Data kategori Data Tools pilih Data Validation

sehingga muncul kotak dialog Data Validation.

3. Pada tab Setting kotak pilihan Allow pilih Whole Number dan pada kotak pilihan

Data pilih Greather than dengan nilai minimum 350.

4. Pada tab Input Message, ceklis check box Show input message when cell is

selected. Lalu pada kotak isian Title isikan “Nilai minimal Score” dan pada kotak

isian Input Message isikan “Score minimal harus bernilai 350”.


5. Pada tab Error Alert beri tanda ceklis pada check box Show error alert after

invalid data is entered. Pada kotak pilihan Style pilih Stop dan isikan “Score <=

350” pada kotak isian Title dan isikan “Score yang Anda masukkan lebih kecil

atau sama dengan 350” pada kotak isian Error Message. Lalu tekan tombol OK.

Hasilnya dapat dilihat sebagai berikut:

Jika Anda memasukkan data yang tidak sesuai (kurang dari 350) maka akan muncul

kotak peringatan seperti berikut:


Soal 18

Berikut ini adalah daftar persediaan barang jenis alat tulis kantor pada toko serbaguna

Semua Ada. Daftar barang berikut barang baru di toko tersebut sehingga harus dibuat

kode barangnya. Tugas Anda adalah membuat kode barangnya dengan format

penulisan 3huruf pertama diawali huruf “ATK” selanjutnya nomor urut barang

tersebut, contohnya untuk cell D5 diisi ATK001.

Jawab:

1. Buka file VALIDATION.xlsx dan buka sheet Validation2.

2. Sorot cell C5:C14. Pada tab ribbon Data category Data Tools pilih Data Validation

sehingga muncul kotak dialog Data Validation.

3. Pada tab Setting kotak pilihan Allow pilih Custom dengan formula

“=LEFT(C5;3)=”ATK” ”.

4. Pada tab Input Message ceklis check box Show input message when cell is

selected. Lalu pada kotak isian Title isikan “Kode Barang” dan pada kotak isian

Input Message isikan “Masukkan Kode Barang dengan huruf pertama ATK”.


5. Pada tab Error Alert beri tanda ceklis pada check box Show error alert after

invalid data is entered. Pada kotak pilihan Style pilih Stop dan isikan “Kode

Barang Salah” pada kotak isian Title dan isikan “3 huruf awal Kode Barang yang

Anda masukkan bukan ATK” pada kotak isian Error Message. Lalu tekan tombol

OK.

Hasilnya dapat dilihat sebagai berikut:


Jika Anda memasukkan data yang tidak sesuai (bukan ATK) maka akan muncul kotak

peringatan seperti berikut:


Soal 19

Pada data di bawah ini , buatlah sebuah tampilan tabel agar lebih mudah dibaca.

Jawab:

1. Buka file MANDATA.xlsx dan buka sheet ConFor1.

2. Sorot cell C4:H23.

3. Pada tab Home kategori Styles pilih Conditional Formatting lalu klik Databars.

4. Pilih styles mana yang Anda inginkan, misalnya Light Blue Databar sehingga

hasilnya seperti berikut:


Soal 20

Dengan menggunakan data yang sama, buatlah format untuk cell dengan nilai

penjualan di atas rata-rata diberi warna merah.

Jawab:

1. Buka file MANDATA.xlsx dan buka sheet ConFor2.

2. Sorot cell C4:H23.

3. Pada tab Home kategori Styles pilih Conditional Formatting >Top/Bottom Rules >

Above Average sehingga hasilnya seperti berikut:


Soal 21

Data pada gambar berikut ini menampilkan daftar tunggakan mahasiswa di Lembaga

Pendidikan Pinter Terus. Anda diminta membuat jumlah tunggakan setiap jurusan.

Jawab:

1. Buka file MANDATA.xlsx dan buka sheet Subtotal1.

2. Pastikan data sudah terurut berdasarkan Jurusan lalu tempatkan penunjuk cell

pada salah satu judul kolom.

3. Pada tab Data kategori Outline klik Subtotal sehingga muncul kotak dialog

Subtotal seperti gambar berikut:


4. Pada kotak dialog Subtotal, tentukan pengelompokkan tabel yang Anda inginkan.

Pada contoh ini karena data dikelompokkan berdasarkan Jurusan, pada kotak

pilihan At each change in: pilih Jurusan.

5. Selanjutnya pada kotak pilihan Use function: pilih Sum dan pada pilihan Check

box Add subtotal to: ceklis Tunggakan. Klik OK sehingga hasilnya menjadi seperti

berikut:

Pada tampilan hasil Subtotal, Anda dapat melihat angka 1, 2 dan 3 di sudut kiri atas.

Angka ini digunakan untuk mengontrol tampilan tabel. Tampilan di atas adalah

tampilan ke-3 atau detil, Anda dapat menampilkan tabel di atas hanya untuk informasi


Subtotal-nya saja dengan mengklik angka 2, sedangkan untuk melihat tampilan Total

saja, Anda dapat mengklik angka 1.

Anda juga dapat mengontrol tampilan tabel dari tanda + atau – yang terdapat di

sebelah kiri nomor baris.


Soal 22

Berikut ini laporan penjualan Toko Online Terus dari beberapa jenis barang. Bagian

keuangan perusahaan ini meminta laporannya dibuat simpel dan dapat mengetahui

dengan cepat berapa jumlah penjualan berdasarkan Bulan dan Jenis Barang. Buatlah

Laporan tersebut sesuai yang diinginkan dengan menggunakan PivotTable.

Jawab:

1. Buka file PIVOT.xlsx dan buka sheet Pivot1.

2. Tempatkan penunjuk cell pada area tabel lalu pada tab Insert kategori Tables klik

PivotTable sehingga muncul kotak dialog Create PivotTable.

3. Pada bagian Choose where you want the pivotTable report to be placed ada 2

pilihan, yaitu:

New Worksheet : untuk menempatkan report PivotTable pada sheet baru.

Existing Worksheet : untuk menempatkan report PivotTable pada sheet yang

sama dengan memilih lokasinya di bagian Location.

Pilih New Worksheet lalu klik OK dan berinama Lap.Penjualan.

4. Pada sheet yang telah diberi nama tadi tampilannya seperti berikut:


5. Layout PivotTable yang masih kosong dapat Anda isi dengan cara drag&drop

field-field yang tersedia dalam PivotTable Field List ke Area Section yang

diinginkan.

6. Ikuti penempatan fieldnya sehingga tampilannya seperti berikut:


Soal 23

Dari soal sebelumnya buatlah laporan total penjualan seperti gambar berikut:

Jawab:

1. Buka file PIVOT.xlsx dan buka sheet Pivot1.

2. Tempatkan penunjuk cell pada area tabel lalu pada tab Insert kategori Tables klik

PivotTable sehingga muncul kotak dialog Create PivotTable.

3. Pada bagian Choose where you want the pivotTable report to be placed ada 2

pilihan, yaitu:

New Worksheet : untuk menempatkan report PivotTable pada sheet baru.

Existing Worksheet : untuk menempatkan report PivotTable pada sheet yang

sama dengan memilih lokasinya di bagian Location.

Pilih New Worksheet lalu klik OK dan berinama Lap.Penjualan.

4. Pada sheet yang telah diberi nama tadi tampilannya seperti berikut:


5. Layout PivotTable yang masih kosong dapat Anda isi dengan cara drag&drop

field-field yang tersedia dalam PivotTable Field List ke Area Section yang

diinginkan.

6. Ikuti penempatan fieldnya seperti gambar berikut:

Mari Berlatih Microsoft Excel 2010 | Mencetak Data 51

6. Mencetak Data

Soal 24

Desita bertugas di bagian keuangan di kantornya. Dia baru saja selesai membuat

Laporan Keuangan. Dia ingin ketika setiap mencetak laporan keuangan tersebut yang

tercetak adalah area laporan tersebut. Bantulah Desita untuk membuat setting print

area laporannya.

Jawab:

1. Buka file PRINT.xlsx dan buka sheet Print1.

2. Sorot area laporannya yaitu cell A2:D56.

3. Pada tab Page Layout kategori Page Setup klik Print Area lalu pilih Set Print Area

sehingga muncul tanda garis putus-putus pada area print yang sudah di setting.


4. Pada tab File pilih Print lalu lihatlah previewnya, yang tampak adalah area print

yang sudah disetting tadi.


Soal 25

Setelah mencetak Laporan keuangan, Desita ingin mencetak tabel Neraca Lajur

miliknya untuk dilaporkan. Namun tabel itu berisi data yang begitu panjang, ketika dia

coba mencetaknya, tabel terbagi menjadi beberapa lembar dan dia kesulitan untuk

membaca tabelnya karena judul tabel hanya tercetak di lembar pertama. Bantulah

Desita untuk mencetak tabel tersebut agar setiap lembar tercetak judul tabelnya.

Jawab:

1. Buka file PRINT.xlsx dan buka sheet Print2.

2. Pada tab Page Layout kategori Page Setup klik Print Titles sehingga muncul kotak

dialog Page Setup tab Sheet.

3. Pada kotak range Print area: masukkan area printnya yaitu cell B2:M81.

4. Pada kotak range Rows to repeat at top masukkan range cell judul tabel dengan

cara menyorot baris 5 dan 6.


5. Klik tombol Print preview untuk melihat hasilnya, seperti berikut:

Mari Berlatih Microsoft Excel 2010 | Menggunakan Fungsi Excel 55

7. Menggunakan Fungsi Excel

Soal 26

Data pada gambar berikut ini merupakan daftar nilai kelas IX IPA 3 pada suatu sekolah.

Anda diminta mengisi Median, Modus, Maksimum, Minimum, Jumlah data dan Jumlah

Nilai.

Jawab:

1. Buka file FUNGSI.xlsx dan buka sheet Statis1.

2. Untuk pengisian Median, sorot cell C14:E14 lalu ketikkan formula

=MEDIAN(D4:C13), kemudian tekan kombinasi tombol Ctrl+Enter.

3. Untuk pengisian Modus, sorot cell C15:E15 lalu ketikkan formula =MODE(C4:C13),

kemudian tekan kombinasi tombol Ctrl+Enter.


4. Untuk pengisian Maksimum, sorot cell C16:E16 lalu ketikkan formula

=MAX(C4:C13), kemudian tekan kombinasi tombol Ctrl+Enter.

5. Untuk pengisian Minimum, sorot cell C17:E17 lalu ketikkan formula

=MIN(C4:C13), kemudian tekan kombinasi tombol Ctrl+Enter.

6. Untuk pengisian Jumlah data, sorot cell C18:E18 lalu ketikkan formula

=COUNT(C4:C13), kemudian tekan kombinasi tombol Ctrl+Enter.

7. Untuk pengisian Jumlah nilai, sorot cell C19:E19 lalu ketikkan formula

=SUM(C4:C13), kemudian tekan kombinasi tombol Ctrl+Enter.

8. Untuk pengisian Rata-rata, sorot cell F4:F13 lalu ketikkan formula

=AVERAGE(C4:C13), kemudian tekan kombinasi tombol Ctrl+Enter.


Sehingga hasil akhirnya akan tampak seperti berikut:


Soal 27

Data berikut ini adalah informasi mengenai alamat pelanggan sebuah toko elektonik.

Namun alamat tersebut tidak dipisahkan antara nama jalan dan kode posnya. Anda

diminta memisahkannya menjadi dua bagian yaitu alamat dan kode posnya.

Jawab:

1. Buka file FUNGSI.xlsx dan buka sheet Teks1.

2. Untuk mengambil kode posnya, sorot cell B4:B18 lalu masukkan formula:

=RIGHT(A4;5) lalu tekan kombinasi tombol Ctrl+Enter.

3. Untuk mengambil alamatnya, sorot cell C4:C18 masukkan formula:

=LEFT(A4;(LEN(A4)-LEN(B4))) lalu tekan kombinasi tombol Ctrl+Enter.

Hasilnya dapat dilihat seperti gambar berikut:


Soal 28

Berikut ini adalah data nomor telepon pelanggan sebuah toko elektronik. Namun cara

penulisan nomor teleponnya tidak rapi dengan berbagai cara penulisan yang tidak

seragam, apalagi disertai dengan penggunaan berbagai karakter-karakter yang tidak

perlu. Tugas Anda adalah merapikan data nomor telepon ke cara penulisan yang

seragam dan rapi engan membuang spasi kosong, karakter “-“ (tanda hubung),

karakter “/” (garis miring) dan “.” (titik). Untuk menyelesaikan tugas ini Anda dapat

menggunakan fungsi SUBSTITUTE.

Jawab:


2. Sorot cell B4:B20 lalu masukkan formula: =SUBSTITUTE

(SUBSTITUTE(SUBSTITUTE(A5;”-“;””);”.”;””);”/”;””) lalu tekan kombinasi tombol

Ctrl+Enter.



Soal 29

Berikut ini adalah data alamat pelanggan sebuah toko elektronik. Namun penulisanya

dibuat terpisah antara Jalan, Nomor, RT dan RW, Kota dan Kode pos. Tugas Anda

adalah menyatukan data tersebut menjadi alamat lengkap dalam satu cell. Untuk

menyelesaikan tugas ini Anda dapat menggunakan fungsi CONCATENATE dan

gabungan fungsi teks LEFT, MID dan RIGHT.

Jawab:


2. Sorot cell E4:E20 lalu masukkan formula: =CONCATENATE("Jalan ";A4;" No.

";LEFT(B4;2);MID(B4;3;6);" RW ";RIGHT(B4;2);" ";C4;" ";D4 ) lalu tekan kombinasi

tombol Ctrl+Enter.



Soal 30

Berikut ini adalah data nilai Ulangan Matematika suatu kelas, tugas Anda adalah

memberi keterangan LULUS atau GAGAL di kolom keterangan dengan ketentuan

penilaian sebagai berikut:

Jika Nilai lebih besar dari 60 maka dinyatakan LULUS dan nilai kurang dari 60

dinyatakan GAGAL. Gunakan fungsi IF untuk menyelesaikan soal ini.

Jawab:

1. Buka file FUNGSI.xlsx dan buka sheet Logika1.

2. Sorot cell C4:C20 lalu masukkan formula: =IF(C4>60;"LULUS";"GAGAL") lalu tekan

kombinasi tombol Ctrl+Enter.



Soal 31

Berikut ini adalah rekap nilai Uji kompetensi akhir seorang siswa, tugas Anda adalah

memberi KODE LL, TT, dan GL di kolom KODE dengan ketentuan penilaian sebagai

berikut:

Untuk nilai sama dengan atau di atas 70 maka LL

Untuk nilai antara 60-69 maka TT

Untuk nilai dibawah 60 maka GL

Setelah itu beri keterangan setiap kode di kolom KETERANGAN dengan ketentuan:

LL: LULUS, TT: TUGAS TAMBAHAN, dan GL: GAGAL.

Jawab:


2. Untuk mengisi Kode, sorot cell D6:D10 lalu masukkan formula:

=IF(C6>=70;"LL";IF(AND(C6>60;C6<69);"TT";IF(C6<60;"GL";""))) lalu tekan


3. Untuk mengisi Keterangan, sorot cell E6:E10 lalu masukkan formula:

=IF(D6="LL";"LULUS";IF(D6="TT";"TUGAS

TAMBAHAN";IF(D6="GL";"GAGAL";""))) lalu tekan kombinasi tombol Ctrl+Enter.

4. Untuk mengisi Nilai Akumulasi Akhir, di cell C11 masukkan formula:

=SUM(C6:C10)/COUNT(C6:C10).



Soal 32

Pak Risman adalah seorang guru komputer di sebuah SMA, pada suatu hari Ia hendak

pergi ke Jakarta untuk memenuhi undangan temannya di sana. Tapi Ia kebingungan

melihat cuaca dan kondisi jalanan di kota Bandung. Ia akan pergi ke Jakarta jika cuaca

cerah atau kondisi jalanan lancar, tugas Anda adalah bantu Pak Risman membuat

keputusan untuk pergi atau tidak dengan membuat tabel keputusan seperti gambar

berikut:

Jawab:


2. Untuk mengisi Keputusan, sorot cell C4:C10 lalu masukkan formula:

=IF(OR(A4="lancar";B4="cerah");"Pergi ke Jakarta";"Diam di Rumah") lalu tekan



Soal 33

Berikut ini adalah rekap nilai Uji komputer di suatu kelas, tugas Anda adalah memberi

keterangan setiap kode di kolom Keterangan dengan ketentuan sesuai dengan tabel

bantu. Gunakan fungsi HLOOKUP untuk menyelesaikan soal ini.

Jawab:

1. Buka file FUNGSI.xlsx dan buka sheet Lookup1.

2. Untuk mengisi Keterangan, sorot cell C4:C9 lalu masukkan formula:

=HLOOKUP(B4;$F$3:$H$4;2) lalu tekan kombinasi tombol Ctrl+Enter.



Soal 34

Toko Komputer “Rich Computer” mempunyai data penjualan sebagai berikut.

Lengkapi laporan tersebut dengan cara:

Untuk pengisian di kolom Kategori, Nama Barang dan Harga Satuan gunakan

merujuk pada tabel bantu Daftar Harga Barang dengan menggunakan fungsi

VLOOKUP.

Untuk pengisian di kolom Total, kalikan Harga satuan dengan Qty.

Jawab:

1. Buka file FUNGSI.xlsx dan buka sheet Lookup2.

2. Untuk mengisi Kategori, sorot cell B4:B23 lalu masukkan formula:

=VLOOKUP($A4;$H$4:$K$12;2) lalu tekan kombinasi tombol Ctrl+Enter.

3. Untuk mengisi Nama Barang, sorot cell C4:C23 lalu masukkan formula:


4. Untuk mengisi Harga Satuan, sorot cell E4:E23 lalu masukkan formula:


5. Untuk mengisi Total, sorot cell F4:F23 lalu masukkan formula: =D4*E4 lalu tekan



Soal 35

Berikut ini adalah data perbandingan antara 10 bus yang melakukan perjalanan dari

Bandung menuju Yogyakarta. Tugas Anda adalah menghitung berapa lama perjalanan

tiap busnya.

Jawab:

1. Buka file FUNGSI.xlsx dan sheet DateTime1.

2. Untuk menghitung waktu tempuh, sorot cell E4:E13 lalu masukkan formula: =D4-

C4 lalu tekan kombinasi tombol Ctrl+Enter.

3. Masih dalam keadaan range cell tersebut disorot, klik kanan range tersebut lalu

pilih Format Cells sehingga muncul kotak dialog Format Cells.

4. Pada kotak dialog Format Cells pilih tab Number lalu pilih Custom. Pada kotak

isian Type masukkan: hh,mm.

5. Hasil dari perhitungan tadi tidak ada keterangan Jam dan Menitnya, untuk itu

Anda harus menambahkan keterangannya.

6. Sorot cell F4:F13, lalu masukkan formula: =HOUR(E4)&" Jam "&MINUTE(E4)&"

Menit".

Hasilnya akan tampak seperti gambar berikut:


Soal 36

Pak Rudy mempunyai beberapa barang yang dia sewa. Dia kebingungan kapan tanggal

jatuh tempo setiap barang karena banyaknya barang dan bervariasinya lama sewa.

Tugas Anda adalah membantu Pak Rudy untuk membuat tabel perhitungan tanggal

jatuh tempo barang-barang sewaannya.

Jawab:

1. Buka file FUNGSI.xlsx dan buka sheet DateTime2.

2. Untuk menghitung tanggal jatuh tempo Ruko, di cell D4 masukkan formula:

=DATE(YEAR(B4)+C4;MONTH(B4);DAY(B4)).

3. Untuk menghitung tanggal jatuh tempo Komputer, di cell D5 masukkan formula:

=DATE(YEAR(B5);MONTH(B5)+C5;DAY(B5)).

4. Untuk menghitung tanggal jatuh tempo Mobil Angkut, di cell D6 masukkan

formula: =DATE(YEAR(B6);MONTH(B6);DAY(B6)+C6).

5. Untuk menghitung tanggal jatuh tempo Alat Kantor, di cell D7 masukkan formula:

=DATE(YEAR(B7);MONTH(B7)+C7;DAY(B7)).

Hasilnya akan tampak seperti gambar berikut:


Soal 37

Berikut ini adalah data waktu penerimaan barang pemasok di sebuah toko elektronik.

Namun cara penulisan tanggalnya tidak rapi dengan berbagai cara penulisan yang

tidak seragam. Tugas Anda adalah merapikan data Tanggal penerimaan ke cara

penulisan yang seragam dan rapi dengan mengubah formatnya ke format Date. Untuk

menyelesaikan tugas ini Anda dapat menggunakan fungsi DATEVALUE.

Jawab:

1. Buka file FUNGSI.xlsx dan buka sheet DateTime3.

2. Sorot cell A4:A10 lalu masukkan formula: =DATEVALUE(A4) lalu tekan kombinasi

tombol Ctrl+Enter.

3. Masih dalam keadaan range tersebut disorot, klik kanan range tersebut lalu pilih

Format Cells sehingga muncul kotak dialog Format Cells.

4. Pada kotak dialog Format Cells pilih tab Number lalu pilih Custom. Pada kotak

isian Type masukkan: .



Soal 38

Radita berprofesi sebagai guru SMU dengan penghasilan per bulan Rp 3 Juta. Untuk

keperluan transportasi dalam tugasnya, Radita ingin membeli sebuah sepeda motor

merk Vario Techno. Setelah beberapa tahun, Radita berhasil menabung sampai

terkumpul uang sebesar Rp 5 Juta. Berhubung harga sepeda motor tersebut tidak

terjangkau jika dibeli secara tunai, Radita memutuskan untuk membeli secara kredit.

Berikut ini informasi dari sebuah dealer yang menjual sepeda motor dengan cara

kredit. Informasi ini menggambarkan penawaran kredit yang disediakan oleh Adiandra

Finance, yaitu:

Harga On the Road (OTR) Rp 15.750.000.-

Bunga 12% per tahun flat

Uang muka 30% dari harga sepeda motor

Tenor 1 tahun atau 12 bulan

Berdasarkan kriteria di atas, Anda diminta untuk menghitung berapa angsuran per

bulan yang harus dibayar oleh Radita?

Jawab:

1. Buka file FUNGSI.xlsx dan buka sheet Keuangan1.

2. Untuk mengisi Harga OTR, klik cell C4, masukkan 15.750.000.

3. Di cell B5 masukkan 30 untuk Uang Muka.

4. Klik kanan cell B5 lalu pilih Format Cells sehingga muncul kotak dialog Format

Cells.


5. Pada kotak dialog Format Cells klik tab Number lalu pilih Custom.

Pada kotak isian Type, masukkan “Uang Muka “ 0% lalu klik OK.

6. Untuk menghitung Uang Muka masukkan formula di cell C5: =B5*C4.

7. Untuk menghitung Harga yang dikreditkan masukkan formula di cell C6: =C4-C5.

8. Untuk menghitung Jangka Waktu, di cell C8 masukkan: “1” dan di cell D7

masukkan formula: =C7*12.

9. Untuk menghitung Bunga, di cell C8 masukkan: “12%” dan di cell D8 masukkan

formula: =C8/12.

10. Untuk menghitung Nilai Kredit Akhir n tahun, di cell B12 masukkan formula:

=”Nilai Kredit Akhir“&C8&” Tahun” dan di cell D12 masukkan formula:

=FV(C8;C7;;-C6).

11. Untuk menghitung Angsuran Bulan, di cell D13 masukkan formula: =D12/D7.



Soal 39

Rudy berprofesi sebagai karyawan swasta dengan penghasilan per bulan Rp 3,5 Juta.

Untuk keperluan transportasi, Rudy ingin membeli sebuah sepeda motor namu Ia

bingung sepeda motor merk apa yang harus Ia beli. Rudy ingin mengetahui

perbandingan perhitungan kredit masing-masing merk sepeda motor untuk

menentukan sepeda motor merk apa yang akan Ia beli.

Berikut ini informasi dari sebuah dealer yang menjual sepeda motor dengan cara

kredit. Informasi ini menggambarkan penawaran kredit yang disediakan oleh Adiandra

Finance, yaitu:

Revo FIT, Rp 11.500.000,- tenor 12 bulan bunga 17% per tahun.

Supra X 125, Rp 14.550.000,- tenor 12 bulan bunga 14% per tahun.

Beat, Rp 11.850.000,- tenor 12 bulan bunga 18% per tahun.

Scoopy, Rp 13.750.000,- tenor 12 bulan bunga 15% per tahun.

Vario Techno, Rp 15.100.000,- tenor 12 bulan bunga 16% per tahun.

Buatlah tabel angsuran per bulan untuk masing-masing merk. Tabel yang diinginkan

adalah sebagai berikut:

Jawab:

1. Buka file FUNGSI.xlsx dan buka sheet Keuangan2.

2. Untuk mengisi Harga OTR, di cell D5 sampai H5, masukkan masing-masing OTR

nya.

3. Untuk menghitung Uang Muka, di cell B6 masukkan: 30%, lalu sorot cell D6:H6

dan masukkan formula: =$B$6*D5 lalu tekan kombinasi tombol Ctrl+Enter.

4. Klik kanan cell B6 lalu pilih Format Cells sehingga muncul kotak dialog Format

Cells.


5. Pada kotak dialog Format Cells klik tab Number lalu pilih Custom.

Pada kotak isian Type, masukkan “Uang Muka “ 0% lalu klik OK.

6. Untuk menghitung Harga yang dikreditkan, sorot cell cell D7:H7 lalu masukkan

formula: =D5-D6 lalu tekan kombinasi tombol Ctrl+Enter.

7. Untuk menghitung Jangka Waktu (Tahun), sorot cell D8:H8 lalu masukkan: “1”

lalu tekan kombinasi tombol Ctrl+Enter.

8. Untuk menghitung Jangka Waktu (Bulan), sorot cell D9:H9 masukkan formula:

=D8*12 lalu tekan kombinasi tombol Ctrl+Enter.

9. Untuk menghitung Bunga per Tahun (%),di cell D10 sampai H10 masukkan

masing-masing ketentuan bunganya.

10. Untuk menghitung Bunga per Bulan (%), sorot cell D11:H11 lalu masukkan

formula: =D10/12 lalu tekan kombinasi tombol Ctrl+Enter.

11. Untuk menghitung Nilai kredit Akhir Periode,sorot cell D15:H15 lalu masukkan

formula: =FV(D10;D8;;-D7) lalu tekan kombinasi tombol Ctrl+Enter.

12. Untuk menghitung cicilan per Tahun, sorot cell D16:H16 lalu masukkan formula:

=D15/D8 lalu tekan kombinasi tombol Ctrl+Enter.

13. Untuk menghitung Cicilan per Bulan, sorot cell D17:H17 lalu masukkan formula:

=D15/D9 lalu tekan kombinasi tombol Ctrl+Enter.



Soal 40

Berikut data mengenai laporan pengiriman barang di sebuah perusahaan melalui jalur

udara. Untuk memudahkan membaca laporan tersebut buatlah rekap jumlah barang

yang sudah terkirim dan yang masih pending berdasarkan kota.

Jawab:

1. Buka file FUNGSI.xlsx dan buka sheet Gabung1.

2. Untuk menghitung Total per Kota, Sorot cell C29:C31 lalu masukkan formula:

=COUNTIF($C$6:$C$25;B29) lalu tekan kombinasi tombol Ctrl+Enter.

3. Untuk menghitung Total, sorot cell C32:E32 lalu masukkan formula:

=SUM(C29:C31) lalu tekan kombinasi tombol Ctrl+Enter.

4. Untuk menghitung jumlah barang yang masih pending per Kota, sorot cell

D29:D31 lalu masukkan formula:

=SUMPRODUCT(($C$6:$C$25=$B29)*($E$6:$E$25=$D$28)) lalu tekan kombinasi

tombol Ctrl+Enter.

5. Untuk menghitung jumlah barang yang sudah diterima per Kota, sorot cell

E29:E31 lalu masukkan formula:

=SUMPRODUCT(($C$6:$C$25=$B29)*($E$6:$E$25=$E$28)) lalu tekan kombinasi

tombol Ctrl+Enter.



Soal 41

Sebuah toko alat tulis ingin membuat laporan bulanan dengan menampilkan informasi

barang apa saja yang terjual hari itu, berapa banyak dan dilengkapi dengan harga jual

dan harga setelah diskon. Anda diminta membuat tabel yang menunjukkan banyaknya

barang yang terjual, total sebelum diskon dan total setelah diskon seperti gambar

berikut. Tabel di sebelah kiri merupakan tabel laporan yang diminta sedangkan tabel

di sebelah kanan merupakan data sumber mengenai harga barang beserta diskonnya.

Jawab:


2. Untuk menghitung Total Harga Brutto, Sorot cell C4:C12 lalu masukkan formula:

=VLOOKUP(A4;$F$3:$H$12;2)*B4 lalu tekan kombinasi tombol Ctrl+Enter.

3. Untuk menghitung Total Harga Netto, Sorot cell D4:D12 lalu masukkan formula:

=(VLOOKUP(A4;$F$3:$H$12;2)-VLOOKUP(A4;$F$3:$H$12;3))*B4 lalu tekan


4. Untuk menghitung Total sorot cell B13:D13 lalu masukkan formula: =SUM(B4:B12)

lalu tekan kombinasi tombol Ctrl+Enter.



Soal 42

Sebuah taman bacaan mempunyai data sebagai berikut. Anda diminta menghitung

berapa banyak buku yang dipinjam di setiap peminjam dan berapa buku yang belum

dikembalikan dan sudah dikembalikan.

Jawab:


2. Untuk menghitung Pinjaman Toni, di cell C9 masukkan formula:

=SUMIF(B2:B8;B3;C2:C8).

3. Untuk menghitung Pinjaman Tono, di cell C10 masukkan formula:


4. Untuk menghitung Pinjaman Tini, di cell C9 masukkan formula:


5. Untuk menghitung Buku yang belum kembali, di cell C12 masukkan formula:

=COUNTIF(D2:D8;$D$2).

6. Untuk menghitung Buku yang sudah kembali, di cell C13 masukkan formula:

=COUNTIF(D2:D8;$D$4).



Soal 43

Berikut adalah format data laporan penginapan tamu di hotel Nyaman Sekali. Buatlah

laporan ini dengan menggunakan fungsi Lookup, IF, SUM dan fungsi gabungan.

Ketentuannya:

Tipe Kamar : Diisi dengan menggunakan fungsi Hlookup dengan tabel referensi

Tabel type kamar.

Nama Kamar : Diisi dengan menggunakan fungsi Vlookup dengan tabel referensi

Tabel Tarif Kamar.

Tarif/malam : Diisi dengan menggunakan fungsi IF dan Vlookup dengan tabel

referensi Tabel Tarif kamar.

Jumlah : = Tarif Kamar * Lama Tinggal.

Pajak : Jika lama tinggal di atas satu minggu maka pajaknya 5% dari Bayar, kurang

dari seminggu maka pajaknya 10% dari Bayar.

Total Pembayaran : =Bayar - Pajak .

Jawab:

1. Buka file FUNGSI.xlsx dan buka sheet Fungsi4.

2. Untuk pengisian Tipe Kamar, sorot cell E6:E21 lalu masukkan formula:

=HLOOKUP(C6;$B$28:$F$29;2) lalu tekan kombinasi tombol Ctrl+Enter.

3. Untuk pengisian Nama Kamar, sorot cell F6:F21 lalu masukkan formula:

=VLOOKUP(D6;$B$34:$F$36;2) lalu tekan kombinasi tombol Ctrl+Enter.

4. Untuk pengisian Tarif/malam, sorot cell G6:G21 lalu masukkan formula:

=IF(E6="FAMILI";VLOOKUP(D6;$B$34:$F$36;3);IF(E6="DOUBLE";VLOOKUP(D6;$


B$34:$F$36;4);IF(E6="SINGLE";VLOOKUP(D6;$B$34:$F$36;5);0))) lalu tekan


5. Untuk pengisian Jumlah, sorot cell I6:I21 lalu masukkan formula: =G6*H6 lalu

tekan kombinasi tombol Ctrl+Enter.

6. Untuk pengisian Pajak, sorot cell J6:J21 lalu masukkan formula:

=IF(H6>7;I6*5%;I6*10%) lalu tekan kombinasi tombol Ctrl+Enter.

7. Untuk pengisian kolom Total Pembayaran, sorot cell K6:K21 lalu masukkan

formula: =I6-J6 lalu tekan kombinas tombol Ctrl+Enter.

8. Untuk pengisian Jumlah Penerimaan, di cell D23 masukkan formula:

=SUM(K6:K21) lalu tekan Enter.

9. Untuk pengisian Jumlah Pajak, di cell D24 masukkan formula: =SUM(J6:J21) lalu

tekan Enter.

10. Untuk pengisian Penerimaan Bersih, di cell D25 masukkan formula: =D23-D24 lalu

tekan Enter.

Hasilnya dapat dilihat seperti berikut:

berlatih microsoft excel 2010

Documents