Kebanyakan orang pertama kali menemui GETPIVOTDATA ketika mereka cuba membina formula di luar jadual pangsi yang menggunakan nombor dalam jadual pangsi. Sebagai contoh, peratusan varians ini tidak akan disalin ke bulan-bulan lain kerana Excel memasukkan fungsi GETPIVOTDATA.
Excel memasukkan GETPIVOTDATA setiap kali anda menggunakan kekunci tetikus atau anak panah untuk menunjuk ke sel di dalam jadual pangsi sambil membina formula di luar jadual pangsi.
Ngomong-ngomong, jika anda tidak mahu fungsi GETPIVOTDATA muncul, cukup ketik formula seperti =D5/C5-1tanpa menggunakan tetikus atau anak panah untuk menunjuk ke sel. Formula itu disalin tanpa masalah.
Berikut adalah set data yang mengandungi satu nombor pelan setiap bulan setiap kedai. Terdapat juga jualan sebenar setiap bulan setiap kedai untuk bulan-bulan yang lengkap. Matlamat anda adalah untuk membina laporan yang menunjukkan maklumat sebenar untuk bulan-bulan yang telah siap dan merancang untuk bulan-bulan mendatang.
Bina jadual pangsi dengan Store in Rows. Masukkan Bulan dan Jenis dalam Lajur. Anda mendapat laporan yang ditunjukkan di bawah ini, dengan Januari Sebenar, Pelan Januari, dan Pelan Januari Sebenar + yang benar-benar tidak masuk akal.
Sekiranya anda memilih sel sebulan dan pergi ke Field Settings, anda boleh menukar Subtotals menjadi None.
Ini menghilangkan Pelan + Sebenar yang tidak berguna. Tetapi anda masih perlu menyingkirkan ruangan rencana untuk Januari hingga April. Tidak ada cara yang baik untuk melakukan ini di dalam jadual pangsi.
Jadi, aliran kerja bulanan anda menjadi:
- Tambahkan yang sebenarnya untuk bulan baru ke set data.
- Bina jadual pangsi baru dari awal.
- Salin jadual pangsi dan tampal sebagai nilai supaya tidak menjadi jadual pangsi lagi.
- Padamkan lajur yang tidak anda perlukan.
Terdapat cara yang lebih baik untuk pergi. Gambar yang sangat dimampatkan berikut menunjukkan lembaran kerja Excel baru yang ditambahkan ke buku kerja. Ini semua hanya Excel lurus, tiada jadual pangsi. Satu-satunya keajaiban adalah fungsi IF di baris 4 yang beralih dari Sebenar ke Rancangan, berdasarkan tarikh di sel P1.
Sel pertama yang perlu diisi adalah Januari Sebenar untuk Baybrook. Klik di sel itu dan taipkan tanda sama.
Dengan menggunakan tetikus, navigasi kembali ke jadual pangsi. Cari sel untuk Januari Sebenar untuk Baybrook. Klik pada sel itu dan tekan Enter. Seperti biasa, Excel membina salah satu fungsi GETPIVOTDATA yang menjengkelkan yang tidak dapat disalin.
Tetapi hari ini, mari kita pelajari sintaks GETPIVOTDATA.
Argumen pertama di bawah adalah medan angka "Penjualan". Argumen kedua ialah sel tempat jadual pangsi berada. Pasangan argumen yang tinggal adalah nama dan nilai medan. Adakah anda melihat apa yang dilakukan formula yang dihasilkan secara automatik? Ia disebut "Baybrook" sebagai nama kedai. Itulah sebabnya anda tidak dapat menyalin formula GETPIVOTDATA yang dihasilkan secara automatik ini. Mereka sebenarnya nama kod keras menjadi formula. Walaupun anda tidak dapat menyalin formula ini, anda boleh mengeditnya. Dalam kes ini, lebih baik anda mengedit formula untuk menunjukkan sel $ D6.
Gambar di bawah menunjukkan formula setelah anda mengeditnya. Sudah ada "Baybrook", "Jan", dan "Sebenar". Sebagai gantinya, anda menunjukkan $ D6, E $ 3, dan E $ 4.
Salin formula ini dan kemudian pilih Paste Special, Formula di semua sel angka yang lain.
Sekarang inilah aliran kerja bulanan anda:
- Bina jadual pangsi jelek yang tidak akan dapat dilihat oleh sesiapa pun.
- Sediakan lembaran kerja laporan.
Setiap bulan, anda mesti:
- Tampal maklumat baru di bawah data.
- Segarkan jadual pangsi jelek.
-
Tukar sel P1 pada helaian laporan untuk menggambarkan bulan baru. Semua nombor dikemas kini.
Anda harus mengakui bahawa menggunakan laporan yang menarik nombor dari jadual pangsi memberikan yang terbaik bagi kedua-dua dunia. Anda bebas memformat laporan dengan cara yang anda tidak dapat memformat jadual pangsi. Baris kosong baik-baik saja. Anda boleh mempunyai simbol mata wang pada baris pertama dan terakhir tetapi tidak di antaranya. Anda juga mendapat garis bawah berganda di bawah jumlah keseluruhan.
Terima kasih kepada @iTrainerMX kerana mencadangkan ciri ini.
