Pivot Table (2 - Menata Data Source)

---

Muatan :

• Kilasan tentang Pivot Table Data Source
• Contoh data untuk penataan data source
• Membuat nama range dinamis untuk data source
• Mendetilkan data source

---

Kilasan

Pada pembahasan lalu, yaitu Pivot Table (1 - Kenalan) sudah dibahas tentang cara membuat pivot table beserta yang dimulai dari mendefinisikan data source untuk pivot table. Informasi yang dapat diambil dari suatu data menggunakan pivot table sangat tergantung pada data source pivot table. Bentuk data source yang sederhana dan berkaidah database adalah bentuk yang relatif sangat sesuai untuk dijadikan data source, meskipun pada hakikatnya data source pivot table adalah sebuah tabel fakta.

---

Contoh data untuk penataan data source

Pembahasan kali ini akan difokuskan pada penataan data source agar bisa memberikan informasi lebih banyak. File untuk pembahasan kali ini bisa diunduh melalui link pada bagian File(s) dibawah sana.

Pembahasan lalu telah sampai pada terbentuknya sebuah data source pada sebuah sheet. Area data juga diberi nama range agar relatif mudah untuk dikelola. Pivot table dibentuk dari area data tersebut dan ditampilkan pada sebuah sheet baru. Data source yang masih berisi data apa adanya dari tabel data TRN tidak memberi informasi yang jelas karena berupa kode-kode data.

Kali ini, sheet data source diletakkan pada sebuah sheet bernama 2_dtsTRN dengan area data yang berbentuk tabel sederhana diberi nama _2dtsTRN_ yang merujuk pada suatu range dengan bunyi formula rujukan :

='2_dtsTRN'!$A$1:$D$840

Pivot table diletakkan pada sebuah sheet bernama 2_pvtTRN dan akan tampak seperti gambar dibawah ini.

Pada gambar tampak jelas bahwa data source yang apa adanya (2_dtsTRN) tidak akan memberi informasi yang jelas dalam pivot table (2_pvtTRN). Bagaimana orang lain akan bisa memahami arti kode-kode dalam field id_produk jika tidak ada penjelasan lebih rinci arti setiap kodenya.

---

Membuat nama range dinamis untuk data source

Tabel yang ada di sheet 2_dtsTRN diberi nama range _2dtsTRN_ dengan formula rujukan :

='2_dtsTRN'!$A$1:$D$840

yang tampak seperti gambar di bawah ini.

Tampak garis putus-putus yang menjadi pembatas suatu area pada gambar bahwa nama range tersebut melingkupi seluruh tabel mulai dari header pada kolom pertama hingga header terkanan, kemudian kebawah sampai di record terakhir.

Pada proses penataan data source tentu saja akan membuat tabel data semakin lebar atau mengalami pertambahan kolom. Dari waktu ke waktu, bisa jadi jumlah record pun akan bertambah. Berubah-ubahnya jumlah record adalah satu keniscayaan dalam pengolahan data. Dengan alasan seperti ini, maka dibutuhkan suatu nama range yang bersifat dinamis dalam melingkupi seluruh tabel data, mulai dari header sampai seluruh record data, apapun yang terjadi terhadap header dan recordnya.

Berikut ini adalah pokok-pokok yang berkaitan tentang pendinamisan nama range.

