Kontekstas leidžia atlikti dinaminę analizę, kurioje formulės rezultatai gali keistis, kad atspindėtų dabartinę pasirinktą eilutę ar langelį ir visus susijusius duomenis. Konteksto supratimas ir veiksmingas konteksto naudojimas yra labai svarbūs kuriant efektyvias formules, atliekant dinamines analizes ir šalinant formulių problemas.
Šiame skyriuje apibrėžiami skirtingi konteksto tipai: eilutės kontekstas, užklausos kontekstas ir filtro kontekstas. Paaiškinama, kaip vertinamas apskaičiuotųjų stulpelių ir "PivotTable" formulių kontekstas.
Paskutinėje šio straipsnio dalyje pateikiami saitai su išsamiais pavyzdžiais, kurie iliustruoja, kaip kinta formulių rezultatai atsižvelgiant į kontekstą.
Konteksto supratimas
"Power Pivot" formulėms įtakos gali turėti "PivotTable" taikomi filtrai, lentelių ryšiai ir formulėse naudojami filtrai. Kontekstas yra tai, kas leidžia atlikti dinaminę analizę. Konteksto supratimas yra svarbus kuriant formules ir šalinant jų triktis.
Yra įvairių tipų kontekstas: eilutės kontekstas, užklausos kontekstas ir filtro kontekstas.
Eilutės kontekstą galima suvokti kaip "dabartinę eilutę". Jei sukūrėte apskaičiuotą stulpelį, eilutės kontekstą sudaro reikšmės kiekvienoje atskiroje eilutėje ir reikšmės stulpeliuose, susijusiuose su dabartine eilute. Taip pat yra funkcijų (ANKSČIAU ir ANKSČIAUS), kurios gauna reikšmę iš dabartinės eilutės ir tą reikšmę naudoja atlikdamos operaciją su visa lentele.
Užklausos kontekstas nurodo duomenų pogrupį, kuris netiesiogiai sukuriamas kiekvienam "PivotTable" langeliui, atsižvelgiant į eilučių ir stulpelių antraštes.
Filtro kontekstas yra kiekviename stulpelyje leidžiamų reikšmių rinkinys, pagrįstas eilutei pritaikytais filtro apribojimais arba apibrėžtais formulės filtro išraiškomis.
Eilutės kontekstas
Jei formulę sukuriate apskaičiuotame stulpelyje, tos formulės eilutės kontekste yra reikšmės iš visų dabartinės eilutės stulpelių. Jei lentelė susijusi su kita lentele, turinyje taip pat yra visos tos kitos lentelės reikšmės, susijusios su dabartine eilute.
Tarkime, sukūrėte apskaičiuojamąjį stulpelį =[Transportavimo mokestis] + [Mokestis], kuris sudeda du stulpelius iš tos pačios lentelės. Ši formulė veikia kaip "Excel" lentelės formulės, kurios automatiškai nurodo reikšmes iš tos pačios eilutės. Atkreipkite dėmesį, kad lentelės skiriasi nuo diapazonų: naudodami diapazono žymėjimą negalite nurodyti reikšmės iš eilutės, esančios prieš dabartinę eilutę, ir negalite nurodyti jokios atsitiktinės vienos reikšmės lentelėje arba langelyje. Visada turite dirbti su lentelėmis ir stulpeliais.
Eilutės kontekstas automatiškai seka ryšius tarp lentelių, kad nustatytų, kurios susijusių lentelių eilutės yra susietos su dabartine eilute.
Pavyzdžiui, toliau pateikta formulė naudoja funkciją RELATED, kad iš susijusios lentelės gautų mokesčio reikšmę, atsižvelgiant į regioną, į kurį buvo išsiųstas užsakymas. Mokesčio vertė nustatoma naudojant regiono reikšmę dabartinėje lentelėje, peržvelgiant regioną susijusioje lentelėje ir tada iš susijusios lentelės gaunant to regiono mokesčio tarifą.
= [Freight] + RELATED('Region'[TaxRate])
Ši formulė tiesiog gauna dabartinio regiono mokesčio tarifą iš lentelės Regionas. Nereikia žinoti ar nurodyti rakto, jungiančio lenteles.
Kelių eilučių kontekstas
Be to, DAX yra funkcijų, kurios kartoja skaičiavimus lentelėje. Šios funkcijos gali turėti kelias dabartines eilutes ir dabartinių eilučių kontekstus. Programavimo terminais tariant, galite kurti formules, kurios rekursuoja vidinį ir išorinį ciklą.
Tarkime, jūsų darbaknygėje yra lentelės Produktai ir Pardavimas . Galbūt norėsite peržiūrėti visą pardavimo lentelę, kurioje gausu operacijų, susijusių su keliais produktais, ir rasti didžiausią kiekvieno produkto užsakymą vienoje operacijoje.
Programoje "Excel" šiam skaičiavimui reikia keletą tarpinių suvestinių, kurias reikėtų perkurti, jei duomenys pasikeistų. Jei esate patyręs "Excel" vartotojas, galite sukurti masyvo formules, kurios atliktų šią užduotį. Taip pat reliacinėje duomenų bazėje galite įrašyti įdėtuosius antrinius pasirinkimus.
Tačiau naudodami DAX galite sukurti vieną formulę, kuri pateikia teisingą reikšmę, o rezultatai automatiškai atnaujinami kiekvieną kartą, kai į lenteles įtraukiate duomenų.
=MAXX(FILTER(Sales,[ProdKey]=BEFORE([ProdKey])),Sales[OrderQty])
Išsamios paaiškinimo apie šią formulę ieškokite funkcijoje BEFOREEARLIER
Trumpai tariant, funkcija EARLIER išsaugo prieš šią operaciją vykdytos operacijos eilutės kontekstą. Funkcija visada atmintyje saugo du konteksto rinkinius: vienas kontekstų rinkinys atitinka dabartinę vidinio formulės ciklo eilutę, o kitas kontekstų rinkinys atitinka dabartinę išorinio formulės ciklo eilutę. DAX automatiškai tiekia reikšmes tarp dviejų ciklų, kad galėtumėte kurti sudėtingas agreguotas reikšmes.
Užklausos kontekstas
Užklausos kontekstas nurodo duomenų pogrupį, kuris netiesiogiai gaunamas formulei. Kai į "PivotTable" langelį nuvedate matą arba kitą reikšmės lauką, "Power Pivot" modulis tikrina eilučių ir stulpelių antraštes, duomenų filtrus ir ataskaitos filtrus, kad nustatytų kontekstą. Tada "Power Pivot" atlieka reikiamus skaičiavimus, kad užpildytų kiekvieną "PivotTable" langelį. Nuskaitomų duomenų rinkinys yra kiekvieno langelio užklausos kontekstas.
Kontekstas gali kisti atsižvelgiant į tai, kur padedate formulę, todėl formulės rezultatai taip pat kinta atsižvelgiant į tai, ar formulę naudojate "PivotTable" su daug grupavimo ir filtrų, ar apskaičiuojamajame stulpelyje be filtrų ir minimalų kontekstą.
Tarkime, sukūrėte šią paprastą formulę, kuri sumuoja lentelės "Sales" stulpelio Pelnas reikšmes:
=SUM('Sales'[Profit])
Jei šią formulę naudosite lentelės "Sales" apskaičiuojamajame stulpelyje, formulės rezultatai bus tokie patys visoje lentelėje, nes formulės užklausos kontekstas visada yra visas lentelės "Sales" duomenų rinkinys. Jūsų rezultatai bus naudingi visiems regionams, visiems produktams, visiems metams ir t. t.
Tačiau paprastai nenorite matyti to paties rezultato šimtus kartų, bet vietoj to norite gauti konkrečių metų, konkrečios šalies ar regiono, konkretaus produkto ar kokio nors šių elementų derinio pelną ir gauti bendrąją sumą.
"PivotTable" lengva pakeisti kontekstą įtraukiant arba pašalinant stulpelių ir eilučių antraštes ir įtraukiant arba šalinant duomenų filtrus. Galite sukurti formulę, panašią į pateiktą aukščiau, matavimu, tada įdėti ją į "PivotTable". Kai į "PivotTable" įtraukiate stulpelių ar eilučių antraštes, keičiate užklausos kontekstą, kuriame matas yra vertinamas. Sluoksniavimo ir filtravimo operacijos taip pat turi įtakos kontekstui. Todėl ta pati formulė, naudojama "PivotTable", vertinama skirtingame kiekvieno langelio užklausos kontekste.
Filtro kontekstas
Filtro kontekstas įtraukiamas, kai nurodote stulpelyje arba lentelėje leidžiamų reikšmių rinkinio filtro apribojimus, naudodami formulės argumentus. Filtro kontekstas taikomas virš kitų kontekstų, pvz., eilutės konteksto arba užklausos konteksto.
Pavyzdžiui, "PivotTable" apskaičiuoja kiekvieno langelio reikšmes pagal eilučių ir stulpelių antraštes, kaip aprašyta ankstesniame skyriuje apie užklausų kontekstą. Vis dėlto matų arba apskaičiuojamųjų stulpelių, kuriuos įtraukiate į "PivotTable", galite nurodyti filtro išraiškas, skirtas valdyti formulės naudojamas reikšmes. Taip pat galite pasirinktinai išvalyti tam tikrų stulpelių filtrus.
Daugiau informacijos apie filtrų kūrimą formulėse žr. Filtravimo funkcijos.
Pavyzdžių, kaip išvalyti filtrus norint sukurti bendrąsias sumas, rasite skyriuje Funkcija ALL.
Pavyzdžių, kaip pasirinktinai išvalyti ir taikyti filtrus formulėse, žr. skyriuje ALLEXCEPT funkcija.
Todėl turite peržiūrėti priemonių arba formulių, naudojamų "PivotTable", apibrėžimus, kad žinotumėte filtro kontekstą interpretuodami formulių rezultatus.
Konteksto nustatymas formulėse
Kai kuriate formulę, "Power Pivot for Excel" pirmiausia patikrina bendrąją sintaksę, tada patikrina jūsų pateiktų stulpelių ir lentelių pavadinimus pagal galimus stulpelius ir lenteles dabartiniame kontekste. Jei "Power Pivot" neranda formulėje nurodytų stulpelių ir lentelių, gausite klaidos pranešimą.
Kontekstas nustatomas, kaip aprašyta ankstesniuose skyriuose, naudojant galimas darbaknygės lenteles, ryšius tarp lentelių ir visus pritaikytus filtrus.
Pavyzdžiui, jei ką tik importavote kai kuriuos duomenis į naują lentelę ir netaikėte jokių filtrų, visas lentelės stulpelių rinkinys yra dabartinio konteksto dalis. Jei turite kelias lenteles, susietas ryšiais, ir dirbate su "PivotTable", kuri buvo filtruota įtraukiant stulpelių antraštes ir naudojant duomenų filtrus, kontekstas apima susijusias lenteles ir visus duomenų filtrus.
Kontekstas yra veiksminga sąvoka, kuri taip pat gali apsunkinti formulių trikčių šalinimą. Rekomenduojame pradėti nuo paprastų formulių ir ryšių, kad pamatytumėte, kaip veikia kontekstas, o tada pradėti eksperimentuoti su paprastomis formulėmis "PivotTable". Tolesniame skyriuje taip pat pateikiami keli pavyzdžiai, kaip formulės naudoja skirtingus konteksto tipus, kad dinamiškai pateiktų rezultatus.
Konteksto formulėse pavyzdžiai
- Funkcija RELATED išplečia dabartinės eilutės kontekstą, įtraukdama reikšmes į susijusį stulpelį. Tai leidžia atlikti peržvalgas. Šioje temoje pateiktame pavyzdyje rodoma filtravimo ir eilutės konteksto sąveika.
- Funkcija FILTER leidžia nurodyti eilutes, kurias norite įtraukti į dabartinį kontekstą. Šioje temoje pateikti pavyzdžiai taip pat parodo, kaip įdėti filtrus į kitas funkcijas, atliekančias agregavimą.
- Funkcija ALL nustato kontekstą formulėje. Jį galite naudoti norėdami nepaisyti filtrų, taikomų atsižvelgiant į užklausos kontekstą.
- Funkcija ALLEXCEPT leidžia pašalinti visus filtrus, išskyrus jūsų nurodytą. Abiejose temose pateikiama pavyzdžių, kurie padės kurti formules ir suprasti sudėtingus kontekstus.
- Funkcijos EARLIER ir EARLIEST leidžia pereiti nuo vienos lentelės prie kitos atliekant skaičiavimus nurodant reikšmę iš vidinio ciklo. Jei esate susipažinę su rekursijos sąvoka ir vidinėmis bei išorinėmis kilpomis, jums patiks funkcijų EARLIER ir EARLIEST teikiama galia. Jei dar nesate susipažinę su šiomis sąvokomis, turėtumėte atidžiai atlikti pavyzdyje nurodytus veiksmus ir pamatyti, kaip vidinis ir išorinis kontekstai naudojami skaičiuojant.
Nuorodų vientisumas
Šiame skyriuje aptariamos kelios išplėstinės sąvokos, susijusios su trūkstamomis reikšmėmis "Power Pivot" lentelėse, sujungtose ryšiais. Šis skyrius gali būti naudingas, jei turite darbaknyges su keliomis lentelėmis ir sudėtingomis formulėmis ir reikia pagalbos suprantant rezultatus.
Jei dar nesate susipažinę su sąryšinių duomenų sąvokomis, rekomenduojame pirmiausia perskaityti įvadinę temą "Ryšių apžvalga".
Nuorodų vientisumas ir "Power Pivot" ryšiai
"PowerPivot" nereikalauja, kad tarp dviejų lentelių būtų įgalintas nuorodų vientisumas, kad būtų apibrėžtas tinkamas ryšys. Vietoj to, kiekvieno ryšio "vienas su daugeliu" "vienas" pusėje sukuriama tuščia eilutė, naudojama visoms nesutampančioms susijusios lentelės eilutėms tvarkyti. Jis efektyviai veikia kaip išorinis SQL sujungimas.
Jei "PivotTable" grupuojate duomenis pagal vieną ryšio pusę, visi nesutampantys duomenys ryšio dalyje "daugelis" sugrupuojami kartu ir bus įtraukti į sumas su tuščia eilutės antrašte. Tuščia antraštė maždaug atitinka "nežinomas narys".
Nežinomo nario supratimas
Nežinomo nario sąvoka tikriausiai jums žinoma, jei dirbote su kelių dimensijų duomenų bazių sistemomis, tokiomis kaip SQL serverio analizės tarnybos. Jei terminas jums naujas, toliau pateiktame pavyzdyje paaiškinama, kas yra nežinomas narys ir kaip jis veikia skaičiavimus.
Tarkime, kuriate skaičiavimą, kuris sumuoja kiekvienos parduotuvės mėnesio pardavimą, bet lentelės Pardavimas stulpelyje trūksta parduotuvės pavadinimo reikšmės. Atsižvelgiant į tai, kad parduotuvės ir pardavimo lentelės yra sujungtos parduotuvės pavadinimu, kas gali nutikti formulėje? Kaip "PivotTable" turėtų grupuoti arba rodyti pardavimo skaičius, nesusijusius su esama parduotuve?
Ši problema dažna duomenų saugyklose, kur didelės faktų duomenų lentelės turi būti logiškai susijusios su dimensijų lentelėmis, kuriose yra informacijos apie parduotuves, regionus ir kitus atributus, naudojamus faktų kategorizavimui ir skaičiavimui. Norint išspręsti problemą, bet kokie nauji faktai, nesusiję su esamu objektu, laikinai priskiriami nežinomam nariui. Štai kodėl nesusiję faktai bus rodomi sugrupuoti "PivotTable" po tuščia antrašte.
Tuščių reikšmių ir tuščios eilutės traktavimas
Tuščios reikšmės skiriasi nuo tuščių eilučių, kurios įtraukiamos siekiant pritaikyti nežinomąjį narį. Tuščia reikšmė yra speciali reikšmė, naudojama nulinėms, tuščioms eilutėms ir kitoms trūkstamoms reikšmėms atvaizduoti. Daugiau informacijos apie tuščią reikšmę, taip pat kitus DAX duomenų tipus, rasite Duomenų modelių duomenų tipai.