Power Pivot ialah tambahan Excel yang boleh anda gunakan untuk melakukan analisis mendalam dan mencipta model data yang kompleks. Menggunakan Power Pivot, gabungkan sejumlah besar data daripada pelbagai sumber, analisis maklumat dengan cepat dan kongsi data dengan mudah.

Dalam kedua-dua Excel dan Power Pivot, anda boleh mencipta model data, koleksi jadual dengan perhubungan. Model data yang dipaparkan dalam buku kerja dalam Excel adalah model data yang sama yang dipaparkan dalam tetingkap Power Pivot. Semua data yang boleh diimport ke dalam Excel tersedia dalam Power Pivot dan sebaliknya.

Nota: Sebelum menguasai maklumat,

Ciri utama PowerPivot for Excel

Import berjuta-juta baris data daripada berbilang sumber data Dengan Power Pivot for Excel, anda boleh mengimport berjuta-juta baris data daripada berbilang sumber ke dalam buku kerja Excel tunggal, mencipta perhubungan antara data yang berbeza, mencipta lajur dan ukuran yang dikira menggunakan formula, mencipta Jadual Pangsi dan Carta Pangsi, dan kemudian menggerudi ke dalam anda. data untuk membuat keputusan perniagaan yang tepat pada masanya - tanpa bantuan pakar IT.

Buka kunci pengiraan dan analisis pantas Proses berjuta-juta baris pada masa yang sama dengan beribu-ribu dan manfaatkan berbilang pemproses dan GB RAM sepenuhnya untuk pemprosesan pengiraan yang pantas. Menangani had sedia ada untuk menganalisis data besar pada komputer dengan algoritma pemampatan yang cekap untuk memuatkan set data walaupun besar ke dalam memori.

Sokongan sumber data tanpa had Menyediakan rangka kerja untuk mengimport dan menggabungkan data sumber dari mana-mana sahaja untuk menganalisis data besar pada desktop anda, termasuk pangkalan data hubungan, sumber multidimensi, perkhidmatan awan, suapan data, fail Excel, fail teks dan data daripada Internet.

Keselamatan dan Pengurusan Panel Kawalan Power Pivot membolehkan pentadbir IT memantau dan mengurus aplikasi biasa mereka untuk memastikan keselamatan, kesinambungan perniagaan dan produktiviti.

Ungkapan Analisis Data (DAX) DAX ialah bahasa formula yang memanjangkan keupayaan pemprosesan data Excel untuk memasukkan kumpulan, pengiraan dan analisis yang lebih kompleks dan kompleks. Sintaks formula DAX sangat serupa dengan formula Excel.

Tugasan dalam Power Pivot atau Excel

Perbezaan utama antara Power Pivot dan Excel ialah anda boleh mencipta model data yang lebih kompleks dalam tetingkap Power Pivot. Berikut adalah beberapa masalah untuk perbandingan.

Import data daripada pelbagai sumber seperti pangkalan data korporat yang besar, suapan berita awam, hamparan dan fail teks pada komputer tempatan anda.	Import semua data daripada sumber data.	Menapis data dan menamakan semula lajur dan jadual semasa import.
Mencipta Jadual	Jadual boleh diletakkan pada mana-mana helaian buku. Helaian boleh mengandungi berbilang jadual.	Jadual disusun sebagai halaman tab berasingan dalam tetingkap Power Pivot.
Mengedit data dalam jadual	Anda boleh menukar nilai dalam sel jadual individu.	Anda tidak boleh menukar sel individu.
Mewujudkan hubungan antara jadual	Dalam kotak dialog Pautan.	Dalam paparan Carta atau kotak dialog Cipta Perhubungan.
Mencipta Pengiraan	Formula Excel.	Formula lanjutan dalam bahasa Ungkapan Analisis Data (DAX).
Mencipta hierarki		Klik butang Jadual Pangsi dalam tetingkap Power Pivot.
Sambungan model untuk Power View	Model data asas dibuat.	Sambungan tersedia, seperti mentakrifkan medan lalai, imej dan nilai unik.
Menggunakan Visual Basic untuk Aplikasi (VBA)		Jangan gunakan VBA dalam tetingkap Power Pivot.
Mengelompokkan data		Mendapatkan maklumat bantuan maklumat tambahan Anda sentiasa boleh bertanya soalan dalam Komuniti Excel Tech, meminta bantuan dalam komuniti Answers atau mencadangkan ciri baharu atau penambahbaikan pada tapak web Excel User Voice.

Power Pivot ialah tambahan yang boleh digunakan untuk melakukan analisis mendalam dalam Excel. Alat tambah terbina dalam beberapa versi Office, tetapi tidak didayakan secara lalai.

Untuk senarai versi Office, termasuk Power Pivot, serta senarai versi yang tidak disyorkan, lihat artikel: Di manakah PowerPivot?

Di bawah ialah cara mendayakan Power Pivot sebelum menggunakannya buat kali pertama.

Pergi ke tab Fail > Pilihan > Alat tambah.

Di padang Kawalan pilih Tambahan COM dan tekan Pergi.

Semak kotak Microsoft Office Power Pivot dan tekan butang okey. Jika versi lain Power Pivot dipasang, ia juga akan disenaraikan dalam senarai tambahan COM. Pilih alat tambah Power Pivot untuk Excel.

Tab Power Pivot muncul pada reben.

Buka tetingkap Power Pivot.

Tetingkap Power Pivot akan dibuka. Di sini, anda boleh mengklik butang Data Luaran untuk menggunakan Wizard Import Jadual untuk menapis data semasa anda menambahkannya pada fail, mencipta perhubungan antara jadual, memperkayakan data dengan pengiraan dan ungkapan serta mencipta jadual pangsi dan carta pangsi daripadanya.

Penyelesaian masalah: Reben Power Pivot Hilang

Dalam sesetengah kes, reben Power Pivot akan muncul dalam menu jika Excel mengesan bahawa tambahan kestabilan berada dalam Microsoft Excel. Ini boleh berlaku jika Excel ranap semasa tetingkap Power Pivot dibuka. Untuk memulihkan ke menu Power Pivot, lakukan yang berikut:

