Menghitung nilai dalam PivotTable

Untuk menghitung nilai dalam PivotTable, Anda dapat menggunakan salah satu atau semua tipe metode penghitungan berikut ini:

• Fungsi ringkasan dalam bidang nilai    Data di area nilai meringkas data sumber yang mendasari dalam PivotTable. Misalnya, data sumber berikut ini:

• Akan menghasilkan PivotTable dan PivotChart berikut ini. Jika membuat PivotChart dari data dalam PivotTable, nilai di PivotChart tersebut akan mencerminkan perhitungan di laporan PivotTable terkait.

• Di PivotTable, bidang kolom Bulan menyediakan item Maret dan April. Bidang baris Kawasan menyediakan item Utara, Selatan, Timur, dan Barat. Nilai pada persimpangan antara kolom April dan baris Utara adalah total pendapatan penjualan dari catatan dalam data sumber yang memiliki nilai BulanApril dan nilai KawasanUtara.

• Di PivotChart, bidang Kawasan dapat berupa bidang kategori yang memperlihatkan Utara, Selatan, Timur, dan Barat sebagai kategori. Bidang Bulan dapat berupa bidang seri yang memperlihatkan item Maret, April, dan Mei sebagai seri yang dinyatakan dalam legenda. Bidang Nilai bernama Jumlah Penjualan dapat berisi penanda data yang mewakili total pendapatan di setiap kawasan untuk setiap bulan. Misalnya, satu penanda data akan mewakili total penjualan untuk April di kawasan Utara, dengan posisinya pada sumbu vertikal (nilai).

• Untuk menghitung bidang nilai, fungsi ringkasan berikut ini tersedia untuk semua tipe data sumber kecuali data sumber Pemrosesan Analitik Online (OLAP).

  Fungsi	Meringkas
  Sum	Jumlah dari nilai. Fungsi ini adalah fungsi default untuk data numerik.
  Count	Jumlah dari nilai data. Fungsi ringkasan Count bekerja seperti fungsi COUNTA. Count adalah fungsi default untuk data selain angka.
  Average	Rata-rata nilai.
  Max	Nilai terbesar.
  Min	Nilai terkecil.
  Product	Hasil kali dari nilai.
  Count Nums	Jumlah nilai data yang berupa angka. Fungsi ringkasan Count Nums berfungsi sama seperti Fungsi COUNT.
  StDev	Perkiraan dari simpangan baku populasi, di mana sampel adalah subkumpulan dari keseluruhan populasi.
  StDevp	Simpangan baku populasi, di mana populasi ada adalah semua data yang diringkas.
  Var	Perkiraan varians populasi, di mana sampel adalah subkumpulan dari keseluruhan populasi.
  Varp	Varians dari populasi, di mana populasi adalah semua data yang diringkas.

• Penghitungan kustom    perhitungan kustom memperlihatkan nilai berdasarkan item atau sel lain di area data. Misalnya, Anda dapat menampilkan nilai dalam bidang data Jumlah Penjualan sebagai persentase dari penjualan Maret, atau sebagai total sementara dari item dalam bidang Bulan.

  Fungsi berikut ini tersedia untuk perhitungan kustom dalam bidang nilai.

  Fungsi	Hasil
  Tanpa Penghitungan	Menampilkan nilai yang dimasukkan dalam bidang.
  % dari Total Keseluruhan	Menampilkan nilai sebagai persentase dari total keseluruhan semua nilai atau poin data dalam laporan.
  % dari Total Kolom	Menampilkan semua nilai dalam setiap kolom atau seri sebagai persentase total untuk kolom atau seri tersebut.
  % dari Total Baris	Menampilkan nilai dalam setiap baris atau kategori sebagai persentase dari total baris atau kategori tersebut.
  % Dari	Menampilkan nilai sebagai persentase dari nilai Item dasar dalam Bidang dasar.
  % dari Total Baris Induk	Menghitung nilai sebagai berikut: (nilai untuk item) / (nilai untuk item induk pada baris)
  % dari Total Kolom Induk	Menghitung nilai sebagai berikut: (nilai untuk item) / (nilai untuk item induk pada kolom)
  % dari Total Induk	Menghitung nilai sebagai berikut: (nilai untuk item) / (nilai untuk item induk dari Bidang dasar yang dipilih)
  Selisih Dari	Menampilkan nilai sebagai selisih dari nilai Item dasar dalam Bidang dasar.
  % Selisih Dari	Menampilkan nilai sebagai selisih persentase dari nilai Item dasar dalam Bidang dasar.
  Total Sementara dalam	Menampilkan nilai untuk item berurutan dalam Bidang dasar sebagai total sementara.
  % Total Berjalan dalam	Menghitung nilai untuk item berurutan di Bidang dasar yang ditampilkan sebagai total sementara dalam persentase.
  Peringkat Terkecil ke Terbesar	Menampilkan peringkat nilai yang dipilih dalam bidang tertentu, mencantumkan item terkecil dalam bidang sebagai 1, dan setiap nilai yang lebih besar akan memiliki nilai peringkat lebih tinggi.
  Peringkat Terbesar ke Terkecil	Menampilkan peringkat nilai yang dipilih dalam bidang tertentu, mencantumkan item terbesar dalam bidang sebagai 1, dan setiap nilai yang lebih kecil akan memiliki nilai peringkat lebih tinggi.
  Indeks	Menghitung nilai sebagai berikut: ((nilai dalam sel) x (Seluruh Total Keseluruhan)) / ((Total Baris Keseluruhan) x (Total Kolom Keseluruhan))

