Vadovėlis: „PivotTable“ duomenų analizė naudojant „Excel 2013“ duomenų modelį

Naudojant „Excel“ galima greičiau negu per valandą sukurti „PivotTable“ ataskaitą, kurioje derinami keliose lentelėse esantys duomenys. Pirmąją šio vadovėlio dalį sudaro duomenų importavimo ir ištyrimo paaiškinimai. Antrojoje dalyje naudosite „Power Pivot“ papildinį, kad apibrėžtumėte duomenų modelį, pagal kurį kuriama ataskaita, ir išmoktumėte įtraukti naujus skaičiavimus, hierarchijas ir „Power View“ ataskaitų optimizacijas.

Pradėkime nuo duomenų importavimo.

  1. Atsisiųsti šio vadovėlio duomenų pavyzdį („ContosoV2“). Daugiau informacijos žr. DAX ir duomenų modelių vadovėliams skirtų duomenų pavyzdžio gavimas. Išskleiskite ir įrašykite duomenų failus lengvai pasiekiamoje vietoje, pvz., aplanke Atsisiuntimai arba Mano atsisiuntimai.

  2. Programoje „Excel“ atidarykite tuščią darbaknygę.

  3. Spustelėkite Duomenys > Gauti išorinių duomenų > Iš „Access“.

  4. Eikite į aplanką, kuriame yra duomenų pavyzdžio failai ir pasirinkite ContosoSales.

  5. Spustelėkite Atidaryti. Jungiatės prie duomenų bazės failo, kuriame yra kelios lentelės, todėl rodomas dialogo langas Lentelės pasirinkimas, kuriame galima pasirinkti norimas importuoti lenteles.

    Lentelės pasirinkimo dialogo langas

  6. Srityje Pasirinkti lentelę pažymėkite Įgalinti keleto lentelių pasirinkimą.

  7. Pasirinkite visas lenteles, tada spustelėkite Gerai.

  8. Srityje Importuoti duomenis spustelėkite „PivotTable“ ataskaita, tada spustelėkite Gerai.

    Pastabos : 

    • Jūs ką tik sukūrėte modelį. Modelis yra duomenų integravimo sluoksnis, automatiškai sukuriamas importuojant arba dirbant su keliomis lentelėmis vienu metu toje pačioje „PivotTable“ ataskaitoje.

    • Modelis programoje „Excel“ dažniausiai yra permatomas, tačiau jį galite peržiūrėti ir keisti tiesiogiai naudodami „ Power Pivot “ papildinį. Programoje „Excel“ duomenų modelis tikrai yra, jei „PivotTable“ laukų sąraše matote lentelių rinkinį. Yra keli lentelių kūrimo būdai. Daugiau informacijos žr. Duomenų modelio kūrimas programoje „Excel“ .

Duomenų eksportavimas naudojant „PivotTable“

Lengvai tyrinėti duomenis galima velkant laukus į „PivotTable“ laukų sąrašo sritis Values, Columns ir Rows.

  1. Laukų sąraše slinkite į apačią, kol rasite lentelę FactSales.

  2. Spustelėkite SalesAmount. Šie duomenys yra skaitiniai, todėl „Excel“ automatiškai perkelia SalesAmount į sritį Values.

  3. Srityje DimDate vilkite ProductSubcategoryName į Columns.

  4. Srityje DimProductSubcategory vilkite ProductSubcategoryName į Columns.

  5. Srityje DimProduct vilkite BrandName į Rows po subkategorija.

Jūsų „PivotTable“ turi atrodyti panašiai į toliau pateiktą ekraną.

„PivotTable“ rodomas duomenų pavyzdys

Taip be didelių pastangų sukūrėte pradinę „PivotTable“, apimančią laukus iš keturių skirtingų lentelių. Ši užduotis tokia paprasta yra dėl iš anksto nustatytų lentelių ryšių. Šaltinyje jau buvo lentelių ryšiai ir jūs importavote visas lenteles atlikdami vieną veiksmą, todėl „Excel“ gali iš naujo sukurti tuos ryšius modelyje.

Tačiau ką daryti, jei duomenys yra iš skirtingų šaltinių arba importuojami vėliau? Paprastai naujus duomenis galite įtraukti sukurdami ryšius pagal atitinkamus stulpelius. Kitas veiksmas: importuoti papildomas lenteles ir sužinoti naujų ryšių kūrimo reikalavimus bei veiksmus.

Papildomų lentelių įtraukimas

Norint išmokti nustatyti lentelių ryšius, reikia kelių papildomų, nesusietų lentelių. Atlikdami šį veiksmą, likusius vadovėlyje naudotus duomenis gausite importuodami vieną papildomą duomenų bazės failą ir įklijuodami duomenis iš darbaknygių.

