Feydeyên Pivot ji hêla Modela Daneyê ve

Dema ku di Excel-ê de tabloyek pivot ava dikin, di qutiya diyalogê ya yekem de, ku ji me tê xwestin ku rêza destpêkê saz bikin û cîhek hilbijêrin ku têxin tabloya pivot, li jêr qutiyek kontrolê ya nediyar lê pir girîng heye - Vê daneyê li Modela Daneyê zêde bikin (Vê daneyê zêde bikin Modela Daneyê) û, hinekî bilindtir, guhêrbar Modela daneyên vê pirtûkê bikar bînin (Modela Daneyên vê pirtûkê bikar bînin):

Mixabin, gelek bikarhênerên ku ji demek dirêj ve bi tabloyên pivot nas in û di xebata xwe de bi serfirazî wan bikar tînin carinan bi rastî wateya van vebijarkan fam nakin û qet wan bikar naynin. Û bêwate. Beriya her tiştî, çêkirina tabloyek pivot ji bo Modela Daneyê çend avantajên pir girîng li gorî tabloya pivot a klasîk a Excel dide me.

Lêbelê, berî ku van "bunan" ji nêz ve binirxînin, em pêşî fam bikin ka, bi rastî, ev Modela Daneyê çi ye?

Modela Daneyê çi ye

Modela Daneyên (bi kurteya MD an DM = Modela Daneyê) deverek taybetî ye di hundurê pelek Excel de ku hûn dikarin daneya tabloyê hilînin - yek an çend tabloyên ku, ger bixwazin, bi hevûdu ve girêdayî ne. Bi rastî, ev databasek piçûk e (kuba OLAP) ku di hundurê pirtûkek xebatê ya Excel-ê de hatî bicîh kirin. Li gorî hilanîna klasîk a daneyan di forma tabloyên birêkûpêk (an jîr) yên li ser pelên Excel-ê bixwe de, Modela Daneyê çend avantajên girîng hene:

• Tabloyên dikarin heta 2 milyar xetên, û pelek Excel dikare ji 1 mîlyonî piçekî zêdetir bi cih bike.
• Tevî mezinahiya giyanî, pêvajokirina tabloyên weha (parzkirin, veqetandin, hesabên li ser wan, kurteya avahiyê, hwd.) tê kirin. pir zû Ji Excel bixwe pir zûtir.
• Bi daneyên di Modelê de, hûn dikarin hesabên zêde (heke bixwazin, pir tevlihev) bikar bînin ava-li zimanê DAX.
  
• Hemî agahdariya ku di Modela Daneyê de hatî barkirin pir e bi xurtî tê pêçan bi karanîna arşîvanek çêkirî ya taybetî û bi hûrgulî mezinahiya pelê Excel-ê orjînal zêde dike.
  

Model ji hêla pêvekek taybetî ya ku di Microsoft Excel-ê de hatî çêkirin tê rêvebirin û hesibandin - powerpivotli ser ku min berê nivîsandiye. Ji bo çalakkirina wê, li ser tabê pêşvebirinê klîk pêvekên COM (Pêşdebir - Pêvekên COM) û qutiya guncan kontrol bikin:

Ger tab pêşvebirinê (pêşdebir)hûn nikarin wê li ser rîbonê bibînin, hûn dikarin wê bi navgîniyê vekin Pelê - Vebijêrk - Sazkirina Ribbon (Pel - Vebijêrk - Ribbon xweş bikin). Ger di pencereya ku li jor hatî xuyang kirin de di navnîşa pêvekên COM de Power Pivot tune be, wê hingê ew di guhertoya we ya Microsoft Office de tune ye 🙁

Li ser tabloya Power Pivot ku xuya dibe, dê bişkokek kesk a mezin a ronahiyê hebe Serekî (Rêvebirin), li ser bikirtînin ku dê paceya Power Pivot li ser Excel veke, ku em ê naveroka Modela Daneyên pirtûka heyî bibînin:

Di rê de têbînîyek girîng: pirtûka xebatê ya Excel tenê dikare yek Modelek Daneyê hebe.

Tabloyan di Modela Daneyê de bar bikin

Ji bo barkirina daneyan di Modelê de, pêşî em tabloyê veguherînin kurtebirra klavyeyê ya dînamîkî ya "aqilmend". Ctrl+T û li ser tabê navekî dostane bidin Kêrker (Mînakkirin). Ev pêngavek pêwîst e.