Pilih Fail > Pilihan > Alat tambah.

Di padang Kawalan pilih Objek yang dilumpuhkan > Pergi.

Pilih Microsoft OfficePower Pivot dan tekan butang Hidupkan.

Jika anda tidak dapat memulihkan reben Power Pivot menggunakan langkah di atas atau reben hilang apabila anda menutup dan membuka semula Excel, lakukan perkara berikut:

tutup Excel;

buka menu Mulakan > Laksanakan dan masukkan arahan regedit;

Dalam Editor Pendaftaran, kembangkan bahagian berikut:

Untuk Excel 2013: HKEY_CURRENT_USER > Perisian > Microsoft > Pejabat > 15.0 > Tetapan Pengguna.

Untuk Excel 2016: HKEY_CURRENT_USER > Perisian > Microsoft > Pejabat > 16.0 > Tetapan Pengguna

Klik kanan PowerPivotExcelAddin dan kemudian klik Padam;

kembali ke bahagian atas Editor Pendaftaran;

meluaskan bahagian HKEY_CURRENT_USER > Perisian > Microsoft > Pejabat > Excel > Addins;

Klik kanan PowerPivotExcelClientAddIn.NativeEntry.1 dan kemudian klik Padam;

tutup editor pendaftaran;

buka Excel;

Dayakan alat tambah dengan mengikuti langkah pada permulaan artikel ini.

Istilah "Power Query", "Power Pivot", "Power BI" dan "kuasa" lain semakin muncul dalam artikel dan bahan tentang Microsoft Excel. Dalam pengalaman saya, tidak semua orang memahami dengan jelas apa yang tersembunyi di sebalik konsep ini, bagaimana ia saling berkaitan dan bagaimana ia boleh membantu pengguna Excel yang mudah.

Mari kita bersihkan keadaan.

Power Query

Kembali pada tahun 2013, kumpulan pembangun yang dicipta khas di dalam Microsoft mengeluarkan tambahan percuma untuk Excel Power Query(nama lain - Penjelajah Data, Dapatkan&Transformasi), yang boleh melakukan banyak perkara yang berguna untuk kerja harian:

• Muatkan data dalam Excel daripada hampir 40 sumber berbeza, termasuk pangkalan data (SQL, Oracle, Access, Teradata...), sistem ERP korporat (SAP, Microsoft Dynamics, 1C...), perkhidmatan Internet (Facebook, Google Analytics , hampir mana-mana tapak ).
• Kumpul data daripada fail semua jenis data utama (XLSX, TXT, CSV, JSON, HTML, XML...), secara individu dan pukal - daripada semua fail dalam folder yang ditentukan. Daripada buku kerja Excel, anda boleh memuat turun data secara automatik daripada semua helaian sekaligus.
• Bersihkan menerima data daripada "sampah": lajur atau baris tambahan, ulangan, maklumat perkhidmatan dalam "pengepala", ruang tambahan atau aksara tidak boleh dicetak, dsb.
• Bawa data ke dalam pesanan: betulkan kes, nombor-sebagai-teks, isi tempat kosong, tambah "pengepala" jadual yang betul, buka teks "terlekat bersama" ke dalam lajur dan lekatkannya semula, bahagikan tarikh kepada komponen, dsb.
• Dalam setiap cara yang mungkin mengubah jadual, membawanya ke dalam bentuk yang diingini (tapis, isih, tukar susunan lajur, transpose, tambah jumlah, kembangkan tab silang menjadi rata dan runtuhkannya kembali).
• Gantikan data dari satu jadual ke jadual lain berdasarkan kebetulan satu atau lebih parameter, i.e. menggantikan fungsi dengan sempurna VLOOKUP dan analognya.

Power Query ditemui dalam dua versi: sebagai tambahan berasingan untuk Excel 2010-2013, yang boleh dimuat turun dari tapak web rasmi Microsoft dan sebagai sebahagian daripada Excel 2016. Dalam kes pertama, selepas pemasangan, tab berasingan muncul dalam Excel:

Dalam Excel 2016, semua fungsi Power Query telah terbina dalam secara lalai dan terletak pada tab Data sebagai satu kumpulan Dapatkan & Ubah:

Keupayaan pilihan ini adalah sama sepenuhnya.

Ciri asas Power Query ialah semua tindakan untuk mengimport dan mengubah data disimpan dalam bentuk pertanyaan - urutan langkah dalam bahasa pengaturcaraan dalaman Power Query, yang secara ringkasnya dipanggil "M". Langkah sentiasa boleh diedit dan dimainkan semula beberapa kali (pertanyaan kemas kini).

Tetingkap Power Query utama biasanya kelihatan seperti ini:

Pada pendapat saya, ini adalah alat tambah yang paling berguna untuk pelbagai pengguna daripada semua yang disenaraikan dalam artikel ini. Banyak tugasan yang sebelum ini anda terpaksa memutarbelitkan formula atau menulis makro kini dengan mudah dan cantik dilakukan dalam Power Query. Selain itu, dengan pengemaskinian automatik seterusnya keputusan. Dan memandangkan ia adalah percuma, dari segi nisbah kualiti harga, Power Query sememangnya tiada tandingan dan mutlak mesti ada untuk mana-mana pengguna Excel lanjutan pertengahan hari ini.

Power Pivot

Power Pivot juga merupakan tambahan untuk Microsoft Excel, tetapi bertujuan untuk tugasan yang sedikit berbeza. Jika Power Query tertumpu pada pengimportan dan pemprosesan, maka Power Pivot diperlukan terutamanya untuk analisis kompleks bagi volum data yang besar. Sebagai anggaran pertama, anda boleh menganggap Power Pivot sebagai jadual pangsi yang dikuasakan.

Prinsip umum bekerja dalam Power Pivot adalah seperti berikut:

