Buannachdan Pivot le Modail Dàta

Nuair a bhios sinn a’ togail clàr pivot ann an Excel, anns a’ chiad bhogsa deasbaid, far an tèid iarraidh oirnn an raon tùsail a shuidheachadh agus àite a thaghadh airson am bòrd pivot a chuir a-steach, tha bogsa-dearbhaidh neo-shoilleir ach glè chudromach gu h-ìosal - Cuir an dàta seo ris a’ mhodail dàta (Cuir ris an dàta seo gu Modail Dàta) agus, beagan nas àirde, an tionndadh Cleachd modail dàta an leabhair seo (Cleachd Modail Dàta den leabhar-obrach seo):

Gu mì-fhortanach, tha mòran de luchd-cleachdaidh a tha air a bhith eòlach air clàran pivot airson ùine mhòr agus gan cleachdadh gu soirbheachail san obair aca uaireannan nach eil a 'tuigsinn brìgh nan roghainnean sin agus nach eil iad gan cleachdadh. Agus gu dìomhain. Às deidh na h-uile, le bhith a’ cruthachadh clàr pivot airson am Modail Dàta bheir sinn grunn bhuannachdan fìor chudromach dhuinn an taca ris a’ chlàr pivot Excel clasaigeach.

Ach, mus smaoinich sinn air na “bunaichean” sin faisg air làimh, tuigidh sinn an-toiseach dè a th’ anns a’ mhodal dàta seo, gu dearbh?

Dè a th’ ann am Modail Dàta

Modail Dàta (air a ghiorrachadh mar MD no DM = Modail Dàta) na raon sònraichte taobh a-staigh faidhle Excel far an urrainn dhut dàta clàr a stòradh - aon chlàr no barrachd ceangailte, ma thogras tu, ri chèile. Gu dearbh, is e stòr-dàta beag a tha seo (ciùb OLAP) freumhaichte am broinn leabhar-obrach Excel. An coimeas ri stòradh dàta clasaigeach ann an cruth chlàran cunbhalach (no snasail) air duilleagan Excel fhèin, tha grunn bhuannachdan cudromach aig a’ mhodal dàta:

• Faodaidh clàran a bhith suas ri 2 billean loidhne, agus faodaidh duilleag Excel a bhith beagan a bharrachd air 1 millean.
• A dh'aindeoin cho mòr 'sa tha iad, thathar a' coileanadh a leithid de bhùird (sìoladh, rèiteachadh, àireamhachadh orra, geàrr-chunntas togail, msaa). glè luath Gu math nas luaithe na Excel fhèin.
• Leis an dàta anns a 'Mhodail, faodaidh tu àireamhachadh a bharrachd (ma thogras tu, gu math iom-fhillte) a dhèanamh a' cleachdadh cànan DAX togte.
  
• Tha a h-uile fiosrachadh a chaidh a luchdachadh a-steach don mhodail dàta glè air a dhlùthadh gu làidir a’ cleachdadh tasglann sònraichte togte agus an ìre mhath àrdaichidh e meud an fhaidhle Excel tùsail.
  

Tha am Modail air a riaghladh agus air a thomhas le tuilleadan sònraichte air a thogail a-steach do Microsoft Excel - cumhachd pivotmu'n do sgriobh mi cheana. Gus a chomasachadh, air an taba -leasachaidh cliog tuilleadan COM (Leasaiche - COM Add-ins) agus thoir sùil air a’ bhogsa iomchaidh:

Ma tha tabaichean -leasachaidh (Leasaiche)chan fhaic thu e air an rioban, faodaidh tu tionndadh troimhe File - Roghainnean - Ribbon Setup (Faidhle - Roghainnean - Gnàthaich Ribbon). Mura h-eil Power Pivot agad san uinneig a chithear gu h-àrd anns an liosta de thuilleadan COM, chan eil e air a thoirt a-steach don dreach agad de Microsoft Office 🙁

Air an tab Power Pivot a nochdas, bidh putan mòr aotrom uaine rianachd (Stiùirich), a’ briogadh air a dh’fhosglas an uinneag Power Pivot air mullach Excel, far am faic sinn na tha ann am Modail Dàta an leabhair làithreach:

Nòta cudromach air an t-slighe: chan urrainn ach aon mhodail dàta a bhith ann an leabhar-obrach Excel.

Luchdaich clàran a-steach don mhodail dàta

Gus dàta a luchdachadh a-steach don mhodail, an toiseach tionndaidh sinn am bòrd gu ath-ghoirid meur-chlàr fiùghantach “smart”. Ctrl+T agus thoir ainm càirdeil air an taba Neach-togail (Dealbhadh). Is e ceum riatanach a tha seo.

An uairsin faodaidh tu aon de na trì dòighean a chleachdadh airson taghadh:

