Salah satu hal yang paling saya cantumkan tentang PowerPivot untuk Excel adalah kemampuan untuk menambahkan tabel pencarian ke kumpulan data Anda. Seringkali, data yang Anda kerjakan tidak memiliki semua bidang yang Anda perlukan untuk analisis Anda. Misalnya, Anda mungkin memiliki bidang tanggal tetapi perlu mengelompokkan data Anda menurut kuartal. Anda bisa menulis rumus, tetapi lebih mudah untuk membuat tabel pencarian sederhana dalam lingkungan PowerPivot.
Anda juga dapat menggunakan tabel pencarian ini untuk pengelompokan lain seperti nama bulan dan paruh pertama / kedua tahun ini. Dalam istilah pergudangan data, Anda sebenarnya membuat tabel dimensi tanggal. Dalam artikel ini, saya akan memberikan Anda beberapa contoh tabel dimensi untuk meningkatkan proyek PowerPivot untuk Excel Anda.
Tabel Dimensi Teks Baru (Pencarian)
Mari kita pertimbangkan tabel dengan data pesanan. Asumsikan tabel memiliki bidang untuk pelanggan, tanggal pesanan, total pesanan, dan jenis pesanan. Kami akan fokus pada bidang jenis pesanan. Asumsikan bidang jenis pesanan termasuk nilai-nilai seperti:
- Netbook
- Desktops
- Pengawas
- Proyektor
- Printer
- Pemindai
- Kamera digital
- Kamera Digital SLR
- Kamera Film
- Kamera perekam
- Telepon Kantor
- Ponsel pintar
- PDA
- Aksesori Telepon Seluler
Pada kenyataannya, Anda akan memiliki kode untuk ini tetapi untuk menjaga contoh ini sederhana, anggap ini adalah nilai-nilai aktual dalam tabel urutan. Dengan menggunakan PowerPivot untuk Excel, Anda akan dengan mudah dapat mengelompokkan pesanan Anda berdasarkan jenis pesanan.
Bagaimana jika Anda menginginkan pengelompokan yang berbeda? Misalnya, anggap Anda memerlukan pengelompokan kategori seperti komputer, kamera, dan ponsel. Tabel urutan tidak memiliki bidang kategori, tetapi Anda dapat dengan mudah membuatnya sebagai tabel pencarian di PowerPivot untuk Excel. Tabel pencarian sampel lengkap di bawah ini Tabel 1 .
Berikut langkahnya:
- Langkah 1: Anda memerlukan daftar yang berbeda dari kolom jenis untuk tabel pencarian Anda. Ini akan menjadi bidang pencarian Anda. Dari kumpulan data Anda, buat daftar nilai berbeda dari bidang jenis pesanan. Masukkan daftar jenis yang berbeda ke dalam buku kerja Excel. Tandai kolom Mengetik .
- Langkah 2: Di kolom di samping kolom pencarian Anda (Ketik), tambahkan bidang baru yang ingin Anda kelompokkan. Dalam contoh kami, tambahkan kolom dengan label yang disebut Kategori .
- Langkah 3: Untuk setiap nilai dalam daftar nilai Anda yang berbeda (jenis dalam contoh ini), tambahkan yang sesuai Kategori nilai-nilai. Dalam contoh sederhana kami, masukkan juga Komputer , Kamera atau Ponsel ke dalam Kategori kolom.
- Langkah 4: Salin Mengetik dan Kategori tabel data ke clipboard Anda.
- Langkah 5: Buka buku kerja Excel dengan data pesanan di PowerPivot untuk Excel. Luncurkan Jendela PowerPivot. Klik Tempel yang akan membawa tabel pencarian Anda yang baru. Berikan nama pada tabel dan pastikan Anda memeriksanya Gunakan baris pertama sebagai tajuk kolom. Klik baik. Anda telah membuat tabel pencarian di PowerPivot.
- Langkah 6: Buat hubungan antara Mengetik bidang di Memesan meja dan Kategori lapangan di tabel pencarian. Klik pada Desain pita dan pilih Buat Hubungan. Buat pilihan di Buat Hubungan dialog dan klik Membuat.
Ketika Anda membuat PivotTable di Excel berdasarkan data PowerPivot, Anda akan dapat mengelompokkan berdasarkan baru Anda Kategori bidang. Perlu diingat bahwa PowerPivot untuk Excel hanya mendukung Inner Join. Jika Anda punya jenis pesanan hilang dari tabel pencarian Anda, semua catatan terkait untuk jenis itu akan hilang dari PivotTable apa pun berdasarkan data PowerPivot. Anda perlu memeriksa ini dari waktu ke waktu.
Tabel Dimensi Tanggal (Pencarian)
Tabel pencarian tanggal kemungkinan besar akan diperlukan di sebagian besar proyek PowerPivot untuk Excel Anda. Sebagian besar set data memiliki beberapa jenis bidang tanggal. Ada fungsi untuk menghitung tahun dan bulan.
Namun, jika Anda membutuhkan teks bulan atau kuartal yang sebenarnya, Anda perlu menulis rumus yang rumit. Jauh lebih mudah untuk memasukkan tabel Tanggal dimensi (lookup) dan mencocokkannya dengan nomor bulan di kumpulan data utama Anda. Anda perlu menambahkan kolom ke tabel pesanan Anda untuk mewakili nomor bulan dari bidang tanggal pesanan. Rumus DAX untuk bulan dalam contoh kita adalah = BULAN (Tanggal Pesanan). Ini akan mengembalikan angka antara 1 dan 12 untuk setiap record. Tabel dimensi kami akan memberikan nilai alternatif, yang tertaut ke nomor bulan. Ini akan memberi Anda fleksibilitas dalam analisis Anda. Tabel dimensi tanggal sampel lengkap ada di bawah Meja 2 .
Dimensi tanggal atau tabel pencarian akan menyertakan 12 rekaman. Kolom bulan akan memiliki nilai 1 - 12. Kolom lain akan mencakup teks bulan singkat, teks bulan penuh, kuartal, dll. Berikut adalah langkah-langkahnya:
- Langkah 1: Salin tabel dari Tabel 2 di bawah dan tempelkan ke PowerPivot. Anda bisa membuat tabel ini di Excel tetapi saya menghemat waktu Anda. Anda harus dapat menempel langsung dari data yang dipilih di bawah ini jika Anda menggunakan Internet Explorer atau Edge. PowerPivot mengambil format tabel dalam pengujian saya. Jika Anda menggunakan browser lain, Anda mungkin harus menempelkan ke Excel terlebih dahulu dan menyalinnya dari Excel untuk mengambil format tabel.
- Langkah 2: Buka buku kerja Excel dengan data pesanan di PowerPivot untuk Excel. Luncurkan Jendela PowerPivot. Klik Pasta yang akan membawa tabel pencarian Anda disalin dari tabel di bawah atau dari Excel. Berikan nama pada tabel dan pastikan Anda memeriksanya Gunakan baris pertama sebagai tajuk kolom. Klik baik. Anda telah membuat tabel pencarian tanggal di PowerPivot.
- Langkah 3: Buat hubungan antara Bulan bidang di Memesan meja dan MonthNumber lapangan di tabel pencarian. Klik pada Desain pita dan pilih Buat Hubungan. Buat pilihan di Buat Hubungan dialog dan klik Membuat.
Sekali lagi, dengan penambahan dimensi tanggal, Anda akan dapat mengelompokkan data dalam PivotTable Anda menggunakan salah satu nilai yang berbeda dari tabel pencarian tanggal. Pengelompokan per kuartal atau nama bulan akan sangat cepat.
Tabel Dimensi Sampel (Pencarian)
Tabel 1
| Mengetik | Kategori |
| Netbook | Komputer |
| Desktops | Komputer |
| Pengawas | Komputer |
| Proyektor & Layar | Komputer |
| Printer, Pemindai & Faks | Komputer |
| Pengaturan & Layanan Komputer | Komputer |
| Aksesoris Komputer | Komputer |
| Kamera digital | Kamera |
| Kamera Digital SLR | Kamera |
| Kamera Film | Kamera |
| Kamera perekam | Kamera |
| Aksesori Kamera & Kamera Video | Kamera |
| Telepon Rumah & Kantor | Telepon |
| Ponsel Layar Sentuh | Telepon |
| Ponsel pintar & PDA | Telepon |
Meja 2
| MonthNumber | MonthTextShort | MonthTextFull | Perempat | Semester |
| 1 | Jan | Januari | Q1 | H1 |
| 2 | Feb | Februari | Q1 | H1 |
| 3 | Merusak | Maret | Q1 | H1 |
| 4 | April | April | Q2 | H1 |
| 5 | Mungkin | Mungkin | Q2 | H1 |
| 6 | Juni | Juni | Q2 | H1 |
| 7 | Jul | Juli | Q3 | H2 |
| 8 | Agustus | Agustus | Q3 | H2 |
| 9 | Sep | September | Q3 | H2 |
| 10 | Okt | Oktober | Q4 | H2 |
| 11 | Nov | November | Q4 | H2 |
| 12 | Des | Desember | Q4 | H2 |
