Rabu, 24 April 2013

Pivot Table (1 - Kenalan)


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 :
  1. Membuat sheet wadah sumber data pivot table
      Sumber data pivot table akan diletakkan pada sebuah sheet baru. Sheet baru ini diberi nama '1_dtsTRN'.
  2. 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)
  3. 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
Sampai disini telah terbentuk sebuah sheet data source bernama 1_dtsTRN yang berisi sebuah tabel data yang area range tabel tersebut diberi nama _1dtsTRN_. Nama range _1dtsTRN_ inilah yang menjadi data source untuk pivot table. Potongan tabel akan terlihat seperti gambar berikut ini :

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 :
  1. Insert sebuah sheet dan beri nama dengan 1_pvtTRN
  2. Klik sebuah cell pada sheet 1_pvtTRN (misal di E9)
  3. Insert Pivot Table dengan cara
    • Excel 2007
      1. ribbon Insert -> group Tables -> Pivot Table -> Pivot Table
      2. 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 :
    • Excel 2003
        1. menu Data -> Pivot table and Pivot chart report
        2. 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
        3. 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
        4. 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 :
Sebuah pivot table telah terbentuk di dalam worksheet. Perbedaan tampilan pivot table antara pada Excel 2007 keatas dengan Excel 2003 adalah dibentuk design report layout. Excel 2003 hanya menyediakan bentuk tabular. Pada Excel 2007 keatas disediakan report layout berupa Compact, Outline, dan Tabular. Pivot table pada Excel 2007 akan tampak persis seperti pada Excel 2003 jika dalam options pivot table display diaktifkan opsi Classic pivot table layout.
    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
      Saat ini tampilan dalam worksheet tidak akan tampak berubah daripada sebelum diubah report layout-nya menjadi Tabular.



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 :
  1. klik kanan Pivot table -> pilih Refresh
  2. Excel 2007 ke atas : klik sebuah cell dalam pivot table -> ribbon Data -> group Connections -> panah kecil di Refresh All -> pilih Refresh
  3. Excel 2003 : klik sebuah cell dalam pivot table -> menu Data -> Refresh Data
Jadi, setiap kali ada perubahan data pada data source, yaitu data di tabel yang berada dalam sheet 1_dtsTRN, maka perlu melakukan refresh table dengan salah satu cara di atas.

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
Berikut ini adalah hal-hal dalam options pivot table yang sering diatur agar relatif lebih nyaman untuk digunakan.
  1. Nama pivot table
      Pada Name : diisi nama pivot table, untuk kali ini akan diberi nama pvtTRN01
  2. Tab Layout & Format
    • Hilangkan centang dari opsi : Autofit column width on update
        agar lebar kolom tidak berubah-ubah
  3. 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
Menata field yang digunakan dalam sebuah summary report dapat dilakukan seperti cara berikut ini. (lihat gambar pivot table yang terbentuk pertama kali dalam worksheet)
  1. Melakukan summary nilai ekspor
    • Pada daftar field, klik terus menerus nama field nilai_usd dan tarik (drag) ke area nilai ukur (measures)
  2. Sampai disini telah terbentuk summary nilai total ekspor dari seluruh komoditi ke seluruh negara pada rentang waktu data yang ada.
  3. 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
  4. 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.
  5. 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
  6. Sampai disini telah terbentuk summary nilai total ekspor per produk per negara tujuan ekspor pada seluruh rentang waktu data yang ada.
  7. 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
  8. 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_.


File(s) :


Coretan terkait :


9 komentar:

  1. mohon informasi kalau mau donlod pembahsan ini dimana kami bisa lakukan suhu ?

    BalasHapus
    Balasan
    1. Untuk 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)

      Hapus
  2. mr kid saya ada data mentah di satu sheet, dan mau di buat pivot di sheet berikutnya.
    kok selalu keluar "pivot table field name not valid", ini kenapa ya???

    thx

    BalasHapus
    Balasan
    1. Coba diperiksa ulang tabel datanya dari hal berikut :
      1. 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.

      ;)

      Hapus
  3. Dear Mr.Kid,
    saya 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-

    BalasHapus
    Balasan
    1. mbak Arista,

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

      Hapus
  4. thanks buat infonya sob,, sangat bermanfaat sekali

    BalasHapus
  5. seringkali pivot excel tsb menjadi excel biasa..bagaimana mengembalikannya

    BalasHapus
    Balasan
    1. Bisa dijelaskan secara detil kronologi pembuatan pivot table sampai hilangnya pivot table dimaksud ?

      Hapus