Benefiċċji tal-Pivot mill-Mudell tad-Data

Meta nibnu tabella ta 'pern f'Excel, fl-ewwel kaxxa ta' djalogu, fejn aħna mitluba nissettjaw il-firxa inizjali u nagħżlu post fejn daħħal it-tabella tal-pern, hemm kaxxa ta 'kontroll mhux viżibbli iżda importanti ħafna hawn taħt - Żid din id-dejta mal-Mudell tad-Data (Żid din id-dejta għall-Mudell tad-Data) u, ftit ogħla, is-swiċċ Uża l-mudell tad-dejta ta’ dan il-ktieb (Uża Mudell tad-Data ta' dan il-ktieb tax-xogħol):

Sfortunatament, ħafna utenti li ilhom familjari mat-tabelli tal-pern għal żmien twil u jużawhom b'suċċess fix-xogħol tagħhom kultant ma jifhmux verament it-tifsira ta 'dawn l-għażliet u qatt ma jużawhom. U għalxejn. Wara kollox, il-ħolqien ta 'pern table għall-Mudell tad-Dejta tagħtina diversi vantaġġi importanti ħafna meta mqabbla mat-tabella pivot klassika ta' Excel.

Madankollu, qabel ma nikkunsidraw dawn il-“buns” mill-qrib, ejja l-ewwel nifhmu x’inhu, fil-fatt, dan il-Mudell tad-Data?

X'inhu Mudell tad-Data

Mudell tad-Data (imqassar bħala MD jew DM = Mudell tad-Data) hija żona speċjali ġewwa fajl Excel fejn tista 'taħżen data tabulari - tabella waħda jew aktar marbuta, jekk mixtieq, ma' xulxin. Fil-fatt, din hija database żgħira (kubu OLAP) inkorporat ġewwa ktieb tax-xogħol Excel. Meta mqabbel mal-ħażna klassika tad-dejta fil-forma ta 'tabelli regolari (jew intelliġenti) fuq folji ta' Excel innifsu, il-Mudell tad-Data għandu diversi vantaġġi sinifikanti:

• It-tabelli jistgħu jkunu sa 2 biljun linja, u folja Excel tista 'taqbel ftit aktar minn miljun.
• Minkejja d-daqs ġiganteski, l-ipproċessar ta 'tabelli bħal dawn (filtrazzjoni, għażla, kalkoli fuqhom, sommarju tal-bini, eċċ.) jitwettaq mgħaġġel ħafna Ħafna aktar mgħaġġla minn Excel innifsu.
• Bid-dejta fil-Mudell, tista 'twettaq kalkoli addizzjonali (jekk mixtieq, kumplessi ħafna) bl-użu lingwa DAX inkorporata.
  
• L-informazzjoni kollha mgħobbija fil-Mudell tad-Dejta hija ħafna ikkompressat ħafna bl-użu ta 'arkivju speċjali mibni u pjuttost moderatament iżid id-daqs tal-fajl Excel oriġinali.
  

Il-Mudell huwa ġestit u kkalkulat minn add-in speċjali mibni f'Microsoft Excel - powerpivotli dwarhom diġà ktibt. Biex tippermettiha, fuq it-tab iżviluppatur ikklikkja COM add-ins (Żviluppatur — COM Add-ins) u iċċekkja l-kaxxa xierqa:

Jekk tabs iżviluppatur (Żviluppatur)ma tistax taraha fuq iż-żigarella, tista 'tixgħelha permezz Fajl – Għażliet – Setup taż-żigarella (Fajl — Għażliet — Ippersonalizza żigarella). Jekk fit-tieqa murija hawn fuq fil-lista ta 'COM add-ins m'għandekx Power Pivot, allura ma tkunx inkluża fil-verżjoni tiegħek ta' Microsoft Office 🙁

Fuq it-tab Power Pivot li tidher, se jkun hemm buttuna kbira aħdar ċar Ġestjoni (Immaniġġja), tikklikkja fuqha se tiftaħ it-tieqa tal-Power Pivot fuq Excel, fejn se naraw il-kontenut tal-Mudell tad-Dejta tal-ktieb kurrenti:

Nota importanti tul it-triq: ktieb tax-xogħol ta 'Excel jista' jkun fih biss Mudell tad-Dejta wieħed.

Tabelli Tagħbija fil-Mudell tad-Dejta

Biex tgħabbi d-dejta fil-Mudell, l-ewwel inbiddlu t-tabella f'shortcut dinamiku "intelliġenti" tat-tastiera Ctrl+T u agħtih isem faċli fuq it-tab kostruttur (Disinn). Dan huwa pass meħtieġ.

Imbagħad tista' tuża kwalunkwe mit-tliet metodi minn fejn tagħżel:

• Agħfas il-buttuna Żid mal-Mudell (Żid mal-Mudell tad-Data) tab powerpivot tab home (Dar).
  
• Għażla ta' timijiet Daħħal – PivotTable (Daħħal — Pivot Table) u ixgħel il-kaxxa tal-kontroll Żid din id-dejta mal-Mudell tad-Data (Żid din id-dejta mal-Mudell tad-Data). F'dan il-każ, skont id-dejta mgħobbija fil-Mudell, tinbena wkoll immedjatament tabella tal-pern.
  
• Fuq it-tab Avvanzat Data (Data) ikklikkja fuq il-buttuna Minn Tabella/Firxa (Mill-Tabella/Firxa)biex tagħbija t-tabella tagħna fl-editur tal-Power Query. Din it-triq hija l-itwal, iżda, jekk mixtieq, hawnhekk tista 'twettaq tindif tad-dejta addizzjonali, editjar u kull tip ta' trasformazzjonijiet, li fihom Power Query hija b'saħħitha ħafna.

  Imbagħad id-data mimxuta tittella' fuq il-Mudell mill-kmand Id-dar — Agħlaq u Tagħbija — Agħlaq u Tagħbija fi... (Dar — Agħlaq&Tagħbija — Agħlaq&Tagħbija għal...). Fit-tieqa li tiftaħ, agħżel l-għażla Oħloq biss konnessjoni (Oħloq konnessjoni biss) u, aktar importanti, poġġi qurdien Żid din id-dejta mal-Mudell tad-Data (Żid din id-dejta mal-Mudell tad-Data).
  

Aħna nibnu sommarju tal-Mudell tad-Data

Biex tibni Mudell tad-Dejta fil-qosor, tista' tuża kwalunkwe minn tliet approċċi:

• Agħfas il-buttuna tabella fil-qosor (Tabella tal-Pern) fit-tieqa tal-Power Pivot.
• Agħżel kmandi f'Excel Daħħal – PivotTable u jaqilbu għall-modalità Uża l-mudell tad-dejta ta’ dan il-ktieb (Daħħal — Pivot Table — Uża l-Mudell tad-Data ta’ dan il-ktieb tax-xogħol).
• Għażla ta' timijiet Daħħal – PivotTable (Daħħal — Pivot Table) u ixgħel il-kaxxa tal-kontroll Żid din id-dejta mal-Mudell tad-Data (Żid din id-dejta mal-Mudell tad-Data). It-tabella "intelliġenti" attwali se titgħabba fil-Mudell u se tinbena tabella fil-qosor għall-Mudell kollu.

Issa li sibna kif tagħbija data fil-Mudell tad-Data u nibnu sommarju fuqha, ejja nesploraw il-benefiċċji u l-vantaġġi li dan jagħtina.

Benefiċċju 1: Relazzjonijiet bejn it-tabelli mingħajr ma tuża formuli

Sommarju regolari jista' jinbena biss bl-użu ta' data minn tabella ta' sors wieħed. Jekk għandek diversi minnhom, pereżempju, bejgħ, lista tal-prezzijiet, direttorju tal-klijenti, reġistru tal-kuntratti, eċċ., allura l-ewwel ikollok tiġbor data mit-tabelli kollha f'waħda billi tuża funzjonijiet bħal VLOOKUP (LOOKUP), INDIĊI (INDIĊI), AKTAR ESPOSTI (TAQBIL), SUMMESLIMN (SUMIFS) u simili. Dan huwa twil, tedious u jmexxi l-Excel tiegħek fi "ħsieb" b'ammont kbir ta 'dejta.

Fil-każ ta 'sommarju tal-Mudell tad-Data, kollox huwa ħafna aktar sempliċi. Huwa biżżejjed li twaqqaf relazzjonijiet bejn it-tabelli darba fit-tieqa tal-Power Pivot - u lest. Biex tagħmel dan, fuq it-tab powerpivot agħfas il-buttuna Ġestjoni (Immaniġġja) u mbagħad fit-tieqa li tidher – il-buttuna Ċart View (Dijagramma View). Jibqa' li jkaxkru ismijiet ta' kolonni (oqsma) komuni (ewlenin) bejn it-tabelli biex jinħolqu links:

Wara dan, fis-sommarju għall-Mudell tad-Dejta, tista 'tarmi fiż-żona ta' sommarju (ringieli, kolonni, filtri, valuri) kwalunkwe qasam minn kwalunkwe tabelli relatati - kollox se jkun marbut u kkalkulat awtomatikament:

Benefiċċju 2: Għadd valuri uniċi

Tabella tal-pern regolari tagħtina l-opportunità li nagħżlu waħda minn diversi funzjonijiet ta’ kalkolu integrati: somma, medja, għadd, minimu, massimu, eċċ. Fis-sommarju tal-Mudell tad-Data, funzjoni utli ħafna hija miżjuda ma’ din il-lista standard biex tgħodd il- numru ta' uniċi (valuri li ma jirrepetux). Bl-għajnuna tagħha, pereżempju, tista 'faċilment tgħodd in-numru ta' oġġetti uniċi ta 'oġġetti (firxa) li nbiegħu f'kull belt.

Ikklikkja bil-lemin fuq il-qasam – kmand Għażliet tal-qasam tal-valur u fuq it-tab operazzjoni Agħżel Numru ta 'elementi differenti (Għadd distint):

Benefiċċju 3: Formuli DAX personalizzati

Xi drabi trid twettaq diversi kalkoli addizzjonali fit-tabelli tal-pern. F'sommarji regolari, dan isir bl-użu ta' oqsma u oġġetti kkalkulati, filwaqt li s-sommarju tal-mudell tad-dejta juża miżuri f'lingwa DAX speċjali (DAX = Espressjonijiet tal-Analiżi tad-Data).

Biex toħloq miżura, agħżel fuq it-tab powerpivot Kmand Miżuri - Oħloq Miżura (Miżuri — Miżura ġdida) jew sempliċement ikklikkja bil-lemin fuq it-tabella fil-lista Oqsma Pivot u agħżel Żid miżura (Żid il-miżura) fil-menu tal-kuntest:

Fit-tieqa li tiftaħ, issettja:

• Isem tal-mejdafejn se tinħażen il-miżura maħluqa.
• Kejjel l-isem – kwalunkwe isem li tifhem għall-qasam il-ġdid.
• deskrizzjoni – fakultattiv.
• Formula – l-iktar ħaġa importanti, għax hawn jew nidħlu manwalment, jew ikklikkja fuq il-buttuna fx u agħżel funzjoni DAX mil-lista, li għandha tikkalkula r-riżultat meta mbagħad nitfgħu l-miżura tagħna fiż-żona tal-Valuri.
• Fil-parti t'isfel tat-tieqa, tista 'tissettja immedjatament il-format tan-numru għall-miżura fil-lista kategorija.
  

Il-lingwa DAX mhix dejjem faċli biex tinftiehem għax topera mhux b'valuri individwali, iżda b'kolonni u tabelli sħaħ, jiġifieri teħtieġ xi ristrutturar tal-ħsieb wara l-formuli klassiċi Excel. Madankollu, huwa worth it, minħabba li l-qawwa tal-kapaċitajiet tagħha fl-ipproċessar ta 'ammonti kbar ta' data huwa diffiċli li tiġi stmata żżejjed.

Benefiċċju 4: Ġerarkiji tal-qasam tad-dwana

Ħafna drabi, meta toħloq rapporti standard, trid tarmi l-istess kombinazzjonijiet ta’ oqsma f’tabelli pivot f’sekwenza partikolari, pereżempju Sena-Kwarti-Xahar-Jum, Jew Kategorija-Prodott, Jew Pajjiż-Belt-Klijent eċċ Fis-sommarju tal-Mudell tad-Data, din il-problema tissolva faċilment billi toħloq tiegħek ġerarkiji — settijiet ta' oqsma tad-dwana.

Fit-tieqa tal-Power Pivot, aqleb għall-modalità chart bil-buttuna Ċart View tab home (Id-Dar — View Dijagramma), agħżel bi Ctrl oqsma mixtieqa u ikklikkja fuqhom bil-lemin. Il-menu tal-kuntest se jkun fih il-kmand Oħloq Ġerarkija (Oħloq ġerarkija):

Il-ġerarkija maħluqa tista 'tissemmi mill-ġdid u mkaxkra fiha bil-maws l-oqsma meħtieġa, sabiex aktar tard f'moviment wieħed ikunu jistgħu jintefgħu fis-sommarju:

Benefiċċju 5: Stensils tad-dwana

Tkompli l-idea tal-paragrafu preċedenti, fis-sommarju tal-Mudell tad-Dejta, tista 'wkoll toħloq is-settijiet ta' elementi tiegħek għal kull qasam. Pereżempju, mil-lista sħiħa ta 'bliet, tista' faċilment tagħmel sett ta 'dawk biss li huma fil-qasam tar-responsabbiltà tiegħek. Jew iġbor biss il-klijenti tiegħek, l-oġġetti tiegħek, eċċ. f'sett speċjali.

Biex tagħmel dan, fuq it-tab Analiżi tal-pivot table fil-lista drop down Oqsma, Oġġetti, u Settijiet hemm kmandi korrispondenti (Analizza — Oqsma, Items & Settijiet — Oħloq sett ibbażat fuq oġġetti ta’ ringiela/kolonna):

Fit-tieqa li tiftaħ, tista 'tneħħi, iżżid jew tibdel b'mod selettiv il-pożizzjoni ta' kwalunkwe element u ssalva s-sett li jirriżulta taħt isem ġdid:

Is-settijiet kollha maħluqa se jintwerew fil-pannell tal-Oqsma tal-PivotTable f'folder separat, minn fejn jistgħu jiġu mkaxkra b'mod liberu lejn iż-żoni tar-ringieli u l-kolonni ta' kwalunkwe PivotTable ġdida:

Benefiċċju 6: Aħbi selettivament it-Tabelli u l-Kolonni

Għalkemm dan huwa vantaġġ żgħir, iżda pjaċevoli ħafna f'xi każijiet. Billi tikklikkja bil-lemin fuq l-isem tal-qasam jew fuq it-tab tal-mejda fit-tieqa tal-Power Pivot, tista 'tagħżel il-kmand Aħbi mill-Klijent Toolkit (Aħbi mill-Għodod tal-Klijent):

Il-kolonna jew it-tabella moħbija se tisparixxi mill-pannell tal-Lista tal-Qasam tat-Tabella Pivot. Huwa konvenjenti ħafna jekk għandek bżonn taħbi mill-utent xi kolonni awżiljarji (per eżempju, ikkalkulati jew kolonni b'valuri ewlenin għall-ħolqien ta 'relazzjonijiet) jew saħansitra tabelli sħaħ.

Benefiċċju 7. drill-down avvanzat

Jekk tikklikkja darbtejn fuq kwalunkwe ċellula fiż-żona tal-valur f'tabella tal-pern regolari, allura Excel juri fuq folja separata kopja tal-framment tad-dejta tas-sors li kien involut fil-kalkolu ta 'din iċ-ċellula. Din hija ħaġa utli ħafna, imsejħa uffiċjalment Drill-down (fil-normalment jgħidu "falla").

Fis-sommarju tal-Mudell tad-Dejta, din l-għodda utli taħdem b'mod aktar sottili. Billi toqgħod fuq kwalunkwe ċellola bir-riżultat li jinteressana, tista’ tikklikkja fuq l-ikona b’lenti li titfaċċa ħdejha (jissejjaħ Esprimi Xejriet) u mbagħad agħżel kwalunkwe qasam li inti interessat fih fi kwalunkwe tabella relatata:

Wara dan, il-valur kurrenti (Mudell = Explorer) se jidħol fiż-żona tal-filtru, u s-sommarju jinbena mill-uffiċċji:

Naturalment, proċedura bħal din tista 'tiġi ripetuta ħafna drabi, u b'mod konsistenti tidħol fid-dejta tiegħek fid-direzzjoni li inti interessat fiha.

Benefiċċju 8: Ikkonverti Pivot għal Funzjonijiet tal-kubu

Jekk tagħżel xi ċellula fis-sommarju għall-Mudell tad-Dejta u mbagħad agħżel fuq it-tab Analiżi tal-pivot table Kmand Għodod OLAP - Ikkonverti għal Formuli (Analizza — Għodod OLAP — Ikkonverti għal formuli), allura s-sommarju kollu jiġi awtomatikament konvertit f'formuli. Issa l-valuri tal-kamp fiż-żona tal-kolonna tar-ringiela u r-riżultati fiż-żona tal-valur se jiġu rkuprati mill-Mudell tad-Dejta bl-użu tal-funzjonijiet tal-kubi speċjali: CUBEVALUE u CUBEMEMBER:

Teknikament, dan ifisser li issa m'aħniex nittrattaw sommarju, iżda ma 'diversi ċelloli b'formuli, jiġifieri nistgħu faċilment nagħmlu kwalunkwe trasformazzjoni bir-rapport tagħna li mhumiex disponibbli fis-sommarju, pereżempju, daħħal ringieli jew kolonni ġodda fin-nofs. tar-rapport, agħmel xi kalkoli addizzjonali ġewwa s-sommarju, irranġahom bi kwalunkwe mod mixtieq, eċċ.

Fl-istess ħin, il-konnessjoni mad-dejta tas-sors, ovvjament, tibqa 'u fil-futur dawn il-formuli se jiġu aġġornati meta jinbidlu s-sorsi. Is-sbuħija!

• Analiżi tal-fatt tal-pjan f'tabella pern b'Power Pivot u Power Query
• Pivot table b'header multiline
• Oħloq database f'Excel billi tuża Power Pivot