RADDINI GUSTI RAHAYU

RADDINI GUSTI RAHAYU [email protected]

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

2

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 | Daftar Isi

3

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 | Pendahuluan

4

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. 2.

Buka file TAMPILAN.xlsx dan buka sheet Tampilan1. Tempatkan penunjuk cell di B4 lalu pada tab View kategori Window klik Freeze Panes lalu pilih Freeze Panes.

Mari Berlatih Microsoft Excel 2010 | Tampilan Workbook

5

3.

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


6

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. 2.

Buka file TAMPILAN.xlsx dan buka sheet Tampilan2. 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:


7

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. 5.

Pilih TAMPILAN.xlsx:2 lalu klik OK. Fitur ini sudah aktif dan sekarang cobalah geser tampilan ke bawah atau ke kanan, maka tampilan window akan ikut bergeser secara bersamaan.


8

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.

Mari Berlatih Microsoft Excel 2010 | Menyisipkan dan Format Grafik

9

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:


10

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: 1. 2. 3.

Aktifkan file GRAFIK.xlsx dan buka sheet Grafik2. Sorot cell B4:D16. Pada tab Insert kategori Charts pilih tipe Line dan Sub-tipe Stacked Line with Markers.


11

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: 1. 2. 3.

Aktifkan file GRAFIK.xlsx dan buka sheet Grafik3. Sorot cell B5:E11. 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.


12

5. Ketika kotak dialog Chage Chart Type muncul, pilih type Line dan sub-tipe Line with Markers, sehingga tampilan grafik menjadi seperti berikut:


13

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: 1. 2. 3.

Aktifkan file GRAFIK.xlsx dan buka sheet Grafik4. Sorot cell B6:C9. Pada tab Insert kategori Charts pilih tipe Column dan sub-tipe 3-D Clustered Column. Sehingga tampilan awal grafik seperti berikut:


14

4.

5.

6. 7. 8.

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. 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.

Klik kanan data series Pisang lalu klik Format Data Series sehingga muncul kotak dialog Format Data Point. Klik tab Fill lalu aktifkan pilihan Picture or texture fill. Untuk memasukkan gambar klik tombol File lalu pilih lokasi gambar pisang yang telah disediakan. Klik tombol Insert dan Close. Ulangi langkah-langkah di atas dengan memilih gambar nanas untuk data series nanas, dan seterusnya.


15

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: 1. 2. 3.

Aktifkan file GRAFIK.xlsx dan buka sheet Grafik5. Sorot cell B5:D17. Pada tab Insert kategori Charts pilih tipe Line dan Sub-tipe Line with Markers. Sehingga tampilan awal grafik seperti berikut:

4.

Secara default, marker yang ditampilkan adalah tanda kotak. Untuk mengubahnya klik data series Pesawat. Klik kanan marker pada data series Pesawat untuk bulan Desember lalu klik Format Data Point. Pada kotak dialog Format Data Point pilih tab Fill lalu aktifkan Picture or texture fill.

5. 6.


16

7. 8. 9.

Klik tombol File lalu masukkan gambar pesawat setelah itu klik Insert. Pilih tab Marker Options lalu aktifkan pilihan Built-in. Atur ukuran gambar pesawat pada pilihan size, misalnya 30. Klik tab Marker Line Color, aktifkan No Line untuk menghilangkan border pada marker, lalu klik Close sehingga tampilannya menjadi seperti berikut:


17

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: 1. 2. 3. 4. 5. 6.

Aktifkan file GRAFIK.xlsx dan buka sheet Grafik6. Sorot cell B4:C10. Pada tab Insert kategori Charts pilih tipe Line dan Sub-tipe Stacked Line with Markers. Klik kanan area grafik lalu pilih Format Chart Area sehingga muncul kotak dialog Format Chart Area. Pada tab fill, pilih Picture or texture fill. Klik tombol File lalu pilih gambar stroberi yang Anda punya. Klik insert dan close.


18

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: 1. 2. 3.

Aktifkan file GRAFIK.xlsx dan buka sheet Grafik7. Sorot cell B5:D11. Pada tab Insert kategori Charts pilih tipe Column dan sub-tipe Clustered Column. Sehingga tampilan awal grafik seperti berikut:

4.

Ubahlah posisi kolom dan baris pada tab Charts Tools Design > Switch Row/Column sehingga hasilnya seperti berikut:


19

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. 6.

