Anggaran dilakukan di tingkat atas - pendapatan menurut lini produk menurut wilayah menurut bulan. Akumulasi menumpuk perlahan dari waktu ke waktu - faktur demi faktur, baris demi baris. Membandingkan berkas Anggaran yang kecil dengan data Aktual yang banyak telah merepotkan selamanya. Saya suka trik ini dari Rob Collie, alias PowerPivotPro.com.
Untuk menyiapkan contoh, Anda memiliki tabel anggaran 54 baris: 1 baris per bulan per kawasan per produk.
File faktur ada di tingkat detail: 422 baris sepanjang tahun ini.
Tidak ada VLOOKUP di dunia yang akan mengizinkan Anda mencocokkan kedua kumpulan data ini. Namun, berkat Power Pivot (alias Model Data di Excel 2013+), ini menjadi mudah.
Anda perlu membuat tabel kecil yang saya sebut "penggabung" untuk menghubungkan dua kumpulan data yang lebih besar.
Dalam kasus saya, Produk, Wilayah, dan Tanggal sama di antara dua tabel. Tabel Produk adalah tabel empat sel kecil. Ditto untuk Region. Buat masing-masing dengan menyalin data dari satu tabel dan menggunakan Hapus Duplikat.
Tabel kalender di sebelah kanan sebenarnya lebih sulit dibuat. Data anggaran memiliki satu baris per bulan, selalu jatuh pada akhir bulan. Data invoice menunjukkan tanggal harian, biasanya hari kerja. Jadi, saya harus menyalin bidang Tanggal dari kedua kumpulan data ke dalam satu kolom dan kemudian menghapus duplikat untuk memastikan bahwa semua tanggal terwakili. Saya kemudian biasa =TEXT(J4,"YYYY-MM")membuat kolom Bulan dari tanggal harian.
Jika Anda tidak memiliki add-in Power Pivot penuh, Anda perlu membuat tabel pivot dari tabel Anggaran dan memilih kotak centang untuk Tambahkan Data Ini ke Model Data.
Seperti dibahas di tip sebelumnya, saat Anda menambahkan bidang ke tabel pivot, Anda harus menentukan enam hubungan. Meskipun Anda dapat melakukan ini dengan enam kunjungan ke dialog Buat Hubungan, saya menjalankan add-in Power Pivot saya dan menggunakan tampilan diagram untuk menentukan enam hubungan.
Berikut adalah kunci untuk membuat semua ini berfungsi: Anda bebas menggunakan bidang numerik dari Anggaran dan dari Aktual. Tetapi jika Anda ingin memperlihatkan Wilayah, Produk, atau Bulan di tabel pivot, mereka harus berasal dari tabel penghubung!
Berikut adalah tabel pivot dengan data yang berasal dari lima tabel. Kolom A berasal dari joiner Wilayah. Baris 2 berasal dari penyambung Kalender. Pemotong Produk berasal dari penggabung Produk. Angka Anggaran berasal dari tabel Anggaran, dan angka Aktual berasal dari tabel Faktur.
Ini berfungsi karena tabel penghubung menerapkan filter ke tabel Anggaran dan Aktual. Ini adalah teknik yang bagus dan memperlihatkan bahwa Power Pivot bukan hanya untuk data besar.
