Õpetus: PivotTable-liigendtabeli andmeanalüüs andmemudeli abil Excel 2013-s

Vähem kui tunni ajaga saate Excelis koostada PivotTable-liigendtabeli aruande, mis kombineerib andmeid mitmest tabelist. Õpetuse esimeses osas näidatakse samm-sammult andmete importimist ja uuringut. Teises osas kasutate Power Pivoti lisandmoodulit aruande aluseks oleva andmemudeli täiustamiseks, õppides, kuidas lisada arvutusi ja hierarhiaid ning optimeerimisi Power View aruannete jaoks.

Alustame andmete importimisest.

  1. Laadige alla näidisandmed (ContosoV2) selle õpetuse jaoks. Täpsemat teavet leiate artiklist DAX-i ja andmemudeli õpetuste jaoks näidisandmete hankimine. Pakkige andmefailid lahti ja salvestage lihtsalt leitavasse kohta, nt kausta Allalaadimised või Minu dokumendid.

  2. Avage Excelis tühi töövihik.

  3. Valige Andmed > Too välisandmed > Accessist.

  4. Minge näidisandmefailide kausta ja valige ContosoSales.

  5. Klõpsake nuppu Ava. Kuna loote ühenduse andmebaasifailiga, mis sisaldab mitut tabelit, kuvatakse dialoog Tabeli valimine, et saaksite valida imporditavad tabelid.

    Dialoog Tabeli valimine

  6. Tabeli valimise jaotises märkige ruut Luba mitme tabeli valimine.

  7. Valige kõik tabelid ja klõpsake nuppu OK.

  8. Andmete importimise jaotises klõpsake valikut PivotTable-liigendtabeli aruanne ja siis nuppu OK.

    Märkmed : 

    • Te ei pruukinud seda isegi märgata, kuid oletegi loonud andmemudeli! Mudel on andmeintegreerimiskiht, mis luuakse automaatselt, kui impordite korraga mitu tabelit või töötate samas PivotTable-liigendtabeli aruandes korraga mitme tabeliga.

    • Mudel on Excelis enamjaolt läbipaistev, ent Power Pivot i lisandmooduli abil saate seda otse vaadata ja muuta. Andmemudeli olemasolu on Excelis selge, kui näete PivotTable-liigendtabeli väljaloendis mitme tabeli kogumit. Mudeli loomiseks on mitu võimalust. Lisateavet leiate artiklist Andmemudeli loomine Excelis .

Andmete uurimine PivotTable-liigendtabeli abil

Andmete uurimine on lihtne, kui lohistate väljad PivotTable-liigendtabeli väljaloendis jaotistesse Väärtused, Veerud ja Read.

  1. Väljaloendis kerige alla, kuni leiate tabeli FactSales.

  2. Klõpsake väärtust SalesAmount (Müügisumma). Kuna need on arvandmed, asetab Excel välja SalesAmount automaatselt alasse Väärtused.

  3. Lohistage alas DimDate (DimKuupäev) väärtus CalendarYear (Kalendriaasta) jaotisse Veerud.

  4. Lohistage alas DimProductSubcategory (DimTooteAlamkategooria) väärtus ProductSubcategoryName (TooteAlamkategooriaNimi) jaotisse Read.

  5. Lohistage alas DimProduct (DimToode) väärtus BrandName (KaubamärgiNimi) jaotisse Read, paigutades selle alamkategooria alla.

Teie PivotTable-liigendtabel peaks sarnanema järgmisel kuvatõmmisel näidatuga.

PivotTable-liigendtabel näidisandmetega

Väga väikse pingutusega on teil nüüd lihtne PivotTable-liigendtabel, mis sisaldab välju neljast erinevast tabelist. Toimingu tegi lihtsaks asjaolu, et tabelite vahel olid seosed juba olemas. Kuna tabeliseosed olid allikas olemas ja kuna importisite kõik tabelid ühe toimingu raames, sai Excel need seosed mudelis uuesti luua.

Mida aga teha siis, kui teie andmed pärinevad erinevatest allikatest või on imporditud hiljem? Üldjuhul saate uued andmed kaasata nii, et loote omavahel vastavuses olevate veergude põhjal seosed. Järgmises toimingus tutvustamegi täiendavate tabelite importimist ning kirjeldame uute seoste loomise nõudeid ja toiminguid.

Tabelite lisamine

Tabeliseoste häälestamisega tutvumiseks peab teil olema täiendavaid, seni veel ühendamata tabeleid, millega saaksite töötada. Käesoleva õpetuse ülejäänud andmete hankimiseks tuleb teil esmalt importida veel üks andmebaasifail ja kleepida andmed kahest muust töövihikust.

