Rakursa pēc datu modeļa priekšrocības

Veidojot rakurstabulu programmā Excel, pašā pirmajā dialoglodziņā, kurā mums tiek prasīts iestatīt sākotnējo diapazonu un izvēlēties vietu rakurstabulas ievietošanai, zemāk ir redzama neuzkrītoša, bet ļoti svarīga izvēles rūtiņa – Pievienojiet šos datus datu modelim (Pievienojiet šos datus uz datu modeli) un, nedaudz augstāk, slēdzis Izmantojiet šīs grāmatas datu modeli (Izmantojiet šīs darbgrāmatas datu modeli):

Diemžēl daudzi lietotāji, kuri jau sen ir iepazinušies ar pivot tabulām un veiksmīgi tās izmanto savā darbā, dažkārt īsti neizprot šo opciju nozīmi un nekad tās neizmanto. Un velti. Galu galā datu modeļa rakurstabulas izveide sniedz mums vairākas ļoti svarīgas priekšrocības salīdzinājumā ar klasisko Excel rakurstabulu.

Tomēr, pirms aplūkot šīs “bulciņas” tuvplānā, vispirms sapratīsim, kas patiesībā ir šis datu modelis?

Kas ir datu modelis

Datu modelis (saīsināti kā MD vai DM = datu modelis) ir īpašs apgabals Excel failā, kurā varat glabāt tabulas datus — vienu vai vairākas tabulas, kas, ja vēlaties, ir saistītas viena ar otru. Faktiski šī ir neliela datu bāze (OLAP kubs), kas ir iegulta Excel darbgrāmatā. Salīdzinot ar klasisko datu glabāšanu parastu (vai viedo) tabulu veidā pašā Excel lapās, datu modelim ir vairākas būtiskas priekšrocības:

• Tabulas var būt līdz 2 miljardi rindu, un Excel lapā var ietilpt nedaudz vairāk par 1 miljonu.
• Neskatoties uz gigantisko izmēru, šādu tabulu apstrāde (filtrēšana, šķirošana, aprēķini uz tām, ēkas kopsavilkums utt.) tiek veikta. ļoti ātri Daudz ātrāk nekā pati Excel.
• Izmantojot modelī esošos datus, varat veikt papildu (ja vēlaties, ļoti sarežģītus) aprēķinus, izmantojot iebūvēta DAX valoda.
  
• Visa informācija, kas tiek ielādēta datu modelī, ir ļoti stipri saspiests izmantojot īpašu iebūvēto arhivētāju un diezgan mēreni palielina sākotnējā Excel faila izmēru.
  

Modeli pārvalda un aprēķina ar īpašu pievienojumprogrammu, kas iebūvēta programmā Microsoft Excel - powerpivotpar ko jau rakstīju. Lai to iespējotu, cilnē attīstītājs klikšķis COM pievienojumprogrammas (Izstrādātājs — COM pievienojumprogrammas) un atzīmējiet atbilstošo lodziņu:

Ja cilnes attīstītājs (Izstrādātājs)to nevar redzēt uz lentes, to var ieslēgt cauri Fails - Opcijas - Lentes iestatīšana (Fails — opcijas — pielāgot lenti). Ja logā, kas parādīts iepriekš COM pievienojumprogrammu sarakstā, jums nav Power Pivot, tas nav iekļauts jūsu Microsoft Office versijā 🙁

Parādītajā cilnē Power Pivot būs liela gaiši zaļa poga vadība (Pārvaldīt), noklikšķinot uz tā, Excel augšpusē tiks atvērts Power Pivot logs, kurā redzēsim pašreizējās grāmatas datu modeļa saturu:

Svarīga piezīme: Excel darbgrāmatā var būt tikai viens datu modelis.

Ielādējiet tabulas datu modelī

Lai ielādētu datus modelī, vispirms tabulu pārvēršam par dinamisku “viedo” īsinājumtaustiņu Ctrl+T un piešķiriet tai draudzīgu nosaukumu cilnē Konstruktors (Dizains). Šis ir obligāts solis.

