Apmācība: rakurstabulas datu analīze programmā Excel 2013, izmantojot datu modeli

Mazāk kā stundas laikā programmā Excel varat izveidot rakurstabulas atskaiti, kurā ir iekļauti vairāku tabulu dati. Pirmajā šīs apmācības daļā ir aprakstītas datu importēšanas un izpētes darbības. Otrajā daļā ir aprakstīts, kā precizēt atskaites pamata modeli, izmantojot pievienojumprogrammu Power Pivot, un ir sniegta informācija par to, kā Power View atskaitēm pievienot aprēķinus un hierarhijas un optimizācijas.

Vispirms importēsim datus.

  1. Lejupielādējiet šai apmācībai nepieciešamos parauga datus (ContosoV2). Papildinformāciju skatiet sadaļā Parauga datu iegūšana DAX un datu modeļa apmācībām. Izvelciet saglabājiet datu failus viegli pieejamā vietā, piemēram, mapē Lejupielādes vai Mani dokumenti.
  2. Atveriet tukšu darbgrāmatu programmā Excel.
  3. Noklikšķiniet uz Dati > Iegūt ārējos datus > No Access.
  4. Pārlūkojiet līdz mapei, kurā atrodas parauga dati, un atlasiet ContosoSales.
  5. Noklikšķiniet uz Atvērt. Tā kā tiek veidots savienojums ar datu bāzes failu, kurā ir vairākas tabulas, tiek parādīts dialoglodziņš Tabulas atlase, lai jūs varētu izvēlēties importējamās tabulas.

Dialoglodziņš Tabulas atlase

  1. Sadaļā Tabulas atlase atzīmējiet izvēles rūtiņu Iespējot vairāku tabulu atlasi.
  2. Izvēlieties visas tabulas un noklikšķiniet uz Labi.
  3. Sadaļā Datu importēšana noklikšķiniet uz Rakurstabulas atskaite un noklikšķiniet uz Labi.

 Piezīme    Iespējams, ka to pat nepamanījāt, taču jūs tikko izveidojāt datu modeli. Modelis ir datu integrācijas slānis, kas tiek automātiski izveidots, kad importējat vairākas tabulas vai vienlaikus strādājat ar vairākām tabulām vienā rakurstabulas atskaitē.

Modelis programmā Excel parasti ir caurspīdīgs, taču jūs to varat skatīt un modificēt tieši, izmantojot pievienojumprogrammu Power Pivot . Programmā Excel uz datu modeļa klātbūtni norāda tabulu kolekcija sarakstā Rakurstabulas lauki. Modeli var izveidot vairākos veidos. Papildinformāciju skatiet rakstā Datu modeļa izveide programmā Excel .

Datu izpēte, izmantojot rakurstabulu

Datus var viegli izpētīt, velkot laukus uz apgabaliem Vērtības, Kolonnas un Rindas rakurstabulas lauku sarakstā.

  1. Lauku sarakstā ritiniet uz leju, līdz ir redzama tabula FactSales.
  2. Noklikšķiniet uz SalesAmount. Tā kā šie ir skaitliski dati, programmā Excel lauks SalesAmount tiek automātiski pievienots apgabalam Vērtības.
  3. Velciet tabulas DimDate lauku CalendarYear uz apgabalu Kolonnas.
  4. Velciet tabulas DimProductSubcategory lauku ProductSubcategoryName uz apgabalu Rindas.
  5. Velciet tabulas DimProduct lauku BrandName uz apgabalu Rindas, novietojot to zem apakškategorijas.

Jūsu rakurstabulai jāizskatās līdzīgi nākamajam attēlam.

Rakurstabula, kurā redzami parauga dati

Bez īpašas piepūles esat izveidojis vienkāršu rakurstabulu, kurā ir iekļauti lauki no četrām dažādām tabulām. Šis uzdevums bija tik vienkāršs, jo starp tabulām jau iepriekš bija relācijas. Tā kā tabulu relācijas pastāvēja avotā un tā kā datus importējāt vienā operācijā, programma Excel modelī varēja atjaunot šīs relācijas.

Taču, ko darīt, ja dati ir no dažādiem avotiem vai tiek importēti vēlāk? Parasti ir iespējams iekļaut jaunus datus, izveidojot relācijas, pamatojoties uz atbilstošām kolonnām. Nākamajā darbībā importēsim papildu tabulas un noskaidrosim, kādas prasības jāievēro un kādas darbības jāveic, lai izveidotu jaunas relācijas.

Papildu tabulu pievienošana

Lai apgūtu tabulu relāciju iestatīšanu, jums nepieciešamas dažas nesaistītas papildu tabulas, ar kurām strādāt. Šajā darbībā jūs iegūsit atlikušos šajā apmācībā izmantotos datus, importējot vienu papildu datu bāzes failu un ielīmējot datus no divām citām darbgrāmatām.

Preču kategoriju pievienošana

  1. Darbgrāmatā atveriet jaunu lapu. Tā tiks lietota papildu datu glabāšanai.
  2. Noklikšķiniet uz Dati > Iegūt ārējos datus > No Access.
  3. Pārlūkojot atrodiet mapi, kurā atrodas parauga dati, un atlasiet ProductCategories. Noklikšķiniet uz Atvērt.
  4. Sadaļā Datu importēšana atlasiet vienumu Tabula un noklikšķiniet uz Labi.

