Muatan :
- Kilasan tentang pivot table
- Konsep pivot table
- Data untuk contoh kasus
- Sumber data untuk pivot table (Pivot Table Data Source)
- Membuat pivot table
- Menata pivot table
Kilasan
Pivot table di dalam Excel adalah sebuah fitur untuk membentuk suatu summary data sederhana. Fitur ini sangat ringan dan bisa relatif dinamis untuk membentuk berbagai macam summary data. Diluar kekurangan yang dimilikinya, tetap saja fitur ini bisa menjadi alat yang sangat bermanfaat, terutama untuk membentuk summary data sederhana. Tingkat kerumitan yang bisa dilakukan oleh fitur ini tetaplah sesuatu yang bersifat sederhana.Coretan kali ini mencoba untuk membahas pivot table dari nol dan bukan sekedar untuk bisa membuat pivot table. Coretan ini berusaha untuk menjabarkan hal-hal dasar agar lebih mudah dalam mengembangkan kreativitas dalam menggunakan fitur pivot table ini. Coretan ini menyertakan file yang bisa diunduh, sehingga bisa dipraktekkan langsung.
Konsep pivot table
Pivot table memiliki konsep sebagai sebuah tabel yang menampilkan tabel fakta (fact table) yang bersifat vertikal ke dalam susunan dimensi (baris dan kolom) dan nilai ukur (values). Dengan demikian maka pivot table hanya mampu menampilkan nilai ukur berupa data yang bertipe numerik. Seluruh data yang tidak bertipe numerik hanya bisa diletakkan pada sisi dimensi. Pivot table menampilkan nilai ukur sebagai nilai agregat.Karakter dasar pivot table sebagai alat menampilkan data tabel fakta mengindikasikan bahwa manfaat pivot table sangat tergantung pada susunan tabel fakta yang menjadi sumber data pivot table. Ketika tabel fakta tersusun baik dan terstruktur secara sederhana, maka bisa diperoleh manfaat yang besar dari pivot table. Semakin rumit susunan tabel fakta, maka semakin rendah manfaat yang bisa diperoleh dari pivot table.
Data untuk contoh kasus
Data yang akan digunakan dalam pembahasan mengenai pivot table ini adalah data-data yang berkaidah database agar bisa disusun sebuah tabel fakta yang baik dan sederhana. Kali ini, data berupa data imajinasi tentang suatu hasil survey kegiatan ekspor beberapa komoditi yang dilakukan pada beberapa perusahan eksportir. Hasil survei tidak terfokus pada kegiatan setiap perusahaan eksportir yang disurvei, tetapi fokus pada nilai ekspor per komoditinya. Hasil survei disusun menjadi tabel-tabel yang berkaidah database, yang terdiri dari tabel-tabel :- PRODUK
- Tabel ini berisi referensi tentang komoditi yang disurvei
- NEGARA
- Tabel ini berisi referensi tentang negara tujuan ekspor
- TRN
- Tabel ini berisi total nilai ekspor setiap bulan per komoditas per negara tujuan ekspor dalam valuta USD
Tabel-tabel diatas memiliki kaidah database dengan ciri khas sebagai berikut : (perhatikan tabel TRN)
- Header
- diletakkan pada baris 1 dan hanya terdiri dari 1 baris saja
- setiap kolom Excel berisi sebuah header, jadi kolom A milik id_produk dan id_produk pasti hanya di kolom A Excel, begitu juga dengan kolom lainnya
- ditata rapat (dari kiri ke kanan mulai A1), sehingga tidak ada kolom kosong tanpa teks header
- setiap cell header berisi data tertentu dan tidak ada dua atau lebih kolom berisi suatu data tanpa header
- tidak ada header yang di-merge cell, baik merge cell baris maupun merge cell kolom
- setiap header menjadi nama setiap kolom data
- Kolom
- setiap kolom data memiliki nama kolom
- setiap kolom berisi data dengan tipe data yang sama
- tidak ada pengaturan alignment pada kolom, agar tampak jelas kesamaan tipe data dalam kolom tersebut
- Records
- record pertama diletakkan tepat pada baris 2
- tidak ada baris kosong diantara dua record, jadi minimal ada satu field dalam record yang memiliki nilai
- semua field terisi penuh jika seharusnya memiliki suatu nilai tertentu, meskipun nilainya sama dengan record sebelumnya (lihat id_produk bernilai 1 tertulis disemua record dan tidak hanya di record pertama saja)
Tabel-tabel di atas sering disebut sebagai raw data. Susunan tabel yang sederhana seperti di atas akan sangat fleksible dalam menerima perkembangan kebutuhan output. Hal ini akan dapat dilihat pada coretan-coretan pivot table selanjutnya, sehingga dapat dirasakan dan dicoba sendiri betapa susunan sederhana yang tidak indah seperti tabel di atas jauh lebih indah diolah daripada tabel-tabel yang disusun dan disimpan dengan format selera mata manusia.
- Pemberitahuan !!!
- Untuk bahan belajar, maka tabel-tabel diatas akan dijaga keasliannya. Setiap bahasan akan dilakukan penyalinan data ke sebuah sheet baru, sehingga akan tampak perbedaan dari satu bahasan ke bahasan lain. Pada dunia nyata, bisa jadi seluruhnya langsung dikerjakan pada tabel-tabel di atas.
Sumber data untuk pivot table (Pivot Table Data Source)
Pada bagian ini, akan dimulai tahapan untuk membuat sebuah pivot table. Seperti bahasan di atas tadi, pivot table tergantung pada susunan tabel fakta yang menjadi sumber data (pivot table data source). Maka langkah pertama dalam membuat pivot table adalah menyiapkan pivot table data source yang berupa tabel fakta.Sebuah tabel database hanya menyimpan item-item pokok setiap tabel saja. Seluruh detil tentang setiap item disimpan dalam tabel yang lainnya, sehingga terbentuk suatu jaringan relasi antar tabel. Contohnya seperti tabel TRN di atas. Tabel TRN menyimpan id_produk tetapi tidak menyimpan nama produk dan segala yang bersifat detil tentang si produk. Seluruh detil produk disimpan dalam tabel bernama PRODUK. Begitu juga dengan kolom negara dalam tabel TRN.
Tabel fakta (fact table) adalah kebalikan dari tabel database. Dalam tabel fakta disimpan seluruh detil item data. Namun, untuk tahap pertama belajar pivot tabel ini, akan digunakan tabel TRN apa adanya sebagai bahan membentuk tabel fakta. Pada penggunaan pivot table di Excel yang bukan bersumber dari sebuah paket data (cube) akan membutuhkan tabel fakta yang detil dan sederhana sebagai pivot table data source.
Berikut langkah-langkah untuk membuat sumber data pivot table :
- Membuat sheet wadah sumber data pivot table
- Sumber data pivot table akan diletakkan pada sebuah sheet baru. Sheet baru ini diberi nama '1_dtsTRN'.
- Menyalin data dari tabel TRN ke sheet 1_dtsTRN
- blok seluruh data di sheet TRN (termasuk header) -> lakukan copy data (CTRL C)
- ke sheet 1_dtsTRN cell A1 -> Paste data (CTRL V)
- Memberi nama range pada tabel yang ada di sheet 1_dtsTRN
- Excel 2007 ke atas
- blok seluruh data (termasuk header) -> klik kanan -> Name a range -> isi Name dengan _1dtsTRN_ -> tekan OK
- Excel 2003
- blok seluruh data (termasuk header) -> menu Insert -> Define -> isi Name dengan _1dtsTRN_ -> tekan OK
- Excel 2007 ke atas
Membuat pivot table
Setelah tabel fakta disiapkan pada tahap sebelumnya, maka pada tahap ini akan mulai dibuat sebuah pivot table. Hal yang perlu diingat adalag nama range _1dtsTRN_ sebagai sumber data pivot table (Pivot Table Data Source). Berikut ini langkah-langkahnya : - Insert sebuah sheet dan beri nama dengan 1_pvtTRN
- Klik sebuah cell pada sheet 1_pvtTRN (misal di E9)
- Insert Pivot Table dengan cara
- Excel 2007
- ribbon Insert -> group Tables -> Pivot Table -> Pivot Table
- Mendefinisikan data source dan lokasi peletakan pivot table
- Pada dialog window yang muncul :
- Pilih Select a table or range, dengan Table/Range diisi nama range yang telah disiapkan untuk menjadi pivot table data source, yaitu _1dtsTRN_
- Pilih Existing Worksheet, dengan Location diisi merujuk cell E9 pada sheet 1_pvtTRN
- Tekan OK, dan dalam worksheet akan tampak seperti gambar berikut :
- ribbon Insert -> group Tables -> Pivot Table -> Pivot Table
- Excel 2003
- menu Data -> Pivot table and Pivot chart report
- Pada dialog pivot table wizard step 1 :
- Where is the data that you want to analyze : pilih Microsoft Excel list or database
- What kind of report that you want to create : pilih Pivot table
- tekan Next untuk ke step 2
- Pada dialog pivot table wizard step 2 :
- Range : diisi dengan nama range yang sudah disiapkan untuk menjadi data source, yaitu _1dtsTRN_
- tekan Next untuk ke step 3
- Pada dialog pivot table wizard step 3 :
- Where do you want to put the Pivot Table : pilih Existing Worksheet
- pada range selector, tunjuk ke cell E9 di sheet 1_pvtTRN
- tekan Finish (abaikan lebih dulu penataan layout), dan dalam worksheet akan tampak seperti gambar berikut :
- Excel 2007
- Pemberitahuan !!!
- Untuk bahan belajar, maka pada pengguna Excel 2007 ke atas diatur bentuk report layout-nya berupa Tabular dengan cara :
- ribbon PivotTable Tools -> menu Design -> group Layout -> Report Layout -> pilih Show in Tabular Form
Menata pivot table
Pivot table yang terbentuk menyimpan data (yang diambil dari data source) di dalam sebuah wadah bernama Pivot Cache. Sebuah pivot cache bisa menjadi banyak Pivot table. Yang tampak dalam worksheet saat ini adalah sebuah pivot table dari pivot cache yang berisi data dari _1dtsTRN_.Data dalam pivot cache akan diperbarui (mengambil ulang data dari data source pivot table) ketika dilakukan refresh table. Cara me-refresh table adalah dengan salah satu cara berikut ini :
- klik kanan Pivot table -> pilih Refresh
- Excel 2007 ke atas : klik sebuah cell dalam pivot table -> ribbon Data -> group Connections -> panah kecil di Refresh All -> pilih Refresh
- Excel 2003 : klik sebuah cell dalam pivot table -> menu Data -> Refresh Data
Jumlah pivot cache yang dibutuhkan adalah sebanyak jumlah tabel data source dan bukan sebanyak dataset output yang ingin dibuat. Pivot cache yang bertugas menyimpan pivot table data source memiliki karakteristik bawaan (default) untuk menyimpan data terus menerus walau workbook dalam keadaan tertutup. Hal ini akan membuat file Excel menjadi lebih besar. Pivot cache juga bisa diatur agar tidak menyimpan data terus menerus, sehingga file Excel tidak akan bertambah besar kecuali sebanyak data yang ditampilkan oleh pivot table. Langkah pengaturan ini dilakukan melalui sisi Options milik pivot table.
Penataan pertama kali adalah menata sisi options milik pivot table. Cara untuk menampilkan dialog Options milik pivot table :
- klik kanan sebuah cell dalam pivot table -> pilih PivotTable Options -> akan muncul dialog window seperti gambar berikut
- Nama pivot table
- Pada Name : diisi nama pivot table, untuk kali ini akan diberi nama pvtTRN01
- Tab Layout & Format
- Hilangkan centang dari opsi : Autofit column width on update
- agar lebar kolom tidak berubah-ubah
- Hilangkan centang dari opsi : Autofit column width on update
- Tab Data
- Dua opsi pertama berikut ini sebaiknya tidak diubah jika pivot table data source kemungkinan besar tidak dimiliki oleh user lain saat file Excel di transfer ke komputer lain.
- Hilangkan centang dari opsi : Save source data with file
- agar data dalam pivot cache dibuang ketika workbook ditutup, sehingga data tidak disimpan terus-menerus dan file Excel bisa lebih terkontrol ukurannya
- Centang opsi : Refresh data when opening the file
- agar pivot cahce diperbarui isinya
- Number of items to retain per field : dipilih None
- agar pivot table hanya berisi item-item yang ada dalam tabel data source saat di-refresh terakhir
- Hilangkan centang dari opsi : Save source data with file
- Melakukan summary nilai ekspor
- Pada daftar field, klik terus menerus nama field nilai_usd dan tarik (drag) ke area nilai ukur (measures)
Sampai disini telah terbentuk summary nilai total ekspor dari seluruh komoditi ke seluruh negara pada rentang waktu data yang ada.
- Melakukan summary nilai ekspor per produk yang diwakili oleh field id_produk
- Pada daftar field, klik terus menerus nama field id_produk dan tarik (drag) ke area dimensi baris
Sampai disini telah terbentuk summary nilai total ekspor per produk ke seluruh negara pada rentang waktu data yang ada. Jika ingin per Negara atau per periode, maka drag kembali id_produk ke daftar field dan lakukan drag pada nama field yang dibutuhkan. Letakkan di area dimensi baris atau di dimensi kolom.
- Melakukan summary nilai ekspor per produk per negara tujuan ekspor
- Pada daftar field, klik terus menerus nama field negara dan tarik (drag) ke area dimensi baris
Sampai disini telah terbentuk summary nilai total ekspor per produk per negara tujuan ekspor pada seluruh rentang waktu data yang ada.
- Melihat sebaran ekspor per produk per negara disetiap periode data
- Pada daftar field, klik terus menerus nama field periode dan tarik (drag) ke area dimensi kolom
Sampai disini telah terbentuk summary nilai total ekspor per produk per negara tujuan ekspor yang tersebar setiap periode data. Hasil penataan di atas akan seperti gambar di bawah ini.
Closing :
Dari proses penataan diatas, bisa terlihat jelas bahwa peletakan field pada dimensi baris berlaku sebagai pembuat grup summary. Ketika field diletakkan pada dimensi kolom, maka cenderung bisa disebut sebagai sebaran data pada item tertentu. Dimensi yang berisi lebih dari satu field, baik baris atau kolom, akan membuat summary menjadi lebih spesifik. Artinya, pivot table melakukan summary dari sisi terluar (grand total) dan semakin spesifik per item grup atau per item sebaran ketika semakin banyak jumlah field dalam satu area dimensi.Field yang di-summary adalah semua field yang diletakkan pada area nilai ukur (measures) yang akan selalu ditampilkan sebagai suatu agregat data yang bertipe numerik. Area nilai ukur (measures) dalam pivot table Excel disebut Values (Data Values) bisa diisi lebih dari satu field nilai ukur. Jika yang diletakkan pada area values ini adalah field yang bertipe data bukan numerik, maka agregat yang digunakan adalah Count yang berarti jumlah record.
Satu area lagi yang ada dalam pivot table adalah area Page Filter atau Report Filter. Area ini umumnya digunakan sebagai area filter dataset. Dalam sebuah tabel fakta yang menjadi pivot table data source, bisa jadi ada lebih dari satu dataset. Area report filter menjadi lokasi peletakan field yang membedakan antar dataset tersebut. Misal, contoh data di atas adalah data hasil survei. Jika ada dataset lain seperti data milik BPS yang disatukan dalam tabel data source _1dtsTRN_ dengan nama field Sumber_Data yang berisi teks BPS (untuk data-data ekspor yang berasal dari BPS) dan teks SURVEI (untuk data-data ekspor hasil survei), maka field Sumber_Data diletakkan pada area report filter. Hal ini tentulah tidak harus saklek demikian. Bisa jadi field id_output diletakkan pada area report filter, yang artinya, setiap item dalam id_produk merupakan sebuah dataset terpisah-pisah ketika berada diluar tabel data source untuk pivot bernama _1dtsTRN_.
mohon informasi kalau mau donlod pembahsan ini dimana kami bisa lakukan suhu ?
BalasHapusUntuk coretannya hanya bisa save page as. Untuk file contoh dan bahan latihan dapat diunduh dengan meng-klik daftar link yang ada pada bagian File(s)
Hapusmr kid saya ada data mentah di satu sheet, dan mau di buat pivot di sheet berikutnya.
BalasHapuskok selalu keluar "pivot table field name not valid", ini kenapa ya???
thx
Coba diperiksa ulang tabel datanya dari hal berikut :
Hapus1. pastikan tidak ada cell di-merge
2. pastikan nama kolom ada disetiap kolom
3. pastikan nama kolom hanya mengguni sebuah cell (1 baris x 1 kolom) saja untuk setiap kolomnya.
4. [optional] pastikan nama kolomnya unique agar nama field yang ditampilkan di pivot table seperti apa yang ada di tabel sumber data, sehingga tidak perlu mengingat-ingat perbedaan nama kolom antara yang di pivot dengan tabel sumbernya.
;)
Dear Mr.Kid,
BalasHapussaya mau bertanya, bagaimana menjadi kan Pivot beda folder excel menjadi 1 sheet excel. contohnya omset 1 tahun jan-jun book1, jul-des book2, bagaimana menjadikan pivot jan-des di book3 ? saya tunggu feedbacknya..
thanks and regards
-A.O-
mbak Arista,
HapusPivot table bekerja dengan sumber data bisa berupa :
1. sebuah tabel
2. banyak tabel dalam sebuah sheet, tetapi sebagai consolidation. Pivot table jenis ini tidak akan seluwes pivot table yang bersumber dari sebuah tabel
Jika mempunyai data dibanyak sheet, apalagi difile yang berbeda, maka diperlukan usaha untuk menyatukannya menjadi sebuah tabel. Untuk kebutuhan ini, Excel menyediakan fitur bernama Get External Data. Bahasa pengolahan data yang digunakan oleh fitur ini adalah SQL. Setelah menjadi sebuah tabel, hasil query bisa ditampilkan sebagai suatu pivot table yang bersumber dari sebuah tabel.
Fitur Get External Data bisa digunakan untuk mengambil data dari berbagai lokasi termasuk dari berbagai database engine seperti MS SQL Server, Oracle, MySQL, MS Access, dsb. Jadi pivot table yang dihasilkan juga bisa semakin padat informasi.
Pembahasan tentang fitur Get External Data bisa dilihat dicoretan yang dimulai dari link berikut ini :
http://excel-mr-kid.blogspot.com.au/2011/04/get-external-data-01-introduction.html
Yuk mencoba fitur Get External Data yuk... dunia baru pengolahan data yang berbasis database akan terbuka dan akan lebih banyak ide-ide yang muncul untuk memudahkan pengolahan data yang mbak gunakan saat ini.
;)
met mencoba n tetap semangat belajar
Wassalam,
Kid.
thanks buat infonya sob,, sangat bermanfaat sekali
BalasHapusseringkali pivot excel tsb menjadi excel biasa..bagaimana mengembalikannya
BalasHapusBisa dijelaskan secara detil kronologi pembuatan pivot table sampai hilangnya pivot table dimaksud ?
Hapus