Pēc tam varat izvēlēties kādu no trim metodēm:

• Nospiediet pogu Pievienot modelim (Pievienot datu modelim) tab powerpivot tab Sākumlapa (Mājas).
  
• Komandu izvēle Ievietošana — PivotTable (Ievietot — rakurstabula) un ieslēdziet izvēles rūtiņu Pievienojiet šos datus datu modelim (Pievienojiet šos datus datu modelim). Šajā gadījumā saskaņā ar modelī ielādētajiem datiem nekavējoties tiek izveidota arī rakurstabula.
  
• Cilnē Papildu Datums (Datums) noklikšķiniet uz pogas No tabulas/diapazona (No tabulas/diapazona)lai ielādētu mūsu tabulu Power Query redaktorā. Šis ceļš ir garākais, taču, ja ir vēlēšanās, šeit var veikt papildus datu tīrīšanu, rediģēšanu un visādas transformācijas, kurās Power Query ir ļoti spēcīgs.

  Pēc tam ķemmētie dati tiek augšupielādēti modelī ar komandu Sākums — Aizvērt un ielādēt — Aizvērt un ielādēt… (Sākums — Aizvērt&Ielādēt — Aizvērt&Ielādēt...). Atvērtajā logā atlasiet opciju Vienkārši izveidojiet savienojumu (Izveidojiet tikai savienojumu) un, galvenais, ielieciet ķeksīti Pievienojiet šos datus datu modelim (Pievienojiet šos datus datu modelim).
  

Mēs veidojam datu modeļa kopsavilkumu

Lai izveidotu kopsavilkuma datu modeli, varat izmantot jebkuru no trim pieejām:

• Nospiediet pogu kopsavilkuma tabula (Rakurstabula) Power Pivot logā.
• Programmā Excel atlasiet komandas Ievietošana — PivotTable un pārslēdzieties uz režīmu Izmantojiet šīs grāmatas datu modeli (Ievietot — rakurstabulu — izmantojiet šīs darbgrāmatas datu modeli).
• Komandu izvēle Ievietošana — PivotTable (Ievietot — rakurstabula) un ieslēdziet izvēles rūtiņu Pievienojiet šos datus datu modelim (Pievienojiet šos datus datu modelim). Pašreizējā “viedā” tabula tiks ielādēta modelī un tiks izveidota kopsavilkuma tabula visam modelim.

Tagad, kad esam izdomājuši, kā ielādēt datus datu modelī un izveidot par to kopsavilkumu, izpētīsim priekšrocības un priekšrocības, ko tas mums sniedz.

1. ieguvums: sakarības starp tabulām, neizmantojot formulas

Regulāru kopsavilkumu var izveidot, tikai izmantojot datus no vienas avota tabulas. Ja jums ir vairāki no tiem, piemēram, pārdošana, cenrādis, klientu katalogs, līgumu reģistrs utt., tad vispirms jums būs jāapkopo dati no visām tabulām vienā, izmantojot tādas funkcijas kā VLOOKUP (MEKLĒT), INDEKSS (INDEX), VAIRĀK IZSLĒGTS (MATCH), SUMMESLIMN (SUMIFS) un tamlīdzīgi. Tas ir garš, nogurdinošs un iedzen jūsu Excel "domā" ar lielu datu apjomu.

Datu modeļa kopsavilkuma gadījumā viss ir daudz vienkāršāk. Pietiek vienreiz Power Pivot logā iestatīt attiecības starp tabulām – un tas ir darīts. Lai to izdarītu, cilnē powerpivot nospiediet pogu vadība (Pārvaldīt) un pēc tam parādītajā logā – poga Diagrammas skats (Diagrammas skats). Atliek vilkt parastos (atslēgas) kolonnu nosaukumus (laukus) starp tabulām, lai izveidotu saites:

Pēc tam datu modeļa kopsavilkumā kopsavilkuma apgabalā (rindas, kolonnas, filtri, vērtības) varat iemest jebkurus laukus no jebkurām saistītām tabulām – viss tiks automātiski saistīts un aprēķināts:

2. ieguvums: saskaitiet unikālas vērtības

