Trik Menggunakan Pivotabhle dan Pivochart

Trik Menggunakan PivotTable, Dan PivotChart

 

Langkah-langkah membuat Pivot Table (Analisis 1: Menghitung jumlah)

1.Klik salah satu sel dari tabel (misalkan A3 dari range A2:D15)

2.Klik ribbon Insert pilih PivotTable

3.Sehingga terbuka jendela Create PivotTable

Pada opsi Choose the data that you want to analyze.

1. Pilih Select Table Range, kemudian akan dipilih secara otomatis oleh Microsoft Excel range yang sesuai. Opsional: Anda juga dapat memilih secara manual dengan drag langsung atau memasukkan alamat range secara manual pada kolom ini.

2. Opsional: anda juga dapat memilih range diluar sheet dengan memilih “Use an external data source” (penggunaan tingkat lanjut).

Pada opsi Choose where you want the PivotTable report to be placed, digunakan untuk menentukan lokasi Pivot Table yang dibuat. Terdapat 2 opsi,

 

1. New Worksheet – Microsoft Excel akan membuat sheet baru untuk Pivot Table.

2. Existing Worksheet – Menempatkan Pivot Table pada sheet yang aktif. Untuk mempermudah, akan digunakan Existing Worksheet. Anda tinggal memasukkan alamat sel kosong untuk penempatan Pivot Table misalkan E1.

Opsional: Untuk penggunaan tingkat lanjut yang bekerja dengan banyak tabel. Anda dapat mencentang “Add this Data to the Data Model“

Kemudian Klik OK

Anda dapat melakukan centang pada PivotTable Fields untuk analisis mencari jumlah pesanan seperti ilustrasi di bawah. Tabel Pivot yang dibentuk, berdasarkan urutan anda mencentang,

 1. Data teks diolah dalam baris bertingkat.

 2. Data numerik diolah dengan menambah kolom.

Sehingga untuk mencari jumlah pesanan tiap produk dapat dengan mencentang (produk)#1 kemudian (jumlah)#2

 

·    

Excel memiliki suatu fitur yang memungkinkan anda lebih cepat dan memudahkan untuk membuat suatu laporan data yakni menggunakan PivotTable, PivotChart, dan Slicer. Dimana PivotTable merupakan tabel interaktif dan atraktif yang dapat menganalisis perbandingan, pola maupun tren data secara ringkas. Sedangkan PivotChart adalah gambar grafik yang dihasilkan dari PivotTable dan Slicer yang dapat melakukan filter data secara praktis.

                                  
Format laporan PivotTable dirancang untuk menyajikan laporan dengan fleksibel menurut baris maupun kolom dari suatu barisan data. Dimana baris data dapat dipindah menjadi kolom, atau kolom data dapat dipindah pula menjadi baris untuk mengamati perbedaan laporan yang dihasilkan terhadap sumber data.

Gambar di atas merupakan hasil akhir suatu contoh worksheet dengan output PivotTable, PivotChart, dan Slicer yang mengacu pada kolom A dan B sebagai sumber datanya. Salah satu manfaat PivotTable yaitu dapat menampilkan dan menyembunyikan nilai berdasarkan kategorinya masing-masing.

Seperti pada contoh di bawah ini jumlah penjualan tiap bulan akan disembunyikan sehingga yang akan ditampilkan yakni nilai subtotal tiap-tiap kuartalnya yang dapat dilakukan dengan cara memilih menu 'Expand Entire Field' untuk menampilkan subkategori/Kuartal tersebut atau 'Collapse Entire Field' untuk menyembunyikannya. Menu tersebut dapat diakses melalui ribbon tab PivotTable Tools > Options > Active Field. Setiap perubahan yang ditetapkan pada PivotTable tentunya berpengaruh pada Grafik PivotChart (dalam contoh grafik batang) yang akan menyesuaikan outputnya dengan otomatis/interaktif.

Tips berikut ini akan menjelaskan proses pembuatan ketiga output  laporan tersebut menurut sumber data seperti dalam contoh yakni barisan sel A3:B613. Adapun database PivotTable dapat ditentukan dari suatu barisan sel atau tabel di dalam Excel atau dapat pula menggunakan sumber data eksternal dengan jenis file berekstensi tertentu yang didukung oleh Excel.

Sumber data yang digunakan dalam contoh terdiri dari 2 kolom yakni Tanggal dengan format Number Custom: [$-421]dddd, d mmmm yyyy;@ dan Qty yang berisi banyaknya penjualan suatu produk. Laporan PivotTable yang akan dihasilkan adalah jumlah penjualan produk berdasarkan bulan kuartal dan tahun.

Langkah Pertama: Membuat PivotTable

Sorot sumber data pada barisan sel A3:B613.

Buka ribbon Insert > Tables > PivotTable.

Pada kotak dialog Create PivotTable, pilih 'Existing Worksheet' di bawah petunjuk 'Choose where you want...' kemudian klik tombol di sudut kotak isian 'Location:' dan letakkan pointer pada sel D3 lalu tekan Enter. Klik OK.

Di samping kanan layar Excel terdapat bilah menu 'PivotTable Field List' lalu anda tandai centang pada 'Tanggal' di bawah petunjuk 'Choose fields to...' sehingga kolom tanggal ditampilkan pada kotak 'Row Labels'. Klik 'Qty' agar kolom tersebut ditampilkan dalam kotak 'Values'.