Tootekategooriate lisamine

  1. Avage töövihikus uus leht. Seda läheb vaja lisaandmete talletamiseks.

  2. Valige Andmed > Too välisandmed > Accessist.

  3. Minge näidisandmefailide kausta ja valige ProductCategories. Klõpsake Ava.

  4. Andmete importimises valige Tabel ja klõpsake nuppu OK.

Asukohaandmete lisamine

  1. Sisestage veel üks leht.

  2. Näidisandmefailidest avage Geography.xlsx, liigutage kursor kohale A1 ja vajutage kõigi andmete valimiseks Ctrl-Shift-End.

  3. Kopeerige andmed lõikelauale.

  4. Kleepige andmed tühjale lehele, mille te just lisasite.

  5. Klõpsake nuppu Vorminda tabelina ja valige suvaline laad. Andmeid tabelina vormindades saate sellele nime anda, millest on kasu hiljem seoste määratlemisel.

  6. Tabelina vormindamisel veenduge, et ruut Minu tabelil on päised oleks märgitud. Klõpsake nuppu OK.

  7. Pange tabelile nimeks Geograafia. Valige Tabeliriistad > Kujundus ja tippige väljale Tabeli nimi Geograafia.

  8. Sulgege Geography.xlsx, et see tööruumist eemaldada.

Poeandmete lisamine

  • Korrake eelmisi samme failiga Stores.xlsx, kleepides selle sisu tühjale lehele. Pange tabelile nimeks Poed.

Nüüd peaks teil olema neli lehte. Leht 1 sisaldab PivotTable-liigendtabelit, lehel 2 on ProductCategories (Tootekategooriad), lehel 3 on Geograafia ja lehel 4 on Poed. Kuna andsite kõigile tabelitele nimed, on seoste loomine järgmises etapis palju lihtsam.

Vastimporditud tabelite väljade kasutamine

Imporditud tabelite väljad saate kohe kasutusele võtta. Kui Excel ei saa kindlaks teha, kuidas mõni väli PivotTable-liigendtabeli aruandesse kaasata, palutakse teil luua tabeliseos, mis seostab uue tabeli mõne samasse mudelisse juba kuuluva tabeliga.

  1. Saadaolevate tabelite täieliku loendi vaatamiseks klõpsake PivotTable-liigendtabeli väljaloendi ülaosas nuppu Kõik.

  2. Liikuge kerides loendi lõppu. Sealt leiate just lisatud uued tabelid.

  3. Laiendage tabelit Poed.

  4. Lohistage väli StoreName (PoeNimi) jaotisse Filtrid.

  5. Nagu näete, palub Excel teil seose luua. Teatis kuvatakse seetõttu, et olete kasutanud välju tabelist, mis pole mudeliga seotud.

  6. Dialoogi „Loo seos“ avamiseks klõpsake nuppu Loo.

  7. Valige tabelis FactSales (TegelikMüük). Kasutatavates näidisandmetes sisaldab FactSales (TegelikMüük) Contoso äritegevuse üksikasjalikku müügi- ja kuluteavet ning teiste tabelite koode, sh kauplusekoode, mis leiduvad ka eelmises toimingus imporditud failis Stores.xlsx.

  8. Valige jaotises Veerg (väline) väärtus StoreKey (KauplusKood).

  9. Valige jaotises Seostuv tabel väärtus Poed.

  10. Valige jaotises Seostuv veerg (primaarne) väärtus StoreKey (KaupluseKood).

  11. Klõpsake nuppu OK.

Excel koostab taustal andmemudeli, mida saab kasutada terves töövihikus piiramatul arvul PivotTable-liigendtabelites, PivotChart-liigenddiagrammides ja Power View aruannetes. Selle mudeli toimimiseks on vajalikud tabeliseosed, mis määravad kindlaks PivotTable-liigendtabelis kasutatavad navigeerimis- ja arvutusteed. Järgmises toimingus loote seosed käsitsi, et ühendada andmed, mille olete just importinud.

Seoste lisamine

Tabeliseosed saate süsteemselt luua kõigi uute imporditud tabelite jaoks. Kui kasutate töövihikut töökaaslastega ühiselt, oskavad teised eelmääratletud seoseid hinnata, eriti juhul, kui nad pole andmetega nii hästi kursis kui teie.

Käsitsi seoseid luues töötate korraga kahe tabeliga. Iga tabeli puhul valite veerud, mis ütlevad Excelile, kuidas otsida seotud ridu teises tabelis.

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

