Buku panduan belajar excel 2010

Microsoft Excel 2010

Bekerja dengan Pengolah Angka| 51

1. Bekerja dengan Pengolah Angka

Mengenal Anatomi Excel

Mempersiapkan Buku Kerja

Mengatur Tayangan Workbook

Macam Data pada Excel

Bentuk Tampilan Data

Membuat Tabel

Cara-cara Menghapus

Rumus & Fungsi

adalah sebuah program spreadsheet yang terpopuler. Penggunaan yang paling menonjol adalah untuk perhitungan numerik, namun

ternyata bisa juga untuk aplikasi non numerik. Jadi Excel adalah program yang serbaguna. Berikut ini adalah beberapa kegunaan Excel:

a. Mengolah angka, misalnya membuat perhitungan anggaran, menganalisis hasil survey, dan melakukan hampir semua jenis analisis keuangan.

b. Membuat grafik dengan berbagai variasi pengaturan tampilannya. c. Pengorganisasian daftar sesuai tata letak baris dan kolom sehingga

pengelolaan data lebih efisien.

d. Mampu mengakses data lainnya karena memiliki fasilitas impor data dari berbagai sumber.

e. Membuat panel kontrol grafis yang memungkinkan meringkas sejumlah besar informasi bisnis dalam format yang ringkas.

f. Membuat grafik dan diagram dengan berbagai bentuk bangun dan SmartArt yang lebih profesional.

g. Otomatisasi kerja atau tugas yang kompleks dengan hanya menekan klik mouse karena fasilitas makro yang dimilikinya.

Sebelum menggunakan Excel dengan berbagai kemampuan yang dimilikinya itu. Alangkah lebih baik jika mengenal terlebih dahulu anatomi atau bagian-bagian dan elemen dari tampilan program Excel, dengan jalan ini kita akan terbiasa dengan berbagai istilah yang digunakan nanti dan tidak ada kendala saat menggunakan excel untuk berbagai keperluan kita.

1.1. Mengenal Anatomi Excel Pastikan program Excel telah ter-install, seperti Microsoft Word, aktifkan Microsoft Excel dengan cara: klik tombol Start, lalu carilah program group Microsoft Office. Dan klik pada Microsoft Excel 2010. Beberapa saat kemudian pada layar monitor akan ditampilkan layout atau tampilan standar buku kerja (workbook) Excel yang baru. Seperti gambar berikut inilah tampilan standar buku kerja (workbook) Excel berikut nama-nama bagian-bagian dan elemennya

Excel

1

e-biz education enterprise

52 | Bekerja dengan Pengolah Angka

Bagian-bagian dan elemen dari Excel yang perlu diketahui:

Nama Penjelasan

Active Cell Indicator Menunjukkan lokasi sel aktif dan berhubungan dg keyboard. Sel aktif pada gambar diatas adalah kolom C, baris 3 atau ditulis C3.

Cell Bagian terkecil dari lembar kerja. Perpotongan antara kolom dan baris, atau satu kotak pada lembar kerja disebut sel (misalnya sel C3). Sedangkan range adalah kumpulan sel yang membentuk persegi, misalnya sel A1 sampai C3 ditandai (ditulis A1..C3), itulah yang disebut range.

Column Letters Kolom lembar kerja Excel yang ditandai atau diberi label huruf A, B, C Z, AA, AB AZ, AAA,AAB XFD berjumlah 16.384 kolom dalam suatu lembar kerja (worksheet). Klik label kolom (column header) untuk mengaktifkan satu lajur kolom. Drag garis pembatas kolom (border column) untuk mengubah lebar kolom.

File Tab Backstage View, klik ini jika ingin bekerja dengan berbagai perintah dokumen seperti menyimpan, mencetak, dsb. Atau mengatur setting tampilan Excel.

Formula Bar Isi sel akan ditampilkan pada baris ini jika Indikator Sel (Active cell indicator) berada pada sel tersebut.

Gridlines Garis-garis yang ada pada lembar kerja, yang membatasi kolom dan baris.

Insert Function Menampilkan sekumpulan berbagai fungsi Excel Insert Worksheet Button Menyisipkan lembar kerja (Worksheet) baru. Secara default ada 3

sheet dalam sebuah buku kerja (Workbook). Name Box Menunjukkan posisi Indikator Sel (Active Cell Indicator) atau nama

sel, range atau object aktif. Page View Button Untuk memilih mode tampilan lembar kerja Excel: Normal, Page

Layout, Page Break Preview. Pointer Mouse Simbol Plus besar warna putih yang berhubungan dengan gerakan

mouse. Klik kanan untuk menampilkan pilihan pop-up menu /

File Tab Tabs Title Bar

Quick Access Toolbar Name Box

Row Numbers

Column Letters

Formula Bar

Insert Function Pointer Mouse

Active Cell Indicator

Sheet Tab Scroll Button

Insert Worksheet Button

Page View Button

Ribbon

Microsoft Excel 2010

Bekerja dengan Pengolah Angka| 53

perintah (Contextual menu). Klik kiri untuk memilih. Klik ganda (double klik) untuk menjalankan pilihan (eksekusi).

Quick Access Toolbar Untuk mengatur penampilan ikon-ikon perintah Excel sehingga akses perintah tersebut lebih cepat sebab selalu tampil diatas Tabs.

Ribbon Groups Berbagai perintah utama Excel dikelompokkan pada bagian ini. Row Numbers Baris lembar kerja Excel yang ditandai atau diberi label angka 1, 2

1.048.576. Klik label baris untuk mengaktifkan satu lajur baris. Drag garis pembatas baris untuk mengubah lebar baris.

Sheet Tab Scroll Button Klik tombol ini untuk menggeser lembar kerja (sheet) yang tidak kelihatan karena banyaknya sheet yang ada.

Tabs Klik nama-nama kelompok perintah (=menu) untuk menampilkan ribbon Excel.

Title Bar Baris judul yang berisi nama file dan program aktif.

1.2. Mempersiapkan Buku Kerja Sebelum bekerja dengan Excel, buku kerja yang digunakan harus dipersiapkan dulu. Excel menyediakan berbagai bentuk buku kerja: buku kerja kosong yang masih baru (blank workbook), buku kerja yang pernah dirancang sendiri (My templates) atau berbagai bentuk buku kerja yang telah tersedia siap pakai (templates) atau siap di download dari office.com. Cara memilih bentuk buku kerja Excel: pilih Tab File, lalu klik New. Berbagai bentuk buku kerja (workbook) akan ditampilkan seperti ini

Pilih salah satu bentuk workbook, lalu klik Create. Jika dipilih dari office.com Templates: pilih salah satu, lalu klik Download. Misalnya, pilih blank workbook, lalu klik Create. Maka dilayar akan ditampilkan buku kerja (workbook) baru yang masih kosong. Pada workbook tersebut terdapat 3 lembar kerja (sheet). Tiap-tiap sheet berisi baris dan kolom yang sama. Untuk menambah jumlah sheet. Klik Insert Worksheet Button

1.2.1. Mengatur layout halaman Untuk mengatur tatanan (layout) halaman atau lembar kerja Excel saat dicetak maupun ditampilkan dilayar, perintah-perintahnya ada pada Group Page Setup, antara lain:

Margins, untuk mengatur batas-batas halaman pencetakan.

Orientation, untuk mengatur orientasi halaman: tegak (portrait) atau mendatar (landscape).

Size, untuk memilih jenis kertas atau ukuran lembar kerja.

Print area, untuk menentukan area yang ditandai saja yang dicetak (Set print area) atau menghapus area yang pencetakan yang telah ditandai (Clear print area).

Breaks, untuk menyisipkan tanda pergantian halaman atau pemenggalan halaman (Insert page break), menghapus tanda pergantian halaman yang dipilih (Remove page break) atau semua tanda pergantian halaman (Reset All Page break).

e-biz education enterprise

54 | Bekerja dengan Pengolah Angka

Background, untuk memberi latar-belakang (background) lembar kerja dengan gambar. Namun, gambar background ini tidak bisa dicetak.

Print Titles, untuk menentukan alamat baris dan kolom yang diulang saat setiap halaman dicetak.

Atau klik pada quick launcher Page Setup, yang akan menampilkan dialog box Page Setup yang berisi berbagai perintah yang nanti akan dibahas tersendiri pada bagian pencetakkan.

1.2.2. Mengelola Sheet Sheet bisa diberi nama, di-copy atau diberi warna yang berbeda-beda. Caranya: klik kanan salah satu sheet, misalnya klik kanan Sheet1. Maka pada Sheet1 akan ditampilkan pilihan perintah seperti berikut

Klik Insert, untuk menyisipkan Sheet baru (bisa berupa Worksheet, Chart atau Macro) disebelah kiri Sheet1. Klik Delete, untuk menghapus Sheet1. Klik Rename, untuk mengganti nama Sheet1. Klik Move or Copy, untuk memindah atau menyalin Sheet1. Klik View Code, menampilkan editor Microsoft Visual Basic Application. Klik Protect Sheet, untuk memproteksi Sheet1. Klik Tab Color, untuk memberi warna Sheet1.

Klik Hide, untuk menyembunyikan Sheet1. Tampilkan kembali dengan memilih Unhide. Klik Select All Sheets, untuk mengaktifkan semua Sheet yang ada.

1.2.3. Mengatur penampilan bagian-bagian Sheet Bagian-bagian utama Sheet antara lain: Gridlines dan Headings. Gridlines adalah garis-garis horisontal dan vertikal yang membentuk lembar kerja menjadi kolom dan baris. Headings adalah label huruf yang menandai kolom (Column Letters) dan label angka yang menandai baris (Row Numbers). Gridlines dan Headlines, mempermudah membaca dan menempatkan data yang akan diolah. Pada Tab Page Layout, Group Sheet Options terdapat checkbox ()untuk menampilkan atau menyembunyikan serta mencetak Gridlines dan Headlines.

Untuk pengaturan penampilan bagian-bagian sheet lebih lanjut, bisa juga dari Office Button, lalu pilih Excel Options. Dialog box Excel Options akan ditampilkan, klik pilihan Advanced. Pada bagian Display options for this worksheet, aktifkan pilihan yang diinginkan: termasuk gridlines color, untuk memilih warna gridlines.

1.2.4. Mengubah ukuran kolom atau baris Mengubah lebar kolom, caranya:

klik pada label kolom yang berupa huruf (Column Letter).

Lalu drag garis batas kolom (border column) ke kiri atau ke kanan. Jika beberapa kolom berurutan sekaligus ukuran lebarnya disamakan, misalnya kolom A hingga E. Caranya:

Klik label kolom A sampai E.

Jika kolom A sampai E telah diblok, lakukan drag pada salah satu garis batas kolom. Untuk mengubah ukuran baris, caranya sama dengan diatas tapi lakukan pada garis batas baris (border row).

Microsoft Excel 2010

Bekerja dengan Pengolah Angka| 55

Cara lainnya adalah:

klik kanan pada label kolom atau baris.

Jika ditampilkan pilihan perintah atau menu, pilih Column Width atau Row Height.

Dialog box Column Width atau Row Height akan ditampilkan, isilah berapa lebar kolom atau tinggi baris yang diinginkan.

Akhiri dengan klik OK.

1.2.5. Mengelola Sel dengan Ribbon Group Cells Excel 2007 juga menyediakan berbagai perintah untuk mengelola sel yang dikelompokkan dalam Ribbon Group Cells. Berbagai perintah yang terdapat pada Ribbon Group Cells adalah:

Insert, berisi berbagai perintah yang digunakan untuk menyisipkan, antara lain: Insert Cells (untuk menyisipkan sel), Insert Sheet Rows (untuk menyisipkan satu baris sel kosong diatas indikator sel), Insert Sheet Columns (untuk menyisipkan satu kolom kosong disebelah kiri indikator sel) dan Insert Sheet (untuk menyisipkan lembar kerja kosong di sebelah kiri

lembar kerja aktif). Ketika dipilih perintah Insert Cells, maka akan ditampilkan dialog box Insert seperti ini

