Cara Menambah dan Menggunakan Medan Kira Jadual Pangsi Excel

Biasanya, sebaik sahaja anda membuat jadual pangsi, anda perlu mengembangkan analisis anda dan memasukkan lebih banyak data/pengiraan di dalamnya.

Jika anda memerlukan titik data baharu yang boleh diperoleh menggunakan titik data sedia ada dalam jadual pangsi, tidak perlu kembali dan menambahnya pada data sumber.Sebaliknya, anda boleh menggunakanMedan Pengiraan Jadual Pangsiuntuk membuat ini.

Apakah Medan Pengiraan Jadual Pangsi?

Mari kita mulakan dengan contoh asas jadual pangsi.

Katakan anda mempunyai set data peruncit dan anda membuat jadual pangsi seperti ini:

Jadual pangsi di atas meringkaskan nilai jualan dan keuntungan peruncit.

Sekarang, bagaimana jika anda juga ingin tahu apakah margin keuntungan peruncit ini (margin keuntungan ialah "keuntungan" dibahagikan dengan "jualan")?

Terdapat beberapa cara untuk melakukan ini:

1. Kembali ke set data asal dan tambah titik data baharu ini.Jadi anda boleh memasukkan lajur baharu dalam data sumber dan mengira margin keuntungan di dalamnya.Setelah ini selesai, anda perlu mengemas kini data sumber jadual pangsi untuk mendapatkan lajur baharu ini sebagai sebahagian daripadanya.
   • Walaupun pendekatan ini berfungsi, anda perlu kembali ke set data secara manual dan melakukan pengiraan.Sebagai contoh, anda mungkin ingin menambah lajur lain untuk mengira purata jualan seunit (jualan/kuantiti).Sekali lagi, anda perlu menambah lajur ini pada data sumber dan kemudian mengemas kini jadual pangsi.
   • Kaedah ini juga mengembang jadual pangsi anda semasa anda menambah data baharu padanya.
2. Tambah pengiraan di luar jadual pangsi.Pilih pilihan ini jika struktur jadual pangsi anda tidak mungkin berubah.Walau bagaimanapun, jika anda menukar jadual pangsi, pengiraan mungkin tidak dikemas kini dengan sewajarnya dan mungkin memberi anda hasil atau ralat yang salah.Seperti yang ditunjukkan di bawah, saya mengira margin keuntungan apabila terdapat peruncit dalam barisan.Tetapi apabila saya menukarnya dari pelanggan ke kawasan, formula menjadi salah.
3. Kira medan menggunakan jadual pangsi.ini adalahGunakan data Jadual Pangsi sedia ada dan kirakancara yang paling berkesan.Fikirkan medan terkira sebagai lajur maya yang anda tambah menggunakan lajur sedia ada dalam jadual pangsi anda.Menggunakan medan pengiraan jadual pangsi mempunyai banyak faedah (seperti yang akan kita lihat kemudian):
   • Ia tidak memerlukan anda bekerja dengan formula atau mengemas kini data sumber.
   • Ia boleh dilanjutkan kerana ia secara automatik mengambil kira sebarang data baharu yang mungkin anda tambahkan pada jadual pangsi.Selepas menambah medan terkira, anda boleh menggunakannya seperti mana-mana medan lain dalam jadual pangsi.
   • Ia mudah dikemas kini dan diurus.Contohnya, jika metrik berubah atau anda perlu menukar pengiraan, anda boleh melakukannya dengan mudah daripada jadual pangsi itu sendiri.

Tambahkan medan terkira pada jadual pangsi

Mari lihat cara menambah medan pengiraan jadual pangsi pada jadual pangsi sedia ada.

Katakan anda mempunyai jadual pangsi yang kelihatan seperti ini, dan anda ingin mengira margin keuntungan untuk setiap peruncit:

Berikut ialah langkah-langkah untuk menambah medan pengiraan Jadual Pangsi:

Selepas menambah medan yang dikira, ia akan muncul sebagai salah satu medan dalam senarai Medan Jadual Pangsi.

Anda kini boleh menggunakan medan terkira ini sebagai mana-mana medan jadual pangsi lain (perhatikan bahawa anda tidak boleh menggunakan medan terkira jadual pangsi sebagai penapis laporan atau penghiris).

Seperti yang saya nyatakan sebelum ini, faedah menggunakan medan pengiraan jadual pangsi ialah anda boleh menukar struktur jadual pangsi dan ia akan melaraskan secara automatik.

Sebagai contoh, jika saya seret dan lepaskan kawasan ke dalam kawasan baris, anda akan mendapat hasil seperti berikut, di mana nilai margin keuntungan untuk peruncit dan wilayah dilaporkan.

 

