Mabhenefiti ePivot neData Model

Kana tichivaka tafura yepivot muExcel, mubhokisi rekutanga renhaurirano, patinokumbirwa kuseta yekutanga uye kusarudza nzvimbo yekuisa tafura yepivot, pane isingaonekwi asi yakakosha bhokisi rekutarisa pazasi - Wedzera iyi data kuData Model (Wedzera iyi data kune Data Model) uye, kumusoro zvishoma, chinja Shandisa mhando yedata yebhuku rino (Shandisa Data Model yebhuku rino rebasa):

Nehurombo, vashandisi vazhinji vanga vachiziva matafura epivot kwenguva yakareba uye nekubudirira kuashandisa mubasa ravo dzimwe nguva havanyatso nzwisisa zvinorehwa nesarudzo idzi uye havambozvishandisa. Uye pasina. Mushure mezvose, kugadzira tafura yepivot yeData Model inotipa akati wandei akakosha mabhenefiti tichienzaniswa neyekare Excel pivot tafura.

Nekudaro, tisati tafunga aya "mabhanzi" padhuze, ngatitangei kunzwisisa kuti chii, muchokwadi, iyi Data Model chii?

Chii chinonzi Data Model

Data Model (yakapfupikiswa seMD kana DM = Data Model) inzvimbo yakakosha mukati meExcel faira kwaunogona kuchengeta tabular data - imwe kana anopfuura matafura akabatanidzwa, kana achida, kune mumwe nemumwe. Muchokwadi, iyi idiki dhatabhesi (OLAP cube) yakamisikidzwa mukati meExcel bhuku rebasa. Kuenzaniswa neyekare yekuchengetedza data muchimiro chenguva dzose (kana smart) matafura pamapepa eExcel pachayo, iyo Data Model ine akati wandei akakosha mabhenefiti:

• Matafura anogona kusvika 2 bhiriyoni mitsetse, uye pepa reExcel rinogona kukwana zvishoma kudarika miriyoni imwe.
• Pasinei nehukuru hukuru, kugadzirisa kwematafura akadaro (kusefa, kuronga, kuverenga pazviri, pfupiso yekuvaka, nezvimwewo) kunoitwa. nekukurumidza kwazvo Inokurumidza kupfuura Excel pachayo.
• Ne data iri muModheni, unogona kuita yekuwedzera (kana uchida, yakaoma kwazvo) kuverenga uchishandisa yakavakirwa-mumutauro weDAX.
  
• Ruzivo rwese rwakaiswa muData Model yakanyanya zvakasimba compressed uchishandisa yakakosha yakavakirwa-mukati-mudura uye zviri pakati nepakati inowedzera saizi yepakutanga Excel faira.
  

Iyo modhi inotungamirwa uye inoverengerwa neyakakosha yekuwedzera-mukati yakavakirwa muMicrosoft Excel - powerpivotzvandatonyora nezvazvo. Kuti uigonese, pane tab yokuvaka watinya COM yekuwedzera-in (Mugadziri - COM Yekuwedzera) uye tarisa bhokisi rakakodzera:

Kana ma tabo yokuvaka (Mugadziri)haugone kuiona paribhoni, unogona kuibatidza Faira - Sarudzo - Ribhoni Setup (Faira - Sarudzo - Gadzirisa Ribhoni). Kana muhwindo rinoratidzwa pamusoro pane rondedzero yeCOM yekuwedzera iwe usina Power Pivot, saka haina kuisirwa mushanduro yako yeMicrosoft Office 🙁

PaPower Pivot tab inoonekwa, pachave nebhatani hombe rakasvibira utariri (Manage), nekudzvanya pane ichavhura iyo Power Pivot hwindo pamusoro peExcel, kwatichaona zviri mukati meData Model yebhuku razvino:

Chinyorwa chakakosha munzira: bhuku rekushanda reExcel rinogona kunge riine imwe Data Model.

Isa matafura muData Model

Kurodha data muModheni, chekutanga tinoshandura tafura kuita "smart" keyboard shortcut Ctrl+T uye upe zita rehushamwari pane tab Constructor (Dhizaini). Iri idanho rinodiwa.

