Keuntungan saka Pivot miturut Model Data

Nalika mbangun tabel pivot ing Excel, ing kothak dialog sing paling pisanan, ing ngendi kita dijaluk nyetel kisaran awal lan milih papan kanggo nglebokake tabel pivot, ana kothak centhang sing ora katon nanging penting banget ing ngisor iki - Tambah data iki menyang Model Data (Tambahake data iki kanggo model data) lan, sethitik luwih dhuwur, ngalih Gunakake model data buku iki (Gunakake Model Data saka buku kerja iki):

Sayange, akeh pangguna sing wis suwe ngerti tabel pivot lan sukses digunakake ing pakaryane, kadhangkala ora ngerti makna opsi kasebut lan ora nate nggunakake. Lan muspra. Sawise kabeh, nggawe tabel pivot kanggo Model Data menehi sawetara kaluwihan sing penting banget dibandhingake karo tabel pivot Excel klasik.

Nanging, sadurunge nimbang "roti" iki kanthi cedhak, ayo padha ngerti apa sejatine Model Data iki?

Apa Model Data

Model Data (disingkat MD utawa DM = Model Data) minangka area khusus ing file Excel ing ngendi sampeyan bisa nyimpen data tabular - siji utawa luwih tabel sing disambung, yen dikarepake, siji liyane. Nyatane, iki minangka database cilik (OLAP cube) sing dipasang ing buku kerja Excel. Dibandhingake karo panyimpenan klasik data ing wangun tabel biasa (utawa pinter) ing lembar Excel dhewe, Model Data duwe sawetara kaluwihan sing signifikan:

• Tabel bisa nganti 2 milyar baris, lan lembar Excel bisa pas luwih saka 1 yuta.
• Senadyan ukuran gedhe, pangolahan tabel kasebut (nyaring, ngurutake, kalkulasi, ringkesan bangunan, lan liya-liyane) ditindakake. banter tenan Luwih cepet tinimbang Excel dhewe.
• Kanthi data ing Model, sampeyan bisa nindakake tambahan (yen dikarepake, banget Komplek) pitungan nggunakake basa DAX sing dibangun.
  
• Kabeh informasi sing dimuat menyang Model Data banget kuwat teken nggunakake arsip dibangun khusus lan rada nambah ukuran file Excel asli.
  

Model kasebut dikelola lan diitung kanthi tambahan khusus sing dibangun ing Microsoft Excel - powerpivotbab kang wis tak tulis. Kanggo ngaktifake, ing tab pangembang klik COM tambahan (Pangembang - COM Add-in) lan priksa kothak sing cocog:

Yen tab pangembang (Pengembang)sampeyan ora bisa ndeleng ing pita, sampeyan bisa nguripake liwat File - Pilihan - Setup Pita (File - Pilihan - Kustomisasi Pita). Yen ing jendhela sing ditampilake ing ndhuwur ing dhaptar add-in COM sampeyan ora duwe Power Pivot, mula ora kalebu ing versi Microsoft Office sampeyan 🙁

Ing tab Power Pivot sing katon, bakal ana tombol ijo cahya sing gedhe Manajemen (Ngatur), ngeklik sing bakal mbukak jendhela Power Pivot ing ndhuwur Excel, ing ngendi kita bakal weruh isi Model Data buku saiki:

Cathetan penting ing dalan: buku kerja Excel mung bisa ngemot siji Model Data.

Muat tabel menyang Model Data

Kanggo mbukak data menyang Model, pisanan kita nguripake meja dadi trabasan keyboard "pinter" dinamis Ctrl+T lan menehi jeneng loropaken ing tab Constructor (Desain). Iki minangka langkah sing dibutuhake.

Banjur sampeyan bisa nggunakake salah siji saka telung cara kanggo milih:

• Pencet tombol Tambah menyang Model (Tambah menyang Model Data) tab powerpivot tab Home (Ngarep).
  
• Milih tim Sisipake - PivotTable (Sisipake - Tabel Pivot) lan nguripake kothak centhang Tambah data iki menyang Model Data (Tambahake data iki menyang Model Data). Ing kasus iki, miturut data sing dimuat ing Model, tabel poros uga langsung dibangun.
  
• Ing tab Lanjut Data (Tanggal) klik ing tombol Saka Tabel / Range (Saka Tabel/Rentang)kanggo mbukak tabel kita menyang editor Power Query. Path iki paling dawa, nanging, yen dikarepake, sampeyan bisa nindakake reresik data tambahan, editing lan kabeh jinis transformasi, kang Power Query kuwat banget.

  Banjur data combed diunggah menyang Model dening printah Ngarep — Nutup lan Muat — Nutup lan Muat ing… (Ngarep — Tutup&Muat — Tutup&Muat menyang…). Ing jendhela sing mbukak, pilih opsi kasebut Mung nggawe sambungan (Mung nggawe sambungan) lan, paling Jahwéh, sijine obah Tambah data iki menyang Model Data (Tambahake data iki menyang Model Data).
  

Kita mbangun ringkesan saka Model Data