Klik kanan sumbu kategori atau sumbu horisontal lalu klik Format Axis sehingga muncul kotak dialog Format Axis. Pada tab Axis Options kategori Vertical Axis Crosses > At category Number, masukkan angka 2 lalu klik tombol Close. Hasilnya terlihat seperti gambar berikut:


20

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. 2.

Buka file PROTECT.xlsx dan buka sheet Protect1. 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. Mari Berlatih Microsoft Excel 2010 | Proteksi Data

21

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. Pada check box list Allow all users of this worksheet to: ceklis lah check box Select unlocked cells. Jika sudah klik tombol OK. 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.

6. 7.

Mari Berlatih Microsoft Excel 2010 | Proteksi Data

22

8.

Untuk membuka proteksinya, pada tab Home kategori Cells, pilih Format lalu klik Unprotect Sheet dan Anda diminta memasukkan password (jika Anda menggunakan password).


23

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. 2. 3. 4. 5. 6.

Buka file PROTECT.xlsx dan buka sheet Protect2. Pastikan seluruh cell dalam keadaan Locked, caranya pada tab Home kategori Cells pilih Format lalu pastikan tombol toogle Lock Cell dalam keadaan aktif. Sorot cell-cell yang berwarna putih atau yang merupakan cell isian Form. Pada tab Home kategori Cells klik Format lalu nonaktifkan Lock Cell. Pada tab Home kategori Cells klik Format lalu klik Protect Sheet sehingga muncul kotak dialog Protect Sheet. Masukkan password jika Anda menginginkannya lalu ceklis check box Select Unlock Cell lalu klik tombol OK.


24

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. 2. 3.

Buka file PROTECT.xlsx dan buka sheet file Protect1. Pada tab File lalu klik Save As sehingga muncul kotak dialog Save As. Pada sebelah kiri tombol Save, klik tombol Tools lalu pilih General Options sehingga muncul kotak dialog General Options.


25

4.

5.

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. 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.


26

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. 2. 3.

Buka file MANDATA.xlsx dan buka sheet Sort1. Tempatkan penunjuk cell di area tabel. Pada tab Data kategori Sort & Filter klik Sort. Sehingga muncul kotak dialog Sort.

Mari Berlatih Microsoft Excel 2010 | Manajemen Data

27

4. Pada kotak pilihan Sort By, pilhlah:

 Column :  Sort On :  Order :

Nama Value

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. 1. 2. 3.

Tempatkan penunjuk cell di area tabel. Pada tab Data kategori Sort & Filter klik Sort. Sehingga muncul kotak dialog Sort. Pada kotak pilihan Sort By, pilhlah:


Asal Sekolah Value A to Z


28

4.

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

 Column :  Sort On :  Order : 5.

Tanggal Lahir Value Oldest to Newest

Setelah itu Klik OK sehingga tampilan datanya menjadi seperti berikut:


29

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. 2. 3. 4.

5.

6.

7.

Buka file MANDATA.xlsx dan buka sheet Sort2. Tempatkan penunjuk cell di area tabel. Pada tab Data kategori Sort & Filter klik Sort. Sehingga muncul kotak dialog Sort. Pada kotak pilihan Sort By, pilhlah:


Nama


Jurusan


Tunggakan

Value

A to Z Klik tombol Add Level, lalu pada kotak pilihan Then By, pilihlah: Value

A to Z Klik tombol Add Level, lalu pada kotak pilihan Then By, pilihlah: Value Smallest to Largest

Setelah itu Klik OK sehingga tampilan datanya menjadi seperti berikut:


30


31

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. 2. 3.

Buka file MANDATA.xlsx dan buka sheet Filter1. Tempatkan penunjuk cell pada salah satu judul kolom, misalnya kolom NIM (B4). 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.


32

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.


33

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. 5.

Tentukan pilihan filter untuk jurusan Informatika dan Bisnis saja dengan langkahlangkah seperti pada jawaban soal sebelumnya. Tentukan pilihan filter untuk Tunggakan yang lebih dari 300 ribu dengan cara seperti terlihat pada gambar berikut:


34

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


35

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. 2. 3.

4.

Buka file MANDATA.xlsx dan buka sheet Validation1. Sorot cell D5:D9. Pada tab Data kategori Data Tools pilih Data Validation sehingga muncul kotak dialog Data Validation. Pada tab Setting kotak pilihan Allow pilih Whole Number dan pada kotak pilihan Data pilih Greather than dengan nilai minimum 350.

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”.