ProductSubcategory ja ProductCategory seostamine

  1. Valige Excelis Andmed > Seosed > Uus.

  2. Valige jaotises Tabel väärtus DimProductSubcategory (DimTooteAlamkategooria).

  3. Valige jaotises Veerg (väline) väärtus ProductCategoryKey (Toote_Kategooriavõti).

  4. Valige jaotises Seostuv tabel väärtus Table_ProductCategory.accdb.

  5. Valige jaotises Seostuv veerg (primaarne) väärtus ProductCategoryKey (Toote_Kategooriavõti).

  6. Klõpsake nuppu OK.

  7. Sulgege dialoog Halda seoseid.

Kategooriate lisamine PivotTable-liigendtabelisse

Kuigi andmemudel on täiendavate tabelite ja seoste kaasamiseks värskendatud, ei kasuta PivotTable-liigendtabel neid veel. Selles toimingus lisate PivotTable-liigendtabeli väljaloendisse välja ProductCategory (Tootekategooria).

  1. Andmemudelis leiduvate tabelite kuvamiseks klõpsake PivotTable-liigendtabeli väljaloendis nuppu Kõik.

  2. Liikuge kerides loendi lõppu.

  3. Eemaldage väli BrandName (Kaubamärk) jaotisest Read.

  4. Laiendage Table_DimProductCategories.accdb.

  5. Lohistage ProductCategoryName (TootekategooriaNimi) jaotisse Read, asetades selle väärtuse ProductSubcategory (TooteAlamkategooria) kohale.

  6. Klõpsake PivotTable-liigendtabeli väljaloendis väärtust Aktiivne, et kontrollida, kas viimati kasutatud tabelid on nüüd PivotTable-liigendtabelis aktiivselt kasutuses.

Kontrollpunkt: vaadake õpitu üle

Teil on nüüd PivotTable-liigendtabel, mis sisaldab mitmest tabelist pärinevaid andmeid, millest osa importisite hiljem. Andmete koondamiseks tuli teil luua tabeliseosed, mida Excel kasutab ridade omavahel vastavusse viimiseks. Saite selgeks, et sobivate andmetega veergude olemasolu on seostuvate ridade otsimisel hädavajalik. Näidisandmefailides sisaldavad kõik tabelid veergu, mida saab sel otstarbel kasutada.

Kuigi PivotTable toimib, olete ilmselt märganud nii mõndagi asja, mida saaks paremaks muuta. PivotTable-liigendtabeli väljaloendis paistab olevat lisatabeleid (DimEntity) ja -veerge (ETLLoadID), mis pole Contoso äriga seotud. Samuti pole me veel integreerinud asukohaandmeid.

Järgmiseks: mudeli vaatamine ja laiendamine Power Pivotis

Järgmises ülesandeseerias kasutate mudeli laiendamiseks Microsoft Office’i lisandmoodulit Power Pivot rakenduses Microsoft Excel 2013. Saate teada, et lisandmooduli diagrammivaadet kasutades on seoste loomine lihtsam. Samuti saate seda lisandmoodulit kasutada arvutuste ja hierarhiate koostamiseks, soovimatute üksuste peitmiseks väljaloendist ja andmete optimeerimiseks täiendavaks aruandluseks.

Märkus. :  Lisandmoodul Power Pivot rakenduses Microsoft Excel 2013 on saadaval Office Professional Plusis. Lisateavet leiate artiklist Power Pivot rakenduses Microsoft Excel 2013.

Lisage Power Pivot Exceli menüülindile, lubades Power Pivoti lisandmooduli.

  1. Valige Fail > Suvandid > Lisandmoodulid.

  2. Klõpsake väljal Halda väärtust COM-lisandmoodulid ja seejärel nuppu Mine.

  1. Märkige ruut Microsoft Office Power Pivot rakenduses Microsoft Excel 2013 ja klõpsake nuppu OK.

Menüülindil on nüüd ka menüü Power Pivot.