1. Kami yang pertama memuatkan data dalam Power Pivot - 15 sumber berbeza disokong: pangkalan data biasa (SQL, Oracle, Access...), fail Excel, fail teks, suapan web data. Selain itu, anda boleh menggunakan Power Query sebagai sumber data, yang menjadikan analisis hampir omnivor.
2. Kemudian di antara meja yang dimuatkan sambungan sedang disediakan atau, seperti yang mereka katakan, ia dicipta Model Data. Ini akan membolehkan anda membina laporan pada masa hadapan pada mana-mana medan daripada jadual sedia ada seolah-olah ia satu jadual. Dan tiada VLOOKUP lagi.
3. Jika perlu, pengiraan tambahan ditambahkan pada Model Data menggunakan lajur yang dikira(bersamaan dengan lajur dengan formula dalam "jadual pintar") dan langkah-langkah(bersamaan dengan medan terkira dalam medan ringkasan). Semua ini ditulis dalam bahasa Power Pivot dalaman khas yang dipanggil DAX (Ekspresi Analisis Data).
4. Pada helaian Excel, laporan yang kami minati dibina menggunakan Model Data dalam borang jadual pangsi dan gambar rajah.

Tetingkap Power Pivot utama kelihatan seperti ini:

Dan inilah rupa Model Data, i.e. semua jadual dimuatkan dengan perhubungan yang dibuat:

Power Pivot mempunyai beberapa ciri yang menjadikannya alat unik untuk tugasan tertentu:

• Dalam Power Pivot tiada had bilangan baris(seperti dalam Excel). Anda boleh memuatkan jadual dalam sebarang saiz dan bekerja dengannya dengan tenang.
• Power Pivot sangat mahir memampatkan data apabila memuatkannya ke dalam Model. Fail teks asal 50 MB boleh bertukar menjadi 3-5 MB dengan mudah selepas dimuat turun.
• Memandangkan Power Pivot pada asasnya mempunyai enjin pangkalan data yang lengkap di bawah hud, ia boleh mengendalikan jumlah maklumat yang besar sangat laju. Adakah anda perlu menganalisis 10-15 juta rekod dan membina ringkasan? Dan semua ini pada komputer riba lama? Tiada masalah!

Malangnya, Power Pivot belum lagi disertakan dalam semua versi Excel. Jika anda mempunyai Excel 2010, anda boleh memuat turunnya secara percuma dari tapak web Microsoft. Tetapi jika anda mempunyai Excel 2013-2016, maka semuanya bergantung pada lesen anda, kerana dalam sesetengah versi ia didayakan (Office Pro Plus, sebagai contoh), dan dalam versi lain ia tidak (Office 365 Home, Office 365 Personal, dll.) Anda boleh mengetahui lebih lanjut mengenai perkara ini.

Peta Kuasa

Alat tambah ini mula-mula muncul pada tahun 2013 dan pada asalnya dipanggil GeoFlow. Ia bertujuan untuk visualisasi geo-data, i.e. maklumat berangka pada peta geografi. Data sumber untuk paparan diambil daripada Model Data Power Pivot yang sama (lihat perenggan sebelumnya).

Versi demo Power Map (hampir tidak berbeza daripada versi penuh dari segi keupayaan, dengan cara itu) boleh dimuat turun sepenuhnya secara percuma, sekali lagi dari tapak web Microsoft. Versi penuh disertakan dalam beberapa pakej Microsoft Office 2013-2016 bersama dengan Power Pivot - dalam bentuk butang Peta 3D pada tab Sisip - Peta 3D:

Ciri utama Power Map:

Alat tambah ini muncul buat kali pertama sebagai sebahagian daripada Excel 2013 dan direka bentuk untuk "menghidupkan semula" data anda - membina graf, carta, peta dan jadual interaktif. Kadang-kadang istilah yang digunakan untuk ini papan pemuka atau kad skor. Intinya ialah anda boleh memasukkan helaian khas tanpa sel ke dalam fail Excel anda - slaid Power View, di mana anda boleh menambah teks, gambar dan pelbagai jenis visualisasi berdasarkan data anda daripada Model Data Power Pivot.

Ia akan kelihatan seperti ini:

Nuansa di sini ialah:

• Data sumber diambil dari tempat yang sama - daripada Model Data Power Pivot.
• Untuk bekerja dengan Power View, anda perlu memasang Silverlight pada komputer anda - analog Microsoft Flash (percuma).

Dengan cara ini, di laman web Microsoft terdapat kursus latihan yang sangat baik tentang Power View dalam bahasa Rusia.

Kuasa BI

Tidak seperti yang sebelumnya, Power BI bukanlah tambahan untuk Excel, tetapi produk berasingan yang mewakili rangkaian keseluruhan alat untuk analisis dan visualisasi perniagaan. Ia terdiri daripada tiga elemen utama:

1. Desktop Power BI- program untuk analisis data dan visualisasi, yang termasuk, antara lain, semua fungsi Power Query dan alat tambah Power Pivot + mekanisme visualisasi yang dipertingkatkan daripada Power View dan Power Map. Anda boleh memuat turun dan memasangnya secara percuma dari tapak web Microsoft.

Dalam Desktop Power BI anda boleh:

• Muatkan data daripada lebih daripada 70 sumber berbeza(sama seperti Power Query + penyambung tambahan).
• ikat jadual ke dalam model (seperti dalam Power Pivot)
• Tambah pengiraan tambahan kepada data menggunakan langkah-langkah Dan lajur yang dikira dalam DAX(seperti dalam Power Pivot)
• Cipta imej yang cantik berdasarkan data laporan interaktif dengan pelbagai jenis visualisasi (sangat serupa dengan Power View, tetapi lebih baik dan lebih berkuasa).
• Menerbitkan membuat laporan di tapak web Perkhidmatan Power BI (lihat bahagian seterusnya) dan berkongsinya dengan rakan sekerja. Selain itu, adalah mungkin untuk memberikan hak yang berbeza (membaca, menyunting) kepada orang yang berbeza.