Parasta rakurstabula dod mums iespēju izvēlēties vienu no vairākām iebūvētām aprēķina funkcijām: summa, vidējais, skaits, minimums, maksimums utt. Datu modeļa kopsavilkumā šim standarta sarakstam ir pievienota ļoti noderīga funkcija, lai uzskaitītu unikālo (neatkārtojamo vērtību) skaits. Ar tās palīdzību, piemēram, jūs varat viegli saskaitīt unikālo preču vienību skaitu (sortimentu), ko mēs pārdodam katrā pilsētā.

Ar peles labo pogu noklikšķiniet uz lauka - komanda Vērtību lauka opcijas un cilnē Darbība izvēlēties Dažādu elementu skaits (Atšķirīgs skaits):

3. ieguvums: pielāgotas DAX formulas

Dažkārt ir jāveic dažādi papildu aprēķini rakurstabulās. Parastos kopsavilkos tas tiek darīts, izmantojot aprēķinātos laukus un objektus, savukārt datu modeļa kopsavilkumā tiek izmantoti mēri īpašā DAX valodā (DAX = Data Analysis Expressions).

Lai izveidotu mēru, cilnē atlasiet powerpivot Komanda Pasākumi — izveidojiet pasākumu (Pasākumi — jauns pasākums) vai vienkārši ar peles labo pogu noklikšķiniet uz tabulas Pivot Fields sarakstā un atlasiet Pievienojiet mēru (Pievienot mēru) konteksta izvēlnē:

Atvērtajā logā iestatiet:

• Tabulas nosaukumskur tiks saglabāts izveidotais mērs.
• Pasākuma nosaukums – jebkurš jaunajam laukam saprotams nosaukums.
• Apraksts - neobligāti.
• Formula – pats svarīgākais, jo šeit mēs vai nu ievadām manuāli, vai noklikšķinām uz pogas fx un sarakstā atlasiet DAX funkciju, kurai ir jāaprēķina rezultāts, kad mēs iemetīsim savu mērījumu vērtību apgabalā.
• Loga apakšējā daļā varat uzreiz iestatīt mēra skaitļa formātu sarakstā Kategorija.
  

DAX valoda ne vienmēr ir viegli saprotama, jo tā darbojas nevis ar atsevišķām vērtībām, bet ar veselām kolonnām un tabulām, ti, prasa zināmu domāšanas pārstrukturēšanu pēc klasiskajām Excel formulām. Tomēr tas ir tā vērts, jo ir grūti pārvērtēt tā spēju apstrādē lielu datu apjomu.

4. ieguvums: pielāgotas lauku hierarhijas

Bieži vien, veidojot standarta atskaites, rakurstabulās noteiktā secībā ir jāiemet vienas un tās pašas lauku kombinācijas, piemēram, Gads-ceturksnis-mēnesis-diena, vai Kategorija-Produkts, vai Valsts-pilsēta-klients utt. Datu modeļa kopsavilkumā šo problēmu var viegli atrisināt, izveidojot savu hierarhijas — pielāgotas lauka komplekti.

Power Pivot logā ar pogu pārslēdzieties uz diagrammas režīmu Diagrammas skats tab Sākumlapa (Sākums — diagrammas skats), atlasiet ar Ctrl vajadzīgos laukus un ar peles labo pogu noklikšķiniet uz tiem. Konteksta izvēlnē būs komanda Izveidojiet hierarhiju (Izveidot hierarhiju):

Izveidoto hierarhiju var pārdēvēt un ar peli ievilkt tajā nepieciešamos laukus, lai vēlāk ar vienu kustību tos varētu iemest kopsavilkumā:

5. ieguvums: pielāgoti trafareti

Turpinot iepriekšējās rindkopas domu, datu modeļa kopsavilkumā varat izveidot arī savas elementu kopas katram laukam. Piemēram, no visa pilsētu saraksta varat viegli izveidot komplektu tikai no tām, kas atrodas jūsu atbildības jomā. Vai arī savāc īpašā komplektā tikai savus klientus, savas preces utt.

