Apkopošana ir veids, kā sakļaut, summēt vai grupēt datus. Ja sākat darbu ar neapstrādātiem datiem no tabulām vai citiem datu avotiem, dati bieži ir plakani, kas nozīmē, ka ir daudz detaļu, bet tie nav organizēti vai grupēti. Kopsavilkumu vai struktūras trūkums var apgrūtināt datu modeļu noteikšanu. Svarīga datu modelēšanas daļa ir definēt apkopojumus, kas vienkāršo, abstrahē vai apkopo modeļus, atbildot uz konkrētu biznesa jautājumu.
Visbiežāk sastopamos apkopojumus, piemēram, tos, kas izmanto AVERAGE, COUNT,DISTINCTCOUNT,MAX, MIN vai SUM , mērā var izveidot automātiski, izmantojot automātisko summēšanu. Citi apkopojumu veidi, piemēram, AVERAGEX, COUNTX, COUNTROWS vai SUMX, atgriež tabulu, un tiem ir nepieciešama formula, kas izveidota, izmantojot datu analīzes izteiksmes (Data Analysis Expressions — DAX).
Apkopojumi pievienojumprogrammā Power Pivot
Apkopojuma grupu izvēlēšanās
Apkopojot datus, dati tiek grupēti pēc atribūtiem, piemēram, produkta, cenas, reģiona vai datuma, un pēc tam jādefinē formula, kas darbojas visiem grupas datiem. Piemēram, veidojot gada kopsummu, tiek izveidots apkopojums. Ja pēc tam izveidojat šī gada attiecību salīdzinājumā ar iepriekšējo gadu un parādāt tās kā procentus, tas ir cits apkopošanas veids.
Lēmumu par datu grupēšanu nosaka uzņēmējdarbības jautājums. Piemēram, apkopojumi var atbildēt uz šādiem jautājumiem:
Skaits Cik darījumu bija mēnesī?
Vidējie rādītāji Kādi bija vidējie pārdošanas rādītāji šajā mēnesī pēc pārdevēja?
Minimālās un maksimālās vērtības Kuri tirdzniecības rajoni bija pieci labākie pārdoto vienību skaita ziņā?
Lai izveidotu aprēķinu, kas atbild uz šiem jautājumiem, jums ir nepieciešami detalizēti dati, kas satur skaitļus, kas jāsaskaita vai jāsummē, un šiem skaitliskajiem datiem jābūt kaut kādā veidā saistītiem ar grupām, kuras izmantosit rezultātu organizēšanai.
Ja datos vēl nav vērtību, ko var izmantot grupēšanai, piemēram, produktu kategorijas vai tā ģeogrāfiskā reģiona nosaukums, kur atrodas veikals, iespējams, vēlēsities ieviest grupas savos datos, pievienojot kategorijas. Veidojot grupas programmā Excel, grupas, kuras vēlaties izmantot programmā Excel, ir manuāli jāieraksta vai jāatlasa no darblapas kolonnām. Tomēr relāciju sistēmā tādas hierarhijas kā produktu kategorijas bieži tiek glabātas citā tabulā, nevis faktu vai vērtību tabulā. Parasti kategoriju tabula ir saistīta ar faktu datiem, izmantojot kādu atslēgu. Piemēram, pieņemsim, ka jūsu datos ir produktu ID, bet ne produktu nosaukumi vai to kategorijas. Lai kategoriju pievienotu plakanai Excel darblapai, būs jānokopē kolonna, kurā bija kategoriju nosaukumi. Izmantojot Power Pivot, varat importēt produktu kategoriju tabulu savā datu modelī, izveidot relāciju starp tabulu ar skaitliskajiem datiem un produktu kategoriju sarakstu un pēc tam izmantot kategorijas, lai grupētu datus. Papildinformāciju skatiet sadaļā Relācijas izveide starp tabulām.
Apkopošanas funkcijas izvēle
Kad ir identificētas un pievienotas izmantojamās grupas, ir jāizlemj, kuras matemātiskās funkcijas izmantot apkopošanai. Bieži vārds apkopojums tiek lietots kā sinonīms matemātiskām vai statistiskām operācijām, kas tiek izmantotas apkopojumos, piemēram, summas, vidējās vērtības, minimums vai skaitļi. Tomēr Power Pivot sniedz iespēju izveidot pielāgotas apkopošanas formulas papildus standarta apkopojumiem, kas ir pieejami gan Power Pivot, gan programmā Excel.
Piemēram, ņemot vērā to pašu vērtību un grupējumu kopu, kas tika izmantota iepriekšējos piemēros, varat izveidot pielāgotus apkopojumus, kas atbild uz šādiem jautājumiem:
Filtrētais skaits Cik transakciju bija mēnesī, izņemot mēneša beigu uzturēšanas logu?
Koeficienti, izmantojot vidējos rādītājus laika gaitā Kāds bija pārdošanas apjoma pieaugums vai samazinājums procentos, salīdzinot ar to pašu periodu pērn?
Grupētas minimālās un maksimālās vērtības Kuri pārdošanas rajoni ierindojās pirmajā vietā katrai produktu kategorijai vai katrai pārdošanas veicināšanai?
Apkopojumu pievienošana formulām un rakurstabulām
Ja jums ir vispārīgs priekšstats par to, kā dati ir jāgrupē, lai tie būtu jēgpilni, un vērtībām, ar kurām vēlaties strādāt, varat izlemt, vai veidot rakurstabulu vai veidot aprēķinus tabulā. Power Pivot paplašina un uzlabo Excel vietējo spēju izveidot apkopojumus, piemēram, summas, skaitu vai vidējos rādītājus. Varat izveidot pielāgotus apkopojumus pievienojumprogrammā Power Pivot vai nu Power Pivot logā, vai Excel rakurstabulas apgabalā.
- Aprēķinātajā kolonnā varat izveidot apkopojumus, kuros ņemts vērā pašreizējās rindas konteksts, lai izgūtu saistītās rindas no citas tabulas, un pēc tam summēt, saskaitīt vai aprēķināt vidējās vērtības saistītajās rindās.
- Pamērā varat izveidot dinamiskus apkopojumus, kas izmanto gan formulā definētos filtrus, gan filtrus, ko nosaka rakurstabulas noformējums un datu griezumu, kolonnu virsrakstu un rindu virsrakstu atlase. Mērus, izmantojot standarta apkopojumus, var izveidot pievienojumprogrammā Power Pivot, izmantojot automātisko summēšanu vai izveidojot formulu. Varat arī izveidot netiešos mērus, izmantojot standarta apkopojumus rakurstabulā programmā Excel.
Grupējumu pievienošana rakurstabulai
Noformējot rakurstabulu, laukus, kas attēlo grupējumus, kategorijas vai hierarhijas, velciet uz rakurstabulas kolonnu un rindu sadaļu, lai grupētu datus. Pēc tam velciet laukus, kuros ir skaitliskas vērtības, uz vērtību apgabalu, lai tos varētu saskaitīt, aprēķināt vidējo vērtību vai summēt.
Ja pievienojat kategorijas rakurstabulai, bet kategorijas dati nav saistīti ar faktiskajiem datiem, var tikt parādīta kļūda vai savdabīgi rezultāti. Parasti Power Pivot mēģina novērst problēmu, automātiski nosakot un iesakot relācijas. Papildinformāciju skatiet sadaļā Darbs ar relācijām rakurstabulās.
Varat arī vilkt laukus datu griezumos, lai atlasītu noteiktas datu grupas skatīšanai. Datu griezumi ļauj interaktīvi grupēt, kārtot un filtrēt rezultātus rakurstabulā.
Darbs ar grupējumiem formulā
Grupējumus un kategorijas var izmantot arī, lai apkopotu tabulās saglabātos datus, izveidojot relācijas starp tabulām, un pēc tam izveidojot formulas, kas izmanto šīs relācijas, lai uzmeklētu saistītās vērtības.
Citiem vārdiem sakot, ja vēlaties izveidot formulu, kas grupē vērtības pēc kategorijas, vispirms ir jāizmanto relācija, lai savienotu tabulu, kurā ir detalizētie dati, un tabulas, kurās atrodas kategorijas, un pēc tam izveidotu formulu.
Papildinformāciju par formulu izveidi, kurās tiek izmantota uzmeklēšana, skatiet sadaļā Uzmeklēšana Power Pivot formulās.
Filtru izmantošana apkopojumos
Jauns līdzeklis pievienojumprogrammā Power Pivot ir iespēja lietot filtrus datu kolonnām un tabulām ne tikai lietotāja interfeisā un rakurstabulā vai diagrammā, bet arī pašās formulās, kas tiek izmantotas apkopojumu aprēķināšanai. Filtrus var izmantot formulās gan aprēķinātajās kolonnās, gan s.
Piemēram, jaunajās DAX apkopošanas funkcijās tā vietā, lai norādītu vērtības, kuras summēt vai skaitīt, kā argumentu varat norādīt visu tabulu. Ja šai tabulai netiek lietots neviens filtrs, apkopošanas funkcija darbojas attiecībā uz visām vērtībām norādītajā tabulas kolonnā. Tomēr DAX tabulā varat izveidot dinamisku vai statisku filtru, lai apkopojums darbotos pret citu datu apakškopu atkarībā no filtra stāvokļa un pašreizējā konteksta.
Apvienojot nosacījumus un filtrus formulās, varat izveidot apkopojumus, kas mainās atkarībā no formulās norādītajām vērtībām vai mainās atkarībā no rindu, virsrakstu un kolonnu virsrakstu atlases rakurstabulā.
Papildinformāciju skatiet sadaļā Datu filtrēšana formulās.
Excel apkopošanas funkciju un DAX apkopošanas funkciju salīdzinājums
Šajā tabulā ir uzskaitītas dažas standarta apkopošanas funkcijas, ko nodrošina programma Excel, un sniegtas saites uz šo funkciju ieviešanu pievienojumprogrammā Power Pivot. Šo funkciju DAX versija darbojas gandrīz tāpat kā Excel versija, ar dažām nelielām atšķirībām sintaksē un dažu datu tipu apstrādē.
Standard apkopošanas funkcijas
| Funkcija | Izmantojiet |
|---|---|
| AVERAGE | Atgriež visu kolonnā esošo skaitļu vidējo vērtību (vidējo aritmētisko). |
| AVERAGEA | Atgriež visu kolonnā esošo vērtību vidējo vērtību (vidējo aritmētisko). Apstrādā tekstu un vērtības, kas nav skaitliskas. |
| COUNT | Saskaita skaitliskās vērtības kolonnā. |
| COUNTA | Saskaita vērtības kolonnā, kuras nav tukšas. |
| MAX | Atgriež kolonnā lielāko skaitlisko vērtību. |
| MAXX | Atgriež tabulā novērtēto izteiksmju kopas lielāko vērtību. |
| MIN | Atgriež kolonnas mazāko skaitlisko vērtību. |
| MINX | Atgriež mazāko vērtību no izteiksmju kopas, kas novērtēta tabulā. |
| SUM | Saskaita visus kolonnas skaitļus. |
DAX apkopošanas funkcijas
DAX ietver apkopošanas funkcijas, kas ļauj norādīt tabulu, kurai jāveic apkopošana. Tāpēc šīs funkcijas ne tikai saskaita vai aprēķina vidējās vērtības kolonnā, bet arī izteiksmi, kas dinamiski definē apkopojamos datus.
Šajā tabulā ir uzskaitītas apkopošanas funkcijas, kas ir pieejamas DAX.
| Funkcija | Izmantojiet |
|---|---|
| AVERAGEX | Aprēķina vidējo izteiksmju kopu, kas novērtēta tabulā. |
| NODOKĻU NODOKLIS | Saskaita izteiksmju kopu, kas novērtēta tabulā. |
| COUNTBLANK | Saskaita tukšās vērtības kolonnā. |
| COUNTX | Saskaita kopējo rindu skaitu tabulā. |
| COUNTROWS | Saskaita to rindu skaitu, kas atgrieztas no ligzdotas tabulas funkcijas, piemēram, filtra funkcijas. |
| SUMX | Atgriež tabulā novērtēto izteiksmju kopas summu. |
DAX un Excel atšķirības Apkopošanas funkcijas
Lai gan šīm funkcijām ir tādi paši nosaukumi kā to Excel kolēģiem, tās izmanto Power Pivot atmiņas analīzes programmu, un ir pārrakstītas darbam ar tabulām un kolonnām. DAX formulu nevar izmantot Excel darbgrāmatā un otrādi. Tās var izmantot tikai Power Pivot logā un rakurstabulās, kuru pamatā ir Power Pivot dati. Turklāt, lai gan funkcijām ir identiski nosaukumi, darbība var nedaudz atšķirties. Papildinformāciju skatiet atsevišķu funkciju atsauces tēmās.
Veids, kā kolonnas tiek novērtētas apkopojumā, arī atšķiras no veida, ko programma Excel apstrādā apkopojumus. Piemērs var palīdzēt ilustrēt.
Pieņemsim, ka vēlaties iegūt summu ar vērtībām, kas norādītas tabulas Pārdošana kolonnā Summa, izveidojiet šādu formulu:
=SUM('Sales'[Amount])
Vienkāršākajā gadījumā funkcija iegūst vērtības no vienas nefiltrētas kolonnas, un rezultāts ir tāds pats kā programmā Excel, kas vienmēr vienkārši summē kolonnas Summa vērtības. Tomēr pievienojumprogrammā Power Pivot formula tiek interpretēta kā "Iegūstiet vērtību summā katrai tabulas Pārdošana rindai un pēc tam saskaitiet šīs atsevišķās vērtības. Power Pivot novērtē katru rindu, kurā tiek veikta apkopošana, un aprēķina vienu skalāru vērtību katrai rindai, un pēc tam veic šo vērtību apkopošanu. Tāpēc formulas rezultāts var atšķirties, ja tabulai tiek lietoti filtri vai vērtības tiek aprēķinātas, pamatojoties uz citiem apkopojumiem, kas, iespējams, ir filtrēti. Papildinformāciju skatiet sadaļā Konteksts DAX formulās.
DAX laika informācijas funkcijas
Papildus tabulas apkopošanas funkcijām, kas aprakstītas iepriekšējā sadaļā, DAX ir apkopošanas funkcijas, kas darbojas ar jūsu norādītajiem datumiem un laikiem, lai nodrošinātu iebūvētu laika informāciju. Šīs funkcijas izmanto datumu diapazonus, lai iegūtu saistītās vērtības un apkopotu šīs vērtības. Varat arī salīdzināt vērtības dažādos datumu diapazonos.
Šajā tabulā ir uzskaitītas laika informācijas funkcijas, ko var izmantot apkopošanai.
| Funkcija | Izmantojiet |
|---|---|
|
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR |
Aprēķina vērtību dotā perioda beigās kalendārā. |
|
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR |
Aprēķina vērtību tā perioda kalendāra beigās, kas ir pirms dotā perioda. |
|
TOTALMTD TOTALYTD TOTALQTD |
Aprēķina vērtību intervālā, kas sākas perioda pirmajā dienā un beidzas vēlākajā datumā norādītajā datumu kolonnā. |
Citas funkcijas sadaļā Laika informācijas funkcija (laika informācijas funkcijas) ir funkcijas, kuras var izmantot, lai izgūtu datumus vai pielāgotus datumu diapazonus izmantošanai apkopošanā. Piemēram, funkciju DATESINPERIOD var izmantot, lai atgrieztu datumu diapazonu, un šo datumu kopu var izmantot kā citas funkcijas argumentu, lai aprēķinātu pielāgotu apkopojumu tikai šiem datumiem.