Datų lentelių išmanymas ir kūrimas naudojant „Power Pivot“ programoje „Excel“

"Power Pivot" datų lentelės yra būtinos naršant ir skaičiuojant duomenis per tam tikrą laiką. Šiame straipsnyje išsamiai aiškinamos datų lentelės ir kaip jas kurti "Power Pivot". Visų pirma šiame straipsnyje aprašoma:

• Kodėl datų lentelė yra svarbi naršant ir skaičiuojant duomenis pagal datą ir laiką.
• Kaip naudoti "Power Pivot" norint įtraukti datų lentelę į duomenų modelį.
• Kaip datų lentelėje sukurti naujus datos stulpelius, pvz., Metai, Mėnuo ir Laikotarpis.
• Kaip kurti ryšius tarp datų lentelių ir faktų lentelių.
• Kaip dirbti su laiku.

Šis straipsnis skirtas vartotojams, pradedantiems naudoti "Power Pivot". Tačiau svarbu jau gerai išmanyti duomenų importavimą, ryšių kūrimą ir apskaičiuojamųjų stulpelių bei matų kūrimą.

Šiame straipsnyje neaprašoma , kaip naudoti DAX Time-Intelligence funkcijas matų formulėse. Daugiau informacijos, kaip kurti matus su DAX laiko informacijos funkcijomis, žr. " Power Pivot" programoje "Excel" laiko informacija.

Turinys

• Datų lentelių supratimas

• Datų lentelės įtraukimas į duomenų modelį

  • Importavimas iš sąryšinės duomenų bazės

  • Datų lentelės kūrimas programoje "Excel"

    • Kaip: sukurti datų lentelę programoje "Excel" ir nukopijuoti ją į duomenų modelį

• Naujų datos stulpelių įtraukimas į datų lentelę

  • DAX datos ir laiko funkcijos

  • Kalendorinių metų formulių pavyzdžiai

    • Metai
    • Mėnuo
    • Ketvirtis
    • Mėnesio pavadinimas

  • Finansinių metų formulių pavyzdžiai

    • Finansiniai metai
    • Finansinis mėnuo
    • Finansinis ketvirtis

• Šventės arba specialios datos

• Pasirinktinis kalendorius – trylika keturių savaičių laikotarpių

  • Savaitė
  • Taškas
  • Laikotarpis Finansiniai metai
  • FiscalYear laikotarpis

• Ryšiai

  • Keli ryšiai
  • Neaktyvūs ryšiai
  • Kelios datų lentelės

• Ypatybė Datų lentelė

• Darbas su laiku

• Datų tinkamumo didinimas

• Priedas

  • Teksto duomenų tipo datų konvertavimas į datos duomenų tipą
  • Papildomi ištekliai

Datų lentelių supratimas

Beveik visa duomenų analizė apima datų ir laiko duomenų naršymą ir palyginimą. Pavyzdžiui, galbūt norėsite susumuoti praėjusio finansinio ketvirčio pardavimų sumas ir tada palyginti šias sumas su kitais ketvirčiais arba galite norėti apskaičiuoti sąskaitos mėnesio pabaigos pabaigos balansą. Kiekvienu iš šių atvejų naudojate datas norėdami grupuoti ir agreguoti pardavimo operacijas arba balansus tam tikru laikotarpiu.

"Power View" ataskaita

Datų lentelėje gali būti daug skirtingų datos ir laiko išraiškų. Pavyzdžiui, datų lentelėje dažnai būna stulpelių, pvz., Finansiniai metai, Mėnuo, Ketvirtis arba Laikotarpis, kuriuos galite pasirinkti kaip laukus iš laukų sąrašo, kai filtruojate duomenis "PivotTable" arba "Power View" ataskaitose.

"Power View" laukų sąrašas

Kad datų stulpeliai, pvz., Metai, Mėnuo ir Ketvirtis įtrauktų visas atitinkamo diapazono datas, datų lentelėje turi būti bent vienas stulpelis su nuosekliu datų rinkiniu. T. y. tame stulpelyje turi būti po vieną eilutę, skirtą kiekvienų į datų lentelę įtrauktų metų kiekvienai dienai.

Pavyzdžiui, jei duomenys, kuriuos norite naršyti, turi datas nuo 2010 m. vasario 1 d. iki 2012 m. lapkričio 30 d., o jūs pateikiate ataskaitą apie kalendorinius metus, tuomet jums reikės datų lentelės su bent datų diapazonu nuo 2010 m. sausio 1 d. iki 2012 m. gruodžio 31 d. Kiekvienų metų datos lentelėje turi būti visos kiekvienų metų dienos. Jei reguliariai atnaujinsite duomenis naujesniais duomenimis, galbūt norėsite pabaigos datą paleisti metais ar dvejais, kad laikui bėgant nereikėtų atnaujinti datų lentelės.

Datų lentelė su nuosekliu datų rinkiniu

Jei pateikiate finansinių metų ataskaitą, galite sukurti datų lentelę su nuosekliu kiekvienų finansinių metų datų rinkiniu. Pavyzdžiui, jei jūsų finansiniai metai prasideda kovo 1 d., o jūs turite 2010 finansinių metų duomenis iki dabartinės datos (pvz., 2013 finansinių metų), galite sukurti datų lentelę, kuri prasideda 2009 03 01 ir apima bent kiekvieną dieną kiekvienais finansiniais metais iki paskutinės datos 2013 finansiniais metais.

Jei teiksite ataskaitas ir už kalendorinius metus, ir už finansinius metus, jums nereikia kurti atskirų datų lentelių. Vienoje datų lentelėje gali būti stulpeliai, skirti kalendoriniams metams ar net trylikos keturių savaičių laikotarpiui. Svarbu tai, kad datų lentelėje yra nuoseklių datų rinkinys visiems metams imtinai.

Datų lentelės įtraukimas į duomenų modelį

Yra keli būdai, kaip įtraukti datų lentelę į duomenų modelį:

• Importavimas iš sąryšinės duomenų bazės ar kito duomenų šaltinio.
• Sukurkite datų lentelę programoje "Excel" ir nukopijuokite arba susiekite su nauja lentele "Power Pivot".
• Importavimas iš "Microsoft Azure" parduotuvės.