Ipapo iwe unogona kushandisa chero yeiyi nzira nhatu dzekusarudza kubva:

• Dhinda bhatani Wedzera kuMuenzaniso (Wedzera kune Data Model) Tab powerpivot Tab Home (Musha).
  
• Kusarudza zvikwata Pinza – PivotTable (Isa - Pivot Tafura) wobatidza bhokisi rekutarisa Wedzera iyi data kuData Model (Wedzera iyi data kune Data Model). Muchiitiko ichi, maererano nedata rakaiswa muModel, tafura yepivot zvakare inovakwa pakarepo.
  
• Pa Advanced tabo Data (Zuva) tinya pane bhatani Kubva Tafura/Range (Kubva Tafura/Range)kurodha tafura yedu muPower Query edhita. Iyi nzira ndiyo yakareba, asi, kana uchida, pano iwe unogona kuita yekuwedzera data kuchenesa, kugadzirisa uye marudzi ese eshanduko, umo Power Query yakasimba kwazvo.

  Ipapo iyo combed data inoiswa kuModeri nemurairo Imba - Vhara uye Rodha - Vhara uye Rodha mukati... (Kumba - Vhara & Rodha - Vhara & Rodha ku…). Muhwindo rinovhurwa, sarudza sarudzo Ingogadzira chinongedzo (Gadzira kubatana chete) uye, zvinonyanya kukosha, isa tiki Wedzera iyi data kuData Model (Wedzera iyi data kune Data Model).
  

Isu tinovaka chidimbu cheData Model

Kuvaka pfupiso yeData Model, unogona kushandisa chero nzira nhatu:

• Press bhatani pfupiso tafura (Pivot Table) muPower Pivot hwindo.
• Sarudza mirairo muExcel Pinza – PivotTable uye chinja kune mode Shandisa mhando yedata yebhuku rino (Isa — Pivot Table — Shandisa iri bhuku rebasa reData Model).
• Kusarudza zvikwata Pinza – PivotTable (Isa - Pivot Tafura) wobatidza bhokisi rekutarisa Wedzera iyi data kuData Model (Wedzera iyi data kune Data Model). Tafura yezvino "smart" ichaiswa muModheni uye tafura yepfupiso ichavakwa yeModheru yese.

Zvino zvatafunga nzira yekurodha data muData Model uye kuvaka pfupiso pairi, ngationgororei mabhenefiti uye zvakanakira izvo izvi zvinotipa.

Benefit 1: Hukama pakati pematafura pasina kushandisa mafomula

Pfupiso yenguva dzose inogona kuvakwa chete uchishandisa data kubva kune imwe sosi tafura. Kana iwe uine akati wandei, semuenzaniso, kutengesa, rondedzero yemitengo, dhairekitori revatengi, rejista yezvibvumirano, nezvimwe, ipapo unozotanga watora data kubva kumatafura ese kuita rimwe uchishandisa mabasa akadai seVLOOKUP. (VLOOKUP), INDEX (INDEX), ZVAKAWANDA AKAFUMURWA (MATCH), SUMMESLIMN (SUMIFS) nezvimwe zvakadaro. Izvi zvakareba, zvinonetesa uye zvinotyaira Excel yako mu "pfungwa" ine huwandu hukuru hwe data.

Munyaya yepfupiso yeData Model, zvese zviri nyore. Zvakakwana kumisikidza hukama pakati pematafura kamwe muPower Pivot hwindo - uye zvaitwa. Kuti uite izvi, pane tab powerpivot dzvanya bhatani utariri (Manage) uyezve pahwindo rinoonekwa - bhatani Sravana Sameeralu Serial XNUMXth Chart View (Diagram View). Izvo zvinosara kudhonza zvakajairika (kiyi) mazita emakoramu (minda) pakati pematafura kugadzira zvinongedzo:

Mushure meizvozvo, muchidimbu cheData Model, unogona kukanda munzvimbo yepfupiso (mitsara, makoramu, mafirita, kukosha) chero minda kubva kune chero matafura ane hukama - zvese zvichabatanidzwa uye kuverengerwa otomatiki:

Batsiro 2: Verenga maitiro akasiyana