Wê hingê hûn dikarin yek ji sê awayan bikar bînin ku ji wan hilbijêrin:

• Bişkojkê bikişînin Li Modelê zêde bike (Li Modela Daneyê zêde bike) tab powerpivot tab Xane (Xane).
  
• Hilbijartina tîmên Têxe - PivotTable (Têxe - Tabloya Pivot) û qutiya kontrolê vekin Vê daneyê li Modela Daneyê zêde bikin (Vê daneyê li Modela Daneyê zêde bike). Di vê rewşê de, li gorî daneyên ku di Modelê de hatine barkirin, tabloyek pivot jî yekser tê çêkirin.
  
• Li ser tabloya Pêşkeftî Jimare (Rojek) pêl bişkoka bike Ji Tablo/Range (Ji sifrê/Range)ku tabloya me di edîtorê Power Query de bar bike. Ev rê herî dirêj e, lê heke bixwaze, li vir hûn dikarin paqijkirina daneya zêde, guherandin û her cûre veguherînan bikin, ku tê de Power Query pir bi hêz e.

  Dûv re daneyên komkirî bi fermanê li Modelê têne barkirin Mal - Girtin û Barkirin - Girtin û Li… (Mal - Girtin & Barkirin - Girtin & Barkirin ...). Di pencereya ku vedibe, vebijarkê hilbijêrin Tenê pêwendiyek çêbikin (Tenê pêwendiyê çêbikin) û, ya herî girîng, tikandin Vê daneyê li Modela Daneyê zêde bikin (Vê daneyê li Modela Daneyê zêde bike).
  

Em kurteya Modela Daneyê ava dikin

Ji bo avakirina modelek daneya kurt, hûn dikarin yek ji sê rêbazan bikar bînin:

• Bişkojka çapê tabloya kurtayî (Tabloya Pivot) di pencereya Power Pivot de.
• Fermanên di Excel de hilbijêrin Têxe - PivotTable û veguherîne modê Modela daneyên vê pirtûkê bikar bînin (Têxe - Tabloya Pivot - Modela Daneyên vê pirtûkê bikar bînin).
• Hilbijartina tîmên Têxe - PivotTable (Têxe - Tabloya Pivot) û qutiya kontrolê vekin Vê daneyê li Modela Daneyê zêde bikin (Vê daneyê li Modela Daneyê zêde bike). Tabloya "aqilmend" a heyî dê di Modelê de were barkirin û tabloyek kurt dê ji bo tevahî Modelê were çêkirin.

Naha ku me fêhm kir ku meriv çawa daneyan di Modela Daneyê de bar dike û li ser wê kurteyek ava dike, bila em feyde û feydeyên ku ev dide me vekolin.

Feyde 1: Têkiliyên di navbera tabloyan de bêyî karanîna formula

Kurteyek birêkûpêk tenê bi karanîna daneyên ji tabloyek çavkaniyek dikare were çêkirin. Ger çend ji wan hebin, wek nimûne, firotan, navnîşa bihayê, pelrêça xerîdar, qeydkirina peymanan, hwd., wê hingê hûn ê pêşî bi karanîna fonksiyonên wekî VLOOKUP daneyên ji hemî tabloyan berhev bikin yek. (VLOOKUP), INDEX (NAVEROK), ZÊDETIR DERXISTIN (WEKWÎ), SUMMESLIMN (SUMIFS) û yên wekî. Ev dirêj, bêzar e û Excel-a we bi hejmareke mezin a daneyan re dixe nav "ramanek".

Di mijara kurteya Modela Daneyê de, her tişt pir hêsan e. Bes e ku meriv di pencereya Power Pivot de carekê têkiliyên di navbera tabloyan de saz bike - û ew pêk tê. Ji bo vê yekê, li ser tabê powerpivot bişkojk bike Serekî (Rêvebirin) û paşê di pencereya ku xuya dike de - bişkojk Dîtina nexşeyê (Nêrîna Diagramê). Dimîne ku navên stûnên (qadan) yên hevpar (kilît) di navbera tabloyan de bikişîne da ku girêdan biafirîne:

Piştî wê, di kurteya Modela Daneyê de, hûn dikarin ji her tabloyên têkildar her qadan bavêjin qada kurte (rêz, stûn, parzûn, nirx) - her tişt dê bixweber were girêdan û hesibandin:

Feyde 2: Nirxên bêhempa bijmêre

Tabloya pivot a birêkûpêk fersendê dide me ku em yek ji çend fonksiyonên hesabkirinê yên çêkirî hilbijêrin: kombûn, navîn, hejmartin, hindiktirîn, herî zêde, hwd. hejmara yekta (nirxên ne-dubare). Bi alîkariya wê, bo nimûne, hûn dikarin bi hêsanî hejmara tiştên yekta yên kelûpelan (range) ku em li her bajarî difroşin bijmêrin.

Li ser zeviyê bikirtînin - ferman Vebijarkên qada nirxê û li ser tabê Operation Helbijartin Hejmara hêmanên cuda (Hejmarek cuda):

Feyde 3: Formulên DAX yên Xweser

Carinan pêdivî ye ku hûn di tabloyên pivot de hesabên cihêreng ên din jî bikin. Di kurteyên birêkûpêk de, ev bi karanîna zevî û tiştên hesabkirî tê kirin, dema ku kurteya modela daneyê tedbîrên bi zimanek taybetî DAX bikar tîne (DAX = Daxuyanên Analîza Daneyê).

Ji bo afirandina pîvanek, li ser tabê hilbijêrin powerpivot Ferman Pîvan - Pîvan biafirînin (Tedbîr - Pîvana nû) an jî tenê li ser tabloya di navnîşa Qadên Pivot de rast-klîk bikin û hilbijêrin Pîvan zêde bikin (Pîvan zêde bike) di menuya çarçoveyê de:

Di pencereya ku vedibe de, saz bikin:

• Navê maseyêcihê ku pîvana hatî çêkirin dê were hilanîn.
• Navê pîvanê - her navek ku hûn ji bo qada nû fam dikin.
• Terîf - vebijarkî.
• Formîl - Ya herî girîng, ji ​​ber ku li vir em bi destan têkevin, an jî li ser bişkojê bitikînin fx û fonksiyonek DAX-ê ji navnîşê hilbijêrin, ku dema ku em dûv re pîvana xwe bavêjin qada Nirxê divê encamê hesab bike.
• Di beşa jêrîn a pencereyê de, hûn dikarin tavilê formata hejmarê ji bo pîvana di navnîşê de destnîşan bikin Liq.
  

Zimanê DAX her gav ne hêsan e ku were fam kirin ji ber ku ne bi nirxên takekesî, lê bi tevahî stûn û tabloyan re dixebite, ango piştî formûlên Excel-ê yên klasîk hin nûavakirina ramanê hewce dike. Lêbelê, ew hêja ye, ji ber ku hêza kapasîteyên wê di hilberandina mîqdarên mezin ên daneyê de dijwar e ku meriv zêde binirxîne.

Feyde 4: Hiyerarşiyên qada xwerû

Bi gelemperî, dema ku raporên standard diafirînin, divê hûn di rêzek diyarkirî de heman berhevokên zeviyan bavêjin nav tabloyên pivot, mînakî Sal-Çar meh-Roj, an Kategorî-Berhem, an Welat-Bajar-Xerîdar hwd. Di kurteya Modela Daneyê de, ev pirsgirêk bi çêkirina xweya xwe bi hêsanî çareser dibe hiyerarşî - setên zeviyên xwerû.

Di pencereya Power Pivot de, bi bişkojkê veguherîne moda nexşeyê Dîtina nexşeyê tab Xane (Mal - Nîşana Diagramê), hilbijêre bi Ctrl zeviyên xwestî û li ser wan rast-klîk bikin. Menûya çarçoveyê dê fermanê bigire Hiyerarşiyê ava bikin (Hiyerarşiyê çêbikin):

Hiyerarşiya hatî afirandin dikare were binav kirin û qadên pêwîst bi mişkê ve tê kaş kirin, da ku paşê di yek tevgerê de ew werin avêtin nav kurteyê:

Feyde 5: Şablonên xwerû

Berdewamkirina ramana paragrafa berê, di kurteya Modela Daneyê de, hûn dikarin ji bo her zeviyê komek hêmanên xwe jî biafirînin. Mînakî, ji tevahiya navnîşa bajaran, hûn dikarin bi hêsanî komek tenê yên ku di qada berpirsiyariya we de ne çêbikin. An jî tenê xerîdarên xwe, tiştên xwe, hwd di nav komek taybetî de berhev bikin.