2. Perkhidmatan dalam talian Power BI- Ringkasnya, ini ialah tapak di mana anda dan setiap pengguna dalam syarikat anda akan mempunyai "kotak pasir" (ruang kerja) mereka sendiri di mana anda boleh memuat naik laporan yang dibuat dalam Desktop Power BI. Selain melihat, ia juga membolehkan anda mengeditnya, menghasilkan semula dalam talian hampir semua fungsi Desktop Power BI. Anda juga boleh meminjam visualisasi individu daripada laporan orang lain di sini, mengumpul papan pemuka anda sendiri daripada mereka.

Ia kelihatan seperti ini:

3. Mudah Alih Power BI- aplikasi untuk iOS / Android / Windows untuk menyambung ke Power BI Service dan melihat (bukan mengedit) laporan dan papan pemuka yang dibuat dengan mudah pada skrin telefon atau tablet anda. Muat turunnya (percuma sepenuhnya).

Pada iPhone, sebagai contoh, laporan yang dijana di atas kelihatan seperti ini:

Dan selama ini mengekalkan interaktiviti dan animasi + disesuaikan untuk sentuhan dan lukisan pada skrin dengan stylus. Sangat selesa. Oleh itu, analitik perniagaan menjadi tersedia kepada semua orang penting syarikat pada bila-bila masa dan di mana-mana sahaja - anda hanya perlukan akses Internet.

Pelan harga Power BI. Desktop Power BI dan Mudah Alih adalah percuma untuk bermula, begitu juga dengan kebanyakan ciri Perkhidmatan Power BI. Jadi untuk kegunaan peribadi atau penggunaan dalam syarikat kecil, anda tidak perlu membayar sesen pun untuk semua perkara di atas dan anda boleh kekal dalam pelan dengan selamat Percuma. Jika anda ingin berkongsi laporan dengan rakan sekerja dan mengurus hak akses mereka, anda perlu beralih kepada Pro($10 sebulan setiap pengguna). Adakah ada lagi Premium- untuk syarikat besar (>500 pengguna) yang memerlukan storan dan kapasiti pelayan yang berasingan untuk data.

Pautan berkaitan

Hebat.
Satu-satunya kelemahan yang ketara dalam Power BI untuk organisasi (tempat saya bekerja) ialah penyimpanan data semasa memuatkan BI pada pelayan Microsoft.
Sebenarnya, daripada berkomunikasi dengan rakan sekerja, menjadi jelas kepada saya bahawa ini menjadi "halangan" apabila memilih sistem BI.
Rakan sekerja memilih Tableau.
(Mungkin kini sesuatu telah berubah dalam 1.5 - 2 tahun)
Bagi PP, PQ, saya menantikan artikel baru dengan penuh rasa syukur dan tidak sabar. Saya satu-satunya (setakat ini) dalam syarikat saya yang menggunakannya secara aktif dan menunjukkan hasil dalam amalan (saya baru-baru ini mengumpul 4.5 juta baris dalam masa 10 minit, mengejutkan rakan sekerja saya).
Bagus.
Tidak lama lagi saya akan pergi ke kursus anda, sekarang saya memilih kursus tentang pemodelan dan membuat Dax menetap di kepala saya.

Power Pivot ialah teknologi pemodelan data yang membolehkan anda membuat model data, mewujudkan perhubungan dan menambah pengiraan. Dengan Power Pivot, anda boleh bekerja dengan set data yang besar, mencipta perhubungan terperinci dan membuat pengiraan yang kompleks (atau mudah)—semuanya dalam persekitaran Excel berprestasi tinggi yang biasa.

Power Pivot ialah salah satu daripada tiga alat analisis data yang tersedia dalam Excel:

Sumber Power Pivot

Bagaimana untuk mendapatkan Power Pivot?

Power Pivot boleh digunakan sebagai tambahan Excel yang boleh didayakan dalam beberapa langkah mudah. Teknologi pemodelan asas Power Pivot juga digunakan dalam Power BI Designer, yang merupakan sebahagian daripada perkhidmatan Power BI yang ditawarkan oleh Microsoft.

Bermula dengan Power Pivot

Bila Alat tambah Power Pivot didayakan, tab muncul pada reben Power Pivot, yang ditunjukkan dalam imej berikut.

Pada tab Reben Power Pivot"pilih elemen" Kawalan"Dalam bab" model data ".

Apabila memilih item Kawalan muncul Tetingkap Power Pivot, di mana anda boleh melihat dan memanipulasi model data, menambah pengiraan, membuat sambungan dan melihat elemen model data Power Pivot. Model data ialah koleksi jadual atau data lain, selalunya dengan perhubungan yang mantap antara mereka. Gambar berikut menunjukkan Tetingkap Power Pivot dengan jadual yang dipaparkan.

DALAM Tetingkap Power Pivot Ia juga mungkin untuk mewujudkan dan menyatakan hubungan secara grafik antara data yang disertakan dalam model. Mengklik ikon perwakilan rajah di sudut kanan bawah Tetingkap Power Pivot, anda akan melihat perhubungan sedia ada dalam model data Power Pivot. Gambar di bawah menunjukkan Tetingkap Power Pivot V pembentangan rajah.

Untuk tutorial pantas tentang menggunakan Power Pivot, lihat artikel berikut:

Sebagai tambahan kepada panduan ini, dalam pautan berikut anda akan dapati menyeluruh Koleksi pautan, sumber dan maklumat tambahan tentang Power Pivot:

Bahagian berikut menyenaraikan sumber dan tutorial tambahan yang memperincikan cara menggunakan Power Pivot, termasuk digabungkan dengan Power Query dan Power View, untuk melaksanakan tugasan risikan perniagaan yang kompleks dan intuitif dalam Excel anda sendiri.

Tutorial PowerPivot

Lihat Power Pivot dalam tindakan untuk membantu anda memahami cara ia berfungsi dan melihat kes penggunaan berguna yang menunjukkan keupayaan Power Pivot. Panduan berikut akan membantu anda bermula:

Ketahui lebih lanjut tentang Power Pivot