Seose lisamine Power Pivotis diagrammivaate abil

  1. Klõpsake Excelis lehte Leht3, et see aktiveerida. Leht3 sisaldab varem imporditud tabelit Geograafia.

  2. Valige menüülindil Power Pivot > Lisa andmemudelisse. Tabel Geograafia lisatakse andmemudelisse. Samuti avatakse Power Pivoti lisandmoodul, mida teil läheb vaja selle toimingu ülejäänud juhiste täitmiseks.

  3. Nagu näete, kuvatakse Power Pivoti aknas kõik mudelisse kuuluvad tabelid (sh Geograafia). Klõpsake läbi paar tabelit. Lisandmoodulis saate vaadata kõiki oma mudelis sisalduvaid andmeid.

  4. Klõpsake Power Pivoti akna jaotises Kuva nuppu Diagrammivaade.

  5. Muutke liuguririba abil diagrammi suurust, nii et näete diagrammil kõiki objekte. Pange tähele, et kaks tabelit pole ülejäänud diagrammiga seotud: DimEntity ja Geography.

  6. Paremklõpsake tabelit DimEntity (DimOlem) ja klõpsake käsku Kustuta. See tabel on algsest andmebaasist pärinev jäänuk, mida pole mudelis vaja.

  7. Suurendage tabelit Geograafia, et näeksite kõiki selle välju. Liuguri abil saate tabelidiagrammi vajadusel suuremaks teha.

  8. Nagu näete, on tabelis Geograafia veerg GeographyKey (GeograafilineKood). See veerg sisaldab väärtusi kõigi tabeli Geograafia ridade kordumatuks tuvastamiseks. Vaatame, kas teised mudeli tabelid kasutavad ka seda koodi. Kui jah, saame luua seose tabeli Geograafia ühendamiseks ülejäänud mudeliga.

  9. Klõpsake käsku Otsi.

  10. Metaandmete otsimise aknas tippige GeographyKey (GeograafilineKood).

  11. Klõpsake mitu korda käsku Otsi järgmine. Näete, et GeographyKey (GeograafilineKood) on olemas tabelites Geograafia ja Poed.

  12. Paigutage tabel Geograafia tabeli Poed kõrvale.

  13. Lohistage tabeli Poed veerg GeographyKey (GeograafilineKood) tabeli Geograafia veerule GeographyKey (GeograafilineKood). Power Pivot tõmbab kahe veeru vahele seost näitava joone.

Selles ülesandes õppisite uut võtet, kuidas tabeleid lisada ja seoseid luua. Nüüd on teil täielikult integreeritud mudel, mille kõik tabelid on ühendatud ja saadaval PivotTable-liigendtabeli lehel Leht1.

Näpunäide. :  Diagrammivaates on mitu tabelidiagrammi täielikult laiendatud; kuvatud on näiteks veerud ETLLoadID, LoadDate ja UpdateDate. Need konkreetsed väljad on jäänused Contoso algsest andmehoidlast, mis lisati andmete kättesaamiseks ja laadimise toetamiseks. Andmemudelis pole neid vaja. Eemaldamiseks tõstke need esile ning esmalt paremklõpsake välja ja seejärel klõpsake käsku Kustuta .

Arvutatud veeru loomine

Power Pivotis saate arvutuste lisamiseks kasutada DAX-i (Data Analysis Expressions) avaldisekeelt. Käesolevas toimingus tuleb teil arvutada kogukasum ja lisada arvutatud veerud, mis viitavad teistest tabelitest pärinevatele andmeväärtustele. Hiljem näete, kuidas viidatud veerge oma mudeli lihtsustamiseks kasutada.

  1. Aktiveerige aknas Power Pivot uuesti andmevaade.

  2. Pange tabelile Table_ProductCategories.accdb suupärasem nimi. Sellele tabelile tuleb teil järgmistes juhistes viidata ning lühem nimi teeb arvutused lihtsamini loetavaks. Paremklõpsake tabeli nime, klõpsake käsku Nimeta ümber, tippige Tootekategooriad ja vajutage siis sisestusklahvi (Enter).

  3. Valige tabel FactSales (TegelikMüük).

  4. Valige Kujundus > Veerud > Lisa.

  5. Tippige tabeli kohale valemiribale järgmine valem. Automaatteksti funktsioon aitab teil tippida veergude ja tabelite täielikud nimed ja loetleb saadaolevad funktsioonid. Samuti võite veergu lihtsalt klõpsata – Power Pivot lisab veeru nime valemisse.

    = [SalesAmount] - [TotalCost] - [ReturnAmount] (= [Müügisumma] - [Kogumaksumus] - [Tagastussumma])

  6. Kui olete valemi koostamise lõpetanud, vajutage selle kinnitamiseks sisestusklahvi (Enter).

    Kõik arvutatud veeru read täidetakse väärtustega. Kui kerite läbi tabeli, näete, et selles veerus on ridadel erinevad väärtused vastavalt iga rea andmetele.

  7. Nimetage veerg ümber, paremklõpsates üksust CalculatedColumn1 ja valides käsu Nimeta veerg ümber. Tippige Kasum ja vajutage sisestusklahvi (Enter).

  8. Nüüd valige tabel DimProduct (DimToode).

  9. Valige Kujundus > Veerud > Lisa.

  10. Tippige tabeli kohale valemiribale järgmine valem.

    = RELATED(Tootekategooriad[TootekategooriaNimi])

    Funktsioon RELATED tagastab väärtuse seotud tabelist. Praegusel juhul sisaldab tabel Tootekategooriad tootekategooriate nimesid, millest on kasu tabelis DimProduct (DimToode), kui koostate kategooriateavet sisaldava hierarhia. Lisateavet selle funktsiooni kohta leiate artiklist Funktsioon RELATED (DAX).

  11. Kui olete valemi koostamise lõpetanud, vajutage selle kinnitamiseks sisestusklahvi (Enter).

    Kõik arvutatud veeru read täidetakse väärtustega. Kui kerite läbi tabeli, näete, et igal real on nüüd tootekategooria nimi.

  12. Nimetage veerg ümber, paremklõpsates üksust CalculatedColumn1 ja valides käsu Nimeta veerg ümber. Tippige Tootekategooria ja vajutage sisestusklahvi (Enter).

  13. Valige Kujundus > Veerud > Lisa.

  14. Tippige tabeli kohale valemiribale järgmine valem ja vajutage valemi kinnitamiseks sisestusklahvi (Enter).

    = RELATED(DimProductSubcategory[ProductSubcategoryName]) (= RELATED(DimTooteAlamkategooria[TooteAlamkategooriaNimi]))

  15. Nimetage veerg ümber, paremklõpsates üksust CalculatedColumn1 ja valides käsu Nimeta veerg ümber. Tippige TooteAlamkategooria ja vajutage sisestusklahvi (Enter).

