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
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
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
- 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 )
- 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] )
- INDEX( referensi_range , indeks_data )
Jadi, pada suatu formula berbunyi :- INDEX( $A$1 , 1 )
- 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.
- Suatu area segi empat memiliki 4 (empat) pojok
- Pojok yang relatif tetap adalah pojok yang dekat dengan batas kiri atau batas atas
- 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
- Bagian '2_dtsTRN'!$A$840 dapat diganti dengan formula Index :
- INDEX('2_dtsTRN'!$A:$A,CountA('2_dtsTRN'!$A:$A))
- Bagian $D$1 dapat diganti dengan formula Index :
- INDEX('2_dtsTRN'!$1:$1,CountA('2_dtsTRN'!$1:$1))
- Kedua bagian tersebut digabungkan menjadi satu agar membentuk rujukan
- =range_pojok_kiri_bawah:range_pojok_kanan_atas
- =INDEX('2_dtsTRN'!$A:$A,CountA('2_dtsTRN'!$A:$A)):INDEX('2_dtsTRN'!$1:$1,CountA('2_dtsTRN'!$1:$1))
- Kemudian, formula tersebut ditulis pada bagian refers to saat edit nama range seperti gambar dibawah dan diakhiri dengan menekan tombol OK.
- Pivot table di sheet 2_pvtTRN di-refresh.
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 )
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) :
Coretan terkait :
- Pivot Table (1 - Kenalan)
- Pivot Table (3 - Pivot field)
- Susun data sebagai tabel yang baik. Kenapa ?
masih bingung pastinya.....
BalasHapusSebaiknya langsung dicoba pada file contoh yang disediakan pada bagian File(s)
BalasHapus:)
bahasanya terlalu baku mas..
BalasHapushehehe...
Hapusketahuan deh kalo bahan ajar disalin ke blog.
Tapi memang blog ini sengaja dibuat seperti itu
Komentar ini telah dihapus oleh pengarang.
BalasHapus