Telkimai papildinyje „PowerPivot“

Agregavimas yra duomenų sutraukimo, apibendrinimo arba grupavimo būdas. Kai pradedate nuo neapdorotų duomenų iš lentelių ar kitų duomenų šaltinių, duomenys dažnai būna pakloti, o tai reiškia, kad yra daug detalių, tačiau jos nėra kaip nors sutvarkytos ar sugrupuotos. Dėl suvestinių ar struktūros trūkumo gali būti sunku aptikti duomenų modelius. Svarbi duomenų modeliavimo dalis yra apibrėžti agregavimus, kurie supaprastina, abstrahuoja arba apibendrina modelius, atsakant į konkretų verslo klausimą.

Dažniausiai pasitaikančius agregavimus, pvz., naudojamus AVERAGE,COUNT,DISTINCTCOUNT, MAX, MIN arba SUM, matas gali būti kuriamas automatiškai naudojant automatinę sudėtį. Kitų tipų agregavimai, pvz., AVERAGEX, COUNTX, COUNTROWS arba SUMX, pateikia lentelę ir reikalauja formulės, sukurtos naudojant duomenų analizės išraiškas (DAX).

"Power Pivot" telkimų supratimas

Agregavimo grupių pasirinkimas

Kai agreguojate duomenis, grupuojate duomenis pagal atributus, pvz., produktas, kaina, regionas ar data, tada apibrėžiate formulę, kuri veikia su visais grupės duomenimis. Pavyzdžiui, kurdami bendrą metų sumą, sukuriate agregavimą. Jei tada sukursite šių metų santykį su praėjusiais metais ir pateiksite juos kaip procentines reikšmes, tai bus kitoks agregavimo tipas.

Sprendimas, kaip grupuoti duomenis, priklauso nuo verslo klausimo. Pavyzdžiui, telkimai gali atsakyti į šiuos klausimus:

Skaičiuoja Kiek operacijų buvo per mėnesį?

Vidurkiai Koks buvo šio mėnesio pardavimų vidurkis pagal pardavėjus?

Mažiausios ir didžiausios reikšmės Kurie pardavimo rajonai buvo penki geriausi pagal parduotų vienetų skaičių?

Norėdami sukurti skaičiavimą, kuris atsako į šiuos klausimus, turite turėti išsamius duomenis su skaičiais, kuriuos reikia suskaičiuoti arba sudėti, ir tie skaitiniai duomenys turi būti kokiu nors būdu susieti su grupėmis, kurias naudosite tvarkydami rezultatus.

Jei duomenyse dar nėra reikšmių, kurias galėtumėte naudoti grupavimui, pvz., produkto kategorijos arba geografinio regiono, kuriame yra parduotuvė, pavadinimo, galbūt norėsite į duomenis įtraukti grupes. Kai kuriate grupes programoje "Excel", turite rankiniu būdu įvesti arba pasirinkti grupes, kurias norite naudoti, iš darbalapio stulpelių. Tačiau santykinėje sistemoje hierarchijos, pvz., produktų kategorijos, dažnai saugomos kitoje lentelėje, o ne faktų ar reikšmių lentelėje. Paprastai kategorijų lentelė su faktiniais duomenimis susiejama tam tikru raktu. Tarkime, kad jūsų duomenyse yra produktų ID, bet ne produktų pavadinimai arba jų kategorijos. Norėdami įtraukti kategoriją į plokščiąjį "Excel" darbalapį, nukopijuokite stulpelį, kuriame yra kategorijų pavadinimai. Naudodami "Power Pivot" galite importuoti produktų kategorijų lentelę į duomenų modelį, sukurti lentelės su skaitiniais duomenimis ir produktų kategorijų sąrašo ryšį ir naudoti kategorijas duomenims grupuoti. Daugiau informacijos rasite ryšio tarp lentelių kūrimas.

Agregavimo funkcijos pasirinkimas

