Salah satu perkara yang paling saya list mengenai PowerPivot untuk Excel adalah keupayaan untuk menambah jadual carian pada set data anda. Kebanyakan masa, data yang anda bekerjasama tidak mempunyai setiap bidang yang anda perlukan untuk analisis anda. Sebagai contoh, anda mungkin mempunyai medan tarikh tetapi perlu mengumpulkan data anda mengikut suku. Anda boleh menulis formula, tetapi lebih mudah untuk membuat jadual carian mudah dalam persekitaran PowerPivot.
Anda juga boleh menggunakan jadual carian ini untuk kumpulan lain seperti nama bulan dan separuh pertama / kedua tahun ini. Dalam istilah pergudangan data, anda sebenarnya membuat jadual dimensi tarikh. Dalam artikel ini, saya akan memberikan anda beberapa contoh jadual dimensi untuk meningkatkan projek PowerPivot untuk Excel anda.
Jadual Teks Dimensi (Lookup)
Mari kita pertimbangkan jadual dengan data pesanan. Anggapkan jadual mempunyai bidang untuk pelanggan, tarikh pesanan, jumlah tempahan, dan jenis pesanan. Kami akan menumpukan pada bidang jenis pesanan. Anggapkan medan jenis pesanan termasuk nilai-nilai seperti:
- Netbook
- Desktop
- Monitor
- Projektor
- Pencetak
- Pengimbas
- Kamera Digital
- Kamera Digital SLR
- Kamera Filem
- Camcorder
- Telefon Pejabat
- Telefon pintar
- PDA
- Aksesori Telefon Bimbit
Pada hakikatnya, anda akan mempunyai kod untuk ini tetapi untuk memastikan contoh ini mudah, anggap ini adalah nilai sebenar dalam jadual pesanan. Menggunakan PowerPivot untuk Excel, anda dengan mudah dapat mengelompokkan pesanan anda mengikut jenis pesanan.
Bagaimana jika anda mahukan kumpulan yang berbeza? Contohnya, anggap anda memerlukan pengelompokan kategori seperti komputer, kamera, dan telefon. Jadual pesanan tidak mempunyai medan kategori, tetapi anda boleh dengan mudah membuatnya sebagai jadual carian dalam PowerPivot untuk Excel. Jadual carian penuh sampel adalah di bawah Jadual 1 .
Berikut adalah langkah-langkah berikut:
- Langkah 1: Anda memerlukan senarai yang berbeza dari medan jenis untuk jadual carian anda. Ini akan menjadi bidang pencarian anda. Dari set data anda, buat senarai nilai yang berbeza dari medan jenis pesanan. Masukkan senarai jenis yang berbeza ke dalam buku kerja Excel. Label lajur Taipkan .
- Langkah 2: Dalam lajur di sebelah lajur carian anda (Jenis), tambahkan medan baru yang ingin anda keluarkan. Dalam contoh kami, tambah lajur dengan label dipanggil Kategori .
- Langkah 3: Untuk setiap nilai dalam senarai nilai anda yang berbeza (jenis dalam contoh ini), tambahkan yang sepadan Kategori nilai-nilai. Dalam contoh mudah kita, masukkan sama ada Komputer , Kamera atau Telefon ke dalam Kategori kolum.
- Langkah 4: Salin Taipkan dan Kategori jadual data ke dalam clipboard anda.
- Langkah 5: Buka buku kerja Excel dengan data pesanan dalam PowerPivot untuk Excel. Lancarkan tetingkap PowerPivot. Klik Tampal yang akan membawa jadual carian baharu anda. Beri jadual nama dan pastikan anda periksa Gunakan baris pertama sebagai tajuk lajur. Klik okey. Anda telah membuat jadual carian dalam PowerPivot.
- Langkah 6: Buat hubungan antara Taipkan bidang dalam Perintah jadual dan Kategori medan dalam jadual carian. Klik pada Reka bentuk reben dan pilih Buat Hubungan. Buat pilihan di dalam Buat Hubungan dialog dan klik Buat.
Apabila anda membuat PivotTable dalam Excel berdasarkan data PowerPivot, anda akan dapat dikelompokkan oleh yang baru anda Kategori bidang. Perlu diingat bahawa PowerPivot untuk Excel hanya menyokong Inner Joins. Jika anda mempunyai jenis pesanan hilang dari jadual carian anda, semua rekod yang sepadan untuk jenis tersebut akan hilang dari sebarang PivotTable berdasarkan data PowerPivot. Anda perlu menyemak ini dari semasa ke semasa.
Jadual Dimensi (Lookup)
Jadual carian Jadual kemungkinan besar diperlukan dalam kebanyakan projek PowerPivot untuk Excel anda. Kebanyakan set data mempunyai beberapa jenis medan tarikh. Terdapat fungsi untuk mengira tahun dan bulan.
Walau bagaimanapun, jika anda memerlukan teks bulan atau suku tahun yang sebenar, anda perlu menulis formula rumit. Lebih mudah memasukkan jadual dimensi (lookup) Tarikh dan sepadan dengan nombor bulan dalam set data utama anda. Anda perlu menambah lajur pada jadual pesanan anda untuk mewakili nombor bulan dari medan tarikh pesanan. Formula DAX untuk bulan dalam contoh kita ialah = BULAN (Tarikh Pesanan). Ini akan mengembalikan nombor antara 1 dan 12 untuk setiap rekod. Jadual dimensi kami akan memberikan nilai ganti, yang menghubungkan ke nombor bulan. Ini akan memberikan anda kelonggaran dalam analisis anda. Jadual dimensi tarikh sampel lengkap adalah di bawah Jadual 2 .
Tarikh dimensi atau jadual carian akan mengandungi 12 rekod. Lajur bulan akan mempunyai nilai 1 - 12. Lajur yang lain akan termasuk teks bulan yang disingkat, teks bulan penuh, suku, dan lain-lain Berikut adalah langkah-langkah:
- Langkah 1: Salin jadual dari Jadual 2 di bawah dan tampal ke PowerPivot. Anda boleh membuat jadual ini dalam Excel tetapi saya menjimatkan masa anda. Anda harus dapat menyalin secara langsung dari data yang dipilih di bawah jika anda menggunakan Internet Explorer atau Edge. PowerPivot mengambil pemformatan meja dalam ujian saya. Sekiranya anda menggunakan penyemak imbas lain, anda perlu terlebih dahulu menempel ke Excel dan menyalinnya dari Excel untuk memilih pemformatan meja.
- Langkah 2: Buka buku kerja Excel dengan data pesanan dalam PowerPivot untuk Excel. Lancarkan tetingkap PowerPivot. Klik Tampal yang akan membawa jadual carian anda disalin dari jadual di bawah atau dari Excel. Beri jadual nama dan pastikan anda periksa Gunakan baris pertama sebagai tajuk lajur. Klik okey. Anda telah membuat jadual carian tarikh dalam PowerPivot.
- Langkah 3: Buat hubungan antara Bulan bidang dalam Perintah jadual dan Jumlah Bulan medan dalam jadual carian. Klik pada Reka bentuk reben dan pilih Buat Hubungan. Buat pilihan di dalam Buat Hubungan dialog dan klik Buat.
Sekali lagi, dengan penambahan dimensi tarikh, anda akan dapat mengumpulkan data dalam PivotTable anda menggunakan mana-mana nilai yang berbeza dari jadual carian tarikh. Pengkumpulan mengikut suku atau nama bulan akan menjadi sekejap.
Jadual Dimensi (Lookup)
Jadual 1
| Taipkan | Kategori |
| Netbook | Komputer |
| Desktop | Komputer |
| Monitor | Komputer |
| Projektor & Skrin | Komputer |
| Pencetak, Pengimbas & Faks | Komputer |
| Persediaan & Perkhidmatan Komputer | Komputer |
| Aksesori Komputer | Komputer |
| Kamera Digital | Kamera |
| Kamera Digital SLR | Kamera |
| Kamera Filem | Kamera |
| Camcorder | Kamera |
| Aksesori Kamera & Kamera | Kamera |
| Telefon Rumah & Pejabat | Telefon |
| Telefon Skrin Sentuh | Telefon |
| Telefon pintar & PDA | Telefon |
Jadual 2
| Jumlah Bulan | MonthTextShort | MonthTextFull | Suku | Semester |
| 1 | Jan | Januari | Q1 | H1 |
| 2 | Feb | Februari | Q1 | H1 |
| 3 | Mar | Mac | Q1 | H1 |
| 4 | Apr | April | Q2 | H1 |
| 5 | Mungkin | Mungkin | Q2 | H1 |
| 6 | Jun | Jun | Q2 | H1 |
| 7 | Jul | Julai | Q3 | H2 |
| 8 | Aug | Ogos | Q3 | H2 |
| 9 | Sep | September | Q3 | H2 |
| 10 | Okt | Oktober | Q4 | H2 |
| 11 | Nov | November | Q4 | H2 |
| 12 | Dec | Disember | Q4 | H2 |