Tafura yepivot yenguva dzose inotipa mukana wekusarudza rimwe remabasa akawanda ekuverenga akavakwa-mukati: sum, avhareji, kuverenga, hushoma, hurefu, zvichingodaro. nhamba yakasiyana (isina kudzokorora kukosha). Nekubatsira kwayo, semuenzaniso, unogona kuverenga nyore nhamba yezvinhu zvakasiyana-siyana zvezvinhu (range) zvatinotengesa muguta rimwe nerimwe.

Tinya-kurudyi pamunda - command Value field options uye pa tab mashandiro Sarudza Nhamba yezvinhu zvakasiyana (Kuwanda kwakasiyana):

Benefit 3: Tsika DAX Formulas

Dzimwe nguva iwe unofanirwa kuita akasiyana ekuwedzera kuverenga mumatafura epivot. Muchidimbu chenguva dzose, izvi zvinoitwa uchishandisa minda yakaverengwa uye zvinhu, nepo muchidimbu chemuenzaniso wedata inoshandisa zviyero mumutauro unokosha weDAX (DAX = Data Analysis Expressions).

Kuti ugadzire chiyero, sarudza pane tab powerpivot murayiro Matanho - Gadzira Chiyero (Matanho - Chiyero chitsva) kana kungobaya-kurudyi patafura muPivot Minda runyorwa uye sarudza Wedzera chiyero (Wedzera chiyero) mumenu yezvinyorwa:

Muhwindo rinovhurwa, isa:

• Tafura zitauko chiyero chakasikwa chichachengetwa.
• Chiyeresa zita - chero zita raunonzwisisa kumunda mutsva.
• tsananguro – zvokusarudza.
• fomura - chinhu chinonyanya kukosha, nekuti pano isu tinopinda nemaoko, kana kudzvanya bhatani fx uye sarudza basa reDAX kubva pane rondedzero, iyo inofanirwa kuverenga mhedzisiro kana isu tokanda chiyero chedu munzvimbo yeValues.
• Muchikamu chezasi chehwindo, iwe unogona pakarepo kuseta iyo nhamba fomati yechiyero mune iyo runyorwa chikamu.
  

Mutauro weDAX hausi nyore kunzwisisa nekuti haushande nehunhu hwemunhu, asi nemakoramu akazara nematafura, kureva kuti inoda kugadziridzwa kwekufunga mushure memhando dzeExcel formula. Zvisinei, zvakakosha, nokuti simba rekugona kwayo mukugadzirisa huwandu hwe data rakaoma kudarika.

Benefits 4: Tsika minda hierarchies

Kazhinji, kana uchigadzira mishumo yakajairwa, unofanirwa kukanda misanganiswa yakafanana yeminda mumatafura epivot mukutevedzana kwakapihwa, semuenzaniso. Gore-Kota-Mwedzi-Zuva, kana Category-Chigadzirwa, kana Nyika-Guta-Mutengi etc. Muchidimbu cheData Model, dambudziko iri rinogadziriswa nyore nyore nekugadzira yako hierarchies - custom field sets.

Muhwindo reSimba Pivot, chinja kune machati mode nebhatani Sravana Sameeralu Serial XNUMXth Chart View Tab Home (Kumba - Dhiyagiramu Maonero), sarudza ne Ctrl minda inodiwa uye tinya pairi kurudyi. Menyu yezvinyorwa ichange iine murairo Gadzira Hierarchy (Gadzira hierarchy):

Iyo yakasikwa hierarchy inogona kupihwa zita uye kudhonzerwa mairi negonzo minda inodiwa, kuitira kuti gare gare mune imwe kufamba vanogona kukandwa muchidimbu:

Batsiro 5: Tsika stencils

Kuenderera mberi nepfungwa yendima yapfuura, muchidimbu cheData Model, unogona zvakare kugadzira yako wega seti yezvinhu zvemunda wega wega. Semuenzaniso, kubva pane yese runyorwa rwemaguta, unogona nyore kugadzira seti yeavo chete vari munharaunda yako yebasa. Kana kuunganidza vatengi vako chete, zvinhu zvako, nezvimwe mune yakakosha seti.