• Brùth am putan Cuir ris a 'mhodail (Cuir ris a’ mhodail dàta) tab cumhachd pivot tab Dachaigh (Dachaigh).
  
• A 'taghadh sgiobaidhean Cuir a-steach - PivotTable (Cuir a-steach - Clàr Pivot) agus tionndaidh air a’ bhogsa-seic Cuir an dàta seo ris a’ mhodail dàta (Cuir an dàta seo ri Modail Dàta). Anns a 'chùis seo, a rèir an dàta a chaidh a luchdachadh a-steach don mhodail, tha clàr pivot cuideachd air a thogail sa bhad.
  
• Air an taba Adhartach Dàta (Ceann-latha) cliog air a ’phutan Bhon chlàr / raon (Bho Clàr/Raon)gus ar clàr a luchdachadh a-steach don deasaiche Power Query. Is e an t-slighe seo an tè as fhaide, ach, ma thogras tu, an seo faodaidh tu glanadh dàta a bharrachd, deasachadh agus gach seòrsa cruth-atharrachaidh a dhèanamh, anns a bheil Power Query gu math làidir.

  An uairsin thèid an dàta cìreadh a luchdachadh suas chun Model leis an àithne Dachaigh - Dùin is Luchdaich - Dùin is luchdaich a-steach… (Dachaigh - Dùin & Luchdaich - Dùin & Luchdaich gu ...). Anns an uinneag a tha a’ fosgladh, tagh an roghainn Dìreach cruthaich ceangal (Cruthaich ceangal a-mhàin) agus, nas cudromaiche, cuir diog Cuir an dàta seo ris a’ mhodail dàta (Cuir an dàta seo ri Modail Dàta).
  

Bidh sinn a’ togail geàrr-chunntas den mhodail dàta

Gus Modail Dàta geàrr-chunntas a thogail, faodaidh tu gin de na trì dòighean-obrach a chleachdadh:

• Brùth putan clàr geàrr-chunntas (Clàr Pivot) ann an uinneag Power Pivot.
• Tagh òrdughan ann an Excel Cuir a-steach - PivotTable agus tionndaidh gu modh Cleachd modail dàta an leabhair seo (Cuir a-steach - Clàr Pivot - Cleachd Modail Dàta an leabhair-obrach seo).
• A 'taghadh sgiobaidhean Cuir a-steach - PivotTable (Cuir a-steach - Clàr Pivot) agus tionndaidh air a’ bhogsa-seic Cuir an dàta seo ris a’ mhodail dàta (Cuir an dàta seo ri Modail Dàta). Thèid an clàr “smart” gnàthach a luchdachadh a-steach don mhodail agus thèid clàr geàrr-chunntas a thogail airson a’ mhodail gu lèir.

A-nis gu bheil sinn air faighinn a-mach mar as urrainn dhuinn dàta a luchdachadh a-steach don mhodal dàta agus geàrr-chunntas a thogail air, leig dhuinn sgrùdadh a dhèanamh air na buannachdan agus na buannachdan a tha seo a’ toirt dhuinn.

Buannachd 1: Dàimhean eadar bùird gun a bhith a’ cleachdadh foirmlean

Chan urrainnear geàrr-chunntas cunbhalach a thogail ach le dàta bho aon chlàr stòr. Ma tha grunn dhiubh agad, mar eisimpleir, reic, liosta phrìsean, eòlaire teachdaiche, clàr chùmhnantan, msaa, feumaidh tu an toiseach dàta a chruinneachadh bho na clàran gu lèir ann an aon a’ cleachdadh gnìomhan leithid VLOOKUP (VLOOKUP), CLÀRACH (INDEX), TUILLEADH DHUILLEAG (MATCH), SUMMESLIMN (SUMIFS) agus an leithid. Tha seo fada, tedious agus a’ draibheadh ​​​​an Excel agad gu “smaoineachadh” le tòrr dàta.

Ann an cùis geàrr-chunntas den mhodail dàta, tha a h-uile dad tòrr nas sìmplidh. Tha e gu leòr dàimhean a stèidheachadh eadar bùird aon uair ann an uinneag Power Pivot - agus tha e deiseil. Gus seo a dhèanamh, air an taba cumhachd pivot brùth am putan rianachd (Stiùirich) agus an uairsin san uinneig a nochdas - am putan Sealladh Cairt (Sealladh Diagram). Tha e fhathast ri ainmean colbh (prìomh) cumanta (raointean) a shlaodadh eadar bùird gus ceanglaichean a chruthachadh:

Às deidh sin, anns a ’gheàrr-chunntas airson a’ mhodail dàta, faodaidh tu raointean sam bith bho chlàran co-cheangailte a thilgeil a-steach don raon geàrr-chunntas (sreathan, colbhan, sìoltachain, luachan) - bidh a h-uile dad ceangailte agus air a thomhas gu fèin-ghluasadach:

