Cara Membuat Pivot Table di Excel, Langkah demi Langkah

Pivot table mengelompokkan baris sebuah tabel berdasarkan kategori, seperti Region, lalu menjumlahkan sebuah angka, seperti Sales, untuk setiap kelompok, tanpa rumus. Untuk membuatnya, klik sebuah sel di data, buka Insert > PivotTable, tekan OK, lalu seret Region ke Rows dan Sales ke Values. Sheet di bawah ini bukan pivot table: sheet ini membuat ringkasan yang sama dengan rumus, jadi kamu bisa melihat totalnya berubah.

RumusReset

F2fx

=SUMIF($A$2:$A$9,E2,$C$2:$C$9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Sales	% of total
2	North	Apple	120		North	455	49%
3	South	Pear	85		South	305	33%
4	North	Pear	240		East	170	18%
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

UNIQUE menampilkan setiap wilayah sekali dan SUMIF menjumlahkannya: North 455, South 305 dan East 170, yaitu 49%, 33% dan 18% dari total 930. Ubah C3 menjadi 185, maka total South dan ketiga persentasenya langsung ikut berubah. Pivot table akan menampilkan angka yang sama, tapi baru setelah kamu melakukan refresh.

Cara membuat pivot table

Sebelum mulai, periksa data sumbernya: satu baris judul dengan nama di setiap kolom, satu data per baris, tidak ada baris atau kolom kosong di tengah, dan tidak ada baris subtotal.

1. Klik sel mana pun di data.
2. Buka Insert > PivotTable (di beberapa versi Insert > PivotTable > From Table/Range).
3. Excel mengisi rentangnya. Pilih New Worksheet lalu tekan OK.
4. Pivot table kosong muncul dengan panel PivotTable Fields di sebelah kanan, yang berisi judul-judul kolommu.
5. Seret Region ke kotak Rows dan Sales ke kotak Values. Pivot table menampilkan setiap wilayah sekali dengan Sum of Sales di sebelahnya, dan sebuah baris Grand Total.
6. Untuk mengubah yang ditampilkan, seret field di antara kotak-kotak itu atau keluar dari panel.

Kalau kamu tidak yakin harus mulai dari mana, Insert > Recommended PivotTables menampilkan beberapa tata letak siap pakai untuk datamu. Di Mac, menunya sama: Insert > PivotTable.

Rows, Columns, Values dan Filters

Panel PivotTable Fields punya empat kotak, dan setiap pivot table adalah pilihan kolom mana masuk ke kotak mana:

• Rows: kategori di sisi kiri, satu baris untuk setiap nilai yang berbeda (Region).
• Columns: kategori di bagian atas, satu kolom untuk setiap nilai yang berbeda (Product).
• Values: angka yang dihitung untuk setiap kombinasi. Sum adalah bawaan untuk kolom angka; Count, Average, Max, Min dan lainnya ada di Value Field Settings.
• Filters: field yang dipakai untuk memfilter seluruh pivot table, ditampilkan sebagai drop-down di atasnya.

Dengan Region di Rows, Product di Columns dan Sales di Values, pivot table untuk data di atas tampak seperti ini:

Sum of Sales   Column Labels
Row Labels     Apple   Pear   Grand Total
East              60    110           170
North            215    240           455
South            150    155           305
Grand Total      425    505           930

Versi rumus dari tata letak itu menampilkan wilayah ke bawah dengan UNIQUE, produk ke samping dengan TRANSPOSE(UNIQUE()), lalu menghitung setiap sel di kisinya dengan satu SUMIFS yang menerima kedua daftar itu sebagai kriteria:

RumusReset

F2fx

=SUMIFS(C2:C9,A2:A9,E2:E4,B2:B9,F1:G1)

	A	B	C	D	E	F	G
1	Region	Product	Sales			Apple	Pear
2	North	Apple	120		North	215	240
3	South	Pear	85		South	150	155
4	North	Pear	240		East	60	110
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

E2 menampilkan North, South dan East ke bawah dengan spill, F1 menampilkan Apple dan Pear ke samping, dan SUMIFS di F2 mengisi kisi 3 kali 2 di antaranya: satu total untuk setiap pasangan wilayah dan produk. Ubah B5 dari Apple menjadi Pear, maka kedua sel East berubah. Urutannya di sini mengikuti urutan nilai pertama kali muncul; pivot table mengurutkan labelnya secara alfabetis.

Count, rata-rata atau persentase, bukan sum

Di pivot table, klik field di kotak Values lalu pilih Value Field Settings. Tab Summarize Values By beralih antara Sum, Count, Average, Max dan Min; tab Show Values As mengubah angkanya menjadi % of Grand Total, % of Column Total, total kumulatif dan lainnya. Setiap pilihan punya padanan rumus langsung:

RumusReset

E2fx

=UNIQUE(A2:A9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Orders	Average
2	North	Apple	120		North	3	151.7
3	South	Pear	85		South	3	101.7
4	North	Pear	240		East	2	85.0
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

North punya 3 pesanan dengan rata-rata 151.7, South 3 pesanan dengan rata-rata 101.7, East 2 pesanan dengan rata-rata 85.0. Kolom % of Grand Total ada di sheet pertama halaman ini.

Filter ringkasan untuk satu produk

Kotak Filters menaruh drop-down di atas pivot table. Versi rumusnya adalah sel dengan drop-down list dan SUMIFS, yang menambahkan satu kondisi lagi ke SUMIF. Pilih sebuah produk di F1:

RumusReset

F1fx

	A	B	C	D	E	F
1	Region	Product	Sales		Product:	Apple▾ApplePear
2	North	Apple	120
3	South	Pear	85		Region	Sales
4	North	Pear	240		North	215
5	East	Apple	60		South	150
6	South	Apple	150		East	60
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Dengan Apple dipilih, North menampilkan 215, South 150 dan East 60. Pilih Pear, maka angkanya berubah menjadi 240, 155 dan 110. Lihat SUMIFS untuk kondisi lainnya dan halaman drop-down list untuk cara menambahkan daftarnya di Excel.

Refresh pivot table

Pivot table menyimpan salinan data sumber (pivot cache) dan tidak menghitung ulang ketika sebuah sel di sumbernya berubah. Setelah mengedit data:

• Klik kanan di mana saja di pivot table lalu pilih Refresh, atau tekan Alt+F5 di Windows.
• Data > Refresh All (Ctrl+Alt+F5) melakukan refresh pada setiap pivot table di workbook.
• Untuk melakukan refresh setiap kali file dibuka, klik kanan pivot table, pilih PivotTable Options, lalu di tab Data centang Refresh data when opening the file.

Baris baru yang ditambahkan di bawah rentang sumber tidak ikut masuk, bahkan setelah refresh. Ubah rentangnya di PivotTable Analyze > Change Data Source, atau lebih baik lagi, ubah sumbernya menjadi tabel sebelum membuat pivot table: pilih datanya lalu tekan Ctrl+T, atau pakai Insert > Table. Tabel bertambah ketika kamu menambahkan baris, dan pivot table mengambilnya saat refresh berikutnya.

Jumlahkan setiap wilayah dengan satu rumus

RumusReset

F2fx

	A	B	C	D	E	F
1	Region	Product	Sales		Region	Sales
2	North	Apple	120		North
3	South	Pear	85		South
4	North	Pear	240		East
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Giliranmu: Di F2, jumlahkan penjualan setiap wilayah yang tercantum di E2:E4 dengan satu rumus.

PeriksaTampilkan jawaban

Jawabannya spill menjadi 455, 305 dan 170. Memberikan seluruh daftar E2:E4 sebagai kriteria SUMIF mengembalikan satu total per wilayah, jadi tidak ada yang perlu disalin ke bawah. Excel 2019 dan versi lebih lama tidak punya UNIQUE maupun spill: ketik wilayahnya di E2:E4 lalu salin =SUMIF($A$2:$A$9,E2,$C$2:$C$9) ke bawah. Tanpa tanda $, rentangnya ikut bergeser ke bawah di setiap baris dan totalnya jadi salah.

GROUPBY dan PIVOTBY: pivot table dalam satu rumus

Excel untuk Microsoft 365 punya dua fungsi yang membuat seluruh ringkasan dari satu rumus dan menghitung ulang seperti rumus biasa, tanpa refresh. Keduanya butuh langganan Microsoft 365 yang masih aktif. Untuk data di atas:

=GROUPBY(A2:A9,C2:C9,SUM)

East      170
North     455
South     305
Total     930

=PIVOTBY(A2:A9,B2:B9,C2:C9,SUM)

          Apple   Pear   Total
East         60    110     170
North       215    240     455
South       150    155     305
Total       425    505     930

GROUPBY menerima field baris, nilainya dan fungsinya (SUM, COUNTA, AVERAGE, MAX, PERCENTOF). PIVOTBY menambahkan field kolom di antaranya. Keduanya mengurutkan label dan menambahkan baris total, seperti pivot table.

Pivot table atau rumus: mana yang dipakai

Pakai pivot table untuk menjelajahi data dan menjawab sebuah pertanyaan sekali saja; pakai rumus untuk ringkasan yang ada di laporan, dipakai oleh rumus lain, dan harus selalu terbaru. Untuk memeriksa angka pivot table, buat ulang satu selnya dengan SUMIFS: kalau keduanya berbeda, biasanya pivot table perlu refresh atau rentang sumbernya terlalu pendek.