Model Data Excel (Power Pivot) membolehkan anda menyambungkan sekumpulan data terperinci sebenar dengan anggaran tingkat tinggi dengan menggunakan jadual joiner.
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 sebenarnya telah menyakitkan selama-lamanya. Saya suka muslihat ini dari Rob Collie, aka PowerPivotPro.com.
Untuk menetapkan contohnya, anda mempunyai jadual anggaran 54 baris: satu 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 tandakan kotak 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.
Tonton Video
- Anda mempunyai set data belanjawan atas-bawah yang kecil
- Anda ingin membandingkan dengan set data sebenar bawah
- Yang sebenarnya mungkin berasal dari daftar invois
- Model data akan membolehkan anda membandingkan set data ukuran berbeza ini
- Jadikan kedua-dua set data ke dalam jadual Ctrl + T
- Untuk setiap medan teks yang ingin anda laporkan, buat jadual joiner
- Salin nilai dan buang pendua
- Untuk tarikh, anda boleh memasukkan tarikh dari kedua jadual dan menukar ke akhir bulan
- Jadikan penyambung menjadi jadual Ctrl + T
- Pilihan tetapi berguna untuk menamakan kelima-lima jadual
- Buat jadual pangsi dari Anggaran dan pilih Model Data
- Bina jadual pangsi menggunakan Anggaran dan Sebenar dari jadual asal
- Semua bidang lain mesti berasal dari jadual joiner
- Tambahkan alat pemotong mengikut Produk
- Buat tiga hubungan dari Budget ke Joiners
- Buat tiga hubungan dari Sebenar hingga Bergabung
- Esok: bagaimana membina hubungan lebih mudah dengan Formula Power Pivot dan DAX
Transkrip Video
Belajar Excel dari podcast, episod 2016 - Anggaran Top-Down vs Sebenar-Bawah!
Hai, saya siarkan keseluruhan buku ini, klik “i” di sudut kanan atas dan ikuti senarai main.
Hei, saya akan mengganggu ini, ini adalah Bill Jelen dari 15 minit dari sekarang. Saya sedar sekarang ini adalah podcast yang sangat panjang, dan anda tergoda untuk hanya mengkliknya, tetapi izinkan saya memberi anda kekurangan ini Sekiranya anda berada di Excel 2013, dan anda pernah mempunyai jadual belanjawan kecil dan jadual sebenar, dan anda perlu memetakannya bersama, ini adalah kemampuan baru yang luar biasa yang kami ada di Excel 2013, yang tidak banyak yang dijelaskan oleh banyak orang , dan anda mungkin tidak tahu mengenainya. Sekiranya ini anda, anda berada pada tahun 2013, dan anda perlu memetakan dua set data ini, luangkan masa, mungkin hari ini, mungkin esok, mungkin tambahkan ke senarai tontonan, sangat berbaloi, ini adalah teknik yang luar biasa.
Baiklah, inilah yang kita ada, di sebelah kiri kita mempunyai belanjawan, belanjawan ini, ia dilakukan di tingkat atas, atas-bawah, kanan untuk setiap barisan produk, untuk setiap wilayah, untuk setiap bulan, ada anggaran . Tidak banyak catatan di sini, jumlah 55, di sebelah kanan kami cuba membandingkannya dengan yang sebenarnya. Yang sebenarnya berasal dari daftar invois, jadi kami mempunyai Wilayah, Produk, dan Pendapatan, tetapi mereka invois individu, lebih banyak data di sini, kami sudah setengah tahun, dan saya sudah mempunyai 423 rekod. Baiklah, jadi bagaimana anda memetakan 55 ke 423 ini? Mungkin sukar dilakukan dengan VLOOKUP, anda mesti meringkaskannya terlebih dahulu, tetapi syukurlah di Excel 2013, model data menjadikannya sangat mudah. Apa yang kita perlukan untuk membiarkan meja besar ini berkomunikasi dengan meja kecil ini adalah perantara, saya memanggil mereka penyambung.Meja kecil kecil, Produk, Wilayah, dan Kalendar, kita akan menggabungkan anggaran dengan tiga jadual ini, kita akan menggabungkan yang sebenarnya dengan ketiga jadual ini, dan secara ajaibnya jadual Pivot akan berfungsi. Baiklah, jadi inilah cara kami melakukannya.
Mula-mula saya perlu membuat penggabung, jadi saya mengambil medan Produk ini dari lajur A, dan saya menyalinnya ke lajur F, dan kemudian Data, Buang Pendua, klik OK, dan kami tinggal dengan meja kecil kecil, 1 tajuk 3 baris. Perkara yang sama untuk Wilayah, ambil wilayah, Ctrl + C, pergi ke lajur G, Tampal, Buang Pendua, klik OK, 3 baris 1 tajuk, baiklah. Sekarang untuk tarikhnya, tarikhnya tidak sama, ini adalah tarikh akhir bulan, ia sebenarnya disimpan sebagai tarikh berakhir bulan, dan ini adalah hari kerja. Saya akan mengambil kedua-dua senarai, Ctrl + C senarai kedua dan tampalkan di sini, Ctrl + V, kemudian saya akan mengambil senarai yang lebih pendek, salin itu, dan tampalkan di bawah, baiklah. Dan sangat menjengkelkan bahawa, walaupun ini disimpan sebagai tarikh, ia muncul sebagai bulan, dan Buang Pendua tidak akan melihatnya sama.Oleh itu, sebelum saya menggunakan Remove Duplicates, saya mesti menukarnya menjadi tarikh pendek. Pilih data itu, Data, Buang Pendua, klik OK, dan kemudian semacam di sini untuk membuatnya berfungsi.
Baiklah, sekarang saya tidak mahu melaporkan mengikut tarikh harian, jadi saya akan menambahkan lajur di sini, lajur carian yang berbunyi Bulan, dan ini akan sama dengan EOMONTH tarikh itu, 0, yang akan membawa kita ke hujung bulan. Ia akan memformatnya sebagai tarikh pendek, dan menyalinnya, baiklah. Sekarang, kita perlu menjadikannya masing-masing menjadi jadual Ctrl + T, jadi dari sini Ctrl + T, Meja saya mempunyai tajuk, cantik. Yang kecil, tidak menyedari itu adalah tajuk di atas, jadi kita harus memastikan tanda itu dan Ctrl + T, baiklah, dan mereka memanggil jadual ini Jadual1, Jadual2, Jadual3, nama yang sangat membosankan, bukan? Oleh itu, saya akan menamakan semula ini dan memanggilnya BudTable, ProdTable, RegTable, CalTable saya, dan kemudian ActTable, baiklah.
Kami bermula dari jadual pertama, dan dengan cara ini kita tidak akan menggunakan PowerPivot hari ini, kita akan melakukan semua ini dengan model data. Oleh itu, Excel 2013 atau yang lebih baru, anda mempunyai Sisipan, PivotTable ini, kami akan mencentang kotak untuk "Tambahkan data ini ke Model Data", klik OK, dan kami mendapatkan senarai bidang kami dengan butang ajaib Semua, yang membolehkan saya memilih dari semua lima jadual dalam buku kerja, Sebenar, Belanjawan, Kalendar, Produk, Wilayah. Baiklah, jadi nombornya akan datang dari jadual Belanjawan, saya akan memasukkan anggaran di sana, dan dari jadual Sebenar saya akan meletakkan yang sebenarnya di sana, tetapi inilah masalahnya untuk jadual Pangsi yang lain. Mana-mana bidang teks lain yang akan kita masukkan di kawasan baris atau kawasan lajur atau sebagai pemotong, mereka mesti berasal dari penggabung, mereka mesti datang dari jadual di antara jadual.
Baiklah, jadi dari jadual Kalendar kita akan mengambil medan Bulan itu dan meletakkannya di bahagian atas, kita akan mengabaikan hubungan lain sekarang. Saya akan menjalin hubungan, tetapi saya mahu menjalin hubungan sekaligus. Dan jadual Wilayah, letakkan kawasan di sebelah. Saya boleh meletakkan produk di sisi, tetapi saya sebenarnya akan menggunakan jadual Produk sebagai alat pemotong, jadi Analisis, Masukkan Slicer, sekali lagi anda harus pergi ke Semua jika anda belum menggunakan jadual Produk. Oleh itu, pergi ke Semua, dan anda akan melihat bahawa Produk tersedia untuk dibuat sebagai pemotong dari produk, seperti itu. Baiklah sekarang, pada ketika ini kita belum menjalin hubungan, jadi semua nombor ini salah. Dan hubungan yang harus kita buat, kita harus membuat 3 jadual dari jadual belanjawan kecil ini, satu ke produk, satu ke kawasan, satu hingga kalendar,itu 3 hubungan. Dan kemudian kita harus menjalin hubungan dari jadual Sebenar ke rantau Produk di Kalendar, jadi sejumlah 6 jadual. Dan ya, ini pasti lebih mudah jika kita mempunyai PowerPivot, tetapi kita tidak atau anggap tidak.
Oleh itu, saya akan menggunakan cara lama, dialog Buat di sini, di mana kita mempunyai jadual Anggaran di sebelah kiri, dan kita akan menggunakan bidang Wilayah dan mengaitkannya dengan jadual Wilayah, bidang Wilayah . Baiklah, 1/6 dibuat. Saya akan memilih Buat, sekali lagi dari jadual Belanjawan kita pergi ke Produk, dan kemudian pautkan ke jadual Produk, ke Produk, klik OK. Dari jadual Anggaran pada medan Tarikh, kita pergi ke jadual Kalendar, dan medan Nasib, klik OK, kita berada di sana, baiklah. Dari jadual Sebenar, kita pergi Wilayah, ke jadual Wilayah, klik OK, dari jadual Sebenar ke Produk, dan dari jadual Sebenar ke Kalendar. Saya benar-benar akan mengambil Nilai dan membuatnya turun, baiklah. Reka Bentuk, Tata Letak Laporan, Tunjukkan dalam Bentuk Jadual untuk mendapatkan pandangan yang saya sukai, Ulangi Semua Label Item, baiklah,ini sungguh luar biasa! Sekarang kita mempunyai jadual kecil yang kecil ini, 50-beberapa catatan dalam jadual ini beratus-ratus rekod, dan kita telah membuat satu jadual Pivot berkat Model Data. Untuk setiap di mana kita dapat melihat Anggaran, kita dapat melihat Pendapatan, dibahagikan berdasarkan Wilayah, dibahagikan berdasarkan Bulan, dan dapat dipotong berdasarkan Produk.
Kini konsep ini datang kepada saya dari Rob Collie yang menjalankan Power Pivot Pro, dan Rob telah membuat banyak buku di luar sana, yang terbarunya adalah "Power Pivot dan Power BI". Saya rasa yang ini sebenarnya ada dalam buku "Power Pivot Alchemy", inilah yang saya lihat ini dan saya katakan "Baiklah, walaupun saya tidak mempunyai berjuta-juta baris untuk melaporkan melalui Power Pivot, inilah yang akan telah membuat perbezaan yang besar dalam hidup saya, mempunyai dua set data yang tidak sesuai dengan ukuran, dan perlu melaporkan dari kedua-duanya. " Contoh ini dan banyak yang lain ada dalam buku ini, akhirnya saya akan mendapat keseluruhan podcast buku, sepertinya akan memakan masa dua setengah bulan. Tetapi anda boleh mendapatkan keseluruhan buku hari ini, pada masa yang sama, pergi ke sana, beli buku itu, $ 10 untuk e-buku, $ 25 untuk buku cetak, dan anda boleh mendapatkan semua petua tersebut sekaligus.
Baiklah, episod yang sangat panjang di sini: kami mempunyai Anggaran atas-bawah kecil dan bahagian bawah sebenarnya, saiznya berbeza, tetapi menggunakan model data di Excel 2013… Dan omong-omong jika anda berada di tahun 2010, anda boleh , secara teori, lakukan ini dengan mendapatkan add-in Power Pivot, dan ikuti semua langkah ini pada tahun 2010. Jadikan kedua set data tersebut menjadi jadual Ctrl + T, dan kemudian gabungkan jadual anda untuk apa sahaja yang anda ingin laporkan, di label baris, atau label lajur, atau pemotong, jadi salin nilai tersebut dan Buang Pendua untuk tarikh. Saya benar-benar mengambil nilai dari kedua-dua jadual, kerana terdapat beberapa nilai unik di masing-masing jadual, dan kemudian saya menggunakan EOMONTH untuk keluar dari sana, menjadikan jadual bergabung menjadi jadual terkawal. Itu pilihan, tetapi saya menamakan semua 5 jadual, kerana lebih mudah ketika anda menjalin hubungan tersebut, dan bukannya dipanggil Jadual1,Jadual2, Jadual3.
Oleh itu, mulakan dari jadual Anggaran, Masukkan, PivotTable, tandakan kotak untuk Model Data, dan kemudian buat jadual Pivot menggunakan Anggaran dan Sebenar. Semua yang lain berasal dari jadual penggabung, jadi Wilayah dan Bulan di kawasan baris dan lajur, pemotong berasal dari jadual Produk. Dan kemudian kami harus menjalin 3 hubungan dari Anggaran dengan penggabung, 3 hubungan dari yang Sebenarnya dengan yang bergabung, dan kami mempunyai jadual Pivot yang menakjubkan. Sekarang esok kita akan melihat penggunaan tab Power Pivot dan membuat beberapa pengiraan tambahan. Oleh itu, semua ini mungkin berlaku, ketika kita ingin memasukkan medan yang dikira, ketika itulah anda perlu membayar $ 2 tambahan sebulan untuk mendapatkan Office 365 versi Pro Plus.
Baiklah, terima kasih kepada Rob Collie dari Power Pivot Pro untuk petua ini, dan terima kasih kepada anda kerana berhenti, kami akan berjumpa anda di lain kali untuk siaran net lain!
Muat turun fail
Muat turun fail sampel di sini: Podcast2016.xlsx