Buannachd 2: Cunnt luachan sònraichte

Bheir clàr pivot cunbhalach cothrom dhuinn aon de ghrunn ghnìomhan àireamhachaidh togte a thaghadh: suim, cuibheasachd, cunntadh, as ìsle, as àirde, msaa. àireamh de luachan sònraichte (neo-ath-aithris). Le cuideachadh, mar eisimpleir, is urrainn dhut gu furasta an àireamh de stuthan sònraichte (raon) a bhios sinn a 'reic anns gach baile-mòr a chunntadh.

Dèan briogadh deas air an raon - àithne Roghainnean raon luach agus air an taba Operation Tagh Àireamh de dhiofar eileamaidean (Cunntas sònraichte):

Buannachd 3: Foirmlean DAX Custom

Uaireannan feumaidh tu diofar àireamhachadh a bharrachd a dhèanamh ann an clàran pivot. Ann an geàrr-chunntasan cunbhalach, tha seo air a dhèanamh le bhith a’ cleachdadh raointean agus nithean àireamhaichte, fhad ‘s a tha geàrr-chunntas a’ mhodail dàta a ’cleachdadh ceumannan ann an cànan sònraichte DAX (DAX = abairtean mion-sgrùdadh dàta).

Gus tomhas a chruthachadh, tagh air an taba cumhachd pivot Command Ceumannan - Cruthaich Tomhas (Ceumannan - Tomhas ùr) no dìreach cliog deas air a’ chlàr anns an liosta Pivot Fields agus tagh Cuir ris an tomhas (Cuir tomhas ris) sa chlàr-taice co-theacsa:

Anns an uinneag a tha a’ fosgladh, suidhich:

• Ainm clàrfar am bi an tomhas cruthaichte air a stòradh.
• Tomhais ainm - ainm sam bith a thuigeas tu airson an raon ùr.
• Tuairisgeul - roghainneil.
• Formula - an rud as cudromaiche, oir an seo thèid sinn a-steach le làimh, no cliog air a’ phutan fx agus tagh gnìomh DAX bhon liosta, a bu chòir an toradh obrachadh a-mach nuair a thilgeas sinn ar tomhas a-steach don raon Luachan.
• Ann am pàirt ìosal na h-uinneige, faodaidh tu an cruth àireamh a shuidheachadh sa bhad airson an tomhas san liosta Roinn-seòrsa.
  

Chan eil an cànan DAX an-còmhnaidh furasta a thuigsinn oir chan eil e ag obrachadh le luachan fa leth, ach le colbhan agus clàran slàn, ie feumar beagan ath-structaradh smaoineachaidh às deidh na foirmlean clasaigeach Excel. Ach, is fhiach e, oir tha e doirbh cus tuairmse a dhèanamh air cumhachd a chomasan ann a bhith a 'giullachd tòrr dàta.

Buannachd 4: rangachd raoin gnàthaichte

Gu tric, nuair a bhios tu a’ cruthachadh aithisgean àbhaisteach, feumaidh tu na h-aon mheasgachaidhean de raointean a thilgeil a-steach do chlàran pivot ann an sreath sònraichte, mar eisimpleir Bliadhna—Ceathramh-Mìos-Latha, no Roinn-Bathar, no Dùthaich-Cathair-Cliant msaa Ann an geàrr-chunntas Modail Dàta, tha an duilgheadas seo furasta fhuasgladh le bhith a’ cruthachadh do chuid fhèin rangachd - seataichean raoin àbhaisteach.

Anns an uinneag Power Pivot, gluais gu modh clàir leis a 'phutan Sealladh Cairt tab Dachaigh (Dachaigh - Sealladh Diagram), tagh le Ctrl raointean a tha thu ag iarraidh agus cliog deas orra. Bidh an àithne anns a’ chlàr co-theacsa Cruthaich rangachd (Cruthaich rangachd):

Faodar an rangachd cruthaichte ath-ainmeachadh agus a shlaodadh a-steach ann leis an luchag na raointean a tha a dhìth, gus an tèid an tilgeil a-steach don gheàrr-chunntas nas fhaide air adhart ann an aon ghluasad:

Buannachd 5: Custom stencils

A 'leantainn air adhart leis a' bheachd air a 'pharagraf roimhe, anns a' gheàrr-chunntas den Modail Dàta, faodaidh tu cuideachd na seataichean eileamaidean agad fhèin a chruthachadh airson gach raon. Mar eisimpleir, bhon liosta iomlan de bhailtean-mòra, faodaidh tu seata a dhèanamh gu furasta de dìreach an fheadhainn a tha nad raon dleastanais. No cruinnich dìreach do luchd-ceannach, do bhathar, msaa ann an seata sònraichte.