Shift cells right, menyisipkan sel pada posisi indikator sel dan sel aktif bergeser ke kanan.

Shift cells down, menyisipkan sel pada posisi indikator sel dan sel aktif bergeser ke bawah.

Entire row, menyisipkan satu baris sel kosong diatas indikator sel. Entire Column, menyisipkan satu kolom kosong disebelah kiri

indikator sel.

Delete, berisi berbagai perintah yang digunakan untuk menghapus, antara lain: Delete Cells (untuk menghapus sel), Delete Sheet Rows (untuk menghapus satu baris), Delete Sheet Columns (menghapus satu kolom), Delete Sheet (menghapus lembar kerja). Ketika dipilih Delete Cells, maka akan ditampilkan dialog box Delete seperti ini

Shift cells left, menghapus sel dimana indikator sel berada dan sel bergeser ke kiri.

Shift cells up, menghapus sel dimana indikator sel berada dan sel bergeser ke atas.

Entire row, menghapus satu baris sel. Entire column, menghapus satu kolom sel.

Format, berisi berbagai perintah yang berhubungan dengan tampilan sel dan lembar kerja (Sheet), antara lain:

Bagian Cell Size, antara lain berisi perintah: Row Height, mengubah tinggi baris; Autofit Row Height, otomatis menyesuaikan tinggi isi sel; Column Width, mengubah lebar kolom; Autofit Column Width, otomatis menyesuaikan lebar isi sel, Default Width, kembali ke lebar standar kolom. Bagian Visibility, antara lain berisi perintah: Hide Rows, menyembunyikan baris; Hide Column, menyembunyikan kolom; Hide Sheet, menyembunyikan lembar kerja; Unhide Rows, menampilkan baris yang tersembunyi; Unhide Columns, menampilkan kolom yang tersembunyi; Unhide Sheet, menyembunyikan lembar kerja yang tersembunyi.

e-biz education enterprise

56 | Bekerja dengan Pengolah Angka

Bagian Organize Sheets, antara lain berisi perintah: Rename Sheet, untuk mengganti nama lembar kerja; Move or Copy Sheet, memindah atau menyalin lembar kerja; Tab Color, memberi warna tab lembar kerja. Ketika dipilih Move or Copy Sheet , maka akan ditampilkan dialog box Move or Copy seperti ini .

Tentukan lembar kerja tujuan pemindahan (Move) atau penyalinan (Copy) pada bagian Move selected sheets To book:

Tentukan posisi lembar kerja yang dipindahkan atau disalin akan di-letakkan sebelum lembar kerja yang mana pada bagian Before sheet:

Aktifkan Create a copy, untuk menyalin lembar kerja.

1.3. Mengatur Tayangan Workbook Tayangan buku kerja (Workbook) dapat diatur dengan sekumpulan perintah-perintah yang terdapat pada Tab View. Berikut ini adalah perintah-perintahnya:

Pada Group Workbook Views, terdapat perintah-perintah: Normal, tayangan normal standar Excel seperti nampak pada gambar workbook diatas. Potongan gambarnya sebagai berikut:

Page Layout, tayangan halaman workbook nampak seperti tayangan halaman saat dicetak, berikut ini adalah potongan gambar workbook dengan mode tayangan page layout:

Page Break Preview, menayangkan workbook berupa penggalan-penggalan halaman dan sesuai dengan tayangan pra-cetak. Gambar disamping adalah penggalan tayangan workbook dengan mode Page Break Preview:

Custom Views, menayangkan bagian-bagian tertentu dari lembar kerja yang diberi nama dan disimpan. Misalnya, isikan lebih dahulu sembarang data di sel A1 dan sel D10. Tandai sel A1..D10. Lalu klik kanan bagian yang ditandai itu, pilih Define Name. Maka akan ditampilkan dialog box New Name seperti ini

Mistar horisontal

Header Margin kanan

Margin kiri

Mistar vertikal

Microsoft Excel 2010

Bekerja dengan Pengolah Angka| 57

Pada bagian Name, berilah nama, misalnya cobaview. Bagian-bagian yang lain biarkan sesuai isi aslinya. Akhiri dengan klik OK. Setelah itu klik lagi Custom Views, maka akan ditayangkan dialog box Custom Views

Klik Add, pada dialog box Add View yang ditampilkan, ketikkan cobaview pada bagian Name. Akhiri dengan klik OK.

Sekarang coba klik Custom Views, adakah nama cobaview? Jika ada, pilih cobaview lalu klik Show. Perhatikan hasilnya!

Full Screen, menayangkan workbook memenuhi layar hanya dengan columns letter dan rows number saja (label kolom dan baris). Untuk mengakhiri mode tayangan Full Screen, tekan tombol Esc keyboard.

Pada Group Show, berisi perintah-perintah untuk menampilkan mistar (Ruler), Gridlines, Formula Bar dan Headings (label kolom dan baris).

Pada Group Zoom, berisi perintah-perintah untuk memperbesar tayangan lembar kerja: Zoom (berisi berbagai pilihan memperbesar tayangan sesuai persentasi), 100%, Zoom to Selection (memperbesar bagian lembar kerja yang aktif).

Pada Group Window, berisi perintah-perintah untuk mengatur tayangan jendela lembar kerja. Pilihan perintah New Window, membuka jendela baru yang berisi lembar kerja yang saat ini aktif. Arrange All, mengatur penayangan semua buku kerja (workbook) yang dibuka saat itu. Jika perintah ini dipilih, maka akan ditampilkan dialog box Arrange Windows seperti ini Ada 4 pilihan bentuk tayangan layar, yaitu Tiled, Horizontal, Vertical dan Cascade. Berikut ini adalah gambar bentuk tayangan tersebut

Tiled Horizontal

Vertikal Cascade

e-biz education enterprise

58 | Bekerja dengan Pengolah Angka

Freeze panez, mengatur pembekuan bagian-bagian dari lembar kerja sehingga bagian yang dibekukan tersebut selalu terlihat di layar. Ada beberapa pilihan perintah, yaitu: Freeze Panes, kolom sebelah kiri dan baris sebelah atas indikator sel aktif dibekukan sehingga kolom dan baris ini tetap terlihat meski lembar kerja digulung kebawah. Freeze Top Row, baris teratas dari lembar kerja yang dibekukan. Freeze First Column, kolom pertama yang dibekukan. Untuk menghilangkan pengaruh Freeze atau mengembalikan ke kondisi semula, pilih Unfreeze Panes. Pilihan Split, jika di klik maka lembar kerja aktif akan terbagi pada posisi indikator sel aktif. Misalnya, seluruh kolom B di tandai. Maka tampilan lembar kerja akan dibagi dua secara vertikal. Untuk menghilangkan pengaruh Split, klik lagi pilihan ini. Pilihan Hide-Unhide, digunakan untuk menyembunyikan atau menampilkan tayangan lembar kerja.

View Side by Side, jika ada 2 lembar kerja terbuka, maka keduanya akan ditayangkan berdampingan sehingga memudahkan dilakukan pembandingan diantara kedua lembar kerja tersebut. Synchronous Scrolling, jika diaktifkan maka penggeseran scrollbar berpengaruh pada kedua lembar kerja yang ditayangkan. Reset Window Position, dua lembar kerja ditayangkan bersama pada ukuran layar yang ukurannya sama.

Pilihan Save Workspace, digunakan untuk menyimpan tayangan jendela/layar saat ini sehingga dapat ditampilkan lagi jika dibutuhkan.

Pilihan Switch Windows, mengganti tayangan lembar kerja yang aktif saat ini

1.4. Macam Data pada Excel Sebagai pengolah angka, Excel membedakan data kedalam 3 dasar jenis data, yaitu: Nilai Numerik (Numerical Value), Teks dan Rumus (Formula). Selain juga data yang berupa grafik, diagram, gambar dan obyek lainnya.

Nilai numerik adalah data yang berupa angka dari 0,1,2 hingga 9 dan kombinasinya. Juga data-data yang menggunakan angka untuk menampilkannya seperti tanggal dan waktu. Teks (Text) adalah data yang berupa huruf atau abjad dari A, B, C hingga Z atau a, b, c hingga z dan kombinasinya. Teks biasanya digunakan sebagai label yang menjelaskan tampilan numerik, memberi keterangan atau komentar dan membuat tampilan numerik menjadi lebih berarti. Pengetikkan teks yang diawali angka, tetap dianggap sebagai data teks. Misalnya, di sel A1 diketik 15 orang. Di sel B1 diketik 100. Jika di sel C1 diberi rumus yang diketik seperti ini: =A1+B1. Maka, ketika di-ENTER hasil yang ditampilkan di sel C1 adalah #VALUE. Hal ini menunjukkan bahwa 15 orang adalah data teks dan bukan data numerik. Agar data tersebut dapat digunakan untuk perhitungan matematik, ketikkan 15 dan orang di sel yang berbeda. Atau gunakan perintah format sel. Bagaimana agar data tersebut dikenali Excel sebagai numerik dengan perintah format sel? Pada materi selanjutnya, hal ini akan dijelaskan.

1.5. Mengatur Bentuk Tampilan Data Sebaiknya ketika kita mengetikkan data numerik, ketik angkanya saja. Misalnya, Rp. 50.000,00, pada lembar kerja ketik angkanya saja: 50000. Agar tampilannya seperti

Microsoft Excel 2010

Bekerja dengan Pengolah Angka| 59

yang diinginkan, tandai data tersebut lalu pilih Tab Home. Pada Ribbon Group Number, pilihlah format angka yang diinginkan dibagian Number Format

Pada pilihan Number Format, kita bisa pilih berbagai format angka beserta contoh tampilannya. Pilihan Comma Style, format angkanya sama dengan Accounting Number Format, yaitu menampilkan angka dengan pemisah ribuan bedanya tanpa simbol mata uang. Increase Decimal, untuk menambah digit desimal sehingga menambah tinggi tingkat akurasi data. Sedangkan Decrease Decimal, untuk mengurangi digit desimal. Untuk format lainnya yang tidak terdapat pada pilihan Number Format, pilihlah More Number Formats atau klik quick launcher Number. Maka akan ditampilkan dialog box Format Cells, pilihlah Custom yang ada pada Tab Number seperti ini

Pada materi sebelumnya digunakan data: 15 orang untuk perhitungan matematik. Agar data tersebut dikenali sebagai data numerik, caranya: pada dialog box disamping pilih format General atau 0. Lalu di bagian Type ketik General orang atau 0 orang.

Data yang ditampilkan pada lembar kerja Excel, bisa juga diatur perataan dan orientasinya. Caranya: pada dialog box Format Cells, pilih Tab Alignment

Pada bagian Text alignment, bisa dipilih berbagai pengaturan untuk perataan teks atau data numerik. Pilihlah pada bagian Horizontal untuk perataan teks secara horisontal rata kiri (left), tengah (center), kanan (right), diulang untuk mengisi ukuran sel horisontal (fill), rata kiri-kanan (justify), rata tengah diantara beberapa sel yang ditandai (center across selection), diratakan ke seluruh bagian sel (distributed). Bagian Vertical, terdapat berbagai pilihan untuk mengatur tampilan teks secara vertikal rata keatas (top), tengah (center), kebawah (bottom), justify dan distributed.

Pada bagian Text Control: aktifkan Wrap text, untuk melakukan pelipatan teks (artinya: jika ukuran data melebihi lebar sel, maka data berikutnya otomatis turun ganti baris tapi pada sel yang sama); aktifkan Shrink to fit, maka ukuran teks akan menyusut sesuai ukuran sel; dan aktifkan Merge cells, untuk menggabungkan beberapa sel jadi satu.

Sedangkan pada bagian Orientation, teks bisa ditampilkan sesuai orientasi yang diinginkan: vertikal menurun, diagonal, dan sebagainya.

