Mga Kaayohan sa Pivot pinaagi sa Data Model

Kung nagtukod usa ka pivot table sa Excel, sa una nga kahon sa diyalogo, diin gihangyo kami nga itakda ang inisyal nga hanay ug pagpili usa ka lugar aron isulud ang pivot table, adunay usa ka dili makita apan hinungdanon kaayo nga checkbox sa ubos - Idugang kini nga datos sa Data Model (Idugang kini nga datos sa Data Model) ug, mas taas og gamay, ang switch Gamita ang modelo sa datos niini nga libro (Gamita ang Modelo sa Data niini nga workbook):

Ikasubo, daghang mga tiggamit nga pamilyar sa mga pivot table sa dugay nga panahon ug malampuson nga gigamit kini sa ilang trabaho usahay dili gyud makasabut sa kahulugan sa kini nga mga kapilian ug dili gyud magamit kini. Ug sa kawang. Human sa tanan, ang paghimo sa usa ka pivot table alang sa Data Model naghatag kanato og daghang importante nga mga bentaha kon itandi sa classic nga Excel pivot table.

Bisan pa, sa wala pa ikonsiderar kini nga mga "buns" sa duol, atong sabton una kung unsa, sa tinuud, kini nga Modelo sa Data?

Unsa ang Modelo sa Data

Modelo sa Data (gipamubo nga MD o DM = Modelo sa Data) usa ka espesyal nga lugar sa sulod sa usa ka file sa Excel diin mahimo nimong tipigan ang tabular data - usa o daghang mga lamesa nga nalambigit, kung gusto, sa usag usa. Sa tinuud, kini usa ka gamay nga database (OLAP cube) nga na-embed sa sulod sa usa ka workbook sa Excel. Kung itandi sa klasiko nga pagtipig sa datos sa porma sa regular (o intelihente) nga mga lamesa sa mga sheet sa Excel mismo, ang Modelo sa Data adunay daghang hinungdanon nga mga bentaha:

• Ang mga lamesa mahimong hangtod sa 2 bilyon nga linya, ug ang usa ka sheet sa Excel mahimong mohaum sa sobra sa 1 ka milyon.
• Bisan pa sa dako nga gidak-on, ang pagproseso sa ingon nga mga lamesa (pagsala, paghan-ay, pagkalkula niini, katingbanan sa pagtukod, ug uban pa) gihimo. paspas kaayo Mas paspas kay sa Excel mismo.
• Uban sa datos sa Modelo, mahimo nimong buhaton ang dugang (kon gusto, komplikado kaayo) nga mga kalkulasyon gamit built-in nga DAX nga pinulongan.
  
• Ang tanan nga impormasyon nga gikarga sa Data Model mao ang kaayo kusog nga gi-compress gamit ang usa ka espesyal nga built-in nga archiver ug medyo kasarangan nga pagtaas sa gidak-on sa orihinal nga file sa Excel.
  

Ang Modelo gidumala ug gikalkula sa usa ka espesyal nga add-in nga gitukod sa Microsoft Excel - powerpivotmahitungod sa akong gisulat na. Aron mahimo kini, sa tab developer click COM nga mga add-in (Developer — COM Add-in) ug susiha ang angay nga kahon:

Kung tabs developer (Developer)dili nimo kini makita sa ribbon, mahimo nimo kini i-on pinaagi sa File - Mga Opsyon - Pag-setup sa Ribbon (File — Mga Opsyon — Ipasibo ang Ribbon). Kung sa bintana nga gipakita sa taas sa lista sa mga COM add-in wala kay Power Pivot, nan wala kini gilakip sa imong bersyon sa Microsoft Office 🙁

Sa Power Pivot tab nga makita, adunay usa ka dako nga light green nga buton Management (Pagdumala), pag-klik kung asa mag-abli sa Power Pivot window sa ibabaw sa Excel, diin atong makita ang sulod sa Data Model sa kasamtangan nga libro:

Usa ka importante nga nota sa dalan: ang usa ka workbook sa Excel mahimo ra nga adunay usa ka Modelo sa Data.

I-load ang mga lamesa sa Data Model

Aron ma-load ang data sa Model, una natong himoon ang lamesa nga usa ka dinamikong "smart" nga shortcut sa keyboard Ctrl+T ug hatagi kini og mahigalaon nga ngalan sa tab Magtutukod (Laraw). Kini usa ka gikinahanglan nga lakang.