• Rumus    Jika fungsi ringkasan dan perhitungan kustom tidak memberikan hasil yang diinginkan, Anda dapat membuat rumus sendiri dalam bidang terhitung dan item terhitung. Misalnya, Anda dapat menambahkan item terhitung menggunakan rumus untuk komisi penjualan, yang mungkin berbeda untuk setiap kawasan. Kemudian, laporan akan secara otomatis menyertakan komisi dalam subtotal dan total keseluruhan.

Penghitungan dan opsi yang tersedia dalam laporan bergantung pada asal data sumber; apakah dari database OLAP atau sumber data non-OLAP.

• Penghitungan berdasarkan data sumber OLAP    Untuk PivotTable yang dibuat dari kubus OLAP, nilai yang diringkas telah dihitung sebelumnya di server OLAP sebelum Excel menampilkan hasil. Anda tidak dapat mengubah cara penghitungan nilai yang telah dihitung ini dalam PivotTable. Sebagai contoh, Anda tidak dapat mengubah fungsi ringkasan yang digunakan untuk menghitung subtotal atau bidang data, atau menambahkan bidang terhitung atau item terhitung.

  Selain itu, jika server OLAP menyediakan bidang terhitung, disebut dengan anggota terhitung, Anda akan melihat bidang ini dalam Daftar Bidang PivotTable. Anda juga akan melihat setiap bidang terhitung dan item terhitung yang dibuat oleh makro yang ditulis dalam Visual Basic for Applications (VBA) dan disimpan dalam buku kerja, tetapi bidang atau item berikut ini tidak akan dapat diubah. Jika memerlukan tipe penghitungan lain, hubungi administrator database OLAP.

  Untuk data sumber OLAP, Anda dapat menyertakan atau mengecualikan nilai bagi item tersembunyi ketika menghitung subtotal dan total keseluruhan.

• Penghitungan berdasarkan data sumber non-OLAP    Dalam PivotTable yang didasarkan pada tipe data eksternal lain atau pada data lembar kerja, Excel menggunakan fungsi ringkasan Sum untuk menghitung bidang nilai yang berisi data numerik, dan fungsi ringkasan Count untuk menghitung bidang data yang berisi teks. Anda dapat memilih fungsi ringkasan yang berbeda, seperti Average, Max, atau Min, untuk menganalisis dan mengustomisasi data lebih lanjut. Anda juga dapat membuat rumus sendiri yang menggunakan elemen laporan atau data lembar kerja lainnya dengan membuat bidang terhitung atau item terhitung dalam bidang.

Anda dapat membuat rumus hanya dalam laporan yang didasarkan pada data sumber non-OLAP. Anda tidak dapat menggunakan rumus dalam laporan yang didasarkan pada database OLAP. Ketika menggunakan rumus dalam PivotTable, Anda harus mengetahui aturan sintaks rumus dan perilaku rumus berikut ini:

