Faedah Pivot mengikut Model Data

Apabila membina jadual pangsi dalam Excel, dalam kotak dialog yang pertama, di mana kita diminta untuk menetapkan julat awal dan memilih tempat untuk memasukkan jadual pangsi, terdapat kotak semak yang tidak mencolok tetapi sangat penting di bawah - Tambahkan data ini pada Model Data (Tambah data ini kepada Model Data) dan, lebih tinggi sedikit, suis Gunakan model data buku ini (Gunakan Model Data buku kerja ini):

Malangnya, ramai pengguna yang telah biasa dengan jadual pangsi untuk masa yang lama dan berjaya menggunakannya dalam kerja mereka kadang-kadang tidak benar-benar memahami maksud pilihan ini dan tidak pernah menggunakannya. Dan sia-sia. Lagipun, mencipta jadual pangsi untuk Model Data memberi kita beberapa kelebihan yang sangat penting berbanding dengan jadual pangsi Excel klasik.

Walau bagaimanapun, sebelum mempertimbangkan "sanggul" ini secara dekat, mari kita fahami terlebih dahulu apakah sebenarnya, Model Data ini?

Apakah Model Data

Model Data (disingkatkan sebagai MD atau DM = Model Data) ialah kawasan khas dalam fail Excel di mana anda boleh menyimpan data jadual - satu atau lebih jadual dipautkan, jika mahu, antara satu sama lain. Sebenarnya, ini ialah pangkalan data kecil (OLAP cube) yang dibenamkan di dalam buku kerja Excel. Berbanding dengan penyimpanan klasik data dalam bentuk jadual biasa (atau pintar) pada helaian Excel itu sendiri, Model Data mempunyai beberapa kelebihan ketara:

• Meja boleh sehingga 2 bilion baris, dan helaian Excel boleh memuatkan lebih sedikit daripada 1 juta.
• Walaupun saiz yang sangat besar, pemprosesan jadual tersebut (penapisan, pengisihan, pengiraan pada mereka, ringkasan bangunan, dll.) dilakukan sangat laju Jauh lebih pantas daripada Excel itu sendiri.
• Dengan data dalam Model, anda boleh melakukan pengiraan tambahan (jika dikehendaki, sangat kompleks) menggunakan bahasa DAX terbina dalam.
  
• Semua maklumat yang dimuatkan ke dalam Model Data adalah sangat dimampatkan dengan kuat menggunakan arkib terbina dalam khas dan agak sederhana meningkatkan saiz fail Excel asal.
  

Model ini diurus dan dikira oleh tambahan khas terbina dalam Microsoft Excel - Powerpivottentang yang telah saya tulis. Untuk mendayakannya, pada tab pemaju klik COM add-in (Pembangun — COM Add-in) dan tandakan kotak yang sesuai:

Jika tab pemaju (Pembangun)anda tidak dapat melihatnya pada reben, anda boleh menghidupkannya melalui Fail – Pilihan – Persediaan Reben (Fail — Pilihan — Sesuaikan Reben). Jika dalam tetingkap yang ditunjukkan di atas dalam senarai add-in COM anda tidak mempunyai Power Pivot, maka ia tidak disertakan dalam versi Microsoft Office anda 🙁

Pada tab Power Pivot yang muncul, akan terdapat butang hijau muda yang besar pengurusan (Mengurus), mengklik pada yang akan membuka tetingkap Power Pivot di atas Excel, di mana kita akan melihat kandungan Model Data buku semasa:

Nota penting sepanjang perjalanan: buku kerja Excel hanya boleh mengandungi satu Model Data.

Muatkan jadual ke dalam Model Data

Untuk memuatkan data ke dalam Model, mula-mula kita tukar jadual menjadi pintasan papan kekunci "pintar" dinamik Ctrl+T dan berikan nama yang mesra pada tab Pembina (Reka Bentuk). Ini adalah langkah yang diperlukan.

Kemudian anda boleh menggunakan mana-mana tiga kaedah untuk dipilih:

• Tekan butang Tambahkan pada Model (Tambahkan pada Model Data) tab Powerpivot tab Laman Utama (Rumah).
  
• Memilih pasukan Sisipkan – Jadual Pangsi (Sisipkan — Jadual Pangsi) dan hidupkan kotak semak Tambahkan data ini pada Model Data (Tambahkan data ini pada Model Data). Dalam kes ini, mengikut data yang dimuatkan ke dalam Model, jadual pangsi juga segera dibina.
  
• Pada tab Lanjutan Tarikh (Tarikh) klik pada butang Daripada Jadual/Julat (Dari Jadual/Julat)untuk memuatkan jadual kami ke dalam editor Power Query. Laluan ini adalah yang terpanjang, tetapi, jika dikehendaki, di sini anda boleh melakukan pembersihan data tambahan, pengeditan dan semua jenis transformasi, di mana Power Query sangat kuat.

  Kemudian data yang disikat dimuat naik ke Model dengan arahan Laman Utama — Tutup dan Muatkan — Tutup dan Muatkan dalam… (Laman Utama — Tutup&Muatkan — Tutup&Muat ke…). Dalam tetingkap yang terbuka, pilih pilihan Buat sambungan sahaja (Hanya buat sambungan) dan, yang paling penting, letakkan tanda Tambahkan data ini pada Model Data (Tambahkan data ini pada Model Data).
  

Kami membina ringkasan Model Data

Untuk membina Model Data ringkasan, anda boleh menggunakan mana-mana daripada tiga pendekatan:

• tekan butang jadual ringkasan (Jadual Pangsi) dalam tetingkap Power Pivot.
• Pilih arahan dalam Excel Sisipkan – Jadual Pangsi dan tukar ke mod Gunakan model data buku ini (Sisipkan — Jadual Pangsi — Gunakan Model Data buku kerja ini).
• Memilih pasukan Sisipkan – Jadual Pangsi (Sisipkan — Jadual Pangsi) dan hidupkan kotak semak Tambahkan data ini pada Model Data (Tambahkan data ini pada Model Data). Jadual "pintar" semasa akan dimuatkan ke dalam Model dan jadual ringkasan akan dibina untuk keseluruhan Model.

Memandangkan kita telah mengetahui cara memuatkan data ke dalam Model Data dan membina ringkasan padanya, mari kita terokai faedah dan kelebihan yang diberikan oleh ini kepada kita.

Faedah 1: Hubungan antara jadual tanpa menggunakan formula

Ringkasan biasa hanya boleh dibina menggunakan data daripada satu jadual sumber. Jika anda mempunyai beberapa daripadanya, contohnya, jualan, senarai harga, direktori pelanggan, daftar kontrak, dsb., maka anda perlu mengumpul data daripada semua jadual menjadi satu menggunakan fungsi seperti VLOOKUP terlebih dahulu. (TAMPILAN), INDEKS (INDEX), LEBIH TERDEDAH (PERLAWANAN), SUMMESLIMN (SUMIFS) dan seumpamanya. Ini panjang, membosankan dan mendorong Excel anda menjadi "pemikiran" dengan sejumlah besar data.

Dalam kes ringkasan Model Data, semuanya adalah lebih mudah. Ia cukup untuk menyediakan perhubungan antara jadual sekali dalam tetingkap Power Pivot – dan ia selesai. Untuk melakukan ini, pada tab Powerpivot tekan butang pengurusan (Mengurus) dan kemudian dalam tetingkap yang muncul – butang Paparan Carta (Pandangan Rajah). Ia kekal untuk menyeret nama lajur (kunci) biasa (medan) antara jadual untuk membuat pautan:

Selepas itu, dalam ringkasan untuk Model Data, anda boleh membuang dalam kawasan ringkasan (baris, lajur, penapis, nilai) mana-mana medan daripada mana-mana jadual yang berkaitan – semuanya akan dipautkan dan dikira secara automatik:

Faedah 2: Kira nilai unik