Pažvelkime į kiekvieną iš jų atidžiau.

Importavimas iš sąryšinės duomenų bazės

Jei importuojate kai kuriuos arba visus duomenis iš duomenų saugyklos ar kito tipo sąryšinės duomenų bazės, gali būti, kad jau yra datų lentelė ir ryšiai tarp jos ir kitų importuojamų duomenų. Datos ir formatas greičiausiai sutaps su jūsų faktinių duomenų datomis, ir šios datos tikriausiai prasideda gerokai praeityje ir siekia toli į ateitį. Importuojama datų lentelė gali būti labai didelė ir joje gali būti datų diapazonas, didesnis nei tas, kurį turite įtraukti į duomenų modelį. Galite naudoti "PowerPivot" lentelių importavimo vediklio išplėstines filtravimo funkcijas ir pasirinktinai pasirinkti tik datas ir konkrečius stulpelius, kurių jums tikrai reikia. Tai gali žymiai sumažinti jūsų darbaknygės dydį ir padidinti našumą.

Lentelių importavimo vediklis

Daugeliu atvejų jums nereikės kurti jokių papildomų stulpelių, pvz., Finansiniai metai, Savaitė, Mėnesio pavadinimas ir kt., nes jie jau bus importuotoje lentelėje. Tačiau kai kuriais atvejais, importavus datų lentelę į duomenų modelį, gali prireikti sukurti papildomų datos stulpelių, atsižvelgiant į konkretų ataskaitų poreikį. Laimei, tai lengva padaryti naudojant DAX. Daugiau apie datų lentelės laukų kūrimą sužinosite vėliau. Kiekviena aplinka yra skirtinga. Jei nesate tikri, ar jūsų duomenų šaltiniuose yra susijusi datos ar kalendoriaus lentelė, kreipkitės į duomenų bazės administratorių.

Datų lentelės kūrimas programoje "Excel"

Galite sukurti datų lentelę programoje "Excel" ir nukopijuoti ją į naują duomenų modelio lentelę. Tai tikrai gana lengva padaryti ir suteikia daug lankstumo.

Kai kuriate datų lentelę programoje "Excel", pradedate nuo vieno stulpelio su nuosekliu datų diapazonu. Tada naudodami "Excel" formules "Excel" darbalapyje galite sukurti papildomų stulpelių, pvz., Metai, Ketvirtis, Mėnuo, Finansiniai metai, Laikotarpis ir t. t., arba, nukopijavę lentelę į duomenų modelį, galite juos kurti kaip apskaičiuojamuosius stulpelius. Papildomų datos stulpelių kūrimas "Power Pivot" aprašytas šio straipsnio skyriuje "Naujų datų stulpelių įtraukimas į datų lentelę ".

Kaip: sukurti datų lentelę programoje "Excel" ir nukopijuoti ją į duomenų modelį

1. Programos "Excel" tuščiame darbalapyje langelyje A1 įveskite stulpelio antraštės pavadinimą, kad nurodytumėte datų diapazoną. Paprastai tai būna kažkas, pvz., DateTime, arba DateKey.

2. A2 langelyje įveskite pradžios datą. Pavyzdžiui, 2010-01-01.

3. Spustelėkite užpildo rankenėlę ir vilkite ją žemyn iki eilutės numerio su pabaigos data. Pavyzdžiui, 2016-12-31.
   

4. Pažymėkite visas stulpelio Data eilutes (įskaitant antraštės pavadinimą langelyje A1).

5. Grupėje Stiliai spustelėkite Formatuoti kaip lentelę ir pasirinkite stilių.

6. Dialogo lange Formatuoti kaip lentelę spustelėkite Gerai.
   

7. Nukopijuokite visas eilutes, įskaitant antraštę.

8. "Power Pivot" skirtuke Pagrindinis spustelėkite Įklijuoti.

9. Įklijavimo peržiūroje>Lentelės pavadinimas įveskite pavadinimą, pvz., Data arba Calendar. Palikite pažymėtą Naudoti pirmąją eilutę kaip stulpelių antraštes, tada spustelėkite Gerai.
   
   Nauja datų lentelė (šiame pavyzdyje pavadinta Calendar) "Power Pivot" atrodo taip:
   

   Pastaba

   Taip pat susietą lentelę galite sukurti naudodami funkciją Įtraukti į duomenų modelį. Tačiau dėl to jūsų darbaknygė tampa be reikalo didelė, nes darbaknygėje yra dvi datų lentelės versijos; vieną programoje "Excel" ir kitą "Power Pivot".

Dabar duomenų modelyje yra datų lentelė. Galite įtraukti naujus datos stulpelius, pvz., Metai, Mėnuo ir t. t., naudodami DAX.

Naujų datos stulpelių įtraukimas į datų lentelę

Datų lentelė su vienu datos stulpeliu, kuriame yra po vieną eilutę kiekvienų metų kiekvienai dienai, yra svarbi apibrėžiant visas datų diapazono datas. Jis taip pat būtinas kuriant ryšį tarp faktų lentelės ir datos lentelės. Tačiau vienas datos stulpelis su viena eilute kiekvienai dienai nėra naudingas analizuojant pagal datas "PivotTable" arba "Power View" ataskaitoje. Norite, kad datų lentelėje būtų stulpelių, kurie padėtų kaupti duomenis pagal datų diapazoną arba grupę. Pavyzdžiui, galite norėti susumuoti pardavimo sumas pagal mėnesį ar ketvirtį arba galite sukurti matą, apskaičiuojantį metų augimą. Kiekvienu iš šių atvejų datų lentelei reikia stulpelių "metai", "mėnuo" arba "ketvirtis", kurie leistų kaupti to laikotarpio duomenis.

Jei importavote datų lentelę iš sąryšinio duomenų šaltinio, joje jau gali būti įvairių norimų datos stulpelių tipų. Kai kuriais atvejais galbūt norėsite modifikuoti kai kuriuos iš šių stulpelių arba sukurti papildomų datos stulpelių. Tai ypač aktualu, jei programoje "Excel" sukuriate savo datų lentelę ir nukopijuojate ją į duomenų modelį. Laimei, sukurti naujus datos stulpelius "Power Pivot" yra gana paprasta naudojant datos ir laiko funkcijas DAX.

