Modul-Excel-2010.pdf

Microsoft®

Office 2010

Microsoft Excel 2010

MIGRATING TO THE BEST

DAFTAR ISI 1.

BEKERJA DENGAN PENGOLAH ANGKA .......................... ................................................ ............................................. ......................... .. 51 1.1. Mengenal Anatomi Excel ............................................................... ........................................................................................ ......................... 51 1.2. Mempersiapkan Buku Kerja .......................................................... ................................................................................... ......................... 53 1.3. Mengatur Tayangan Workbook................................................................... .............................................................................. ........... 56 1.4. Macam Data pada Excel ................................................................ ......................................................................................... ......................... 58 1.5. Mengatur Bentuk Tampilan Data ............................................................. ........................................................................... .............. 58 1.6. Membuat Tabel .................................................................. ...................................................................................................... .................................... 60 1.7. Cara-cara Menghapus....................... Menghapus.......................................................................................... ...................................................................... ... 60 1.8. Rumus & Fungsi .................................................................. ...................................................................................................... .................................... 61 PRAKTIKUM 1 ........................................................... ...................................................................................................................... ........................................................... 65 65

2.

MENGOLAH DATA SEL ........................ .............................................. ............................................. ............................................. ............................ ...... 67 2.1. Memasukkan Data dengan Fill Handle ................................................................... 67 2.2. Menggunakan Fasilitas Autofill ................................................................... .............................................................................. ........... 67 2.3. Menyalin Data ........................................................ ........................................................................................................ ................................................ 68 2.4. Alamat sel ............................................................... ............................................................................................................... ................................................ 69 2.5. Fungsi dan Operator Logika........................................................... .................................................................................... ......................... 69 PRAKTIKUM 2 ........................................................... ...................................................................................................................... ........................................................... 70 70

3.

MENGOLAH DATA SPESIFIK ............................... ..................................................... ............................................ .................................... .............. 71 3.1. Conditional Formatting.................................................................. Formatting........................................................................................... ......................... 71 3.2. Memilih Bentuk Tabel ................................................................... ............................................................................................ ......................... 72 3.3. Bekerja dengan Data Spesifik ...................................................................... ................................................................................. ........... 72 PRAKTIKUM 3 ........................................................... ...................................................................................................................... ........................................................... 74 74

4.

PENGOLAHAN PENGOLAHAN TABEL DAN DATA ............................................. .................................................................... .................................... ............. 76 4.1. Memberi Nama Range Tabel ......................................................... .................................................................................. ......................... 76 4.2. Fungsi Pembacaan Pembacaan Tabel ............................................................... ........................................................................................ ......................... 76 4.3. Pemeriksaan Kesalahan Pengolahan Pengolahan Data .......................................................... ............................................................. ... 78 PRAKTIKUM 4 ........................................................... ...................................................................................................................... ........................................................... 79 79

5.

MENINJAU & MENGANALISA MENGANALISA DATA .............................. .................................................... ............................................. ......................... .. 80 5.1. Memberi Komentar ............................................................ ................................................................................................ .................................... 80 5.2. Validasi Data ........................................................... ........................................................................................................... ................................................ 80 5.3. Menganalisa data .............................................................. ................................................................................................... ..................................... 82 5.4. Bekerja dengan Outline ................................................................. .......................................................................................... ......................... 84 5.5. Bekerja dengan PivotTable ............................................................ ..................................................................................... ......................... 85 5.6. Bekerja dengan Slicer ......................................................... ............................................................................................. .................................... 88 5.7. Melindungi Dokumen ......................................................... ............................................................................................. .................................... 89 PRAKTIKUM 5 ........................................................... ...................................................................................................................... ........................................................... 90 90

6.

MEMBUAT GRAFIK & DIAGRAM........................................... ................................................................. ....................................... ................. 92 6.1. Membuat Grafik Standar ............................................................... ........................................................................................ ......................... 92 6.2. Grafik Bergambar Bergambar .............................................................. ................................................................................................... ..................................... 93 6.3. Grafik dengan Data Persentase ................................................................... .............................................................................. ........... 93 6.4. Menemukan Trend ............................................................. ................................................................................................. .................................... 94 6.5. Membuat Sparkline ............................................................ ................................................................................................ .................................... 95 PRAKTIKUM 6 ........................................................... ...................................................................................................................... ........................................................... 96 96

LAMPIRAN PRAKTIKUM EXCEL LEBIH LANJUT ................................................ ....................................................................... ......................... .. 97