Pengaturan alignment teks bisa juga dipilih pada Group Alignment di Tab Home berikut ini

Accounting Number

Format

Percent Style Comma Style

Increase Decimal

Decrease Decimal

Contoh tampilan

format sel yang dipilih

Ketik disini format sel

yang diinginkan

Pilihan format sel yang

disediakan Excel

e-biz education enterprise

60 | Bekerja dengan Pengolah Angka

1.6. Membuat Tabel Tabel atau bingkai (border) pada lembar kerja Excel bisa dibuat sebelum ataupun sesudah data diketikkan ke dalam lembar kerja. Caranya: tandai sel yang hendak diberi bingkai. Dari Tab Home, klik Borders yang ada pada Group Font.

Atau jika dipilih More Borders.. maka Dialog box Format Cells pada Tab Border akan ditampilkan gambar disamping. Pada prinsipnya sama fungsi bagian-bagian perintahnya, hanya saja jika membuat bingkai dengan pilihan perintah yang ada pada dialog box ini, sel-sel data harus ditandai lebih dahulu. Bedanya hanya satu, yaitu kita bisa membuat garis diagonal pada bingkai.

1.7. Cara-cara Menghapus Ada berbagai cara penghapusan yang disediakan oleh Excel, yaitu:

Cara I: tandai sel-sel yang berisi data yang akan dihapus, lalu tekan tombol Delete pada Keyboard. Cara ini hanya menghapus isi sel saja, sedangkan garis, bingkai dan format sel tetap berlaku.

Cara II: klik kanan pada sel-sel yang ditandai, lalu pilih Clear Contents pada pilihan perintah yang ditampilkan (pop-up menu). Cara ini juga menghapus isi sel saja, garis, bingkai dan format sel tetap berlaku.

Cara III: untuk menghapus bingkainya saja, tandai sel-sel yang berbingkai. Pilih Tab Home, klik drop-down (panah bawah) yang ada pada ikon Border pada Group Font. Cari pilihan perintah No Border.

Cara IV: pilih Tab Home, klik drop-down (panah bawah) yang ada pada ikon Border pada Group Font. Cari pilihan perintah Erase Border. Hapus garis-garis bingkai dengan meng-klik garis atau drag garis yang akan dihapus. Cara ini juga hanya menghapus garis-garis border saja.

Cara V: tandai sel-sel yang berisi data yang akan dihapus, klik kanan pada data tersebut lalu pilih Delete Jika perintah ini dipilih, maka akan ditampilkan dialog box Delete gambar disamping ini

Pilih Shift cell left, untuk menghapus dan menggeser sel kekiri. Pilih Shift cell up, untuk menghapus dan menggeser sel ke atas. Pilih Entire row, untuk menghapus seluruh baris dan seluruh sel

bergeser ke atas. Pilih Entire column, untuk menghapus seluruh kolom dan seluruh

kolom bergeser kekiri.

Cara VI: tandai sel-sel yang berisi data yang akan dihapus, pilih Tab Home. Pada Ribbon Group Cells, pilih Delete.

Cara VII: tandai sel-sel yang berisi data yang akan dihapus, pilih Tab Home. Pada Ribbon Group Editing, pilih Clear. Ada beberapa pilihan Clear sebagai berikut:

Clear All, menghapus semua yang ada pada sel-sel yang ditandai termasuk bingkai, garis, dan format sel. Clear Formats, menghapus semua format sel-nya saja. Clear Contents, menghapus semua isi sel-nya saja. Clear Comments, menghapus semua komentar (Comment) saja.

Microsoft Excel 2010

Bekerja dengan Pengolah Angka| 61

1.8. Rumus & Fungsi Rumus (Formula) dan fungsi (Function) amatlah vital dan akan sering digunakan dalam pengolahan angka. Sebab itu pada bagian ini akan dijelaskan hal-hal yang perlu diketahui dalam pemakaian rumus dan fungsi.

1.8.1. Rumus Formula atau rumus digunakan untuk melakukan perhitungan tertentu atau memroses data sesuai ketentuan. Cara pemberian rumus harus diawali =. Excel memroses rumus sesuai hirarkhi (urutan derajat/kepentingan). Hirarkhi operator matematika adalah sebagai berikut:

Hirarkhi Operator/ekspresi

1 ( ) dalam tanda kurung

2 ^ pangkat

3 * kali

4 + penjumlahan pengurangan / pembagian

Rumus yang sifatnya tetap dan baku bisa diprogram sedemikian sehingga mempersingkat penulisannya. Rumus yang terprogram disebut Fungsi (Function).

1.8.2. Macam-macam Fungsi Excel menyediakan berbagai bentuk fungsi yang dikelompokkan dalam beberapa kategori, antara lain: fungsi Financial, Date & Time, Math & Trig, Statistical, Lookup & Reference, Database, Text, Logical, Information, Engineering dan sebagainya. Fungsi bisa diketikkan langsung di sel yang diinginkan, menggunakan dialog box Function Argument atau pilihan perintah yang ada pada Tab Home Function. Langkah-langkahnya adalah:

a. Menggunakan fungsi dengan dialog box Function Argument: 1. Gunakan data seperti gambar diatas. Letakkan indikator sel aktif di sel C7. 2. Klik Insert Function yang terletak di

sudut kiri Formula Bar.

3. Maka akan ditampilkan dialog box Insert Function seperti ini

Pilih kategori Math & Trig disini

Pilih fungsi SUM

Akhiri dengan klik OK!

4. Setelah itu akan ditampilkan dialog box Function Argument seperti ini

Tentukan disini alamat sel yang akan diproses

e-biz education enterprise

62 | Bekerja dengan Pengolah Angka

b. Menggunakan fungsi dengan Tab Home Insert Function: 1. Pilih Tab Home, pada Ribbon Group Editing klik insert function. 2. Pilih fungsi yang diinginkan pada pilihan fungsi-fungsi

yang ada. 3. Tentukan alamat sel yang akan diproses.

1.8.3. Macam-macam Fungsi Kita bisa juga menggunakan berbagai macam fungsi Excel yang ada pada Tab Formulas, Ribbon Group Function Library berikut ini

Namun, hanya beberapa fungsi-fungsi yang penting dan sering digunakan saja yang akan dijelaskan pada materi berikut ini.

Jika diketahui data seperti lembar kerja disamping ini

Maka, jika diproses menggunakan macam-macam fungsi Excel berikut ini, hasilnya:

Kategori Nama Fungsi

Math & Trig :

=ABS(bilangan) Menghasilkan nilai absolut dari sebuah bilangan. Contoh: =ABS(-4) hasilnya: 4 =ABS(4) hasilnya: 4

=CEILING(bilangan, basis pembulatan) Membulatkan bilangan ke atas sesuai basis pembulatan. Contoh: =CEILING(23575,1000) hasilnya: 24000 =CEILING(23575,100) hasilnya: 23600

=FLOOR(bilangan, basis pembulatan) Membulatkan bilangan ke bawah sesuai basis pembulatan. Contoh: =FLOOR(23575,1000) hasilnya: 23000 =FLOOR(23575,100) hasilnya: 23500

=INT(bilangan) Membulatkan ke nilai terendah integer. Contoh: =INT(2.89) hasilnya: 2 =INT(-2.89) hasilnya: -3

=MOD(bilangan, pembagi) Mencari sisa pembagian. Contoh: =MOD(9,2) hasilnya: 1 =MOD(12,5) hasilnya: 2

=PRODUCT(bilangan1,[ bilangan2],[ bilangan3],) Mengalikan isi sel. Contoh: =PRODUCT(1,5,9) hasilnya: 45 =PRODUCT(A1:A2) hasilnya: 18

=ROUND(bilangan, digit pembulatan) Membulatkan sesuai digit tertentu. Contoh: =ROUND(17565,-3) hasilnya: 18000 =ROUND(17565.48,1) hasilnya: 17565.5

=ROUNDDOWN(bilangan, digit pembulatan) Membulatkan ke bawah sesuai digit tertentu. Contoh: =ROUNDDOWN(17565,-3) hasilnya: 17000 =ROUNDDOWN(17565.48,1) hasilnya: 17565.4

Microsoft Excel 2010

Bekerja dengan Pengolah Angka| 63

=ROUNDUP(bilangan, digit pembulatan) Membulatkan ke atas sesuai digit tertentu. Contoh: =ROUNDUP(17565,-3) hasilnya: 18000 =ROUNDUP(17565.48,1) hasilnya: 17565.5

=SUBTOTAL(kode fungsi Mat, range nilai) Mencari hasil sesuai kode fungsi matematika range sel tertentu. Contoh: =SUBTOTAL(9,A1:A6) hasilnya: 35 (9=SUM) =SUBTOTAL(4,A1:A6) hasilnya: 9 (4=MAX)

=SUM(bilangan1,[ bilangan2],[ bilangan3],) Menjumlahkan isi sel. Contoh: =SUM(1,5,9) hasilnya: 15 =SUM(A1:A2) hasilnya: 9

=SUMIF(range, criteria) Menjumlahkan isi sel yang sesuai kriteria. Contoh: =SUMIF(A1:A6,5) hasilnya: 5 =SUMIF(A1:A6,5) hasilnya: 7.67

