Menggunakan tabel pivot

Sebelumnya, saya sudah berbicara tentang fakta itu ketika mengacu pada sel tabel pivot alih-alih tautan normal, fungsi GET.DATA.PUMP.TABLE dikembalikan (lihat). Jika Anda tertarik sebagai untuk mengatasi ketidaknyamanan ini, saya sarankan untuk mengacu pada catatan yang disebutkan. Jika Anda bertanya-tanya, mengapa ini adalah bagaimana hal itu terjadi, dan juga apa aspek positif dari fungsi GET DATA OF THE PANEL.TABLE, maka saya menawarkan penggalan dari buku Jelen, Alexander. (bab 15). Teknik yang sedang dipertimbangkan akan memungkinkan Anda untuk mengatasi banyak masalah yang menyebabkan sakit kepala bagi pengguna tabel pivot, khususnya:

Saat PivotTable di-refresh, pemformatan yang diterapkan sebelumnya menghilang. Format angka hilang. Hasil penyesuaian lebar kolom menghilang.
Tidak ada jalan mudah membuat tabel pivot asimetris. Satu-satunya opsi adalah menggunakan kumpulan bernama, tetapi metode ini hanya tersedia bagi mereka yang menggunakan Tabel Pivot Model Data dan bukan Tabel Pivot biasa.
Excel tidak dapat mengingat template. Jika Anda perlu membuat ulang PivotTable berulang kali, Anda harus mengelompokkan ulang, menerapkan bidang terhitung dan anggota, dan melakukan sejumlah tugas serupa lainnya.

Faktanya, semua yang dijelaskan di sini bukanlah hal baru. Selain itu, teknik serupa telah digunakan sejak Excel 2002. Namun, komunikasi saya dengan pengguna menunjukkan bahwa kurang dari 1% yang mengenalnya. Satu-satunya pertanyaan yang dimiliki pengguna adalah bagaimana mematikan fungsi GET DATA OF PANEL.TABLE yang aneh. Itu sangat disayangkan…

Unduh catatan dalam format atau, contoh dalam format

Baiklah, mari kita mulai secara berurutan.

Bagaimana meninggalkan fungsi bermasalah GET DATA.PERSONAL TABLES

Fungsi GET PIVOTTABLE DATA telah lama memusingkan banyak pengguna. Tiba-tiba, tanpa peringatan apa pun, perilaku PivotTable berubah di Excel 2002. Segera setelah Anda mulai membuat rumus di luar PivotTable yang mereferensikan datanya, fungsi ini muncul begitu saja.

Misalkan pada tabel pivot yang ditunjukkan pada Gambar. 1, Anda perlu membandingkan data untuk tahun 2015 dan 2014.

Angka: 1. Tabel pivot asli

Tambahkan tajuk "% Height" ke sel D3.
Salin format dari sel C3 ke sel D3.
Masukkan tanda sama dengan di sel D4.
Klik pada sel C4.
Masukkan / (garis miring) untuk mewakili operasi pembagian.
Klik pada sel B4.
Masukkan -1 dan tekan kombinasi tombol untuk tinggal di sel yang sama. Format hasilnya sebagai persentase. Anda akan melihat bahwa Barat mengalami penurunan pendapatan sebesar 43,8% (Gambar 2). Hasil yang tidak terlalu bagus.
Saat Anda selesai memasukkan rumus pertama Anda, pilih sel D4.
Klik dua kali pada kotak kecil yang terletak di sudut kanan bawah sel. Kotak ini mewakili gagang isian yang bisa Anda gunakan untuk menyalin rumus untuk mengisi seluruh kolom dalam laporan.

Setelah Anda selesai menyalin rumus, melihat sekilas ke layar, Anda akan menyadari bahwa ada sesuatu yang salah - setiap wilayah menunjukkan penurunan sebesar 43,8% sepanjang tahun (Gbr. 3).

Angka: 3. Setelah Anda selesai menyalin rumus ke semua sel di kolom, Anda akan melihat bahwa setiap wilayah menunjukkan penurunan 43,8%

Ini tidak mungkin terjadi dalam kehidupan nyata. Siapa pun akan memberi tahu Anda bahwa setelah mengikuti langkah-langkah di atas, Excel akan membuat rumus \u003d C4 / B4-1. Kembali ke sel D4 dan perhatikan bilah rumus (Gambar 4). Hanya beberapa setan! Rumus sederhana \u003d C4 / B4-1 sudah tidak ada lagi. Alih-alih itu, program menggantikan konstruksi kompleks dengan fungsi GET.DATA.PUMP.TABLE. Mengapa rumus ini memberikan hasil yang benar di sel D4, tetapi setelah disalin ke sel di bawahnya menolak untuk bekerja?

Reaksi pertama dari setiap pengguna terhadap apa yang terjadi adalah sebagai berikut: "Apa konstruksi GET.DATA.PERSONAL.TABLE aneh yang merusak laporan saya?" Sebagian besar pengguna ingin segera menyingkirkan fitur ini. Beberapa orang akan mengajukan pertanyaan, "Mengapa Microsoft memberikan fitur ini kepada kami?"

Tidak ada yang seperti ini di waktu Excel 2000. Setelah mulai secara teratur menemukan fungsi GET.DATA.PERSONAL.TABLES, saya membencinya. Ketika di salah satu seminar seseorang bertanya kepada saya bagaimana itu bisa digunakan untuk kebaikan perjuangan, saya tercengang. Saya tidak pernah menanyakan diri saya pertanyaan seperti itu! Menurut pendapat saya, dan menurut sebagian besar pengguna Excel, fungsi GET.DATA.PUMP.TABLE adalah produk kejahatan yang tidak ada hubungannya dengan kekuatan kebaikan. Untungnya, ada dua cara untuk menonaktifkan fitur ini.

Mengunci fungsi GET PIVOTTABLE DATA dengan memasukkan rumus.Ada cara sederhana untuk mencegah fungsi GET PIVOTTABLE muncul. Untuk melakukan ini, Anda perlu membuat rumus tanpa menggunakan tombol mouse atau kursor. Ikuti saja langkah-langkah ini.

