Liitmised Power Pivotis

Liitmised on andmete ahendamise, summeerimise või rühmitamise viis. Kui alustate toorandmetega tabelitest või muudest andmeallikatest, on andmed sageli ühetaolised, mis tähendab, et üksikasju on palju, kuid andmeid pole kuidagi organiseeritud ega rühmitatud. See kokkuvõtete või struktuuri puudumine võib muuta andmemustrite tuvastamise keeruliseks. Andmete modelleerimise oluline osa selliste liitmiste määratlemine, mis mustreid kindlale äriküsimusele vastuse andmiseks lihtsustavad, neist ülevaate annavad või neid summeerivad.

Kõige tavalisemad liitmised, nt sellised, mis kasutavad funktsioone AVERAGE, COUNT, DISTINCTCOUNT, MAX, MIN või SUM, saab luua AutoSumi abil automaatselt mõõdus. Muud tüüpi liitmised (nt AVERAGEX, COUNTX, COUNTROWS või SUMX ) tagastavad tabeli ja nõuavad andmeanalüüsi avaldiste (DAX) abil loodud valemit.

Liitmiste mõistmine Power Pivotis

Rühmade valimine liitmiseks

Andmete liitmisel rühmitate andmed atribuutide kaupa, nt toode, hind, piirkond või kuupäev, ja määratlete seejärel valemi, mis toimib kogu rühma andmetel. Näiteks kui loote aasta kogusumma, tekitate liitmise. Kui loote seejärel selle aasta osakaalu eelneva aasta suhtes ning esitate need protsentidena, on see teist tüüpi liitmine.

Otsus, kuidas andmeid rühmitada, tuleneb äriküsimusest. Näiteks suudavad liitmised vastata järgmistele küsimustele.

Loeb Kui palju tehinguid kuuga tehti?

Keskmised Milline oli keskmine müük sellel kuul müügiesindaja järgi?

Miinimum- ja maksimumväärtused Millised müügipiirkonnad olid müüdud üksuste poolest viis esimest?

Arvutuse loomiseks, mis neile küsimustele vastab, peavad teil olema üksikasjalikud andmed, mis sisaldavad loendatavaid või summeeritavaid arve, ja need arvandmed peavad olema teatud viisil seotud rühmadega, mida tulemuste organiseerimiseks kasutate.

Kui andmed ei sisalda väärtusi, mida saab rühmitamiseks kasutada (nt tootekategooria või geograafilise piirkonna nimi, kus kauplus asub), võib olla vaja andmetele kategooriate lisamisega rühmad määrata. Excelis rühmade loomisel tuleb käsitsi tippida või valida rühmad, mida soovite kasutada, töölehe veergude hulgast. Relatsioonsüsteemis talletatakse hierarhiad (nt tootekategooriad) aga sageli fakti- või väärtusetabelist erinevas tabelis. Kategooriate tabel on faktitabeliga enamasti teatud võtme kaudu lingitud. Oletagem näiteks, et teie andmetes on olemas tootekoodid, kuid puuduvad toodete nimed või nende kategooriad. Kategooria lisamiseks tavalisele Exceli töölehele tuleks kopeerida sinna kategooriate nimesid sisaldav veerg. Power Pivoti abil saate importida tootekategooriate tabeli oma andmemudelisse, luua arvuandmetega tabeli ja tootekategooriate loendi vahel seose ning seejärel kasutada kategooriaid andmete rühmitamiseks. Lisateavet leiate teemast Tabelite vahel seoste loomine .

Funktsiooni valimine liitmiseks

Pärast kasutatavate rühmituste tuvastamist ja lisamist peate otsustama, milliseid matemaatilisi funktsioone liitmiseks kasutada. Sageli kasutatakse sõna „liitmine” sünonüümina matemaatiliste või statistiliste toimingute kohta, mida liitmistes kasutatakse (nt summad, keskmised, miinimum või loendused). Kuid Power Pivot võimaldab teil lisaks Power Pivotis ja Excelis leiduvatele tavalistele liitmisfunktsioonidele luua liitmiseks kohandatud valemeid.

Näiteks saaksite samade väärtuste ja rühmitustega, mida kasutati eelmistes näidetes, luua kohandatud liitmisi, mis vastavad järgmistele küsimustele.

Filtreeritud arvud Kui palju tehinguid kuu jooksul tehti, jättes välja kuu lõpu hooldusakna?

Suhtarvud, mis kasutavad aja jooksul keskmisi Milline oli kasvuprotsent või müügi vähenemine võrreldes eelmise aasta sama perioodiga?

Rühmitatud miinimum- ja maksimumväärtused Millised müügipiirkonnad olid parimad iga tootekategooria või müügikampaania kohta?

Liitmiste lisamine valemitele ja PivotTable-liigendtabelitele