Ģeogrāfisko datu pievienošana

  1. Ievietojiet vēl vienu lapu.
  2. Atveriet parauga datu failu Geography.xlsx, ievietojiet kursoru šūnā A1 un pēc tam nospiediet taustiņu kombināciju Ctrl-Shift-End, lai atlasītu visus datus.
  3. Kopējiet datus starpliktuvē.
  4. Ielīmējiet datus tikko pievienotajā tukšajā lapā.
  5. Noklikšķiniet uz Formatēt kā tabulu un izvēlieties jebkuru stilu. Formatējot datus kā tabulu, tai var piešķirt nosaukumu, kas noderēs, kad vēlāk definēsit relācijas.
  6. Sadaļā Formatēt kā tabulu pārbaudiet, vai ir atzīmēta izvēles rūtiņa Manai tabulai ir galvenes. Noklikšķiniet uz Labi.
  7. Piešķiriet tabulai nosaukumu Geography. Sadaļas Tabulu rīki > Noformējums laukā Tabulas nosaukums ievadiet Geography.
  8. Aizveriet failu Geography.xlsx, lai to noņemtu no darbvietas.

Veikalu datu pievienošana

  • Atkārtojiet iepriekš norādītās darbības ar failu Stores.xlsx un ielīmējiet tā saturu tukšā lapā. Piešķiriet tabulai nosaukumu Stores.

Tagad ir izveidotas četras lapas. 1. lapā ir rakurstabula, 2. lapā ir tabula ProductCategories, 3. lapā ir tabula Geography un 4. lapā ir tabula Stores. Tā kā jūs katrai tabulai piešķīrāt nosaukumu, nākamās darbības ietvaros būs daudz vienkāršāk izveidot relācijas.

Lauku izmantošana no tikko importētām tabulām

Jūs varat uzreiz sākt izmantot laukus no tikko importētajām tabulām. Ja programma Excel nevar noteikt, kā lauku iekļaut rakurstabulas atskaitē, jums tiks lūgts izveidot tabulu relāciju, kas saista jauno tabulu ar tabulu, kas jau iekļauta modelī.

  1. Lai skatītu visu pieejamo tabulu sarakstu, sadaļas Rakurstabulas lauki augšdaļā noklikšķiniet uz Viss.
  2. Ritiniet līdz saraksta apakšdaļai. Šeit atradīsit tikko pievienotās jaunās tabulas.
  3. Izvērsiet tabulu Stores.
  4. Velciet lauku StoreName uz apgabalu Filtri.
  5. Ievērojiet, ka programma Excel aicina izveidot relāciju. Šis paziņojums tiek parādīts, jo esat izmantojis laukus no tabulas, kas nav saistīta ar modeli.
  6. Lai atvērtu dialogu Relācijas izveide, noklikšķiniet uz Izveidot.
  7. Sadaļā Tabula izvēlieties tabulu FactSales. Šajā apmācībā lietoto parauga datu tabulā FactSales ir iekļauta detalizēta informācija par uzņēmuma Contoso pārdošanas ieņēmumiem un izdevumiem, kā arī ar citām tabulām saistītas atslēgas, tostarp veikalu kodi, kas ir iekļauti arī iepriekšējās darbības ietvaros importētajā failā Stores.xlsx.
  8. Sadaļā Kolonna (ārējā) izvēlieties kolonnu StoreKey.
  9. Sadaļā Saistītā tabula izvēlieties tabulu Stores.
  10. Sadaļā Saistītā kolonna (primārā) izvēlieties kolonnu StoreKey.
  11. Noklikšķiniet uz Labi.

Fonā programmā Excel tiek izveidots datu modelis, kuru var lietot visā darbgrāmatā, turklāt neierobežotā skaitā rakurstabulu, rakursdiagrammu un Power View atskaišu. Šī modeļa pamatā ir tabulu relācijas, ar kurām tiek norādīti ceļi datu aprēķinam un navigācijai rakurstabulas atskaitē. Nākamajā uzdevumā tiks izveidotas relācijas, lai savienotu tikko importētos datus.

Relāciju pievienošana

Jūs varat sistemātiski izveidot tabulu relācijas visām jaunām tabulām, kuras importējat. Ja darbgrāmatu koplietojat ar kolēģiem, viņi novērtēs iepriekš definētas relācijas, ja datus nepārzinās tik labi kā jūs.

Veidojot relācijas manuāli, vienlaicīgi tiek apstrādātas divas tabulas. Katrā tabulā ir jāizvēlas kolonnas, kas programmā Excel tiks lietotas saistīto rindu uzmeklēšanai citā tabulā.