DAX datos ir laiko funkcijos

Jei kada nors dirbote su datos ir laiko funkcijomis "Excel" formulėse, tikriausiai būsite susipažinę su datos ir laiko funkcijomis. Nors šios funkcijos panašios į jų atitikmenis programoje "Excel", yra keli svarbūs skirtumai:

• DAX datos ir laiko funkcijos naudoja datetime duomenų tipą.
• Jie gali paimti reikšmes iš stulpelio kaip argumentą.
• Jie gali būti naudojami datos reikšmėms grąžinti ir (arba) valdyti.

Šios funkcijos dažnai naudojamos kuriant pasirinktinius datos stulpelius datų lentelėje, todėl jas svarbu suprasti. Naudosime keletą šių funkcijų, kad sukurtume stulpelius Metai, Ketvirtis, FiscalMonth ir t. t.

DAX apima šias datos ir laiko funkcijas:

• DATA
• DATEVALUE
• KITA DIENA
• EDATE
• EOMONTH
• HOUR
• MINUTE
• MONTH
• NOW
• SECOND
• TIME
• TIMEVALUE
• ŠIANDIEN
• WEEKDAY
• WEEKNUM
• YEAR
• YEARFRAC

Formulėse galite naudoti ir daug kitų DAX funkcijų. Pvz., daugelis čia aprašytų formulių naudoja matematines ir trigonometrines funkcijas , pvz., MOD ir TRUNC, logines funkcijas , pvz., IF, ir teksto funkcijas , pvz., FORMAT Daugiau informacijos apie kitas DAX funkcijas rasite toliau šiame straipsnyje esančiame skyriuje Papildomi ištekliai .

Kalendorinių metų formulių pavyzdžiai

Šiuose pavyzdžiuose aprašomos formulės, naudojamos kuriant papildomus stulpelius datų lentelėje pavadinimu Calendar. Vienas stulpelis pavadinimu Data jau yra ir jame yra nuoseklus datų diapazonas nuo 2010-01-01 iki 2016-12-31.

Metai

=YEAR([data])

Šioje formulėje funkcija YEAR grąžina metus iš reikšmės stulpelyje Data. Kadangi stulpelio Data reikšmė yra datetime duomenų tipo, funkcija YEAR žino, kaip iš jos grąžinti metus.

Mėnuo

=MONTH([data])

Šioje formulėje, panašiai kaip ir funkcijoje YEAR, galime tiesiog naudoti funkciją MONTH , kad iš stulpelio Data būtų pateikta mėnesio reikšmė.

Ketvirtis

=INT(([mėnuo]+2)/3)

Šioje formulėje naudojame funkciją INT , kad datos reikšmė būtų grąžinama kaip sveikasis skaičius. Funkcijos INT argumentas, kurį nurodome, yra reikšmė iš stulpelio Mėnuo, pridėkite 2 ir padalinkite iš 3, kad gautume ketvirtį, nuo 1 iki 4.

Mėnesio pavadinimas

=FORMAT([date],"mmmm")

Šioje formulėje, norėdami gauti mėnesio pavadinimą, naudojame funkciją FORMAT , kad konvertuotume skaitinę reikšmę iš stulpelio Data į tekstą. Kaip pirmąjį argumentą nurodome stulpelį Date, o tada formatą; Norime, kad mūsų mėnesio pavadinime būtų rodomi visi simboliai, todėl naudojame "mmmm". Mūsų rezultatas atrodo taip:

Jei norime grąžinti mėnesio pavadinimą sutrumpintą iki trijų raidžių, vartosime "mmm" formato argumente.

Savaitės diena

=FORMAT([date],"ddd")

Šioje formulėje naudojame funkciją FORMAT, kad gautume dienos pavadinimą. Kadangi norime tik sutrumpinto dienos pavadinimo, formato argumente nurodome "ddd".

„PivotTable“ pavyzdys

Kai turite datų laukus, pvz., metus, ketvirtį, mėnesį ir t. t., galite juos naudoti "PivotTable" arba ataskaitoje. Pavyzdžiui, toliau pateiktame paveikslėlyje rodomas laukas Pardavimo kiekis iš lentelės Pardavimo faktų reikšmės (REIKŠMĖS) ir metai ir ketvirtis iš dimensijų lentelės Kalendorius (eilutės). SalesAmount agreguojamas metų ir ketvirčio kontekste.

Finansinių metų formulių pavyzdžiai

Finansiniai metai

=IF([mėnuo]<= 6,[metai],[metai]+1)

Šiame pavyzdyje finansiniai metai prasideda liepos 1 d.

Nėra funkcijos, kuri galėtų išgauti finansinius metus pagal datos reikšmę, nes finansinių metų pradžios ir pabaigos datos dažnai skiriasi nuo kalendorinių metų datų. Norėdami gauti finansinius metus, pirmiausia naudojame funkciją IF , kad patikrintume, ar mėnesio reikšmė yra mažesnė arba lygi 6. Antrajame argumente, jei mėnesio reikšmė yra mažesnė arba lygi 6, pateikia reikšmę iš stulpelio Metai. Jei ne, tada grąžinkite reikšmę iš Year ir pridėkite 1.

Kitas būdas nurodyti finansinių metų pabaigos mėnesio reikšmę – tiesiog sukurti matą, kuris tiesiog nurodo mėnesį. Pavyzdžiui, FYE:=6. Tada vietoj mėnesio numerio galite nurodyti priemonės pavadinimą. Pavyzdžiui, =IF([Mėnuo]<=[FYE],[Metai],[Metai]+1). Tai suteikia daugiau lankstumo nurodant finansinių metų pabaigos mėnesį keliose skirtingose formulėse.

Finansinis mėnuo

=IF([mėnuo]<= 6, 6+[mėnuo], [mėnuo]- 6)