Jadual pangsi biasa memberi kita peluang untuk memilih salah satu daripada beberapa fungsi pengiraan terbina dalam: jumlah, purata, kiraan, minimum, maksimum, dll. Dalam ringkasan Model Data, fungsi yang sangat berguna ditambahkan pada senarai standard ini untuk mengira bilangan unik (nilai tidak berulang). Dengan bantuannya, sebagai contoh, anda boleh mengira dengan mudah bilangan barangan unik (julat) yang kami jual di setiap bandar.

Klik kanan pada medan - arahan Pilihan medan nilai dan pada tab Operasi Pilih Bilangan elemen yang berbeza (Kiraan berbeza):

Faedah 3: Formula DAX Tersuai

Kadangkala anda perlu melakukan pelbagai pengiraan tambahan dalam jadual pangsi. Dalam ringkasan biasa, ini dilakukan menggunakan medan dan objek yang dikira, manakala ringkasan model data menggunakan ukuran dalam bahasa DAX khas (DAX = Ungkapan Analisis Data).

Untuk membuat ukuran, pilih pada tab Powerpivot Perintah Langkah – Buat Ukuran (Langkah - Ukuran baharu) atau hanya klik kanan pada jadual dalam senarai Medan Pangsi dan pilih Tambah ukuran (Tambah ukuran) dalam menu konteks:

Dalam tetingkap yang terbuka, tetapkan:

• Nama jadualdi mana ukuran yang dibuat akan disimpan.
• Ukur nama – sebarang nama yang anda fahami untuk medan baharu.
• Penerangan Produk – pilihan.
• Formula – perkara yang paling penting, kerana di sini kita sama ada secara manual masukkan, atau klik pada butang fx dan pilih fungsi DAX dari senarai, yang sepatutnya mengira hasilnya apabila kami kemudian membuang ukuran kami ke dalam kawasan Nilai.
• Di bahagian bawah tetingkap, anda boleh segera menetapkan format nombor untuk ukuran dalam senarai kategori.
  

Bahasa DAX tidak selalu mudah difahami kerana beroperasi bukan dengan nilai individu, tetapi dengan keseluruhan lajur dan jadual, iaitu memerlukan beberapa penstrukturan semula pemikiran selepas formula Excel klasik. Walau bagaimanapun, ia berbaloi, kerana kuasa keupayaannya dalam memproses sejumlah besar data adalah sukar untuk dinilai terlalu tinggi.

Faedah 4: Hierarki medan tersuai

Selalunya, apabila membuat laporan standard, anda perlu membuang gabungan medan yang sama ke dalam jadual pangsi dalam urutan tertentu, contohnya. Tahun-Suku-Bulan-Hari, Atau Kategori-Produk, Atau Negara-Bandar-Pelanggan dll. Dalam ringkasan Model Data, masalah ini mudah diselesaikan dengan mencipta sendiri hierarki — set medan tersuai.

Dalam tetingkap Power Pivot, tukar ke mod carta dengan butang Paparan Carta tab Laman Utama (Laman Utama — Paparan Rajah), pilih dengan Ctrl medan yang dikehendaki dan klik kanan padanya. Menu konteks akan mengandungi arahan Buat Hierarki (Buat hierarki):

Hierarki yang dibuat boleh dinamakan semula dan diseret ke dalamnya dengan tetikus medan yang diperlukan, supaya kemudian dalam satu pergerakan mereka boleh dilemparkan ke dalam ringkasan:

Faedah 5: Stensil tersuai

Meneruskan idea perenggan sebelumnya, dalam ringkasan Model Data, anda juga boleh membuat set elemen anda sendiri untuk setiap medan. Sebagai contoh, daripada keseluruhan senarai bandar, anda boleh dengan mudah membuat satu set hanya mereka yang berada dalam bidang tanggungjawab anda. Atau kumpulkan hanya pelanggan anda, barangan anda, dsb. ke dalam set khas.

