Applies ToMicrosoft 365 rakendus Excel Excel 2024 Excel 2021 Excel 2019 Excel 2016

Liitmised on andmete kokkupaneku, 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 levinumad liitmised, näiteks need, mis kasutavad funktsiooni AVERAGE, COUNT, DISTINCTCOUNT, MAX, MIN või SUM , saab automaatsumma abil luua mõõdus automaatselt. Muud tüüpi liitmised (nt AVERAGEX, COUNTX, COUNTROWS või SUMX) tagastavad tabeli ja nõuavad andmeanalüüsi avaldiste (DAX) abil loodud valemit.

Ülevaade Power Pivoti liitmisfunktsioonidest

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.

Loendamised   Mitu tehingut kuu jooksul tehti?

Keskmised    Milline oli keskmine müük vastaval kuul müügiesindaja kohta?

Miinimum- ja maksimumväärtused    Millised müügipiirkonnad olid müüdud üksuste puhul esimesel viiel kohal?

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 Pivotiga saate importida tootekategooriate tabeli oma andmemudelisse, luua seose arvandmetega tabeli ja tootekategooriate loendi vahel ning kasutada seejärel 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). Power Pivot võimaldab aga luua liitmistevõimaluste jaoks kohandatud valemid lisaks Power Pivotis ja Excelis kasutatavatele standardsetele liitmistele.

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

Filtreeritud loendamised   Kui palju tehinguid kuu jooksul toimus, arvestamata kuu lõpu hooldusperioodi?

Suhtarvud, kasutades ajas keskmisi    Milline oli kasvuprotsent või müügi vähenemine võrreldes sama perioodiga eelneval aastal?

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

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 täiustab Exceli võimalusi liitmistehete (nt summade, loenduste või keskmiste) arvutamiseks. 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. Power Pivotis saab funktsiooniga AutoSum abil või valemit koostades luua standardliitmisi kasutavaid mõõte. 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 püüab Power Pivot probleemi kõrvaldada, 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 andmeid sisaldavatele veergudele ja tabelitele mitte ainult kasutajaliideses ja PivotTable-liigendtabelis või diagrammis, vaid ka otse valemites, mida te liitmisarvutuste jaoks kasutate. 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 ära toodud teatud Exceli standardsed liitmisfunktsioonid ja antud 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

KESKMINE

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.

SUMMA

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 samad nimed nagu vastavatel Exceli funktsioonidel, kasutavad nad Power Pivoti mälusisesest analüüsimootorit ning on kujundatud ümber töötamiseks tabelite ja veergudega. 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 valemit aga kui käsku „too väärtus veerust Amount iga rea kohta tabelis Sales ja liida siis need eraldiseisvad väärtused”. Power Pivot hindab iga rida, mille kohta liitmine toimub, ning arvutab ühe skalaarväärtuse iga rea kohta ja seejärel liidab 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.

AVAMISKUU

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.

Muud funktsioonid jaotises Ajateabe funktsioon (Ajateabefunktsioonid) on funktsioonid, mida saab kasutada liitmisel 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.

Kas vajate veel abi?

Kas soovite rohkem valikuvariante?

Siin saate tutvuda tellimusega kaasnevate eelistega, sirvida koolituskursusi, õppida seadet kaitsma ja teha veel palju muud.

Kogukonnad aitavad teil küsimusi esitada ja neile vastuseid saada, anda tagasisidet ja saada nõu rikkalike teadmistega asjatundjatelt.