Power Pivot mudah digunakan dan pantas. Tambahan ini membolehkan anda membuat pengiraan, penunjuk dan formula yang kaya dan kompleks. Pautan di bawah akan membantu anda mengenal pasti banyak tugas yang boleh anda lakukan dengan Power Pivot.

Pautan ke forum dan topik berkaitan

Ramai orang yang berbeza menggunakan Power Query dan mereka gembira untuk berkongsi perkara yang mereka pelajari. Gunakan sumber di bawah untuk berhubung dengan orang lain dalam komuniti Power Query.

Excel Power Pivot ialah alat yang membolehkan anda mencipta sistem risikan perniagaan. Dalam artikel ini kita akan melihat metodologi untuk menentukan penunjuk yang sama dari bidang pelaporan pengurusan - pertama berdasarkan formula Excel, kemudian menggunakan Excel Power Pivot untuk menilai semua keupayaan dan kelebihannya.

Apa itu Power Pivot

Excel Power Pivot ialah alat yang membolehkan pengguna mencipta sistem risikan perniagaan mereka sendiri menggunakan Excel - berdasarkan pangkalan data jadual.

Sebagai peraturan, jadual pangsi, pada mulanya dibina ke dalam Excel, tidak memerlukan sebarang pengetahuan khusus dan tidak boleh diganti jika anda memerlukan analitik visual dalam Excel. Buku membolehkan anda menyimpan data dalam jadual berasingan jika mahu. Selain itu, Excel menyokong pengendali untuk bekerja dengan tatasusunan data - kit alat yang sangat berguna untuk analitik.

Alat tambah Excel Power Pivot hanya berfungsi dalam Office 2010 dan lebih tinggi dan mempunyai beberapa ciri:

• melakukan semua pengiraan secara langsung dalam RAM komputer, yang membolehkan prestasi sistem yang tinggi, serta keupayaan untuk memproses sejumlah besar data (saiz jadual sumber boleh mencapai berjuta-juta baris);
• membolehkan anda membuat jadual pangsi pada pangkalan anda. Nilai aplikasi Excel PowerPivot untuk pembiaya dan penganalisis terletak pada hakikat bahawa dengan bantuan tambahan anda kini boleh memproses secara bebas jumlah maklumat sedemikian yang sebelum ini memerlukan penggunaan perisian khusus (DBMS) dan penglibatan pakar IT yang berkelayakan ;
• mengandungi bahasa ekspresi analisis data (Ekspresi Analisis Data, disingkat sebagai DAX), yang direka untuk membangunkan peraturan logik perniagaan baharu. Ringkasnya, ia boleh dianggap sebagai versi lanjutan formula tatasusunan. Formula DAX direka khusus untuk sedekat mungkin dengan sintaks Excel. Walaupun serupa dengan fungsi lembaran kerja Excel standard, formula DAX hanya ditulis dalam cangkerang Power Pivot. Bahasa DAX mempunyai pelbagai keupayaan, dan maklumat tentang pelbagai cara untuk menggunakannya tersedia di Internet.

Seperti yang ditunjukkan oleh amalan, populariti teknologi tertentu selalunya bergantung bukan pada tahap dan pelbagai masalah yang diselesaikan dengan bantuannya, tetapi pada seberapa berkesan dan ringkas anda boleh beralih kepadanya menggunakan pengetahuan dan pengalaman anda. Artikel ini membincangkan skim untuk mencipta penunjuk yang sama daripada bidang pelaporan pengurusan - pertama berdasarkan formula Excel, kemudian menggunakan arahan DAX. Ini akan memastikan bahawa bekerja dengan Excel Power Pivot adalah sama seperti bekerja dengan hamparan Excel biasa.

Sebagai contoh, mari kita pilih penunjuk "Tahap pendapatan pelanggan bulanan", yang membolehkan kita menilai rakan niaga syarikat mengikut tahap keuntungan. Dengan bantuannya, menjadi jelas bahagian jumlah hasil yang datang daripada pelanggan pelbagai jenis, pengagihan kuantitatif mereka ke dalam kumpulan, dinamik perubahan dalam pangkalan pelanggan, dll. Walau bagaimanapun, pengiraan penunjuk sedemikian adalah penuh dengan kesukaran tertentu:

1. Dalam satu tempoh kalendar, pelanggan boleh menggunakan beberapa perkhidmatan sekaligus (membeli beberapa barangan) - oleh itu, untuk mengira jumlah tahap keuntungan, adalah perlu untuk menjumlahkan pendapatan yang diterima daripada semua perkhidmatan yang diberikan kepada pelanggan dalam tempoh yang dianalisis;
2. Bergantung pada jumlah pendapatan yang dikira, pelanggan mesti ditugaskan kepada salah satu kumpulan yang telah ditetapkan.

Dengan mengambil kira keistimewaan syarat pengiraan, penunjuk "Tahap Keberuntungan" akan menjadi contoh yang baik untuk mengkaji logik pelaksanaan perintah yang mendasari bahasa DAX.

Rujukan

Menggunakan formula tatasusunan

Mari kita ingat bahawa sebagai tambahan kepada fungsi lembaran kerja biasa (susunan argumen input - satu nilai output, yang ditulis pada sel jadual), terdapat kelas fungsi berasingan yang dipanggil formula tatasusunan (susunan argumen input - tatasusunan daripada nilai keluaran). Pengendali sedemikian dimasukkan serta-merta untuk julat sel. Dalam kes ini, nilai elemen tertentu bergantung bukan sahaja pada komponen biasa kepada semua (satu formula pengiraan), tetapi juga pada tempatnya dalam tatasusunan (konteks pelaksanaan yang dipanggil). Ia adalah konteks pelaksanaan yang merupakan salah satu asas di mana logik pengiraan analitik terletak. Oleh itu, di bawah kami akan menerangkan secara ringkas apakah prinsip operasinya dalam Excel.

Mari kita lihat ungkapan (lihat Rajah 1). Parameter inputnya ialah dua julat sel: "Baris", diserlahkan dengan warna kuning dan "Lajur" dengan isian biru. Di kawasan yang dihadkan oleh julat ini, formula tatasusunan dalam bentuk berikut diperkenalkan: (=Lajur Baris).

