Halaman 1 dari 33 Nurjati Widodo, S.AP, M.AP Lesson 3: MENGGUNAKAN DATA DI FORM OPINI 1. Tabel Pivot untuk Informasi dan Opini Tabel pivot pada dasarnya sebuah tool ringkasan data yang secara otomatis bisa mengurutkan, menghitung, menjumlah atau memberikan nilai rata-rata dari data yang kita masukkan pada tabel ataupun worksheet. Dinamai tabel pivot karena kemampuannya yang bisa mengatur (set up) dan merubah ke struktur ringkasan dengan melakukan dragging and dropping fields (menyeret dan meletakkan lajur kolom tertentu) secara grafik (atau merotasi/mempivot data). Yang dimaksud Field adalah kolom. Pivot Table dan Pivot Chart Pivot Table adalah salah satu fasilitas didalam Excel, dimana ia berupa table interaktif yang sangat cepat dalam menganalisa, mengkombinasikan dan membandingkan sejumlah data baik sedikit maupun banyak. Dalam Pivot Table, kita dapat memilih suatu baris atau kolom yang hendak dijadikan referensi dalam melihat jumlah total dari suatu data. Pivot Table hanya bersifat report/laporan, artinya Anda tidak dapat mengedit data pada PivotTable. Jika ingin mengedit data, maka Anda harus melakukannya pada table aslinya (sumber data). Adapun PivotChart adalah sama saja dengan PivotTable, hanya saja ia ditampilkan berupa Grafik. Kapankah anda menggunakan sebuah tabel pivot? Yakni ketika anda mempunyai beberapa isu dan keperluan berikut ini: a. Sejumlah besar data transaksi yang telah menjadi semakin sulit untuk menganalisis dan meringkas dengan cara yang berarti. b. Perlu mencari hubungan dan pengelompokan dalam data anda c. Perlu mencari sebuah daftar nilai khusus untuk satu field di data anda d. Perlu mencari tren data menggunakan periode waktu yang bervariasi e. Mengantisipasi seringnya permintaan perubahan pada analisis data anda f. Perlu membuat subtotal yang sering kali memasukkan tambahan baru g. Perlu mengatur data anda ke dalam sebuah format yang mudah dibuat diagram. Untuk hasil yang terbaik, sumber data tabel pivot seharusnya dalam format mendatar (tabular) dengan disertai judul dan masing-masing field berisi data. Demikian pula data kita yang memang sudah berformat tabular sehingga kita siap menggunakan tabel pivot. a. Copy worksheet sales ke sheet baru (sheet (2)). b. Klik Insert Tab. Kemudian pilih tombol pivot table. Jendela Create Pivot Table akan terbuka. c. Pilih rentang sel A3:I510 dan klik OK. Sebuah tabel pivot akan dibuat.
33
Embed
Lesson 3: MENGGUNAKAN DATA DI FORM OPINI 1. Tabel Pivot ...nurjatiwidodo.lecture.ub.ac.id/files/2013/01/Lesson-3.pdf · berapa per bulannya? Di pertanyaan ini, kita perlu, total,
This document is posted to help you gain knowledge. Please leave a comment to let me know what you think about it! Share it to your friends and learn new things together.
Transcript
Halaman 1 dari 33
Nurjati Widodo, S.AP, M.AP
Lesson 3: MENGGUNAKAN DATA DI FORM OPINI
1. Tabel Pivot untuk Informasi dan Opini
Tabel pivot pada dasarnya sebuah tool ringkasan data yang secara otomatis bisa
mengurutkan, menghitung, menjumlah atau memberikan nilai rata-rata dari data yang kita
masukkan pada tabel ataupun worksheet. Dinamai tabel pivot karena kemampuannya yang
bisa mengatur (set up) dan merubah ke struktur ringkasan dengan melakukan dragging and
dropping fields (menyeret dan meletakkan lajur kolom tertentu) secara grafik (atau
merotasi/mempivot data). Yang dimaksud Field adalah kolom.
Pivot Table dan Pivot Chart Pivot Table adalah salah satu fasilitas didalam Excel, dimana ia
berupa table interaktif yang sangat cepat dalam menganalisa, mengkombinasikan dan
membandingkan sejumlah data baik sedikit maupun banyak. Dalam Pivot Table, kita dapat
memilih suatu baris atau kolom yang hendak dijadikan referensi dalam melihat jumlah total
dari suatu data. Pivot Table hanya bersifat report/laporan, artinya Anda tidak dapat mengedit
data pada PivotTable. Jika ingin mengedit data, maka Anda harus melakukannya pada table
aslinya (sumber data). Adapun PivotChart adalah sama saja dengan PivotTable, hanya saja ia
ditampilkan berupa Grafik.
Kapankah anda menggunakan sebuah tabel pivot? Yakni ketika anda mempunyai beberapa
isu dan keperluan berikut ini:
a. Sejumlah besar data transaksi yang telah menjadi semakin sulit untuk menganalisis dan
meringkas dengan cara yang berarti.
b. Perlu mencari hubungan dan pengelompokan dalam data anda
c. Perlu mencari sebuah daftar nilai khusus untuk satu field di data anda
d. Perlu mencari tren data menggunakan periode waktu yang bervariasi
e. Mengantisipasi seringnya permintaan perubahan pada analisis data anda
f. Perlu membuat subtotal yang sering kali memasukkan tambahan baru
g. Perlu mengatur data anda ke dalam sebuah format yang mudah dibuat diagram.
Untuk hasil yang terbaik, sumber data tabel pivot seharusnya dalam format mendatar (tabular)
dengan disertai judul dan masing-masing field berisi data. Demikian pula data kita yang
memang sudah berformat tabular sehingga kita siap menggunakan tabel pivot.
a. Copy worksheet sales ke sheet baru (sheet (2)).
b. Klik Insert Tab. Kemudian pilih tombol pivot table. Jendela Create Pivot Table akan
terbuka.
c. Pilih rentang sel A3:I510 dan klik OK. Sebuah tabel pivot akan dibuat.
Halaman 2 dari 33
Nurjati Widodo, S.AP, M.AP
Catatan: sebuah table pivot bisa dibuat di worksheet baru ataupun di worksheet yang sudah
ada. Data bisa berasal dari sebuah worksheet ataupun secara langsung dari sebuah
database.
Ketika anda membuat tabel pivot, di dalam tabel belum ada data (kosong). Untuk
memasukkan data anda perlu memilih field (kolom) dan memasukkannya ke dalam keempat
“drop zones” (wilayah tempat di mana kita bisa menjatuhkan sejumlah data), temukan di Pivot
Table Field List; yakni Report Filter, Column Labels, Row Labels, dan Values.
Report Filter – memasukkan sebuah field (kolom) ke Report Filter drop zone, memasukkan
field tersebut di area filter dari tabel pivot anda, memperbolehkan anda mem-filter item-item
Drop Zones
Halaman 3 dari 33
Nurjati Widodo, S.AP, M.AP
data khusus tersebut. Dengan kata lain Report Filter adalah komponen PivotTable yang
digunakan untuk memilih subset dari laporan Pivot Table yang digunakan. Komponen ini tidak
terdapat di area report.
Column Labels – memasukkan suatu field ke dalam Column Labels drop zone untuk
menampilkan nilai khusus dari field tersebut di seluruh bagian atas tabel pivot.
Row Labels – memasukkan suatu field ke dalam Row Labels drop zone untuk menampilkan
nilai khusus dari field tersebut terletak menurun di sisi kiri dari tabel pivot.
Values – memasukkan suatu field ke dalam Values drop zone, caranya dengan memasukkan
field tersebut di area values dari tabel pivot anda, selain itu juga memungkinkan anda
melakukan perhitungan khusus menggunakan nilai yang ada di field.
Mari menggunakan tabel pivot untuk membantu kita menjawab beberapa pertanyaan dari
lesson 2 berikut ini:
Pertanyaan 1. Pasar mana yang sudah menjual produk kita?
1. Klik ke field Location. Maka anda akan mendapat jawabannya.
Pertanyaan 2. Siapa partner kita?
1. Klik ke field Partner. Maka anda juga bisa mendapat jawabannya di sini.
Halaman 4 dari 33
Nurjati Widodo, S.AP, M.AP
Pertanyaan 3. Berapa banyak bulan dari data yang kita punya?
1. Klik ke field Months.
2. Anda akan melihat bahwa hasilnya mengembalikan nilai bukan 1 hingga 12. Tetapi
mendapat jawaban 2903. Karena excel secara otomatis akan menjumlahkan semua bulan
yang ada di data. Untuk melihat berapa banyak bulan yang kita punya, hanya dengan
mengklik dan drag kotak Sum of Month ke kotak row label.
Halaman 5 dari 33
Nurjati Widodo, S.AP, M.AP
Pertanyaan 4. Berapa total penjualan untuk masing-masing lokasi di tahun tersebut? Dan
berapa per bulannya? Di pertanyaan ini, kita perlu, total, lokasi dan bulan untuk tabel pivot kita.
1. Masukkan field bulan, lokasi dan total ke tabel pivot.
2. Karena kita perlu data tahun dan bukan bulan, mari pindahkan posisi bulan di bawah posisi
lokasi, dengan menggeser (drag). Sekarang anda bisa melihat tabel pivot menampilkan
informasi yang lebih baik.
Catatan: meletakkan posisi lebih di atas pada kotak drop zone Row Labels, berarti
menjadikan parent (posisi utama) dari data yang diletakkan dibawahnya.
3. Klik pada kotak kecil negatif untuk menggulung tabel ke atas.
Halaman 6 dari 33
Nurjati Widodo, S.AP, M.AP
Di ilustrasi di atas anda akan melihat bahwa tabel pivot sudah memberi anda total penjualan
masing-masing bulan dan masing-masing tahun dalam tabel. Anda juga dapat menggulung
informasi ke atas ataupun ke bawah dengan meng-klik pada kotak + atau -.
Pertanyaan 5. Partner mana yang berkontribusi paling banyak pada keseluruhan penjualan?
Pada pertanyaan ini kita perlu tahu partner kita dan nilai penjualan.
1. Masukkan partner dan nilai penjualan ke tabel pivot. Maka anda akan mendapatkan
jawaban.
2. Pada tabel pivot, anda dapat memasukkan pemformatan bersyarat (conditional formatting)
untuk membantu anda mengidentifikasi siapa yang mempunyai penjualan terbanyak. Anda
Halaman 7 dari 33
Nurjati Widodo, S.AP, M.AP
bisa menggunakan Colour Scales atau Data Bars. Sorot pada data dan klik conditional
formatting untuk format yang anda inginkan.
Pertanyaan 6. Pada masing-masing lokasi, siapakah partner terkuat dalam penjualan? Pada
pertanyaan ini, kita perlu tahu partner, lokasi dan total penjualan.
1. Masukkan partner, lokasi dan total penjualan ke tabel pivot.
2. Karena fokusnya adalah lokasi, pindahkan atau pastikan lokasi berada di atas partner.
3. Untuk membantu melihat dengan lebih mudah, kita bisa menggunakan conditional
formatting. Untuk efek yang lebih dramatis, anda bisa menggunakan Data Bar.
Halaman 8 dari 33
Nurjati Widodo, S.AP, M.AP
Seperti yang anda lihat, yang kita lakukan adalah cara yang lebih mudah dari pada
menggunakan rumus di Lesson 2. Di sini hanya perlu menggunakan sedikit klik untuk
mendapatkan hasil yang sama.
Pertanyaan 7. Berapa banyak unit yang terjual pada masing-masing partner di tiap-tiap area
dan total penjualan di tiap area? Pada pertanyaan ini kita perlu tahu partner, lokasi, jumlah unit
terjual (amount) dan total penjualan.
1. Masukkan partner, lokasi, amount dan total penjualan ke tabel pivot.
2. Karena fokusnya adalah lokasi, pastikan lokasi berada pada posisi di atas partner.
3. Untuk membantu melihat lebih mudah, kita bisa memformat angka-angka. Sorot Data yang
berisi angka, klik tombol koma (,) pada tab home. Lihat hasilnya. Ini akan memberikan
angka tersebut sebuah format accounting.
Atau jika tidak menghasilkan seperti yang kita inginkan, klik Format Cells: Number, isikan 2
pada desimal Place untuk memberi 2 digit di belakang koma. Dan cek/centang kotak use
1000 separator (.).
Halaman 9 dari 33
Nurjati Widodo, S.AP, M.AP
2. Perubahan Kosmetik Tabel Pivot.
Anda bisa memformat tabel pivot anda dengan menggunakan sebagian besar fungsi alat-alat
pemformatan. Cara paling mudah untuk mendapat tampilan dan kesan yang baru adalah
dengan menggunakan Tab Design. Tab Design hanya akan muncul ketika anda klik pada
tabel pivot.
1. Menggunakan contoh dari pertanyaan 7, klik pada Tab Design.
2. Klik pada panah ke bawah atau jatuhkan ke bawah kotak Pivot Table Styles untuk
melihat berbagai variasi model dan warna.
3. Ketika anda memindahkan pointer mouse di atas salah satu desain, tabel pivot secara
otomatis akan berubah. Yang maksudnya memberikan anda sebuah tampilan sebelum
anda benar-benar memilih.
4. Ketika sudah menentukan pilihan, klik di tempat itu.
Ada tiga tipe dari layout (tata letak) tabel pivot, yakni Compact, Outline dan Tabular. Secara
default (setelan standar), semua tabel pivot baru akan menggunakan layout Compact. Menu
untuk memodifikasi tata letak ada di bawah Tab Design. Fungsi utamanya adalah
memperkuat kreativitas pengguna. Mari kita lihat perbedaan report layout terlebih dahulu.
1. Klik tab Design.
2. Klik pada tombol Report Layout.
3. Tiga tipe layout akan ditunjukkan di menu dengan dua variasi, mengulang item atau tidak
mengulang item. Fungsi ini memperbolehkan ataupun menghilangkan informasi di header
(judul).
Halaman 10 dari 33
Nurjati Widodo, S.AP, M.AP
Fungsi layout yang selanjutnya adalah fungsi subtotal. Fitur ini memindahkan subtotal ke
posisi paling atas atau paling bawah dari masing-masing grup, atau mematikannya. Layout
secara default akan menunjukkan semua subtotal berada di atas. Mari kita ubah subtotal ke
bawah.
1. Klik pada tab Design.
2. Klik pada subtotal dan pilih Show All Subtotals at Bottom of Group, semua sub total
akan berada di bawah masing-masing grup.
Compact Layout Outline Layout Tabular Layout
Halaman 11 dari 33
Nurjati Widodo, S.AP, M.AP
Fungsi Grand Total digunakan untuk mengaktifkan grand total ataupun juga mematikannya
pada baris dan kolom. Secara default, Grand Total ditunjukkan pada kolom. Mari kita
hilangkan Grand Total.
1. Klik pada Tab Design
2. Klik pada grand total dan pilih Off for rows and columns, untuk mematikan pada baris
dan kolom.
Fungsi Blank Rows (baris kosong) memungkinkan pengguna memasukkan atau
menghilangkan baris kosong antar item. Secara default, tidak ada baris kosong di antara item.
Mari kita masukkan satu baris kosong setelah item.
Halaman 12 dari 33
Nurjati Widodo, S.AP, M.AP
1. Klik tab Design.
2. Klik pada Blank Rows, dan pilih Insert Blank Line Rafter Beach Item, untuk
memasukkan baris kosong setelah masing-masing item.
Anda juga bisa menyembunyikan tombol kotak drill up/down pada tabel pivot. Secara default
fungsi ini menyala. Mari kita coba matikan.
1. Klik Tab Option.
2. Klik tombol +/-. Maka kotak kecil +/- di samping judul baris akan hilang.
Halaman 13 dari 33
Nurjati Widodo, S.AP, M.AP
3. Melakukan Penghitungan di Tabel Pivot.
Tabel pivot juga memberikan kita kemampuan untuk menganalisis data dengan melakukan
perhitungan yang tidak dilakukan di set data aslinya. Cara melakukannya adalah dengan
menggunakan fungsi calculated field (menghitung field) dan calculated items (menghitung
item). Calculated Field merupakan suatu data field yang dibuat dari suatu perhitungan buatan
menggunakan data mentah atau dengan memasukkan suatu kolom virtual. Sedangkan
Calculated Items merupakan suatu data item yang dibuat dengan mengeksekusi suatu
perhitungan atau memasukkan suatu baris virtual.
Pada data mentah kita, kita bisa melihat bahwa masing-masing partner diberikan diskon ketika
mereka melakukan pembelian. Menggunakan tabel pivot kita, mari hitung persentase diskon
yang diberikan kepada partner menggunakan fungsi calculated field. Fungsi ini digunakan
untuk melakukan perhitungan dalam keseluruhan item yang ada dalam field (kolom pivot)
1. Copy worksheet Sales ke worksheet baru (rename sheet tersebut menjadi
Workingsales4).
2. Buat tabel pivot baru menggunakan data mentah.
3. Masukkan Partner, Location dan Total penjualan. Langkah ini akan memberi anda tabel
pivot dengan data partner penjualan berdasarkan lokasi pasar.
4. Untuk membuat Calculated Field, pada Tab Options klik Calculation. Klik Field, Items &
Sets. Pilih Calculated Field. Jendela Insert Calculated Field akan muncul.
5. Mari kita berikan data field baru tersebut nama. Di kotak input nama, ketikkan % Discount.
6. Untuk mencari persentase diskon yang diberikan, kita perlu membuat satu rumus. Pilih
field discount dan kemudian klik tombol Insert Field.
Halaman 14 dari 33
Nurjati Widodo, S.AP, M.AP
7. Kemudian ketik garis miring (/) setelah itu. Pilih field Price dan kemudian klik tombol insert
field.
8. Rumus dituliskan sebagai berikut =Discount/Price
9. Kemudian klik OK.
Halaman 15 dari 33
Nurjati Widodo, S.AP, M.AP
Setelah anda klik OK, suatu kolom baru dimasukkan ke tabel pivot dan suatu field baru
dimasukkan ke field tabel anda. Kemudian anda dapat memformat nomor untuk mewakili
persentase dengan klik pada % yang ada di Tab Home.
Jika anda sekarang memasukkan data field Month ke Row Labels, anda dapat melihat
diskon diberikan di bulan tersebut. Jangan lupa juga berikan format sel persentase (%) untuk
memberikan tampilan lebih baik.
Halaman 16 dari 33
Nurjati Widodo, S.AP, M.AP
Untuk menghitung rata-rata penjualan selama 6 bulan pertama, kita akan menggunakan
fungsi Calculated Item. Fungsi ini digunakan untuk memisahkan perhitungan dengan
menggunakan sebagian dari data yang ada (hanya item tertentu).
1. Copy worksheet Sales ke worksheet baru dan rename sheet tersebut menjadi
Workingsales5.
2. Buat tabel pivot baru menggunakan data mentah. Masukkan field Month ke Row Labels
dan field Total ke ∑ Values. Cara ini akan memberikan kita nilai penjualan berdasarkan
bulan. Agar tampilan angka-angka lebih mudah dilihat, gunakan pengaturan Format Cell:
Number.
Halaman 17 dari 33
Nurjati Widodo, S.AP, M.AP
3. Untuk membuat Calculated Item, klik Tab Options. Klik Calculations, klik Fields, Items