i

DAFTAR ISI 1.

BEKERJA DENGAN PENGOLAH ANGKA .......................... ................................................ ............................................. ......................... .. 51 1.1. Mengenal Anatomi Excel ............................................................... ........................................................................................ ......................... 51 1.2. Mempersiapkan Buku Kerja .......................................................... ................................................................................... ......................... 53 1.3. Mengatur Tayangan Workbook................................................................... .............................................................................. ........... 56 1.4. Macam Data pada Excel ................................................................ ......................................................................................... ......................... 58 1.5. Mengatur Bentuk Tampilan Data ............................................................. ........................................................................... .............. 58 1.6. Membuat Tabel .................................................................. ...................................................................................................... .................................... 60 1.7. Cara-cara Menghapus....................... Menghapus.......................................................................................... ...................................................................... ... 60 1.8. Rumus & Fungsi .................................................................. ...................................................................................................... .................................... 61 PRAKTIKUM 1 ........................................................... ...................................................................................................................... ........................................................... 65 65

2.

MENGOLAH DATA SEL ........................ .............................................. ............................................. ............................................. ............................ ...... 67 2.1. Memasukkan Data dengan Fill Handle ................................................................... 67 2.2. Menggunakan Fasilitas Autofill ................................................................... .............................................................................. ........... 67 2.3. Menyalin Data ........................................................ ........................................................................................................ ................................................ 68 2.4. Alamat sel ............................................................... ............................................................................................................... ................................................ 69 2.5. Fungsi dan Operator Logika........................................................... .................................................................................... ......................... 69 PRAKTIKUM 2 ........................................................... ...................................................................................................................... ........................................................... 70 70

3.

MENGOLAH DATA SPESIFIK ............................... ..................................................... ............................................ .................................... .............. 71 3.1. Conditional Formatting.................................................................. Formatting........................................................................................... ......................... 71 3.2. Memilih Bentuk Tabel ................................................................... ............................................................................................ ......................... 72 3.3. Bekerja dengan Data Spesifik ...................................................................... ................................................................................. ........... 72 PRAKTIKUM 3 ........................................................... ...................................................................................................................... ........................................................... 74 74

4.

PENGOLAHAN PENGOLAHAN TABEL DAN DATA ............................................. .................................................................... .................................... ............. 76 4.1. Memberi Nama Range Tabel ......................................................... .................................................................................. ......................... 76 4.2. Fungsi Pembacaan Pembacaan Tabel ............................................................... ........................................................................................ ......................... 76 4.3. Pemeriksaan Kesalahan Pengolahan Pengolahan Data .......................................................... ............................................................. ... 78 PRAKTIKUM 4 ........................................................... ...................................................................................................................... ........................................................... 79 79

5.

MENINJAU & MENGANALISA MENGANALISA DATA .............................. .................................................... ............................................. ......................... .. 80 5.1. Memberi Komentar ............................................................ ................................................................................................ .................................... 80 5.2. Validasi Data ........................................................... ........................................................................................................... ................................................ 80 5.3. Menganalisa data .............................................................. ................................................................................................... ..................................... 82 5.4. Bekerja dengan Outline ................................................................. .......................................................................................... ......................... 84 5.5. Bekerja dengan PivotTable ............................................................ ..................................................................................... ......................... 85 5.6. Bekerja dengan Slicer ......................................................... ............................................................................................. .................................... 88 5.7. Melindungi Dokumen ......................................................... ............................................................................................. .................................... 89 PRAKTIKUM 5 ........................................................... ...................................................................................................................... ........................................................... 90 90

6.

MEMBUAT GRAFIK & DIAGRAM........................................... ................................................................. ....................................... ................. 92 6.1. Membuat Grafik Standar ............................................................... ........................................................................................ ......................... 92 6.2. Grafik Bergambar Bergambar .............................................................. ................................................................................................... ..................................... 93 6.3. Grafik dengan Data Persentase ................................................................... .............................................................................. ........... 93 6.4. Menemukan Trend ............................................................. ................................................................................................. .................................... 94 6.5. Membuat Sparkline ............................................................ ................................................................................................ .................................... 95 PRAKTIKUM 6 ........................................................... ...................................................................................................................... ........................................................... 96 96

LAMPIRAN PRAKTIKUM EXCEL LEBIH LANJUT ................................................ ....................................................................... ......................... .. 97