Kolonnu ProductSubcategory un ProductCategory relācijas izveide

  1. Programmā Excel noklikšķiniet uz Dati > Relācijas > Jauna.
  2. Sadaļā Tabula izvēlieties tabulu DimProductSubcategory.
  3. Sadaļā Kolonna (ārējā) izvēlieties kolonnu ProductCategoryKey.
  4. Sadaļā Saistītā tabula izvēlietiesTable_ProductCategory.accdb.
  5. Sadaļā Saistītā kolonna (primārā) izvēlieties kolonnu ProductCategoryKey.
  6. Noklikšķiniet uz Labi.
  7. Aizveriet dialogu Relāciju pārvaldība.

Kategoriju pievienošana rakurstabulai

Lai gan datu modelis ir atjaunināts, tajā iekļaujot papildu tabulas un relācijas, rakurstabula tās vēl neizmanto. Šī uzdevuma ietvaros tabula ProductCategory tiks pievienota rakurstabulas lauku sarakstam.

  1. Rakurstabulas laiku sarakstā noklikšķiniet uz Viss, lai tiktu parādītas datu modelī iekļautās tabulas.
  2. Ritiniet līdz saraksta apakšdaļai.
  3. Noņemiet lauku BrandName no apgabala Rindas.
  4. Izvērsiet tabulu Table_DimProductCategories.accdb.
  5. Velciet lauku ProductCategoryName uz apgabalu Rindas, novietojot to virs lauka ProductSubcategory.
  6. Rakurstabulas lauku sarakstā noklikšķiniet uz Aktīvs, lai pārliecinātos, vai rakurstabulā tagad ir pieejama informācija par tikko lietotajām tabulām.

Kontrolpunkts: pārskats par apgūto

Tagad esat izveidojis rakurstabulu, kurā ir iekļauti dati no vairākām tabulām, dažas no kurām importējāt vēlāk. Lai šos datus savienotu, jūs izveidojāt tabulu relācijas, kas programmā Excel tiek lietotas rindu korelācijai. Jūs uzzinājāt, ka, lai uzmeklētu saistītas rindas, ir nepieciešamas kolonnas ar savstarpēji atbilstošiem datiem. Katrā parauga datu failos iekļautajā tabulā ir kolonna, kuru var lietot šim mērķim.

Lai gan rakurstabula darbojas, droši vien pamanījāt vairākus aspektus, kurus varētu uzlabot. Rakurstabulas lauku sarakstā ir liekas tabulas (DimEntity) un kolonnas (ETLLoadID), kas nav saistītas ar uzņēmumu Contoso. Turklāt joprojām nav integrēti ģeogrāfiskie dati.

Nākamie uzdevumi: modeļa skatīšana un izvēršana pievienojumprogrammā Power Pivot

Nākamo uzdevumu ietvaros modelis tiks paplašināts, izmantojot pievienojumprogrammu Microsoft Office Microsoft Excel 2013 pievienojumprogramma Power Pivot. Jūs uzzināsit, ka, izmantojot pievienojumprogrammā pieejamo diagrammas skatu, relācijas var izveidot vienkāršāk. Pievienojumprogramma tiks arī izmantota, lai izveidotu aprēķinus un hierarhijas, paslēptu vienumus, kas nav vajadzīgi lauku sarakstā, un optimizētu datus papildu atskaišu izveidei.

 Piezīme    Pievienojumprogramma Microsoft Excel 2013 pievienojumprogramma Power Pivot ir pieejama sistēmā Microsoft Office Professional Plus. Papildinformāciju skatiet rakstā Microsoft Excel 2013 pievienojumprogramma Power Pivot Pievienojumprogramma

Pievienojiet Power Pivot Excel lentei, iespējojot pievienojumprogrammu Power Pivot.

  1. Atveriet vienumu Fails > Opcijas > Pievienojumprogrammas.
  2. Lodziņā Pārvaldīt noklikšķiniet uz COM pievienojumprogrammas> Doties uz.
  3. Atzīmējiet rūtiņu Microsoft Office Microsoft Excel 2013 pievienojumprogramma Power Pivot un pēc tam noklikšķiniet uz Labi.

Lentei tagad ir Power Pivot cilne.