Kui teate üldjoontes, kuidas peaks andmeid sisukuse huvides rühmitama ja milliste väärtustega soovite tegelda, saate otsustada, kas koostada PivotTable-liigendtabel või luua arvutused tabelis. Power Pivot laiendab ja parandab Exceli võimet luua liitmisi (nt summasid, loendusi või keskmisi). Kohandatud liitmisi saate Power Pivotis luua kas Power Pivoti aknas või Exceli PivotTable-liigendtabeli alas.

• Saate arvutatud veerus koostada liitmisi, mis arvestavad praeguse rea konteksti seotud ridade toomiseks teisest tabelist ja seejärel summeerida, loendada või võtta nende väärtuste keskmine seotud ridadel.
• Saate mõõdus koostada dünaamilisi liitmisi, mis kasutavad nii neid filtreid, mis on valemis määratud, kui ka neid, mis on rakendatud PivotTable’i kujunduse ja tükeldite valiku, veerupealkirjade ning reapealkirjadega. Standardseid liitmisi kasutavate mõõtude loomiseks saab kasutada Power Pivoti automaatsumma funktsiooni või valemit. Samuti saate luua peidetud mõõte, kasutades Exceli PivotTable-liigendtabelis standardliitmisi.

Rühmituste lisamine PivotTable-liigendtabelitele

PivotTable’i kujundamisel lohistate rühmitusi, kategooriaid või hierarhiaid kajastavad väljad andmete rühmitamiseks PivotTable’i veergude ja ridade ossa. Seejärel lohistate arvväärtusi sisaldavad väljad väärtuste alale, et neid saaks loendada, nende keskmist arvutada või neid summeerida.

Kui lisate PivotTable-liigendtabelile kategooriaid, kuid kategooriate andmed pole faktiandmetega seotud, võite saada tõrke või imelikud tulemused. Tavaliselt proovib Power Pivot probleemi lahendada, tuvastades ja soovitades seoseid automaatselt. Lisateavet leiate artiklist PivotTable-liigendtabelites seostega töötamine.

Saate lohistada välju ka tükelditesse, et valida vaatamiseks teatud andmerühmi. Tükeldid lubavad teil interaktiivselt tulemusi PivotTable’is rühmitada, sortida ja filtreerida.

Rühmitustega töötamine valemis

Saate kasutada rühmitusi ja kategooriaid ka tabelites talletatud andmete liitmiseks, luues tabelite vahel seoseid ja seejärel valemeid, mis nende seoste abil seotud väärtusi otsivad.

Teisisõnu, kui soovite luua valemi, mis rühmitab väärtused kategooriate kaupa, peaksite esmalt kasutama seost üksikasjalikke andmeid sisaldava tabeli ühendamiseks kategooriaid sisaldavate tabelitega ning koostama seejärel valemi.

Lisateavet otsingut kasutavate valemite koostamise kohta vt teemast Otsingud PowerPivoti valemites.

Filtrite kasutamine liitmistes

Power Pivoti uus funktsioon on võimalus rakendada filtreid andmeveergudele ja -tabelitele mitte ainult kasutajaliideses, PivotTable-liigendtabelis või diagrammis, vaid ka valemites, mida kasutate liitmiste arvutamiseks. Filtreid saab valemites kasutada nii arvutuslikes veergudes kui ka mõõtudes.

Näiteks saate uute DAX-i liitmisfunktsioonide puhul summeeritavate või loendatavate väärtuste täpsustamise asemel määrata argumendiks terve tabeli. Kui tabelile filtreid rakendatud pole, toimib liitmisfunktsioon kõigi tabeli vastavas veerus olevate väärtuste suhtes. Kuid DAX-is saate luua tabelile dünaamilise või staatilise filtri, et liitmisel arvestataks teistsugust andmete alamkogumit, olenevalt filtri tingimusest ja jooksvast kontekstist.

Tingimuste ja filtrite kombineerimisega valemites saate luua liitmisi, mis muutuvad olenevalt valemites antud väärtustest või rea- ja veerupealkirjade valikust PivotTable’is.

Lisateavet vt teemast Andmete filtreerimine valemites.

DAX-i ja Exceli liitmisfunktsioonide võrdlus

Järgmises tabelis on loetletud mõned Exceli standardsed liitmisfunktsioonid ja lingid nende funktsioonide rakendamiseks Power Pivotis. Nende funktsioonide DAX-i versioon toimib suuresti sarnaselt Exceli versiooniga teatud pisierinevustega süntaksis ja teatud andmetüüpide käsitlemisel.

Standardsed liitmisfunktsioonid

