Manteision Colyn yn ôl Model Data

Wrth adeiladu tabl colyn yn Excel, yn y blwch deialog cyntaf un, lle gofynnir i ni osod yr ystod gychwynnol a dewis lle i fewnosod y tabl colyn, mae blwch gwirio anamlwg ond pwysig iawn isod - Ychwanegwch y data hwn at y Model Data (Ychwanegwch y data hwn i'r Model Data) ac, ychydig yn uwch, y switsh Defnyddiwch fodel data'r llyfr hwn (Defnyddiwch Fodel Data'r llyfr gwaith hwn):

Yn anffodus, weithiau nid yw llawer o ddefnyddwyr sydd wedi bod yn gyfarwydd â thablau colyn ers amser maith ac yn eu defnyddio'n llwyddiannus yn eu gwaith yn deall ystyr yr opsiynau hyn mewn gwirionedd a byth yn eu defnyddio. Ac yn ofer. Wedi'r cyfan, mae creu tabl colyn ar gyfer y Model Data yn rhoi nifer o fanteision pwysig iawn i ni o gymharu â thabl colyn clasurol Excel.

Fodd bynnag, cyn ystyried y “byniau” hyn yn agos, gadewch i ni ddeall yn gyntaf beth, mewn gwirionedd, yw'r Model Data hwn?

Beth yw Model Data

Model Data (wedi'i dalfyrru fel MD neu DM = Model Data) yn faes arbennig y tu mewn i ffeil Excel lle gallwch storio data tabl - un neu fwy o dablau wedi'u cysylltu, os dymunir, â'i gilydd. Mewn gwirionedd, cronfa ddata fach yw hon (ciwb OLAP) sydd wedi'i hymgorffori mewn llyfr gwaith Excel. O'i gymharu â storio data clasurol ar ffurf tablau rheolaidd (neu glyfar) ar ddalennau Excel ei hun, mae gan y Model Data nifer o fanteision sylweddol:

• Gall tablau fod hyd at 2 biliwn o linellau, a gall taflen Excel ffitio ychydig yn fwy na 1 miliwn.
• Er gwaethaf y maint enfawr, mae prosesu tablau o'r fath (hidlo, didoli, cyfrifiadau arnynt, crynodeb adeiladu, ac ati) yn cael ei berfformio Cyflym iawn Yn llawer cyflymach nag Excel ei hun.
• Gyda'r data yn y Model, gallwch wneud cyfrifiadau ychwanegol (os dymunir, cymhleth iawn) gan ddefnyddio iaith DAX adeiledig.
  
• Mae'r holl wybodaeth a lwythir i'r Model Data yn iawn wedi'i gywasgu'n gryf defnyddio archifydd adeiledig arbennig ac yn hytrach yn cynyddu maint y ffeil Excel wreiddiol yn gymedrol.
  

Mae'r Model yn cael ei reoli a'i gyfrifo gan ychwanegiad arbennig sydd wedi'i ymgorffori yn Microsoft Excel - Powerpivotyr wyf eisoes wedi ysgrifennu amdano. Er mwyn ei alluogi, ar y tab datblygwr cliciwch Ychwanegion COM (Datblygwr - Ychwanegion COM) a thiciwch y blwch priodol:

Os tabiau datblygwr (Datblygwr)ni allwch ei weld ar y rhuban, gallwch ei droi ymlaen Ffeil - Opsiynau - Gosod Rhuban (Ffeil - Opsiynau - Addasu Rhuban). Os nad oes gennych Power Pivot yn y ffenestr a ddangosir uchod yn y rhestr o ychwanegion COM, yna nid yw wedi'i gynnwys yn eich fersiwn o Microsoft Office 🙁

Ar y tab Power Pivot sy'n ymddangos, bydd botwm gwyrdd golau mawr rheoli (Rheoli), gan glicio ar a fydd yn agor y ffenestr Power Pivot ar ben Excel, lle byddwn yn gweld cynnwys Model Data'r llyfr cyfredol:

Nodyn pwysig ar hyd y ffordd: dim ond un Model Data y gall llyfr gwaith Excel ei gynnwys.

Llwytho tablau i'r Model Data

I lwytho data i'r Model, yn gyntaf rydym yn troi'r tabl yn llwybr byr bysellfwrdd deinamig “smart”. Ctrl+T a rho enw cyfeillgar ar y tab Constructor (Dylunio). Mae hwn yn gam gofynnol.

Yna gallwch ddefnyddio unrhyw un o'r tri dull i ddewis ohonynt:

• Pwyswch y botwm Ychwanegu at y Model (Ychwanegu at y Model Data) tab Powerpivot tab Hafan (Cartref).
  
• Dewis timau Mewnosod – PivotTable (Mewnosod — Tabl Colyn) a throwch y blwch ticio ymlaen Ychwanegwch y data hwn at y Model Data (Ychwanegwch y data hwn at y Model Data). Yn yr achos hwn, yn ôl y data a lwythwyd i'r Model, mae tabl colyn hefyd yn cael ei adeiladu ar unwaith.
  
• Ar y tab Advanced Dyddiad (Dyddiad) cliciwch ar y botwm O'r Tabl / Ystod (O'r Tabl / Ystod)i lwytho ein bwrdd i mewn i'r golygydd Power Query. Y llwybr hwn yw'r hiraf, ond, os dymunir, yma gallwch chi berfformio glanhau data ychwanegol, golygu a phob math o drawsnewidiadau, lle mae Power Query yn gryf iawn.

  Yna mae'r data cribo yn cael ei lanlwytho i'r Model gan y gorchymyn Cartref - Cau a Llwythwch - Cau a Llwythwch i mewn… (Cartref - Cau a Llwyth - Cau a Llwythwch i…). Yn y ffenestr sy'n agor, dewiswch yr opsiwn Dim ond creu cysylltiad (Dim ond creu cysylltiad) ac, yn bwysicaf oll, rhowch dic Ychwanegwch y data hwn at y Model Data (Ychwanegwch y data hwn at y Model Data).
  

Rydym yn adeiladu crynodeb o'r Model Data

I adeiladu Model Data cryno, gallwch ddefnyddio unrhyw un o dri dull:

• Gwasgwch y botwm tabl cryno (Tabl Colyn) yn y ffenestr Power Pivot.
• Dewiswch orchmynion yn Excel Mewnosod – PivotTable a newid i modd Defnyddiwch fodel data'r llyfr hwn (Mewnosod — Tabl Colyn — Defnyddiwch Fodel Data'r llyfr gwaith hwn).
• Dewis timau Mewnosod – PivotTable (Mewnosod — Tabl Colyn) a throwch y blwch ticio ymlaen Ychwanegwch y data hwn at y Model Data (Ychwanegwch y data hwn at y Model Data). Bydd y tabl “clyfar” cyfredol yn cael ei lwytho i mewn i'r Model a bydd tabl cryno yn cael ei adeiladu ar gyfer y Model cyfan.

Nawr ein bod wedi cyfrifo sut i lwytho data i'r Model Data ac adeiladu crynodeb arno, gadewch i ni archwilio'r manteision a'r manteision y mae hyn yn eu rhoi i ni.

Mantais 1: Perthynas rhwng tablau heb ddefnyddio fformiwlâu

Dim ond gan ddefnyddio data o un tabl ffynhonnell y gellir adeiladu crynodeb rheolaidd. Os oes gennych chi nifer ohonynt, er enghraifft, gwerthiannau, rhestr brisiau, cyfeiriadur cwsmeriaid, cofrestr contractau, ac ati, yna yn gyntaf bydd yn rhaid i chi gasglu data o bob tabl yn un gan ddefnyddio swyddogaethau fel VLOOKUP (VLOOKUP), MYNEGAI (MYNEGAI), MWY AMLWG (MATCH), SUMMESLIMN (SUMIFS) ac yn y blaen. Mae hyn yn hir, yn ddiflas ac yn gyrru'ch Excel i "feddwl" gyda llawer iawn o ddata.

Yn achos crynodeb o'r Model Data, mae popeth yn llawer symlach. Mae'n ddigon sefydlu perthnasoedd rhwng byrddau unwaith yn y ffenestr Power Pivot - ac mae wedi gwneud hynny. I wneud hyn, ar y tab Powerpivot pwyswch y botwm rheoli (Rheoli) ac yna yn y ffenestr sy'n ymddangos - y botwm Golygfa Siart (Gwedd Diagram). Erys i lusgo enwau colofn (meysydd) cyffredin (allweddol) rhwng tablau i greu dolenni:

Ar ôl hynny, yn y crynodeb ar gyfer y Model Data, gallwch chi daflu unrhyw feysydd o unrhyw dablau cysylltiedig yn yr ardal gryno (rhesi, colofnau, hidlwyr, gwerthoedd) - bydd popeth yn cael ei gysylltu a'i gyfrifo'n awtomatig:

Mantais 2: Cyfrif gwerthoedd unigryw

Mae tabl colyn rheolaidd yn rhoi'r cyfle i ni ddewis un o nifer o swyddogaethau cyfrifo adeiledig: swm, cyfartaledd, cyfrif, lleiafswm, uchafswm, ac ati. Yng nghrynodeb y Model Data, ychwanegir swyddogaeth ddefnyddiol iawn at y rhestr safonol hon i gyfrif y nifer unigryw (gwerthoedd nad ydynt yn ailadrodd). Gyda'i help, er enghraifft, gallwch chi gyfrif yn hawdd nifer yr eitemau unigryw o nwyddau (ystod) rydyn ni'n eu gwerthu ym mhob dinas.

De-gliciwch ar y maes - gorchymyn Gwerth opsiynau maes ac ar y tab Ymgyrch Dewiswch Nifer o wahanol elfennau (Cyfrif ar wahân):

Budd 3: Fformiwlâu DAX Custom

Weithiau mae'n rhaid i chi wneud cyfrifiadau ychwanegol amrywiol mewn tablau colyn. Mewn crynodebau rheolaidd, gwneir hyn gan ddefnyddio meysydd a gwrthrychau wedi'u cyfrifo, tra bod y crynodeb model data yn defnyddio mesurau mewn iaith DAX arbennig (DAX = Mynegiadau Dadansoddi Data).

I greu mesur, dewiswch ar y tab Powerpivot Gorchymyn Mesurau – Creu Mesur (Mesurau - Mesur newydd) neu de-gliciwch ar y tabl yn y rhestr Pivot Fields a dewis Ychwanegu mesur (Ychwanegu mesur) yn y ddewislen cyd-destun:

Yn y ffenestr sy'n agor, gosodwch:

• Enw'r tabllle bydd y mesur a grëwyd yn cael ei storio.
• Mesur enw – unrhyw enw rydych chi'n ei ddeall ar gyfer y maes newydd.
• Disgrifiad - dewisol.
• Fformiwla - y peth pwysicaf, oherwydd yma rydyn ni naill ai'n mynd i mewn â llaw, neu'n clicio ar y botwm fx a dewiswch swyddogaeth DAX o'r rhestr, a ddylai gyfrifo'r canlyniad pan fyddwn wedyn yn taflu ein mesur i'r ardal Gwerthoedd.
• Yn rhan isaf y ffenestr, gallwch chi osod y fformat rhif ar gyfer y mesur yn y rhestr ar unwaith Categori.
  

Nid yw'r iaith DAX bob amser yn hawdd i'w deall oherwydd nid yw'n gweithredu gyda gwerthoedd unigol, ond gyda cholofnau a thablau cyfan, hy mae angen rhywfaint o ailstrwythuro meddwl ar ôl y fformiwlâu Excel clasurol. Fodd bynnag, mae'n werth chweil, oherwydd mae'n anodd goramcangyfrif pŵer ei alluoedd wrth brosesu symiau mawr o ddata.

Mantais 4: Hierarchaethau maes personol

Yn aml, wrth greu adroddiadau safonol, mae'n rhaid i chi daflu'r un cyfuniadau o feysydd i dablau colyn mewn dilyniant penodol, er enghraifft Blwyddyn-Chwarter-Mis-Dydd, neu Categori-Cynnyrch, neu Gwlad-Dinas-Cleient ac ati. Yn y crynodeb Model Data, mae'n hawdd datrys y broblem hon trwy greu un eich hun hierarchaethau - setiau maes wedi'u teilwra.

Yn y ffenestr Power Pivot, newidiwch i'r modd siart gyda'r botwm Golygfa Siart tab Hafan (Cartref - Golwg Diagram), dewiswch gyda Ctrl meysydd dymunol a de-gliciwch arnynt. Bydd y ddewislen cyd-destun yn cynnwys y gorchymyn Creu Hierarchaeth (Creu hierarchaeth):

Gellir ailenwi'r hierarchaeth a grëwyd a'i llusgo i mewn iddi gyda'r llygoden y meysydd gofynnol, fel y gellir eu taflu i'r crynodeb yn ddiweddarach mewn un symudiad:

Budd 5: Stensiliau personol

Gan barhau â syniad y paragraff blaenorol, yn y crynodeb o'r Model Data, gallwch hefyd greu eich setiau eich hun o elfennau ar gyfer pob maes. Er enghraifft, o'r rhestr gyfan o ddinasoedd, gallwch chi wneud set o ddim ond y rhai sydd yn eich maes cyfrifoldeb yn hawdd. Neu casglwch eich cwsmeriaid, eich nwyddau, ac ati yn unig i set arbennig.

I wneud hyn, ar y tab Dadansoddiad tabl colyn yn y gwymplen Meysydd, Eitemau, a Setiau mae yna orchmynion cyfatebol (Dadansoddwch - Fields, Items & Sets - Creu set yn seiliedig ar eitemau rhes / colofn):

Yn y ffenestr sy'n agor, gallwch dynnu, ychwanegu neu newid lleoliad unrhyw elfennau yn ddetholus ac arbed y set canlyniadol o dan enw newydd:

Bydd yr holl setiau a grëwyd yn cael eu harddangos ym mhanel PivotTable Fields mewn ffolder ar wahân, lle gellir eu llusgo'n rhydd i ardaloedd rhesi a cholofnau unrhyw PivotTable newydd:

Mantais 6: Cuddio Tablau a Cholofnau'n Ddewisol

Er bod hyn yn fantais fach, ond dymunol iawn mewn rhai achosion. Trwy dde-glicio ar enw'r maes neu ar y tab bwrdd yn y ffenestr Power Pivot, gallwch ddewis y gorchymyn Cuddio o'r Pecyn Cymorth Cleient (Cuddio o Offer Cleient):

Bydd y golofn neu'r tabl cudd yn diflannu o'r cwarel Rhestr Maes PivotTable. Mae'n gyfleus iawn os oes angen i chi guddio rhai colofnau ategol oddi wrth y defnyddiwr (er enghraifft, wedi'u cyfrifo neu golofnau gyda gwerthoedd allweddol ar gyfer creu perthnasoedd) neu hyd yn oed tablau cyfan.