Pergi ke sel D4 dan ketik \u003d (tanda sama dengan).
Masukkan C4.
Masukkan / (garis miring untuk pembagian).
Masukkan B4.
Masukkan -1.
Klik Memasukkan.

Anda sekarang telah membuat reguler rumus Excel, yang dapat disalin ke sel di bawah kolom dan dengan itu Anda bisa mendapatkan hasil yang benar (Gbr. 5). Seperti yang Anda lihat, Anda dapat membuat rumus di area di luar PivotTable yang merujuk ke data di dalam PivotTable. Dan mereka yang tidak percaya bahwa ini mungkin, biarkan mereka melakukan tindakan yang dijelaskan sendiri.

Angka: 5. Cukup masukkan \u003d C4 / B4-1 dari keyboard, dan rumus akan berfungsi sesuai kebutuhan Anda

Beberapa pengguna akan merasa tidak nyaman dengan urutan biasa memasukkan rumus. Selain itu, opsi yang diusulkan lebih melelahkan. Jika Anda salah satu dari pengguna ini, cara kedua adalah untuk Anda ...

Nonaktifkan fungsi GET PANEL.TABLE DATA.Anda dapat menonaktifkan fungsi GET PANEL.TABLE DATA secara permanen. Klik pada menu ribbon Mengajukan → Parameter... Di jendela yang terbuka ParameterUnggul pergi ke kontribusi Rumus dan hapus centang pada kotak di sebelah opsi Gunakan fungsiGetPivotData untuk Tautan Tabel Pivot... Klik Baik.

Opsi alternatif. Klik di tabel pivot, dan di tab kontekstual yang muncul Analisis klik pada daftar drop-down di sebelah tombol Parameter... Hapus centang pada kotak di sebelah Buat GetPivotData (gbr. 7). Kotak centang aktif secara default.

Mengapa Microsoft menawarkan fitur GET PANEL.TABLES.Jika fitur ini sangat buruk, mengapa Microsoft mengaktifkannya secara default? Mengapa mereka peduli tentang mempertahankan dukungan untuk fitur ini di versi Excel yang lebih baru? Apakah mereka menyadari sentimen pengguna? Dan kami beralih ke bagian yang menyenangkan ...

Menggunakan DAPATKAN DATA PIVOTTABLE untuk Meningkatkan PivotTable

Tabel pivot adalah penemuan besar umat manusia. Tabel pivot dibuat hanya dengan beberapa klik, menghilangkan kebutuhan untuk menerapkan filter lanjutan, fungsi BDSUMM, dan tabel data. Dengan tabel pivot, Anda dapat membuat laporan satu halaman dari data dalam jumlah besar. Keunggulan ini membayangi beberapa kelemahan tabel pivot, seperti pemformatan yang tidak bersemangat dan kebutuhan untuk mengonversi tabel pivot menjadi nilai untuk penyesuaian tambahan. Dalam gambar. Gambar 8 menunjukkan proses umum untuk membuat tabel pivot. Dalam hal ini, semuanya dimulai dengan data awal. Kami membuat tabel pivot dan menggunakan semua teknik yang memungkinkan untuk menyesuaikan dan meningkatkannya. Terkadang kami mengonversi tabel pivot menjadi nilai dan melakukan beberapa pemformatan akhir.

Metodologi PivotTable baru yang diusulkan oleh Rob Colley (pengembang di Microsoft) dan dibahas nanti adalah hasil perbaikan proses yang dijelaskan di atas. Dalam hal ini, tabel pivot primitif dibuat terlebih dahulu. Anda tidak perlu memformat tabel ini. Kemudian satu langkah, proses yang relatif memakan waktu dilakukan untuk membuat shell yang diformat dengan baik yang akan menampung laporan akhir. Setelah itu, fungsi GET.DATA.PUMP.TABLE digunakan untuk dengan cepat mengisi laporan yang ada di shell dengan data. Setelah menerima data baru, Anda dapat meletakkannya di sheet, mengupdate tabel pivot primitif dan mencetak laporan yang ada di shell (Gambar 9). Teknik ini memiliki sejumlah keuntungan yang tak terbantahkan. Misalnya, Anda tidak perlu khawatir tentang pemformatan laporan Anda segera setelah Anda membuatnya. Proses pembuatan tabel pivot menjadi hampir sepenuhnya otomatis.

Bagian berikut membahas cara membuat laporan dinamis yang menampilkan angka aktual untuk bulan lalu dan target untuk bulan mendatang.

Buat tabel pivot primitif.Data awal (Gbr. 10) disajikan dalam bentuk transaksi yang berisi informasi mengenai indikator terencana dan aktual untuk setiap wilayah yang terdapat cabang perusahaannya. Angka yang direncanakan dirinci pada tingkat bulan, sedangkan angka aktual dirinci pada tingkat hari individu. Indikator yang direncanakan dibuat untuk tahun depan, dan indikator aktual dibuat untuk beberapa bulan terakhir. Karena laporan akan diperbarui setiap bulan, proses ini sangat disederhanakan jika sumber data PivotTable bertambah besar saat data baru ditambahkan ke bagian bawah. Dalam versi lama Pembuatan Excel sumber data serupa diimplementasikan menggunakan rentang dinamis bernama menggunakan fungsi OFFSET (lihat detail). Saat bekerja di Excel 2013, cukup pilih salah satu sel data dan tekan kombinasi tombol Ctrl + T (buat Tabel). Ini akan membuat kumpulan data bernama yang secara otomatis meluas saat baris dan kolom baru ditambahkan.

Sekarang mari buat tabel pivot. Fungsi GET PIVOTTABLE DATA cukup kuat, tetapi hanya dapat mengembalikan nilai yang muncul di PivotTable sebenarnya. Fungsi ini tidak dapat melakukan pemindaian cache untuk menghitung item yang tidak ada di tabel pivot.

Buat tabel pivot:

Pilih Tim Memasukkan → Tabel pivotlalu di dialog Membuat tabel pivotklik baik.
Di Daftar Bidang PivotTable, pilih bidang tanggal... Daftar tanggal akan muncul di sisi kiri tabel pivot (Gbr. 11).
Pilih sel tanggal mana saja, misalnya A4. Di tab kontekstual Analisisterletak di kumpulan tab kontekstual Bekerja dengan tabel pivot, klik tombolnya Pengelompokan berdasarkan bidang (lihat detailnya). Di kotak dialog Pengelompokan pilih salah satu opsi Bulan (gbr. 12). Klik baik... Nama bulan akan muncul di sisi kiri tabel pivot (Gbr. 13).
Seret bidang tersebut tanggal ke area Kolom PivotTable.
Seret bidang tersebut Indeks ke area Kolom dari Daftar Bidang PivotTable.
Pilih bidang Wilayahuntuk ditampilkan di kolom kiri tabel pivot.
Pilih bidang Pendapatanyang muncul di area Nilai PivotTable.

Angka: 11. Mulailah dengan mengelompokkan menurut bidang tanggal

Pada titik ini, tabel pivot kami terlihat sangat primitif (Gambar 14). Saya benar-benar tidak suka tulisan Nama baris dan Nama kolom... Tidak praktis untuk menampilkan total Rencana Jan dan Fakta Jan di kolom D, dll. Tapi jangan khawatir penampilan tabel pivot ini, karena selain Anda, tidak ada orang lain yang akan melihatnya. Mulai saat ini, kami akan membuat pembungkus laporan, yang sumber datanya akan menjadi tabel pivot yang baru dibuat.

Pembuatan shell laporan.Sisipkan lembar kosong ke dalam buku kerja Anda. Mari kita kesampingkan alat PivotTable untuk sementara waktu dan beralih ke alat Excel biasa. Tugas kita adalah menggunakan rumus dan pemformatan untuk membuat laporan indah yang tidak malu untuk ditampilkan kepada manajer.

Lanjutkan sebagai berikut (gbr. 15).