• Elemen rumus PivotTable    Dalam rumus yang dibuat untuk bidang terhitung dan item terhitung, Anda dapat menggunakan operator dan ekspresi seperti dalam rumus lembar kerja lain. Anda dapat menggunakan konstanta dan merujuk pada data dari laporan, tetapi tidak dapat menggunakan referensi sel atau nama yang ditentukan. Anda tidak dapat menggunakan fungsi lembar kerja yang memerlukan referensi sel atau nama yang ditentukan sebagai argumen, serta tidak dapat menggunakan fungsi array.

• Nama bidang dan item    Excel menggunakan nama bidang dan item untuk mengidentifikasi elemen laporan dalam rumus. Dalam contoh berikut ini, data dalam rentang C3:C9 menggunakan nama bidang ProdukSusu. Item terhitung dalam bidang Tipe yang memperkirakan penjualan untuk produk baru berdasarkan penjualan Produk Susu dapat menggunakan rumus seperti =ProdukSusu * 115%.

  

  Catatan: Di PivotChart, nama bidang ditampilkan dalam daftar bidang PivotTable, dan nama item dapat dilihat dalam daftar menurun setiap bidang. Jangan tertukar antara nama ini dengan nama dalam tips bagan yang mencerminkan nama poin data dan seri.

• Rumus beroperasi pada total jumlah, bukan catatan tersendiri    Rumus untuk bidang terhitung beroperasi pada jumlah data yang mendasari bagi bidang apa pun dalam rumus. Misalnya, rumus bidang terhitung =Penjualan * 1.2 akan mengalikan jumlah penjualan untuk setiap tipe dan kawasan dengan 1.2; bukan mengalikan setiap penjualan individu dengan 1.2 lalu menjumlahkan jumlah yang dikalikan.

  Rumus untuk item terhitung beroperasi pada catatan tersendiri. Misalnya, rumus item terhitung =ProdukSusu *115% akan mengalikan setiap penjualan individu Produk Susu dengan 115%, dan jumlah yang dikalikan diringkas bersama di area Nilai.

• Spasi, angka dan simbol dalam nama    Dalam nama yang menyertakan lebih dari satu bidang, bidang dapat disusun dalam urutan apa pun. Dalam contoh di atas, sel C6:D6 dapat berupa 'April Utara' atau 'Utara April'. Gunakan tanda kutip tunggal di sekitar nama yang mengandung lebih dari satu kata, atau angka atau simbol.

• Total    Rumus tidak dapat merujuk ke total (seperti Total Maret, Total April, dan Total Keseluruhan dalam contoh).

• Nama bidang dalam referensi item    Anda dapat menyertakan nama bidang dalam referensi ke suatu item. Nama item harus diapit oleh tanda kurung siku, misalnya, Kawasan[Utara]. Gunakan format ini untuk menghindari kesalahan #NAMA? ketika dua item pada dua bidang yang berbeda dalam laporan memiliki nama yang sama. Misalnya, jika suatu laporan memiliki item bernama Daging di bidang Tipe dan item lain bernama Daging di bidang Kategori, Anda dapat mencegah kesalahan #NAMA? dengan merujuk pada item sebagai Tipe[Daging] dan Kategori[Daging].

• Merujuk ke item menurut posisi    Anda dapat merujuk ke item menurut posisinya dalam laporan sebagaimana item diurutkan dan ditampilkan saat ini. Tipe[1] adalah ProdukSusu, dan Tipe[2] adalah MakananLaut. Item yang dirujuk dengan cara ini dapat berubah setiap kali posisi item berubah atau ketika item lain ditampilkan atau disembunyikan. Item tersembunyi tidak akan dihitung dalam indeks ini.

  Anda dapat menggunakan posisi relatif untuk merujuk pada item. Posisi ditentukan berdasarkan item terhitung yang berisi rumus. Jika Selatan adalah kawasan saat ini, Kawasan[-1] adalah Utara; jika Utara adalah kawasan saat ini, Kawasan[+1] adalah Selatan. Sebagai contoh, item terhitung dapat menggunakan rumus =Kawasan[-1] * 3%. Jika posisi yang Anda tentukan berada sebelum item pertama atau setelah item terakhir dalam bidang, rumus akan menghasilkan kesalahan #REF! .

Untuk menggunakan rumus di PivotChart, buat rumus dalam PivotTable terkait, yang dapat digunakan untuk melihat setiap nilai yang menyusun data Anda, kemudian lihat hasilnya dalam bentuk grafis di PivotChart.