Šioje formulėje nurodome, ar [Mėnuo] reikšmė yra mažesnė arba lygi 6, tada imame 6 ir pridedame reikšmę iš Mėnesio, priešingu atveju iš reikšmės atimame 6 iš reikšmės iš [Mėnuo].

Finansinis ketvirtis

=INT(([FiscalMonth]+2)/3)

FiscalQuarter formulė yra beveik tokia pati kaip mūsų kalendorinių metų ketvirčio formulė. Vienintelis skirtumas yra tas, kad nurodėme [FiscalMonth], o ne [Month].

Šventės arba specialios datos

Galite įtraukti datos stulpelį, nurodantį, kad tam tikros datos yra šventės ar kita ypatinga data. Pavyzdžiui, galite norėti susumuoti Naujųjų metų dienos pardavimo sumas įtraukdami lauką Šventė į "PivotTable" kaip duomenų filtrą arba filtrą. Kitais atvejais šias datas galite neįtraukti į kitus datos stulpelius arba matą.

Įtraukti šventes ar ypatingas dienas yra gana paprasta. Programoje "Excel" galite sukurti lentelę, kurioje yra norimos įtraukti datos. Tada galite kopijuoti arba naudoti funkciją Įtraukti į duomenų modelį, kad įtrauktumėte jį į duomenų modelį kaip susietą lentelę. Daugeliu atvejų nėra būtina sukurti ryšį tarp lentelės ir lentelės Kalendorius. Visos jį nurodančios formulės gali naudoti funkciją LOOKUPVALUE , kad būtų pateiktos reikšmės.

Toliau pateikiamas programoje "Excel" sukurtos lentelės, kurioje įtrauktos į datų lentelę įtrauktinos šventės, pavyzdys:

Data	Šventės
1/1/2010	Naujieji metai
11/25/2010	Padėkos diena
12/25/2010	Kalėdos
2011 01 01	Naujieji metai
11/24/2011	Padėkos diena
12/25/2011	Kalėdos
2012/1/1	Naujieji metai
11/22/2012	Padėkos diena
12/25/2012	Kalėdos
1/1/2013	Naujieji metai
11/28/2013	Padėkos diena
12/25/2013	Kalėdos
11/27/2014	Padėkos diena
12/25/2014	Kalėdos
1/1/2014	Naujieji metai
11/27/2014	Padėkos diena
12/25/2014	Kalėdos
1/1/2015	Naujieji metai
11/26/2014	Padėkos diena
12/25/2015	Kalėdos
1/1/2016	Naujieji metai
11/24/2016	Padėkos diena
12/25/2016	Kalėdos

Datų lentelėje sukuriame stulpelį, pavadintą Atostogos , ir naudojame formulę, tokią kaip ši:

=LOOKUPVALUE(Holidays[Holiday],Holidays[Date],Calendar[date])

Pažvelkime į šią formulę atidžiau.

Naudojame funkciją LOOKUPVALUE, kad gautume reikšmes iš lentelės Holidays stulpelio Holidays. Pirmajame argumente nurodome stulpelį, kuriame bus rezultato reikšmė. Stulpelį Šventės nurodome lentelėje Šventės, nes būtent tokią reikšmę norime grąžinti.

=LOOKUPVALUE(Holidays[Holiday],Holidays[Date],Calendar[date])

Tada nurodome antrąjį argumentą, paieškos stulpelį, kuriame yra datos, kurių norime ieškoti. Lentelės Šventėsstulpelį Data nurodome taip:

=LOOKUPVALUE(Holidays[Holiday],Holidays[Date],Calendar[date])

Galiausiai nurodome lentelės Kalendorius stulpelį, kuriame yra datos, kurių norime ieškoti lentelėje Atostogos . Tai, žinoma, yra lentelėsKalendorius stulpelis Data.

=LOOKUPVALUE(Holidays[Holiday],Holidays[Date],Calendar[date])

Stulpelis "Holiday" pateiks kiekvienos eilutės, kurios datos reikšmė atitinka lentelės "Holidays" datą, pavadinimą.

Pasirinktinis kalendorius – trylika keturių savaičių laikotarpių

Kai kurios organizacijos, pavyzdžiui, mažmeninė prekyba ar maitinimo paslaugos, dažnai teikia ataskaitas skirtingais laikotarpiais, pavyzdžiui, trylika keturių savaičių laikotarpių. Trylikos keturių savaičių kalendoriuje kiekvienas laikotarpis yra 28 dienos; todėl kiekvieną laikotarpį sudaro keturi pirmadieniai, keturi antradieniai, keturi trečiadieniai ir t. t. Kiekvienas laikotarpis turi tą patį dienų skaičių ir paprastai, švenčių dienos kiekvienais metais patenka į tą patį laikotarpį. Galite pasirinkti pradėti laikotarpį bet kurią savaitės dieną. Kaip ir su kalendoriaus ar finansinių metų datomis, galite naudoti DAX norėdami sukurti papildomų stulpelių su pasirinktinėmis datomis.

Toliau pateiktuose pavyzdžiuose pirmasis visas laikotarpis prasideda pirmąjį finansinių metų sekmadienį. Šiuo atveju finansiniai metai prasideda 07/1.

Savaitė

Ši reikšmė pateikia savaitės numerį, kuris prasideda nuo pirmosios visos finansinių metų savaitės. Šiame pavyzdyje pirma visa savaitė prasideda sekmadienį, todėl pirma visa pirmųjų finansinių metų savaitė lentelėje Kalendorius iš tikrųjų prasideda 2010-07-04 ir tęsiasi visą paskutinę visą savaitę lentelėje Kalendorius. Nors ši reikšmė nėra tokia naudinga analizuojant, ją būtina apskaičiuoti, kad būtų galima naudoti kitose 28 dienų laikotarpio formulėse.

=INT([data]-40356)/7)

Pažvelkime į šią formulę atidžiau.

Pirmiausia sukuriame formulę, kuri iš stulpelio Data pateikia reikšmes kaip sveikąjį skaičių, kaip parodyta toliau:

=INT([data])

Tada norime ieškoti pirmojo sekmadienio pirmaisiais finansiniais metais. Matome, kad tai yra 2010-07-04.

