Pierakstīties, izmantojot Microsoft
Pierakstīties vai izveidot kontu
Sveicināti!
Atlasīt citu kontu.
Jums ir vairāki konti
Izvēlieties kontu, ar kuru vēlaties pierakstīties.

Kad pirmoreiz uzzināsit, kā izmantot power Pivot, lielākā daļa lietotāju atklāj, ka reālā jauda kādā veidā tiek apkopota vai aprēķina rezultātu. Ja jūsu datos ir kolonna ar skaitliskām vērtībām, varat to vienkārši apkopot, atlasot to rakurstabulā vai Power View lauku sarakstā. Pēc būtības tā ir skaitliska vērtība, tāpēc tā tiks automātiski summēta, aprēķinātā vidējā vērtība, aprēķināts vai jebkāds jūsu atlasītais apkopojuma tips. Tas tiek dēvēts par netīto pasākumu. Netiešie pasākumi ir lieliski paredzēti ātrai un vienkāršai apkopošanai, taču tiem ir ierobežojumi, un šos ierobežojumus gandrīz vienmēr var atrisināt, izmantojot tiešus pasākumus unaprēķinātas kolonnas.

Vispirms apskatīsim piemēru, kurā izmantojam aprēķināto kolonnu, lai pievienotu jaunu teksta vērtību katrai tabulas Produkts rindai. Katra rinda tabulā Produkti satur visu veidu informāciju par katru pārdoto produktu. Mums ir kolonnas Produkta nosaukums, Krāsa, Lielums, Cena utt. Mums ir cita saistīta tabula ar nosaukumu Produkta kategorija, kurā ir kolonna ProductCategoryName. Mēs vēlamies, lai katram produktam tabulā Produkti būtu iekļauts produktu kategorijas nosaukums no tabulas Produktu kategorija. Mūsu tabulā Produkti varam izveidot aprēķinātu kolonnu ar nosaukumu Produktu kategorija, piemēram, šādi:

Pārskatāmais elements ar divām dzeltenām ziņojumu joslām

Mūsu jaunajā produktu kategorijas formulā tiek izmantota funkcija RELATED DAX, lai iegūtu vērtības no kolonnas ProductCategoryName saistītajā tabulā Produktu kategorija, un pēc tam ievada šīs vērtības katram produktam (katra rinda) tabulā Produkts.

Šis ir lielisks piemērs, kā mēs varam izmantot aprēķināto kolonnu, lai pievienotu fiksētu vērtību katrai rindai, ko varam izmantot vēlāk rakurstabulas apgabalā RINDAS, KOLONNAS vai FILTRI vai Power View atskaitē.

Izveidosim vēl vienu piemēru, kurā vēlamies aprēķināt peļņas normu mūsu produktu kategorijām. Tas ir bieži sastopams scenārijs pat bieži lietotās apmācībās. Mums ir pārdošanas tabula mūsu datu modelī, kurā ir transakciju dati, un pastāv relācija starp tabulu Pārdošana un tabulu Produktu kategorija. Tabulā Pārdošana ir kolonna ar pārdošanas apjomu un citu kolonnu, kurā ir izmaksas.

Mēs varam izveidot aprēķinātu kolonnu, kas aprēķina katras rindas peļņas summu, atņemot vērtības kolonnā COGS no vērtībām kolonnā SalesAmount, līdzīgi šim piemēram:

Opcija Atšķirīga galvene un kājene pirmajā lappusē

Tagad mēs varam izveidot rakurstabulu un vilkt lauku Produktu kategorija uz KOLONNAS, bet mūsu jaunais lauks Peļņa apgabalā VĒRTĪBAS (PowerPivot tabulas kolonna ir lauks rakurstabulas lauku sarakstā). Rezultāts ir netiešais mērs ar nosaukumu Peļņas summa. Tas ir apkopots vērtību apjoms no peļņas kolonnas katrai produktu kategorijai. Mūsu rezultāts izskatās šādi:

MelbourneIT-Configure-5

Šajā gadījumā vērtība vērtību laukā ir saprotama tikai ar vērtību lauku. Ja peļņa tiktu ielikta apgabalā KOLONNAS, rakurstabula izskatīsies šādi:

PivotTable with no useful values

Lauks Mūsu peļņa nenodrošina noderīgu informāciju, ja tas tiek ievietots apgabalos KOLONNAS, RINDAS un FILTRI. Tas ir saprotams tikai kā apkopotā vērtība apgabalā VĒRTĪBAS.

