Adakah anda benci fungsi GETPIVOTDATA Excel? Mengapa ia muncul? Bagaimana anda boleh mencegahnya? Adakah penggunaan GETPIVOTDATA baik?
Kebanyakan orang pertama kali menemui GETPIVOTDATA ketika mereka cuba membina formula di luar pivot table yang menggunakan nombor dalam pivot table. 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-1 tanpa menggunakan kekunci 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 Simpan dalam ROWS. Masukkan Bulan dan Jenis dalam COLUMNS. 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 subtotal 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 kecil 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.
Inilah formula selepas anda mengeditnya. Sudah ada "Baybrook", "Jan", dan "Sebenar". Sebaliknya, anda menunjukkan $ D6, E $ 3, E $ 4.
Salin formula ini dan kemudian pilih Paste Special, Formula di semua sel angka yang lain.
Sekarang inilah aliran kerja tahunan 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.
Tukar Sel P1
Anda harus mengakui bahawa menggunakan laporan biasa yang menarik nombor dari jadual pangsi memberikan yang terbaik dari kedua 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.
Tonton Video
- GetPivotData berlaku apabila formula menunjuk ke dalam jadual pangsi
- Walaupun formula awal betul, anda tidak boleh menyalin formula
- Sebilangan besar orang membenci getpivotdata dan mahu menghalangnya
- Kaedah 1: Bina formula tanpa tetikus atau kekunci anak panah
- Kaedah 2: Matikan GetPivotData secara kekal menggunakan dropdown di sebelah pilihan
- Tetapi ada penggunaan untuk GetPivotData
- Anda pengurus mahukan laporan dengan Sebenar untuk beberapa bulan terakhir dan anggaran untuk masa depan
- Aliran kerja biasa meminta anda membuat jadual pangsi, menukar ke nilai, menghapus lajur
- Mengeluarkan Subtotal untuk mengelakkan Pelan Januari + Sebenar menggunakan Tetapan Medan
- Sebaliknya, buat jadual pangsi dengan data "terlalu banyak"
- Gunakan lembaran kerja laporan yang diformat dengan baik
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Dari sel data pertama pada lembaran kerja, bina formula dengan tetikus
- Benarkan GetPivotData berlaku
- Kaji sintaks GetPivotData (medan untuk kembali, lokasi pangsi, pasangan)
- Tukar nilai kod keras untuk menunjuk ke sel
- Menekan F4 tiga kali hanya mengunci lajur
- Menekan F4 dua kali hanya mengunci baris
- Tampalkan Formula Khas
- Aliran kerja bulan depan: Tambahkan data, muat semula jadual pangsi, ubah tarikh
- Sangat berhati-hati untuk berjaga-jaga di kedai baru
Transkrip Video
Belajar Excel dari podcast, episod 2013 - GetPivotData Mungkin Tidak Jahat sepenuhnya!
Saya akan menyiarkan keseluruhan buku ini, klik “i” di sudut kanan atas untuk melanggan.
Baiklah, pada episod 1998 saya bercakap sebentar mengenai masalah GetPivotData ini. Sekiranya kita mengira varians% dan kita berada di luar jadual Pivot yang menunjuk ke dalam, dan saya menggunakan tetikus atau anak panah, jadi 2019 / 2018-1. Jawapan ini yang akan kami sampaikan di sini adalah betul untuk bulan Januari, tetapi apabila kami mengklik dua kali untuk menyalinnya, formula tidak menyalin, kami mendapat jawapan Januari hingga ke bawah. Dan ketika kita melihatnya, kita mendapat GetPivotData, saya tidak menaip GetPivotData, saya hanya menunjukkan sel-sel itu, dan ini mula berlaku kembali di Excel 2002 tanpa sebarang amaran. Dan pada ketika itu saya mengatakan bahawa cara untuk mengelakkannya adalah dengan memasukkan formula C5 / B5-1, dan anda akan mendapat formula yang boleh anda salin. Atau jika anda hanya membenci GetPivotData, jika "benar-benar jahat", pergi ke tab Analisis, jangan 't buka butang Opsyen. Kembali ke jadual Pivot, pergi ke tab Analisis, buka dropdown di sebelah Pilihan, hapus centang kotak ini, ini adalah tetapan global. Setelah anda mematikannya, ia akan dimatikan selamanya, baiklah
Selalunya soalan yang saya dapat adalah "Bagaimana cara mematikan GetPivotData?" tetapi sesekali saya akan mendapatkan seseorang yang menyukai GetPivotData. Dan saya makan tengah hari dengan Rob Collie ketika dia masih di Microsoft, dan dia berkata, "Baiklah, pelanggan dalaman kami menyukai GetPivotData." Saya berkata "Apa? Tidak, semua orang membenci GetPivotData! " Rob berkata "Anda betul, di luar Microsoft, mereka benar-benar membenci GetPivotData." Saya bercakap mengenai akauntan di dalam Microsoft, dan kemudian saya bertemu dengan seorang yang kini bekerja untuk pasukan Excel, Carlos, dan Carlos adalah salah seorang akauntan yang menggunakan kaedah ini.
Baiklah, jadi inilah yang harus kita lakukan. Kami mempunyai laporan kami, satu set data di sini bahawa setiap bulan kami mempunyai rancangan untuk setiap kedai, dan kemudian di bahagian bawah kami mengumpulkan data yang sebenarnya. Baiklah, jadi kita mempunyai maklumat sebenar untuk bulan Januari hingga Disember, tetapi hanya mempunyai yang sebenarnya selama beberapa bulan, bulan yang telah berlalu. Dan apa yang ingin dilakukan oleh pengurus kami ialah membuat laporan dengan kedai di sebelah kiri, hanya kedai Texas sahaja, untuk menjadikan hidup lebih sukar. Dan kemudian berlalu kita mempunyai bulan, dan jika kita mempunyai yang sebenarnya untuk bulan itu, kita menunjukkan yang sebenarnya, jadi Januari sebenarnya, Februari sebenarnya, Mac sebenarnya, April sebenarnya. Tetapi kemudian untuk bulan-bulan di mana kita tidak mempunyai hakikat, kita beralih dan menunjukkan anggaran, jadi belanjakan hingga Disember, dan kemudian jumlahnya semuanya, baiklah. Nah, apabila anda mencuba dan membuat jadual Pivot ini, ya,ia tidak berfungsi.
Oleh itu, masukkan PivotTable, Lembar Kerja Baru, anda meletakkan Simpan di sebelah kiri, sebelah yang cantik, letakkan Bulan di bahagian atas, letakkan Jenis di seberang atas, letakkan Jualan di sini, baiklah. Oleh itu, inilah yang harus kita mulakan, jadi kita mempunyai rancangan Januari sebenar, Januari, dan kemudian rancangan tambah Januari sebenar yang tidak berguna. Tidak ada yang akan menggunakan ini, tetapi saya dapat menyingkirkan lajur kelabu ini, cukup mudah, ada di sini untuk sel ini, pergi ke Tetapan Medan, dan ubah Subtotal menjadi Tiada. Tetapi sama sekali tidak ada cara untuk saya membuang rancangan Januari yang juga tidak akan menghapus rancangan April Mei Jun Julai, baiklah, tidak ada cara untuk menyingkirkan ini. Jadi pada masa ini setiap bulan, saya buntu memilih keseluruhan jadual Pivot, pergi ke Salin, dan kemudian Tampal, Tampal Nilai. Ini bukan jadual Pivot lagi,dan kemudian saya mula menghapus lajur secara manual yang tidak muncul dalam laporan.
Baiklah, itu kaedah biasa, tetapi akauntan di Microsoft telah menambahkan langkah tambahan pada bulan Januari, memerlukan 15 minit, dan langkah ini membolehkan jadual Pivot ini hidup selamanya, bukan? Saya menyebutnya sebagai jadual Pivot paling buruk di dunia, dan akauntan di Microsoft menerima bahawa ini adalah jadual Pivot paling buruk di dunia, tetapi tidak ada yang akan melihat laporan ini kecuali untuk mereka. Apa yang mereka buat, adalah mereka datang ke sini untuk membuat lembaran baru, dan membina laporan yang dikehendaki oleh pengurus mereka. Baiklah, jadi di sini terdapat kedai di sebelah kiri, malah saya kumpulkan ke Houston, Dallas dan Lain-lain, ini adalah laporan yang diformat dengan baik. Saya telah mengetengahkan jumlahnya, anda akan melihat bahawa apabila kita mendapat beberapa nombor, ada mata wang pada baris pertama, tetapi bukan baris berikutnya, baris kosong. Ooh, baris kosong di jadual Pangsi.Dan sedikit logik di sini, di mana saya dapat memasukkan tarikh lulus dalam sel P1, dan kemudian saya mempunyai formula di sini yang menganalisis bahawa JIKA bulan tarikh lulus adalah> lajur ini, dan kemudian letakkan kata Sebenar, jika tidak letakkan perkataan Plan, baiklah. Jadi yang harus saya lakukan adalah mengubahnya sehingga tarikh, dan kemudian perkataan Sebenar membalikkan rancangan, Baiklah.
Sekarang, inilah yang kita lakukan, kita akan membiarkan diri kita menjadi GetPivotData'd, bukan? Saya tidak pasti bahawa itu kata kerja, tetapi kami akan membenarkan Microsoft mendapatkan GetPivotData. Oleh itu, saya mula membina formula dengan =, saya mengambil tetikus, dan saya akan mencari Baybrook Januari yang sebenar! Oleh itu, saya kembali ke jadual Pivot paling buruk di dunia, saya dapati Baybrook, saya menjumpai Januari, saya menjumpai yang sebenarnya, dan saya mengklik Enter, dan membiarkan mereka melakukannya kepada saya, baiklah, di sana kita pergi, kita sekarang mempunyai formula GetPivotData. Saya ingat pada hari saya melakukan ini, seperti, anda tahu, setelah Rob menjelaskan kepada saya apa yang mereka lakukan, dan saya kembali dan mencubanya. Sekarang secara tiba-tiba, sepanjang hidup saya, saya telah menyingkirkan GetPivotData, saya sebenarnya tidak pernah memeluk GetPivotData. Jadi apa sebenarnya, item pertama adalah apa yang kita cari, di sana 'bidang yang disebut Penjualan, di sinilah jadual Pivot bermula, dan ia boleh menjadi sel apa pun dalam jadual Pivot, mereka menggunakan tangan kiri atas.
Baiklah, ini adalah nama bidang "Kedai", dan kemudian mereka telah menggunakan kode "Baybrook", ini adalah nama bidang "Bulan", mereka menggunakan kode "Januari", ini adalah nama bidang "Jenis", dan mereka ' sudah mempunyai kod keras "Sebenar". Itulah sebabnya mengapa anda tidak dapat menyalinnya, kerana nilai-nilai mereka telah dikodkan dengan sukar. Tetapi akauntan di Microsoft, Carlos dan rakan sekerjanya menyedari “Wah, tunggu sebentar, kita ada perkataan Baybrook di sini, kita ada Januari di sini, kita ada Sebenarnya di sini. Kita hanya perlu mengubah formula ini untuk menunjukkan sel yang sebenarnya dalam laporan dan bukannya menggunakan kod keras. " Baiklah, jadi mereka memanggil ini parameterisasi GetPivotData.
Keluarkan perkataan Baybrook, datang ke sini dan klik pada sel D6. Sekarang, saya perlu mengunci ini ke lajur, baiklah, jadi saya menekan kekunci F4 sebanyak 3 kali, dapatkan satu $ sebelum D, baiklah. Untuk bulan Januari saya mengeluarkan Januari yang dikodkan keras, saya mengklik sel E3, saya akan menekan F4 dua kali untuk menguncinya hingga baris, E $ 3. Ketik Sebenar, hapus kata Sebenar, klik pada E4, sekali lagi F4 dua kali, baiklah, dan saya mendapat formula yang kini menarik kembali data itu. Saya akan menyalinnya, dan kemudian Tampal Istimewa, pilih Format, alt = "" ESF, lihat F digarisbawahi di sana, ESF Enter, dan kemudian sekarang, saya telah melakukan bahawa saya hanya akan mengulanginya dengan F4, F4 adalah pengulangan, dan F4. Baiklah, jadi sekarang kita mempunyai laporan yang bagus, laporan itu kosong, formatnya, garis bawah perakaunan tunggal di bawah setiap bahagian,di bahagian bawahnya mempunyai garis panduan perakaunan berganda.
Betul, anda tidak pernah memasukkan barang ini ke dalam jadual Pangsi, itu mustahil, tetapi laporan ini didorong dari jadual Pangsi. Oleh itu, apa yang kita lakukan apabila kita mendapat barang-barang Mei, kembali ke sini, tampalkannya, muat semula jadual Pivot paling buruk di dunia, dan kemudian di sini di laporan, ubah tarikh tamat dari 4/30 hingga 5/31. Dan apa yang dilakukan adalah yang menyebabkan formula ini beralih dari kata Rencana ke Sebenar, yang mengambil dan menarik yang sebenarnya dari laporan, bukannya rancangan, baik-baik saja. Sekarang, inilah perkara yang - ini hebat, bukan? Saya dapat melihat di mana saya akan melakukan ini sekiranya saya masih, anda tahu, bekerja di bidang perakaunan.
Perkara yang harus anda berhati-hati ialah jika mereka membina kedai baru, anda harus tahu untuk menambahkannya secara manual, betul, data akan muncul di jadual Pivot, tetapi anda akan menambahkannya secara manual. Sekarang ini adalah sebahagian daripada semua kedai, jika melaporkan semua kedai, saya mungkin di sini, di luar julat cetakan, mempunyai sesuatu yang menarik jumlah keseluruhan dari jadual Pivot. Dan kemudian saya akan tahu, jika jumlah ini tidak sepadan dengan jumlah keseluruhan dari jadual Pivot, ada sesuatu yang tidak kena, dan mempunyai fungsi JIKA di sini Mengatakan "Hai, anda tahu, data baru yang telah ditambahkan, berhati-hatilah. " Mereka memiliki semacam mekanisme untuk mengesan bahawa data baru ada. Tetapi saya faham, ia adalah penggunaan yang menarik. Jadi, walaupun sepanjang masa GetPivotData membuat kita gila, sebenarnya ada gunanya. Baiklah,jadi itu adalah petua # 21 daripada 40 dalam buku, beli buku sekarang, pesan dalam talian, klik "i" di sudut kanan atas.
Ringkasan panjang dan panjang hari ini, baiklah: GetPivotData berlaku apabila formula menunjuk ke dalam jadual Pivot, formula di luar jadual Pivot menunjuk ke dalam. Walaupun formula awalnya betul, ia tidak akan disalin. Sebilangan besar orang membenci GetPivotData dan mahu menghalangnya. Oleh itu, anda boleh membina formula tanpa tetikus atau kekunci anak panah, cukup taipkan formula, atau matikan GetPivotData secara kekal, ah, tetapi ada gunanya, baiklah. Oleh itu, kita mesti membuat laporan dengan laporan sebenar untuk bulan lalu, anggaran untuk masa depan. Aliran kerja biasa, buat jadual Pivot, ubah menjadi nilai, Padam lajur. Ada cara untuk membuang subtotal dengan menggunakan Tetapan Medan, menyingkirkan rancangan tambah Januari yang sebenarnya. Sebaliknya kami hanya akan membuat jadual Pivot paling buruk di dunia dengan terlalu banyak data.
Bina lembaran kerja laporan lama yang diformat dengan baik, mungkin dengan sedikit logik untuk menukar perkataan Sebenar untuk Merancang. Dan kemudian dari sel laporan pertama, tempat pertama di mana nombor akan berada dalam laporan itu, ketik an =, pergi ke jadual Pivot dan biarkan GetPivotData berlaku. Kami memeriksa sintaks GetPivotData, jadi bidang untuk kembali, Penjualan, tempat jadual Pivot tinggal, dan kemudian pasangan kriteria, nama bidang dan nilainya. Kita akan membuang nilai kod keras dan menunjuk ke sel, menekan F4 3 kali hanya mengunci lajur, menekan F4 2 kali mengunci baris sahaja, salin formula itu, Tampalkan Formula Khas. Saya memberikan petua tambahan di sana bahawa F4 adalah pengulangan, jadi saya hanya perlu pergi ke dialog Tampal Khas sekali, dan kemudian untuk Formula Tampal Khas berikutnya yang baru menggunakan F4. Bulan depan tambahkan data,muat semula jadual Pangsi, ubah tarikh lulus. Pastikan mereka tidak membina kedai baru, anda tahu, mempunyai semacam mekanisme, baik manual, atau formula periksa, periksa. Terima kasih kepada iTrainerMX di Twitter, yang mencadangkan GetPivotData, juga Carlos dan Rob dari Microsoft, Rob sekarang dari Power Pivot Pro. Carlos kerana menggunakan ini, dan Rob kerana memberitahu saya bahawa Carlos menggunakannya, saya berjumpa dengan Carlos kemudian, dan dia mengesahkan ya, dia adalah salah seorang akauntan yang menggunakan ini sepanjang masa di Microsoft, baiklah, ini dia.dan Rob kerana memberitahu saya bahawa Carlos menggunakannya, saya berjumpa dengan Carlos kemudian, dan dia mengesahkan ya, dia adalah salah seorang akauntan yang menggunakan ini sepanjang masa di Microsoft, baiklah, ini dia.dan Rob kerana memberitahu saya bahawa Carlos menggunakannya, saya berjumpa dengan Carlos kemudian, dan dia mengesahkan ya, dia adalah salah seorang akauntan yang menggunakan ini sepanjang masa di Microsoft, baiklah, ini dia.
Baiklah, saya ingin mengucapkan terima kasih kerana mampir, kami akan berjumpa anda di lain kali untuk siaran net lain!
Muat turun fail
Muat turun fail contoh di sini: Podcast2013.xlsx