Hierarhia loomine

Enamik mudeleid sisaldab loomupäraselt hierarhilisi andmeid. Tavalised näited on kalendriandmed, asukohaandmed ja tootekategooriad. Hierarhiate loomine on kasulik, kuna saate aruandesse lohistada ühe üksuse (hierarhia), selle asemel, et samu välju ikka ja jälle uuesti kokku panna ning korraldada.

  1. Lülituge Power Pivotis diagrammivaatele. Laiendage tabelit DimDate (DimKuupäev), et näeksite hõlpsamini kõiki selle välju.

  2. Vajutage ja hoidke all klahvi Ctrl ning klõpsake veerge CalendarYear, CalendarQuarter ja CalendarMonth (peate tabeli läbi kerima).

  3. Kui kolm veergu on valitud, paremklõpsake ühte neist ja klõpsake siis käsku Loo hierarhia. Tabeli allosas luuakse hierarhia emasõlm Hierarhia 1 ja valitud veerud kopeeritakse hierarhiasse tütarsõlmedena.

  4. Tippige oma uue hierarhia nimeks Kuupäevad.

  5. Lisage hierarhiasse veerg FullDateLabel (TäielikKuupäevasilt). Paremklõpsake veergu FullDateLabel (TäielikKuupäevasilt) ja klõpsake käsku Lisa hierarhiasse. Valige Date (Kuupäev). FullDateLabel (TäielikKuupäevasilt) sisaldab täielikku kuupäeva (sh aastat, kuud ja kuupäeva). Veenduge, et FullDateLabel (TäielikKuupäevasilt) oleks hierarhias kõige lõpus. Nüüd on teil mitmetasemeline hierarhia, mis sisaldab aastat, kvartalit, kuud ja üksikuid kalendripäevi.

  6. Endiselt diagrammivaates olles valige tabel DimProduct ja klõpsake seejärel tabelipäises nuppu Loo hierarhia. Tabeli allosas kuvatakse tühi hierarhia emasõlm.

  7. Tippige oma uue hierarhia nimeks Tootekategooriad.

  8. Hierarhia tütarsõlmede loomiseks lohistage Tootekategooria ja TooteAlamkategooria hierarhiasse.

  9. Paremklõpsake väärtust ProductName (Tootenimi) ja valige Lisa hierarhiasse. Valige Tootekategooriad.

Nüüd, kui tunnete mitut viisi hierarhia loomiseks, kasutame neid PivotTable-liigendtabelis.

  1. Minge tagasi Excelisse.

  2. Eemaldage lehelt Leht1 (see leht sisaldab PivotTable-liigendtabelit) jaotises Read asuvad väljad.

  3. Asendage need uue hierarhiaga Tootekategooriad tabelis DimProduct (DimToode).

  4. Samamoodi asendage CalendarYear (Kalendriaasta) jaotises Veerud hierarhiaga Kuupäevad tabelis DimDate (DimKuupäev).

Kui nüüd andmeid uuesti uurite, on hierarhiate kasutamise eelised kohe näha. Erinevaid PivotTable-liigendtabeli alasid saab omaette laiendada ja sulgeda, mis annab saadaoleva ruumi kasutamise üle parema kontrolli. Lisaks saate jaotistesse Read ja Veerud ühe hierarhia lisamisega rikkalike võimalustega ja vahetu süvitsimineku, ilma et peaksite sama tulemuse saavutamiseks mitmeid välju kuhjama.

Veergude peitmine