i


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



Excel

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 b. c. d. e. f. g.

survey , dan melakukan hampir semua jenis analisis keuangan. Membuat grafik dengan berbagai variasi pengaturan tampilannya. Pengorganisasian daftar sesuai tata letak baris dan kolom sehingga pengelolaan data lebih efisien. Mampu mengakses data lainnya karena memiliki fasilitas impor data dari berbagai sumber. Membuat panel kontrol grafis yang memungkinkan meringkas sejumlah besar informasi bisnis dalam format yang ringkas. Membuat grafik dan diagram dengan berbagai bentuk bangun dan SmartArt yang lebih profesional. 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 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 bagian- bagian dan elemennya …

Bekerja dengan Pengolah Angka| 51

e-biz education enterprise Row Numbers Quick Access Toolbar

Name Box

Tabs

File Tab

Formula Bar Title Bar

Insert Function Insert Worksheet Button Active Cell Indicator Sheet Tab Scroll Button

Column Letters

Pointer Mouse Page View Button Ribbon

Bagian-bagian Bagian-bagian dan elemen dari Excel yang 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. 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. Kolom lembar kerja Excel yang yang ditandai atau diberi label huruf A, B, C – Z, – Z, AA, AB – AB – AZ, AZ, AAA,AAB – AAA,AAB – XFD 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. Backstage View , klik ini jika ingin bekerja dengan berbagai perintah dokumen seperti menyimpan, mencetak, dsb. Atau mengatur setting tampilan Excel . (Active cell Isi sel akan ditampilkan pada baris ini jika Indikator Sel Active indicator ) berada pada sel tersebut. Garis-garis yang ada pada lembar kerja, yang membatasi kolom dan baris. Menampilkan sekumpulan berbagai fungsi Excel Menyisipkan lembar kerja (Worksheet ) baru. Secara default ada ada 3 sheet dalam dalam sebuah buku kerja ( Workbook ). ). Active Cell Indicator ) atau nama Menunjukkan posisi Indikator Sel ( Active sel, range atau object aktif. aktif. Untuk memilih mode tampilan lembar kerja Excel : Normal, Page Layout, Page Break Preview . Simbol Plus besar warna putih yang berhubungan dengan gerakan mouse. Klik kanan untuk menampilkan pilihan pop-up menu /

Cell

Column Letters

File Tab

Formula Bar Gridlines Insert Function Insert Worksheet Button Name Box Page View Button Pointer Mouse

52 | Bekerja dengan Pengolah Angka


Quick Access Toolbar Ribbon Groups Row Numbers

Sheet Tab Scroll Button Tabs Title Bar

1.2.

perintah (Contextual menu). Klik kiri untuk memilih. Klik ganda (double klik ) untuk menjalankan pilihan (eksekusi). Untuk mengatur penampilan ikon-ikon perintah Excel sehingga akses perintah tersebut lebih cepat sebab selalu tampil diatas Tabs. Berbagai perintah utama Excel dikelompokkan pada bagian ini. 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. Klik tombol ini untuk menggeser lembar kerja (sheet ) yang tidak kelihatan karena banyaknya sheet yang ada. Klik nama-nama kelompok perintah (=menu) untuk menampilkan ribbon Excel . Baris judul yang berisi nama file dan program aktif.

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

 



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 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 diletakkan 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 : Mistar vertikal Margin kiri

Header

Margin kanan

Mistar horisontal

Page Break Preview , menayangkan workbook berupa penggalanpenggalan 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 …



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 … Horizontal Tiled

Vertikal

Cascade


e-biz education enterprise 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



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

Decrease Decimal

Percent Style

Comma Style

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 … Contoh tampilan format sel yang dipilih Ketik disini format sel yang diinginkan Pilihan format sel yang disediakan Excel

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 untuk menggabungkan Merge cells, 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 …


e-biz education enterprise

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.



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 b. Menggunakan fungsi dengan Tab Home – Insert Function: 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.

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

Math & Trig :

Nama Fungsi =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



=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 =SUMIFS(range yg dijumlah, range kriteria1, kriteria1, …) Menjumlahkan isi sel yang sesuai dengan beberapa kriteria. Contoh: =SUMIFS(A1:A6,A1:A6,">4",A1:A6,"<8") hasilnya: 11 =SUMPRODUCT(array1, array2) Mengalikan bilangan yang berhubungan / sesuai dari array1 dengan array2, kemudian menjumlahkan hasilnya. Contoh: =SUMPRODUCT(A1:A3,A4:A6) hasilnya: 105 =SUMPRODUCT(A1:A2,A3:A4) hasilnya: 39 =AVERAGE(bilangan1,[ bilangan2],[ bilangan3], …) Menghitung rata-rata isi sel. Contoh: =AVERAGE(4,6,2) hasilnya: 4 =AVERAGE(A1:A4) hasilnya: 4.5 =AVERAGEIF(range,kriteria) Menghitung rata-rata isi sel yang memenuhi kriteria. Contoh: =AVERAGEIF(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,"<8") hasilnya: 5.5

Statistical:

=COUNT(nilai1,[nilai2],[nilai3],…) Menghitung banyaknya sel yang berisi angka. Contoh: =COUNT(1,3,4,”C”,5) hasilnya: 4 =COUNT(A1:A6) hasilnya: 6 =COUNTA(nilai1,[nilai2],[nilai3],…) Menghitung banyaknya sel yang berisi data / tidak kosong. Contoh: =COUNTA(1,3,4,”C”,5) hasilnya: 5 =COUNTA(A1:A7) hasilnya: 6 =COUNTBLANK(range) Menghitung banyaknya sel yang kosong. Contoh: =COUNTBLANK(A1:A6) hasilnya: 0 =COUNTBLANK(A1:A7) hasilnya: 1 =COUNTIF(range, criteria) Menghitung banyaknya sel yang berisi angka yang sesuai kriteria. Contoh: =COUNTIF(A1:A6,5) hasilnya: 1 =COUNTIF(A1:A6,”<5”) hasilnya: 2


e-biz education enterprise =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,”<8”) hasilnya: 2 =FREQUENCY(data array, bins array) Menghitung banyaknya angka yang kurang dari sama dengan (<=). Contoh: =FREQUENCY(A1:A6,5) hasilnya: 3 =FREQUENCY(A1:A6,A2) hasilnya: 4 =MAX(bilangan1,[ bilangan2 ],[ bilangan3],…) Mencari bilangan yang tertinggi / terbesar. Contoh: =MAX(1,5,2) hasilnya: 5 =MAX(A1:A6) hasilnya: 9 =MEDIAN(bilangan1,[ bilangan2],[ bilangan3],…) Mencari nilai tengah. Contoh: =MEDIAN(2,4,6,8) hasilnya: 5 =MEDIAN(A1:A6) hasilnya: 5.5 =MIN(bilangan1,[ bilangan2],[ bilangan3],…) Mencari nilai terendah / terkecil. Contoh: =MIN(4,1,5) hasilnya: 1 =MIN(A1:A6) hasilnya: 3 =RANK(bilangan,seluruh data,[jenis urutan]) Mencari ranking atau urutan bilangan (jenis urutan 0-desc, 1-asc). Contoh: =RANK(A1,$A$1:$A$6,1) hasilnya: 1 Jika di-copy hingga baris 6 hasilnya: 1,4,3,2,5,6 Misalnya di sel A1 berisi tanggal 2/24/2011 (=hari Kamis) =DATE(tahun,bulan,tanggal) Untuk menampilkan tanggal sesuai format Excel. Contoh: =DATE(2010,12,02) hasilnya: 12/2/2010 Jika diformat … hasilnya: 2-Dec-10 =EOMONTH(tanggal mulai, bulan ke-) Menghasilkan hari terakhir dari suatu bulan yang dimulai dari tanggal mulai. Contoh: =EOMONTH(A1,1) hasilnya: 3/31/2011 =EOMONTH(A1,-2) hasilnya: 12/31/2010 =WEEKDAY(alamat tanggal, [bilangan hari]) Menghasilkan suatu hari dalam bilangan. Hari Minggu = 1, Senin = 2, Selasa = 3, dan seterusnya. Date & Time: Contoh: =WEEKDAY(A1) hasilnya: 5 =WEEKDAY(A1,2) hasilnya: 4 =TODAY() Menampilkan tanggal hari ini. Contoh: =TODAY() hasilnya: 24/2/2011 =NOW() Menampilkan tanggal dan waktu hari ini. Contoh: =NOW() hasilnya: 24/2/2011 13:15 =TIME(jam, menit, detik) Untuk menampilkan waktu. Contoh: =TIME(10,30,0) hasilnya: 10:30 AM =TIME(14,30,0) hasilnya: 2:30 PM