Produktų kategorijų įtraukimas

  1. Atidarykite naują „Excel“ darbalapį. Jame bus išsaugoti papildomi duomenys.

  2. Spustelėkite Duomenys > Gauti išorinių duomenų > Iš „Access“.

  3. Eikite į aplanką, kuriame yra duomenų pavyzdžio failai ir pasirinkite ProductCategories. Spustelėkite Atidaryti.

  4. Srityje Importuoti duomenis pasirinkite Lentelė, tada spustelėkite Gerai.

Geografinių duomenų įtraukimas

  1. Įterpkite kitą lapą.

  2. Duomenų pavyzdžio faile atidarykite Geography.xlsx, perkelkite pelės žymeklį į A1, tada paspauskite Ctrl-Shift-End, kad būtų pažymėti visi duomenys.

  3. Nukopijuokite duomenis į mainų sritį.

  4. Įklijuokite duomenis tuščiame lape, kurį neseniai įterpėte.

  5. Spustelėkite Formatuoti kaip lentelę ir pasirinkite bet kurį stilių. Formatuojant duomenis kaip lentelę, galima nurodyti jos pavadinimą, o tai bus patogu vėliau apibrėžiant ryšius.

  6. Įsitikinkite, kad srityje Formatuoti kaip lentelę pažymėta Mano lentelėse yra antraščių. Spustelėkite Gerai.

  7. Įveskite lentelės pavadinimą Geography. Srities Lentelės įrankiai > Dizainas dalyje Lentelės pavadinimas įveskite Geography.

  8. Uždarykite Geography.xlsx, kad jis būtų išvalytas iš darbo srities.

Parduotuvių duomenų įtraukimas

  • Pakartokite ankstesnius veiksmus su failu Stores.xlsx, įklijuodami jo turinį į tuščią lapą. Įveskite lentelės pavadinimą Geography.

Turėtų būti keturi lapai. 1 lape yra „PivotTable“, 2 lape yra ProductCategories, 3 lape yra Geography, o 4 lape yra Stores. Jūs suteikėte lentelėms pavadinimus, todėl kitą veiksmą – ryšių sukūrimą – atlikti bus daug paprasčiau.

Laukų iš naujai importuotų lentelių naudojimas

Galite iš karto pradėti naudoti laukus iš lentelių, kurias ką tik importavote. Jei „Excel“ negali nustatyti, kaip įtraukti lauką į „PivotTable“ ataskaitą, būsite paprašyti sukurti lentelės ryšį, kuries susies naują lentelę su ta, kuri jau yra modelyje.

  1. Sąrašo „PivotTable“ laukai viršuje spustelėkite Visi, kad peržiūrėtumėte visą galimų lentelių sąrašą.

  2. Slinkite į sąrašo apačią. Čia rasite ką tik įtrauktas naujas lenteles.

  3. Išskleiskite Stores.

  4. Vilkite StoreName į sritį Filtrai.

  5. Atminkite, kad „Excel“ įspėja apie sukurtą ryšį. Šis pranešimas rodomas, nes naudojote laukus iš lentelės, kuri nėra susieta su modeliu.

  6. Spustelėkite Kurti, kad atidarytumėte dialogo langą Ryšio kūrimas.

  7. Dalyje Table pasirinkite FactSales. Naudojamame duomenų pavyzdyje FactSales yra „Contoso“ verslo pardavimo ir išsami išlaidų informacija bei raktai į kitas lenteles, įskaitant parduotuvių kodus, kurie taip pat pateikti faile Stores.xlsx, kurį importavote anksčiau.

  8. Dalyje Column (Foreign) pasirinkite StoreKey.

  9. Dalyje Related Table pasirinkite Stores.

  10. Dalyje Related Column (Primary) pasirinkite StoreKey.

  11. Spustelėkite Gerai.

Programa „Excel“ kuria duomenų modelį, kurį visoje darbaknygėje galima naudoti kuriant bet kokį skaičių „PivotTable“, „PivotCharts“ ar „Power View“ ataskaitų. Šiam modeliui labai svarbūs ryšiai, kuriais lentelės susietos tarpusavyje, nes jais nustatomi keliai, naudojami perkeliant duomenis ir skaičiuojant juos „PivotTable“ ataskaitoje. Atlikdami tolesnę užduotį sukursite ryšius, kuriais bus sujungti importuoti duomenys.

Ryšių įtraukimas

Galite nuosekliai kurti ryšius tarp visų naujų lentelių, kurias importuojate. Jei bendrinate darbaknygę su kolegomis, kurie neturi tų duomenų, kuriuos turite jūs, būtų gerai turėti iš anksto nustatytus ryšius.

Kuriant ryšius rankiniu būdu, dirbsite su dviem lentelėmis vienu metu. Kiekvienai lentelei pasirinksite stulpelius, kurie informuos „Excel“, kaip peržvelgti susijusias kitos lentelės eilutes.