Dabar iš šios reikšmės atimkite 40356 (sveikasis skaičius nuo 2010-06-27, paskutinio praėjusių finansinių metų sekmadienio), kad gautumėte dienų skaičių nuo dienų pradžios mūsų kalendoriaus lentelėje, pvz.:

=INT([data]-40356)

Tada padalinkite rezultatą iš 7 (savaitės dienų) taip:

=INT(([data]-40356)/7)

Rezultatas atrodo taip:

Taškas

Šiame pasirinktiniame kalendoriuje laikotarpis apima 28 dienas ir jis visada prasideda sekmadienį. Šiame stulpelyje bus pateiktas laikotarpio, prasidedančio pirmųjų finansinių metų pirmuoju sekmadieniu, numeris.

=INT(([Savaitė]+3)/4)

Pažvelkime į šią formulę atidžiau.

Pirmiausia sukuriame formulę, kuri iš stulpelio Week pateikia reikšmę kaip sveikąjį skaičių, kaip parodyta toliau:

= INT([Savaitė])

Tada prie tos reikšmės pridėkite 3, pvz.:

=INT([Savaitė]+3)

Tada padalinkite rezultatą iš 4 taip:

=INT(([Savaitė]+3)/4)

Rezultatas atrodo taip:

Laikotarpis Finansiniai metai

Ši vertė grąžina laikotarpio finansinius metus.

=INT(([Periodas]+12)/13)+2008

Pažvelkime į šią formulę atidžiau.

Pirmiausia sukuriame formulę, kuri pateikia reikšmę iš Period ir sudeda 12:

=([Taškas]+12)

Rezultatą padaliname iš 13, nes finansiniuose metuose yra trylika 28 dienų laikotarpių:

=(([Laikotarpis]+12)/13)

Įtraukiame 2010, nes tai pirmieji metai lentelėje:

=(([Laikotarpis]+12)/13)+2010

Galiausiai, naudojame funkciją INT, kad pašalintume bet kokią rezultato dalį ir grąžintume sveikąjį skaičių, padalijus iš 13, taip:

= INT(([Periodas]+12)/13)+2010

Rezultatas atrodo taip:

FiscalYear laikotarpis

Ši reikšmė pateikia laikotarpio numerį nuo 1 iki 13, pradedant nuo kiekvieno finansinių metų pirmojo pilno laikotarpio (prasidedančio sekmadienį).

=IF(MOD([Period],13), MOD([Period],13),13)

Ši formulė yra šiek tiek sudėtingesnė, todėl pirmiausia ją aprašysime mums geriau suprantama kalba. Ši formulė nurodo padalyti reikšmę iš [Laikotarpis] iš 13, kad gautumėte laikotarpio numerį (1–13) metuose. Jei šis skaičius yra 0, tada pateikite 13.

Pirma, mes sukuriame formulę, kuri grąžina liekaną reikšmę iš Periodas 13. MOD (matematines ir trigonometrines funkcijas) galime naudoti taip:

= MOD([Periodas],13)

Tai dažniausiai duoda mums norimą rezultatą, išskyrus tuos atvejus, kai laikotarpio reikšmė yra 0, nes tos datos nepriklauso pirmiesiems finansiniams metams, pvz., pirmosioms penkioms dienoms mūsų pavyzdyje Kalendoriaus datų lentelėje. Galime tuo pasirūpinti naudodami funkciją IF. Jei mūsų rezultatas yra 0, grąžiname 13, kaip šis:

= IF(MOD([Period],13),MOD([Period],13),13)

Rezultatas atrodo taip:

„PivotTable“ pavyzdys

Toliau pateiktame paveikslėlyje pavaizduota "PivotTable" su lauku Pardavimo_kiekis iš pardavimo faktų lentelės VALUES ir laukais PeriodFiscalYear ir PeriodInFiscalYear iš kalendoriaus datos dimensijų lentelės EILUTĖS. SalesAmount kontekste agreguojama pagal finansinius metus ir finansinių metų 28 dienų laikotarpį.

Ryšiai

Duomenų modelyje sukūrę datų lentelę, norėdami pradėti naršyti duomenis "PivotTable" ir ataskaitose bei agreguoti duomenis pagal stulpelius datos dimensijų lentelėje, turite sukurti ryšį tarp faktų lentelės su operacijų duomenimis ir datų lentelės.

Kadangi jums reikia sukurti ryšį pagal datas, norėsite įsitikinti, kad sukuriate ryšį tarp stulpelių, kurių reikšmės yra datetime (Date) duomenų tipo.

Kiekvienai faktų lentelės datos reikšmei susijusiame peržvalgos stulpelyje datų lentelėje turi būti sutampančių reikšmių. Pvz., eilutė (operacijos įrašas) pardavimo faktų lentelėje, kurios reikšmė yra 8/15/2012 12:00 AM stulpelyje DateKey turi turėti atitinkančią reikšmę susijusios datos stulpelyje datos lentelėje (pavadintas kalendorius). Tai viena iš svarbiausių priežasčių, kodėl norite, kad datų lentelės stulpelyje būtų gretimas datų diapazonas, apimantis bet kokias galimas faktų lentelės datas.

Jei bandydami sukurti ryšį gaunate tipo neatitikimo klaidą, greičiausiai taip yra dėl to, kad faktų lentelės stulpelis nėra datos duomenų tipo. Taip gali nutikti, kai "PowerPivot" negali automatiškai konvertuoti ne datos (paprastai tai būna teksto duomenų tipas) į datos duomenų tipą. Faktų lentelėje esantį stulpelį galite naudoti, bet turėsite konvertuoti duomenis naudodami DAX formulę naujame apskaičiuotame stulpelyje. Žr. Teksto duomenų tipo datų konvertavimas į datos duomenų tipą toliau priede.

Keli ryšiai

Kai kuriais atvejais gali prireikti sukurti kelis ryšius arba kelias datų lenteles. Pavyzdžiui, jei pardavimo faktų lentelėje yra keli datos laukai, pvz., DateKey, ShipDate ir ReturnDate, jie visi gali turėti ryšius su datos lentelės lauku Date, tačiau tik vienas iš jų gali būti aktyvus. Šiuo atveju, kadangi "DateKey" yra operacijos data, taigi ir svarbiausia data, ši funkcija geriausiai pasitarnautų kaip aktyvus ryšys. Kiti turi neaktyvius santykius.