Misalnya, PivotChart berikut ini memperlihatkan penjualan untuk setiap tenaga penjual per kawasan:

Untuk melihat angka penjualan jika meningkat 10 persen, Anda dapat membuat bidang terhitung dalam PivotTable terkait yang menggunakan rumus seperti =Penjualan * 110%.

Hasil akan langsung muncul di PivotChart, seperti yang diperlihatkan pada bagan berikut:

Untuk melihat penanda data terpisah bagi penjualan di kawasan Utara dikurangi 8 persen biaya transportasi, Anda dapat membuat item terhitung di bidang Kawasan dengan rumus seperti =Utara – (Utara * 8%).

Bagan hasil akan terlihat seperti ini:

Namun, item terhitung yang dibuat dalam bidang Tenaga Penjual akan muncul sebagai seri yang diwakili dalam legenda dan muncul dalam bagan sebagai poin data di setiap kategori.

Sebelum memulai, putuskan apakah Anda menginginkan item terhitung atau bidang terhitung dalam bidang. Gunakan bidang terhitung jika ingin menggunakan data dari bidang lain dalam rumus. Gunakan item terhitung jika ingin rumus menggunakan data dari satu atau beberapa item tertentu dalam bidang.

Untuk item terhitung, Anda dapat memasukkan rumus yang berbeda per sel. Contohnya, jika suatu item terhitung yang bernama OrangeCounty memiliki rumus =Jeruk * .25 di semua bulan, Anda dapat mengubah rumus menjadi =Jeruk *.5 untuk bulan Juni, Juli, dan Agustus.

Jika memiliki beberapa item terhitung atau rumus, Anda dapat menyesuaikan urutan penghitungan.

Menambahkan bidang terhitung

1. Klik PivotTable.

   Tindakan ini akan menampilkan Alat PivotTable, menambahkan tab Analisis dan Desain.

2. Pada tab Analisis, dalam grup Penghitungan, klik Bidang, Item, & Set, lalu klik Bidang Terhitung.

   

3. Dalam kotak Nama, ketikkan nama untuk bidang tersebut.

4. Dalam kotak Rumus, masukkan rumus untuk bidang.

   Untuk menggunakan data dari bidang lain dalam rumus, klik bidang dalam kotak Bidang, lalu klik Sisipkan Bidang. Misalnya, untuk menghitung komisi 15% pada setiap nilai di bidang Penjualan, Anda dapat memasukkan = Penjualan * 15%.

5. Klik Tambahkan.

Menambahkan item terhitung pada bidang

1. Klik PivotTable.

   Tindakan ini akan menampilkan Alat PivotTable, menambahkan tab Analisis dan Desain.

2. Jika item dalam bidang dikelompokkan, pada tab Analisis, dalam grup Kelompokkan, klik Pisahkan.

   

3. Klik bidang tempat Anda ingin menambahkan item terhitung.

4. Pada tab Analisis, dalam grup Penghitungan, klik Bidang, Item, & Set, lalu klik Item Terhitung.

   

5. Dalam kotak Nama, ketikkan nama untuk item terhitung.

6. Dalam kotak Rumus, masukkan rumus untuk item.

   Untuk menggunakan data dari suatu item dalam rumus, klik item dalam daftar Item, lalu klik Sisipkan Item (item harus berasal dari bidang yang sama dengan item terhitung).

7. Klik Tambahkan.

Memasukkan rumus yang berbeda dalam setiap sel untuk item terhitung

1. Klik sel yang ingin Anda ubah rumusnya.

   Untuk mengubah rumus bagi beberapa sel, tahan tombol CTRL dan klik sel tambahan.

2. Di bilah rumus, ketikkan perubahan pada rumus.

Menyesuaikan urutan penghitungan untuk beberapa item terhitung atau rumus

1. Klik PivotTable.

   Tindakan ini akan menampilkan Alat PivotTable, menambahkan tab Analisis dan Desain.

2. Pada tab Analisis, dalam grup Penghitungan, klik Bidang, Item, & Set, lalu klik Urutan Penyelesaian.

   

3. Klik rumus, lalu klik Pindahkan ke Atas atau Pindahkan ke Bawah.

4. Lanjutkan hingga rumus berada dalam urutan penghitungan yang diinginkan.

Anda dapat menampilkan daftar semua rumus yang digunakan dalam PivotTable saat ini.