“ ryšiai

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Susiekite ProductSubcategory su ProductCategory

  1. Programoje „Excel“ spustelėkite Duomenys > Ryšiai > Naujas.

  2. Dalyje Table pasirinkite DimProductSubcategory.

  3. Dalyje Column (Foreign) pasirinkite ProductCategoryKey.

  4. Dalyje Related Table pasirinkite Table_ProductCategory.accdb.

  5. Dalyje Related Column (Primary) pasirinkite ProductCategoryKey.

  6. Spustelėkite Gerai.

  7. Uždarykite dialogo langą Ryšių tvarkymas.

Kategorijų įtraukimas į „PivotTable“.

Nors duomenų modelis buvo atnaujintas ir į jį buvo įtraukta papildomų lentelių ir ryšių, „PivotTable“ jų dar nenaudoja. Atlikdami šią užduotį jūs įtrauksite „ProductCategory“ į „PivotTable“ laukų sąrašą.

  1. „PivotTable“ laukų dalyje spustelėkite Visi, kad būtų rodomos duomenų modelio lentelės.

  2. Slinkite į sąrašo apačią.

  3. Srityje Eilutės pašalinkite BrandName.

  4. Išskleiskite Table_DimProductCategories.accdb.

  5. Vilkite ProductCategoryName į sritį Eilutės ir palikite virš ProductSubcategory.

  6. „PivotTable“ laukų srityje spustelėkite Aktyvus. Taip įsitikinsite, kad lentelės, kurias ką tik naudojote, yra aktyviai naudojamos „PivotTable“.

Kontrolinis taškas: peržiūrėkite, ką išmokote

Sukūrėte „PivotTable“, kuri apima duomenis iš kelių lentelių; kelias iš jų vėliau importavote. Duomenims paimti reikėjo sukurti lentelių ryšius, kuriuos programa „Excel“ naudoja eilutėms susieti. Sužinojote, kad norint peržvelgti susijusius duomenis, labai svarbu sukurti stulpelius, kuriuose būtų atitinkančių eilučių. Visose duomenų pavyzdžio failuose esančiose lentelėse yra šiam tikslui skirtas stulpelis.

Nors „PivotTable“ veikia, tikriausiai pastebėjote kelis dalykus, kuriuos būtų galima patobulinti. Panašu, kad „PivotTable“ laukų sąraše yra papildomų lentelių (DimEntity) ir stulpelių (ETLLoadID), kurie nėra susiję su „Contoso“ verslu. Be to, mes neintegravome geografinių duomenų.

Toliau: modelio peržiūra ir išplėtimas naudojant „Power Pivot“

Atlikdami tolesnes užduotis modeliui išplėsti naudosite „Microsoft Office“ „„Microsoft Excel 2013“ papildinys „Power Pivot““ papildinį. Sužinosite, kad naudojant papildinio diagramos rodinį galima lengviau sukurti ryšius. Naudodami papildinį taip pat sukursite skaičiavimus ir hierarchijas, slėpsite elementus, kurie neturi būti rodomi laukų sąraše, ir optimizuosite duomenis, naudojamus papildomoms ataskaitoms.

Pastaba :  „ „Microsoft Excel 2013“ papildinys „Power Pivot“ “ papildinys yra įtrauktas į „Office Professional Plus“. Daugiau informacijos rasite skyriuje „„Power Pivot“, esanti „Microsoft Excel 2013““ papildinys“.

Įtraukite „Power Pivot“ į „Excel“ juostą įgalindami „Power Pivot“ papildinį.

  1. Eikite į Failas > Parinktys > Papildiniai.

  2. Lauke Valdyti spustelėkite COM papildiniai> Pirmyn.

  1. Pažymėkite žymės langelį Microsoft Office „Microsoft Excel 2013“ papildinys „Power Pivot“, tada spustelėkite Gerai.

Juostelėje dabar yra skirtukas Power Pivot.

