Kostir Pivot by Data Model

Þegar þú byggir snúningstöflu í Excel, í fyrsta svarglugganum, þar sem við erum beðin um að stilla upphafssviðið og velja stað til að setja inn snúningstöfluna, er lítt áberandi en mjög mikilvægur gátreitur fyrir neðan – Bættu þessum gögnum við gagnalíkanið (Bættu þessum gögnum við til gagnalíkans) og aðeins hærra, rofann Notaðu gagnalíkan þessarar bókar (Notaðu gagnalíkan þessarar vinnubókar):

Því miður skilja margir notendur sem hafa kannast við snúningstöflur í langan tíma og nota þær með góðum árangri í starfi sínu stundum ekki raunverulega merkingu þessara valkosta og nota þá aldrei. Og til einskis. Þegar allt kemur til alls, að búa til snúningstöflu fyrir gagnalíkanið gefur okkur nokkra mjög mikilvæga kosti samanborið við klassíska Excel snúningstöfluna.

Hins vegar, áður en þessar „bollur“ eru skoðaðar í návígi, skulum við fyrst skilja hvað þetta gagnalíkan er í raun og veru?

Hvað er gagnalíkan

Gagnalíkan (skammstafað sem MD eða DM = Data Model) er sérstakt svæði inni í Excel skrá þar sem þú getur geymt töflugögn - ein eða fleiri töflur tengdar, ef þess er óskað, hver við aðra. Reyndar er þetta lítill gagnagrunnur (OLAP teningur) sem er felldur inn í Excel vinnubók. Í samanburði við klassíska geymslu gagna í formi venjulegra (eða snjallra) taflna á Excel blöðum sjálfum hefur gagnalíkanið nokkra mikilvæga kosti:

• Töflur geta verið allt að 2 milljarðar línur, og Excel blað getur rúmað aðeins meira en 1 milljón.
• Þrátt fyrir risastóra stærð fer fram vinnsla slíkra taflna (síun, flokkun, útreikningar á þeim, byggingaryfirlit o.s.frv.) mjög hratt Miklu hraðari en Excel sjálft.
• Með gögnunum í líkaninu geturðu framkvæmt viðbótarútreikninga (ef þess er óskað, mjög flóknir) með því að nota innbyggt DAX tungumál.
  
• Allar upplýsingar hlaðnar inn í gagnalíkanið eru mjög mjög þjappað með því að nota sérstakan innbyggðan skjalavörð og stækkar í meðallagi stærð upprunalegu Excel-skrárinnar.
  

Líkaninu er stjórnað og reiknað með sérstakri viðbót sem er innbyggð í Microsoft Excel - powerpivotsem ég hef þegar skrifað um. Til að virkja það, á flipanum verktaki smella COM viðbætur (Hönnuði - COM viðbætur) og merktu við viðeigandi reit:

Ef flipar verktaki (hönnuður)þú getur ekki séð það á borði, þú getur kveikt á því í gegn Skrá – Valkostir – Uppsetning borða (Skrá — Valkostir — Sérsníða borði). Ef þú ert ekki með Power Pivot í glugganum sem sýndur er hér að ofan á listanum yfir COM-viðbætur, þá er það ekki innifalið í útgáfunni þinni af Microsoft Office 🙁

Á Power Pivot flipanum sem birtist verður stór ljósgrænn hnappur stjórnun (Stjórna), með því að smella á það opnast Power Pivot glugginn ofan á Excel, þar sem við munum sjá innihald gagnalíkans núverandi bókar:

Mikilvæg athugasemd á leiðinni: Excel vinnubók getur aðeins innihaldið eitt gagnalíkan.

Hlaða töflum inn í gagnalíkanið

Til að hlaða gögnum inn í líkanið breytum við töflunni fyrst í kraftmikla „snjöllu“ flýtilykla Ctrl+T og gefðu því vinalegt nafn á flipanum Framkvæmdaaðili (Hönnun). Þetta er nauðsynlegt skref.