36

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:


37

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. 2. 3.

4.

Buka file VALIDATION.xlsx dan buka sheet Validation2. Sorot cell C5:C14. Pada tab ribbon Data category Data Tools pilih Data Validation sehingga muncul kotak dialog Data Validation. Pada tab Setting kotak pilihan Allow pilih Custom dengan formula “=LEFT(C5;3)=”ATK” ”.

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”.


38

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:


39

Jika Anda memasukkan data yang tidak sesuai (bukan ATK) maka akan muncul kotak peringatan seperti berikut:


40

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

Jawab: 1. 2. 3. 4.

Buka file MANDATA.xlsx dan buka sheet ConFor1. Sorot cell C4:H23. Pada tab Home kategori Styles pilih Conditional Formatting lalu klik Databars. Pilih styles mana yang Anda inginkan, misalnya Light Blue Databar sehingga hasilnya seperti berikut:


41

Soal 20 Dengan menggunakan data yang sama, buatlah format untuk cell dengan nilai penjualan di atas rata-rata diberi warna merah. Jawab: 1. 2. 3.

Buka file MANDATA.xlsx dan buka sheet ConFor2. Sorot cell C4:H23. Pada tab Home kategori Styles pilih Conditional Formatting >Top/Bottom Rules > Above Average sehingga hasilnya seperti berikut:


42

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. 2. 3.

Buka file MANDATA.xlsx dan buka sheet Subtotal1. Pastikan data sudah terurut berdasarkan Jurusan lalu tempatkan penunjuk cell pada salah satu judul kolom. Pada tab Data kategori Outline klik Subtotal sehingga muncul kotak dialog Subtotal seperti gambar berikut:


43

4.

5.

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. 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 Mari Berlatih Microsoft Excel 2010 | Manajemen Data

44

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.


45

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. 2.

Buka file PIVOT.xlsx dan buka sheet Pivot1. 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

4.

sama dengan memilih lokasinya di bagian Location. Pilih New Worksheet lalu klik OK dan berinama Lap.Penjualan. Pada sheet yang telah diberi nama tadi tampilannya seperti berikut:


46

5.

6.

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. Ikuti penempatan fieldnya sehingga tampilannya seperti berikut:


47


48

Soal 23 Dari soal sebelumnya buatlah laporan total penjualan seperti gambar berikut:

Jawab: 1. 2.

Buka file PIVOT.xlsx dan buka sheet Pivot1. 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

4.

sama dengan memilih lokasinya di bagian Location. Pilih New Worksheet lalu klik OK dan berinama Lap.Penjualan. Pada sheet yang telah diberi nama tadi tampilannya seperti berikut:


49

5.

6.

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. Ikuti penempatan fieldnya seperti gambar berikut:


50

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. 2. 3.

Buka file PRINT.xlsx dan buka sheet Print1. Sorot area laporannya yaitu cell A2:D56. 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.

Mari Berlatih Microsoft Excel 2010 | Mencetak Data

51

4.

Pada tab File pilih Print lalu lihatlah previewnya, yang tampak adalah area print yang sudah disetting tadi.


52

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. 2. 3. 4.

Buka file PRINT.xlsx dan buka sheet Print2. Pada tab Page Layout kategori Page Setup klik Print Titles sehingga muncul kotak dialog Page Setup tab Sheet. Pada kotak range Print area: masukkan area printnya yaitu cell B2:M81. Pada kotak range Rows to repeat at top masukkan range cell judul tabel dengan cara menyorot baris 5 dan 6.


53

5.

Klik tombol Print preview untuk melihat hasilnya, seperti berikut:


54

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. 2.

Buka file FUNGSI.xlsx dan buka sheet Statis1. 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.

Mari Berlatih Microsoft Excel 2010 | Menggunakan Fungsi Excel

55

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.


56

Sehingga hasil akhirnya akan tampak seperti berikut:


57

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:


58

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: 1. 2.

Buka file FUNGSI.xlsx dan buka sheet Teks2. Sorot cell B4:B20 lalu masukkan formula: =SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(A5;”-“;””);”.”;””);”/”;””) lalu tekan kombinasi tombol Ctrl+Enter.

Hasilnya dapat dilihat seperti gambar berikut:


59

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: 1. 2.

Buka file FUNGSI.xlsx dan buka sheet Teks3. 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.