Text:

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



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.



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 sendirisendiri. 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: pointer mouse yang

diletakkan di sudut kanan bawah indikator sel aktif, bentuknya berubah dari plus tebal warna putih menjadi plus tipis.

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.

Mengolah Data Sel | 67

e-biz education enterprise 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 seriesnya, 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: Formulas (F)

Formulas and Number Formatting (O) Keep source Formatting (K)

Paste (P)

Transpose (T) No Borders (B)

Keep Source Column Width (W)

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)

Values and Number Formatting (A) Values and Source Formatting (E)

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. Paste Link (N) Formatting (R)

Picture (U) Link Picture (I)

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.

68 | Mengolah Data Sel


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 C1<50, nilainya “KURANG”. Jika C1 berisi data 50 – 80, nilainya “CUKUP”. Selain itu, nilainya “BAIK”. Maka penulisan rumusnya di D1: =IF(C1<50,”KURANG”,IF(C1>80,”BAIK”,”CUKUP”)) Atau =IF(C1>80,"BAIK",IF(C1<50,"KURANG","CUKUP")) Terkadang kondisi yang diuji memiliki lebih dari 1 kriteria. Misal: jika nilai teori > 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”)

Mengolah Data Sel | 69


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

WORD

EXCEL

PPOINT

OUTLOOK