Lai to izdarītu, cilnē Rakurstabulas analīze nolaižamajā sarakstā Lauki, vienumi un komplekti ir atbilstošas ​​komandas (Analizēt — Fields, Ivienumi un komplekti — izveidojiet kopu, pamatojoties uz rindu/kolonnu vienumiem):

Atvērtajā logā varat selektīvi noņemt, pievienot vai mainīt jebkuru elementu pozīciju un saglabāt iegūto kopu ar jaunu nosaukumu:

Visas izveidotās kopas tiks parādītas panelī Rakurstabulas lauki atsevišķā mapē, no kurienes tās varēs brīvi vilkt uz jebkuras jaunas rakurstabulas rindu un kolonnu apgabaliem:

6. ieguvums: selektīvi paslēpiet tabulas un kolonnas

Lai gan tā ir neliela, bet dažos gadījumos ļoti patīkama priekšrocība. Power Pivot logā ar peles labo pogu noklikšķinot uz lauka nosaukuma vai tabulas cilnes, varat atlasīt komandu Paslēpt no Client Toolkit (Paslēpt no klientu rīkiem):

Slēptā kolonna vai tabula pazudīs no rakurstabulas lauku saraksta rūts. Tas ir ļoti ērti, ja jums ir jāslēpj no lietotāja dažas papildu kolonnas (piemēram, aprēķinātās vai kolonnas ar galvenajām vērtībām attiecību veidošanai) vai pat veselas tabulas.

Ieguvums 7. Uzlabota urbšana

Ja parastā rakurstabulā veicat dubultklikšķi uz jebkuras šūnas vērtību apgabalā, programma Excel atsevišķā lapā parāda avota datu fragmenta kopiju, kas tika izmantota šīs šūnas aprēķināšanā. Šī ir ļoti ērta lieta, ko oficiāli sauc par Drill-down (parasti viņi saka “neizdevās”).

Datu modeļa kopsavilkumā šis parocīgais rīks darbojas smalkāk. Stāvot uz jebkuras šūnas ar rezultātu, kas mūs interesē, varat noklikšķināt uz ikonas ar palielināmo stiklu, kas parādās blakus tai (to sauc Izteikt tendences) un pēc tam jebkurā saistītā tabulā atlasiet jebkuru lauku, kas jūs interesē:

Pēc tam pašreizējā vērtība (Modelis = Explorer) nonāks filtra apgabalā, un kopsavilkumu veidos biroji:

Protams, šādu procedūru var atkārtot daudzas reizes, konsekventi iedziļinoties savos datos sev interesējošā virzienā.

8. ieguvums: pārveidojiet Pivot par kuba funkcijām

Ja datu modeļa kopsavilkumā atlasāt jebkuru šūnu un pēc tam atlasiet cilnē Rakurstabulas analīze Komanda OLAP rīki - konvertēt uz formulām (Analizēt — OLAP rīki — konvertēt uz formulām), tad viss kopsavilkums tiks automātiski pārveidots formulās. Tagad lauka vērtības rindu kolonnas apgabalā un rezultāti vērtību apgabalā tiks izgūti no datu modeļa, izmantojot īpašās kuba funkcijas: CUBEVALUE un CUBEMEMBER:

Tehniski tas nozīmē, ka tagad mēs nodarbojamies nevis ar kopsavilkumu, bet ar vairākām šūnām ar formulām, proti, ar savu atskaiti varam viegli veikt jebkādas transformācijas, kas nav pieejamas kopsavilkumā, piemēram, ievietot vidū jaunas rindas vai kolonnas. punktā, kopsavilkumā veikt jebkādus papildu aprēķinus, sakārtot tos vēlamajā veidā utt.

Tajā pašā laikā saikne ar avota datiem, protams, saglabājas un turpmāk šīs formulas tiks aktualizētas, mainoties avotiem. Skaistums!

• Plāna faktu analīze rakurstabulā ar Power Pivot un Power Query
• Rakurstabula ar vairāku rindiņu galveni
• Izveidojiet datu bāzi programmā Excel, izmantojot programmu Power Pivot