60

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. 2.

Buka file FUNGSI.xlsx dan buka sheet Logika1. Sorot cell C4:C20 lalu masukkan formula: =IF(C4>60;"LULUS";"GAGAL") lalu tekan kombinasi tombol Ctrl+Enter.



61

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: 1. Buka file FUNGSI.xlsx dan buka sheet Logika2. 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 kombinasi tombol Ctrl+Enter. 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). Hasilnya dapat dilihat seperti gambar berikut:


62

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: 1. 2.

Buka file FUNGSI.xlsx dan buka sheet Logika3. Untuk mengisi Keputusan, sorot cell C4:C10 lalu masukkan formula: =IF(OR(A4="lancar";B4="cerah");"Pergi ke Jakarta";"Diam di Rumah") lalu tekan kombinasi tombol Ctrl+Enter.


63

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. 2.

Buka file FUNGSI.xlsx dan buka sheet Lookup1. Untuk mengisi Keterangan, sorot cell C4:C9 lalu masukkan formula: =HLOOKUP(B4;$F$3:$H$4;2) lalu tekan kombinasi tombol Ctrl+Enter.



64

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. 2. 3. 4. 5.

Buka file FUNGSI.xlsx dan buka sheet Lookup2. Untuk mengisi Kategori, sorot cell B4:B23 lalu masukkan formula: =VLOOKUP($A4;$H$4:$K$12;2) lalu tekan kombinasi tombol Ctrl+Enter. Untuk mengisi Nama Barang, sorot cell C4:C23 lalu masukkan formula: =VLOOKUP($A4;$H$4:$K$12;3) lalu tekan kombinasi tombol Ctrl+Enter. Untuk mengisi Harga Satuan, sorot cell E4:E23 lalu masukkan formula: =VLOOKUP($A4;$H$4:$K$12;4) lalu tekan kombinasi tombol Ctrl+Enter. Untuk mengisi Total, sorot cell F4:F23 lalu masukkan formula: =D4*E4 lalu tekan kombinasi tombol Ctrl+Enter.


65



66

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. 2. 3. 4. 5. 6.

Buka file FUNGSI.xlsx dan sheet DateTime1. Untuk menghitung waktu tempuh, sorot cell E4:E13 lalu masukkan formula: =D4C4 lalu tekan kombinasi tombol Ctrl+Enter. Masih dalam keadaan range cell tersebut disorot, klik kanan range tersebut lalu pilih Format Cells sehingga muncul kotak dialog Format Cells. Pada kotak dialog Format Cells pilih tab Number lalu pilih Custom. Pada kotak isian Type masukkan: hh,mm. Hasil dari perhitungan tadi tidak ada keterangan Jam dan Menitnya, untuk itu Anda harus menambahkan keterangannya. Sorot cell F4:F13, lalu masukkan formula: =HOUR(E4)&" Jam "&MINUTE(E4)&" Menit".

Hasilnya akan tampak seperti gambar berikut:


67

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. 2. 3. 4. 5.

Buka file FUNGSI.xlsx dan buka sheet DateTime2. Untuk menghitung tanggal jatuh tempo Ruko, di cell D4 masukkan formula: =DATE(YEAR(B4)+C4;MONTH(B4);DAY(B4)). Untuk menghitung tanggal jatuh tempo Komputer, di cell D5 masukkan formula: =DATE(YEAR(B5);MONTH(B5)+C5;DAY(B5)). Untuk menghitung tanggal jatuh tempo Mobil Angkut, di cell D6 masukkan formula: =DATE(YEAR(B6);MONTH(B6);DAY(B6)+C6). 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:


68

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. 2. 3. 4.

Buka file FUNGSI.xlsx dan buka sheet DateTime3. Sorot cell A4:A10 lalu masukkan formula: =DATEVALUE(A4) lalu tekan kombinasi tombol Ctrl+Enter. Masih dalam keadaan range tersebut disorot, klik kanan range tersebut lalu pilih Format Cells sehingga muncul kotak dialog Format Cells. Pada kotak dialog Format Cells pilih tab Number lalu pilih Custom. Pada kotak isian Type masukkan: .



69

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. 2. 3. 4.

Buka file FUNGSI.xlsx dan buka sheet Keuangan1. Untuk mengisi Harga OTR, klik cell C4, masukkan 15.750.000. Di cell B5 masukkan 30 untuk Uang Muka. Klik kanan cell B5 lalu pilih Format Cells sehingga muncul kotak dialog Format Cells.


