Mga Benepisyo ng Pivot ayon sa Modelo ng Data

Kapag gumagawa ng pivot table sa Excel, sa pinakaunang dialog box, kung saan hihilingin sa amin na itakda ang paunang hanay at pumili ng lugar kung saan ilalagay ang pivot table, mayroong isang hindi mahalata ngunit napakahalagang checkbox sa ibaba - Idagdag ang data na ito sa Modelo ng Data (Idagdag ang data na ito sa Modelo ng Data) at, medyo mas mataas, ang switch Gamitin ang modelo ng data ng aklat na ito (Gumamit ng Modelo ng Data ng workbook na ito):

Sa kasamaang palad, maraming mga gumagamit na pamilyar sa mga talahanayan ng pivot sa loob ng mahabang panahon at matagumpay na ginagamit ang mga ito sa kanilang trabaho kung minsan ay hindi talaga naiintindihan ang kahulugan ng mga pagpipiliang ito at hindi kailanman ginagamit ang mga ito. At walang kabuluhan. Pagkatapos ng lahat, ang paggawa ng pivot table para sa Data Model ay nagbibigay sa amin ng ilang napakahalagang bentahe kumpara sa classic na Excel pivot table.

Gayunpaman, bago isaalang-alang ang mga “buns” na ito nang malapitan, unawain muna natin kung ano, sa katunayan, itong Data Model?

Ano ang Modelo ng Data

Modelo ng Data (pinaikling MD o DM = Modelo ng Data) ay isang espesyal na lugar sa loob ng isang Excel file kung saan maaari kang mag-imbak ng tabular na data - isa o higit pang mga talahanayan na naka-link, kung nais, sa isa't isa. Sa katunayan, ito ay isang maliit na database (OLAP cube) na naka-embed sa loob ng isang Excel workbook. Kung ikukumpara sa klasikong pag-iimbak ng data sa anyo ng mga regular (o matalino) na mga talahanayan sa mga sheet ng Excel mismo, ang Modelo ng Data ay may ilang makabuluhang pakinabang:

• Ang mga talahanayan ay maaaring hanggang sa 2 bilyong linya, at ang isang Excel sheet ay maaaring magkasya nang higit sa 1 milyon.
• Sa kabila ng napakalaking sukat, ang pagproseso ng naturang mga talahanayan (pag-filter, pag-uuri, pagkalkula sa mga ito, buod ng gusali, atbp.) napakabilis Higit na mas mabilis kaysa sa Excel mismo.
• Gamit ang data sa Modelo, maaari kang magsagawa ng karagdagang (kung ninanais, napakasalimuot) na mga kalkulasyon gamit built-in na wika ng DAX.
  
• Ang lahat ng impormasyon na na-load sa Modelo ng Data ay napaka malakas na naka-compress gamit ang isang espesyal na built-in na archiver at sa halip ay pinapataas ang laki ng orihinal na Excel file.
  

Ang Modelo ay pinamamahalaan at kinakalkula ng isang espesyal na add-in na nakapaloob sa Microsoft Excel - powerpivottungkol sa kung saan naisulat ko na. Upang paganahin ito, sa tab developer mag-click COM add-in (Developer — COM Add-in) at lagyan ng tsek ang naaangkop na kahon:

Kung tabs developer (Developer)hindi mo ito makikita sa ribbon, maaari mo itong i-on sa pamamagitan ng File – Opsyon – Ribbon Setup (File — Mga Pagpipilian — I-customize ang Ribbon). Kung sa window na ipinapakita sa itaas sa listahan ng mga COM add-in ay wala kang Power Pivot, hindi ito kasama sa iyong bersyon ng Microsoft Office 🙁

Sa tab na Power Pivot na lalabas, magkakaroon ng malaking light green na button pamamahala (Pamahalaan), pag-click kung saan magbubukas ang Power Pivot window sa itaas ng Excel, kung saan makikita natin ang mga nilalaman ng Modelo ng Data ng kasalukuyang aklat:

Isang mahalagang tala habang nasa daan: ang isang Excel workbook ay maaari lamang maglaman ng isang Modelo ng Data.

Mag-load ng mga talahanayan sa Modelo ng Data

Upang mag-load ng data sa Modelo, gagawin muna natin ang talahanayan sa isang dynamic na "matalinong" keyboard shortcut Ctrl+T at bigyan ito ng magiliw na pangalan sa tab Tagagawa (Disenyo). Ito ay isang kinakailangang hakbang.