Formula ini ialah perwakilan padat operasi menambah pasangan elemen daripada tatasusunan asal. Seperti yang dapat dilihat daripada rajah, ia mempunyai ejaan yang sama untuk semua elemen julat daripada skop. Dalam kes ini, nilai dalam sel jadual yang dihasilkan berbeza antara satu sama lain, yang dijelaskan oleh konteks pelaksanaan. Sebagai contoh, sel E4 yang diserlahkan dalam rajah terletak dalam lajur ketiga dan baris kedua tatasusunan, yang sepadan dengan elemen kedua tatasusunan Baris dan elemen ketiga tatasusunan Lajur.

Mod pengiraan yang diterangkan tidak kelihatan agak biasa, kerana dalam formula tidak ada petunjuk jelas sel mana yang harus dipilih untuk penambahan. Oleh itu, menguasai teknik ini mengambil sedikit masa. Tetapi selepas mempelajari teknik ini, pengguna akan dapat mencipta ungkapan untuk persekitaran OLAP berbilang dimensi. Oleh itu, keupayaan asas program Excel dikembangkan dengan menambah pengiraan analitikal berdasarkan sintaks formula lembaran kerja.

Bekerja dengan formula Excel standard

Katakan kita mempunyai laporan hasil syarikat, yang diperincikan oleh tiga penganalisis (rakan niaga, jenis perkhidmatan dan tempoh kalendar).

Dalam Rajah 2, data sumber dibentangkan dalam lajur "Syarikat", "Perkhidmatan", "Tempoh", "Pendapatan". Kami ingin mencipta dua lajur baru yang dikira "Jumlah_Pendapatan", di mana jumlah pendapatan untuk pelanggan dalam tempoh tertentu dikira, serta "Tahap_Pendapatan", yang, bergantung pada tahap "Jumlah_Pendapatan", menentukan kumpulan pendapatan (pendapatan tahap ditunjukkan di sebelah kanan Rajah 2).

Untuk kemudahan dan kejelasan pengiraan seterusnya, kami segera menukar data sumber kepada mod "Jadual". Untuk melakukan ini, kami akan menggunakan arahan yang sepadan pada tab "Sisipkan" dan berikan nama kami sendiri "Fact_Table".

Matlamat kami. Untuk setiap baris jadual fakta, hitung jumlah pendapatan yang sepadan dengan pelanggan dan bulan. Sebagai contoh, "Pelanggan A" pada Februari 2013 telah disediakan dengan tiga perkhidmatan dalam jumlah 15, 15 dan 25 unit. Oleh itu, dalam setiap baris yang berkaitan dengan tempoh tertentu, nilai yang sama mesti dimasukkan - 55 unit (15 + 15 + 25). Operasi ini dilakukan dalam dua peringkat.

Peringkat 1. Di antara semua baris jadual fakta, mereka yang nilai atribut "Syarikat" dan "Tempoh" sepadan dengan baris semasa ditapis.

Peringkat 2. Nilai atribut "Pendapatan" dalam set ditapis yang terhasil disimpulkan.

Fungsi SUMIFS sangat sesuai untuk tugas ini, membolehkan anda menjumlahkan julat merentasi beberapa keadaan sekaligus. Soalan utama ialah bagaimana untuk memasukkan formula ini ke dalam lajur yang dikira. Lagipun, dalam jadual, sebarang formula mesti dinyatakan untuk semua baris sekaligus. Kita perlu mentakrifkan hanya satu, tetapi konteks pelaksanaannya berubah bergantung pada parameter baris aktif.

Mari masukkan ungkapan 1 ke dalam mana-mana baris lajur "Jumlah_Pendapatan" Hanya tekan Enter, selepas itu ia akan dimuatkan dalam semua baris jadual.