• Pendinamisan nama range selalu dilakukan dengan mengubah formula rujukan (bagian refers to yang ada dalam gambar). Rujukan tersebut umumnya dibentuk dengan formula-formula yang menghasilkan suatu referensi range menggunalan fungsi-fungsi seperti Index, Offset, atau Indirect. Pada bahasan kali ini dipilih penggunaan fungsi Index yang tidak bersifat volatile.
• Secara sekilas, telah dijelaskan bahwa perubahan tabel bisa dalam arah :
  
  • horisontal (penambahan atau pengurangan jumlah kolom)
  • vertikal (penambahan atau pengurangan jumlah record
  Artinya, akan ada 2 (dua) sisi yang didinamiskan, yaitu rujukan kearah horisontal dan kearah vertikal. Sebuah tabel juga memiliki 2 (dua) dimensi, yaitu dimensi baris (jumlah baris=jumlah record + 1 baris header) dan dimensi kolom, sehingga bisa dinotasikan tabel R baris x C kolom.
  
  1. MENENTUKAN DIMENSI BARIS
     • Jumlah baris ditentukan berdasar kolom yang selalu terisi penuh.
     • Kolom seperti ini sering disebut kolom utama atau kolom kunci (key).
     • Pada gambar di atas, tampak bahwa kolom id_produk selalu terisi penuh.
     • Maka diputuskan kolom id_produk yang ada di kolom A adalah kolom penentu jumlah baris.
     • Karena terisi penuh, maka jumlah baris tabel (records + header) adalah sebanyak cell di kolom A yang berisi data.
     • Fungsi CountA berguna untuk menghitung jumlah cell yang berisi data.
     • Maka digunakanlah fungsi CountA untuk menentukan dimensi baris dengan bentuk formula :
       =CountA( '2_dtsTRN'!$A:$A )
  2. MENENTUKAN DIMENSI KOLOM
     • Jumlah kolom ditentukan berdasar baris header, karena baris header pasti terisi penuh dan rapat.
     • Baris header selalu berupa 1 (satu baris) tanpa merge cell.
     • Maka dimensi tabel sisi kolom ditentukan dengan menghitung jumlah cell yang terisi pada baris 1 (satu).
     • Fungsi CountA berguna untuk menghitung jumlah cell yang berisi data.
     • Maka digunakanlah fungsi CountA untuk menentukan dimensi kolom dengan bentuk formula :
       =CountA( '2_dtsTRN'!$1:$1 )
• Penyusunan formula untuk bagian refers to dalam pembuatan nama range dinamis akan menggunakan fungsi Index yang bisa menghasilkan suatu referensi cell ketika diberi inputan referensi range. Penggunaan fungsi Index untuk kebutuhan ini adalah dengan susunan :
  INDEX( referensi_range , indeks_baris [, indeks_kolom] )
  Pada kondisi referensi_range berupa area range 1 (satu) baris saja atau 1 (satu) kolom saja, maka susunan fungsi Index bisa berupa :
  INDEX( referensi_range , indeks_data )
  dengan indeks_data berisi angka posisi range dalam referensi_range yang akan diambil atau digunakan.
  
  Jadi, pada suatu formula berbunyi :
  INDEX( $A$1 , 1 )
  akan menghasilkan range A1 dan bukan nilai A1. Misalkan A1 berisi nilai data tertentu (misal tanggal hari ini), hasil formula di atas adalah range A1. Ketika range A1 menjadi hasil dan harus ditampilkan ke sebuah cell (cell berisi formula di atas), maka secara default yang ditampilkan pada cell berisi formula tersebut adalah nilainya, yaitu tanggal hari ini. Hal ini bisa dibuktikan dengan bantuan fungsi CountIF yang membutuhkan inputan berupa referensi range dalam formula berikut :
  CountIF( INDEX( $A$1 , 1 ) , ">0" )
  • Jika hasil bagian INDEX adalah nilai di A1, maka CountIF tidak akan bersedia menerimanya dan formula tersebut tidak akan dapat di-entri masuk ke cell hasil (tidak dapat ditulis di cell D4 misalnya).
  • Jika hasil bagian INDEX adalah range A1, maka CountIF bersedia menerimanya dan formula tersebut dapat di-entri masuk ke cell hasil.
• Nama range _2dtsTRN_ yang dirujuk dengan formula ='2_dtsTRN'!$A$1:$D$840 akan membentuk suatu area range dari range $A$1 sampai dengan (karakter titik dua) range $D$840. Bagian dari range tertentu dan sampai dengan range tertentu itulah yang akan menggunakan fungsi Index, sehingga dapat berbunyi dari range INDEX( blabla ) sampai dengan (karakter titik dua) range INDEX( blibli ), yang akan tampak sebagai :
  INDEX( blabla ):INDEX( blibli )
• Pada rujukan ='2_dtsTRN'!$A$1:$D$840 menunjukkan bahwa pembentukan area range dimulai dari pojok kiri atas (bagian $A$1) sampai dengan pojok kanan bawah (bagian $D$840), sehingga membentuk suatu segi empat. Maka bisa terlihat bahwa :
  • Pojok kiri atas adalah tetap karena dijepit oleh batas kiri yang tidak bisa berubah lagi karena di kolom pertama dan batas atas yang juga tidak bisa berubah lagi karena di baris pertama.
  • Pojok kanan bawah menjadi suatu titik yang sangat labil bergerak, yaitu bisa bergerak ke bawah karena bertambahnya record dan bisa bergerak ke kanan karena bertambahnya kolom.
  Tentu saja akan sangat mudah untuk menentukan pojok kiri atas, tetapi sangat sulit untuk menentukan pojok kanan bawah. Oleh sebab itu, dengan dasar pemikiran bahwa :
  • Suatu area segi empat memiliki 4 (empat) pojok
  • Pojok yang relatif tetap adalah pojok yang dekat dengan batas kiri atau batas atas
  maka penentuan area range akan lebih mudah jika dimulai dari pojok kanan atas sampai dengan pojok kiri bawah, dimana :
  • Pojok kanan atas adalah pada baris ke-1 di kolom yang berubah-ubah
  • Pojok kiri bawah adalah pada baris yang berubah-ubah di kolom ke-1
  Untuk rujukan berbunyi ='2_dtsTRN'!$A$1:$D$840 akan menghasilkan area range yang sama dengan rujukan berbunyi ='2_dtsTRN'!$A$840:$D$1 atau ='2_dtsTRN'!$D$1:$A$840

Langkah-langkah pendinamisan nama range _2dtsTRN_ yang akan diubah agar merujuk dari pojok kanan atas sampai dengan pojok kiri bawah atau dalam bentuk rujukan ='2_dtsTRN'!$A$840:$D$1 adalah sebagai berikut :

1. Bagian '2_dtsTRN'!$A$840 dapat diganti dengan formula Index :
   INDEX('2_dtsTRN'!$A:$A,CountA('2_dtsTRN'!$A:$A))
   karena merujuk record terakhir di kolom pertama (kolom A), dimana record terakhir bisa diketahui nomor barisnya dengan fungsi CountA seperti yang telah dijelaskan di atas.
2. Bagian $D$1 dapat diganti dengan formula Index :
   INDEX('2_dtsTRN'!$1:$1,CountA('2_dtsTRN'!$1:$1))
   karena merujuk kolom terakhir di baris header (baris pertama), dimana kolom terakhir bisa diketahui nomor kolomnya dengan fungsi CountA seperti yang telah dijelaskan di atas.
3. Kedua bagian tersebut digabungkan menjadi satu agar membentuk rujukan
   =range_pojok_kiri_bawah:range_pojok_kanan_atas
   sehingga akan berbunyi :
   =INDEX('2_dtsTRN'!$A:$A,CountA('2_dtsTRN'!$A:$A)):INDEX('2_dtsTRN'!$1:$1,CountA('2_dtsTRN'!$1:$1))
4. Kemudian, formula tersebut ditulis pada bagian refers to saat edit nama range seperti gambar dibawah dan diakhiri dengan menekan tombol OK.
   
5. Pivot table di sheet 2_pvtTRN di-refresh.

Sampai disini, pivot table telah memiliki data source yang bersifat dinamis. Pengubahan data dalam tabel fakta yang menjadi data source pivot table akan tampak dalam pivot table setelah melakukan refresh pada pivot table-nya.

---

Mendetilkan data source

Setelah pivot table merujuk ke suatu nama range yang dirujuk dengan formula dinamis, maka penambahan atau pengurangan data maupun kolom pada tabel fakta akan diikuti dengan berubahnya area range yang menjadi pivot tabke data source. Pada proses penyusunan data source yang detil, dibutuhkan penambahan kolom. Kolom yang ditambahkan selalu diletakkan pada sisi kanan tabel yang ada.

Untuk saat ini, data id_produk akan didetilkan agar pivot table bisa menampilkan nama-nama produk yang ditunjuk oleh id_produk. Artinya, ada sebuah tabel referensi yang berisi data-data id_produk beserta nama produknya. Tabel tersebut dibuat pada sebuah sheet bernama PRODUK seperti gambar dibawah ini.

Area range A1:B7 diberi nama range _refPRODUK_ dan akan digunakan dalam proses pendetilan data source selanjutnya.

Kembali ke sheet data source bernama 2_dtsTRN. Pada kolom kosong pertama di kanan tabel diberi header PRODUK (pada baris 1 di kolom E atau di range E1). Kemudian pada posisi record pertama (baris 2) di kolom PRODUK ini diberi formula lookup berdasar id_produk untuk mengambil data nama produk di kolom produk diarea range bernama _refPRODUK_. Kolom produk berada di kolom ke-2. Sifat data referensi adalah unique berdasar id_produk.

Jadi, pada sheet 2_dtsTRN di range E2 diberi formula :

=VLookUp( A2 , _refPRODUK_ , 2 , 0 )

kemudian formula di-copy ke seluruh record menjadi seperti gambar dibawah ini.

Setiap kali terjadi perubahan pada sisi tabel fakta, maka pivot table harus di-refresh agar bisa mendapatkan data terkini yang ada dalam data source. Setelah di-refresh, maka akan tampak pada daftar field (field list) sebuah field baru bernama produk seperti gambar dibawah ini.

Field baru bernama produk tersebut bisa ditambahkan ke dalam pivot table. Field produk yang menjadi keterangan detil dari id_produk diletakkan bersama id_produk dengan drag nama field produk ke bagian Row Labels setelah (dibawah) field id_produk. Hasilnya akan seperti gambar dibawah ini.

---

Closing :

Nama range yang dinamis, yang dijadikan sebagai sumber data source bagi pivot table akan memudahkan dalam mengelola tabel fakta. Proses pendetilan data source bisa diotomasi dengan VBA agar tabel fakta tidak berisi formula.

Penyusunan data source seperti di atas digunakan pada Excel Pivot Table dengan data yang bukan berasal dari suatu server analysis. Khusus untuk data-data yang berasal dari server analysis yang umumnya berbentuk sebuah cube dan diakses sebagai OLAP (Online Analytical Processing), maka data source telah berupa tabel fakta yang disertai dengan tabel-tabel referensi yang berisi dimensi-dimensi data beserta hirarki drilldown-nya.

Akses data sebagai OLAP diperoleh ketika pivot table mendapatkan data source dari server analysis. Mulai Excel 2010 telah ada Addin bernama PowerPivot yang pada dasarnya adalah membuatkan server analysis virtual dan menyusunkan sebuah cube dari berbagai sumber data, yang kemudian ditampilkan dalam pivot table dengan akses sebagai OLAP.

Prinsip dasar penggunaan Pivot Table (baik Excel Pivot Table maupun Power Pivot) adalah seperti yang telah dijabarkan di atas, dimana susunan data source menjadi titik awal yang sangat penting untuk bisa mendapatkan data analisis multi dimensi yang baik.

---

File(s) :

Pivot table 2 - Menata Data Source pada sheet 2_dtsTRN dan 2_pvtTRN

---

Coretan terkait :

• Pivot Table (1 - Kenalan)
• Pivot Table (3 - Pivot field)
• Susun data sebagai tabel yang baik. Kenapa ?

---