Dayon mahimo nimong gamiton ang bisan hain sa tulo ka mga paagi sa pagpili gikan sa:

• Pagpadayon sa buton Idugang sa Modelo (Idugang sa Data Model) tab powerpivot tab Home (Balay).
  
• Pagpili sa mga team Isulod – PivotTable (Sal-ot — Pivot Table) ug i-on ang checkbox Idugang kini nga datos sa Data Model (Idugang kini nga datos sa Data Model). Sa kini nga kaso, sumala sa datos nga gikarga sa Modelo, ang usa ka pivot table gihimo usab dayon.
  
• Sa Advanced tab Data (Petsa) i-klik ang buton Gikan sa Table/Range (Gikan sa Talaan/Sakop)aron makarga ang among lamesa sa editor sa Power Query. Kini nga dalan mao ang pinakataas, apan, kung gusto, dinhi mahimo nimo ang dugang nga paglimpyo sa datos, pag-edit ug tanan nga matang sa mga pagbag-o, diin ang Power Query kusog kaayo.

  Unya ang combed data gi-upload sa Model pinaagi sa sugo Panimalay — Pagsira ug Pag-load — Pagsira ug Pag-load sa… (Balay — Close&Load — Close&Load to…). Sa bintana nga nagbukas, pilia ang kapilian Paghimo lang og koneksyon (Paghimo lang og koneksyon) ug, labaw sa tanan, pagbutang ug tsek Idugang kini nga datos sa Data Model (Idugang kini nga datos sa Data Model).
  

Naghimo kami og usa ka summary sa Data Model

Aron makahimo og summary Data Model, mahimo nimong gamiton ang bisan asa sa tulo ka mga pamaagi:

• Butang pindota summary table (Pivot Table) sa Power Pivot nga bintana.
• Pagpili mga sugo sa Excel Isulod – PivotTable ug switch sa mode Gamita ang modelo sa datos niini nga libro (Sal-ot — Pivot Table — Gamita ang Modelo sa Data sa workbook).
• Pagpili sa mga team Isulod – PivotTable (Sal-ot — Pivot Table) ug i-on ang checkbox Idugang kini nga datos sa Data Model (Idugang kini nga datos sa Data Model). Ang kasamtangan nga "smart" nga lamesa i-load sa Modelo ug usa ka summary table ang pagatukoron alang sa tibuok Model.

Karon nga nahibal-an na namon kung giunsa ang pagkarga sa datos sa Modelo sa Data ug paghimo usa ka katingbanan niini, susihon naton ang mga benepisyo ug bentaha nga gihatag niini kanato.

Benepisyo 1: Mga relasyon tali sa mga lamesa nga wala mogamit mga pormula

Ang usa ka regular nga summary mahimo lamang nga matukod gamit ang data gikan sa usa ka source table. Kung daghan ka niini, pananglitan, pagbaligya, lista sa presyo, direktoryo sa kostumer, rehistro sa mga kontrata, ug uban pa, kinahanglan nimo una nga mangolekta og datos gikan sa tanan nga mga lamesa sa usa gamit ang mga function sama sa VLOOKUP (VLOOKUP), INDEX (INDEX), MAS EXPOSED (PAGTABANG), SUMMESLIMN (SUMIFS) ug ang sama. Kini dugay, makakapoy ug nagdala sa imong Excel sa usa ka "hunahuna" nga adunay daghang data.

Sa kaso sa usa ka summary sa Data Model, ang tanan mas simple. Igo na ang pagpahimutang sa mga relasyon tali sa mga lamesa kausa sa bintana sa Power Pivot – ug nahuman na. Aron mahimo kini, sa tab powerpivot ipadayon ang buton Management (Pagdumala) ug dayon sa bintana nga makita - ang buton Pagtan-aw sa Tsart (Pagtan-aw sa diagram). Nagpabilin kini sa pag-drag sa komon (key) nga mga ngalan sa kolum (mga field) tali sa mga lamesa aron makahimo og mga link:

Human niana, sa summary alang sa Data Model, mahimo nimong ilabay ang summary area (mga laray, mga kolum, mga filter, mga bili) sa bisan unsang mga field gikan sa bisan unsang may kalabutan nga mga lamesa - ang tanan masumpay ug awtomatikong makalkula:

Kaayohan 2: Pag-ihap sa talagsaong mga mithi