Ryšio įtraukimas naudojant „Power Pivot“ diagramos rodinį

  1. Programoje „Excel“ spustelėkite 3 lapą, kad jį suaktyvintumėte. 3 lape yra anksčiau importuota lentelė „Geography“.

  2. Juostelėje spustelėkite Power Pivot > Įtraukti į duomenų modelį. Šiuo veiksmu lentelė „Geography“ įtraukiama į modelį. Taip pat atidaromas „Power Pivot“ papildinys, kurį naudosite atlikdami kitus užduoties veiksmus.

  3. Atminkite, kad „Power Pivot“ lange rodomos visos modelyje esančios lentelės, įskaitant „Geography“. Spustelėkite kelias lenteles. Papildinyje galite peržiūrėti visus modelyje esančius duomenis.

  4. „Power Pivot“ lango srityje Peržiūra spustelėkite Diagramos rodinys.

  5. Naudodami slinkties juostą pakeiskite diagramos dydį, kad matytumėte visus joje esančius objektus. Atkreipkite dėmesį, kad dvi lentelės nėra susijusios su kitais diagramoje esančiais duomenimis: DimEntity ir Geography.

  6. Dešiniuoju pelės mygtuku spustelėkite DimEntity, tada spustelėkite Naikinti. Ši lentelė yra ankstesnės duomenų bazės liekana ir jos modelyje nereikia.

  7. Priartinkite lentelę „Geography“, kad galėtumėte peržiūrėti visus jos laukus. Norėdami padidinti diagramą, galite naudoti slankiklį.

  8. Atkreipkite dėmesį į lentelėje „Geography“ esantį GeographyKey. Tai stulpelis, kuriame yra reikšmės, unikaliai identifikuojančios kiekvieną lentelės „Geography“ eilutę. Išsiaiškinkite, ar kitos modelio lentelės naudoja šį raktą. Jeigu naudoja, galime sukurti ryšį, kuris sujungs lentelę „Geography“ su modeliu.

  9. Spustelėkite Rasti.

  10. Dalyje Rasti metaduomenis įveskite GeographyKey.

  11. Kelis kartus spustelėkite Rasti kitą. Pastebėsite, kad GeographyKey rodomas lentelėje „Geography“ ir lentelėje „Stores“.

  12. Padėkite lentelę „Geography“ taip, kad ji būtų šalia „Stores“.

  13. Vilkite lentelės „Stores“ stulpelį GeographyKey prie lentelės „Geography“ stulpelio GeographyKey. „Power Pivot“ nubrėžia liniją tarp dviejų stulpelių. Ja nurodoma, kad buvo sukurtas ryšys.

Atlikdami šią užduotį išmokote įtraukti lenteles ir kurti ryšius. Sukūrėte visiškai integruotą modelį su visomis „PivotTable“ 1 lape pasiekiamomis sujungtomis lentelėmis.

Patarimas :  Diagramos rodinyje kelios lentelių diagramos yra visiškai išskleistos ir juose rodomi stulpeliai ETLLoadID, LoadDate ir UpdateDate. Šie ypatingi laukai yra pradinės „Contoso“ duomenų bazės, naudotos išskleidžiant duomenis ir įkeliant veiksmus, artefaktai. Modelyje jų nereikia. Norėdami jų atsikratyti, paryškinkite juos, dešiniuoju pelės mygtuku spustelėkite lauką, tada spustelėkite Naikinti .

Apskaičiuojamojo stulpelio kūrimas

Naudojant „Power Pivot“ galima įtraukti skaičiavimus naudojant duomenų analizės išraiškas (DAX). Atliekant tolesnę užduotį skaičiuosite bendrąjį pelną ir įtrauksite apskaičiuotų stulpelių nuorodas, kurios ima duomenų reikšmes iš kitų lentelių. Vėliau sužinosite, kaip naudoti nurodytus stulpelius siekiant supaprastinti modelį.

  1. „Power Pivot“ lange vėl perjunkite duomenų rodinį.

  2. Lentelei Table_ProductCategories accdb suteikite patogesnį pavadinimą. Šią lentelę naudosite tolesniems veiksmams, o trumpesnis pavadinimas leis lengviau perskaityti skaičiavimų informaciją. Dešiniuoju pelės mygtuku spustelėkite lentelę, tada spustelėkite Pervardyti, įveskite ProductCategories ir paspauskite Enter.

  3. Pasirinkite lentelę FactSales.

  4. Spustelėkite Dizainas > Stulpeliai > Įtraukti.

  5. Virš lentelės esančioje formulės juostoje įveskite toliau nurodytą formulę. Automatinio vykdymo funkcija padeda įvesti visiškai apibrėžtus stulpelių ir lentelių pavadinimus bei nurodo galimas funkcijas. Galite tiesiog spustelėti stulpelį, o „Power Pivot“ įtrauks stulpelio pavadinimą į formulę.

    = [SalesAmount] – [TotalCost] – [ReturnAmount]

  6. Sukurtą formulę patvirtinkite spustelėdami klavišą Enter.

    Visose apskaičiuojamojo stulpelio eilutėse pateikiamos reikšmės. Jei slinksite lentele žemyn, pamatysite, kad šio stulpelio eilučių reikšmės gali būti skirtingos, atsižvelgiant į kiekvienoje eilutėje esančius duomenis.

  7. Pervardykite stulpelį dešiniuoju pelės mygtuku spustelėdami CalculatedColumn1 ir pasirinkdami Pervardyti stulpelį. Įveskite Profit, tada paspauskite klavišą Enter.

  8. Pasirinkite lentelę DimProduct .

  9. Spustelėkite Dizainas > Stulpeliai > Įtraukti.

  10. Virš lentelės esančioje formulės juostoje įveskite toliau nurodytą formulę.

    = RELATED(ProductCategories[ProductCategoryName])

    Funkcija RELATED pateikia reikšmę iš susijusios lentelės. Šiuo atveju lentelėje „ProductCategories“ yra produktų kategorijų pavadinimai, kuriuos kuriant hierarchiją, apimančią kategorijos informaciją, būtų patogu turėti lentelėje „DimProduct“. Daugiau informacijos apie šią funkciją žr. Funkcija RELATED (DAX).

  11. Sukurtą formulę patvirtinkite spustelėdami klavišą Enter.

    Visose apskaičiuojamojo stulpelio eilutėse pateikiamos reikšmės. Jeigu slinksite lentelę žemyn, pamatysite, kad kiekvienoje eilutėje nurodytas produkto kategorijos pavadinimas.

  12. Pervardykite stulpelį dešiniuoju pelės mygtuku spustelėdami CalculatedColumn1 ir pasirinkdami Pervardyti stulpelį. Įveskite ProductCategory, tada paspauskite klavišą Enter.

  13. Spustelėkite Dizainas > Stulpeliai > Įtraukti.

  14. Virš lentelės esančioje formulės juostoje įveskite toliau nurodytą formulę, tada paspauskite klavišą Enter, kad ją patvirtintumėte.

    = RELATED(DimProductSubcategory[ProductSubcategoryName])

  15. Pervardykite stulpelį dešiniuoju pelės mygtuku spustelėdami CalculatedColumn1 ir pasirinkdami Pervardyti stulpelį. Įveskite ProductSubcategory, tada paspauskite klavišą Enter.

