Cara Menggunakan Pivot Table Dan Pivot Chart

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

1.    Pivot Tabel

Pivot Table dirancang untuk menyajikan laporan dengan fleksibel menurut baris maupun kolom dari suatu barisan data. Yang dimana baris data dapat dipindahkan menjadi kolom, atau kolom data dapat dipindahkan pula menjadi baris untuk mengamati perbedaan laporan yang dihasilkan terhadap sumber data. Sebelum membuat grafik tabel pivot atau pivot chart anda perlu membuat pivot tabel dari data yang akan dibuat grafik pivot.

Misalkan akan digunakan contoh pada tutorial sebelumnya untuk membuat grafik penjualan pabrik kertas dari tabel berikut:

	A	B	C	D
1	Data Pemesanan Produk 1 Februari
2	No.Nota	Produk	Bahan	Jumlah
3	A01	Buku 32 lbr	A4	1230
4	A01	Buku 64 lbr	A4	700
5	A02	Folio F4	F3	94000
6	A02	Buku 32 lbr	A4	7080
7	A03	Buku 64 lbr	A4	8000
8	A04	HVS A4	A4	1800
9	A04	Folio A4	A3	44400
10	A05	Buku 64 lbr	A4	9000
11	A06	Buku gambar A3	A3+	36400
12	A06	Buku 32 lbr	A4	1400
13	A07	Buku 32 lbr	A4	7000
14	A07	Folio F4	F3	6800
15	A07	Buku Gambar A3	A3+	1000

Akan dibuat grafik yang menunjukkan banyaknya jenis produk yang dipesan oleh 7 pemesan per 1 Februari 2018.

Berikut ini langkah-langkah dalam membuat Pivot Tabel:

a.    Membuat pivot tabel dari data

·      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'.

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

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

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

e.    Membuat PivotChart

·      Letakkan pointer sel pada PivotTable.

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

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

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

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

·      Sesuaikan posisi dan ukuran grafik yang telah anda buat.

2.    Pivot Chart

Chart atau grafik merupakan bagian penting dalam penyajian sebuah data. Begitu juga untuk data yang sudah disusun dalam bentuk pivot table. Dalam pivot table, sebuah data mentah akan dibuat lebih teratur dan dapat disesuaikan dengan keinginan sehingga penyajiannya lebih mudah dimengerti. Bentuk chart dari pivot table juga memiliki keunggulan dibanding chart atau grafik biasa. Berikut ini langkah-langkah dalam membuat Pivot Tabel:

Tampilkan menggunakan Pivot chart dari tabel “sales of car 2018” dibawah ini untuk komparasi antar produk, Buatlah laporan berdasarkan “product“, “branch“, dan hitung “total“.

1.      Buka File Pivot Chart.xlsx

2.      Lalu letakan Pointer pada area data.

3.      Lalu masuk ke Tab Insert pilih PivotChart, “pada Versi Excel 2013 keatas anda dapat langsung membuat pivot chart dari bentuk tabel (Range) biasa.

4.      Kemudian pada window Create Pivot Chart, atur parameter sebagai berikut :

·         Table/Range secara otomatis akan terseleksi, jika dibutuhkan anda dapat seleksi ulang area datanya.

·         Pilih new worksheet untuk lokasi pivotachart agar mudah dianalisa.

5.      Kliklah OK

6.      Lalu pada Sheet baru akan di tambahkan area PivotTable dan PivotChart Kosong, gunakan drag dan drop untuk menampilkan datanya. PivotTable dan PivotChartnya.

7.      Pada drag Field “Car“, “Branch“, dan “Total” dari Pivotable Field List ke Area Pivot, atau Gunakan Ceklist untuk menampilkan data pada PivotTable report.

8.      Lalu letakan Field “Car” dan “Branch” pada area “ROWS”

9.      Letakan Field “Total” pada area “VALUES” (setiap field yang mau di hitung letakan pada field Values”

10.  Maka laporan akan otomatis terbentuk di PivotTable dan PivotChart tampilanya seperti dibawah ini:

11.  Untuk melakukan komparasi penjualan antar cabang anda gunakan filter Field “Branch” sebagai contoh akan melihat penjualan area jakarta dan bandung.