Ang usa ka regular nga pivot table naghatag kanato og oportunidad sa pagpili sa usa sa pipila ka mga built-in nga kalkulasyon nga mga function: sum, average, count, minimum, maximum, ug uban pa. gidaghanon sa talagsaon (dili nagbalikbalik nga mga bili). Uban sa tabang niini, pananglitan, dali nimo maihap ang gidaghanon sa mga talagsaon nga mga butang sa mga butang (range) nga among gibaligya sa matag lungsod.

Pag-right-click sa field - command Mga kapilian sa natad sa kantidad ug sa tab Operasyon Pagpili Gidaghanon sa lainlaing mga elemento (Lain nga ihap):

Kaayohan 3: Pasadya nga mga Pormula sa DAX

Usahay kinahanglan nimo nga buhaton ang lainlaing mga dugang nga kalkulasyon sa mga pivot table. Sa regular nga mga summary, kini gihimo gamit ang kalkulado nga mga natad ug mga butang, samtang ang data model summary naggamit sa mga lakang sa usa ka espesyal nga DAX nga pinulongan (DAX = Data Analysis Expressions).

Aron makahimo usa ka sukod, pagpili sa tab powerpivot Command Mga Lakang – Paghimog Sukdanan (Mga sukod - Bag-ong sukod) o pag-right-click lang sa lamesa sa listahan sa Pivot Fields ug pilia Idugang ang sukod (Idugang ang sukod) sa menu sa konteksto:

Sa bintana nga nagbukas, itakda ang:

• Ngalan sa lamesadiin ang gibuhat nga sukod itago.
• Sukda ang ngalan – bisan unsang ngalan nga imong nasabtan para sa bag-ong field.
• Description - kapilian.
• pormula – ang labing importante nga butang, tungod kay dinhi kita manwal mosulod, o i-klik ang button fx ug pilia ang usa ka function sa DAX gikan sa lista, nga kinahanglan nga kuwentahon ang resulta kung atong ihulog ang atong sukod sa lugar sa Mga kantidad.
• Sa ubos nga bahin sa bintana, mahimo nimong itakda dayon ang format sa numero alang sa sukod sa lista Kategoriya.
  

Ang DAX nga lengguwahe dili kanunay sayon ​​sabton tungod kay naglihok dili sa tagsa-tagsa nga mga bili, apan uban sa tibuok nga mga kolum ug mga lamesa, ie nagkinahanglan og pipila ka pagbag-o sa panghunahuna human sa klasiko nga mga pormula sa Excel. Bisan pa, takus kini, tungod kay ang gahum sa mga kapabilidad niini sa pagproseso sa daghang mga datos lisud nga sobra ang pagtantiya.

Kaayohan 4: Pasadya nga mga hierarchy sa uma

Kasagaran, kung maghimo mga sumbanan nga mga taho, kinahanglan nimo nga ihulog ang parehas nga mga kombinasyon sa mga natad sa mga pivot table sa usa ka gihatag nga pagkasunod-sunod, pananglitan. Tuig-Quarter-Month-Day, o Kategorya-Produkto, o Nasud-Syudad-Kliyente ug uban pa. Sa summary sa Modelo sa Data, kini nga problema daling masulbad pinaagi sa paghimo sa imong kaugalingon mga hierarchy - naandan nga mga set sa uma.

Sa Power Pivot window, balhin sa chart mode gamit ang buton Pagtan-aw sa Tsart tab Home (Balay — Pagtan-aw sa Diagram), pilia uban sa Ctrl gusto nga mga natad ug pag-right-click sa kanila. Ang menu sa konteksto maglangkob sa mando Paghimo Hierarchy (Paghimo ug hierarchy):

Ang gibuhat nga hierarchy mahimong mabag-o sa ngalan ug ma-drag ngadto niini gamit ang mouse ang gikinahanglan nga mga natad, aron sa ulahi sa usa ka paglihok mahimo silang ihulog sa summary:

Kaayohan 5: Pasadya nga mga stencil

Ang pagpadayon sa ideya sa miaging parapo, sa katingbanan sa Modelo sa Data, mahimo ka usab maghimo sa imong kaugalingon nga mga set sa mga elemento alang sa matag uma. Pananglitan, gikan sa tibuuk nga lista sa mga lungsod, dali ka makahimo usa ka hugpong sa mga naa sa imong lugar nga responsibilidad. O pagkolekta lamang sa imong mga kustomer, imong mga butang, ug uban pa ngadto sa usa ka espesyal nga set.