Hierarchijos kūrimas

Daugelis modelių apima duomenis, kurie yra hierarchiniai. Įprasti pavyzdžiai gali būti kalendoriaus duomenys, geografiniai duomenys ir produktų kategorijos. Hierarchijas kurti patogu todėl, kad galima vilkti vieną elementą (hierarchiją) į ataskaitą ir nereikia kelis kartus rinkti bei tvarkyti tų pačių laukų.

  1. Perjunkite „Power Pivot“ diagramos rodinį. Išskleiskite lentelę DimDate, kad būtų patogu peržiūrėti visus jos laukus.

  2. Paspauskite ir laikykite nuspaustą klavišą Ctrl, tada spustelėkite stulpelius CalendarYear, CalendarQuarter ir CalendarMonth (reikės slinkti lentele žemyn).

  3. Pasirinkę tris stulpelius, dešiniuoju pelės mygtuku spustelėkite juos, tada spustelėkite Kurti hierarchiją. Pirminis hierarchijos mazgas (1 hierarchija) sukuriamas lentelės apačioje, o pažymėti stulpeliai nukopijuojami į hierarchiją kaip antriniai mazgai.

  4. Kaip naujos hierarchijos pavadinimą įveskite Dates.

  5. Įtraukite stulpelį FullDateLabel į hierarchiją. Dešiniuoju pelės mygtuku spustelėkite FullDateLabel ir pasirinkite Įtraukti į hierarchiją. Pasirinkite Data. Lauke FullDateLabel rašoma visa data, įskaitant metus, mėnesį ir dieną. Patikrinkite, ar FullDateLabel rodoma hierarchijos apačioje. Dabar turite kelių lygių hierarchiją, kurią sudaro metai, mėnuo ir atskirtos kalendoriaus dienos.

  6. Diagramos rodinyje pasirinkite lentelę DimProduct, tada spustelėkite lentelės antraštės mygtuką Kurti hierarchiją. Lentelės apačioje rodomas tuščias hierarchijos pirminis mazgas.

  7. Kaip naujos hierarchijos pavadinimą įveskite Product Categories.

  8. Norėdami sukurti hierarchijos antrinius mazgus, vilkite ProductCategory ir ProductSubcategory į hierarchiją.

  9. Dešiniuoju pelės mygtuku spustelėkite ProductName ir pasirinkite Įtraukti į hierarchiją. Pasirinkite Produktų kategorijos.

Jau sužinojote kelis skirtingus hierarchijos kūrimo būdus, todėl galima juos panaudoti „PivotTable“.

  1. Grįžkite į „Excel“.

  2. 1 lape (lapas, kuriame yra „PivotTable“) pašalinkite laukus srityje Rows.

  3. Pakeiskite juos nauja hierarchija Product Categories srityje DimProduct.

  4. Panašiai pakeiskite CalendarYear srityje Columns hierarchija Dates srityje DimDate.

Peržiūrėdami duomenis pamatysite hierarchijų naudojimo teikiamą naudą. Galima nepriklausomai išskleisti ir suskleisti skirtingas „PivotTable“ sritis ir geriau valdyti pateiktą vietą. Taip pat įtraukdami vieną hierarchiją į Rows ir Columns pamatysite išsamią detalizaciją ir nereikės kurti kelių stulpelių, kad gautumėte panašius rezultatus.

Stulpelių slėpimas

Sukūrėte hierarchiją „Product Categories“ ir perkėlėte ją į DimProduct, todėl „PivotTable“ laukų sąraše jums nebereikia DimProductCategory arba DimProductSubcategory. Atlikdami tolesnę užduotį sužinosite, kaip slėpti nesusijusias lenteles ir stulpelius, užimančius vietą „PivotTable“ laukų sąraše. Paslėpus lenteles ir stulpelius pagerinamos ataskaitų kūrimo galimybės nekeičiant modelio, teikiančio duomenų ryšius ir skaičiavimus.