Relācijas pievienošana, izmantojot pievienojumprogrammas Power Pivot diagrammas skatu

  1. Programmā Excel, noklikšķiniet uz 3. lapas, lai to aktivizētu. 3. lapā ir iekļauta iepriekš importētā tabula Geography.
  2. Lentē noklikšķiniet uz Power Pivot > Pievienot datu modelim. Veicot šo darbību, tabula Geography tiek pievienota modelim. Tiek arī atvērta pievienojumprogramma Power Pivot, ko izmantosit, lai veiktu atlikušās šī uzdevuma darbības.
  3. Pievērsiet uzmanību, ka Power Pivot logā ir redzamas visas modeļa tabulas, tostarp Geography. Klikšķinot izpētiet dažas tabulas. Pievienojumprogrammā varat skatīt visus jūsu modelī iekļautos datus.
  4. Pievienojumprogrammas Power Pivot loga sadaļā Skats noklikšķiniet uz Diagrammas skats.
  5. Izmantojot slīdņa joslu, mainiet diagrammas lielumu tā, lai būtu redzami visi diagrammas objekti. Pievērsiet uzmanību tam, ka divas tabulas DimEntity un Geography nav saistītas ar pārējiem diagrammas datiem.
  6. Ar peles labo pogu noklikšķiniet uz DimEntity un noklikšķiniet uz Dzēst. Šī tabula ir sākotnējās datu bāzes artefakts, un tā modelī nav vajadzīga.
  7. Tuviniet tabulu Geography, lai varētu skatīt visus tās laukus. Izmantojot slīdni, varat palielināt tabulu diagrammu.
  8. Pievērsiet uzmanību tabulas Geography kolonnai GeographyKey. Šajā kolonnā ir iekļautas vērtības, ar kurām tiek unikāli identificēta katra tabulas Geography rinda. Tagad ir jāpārbauda, vai šī atslēga tiek lietota arī citās modeļa tabulās. Ja tā ir, var izveidot relāciju, lai tabulu Geography saistītu ar pārējo modeli.
  9. Noklikšķiniet uz Atrast.
  10. Sadaļā Metadatu atrašana ievadiet GeographyKey.
  11. Vairākas reizes noklikšķiniet uz Atrast nākamo. Ievērojiet, ka kolonna GeographyKey tiek atrasta gan tabulā Geography, gan tabulā Stores.
  12. Pārvietojiet tabulu Geography, lai to pārvietotu blakus tabulai Stores.
  13. Velciet tabulas Stores kolonnu GeographyKey uz tabulas Geography kolonnu GeographyKey. Pievienojumprogramma Power Pivot starp šīm divām kolonnām tiek uzzīmēta līnija, ar kuru ir apzīmēta relācija.

Šī uzdevuma ietvaros jūs apguvāt jaunu tabulu pievienošanas un relāciju izveides metodi. Tagad esat izveidojis pilnībā integrētu modeli, kurā visas tabulas ir savienotas un pieejamas 1. lapā iekļautajā rakurstabulā.

 Padoms    Diagrammas skatā vairākas tabulu diagrammas ir pilnībā izvērstas un ir redzamas tādas kolonnas kā ETLLoadID, LoadDate un UpdateDate. Šie konkrētie lauki ir sākotnējās Contoso datu noliktavas artefakti, kas tika pievienoti, lai nodrošinātu datu izvilkšanas un ielādes operācijas. Modelī tie nav vajadzīgi. Lai no tiem atbrīvotos, iezīmējiet kādu lauku, noklikšķiniet uz tā ar peles labo pogu un pēc tam noklikšķiniet uz Dzēst .

Aprēķinātās kolonnas izveide

Pievienojumprogrammā Power Pivot varat pievienot aprēķinus, izmantojot datu analīzes izteiksmes (DAX). Šī uzdevuma ietvaros tiks aprēķināta kopējā peļņa un tiks pievienota aprēķināta kolonna, kas veido atsauci uz datu vērtībām no citām tabulām. Vēlāk uzzināsit, kā ar atsaucēm norādītās kolonnas var izmantot, lai vienkāršotu modeli.

  1. Power Pivot logā pārslēdziet atpakaļ datu skatu.
  2. Pārdēvējiet tabulu Table_ProductCategories accdb, piešķirot tai vienkāršāku nosaukumu. Uz šo tabulu tiks veidotas atsauces turpmākajās darbībās, un, izmantojot īsāku nosaukumu, aprēķinus būs vieglāk lasīt. Noklikšķiniet ar peles labo pogu uz tabulas nosaukuma, noklikšķiniet uz Pārdēvēt, ierakstiet ProductCategories un pēc tam nospiediet taustiņu Enter.
  3. Atlasiet tabulu FactSales.
  4. Noklikšķiniet uz Noformējums > Kolonnas > Pievienot.
  5. Virs tabulas redzamajā formulu joslā ievadiet tālāk norādīto formulu. Automātiskās pabeigšanas līdzeklis atvieglo kolonnu un tabulu pilno nosaukumu ievadi, kā arī sniedz pieejamo funkciju sarakstu. Varat arī vienkārši noklikšķināt uz kolonnas un Power Pivot formulai pievieno kolonnas nosaukumu.

= [SalesAmount] - [TotalCost] - [ReturnAmount]

  1. Kad formulas veidošana ir pabeigta, nospiediet taustiņu Enter, lai akceptētu formulu.

Katrā aprēķinātās kolonnas rindā tiek ievietota vērtība. Ritinot tabulu uz leju, ievērosit, ka šīs kolonnas rindu vērtības var atšķirties atkarībā no katrā rindā iekļautajiem datiem.

  1. Pārdēvējiet kolonnu, ar peles labo pogu noklikšķinot uz Aprēķinātā_kolonna1 un atlasot Pārdēvēt kolonnu. Ievadiet Profit un pēc tam nospiediet taustiņu Enter.
  2. Tagad atlasiet tabulu DimProduct.
  3. Noklikšķiniet uz Noformējums > Kolonnas > Pievienot.
  4. Virs tabulas redzamajā formulu joslā ievadiet tālāk norādīto formulu.

= RELATED(ProductCategories[ProductCategoryName])