Þá geturðu notað hvaða af þremur aðferðum sem er til að velja úr:

• Ýttu á takkann Bæta við líkan (Bæta við gagnalíkan) flipi powerpivot flipi Heim (Heim).
  
• Að velja lið Setja inn - PivotTable (Setja inn - snúningstafla) og kveiktu á gátreitnum Bættu þessum gögnum við gagnalíkanið (Bæta þessum gögnum við gagnalíkan). Í þessu tilviki, samkvæmt gögnunum sem hlaðið er inn í líkanið, er snúningstafla strax byggð.
  
• Á Advanced flipanum Gögn (Dagsetning) smelltu á hnappinn Frá borði/sviði (Úr töflu/sviði)til að hlaða töflunni okkar inn í Power Query ritilinn. Þessi leið er lengst, en ef þess er óskað, hér geturðu framkvæmt viðbótargagnahreinsun, klippingu og alls kyns umbreytingar, þar sem Power Query er mjög sterk.

  Síðan er greiddum gögnum hlaðið upp á líkanið með skipuninni Heim — Loka og hlaða — Loka og hlaða inn... (Heima — Loka&hlaða — Loka&hlaða til...). Í glugganum sem opnast skaltu velja valkostinn Búðu bara til tengingu (Búa bara til tengingu) og síðast en ekki síst, setja hak Bættu þessum gögnum við gagnalíkanið (Bæta þessum gögnum við gagnalíkan).
  

Við smíðum samantekt á gagnalíkaninu

Til að búa til yfirlitsgagnalíkan geturðu notað hvaða af þremur aðferðum sem er:

• Ýtið á hnappinn yfirlitstöflu (Pivot Tafla) í Power Pivot glugganum.
• Veldu skipanir í Excel Setja inn - PivotTable og skiptu yfir í ham Notaðu gagnalíkan þessarar bókar (Setja inn - snúningstafla - Notaðu gagnalíkan þessarar vinnubókar).
• Að velja lið Setja inn - PivotTable (Setja inn - snúningstafla) og kveiktu á gátreitnum Bættu þessum gögnum við gagnalíkanið (Bæta þessum gögnum við gagnalíkan). Núverandi „snjöll“ tafla verður hlaðin inn í líkanið og yfirlitstafla verður byggð fyrir allt líkanið.

Nú þegar við höfum fundið út hvernig á að hlaða gögnum inn í gagnalíkanið og búa til samantekt á því, skulum við kanna kosti og kosti sem þetta gefur okkur.

Ávinningur 1: Tengsl á milli taflna án þess að nota formúlur

Venjulegt yfirlit er aðeins hægt að búa til með því að nota gögn úr einni upprunatöflu. Ef þú ert með nokkrar af þeim, til dæmis sölu, verðskrá, viðskiptavinaskrá, samningaskrá o.s.frv., þá þarftu fyrst að safna gögnum úr öllum töflum í eina með aðgerðum eins og VLOOKUP (SKRÁNING), VÍSITALA (VÍSITALA), FYRIR MEIRA (MATCH), SUMMESLIMN (SUMIFS) og þess háttar. Þetta er langt, leiðinlegt og rekur Excel þinn í „hugsun“ með miklu magni af gögnum.

Þegar um er að ræða samantekt á gagnalíkaninu er allt miklu einfaldara. Það er nóg að setja upp tengsl á milli borða einu sinni í Power Pivot glugganum – og það er búið. Til að gera þetta, á flipanum powerpivot Ýttu á takkann stjórnun (Stjórna) og svo í glugganum sem birtist – hnappurinn Myndritasýn (Skýringarmynd). Það er eftir að draga algeng (lykla) dálknöfn (reitir) á milli töflur til að búa til tengla:

Eftir það, í samantektinni fyrir gagnalíkanið, geturðu hent inn á yfirlitssvæðið (línur, dálkar, síur, gildi) hvaða reiti sem er úr hvaða töflum sem er – allt verður tengt og reiknað sjálfkrafa:

Ávinningur 2: Teldu einstök gildi

Venjuleg snúningstafla gefur okkur tækifæri til að velja eina af nokkrum innbyggðum reikniaðgerðum: summa, meðaltal, fjölda, lágmark, hámark o.s.frv. Í samantekt gagnalíkans er mjög gagnlegri aðgerð bætt við þennan staðlaða lista til að telja fjöldi einstakra (ekki endurtekin gildi). Með hjálp þess geturðu til dæmis auðveldlega talið fjölda einstaka vara (úrval) sem við seljum í hverri borg.

Hægrismelltu á reitinn – skipun Valkostir gildissviðs og á flipanum Notkun Veldu Fjöldi mismunandi þátta (Aðgreindur fjöldi):

Ávinningur 3: Sérsniðnar DAX formúlur

Stundum þarf að framkvæma ýmsa viðbótarútreikninga í snúningstöflum. Í venjulegum samantektum er þetta gert með reiknuðum reitum og hlutum, en í samantekt gagnalíkana eru mælingar á sérstöku DAX tungumáli (DAX = Data Analysis Expressions).

Til að búa til mælikvarða skaltu velja á flipanum powerpivot Skipun Ráðstafanir - Búðu til mælikvarða (Mælingar — Nýr mælikvarði) eða bara hægrismelltu á töfluna í Pivot Fields listanum og veldu Bæta við mælikvarða (Bæta við mælikvarða) í samhengisvalmyndinni:

Í glugganum sem opnast skaltu stilla:

• Heiti töfluþar sem stofnað mál verður geymt.
• Nafn máls – hvaða nafn sem þú skilur á nýja sviðinu.
• Lýsing - valfrjálst.
• Formúla – það mikilvægasta, því hér slærð við annað hvort inn handvirkt eða smellum á hnappinn fx og veldu DAX fall af listanum, sem ætti að reikna út niðurstöðuna þegar við hendum síðan mælikvarða okkar inn á Values ​​​​svæðið.
• Í neðri hluta gluggans er strax hægt að stilla tölusnið fyrir mælinguna á listanum Flokkur.
  

DAX tungumálið er ekki alltaf auðvelt að skilja vegna þess að það starfar ekki með einstökum gildum, heldur með heilum dálkum og töflum, þ.e. krefst einhverrar endurskipulagningar hugsunar eftir klassískum Excel formúlum. Hins vegar er það þess virði, vegna þess að það er erfitt að ofmeta kraft getu þess til að vinna mikið magn af gögnum.

Ávinningur 4: Sérsniðin sviðsstigveldi

Oft, þegar þú býrð til staðlaðar skýrslur, þarftu að henda sömu samsetningum reita í snúningstöflur í tiltekinni röð, til dæmis Ár-Fjórðungs-Mánaðardagur, eða Flokkur-vara, eða Land-Borg-Viðskiptavinur osfrv. Í samantekt gagnalíkans er þetta vandamál auðveldlega leyst með því að búa til þitt eigið stigveldi — sérsniðin reitasett.

Í Power Pivot glugganum skaltu skipta yfir í kortastillingu með hnappinum Myndritasýn flipi Heim (Heima — Skýringarmynd), veldu með Ctrl reiti sem þú vilt og hægrismelltu á þá. Samhengisvalmyndin mun innihalda skipunina Búðu til stigveldi (Búa til stigveldi):

Hægt er að endurnefna stigveldið sem búið var til og draga inn í það með músinni nauðsynlega reiti, svo að síðar í einni hreyfingu er hægt að henda þeim inn í samantektina:

Ávinningur 5: Sérsniðin stencils

Með því að halda áfram hugmyndinni um fyrri málsgrein, í samantekt gagnalíkansins, geturðu líka búið til þín eigin sett af þáttum fyrir hvern reit. Til dæmis, af öllum listanum yfir borgir, geturðu auðveldlega búið til safn af þeim sem eru á ábyrgðarsvæði þínu. Eða safnaðu aðeins viðskiptavinum þínum, vörum þínum osfrv. í sérstakt sett.