Kanggo mbangun Model Data ringkesan, sampeyan bisa nggunakake salah siji saka telung pendekatan:

• Tombol penet tabel ringkesan (Tabel Pivot) ing jendhela Power Pivot.
• Pilih printah ing Excel Sisipake - PivotTable lan ngalih menyang mode Gunakake model data buku iki (Sisipake — Tabel Pivot — Gunakake Model Data buku kerja iki).
• Milih tim Sisipake - PivotTable (Sisipake - Tabel Pivot) lan nguripake kothak centhang Tambah data iki menyang Model Data (Tambahake data iki menyang Model Data). Tabel "pinter" saiki bakal dimuat menyang Model lan tabel ringkesan bakal dibangun kanggo kabeh Model.

Saiki kita wis ngerti carane mbukak data menyang Model Data lan nggawe ringkesan, ayo goleki keuntungan lan keuntungan sing diwenehake.

Keuntungan 1: Hubungan antarane tabel tanpa nggunakake rumus

Ringkesan biasa mung bisa digawe nggunakake data saka siji tabel sumber. Yen sampeyan duwe sawetara, contone, dodolan, dhaptar rega, direktori pelanggan, daftar kontrak, lan liya-liyane, mula sampeyan kudu ngumpulake data saka kabeh tabel dadi siji nggunakake fungsi kayata VLOOKUP. (VLOOKUP), INDEKS (INDEX), LUWIH DIBUKA (PERTANDINGAN), SUMMESLIMN (SUMIFS) lan liya-liyane. Iki dawa, mboseni lan ndadekake Excel dadi "pikiran" kanthi jumlah data sing akeh.

Ing kasus ringkesan Model Data, kabeh luwih gampang. Cukup kanggo nyetel hubungan antarane tabel sapisan ing jendhela Power Pivot - lan wis rampung. Kanggo nindakake iki, ing tab powerpivot penet tombol Manajemen (Ngatur) banjur ing jendhela sing katon - tombol Tampilan Bagan (Deleng Diagram). Iku tetep kanggo nyeret umum (kunci) jeneng kolom (kolom) antarane tabel kanggo nggawe pranala:

Sawise iku, ing ringkesan kanggo Model Data, sampeyan bisa mbuwang ing area ringkesan (baris, kolom, saringan, nilai) sembarang kolom saka tabel sing gegandhengan - kabeh bakal disambung lan diwilang kanthi otomatis:

Keuntungan 2: Count nilai unik

Tabel poros biasa menehi kesempatan kanggo milih salah siji saka sawetara fungsi pitungan sing dibangun: jumlah, rata-rata, count, minimal, maksimum, lan sapiturute. Ing ringkesan Model Data, fungsi sing migunani banget ditambahake ing dhaptar standar iki kanggo ngitung nomer unik (non-baleni nilai). Kanthi bantuan, contone, sampeyan bisa kanthi gampang ngetung jumlah barang unik (jarak) sing didol ing saben kutha.

Klik-tengen ing lapangan - printah Pilihan lapangan Nilai lan ing tab operasi Pilih Jumlah unsur sing beda (Cacahe beda):

Keuntungan 3: Formula DAX Custom

Kadhangkala sampeyan kudu nindakake macem-macem petungan tambahan ing tabel pivot. Ing ringkesan biasa, iki ditindakake kanthi nggunakake kolom lan obyek sing diwilang, dene ringkesan model data nggunakake ukuran ing basa DAX khusus (DAX = Ekspresi Analisis Data).

Kanggo nggawe ukuran, pilih ing tab powerpivot Command Ukuran - Nggawe Ukuran (Ukuran - Ukuran anyar) utawa mung klik-tengen ing meja ing dhaftar Pivot Fields banjur pilih Tambah ukuran (Tambah ukuran) ing menu konteks:

Ing jendhela sing mbukak, atur:

• Jeneng mejangendi ukuran digawe bakal disimpen.
• Ngukur jeneng – jeneng apa wae sing sampeyan ngerti kanggo lapangan anyar.
• Description – opsional.
• Formula - sing paling penting, amarga ing kene kita ngetik kanthi manual, utawa klik tombol kasebut fx lan pilih fungsi DAX saka dhaptar, sing kudu ngetung asil nalika kita mbuwang ukuran menyang area Nilai.
• Ing sisih ngisor jendhela, sampeyan bisa langsung nyetel format nomer kanggo ukuran ing dhaftar Kategori.
  

Basa DAX ora tansah gampang dingerteni amarga ora nganggo nilai individu, nanging kanthi kabeh kolom lan tabel, yaiku mbutuhake sawetara restrukturisasi pamikiran sawise rumus Excel klasik. Nanging, iku worth iku, amarga daya saka kabisan ing Processing jumlah gedhe saka data iku angel overestimate.

Keuntungan 4: Hierarki lapangan khusus

Asring, nalika nggawe laporan standar, sampeyan kudu mbuwang kombinasi kolom sing padha menyang tabel pivot ing urutan tartamtu, contone. Taun-Seprapat-Wulan-Dina, utawa Kategori-Produk, utawa Negara-Kutha-klien etc Ing ringkesan Model Data, masalah iki gampang ditanggulangi dening nggawe dhewe hirarki - set lapangan khusus.