70

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. Hasilnya dapat dilihat seperti gambar berikut:


71

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. 2. 3. 4.

Buka file FUNGSI.xlsx dan buka sheet Keuangan2. Untuk mengisi Harga OTR, di cell D5 sampai H5, masukkan masing-masing OTR nya. 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. Klik kanan cell B6 lalu pilih Format Cells sehingga muncul kotak dialog Format Cells. Mari Berlatih Microsoft Excel 2010 | Menggunakan Fungsi Excel

72

5. 6. 7. 8. 9. 10. 11. 12. 13.

Pada kotak dialog Format Cells klik tab Number lalu pilih Custom. Pada kotak isian Type, masukkan “Uang Muka “ 0% lalu klik OK. Untuk menghitung Harga yang dikreditkan, sorot cell cell D7:H7 lalu masukkan formula: =D5-D6 lalu tekan kombinasi tombol Ctrl+Enter. Untuk menghitung Jangka Waktu (Tahun), sorot cell D8:H8 lalu masukkan: “1” lalu tekan kombinasi tombol Ctrl+Enter. Untuk menghitung Jangka Waktu (Bulan), sorot cell D9:H9 masukkan formula: =D8*12 lalu tekan kombinasi tombol Ctrl+Enter. Untuk menghitung Bunga per Tahun (%),di cell D10 sampai H10 masukkan masing-masing ketentuan bunganya. Untuk menghitung Bunga per Bulan (%), sorot cell D11:H11 lalu masukkan formula: =D10/12 lalu tekan kombinasi tombol Ctrl+Enter. Untuk menghitung Nilai kredit Akhir Periode,sorot cell D15:H15 lalu masukkan formula: =FV(D10;D8;;-D7) lalu tekan kombinasi tombol Ctrl+Enter. Untuk menghitung cicilan per Tahun, sorot cell D16:H16 lalu masukkan formula: =D15/D8 lalu tekan kombinasi tombol Ctrl+Enter. Untuk menghitung Cicilan per Bulan, sorot cell D17:H17 lalu masukkan formula: =D15/D9 lalu tekan kombinasi tombol Ctrl+Enter.



73

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. 2. 3. 4.

5.

Buka file FUNGSI.xlsx dan buka sheet Gabung1. Untuk menghitung Total per Kota, Sorot cell C29:C31 lalu masukkan formula: =COUNTIF($C$6:$C$25;B29) lalu tekan kombinasi tombol Ctrl+Enter. Untuk menghitung Total, sorot cell C32:E32 lalu masukkan formula: =SUM(C29:C31) lalu tekan kombinasi tombol Ctrl+Enter. 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. 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.



74


75

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: 1. Buka file FUNGSI.xlsx dan buka sheet Gabung2. 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 kombinasi tombol Ctrl+Enter. 4. Untuk menghitung Total sorot cell B13:D13 lalu masukkan formula: =SUM(B4:B12) lalu tekan kombinasi tombol Ctrl+Enter. Hasilnya dapat dilihat seperti gambar berikut:


76

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: 1. Buka file FUNGSI.xlsx dan buka sheet Gabung3. 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: =SUMIF(B2:B8;B7;C2:C8). 4. Untuk menghitung Pinjaman Tini, di cell C9 masukkan formula: =SUMIF(B2:B8;B6;C2:C8). 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). Hasilnya dapat dilihat seperti gambar berikut:


77

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.



Total Pembayaran : =Bayar - Pajak

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

Jawab: 1. 2. 3. 4.

Buka file FUNGSI.xlsx dan buka sheet Fungsi4. Untuk pengisian Tipe Kamar, sorot cell E6:E21 lalu masukkan formula: =HLOOKUP(C6;$B$28:$F$29;2) lalu tekan kombinasi tombol Ctrl+Enter. Untuk pengisian Nama Kamar, sorot cell F6:F21 lalu masukkan formula: =VLOOKUP(D6;$B$34:$F$36;2) lalu tekan kombinasi tombol Ctrl+Enter. 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;$


78

B$34:$F$36;4);IF(E6="SINGLE";VLOOKUP(D6;$B$34:$F$36;5);0))) lalu tekan kombinasi tombol Ctrl+Enter. 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:


79

RADDINI GUSTI RAHAYU

Recommend Documents