Tidak mempunyai Power Pivot? Tidak penting. Sebilangan besar Power Pivot dibina dalam Excel 2013 dan lebih banyak lagi di Excel 2016. Hari ini, petua kami dari Ash adalah menggabungkan jadual dalam jadual pangsi.
Setiap hari Rabu selama tujuh minggu, saya memaparkan salah satu petua kegemaran dari Ash Sharma. Ash adalah pengurus produk dalam pasukan Excel. Pasukannya membawa anda jadual pangsi dan banyak perkara baik yang lain. Hari ini, ciri kegemaran Ash adalah menggabungkan beberapa set data menggunakan Hubungan dan Model Data.
Katakan bahawa jabatan IT anda memberi anda set data yang ditunjukkan di lajur A: D. Terdapat bidang untuk pelanggan dan pasaran. Anda perlu menggabungkan pasaran tertentu ke kawasan. Setiap pelanggan tergolong dalam sektor. Wilayah dan Sektor tidak ada dalam data asal, tetapi anda mempunyai jadual pencarian untuk memberikan maklumat ini.
Biasanya, anda akan meratakan data dengan menggunakan VLOOKUP untuk menarik data dari jadual oren dan kuning ke meja biru. Tetapi kerana medan kunci tidak ada di sebelah kiri setiap jadual, anda perlu beralih ke INDEX dan MATCH, atau menyusun semula jadual pencarian.
Bermula di Excel 2013, anda boleh meninggalkan jadual carian di mana mereka berada dan menggabungkannya dalam laporan jadual pangsi itu sendiri.
Untuk teknik ini berfungsi, ketiga-tiga jadual mesti diformat sebagai Jadual. Pilih satu sel di setiap set data dan pilih Laman Utama, Format sebagai Jadual atau tekan Ctrl + T. Ketiga jadual pada awalnya akan dipanggil Jadual1, Jadual2, dan Jadual3. Saya menggunakan tab Reka Bentuk Alat Meja Pita dan menamakan semula setiap jadual. Saya juga menukar warna setiap meja. Dalam contoh ini, jadual biru dipanggil Data. Jadual oren adalah TableTable. Jadual kuning ialah Jadual Sektor.
Catatan
Ada yang akan memberitahu anda bahawa anda harus menggunakan nama geeky seperti Fact, TblSector dan TblRegion. Sekiranya ada yang menyusahkan anda seperti ini, curi pelindung saku mereka dan beritahu mereka bahawa anda lebih suka nama berbahasa Inggeris.
Untuk menamakan semula jadual, ketik nama baru di dalam kotak di sebelah kiri tab Reka Bentuk Meja. Nama jadual tidak boleh mempunyai ruang.
Setelah ketiga-tiga jadual ditentukan, pergi ke tab Data dan klik pada Hubungan.
Dalam dialog Urus Hubungan, klik Baru. Dalam dialog Buat Hubungan, tentukan bahawa bidang Pelanggan tabel Data berkaitan dengan Medan Pelanggan SectorTable. Klik OK.
Tentukan hubungan baru yang lain antara medan Pasar di medan Jadual Data dan Kawasan. Setelah menentukan kedua-dua hubungan, anda akan melihatnya dalam dialog Urus Hubungan.
Tahniah: anda baru sahaja membina Model Data dalam buku kerja anda. Sudah tiba masanya untuk membina jadual pangsi.
Pilih sel kosong di mana anda mahu jadual pangsi anda muncul. Secara lalai, dialog Buat PivotTable akan memilih Menggunakan Model Data Buku Kerja Ini. Lokasi jadual pangsi akan lalai ke sel yang anda pilih. Klik OK.
Senarai Medan Jadual Pangsi akan menyenaraikan ketiga-tiga jadual. Gunakan segitiga di sebelah kiri meja untuk meluaskan nama jadual untuk menunjukkan medan kepada anda.
Kembangkan jadual Data. Pilih medan Hasil. Secara automatik ia akan berpindah ke kawasan Nilai. Kembangkan Jadual Sektor. Pilih bidang Sektor. Ia akan bergerak ke kawasan Rows. Kembangkan Jadual Kawasan. Seret medan Wilayah ke kawasan Lajur. Anda sekarang akan mempunyai jadual pangsi yang merangkum data dari tiga jadual.
Catatan
Dalam setiap buku yang saya tulis sebelum ini, saya menggunakan teknik yang berbeza untuk membina laporan ini. Setelah menentukan tiga jadual, saya memilih sel A1 dan Masukkan, Pivot Table. Saya mencentang kotak untuk Menambah Data Ini ke Model Data. Dalam senarai Medan Jadual Pangsi, pilih Semua dari bahagian atas senarai. Pilih bidang untuk laporan, dan kemudian tentukan hubungan selepas fakta. Teknik yang dinyatakan di atas nampak lebih lancar dan sebenarnya melibatkan sedikit perancangan ke depan. Orang yang menggunakan Option Explicit dalam kod VBA mereka pasti menyukai kaedah ini.
Hubungan dalam model data menjadikan Excel lebih terasa seperti Access atau SQL Server, tetapi dengan semua kebaikan Excel.
Saya suka bertanya kepada pasukan Excel mengenai ciri kegemaran mereka. Setiap hari Rabu, saya akan berkongsi salah satu jawapan mereka. Terima kasih kepada Ash Sharma kerana membekalkan idea ini.
Pemikiran Excel Hari Ini
Saya telah meminta nasihat rakan Excel saya mengenai Excel. Pemikiran hari ini untuk merenungkan:
"Jangan cari jika anda menjalin hubungan"
John Michaloudis