Toliau pateikta "PivotTable" apskaičiuoja bendrą pardavimą pagal finansinius metus ir finansinį ketvirtį. Matas pavadinimu Total Sales, su formule Total Sales:=SUM([SalesAmount]), dedamas į VALUES, o FiscalYear ir FiscalQuarter laukai iš Calendar date lentelės dedami į ROWS.

Ši paprasta "PivotTable" veikia tinkamai, nes norime sumuoti bendras pardavimo apimtis pagal "DateKey " operacijos datą. Mūsų Total Sales matas naudoja DateKey datas ir yra sumuojamas pagal finansinius metus ir finansinį ketvirtį, nes yra ryšys tarp DateKey lentelėje Sales ir Date stulpelio Calendar date lentelėje.

Neaktyvūs ryšiai

Tačiau ką daryti, jei norėtume sumuoti bendrą pardavimą ne pagal operacijos, o pagal siuntimo datą? Mums reikia ryšio tarp stulpelio ShipDate lentelėje Sales ir stulpelio Date lentelėje Calendar. Jei nesukursime šio ryšio, mūsų agregavimas visada bus pagrįstas operacijos data. Tačiau mes galime turėti kelis ryšius, nors tik vienas gali būti aktyvus, ir kadangi operacijos data yra svarbiausia, ji gauna aktyvų ryšį su Calendar lentele.

Šiuo atveju išsiuntimo datos ryšys yra neaktyvus, todėl bet kuri matavimo formulė, sukurta agreguoti duomenis pagal siuntimo datas, turi nurodyti neaktyvų ryšį naudodama funkciją USERELATIONSHIP .

Pvz., kadangi yra neaktyvus ryšys tarp stulpelio ShipDate lentelėje Sales ir stulpelio Date lentelėje Calendar , galime sukurti matą, kuris sumuoja bendrą pardavimą pagal siuntimo datą. Naudojame tokią formulę, kad nustatytume naudotiną ryšį:

Bendras pardavimas pagal išsiuntimo datą:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

Ši formulė paprasčiausiai nurodo: Apskaičiuokite SalesAmount sumą, bet filtruokite naudodami ryšį tarp stulpelio ShipDate lentelėje Sales ir stulpelio Date lentelėje Calendar.

Dabar, jei sukursime "PivotTable" ir apskaičiuosime pardavimo pagal siuntimo datą matą VERTĖSE, o finansinius metus ir finansinį ketvirtį – ROWS, matysime tą pačią bendrąją sumą, tačiau visos kitos finansinių metų ir finansinių metų sumos skiriasi, nes pagrįstos išsiuntimo data, o ne operacijos data.

Naudojant neaktyvius ryšius galima naudoti tik vieną datų lentelę, tačiau reikia, kad matai (pvz., "Total Sales by Ship Date") formulėje nurodytų neaktyvų ryšį. Yra ir kita alternatyva, t. y. naudoti kelias datų lenteles.

Kelios datų lentelės

Kitas būdas dirbti su keliais datos stulpeliais faktų lentelėje yra sukurti kelias datos lenteles ir sukurti atskirus aktyvius ryšius tarp jų. Dar kartą pažvelkime į lentelės Pardavimas pavyzdį. Turime tris stulpelius su datomis, apie kurias galime norėti kaupti duomenis:

• DateKey su kiekvienos operacijos pardavimo data.
• A SiuntimoData – su data ir laiku, kada parduotos prekės buvo išsiųstos klientui.
• A ReturnDate – su data ir laiku, kai buvo gauta viena ar daugiau grąžintų prekių.

Atminkite, kad laukas "DateKey" su operacijos data yra svarbiausias. Didžiąją dalį agregavimo atliksime remdamiesi šiomis datomis, todėl tikrai norėsime ryšio tarp jo ir lentelės Calendar stulpelio Date. Jei nenorime sukurti neaktyvių ryšių tarp ShipDate ir ReturnDate bei datos lauko Calendar lentelėje, todėl reikia specialių priemonių formulių, galime sukurti papildomas siuntimo datos ir grąžinimo datos lenteles. Tada galime užmegzti aktyvius santykius tarp jų.

Šiame pavyzdyje sukūrėme kitą datų lentelę, pavadintą ShipCalendar. Tai, žinoma, taip pat reiškia, kad reikia sukurti papildomus datos stulpelius, o kadangi šie datos stulpeliai yra kitoje datų lentelėje, norime juos pavadinti taip, kad skirtųsi nuo tų pačių stulpelių Calendar lentelėje. Pavyzdžiui, sukūrėme stulpelius, pavadintus SiuntimoMetai, SiuntimoMėnuo, SiuntimoKetvirtis ir t. t.

Jei sukursime savo "PivotTable" ir įdėsime bendro pardavimo matą kaip VALUES bei ShipFiscalYear ir ShipFiscalQuarter eilutėse, matysime tuos pačius rezultatus, kuriuos matėme kurdami neaktyvų ryšį ir specialų apskaičiuojamąjį lauką Total Sales by Ship Date.

Kiekvienas iš šių metodų reikalauja kruopštaus apsvarstymo. Naudojant kelis ryšius su viena datų lentele, gali tekti sukurti specialiąsias priemones, kurios tranzito neaktyvius ryšius naudojant funkciją USERELATIONSHIP. Kita vertus, laukų sąraše gali būti painu kurti kelias datų lenteles, o kadangi duomenų modelyje turite daugiau lentelių, tam reikės daugiau atminties. Eksperimentuokite, kas jums labiausiai tinka.

Ypatybė Datų lentelė

Ypatybė Datų lentelė nustato metaduomenis, būtinus, kad tinkamai veiktų Time-Intelligence funkcijos, pvz., TOTALYTD, PREVIOUSMONTH ir DATESBETWEEN. Kai skaičiavimas vykdomas naudojant vieną iš šių funkcijų, "PowerPivot" formulių modulis žino, kur nueiti norint gauti reikiamas datas.