“ ataskaitos stulpelių ir lentelių slėpimas

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Galima paslėpti atskirus stulpelius, jų diapazoną arba visą lentelę. Lentelių ir stulpelių pavadinimai papilkinami ir nebebus rodomi ataskaitų klientams, naudojantiems modelį. Paslėpti stulpeliai papilkinami modelyje, taip nurodant jų būseną, bet jie vis tiek rodomi duomenų rodinyje, kad galėtumėte toliau su jais dirbti.

  1. Įsitikinkite, kad „Power Pivot“ papildinyje pasirinktas duomenų rodinys.

  2. Apačioje esančiuose skirtukuose dešiniuoju pelės mygtuku spustelėkite DimProductSubcategory ir pasirinkite Slėpti kliento įrankiuose.

  3. Pakartokite su ProductCategories.

  4. Atidarykite DimProduct.

  5. Dešiniuoju pelės mygtuku toliau nurodytus stulpelius, tada spustelėkite Slėpti kliento įrankiuose:

    • ProductKey

    • ProductLabel

    • ProductSubcategory

  6. Kelių greta esančių stulpelių pasirinkimas. Pradėkite nuo ClassID ir tęskite iki pabaigoje esančio ProductSubcategory. Norėdami juos paslėpti, spustelėkite dešiniuoju pelės mygtuku.

  7. Pakartokite veiksmus kitose lentelėse, pašalindami ID, raktus ar kitą informaciją, kurios nenaudosite ataskaitoje.

Vėl įjunkite „Excel“ 1 lape esantį „PivotTable“ laukų sąrašą, kad galėtumėte pamatyti skirtumą. Sumažintas lentelių skaičius, o srityje DimProduct yra tik tie elementai, kuriuos tikriausiai naudosite analizuodami pardavimą.

„Power View“ ataskaitos kūrimas

Duomenų modelio teikiami pranašumai naudojami ne tik „PivotTable“ ataskaitose. Naudodami ką tik sukurtą modelį galite įtraukti „Power View“ lapą ir išbandyti kelis pateiktus maketus.

  1. Programoje „Excel“ spustelėkite Įterpti > Power View.

    Pastaba :  Jei šiame kompiuteryje „Power View“ naudojate pirmą kartą, būsite paraginti įgalinti papildinį ir pirmiausia įdiegti „Silverlight“.

  2. Sąraše Power View laukai spustelėkite rodyklę šalia lentelės FactSales, tada spustelėkite SalesAmount.

  3. Išplėskite lentelę Geography ir spustelėkite RegionCountryName.

  4. Juostelėje spustelėkite Žemėlapis.

  5. Rodoma žemėlapio ataskaita. Norėdami pakeisti dydį, vilkite jo kampą. Žemėlapyje mėlyni įvairaus dydžio apskritimai nurodo skirtingų šalių ir regionų pardavimo rezultatus.

Optimizavimas „Power View“ ataskaitoms

Šiek tiek pakeitę savo modelį, gausite daugiau intuityvių atsakymų, kai kursite „Power View“ ataskaitą. Šioje užduotyje įtrauksite kelių gamintojų svetainių URL, tada skirstysite tuos duomenis, pvz., svetainių URL, pagal kategorijas tam, kad URL adresas būtų rodomas kaip saitas.

Pirmiausia įtraukite URL į savo darbaknygę.

  1. Programoje „Excel“ atidarykite naują lapą ir nukopijuokite šias reikšmes:

ManufacturerURL

ManufacturerID

http://www.contoso.com

Contoso, LTD

http://www.adventure-works.com

Adventure Works

http://www.fabrikam.com

Fabrikam, Inc.

  1. Suformatuokite langelius kaip lentelę ir pavadinkite ją URL.

  2. Sukurkite ryšį tarp URL ir lentelės, kurioje yra gamintojų pavadinimai, DimProduct:

    1. Spustelėkite Duomenys > Ryšiai. Rodomas dialogo langas Ryšiai.

    2. Spustelėkite Nauja.

    3. Lauke Lentelė pasirinkite DimProduct.

    4. Lauke Stulpelis pasirinkite Gamintojas.

    5. Lauke Susijusi lentelė pasirinkite URL.

    6. Dalyje Related Column (Primary) pasirinkite ProductCategoryKey.

Norėdami palyginti pradinius ir galutinius rezultatus, įjunkite naują „Power View“ ataskaitą ir įtraukite į ją laukus FactSales | SalesAmount, dimProduct | Manufacturer ir URL | ManufacturerURL. Atminkite, kad URL rodomi kaip statinis tekstas.