Dalam contoh di atas, saya menggunakan formula mudah (=Keuntungan/Jualan) untuk memasukkan medan yang dikira.Walau bagaimanapun, anda juga boleh menggunakan beberapa formula lanjutan.

Sebelum saya menunjukkan kepada anda contoh menggunakan formula lanjutan untuk mencipta medan pengiraan Jadual Pangsi, berikut ialah beberapa perkara yang anda mesti ketahui:

• Anda tidak boleh menggunakan rujukan atau julat yang dinamakan semasa membuat medan pengiraan Jadual Pangsi.Ini akan mengecualikan banyak formula seperti VLOOKUP, INDEX, OFFSET, dll.Walau bagaimanapun, anda boleh menggunakan formula yang berfungsi tanpa rujukan (cth SUM, IF, COUNT, dll...).
• Anda boleh menggunakan pemalar dalam formula.Contohnya, jika anda ingin mengetahui ramalan pertumbuhan jualan yang dijangka meningkat sebanyak 10%, anda akan menggunakan formula =Jualan*1.1 (dengan 1.1 ialah pemalar).
• Ikut susunan keutamaan dalam formula yang menjana medan terkira.Sebagai amalan terbaik, gunakan kurungan untuk memastikan anda tidak perlu mengingati keutamaan.

Sekarang mari kita lihat contoh mencipta medan terkira menggunakan formula lanjutan.

Katakan anda mempunyai set data yang ditunjukkan di bawah dan anda perlu memaparkan jualan yang diramalkan dalam jadual pangsi.

Untuk nilai ramalan, anda akan menggunakan peningkatan jualan sebanyak 300% untuk peruncit besar (lebih 5 juta jualan) dan peningkatan jualan sebanyak 300% untuk peruncit kecil dan sederhana (jualan kurang daripada 10 juta).

Nota: Angka jualan di sini adalah palsu dan telah digunakan untuk menggambarkan contoh dalam tutorial ini.

Begini cara melakukannya:

Ini akan menambah lajur baharu pada Jadual Pangsi dengan nilai ramalan jualan.

Masalah medan pengiraan jadual pangsi

Medan terkira ialah ciri hebat yang benar-benar meningkatkan nilai jadual pangsi dengan pengiraan medan, sambil mengekalkan segala-galanya berskala dan terurus.

Walau bagaimanapun, terdapat masalah dengan medan pengiraan Jadual Pangsi yang mesti anda fahami sebelum menggunakannya.

Katakan, saya mempunyai jadual pangsi seperti di bawah dan saya menggunakan medan terkira untuk mendapatkan nombor jualan ramalan.

Ambil perhatian bahawa jumlah kecil dan jumlah adalah salah.

Walaupun ini sepatutnya menambah nilai ramalan jualan yang berasingan untuk setiap peruncit, pada hakikatnya, ia mengikut formula medan terkira yang sama yang kami buat.

Jadi untuk Jumlah Selatan, walaupun nilainya sepatutnya 22,824,000, Jumlah Selatan secara salah melaporkannya sebagai 22,287,000.Ini berlaku kerana ia menggunakan formula 21,225,800*1.05 untuk mendapatkan nilai.

Malangnya, anda tidak boleh membetulkan masalah ini.

Cara terbaik untuk menangani perkara ini ialah mengalih keluar subjumlah dan jumlah besar daripada jadual pangsi.

Anda juga boleh lulus Disampaikan oleh DebraBeberapa penyelesaian inovatif untuk menangani masalah ini.

Bagaimana untuk mengubah suai atau memadam medan pengiraan jadual pangsi?

Selepas anda mencipta medan pengiraan Jadual Pangsi, anda boleh mengubah suai formula atau memadamkannya menggunakan langkah berikut:

Bagaimana untuk mendapatkan senarai semua formula medan yang dikira?

Jika anda mencipta banyak medan terkira Jadual Pangsi, anda tidak perlu risau tentang menjejaki formula yang digunakan dalam setiap medan.

Excel membolehkan anda membuat senarai semua formula yang digunakan untuk membuat medan terkira dengan cepat.

Berikut ialah langkah-langkah untuk mendapatkan senarai semua formula medan terkira dengan cepat:

• Pilih mana-mana sel dalam jadual pangsi.
• Pergi ke Alat Jadual Pangsi -> Analisis -> Medan, Item dan Set -> Formula Senarai.

Sebaik sahaja anda mengklik pada formula senarai, Excel secara automatik akan memasukkan lembaran kerja baharu dengan butiran semua medan/item terkira yang anda gunakan dalam jadual pangsi.

Ini boleh menjadi alat yang sangat berguna jika anda perlu menghantar kerja kepada pelanggan atau berkongsi dengan pasukan anda.