Gus seo a dhèanamh, air an taba Mion-sgrùdadh clàr pivot anns an liosta tuiteam-sìos Raoin, Nithean, agus Seataichean tha òrdughan co-fhreagarrach ann (Mion-sgrùdadh - Fields, Items & Sets - Cruthaich seata stèidhichte air nithean sreath / colbh):

Anns an uinneag a tha a’ fosgladh, faodaidh tu suidheachadh eileamaidean sam bith a thoirt air falbh, a chur ris no atharrachadh gu roghnach agus an seata a thig às a shàbhaladh fo ainm ùr:

Thèid a h-uile seata cruthaichte a thaisbeanadh anns a’ phannal PivotTable Fields ann am pasgan air leth, às am faodar an slaodadh gu saor gu raointean nan sreathan agus nan colbhan ann am PivotTable ùr sam bith:

Buannachd 6: Falaich clàran is colbhan gu roghnach

Ged a tha seo na bhuannachd bheag, ach gu math tlachdmhor ann an cuid de chùisean. Le bhith a’ briogadh deas air ainm an raoin no air taba a’ bhùird ann an uinneag Power Pivot, faodaidh tu an àithne a thaghadh Falaich bho Client Toolkit (Falaich bho Innealan Cliant):

Thèid an colbh no an clàr falaichte à sealladh bhon phana Liosta Achaidh PivotTable. Tha e gu math goireasach ma dh'fheumas tu cuid de cholbhan taice a chuir am falach bhon neach-cleachdaidh (mar eisimpleir, àireamhachadh no colbhan le prìomh luachan airson dàimhean a chruthachadh) no eadhon clàran slàn.

Buannachd 7. Adhartach drile-sìos

Ma nì thu briogadh dùbailte air cealla sam bith san raon luach ann an clàr pivot cunbhalach, an uairsin bidh Excel a’ taisbeanadh leth-bhreac den chriomag dàta tùsail a bha an sàs ann an àireamhachadh na cealla seo air duilleag air leth. Is e rud gu math feumail a tha seo, ris an canar gu h-oifigeil Drill-down (mar as trice bidh iad ag ràdh “fàiligeadh”).

Anns a 'gheàrr-chunntas Modail Dàta, bidh an inneal feumail seo ag obair nas sìmplidhe. Le bhith a’ seasamh air cealla sam bith leis an toradh a tha inntinneach dhuinn, faodaidh tu briogadh air an ìomhaigh le glainne-mheudachaidh a nochdas ri thaobh (canar ris Cur an cèill Trends) agus an uairsin tagh raon sam bith anns a bheil ùidh agad ann an clàr co-cheangailte sam bith:

Às deidh sin, thèid an luach làithreach (Model = Explorer) a-steach don raon sìoltachain, agus thèid an geàrr-chunntas a thogail le oifisean:

Gu dearbh, faodar a leithid de dhòigh-obrach ath-aithris grunn thursan, a ’toirt a-steach an dàta agad gu cunbhalach anns an taobh anns a bheil ùidh agad.

Buannachd 8: Tionndaidh Pivot gu gnìomhan Cube

Ma thaghas tu cealla sam bith sa gheàrr-chunntas airson a’ Mhodail Dàta agus an uairsin tagh air an taba Mion-sgrùdadh clàr pivot Command Innealan OLAP - Tionndaidh gu foirmlean (Mion-sgrùdadh - Innealan OLAP - Tionndaidh gu foirmlean), an uairsin thèid an geàrr-chunntas gu lèir a thionndadh gu foirmlean gu fèin-ghluasadach. A-nis thèid na luachan achaidh anns an raon colbh-sreath agus na toraidhean anns an raon luach fhaighinn air ais bhon mhodail dàta a ’cleachdadh na gnìomhan ciùb sònraichte: CUBEVALUE agus CUBEMEMBER:

Gu teicnigeach, tha seo a 'ciallachadh nach eil sinn a-nis a' dèiligeadh ri geàrr-chunntas, ach le grunn cheallan le foirmlean, ie is urrainn dhuinn cruth-atharrachaidhean sam bith a dhèanamh leis an aithisg againn nach eil rim faighinn sa gheàrr-chunntas, mar eisimpleir, cuir a-steach sreathan no colbhan ùra sa mheadhan. den aithisg, dèan àireamhachadh a bharrachd sa gheàrr-chunntas, cuir air dòigh iad ann an dòigh sam bith a tha thu ag iarraidh, msaa.

Aig an aon àm, tha an ceangal ris an stòr dàta, gu dearbh, fhathast agus san àm ri teachd thèid na foirmlean sin ùrachadh nuair a dh’ atharraicheas na stòran. Am bòidhchead!

• Mion-sgrùdadh plana-fìrinn ann an clàr pivot le Power Pivot agus Power Query
• Clàr pivot le bann-cinn ioma-loidhne
• Cruthaich stòr-dàta ann an Excel a’ cleachdadh Power Pivot