Apakah Anda membenci fungsi GETPIVOTDATA Excel? Mengapa itu muncul? Bagaimana Anda mencegahnya? Apakah ada gunanya GETPIVOTDATA?
Kebanyakan orang pertama kali menemukan GETPIVOTDATA ketika mereka mencoba membuat rumus di luar tabel pivot yang menggunakan angka di tabel pivot. Misalnya, persentase varian ini tidak akan disalin ke bulan-bulan lainnya karena Excel memasukkan fungsi GETPIVOTDATA.
Excel menyisipkan GETPIVOTDATA setiap kali Anda menggunakan mouse atau tombol panah untuk menunjuk ke sel di dalam tabel pivot sambil membuat rumus di luar tabel pivot.
Omong-omong, jika Anda tidak ingin fungsi GETPIVOTDATA muncul, cukup ketikkan rumus seperti = D5 / C5-1 tanpa menggunakan tombol mouse atau panah untuk menunjuk ke sel. Rumus itu menyalin tanpa masalah.
Berikut adalah kumpulan data yang berisi satu nomor paket per bulan per toko. Ada juga penjualan aktual per bulan per toko untuk bulan-bulan yang telah selesai. Sasaran Anda adalah membuat laporan yang menunjukkan bulan-bulan aktual yang diselesaikan dan merencanakan bulan-bulan mendatang.
Buat tabel pivot dengan Simpan di BARIS. Letakkan Bulan dan Ketik di KOLOM. Anda mendapatkan laporan yang ditunjukkan di bawah ini, dengan Aktual Januari, Rencana Januari, dan Rencana + Aktual Januari yang sama sekali tidak masuk akal.
Jika Anda memilih sel bulan dan masuk ke pengaturan bidang, Anda bisa mengubah subtotal menjadi tidak ada.
Ini menghapus Paket Aktual + yang tidak berguna. Tetapi Anda masih harus menyingkirkan kolom rencana untuk Januari hingga April. Tidak ada cara yang baik untuk melakukan ini di dalam tabel pivot.
Jadi, alur kerja bulanan Anda menjadi:
- Tambahkan data aktual untuk bulan baru ke kumpulan data.
- Buat tabel pivot baru dari awal.
- Salin tabel pivot dan tempel sebagai nilai sehingga bukan tabel pivot lagi.
- Hapus kolom yang tidak Anda butuhkan.
Ada cara yang lebih baik untuk pergi. Gambar yang sangat kecil berikut ini memperlihatkan lembar kerja Excel baru yang ditambahkan ke buku kerja. Ini semua hanya Excel lurus, tidak ada tabel pivot. Satu-satunya keajaiban adalah fungsi IF di baris 4 yang beralih dari Aktual ke Rencana berdasarkan tanggal di sel P1.
Sel pertama yang perlu diisi adalah Januari, Sebenarnya untuk Baybrook. Klik di sel itu dan ketik tanda sama dengan. Menggunakan mouse, navigasikan kembali ke tabel pivot. Temukan sel untuk January Actuals untuk Baybrook. Klik pada sel itu dan tekan Enter. Seperti biasa, Excel membuat salah satu fungsi GETPIVOTDATA yang mengganggu yang tidak dapat disalin.
Tapi hari ini, mari kita pelajari sintaks GETPIVOTDATA.
Argumen pertama di bawah ini adalah bidang numerik "Penjualan". Argumen kedua adalah sel tempat tabel pivot berada. Pasangan argumen yang tersisa adalah nama dan nilai bidang. Apakah Anda melihat apa yang dilakukan rumus yang dibuat secara otomatis? Itu hardcode "Baybrook" sebagai nama toko. Itulah mengapa Anda tidak dapat menyalin rumus GETPIVOTDATA yang dibuat secara otomatis ini. Mereka sebenarnya memasukkan nama kode keras ke dalam rumus. Meskipun Anda tidak bisa menyalin rumus ini, Anda bisa mengeditnya. Dalam kasus ini, akan lebih baik jika Anda mengedit rumus agar mengarah ke sel $ D6.
Berikut rumusnya setelah kamu edit. Hilang sudah "Baybrook", "Jan", dan "Actual". Sebaliknya, Anda menunjuk ke $ D6, E $ 3, E $ 4.
Salin rumus ini lalu pilih Tempel Spesial, Rumus di semua sel numerik lainnya.
Sekarang inilah alur kerja tahunan Anda:
- Buat tabel pivot jelek yang tidak akan pernah dilihat orang.
- Siapkan lembar kerja laporan.
Setiap bulan, Anda harus:
- Tempelkan aktual baru di bawah data.
- Segarkan tabel pivot jelek.
-
Ubah sel P1 pada lembar laporan untuk mencerminkan bulan baru. Semua nomor diperbarui.
Ubah Sel P1
Anda harus mengakui bahwa menggunakan laporan biasa yang menarik angka dari tabel pivot memberi Anda yang terbaik dari kedua dunia. Anda bebas memformat laporan dengan cara yang tidak dapat Anda lakukan untuk memformat tabel pivot. Baris kosong boleh-boleh saja. Anda dapat memiliki simbol mata uang di baris pertama dan terakhir tetapi tidak di antara keduanya. Anda juga mendapatkan garis bawah ganda di bawah total keseluruhan.
Terima kasih kepada @iTrainerMX karena telah menyarankan fitur ini.
Menonton video
- GetPivotData terjadi saat rumus menunjuk ke dalam tabel pivot
- Meskipun rumus awal sudah benar, Anda tidak dapat menyalin rumus tersebut
- Kebanyakan orang membenci getpivotdata dan ingin mencegahnya
- Metode 1: Buat rumus tanpa tombol mouse atau panah
- Metode 2: Matikan GetPivotData secara permanen menggunakan menu turun bawah di samping opsi
- Tapi ada gunanya GetPivotData
- Manajer Anda menginginkan laporan dengan Aktual untuk bulan lalu dan anggaran untuk masa depan
- Alur kerja normal akan membuat Anda membuat tabel pivot, mengonversi ke nilai, menghapus kolom
- Menghapus Subtotal untuk mencegah Januari Aktual + Rencana menggunakan Pengaturan Bidang
- Sebaliknya, buat tabel pivot dengan data "terlalu banyak"
- Gunakan lembar kerja laporan yang diformat dengan baik
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Dari sel data pertama di lembar kerja, buat rumus dengan mouse
- Izinkan GetPivotData terjadi
- Periksa sintaks GetPivotData (bidang untuk dikembalikan, lokasi pivot, pasangan)
- Ubah nilai hard-code untuk menunjuk ke sel
- Menekan F4 tiga kali hanya mengunci kolom
- Menekan F4 dua kali hanya mengunci baris
- Tempel Rumus Khusus
- Alur kerja bulan depan: Tambahkan data, perbarui tabel pivot, ubah tanggal
- Sangat berhati-hati untuk berhati-hati terhadap toko baru
Transkrip Video
Pelajari Excel dari podcast, episode 2013 - GetPivotData Mungkin Tidak Sepenuhnya Jahat!
Saya akan membuat podcasting seluruh buku ini, klik "i" di pojok kanan atas untuk berlangganan.
Baiklah, kembali ke episode 1998 saya berbicara singkat tentang masalah GetPivotData ini. Jika kita menghitung% varians dan kita berada di luar tabel Pivot yang menunjuk ke dalam, dan saya menggunakan mouse atau tombol panah, maka 2019 / 2018-1. Jawaban yang akan kita dapatkan di sini benar untuk bulan Januari, tetapi ketika kita mengklik dua kali untuk menyalinnya, rumusnya tidak menyalin, kita mendapatkan jawaban bulan Januari sepenuhnya. Dan ketika kita melihatnya, kita mendapatkan GetPivotData, saya tidak mengetik GetPivotData, saya hanya menunjuk ke sel-sel itu, dan ini mulai terjadi kembali di Excel 2002 tanpa peringatan apa pun. Dan pada saat itu saya mengatakan bahwa cara untuk menghindarinya adalah dengan mengetikkan rumus C5 / B5-1, dan Anda akan mendapatkan rumus yang dapat Anda salin. Atau jika Anda hanya membenci GetPivotData, jika itu "benar-benar jahat", buka tab Analisis, jangant buka tombol Opsi. Kembali ke tabel Pivot, buka tab Analisis, buka dropdown di sebelah Opsi, hapus centang pada kotak ini, ini adalah pengaturan global. Setelah Anda mematikannya, itu akan mati selamanya, oke.
Sebagian besar pertanyaan yang saya dapatkan adalah "Bagaimana cara menonaktifkan GetPivotData?" tetapi sesekali saya akan mendapatkan seseorang yang menyukai GetPivotData. Dan saya sedang makan siang dengan Rob Collie ketika dia masih di Microsoft, dan dia berkata, “Pelanggan internal kami menyukai GetPivotData.” Aku bilang apa? Tidak, semua orang membenci GetPivotData! ” Rob berkata "Anda benar, di luar Microsoft, tentu saja, mereka membenci GetPivotData." Saya berbicara tentang akuntan di dalam Microsoft, dan kemudian saya bertemu dengan orang yang sekarang bekerja untuk tim Excel, Carlos, dan Carlos adalah salah satu akuntan yang menggunakan metode ini.
Baiklah, jadi inilah yang harus kita lakukan. Kami memiliki laporan kami, kumpulan data di sini yang untuk setiap bulan kami memiliki rencana untuk setiap toko, dan kemudian di bagian bawah kami mengumpulkan yang sebenarnya. Baiklah, jadi kita punya aktual untuk Januari sampai Desember, tapi hanya punya aktual untuk beberapa bulan, bulan-bulan yang telah berlalu. Dan apa yang manajer kami ingin agar kami lakukan adalah membuat laporan dengan toko di sisi kiri, hanya toko Texas, tentu saja, untuk membuat hidup lebih sulit. Dan kemudian melewati kami memiliki bulan, dan jika kami memiliki aktual untuk bulan itu, kami menunjukkan aktual, jadi Januari aktual, Februari aktual, Maret aktual, April aktual. Tapi kemudian untuk bulan-bulan di mana kami tidak memiliki aktual, kami beralih dan menunjukkan anggaran, jadi anggarkan hingga Desember, dan kemudian totalkan semuanya, oke. Nah, saat Anda mencoba dan membuat tabel Pivot ini, ya,itu tidak berhasil.
Jadi masukkan PivotTable, Lembar Kerja Baru, Anda meletakkan Simpan di sisi kiri, sisi yang indah, letakkan Bulan di atas, letakkan Jenis di bagian atas, letakkan Penjualan di sini, oke. Jadi inilah yang kami dapatkan yang harus kami mulai kerjakan, jadi kami memiliki rencana Januari aktual, rencana Januari, dan kemudian rencana plus aktual Januari yang sama sekali tidak berguna. Tidak ada yang akan pernah menggunakan ini, tapi saya bisa menyingkirkan kolom abu-abu ini, itu cukup mudah, beberapa di sini ke sel ini, buka Pengaturan Bidang, dan ubah Subtotal menjadi Tidak Ada. Tapi sama sekali tidak ada cara bagiku untuk menghapus rencana Januari yang juga tidak akan menghapus rencana April Mei Mei Juni, oke, tidak ada cara untuk menyingkirkan ini. Jadi pada titik ini setiap bulan, saya terjebak memilih seluruh tabel Pivot, pergi ke Salin, lalu Tempel, Tempel Nilai. Ini bukan tabel Pivot lagi,lalu saya mulai secara manual menghapus kolom yang tidak muncul dalam laporan.
Baiklah, itu metode normal, tetapi akuntan di Microsoft telah menambahkan langkah ekstra di bulan Januari, dibutuhkan 15 menit, dan langkah ini memungkinkan tabel Pivot ini untuk hidup selamanya, bukan? Saya menyebutnya tabel Pivot paling jelek di dunia, dan akuntan di Microsoft menerima bahwa ini adalah tabel Pivot paling jelek di dunia, tetapi tidak ada yang akan melihat laporan ini kecuali mereka. Apa yang mereka lakukan, apakah mereka datang ke sini untuk lembar baru, dan membuat laporan yang diinginkan manajer mereka. Baiklah, inilah toko-toko di sisi kiri, saya bahkan mengelompokkannya menjadi Houston, Dallas dan Other, ini adalah laporan yang diformat dengan baik. Saya telah menyoroti totalnya, Anda akan melihat bahwa ketika kami memasukkan beberapa angka, ada mata uang di baris pertama, tetapi bukan baris berikutnya ini, baris kosong. Ooh, baris kosong di tabel Pivot.Dan satu logika kecil di sini, di mana saya bisa memasukkan tanggal tayang di sel P1, dan kemudian saya memiliki rumus di sini yang menganalisis bahwa JIKA bulan tanggal tayang adalah> kolom ini, dan kemudian meletakkan kata Aktual, jika tidak letakkan kata Rencana, baiklah. Jadi yang harus saya lakukan adalah mengubah tanggal ini, lalu kata Aktual, balik ke rencana, Baiklah.
Sekarang, inilah yang kami lakukan, kami akan membiarkan diri kami menjadi GetPivotData, bukan? Saya tidak yakin itu kata kerja, tapi kami akan mengizinkan Microsoft untuk GetPivotData. Jadi saya mulai membuat formula dengan =, saya memegang mouse, dan saya akan mencari Baybrook bulan Januari yang sebenarnya! Jadi saya kembali ke tabel Pivot paling jelek di dunia, saya menemukan Baybrook, saya menemukan Januari, saya menemukan yang sebenarnya, dan saya klik Enter, dan biarkan mereka melakukannya untuk saya, oke, begitulah, kami sekarang memiliki rumus GetPivotData. Saya ingat hari saya melakukan ini, rasanya seperti, Anda tahu, setelah Rob menjelaskan kepada saya apa yang mereka lakukan, dan saya kembali dan mencobanya. Sekarang tiba-tiba, sepanjang hidup saya, saya telah menyingkirkan GetPivotData, saya tidak pernah benar-benar memeluk GetPivotData. Jadi apa itu, apakah barang pertama adalah apa yang kita cari, disana 'Sebuah bidang bernama Penjualan, di sinilah tabel Pivot dimulai, dan dapat berupa sel mana saja di tabel Pivot, mereka menggunakan tangan kiri atas.
Baiklah, ini adalah nama kolom "Store", lalu mereka telah melakukan hardcode "Baybrook", ini adalah nama kolom "Bulan", mereka telah melakukan hardcode "Januari", ini adalah nama kolom "Jenis", dan mereka ' ve hardcode "Actual". ITU mengapa Anda tidak dapat menyalinnya, karena nilainya telah di-hardcode. Tetapi akuntan di Microsoft, Carlos dan rekan kerjanya menyadari, “Wah, tunggu sebentar, kami memiliki kata Baybrook di sini, kami memiliki Januari di sini, kami memiliki Aktual di sini. Kami hanya perlu mengubah rumus ini untuk menunjuk ke sel sebenarnya dalam laporan alih-alih di-hardcode. ” Baiklah, jadi mereka menyebut ini parameterisasi GetPivotData.
Hapus kata Baybrook, datang ke sini dan klik di sel D6. Sekarang, saya perlu mengunci ini ke kolom, oke, jadi saya tekan tombol F4 3 kali, dapatkan satu $ sebelum D, oke. Untuk bulan Januari saya menghapus Januari yang di-hardcode, saya mengklik sel E3, saya akan menekan F4 dua kali untuk menguncinya ke baris, E $ 3. Ketik Aktual, hapus kata Aktual, klik E4, sekali lagi F4 dua kali, baiklah, dan saya mendapatkan rumus yang sekarang menarik data itu kembali. Saya akan menyalinnya, lalu Tempel Spesial, pilih Format, alt = "" ESF, lihat F digarisbawahi di sana, ESF Enter, lalu sekarang, saya sudah selesai, saya akan ulangi dengan F4, F4 adalah pengulangan, dan F4. Baiklah, jadi sekarang kita memiliki laporan yang tampak bagus, ada kosong, ada format, ada garis bawah akuntansi tunggal di bawah setiap bagian,di bagian paling bawah terdapat garis bawah akuntansi ganda.
Benar, Anda tidak pernah mendapatkan barang-barang ini di tabel Pivot, itu tidak mungkin, tapi laporan ini didorong dari tabel Pivot. Jadi apa yang kita lakukan saat mendapatkan bulan Mei yang sebenarnya, kembali ke sini, tempelkan, segarkan tabel Pivot paling jelek di dunia, lalu di sini di laporan cukup ubah tanggal tayang dari 30/4 menjadi 31/5. Dan apa yang dilakukannya adalah yang menyebabkan rumus ini beralih dari kata Rencana ke Aktual, yang menggantikan dan menarik aktual dari laporan, bukan dari rencana, oke. Nah, inilah hal yang- ini bagus, bukan? Saya dapat melihat di mana saya akan sering melakukan ini jika saya masih, Anda tahu, bekerja di bidang akuntansi.
Hal yang harus Anda perhatikan adalah jika mereka membangun toko baru, Anda harus tahu cara menambahkannya secara manual, benar, datanya akan muncul di tabel Pivot, tetapi Anda akan menambahkannya secara manual. Sekarang ini adalah bagian dari semua toko, jika itu melaporkan semua toko, saya mungkin di luar sini, di luar jangkauan cetak, memiliki sesuatu yang menarik total keseluruhan dari tabel Pivot. Dan kemudian saya akan tahu, jika total ini tidak cocok dengan total keseluruhan dari tabel Pivot, ada sesuatu yang salah, dan memiliki fungsi IF di sini. Mengatakan “Hai, ada, Anda tahu, data baru yang telah ditambahkan, berhati-hatilah. ” Mereka memiliki semacam mekanisme untuk mendeteksi bahwa data baru ada di sana. Tapi saya mengerti, itu penggunaan yang keren. Jadi, meskipun GetPivotData sering kali membuat kita gila, sebenarnya ada manfaatnya. Baik,Jadi itulah tip # 21 dari 40 di buku, beli buku sekarang juga, pesan online, klik “i” di pojok kanan atas.
Rekap panjang dan panjang hari ini, baiklah: GetPivotData terjadi saat rumus menunjuk di dalam tabel Pivot, rumus di luar tabel Pivot mengarah ke dalamnya. Meskipun rumus awal benar, rumus tidak akan disalin. Kebanyakan orang membenci GetPivotData dan ingin mencegahnya. Jadi Anda bisa membuat rumus tanpa mouse atau tombol panah, cukup ketikkan rumusnya, atau nonaktifkan GetPivotData secara permanen, ah, tapi ada gunanya, oke. Jadi kami harus membuat laporan dengan aktual untuk bulan lalu, anggaran untuk masa depan. Alur kerja normal, buat tabel Pivot, ubah menjadi nilai, Hapus kolom. Ada cara untuk menghapus subtotal dengan menggunakan pengaturan bidang, menyingkirkan rencana plus aktual Januari itu. Sebagai gantinya kita hanya akan membuat tabel Pivot paling jelek di dunia dengan terlalu banyak data.
Buat lembar kerja laporan lama yang diformat dengan baik dan biasa saja dengan mungkin sedikit logika untuk mengubah kata Aktual menjadi Rencana. Dan kemudian dari sel laporan pertama, tempat pertama di mana angka akan berada dalam laporan itu, ketik =, arahkan ke tabel Pivot dan biarkan GetPivotData terjadi. Kami memeriksa sintaks GetPivotData, jadi ini adalah bidang yang akan dikembalikan, Penjualan, tempat tabel Pivot berada, lalu pasangan kriteria, nama bidang, dan nilainya. Kami akan menghapus nilai hardcode dan menunjuk ke sel, menekan F4 3 kali hanya mengunci kolom, menekan F4 2 kali hanya mengunci baris, menyalin rumus itu, Tempel Rumus Khusus. Saya memberikan tip tambahan di sana bahwa F4 adalah ulangan, jadi saya hanya harus pergi ke dialog Tempel Spesial sekali, dan kemudian untuk Rumus Khusus Tempel berikutnya hanya menggunakan F4. Bulan depan tambahkan datanya,segarkan tabel Pivot, ubah tanggal tayang. Pastikan mereka tidak membangun toko baru, Anda tahu, memiliki semacam mekanisme, baik manual, atau rumus cek, periksa. Terima kasih kepada iTrainerMX di Twitter, yang menyarankan GetPivotData, juga Carlos dan Rob dari Microsoft, Rob sekarang dari Power Pivot Pro. Carlos untuk menggunakan ini, dan Rob untuk memberitahu saya bahwa Carlos menggunakannya, saya bertemu Carlos kemudian, dan dia mengkonfirmasi ya, dia adalah salah satu akuntan yang menggunakan ini sepanjang waktu di Microsoft, baiklah, ini dia.dan Rob karena memberi tahu saya bahwa Carlos menggunakannya, saya bertemu Carlos kemudian, dan dia mengonfirmasi ya, dia adalah salah satu akuntan yang menggunakan ini sepanjang waktu di Microsoft, baiklah, begitulah.dan Rob karena memberi tahu saya bahwa Carlos menggunakannya, saya bertemu Carlos kemudian, dan dia mengonfirmasi ya, dia adalah salah satu akuntan yang menggunakan ini sepanjang waktu di Microsoft, baiklah, begitulah.
Baiklah, saya ingin mengucapkan terima kasih telah mampir, sampai jumpa di lain waktu untuk netcast lain dari!
Unduh berkas
Unduh file contoh di sini: Podcast2013.xlsx