Nustačius ir įtraukus naudotinas grupes, turite nuspręsti, kurias matematines funkcijas naudoti agreguojant. Dažnai žodis agregavimo vartojamas kaip matematinių ar statistinių operacijų, naudojamų agreguojant, pvz., sumų, vidurkių, minimumo ar skaičiavimų, sinonimas. Tačiau "Power Pivot" leidžia kurti pasirinktines agregavimo formules, greta standartinių agregavimų, randamų "PowerPivot" ir "Excel".

Pvz., turėdami tą patį reikšmių ir grupių rinkinį, kuris buvo naudotas ankstesniuose pavyzdžiuose, galite sukurti pasirinktinius agregavimus, kurie atsako į šiuos klausimus:

Filtruoti skaičiai Kiek operacijų buvo per mėnesį, neįskaitant priežiūros mėnesio pabaigos lango?

Santykiai naudojant vidurkius per tam tikrą laiką Koks buvo procentinis pardavimų augimas ar sumažėjimas, palyginti su tuo pačiu laikotarpiu pernai?

Sugrupuotos minimalios ir didžiausios reikšmės Kurie pardavimo rajonai buvo įvertinti geriausiai pagal kiekvieną produktų kategoriją ar kiekvieną pardavimo skatinimą?

Agregavimų įtraukimas į formules ir "PivotTables"

Kai suprantate, kaip turėtų būti grupuojami jūsų duomenys, kad jie būtų prasmingi, ir su kokiomis reikšmėmis norite dirbti, galite nuspręsti, ar kurti "PivotTable", ar kurti skaičiavimus lentelėje. "PowerPivot" išplečia ir pagerina vietines "Excel" galimybes kurti agregavimus, pvz., sumas, skaičiuoja ar vidurkius. Galite kurti pasirinktinius agregavimus "Power Pivot" lange arba "Excel" "PivotTable" srityje.

• Apskaičiuotame stulpelyje galite kurti agregavimus, kurie atsižvelgia į dabartinį eilutės kontekstą, kad gautumėte susijusias eilutes iš kitos lentelės, ir tada sudėtumėte, suskaičiuotumėte ar vidurkį gautumėte tas reikšmes susijusiose eilutėse.
• Matas gali kurti dinaminius agregavimus, naudojančius tiek filtrus, apibrėžtus formulėje, tiek filtrus, nustatytus pagal "PivotTable" dizainą ir duomenų filtrų, stulpelių antraščių ir eilučių antraščių pasirinkimą. Matai naudojant standartinius agregavimus gali būti sukurti "PowerPivot" naudojant automatinę sudėtį arba sukuriant formulę. Taip pat galite sukurti netiesioginius matus naudodami standartinius agregavimus "PivotTable" programoje "Excel".

Grupavimo įtraukimas į "PivotTable"

Kurdami "PivotTable", laukus, vaizduojančius grupavimus, kategorijas ar hierarchijas, vilkite į "PivotTable" stulpelių ir eilučių sritį, kad sugrupuotumėte duomenis. Tada laukus, kuriuose yra skaitinių reikšmių, vilkite į reikšmių sritį, kad juos būtų galima skaičiuoti, apskaičiuoti jų vidurkį arba sudėti.

Jei įtraukiate kategorijas į "PivotTable", bet kategorijos duomenys nėra susiję su faktiniais duomenimis, galite gauti klaidą arba netinkamus rezultatus. Paprastai "Power Pivot" bandys išspręsti problemą automatiškai aptikdama ir siūlydama ryšius. Daugiau informacijos rasite "Darbas su "PivotTable" ryšiais.

Taip pat galite vilkti laukus į duomenų filtrus, kad pasirinktumėte peržiūrėti tam tikras duomenų grupes. Duomenų filtrai leidžia interaktyviai grupuoti, rūšiuoti ir filtruoti rezultatus "PivotTable".

Darbas su grupėmis formulėje

Taip pat grupavimą ir kategorijas galite naudoti norėdami agreguoti lentelėse saugomus duomenis sukurdami lentelių ryšius, tada kurdami formules, kurios išnaudoja tuos ryšius susijusioms reikšmėms ieškoti.