Pagkatapos ay maaari mong gamitin ang alinman sa tatlong paraan upang pumili mula sa:

• Pindutin ang pindutan Idagdag sa Modelo (Idagdag sa Modelo ng Data) tab powerpivot tab Tahanan (Tahanan).
  
• Pagpili ng mga koponan Ipasok – PivotTable (Ipasok — Pivot Table) at i-on ang checkbox Idagdag ang data na ito sa Modelo ng Data (Idagdag ang data na ito sa Modelo ng Data). Sa kasong ito, ayon sa data na na-load sa Modelo, ang isang pivot table ay agad ding binuo.
  
• Sa Advanced tab data (Petsa) i-click ang pindutan Mula sa Table/Range (Mula sa Talahanayan/Hanay)upang i-load ang aming talahanayan sa editor ng Power Query. Ang landas na ito ang pinakamahaba, ngunit, kung ninanais, dito maaari kang magsagawa ng karagdagang paglilinis ng data, pag-edit at lahat ng uri ng pagbabago, kung saan napakalakas ng Power Query.

  Pagkatapos ang combed data ay na-upload sa Model sa pamamagitan ng command Tahanan — Isara at I-load — Isara at I-load sa… (Home — Isara&I-load — Isara&I-load sa…). Sa window na bubukas, piliin ang opsyon Gumawa lang ng koneksyon (Gumawa lamang ng koneksyon) at, higit sa lahat, lagyan ng tsek Idagdag ang data na ito sa Modelo ng Data (Idagdag ang data na ito sa Modelo ng Data).
  

Bumubuo kami ng buod ng Modelo ng Data

Upang bumuo ng isang buod na Modelo ng Data, maaari mong gamitin ang alinman sa tatlong mga diskarte:

• Pindutin ang pindutan talahanayan ng buod (Pivot Table) sa Power Pivot window.
• Pumili ng mga command sa Excel Ipasok – PivotTable at lumipat sa mode Gamitin ang modelo ng data ng aklat na ito (Insert — Pivot Table — Gamitin ang Modelo ng Data ng workbook na ito).
• Pagpili ng mga koponan Ipasok – PivotTable (Ipasok — Pivot Table) at i-on ang checkbox Idagdag ang data na ito sa Modelo ng Data (Idagdag ang data na ito sa Modelo ng Data). Ang kasalukuyang "smart" na talahanayan ay ilo-load sa Modelo at isang buod na talahanayan ay bubuo para sa buong Modelo.

Ngayong naisip na natin kung paano mag-load ng data sa Modelo ng Data at bumuo ng buod tungkol dito, tuklasin natin ang mga benepisyo at pakinabang na ibinibigay nito sa atin.

Benepisyo 1: Mga ugnayan sa pagitan ng mga talahanayan nang hindi gumagamit ng mga formula

Ang isang regular na buod ay maaari lamang gawin gamit ang data mula sa isang source table. Kung mayroon kang ilan sa mga ito, halimbawa, mga benta, listahan ng presyo, direktoryo ng customer, rehistro ng mga kontrata, atbp., pagkatapos ay kailangan mo munang mangolekta ng data mula sa lahat ng mga talahanayan sa isa gamit ang mga function tulad ng VLOOKUP (VLOOKUP), INDEX (INDEX), MAS EXPOSED (MATCH), SUMMESLIMN (SUMIFS) at mga katulad nito. Ito ay mahaba, nakakapagod at nagtutulak sa iyong Excel sa isang "kaisipan" na may malaking halaga ng data.

Sa kaso ng isang buod ng Modelo ng Data, ang lahat ay mas simple. Sapat na ang pag-set up ng mga ugnayan sa pagitan ng mga talahanayan nang isang beses sa window ng Power Pivot – at tapos na ito. Upang gawin ito, sa tab powerpivot pindutin ang pindutan pamamahala (Pamahalaan) at pagkatapos ay sa window na lilitaw - ang pindutan Pagtingin sa Tsart (Tingnan ang Diagram). Nananatili itong i-drag ang mga karaniwang (key) na pangalan ng column (mga patlang) sa pagitan ng mga talahanayan upang lumikha ng mga link:

Pagkatapos nito, sa buod para sa Modelo ng Data, maaari mong itapon sa lugar ng buod (mga hilera, column, filter, value) ang anumang mga field mula sa anumang nauugnay na mga talahanayan – lahat ay mali-link at awtomatikong kakalkulahin:

Benepisyo 2: Bilangin ang mga natatanging halaga

Ang isang regular na pivot table ay nagbibigay sa amin ng pagkakataong pumili ng isa sa ilang mga built-in na function ng pagkalkula: sum, average, count, minimum, maximum, atbp. Sa buod ng Modelo ng Data, isang napaka-kapaki-pakinabang na function ay idinagdag sa karaniwang listahang ito upang mabilang ang bilang ng natatangi (hindi umuulit na mga halaga). Sa tulong nito, halimbawa, madali mong mabibilang ang bilang ng mga natatanging item ng mga kalakal (range) na ibinebenta namin sa bawat lungsod.

Mag-right-click sa field - command Mga pagpipilian sa field ng halaga at sa tab Operasyon Piliin Bilang ng iba't ibang elemento (Nakakaibang bilang):

Benepisyo 3: Mga Custom na Formula ng DAX

Minsan kailangan mong magsagawa ng iba't ibang karagdagang mga kalkulasyon sa mga pivot table. Sa mga regular na buod, ginagawa ito gamit ang mga kalkuladong field at object, habang ang buod ng modelo ng data ay gumagamit ng mga sukat sa isang espesyal na wika ng DAX (DAX = Data Analysis Expressions).

Para gumawa ng sukat, piliin sa tab powerpivot Utos Mga Panukala – Lumikha ng Panukala (Mga Panukala — Bagong sukat) o i-right-click lang sa talahanayan sa listahan ng Pivot Fields at piliin Magdagdag ng sukat (Magdagdag ng sukat) sa menu ng konteksto:

Sa window na bubukas, itakda ang:

• Pangalan ng talahanayankung saan itatabi ang ginawang panukala.
• Sukatin ang pangalan – anumang pangalan na naiintindihan mo para sa bagong field.
• paglalarawan - opsyonal.
• Pormula – ang pinakamahalagang bagay, dahil dito kami ay manu-manong pumasok, o mag-click sa pindutan fx at pumili ng isang function ng DAX mula sa listahan, na dapat kalkulahin ang resulta kapag itinapon namin ang aming sukat sa lugar ng Mga Halaga.
• Sa ibabang bahagi ng window, maaari mong agad na itakda ang format ng numero para sa sukat sa listahan kategorya.
  

Ang wika ng DAX ay hindi laging madaling maunawaan dahil gumagana hindi sa mga indibidwal na halaga, ngunit sa buong mga column at talahanayan, ibig sabihin, nangangailangan ng ilang muling pagsasaayos ng pag-iisip pagkatapos ng mga klasikong formula ng Excel. Gayunpaman, ito ay katumbas ng halaga, dahil ang kapangyarihan ng mga kakayahan nito sa pagpoproseso ng malalaking halaga ng data ay mahirap i-overestimate.

Benepisyo 4: Mga custom na hierarchy ng field

Kadalasan, kapag gumagawa ng mga karaniwang ulat, kailangan mong itapon ang parehong mga kumbinasyon ng mga field sa mga pivot table sa isang naibigay na pagkakasunod-sunod, halimbawa. Year-Quarter-Month-Day, O Kategorya-Produkto, O Bansa-City-Client atbp. Sa buod ng Modelo ng Data, ang problemang ito ay madaling malutas sa pamamagitan ng paggawa ng iyong sarili mga hierarchy - mga pasadyang hanay ng field.

Sa Power Pivot window, lumipat sa chart mode gamit ang button Pagtingin sa Tsart tab Tahanan (Home — View ng Diagram), piliin gamit ang Ctrl ninanais na mga patlang at i-right-click sa mga ito. Ang menu ng konteksto ay maglalaman ng utos Lumikha ng Hierarchy (Gumawa ng hierarchy):

Ang nilikhang hierarchy ay maaaring palitan ng pangalan at i-drag dito gamit ang mouse ang mga kinakailangang field, upang sa ibang pagkakataon sa isang paggalaw ay maihagis sila sa buod:

Benepisyo 5: Mga custom na stencil

Sa pagpapatuloy ng ideya ng nakaraang talata, sa buod ng Modelo ng Data, maaari ka ring lumikha ng iyong sariling mga hanay ng mga elemento para sa bawat field. Halimbawa, mula sa buong listahan ng mga lungsod, madali kang makagawa ng isang hanay ng mga nasa iyong lugar ng responsibilidad. O kolektahin lamang ang iyong mga customer, ang iyong mga kalakal, atbp. sa isang espesyal na hanay.