Til að gera þetta, á flipanum Snúningstöflugreining í fellilistanum Reitir, hlutir og sett það eru samsvarandi skipanir (Greining - Fields, Isetningar og setur - Búðu til sett byggt á röð / dálki atriði):

Í glugganum sem opnast geturðu valið fjarlægt, bætt við eða breytt staðsetningu hvaða þátta sem er og vistað settið sem myndast undir nýju nafni:

Öll búin til sett munu birtast á PivotTable Fields spjaldinu í sérstakri möppu, þaðan sem hægt er að draga þau frjálslega yfir í línur og dálkasvæði hvers nýs PivotTable:

Ávinningur 6: Fela töflur og dálka með vali

Þó að þetta sé lítill, en mjög skemmtilegur kostur í sumum tilfellum. Með því að hægrismella á heiti reitsins eða á töfluflipann í Power Pivot glugganum geturðu valið skipunina Fela frá Client Toolkit (Fela frá verkfærum viðskiptavinar):

Falda dálkurinn eða taflan hverfur úr reitalistanum með PivotTable. Það er mjög þægilegt ef þú þarft að fela fyrir notandanum nokkra aukadálka (til dæmis reiknaða eða dálka með lykilgildum til að búa til sambönd) eða jafnvel heilar töflur.

Ávinningur 7. Ítarlegri drill-down

Ef þú tvísmellir á einhvern reit á gildissvæðinu í venjulegri snúningstöflu, þá birtir Excel á sérstakt blaði afrit af frumgagnabrotinu sem tók þátt í útreikningi þessa reits. Þetta er mjög handlaginn hlutur, opinberlega kallaður Drill-down (í þeir segja venjulega „fail“).

Í samantekt gagnalíkans virkar þetta handhæga tól lúmskari. Með því að standa á hvaða reit sem er með niðurstöðuna sem vekur áhuga okkar geturðu smellt á táknið með stækkunargleri sem birtist við hliðina (það kallast Express Trends) og veldu síðan hvaða reit sem þú hefur áhuga á í tengdri töflu:

Eftir það mun núverandi gildi (Model = Explorer) fara inn á síusvæðið og samantektin verður byggð af skrifstofum:

Auðvitað er hægt að endurtaka slíka aðferð mörgum sinnum og kafa stöðugt ofan í gögnin þín í þá átt sem þú hefur áhuga á.

Ávinningur 8: Umbreyttu Pivot í Cube aðgerðir

Ef þú velur einhvern reit í samantektinni fyrir gagnalíkanið og velur síðan á flipanum Snúningstöflugreining Skipun OLAP verkfæri - Umbreyttu í formúlur (Greining - OLAP Tools - Umbreyta í formúlur), þá verður öllu samantektinni sjálfkrafa breytt í formúlur. Nú verða reitgildin á línu-dálkasvæðinu og niðurstöðurnar á gildissvæðinu sóttar úr gagnalíkaninu með því að nota sérstaka teningaaðgerðirnar: CUBEVALUE og CUBEMEMBER:

Tæknilega þýðir þetta að nú erum við ekki að fást við samantekt, heldur nokkrar frumur með formúlum, þ.e. við getum auðveldlega gert allar umbreytingar með skýrslunni okkar sem eru ekki tiltækar í samantektinni, td sett nýjar línur eða dálka inn í miðjuna. skýrslunnar, gera frekari útreikninga inni í samantektinni, raða þeim á þann hátt sem óskað er, o.s.frv.

Á sama tíma helst tengingin við heimildagögnin að sjálfsögðu áfram og í framtíðinni verða þessar formúlur uppfærðar þegar heimildir breytast. Fegurðin!

• Áætlun-staðreynda greining í pivot töflu með Power Pivot og Power Query
• Snúningstafla með marglínuhaus
• Búðu til gagnagrunn í Excel með Power Pivot