Kitaip tariant, jei norite sukurti formulę, kuri grupuoja reikšmes pagal kategoriją, pirmiausia turite naudoti ryšį, kad sujungtumėte lentelę su išsamiais duomenimis ir lenteles su kategorijomis, tada sukurkite formulę.

Daugiau informacijos apie formulių, kurios naudoja peržvalgas, kūrimą rasite dalyje "Power Pivot" formulių peržvalgos.

Filtrų naudojimas agregavimams

Nauja "Power Pivot" funkcija – galimybė taikyti filtrus duomenų stulpeliams ir lentelėms ne tik vartotojo sąsajoje ir "PivotTable" arba diagramoje, bet ir pačiose formulėse, kurias naudojate agregavimams apskaičiuoti. Filtrus galima naudoti formulėse ir apskaičiuojamuosiuose stulpeliuose, ir langeliuose.

Pvz., naujosiose DAX agregavimo funkcijose užuot nurodę reikšmes, kurioms reikia sumuoti ar skaičiuoti, galite nurodyti visą lentelę kaip argumentą. Jei lentelei nepritaikysite jokių filtrų, funkcija agregavimo veiks su visomis reikšmėmis nurodytame lentelės stulpelyje. Tačiau DAX galite sukurti dinaminį arba statinį lentelės filtrą, kad agregavimas veiktų su skirtingais duomenų poaibiais, atsižvelgiant į filtro būklę ir dabartinį kontekstą.

Formulėse derindami sąlygas ir filtrus galite kurti agregavimus, kurie kinta priklausomai nuo formulėse pateiktų reikšmių arba kurie kinta priklausomai nuo "PivotTable" eilučių, antraščių ir stulpelių antraščių pasirinkimo.

Daugiau informacijos rasite Duomenų filtravimas formulėse.

"Excel" agregavimo funkcijų ir DAX agregavimo funkcijų palyginimas

Šioje lentelėje išvardytos kai kurios standartinės agregavimo funkcijos, kurias teikia "Excel", ir pateikiami šių funkcijų diegimo "Power Pivot" saitai. Šių funkcijų DAX versija veikia panašiai kaip "Excel" versija, yra nedidelių sintaksės ir tam tikrų duomenų tipų tvarkymo skirtumų.

Standard Agregavimo funkcijos

Funkcija	Naudokite
AVERAGE	Grąžina visų skaičių stulpelyje vidurkį (aritmetinį vidurkį).
AVERAGEA	Grąžina visų stulpelio reikšmių vidurkį (aritmetinį vidurkį). Apdoroja teksto ir ne skaitines reikšmes.
COUNT	Suskaičiuoja skaitinių reikšmių skaičių stulpelyje.
COUNTA	Apskaičiuoja, kiek stulpelyje yra reikšmių, kurios nėra tuščios.
MAX	Grąžina didžiausią skaitinę reikšmę stulpelyje.
MAXX	Pateikia didžiausią reikšmę iš reiškinių rinkinio, įvertinto lentelėje.
MIN	Grąžina mažiausią skaitinę reikšmę stulpelyje.
MINX	Pateikia mažiausią reikšmę iš reiškinių rinkinio, įvertinto lentelėje.
SUM	Sudeda visus skaičius stulpelyje.

DAX agregavimo funkcijos

DAX turi agregavimo funkcijas, kurios leidžia nurodyti lentelę, kurioje bus atliekamas agregavimas. Todėl užuot tiesiog pridėjus ar nustačius vidurkį stulpelyje, šios funkcijos leidžia sukurti reiškinį, kuris dinamiškai apibrėžia agreguotinus duomenis.

Šioje lentelėje pateikiamos agregavimo funkcijos, galimos DAX.