Funkcija RELATED atgriež vērtību no saistītas tabulas. Šajā gadījumā tabulā ProductCategories ir iekļauti preču kategoriju nosaukumi, kas būs noderīgi tabulā DimProduct, kad veidosit hierarhiju, kurā būs iekļauta informācija par kategoriju. Papildinformāciju par šo funkciju skatiet sadaļā Funkcija RELATED (DAX).

  1. Kad formulas veidošana ir pabeigta, nospiediet taustiņu Enter, lai akceptētu formulu.

Katrā aprēķinātās kolonnas rindā tiek ievietota vērtība. Ritinot tabulu uz leju, ievērosit, ka tagad katrā rindā ir norādīts preces kategorijas nosaukums.

  1. Pārdēvējiet kolonnu, ar peles labo pogu noklikšķinot uz Aprēķinātā_kolonna1 un atlasot Pārdēvēt kolonnu. Ievadiet ProductCategory un pēc tam nospiediet taustiņu Enter.
  2. Noklikšķiniet uz Noformējums > Kolonnas > Pievienot.
  3. Virs tabulas redzamajā formulu joslā ierakstiet tālāk norādīto formulu un pēc tam nospiediet taustiņu Enter, lai to pieņemtu.

= RELATED(DimProductSubcategory[ProductSubcategoryName])

  1. Pārdēvējiet kolonnu, ar peles labo pogu noklikšķinot uz Aprēķinātā_kolonna1 un atlasot Pārdēvēt kolonnu. Ievadiet ProductSubcategory un pēc tam nospiediet taustiņu Enter.

Hierarhijas izveide

Vairumā modeļu ir iekļauti dati, kuriem ir mantota hierarhija. Izplatītākie piemēri ir kalendāra dati, ģeogrāfiskie dati un produktu kategorijas. Hierarhiju izveide ir noderīga, jo pēc tam atskaitē var ievilkt vienu vienumu (hierarhiju) un vairs nav atkārtoti jāgrupē un jākārto vieni un tie paši lauki.

  1. Pievienojumprogrammā Power Pivot aktivizējiet diagrammas skatu. Izvērsiet tabulu DimDate, lai varētu ērtāk apskatīt visus tās laukus.
  2. Nospiediet taustiņu Ctrl un, turot to nospiestu, noklikšķiniet uz kolonnām CalendarYear, CalendarQuarter un CalendarMonth (tabula ir jāritina uz leju).
  3. Kad ir atlasīts trīs kolonnas, ar labo peles pogu noklikšķiniet uz kādas no atlasītajām kolonnām un noklikšķiniet uz Izveidot hierarhiju. Tabulas apakšdaļā tiek izveidots vecākobjekta hierarhijas mezgls (1. hierarhija), un atlasītās kolonnas tiek kopētas hierarhijas apakšdaļā kā bērnobjektu mezgli.
  4. Piešķiriet jaunajai hierarhijai nosaukumu Dates.
  5. Pievienojiet hierarhijai kolonnu FullDateLabel. Ar peles labo pogu noklikšķiniet uz FullDateLabel un atlasiet Pievienot hierarhijai. Izvēlieties Date. Kolonnā FullDateLabel ir iekļauts pilns datums: gads, mēnesis un diena. Pārbaudiet, vai kolonna FullDateLabel hierarhijā ir redzama kā pēdējā. Tagad ir izveidota vairāku līmeņu hierarhija, kurā iekļauts gads, ceturksnis, mēnesi un atsevišķas kalendārās dienas.
  6. Joprojām lietojot diagrammas skatu, norādiet uz tabulu DimProduct un pēc tam tabulas galvenē noklikšķiniet uz pogas Izveidot hierarhiju. Tabulas apakšdaļā tiek parādīts tukšs hierarhijas vecākobjekta mezgls.
  7. Piešķiriet jaunajai hierarhijai nosaukumu Product Categories.
  8. Lai izveidotu hierarhijas bērnobjektu mezglus, ievelciet hierarhijā kolonnas ProductCategory un ProductSubcategory.
  9. Ar peles labo pogu noklikšķiniet uz ProductName un atlasiet Pievienot hierarhijai. Izvēlieties hierarhiju Product Categories.

Tagad esat apguvis vairākas atšķirīgas hierarhiju izveides metodes un varat tās izmantot rakurstabulā.

  1. Atgriezieties programmā Excel.
  2. Atveriet 1. lapu (lapu, kurā ir iekļauta rakurstabula) un noņemiet laukus no apgabala Rindas.
  3. Aizstājiet šos laukus ar tabulas DimProduct jauno hierarhiju Product Categories.
  4. Līdzīgā veidā apgabalā Kolonnas aizstājiet lauku CalendarYear ar tabulas DimDate hierarhiju Dates.

Tagad izpētot datus, ir skaidri redzamas hierarhiju lietošanas priekšrocības. Varat neatkarīgi izvērst un aizvērt dažādus rakurstabulas apgabalus, tādējādi precīzāk kontrolējot pieejamās vietas lietojumu. Turklāt, pievienojot apgabaliem Rindas un Kolonnas vienu hierarhiju, nekavējoties tiek iegūts bagātīgs izklāsts — līdzīgu rezultātu var iegūt, apkopojot vairākus laukus, taču tas nav nepieciešams.

Kolonnu slēpšana