ACCESS

LULUS

IPK

KETERANGAN RANKING

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 REMIDI TDK LULUS

f.

JUMLAH

NILAI

NAMA MAHASISWA

: : :

5 3 2

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()

70 | Mengolah Data Sel


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.

Mengolah Data Spesifik | 71

e-biz education enterprise 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 …

72 | Mengolah Data Spesifik


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 mengurutkan 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 …



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

74 | Mengolah Data Spesifik


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 t ahun. d. Tampilkan semua peserta yang berasal dari UMUM dan PERUSAHAAN yang berusia <= 30 tahun. e. Tampilkan semua peserta yang berasal dari UMUM dan PERUSAHAAN yang mendapatkan Diskon. f. Tampilkan semua peserta yang membayar > 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:



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)

76 | Pengolahan Tabel dan Data


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 …

Harus di baris pertama dan urut. Ini adalah indeks no 1

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 …

Nilai kunci, harus sama dg kolom pertama tabel data 



Harus di kolom pertama dan urut. Ini adalah indeks no 1

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

Pengolahan Tabel dan Data | 77


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 rumusrumus 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 ####### #VALUE #NAME? #REF! #DIV/0!

78 | Pengolahan Tabel dan Data

Keterangan Kolom kurang lebar untuk menampilkan data atau hasil pengolahan data. Jenis data salah. Kalau dalam proses perhitungan matematik berarti ada data yang bukan numerik Formula atau Fungsi (Function) tidak dikenali. Salah dalam penulisan fungsi atau rumus. Formula mengacu atau membaca data yang tidak ada atau salah dalam menuliskan alamat sel datanya. Formula berusaha membagi dengan 0. Pembagian dengan angka nol tidak diperkenankan.


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.

Pengolahan Tabel dan Data | 79


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:

80 | Meninjau dan Menganalisa Data


Contoh Tampilan Input Message Input Cell

Error Alert

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 (diantara) nilai terendah Between (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.

Meninjau dan Menganalisa Data | 81

e-biz education enterprise 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 ditampilkan 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


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

d. e. f.

pilih Scenario Manager … 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). Pilihlah alamat sel yang akan disimpan dan diubah isinya pada bagian Changing cells:, pastikan alamat selnya adalah sel H3:H5. Di bagian Protection: aktifkan Prevent changes (mencegah perubahan). 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 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 .



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 Tombol

j.

5.5.

untuk menyembunyikan detail data (Hide Detail ). untuk menampilkan detail data (Show Detail ).

Selanjutnya coba sendiri membuat outline berdasarkan BULAN-TAHUN.

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.



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 …



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.



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 … Field yang difilter

Clear filter

Semua item difilter

item difilter

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.



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



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.

MARKETING

MARKETER

KERJA

KERJA

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


DIVISI

JUML.

JUMLAH

JUMLAH

JUAL

BAYAR

KOMISI


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, 

 





   













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





92 | Membuat Grafik dan Diagram


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. Langkahlangkahnya 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 gambargambar clip art berikut ini:

c. d. e. f. g. h. i. j. k. l.

6.3.