1. Klik PivotTable.

   Tindakan ini akan menampilkan Alat PivotTable, menambahkan tab Analisis dan Desain.

2. Pada tab Analisis, dalam grup Penghitungan, klik Bidang, Item, & Set, lalu klik Daftar Rumus.

   

Sebelum mengedit rumus, tentukan apakah rumus tersebut berada dalam bidang terhitung atau item terhitung. Jika rumus berada dalam item terhitung, tentukan juga apakah rumus tersebut merupakan satu-satunya rumus untuk item terhitung.

Untuk item terhitung, Anda dapat mengedit setiap rumus bagi sel tertentu dari item terhitung. Contohnya, jika suatu item terhitung yang bernama HitungJeruk memiliki rumus =Jeruk * .25 di semua bulan, Anda dapat mengubah rumus menjadi =Jeruk *.5 untuk bulan Juni, Juli, dan Agustus.

Menentukan apakah rumus berada dalam bidang terhitung atau item terhitung

1. Klik PivotTable.

   Tindakan ini akan menampilkan Alat PivotTable, menambahkan tab Analisis dan Desain.

2. Pada tab Analisis, dalam grup Penghitungan, klik Bidang, Item, & Set, lalu klik Daftar Rumus.

   

3. Dalam daftar rumus, temukan rumus yang ingin diubah, tercantum di bawah Bidang Terhitung atau Item Terhitung.
   
   Ketika terdapat beberapa rumus untuk item terhitung, rumus default yang dimasukkan saat item dibuat memiliki nama item terhitung di kolom B. Untuk rumus tambahan untuk item terhitung, kolom B berisi nama item terhitung dan nama item yang beririsan.

   Misalnya, Anda mungkin memiliki rumus default untuk item terhitung bernama ItemSaya, dan rumus lain untuk item ini diidentifikasi sebagai Penjualan Januari ItemSaya. Di PivotTable, Anda akan menemukan rumus ini dalam sel Penjualan untuk baris ItemSaya dan kolom Januari.

4. Lanjutkan dengan salah satu metode pengeditan berikut ini.

Mengedit rumus bidang terhitung

1. Klik PivotTable.

   Tindakan ini akan menampilkan Alat PivotTable, menambahkan tab Analisis dan Desain.

2. Pada tab Analisis, dalam grup Penghitungan, klik Bidang, Item, & Set, lalu klik Bidang Terhitung.

   

3. Dalam kotak Nama, pilih bidang terhitung berisi rumus yang ingin diubah.

4. Dalam kotak Rumus, edit rumus.

5. Klik Ubah.

Mengedit rumus tunggal untuk item terhitung

1. Klik bidang yang berisi item terhitung.

2. Pada tab Analisis, dalam grup Penghitungan, klik Bidang, Item, & Set, lalu klik Item Terhitung.

   

3. Dalam kotak Nama, pilih item terhitung.

4. Dalam kotak Rumus, edit rumus.

5. Klik Ubah.

Mengedit rumus individu untuk sel tertentu dari item terhitung

1. Klik sel yang ingin Anda ubah rumusnya.

   Untuk mengubah rumus bagi beberapa sel, tahan tombol CTRL dan klik sel tambahan.

2. Di bilah rumus, ketikkan perubahan pada rumus.

   Tips: Jika memiliki beberapa item terhitung atau rumus, Anda dapat menyesuaikan urutan penghitungan. Untuk informasi selengkapnya, lihat Menyesuaikan urutan penghitungan untuk beberapa rumus atau item terhitung.

1. Pastikan apakah rumus berada dalam bidang terhitung atau item terhitung.

   Bidang terhitung muncul dalam Daftar Bidang PivotTable. Item terhitung muncul sebagai item dalam bidang lain.

2. Lakukan salah satu hal berikut:

   • Untuk menghapus bidang terhitung, klik di mana saja dalam PivotTable.

   • Untuk menghapus item terhitung, dalam PivotTable, klik bidang berisi item yang ingin dihapus.

     Tindakan ini akan menampilkan Alat PivotTable, menambahkan tab Analisis dan Desain.

3. Pada tab Analisis, dalam grup Penghitungan, klik Bidang, Item, & Set, lalu klik Bidang Terhitung atau Item Terhitung.

   

4. Dalam kotak Nama, pilih bidang atau item yang ingin dihapus.

5. Klik Hapus.