Ungkapan 1:
SUMIFS([Hasil]; [Syarikat]; Jadual_Fakta[[#baris ini];[Syarikat]]; [Tempoh]; Jadual_Fakta[[#baris ini]; [Tempoh]])

Mari lihat bagaimana formula berfungsi. Setiap kali anda memanggil (dalam setiap baris) fungsi SUMIFS, keseluruhan jadual fakta (lajur "Hasil"nya) digunakan sekali gus. Tetapi pada setiap lelaran, hanya baris yang sepadan dengan nilai atribut baris semasa dijumlahkan. Untuk melakukan ini, hujah khas telah ditambahkan pada formula - [# Baris ini], yang bertindak sebagai pautan ke baris semasa. Khususnya, untuk baris yang berkaitan dengan "Pelanggan, A" dan Februari 2013, nilai parameter [[#This row];[Company]] akan sama dengan "Client, A", dan [[#This row]; [Tempoh]] - “02/01/2013”.

Jadi, kami telah menerangkan semua pengiraan yang diperlukan dengan hanya satu formula. Dalam kes ini, argumen input formula berubah secara dinamik bergantung pada tempat ia dipanggil.

Ambil perhatian bahawa format rakaman yang dibentangkan berbeza dengan ketara daripada mod Excel standard, di mana parameter input dinyatakan secara eksplisit dalam formula. Sebagai contoh, kita boleh menukar jadual fakta kembali kepada julat biasa (Tukar kepada Julat pada tab Alat Jadual). Kemudian, sebagai contoh, ungkapan 1 untuk baris 5 akan mengambil bentuk ungkapan 2:

Ungkapan 2:
=SUMIFS(Julat_Biasa!$D$2:$D$37; Julat_Biasa!$A$2:$A$37; Julat_Biasa!$A5; Julat_Biasa!$C$2:$C$37; Julat_Biasa!$C5)

Untuk baris 6, ungkapan akan kelihatan sama, dengan satu-satunya perbezaan bahawa bukannya $A5 akan ada $A6 (dan bukannya $C5 - $C6). Dalam kes ini, hasil pengiraan ungkapan 2 bertepatan dengan nilai ungkapan 1 dalam baris yang sepadan, yang membolehkan kita menganggapnya sama.

Sekarang kita perlu menyusun pendapatan bulanan pelanggan mengikut kumpulan. Berdasarkan penunjuk keuntungan integral yang dikira sebelum ini, kita mesti memilih baris yang sepadan dalam jadual luaran (berbanding dengan jadual fakta). Operasi ini mudah dilakukan dalam Excel kerana ia mempunyai banyak pengendali untuk bekerja dengan tatasusunan dan julat.

Khususnya, fungsi INDEX membolehkan kita memperoleh nilai dengan mudah daripada tatasusunan dua dimensi dalam mana-mana sel buku kerja kita hanya perlu mengetahui pengecam objek yang sepadan. Mari tulis ungkapan 3 dalam lajur "Tahap_Pendapatan":

Ungkapan 3:
=INDEX(Tahap[Tahap]; MATCH(Jadual_Fakta[[#baris ini]; [Jumlah_Pendapatan]]; Tahap[Mula]))

Formula yang dibentangkan dalam ungkapan 3, seperti ungkapan 1, dilaksanakan dalam konteks semasa jadual fakta - ia menggunakan rujukan kepada baris aktif. Di samping itu, menggunakan fungsi INDEX kita mengakses objek luaran - jadual "Tahap". Di dalamnya, menggunakan fungsi SEARCH, rentetan yang sesuai dicari.

Mari kita perhatikan sekali lagi bagaimana kami berjaya mendapatkan data daripada jadual luaran: kami memilih fungsi yang sesuai dan menentukan pengecam objek yang dikehendaki di dalamnya sebagai hujah.

Bekerja dengan Excel Power Pivot

Mari lihat cara Power Pivot melaksanakan operasi yang diterangkan dalam bahagian pertama artikel.

Memasang Excel Power Pivot. Mula-mula, add-on mesti dipasang (anda boleh memuat turunnya secara percuma dari laman web rasmi Microsoft). Tab "Power Pivot" baharu sepatutnya muncul pada reben Excel. Jika add-in tidak didayakan serta-merta, anda perlu mendayakannya secara manual: "Excel Options" - "Add-Ins", dalam senarai "Urus", pilih "COM Add-in" - "Go". Apa yang anda perlu lakukan ialah tandakan kotak di sebelah "Power Pivot for Excel". Tetingkap aktif hanya dalam dokumen format xlsx.

Penyediaan data awal. Pangkalan data PowerPivot menyokong pelbagai jenis sumber. Khususnya, anda boleh memuatkan data ke dalamnya daripada MS Access atau SQL Server DBMS. Dalam kes ini, jumlah maklumat yang diproses boleh berjumlah berjuta-juta baris. Tetapi bagi pengguna biasa, keupayaan untuk menyimpan maklumat sumber pada helaian dalam buku kerja Excel biasa adalah lebih berharga.

Dalam contoh yang kita pertimbangkan dalam artikel, hanya dua jadual digunakan - jadual fakta, serta jadual rujukan tambahan kecil dengan tahap keuntungan. Untuk menambahkannya pada model Power Pivot, anda hanya perlu memenuhi dua syarat:

1. Letakkan meja pada helaian buku yang berasingan. Ambil perhatian bahawa keperluan ini berbeza daripada mod pengendalian Excel standard, yang membenarkan penempatan bilangan jadual tanpa had yang menerangkan entiti berbeza pada satu helaian.

2. Simpan jadual secara berasingan daripada fail laporan ringkasan. Jika perlu, jadual dengan data malah boleh diletakkan dalam buku kerja Excel yang berbeza - Power Pivot boleh menyokong berbilang sambungan terbuka selari kepada sumber data jenis tertentu.

Apabila menyalin data sumber ke helaian baharu, adalah disyorkan untuk segera memberikan nama bermaklumat kepada helaian yang terakhir. Apabila anda kemudiannya mengimport data ke dalam Power Pivot, nama objek asal menjadi nama jadual yang sepadan dalam model.

Seterusnya, buat buku kerja Excel baharu, yang akan kami panggil "Data Asal". Mari letakkan jadual dengan data awal pada satu helaian yang dipanggil "Table_Facts", dan pada helaian lain "Levels" - jadual rujukan dengan tahap keuntungan.

Sekarang mari buat buku kerja Excel lain, panggil tetingkap PowerPivot di dalamnya dan import data daripada fail "Data Asal" ke dalamnya. Untuk mengimport data, anda perlu memanggil perintah "Dapatkan data luaran daripada sumber lain" dalam menu "Fail", dan kemudian jenis "Fail Excel", menjalankan Wizard Import Data.

Pada peringkat pertama kerjanya, kami menunjukkan lokasi fizikal fail data. Kami mengesyorkan agar anda segera mendayakan pilihan "Gunakan baris pertama untuk tajuk lajur" - kemudian lajur PowerPivot akan menerima nama lajur secara automatik daripada buku kerja Excel.

Pada peringkat kedua, kami memilih jadual yang akan digunakan dalam model. Dalam kes kita, kita harus memilih kedua-duanya.

Hasil daripada operasi yang dilakukan, jadual sumber harus muncul dalam tetingkap Power Pivot (lihat Rajah 3). Anda boleh bertukar antara mereka dengan memilih tab yang sesuai di sudut kiri bawah tetingkap.

Dalam setiap jadual, anda boleh mencipta dua jenis objek: lajur terhitung dan medan terhitung.

Lajur yang dikira bertindak sebagai dimensi dalam Jadual Pangsi—ia mengandungi nilai yang kemudiannya diletakkan dalam kawasan baris atau lajur pada reka letak laporan.

Medan terkira - ukuran, iaitu, penunjuk agregat yang diletakkan di dalam ruang kerja laporan - ditakrifkan dalam kawasan pengiraan dalam tetingkap paparan data Power Pivot, kawasan ini diletakkan di bawah tahap data jadual.

Dalam artikel ini, kami tidak akan menyentuh isu membuat medan tersuai, tetapi hanya akan mempertimbangkan untuk menambah lajur terkira baharu pada model. Dalam Power Pivot, mereka bertindak sebagai analog lengkap lajur yang dikira dalam jadual Excel. Khususnya, untuk semua elemen satu lajur, formula tunggal dalam bahasa DAX sentiasa ditakrifkan, yang dimasukkan dalam tetingkap formula Model Builder.

Seperti jadual biasa, formula DAX dilaksanakan dalam konteks baris. Tetapi tidak seperti jadual, tidak perlu menyatakan parameter [# This line] secara eksplisit. Ringkasnya, walaupun tanpa menulis parameter [# This line] ke dalam formula DAX, anda boleh menganggap bahawa ia ada di dalamnya dan menggunakannya dalam pengiraan. Walaupun ciri ini menjadikan formula DAX kurang visual, ia membolehkan ekspresi ditulis dengan lebih padat.

Dengan mengambil kira ciri yang disenaraikan untuk bekerja dengan formula DAX, kami akan mencipta lajur baharu "Hasil" dengan formula berikut (ungkapan 4):

Ungkapan 4:
=KIRA(jumlah([Jumlah]); ALLEXCEPT("Jadual_Fakta"; "Jadual_Fakta"[Syarikat]; "Jadual_Fakta"[Tempoh]))

Mari kita lihat prinsip operasi ungkapan 4.

Pertama, untuk setiap baris jadual fakta, operator ini menerima sebagai input jadual sementara yang bertepatan dengan jadual fakta itu sendiri. Penapis yang ditakrifkan oleh konteks semasa kemudian digunakan pada jadual pementasan ini. Ia mengandungi baris yang atributnya bertepatan dengan nilai baris semasa: [[#This line]; [Syarikat]], [[#Barisan ini]; [Perkhidmatan]], [[#Barisan ini]; [Tempoh]], [[#Baris ini]; [Jumlah]].

Walau bagaimanapun, ungkapan ALLEXCEPT ("Fact_Table"; "Fact_Table" [Syarikat]; "Fact_Table" [Tempoh]) memerlukan penapis konteks semasa dikosongkan untuk semua lajur kecuali [Syarikat] dan [Tempoh]. Ternyata dalam jadual perantaraan kami hanya meninggalkan baris yang nilai atribut [Syarikat] dan [Tempoh] bertepatan dengan baris semasa. Dalam jadual yang ditapis dengan cara ini, atribut [Jumlah] dipilih, nilai yang kemudiannya dijumlahkan.

Pada pandangan pertama, formula mungkin kelihatan terlalu mengelirukan, tetapi pada hakikatnya ia hanya menghasilkan semula pengiraan ungkapan 1, yang kami sebutkan pada awal artikel. Nilai dalam lajur "Hasil" akan menjadi pengesahan yang jelas, kerana ia bertepatan dengan lajur "Jumlah_Hasil" dalam Rajah. 2.

Sekarang mari tambah satu lagi lajur yang dikira pada jadual - "Tahap_Teknikal". Di dalamnya, bertentangan dengan setiap jumlah yang dikira pada peringkat pertama, kami akan menunjukkan nilai berangka skala dari jadual "Tahap". Malah, kita perlu mendapatkan nilai dari jadual lain dalam pangkalan "Fact_Table". Seperti halnya dengan pengiraan biasa dalam Excel, untuk melakukan ini, hanya pilih fungsi yang sesuai dan berikan pengecam objek yang dikehendaki sebagai hujah.

Mari masukkan formula berikut ke dalam lajur "Tahap_Teknikal" (ungkapan 5):

Ungkapan 5:
=KIRA(maks("Tahap"[Mula]);filter(semua("Tahap"[Mula]);"Tahap"[Mula]

Dalam ungkapan yang dibentangkan, fungsi pengagregatan MAX() digunakan pada jadual luaran "Tahap". Pertama, ia ditapis mengikut kekangan konteks semasa - hanya baris yang kurang daripada nombor dalam lajur Hasil bagi baris aktif yang ditinggalkan dalam jadual.

Akhir sekali, mari tambah lajur terakhir yang dikira pada jadual kami - "Dokh_Level". Di dalamnya kami akan memaparkan baris dengan nama tahap dari jadual "Tahap". Operasi sedemikian dilakukan menggunakan VLOOKUP berfungsi dalam Excel dan GLOOKUP (VLOOKUP dan HLOOKUP). Bahasa DAX mempunyai fungsi yang dipanggil LOOKUPVALUE, yang serupa dengan pengendali VLOOKUP.

Oleh itu, dalam lajur "Tahap_Pendapatan" adalah cukup untuk menulis ungkapan mudah bentuk berikut (ungkapan 6):

Ungkapan 6:
=LOOKUPVALUE("Tahap"[Tahap]; "Tahap"[Mula]; [Tahap_Teknikal])

Kami beralih ke peringkat akhir menjana laporan ringkasan. Memandangkan kami menggunakan jadual carian untuk tujuan rasmi sahaja, lajurnya tidak boleh ditunjukkan dalam laporan. Begitu juga, laporan ringkasan mungkin tidak menunjukkan semua lajur jadual fakta yang mengandungi pengiraan perantaraan.

Untuk mengehadkan set analitik dalam Excel, kami akan menggunakan arahan "Sembunyikan daripada set alat klien" (untuk melakukan ini, cuma klik kanan pada lajur yang kami perlukan dan pilih item yang sesuai dalam menu konteks). Lajur tersembunyi dalam Pembina Model dikelabukan. Untuk beralih kepada persekitaran Excel dan bekerja dengan laporan ringkasan yang terhasil, cuma pilih arahan "Tukar ke buku kerja Excel".

Pembaca boleh menyemak sendiri bahawa laporan ringkasan yang dibuat berdasarkan PowerPivot, dari segi fungsinya, sepadan sepenuhnya dengan keupayaan jadual pangsi biasa.

Bantuan untuk menggunakan alat tambah PowerPivot dan bahasa DAX, termasuk tutorial "Ketahui Asas DAX dalam 30 Minit" di tapak web rasmi MS Office, boleh didapati dalam talian.