Di sel A1, masukkan nama laporan - Indikator terencana dan aktual menurut wilayah.
Buka tab rumah, klik tombolnya Gaya Sel pilih format Judul 1.
Di sel A2, masukkan rumusnya \u003d EONTHS (TODAY (); 0). Fungsi ini mengembalikan hari terakhir bulan ini. Misalnya, jika Anda membaca baris ini pada 14 Agustus 2014, sel A2 akan menampilkan tanggal 31 Agustus 2014.
Pilih sel A2. Tekan kombinasi tombol Ctrl + 1 untuk menampilkan kotak dialog Format sel... Di tab Jumlah klik item Semua format... Masukkan format angka kustom sebagai "Dari bulan" MMMM "merencanakan indikator" (gbr 16). Hasilnya, tanggal yang dihitung akan terlihat seperti teks.
Di sel A5 masukkan judul Wilayah.
Masukkan tajuk kawasan di sel yang tersisa di kolom A. Judul kawasan harus cocok dengan nama kawasan yang ditampilkan di tabel pivot.
Tambahkan label ke kolom untuk total menurut departemen, jika perlu.
Di bagian bawah laporan tambahkan baris Total untuk perusahaan.
Di sel B4, masukkan rumusnya \u003d DATE (YEAR ($ A $ 2); COLUMN (A1); 1). Rumus ini mengembalikan tanggal 01/01/2014, 01/02/2104, dll., Hari pertama dari semua 12 bulan pada tahun ini.
Pilih sel B4. Tekan kombinasi tombol Ctrl + 1 untuk membuka jendela Format sel... Di tab Jumlah Dalam bab Semua format masukkan format angka kustom Mmm... Format ini menampilkan nama bulan dengan tiga huruf. Sejajarkan teks di sebelah kanan sel.
Salin konten sel B4 ke kisaran C4: M4. Baris dengan nama bulan muncul di bagian atas tabel pivot.
Di sel B5, masukkan rumusnya \u003d IF (MONTH (B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Fakta, dan untuk saat ini dan masa depan - Rencana.
Tambahkan judul ke sel N5 Hasil... Ke sel O4 - Hasil, O5 - Rencana, P5 - % deviasi.
Masukkan rumus Excel biasa yang digunakan untuk menghitung total departemen, baris total perusahaan, kolom total keseluruhan, dan kolom% varian:
1. di sel B8, masukkan rumus \u003d SUM (B6: B7) dan salin ke sel lain di baris;
2. di sel N6, masukkan rumus \u003d SUM (B6: M6) dan salin ke sel lain di kolom;
3. di sel P6, masukkan rumusnya \u003d IFERROR ((N6 / O10) -1; 0) dan salin ke sel lain di kolom;
4. di sel B13, masukkan rumus \u003d SUM (B10: B12) dan salin ke sel lain di baris;
5. di sel B17 masukkan rumus \u003d SUM (B15: B16) dan salin ke sel lain di baris;
6. di sel B19 masukkan rumus \u003d SUM (B6: B18) / 2 dan salin ke sel lain di baris.
Terapkan gaya Judul 4 ke label di kolom A dan ke judul di baris 4 dan 5.
Untuk rentang sel B6: O19, pilih format angka # ## 0.
Untuk sel di kolom P, pilih format angka 0,0%.

Jadi, kami telah menyelesaikan pembuatan shell laporan yang ditunjukkan pada Gambar. 15. Laporan ini mencakup semua format yang diperlukan. Bagian selanjutnya menunjukkan cara menggunakan fungsi GET PIVOTTABLE DATA untuk menyelesaikan laporan.

Angka: 15. Laporkan pembungkus sebelum menambahkan rumus GET.DATA.PERSONAL.TABLES

Menggunakan fungsi GET PIVOTTABLE DATA untuk mengisi shell laporan dengan data.Mulai sekarang, Anda akan dapat merasakan semua manfaat menggunakan fungsi GET PANEL.TABLE DATA. Jika Anda telah mengosongkan kotak centang untuk mengaktifkan fungsi ini, kembali ke pengaturan yang sesuai dan kembalikan kotak centang (lihat keterangan pada Gambar 6 atau 7).

Pilih sel B6 dari shell laporan. Sel ini sesuai dengan wilayah Timur Laut dan angka aktual untuk Januari.

Masukkan \u003d (tanda sama dengan) untuk mulai memasukkan rumus.
Pergi ke lembar dari tabel pivot dan klik pada sel yang sesuai dengan wilayah timur laut dan angka aktual untuk Januari - C12 (Gbr. 17).
Tekan tombolnya Memasukkanuntuk menyelesaikan memasukkan rumus dan kembali ke shell laporan. Hasilnya, Excel akan menambahkan fungsi GET PIVOTTABLE DATA di sel B6. Sel akan menampilkan nilai $ 277.435.

Ingatlah angka ini, karena akan dibutuhkan saat membandingkan dengan hasil rumus, yang akan Anda edit nanti. Rumus yang dihasilkan oleh program memiliki bentuk sebagai berikut: \u003d GET.DATA.PUMP.TABLE ("Income"; 'Fig. 11-14 ′! $ A $ 3; "Region"; "North-East"; "Date" ; 1; "Indikator"; "Fakta"). Jika sejauh ini Anda telah mengabaikan fungsi GET PIVOTTABLE DATA, sekarang saatnya untuk melihat lebih dekat. Dalam gambar. 18 rumus ini ditampilkan dalam mode edit bersama dengan petunjuk.

Argumen fungsi:

Data_field. Bidang dari area nilai tabel pivot. Harap diperhatikan: dalam hal ini, field digunakan Pendapatan, tapi tidak Jumlah di bidang Pendapatan.
Tabel pivot. Dengan parameter ini, Microsoft bertanya kepada Anda, "Tabel pivot mana yang ingin Anda gunakan?" Cukup menentukan salah satu sel tabel pivot. Entri 'Fig. 11-14 '! $ A $ 3 mengacu pada sel pertama dalam tabel pivot tempat data dimasukkan. Karena dalam kasus kami, Anda dapat menentukan sel apa pun yang terkait dengan tabel pivot, biarkan argumen tidak berubah. Alamat sel $ A $ 3 cocok dalam segala hal.
Bidang 1; elemen 1. Dalam rumus yang dibuat secara otomatis, nama bidang dipilih Wilayah, dan sebagai nilai bidang - Timur laut... Di sinilah letak masalah yang muncul saat bekerja dengan fungsi GET DATA.PUMP.TABLE. Nilai yang dipilih secara otomatis tidak dapat disalin karena di-hardcode. Oleh karena itu, jika Anda menyalin rumus di seluruh area laporan, Anda harus mengubahnya secara manual. Ganti Northeast dengan referensi sel berbentuk $ A6. Dengan menempatkan tanda dolar di depan Kolom A, Anda menentukan apakah bagian baris dari referensi dapat diubah saat Anda menyalin rumus ke dalam sel di kolom.
Bidang 2; item 2. Pasangan argumen ini mendefinisikan bidang tanggal dengan nilai 1. Jika PivotTable asli dikelompokkan berdasarkan bulan, bidang bulan mempertahankan nama bidang asli tanggal... Nilai numerik bulan tersebut adalah 1, yang sesuai dengan Januari. Sangat tidak disarankan untuk menggunakan nilai seperti itu saat membuat rumus besar yang diatur dalam lusinan atau bahkan ratusan sel laporan. Lebih baik menggunakan rumus yang menghitung nilai bidang tanggal, seperti rumus di sel B4. Alih-alih 1 dalam kasus ini, Anda dapat menggunakan rumus MONTH (Dalam $ 4). Tanda dolar sebelum angka 4 menunjukkan bahwa rumus dapat menetapkan nilai ke bidang tanggal berdasarkan bulan lain saat rumus disalin ke dalam sel di baris.
Bidang 3; elemen 3. Dalam hal ini, nama bidang ditetapkan secara otomatis Indeks dan nilai bidang Fakta... Nilai ini benar untuk bulan Januari, tetapi untuk bulan-bulan berikutnya nilai bidang harus diubah ke Rencana. Ubah nilai hard-code dari bidang tersebut Fakta ke tautan B $ 5.
Bidang 4; Elemen 4. Argumen ini tidak digunakan karena ladang sudah berakhir.

Rumus baru ditunjukkan pada Gambar. 19. Dalam satu menit, alih-alih formula hard-code yang dirancang untuk bekerja dengan satu nilai, formula fleksibel telah dibuat yang dapat disalin ke semua sel dalam dataset. Tekan tombolnya Memasukkandan Anda akan mendapatkan hasil yang sama seperti sebelum mengedit rumus. Rumus yang diedit mengambil bentuk berikut: \u003d GET.PUMP.TABLE DATA ("Income"; 'Fig. 11-14 ′! $ A $ 3; "Region"; $ A6; "Date"; MONTH (B $ 4) ; "Indikator"; B $ 5)

Angka: 19. Setelah menyelesaikan pengeditan, rumus GET.DATA.PERSONAL.TABLE cocok untuk disalin ke semua sel rentang

Salin rumus ke sel kosong mana pun di kolom B: M tempat hasil dihitung. Sekarang karena laporan tersebut berisi bilangan real, Anda dapat membuat penyesuaian akhir pada lebar kolom.

Pada langkah selanjutnya, kita akan menyiapkan rumus GET.PUMP.TABLE untuk menghitung target akhir. Jika Anda baru saja menyalin rumus ke sel O6, pesan kesalahan #REF! Alasan kesalahan ini adalah karena kata tersebut Hasil di sel O4 bukan nama bulan. Untuk memastikan pengoperasian yang benar dari fungsi GET PIVOTTABLE DATA, nilai yang diperlukan harus ada di PivotTable. Tapi karena di tabel pivot asli, lapangan Indeks adalah bidang kedua di bidang kolom, kolom data Ringkasan rencana sebenarnya tidak ada. Pindahkan lapangan Indeks sehingga menjadi yang pertama di area kolom (Gambar 20).

Angka: 20. Sesuaikan tata letak bidang di area kolom sehingga kolom muncul Buat garis besar rencana

Bandingkan dengan ara. 14. Di sana, di area KOLOM, lapangan pertama adalah tanggal, yang mengarah pada fakta bahwa pada mulanya kolom dikelompokkan berdasarkan tanggal, dan di dalam setiap bulan berdasarkan rencana / fakta. Sekarang yang pertama adalah bidang Indikator, dan dalam ringkasan, kolomnya adalah yang pertama Rencana, di dalam diurutkan berdasarkan bulan dan kemudian semua kolom Fakta.

Kembali ke lembar amplop laporan, berdiri di sel O6, ketik \u003d (tanda sama dengan) dan lihat sel N12 pada lembar tabel pivot, sesuai dengan hasil yang direncanakan di wilayah Timur Laut. Klik Memasukkan... Hasilnya adalah rumusnya \u003d GET.DATA.PERSONAL.TABLES ("Pendapatan"; "Gbr. 11-14"! $ A $ 3; "Wilayah"; "Timur Laut"; "Indikator"; "Rencana"). Edit itu: \u003d GET. DATA.SUMMARY.TABLES ("Pendapatan"; ’Gambar 11-14 ′! $ A $ 3;" Wilayah "; $ A6;" Indikator "; O $ 5). Salin rumus ini ke sel lain di kolom O (Gambar 21). Harap dicatat bahwa meskipun Anda memindahkan area berbeda dari laporan PivotTable, pembungkusnya bekerja dengan benar. Tentu saja, jika Anda membuat beberapa bidang pivot tidak aktif, shell tidak akan mengatasi ini ...

Angka: 21. Laporan akhir yang dapat disajikan kepada manajer

Anda sekarang memiliki pembungkus laporan berformat baik yang menggunakan nilai dari PivotTable dinamis. Meskipun pembuatan awal laporan membutuhkan waktu yang cukup lama, pemutakhiran hanya membutuhkan waktu beberapa menit.

Perbarui laporan.Untuk memperbarui laporan dengan data untuk bulan-bulan mendatang, ikuti langkah-langkah berikut.

Masukkan angka-angka aktual di bawah kumpulan data asli. Karena data sumber dalam format tabel, pemformatan tabel secara otomatis disebarkan ke baris data baru. Ini juga memperluas definisi PivotTable asli (di file Excel, saya sudah menambahkan angka sebenarnya sepanjang tahun).
Buka tabel pivot. Klik kanan dan pilih Menyegarkan... Tampilan tabel pivot akan berubah, tapi tidak apa-apa.
Pergi ke shell laporan. Pada prinsipnya semua sudah dilakukan untuk mengupdate report tersebut, namun tidak ada salahnya untuk menguji hasilnya. Ubah rumus di sel A2, misalnya, menjadi ini: \u003d EON MONTHS (TODAY () +31 ; 0), dan lihat apa yang terjadi.

Dengan menambahkan data penjualan aktual baru setiap bulan, Anda tidak perlu khawatir tentang membuat ulang format, rumus, dll. Proses pemutakhiran laporan yang dijelaskan ini sangat sederhana sehingga Anda akan melupakan masalah yang muncul selama penyusunan laporan bulanan selamanya. Satu-satunya masalah dapat muncul jika perusahaan ditata ulang, akibatnya wilayah baru dapat muncul di tabel pivot. Untuk memastikan bahwa rumus bekerja dengan benar, periksa bahwa total keseluruhan dalam laporan Anda cocok dengan total dalam tabel pivot. Saat wilayah baru muncul, cukup tambahkan ke lembar terbungkus dan seret rumus yang sesuai.

Saya tidak berpikir saya akan mengatakan hal berikut: “Fungsi GET.DATA.PUMP.TABLE adalah berkah terbesar. Bagaimana kita bisa hidup tanpa dia sebelumnya? "

Dalam aslinya, data awal Jelena diatur sehingga rumus selanjutnya bekerja dengan benar hanya pada Juli 2015. Dalam file Excel yang dilampirkan pada catatan ini, saya memodifikasi data asli, serta beberapa rumus sehingga semuanya berfungsi, terlepas dari tanggal kapan Anda, Anda akan bereksperimen dengan file Excel terlampir. Sayangnya, rumusnya harus rumit.

Sebelumnya saya sudah berbicara tentang fakta bahwa ketika mereferensikan sel tabel pivot, alih-alih tautan normal, fungsi GET.PIVOTTABLE DATA dikembalikan (lihat). Jika Anda tertarik sebagai untuk mengatasi ketidaknyamanan ini, saya sarankan untuk mengacu pada catatan yang disebutkan. Jika Anda bertanya-tanya, mengapa ini adalah bagaimana hal itu terjadi, dan juga apa aspek positif dari fungsi GET DATA OF THE PANEL.TABLE, maka saya menawarkan penggalan dari buku Jelen, Alexander. (bab 15). Teknik yang sedang dipertimbangkan akan memungkinkan Anda untuk mengatasi banyak masalah yang menyebabkan sakit kepala bagi pengguna tabel pivot, khususnya:

Saat PivotTable di-refresh, pemformatan yang diterapkan sebelumnya menghilang. Format angka hilang. Hasil penyesuaian lebar kolom menghilang.
Tidak ada cara mudah untuk membuat PivotTable asimetris. Satu-satunya opsi adalah menggunakan kumpulan bernama, tetapi metode ini hanya tersedia bagi mereka yang menggunakan Tabel Pivot Model Data dan bukan Tabel Pivot biasa.
Excel tidak dapat mengingat template. Jika Anda perlu membuat ulang PivotTable berulang kali, Anda harus mengelompokkan ulang, menerapkan bidang terhitung dan anggota, dan melakukan sejumlah tugas serupa lainnya.

Unduh catatan dalam format atau, contoh dalam format

Baiklah, mari kita mulai secara berurutan.

Bagaimana meninggalkan fungsi bermasalah GET DATA.PERSONAL TABLES

Misalkan pada tabel pivot yang ditunjukkan pada Gambar. 1, Anda perlu membandingkan data untuk tahun 2015 dan 2014.

Angka: 1. Tabel pivot asli

Tambahkan tajuk "% Height" ke sel D3.
Salin format dari sel C3 ke sel D3.
Masukkan tanda sama dengan di sel D4.
Klik pada sel C4.
Masukkan / (garis miring) untuk mewakili operasi pembagian.
Klik pada sel B4.
Masukkan -1 dan tekan kombinasi tombol untuk tinggal di sel yang sama. Format hasilnya sebagai persentase. Anda akan melihat bahwa Barat mengalami penurunan pendapatan sebesar 43,8% (Gambar 2). Hasil yang tidak terlalu bagus.
Saat Anda selesai memasukkan rumus pertama Anda, pilih sel D4.
Klik dua kali pada kotak kecil yang terletak di sudut kanan bawah sel. Kotak ini mewakili gagang isian yang bisa Anda gunakan untuk menyalin rumus untuk mengisi seluruh kolom dalam laporan.

Setelah Anda selesai menyalin rumus, melihat sekilas ke layar, Anda akan menyadari bahwa ada sesuatu yang salah - setiap wilayah menunjukkan penurunan sebesar 43,8% sepanjang tahun (Gbr. 3).

Angka: 3. Setelah Anda selesai menyalin rumus ke semua sel di kolom, Anda akan melihat bahwa setiap wilayah menunjukkan penurunan 43,8%

Hal seperti ini tidak terjadi pada masa Excel 2000. Ketika saya mulai secara teratur menemukan fungsi GETDATA.PERSONAL.TABLES, saya hanya membencinya. Ketika di salah satu seminar seseorang bertanya kepada saya bagaimana itu bisa digunakan untuk kebaikan perjuangan, saya tercengang. Saya tidak pernah menanyakan pertanyaan seperti itu! Menurut pendapat saya, dan menurut sebagian besar pengguna Excel, fungsi GET.DATA.PUMP.TABLE adalah produk kejahatan yang tidak ada hubungannya dengan kekuatan kebaikan. Untungnya, ada dua cara untuk menonaktifkan fitur ini.

Pergi ke sel D4 dan ketik \u003d (tanda sama dengan).
Masukkan C4.
Masukkan / (garis miring untuk pembagian).
Masukkan B4.
Masukkan -1.
Klik Memasukkan.

Anda sekarang telah membuat rumus Excel biasa yang dapat Anda salin ke dalam sel kolom di bawah ini untuk mendapatkan hasil yang benar (Gambar 5). Seperti yang Anda lihat, Anda bisa membuat rumus di area di luar PivotTable yang merujuk ke data di dalam PivotTable. Dan mereka yang tidak percaya bahwa ini mungkin, biarkan mereka melakukan tindakan yang dijelaskan sendiri.

Angka: 5. Cukup masukkan \u003d C4 / B4-1 dari keyboard, dan rumus akan berfungsi sesuai kebutuhan Anda

Menggunakan DAPATKAN DATA PIVOTTABLE untuk Meningkatkan PivotTable

Bagian berikut membahas cara membuat laporan dinamis yang menampilkan angka aktual untuk bulan lalu dan target untuk bulan mendatang.

Buat tabel pivot primitif.Data awal (Gbr. 10) disajikan dalam bentuk transaksi yang berisi informasi tentang indikator terencana dan aktual untuk setiap wilayah yang terdapat cabang perusahaan. Angka yang direncanakan dirinci pada tingkat bulan, sedangkan angka aktual dirinci pada tingkat hari individu. Indikator yang direncanakan dibuat untuk tahun depan, dan indikator aktual dibuat untuk beberapa bulan terakhir. Karena laporan akan diperbarui setiap bulan, proses ini sangat disederhanakan jika sumber data PivotTable bertambah besar saat data baru ditambahkan ke bagian bawah. Di versi Excel yang lebih lama, sumber data semacam itu dibuat menggunakan rentang dinamis bernama menggunakan fungsi OFFSET (lihat detailnya). Saat bekerja di Excel 2013, cukup pilih salah satu sel data, dan tekan kombinasi tombol Ctrl + T (buat Tabel). Ini akan membuat kumpulan data bernama yang secara otomatis meluas saat baris dan kolom baru ditambahkan.

Buat tabel pivot:

Pilih Tim Memasukkan → Tabel pivotlalu di dialog Membuat tabel pivotklik baik.
Di Daftar Bidang PivotTable, pilih bidang tanggal... Daftar tanggal akan muncul di sisi kiri tabel pivot (Gbr. 11).
Pilih sel tanggal mana saja, misalnya A4. Di tab kontekstual Analisisterletak di kumpulan tab kontekstual Bekerja dengan tabel pivot, klik tombolnya Pengelompokan berdasarkan bidang (lihat detailnya). Di kotak dialog Pengelompokan pilih salah satu opsi Bulan (gbr. 12). Klik baik... Nama bulan akan muncul di sisi kiri tabel pivot (Gbr. 13).
Seret bidang tersebut tanggal ke area Kolom PivotTable.
Seret bidang tersebut Indeks ke area Kolom dari Daftar Bidang PivotTable.
Pilih bidang Wilayahuntuk ditampilkan di kolom kiri tabel pivot.
Pilih bidang Pendapatanyang muncul di area Nilai PivotTable.

Angka: 11. Mulailah dengan mengelompokkan menurut bidang tanggal

Pada titik ini, tabel pivot kami terlihat sangat primitif (Gambar 14). Saya benar-benar tidak suka tulisan Nama baris dan Nama kolom... Tidak praktis untuk menampilkan total Rencana Jan dan Fakta Jan di kolom D, dll. Tapi jangan khawatir dengan tampilan tabel pivot ini, karena tidak ada orang lain yang akan melihatnya kecuali Anda. Mulai saat ini, kami akan membuat pembungkus laporan, yang sumber datanya akan menjadi tabel pivot yang baru dibuat.

Lanjutkan sebagai berikut (gbr. 15).

Di sel A1, masukkan nama laporan - Indikator terencana dan aktual menurut wilayah.
Buka tab rumah, klik tombolnya Gaya Sel pilih format Judul 1.
Di sel A2, masukkan rumusnya \u003d EONTHS (TODAY (); 0). Fungsi ini mengembalikan hari terakhir bulan ini. Misalnya, jika Anda membaca baris ini pada 14 Agustus 2014, sel A2 akan menampilkan tanggal 31 Agustus 2014.
Pilih sel A2. Tekan kombinasi tombol Ctrl + 1 untuk menampilkan kotak dialog Format sel... Di tab Jumlah klik item Semua format... Masukkan format angka kustom sebagai "Dari bulan" MMMM "merencanakan indikator" (gbr 16). Hasilnya, tanggal yang dihitung akan terlihat seperti teks.
Di sel A5 masukkan judul Wilayah.
Masukkan tajuk kawasan di sel yang tersisa di kolom A. Judul kawasan harus cocok dengan nama kawasan yang ditampilkan di tabel pivot.
Tambahkan label ke kolom untuk total menurut departemen, jika perlu.
Di bagian bawah laporan tambahkan baris Total untuk perusahaan.
Di sel B4, masukkan rumusnya \u003d DATE (YEAR ($ A $ 2); COLUMN (A1); 1). Rumus ini mengembalikan tanggal 01/01/2014, 01/02/2104, dll., Hari pertama dari semua 12 bulan pada tahun ini.
Pilih sel B4. Tekan kombinasi tombol Ctrl + 1 untuk membuka jendela Format sel... Di tab Jumlah Dalam bab Semua format masukkan format angka kustom Mmm... Format ini menampilkan nama bulan dengan tiga huruf. Sejajarkan teks di sebelah kanan sel.
Salin konten sel B4 ke kisaran C4: M4. Baris dengan nama bulan muncul di bagian atas tabel pivot.
Di sel B5, masukkan rumusnya \u003d IF (MONTH (B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Fakta, dan untuk saat ini dan masa depan - Rencana.
Tambahkan judul ke sel N5 Hasil... Ke sel O4 - Hasil, O5 - Rencana, P5 - % deviasi.
Masukkan rumus Excel biasa yang digunakan untuk menghitung total departemen, baris total perusahaan, kolom total keseluruhan, dan kolom% varian:
1. di sel B8, masukkan rumus \u003d SUM (B6: B7) dan salin ke sel lain di baris;
2. di sel N6, masukkan rumus \u003d SUM (B6: M6) dan salin ke sel lain di kolom;
3. di sel P6, masukkan rumusnya \u003d IFERROR ((N6 / O10) -1; 0) dan salin ke sel lain di kolom;
4. di sel B13, masukkan rumus \u003d SUM (B10: B12) dan salin ke sel lain di baris;
5. di sel B17 masukkan rumus \u003d SUM (B15: B16) dan salin ke sel lain di baris;
6. di sel B19 masukkan rumus \u003d SUM (B6: B18) / 2 dan salin ke sel lain di baris.
Terapkan gaya Judul 4 ke label di kolom A dan ke judul di baris 4 dan 5.
Untuk rentang sel B6: O19, pilih format angka # ## 0.
Untuk sel di kolom P, pilih format angka 0,0%.

Angka: 15. Laporkan pembungkus sebelum menambahkan rumus GET.DATA.PERSONAL.TABLES

Pilih sel B6 dari shell laporan. Sel ini sesuai dengan wilayah Timur Laut dan angka aktual untuk Januari.

Masukkan \u003d (tanda sama dengan) untuk mulai memasukkan rumus.
Pergi ke lembar dari tabel pivot dan klik pada sel yang sesuai dengan wilayah timur laut dan angka aktual untuk Januari - C12 (Gbr. 17).
Tekan tombolnya Memasukkanuntuk menyelesaikan memasukkan rumus dan kembali ke shell laporan. Hasilnya, Excel akan menambahkan fungsi GET PIVOTTABLE DATA di sel B6. Sel akan menampilkan nilai $ 277.435.

Argumen fungsi:

Data_field. Bidang dari area nilai tabel pivot. Harap diperhatikan: dalam hal ini, field digunakan Pendapatan, tapi tidak Jumlah di bidang Pendapatan.
Tabel pivot. Dengan parameter ini, Microsoft bertanya kepada Anda, "Tabel pivot mana yang ingin Anda gunakan?" Cukup menentukan salah satu sel tabel pivot. Entri 'Fig. 11-14 '! $ A $ 3 mengacu pada sel pertama dalam tabel pivot tempat data dimasukkan. Karena dalam kasus kami, Anda dapat menentukan sel apa pun yang terkait dengan tabel pivot, biarkan argumen tidak berubah. Alamat sel $ A $ 3 cocok dalam segala hal.
Bidang 1; elemen 1. Dalam rumus yang dibuat secara otomatis, nama bidang dipilih Wilayah, dan sebagai nilai bidang - Timur laut... Di sinilah letak masalah yang muncul saat bekerja dengan fungsi GET DATA.PUMP.TABLE. Nilai yang dipilih secara otomatis tidak dapat disalin karena di-hardcode. Oleh karena itu, jika Anda menyalin rumus di seluruh area laporan, Anda harus mengubahnya secara manual. Ganti Northeast dengan referensi sel berbentuk $ A6. Dengan menempatkan tanda dolar di depan Kolom A, Anda menentukan apakah bagian baris dari referensi dapat diubah saat Anda menyalin rumus ke dalam sel di kolom.
Bidang 2; item 2. Pasangan argumen ini mendefinisikan bidang tanggal dengan nilai 1. Jika PivotTable asli dikelompokkan berdasarkan bulan, bidang bulan mempertahankan nama bidang asli tanggal... Nilai numerik bulan tersebut adalah 1, yang sesuai dengan Januari. Sangat tidak disarankan untuk menggunakan nilai seperti itu saat membuat rumus besar yang diatur dalam lusinan atau bahkan ratusan sel laporan. Lebih baik menggunakan rumus yang menghitung nilai bidang tanggal, seperti rumus di sel B4. Alih-alih 1 dalam kasus ini, Anda dapat menggunakan rumus MONTH (Dalam $ 4). Tanda dolar sebelum angka 4 menunjukkan bahwa rumus dapat menetapkan nilai ke bidang tanggal berdasarkan bulan lain saat rumus disalin ke dalam sel di baris.
Bidang 3; elemen 3. Dalam hal ini, nama bidang ditetapkan secara otomatis Indeks dan nilai bidang Fakta... Nilai ini benar untuk bulan Januari, tetapi untuk bulan-bulan berikutnya nilai bidang harus diubah ke Rencana. Ubah nilai hard-code dari bidang tersebut Fakta ke tautan B $ 5.
Bidang 4; Elemen 4. Argumen ini tidak digunakan karena ladang sudah berakhir.

Angka: 19. Setelah menyelesaikan pengeditan, rumus GET.DATA.PERSONAL.TABLE cocok untuk disalin ke semua sel rentang

Salin rumus ke sel kosong mana pun di kolom B: M tempat hasil dihitung. Sekarang karena laporan tersebut berisi bilangan real, Anda dapat membuat penyesuaian akhir pada lebar kolom.

Angka: 20. Sesuaikan tata letak bidang di area kolom sehingga kolom muncul Buat garis besar rencana

Angka: 21. Laporan akhir yang dapat disajikan kepada manajer

Perbarui laporan.Untuk memperbarui laporan dengan data untuk bulan-bulan mendatang, ikuti langkah-langkah berikut.

Masukkan angka-angka aktual di bawah kumpulan data asli. Karena data sumber dalam format tabel, pemformatan tabel secara otomatis disebarkan ke baris data baru. Ini juga memperluas definisi PivotTable asli (di file Excel, saya sudah menambahkan angka sebenarnya sepanjang tahun).
Buka tabel pivot. Klik kanan dan pilih Menyegarkan... Tampilan tabel pivot akan berubah, tapi tidak apa-apa.
Pergi ke shell laporan. Pada prinsipnya semua sudah dilakukan untuk mengupdate report tersebut, namun tidak ada salahnya untuk menguji hasilnya. Ubah rumus di sel A2, misalnya, menjadi ini: \u003d EON MONTHS (TODAY () +31 ; 0), dan lihat apa yang terjadi.

Saya tidak berpikir saya akan mengatakan hal berikut: “Fungsi GET.DATA.PUMP.TABLE adalah berkah terbesar. Bagaimana kita bisa hidup tanpa dia sebelumnya? "

Untuk tabel pivot, fungsi GET.DATA.PIVOTTABLE adalah fungsi yang mengembalikan data yang disimpan dalam laporan tabel pivot.

Untuk mendapatkan akses cepat ke fungsi tersebut, Anda harus memasukkan tanda sama dengan di sel (\u003d) dan memilih sel yang diperlukan dalam tabel pivot. Excel akan menghasilkan fungsi GET PIVOTTABLE DATA secara otomatis.

Nonaktifkan pembuatan GetPivotData

Untuk mematikan pembuatan otomatis fungsi GET PIVOTTABLE, pilih sel mana saja di tabel pivot, klik tab Bekerja dengan tabel pivot -\u003e Opsike grup Tabel pivot.Klik panah bawah di samping tab Parameter.Di menu drop-down, hapus centang pada kotak MembuatGetPivotData.

Menggunakan Referensi Sel dalam fungsi GET DATA.PUMP.TABLE

Daripada menentukan nama item atau bidang dalam fungsi GET.DATA.PUMP.TABLE, Anda dapat merujuk ke sel di lembar kerja. Dalam contoh di bawah ini, sel E3 berisi nama produk, dan rumus di sel E4 merujuk padanya. Hasilnya, total volume kue akan dikembalikan.

Menggunakan Referensi Bidang PivotTable

Tidak ada pertanyaan tentang bagaimana tautan ke item tabel pivot bekerja, masalah muncul jika kita ingin merujuk ke bidang data.

Dalam contoh, sel E3 berisi nama bidang data "Kuantitas", dan alangkah baiknya untuk merujuk ke sel ini dalam fungsi, daripada menggunakan nama bidang dalam rumus GET.DATA.PUMP.TABLE.

Namun, jika kita mengubah argumen pertama data_fieldke referensi ke sel E3, Excel akan mengembalikan kesalahan #REF!

GET.PERSONAL.TABLE DATA (E3; $ A $ 3)

Masalahnya diselesaikan hanya dengan menambahkan string kosong ("") ke awal atau akhir referensi sel.

GET.PERSONAL.TABLE DATA (E3 & ""; $ A $ 3)

Koreksi sederhana pada rumus akan mengembalikan nilai yang benar.

Menggunakan tanggal dalam fungsi GET.DATA.PUMP.TABLE

Jika Anda menggunakan tanggal dalam fungsi GET PIVOTTABLE DATA, Anda mungkin mengalami masalah meskipun tanggal tersebut ditampilkan di PivotTable. Misalnya, rumus di bawah ini adalah tanggal "04/21/2013" dan tabel pivot berisi bidang dengan tanggal penjualan. Namun, rumus di sel E4 mengembalikan kesalahan.

GET.DATA.PERSONAL.TABLE ("Volume"; $ A $ 3; "Tanggal"; "04/21/2013")

Untuk menghindari kesalahan terkait tanggal, Anda dapat menggunakan salah satu dari metode berikut ini:

Bandingkan Format Tanggal dalam Rumus dan Tabel Pivot
Gunakan fungsi DATEVALUE
Gunakan fungsi DATE
Lihat sel dengan tanggal yang benar

Bandingkan format tanggal dalam rumus dan tabel pivot.

Untuk mendapatkan hasil yang benar, saat menggunakan fungsi GET PIVOTTABLE DATA, pastikan format tanggal dari argumen rumus dan PivotTable sama.

Di sel E4, rumus menggunakan format tanggal "DD.MM.YYYY", dan akibatnya, informasi yang benar dikembalikan.

Menggunakan fungsi DATEVALUE

Daripada memasukkan tanggal secara manual dalam rumus, Anda dapat menambahkan fungsi DATEVALUE untuk mengembalikan tanggal.

Di sel E4, tanggal dimasukkan menggunakan fungsi DATEVALUE, dan Excel mengembalikan informasi yang diperlukan.

RECEIVE.SUMMARY.TABLE.DATA ("Volume"; $ A $ 3; "Date"; DATEVALUE ("04.21.2013"))

Menggunakan fungsi DATE

Daripada memasukkan tanggal secara manual dalam rumus, Anda bisa menggunakan fungsi DATE, yang akan mengembalikan informasi yang diperlukan dengan benar.

RECEIVE.SUMMARY.TABLE.DATA ("Volume"; $ A $ 3; "Date"; DATE (2013; 4; 21))

Referensi sel tanggal

Alih-alih memasukkan tanggal secara manual dalam rumus, Anda bisa merujuk ke sel yang berisi tanggal (dalam format apa pun yang diinterpretasikan Excel data sebagai tanggal). Dalam contoh di sel E4, rumus tersebut merujuk ke sel E3 dan Excel mengembalikan data yang benar.

RECEIVE.SUMMARY.TABLE.DATA ("Volume"; $ A $ 3; "Tanggal"; E3)