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.
Ngomong-ngomong, jika Anda tidak ingin fungsi GETPIVOTDATA muncul, cukup ketikkan rumus seperti =D5/C5-1tanpa 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 Jenis 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 terkompresi 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 January Actual untuk Baybrook. Klik di sel itu dan ketik tanda sama dengan.
Menggunakan mouse, navigasikan kembali ke tabel pivot. Temukan sel untuk Januari Aktual 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.
Gambar di bawah ini memperlihatkan rumus setelah Anda mengeditnya. Hilang sudah "Baybrook", "Jan", dan "Actual". Sebaliknya, Anda menunjuk ke $ D6, E $ 3, dan E $ 4.
Salin rumus ini lalu pilih Tempel Spesial, Rumus di semua sel numerik lainnya.
Sekarang, inilah alur kerja bulanan 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.
Anda harus mengakui bahwa menggunakan laporan 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.