Aron mahimo kini, sa tab Pagtuki sa pivot table sa drop down list Fields, Items, ug Sets adunay katugbang nga mga sugo (Pag-analisar - Fields, Items & Sets — Paghimo set base sa row/column items):

Sa bintana nga nagbukas, mahimo nimong pilion nga tangtangon, idugang o usbon ang posisyon sa bisan unsang mga elemento ug i-save ang sangputanan nga set sa ilawom sa usa ka bag-ong ngalan:

Ang tanan nga gibuhat nga mga set ipakita sa panel sa PivotTable Fields sa usa ka bulag nga folder, gikan diin mahimo silang gawasnon nga maguyod sa mga linya ug kolum nga lugar sa bisan unsang bag-ong PivotTable:

Kaayohan 6: Pilia nga Itago ang mga Talaan ug Mga Kolum

Bisan tuod kini mao ang usa ka gamay, apan kaayo makapahimuot nga bentaha sa pipila ka mga kaso. Pinaagi sa pag-right-click sa field name o sa table tab sa Power Pivot window, mahimo nimong pilion ang command Tagoa gikan sa Client Toolkit (Tagoa gikan sa Client Tools):

Ang tinago nga kolum o lamesa mawala gikan sa PivotTable Field List pane. Kini sayon ​​​​kaayo kung kinahanglan nimo nga itago gikan sa tiggamit ang pipila ka mga auxiliary nga mga kolum (pananglitan, kalkulado o mga kolum nga adunay hinungdanon nga mga kantidad alang sa paghimo og mga relasyon) o bisan ang tibuuk nga mga lamesa.

Benepisyo 7. Abanteng drill-down

Kung doble-klik nimo ang bisan unsang cell sa lugar nga kantidad sa usa ka regular nga pivot table, dayon ipakita sa Excel sa usa ka bulag nga sheet ang usa ka kopya sa tipik sa gigikanan nga datos nga nahilambigit sa pagkalkula niini nga cell. Kini usa ka praktikal nga butang, nga opisyal nga gitawag nga Drill-down (sa kasagaran nila giingon nga "mapakyas").

Sa summary sa Modelo sa Data, kini nga magamit nga himan labi ka dali nga molihok. Pinaagi sa pagbarug sa bisan unsang cell nga adunay resulta nga nakapainteres kanamo, mahimo nimong i-klik ang icon nga adunay usa ka magnifying glass nga mogawas tupad niini (gitawag kini nga Express Trends) ug dayon pilia ang bisan unsang natad nga interesado ka sa bisan unsang may kalabutan nga lamesa:

Human niana, ang kasamtangan nga bili (Model = Explorer) moadto sa filter area, ug ang summary pagatukuron sa mga opisina:

Siyempre, ang ingon nga pamaagi mahimong masubli sa daghang mga higayon, kanunay nga nagsusi sa imong datos sa direksyon nga imong gusto.

Kaayohan 8: I-convert ang Pivot ngadto sa Cube Functions

Kung mopili ka ug bisan unsang cell sa summary para sa Data Model ug dayon pilia sa tab Pagtuki sa pivot table Command Mga Himan sa OLAP – Pag-convert sa mga Pormula (Pag-analisar - Mga Tool sa OLAP - Pag-convert sa mga pormula), unya ang tibuok summary awtomatikong makabig ngadto sa mga pormula. Karon ang mga kantidad sa field sa row-column area ug ang mga resulta sa value area makuha gikan sa Data Model gamit ang espesyal nga cube functions: CUBEVALUE ug CUBEMEMBER:

Sa teknikal nga paagi, kini nagpasabut nga karon wala kami nag-atubang sa usa ka katingbanan, apan sa daghang mga selyula nga adunay mga pormula, ie dali namon mahimo ang bisan unsang mga pagbag-o sa among taho nga wala magamit sa katingbanan, pananglitan, pagsal-ot sa bag-ong mga laray o kolum sa tunga. sa report, paghimo ug bisan unsang dugang nga mga kalkulasyon sulod sa summary, paghan-ay niini sa bisan unsang gusto nga paagi, ug uban pa.

Sa parehas nga oras, ang koneksyon sa gigikanan nga datos, siyempre, nagpabilin ug sa umaabot kini nga mga pormula ma-update kung mabag-o ang mga gigikanan. Ang kagwapa!

• Plan-fact analysis sa pivot table nga adunay Power Pivot ug Power Query
• Pivot table nga adunay multiline header
• Paghimo og database sa Excel gamit ang Power Pivot