Norėdami tekstą paversti aktyviu URL hipersaitus, reikia priskirti kategoriją. Norėdami priskirti stulpelio kategoriją, turite naudoti „Power Pivot“.

  1. „Power Pivot“ atidarykite URL.

  2. Pasirinkite ManufacturerURL.

  3. Spustelėkite Išplėstinis > Ataskaitų ypatybės > Duomenų kategorija: nesuskirstyta į kategorijas.

  4. Spustelėkite rodyklę žemyn.

  5. Pasirinkite Žiniatinklio URL.

  6. Programoje „Excel“ spustelėkite Įterpti > Power View.

  7. Sąraše „Power View“ laukai pasirinkite FactSales | SalesAmount, dimProduct | Manufacturer ir URL | ManufacturerURL. Dabar URL adresai rodomi kaip aktyvūs hipersaitai.

Kitos „Power View“ optimizacijos apima numatytojo laukų rinkinio nustatymą kiekvienai lentelei ir ypatybių, kurios apibrėžia, ar pasikartojančių duomenų eilutės agreguojamos, ar vardijamos atskirai, nustatymą. Daugiau informacijos žr. Numatytojo „Power View“ ataskaitų laukų rinkinio konfigūravimas ir „Power View“ ataskaitų lentelių veikimo būdo ypatybių konfigūravimas.

Apskaičiuotų laukų kūrimas

Antrojoje užduotyje „Duomenų tyrimas naudojant „PivotTable“ sąraše „PivotTable“ laukai spustelėjote SalesAmount. SalesAmount yra skaičių stulpelis, todėl jis buvo automatiškai įtrauktas į „PivotTable“ sritį Reikšmės. SalesAmount sumos eilutė buvo parengta skaičiuoti pardavimo sumas, kad ir kokie filtrai pritaikomi. Šiuo atveju iš pradžių filtrai netaikomi, tačiau taikomi CalendarYear, ProductSubcategoryName ir BrandName.

Jūs sukūrėte numanomą apskaičiuotą lauką, kuris leidžia analizuoti pardavimo kiekius iš lentelės FactSales lyginant su tokiai laukais kaip produktų kategorija, regionas, data. Numatomus apskaičiuotus laukus sukuria programa „Excel“ velkant lauką į sritį Reikšmės arba spustelint skaičiaus lauką, kaip darėte su SalesAmount. Numanomi apskaičiuoti laukai yra automatiškai jums sukurtos formulės, kuriose naudojamos standartinės agregavimo funkcijos, pvz., SUM, COUNT ir AVERAGE.

Yra ir kitokių apskaičiuotų laukų. Detalius apskaičiuotus laukus galite kurti naudodami „Power Pivot“. Priešingai nei numanomas apskaičiuotas laukas, kuris gali būti naudojamas tik toje „PivotTable“, kurioje jis buvo sukurtas, detalūs apskaičiuoti laukai gali būti naudojami bet kurioje darbaknygės „PivotTable“ arba ataskaitoje, kurioje naudojamas duomenų modelis kaip duomenų šaltinis. Kartu su „Power Pivot“ sukurtais detaliais apskaičiuotais laukais, galite naudoti automatinės sudėties funkciją ir automatiškai sukurti apskaičiuotus laukus per standartinius telkinius arba galite sukurti savo laukus pagal formulę, sukurtą naudojant duomenų analizės išraiškas (DAX).

Kaip suprantate, apskaičiuoti laukai gali padėti analizuoti duomenis pasitelkus nepaprastai galingus metodus, todėl sužinokime, kaip galite juos sukurti.

Kurti apskaičiuotus laukus „Power Pivot“ yra lengva, kai naudojate funkciją AutoSum.

  1. Lentelėje FactSales spustelėkite stulpelį Profit.

  2. Spustelėkite Skaičiavimai > AutoSum. Atminkite, kad naujas apskaičiuotas laukas Profit suma buvo automatiškai sukurta skaičiavimų srities langelyje tiesiai po stulpeliu Profit.

  3. „Excel“ skirtuko 1 lapas laukų sąrašo dalyje FactSales spustelėkite Profit suma.

Štai ir viskas! Tiek ir tereikia norint sukurti apskaičiuotą lauką pagal standartinį „Power Pivot“ telkinį. Kaip matote, vos per kelias minutes sukūrėte apskaičiuotą lauką Profit suma ir įtraukėte jį į „PivotTable“. Dabar bus lengva analizuoti pelną atsižvelgiant į taikomus filtrus. Šiuo atveju galite matyti Profit suma, išfiltruotą pagal produktų kategoriją ir datos hierarchiją.

Tačiau ką daryti, jei reikia išsamesnės analizės, pvz., konkretaus kanalo, produkto ar kategorijos pardavimo rezultatų? Tam reikės sukurti kitą apskaičiuotą lauką, kuris skaičiuoja eilučių skaičių, lentelėje FactSales kiekviena eilutė atitinka pardavimą atsižvelgiant į taikomus filtrus.

  1. Lentelėje FactSales spustelėkite stulpelį SalesKey.

  2. Lauke Skaičiavimai spustelėkite rodyklę žemyn ties AutoSum > Kiekis.

  3. Pervardykite naują apskaičiuotą lauką dešiniuoju pelės mygtuku skaičiavimo srityje spustelėdami SalesKey kiekis ir pasirinkdami Pervardyti. Įveskite Kiekis, tada paspauskite klavišą Enter.

  4. „Excel“ skirtuko 1 lapas laukų sąrašo dalyje FactSales spustelėkite Profit suma.