Upang gawin ito, sa tab Pagsusuri ng talahanayan ng pivot sa drop down list Mga Field, Item, at Set may mga kaukulang utos (Pag-aralan - Fields, Items & Sets — Lumikha ng set batay sa row/column item):

Sa window na bubukas, maaari mong piliing tanggalin, idagdag o baguhin ang posisyon ng anumang elemento at i-save ang resultang set sa ilalim ng bagong pangalan:

Ang lahat ng nilikhang set ay ipapakita sa panel ng PivotTable Fields sa isang hiwalay na folder, mula sa kung saan maaari silang malayang i-drag sa mga row at column area ng anumang bagong PivotTable:

Benepisyo 6: Piliing Itago ang mga Table at Column

Kahit na ito ay isang maliit, ngunit napaka-kaaya-aya na kalamangan sa ilang mga kaso. Sa pamamagitan ng pag-right-click sa pangalan ng field o sa tab na talahanayan sa Power Pivot window, maaari mong piliin ang command Itago mula sa Client Toolkit (Itago mula sa Client Tools):

Mawawala ang nakatagong column o table sa pane ng Listahan ng Field ng PivotTable. Ito ay napaka-maginhawa kung kailangan mong itago mula sa gumagamit ang ilang mga pantulong na haligi (halimbawa, kalkulado o mga haligi na may mga pangunahing halaga para sa paglikha ng mga relasyon) o kahit na buong mga talahanayan.

Benepisyo 7. Advanced na drill-down

Kung nag-double click ka sa anumang cell sa lugar ng halaga sa isang regular na pivot table, ipapakita ng Excel sa isang hiwalay na sheet ang isang kopya ng fragment ng source data na kasangkot sa pagkalkula ng cell na ito. Ito ay isang napaka-madaling bagay, opisyal na tinatawag na Drill-down (sa karaniwang sinasabi nilang "fail").

Sa buod ng Modelo ng Data, ang madaling gamiting tool na ito ay gumagana nang mas banayad. Sa pamamagitan ng pagtayo sa anumang cell na may resultang interesado sa amin, maaari kang mag-click sa icon na may magnifying glass na lalabas sa tabi nito (tinatawag itong Express Trends) at pagkatapos ay pumili ng anumang field na interesado ka sa anumang nauugnay na talahanayan:

Pagkatapos nito, ang kasalukuyang halaga (Model = Explorer) ay mapupunta sa filter area, at ang buod ay bubuuin ng mga opisina:

Siyempre, ang ganitong pamamaraan ay maaaring ulitin nang maraming beses, patuloy na pag-aralan ang iyong data sa direksyon kung saan ka interesado.

Benepisyo 8: I-convert ang Pivot sa Cube Function

Kung pipili ka ng anumang cell sa buod para sa Modelo ng Data at pagkatapos ay pumili sa tab Pagsusuri ng talahanayan ng pivot Utos OLAP Tools – I-convert sa Mga Formula (Analyze — OLAP Tools — I-convert sa mga formula), pagkatapos ay awtomatikong mako-convert sa mga formula ang buong buod. Ngayon ang mga halaga ng field sa row-column area at ang mga resulta sa value area ay kukunin mula sa Data Model gamit ang mga espesyal na cube function: CUBEVALUE at CUBEMEMBER:

Sa teknikal, nangangahulugan ito na ngayon ay hindi tayo nakikitungo sa isang buod, ngunit sa ilang mga cell na may mga formula, ibig sabihin, madali nating magawa ang anumang pagbabago sa ating ulat na hindi available sa buod, halimbawa, magpasok ng mga bagong hilera o column sa gitna ng ulat, gumawa ng anumang karagdagang kalkulasyon sa loob ng buod, ayusin ang mga ito sa anumang gustong paraan, atbp.

Kasabay nito, ang koneksyon sa source data, siyempre, ay nananatili at sa hinaharap ang mga formula na ito ay ia-update kapag nagbago ang mga source. Ang kagandahan!

• Plan-fact analysis sa isang pivot table na may Power Pivot at Power Query
• Pivot table na may multiline na header
• Gumawa ng database sa Excel gamit ang Power Pivot