Office CCNA PHP Aktifkan salah satu kolom grafik. Pilih Tab Format Chart Tools. Pada group Current Selection, pilih Format Selection. Pada dialog box Format Data Series pilih Fill . Di bagian kanan berbagai pilihan Fill , aktifkan Picture or texture fill . Klik tombol Clip Art , lalu pilih salah satu gambar Clip Art . Lalu pilih Stack and Scale with, isi dengan angka: 5. Akhiri dengan klik Close. Ulangi langkah pada poin c sampai j untuk kolom CCNA dan PHP dengan gambar clip art yang telah ditentukan. Hasil akhir grafik akan nampak seperti gambar dibawah ini …

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 Charts pilih jenis grafik Exploded pie in 3-D.

Membuat Grafik dan Diagram | 93

e-biz education enterprise c. Sampai disini grafik pie 3-D telah ditampilkan. Aktifkan Tab Layout Chart Tools, lalu pilih Legend – None. d. Klik Data Labels, lalu pilih More Data Label Options …

Akan ditampilkan dialog box Format Data Labels seperti gambar disamping ini … Pada bagian Label Options, aktifkan Category Name, Percentage dan Show Leader Lines. Pada bagian Label Position, aktifkan Outside End . Akhiri dengan klik Close.

e. Ubah judul grafik (chart title): Persentasi f.

6.4.

Peserta Pelatihan oleh Lembaga-lembaga. Aturlah elemen-elemen grafik dengan perintahperintah yang telah dipelajari sehingga grafik akan ditampilkan seperti ini …

Menemukan Trend

Excel 2007 mampu memberikan bantuan dalam melakukan analisis atau perkiraan kondisi perusahan atau suatu pekerjaan dengan menunjukkan kecenderungan yang sedang terjadi melalui data yang ada. Fasilitas yang digunakan untuk hal ini adalah Trend . Cara menemukan trend dari grafik yang ada adalah sebagai berikut: a. Aktifkan grafik standar (non 3-D) yang telah dibuat diatas. b. Pilih Tab Layout Chart Tools, pada group Analysis klik Trendline. Maka akan ditampilkan pilihan perintah seperti gambar disamping ini …

Pilihlah, misalnya Linear Trendline. Maka akan ditampilkan dialog box Add Trendline, untuk memilih data apa yang akan ditampilkan trend . Pada langkah ini, misalnya, dipilih CCNA.



c. Lebih detailnya pemberian trend dapat dipilih perintah More Trendline Options …, pada pilihan ini dialog box Format Trendline akan ditampilkan, seperti gambar disamping ini ... d. Setelah klik Close, maka tampilan grafik akan ditambahkan garis trend , yang bisa menunjukkan arah kecenderungan data di masa yang akan datang. e. Cobalah sendiri dengan pilihan trendtrend yang lain! Misalnya juga dengan menentukan berapa periode kedepan (Forward ) dan kebelakang (Backward ) pada bagian Forecast .

6.5.

Membuat Sparkline

Kita dapat membuat grafik dalam workbook Excel untuk meringkas data secara visual, dengan menggunakan legenda, label, dan warna untuk menyoroti aspek data tertentu. Namun dengan fasilitas terbaru yang disediakan oleh Excel 2010 , kita dimungkinkan untuk membuat grafik yang sangat kecil untuk meringkas data. Grafik tersebut ditampilkan hanya pada satu sel saja. Caranya adalah sebagai berikut: a. Aktifkan tab Insert . b. Lalu pada group Sparklines, klik Column. Maka akan ditampilkan dialog box Create Sparklines berikut ini … Tentukan alamat range data yang akan dibuatkan sparkline pada bagian Data Range. Tentukan lokasi penampilan sparkline pada bagian Location Range.

c. Jika sel yang berisi Sparkline diaktifkan, maka Tab Sparkline Tools Design akan ditampilkan seperti ini …

Pada Tab Design Sparkline Tools, terdapat beberapa Ribbon Group, yaitu: Group Sparkline, berisi perintah untuk membuat atau melakukan edit data sparkline. Group Type, berisi pilihan bentuk Sparkline (Line, Column, Win/Loss). Group Show , berisi pilihan untuk menampilkan atau memberi warna (tanda) yang berbeda pada data atau poin tertentu, misalnya: poin tertinggi (High Point ), poin terendah (Low Point ), poin pertama (First Point ), poin terakhir (Last Point ), poin negatif (Negative Points) dan yang dijadikan penanda (Markers). Group Style, berisi pilihan bentuk (Style) Sparkline yang dipilih; pilihan warna Sparkline (Sparkline Color ) dan warna penanda ( Marker Color ). Group, berisi pilihan perintah untuk mengatur sumbu sparkline, group / ungroup dan Clear (untuk menghapus Sparkline). 

 