Tagad, kad esat izveidojis un tabulai DimProduct pievienojis hierarhiju Product Categories, rakurstabulas lauku sarakstā vairs nav vajadzīga kolonna DimProductCategory vai DimProductSubcategory. Šī uzdevuma ietvaros jūs uzzināsit, kā paslēpt nevajadzīgās tabulas un kolonnas, kas aizņem vietu rakurstabulas lauku sarakstā. Paslēpjot tabulas un kolonnas, tiek atvieglota atskaišu izveide, taču netiek ietekmēts modelis, kas nodrošina datu relācijas un aprēķinus.

Varat paslēpt atsevišķas kolonnas, kolonnu diapazonu vai visu tabulu. Tabulu un kolonnu nosaukumi tiek pelēkoti, norādot, ka tās nav redzama atskaišu izveides klientos, kuros tiek lietots šis modelis. Paslēptās kolonnas modelī ir pelēkotas, lai norādītu to statusu, taču tās joprojām ir redzamas datu skatā, lai jūs varētu turpināt tās lietot.

  1. Pievienojumprogrammā Power Pivot pārliecinieties, vai ir aktivizēts datu skats.
  2. Ar peles labo pogu noklikšķiniet uz tabulas beigās esošās cilnes DimProductSubcategory un atlasiet opciju Paslēpt no klientu rīkiem.
  3. Atkārtojiet iepriekš norādīto darbību ar tabulas ProductCategories cilni.
  4. Atveriet tabulu DimProduct.
  5. Ar peles labo pogu noklikšķiniet uz tālāk norādītajām kolonnām un noklikšķiniet uz Paslēpt no klientu rīkiem.
  • ProductKey
  • ProductLabel
  • ProductSubcategory
  1. Atlasiet vairākas secīgas kolonnas. Sāciet ar kolonnu ClassID un turpiniet līdz kolonnai ProductSubcategory. Noklikšķiniet ar peles labo pogu, lai paslēptu šīs kolonnas.
  2. Atkārtojiet iepriekš norādītās darbības ar citām tabulām, noņemot ID, atslēgas vai citus datus, kas netiks lietoti šajā atskaitē.

Programmā Excel pārejiet atpakaļ uz 1. lapu ar rakurstabulas lauku sarakstu, lai redzētu atšķirību. Tagad sarakstā ir mazāk tabulu un tabulā DimProduct ir iekļauti tikai tie vienumi, kurus, visticamāk, lietosit pārdošanas analīzei.

Power View atskaites izveide

Datu modelis sniedz priekšrocības ne tikai rakurstabulas atskaitēs, bet arī citās atskaitēs. Izmantojot tikko izveidoto modeli, varat pievienot Power View lapu, lai izmēģinātu dažus tajā pieejamos izkārtojumus.

  1. Programmā Excel noklikšķiniet uz Ievietot > Power View.

 Piezīme    Ja šī ir pirmā Power View lietošanas reize šajā datorā, tiks parādīta uzvedne, aicinot vispirms iespējot šo pievienojumprogrammu un instalēt Silverlight.

  1. Sadaļā Power View lauki noklikšķiniet uz bultiņas blakus tabulai FactSales un noklikšķiniet uz SalesAmount.
  2. Izvērsiet tabulu Geography un noklikšķiniet uz RegionCountryName.
  3. Lentē noklikšķiniet uz Karte.
  4. Tiek parādīta kartes atskaite. Velciet kādu no stūriem, lai mainītu atskaites lielumu. Kartē ar dažāda lieluma apļiem zilā krāsā ir norādīti pārdošanas rādītāji dažādās valstīs vai reģionos.

Optimizācija Power View atskaišu izveidei

Modelī veicot dažas nelielas izmaiņas, tiks radītas intuitīvākas atbildes, noformējot Power View atskaiti. Šajā uzdevumā pievienosim vairāku ražotāju tīmekļa vietņu vietrāžus URL un pēc tam kategorizēsim šos datus kā tīmekļa URL, lai URL adrese tiktu parādīta kā saite.

Iesākumā darbgrāmatā ir jāpievieno vietrāži URL.

  1. Programmā Excel atveriet jaunu lapu un pārkopējiet šīs vērtības:
ManufacturerURL    ManufacturerID
http://www.contoso.com Contoso, LTD
http://www.adventure-works.com Adventure Works
http://www.fabrikam.com Fabrikam, Inc.
  1. Formatējiet šūnas kā tabulu un pēc tam piešķiriet tabulai nosaukumuURL.
  2. Izveidojiet relāciju starp URL un tabulu, kurā norādīti ražotāju nosaukumi — DimProduct:
  1. Noklikšķiniet uz Dati > Relācijas. Tiek parādīts dialogs Relācijas izveide.
  2. Noklikšķiniet uz Jauna.
  3. Sadaļā Tabula atlasiet DimProduct.
  4. Sadaļā Kolonna atlasiet Manufacturer.
  5. Sadaļā Saistītā tabula atlasietURL.
  6. Sadaļā Saistītā kolonna (primārā) atlasiet ManufacturerID.