Nüüd, kui olete loonud Tootekategooriate hierarhia ja pannud selle tabelisse DimProduct, ei vaja te PivotTable-liigendtabeli väljaloendis enam välju DimProductCategory ega DimProductSubcategory. Selles ülesandes õpite, kuidas peita kõrvalisi tabeleid ja veerge, mis võtavad PivotTable-liigendtabeli väljaloendis ruumi. Tabelite ja veergude peitmisega parandate aruandluskogemust, mõjutamata andmeseoseid ja -arvutusi pakkuvat mudelit.

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

Peita saate nii üksikveerge, veeruvahemiku kui ka terve tabeli. Tabeli- ja veerunimed muutuvad tuhmiks – näitamaks, et need on mudelit kasutavate aruandlusklientide jaoks peidetud. Peidetud veerud on nende oleku tähistamiseks mudelis tuhmid, kuid jäävad andmevaates nähtavaks, et saaksite jätkata nendega töötamist.

  1. Veenduge, et Power Pivotis on valitud andmevaade.

  2. Paremklõpsake akna allservas olevat sakki DimProductSubcategory ja siis klõpsake käsku Peida klienditööriistades.

  3. Korrake valikuga ProductCategories.

  4. Avage DimProduct.

  5. Paremklõpsake järgmisi veerge ja klõpsake käsku Peida klienditööriistades.

    • ProductKey

    • ProductLabel

    • ProductSubcategory

  6. Valige korraga mitu järjestikust veergu. Alustage veeruga ClassID (KlassiID) ja lõpetage veeruga TooteAlamkategooria. Nende peitmiseks tehke paremklõps.

  7. Korrake teiste tabelitega, eemaldades ID-d, võtmed ja muud üksikasjad, mida te selles aruandes ei kasuta.

Erinevuse nägemiseks aktiveerige uuesti Exceli Leht1, kus asub PivotTable-liigendtabeli väljaloend. Tabelite arv on väiksem ja DimProduct (DimToode) sisaldab ainult neid üksusi, mida te müügi analüüsimisel tõenäolisemalt kasutate.

Power View aruande loomine

PivotTable-liigendtabeli aruanded pole ainsat tüüpi aruanded, millel andmemudelist kasu on. Kasutades sama mudelit, mille te just koostasite, saate lisada Power View lehe selle pakutavate paigutuste proovimiseks.

  1. Valige Excelis Lisa > Power View.

    Märkus. :  Kui te pole Power View'd selles seadmes varem kasutanud, palutakse teil esmalt lisandmoodul lubada ja installida Silverlight.

  2. Power View väljaloendis klõpsake tabeli FactSales (TegelikMüük) kõrval olevat noolenuppu ja siis välja SalesAmount (Müügisumma).

  3. Laiendage tabelit Geograafia ja klõpsake välja RegionCountryName (PiirkonnaRiigiNimi).

  4. Klõpsake menüülindil nuppu Kaart.

  5. Ilmub kaardiaruanne. Selle suurendamiseks lohistage mõnda nurka. Kaardil näitavad erineva suurusega sinised ringid erinevate riikide või regioonide müügitulemusi.

Power View’ aruandluseks optimeerimine

Mudelis mõne väikese muudatuse tegemine annab Power View aruande kujundamisel intuitiivsemaid vastuseid. Selles toimingus tuleb teil lisada mitme tootja veebisaitide URL-id ning seejärel liigitada need andmed veebisaitide URL-idena, et URL-aadress kuvataks lingina.

Esimese sammuna tuleb teil URL-id oma töövihikusse lisada.

  1. Avage Excelis uus leht ja kopeerige need väärtused:

TootjaURL

TootjaID

http://www.contoso.com

Contoso, LTD

http://www.adventure-works.com

Adventure Works

http://www.fabrikam.com

Fabrikam, Inc.

  1. Vormindage lahtrid tabelina ja pange siis tabelile nimeks URL.

  2. Looge seos tabeli URL ja tootjate nimesid sisaldava tabeli DimProduct (DimToode) vahel.

    1. Valige Andmed > Seosed. Kuvatakse dialoog „Loo seos“.

    2. Klõpsake nuppu Uus.

    3. Valige jaotises Tabel väärtus DimProduct (DimToode).

    4. Valige jaotises Veerg väärtus Manufacturer (Tootja).

    5. Valige jaotises Seostuv tabel väärtus URL.

    6. Valige jaotises Seostuv veerg (primaarne) väärtus TootjaID.

Eelmiste ja uute tulemite võrdlemiseks looge uus Power View aruanne ning lisage aruandesse FactSales | SalesAmount (TegelikMüük | Müügisumma), dimProduct | Manufacturer (DimToode | Tootja) ja URL | ManufacturerURL (URL | TootjaURL). Nagu näete, kuvatakse URL-id staatilise tekstina.