Funktsioon	Tulemus
AVERAGE	Tagastab veeru kõigi arvude keskmise (aritmeetilise keskmise).
AVERAGEA	Tagastab veeru kõikide väärtuste keskmise (aritmeetilise keskmise). Töötleb teksti ja mittearvväärtusi.
COUNT	Loendab veerus olevate arvväärtuste arvu.
COUNTA	Loendab mittetühjade väärtuste arvu veerus.
MAX	Tagastab veeru suurima arvulise väärtuse.
MAXX	Tagastab suurima väärtuse tabelis hinnatud avaldiste kogumist.
MIN	Tagastab veeru vähima arvulise väärtuse.
MINX	Tagastab vähima väärtuse tabelis hinnatud avaldiste kogumist.
SUM	Liidab kõik veerus olevad arvud.

DAX-i liitmisfunktsioonid

DAX-is on liitmisfunktsioonid, mis lubavad määrata tabeli, kus liitmistoiming tehakse. Seetõttu lubavad need funktsioonid veerus väärtuste liitmise või keskmise arvutamise asemel luua avaldise, mis määrab dünaamiliselt liidetavad andmed.

Järgmises tabelis loetletakse DAX-is saadaolevad liitmisfunktsioonid.

Funktsioon	Tulemus
AVERAGEX	Arvutab tabelis hinnatavate avaldiste kogumi keskmise.
COUNTAX	Loendab tabelis hinnatavate avaldiste kogumit.
COUNTBLANK	Loendab veerus olevate tühjade väärtuste arvu.
COUNTX	Loendab ridade koguarvu tabelis.
COUNTROWS	Loendab ridade arvu, mis on saadud pesastatud tabelifunktsioonist, nt filtrifunktsioonist.
SUMX	Tagastab tabelis hinnatud avaldiste kogumi summa.

DAX-i ja Exceli liitmisfunktsioonide erinevused

Kuigi nendel funktsioonidel on Exceli omadega samad nimed, kasutavad nad Power Pivoti mälusisese analüüsimootorit ning need on tabelite ja veergudega töötamiseks ümber kirjutatud. Te ei saa kasutada DAX-i valemit Exceli töövihikus ega vastupidi. Neid saab kasutada ainult Power Pivoti aknas ja Power Pivoti andmetel põhinevates PivotTable-liigendtabelites. Kuigi funktsioonidel on samad nimed, võivad nad ka veidi erinevalt toimida. Lisateavet leiate vastavate funktsioonide spikriartiklitest.

Veergude hindamine liitmisel erineb samuti sellest, kuidas Excel liitmiste puhul toimib. Näide võib aidata seda illustreerida.

Oletame, et soovite väärtuste summat tabeli Sales veerus Amount, seega koostate järgmise valemi.

=SUM('Sales'[Amount])

Lihtsaimal juhul toob funktsioon väärtused ühest filtreerimata veerust ning tulemus on sama, mis Excelis, kus väärtused veerus Amount alati lihtsalt liidetakse. Power Pivotis tõlgendatakse aga valemit nii: "Too väärtus tabeli Müük iga rea kohta väljale Summa ja seejärel liitke need üksikud väärtused. Power Pivot hindab igat rida, mille üle liitmine toimub, ja arvutab iga rea jaoks ühe skalaarväärtuse ning seejärel liidstab need väärtused. Seetõttu võib valemi tulemus erineda, kui tabelile on rakendatud filtrid või kui väärtused arvutatakse teiste liitmiste põhjal, mis võivad olla filtreeritud. Lisateavet leiate artiklist DAX-i valemite kontekst.

DAX-i ajateabe funktsioonid

Peale tabeli liitmise funktsioonide, mida eelmises jaotises kirjeldati, on DAX-il liitmisfunktsioonid, mis toimivad teie määratud kuupäevade ja kellaaegadega, pakkudes sisseehitatud ajateavet. Need funktsioonid kasutavad kuupäevade vahemikke seotud väärtuste toomiseks ja väärtuste liitmiseks. Saate ka võrrelda väärtusi kuupäevavahemike alusel.

Järgmises tabelis loetletakse ajateabe funktsioonid, mida liitmiseks kasutada saab.

Funktsioon	Tulemus
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR	Arvutab antud perioodi lõpu kalendriväärtuse.
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR	Arvutab antud perioodile eelneva kalendriväärtuse perioodi.
TOTALMTD TOTALYTD TOTALQTD	Arvutab intervalli väärtuse, mis algab perioodi esimesel päeval ja lõpeb määratud kuupäevaveeru hiliseimal kuupäeval.

Teised funktsioonid ajateabe funktsiooni jaotises (Ajateabe funktsioonid) on funktsioonid, mida saab kasutada liitmises kasutatavate kuupäevade või kohandatud kuupäevavahemike toomiseks. Näiteks saate funktsiooni DATESINPERIOD kasutada kuupäevade vahemiku toomiseks ja kasutada seda kui päevade kogumit argumendina teise funktsiooni jaoks just nende kuupäevade kohandatud liitmiseks.