Lai salīdzinātu rezultātus pirms un pēc šīs darbības, izveidojiet jaunu Power View atskaiti un pievienojiet atskaitei FactSales | SalesAmount, dimProduct | Manufacturer un URL | ManufacturerURL. Pievērsiet uzmanību, ka vietrāži URL tiek parādīti kā statisks teksts.

Lai URL renderētu kā aktīvu hipersaiti, jāveic kategorizēšana. Lai kategorizētu kolonnu, izmantosim pievienojumprogrammu Power Pivot.

  1. Pievienojumprogrammā Power Pivot atveriet URL.
  2. Atlasiet ManufacturerURL.
  3. Noklikšķiniet uz Papildu > Atskaišu izveides atribūti > Datu kategorija: Neatpazīts.
  4. Noklikšķiniet uz lejupvērstās bultiņas.
  5. Atlasiet Tīmekļa URL.
  6. Programmā Excel noklikšķiniet uz Ievietot > Power View.
  7. Sadaļā Power View lauki, atlasiet FactSales | SalesAmount, dimProduct | Manufacturer, un URL | ManufacturerURL. Šoreiz vietrāži URL tiks parādīti kā reālas hipersaites.

Lai datus optimizētu Power View atskaišu izveidei, tiek veiktas arī tādas darbības kā noklusējuma lauku kopas definēšana katrai tabulai un tādu rekvizītu iestatīšana, ar kuriem tiek noteikts, vai rindas ar vienādiem datiem tiek apkopotas vai norādītas atsevišķi. Papildinformāciju skatiet sadaļās Power View atskaišu noklusējuma lauku kopas konfigurēšana un Power View atskaišu tabulu izturēšanās rekvizītu konfigurēšana.

Aprēķinātu lauku izveide

Otrajā uzdevumā "Datu izpēte, izmantojot rakurstabulu" jūs sarakstā Rakurstabulas lauki noklikšķinājāt uz lauka SalesAmount. Tā kā SalesAmount ir skaitliska kolonna, tā tika automātiski ievietota rakurstabulas apgabalā Vērtības. Pēc tam tika sagatavots lauks Sum of SalesAmount, lai aprēķinātu pārdošanas apjomu neatkarīgi no lietotajiem filtriem. Šajā gadījumā iesākumā filtru nebija, taču pēc tam tika lietoti CalendarYear, ProductSubcategoryName un BrandName.

Patiesībā jūs izveidojāt netiešu aprēķināto lauku, ļaujot viegli analizēt pārdošanas apjomu no tabulas FactSales attiecībā pret citiem laukiem, piemēram, produktu kategoriju, reģionu un datumiem. Programma Excel izveido netiešus aprēķinātos laukus, kad velkat lauku uz apgabalu Vērtības vai kad noklikšķināt uz skaitliska lauka, kā to darījāt ar SalesAmount. Netieši aprēķinātie lauki ir formulas, kas izmanto jums automātiski izveidotas standarta apkopošanas funkcijas, piemēram, SUM, COUNT un AVERAGE.

Ir arī citu veidu aprēķinātie lauki. Jūs varat pievienojumprogrammā Power Pivot izveidot tiešus aprēķinātos laukus. Atšķirībā no netieša aprēķinātā lauka, ko var izmantot tikai tajā rakurstabulā, kurā tas izveidots, tiešus aprēķinātos laukus var izmantot jebkurā darbgrāmatas rakurstabulā vai jebkurā atskaitē, kas kā datu avotu izmanto attiecīgo datu modeli. Izmantojot pievienojumprogrammā Power Pivot izveidotus tiešos aprēķinātos datu modeļus, jūs varat izmantot automātisko summu, lai automātiski izveidotu aprēķinātos laukus ar standarta apkopošanas funkciju palīdzību, vai arī varat izveidot savu lauku, lietojot formulu, kas izveidota, izmantojot datu analīzes izteiksmes (DAX).

Kā varat iztēloties, ar aprēķinātu lauku palīdzību jūs varat vieglāk analizēt datus apjomīgos un efektīvos veidos, tāpēc iesākumā apgūsim, kā tos izveidot.

Aprēķinātos laukus pievienojumprogrammā Power Pivot var viegli izveidot, izmantojot komandu Automātiskā summēšana.

  1. Tabulā FactSales noklikšķiniet uz kolonnas Profit.
  2. Noklikšķiniet uz Aprēķini > Automātiskā summēšana. Ievērojiet, ka aprēķinu apgabala šūnā tieši zem kolonnas Profit ir automātiski izveidots jauns aprēķinātais lauks Profit vērtību summa.
  3. Programmas Excel 1. lapas lauku saraksta tabulā FactSales noklikšķiniet uz Profit vērtību summa.

Gatavs! Šīs bija vienīgās veicamās darbības, lai izveidotu aprēķinātu lauku, pievienojumprogrammā Power Pivot izveidojot standarta apkopošanu. Kā redzams, pāris minūtēs ir izveidots aprēķinātais lauks SUM of Profit un pievienots rakurstabulai, ļaujot viegli analizēt peļņu atkarībā no lietotajiem filtriem. Šajā gadījumā Sum of Profit ir filtrēts pēc hierarhijām Product Category un Dates.