Funkcija	Naudokite
AVERAGEX	Išveda reiškinių rinkinio, įvertinto lentelėje, vidurkį.
MOKESČIAI	Skaičiuoja reiškinių, įvertintų visoje lentelėje, rinkinį.
COUNTBLANK	Suskaičiuoja, kiek stulpelyje yra tuščių reikšmių.
COUNTX	Suskaičiuoja bendrą lentelės eilučių skaičių.
COUNTROWS	Suskaičiuoja, kiek eilučių grąžinta iš įdėtosios lentelės funkcijos, pvz., funkcijos FILTER.
SUMX	Pateikia reiškinių rinkinio, įvertinto lentelėje, sumą.

DAX ir "Excel" skirtumai Agregavimo funkcijos

Nors šių funkcijų pavadinimai tokie patys kaip ir jų "Excel" atitikmenų, jos naudoja "Power Pivot" atminties analizės modulį ir buvo perrašytos veikti su lentelėmis ir stulpeliais. DAX formulės negalima naudoti "Excel" darbaknygėje ir atvirkščiai. Juos galima naudoti tik "Power Pivot" lange ir "PivotTable", pagrįstose "Power Pivot" duomenimis. Be to, nors funkcijų pavadinimai identiški, veikimas gali šiek tiek skirtis. Daugiau informacijos ieškokite atskirose funkcijų nuorodų temose.

Stulpelių įvertinimas agregavus taip pat skiriasi nuo to, kaip programa "Excel" agreguoja. Pavyzdys gali padėti iliustruoti.

Tarkime, norite gauti reikšmių, esančių lentelės Pardavimai stulpelyje Suma, sumą, todėl sukūrėte šią formulę:

=SUM('Sales'[Amount])

Paprasčiausiu atveju funkcija gauna reikšmes iš vieno nefiltruoto stulpelio, o rezultatas yra toks pat kaip programoje "Excel", kuri tiesiog sudeda stulpelio Kiekis reikšmes. Tačiau "Power Pivot" formulė interpretuojama taip: "Gaukite kiekvienos lentelės Pardavimas eilutės sumos reikšmę ir sudėkite tas atskiras reikšmes. "PowerPivot" įvertina kiekvieną eilutę, kurioje atliekamas agregavimas, ir kiekvienai eilutei apskaičiuoja vieną skaliarinę reikšmę, o tada agreguoja tas reikšmes. Todėl formulės rezultatas gali skirtis, jei lentelei buvo pritaikyti filtrai arba jei reikšmės skaičiuojamos pagal kitus agregavimus, kurie gali būti filtruojami. Daugiau informacijos žr. DAX formulių kontekstas.

DAX laiko informacijos funkcijos

Be ankstesniame skyriuje aprašytų lentelių agregavimo funkcijų, DAX turi agregavimo funkcijas, kurios veikia su jūsų nurodytomis datomis ir laikais, kad būtų galima teikti įtaisytąją laiko informaciją. Šios funkcijos naudoja datų diapazonus, kad gautų susijusias reikšmes ir agreguotų reikšmes. Taip pat galite palyginti reikšmes visuose datų diapazonuose.

Šioje lentelėje pateikiamos laiko informacijos funkcijos, kurios gali būti naudojamos agreguojant.

Funkcija	Naudokite
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR	Apskaičiuoja reikšmę pateikto laikotarpio kalendorinėje pabaigoje.
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR	Apskaičiuoja reikšmę laikotarpio, buvusio prieš nurodytą laikotarpį, kalendorinę pabaigą.
TOTALMTD TOTALYTD TOTALQTD	Apskaičiuoja intervalo, kuris prasideda pirmąją laikotarpio dieną ir baigiasi vėliausia nurodyto laikotarpio stulpelio data, reikšmę.

Kitos laiko informacijos funkcijų skyriaus funkcijos (laiko informacijos funkcijos) – tai funkcijos, kurias galima naudoti norint gauti datas arba pasirinktinius datų diapazonus, skirtus naudoti agreguojant. Pavyzdžiui, galite naudoti funkciją DATESINPERIOD datų diapazonui grąžinti ir tą datų rinkinį naudoti kaip argumentą kitai funkcijai pasirinktiniam agregavimui apskaičiuoti.