Mantais 7. Uwch drilio i lawr

Os ydych chi'n clicio ddwywaith ar unrhyw gell yn yr ardal werth mewn tabl colyn rheolaidd, yna mae Excel yn dangos copi o'r darn data ffynhonnell a oedd yn ymwneud â chyfrifo'r gell hon ar ddalen ar wahân. Mae hwn yn beth defnyddiol iawn, a elwir yn swyddogol Drill-down (yn y maent fel arfer yn dweud “methu”).

Yn y crynodeb Model Data, mae'r offeryn defnyddiol hwn yn gweithio'n fwy cynnil. Wrth sefyll ar unrhyw gell gyda'r canlyniad sydd o ddiddordeb i ni, gallwch glicio ar yr eicon gyda chwyddwydr sy'n ymddangos wrth ei ymyl (fe'i gelwir Tueddiadau Mynegi) ac yna dewiswch unrhyw faes y mae gennych ddiddordeb ynddo mewn unrhyw dabl cysylltiedig:

Ar ôl hynny, bydd y gwerth cyfredol (Model = Explorer) yn mynd i'r ardal hidlo, a bydd y crynodeb yn cael ei adeiladu gan swyddfeydd:

Wrth gwrs, gellir ailadrodd gweithdrefn o'r fath lawer gwaith, gan ymchwilio'n gyson i'ch data i'r cyfeiriad y mae gennych ddiddordeb ynddo.

Mantais 8: Trosi Colyn i Swyddogaethau Ciwb

Os dewiswch unrhyw gell yn y crynodeb ar gyfer y Model Data ac yna dewiswch ar y tab Dadansoddiad tabl colyn Gorchymyn Offer OLAP - Trosi i Fformiwlâu (Dadansoddi - Offer OLAP - Trosi i fformiwlâu), yna bydd y crynodeb cyfan yn cael ei drawsnewid yn awtomatig i fformiwlâu. Nawr bydd y gwerthoedd maes yn ardal y golofn rhes a'r canlyniadau yn yr ardal werth yn cael eu hadalw o'r Model Data gan ddefnyddio'r swyddogaethau ciwb arbennig: CUBEVALUE a CUBEMEMBER:

Yn dechnegol, mae hyn yn golygu nad ydym bellach yn delio â chrynodeb, ond â sawl cell â fformiwlâu, hy gallwn yn hawdd wneud unrhyw drawsnewidiadau â'n hadroddiad nad ydynt ar gael yn y crynodeb, er enghraifft, mewnosod rhesi neu golofnau newydd yn y canol. o'r adroddiad, gwnewch unrhyw gyfrifiadau ychwanegol yn y crynodeb, trefnwch nhw mewn unrhyw ffordd ddymunol, ac ati.

Ar yr un pryd, mae'r cysylltiad â'r data ffynhonnell, wrth gwrs, yn parhau ac yn y dyfodol bydd y fformiwlâu hyn yn cael eu diweddaru pan fydd y ffynonellau'n newid. Y harddwch!

• Dadansoddiad cynllun-ffaith mewn tabl colyn gyda Power Pivot a Power Query
• Tabl colyn gyda phennawd aml-linell
• Creu cronfa ddata yn Excel gan ddefnyddio Power Pivot