URL-i renderdamiseks aktiivse hüperlingina tuleb see vastavalt liigitada (sellele tuleb määrata kategooria). Veeru liigitamiseks kasutage Power Pivotit.

  1. Avage Power Pivoti aknas URL.

  2. Valige väärtus TootjaURL.

  3. Valige Täpsemalt > Aruandluse omadused > Andmekategooria: Kategoriseerimata.

  4. Klõpsake allanoolt.

  5. Valige väärtus Veebisaidi URL.

  6. Valige Excelis Lisa > Power View.

  7. Valige Power View väljaloendis FactSales | SalesAmount (TegelikMüük | Müügisumma), dimProduct | Manufacturer (DimToode | Tootja) ja URL | ManufacturerURL (URL | Tootja URL). Seekord kuvatakse URL-id tegelike hüperlinkidena.

Muud Power View’ optimeerimisvõimalused sisaldavad iga tabeli jaoks seatava vaikevälja määratlemist ja atribuutide seadistamist, mis määravad, kas korduvate andmete read ühendatakse või loetletakse iseseisvalt. Lisateavet leiate teemadest Power View’ aruandele seatud vaikevälja konfigureerimine ja Power View’ aruannetes tabeli käitumise atribuutide konfigureerimine.

Arvutatud väljade loomine

Teises toimingus (Andmete uurimine PivotTable-liigendtabeli abil) klõpsasite PivotTable-liigendtabeli väljaloendis välja SalesAmount (Müügisumma). Kuna SalesAmount (Müügisumma) on arvuline veerg, paigutati see automaatselt PivotTable-liigendtabeli jaotisse Väärtused. Seejärel oli välja SalesAmount (Müügisumma) summafunktsioon valmis arvutama mis tahes rakendatavale filtrile vastavaid müügisummasid. Käesoleval juhul ei rakendatud alguses üldse filtreid ning seejärel rakendati filtrid CalendarYear (Kalendriaasta), ProductSubcategoryName (TooteAlamkategooriaNimi) ja BrandName (Kaubamärk).

Sisuliselt lõite selle tegevusega kaudselt arvutatud välja, mis hõlbustab tabeli FactSales (TegelikMüük) müügisummade analüüsimist ja muude väljadega (nt tootekategooria, piirkond ja kuupäevad) võrdlemist. Excel loob kaudsed arvutatud väljad siis, kui lohistate mõne välja väärtuste alasse või klõpsate mõnda arvvälja (nagu välja SalesAmount (Müügisumma) korral). Kaudsed arvutatud väljad on valemid, mis kasutavad standardseid liitmisfunktsioone (nt SUM, COUNT ja AVERAGE) ja mis luuakse teie jaoks automaatselt.

On ka muud tüüpi arvutatud välju. Power Pivotis saate luua otseseid arvutatud välju. Erinevalt kaudsest arvutatud väljast, mida saab kasutada üksnes PivotTable-liigendtabelis, kus need loodi, saab otseseid arvutatud välju kasutada kõigis töövihikus leiduvates PivotTable-liigendtabelites ja kõigis aruannetes, mis kasutavad andmeallikana andmemudelit. Power Pivotis loodud otseste arvutatud väljade korral saate automaatsumma funktsiooni kasutada arvutatud väljade automaatseks loomiseks standardsete liitmisfunktsioonidega, kuid soovi korral saate väljad ka ise luua, kasutades DAX-keeles (Data Analysis Expressions) loodud valemit.

Nagu võite arvata, aitab arvutatud väljade loomine teil andmeid analüüsida väga võimsal viisil. Seetõttu uurimegi järgmiseks, kuidas neid luua.

Arvutatud väljade loomine Power Pivotis on lihtne, kui kasutate funktsiooni Automaatsumma.

  1. Klõpsake tabelis FactSales (TegelikMüük) veergu Kasum.

  2. Valige Arvutused > Automaatsumma. Nagu näete, luuakse arvutusalas otse veeru Profit (Kasum) all automaatselt uus väli Veeru Kasum summa.

  3. Klõpsake Exceli lehel 1 väljaloendi jaotises FactSales (TegelikMüük) väärtust Veeru Kasum summa.

Ongi kõik! Nii lihtne ongi arvutatud välja loomine Power Pivoti standardsete liitmisfunktsioonide abil. Nagu näete, olete kõigest mõne minutiga loonud arvutatud välja „Veeru Kasum summa“ ja lisanud selle oma PivotTable-liigendtabelisse, mis lihtsustab kasumi analüüsimist vastavalt rakendatud filtritele. Käesoleval juhul näete välja „Veeru Kasum summa“ filtreerituna hierarhiate Tootekategooria ja Kuupäevad alusel.