Membuat Grafik dan Diagram | 95


PRAKTIKUM 6 1. Buatlah grafik Column jika diketahui tabel data sebagai berikut:

Tambahkan Linear Trendline untuk Royalti dan Investasi. Gantilah Sheet1 menjadi GRAFIK-1. Simpan dengan nama PRAKTIKUM-6. 2. Buatlah juga grafik exploded Pie in 3-D : pindahkan ke lembar kerja chart yang baru, lalu ganti nama Sheet -nya menjadi GRAFIK-2; tambahkan judul grafik ‘Grafik Kondisi Financial Perusahaan Tahun 2005– 2009 , juga legend seperti contoh pada materi diatas. Catatan: untuk memindahkan grafik ke lembar kerja chart , caranya adalah dengan meng-klik kanan grafik. Dari pilihan menu yang ditampilkan, pilih Move Chart …, pada dialog box yang ditampilkan pilih New chart . Atau aktifkan Tab Design Chart Tools, klik Move Chart yang ada di group Location. ’

3. Sekarang buatlah grafik bergambar, dengan gambar ClipArt

untuk royalti,

untuk obligasi, untuk deposito dan untuk investasi dan dengan skala 1:25. Pindahkan ke Sheet baru dan beri nama GRAFIK-3.



1. Buatlah tabel seperti berikut ini, dengan ketentuan: Data Masukan : Nama Sales, Harga, dan penjualan bulan JAN – JUL. Kolom : Jumlah Terjual, Total Harga, Prosentase, Jumlah, Rata-rata, Tertinggi dan Terendah diisi menggunakan rumus dan fungsi.

2. Tambahkan pada tabel diatas kolom BONUS, SESUAI TARGET dan JUMLAH KUOTA. BONUS = 5% dari Total Harga, jika Jumlah Terjual >= 25 2,5% dari Total Harga, jika Jumlah Terjual diantara 20 – 25, selain itu 0% SESUAI TARGET = berapa bulan yang mencapai target (target >= 5) JUMLAH KUOTA = jumlah penjualan yang mencapai target

3. Agar pelaksanaan training berjalan dengan lancar, para mahasiswa atau lembaga diharapkan membeli modul yang disediakan oleh perusahaan. Berikut ini adalah daftar harga modul training, buatlah daftar harga ini dengan ketentuan: Data masukan: harga modul per-eks, jumlah eksemplar.  

Nilai Rp … yang tertulis pada tabel adalah contoh jika benar.



Carilah 1 rumus saja untuk mengisi seluruh tabel daftar harga dibawah ini:

Lampiran Praktikum Excel Lebih Lanjut | 97


P

Training Enterprise Jl. Pucang Rinenggo 23 Surabaya 60235 – Jawa Timur - Indonesia  +62-31-8495758  +62-31-8495757  [email protected]

HARGA MODUL PER-EKS

OFFICE Rp

45,000

WEB DESAIN Rp

JUMLAH

50,000

AKUNTANSI Rp

65,000

ANIMASI Rp

70,000

HARGA TOTAL MODUL

EKSEMPLAR

OFFICE

WEB DESAIN

ANIMASI

AKUNTANSI

1

Rp

45,000

Rp

50,000

Rp

65,000

Rp

70,000

2

Rp

90,000

Rp

100,000

Rp

130,000

Rp

140,000

3 4 5 10 15 20 25 30 35 40 45 50

4. Buatlah tabel yang berisi tentang catatan honor yang akan diterima trainer untuk bulan Desember 2010, dengan ketentuan: Data masukan: Dibuat tanggal, Nama Trainer, Mulai kerja, dan Waktu training (mulai, selesai). Masa kerja = Dibuat tanggal – Mulai kerja Lama kerja = Waktu Training (selesai – mulai) Honor Bulan ini = Lama Kerja x Rp.50.000,- x 4 Masa kerja dan Lama kerja dalam bilangan angka bukan tahun atau jam (Number) 

   

98 | Lampiran Praktikum Excel Lebih Lanjut

Modul-Excel-2010.pdf

Recommend Documents