Kai nustatote ypatybę Datų lentelė, joje nurodote datų lentelę ir datos (datos/laiko) duomenų tipo stulpelį.

Kaip: nustatyti ypatybę Datų lentelė

1. "PowerPivot" lange pasirinkite lentelę Calendar.
2. Skirtuke Dizainas spustelėkite Pažymėti kaip datą Lentelė.
3. Dialogo lange Žymėti kaip datų lentelę pasirinkite stulpelį su unikaliomis reikšmėmis ir datos duomenų tipu.

Darbas su laiku

Visos datos reikšmės su datos duomenų tipu "Excel" arba "„SQL Server“" iš tikrųjų yra skaičius. Į šį skaičių įeina skaitmenys, nurodantys laiką. Daugeliu atvejų, kad laikas kiekvienai eilutei yra vidurnaktis. Pvz., jei pardavimo faktų lentelės lauke DateTimeKey yra reikšmės, pvz., 10/19/2010 12:00:00 AM, tai reiškia, kad reikšmės atitinka dienos tikslumo lygį. Jei lauko DateTimeKey reikšmėse yra įtrauktas laikas, pvz., 10/19/2010 8:44:00 AM, tai reiškia, kad reikšmės yra minutės tikslumo lygio. Reikšmės taip pat gali būti valandos ar net sekundžių tikslumo lygio. Laiko reikšmės tikslumas turės didelės įtakos tam, kaip sukursite datų lentelę ir ryšius tarp jos ir faktų lentelės.

Turite nustatyti, ar duomenis kaupsite dienos, ar laiko tikslumu. Kitaip tariant, galbūt norėsite naudoti datos lentelės stulpelius, pvz., Rytas, Popietė arba Valanda, kaip laiko datos laukus "PivotTable" Eilučių, Stulpelių arba Filtrų srityse.

Jei ketinate kaupti duomenis iki laiko lygio, tada datų lentelei reikės datų stulpelio, kuriame būtų įtrauktas laikas. Tiesą sakant, reikės datos stulpelio su viena eilute, reiškiančia kiekvieną valandą, o gal net kiekvieną minutę, kiekvieną dieną kiekvienais metais datų diapazone. Taip yra todėl, kad norint sukurti ryšį tarp faktų lentelės stulpelio DateTimeKey ir datų lentelės stulpelio Date, reikia turėti sutampančias reikšmes. Kaip galite įsivaizduoti, jei įtrauksite daug metų, tai gali sudaryti labai didelę datų lentelę.

Tačiau dažniausiai norima kaupti tik dienos duomenis. Kitaip tariant, stulpelius, pvz., Metai, Mėnuo, Savaitė arba Savaitės diena naudosite kaip laukus "PivotTable" eilučių, stulpelių arba filtrų srityse. Šiuo atveju datų lentelės stulpelyje turi būti tik viena eilutė, skirta kiekvienai metų dienai, kaip aprašėme anksčiau.

Jei jūsų datos stulpelyje nurodytas laiko tikslumas, bet agreguojate tik iki dienos lygio, norint sukurti ryšį tarp faktų lentelės ir datos lentelės, gali tekti modifikuoti faktų lentelę sukurdami naują stulpelį, kuris sutrumpins datos stulpelio reikšmes iki dienos reikšmės. Kitaip tariant, konvertuokite reikšmę, pvz., 10/19/2010 8:44:00AM į 10/19/2010 12:00:00 AM. Tada galite sukurti ryšį tarp šio naujo stulpelio ir datų lentelės stulpelio datos, nes reikšmės sutampa.

Pažvelkime į pavyzdį. Šiame paveikslėlyje pavaizduotas "DateTimeKey" stulpelis pardavimo faktų lentelėje. Visi šios lentelės duomenų agregavimas turi būti atliekamas tik dienos lygiu, naudojant "Calendar" datų lentelės stulpelius, pvz., Metai, Mėnuo, Ketvirtis ir kt. Į reikšmę įtrauktas laikas nėra svarbus, tik faktinė data.

Kadangi mums nereikia analizuoti šių duomenų iki laiko lygio, mums nereikia stulpelio Data Calendar datų lentelėje įtraukti vieną eilutę kiekvienai valandai ir minutei kiekvienos dienos kiekvienais metais. Taigi, datos lentelės stulpelis Data atrodo taip:

Norėdami sukurti ryšį tarp lentelės "Sales" stulpelio "DateTimeKey" ir lentelės "Calendar" stulpelio "Date", galime sukurti naują apskaičiuojamąjį stulpelį lentelėje "Pardavimo faktai" ir naudoti funkciją TRUNC, kad sutrumpintume datos ir laiko reikšmę stulpelyje "DateTimeKey" į datos reikšmę, atitinkančią stulpelio "Date" reikšmes lentelėje "Calendar". Mūsų formulė atrodo taip:

=TRUNC([DateTimeKey],0)

Bus pateiktas naujas stulpelis (pavadinome "DateKey") su data iš stulpelio "DateTimeKey" ir kiekvienos eilutės laiku 12:00:00:

Dabar galime sukurti ryšį tarp šio naujo (DateKey) stulpelio ir stulpelio Date lentelėje Calendar.

Taip pat lentelėje "Sales" galime sukurti apskaičiuojamąjį stulpelį, kuris sumažina stulpelio "DateTimeKey" laiko tikslumą iki valandos tikslumo lygio. Tokiu atveju funkcija TRUNC neveiks, tačiau vis tiek galime naudoti kitas DAX datos ir laiko funkcijas, kad išgautume ir iš naujo sujungtume naują reikšmę iki valandos tikslumo. Galime naudoti tokią formulę:

= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)

Mūsų naujas stulpelis atrodo taip:

Jei datos lentelės stulpelyje Data yra valandos tikslumo reikšmės, galime sukurti ryšį tarp jų.

Datų tinkamumo didinimas