Ji bo vê yekê, li ser tabê Analîza tabloya pivot di lîsteya drop down Qadan, Tişt û Set fermanên têkildar hene (Analîz - Zeviyên, Items & Set - Li ser bingeha hêmanên rêz / stûnê komek biafirînin):

Di pencereya ku vedibe, hûn dikarin bi bijartî pozîsyona her hêmanan jê bikin, lê zêde bikin an biguhezînin û berhevoka encam di bin navek nû de hilînin:

Hemî komên hatine afirandin dê di panela Qadên PivotTable de di peldankek cihêreng de werin xuyang kirin, ji wir ew dikarin bi serbestî berbi rêz û stûnên her PivotTable-ya nû ve werin kişandin:

Feyde 6: Bi Hilbijartî Tablo û Stûnan Veşêrin

Her çend ev di hin rewşan de avantajek piçûk, lê pir xweş e. Bi klîkkirina rastê li ser navê zeviyê an li ser tabloya sifrê ya di pencereya Power Pivot de, hûn dikarin fermanê hilbijêrin Veşêre ji Client Toolkit (Ji Amûrên Xerîdar veşêre):

Stûn an tabloya veşartî dê ji pencereya Lîsteya Qada PivotTable winda bibe. Heke hûn hewce ne ku hin stûnên alîkar ji bikarhêner veşêrin (mînakî, jimartin an stûnên bi nirxên sereke yên ji bo afirandina têkiliyan) an jî tabloyên tevahî, pir hêsan e.

Feyde 7. Drill-down pêşketî

If you double-click on any cell in the value area in a regular pivot table, then Excel displays on a separate sheet a copy of the source data fragment that was involved in the calculation of this cell. This is a very handy thing, officially called Drill-down (in they usually say “fail”).

Di kurteya Modela Daneyê de, ev amûrek bikêr bi hûrgulî dixebite. Bi rawestana li ser hucreyek ku encama ku me eleqedar dike, hûn dikarin li ser îkonê bi şûşeyek mezinker a ku li kêleka wê derdikeve bikirtînin (ku jê re tê gotin Trendên Express) û dûv re her qada ku hûn jê re eleqedar in di tabloya têkildar de hilbijêrin:

Piştî wê, nirxa heyî (Model = Explorer) dê bikeve qada parzûnê, û kurte dê ji hêla nivîsgehan ve were çêkirin:

Bê guman, pêvajoyek wusa dikare gelek caran were dubare kirin, bi domdarî li ser riya ku hûn jê re eleqedar in di nav daneyên xwe de hûr bibin.

Feyde 8: Pivot veguherînin Fonksiyonên Kubeyê

Ger hûn ji bo Modela Daneyê di kurtenivîsê de şaneyek hilbijêrin û dûv re li ser tabê hilbijêrin Analîza tabloya pivot Ferman Amûrên OLAP - Veguhezînin Formulan (Analîz bikin - Amûrên OLAP - Veguhezînin formulan), wê hingê dê tevahî kurtasî bixweber veguhezînin formulan. Naha nirxên zeviyê li qada rêz-stûnê û encamên li qada nirxê dê ji Modela Daneyê bi karanîna fonksiyonên kubayê yên taybetî werin wergirtin: CUBEVALUE û CUBEMEMBER:

Ji hêla teknîkî ve, ev tê vê wateyê ku naha em bi kurtenivîsekê re mijûl dibin, lê bi çend şaneyên bi formul re mijûl dibin, ango em dikarin bi raporta xwe re bi hêsanî veguherînên ku di kurtenivîsê de peyda nabin, bikin, mînakî, rêz an stûnên nû têxin navîn. ya raporê, di hundurê kurtenivîsê de hesabên zêde bikin, bi her awayê ku tê xwestin rêz bikin, hwd.

Di heman demê de, pêwendiya bi daneyên çavkaniyê re, bê guman, dimîne û di pêşerojê de dema ku çavkanî biguhezin dê ev formul bêne nûve kirin. The beauty!

• Analîzkirina plan-rastiyê di tabloyek pivot de bi Power Pivot û Power Query
• Tabloya pivot bi sernavê pirreng
• Bi karanîna Power Pivot di Excel de databasek çêbikin