Ing jendhela Power Pivot, pindhah menyang mode grafik kanthi tombol Tampilan Bagan tab Home (Ngarep - Tampilan Diagram), pilih karo Ctrl kolom sing dipengini lan klik-tengen ing. Menu konteks bakal ngemot perintah kasebut Nggawe Hierarki (Nggawe hirarki):

Hierarki sing digawe bisa diganti jeneng lan nyeret menyang kolom sing dibutuhake nganggo mouse, supaya mengko ing siji gerakan bisa dibuwang menyang ringkesan:

Keuntungan 5: Stensil khusus

Nerusake ide paragraf sadurunge, ing ringkesan Model Data, sampeyan uga bisa nggawe set unsur dhewe kanggo saben lapangan. Contone, saka kabeh dhaptar kutha, sampeyan bisa kanthi gampang nggawe set mung sing ana ing wilayah tanggung jawab sampeyan. Utawa ngumpulake mung pelanggan, barang sampeyan, lan liya-liyane dadi set khusus.

Kanggo nindakake iki, ing tab Analisis tabel pivot ing dhaptar gulung mudhun Bidang, Item, lan Set ana prentah sing cocog (Analisis - Fields, Items & Sets - Nggawe set adhedhasar item baris / kolom):

Ing jendhela sing mbukak, sampeyan bisa kanthi selektif mbusak, nambah utawa ngganti posisi unsur lan nyimpen set sing diasilake ing jeneng anyar:

Kabeh set digawe bakal ditampilake ing panel PivotTable Fields ing folder kapisah, saka ngendi padha bisa bebas nyeret menyang baris lan kolom area sembarang PivotTable anyar:

Keuntungan 6: Selektif Singidaken Tabel lan Kolom

Senajan iki cilik, nanging kauntungan banget penake ing sawetara kasus. Kanthi ngeklik tengen ing jeneng lapangan utawa ing tab tabel ing jendhela Power Pivot, sampeyan bisa milih printah kasebut Singidaken saka Toolkit Klien (Singidaken saka Alat Klien):

Kolom utawa tabel sing didhelikake bakal ilang saka panel Dhaptar Lapangan PivotTable. Trep banget yen sampeyan kudu ndhelikake saka pangguna sawetara kolom tambahan (contone, diwilang utawa kolom kanthi nilai kunci kanggo nggawe hubungan) utawa malah kabeh tabel.

Wuku 7. Majeng drill-mudhun

Yen sampeyan ngeklik kaping pindho ing sembarang sel ing area nilai ing tabel poros biasa, Excel bakal nampilake ing sheet kapisah salinan fragmen data sumber sing melu pitungan sel iki. Iki bab banget Handy, resmi disebut Drill-mudhun (ing padha biasane ngomong "gagal").

Ing ringkesan Model Data, alat praktis iki bisa digunakake kanthi luwih halus. Kanthi ngadeg ing sel apa wae kanthi asil sing menarik, sampeyan bisa ngeklik lambang kanthi kaca pembesar sing muncul ing jejere (disebut Tren Ekspres) banjur pilih kolom sing sampeyan minati ing tabel sing gegandhengan:

Sawisé iku, nilai saiki (Model = Explorer) bakal pindhah menyang area panyaring, lan ringkesan bakal dibangun dening kantor:

Mesthine, prosedur kasebut bisa diulang kaping pirang-pirang, terus-terusan nyelidiki data sampeyan menyang arah sing sampeyan minati.

Keuntungan 8: Ngonversi Pivot menyang Fungsi Kubus

Yen sampeyan milih sel ing ringkesan kanggo Model Data banjur pilih ing tab Analisis tabel pivot Command Alat OLAP - Ngonversi menyang Rumus (Analisis - Alat OLAP - Ngonversi menyang rumus), banjur kabeh ringkesan bakal otomatis diowahi dadi rumus. Saiki nilai lapangan ing area baris-kolom lan asil ing area nilai bakal dijupuk saka Model Data nggunakake fungsi kubus khusus: CUBEVALUE lan CUBEMEMBER:

Secara teknis, iki tegese saiki kita ora ngurusi ringkesan, nanging kanthi sawetara sel kanthi rumus, yaiku kita bisa kanthi gampang nindakake transformasi apa wae kanthi laporan sing ora kasedhiya ing ringkesan, contone, masang baris utawa kolom anyar ing tengah. laporan, apa wae petungan tambahan nang ringkesan, ngatur ing sembarang cara sing dikarepake, etc.

Ing wektu sing padha, sambungan karo data sumber, mesthi, tetep lan ing mangsa ngarep rumus iki bakal dianyari nalika sumber diganti. Kaendahan!

• Analisis rencana-fakta ing tabel pivot kanthi Power Pivot lan Power Query
• Tabel pivot kanthi header multiline
• Nggawe database ing Excel nggunakake Power Pivot