Mida aga teha juhul, kui vajate üksikasjalikumat analüüsi, näiteks kindla müügikanali kaudu tehtud või mõne kindla toote või kategooria müükide arvu? Selleks peate looma uue arvutatud välja, mis loendab ridade arvu, üks rida iga tabelis FactSales (TegelikMüük) leiduva müügi kohta, sõltuvalt rakendatud filtritest.

  1. Klõpsake tabelis FactSales (TegelikMüük) veergu SalesKey (Müügivõti).

  2. Klõpsake jaotises Arvutused nupu Automaatsumma allanoolt ja siis käsku Loendus.

  3. Nimetage uus arvutatud väli ümber, paremklõpsates arvutusalal välja Veeru SalesKey loendus ja klõpsates siis käsku Nimeta ümber. Tippige uueks nimeks Arv ja vajutage siis sisestusklahvi (Enter).

  4. Klõpsake Exceli lehel 1 väljaloendi jaotises FactSales (TegelikMüük) väärtust Arv.

Nagu näete, on PivotTable-liigendtabelisse lisatud uus veerg Arv, kus on näha müükide arv sõltuvalt sellest, millised filtrid on rakendatud. Sarnaselt arvutatud väljaga "Veeru Kasum summa" on ka väli Arv siinses näites filtreeritud hierarhiate Tootekategooria ja Kuupäevad alusel.

Loogem veel üks väli. Seekord tuleb teil luua arvutatud väli, mis arvutab protsendi kogumüügist konkreetse konteksti või filtri korral. Erinevalt varasematest arvutatud väljadest, mille loomiseks kasutasite automaatsumma funktsiooni, tuleb teil seekord käsitsi valem sisestada.

  1. Klõpsake tabelis FactSales (TegelikMüük) arvutusalas tühja lahtrit. Näpunäide: ülemine vasakpoolne lahter on arvutatud väljade paigutamisega alustamiseks enamasti parim koht. Sel viisil on välju kergem üles leida. Arvutatud väljad saate igal ajal arvutusalasse teisaldada.

  2. Tippige valemiribale (IntelliSense'i kasutades) järgmine valem: Percentage of All Products:=[Count]/CALCULATE([Count], ALL(DimProduct))

  3. Valemi kinnitamiseks vajutage sisestusklahvi (ENTER).

  4. Klõpsake Exceli lehel 1 väljaloendi jaotises FactSales (TegelikMüük) väärtust Protsent kõigist toodetest.

  5. Valige PivotTable-liigendtabelis kõik veerud Protsent kõigist toodetest.

  6. Klõpsake menüüs Avaleht nuppu Arv ja siis väärtust Protsent. Kasutage iga uue veeru vormindamiseks kahte komakohta.

See uus arvutatud väli arvutab protsendi kogumüügist vastava filtri kontekstis. Käesoleval juhul on meie filtrikontekstiks endiselt hierarhiad Tootekategooria ja Kuupäevad. Muu hulgas näete näiteks seda, et arvutite müük (protsendina kõigi toodete müügist kokku) on aastate jooksul kasvanud.

Valemite loomine nii arvutatud veergude kui ka arvutatud väljade jaoks on üsna lihtne, kui olete Exceli valemite loomisega juba tuttav. Sõltumata sellest, kas olete Exceli valemitega tuttav või mitte, on DAX-valemite loomisega tutvumisel suureks abiks teema Kiirhäälestus: õppige DAX-i põhiteadmisi 30 minutiga õppetükid.

Tehtud töö salvestamine

Salvestage oma töövihik, et saaksite seda kasutada ka muude õppetükkide jaoks või edasiseks uurimiseks.

Järgmised sammud

Kuigi saate andmeid Excelist hõlpsasti importida, käib importimine Power Pivoti lisandmooduli kaudu tihti kiiremini ja tõhusamalt. Saate imporditavaid andmeid filtreerida ja mittevajalikud veerud välja jätta. Samuti saate valida, kas kasutada andmete toomiseks päringukoosturit või päringukäsku. Järgmiseks võiksitegi nende alternatiivsete võimalustega lähemalt tutvust teha: Andmekanalist andmete toomine lisandmoodulis Power Pivot ja Andmete importimine analüüsiteenustest või lisandmoodulist Power Pivot.

Power View’ aruandlus on loodud töötamiseks selliste andmemudelitega, nagu te just koostasite. Järgmistes teemades saate õppida, kuidas Excelis Power View’ abil põhjalikumalt ja rikkalikumalt andmeid visualiseerida: Power View’ käivitamine Excel 2013-s ja Power View: andmete uurimine, visualiseerimine ja esitlemine.

Proovige oma andmemudelit paremate Power View aruannete loomiseks rikastada. Selleks lugege järgmist artiklit: Õpetus: andmemudeli optimeerimine Power View aruandluse jaoks.

Täiendage oma oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×