Kuti uite izvi, pane tab Pivot table analysis mune yekudonhedza pasi Minda, Zvinhu, uye Seti kune mirairo inoenderana (Ongorora - Minda, Items & Sets - Gadzira seti yakavakirwa pamutsara / koramu zvinhu):

Muhwindo rinovhurwa, unogona kusarudza kubvisa, kuwedzera kana kushandura chinzvimbo chechero zvinhu uye chengetedza mhedzisiro yakatarwa pasi pezita idzva:

Maseti ese akagadzirwa acharatidzwa muPivotTable Fields pani mune imwe folda yakaparadzana, kubva kwaanogona kudhonzerwa zvakasununguka kumitsara nemakoramu nzvimbo dzepivotTable itsva:

Batsiro 6: Unosarudza Viga Matafura neMakoramu

Kunyangwe ichi chiri chidiki, asi chinonakidza kwazvo mune zvimwe zviitiko. Nekudzvanya-kurudyi pazita remunda kana patafura tebhu muPower Pivot hwindo, unogona kusarudza rairo Viga kubva kune Client Toolkit (Viga kubva kune Client Tools):

Iyo yakavanzika column kana tafura ichanyangarika kubva paPivotTable Field List pane. Izvo zvakanyanya nyore kana iwe uchida kuvanza kubva kune mushandisi mamwe makoramu ebetsero (semuenzaniso, akaverengerwa kana makoramu ane akakosha kukosha kwekugadzira hukama) kana kunyange matafura ese.

Benefit 7. Advanced drill-down

Kana iwe ukadzvanya kaviri pane chero sero munzvimbo yekukosha mune yenguva dzose pivot tafura, ipapo Excel inoratidzira pane imwe pepa kopi yekwakabva data fragment iyo yakabatanidzwa mukuverenga kwesero iri. Ichi chinhu chinobatsira, chinonzi Drill-down (mune vanowanzoti "kukundikana").

Muchidimbu cheData Model, ichi chishandiso chinoshanda chinoshanda zvakapusa. Nekumira pane chero sero ine mhedzisiro inotifarira, unogona kudzvanya pachiratidzo negirazi rinokudza rinobuda padivi payo (inonzi Express Trends) wobva wasarudza chero munda waunofarira mune chero tafura ine hukama:

Mushure meizvozvo, kukosha kwazvino (Model = Explorer) kuchapinda munzvimbo yekusefa, uye pfupiso ichavakwa nemahofisi:

Ehe, maitiro akadai anogona kudzokororwa kakawanda, achiramba achiongorora mune yako data munzira yauri kufarira.

Batsiro 8: Shandura Pivot kuita Cube Mabasa

Kana ukasarudza chero sero muchidimbu cheData Model wobva wasarudza pane tab Pivot table analysis murayiro OLAP Zvishandiso - Shandura kuFormulas (Ongorora - OLAP Zvishandiso - Shandura kuita mafomula), ipapo pfupiso yacho yese inozoshandurwa otomatiki kuita mafomula. Ikozvino iwo munda wakakoshera mumutsara-column nzvimbo uye mhedzisiro munzvimbo yekukosha ichatorwa kubva kuData Model uchishandisa akakosha cube mabasa: CUBEVALUE uye CUBEMEMBER:

Nehunyanzvi, izvi zvinoreva kuti ikozvino hatisi kubata nechidimbu, asi nemaseru akati wandei ane mafomula, kureva kuti tinokwanisa kuita chero shanduko neshumo yedu isingawanikwe muchidimbu, semuenzaniso, isa mitsetse mitsva kana makoramu pakati. yemushumo, ita chero mamwe maverengero mukati mepfupiso, varonge nenzira ipi yaunoda, nezvimwe.

Panguva imwecheteyo, iyo yekubatanidza neyekunobva data, hongu, inoramba iripo uye mune ramangwana aya mafomula achagadziridzwa kana masosi achinja. Runako rwacho!

• Ronga-chokwadi ongororo mutafura yepivot ine Power Pivot uye Power Query
• Pivot tafura ine multiline musoro
• Gadzira dhatabhesi muExcel uchishandisa Power Pivot