Model Data Excel (Power Pivot) memungkinkan Anda untuk menyambungkan kumpulan data detail yang besar ke anggaran tingkat atas dengan menggunakan tabel penghubung.
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 file 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: satu 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 mencentang kotak 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.
Menonton video
- Anda memiliki kumpulan data anggaran atas-bawah yang kecil
- Anda ingin membandingkan dengan kumpulan data bottom-up aktual
- Yang sebenarnya mungkin berasal dari daftar faktur
- Model data akan memungkinkan Anda membandingkan kumpulan data dengan ukuran berbeda ini
- Buat kedua kumpulan data menjadi tabel Ctrl + T.
- Untuk setiap bidang teks yang ingin Anda laporkan, buat tabel penghubung
- Salin nilai dan hapus duplikat
- Untuk tanggal, Anda dapat menyertakan tanggal dari kedua tabel dan mengubahnya menjadi akhir bulan
- Jadikan penggabung menjadi tabel Ctrl + T.
- Opsional tetapi bermanfaat untuk memberi nama kelima tabel
- Buat tabel pivot dari Anggaran dan pilih Model Data
- Buat tabel pivot menggunakan Anggaran dan Aktual dari tabel asli
- Semua bidang lainnya harus berasal dari tabel penghubung
- Tambahkan pemotong menurut Produk
- Buat tiga hubungan dari Anggaran ke Penggabung
- Ciptakan tiga hubungan dari Actual to Joiners
- Besok: bagaimana membangun hubungan lebih mudah dengan rumus Power Pivot dan DAX
Transkrip Video
Pelajari Excel dari podcast, episode 2016 - Anggaran Top-Down vs Aktual Bottom-Up!
Hai, saya membuat podcasting seluruh buku ini, klik "i" di pojok kanan atas dan ikuti playlist.
Hei, saya akan menghentikan ini, ini Bill Jelen mulai 15 menit dari sekarang. Saya menyadari sekarang ini adalah podcast yang sangat panjang, dan Anda tergoda untuk mengkliknya saja, tapi izinkan saya memberi Anda sesingkat ini. Jika Anda menggunakan Excel 2013, dan Anda pernah memiliki tabel anggaran kecil dan tabel aktual yang sangat besar, dan Anda perlu memetakannya bersama-sama, ini adalah kemampuan baru yang luar biasa yang kami miliki di Excel 2013, yang tidak dijelaskan banyak orang , dan Anda mungkin tidak mengetahuinya. Jika ini Anda, Anda di tahun 2013, dan Anda perlu memetakan kedua kumpulan data ini, luangkan waktu, mungkin hari ini, mungkin besok, mungkin tambahkan ke daftar pantauan, itu sepadan, ini teknik yang luar biasa.
Baiklah, inilah yang kami miliki, di sisi kiri kami memiliki anggaran, anggaran ini, dilakukan di tingkat atas, dari atas ke bawah, tepat untuk setiap lini produk, untuk setiap wilayah, untuk setiap bulan, ada anggaran . Tidak banyak catatan di sini, hitungan 55, di sisi kanan kami mencoba membandingkan ini dengan yang sebenarnya. Yang sebenarnya berasal dari daftar faktur, jadi kami memiliki Wilayah, Produk, dan Pendapatan, tetapi itu adalah faktur individual, lebih banyak data di sini, kami sudah setengah tahun, dan saya sudah memiliki 423 catatan. Baiklah, jadi bagaimana Anda memetakan 55 ini ke 423 ini? Mungkin sulit dilakukan dengan VLOOKUP, Anda harus meringkasnya terlebih dahulu, tetapi untungnya di Excel 2013, model data membuatnya sangat, sangat mudah. Yang kita butuhkan untuk memungkinkan tabel besar dan besar ini untuk berkomunikasi dengan tabel kecil ini adalah perantara, saya menyebutnya penggabung.Tabel kecil kecil, Produk, Kawasan, dan Kalender, kita akan menggabungkan anggaran ke tiga tabel ini, kita akan menggabungkan yang sebenarnya ke tiga tabel ini, dan secara ajaib tabel Pivot akan berfungsi. Baiklah, jadi begini cara kami melakukannya.
Pertama-tama saya perlu membuat penggabung, jadi saya mengambil bidang Produk ini dari kolom A, dan saya menyalinnya ke kolom F, lalu Data, Hapus Duplikat, klik OK, dan kami memiliki tabel kecil kecil, 1 judul 3 baris. Hal yang sama untuk Wilayah, ambil wilayahnya, Ctrl + C, pergi ke kolom G, Tempel, Hapus Duplikat, klik OK, header 3 baris 1, baiklah. Sekarang untuk tanggal, tanggalnya tidak sama, ini adalah tanggal akhir bulan, sebenarnya disimpan sebagai tanggal akhir bulan, dan ini adalah hari kerja. Saya akan mengambil kedua daftar, Ctrl + C daftar kedua dan menempelkannya di sini, Ctrl + V, lalu saya akan mengambil daftar yang lebih pendek, menyalinnya, dan menempelkannya di bawah, oke. Dan sangat menjengkelkan bahwa, meskipun ini disimpan sebagai tanggal, mereka muncul sebagai bulan, dan Hapus Duplikat tidak akan melihatnya sama.Jadi sebelum saya menggunakan Remove Duplicates, saya harus mengubahnya menjadi tanggal yang singkat. Pilih data itu, Data, Hapus Duplikat, klik OK, dan kemudian sedikit urutkan di sini untuk membuatnya berfungsi.
Baiklah, sekarang saya tidak ingin melaporkan menurut tanggal harian, jadi saya akan menambahkan kolom di sini, kolom pencarian yang bertuliskan Bulan, dan ini akan sama dengan EOMONTH tanggal itu,, 0, yang akan membuat kita keluar akhir bulan. Ini akan memformatnya sebagai tanggal pendek, dan menyalinnya, oke. Sekarang, kita perlu membuat masing-masing ini menjadi tabel Ctrl + T, jadi dari sini Ctrl + T, Tabel saya memiliki header, cantik. Yang kecil, tidak disadari itu adalah tajuk di sana, jadi kita harus memastikan untuk menandai itu dan Ctrl + T, oke, dan mereka menyebut tabel ini Tabel1, Tabel2, Tabel3, nama yang sangat membosankan, bukan? Jadi saya akan mengganti nama ini dan menyebutnya BudTable, ProdTable, RegTable, CalTable saya, dan kemudian ActTable, oke.
Kita mulai dari tabel pertama, dan ngomong-ngomong kita tidak akan menggunakan PowerPivot hari ini, kita akan melakukan semua ini dengan model data. Jadi, Excel 2013 atau yang lebih baru, Anda memiliki Sisipan ini, PivotTable, kami akan mencentang kotak untuk "Tambahkan data ini ke Model Data", klik OK, dan kami mendapatkan daftar bidang kami dengan tombol Semua ajaib, yang memungkinkan saya memilih dari kelima tabel di buku kerja, Aktual, Anggaran, Kalender, Produk, Kawasan. Baiklah, jadi angkanya akan berasal dari tabel Anggaran, saya akan memasukkan anggaran di sana, dan dari tabel Aktual saya akan memasukkan yang sebenarnya di sana, tapi ini hal untuk tabel Pivot lainnya. Bidang teks lain yang akan kita letakkan di area baris atau area kolom atau sebagai pemotong, mereka harus berasal dari penyambung, mereka harus berasal dari tabel tersebut di antara tabel.
Baiklah, jadi dari tabel Kalender kita akan mengambil bidang Bulan itu dan meletakkannya di bagian atas, kita akan mengabaikan hubungan lain sekarang. Saya akan menciptakan hubungan, tetapi saya ingin membuat semuanya sekaligus. Dan tabel Wilayah, letakkan wilayah di samping. Saya bisa meletakkan produk di samping, tetapi saya sebenarnya akan menggunakan tabel Produk sebagai pemotong, jadi Analisis, Sisipkan Pemotong, sekali lagi Anda harus pergi ke Semua jika Anda belum menggunakan tabel Produk. Jadi buka Semua, dan Anda akan melihat bahwa Produk tersedia untuk dibuat sebagai pemotong dari produk, seperti itu. Baiklah sekarang, saat ini kita belum membuat hubungan, jadi semua angka ini salah. Dan hubungan yang harus kita buat, kita harus membuat 3 tabel dari tabel anggaran kecil ini, satu ke produk, satu ke kawasan, satu ke kalender,itulah 3 hubungan. Dan kemudian kita harus membuat hubungan dari tabel Aktual ke kawasan Produk di Kalender, jadi total 6 tabel. Dan ya, ini pasti akan lebih mudah jika kita memiliki PowerPivot, tapi kita tidak atau anggap saja tidak.
Jadi saya akan menggunakan cara lama, dialog Buat di sini, di mana kita memiliki tabel Anggaran di sebelah kiri, dan kita akan menggunakan bidang Wilayah dan menghubungkannya dengan tabel Wilayah, bidang Wilayah. . Baiklah, 1/6 sudah dibuat. Saya akan memilih Buat, lagi dari tabel Anggaran kita pergi ke Produk, dan kemudian tautkan ke tabel Produk, ke Produk, klik OK. Dari tabel Anggaran bidang Tanggal, kami pergi ke tabel Kalender, dan bidang Nasib, klik OK, kami setengah jalan, baiklah. Dari tabel Aktual, kita masuk Kawasan, ke tabel Kawasan, klik OK, dari tabel Aktual ke Produk, dan dari tabel Aktual ke Kalender. Saya benar-benar akan mengambil Nilai-Nilai dan membuatnya turun ke samping, oke. Desain, Tata Letak Laporan, Perlihatkan dalam Bentuk Tabular untuk mendapatkan tampilan yang saya sukai, Ulangi Semua Label Item, baiklah,ini benar-benar luar biasa! Sekarang kita memiliki tabel kecil kecil ini, 50-an catatan dalam tabel ratusan catatan ini, dan kita telah membuat tabel Pivot tunggal berkat Model Data. Untuk setiap tempat kita dapat melihat Anggaran, kita dapat melihat Pendapatan, itu dikelompokkan berdasarkan Wilayah, itu dikelompokkan berdasarkan Bulan, dan itu dapat diiris berdasarkan Produk.
Sekarang konsep ini datang kepada saya dari Rob Collie yang menjalankan Power Pivot Pro, dan Rob telah membuat banyak buku di luar sana, yang terbaru adalah "Power Pivot dan Power BI". Saya pikir yang ini sebenarnya ada di buku "Power Pivot Alchemy", yang saya lihat ini dan saya berkata, "Yah, ini, meskipun saya tidak memiliki jutaan baris untuk dilaporkan melalui Power Pivot, ini adalah salah satu yang akan telah membuat perbedaan BESAR dalam hidup saya, memiliki dua kumpulan data dengan ukuran yang tidak cocok, dan perlu melaporkan dari keduanya. ” Nah contoh ini dan banyak lainnya ada di buku ini, saya akhirnya akan mendapatkan seluruh podcast buku, sepertinya itu akan memakan waktu dua setengah bulan. Tapi Anda bisa mendapatkan seluruh bukunya hari ini, pada waktu yang sama, pergi ke sana, beli buku itu, $ 10 untuk e-book, $ 25 untuk buku cetak, dan Anda bisa mendapatkan semua tip itu sekaligus.
Baiklah, episode yang sangat panjang di sini: kami memiliki Anggaran atas-bawah yang kecil dan anggaran dasar ke atas Sebenarnya, ukurannya berbeda, tetapi menggunakan model data di Excel 2013… Dan omong-omong, jika Anda berada di tahun 2010, Anda bisa , secara teori, lakukan ini dengan mendapatkan add-in Power Pivot, dan lakukan semua langkah ini pada tahun 2010. Jadikan kedua kumpulan data menjadi tabel Ctrl + T, lalu gabungkan tabel Anda untuk apa pun yang ingin Anda laporkan, di label baris, atau label kolom, atau pemotong, jadi salin nilai tersebut dan Hapus duplikat untuk tanggal. Saya benar-benar mengambil nilai dari kedua tabel, karena ada beberapa nilai unik di masing-masing, dan kemudian saya menggunakan EOMONTH untuk keluar, membuat tabel penggabung tersebut menjadi tabel yang dikontrol. Ini opsional, tetapi saya menamai semua 5 tabel, karena lebih mudah ketika Anda menyiapkan hubungan tersebut, daripada dipanggil Tabel1,Tabel2, Tabel3.
Jadi, mulai dari tabel Anggaran, Sisipkan, PivotTable, centang kotak untuk Model Data, lalu buat tabel Pivot menggunakan Anggaran dan Aktual. Semua yang lain berasal dari tabel penghubung, jadi Kawasan dan Bulan di area baris dan kolom, pemotong berasal dari tabel Produk. Dan kemudian kami harus membuat 3 hubungan dari Anggaran ke penyambung, 3 hubungan dari Aktual ke penyambung, dan kami memiliki tabel Pivot yang menakjubkan. Sekarang besok kita akan membahas penggunaan tab Power Pivot dan membuat beberapa penghitungan tambahan. Jadi semua ini dimungkinkan, saat kami ingin menyisipkan kolom kalkulasi, saat itulah Anda harus membayar ekstra $ 2 sebulan untuk mendapatkan Office 365 versi Pro Plus.
Baiklah, terima kasih kepada Rob Collie dari Power Pivot Pro untuk tip ini, dan terima kasih kepada Anda karena telah mampir, sampai jumpa lagi untuk netcast lain dari!
Unduh berkas
Unduh file contoh di sini: Podcast2016.xlsx
