Konten
Pemotong dalam tabel pivot dapat digunakan tidak hanya dengan cara klasik – untuk memfilter data sumber, tetapi juga untuk beralih di antara berbagai jenis penghitungan di area nilai:
Menerapkan ini sangat mudah – yang Anda butuhkan hanyalah beberapa rumus dan tabel tambahan. Yah, kami akan melakukan semua ini bukan dalam ringkasan biasa, tetapi dalam ringkasan yang dibuat sesuai dengan Model Data Power Pivot.
Langkah 1. Menghubungkan add-in Power Pivot
Jika tab add-in Power Pivot tidak terlihat di Excel, Anda harus mengaktifkannya terlebih dahulu. Ada dua opsi untuk ini:
- Tab pembangun - tombol COM tambahan (Pengembang — Add-in COM)
- File – Opsi – Add-in – COM Add-in – Buka (File — Opsi — Add-in — COM-Add-in — Buka)
Jika ini tidak membantu, coba mulai ulang Microsoft Excel.
Langkah 2: Muat Data ke Model Data Power Pivot
Kami akan memiliki dua tabel sebagai data awal:
Yang pertama adalah tabel dengan penjualan, yang nantinya akan kami buat ringkasannya. Yang kedua adalah tabel tambahan, di mana nama-nama tombol dari irisan masa depan dimasukkan.
Kedua tabel ini perlu diubah menjadi "pintar" (dinamis) dengan pintasan keyboard Ctrl+T atau tim Beranda – Format sebagai tabel (Beranda — Format sebagai Tabel) dan diinginkan untuk memberi mereka nama yang waras di tab Pembina (Rancangan). Biarlah misalnya Penjualan и Layanan.
Setelah itu, setiap tabel secara bergantian perlu dimuat ke dalam Model Data – untuk ini kita gunakan pada tab Powerpivot tombol Tambahkan ke Model Data (Tambahkan ke Model Data).
Bidang terhitung dalam PivotTable menurut Model Data disebut ukuran. Mari kita buat ukuran yang akan menampilkan nama tombol yang ditekan pada irisan yang akan datang. Untuk melakukan ini, di salah satu tabel kami, pilih sel kosong di panel kalkulasi bawah dan masukkan konstruksi berikut ke dalam bilah rumus:
Di sini, nama ukuran didahulukan (Tombol yang ditekan), lalu setelah titik dua dan tanda sama dengan, rumus untuk menghitungnya menggunakan fungsi NILAI DAX dibangun ke dalam Power Pivot.
Jika Anda mengulangi ini bukan di Power Pivot, tetapi di Power BI, maka titik dua tidak diperlukan dan sebagai gantinya NILAI Anda dapat menggunakan mitranya yang lebih modern – fungsinya NILAI TERPILIH.
Kami tidak memperhatikan kesalahan di bagian bawah jendela yang muncul setelah memasukkan rumus – kesalahan itu muncul, karena kami belum memiliki ringkasan dan irisan di mana sesuatu diklik.
Langkah selanjutnya adalah membuat ukuran untuk opsi perhitungan yang berbeda tergantung pada nilai ukuran sebelumnya Tombol yang ditekan. Di sini rumusnya sedikit lebih rumit:
Mari kita uraikan sepotong demi sepotong:
- fungsi SWITCH – analog IF bersarang – memeriksa pemenuhan kondisi yang ditentukan dan mengembalikan nilai yang berbeda tergantung pada pemenuhan beberapa di antaranya.
- fungsi BENAR() – memberikan logika “benar” sehingga kondisi yang diperiksa kemudian oleh fungsi SWITCH hanya berfungsi jika terpenuhi, yaitu kebenaran.
- Kemudian kami memeriksa nilai ukuran yang ditekan tombol dan menghitung hasil akhir untuk tiga opsi berbeda – sebagai jumlah biaya, pemeriksaan rata-rata, dan jumlah pengguna unik. Untuk menghitung nilai unik, gunakan fungsi DISINCTCOUNT, dan untuk pembulatan – ROUND.
- Jika tidak ada satu pun dari ketiga kondisi di atas yang terpenuhi, maka argumen terakhir dari fungsi SWITCH ditampilkan – kami menetapkannya sebagai dummy menggunakan fungsi KOSONG().
Langkah 5. Membangun ringkasan dan menambahkan irisan
Tetap kembali dari Power Pivot ke Excel dan buat tabel pivot di sana untuk semua data dan pengukuran kami. Untuk melakukan ini, di jendela Power Pivot aktif Utama perintah pilih tab tabel ringkasan (Beranda — Tabel Pivot).
Kemudian:
- Kami melempar lapangan Produk dari tabel Penjualan ke daerah Baris (Baris).
- Melempar lapangan di sana Hasil dari tabel Layanan.
- Klik kanan pada bidang Hasildan pilih tim Tambahkan sebagai irisan (Tambahkan sebagai Pemotong).
- Melempar ukuran kedua Kesimpulan dari tabel Layanan ke daerah Nilai - Nilai (Nilai).
Di sini, sebenarnya, semua triknya. Sekarang Anda dapat mengklik tombol pemotong – dan total dalam tabel pivot akan beralih ke fungsi yang Anda butuhkan.
Kecantikan
- Manfaat Pivot menurut Model Data
- Analisis rencana-fakta dalam tabel pivot di Power Pivot
- Buat database di Excel menggunakan add-in Power Pivot