Taču, ko darīt, ja nepieciešama detalizētāka analīze, piemēram, noteikta kanāla, produkta vai kategorijas pārdošanas rezultāti? Lai to izdarītu, ir jāizveido vēl viens aprēķināts lauks, kurā tiek skaitīts rindu skaits — pa vienai katram pārdošanas darījumam tabulā FactSales atkarībā no lietotajiem filtriem.

  1. Tabulā FactSales noklikšķiniet uz kolonnas SalesKey.
  2. Sadaļā Aprēķini noklikšķiniet uz lejupvērstās bultiņas pie Automātiskā summēšana > Skaits.
  3. Pārdēvējiet jauno aprēķināto lauku, aprēķinu apgabalā ar peles labo pogu noklikšķinot uz SalesKey skaits un atlasot Pārdēvēt. Ierakstiet Skaits un nospiediet taustiņu Enter.
  4. Programmas Excel 1. lapas lauku saraksta tabulā FactSales noklikšķiniet uz Skaits.

Ievērojiet, ka rakurstabulai ir pievienota jauna kolonna Skaits, kurā parādīts pārdošanas darījumu skaits atkarībā no lietotajiem filtriem. Tāpat kā attiecībā uz aprēķināto lauku Profit vērtību summa, jūs redzēsit skaitu, kas filtrēts pēc hierarhijām Product Category un Dates.

Izveidosim vēl vienu lauku. Šoreiz jūs izveidosit aprēķināto lauku, kas aprēķina kopējā pārdošanas apjoma procentus noteiktam kontekstam vai filtram. Tomēr atšķirībā no iepriekšējiem aprēķinātiem laukiem, ko izveidojāt, izmantojot automātisko summu, šoreiz formulu ievadīsit manuāli.

  1. Tabulā FactSales noklikšķiniet uz jebkuras no tukšajām šūnām aprēķinu apgabalā. Padoms: aprēķināto lauku ievietošanai piemērota vieta ir augšējā kreisās puses šūna. Šādi tos var vieglāk atrast. Jūs varat pārvietot jebkuru aprēķināto lauku aprēķinu apgabalā.
  2. Formulas joslā ierakstiet un izmantojiet IntelliSense, lai izveidotu šādu formulu: Visu produktu procenti:=[Count]/CALCULATE([Count], ALL(DimProduct))
  3. Nospiediet taustiņu ENTER, lai apstiprinātu formulu.
  4. Programmas Excel 1. lapas lauku saraksta tabulā FactSales noklikšķiniet uz Visu produktu procenti.
  5. Rakurstabulā atlasiet visas kolonnas Visu produktu procenti.
  6. Cilnē Sākums noklikšķiniet uz Skaits > Procenti. Izmantojiet divas decimāldaļas, lai formatētu jaunās kolonnas.

Šis jaunais aprēķinātais lauks aprēķina pārdošanas kopējo apjomu procentos noteikta filtra kontekstā. Šajā gadījumā mūsu filtra konteksts joprojām ir hierarhijas Product Category un Dates. Piemēram, var redzēt, ka gadu gaitā ir palielinājusies datoru procentuālā attiecībā no kopējā produktu pārdošanas apjoma.

Ja zināt, kā veidot Excel formulas, gan aprēķināto kolonnu, gan aprēķināto lauku formulu izveide būs samērā vienkārša. Neatkarīgi no tā, vai pārzināt Excel formulu veidošanu, lieliska vieta, kur apgūt DAX formulu pamatus, ir nodarbības rakstā Īsā pamācība: valodas DAX pamatfunkciju apguve 30 minūtēs.

Darba saglabāšana

Saglabājiet darbgrāmatu, lai to varētu izmantot citās apmācībās vai izpētītu sīkāk.

Nākamās darbības

Lai gan varat ērti importēt datus no programmas Excel, bieži vien to var izdarīt ātrāk un efektīvāk, izmantojot pievienojumprogrammu Power Pivot. Varat filtrēt importētos datus, neiekļaujot kolonnas, kas jums nebūs vajadzības. Varat arī izvēlēties, vai datu izguvei lietot vaicājumu veidotāju vai vaicājuma komandu. Informāciju par šīm alternatīvajām metodēm skatiet sadaļās Datu iegūšana no datu plūsmas pievienojumprogrammā Power Pivot un Datu importēšana no analīzes pakalpojumiem vai pievienojumprogrammas Power Pivot.

Power View atskaitēs ir paredzēts lietot datu modeļus, kas līdzinās tam, ko tikko izveidojāt. Papildinformāciju par bagātīgajām datu vizualizācijas iespējām, ko programmā Excel nodrošina līdzeklis Power View, skatiet sadaļās Programmas Excel 2013 līdzekļa Power View startēšana un Power View: datu izpēte, vizualizācija un attēlojums.

Pamēģiniet uzlabot savu datu modeli, lai sagatavotu labākas Power View atskaites, apgūstot rakstu Apmācība: datu modeļa optimizēšana Power View atskaišu veidošanai

 
 
Attiecas uz:
Excel 2013, Excel 2013 pievienojumprogramma Power Pivot