Ganti nama kolom 'Tanggal' di kotak 'Row Labels' menjadi 'Bulan' dengan cara klik icon segitiga di samping 'Tanggal' lalu pilih 'Field Settings...' dan ganti pula nama 'Sum of Qty' pada kotak 'Values' menjadi 'Jumlah Penjualan'.

Langkah Kedua: Menetapkan Output PivotTable

 

Sorot kolom 'Row Labels' dengan cara letakkan pointer pada sel D4 kemudian klik tepi bagian atas sel tersebut hingga kursor berubah menjadi panah menurun ke bawah lalu klik tepi sel tersebut. 

Buka ribbon tab PivotChart Tools > Options > Group > Group Selection.

Pada kotak dialog 'Grouping', hilangkan tanda centang pada 'Ending at' kemudian sorot/klik Months, Quarters, dan Years pada kotak pilihan 'By'. Klik OK.

Letakkan pointer pada sel D4 untuk menyoroti tahun lalu buka ribbon PivotChart Tools > Options > Active Field > Collapse Entire Field. Subkategori Qtr dan bulan untuk sementara dapat disembunyikan dulu.

Hilangkan 'Row Labels' dan tombol filter drop-down-nya dengan cara klik kanan sel D3 untuk menampilkan menu shortcut lalu pilih 'PivotTable Options...'.

Pada kotak dialognya, buka tab 'Display' lalu hilangkan tanda centang pada 'Display field captions and filter drop downs'. Klik OK.

Ganti 'Grand Total' pada sel D6 menjadi 'Total Penjualan' melalui Formula Bar. 

Sesuaikan lebar kolomnya dan tampilkan kembali kedua subkategori yang disembunyikan melalui ribbon PivotChart Tools > Options > Active Field > Expand Entire Field..

Langkah Ketiga: 

Menampilkan Subtotal, Menyesuaikan Format dan Desain PivotTable

Tempatkan pointer sel pada PivotTable.

Ganti 'Years' pada Row Labels menjadi 'Tahun' melalui 'Field Settings...' menu shortcut/klik kanan, kemudian pada tab 'Layout&Print' hilangkan tanda centang pada 'Display subtotals at the top...' dan tandai centang pada 'Insert blank line...' untuk memberikan baris baru di setiap label tahun. Klik OK.

Letakkan pointer pada sel D4 untuk menunjuk tahun kemudian klik kanan > Subtotal "Tahun" sehingga Subtotal dapat ditampilkan.

Ganti 'Quarters' pada Row Labels menjadi 'Kuartal' melalui 'Field Settings...' menu shortcut/klik kanan, kemudian pada tab 'Layout&Print' hilangkan tanda centang pada 'Display subtotals at the top...'. Klik OK.

Letakkan pointer pada sel D5 untuk menunjuk kuartal kemudian klik kanan > Subtotal "Kuartal".

Ubah format Number 'Jumlah Penjualan' pada kotak 'Values' dengan menggunakan Category: Currency, Decimal places: 0, dan Symbol: None lalu klik OK.

Untuk mengubah tampilan PivotTable, anda dapat membuka PivotChart Tools > Design > PivotTable Styles dan pilihlah tampilan desain yang anda kehendaki.

Langkah Keempat: Memperbaiki Output PivotTable

 

Letakkan pointer pada PivotTable.

Buka kotak dialog Find and Replace dengan cara menekan tombol CTRL+F pada keyboard, pilih tab Replace lalu ketik 'Qtr' pada kotak isian 'Find what:' dan ganti menjadi 'Kuartal ' pada 'Replace with:' klik tombol 'Replace All' dan klik OK.

Perbaiki nama bulan Mei, Agustus, Oktober hingga Desember.

Untuk mengubah '2015 Total' pada sel D25 menjadi 'Total 2015' lakukanlah melalui Formula bar, demikian pula 'Kuartal 1 Total' pada sel D9 menjadi 'Sub Total Kuartal 1', maka Excel secara otomatis akan mengganti tulisan lainnya.

Setelah itu, anda sesuaikan lebar kolom D.

 

Letakkan pointer pada PivotTable.

 

Perbaiki nama bulan Mei, Agustus, Oktober hingga Desember.

Untuk mengubah '2015 Total' pada sel D25 menjadi 'Total 2015' lakukanlah melalui Formula bar, demikian pula 'Kuartal 1 Total' pada sel D9 menjadi 'Sub Total Kuartal 1', maka Excel secara otomatis akan mengganti tulisan lainnya.

Setelah itu, anda sesuaikan lebar kolom D.

Langkah Kelima: Membuat PivotChart

1. Letakkan pointer sel pada PivotTable.

2. Buka ribbon tab PivotChart Tools > Options > Tools > PivotChart.

3. Pada kotak dialog Insert Chart pilih Column > Clustered Column klik OK.

4. Hilangkan legenda melalui ribbon tab PivotChart Tools > Layout > Labels > Legend > None. 

5. Hilangkan judul grafik melalui ribbon tab PivotChart Tools > Layout > Labels > Chart Title > None. 

6. Sesuaikan posisi dan ukuran grafik yang telah anda buat.

Selain itu Pivot Table menyediakan filter button untuk mempermudah mengurutkan data. 

Demikianlah penjelasan materi tentang trik untuk menggunakan Pivotable dan Pivotchart. Semoga artikel ini dapat membantu dan bermanfaat untuk pembaca artikel ini. Terimakasih. Jangan lupa kunjungi www.bunglonmerah.blogspot.com