Ir izveidota kolonna ar nosaukumu Peļņa, kas aprēķina peļņas normu katrai tabulas Pārdošana rindai. Pēc tam mēs pievienojām peļņu mūsu rakurstabulas apgabalam VĒRTĪBAS, automātiski izveidojot netīšu pasākumu, kurā tiek aprēķināts rezultāts katrai produktu kategorijai. Ja jums ir domājat, ka mēs divreiz aprēķināto peļņu mūsu produktu kategorijām, tad viss ir pareizi. Vispirms mēs aprēķinājām peļņu katrai tabulas Pārdošana rindai, un pēc tam pievienojām peļņu apgabalam VĒRTĪBAS, kur tā tika apkopota katrai produktu kategorijai. Ja arī jūs domājat, ka mums nav jāizveido kolonna Aprēķinātā peļņa, arī tā ir pareiza. Bet kā tad aprēķināt savu peļņu, neizveidojot aprēķināto kolonnu Peļņa?

Peļņa tiktu tiešām labāk aprēķināta kā tieša mērs.

Pašlaik, lai salīdzinātu rezultātus, atstājam aprēķināto kolonnu Peļņa tabulā Pārdošana un Produktu kategorija kolonnāS un Peļņa mūsu rakurstabulā VĒRTĪBAS.

Mūsu pārdošanas tabulas aprēķinu apgabalā mēs izveidosim mēru ar nosaukumu Kopējā peļņa (lai izvairītos no nosaukumdošanas konfliktiem). Beigās tas dod tādus pašus rezultātus kā iepriekš, taču neierēķina aprēķināto kolonnu Peļņa.

Vispirms tabulā Pārdošana tiek atlasīta kolonna PārdSumma un pēc tam jānoklikšķina uz Automātiskā summēšanu, lai izveidotu tiešu mēru PārdSumma summa. Atcerieties, ka tieša mērs ir tas, ko izveidojām tabulas aprēķinu apgabalā pievienojumdiagrammā Power Pivot. Tas pats tiek paveikts kolonnā COGS. Lai atvieglotu identificēšanu, mēs pārdēvēsim šīs tabulas Total SalesAmount un Total COGS.

AutoSum button in Power Pivot

Pēc tam, izmantojot šo formulu, izveidojam vēl vienu mēru:

Kopējā peļņa:=[ Total SalesAmount] - [Total COGS]

Piezīme.: Mēs varētu arī uzrakstīt mūsu formulu kā kopējo peļņu:=SUM([SalesAmount]) - SUM([COGS]), bet, izveidojot atsevišķus mērus Total SalesAmount un Total COGS, varam tos izmantot arī savā rakurstabulā, un mēs varam tos izmantot kā argumentus visās citās mērvienību formulās.

Pēc mūsu jaunā kopējās peļņas mēra formāta maiņas uz valūtu, varam to pievienot mūsu rakurstabulai.

PivotTable

Jūs varat redzēt, ka mūsu jaunais kopējās peļņas mērs atgriež tos pašus rezultātus kā izveidojot aprēķināto kolonnu Peļņa un pēc tam to ievietojot kolonnā VALUES. Atšķirība ir mūsu kopējās peļņas mērs ir daudz efektīvāks un padara mūsu datu modeli tīrāku un vienkāršāku, jo tos aprēķinām vienlaikus un tikai ar laukiem, ko mēs atlasām mūsu rakurstabulai. Patiesībā šī kolonna Peļņa nav jāaprēķina.

Kāpēc šī pēdējā daļa ir svarīga? Aprēķinātās kolonnas pievieno datus datu modelim, un dati aizņem atmiņu. Atsvaidzinot datu modeli, ir nepieciešami arī apstrādes resursi, lai pārrēķinātu visas vērtības kolonnā Peļņa. Mums nav tik tiešām nepieciešams izmantot šādam resursus, jo mēs tiešām vēlamies aprēķināt savu peļņu, kad rakurstabulā atlasām laukus, par kuriem mēs vēlamies saņemt peļņu, piemēram, produktu kategorijas, reģionu vai datumus.

Apskatīsim citu piemēru. Kādā aprēķinātajā kolonnā tiek izveidoti rezultāti, kas pirmajā acu uzmetienā izskatās pareizi, bet....

Šajā piemērā mēs vēlamies aprēķināt pārdošanas apjomus kā procentuālu vērtību no kopējā pārdošanas apjoma. Savā tabulā Pārdošana izveidojam aprēķināto kolonnu ar nosaukumu % no pārdošanas apjoma, piemēram:

Poga Tekstlodziņš

Mūsu formulā ir norādīts: Katrai tabulas Pārdošana rindai summu kolonnā SalesAmount daliet ar visu kolonnā SalesAmount visu summu SUM.

Ja izveidojam rakurstabulu un pievienosim produktu kategoriju KOLONNĀM un atlasām mūsu jauno kolonnu % no pārdošanas apjoma, lai to ievietotu vērtībām, iegūstam katras mūsu produktu kategorijas % no pārdošanas kopsummas.

PivotTable showing Sum of % of Sales for Product Categories

labi. Līdz šim tas izskatās lieliski. Taču pievienosim datu griezumu. Tiek pievienots kalendārais gads, un pēc tam tiek atlasīts gads. Šajā gadījumā mēs atlasām 2007. Mēs to iegūstam.

Sum of % of Sales incorrect result in PivotTable

Pirmajā acu uzmetienā tas joprojām var izskatīties pareizi. Taču mūsu procentuālajām procentiem tiešām vajadzētu būt 100%, jo mēs vēlamies uzzināt procentuālo vērtību no kopējā pārdošanas apjoma katrā no mūsu produktu kategorijām 2007. gadā. Kas notika?

Our % of Sales column calculated a percent for each row that is the value in the SalesAmount column divided by the sum total of all values in the SalesAmount column. Vērtības aprēķinātajā kolonnā ir fiksētas. Tie ir nemainīgs rezultāts katrai tabulas rindai. Kad savā rakurstabulā pievienojām pārdošanas % no vērtības, tā tika apkopota kā visu kolonnas SalesAmount vērtību summa. Šī visu kolonnas Pārdošanas % vērtību summa vienmēr būs 100%.

Padoms.: Noteikti izlasiet DAX formulu kontekstu. Tā nodrošina izpratni par rindu līmeņa kontekstu un filtra kontekstu, kas ir tas, ko mēs šeit aprakstam.

Mēs varam izdzēst aprēķināto kolonnu % no pārdošanas apjoma, jo šī kolonna mums nepalīdzēs. Tā vietā mēs izveidosim pasākumu, kas pareizi aprēķinātu mūsu procentuālo vērtību no kopējā pārdošanas apjoma neatkarīgi no lietotajiem filtriem vai datu griezumiem.

Vai atceraties iepriekš izveidoto Mērs TotalSalesAmount, kurā tiek vienkārši summēts kolonna SalesAmount? Mēs to izmantojām kā argumentu mūsu jaunajā aprēķinātajā laukā, un mēs to izmantosim vēlreiz kā argumentu mūsu jaunajā aprēķinātajā laukā.

Padoms.: Noteiktu pasākumu, piemēram, Total SalesAmount un Total COGS, izveide ir ne tikai noderīga rakurstabulā vai atskaitē, bet arī ir noderīga kā argumenti citos mēra gadījumos, kad nepieciešams rezultāts kā arguments. Tas padara formulas efektīvāku un vieglāk lasāmas. Šī ir laba datu modelēšanas prakse.

Mēs izveidojam jaunu mēru, izmantojot šādu formulu:

% no kopējā pārdošanas apjoma:=([SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())

Šajā formulā ir norādīts: daliet rezultātu no Total SalesAmount ar SalesAmount kopsummu bez kolonnas vai rindas filtriem, kas nav definēti rakurstabulā.

Padoms.: Noteikti izlasiet DAX atsaucē par funkcijām CALCULATE un ALLSELECTED.

Tagad, pievienojot rakurstabulai jaunos % no pārdošanas kopsummas, iegūstam:

Darbvirsmas līdzekļu izsekošanas veidne

Tas izskatās labāk. Tagad mūsu % no pārdošanas kopsummas katrai produktu kategorijai tiek aprēķināti kā procentuālā daļa no kopējā pārdošanas apjoma 2007. gadā. Ja datu griezumā CalendarYear atlasām citu gadu vai vairāk par vienu gadu, iegūstam jaunas procentuālās vērtības attiecībā uz mūsu produktu kategorijām, taču mūsu gala summa joprojām ir 100%. Mēs varam pievienot arī citus datu griezumus un filtrus. Mūsu % no pārdošanas kopsummas mērs vienmēr radīs procentuālo vērtību no kopējā pārdošanas apjoma neatkarīgi no izmantotajiem datu griezumiem vai filtriem. Izmantojot mērus, rezultāts vienmēr tiek aprēķināts atbilstoši kontekstam, ko nosaka lauki kolonnās un RINDĀS, un pēc lietotajiem filtriem vai datu griezumiem. Tās ir mēra pilnvaras.

Tālāk ir norādīts dažas vadlīnijas, kas palīdzēs izlemt, vai aprēķinātā kolonna vai mērs ir piemērots noteiktam aprēķinam.

Aprēķināto kolonnu izmantošana

  • Ja vēlaties, lai jaunie dati tiktu rādīti rakurstabulā ROWS, COLUMNS vai FILTERS, asī, APZĪMĒJUMOS vai TILE BY Power View vizualizācijā, izmantojiet aprēķināto kolonnu. Tāpat kā parastas datu kolonnas, arī aprēķinātās kolonnas var izmantot kā lauku jebkurā apgabalā, un, ja tās ir skaitliskas, tās var apkopot arī tabulā VALUES.

  • Ja vēlaties, lai jaunie dati rindā būtu fiksēta vērtība. Piemēram, jums ir datumu tabula ar datumu kolonnu un jūs vēlaties citu kolonnu, kurā ir tikai mēneša numurs. Varat izveidot aprēķināto kolonnu, kas aprēķina tikai mēneša numuru no datumiem kolonnā Datums. Piemēram, =MONTH('Datums'[Datums]).

  • Ja vēlaties tabulai pievienot teksta vērtību katrai rindai, izmantojiet aprēķināto kolonnu. Laukos ar teksta vērtībām vērtības nekad nevar apkopot vērtību VALUES. Piemēram, =FORMAT('Date'[Date],"mmmm") sniedz mums mēneša nosaukumu katram datumam datumu tabulas Datums kolonnā Datums.

Izmantot pasākumus

  • Ja aprēķina rezultāts vienmēr būs atkarīgs no citiem laukiem, kurus atlasāt rakurstabulā.

  • Ja ir jāveic sarežģītāki aprēķini, piemēram, jāaprēķina skaits, izmantojot kāda veida filtru, vai arī jāaprēķina, kas ir pa gadiem, vai arī dispersija, izmantojiet aprēķināto lauku.

  • Ja vēlaties minimāli paturēt darbgrāmatas lielumu un palielināt tās veiktspēju, izveidojiet pēc iespējas vairāk aprēķinu. Daudzos gadījumos var izmantot visus aprēķinus, būtiski samazinot darbgrāmatas lielumu un paātrinot atsvaidzināšanas laiku.

Ņemiet vērā, ka ar aprēķināto kolonnu izveidi nekas nav kārtībā kā mūsu peļņas kolonnai un pēc tam to apkopot rakurstabulā vai atskaitē. Patiesībā tas ir tiešām labs un viegls veids, kā uzzināt par saviem aprēķiniem un tos izveidot. Izprotot šo divu ļoti jaudīgo Power Pivot līdzekļu augšanu, vēlēsities izveidot jums vispiemērotāko un precīzāko datu modeli. Cerams, ka šeit apgūtā informācija jums palīdzēs. Šeit ir arī daži ļoti lieliski resursi, kas var jums palīdzēt. Lūk, tikai daži: DAX formulu konteksts,apkopojumipievienojumdiagrammā Power Pivot un DAX resursu centrā. Lai gan tā ir nedaudz sarežģītāka un tiek novirzīta uz grāmatvedības un finanšu speciālistiem, datu modelēšana un analīze, izmantojot microsoft Power Pivot Excel paraugā, tiek ielādēta ar lieliskiem datu modelēšanas un formulu piemēriem.

Nepieciešama papildu palīdzība?

Vēlaties vairāk opciju?

Izpētiet abonementa priekšrocības, pārlūkojiet apmācības kursus, uzziniet, kā aizsargāt ierīci un veikt citas darbības.

Kopienas palīdz uzdot jautājumus un atbildēt uz tiem, sniegt atsauksmes, kā arī saņemt informāciju no ekspertiem ar bagātīgām zināšanām.

Vai šī informācija bija noderīga?

Cik lielā mērā esat apmierināts ar valodas kvalitāti?
Kas ietekmēja jūsu pieredzi?
Nospiežot Iesniegt, jūsu atsauksmes tiks izmantotas Microsoft produktu un pakalpojumu uzlabošanai. Jūsu IT administrators varēs vākt šos datus. Paziņojums par konfidencialitāti.

Paldies par jūsu atsauksmēm!

×