=AVERAGEIFS(range yang dirata-rata,range kriteria,kriteria) Menghitung rata-rata isi sel yang memenuhi beberapa kriteria. Contoh: =AVERAGEIFS(A1:A6,A1:A6,">4",A1:A6,"

e-biz education enterprise

64 | Bekerja dengan Pengolah Angka

=COUNTIFS(range kriteria1, kriteria1, range kriteria2, kriteria2) Menghitung banyaknya sel yang berisi angka yang sesuai dengan beberapa kriteria. Contoh: =COUNTIFS(A1:A6,>4,A1:A6,

Microsoft Excel 2010

Bekerja dengan Pengolah Angka| 65

=YEAR(alamat tanggal) Menghasilkan tahun dari suatu tanggal. Contoh: =YEAR(A1) hasilnya: 2011

=MONTH(alamat tanggal) Menghasilkan bulan dari suatu tanggal. Contoh: =MONTH(A1) hasilnya: 2

Misalnya di sel B1 berisi data: AR200715

Text:

=CONCATENATE(teks1, teks2, ) Menggabungkan teks. Contoh: =CONCATENATE(SUKA, CITA) hasilnya: SUKA CITA =SUKA& CITA hasilnya: SUKA CITA

=LEFT(text,[sejumlah n]) Menampilkan teks disebelah kiri sejumlah n. Contoh: =LEFT(B1,2) hasilnya: AR

=LEN(text) Menghitung panjang teks atau banyaknya karakter teks. Contoh: LEN(B1) hasilnya: 8

=LOWER(teks) Mengubah teks menjadi huruf kecil semua. Contoh: =LOWER(SURAbaya) hasilnya: surabaya

=MID(text, posisi ke-n,sejumlah n) Menampilkan teks dari posisi ke-n dari kiri, sejumlah n. Contoh: =MID(B1,3,4) hasilnya: 2007

=RIGHT(text,[sejumlah n]) Menampilkan teks disebelah kanan sejumlah n. Contoh: =RIGHT(B1,2) hasilnya: 15

=UPPER(teks) Mengubah teks menjadi huruf besar semua. Contoh: =UPPER(surabaya) hasilnya: SURABAYA

=VALUE(teks) Mengubah angka berjenis label menjadi berjenis value. Contoh: =VALUE($100) hasilnya: 100

PRAKTIKUM 1

1. Buatlah tabel seperti gambar dibawah ini, dengan ketentuan:

a. Buatlah di alamat baris dan kolom yang sama dengan gambar (ingat satu sel untuk satu data).

b. Kolom: TOTAL; baris: JUMLAH, RATA-RATA, TERBANYAK, TERENDAH diisi menggunakan formula (rumus) atau function (fungsi).

c. Simpan dengan nama PRAKTIKUM-1. d. Beri nama sheet1 dengan nama TABEL-1, dan beri warna biru.

e-biz education enterprise

66 | Bekerja dengan Pengolah Angka

2. Sekarang buatlah tabel berikut ini, di sheet2

a. Data masukkan (Input Data: data yang langsung diketik) adalah NEGARA, JUMLAH INVESTASI dan PERKIRAAN NILAI TUKAR RUPIAH. Selain itu gunakan rumus dan fungsi.

b. Beri nama sheet2 dengan nama TABEL-2, dan beri warna lain.

3. Sekarang ikuti langkah-langkah berikut ini:

a. Bukalah lembar kerja Excel yang baru (Book2). b. Atur penampilannya dengan mengaktifkan Tab View, lalu klik Arrange All

Vertical.

c. Copy-kan TABEL-2 ke lembar kerja baru (Book2) mulai kolom A. d. Aktifkan Book2, pilih perintah Freeze Panes Freeze First Column. Perhatikan

apa yang terjadi!

e. Non aktifkan Freeze Panes. Close (tutup) Book2 dan tidak perlu disimpan. f. Simpan praktikum tetap dengan nama PRAKTIKUM-1.

Microsoft Excel 2010

Mengolah Data Sel | 67

2. Mengolah Data Sel

Memasukkan Data dengan Fill Handle

Menggunakan Fasilitas Autofill

Menyalin Data

Alamat Sel

Fungsi dan Operator Logika

Yang dimaksud mengolah data sel disini adalah bagaimana memasukkan data ke dalam sel lembar kerja (worksheet), bagaimana cara menyalin data sel, bagaimana membuat data berurutan dalam sel, apa saja jenis alamat sel lembar kerja Excel, serta bagaimana mengolah data yang ada pada sel dengan menggunakan fungsi dan operator logika.

2.1. Memasukkan Data dengan Fill Handle Cara memasukkan data kedalam lembar kerja Excel yaitu dengan mengarahkan indikator sel aktif ke sel yang akan diisi data, sebab setiap sel bisa diisi dengan data. Dalam pengolahan data angka, pastikan data angka diketik di sel atau kolom sendiri-sendiri. Data dalam sel bisa juga disalin ke sel yang lain, caranya sama dengan menyalin data pada Word. Atau, kalau dalam Excel bisa juga menyalin dan memasukkan data menggunakan fill handle ...

Fill handle juga bisa digunakan untuk membuat data yang berurut, misalnya:

a. Gantilah teks Januari diatas, dengan teks January (tergantung setting bahasa pada komputer yang digunakan).

b. Dengan fill handle, drag hingga sel B13. c. Hasilnya adalah nama bulan urut dari January hingga December.

Coba lakukan hal yang sama untuk membuat nama-nama hari! Sekarang cobalah untuk membuat bilangan kelipatan 5. Lakukan dengan data seperti gambar disamping ini: ketik angka 5 di sel B2 dan 10 di sel B3, blok kedua angka itu lalu drag fill handle hingga sel B10. Apa yang terjadi?

2.2. Menggunakan Fasilitas Autofill Memasukkan data seperti diatas juga bisa menggunakan fasilitas Autofill. Caranya:

a. Ketik Sunday di sel A1. b. Aktifkan sel A1, dan dengan fill handle, drag hingga sel B3.

Fill Handle: pointer mouse yang

diletakkan di sudut kanan bawah

indikator sel aktif, bentuknya

berubah dari plus tebal warna putih

menjadi plus tipis.

2

e-biz education enterprise

68 | Mengolah Data Sel

c. Lepaskan klik mouse, dan perhatikan di kanan bawah data, ditampilkan beberapa pilihan perintah seperti nampak pada gambar di samping ini

Copy Cells, untuk menyalin isi sel. Fill Series, untuk menyalin data berurut sesuai series-

nya, yaitu nama hari. Formatting Only, isi sel tidak disalin, hanya format

sel-nya saja. Fill Without Formatting, menyalin isi sel tanpa format. Fill Days, Weekdays, Month, dan sebagainya adalah untuk menyalin isi sel sebagai nama hari, bulan, dan sebagainya.

Autofill bisa juga dilakukan dengan perintah yang ada pada Tab Home, Ribbon Group Editing seperti ini

Tandai dulu sel yang akan diisi data berurut, lalu pilih Series.

2.3. Menyalin Data Untuk menyalin data, caranya: Pastikan Tab Home aktif, pada Ribbon Group Clipboard klik Copy. Maka akan ditampilkan pilihan seperti ini

Pilih Copy atau tekan tombol CTRL+C, untuk menyalin data termasuk formatnya ke dalam Clipboard. Pilih Copy as Picture, untuk menyalin data sebagai gambar ke dalam Clipboard.

Untuk menampilkan isi Clipboard, caranya adalah dengan memilih perintah Paste yang ada di Group Clipboard. Maka akan ditampilkan pilihan-pilihan sebagai berikut: Paste (P), menyalin isi clipboard sesuai data aslinya. Formulas (F), menyalin hanya rumusnya saja. Formulas and Number Formatting (O), menyalin rumus dan format angkanya. Keep Source Formatting (K), menyalin dengan tetap menjaga format sumber data (data asli). No Borders (B), menyalin tanpa bingkai. Keep Source Column Width (W), menyalin dengan tetap menjaga lebar kolom sumber data. Transpose (T), menyalin dengan hasil terbalik, kolom jadi baris baris jadi kolom.

Values (V), menyalin isi sel / datanya saja. Values and Number Formatting (A), menyalin isi sel / data berikut formatnya. Values and Source Formatting (E), menyalin isi sel dan menjaga format sumber datanya.

Formatting (R), menyalin hanya formatnya saja. Paste Link (N), menyalin link (hubungan dengan data lain). Picture (U), menyalin sebagai gambar. Link Picture (I), menyalin link gambar.

Paste (P)

Formulas (F) Formulas and Number Formatting (O)

Keep source Formatting (K)

No Borders (B) Keep Source Column Width (W)

Transpose (T)

Values and Number Formatting (A)

Values and Source Formatting (E) Values (V)

Paste Link (N)

Formatting (R)

Picture (U)

Link Picture (I)

Microsoft Excel 2010

Mengolah Data Sel | 69

2.4. Alamat sel Lembar kerja Excel yang berlajur yang terdiri dari kolom dan baris, memudahkan pemasukkan dan pengolahan data, sebab data yang dimasukkan ke dalam sel memiliki alamat. Hal ini sangat membantu pengguna, sebab jika dilakukan perubahan data, Excel segera melakukan perhitungan ulang (recalculation). Alamat sel ada 3 macam, yaitu:

a. Alamat sel relatif, adalah alamat sel yang selalu berubah kolom dan barisnya jika dilakukan perubahan posisi. Contoh: =A2*B1

b. Alamat sel semi absolut, adalah alamat sel yang akan berubah baris atau kolomnya saja jika dilakukan perubahan posisi. Contoh: =$A2*B$1

c. Alamat sel absolut, adalah alamat sel yang tidak berubah kolom dan barisnya walau dilakukan perubahan posisi. Contoh: =$A$2*$B$1

Simbol $ diawal alamat kolom atau baris berguna untuk mengunci alamat sel atau kolom tersebut.

2.5. Fungsi dan Operator Logika Sekarang kita akan membahas pengolahan data dengan menggunakan fungsi dan operator logika. Fungsi logika digunakan untuk mengolah data sesuai kondisi, sebab itu fungsi logika biasanya juga disebut conditional formula. Fungsi logika yang sering digunakan adalah:

=IF(kondisi yang diuji,[Nilai jika kondisi benar],[Nilai jika kondisi salah])

Kondisi dan Nilai bisa berupa angka, rumus, atau teks. Misal: di sel A1 diketik data 50. Kondisi yang diuji : Apakah sel A1 berisi data lebih dari 50? Nilai jika kondisi salah : BAIK Nilai jika kondisi benar : KURANG Penulisan rumus di sel B1 : =IF(A1>50,BAIK,KURANG) Hasil yang nampak di sel B1 : KURANG Coba ganti-ganti angka yang ada di sel A1!

Namun, jika kondisi yang diuji memiliki 3 Nilai. Misal: di sel C1 diketik data 50. Nilai yang disediakan: jika C180,"BAIK",IF(C1 70 DAN nilai praktik > 70, maka nilainya LULUS. Jika tidak, maka nilainya GAGAL.

Dalam kasus seperti ini, maka fungsi logika harus digunakan bersama operator logika. Ada 2 operator logika, yaitu:

=AND(kondisi1, [kondisi2], [kondisi3], )

dan

=OR(kondisi1, [kondisi2], [kondisi3], )

Seandainya, nilai teori diketik di sel E1 dan nilai praktik di sel F1. Maka kasus diatas diselesaikan dengan rumus:

=IF(AND(E1>70,F1>70),LULUS,GAGAL)

e-biz education enterprise

70 | Mengolah Data Sel

PRAKTIKUM 2

1. Buatlah tabel seperti gambar dibawah ini, dengan ketentuan:

a. Data masukkan berupa Nama Mahasiswa, dan Nilai 5 materi uji. b. Jumlah Lulus = jumlah materi uji yang nilainya > 70. c. IPK = jumlah rata-rata nilai semua materi uji. d. Keterangan = LULUS, jika JUMLAH LULUS > 3 dan IPK > 70

REMIDI, jika JUMLAH LULUS >= 2 dan IPK > 60 Selain itu berarti TDK LULUS

e. Carilah juga ranking nilai mahasiswa berdasarkan IPK-nya.

DAFTAR NILAI TRAINING PAP TAHUN 2010 PAKET APLIKASI PERKANTORAN

NOMOR URUT

NAMA MAHASISWA

NILAI JUMLAH IPK KETERANGAN RANKING

WORD EXCEL PPOINT OUTLOOK ACCESS LULUS

1 MAY DIANA 80 75 70 72 60 3 71.40 REMIDI 7

2 YOHANNES 72 70 80 75 90 4 77.40 LULUS 2

3 AGUSTINA 75 70 90 75 80 4 78.00 LULUS 1

4 WULANDARI 55 65 50 80 60 1 62.00 TDK LULUS 10

5 ISWOYO 60 85 75 75 80 4 75.00 LULUS 6

6 MAPALITA 90 80 75 70 65 3 76.00 REMIDI 4

7 TYASTUTI 70 65 60 65 65 0 65.00 TDK LULUS 9

8 NUGROHO 75 65 72 70 75 3 71.40 REMIDI 7

9 THERESIA 70 80 72 85 75 4 76.40 LULUS 3

10 ANDRIYA 85 65 75 80 75 4 76.00 LULUS 4

LULUS : 5 REMIDI : 3 TDK LULUS : 2

f. Simpan dengan nama PRAKTIKUM-2. Dan Sheet1 ganti dengan nama TABEL-1.

Fungsi yang digunakan: =COUNTIF(), =IF(), =AND(), RANK

2. Buatlah tabel berikut ini dengan ketentuan:

a. Kerjakan di Sheet2, lalu ganti nama Sheet2 menjadi TABEL-2. b. Data masukkan berupa 1 Dollar =, Software Produk, dan Jumlah Unit Penjualan

dari bulan Januari-Juni.

c. Target penjualan tiap bulan >4. Karena itu Target: Sesuai = Jumlah bulan yang memenuhi target. Jumlah = Jumlah penjualan semua bulan yang memenuhi target.

d. Setiap jumlah penjualan yang memenuhi target mendapat reward $5, karena itu: Jumlah Reward = (Target) Jumlah x 5 x 1 Dollar (Rp.9,800)

e. Jumlah reward diuangkan dalam pecahan Rp.100.000-an, Rp.10.000-an dan Rp.1.000-an.

Fungsi yang digunakan: =COUNTIF(), =SUMIF(), =MOD(), =INT()

Microsoft Excel 2010

Mengolah Data Spesifik | 71

3. Mengolah Data Spesifik

Conditional Formatting

Memilih Bentuk Tabel

Bekerja dengan Data Spesifik

Data spesifik adalah data yang sesuai dengan syarat-syarat yang ditentukan atau sesuai dengan kondisi tertentu. Data spesifik ini kemudian bisa diberi penanda: disorot dengan warna (highlight), isi sel dicetak dengan penekanan atau format khusus, atau ditandai sesuai skala dengan warna visual, ikon dan data bar; diurutkan; dan ditampilkan sesuai kriteria tertentu.

3.1. Conditional Formatting Conditional Formatting adalah format sel (datanya yang diformat atau isi selnya yang diformat) yang sesuai dengan kondisi atau kriteria yang diberikan.

Tandai sel-sel yang berisi data tertentu. Pada Tab Home, di Ribbon Group Styles, klik Conditional Formatting Highlight Cells Rules, akan menandai atau memberi warna sel-sel yang

memenuhi kriteria: lebih besar dari (Greater Than), lebih kecil dari (Less Than), diantara (Between), sama dengan (Equal To), sel yang berisi teks (Text that Contains), tanggal (A Date Occuring), dan nilai atau data rangkap (Duplicate Values).

Top/Bottom Rules, akan menandai atau memberi warna sel-sel yang memenuhi kriteria terbesar / terkecil.

Sel-sel ditandai dengan berbagai warna gradasi atau ikon berdasarkan nilai terbesar hingga terkecil (Data bars, Color Scales, dan Icon Sets).

Jika memberi kriteria secara manual, pilih New Rule Maka dialog box New Formatting Rule akan ditampilkan seperti ini Jenis kriteria atau aturan, pilih disini (Select a Rule Type:) Penjabaran kriteria atau aturan, pilih dan atur disini (Edit the Rule Description:)

Jika ingin memberi beragam kriteria atau aturan, pilih Manage Rules dan akan ditampilkan dialog box Conditional Formatting Rules Manager

Pilih New Rule untuk memberi aturan baru. Atau melakukan perubahan aturan dengan Edit Rule. Menghapus aturan dengan Delete Rule serta mengatur urutannya dengan Order rule.

3

e-biz education enterprise

72 | Mengolah Data Spesifik

Untuk menghapus berbagai kriteria atau aturan yang diterapkan, pilih Clear Rules from Selected Cell (menghapus kriteria yang ada di sel yang ditandai), Clear Rules from Entire Sheet (menghapus semua kriteria di seluruh lembar kerja), Clear Rules from This Table (menghapus kriteria dari tabel) atau Clear Rules from This PivotTable (menghapus kriteria dari PivotTabel).

3.2. Memilih Bentuk Tabel Excel 2010 mempermudah kita membuat tabel yang langsung diberi tombol filter. Caranya:

Ketik datanya terlebih dahulu. Tandai data tersebut. Klik Tab Home, lalu pilih Format as Table pada Ribbon Group Styles. Pilih Style tabel yang diinginkan.

Jika tabel diaktifkan, maka akan ditambahkan Tab Table Tools Design seperti ini

Ada beberapa Ribbon Group yang berisi berbagai perintah untuk pengolahan tabel yang telah kita buat, yaitu: Pada Group Properties, terdapat bagian untuk memberi nama tabel (Table

Name) dan perintah untuk memperbesar range tabel (Resize Table). Group Tools, berisi perintah untuk membuat ringkasan tabel dengan PivotTable

(Summarize with PivotTable); perintah untuk menghapus duplikasi data (Remove Duplicates); dan perintah untuk konversi data tabel menjadi data range biasa (Convert to Range).

Group External Table Data, berisi perintah-perintah untuk eksport data ke dalam web.

Group Table Style Options, berisi perintah-perintah untuk mengatur Style tabel. Group Table Styles, berisi berbagai pilihan Style tabel.

Membuat tabel bisa juga menggunakan pilihan yang ada pada Tab Insert. Lalu tandai tabel mulai dari judul kolom. Klik Table yang ada pada Ribbon Group Tables atau tekan tombol CTRL+T. Pada dialog box Create Table seperti gambar disamping, akan ditampilkan alamat tabel yang ditandai pada bagian Where is the data for your table?. Lalu aktifkan My table has headers, jika tabel yang ditandai mulai dari judul kolom. Akhiri dengan klik OK, tabel-pun jadi dan kita bisa bekerja pada Tab Table Tools Design.

3.3. Bekerja dengan Data Spesifik Selama proses pengolahan data, terkadang dibutuhkan data spesifik. Misalnya, data yang dibaca harus urut, menampilkan 10 besar data saja, menampilkan data dengan kriteria tertentu, dan sebagainya. Cara melakukannya adalah sebagai berikut: pastikan Tab Data aktif. Pada Ribbon Group Sort & Filter, terdapat berbagai pilihan perintah seperi ini

Microsoft Excel 2010

Mengolah Data Spesifik | 73

a. Ascending, urut naik dari kecil ke besar.

b. Descending, urut turun dari besar ke kecil.

Jika ditampilkan pesan pendek, seperti berikut ini: Pilih Expand the selection, jika ingin memperluas

data yang ditandai. Pilih Continue with the current selection, jika

ingin melanjutkan mengurutkan data yang ditandai saja.

c. Pilih perintah sort maka akan ditampilkan dialog box Sort seperti gambar dibawah ini Pada bagian Sort by, pilih

NAMA, pada bagian Order, pilih A to Z.

Klik Add Level. Lalu isi bagian Sort by, pilih JUMLAH, pada bagian Order, pilih Z to A.

d. Cara lain untuk melakuan pengurutan data, adalah dengan memanfaatkan fasilitas

Filter. Untuk mengaktifkan fasilitas filter, klik Filter yang ada di Ribbon Group Sort & Filter. Sekarang, coba tandai tabel dari sel A2 sampai sel C7. Lalu klik Filter. Maka tabel data akan nampak seperti ini

di sisi kanan setiap judul kolom ditambahkan tombol Filter

Pada tombol filter ini, kita bisa langsung me-ngurutkan data kolom dimana tombol filter berada.

e. Dengan tombol Filter tersebut, kita juga bisa menampilkan data spesifik, misalnya: menampilkan semua nama yang berawalan K saja. Caranya: Tandai tabel data dari sel A2 sampai dengan sel C7. Klik filter yang ada di kolom NAMA, pada bagian Text

Filter ketik: K* ( * adalah karakter unik yang mewakili semua karakter, sedangkan ? mewakili satu karakter).

Atau klik Text Filters, pilih Equals. Setelah tampil dialog box Custom Autofilter ketik K*, seperti ini

e-biz education enterprise

74 | Mengolah Data Spesifik

PRAKTIKUM 3

1. Bukalah PRAKTIKUM-2, TABEL-1, lalu copy-kan ke lembar kerja yang baru.

a. Kolom IPK berilah Conditional Formatting data bars, juga icon sets indicators. b. Kolom Keterangan: tandai dengan warna merah dan font tebal bagi yang TDK

LULUS. Sehingga tabelnya nampak seperti ini

c. Simpan dengan nama PRAKTIKUM-3, lalu Sheet1 ganti menjadi DATA-1.

2. Buatlah tabel berikut ini di Sheet2, lalu ganti namanya menjadi DATA-2:

Ketentuannya: a. Data masukkan berupa: No.Induk, Nama Peserta, Jenis Kel. dan Usia. b. No.Induk berisi informasi sebagai berikut:

Materi Asal bulan daftar no.urut

P U 08 100 Asal = Jika U, maka peserta berasal dari UMUM, P berasal dari PERUSAHAAN

dan S berasal dari SEKOLAH. Materi = Jika P, maka peserta mengikuti training dengan materi PAP, jika W

materi WEB dan J materi JARINGAN. Lama = Jika mengikuti training PAP lamanya 3 bln, JARINGAN lama training 6

bln dan WEB lama training 8 bln. Biaya = training PAP biayanya Rp.350.000, WEB biayanya Rp.1.200.000 dan

JARINGAN biayanya Rp.1.800.000. c. Ingat data Usia, Lama dan Biaya berupa angka atau VALUE. d. Diskon = - Jika peserta mengikuti training dengan materi PAP dan berasal dari

SEKOLAH, maka akan mendapat DISKON sebesar 10% dari Biaya. - Jika peserta mendaftarkan diri pada bulan 08 atau berasal dari

SEKOLAH, maka akan mendapat DISKON sebesar 5% dari Biaya. - Selain itu tidak akan mendapat DISKON.

e. Bayar = Biaya - Diskon

Microsoft Excel 2010

Mengolah Data Spesifik | 75

3. Copy-kan tabel DATA-2 ke Sheet3, lalu ganti nama Sheet3 menjadi DATA-3. 4. Ubahlah format tabel dengan style: Table Style Medium 10. 5. Sekarang, cobalah bekerja dengan data spesifik sebagai berikut:

a. Tampilkan semua peserta yang mengikuti training PAP. b. Tampilkan semua peserta Perempuan yang mengikuti training JARINGAN. c. Tampilkan semua peserta yang berasal dari UMUM dan berusia >= 30 tahun. d. Tampilkan semua peserta yang berasal dari UMUM dan PERUSAHAAN yang

berusia Rp.1.500.000,-. g. Tampilkan semua peserta yang membayar < Rp.500.000,- dan mendapat Diskon. h. Tampilkan semua peserta yang membayar diantara Rp.1.000.000,- hingga

Rp.1.500.000,- i. Tampilkan semua peserta yang mendaftar tidak di bulan 8. j. Tampilkan semua peserta yang mendaftar di bulan 8 dan berasal dari UMUM. k. Copy tabel yang ada di DATA-3 ke Sheet4. Aktifkan Sheet4, urutkan tabel

berdasarkan Bayar dari yang terbesar hingga terkecil. Ganti nama Sheet4 dengan nama DATA-4. Lalu kolom Bayar berilah Conditional Formatting Color Scales, juga icon sets 4 Ratings.

l. Dengan Conditional Formatting, tandai atau format dengan warna putih dan background (fill) gelap semua data peserta yang berasal dari SEKOLAH. Sehingga tabel (DATA-4) akhirnya nampak seperti ini:

e-biz education enterprise

76 | Pengolahan Tabel dan Data

4. Pengolahan Tabel dan Data

Memberi Nama Range Tabel

Fungsi Pembacaan Tabel

Pemeriksaan Kesalahan Pengolahan Data

Pada bab ini, kita akan membahas tentang pengolahan tabel dan data: bagaimana memberi nama tabel atau range sel, membaca tabel secara horisontal atau vertikal, diurutkan, atau membuat tabel dengan memilih berbagai bentuk tabel yang telah tersedia dan bekerja dengan data spesifik sesuai kriteria yang kita inginkan.

4.1. Memberi Nama Range Tabel Penulisan alamat sel atau range, bisa diganti dengan memberi nama tabel atau range tertentu pada lembar kerja. Dengan cara ini, kita tidak perlu menulis alamat yang kadang panjang dan sulit diingat. Untuk memroses data yang ada pada alamat sel tersebut, cukup diketik namanya. Cara memberi nama range atau tabel: pastikan Tab Formula aktif. Pada Ribbon Group Defined Names, klik Define Name. Lalu pilih Define Name, dialog box New Name akan ditampilkan seperti ini

disini, ketikkan nama tabel atau range

disini, jika ingin memberi komentar

disini, tentukan alamat sel atau range yang akan diberi nama

Nama-nama tabel atau range yang pernah kita buat, bisa dikelola dengan memilih Name Manager.

Sebagai contoh, lakukan langkah-langkah berikut ini:

a. Tandai sel A1 sampai A3. b. Beri nama: coba. c. Di sel B1 atau sel manapun ketik: =SUM(coba) d. Perhatikan hasilnya?

4.2. Fungsi Pembacaan Tabel Fungsi (function) berikut digunakan untuk mengisi sel kosong dengan cara membaca dan membandingkan nilai kunci dengan tabel data. Ada dua fungsi yang bisa digunakan, yaitu: HLOOKUP untuk tabel horisontal dan VLOOKUP untuk tabel vertikal. Secara lengkap penulisan rumus atau fungsi-nya seperti ini

=HLOOKUP(nilai kunci, tabel data, nomor indeks baris)

=VLOOKUP(nilai kunci, tabel data, nomor indeks colom)

4

Microsoft Excel 2010

Pengolahan Tabel dan Data | 77

Nilai kunci adalah data yang berupa angka, kode tertentu atau teks yang digunakan sebagai pembanding dengan tabel data. untuk menghindari kesalahan gunakan pembanding yang sama dengan data yang ada pada kolom pertama tabel data.

Tabel data adalah tabel yang berisi data lengkap atau informasi yang menjadi acuan untuk dibaca dan diisikan ke dalam tabel isian. Tabel data harus memenuhi kriteria: data yang akan dibandingkan dengan nilai kunci harus terletak di kolom / baris pertama dan harus urut naik (ascending).

Nomor indeks baris atau kolom adalah posisi / urutan baris atau kolom yang akan dibaca.

Contoh pemakaian HLOOKUP, perhatikan gambar berikut ini

Cara mengisi tabel isian (LAPORAN BULANAN):

a. Beri nama tabel data (TABEL PROGRAM). Caranya: Tandai sel F2 sampai I4. Pastikan Tab Formula aktif. Di Ribbon Group Defined Names, klik Define

Name, lalu pilih Define Name. Pada dialog box New Name, beri nama: program2 di bagian Name. Akhiri

dengan klik OK. b. Klik sel C3. Ketik rumus berikut: =HLOOKUP(B3,program2,2) c. Coba tuliskan sendiri rumus untuk kolom HARGA (sel D3).

Contoh pemakaian VLOOKUP, perhatikan gambar berikut ini

Tabel LAPORAN BULANAN adalah tabel isian, karena ada beberapa kolom yang masih kosong dan harus diisi.

Tabel TABEL PROGRAM adalah tabel data yang jadi acuan untuk mengisi tabel data sebab berisi informasi yang sudah lengkap.

Cara mengisi tabel isian (LAPORAN BULANAN): a. Beri nama tabel data (TABEL PROGRAM). Caranya:

Tandai sel F2 sampai H5. Pastikan Tab Formula aktif. Di Ribbon Group Defined Names, klik Define

Name, lalu pilih Define Name. Pada dialog box New Name, beri nama: program di bagian Name. Akhiri

dengan klik OK. b. Klik sel C3. Ketik rumus berikut: =VLOOKUP(B3,program,2) c. Coba tuliskan sendiri rumus untuk kolom HARGA (sel D3).

Harus di kolom pertama

dan urut. Ini adalah

indeks no 1

Nilai kunci, harus sama dg

kolom pertama tabel data

Harus di baris pertama

dan urut. Ini adalah

indeks no 1

e-biz education enterprise

78 | Pengolahan Tabel dan Data

4.3. Pemeriksaan Kesalahan Pengolahan Data Ketika melakukan pengolahan data, terkadang terjadi kesalahan. Excel 2007 memiliki fasilitas untuk memeriksa kesalahan dalam pengolahan data, yaitu Formula Auditing. Fasilitas ini ada di Tab Formulas dan berisi perintah-perintah: Trace Precedents, perintah untuk menampilkan tanda panah yang menunjukkan

sel-sel mana saja yang mempengaruhi nilai dari sel yang dipilih. Pada gambar sel yang dipilih adalah sel C4. Jadi sel-sel yang diberi bingkai (sel B2:B5) mempengaruhi nilai yang ada di sel C4.

Trace Dependents, perintah untuk menampilkan tanda panah yang menunjukkan sel mana yang dipengaruhi oleh nilai

dari sel yang dipilih. Pada gambar sel yang dipilih adalah sel E1. Jadi sel-sel yang ditunjuk anak panah (sel F2:F5) dipengaruhi oleh nilai yang ada di sel E1. Atau nilai yang ada di sel F2:F5 tergantung dari nilai yang ada di sel E2.

Remove Arrows, perintah yang digunakan untuk menghilangkan tanda panah. Show Formulas, untuk menampilkan pada lembar kerja isi sel yang berupa rumus-

rumus yang diketik di dalamnya dan bukan hasil akhir perhitungan rumus-rumus tersebut.

Error Checking, untuk memeriksa dan menunjukkan sel yang berisi rumus yang salah.

Evaluate Formula, untuk mengevaluasi tiap-tiap bagian dari rumus sehingga diketahui kesalahannya ada di sel mana.

Watch Window, jendela khusus yang ditampilkan untuk memonitor nilai yang ada pada sel tertentu berasal dari rumus apa.

Selain itu, Excel juga terkadang menunjukkan kesalahan dengan menampilkan kode atau pesan kesalahan di sel aktif atau di tabel data. Kode atau pesan kesalahan tersebut adalah:

Kode Kesalahan Keterangan

####### Kolom kurang lebar untuk menampilkan data atau hasil pengolahan data.

#VALUE Jenis data salah. Kalau dalam proses perhitungan matematik berarti ada data yang bukan numerik

#NAME? Formula atau Fungsi (Function) tidak dikenali. Salah dalam penulisan fungsi atau rumus.

#REF! Formula mengacu atau membaca data yang tidak ada atau salah dalam menuliskan alamat sel datanya.

#DIV/0! Formula berusaha membagi dengan 0. Pembagian dengan angka nol tidak diperkenankan.

Microsoft Excel 2010

Pengolahan Tabel dan Data | 79

PRAKTIKUM 4

1. Buatlah tabel seperti gambar dibawah ini, dengan ketentuan:

a. Tabel Daftar Biaya Training, beri nama: DATA. b. Tabel isian adalah tabel DATA PESERTA TRAINING, ketentuannya: data

masukkan NIM, Nama Mahasiswa, dan Jum.Sesi.

c. Kolom Jurusan, Biaya dan Discount diisi dengan membaca tabel data. d. Jum.Bayar = Biaya Jum. Sesi (discount Biaya Jum.Sesi). e. Simpan dengan nama: PRAKTIKUM-4 dan ganti Sheet1 dengan TABEL-1.

2. Kerjakan tabel seperti berikut ini di Sheet2 lalu ganti namanya menjadi TABEL-2:

Ketentuan: Data masukkan adalah Nama Peserta, Kelas, Status, Jumlah Sesi. Total Biaya = (Biaya Per-Sesi x Jumlah Sesi) Diskon Pemberian Diskon berdasarkan Kelas dan Status pegawai.

e-biz education enterprise

80 | Meninjau dan Menganalisa Data

5. Meninjau & Menganalisa Data

Memberi Komentar

Validasi Data

Menganalisa Data

Bekerja dengan PivotTable, PivotChart dan Slicer Melindungi Dokumen

Data yang diketik ke dalam lembar kerja Excel dapat diperiksa benar atau tidaknya, untuk selanjutnya dianalisa dengan metode-metode tertentu. Excel 2007 menyediakan fasilitas tersebut pada Group Data Tools. Bab ini akan menguraikan fasilitas-fasilitas tersebut.

5.1. Memberi Komentar Agar pemakaian bersama lembar kerja atau kolaborasi dengan orang lain lancar dan dimengerti, sebaiknya untuk sel-sel tertentu yang diperkirakan dapat menimbulkan salah pengertian diberi komentar. Pemberian komentar penting juga agar tidak ada suatu pengertian yang terlupa, jadi sebuah catatan dan memberi keterangan. Cara memberi komentar pada suatu sel adalah:

a. Tandai sel atau letakkan indikator sel aktif di sel yang akan diberi komentar. b. Pilih Tab Review, klik New Comment yang ada pada Group Comments. c. Pada sel aktif tersebut akan ditampilkan Comment Flag dan kotak isian

komentar disisi kanan sel.

d. Tulis komentar dalam kotak isian tersebut. Lalu klik sembarang sel. Setiap kali sel tersebut ditunjuk, maka kotak komentar akan muncul.

Jika sel yang telah diberi komentar di klik, maka semua perintah yang ada pada Ribbon Group Comments akan aktif. Perintah-perintah itu antara lain:

Edit Comment, untuk melakukan perubahan pada komentar yang telah diberikan.

Delete, untuk menghapus komentar.

Previous, menuju ke komentar sebelumnya.

Next, menuju ke komentar selanjutnya.

Show/Hide Comment, menampilkan atau menyembunyikan komentar.

Show All Comments, menampilkan semua komentar.

5.2. Validasi Data Seorang pengguna kadang lupa kriteria data yang diperbolehkan dimasukkan ke dalam suatu sel, agar tidak terjadi kesalahan perlu ada pemberitahuan, peringatan atau pembatasan. Perhatikan contoh tampilan berikut ini:

5

Microsoft Excel 2010

Meninjau dan Menganalisa Data | 81

Contoh Tampilan

Bagaimana Membuatnya? Untuk membuat contoh tampilan diatas, caranya adalah sebagai berikut:

a. Ketik teks Masukkan Nilai UAS : di sel A2. b. Letakkan indikator aktif sel di sel C2 (klik sel C2). c. Aktifkan Tab Data. Pada Ribbon Group Data Tools, pilih Data Validation lalu klik

Data Validations

d. Maka dialog box Data Validation akan ditampilkan seperti ini

Pilihlah jenis data apa yang diijinkan dimasukkan / di-inputkan di bagian Allow. Jika dipilih semua bilangan (Whole number) yang diijinkan, maka Data berapa saja yang diijinkan. Dalam praktikum ini pilihlah Between (diantara) nilai terendah (Minimum) 40 dan tertinggi (Maximum) 100.

e. Tetap pada dialog box Data Validation, pilih Tab box Input Message.

Aktifkan Show input message when cell is selected, agar pesan ditampilkan saat sel aktif (C2) di-klik. Ketika sel C2 aktif, dan agar pesan saat akan input data ditampilkan di layar, isilah Title: dengan teks: Informasi Penting! dan di bagian Input Message teks: Nilai yang dimasukkan harus diantara 10 100!. Akhiri dengan klik OK.

Input Message Input Cell Error Alert

Message

e-biz education enterprise

82 | Meninjau dan Menganalisa Data

f. Masih tetap di dialog box Data Validation, pilihlah Tab box Error Allert.

Aktifkan Show error alert after invalid data is entered, agar pesan kesalahan ditam-pilkan sesudah memasukkan data. Pada bagian Style, pilih Stop agar respon jika salah input data adalah berhenti. Pilih Warning, hanya sebagai peringatan dan bisa melanjutkan proses data. Atau pilih Information, agar pesan yang ditampilkan hanya sebagai informasi dan bisa melanjutkan proses data. Untuk praktikum ini pilihlah Stop.

g. Akhiri pengaturan validasi data dengan klik OK. h. Coba dan amati hasilnya!

Untuk menghapus penggunaan fasilitas Validasi: aktifkan sel validasi atau tandai seluruh sel. Klik Data Validation yang ada di Group Data Tools. Lalu pilih Data Validation, pada dialog box Data Validation klik Clear All.

5.3. Menganalisa data Suatu saat muncul pertanyaan Bagaimana jika input data diubah?, bagaimana hasil akhirnya?. Dari hasil akhir ini bisa diketahui, sudah maksimal tidak perubahan input data tersebut. Jika tidak, data asli tetap ada dan tidak terhapus. Nah, ini adalah salah satu cara menganalisa data. Selain membuat ringkasan per-item data. Pada bab ini akan diuraikan cara-cara menganalisa data

5.3.1. Skenario Isi dari suatu sel bisa disimpan tersendiri, termasuk perubahan-perubahan data pada sel tersebut. Tujuannya: perubahan-perubahan data pada sel dapat dilakukan tanpa menghilangkan data asli. Selain itu, dapat dibuat lembar kerja tersendiri yang berisi ringkasan perubahan data, seperti contoh tampilan berikut ini

Contoh Tampilan

Outline Column

Lembar kerja data Lembar kerja ringkasan skenario

Microsoft Excel 2010

Meninjau dan Menganalisa Data | 83

Bagaimana Membuatnya?

Untuk membuat contoh tampilan diatas, caranya adalah sebagai berikut:

a. Bukalah file ini (VLOOKUP) dan tambahkan baris JUMLAH serta rumus yang menjumlahkan kolom HARGA. Sehingga tabel nampak seperti ini

b. Aktifkan Tab Data. Pada Ribbon Group Data Tools, klik What-If Analysis lalu pilih Scenario Manager

c. Maka akan ditampilkan dialog box Scenario Manager. Klik Add, dan dialog box Add Scenario akan ditampilkan. Lalu isikan: Data Asli pada bagian Scenario Name: (bagian ini untuk memberi nama skenario).

d. Pilihlah alamat sel yang akan disimpan dan diubah isinya pada bagian Changing cells:, pastikan alamat selnya adalah sel H3:H5.

e. Di bagian Protection: aktifkan Prevent changes (mencegah perubahan).

f. Klik OK. Maka akan ditampilkan dialog box Scenario Values

g. Data yang ada pada dialog box Scenario Values adalah data asli. Jangan lakukan perubahan. Klik OK.

h. Setelah kembali ke dialog box Scenario Manager, pilih Add. Dialog box Add Scenario ditampilkan. Lalu isi: Perubahan Biaya1 pada bagian Scenario Name: .

i. Langsung klik OK untuk mengakhiri dialog box Add Scenario dan masuk ke dialog box Scenario Values. Ubah datanya: $H$3 = 1500000, $H$4 = 350000, $H$5 = 450000. Akhiri dengan OK.

j. Lakukan sekali lagi cara-cara diatas. Beri nama Scenario Name yang baru: Perubahan Biaya2. Lalu isikan pada Scenario Values: $H$3 = 2000000, $H$4 = 400000, $H$5 = 500000. Akhiri dengan OK.

k. Jika langkah-langkah diatas telah dilakukan, maka pada dialog box Scenario Manager nampak seperti ini

Pilih nama skenario disini

Lalu pilih Show, untuk melihat perubahan data pada HARGA atau sel H3:H5

Untuk membuat lembar kerja ringkasan skenario. Klik tombol Summary

e-biz education enterprise

84 | Meninjau dan Menganalisa Data

l. Jika tombol Summary di klik, maka akan ditampilkan dialog box Scenario Summary. Pilihlah Scenario Summary.

m. Pada bagian Result cells, isikan alamat sel JUMLAH atau sel D7.

n. Sekarang lihat hasilnya!

5.3.2. Goal Seek Cara menganalisa data berikut ini adalah dengan menetapkan tujuan atau target yang hendak dicapai, lalu menetapkan item data apa yang akan diubah atau diperbaharui. Excel akan memberi nilai akhir dari item data tersebut agar target yang ditetapkan tercapai. Untuk memperjelas materi ini, cobalah praktikum berikut:

a. Bukalah file ini (VLOOKUP) b. Aktifkan Tab Data. Pada Ribbon Group Data Tools, klik What-If Analysis lalu

pilih Goal Seek

c. Maka akan ditampilkan dialog box Goal Seek, berikut ini

Set cell, isilah dengan alamat sel yang berisi target atau nilai yang hendak dicapai. Tetapkan alamat sel target-nya adalah: $D$7 (JUMLAH).

To value, isilah dengan target atau nilai yang hendak dicapai. Isikan, misalnya: 3000000. By changing cell, isilah dengan alamat sel tunggal yang ingin diubah nilainya. Tetapkan alamat sel yang diubah adalah: $H$3 (HARGA CCNA).

d. Klik OK. Maka dialog box Goal Seek Status akan ditampilkan seperti ini

Nampak bahwa target atau nilai yang hendak dicapai (sel D7) berubah jadi 3000000

e. Perhatikan item data di sel H3 (HARGA CCNA), berubah menjadi: 1,150,000. f. Klik Cancel jika tetap mempertahankan data asli.

5.4. Bekerja dengan Outline Outline adalah fasilitas untuk mengorganisasi data sesuai levelnya, sehingga kita bisa menampilkan laporan dengan ringkas sesuai level yang ada. Setiap level biasanya dikelompokkan kedalam item atau field data yang sejenis dengan menggunakan fasilitas sort (mengurutkan). Misalnya, pada tabel berikut ini:

a. Buatlah tabel LAPORAN TAHUNAN seperti gambar disamping ini

b. Tandai tabel mulai dari sel A3 sampai sel D15.

c. Urutkan tabel data berdasarkan PROGRAM, caranya: pada Tab Data, klik Sort yang ada di Group Sort & Filter.

Microsoft Excel 2010

Meninjau dan Menganalisa Data | 85

d. Pada dialog box Sort, atur dan pilih bentuk pengurutan data yang diinginkan: pada bagian Sort by, pilih PROGRAM; Order pilih A to Z.

e. Sekarang tabel data diurutkan berdasarkan PROGRAM. Lalu tetap tandai tabel tersebut seperti langkah atau poin b.

f. Pastikan tetap di Tab Data. Pada Ribbon Group Outline, pilih Group Group. g. Maka akan ditampilkan dialog box Group. Pilihlah Rows, untuk membuat outline

berdasarkan data baris.

h. Kemudian klik Subtotal yang ada pada Group Outline. Pada dialog box Subtotal yang muncul, lakukan:

pada bagian At each change in, pilih PROGRAM agar data dikelompokan pada setiap Program.

Pilih Sum pada Use Function, untuk menjumlah tiap kelompok program.

Pilih HARGA agar data Harga yang dijumlahkan menurut Subtotal tiap kelompok data.

Akhiri dengan klik tombol OK.

i. Hasil akhirnya akan nampak seperti gambar disamping ini

Ini adalah tombol Outline level

Tombol untuk menyembunyikan detail data (Hide Detail).

Tombol untuk menampilkan detail data (Show Detail).

j. Selanjutnya coba sendiri membuat outline berdasarkan BULAN-TAHUN.

5.5. Bekerja dengan PivotTable Semakin banyak data yang diolah, akan semakin rumit pula untuk mengorganisir data tersebut. Dengan PivotTable, memungkinkan kita untuk membuat lembar kerja yang mampu menyimpan, menyaring dan menyusun ulang data secara dinamis untuk menekankan aspek tertentu dari data yang kita miliki.

e-biz education enterprise

86 | Meninjau dan Menganalisa Data

5.5.1. PivotTable Misalnya, diketahui data seperti gambar disamping ini

Cara membuat PivotTable dari data tersebut:

a. Tandai tabel data mulai dari sel A3:E22.

b. Aktifkan Tab Insert. Pada Ribbon Group Tables, klik PivotTable lalu pilih PivotTable.

c. Maka akan ditampilkan dialog box Create PivotTable seperti berikut ini

Pastikan alamat tabel yang digunakan pada bagian Select a table or range. Lalu pilih saja New Worksheet, untuk membuat PivotTable di lembar kerja yang baru.

Klik OK, untuk menetapkan pengaturan tersebut. Maka akan ditampilkan lembar kerja seperti dibawah ini

Ini adalah tampilan lembar kerja

PivotTable.

d. Sekarang kita kerja dengan panel PivotTable Field List. Aktifkan semua field (TGL, BULAN, TAHUN, PROGRAM, HARGA) yang ada di bagian Choose fields to add to report.

e. Lalu atur field-field yang telah diaktifkan tersebut ke area-area yang ada dengan cara di drag atau klik tombol dropdown (panah bawah) yang ada di setiap field:

Field PROGRAM dan TAHUN di area Report Filter; TGL di area Column Labels; BULAN di area Row Labels; dan HARGA di area Values.

Sehingga tampilan lembar kerja PivotTable nampak seperti ini

Microsoft Excel 2010

Meninjau dan Menganalisa Data | 87

f. Cobalah melakukan penyeleksian data tertentu sesuai keinginan dengan

PivotTable yang telah dirancang tersebut!

g. Jenis nilai yang ditampilkan pada PivotTable diatas adalah penjumlahan (SUM) dan bisa diubah sesuai keinginan, caranya: klik field Sum of HARGA yang ada di area Values. Pada pilihan perintah yang ditampilkan pilih Value Field Settings, seperti gambar di samping

h. Pilih jenis proses yang diinginkan pada bagian Summarize value field by (coba pilih Max, untuk menampilkan nilai tertinggi).

5.5.2. PivotChart Selain dalam bentuk tabel, PivotTable bisa diwujudkan dalam bentuk PivotChart yang berupa gambar grafik seperti ini

Caranya, pilih Tab Insert. Pada Ribbon Group Tables, klik PivotTable lalu pilih PivotChart. Simpan data dan chart dengan nama DATA PIVOTTABLE.

e-biz education enterprise

88 | Meninjau dan Menganalisa Data

5.6. Bekerja dengan Slicer Adalah komponen yang mempermudah penyaringan data dalam laporan PivotTable, tanpa perlu membuka daftar drop-down untuk menemukan item yang ingin di filter.

Cara menggunakan Slicer:

a. Aktifkan PivotTable yang pernah dibuat (DATA PIVOTTABLE), lalu klik tab Insert. Pada group filter, klik Slicer

b. Maka akan ditampilkan kotak dialog Insert Slicers, seperti berikut ini

c. Dalam kotak dialog Insert Slicers akan ditampilkan field-field tabel data yang ada. Pilih field yang akan di filter. Misalnya, field TGL dan BULAN. Maka akan ditampilkan kotak pilihan Slicer yang berisi item-item filter seperti ini

d. Untuk memilih lebih dari satu item, tekan CTRL dan item lainnya. Lalu perhatikan

hasilnya!

Saat kotak dialog Slicer aktif, maka akan ditampilkan tab Slicer Tools Options yang berisi berbagai perintah untuk pengaturan Slicer seperti nampak berikut ini

Cobalah semua perintah yang ada di ribbon group tersebut, namun yang terpenting adalah: PivotTable Connections yang dapat menghubungkan (koneksi) dengan PivotTable atau PivotChart yang aktif.

Field yang difilter Clear filter Semua item difilter

item difilter

Microsoft Excel 2010

Meninjau dan Menganalisa Data | 89

5.7. Melindungi Dokumen Kita bisa melindungi data atau lembar kerja kita, bahkan dokumen atau file kita bisa juga dilindungi dari orang lain yang tidak berhak atau tidak tahu kuncinya. Beberapa macam bentuk perlindungan tersebut, adalah:

5.7.1. Proteksi Lembar Kerja Dengan fasilitas proteksi lembar kerja ini, kita bisa melindungi lembar kerja kita dari input atau edit data, format sel, format kolom, menyisipkan kolom atau baris, menghapus kolom atau baris dan sebagainya. Cara melakukan proteksi lembar kerja adalah: aktifkan Tab Review, lalu klik Protect Sheet yang ada pada Group Changes. Maka, dialog box Protect Sheet berikut ini akan ditampilkan

Tetapkan kata kunci (password) di bagian Password to unprotect sheet:

Aktifkan beberapa pilihan pada bagian Allow all users of this worksheet to, untuk mengatur perlakuan atau perubahan

apa saja yang diperbolehkan.

5.7.2. Proteksi Buku Kerja Fasilitas ini melindungi buku kerja dari perubahan-perubahan struktur buku kerja misalnya, menghapus, memindahkan dan menambah lembar kerja (sheet) yang ada pada suatu buku kerja (Workbook). Cara melakukan proteksi buku kerja adalah: aktifkan Tab Review, lalu klik Protect Workbook yang ada pada Group Changes. Maka, dialog box Protect Structure and Windows berikut ini akan ditampilkan Pilihlah, jenis perlindungan yang diinginkan pada bagian Protect workbook for, lalu tetapkan kata kunci (password) yang digunakan.

5.7.3. Proteksi Dokumen Berbeda dari fasilitas proteksi sebelumnya, fasilitas ini digunakan untuk melindungi dokumen agar tidak bisa dibuka bahkan dimodifikasi oleh yang tidak berhak atau yang tidak tahu kata kuncinya (password). Caranya sama dengan pembahasan proteksi file Word, yaitu: pilih Tab Office Button. Dari berbagai pilihan yang ada, klik Save As.

Maka akan ditampilkan dialog box Save As, klik pilihan Tools yang ada dikiri bawah dialog box. Lalu pilih General Options .

Nampak dilayar ditampilkan dialog box General Options seperti ini

Aktifkan Always create backup, jika ingin membuat file cadangannya.

Tetapkan kata kuncinya (password).

e-biz education enterprise

90 | Meninjau dan Menganalisa Data

PRAKTIKUM 5

1. Buatlah analisa biaya pengadaan training PAP, dengan membuat tabel analisa seperti gambar dibawah ini di Sheet1 yang diganti namanya menjadi ANALISA-1.

a. BEP (Break Even Point) adalah titik impas, dimana pendapatan yang didapat impas digunakan untuk biaya pengeluaran. Nilai BEP pada tabel analisa tersebut telah ditentukan dari awal.

b. Pemasukan = Mahasiswa x Biaya c. Analisalah, seandainya:

BEP = Rp.1.500.000,-, dan jumlah kapasitas ruang training hanya untuk 12 mahasiswa. Berapa biaya minimal yang dikeluarkan per-mahasiswa?

BEP = Rp.1.500.000,-, dan biaya yang dikenakan per-mahasiswa Rp.110.000,-. Berapa jumlah minimal mahasiswa peserta training (hasilnya dibulatkan keatas)?

Jika BEP = Rp.2.750.000,-, dan jumlah kapasitas ruang training hanya untuk 12 mahasiswa. Berapa biaya minimal yang harus dikeluarkan oleh setiap mahasiswa?

2. Buatlah Tabel berikut ini dengan ketentuan:

Tabel isiannya adalah Tabel Marketing Software, Tabel Datanya adalah Data Divisi.

Data masukan: Laporan dibuat tanggal, Kode Marketing, Nama Marketer dan Jumlah Jual.

Kode Marketing berisi informasi: 4 angka pertama adalah tahun mulai kerja, karakter ke-5

berupa huruf adalah kode divisi (C=Corporate, E=Education, P=Personal).

Masa Kerja = Laporan dibuat tanggal Tahun mulai kerja

Gol.Kerja = A jika masa kerja < 3 thn, B jika masa kerja 3 7 tahun, C jika masa kerja 8

10 tahun dan D jika masa kerja > 10 tahun.

LAPORAN DIBUAT TANGGAL : 06-Dec-10 KODE NAMA MASA GOL.

DIVISI JUML. JUMLAH JUMLAH

MARKETING MARKETER KERJA KERJA JUAL BAYAR KOMISI

1995C100 TEDUH PRIMAN 15 Thn D CORPORATE 2 Rp 10,000,000 Rp -

2005E101 FREDERIKA 5 Thn B EDUCATION 2 Rp 5,000,000 Rp -

1999C102 LENY NJUNTAK 11 Thn D CORPORATE 4 Rp 19,500,000 Rp 1,000,000

1995P103 SULISTYO 15 Thn D PERSONAL 8 Rp 7,920,000 Rp 80,000

2002P104 HERRY SABATH 8 Thn C PERSONAL 9 Rp 8,910,000 Rp 90,000

1998E105 RAYUNIKA NGGEO 12 Thn D EDUCATION 3 Rp 7,500,000 Rp -

2008E106 PURWIGETI 2 Thn A EDUCATION 5 Rp 11,875,000 Rp 312,500

2009P107 ANITA CAROLINA 1 Thn A PERSONAL 10 Rp 9,900,000 Rp 100,000

2004P108 SLAMET MUSTAFA 6 Thn B PERSONAL 4 Rp 4,000,000 Rp -

2006E109 YUSTININGTYAS 4 Thn B EDUCATION 2 Rp 5,000,000 Rp -

2007C110 ASTRID LOPPIES 3 Thn B CORPORATE 3 Rp 14,625,000 Rp 750,000

Microsoft Excel 2010

Meninjau dan Menganalisa Data | 91

DIVISI HARGA DISCOUNT KOMISI TARGET

CORPORATE Rp 5,000,000 2.5% 5.0% Rp 10,000,000

EDUCATION Rp 2,500,000 5.0% 2.5% Rp 7,500,000

PERSONAL Rp 1,000,000 1.0% 1.0% Rp 5,000,000

Rumus lainnya: Discount = diberikan jika jumlah bayar lebih dari target Jumlah Bayar = (Jumlah Jual x Harga) Discount Jumlah Komisi = diberikan sebesar Komisi (%) dari (Jumlah Jual x Harga) yang

Jumlah bayarnya memenuhi target. Jika benar, tabel isian akan nampak seperti tampilan tabel diatas.

Praktikum-praktikum berikut ini tetap menggunakan tabel isian soal nomor 2: 3. Buatlah PivotTable dengan ketentuan:

Aktifkan semua field yang ada.

Pada bagian Report Filter berisi field: DIVISI, GOL.KERJA, KODE MARKETING dan MASA KERJA

Pada bagian Row Labels berisi field: NAMA MARKETER

Pada bagian Column Labels Values tidak berisi field (kosong)

Selebihnya ada pada bagian Row Labels Values 4. Buat juga PivotChart-nya, dengan ketentuan:

Aktifkan field-field: KODE MARKETING, GOL.KERJA, DIVISI, dan JUML.JUAL

Pada bagian Report Filter berisi field: KODE MARKETING

Pada bagian Legend Field tidak berisi field (kosong)

Pada bagian Axis Fields berisi field: DIVISI, GOL.KERJA

Pada bagian Values berisi field: JUML.JUAL 5. Buatlah juga Grafik yang menunjukkan persentase Jumlah bayar dari ketiga divisi yang ada. 6. Kolom JUMLAH BAYAR, berilah Conditional Formatting - Data bars ; kolom JUMLAH KOMISI,

berilah Conditional Formatting Icon Sets Ratings 3 Stars, dengan ketentuan Gold Star jika JUMLAH KOMISI >= 500000, Half Gold Star jika JUMLAH KOMISI >= 250000 dan Silver Star jika JUMLAH KOMISI < 250000.

7. Copy Sheet yang berisi tabel isian ke sheet baru dan lakukan analisa data seperti berikut:

Berapa banyaknya software yang dijual oleh PURWIGETI agar dia mendapatkan komisi sebesar Rp.1.000.000,-

Berapa komisi yang diterima ANITA CAROLINA jika dia berhasil memberikan JUMLAH BAYAR sebesar Rp.25.740.000,-

Berapa JUMLAH BAYAR yang dihasilkan oleh ASTRID LOPPIES jika dia menginginkan mendapatkan komisi sebesar Rp.2.000.000,-

Berapa persen komisi dinaikkan agar HERRY SABATH memperoleh komisi sebesar Rp.100.000,-

Berapa persen discount yang diberikan agar JUMLAH BAYAR yang diberikan oleh LENY NJUNTAK dibulatkan sebesar Rp.19.000.000,-

e-biz education enterprise

92 | Membuat Grafik dan Diagram

6. Membuat Grafik & Diagram

Membuat Grafik Standar

Grafik Bergambar

Grafik dengan Data Persentase Menemukan Trend Membuat Sparkline

Untuk membuat grafik, pilih data yang akan dibuatkan grafiknya. Misalnya, tabel datanya seperti ini

Selanjutnya, berikut ini adalah langkah-langkahnya

6.1. Membuat Grafik Standar Langkah-langkah membuat grafik standar:

a. Pilih data dengan menandai mulai sel A2:D6. b. Pilih Tab Insert, pada Ribbon Group Charts pilih jenis grafik

yang diinginkan. Misalnya, dari jenis Column pilih Clustered Column yang ada pada subjenis 2-D Column.

c. Perhatikan pada Tab menu ditampilkan 3 tab baru yaitu, Tab Chart Tools: Design: berisi group Type, untuk mengubah jenis grafik (Change Chart Type),

menyimpan grafik sebagai Template (Save As Template); group Data, untuk mengubah baris dan kolom data (Switch Row/Column), memilih sumber data (Select Data); group Chart Layouts, untuk mengatur layout grafik; group Chart Styles, untuk memilih style grafik; dan group Move Chart, untuk memindahkan lokasi grafik ke lembar baru atau lembar kerja yang sama dengan data.

Layout: berisi group Current Selection, untuk memilih bagian-bagian dari grafik yang diaktifkan saat ini; group Insert, untuk menyisipkan gambar (picture), bangun (shapes) dan kotak teks (text box); group Labels, berisi berbagai pilihan perintah untuk mengatur pemberian label atau keterangan grafik; group Axes, untuk mengatur sumbu (axes) dan garis data grafik (gridlines); group Background, untuk mengatur latar-belakang area grafik; group Analysis, untuk menganalisis grafik; dan group Properties, untuk memberi nama grafik (chart name).

Format: berisi berbagai perintah untuk melakukan format teks pada area grafik. Pada langkah ini, aktifkan Tab Design Chart Tools lalu pilih Layout 9 pada group Chart Layouts. Ketikkan pada Chart Title: Jumlah Peserta Pelatihan 2005-2009; Axis Title sumbu Y: Jumlah; dan Axis Title sumbu X: Lembaga. Dan pilih Style 26 pada group Chart Styles.

d. Aktifkan Tab Layout Chart Tools, pada group Axes pilih Gridlines Primary Vertical Gridlines Major Gridlines.

e. Aktifkan Tab Format Chart Tools, lalu pilihlah WordArt Styles untuk memformat tampilan judul grafik.

6

Microsoft Excel 2010

Membuat Grafik dan Diagram | 93

6.2. Grafik Bergambar Sekarang kita akan membuat grafik bergambar, maksudnya kolom-kolom grafik pada grafik standar bisa diisi gambar yang mewakili data dengan skala tertentu. Langkah-langkahnya adalah sebagai berikut:

a. Tetap menggunakan grafik standar yang telah dibuat pada bagian diatas. b. Misalnya, data-data tersebut (Office, CCNA dan PHP) diwakili oleh gambar-

gambar clip art berikut ini:

Office CCNA PHP

c. Aktifkan salah satu kolom grafik. d. Pilih Tab Format Chart Tools. e. Pada group Current Selection, pilih Format

Selection.

f. Pada dialog box Format Data Series pilih Fill. g. Di bagian kanan berbagai pilihan Fill,

aktifkan Picture or texture fill.

h. Klik tombol Clip Art, lalu pilih salah satu gambar Clip Art.

i. Lalu pilih Stack and Scale with, isi dengan angka: 5.

j. Akhiri dengan klik Close. k. Ulangi langkah pada poin c sampai j untuk kolom CCNA dan PHP dengan gambar

clip art yang telah ditentukan.

l. Hasil akhir grafik akan nampak seperti gambar dibawah ini

6.3. Grafik dengan Data Persentase Grafik dengan data persentase biasanya ditunjukkan oleh grafik dengan jenis Pie. Atau dengan kata lain grafik ini menampilkan ringkasan data dengan membandingkan item satu dengan item lainnya berupa persentasenya saja. Sebab itu hanya dua kolom data saja yang ditandai. Berikut ini langkah-langkahnya:

a. Masih menggunakan tabel data diatas. Tandai sel A2:A6, lalu sambil menekan tombol CTRL keyboard tandai sel E2:E6.

b. Aktifkan Tab Insert, pada group C