Atkreipkite dėmesį, kad naujasis stulpelis Kiekis yra įtrauktas į „PivotTable“. Jame rodomas pardavimų skaičius atsižvelgiant į taikomus filtrus. Taip pat kaip ir apskaičiuotas laukas Profit suma, galite matyti Kiekį, filtruojamą pagal produktų kategoriją ir datos hierarchiją.

Sukurkite kitą lauką. Dabar sukursite apskaičiuotą lauką, kuris skaičiuos bendro pardavimo procentą, skirtą tam tikram kontekstui arba filtrui. Tačiau priešingai nei anksčiau apskaičiuotuose laukuose, kuriuos sukūrėte naudodami automatinės sudėties funkciją, šį kartą formulę įvesite rankiniu būdu.

  1. Lentelėje FactSales spustelėkite bet kurį tuščią langelį, esantį skaičiavimo srityje. Patarimas: Puiki vieta apskaičiuotiems laukams įtraukti ura viršutinis kairysis langelis. Taip juos lengviau rasite. Galite apžiūrėti visus apskaičiuotus laukus, esančius skaičiavimo srityje.

  2. Formulių juostoje įveskite ir naudokite IntelliSense, kad sukurtumėte šią formulę: Percentage of All Products:=[Count]/CALCULATE([Count], ALL(DimProduct))

  3. Norėdami patvirtinti formulę, paspauskite klavišą ENTER.

  4. „Excel“ skirtuko 1 lapas laukų sąrašo dalyje FactSales spustelėkite Percentage of All Products.

  5. „PivotTable“ pasirinkite kelis Percentage of All Products stulpelius.

  6. Skirtuke Pagrindinis puslapis spustelėkite Skaičius > Procentinis dydis. Norėdami formatuoti kiekvieną naują stulpelį, naudokite du skaičius po dešimtainio kablelio.

Šis naujas apskaičiuotas laukas apskaičiuoja bendrą pardavimo procentinę dalį pagal duotą filtro kontekstą. Šiuo atveju filtro kontekstas yra produktų kategorija ir datos hierarchijos. Pavyzdžiui, galite pastebėti, kad per metus padidėjo kompiuterių bendrojo pardavimo procentinė dalis.

Kurti formules tiek apskaičiuotiems stulpeliams, tiek apskaičiuotiems laukams bus gana lengva, jei žinote, kaip kurti formules programoje „Excel“. Nesvarbu, ar mokate kurti formules „Excel“, ar ne, DAX formulių kūrimo pagrindus galite išmokti kurti sekdami pamokas Greitasis pasirengimas darbui: DAX pagrindai per 30 minučių.

Darbo įrašymas

Įrašykite darbaknygę taip, kad galėtumėte ją naudoti su kitais vadovėliais ar tolesniam tyrimui.

Kiti žingsniai

Duomenis galima nesudėtingai importuoti naudojant iš „Excel“, tačiau dažniausiai greičiau ir efektyviau importuosite naudodami „Power Pivot“ papildinį. Importuojamus duomenis galima filtruoti, išskyrus stulpelius, kurių jums nereikia. Taip pat galite pasirinkti pateikti duomenis į užklausų daryklę arba užklausų komandą. Toliau pateikti alternatyvūs būdai: Duomenų gavimas iš duomenų informacijos santraukos „„Power Pivot““ ir Duomenų importavimas iš analizės tarnybų arba „„Power Pivot““.

„Power View“ ataskaitos veikia duomenų modeliuose panašiai kaip ta, kurią ką tik sukūrėte. Sužinokite daugiau apie išsamius duomenų vizualizavimus, kuriuos „Power View“ įgalina naudoti programoje „Excel“: „Power View“ paleidimas programoje „Excel 2013“ ir „Power View“: duomenų tyrinėjimas, vizualizavimas ir pateikimas.

Bandykite patobulinti savo duomenų modelį ir sukurti geresnių „Power View“ ataskaitų perskaitę Vadovėlis: „Power View“ ataskaitų duomenų modelio optimizavimas

Tobulinkite savo įgūdžius
Ieškoti mokymo
Pirmiausia gaukite naujų funkcijų
Prisijunkite prie „Office Insider“ dalyvių

Ar ši informacija buvo naudinga?

Dėkojame už jūsų atsiliepimus!

Dėkojame už jūsų atsiliepimą! Panašu, kad gali būti naudinga jus sujungti su vienu iš mūsų „Office“ palaikymo agentų.

×