Belanjawan dilakukan di peringkat teratas - pendapatan mengikut barisan produk mengikut wilayah mengikut bulan. Sebenar terkumpul perlahan dari masa ke masa - invois mengikut invois, item baris demi item baris. Membandingkan fail Anggaran kecil dengan data Sebenar yang besar telah menyakitkan selama-lamanya. Saya suka muslihat ini dari Rob Collie, aka PowerPivotPro.com.
Untuk menetapkan contohnya, anda mempunyai jadual anggaran 54 baris: 1 baris per bulan per wilayah per produk.
Fail invois berada pada tahap terperinci: sejauh ini 422 baris tahun ini.
Tidak ada VLOOKUP di dunia yang akan membiarkan anda sepadan dengan dua set data ini. Tetapi, terima kasih kepada Power Pivot (alias Model Data dalam Excel 2013+), ini menjadi mudah.
Anda perlu membuat jadual kecil yang saya panggil "joiners" untuk menghubungkan dua set data yang lebih besar.
Dalam kes saya, Produk, Wilayah, dan Tarikh sama antara kedua-dua jadual. Jadual Produk adalah jadual empat sel kecil. Ditto untuk Wilayah. Buat masing-masing dengan menyalin data dari satu jadual dan menggunakan Remove Duplicates.
Jadual kalendar di sebelah kanan sebenarnya lebih sukar untuk dibuat. Data anggaran mempunyai satu baris setiap bulan, selalu jatuh pada akhir bulan. Data invois menunjukkan tarikh harian, biasanya hari kerja. Oleh itu, saya terpaksa menyalin bidang Tarikh dari kedua-dua set data ke dalam satu lajur dan kemudian membuang pendua untuk memastikan bahawa semua tarikh ditunjukkan. Saya kemudian =TEXT(J4,"YYYY-MM")membuat kolum Bulan dari tarikh harian.
Sekiranya anda tidak mempunyai tambahan Power Pivot penuh, anda perlu membuat jadual pangsi dari jadual Anggaran dan pilih kotak pilihan untuk Menambah Data ini ke Model Data.
Seperti yang dibincangkan dalam petua sebelumnya, semasa anda menambahkan medan ke jadual pangsi, anda harus menentukan enam hubungan. Walaupun anda dapat melakukan ini dengan enam lawatan ke dialog Buat Hubungan, saya mengaktifkan tambahan Power Pivot saya dan menggunakan paparan gambar rajah untuk menentukan enam hubungan.
Inilah kunci untuk membuat semua ini berfungsi: Anda bebas menggunakan bidang angka dari Anggaran dan dari Sebenar. Tetapi jika anda ingin menunjukkan Wilayah, Produk, atau Bulan di dalam jadual pangsi, mereka mesti datang dari jadual penggabung!
Berikut adalah jadual pangsi dengan data yang berasal dari lima jadual. Lajur A berasal dari penggabung Wilayah. Baris 2 berasal dari Calendar joiner. Slicer Produk adalah dari produk Joiner. Nombor Anggaran berasal dari jadual Anggaran, dan Nombor Sebenar berasal dari jadual Invois.
Ini berfungsi kerana jadual penggabung menerapkan penapis pada jadual Belanjawan dan Sebenar. Ini adalah teknik yang indah dan menunjukkan bahawa Power Pivot bukan hanya untuk data besar.