Daugelis datų lentelėje sukurtų stulpelių yra būtini kitiems laukams, tačiau iš tikrųjų nėra tokie naudingi analizuojant. Pavyzdžiui, mūsų minėtos ir visame straipsnyje nurodytos lentelės "Sales" laukas "DateKey" yra svarbus, nes kiekvienos operacijos atveju ta operacija įrašoma kaip įvykdyta tam tikrą dieną ir tam tikru laiku. Tačiau analizės ir ataskaitų požiūriu jis nėra toks naudingas, nes negalime jo naudoti kaip eilutės, stulpelio arba filtro lauko "Pivot" lentelėje arba ataskaitoje.

Panašiai mūsų pavyzdyje Calendar lentelės stulpelis Data yra labai naudingas, kritiškas, tačiau jo negalima naudoti kaip dimensijos "PivotTable".

Kad lentelės ir jose esantys stulpeliai būtų kuo naudingesni ir kad "PivotTable" arba "Power View" ataskaitos laukų sąrašus būtų lengviau naršyti, svarbu kliento įrankiuose paslėpti nereikalingus stulpelius. Taip pat tam tikras lenteles galite pageidauti paslėpti. Anksčiau pateiktoje lentelėje Šventės yra šventinių dienų datos, kurios svarbios tam tikruose lentelės Kalendorius stulpeliuose, tačiau negalite naudoti pačių lentelės Šventės stulpelių Data ir Šventės kaip "PivotTable" laukų. Čia vėlgi, norėdami, kad laukų sąrašus būtų lengviau naršyti, galite paslėpti visą lentelę Šventės.

Kitas svarbus darbo su datomis aspektas yra pavadinimų suteikimo konvencijos. "Power Pivot" lenteles ir stulpelius galite pavadinti taip, kaip norite. Tačiau nepamirškite, ypač jei darbaknygę naudosite bendrai su kitais vartotojais, nes gera pavadinimų suteikimo konvencija padeda lengviau identifikuoti lenteles ir datas ne tik laukų sąrašuose, bet ir "Power Pivot" bei DAX formulėse.

Kai duomenų modelyje yra datų lentelė, galite pradėti kurti matus, kurie padės išnaudoti visas duomenų galimybes. Kai kurie gali būti paprasti, pvz., susumuoti šių metų pardavimo sumas, o kiti gali būti sudėtingesni, nes reikia filtruoti pagal konkretų unikalių datų diapazoną. Sužinokite daugiau apie "Power Pivot" irlaiko informacijos funkcijų matus.

Priedas

Teksto duomenų tipo datų konvertavimas į datos duomenų tipą

Kai kuriais atvejais faktų lentelėje su operacijų duomenimis gali būti teksto duomenų tipo datos. Tai yra data, kuri rodoma kaip 2012-12-04T11:47:09 iš tikrųjų nėra data arba bent jau ne tokia, kokią "PowerPivot" gali suprasti. Iš tikrųjų tai tėra tekstas, kuris skaitomas kaip data. Norint sukurti ryšį tarp faktų lentelės datos stulpelio ir datų lentelės datos stulpelio, abu stulpeliai turi būti datos duomenų tipo.

Paprastai, kai bandote keisti datų stulpelio, kuris yra tekstinis duomenų tipas, duomenų tipą į datos duomenų tipą, "PowerPivot" gali interpretuoti datas ir automatiškai konvertuoti jas į tikros datos duomenų tipą. Jei "Power Pivot" negali atlikti duomenų tipo konvertavimo, gausite tipo neatitikimo klaidą.

Tačiau vis tiek galima konvertuoti datas į tikros datos duomenų tipą. Galite sukurti naują apskaičiuojamąjį stulpelį ir naudodami DAX formulę išanalizuoti metus, mėnesį, dieną, laiką ir t. t. iš teksto eilučių, tada vėl sujungti taip, kad "Power Pivot" galėtų skaityti kaip tikrą datą.

Šiame pavyzdyje į "Power Pivot" importavome faktų lentelę pavadinimu Pardavimai. Jame yra stulpelis, pavadintas DateTime. Reikšmės rodomos taip:

Jei pažvelgtume į duomenų tipą grupėje Formatavimas "Power Pivot" skirtuke Pagrindinis, pamatytume, kad tai yra duomenų tipas Tekstas.

Negalime sukurti ryšio tarp stulpelio DateTime ir stulpelio Date mūsų datų lentelėje, nes duomenų tipai nesutampa. Jei bandysime pakeisti duomenų tipą į Data, gausime tipo neatitikimo klaidą:

Šiuo atveju "PowerPivot" nepavyko konvertuoti duomenų tipo iš teksto į datą. Mes vis dar galime naudoti šį stulpelį, bet tam, kad jis taptų tikros datos duomenų tipu, turime sukurti naują stulpelį, kuris išanalizuotų tekstą ir iš naujo sukurtų jį į reikšmę, kurią "Power Pivot" gali padaryti datos duomenų tipu.

Prisiminkite, kad anksčiau šiame straipsnyje skyriuje Darbas su laiku; Jei nebūtina, kad jūsų analizė būtų tiksli pagal paros laiką, faktų lentelėje esančias datas turėtumėte konvertuoti į dienos tikslumo lygį. Atsižvelgdami į tai, norime, kad naujojo stulpelio reikšmės atitiktų dienos tikslumą (neįtraukiant laiko). Galime konvertuoti stulpelio DateTime reikšmes į datos duomenų tipą ir pašalinti laiko tikslumo lygį naudodami šią formulę:

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))

Atsiras naujas stulpelis (šiuo atveju pavadintas Data). "Power Pivot" netgi aptinka reikšmes kaip datas ir automatiškai nustato duomenų tipą į Data.

Jei norime išsaugoti laiko tikslumo lygį, paprasčiausiai išplečiame formulę, įtraukdami valandas, minutes ir sekundes.

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +

TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))

Dabar, kai turime datos duomenų tipo stulpelį, galime sukurti ryšį tarp jo ir datos stulpelio.

Papildomi ištekliai

„Power Pivot“ datos

"Power Pivot" skaičiavimai

Greitasis pasirengimas darbui: DAX pagrindai per 30 minučių

Duomenų analizės reiškinių nuoroda

DAX išteklių centras