Untuk melakukan ini, pada tab Analisis jadual pangsi dalam senarai juntai bawah Medan, Item dan Set terdapat arahan yang sepadan (Analisis - Fields, Items & Sets — Buat set berdasarkan item baris/lajur):

Dalam tetingkap yang terbuka, anda boleh mengalih keluar, menambah atau menukar kedudukan mana-mana elemen secara selektif dan menyimpan set yang terhasil di bawah nama baharu:

Semua set yang dibuat akan dipaparkan dalam panel Medan Jadual Pangsi dalam folder berasingan, dari mana set tersebut boleh diseret secara bebas ke kawasan baris dan lajur mana-mana Jadual Pangsi baharu:

Faedah 6: Sembunyikan Jadual dan Lajur secara terpilih

Walaupun ini adalah kelebihan yang kecil, tetapi sangat menyenangkan dalam beberapa kes. Dengan mengklik kanan pada nama medan atau pada tab jadual dalam tetingkap Power Pivot, anda boleh memilih arahan Sembunyikan daripada Kit Alat Pelanggan (Sembunyikan daripada Alat Pelanggan):

Lajur atau jadual tersembunyi akan hilang daripada anak tetingkap Senarai Medan Jadual Pangsi. Ia sangat mudah jika anda perlu menyembunyikan daripada pengguna beberapa lajur tambahan (contohnya, dikira atau lajur dengan nilai utama untuk mewujudkan hubungan) atau bahkan keseluruhan jadual.

Faedah 7. latih tubi lanjutan

Jika anda mengklik dua kali pada mana-mana sel dalam kawasan nilai dalam jadual pangsi biasa, maka Excel memaparkan pada helaian berasingan salinan serpihan data sumber yang terlibat dalam pengiraan sel ini. Ini adalah perkara yang sangat berguna, secara rasmi dipanggil Drill-down (biasanya mereka menyebut "gagal").

Dalam ringkasan Model Data, alat berguna ini berfungsi dengan lebih halus. Dengan berdiri di atas mana-mana sel dengan hasil yang menarik minat kami, anda boleh mengklik pada ikon dengan kaca pembesar yang muncul di sebelahnya (ia dipanggil Aliran Ekspres) dan kemudian pilih mana-mana medan yang anda minati dalam mana-mana jadual berkaitan:

Selepas itu, nilai semasa (Model = Explorer) akan masuk ke kawasan penapis, dan ringkasan akan dibina oleh pejabat:

Sudah tentu, prosedur sedemikian boleh diulang berkali-kali, secara konsisten menyelidiki data anda ke arah yang anda minati.

Faedah 8: Tukar Fungsi Pivot kepada Kiub

Jika anda memilih mana-mana sel dalam ringkasan untuk Model Data dan kemudian pilih pada tab Analisis jadual pangsi Perintah Alat OLAP – Tukar kepada Formula (Analisis — Alat OLAP — Tukar kepada formula), maka keseluruhan ringkasan akan ditukar kepada formula secara automatik. Kini nilai medan dalam kawasan baris-lajur dan hasil dalam kawasan nilai akan diambil daripada Model Data menggunakan fungsi kubus khas: CUBEVALUE dan CUBEMEMBER:

Secara teknikal, ini bermakna bahawa sekarang kita tidak berurusan dengan ringkasan, tetapi dengan beberapa sel dengan formula, iaitu kita boleh dengan mudah melakukan sebarang transformasi dengan laporan kami yang tidak tersedia dalam ringkasan, contohnya, masukkan baris atau lajur baharu ke tengah. laporan, lakukan apa-apa pengiraan tambahan di dalam ringkasan, susunkannya dalam apa-apa cara yang diingini, dsb.

Pada masa yang sama, sambungan dengan data sumber, sudah tentu, kekal dan pada masa hadapan formula ini akan dikemas kini apabila sumber berubah. Kecantikan!

• Analisis fakta pelan dalam jadual pangsi dengan Power Pivot dan Power Query
• Jadual pangsi dengan pengepala berbilang baris
• Cipta pangkalan data dalam Excel menggunakan Power Pivot