Excel datu modelis (Power Pivot) ļauj savienot lielu detalizētu faktisko datu kopu ar augstākā līmeņa budžetu, izmantojot galdnieku tabulas.
Budžeti tiek veikti augstākajā līmenī - ieņēmumi pa produktu līnijām pa reģioniem pa mēnešiem. Faktiskie laika gaitā uzkrājas lēnām - rēķins pa rēķinam, rinda pa rindai. Mazā budžeta faila salīdzināšana ar apjomīgajiem Faktiskie dati ir uz visiem laikiem sagādājusi sāpes. Man patīk šis triks no Roba Kolija, aka PowerPivotPro.com.
Lai izveidotu piemēru, jums ir 54 rindu budžeta tabula: viena rinda mēnesī katram reģionam katram produktam.
Rēķina fails ir detalizēts: šogad līdz šim ir 422 rindas.
Pasaulē nav neviena VLOOKUP, kas jebkad ļautu jums saskaņot šīs divas datu kopas. Bet, pateicoties Power Pivot (jeb datu modelim programmā Excel 2013+), tas kļūst viegli.
Jums ir jāizveido mazas mazas tabulas, kuras es saucu par “galdniekiem”, lai saistītu divas lielākas datu kopas. Manā gadījumā starp abām tabulām ir kopīgs produkts, reģions un datums. Produktu tabula ir niecīga četru šūnu tabula. Tas pats attiecas uz reģionu. Izveidojiet katru no tiem, kopējot datus no vienas tabulas un izmantojot Noņemt dublikātus.
Labajā pusē esošo kalendāra tabulu bija grūtāk izveidot. Budžeta datiem ir viena rinda mēnesī, vienmēr nokrītot uz mēneša beigām. Rēķina datos tiek rādīti dienas datumi, parasti darba dienas. Tātad man bija jākopē datuma lauks no abām datu kopām vienā kolonnā un pēc tam jānoņem dublikāti, lai pārliecinātos, ka visi datumi ir attēloti. Pēc tam es =TEXT(J4,"YYYY-MM")izveidoju Mēness sleju no ikdienas datumiem.
Ja jums nav pilnas Power Pivot pievienojumprogrammas, jums ir jāizveido rakurstabula no tabulas Budžets un jāatzīmē izvēles rūtiņa Pievienot šos datus datu modelim.
Kā tika apspriests iepriekšējā padomā, pievienojot laukus rakurstabulai, būs jādefinē sešas sakarības. Lai gan jūs to varētu izdarīt ar sešiem apmeklējumiem dialoglodziņā Attiecību izveide, es aktivizēju savu Power Pivot pievienojumprogrammu un sešu attiecību noteikšanai izmantoju diagrammas skatu.
Lūk, kā panākt visu šo darbu: jūs varat izmantot skaitliskos laukus no Budžeta un no Faktiskais. Bet, ja pagrieziena tabulā vēlaties parādīt reģionu, produktu vai mēnesi, tiem jānāk no galdnieku tabulām!
Šeit ir rakurstabula ar datiem, kas iegūti no piecām tabulām. A kolonna nāk no reģiona galdnieka. 2. rinda nāk no kalendāra galdnieka. Produkta sagriezējs ir no Produkta galdnieka. Budžeta numuri nāk no tabulas Budžets, un faktiskie numuri nāk no tabulas Rēķins.
Tas darbojas, jo galdnieku tabulās tiek lietoti filtri tabulai Budžets un Faktiskais. Tā ir skaista tehnika un parāda, ka Power Pivot nav paredzēts tikai lielajiem datiem.
Skatīties video
- Jums ir neliela budžeta datu kopa no augšas uz leju
- Jūs vēlaties salīdzināt ar augšējo faktisko datu kopu
- Faktiskie dati var nākt no rēķinu reģistra
- Datu modelis ļaus salīdzināt šīs atšķirīgā lieluma datu kopas
- Abas datu kopas izveidojiet tabulā Ctrl + T
- Katram teksta laukam, par kuru vēlaties ziņot, izveidojiet galdnieku tabulu
- Nokopējiet vērtības un noņemiet dublikātus
- Datumiem varat iekļaut datumus no abām tabulām un konvertēt uz mēneša beigām
- Padariet galdniekus par Ctrl + T tabulām
- Pēc izvēles, bet noderīgi nosaukt visas piecas tabulas
- Budžetā izveidojiet rakurstabulu un izvēlieties datu modeli
- Izveidojiet rakurstabulu, izmantojot sākotnējo tabulu budžetu un faktisko
- Visiem pārējiem laukiem jānāk no galdnieku tabulām
- Pievienojiet sagriezējus pēc produkta
- Izveidojiet trīs attiecības no Budžeta līdz Galdniekiem
- Izveidojiet trīs attiecības no Actual līdz Joiners
- Rīt: kā ir vieglāk veidot attiecības, izmantojot Power Pivot un DAX Formulas
Video atšifrējums
Uzziniet Excel no podcast, 2016. gada epizodes - Budžets no augšas uz leju salīdzinājumā ar faktisko no apakšas uz augšu!
Hei, es aprašu visu šo grāmatu, augšējā labajā stūrī noklikšķiniet uz “i” un sekojiet atskaņošanas sarakstam.
Hei, es to pārtraucu, šis ir Bils Jelens no 15 minūtēm. Es saprotu, ka tagad tas ir neticami ilgs podkāsts, un jums ir kārdinājums vienkārši noklikšķināt tieši caur to, bet ļaujiet man jums to vienkārši pateikt. Ja izmantojat programmu Excel 2013 un jums kādreiz ir bijusi maza budžeta tabula un liela faktisko tabula, un jums tās ir jāaplūko kopā, šī ir pārsteidzoša jauna spēja, kas mums ir programmā Excel 2013, un ko daudzi cilvēki nav izskaidrojuši , un jūs, iespējams, nezināt par to. Ja tas esat jūs, jūs esat 2013. gadā, un jums ir jāapzīmē šīs divas datu kopas, veltiet laiku, varbūt šodien, varbūt rīt, varbūt pievienojiet to novērošanas sarakstam, tas ir tā vērts, tā ir pārsteidzoša tehnika.
Labi, lūk, kas mums ir, kreisajā pusē mums ir budžets, šis budžets, tas tiek veikts augšējā līmenī, no augšas uz leju, pa labi katrai produktu līnijai, katram reģionam, katram mēnesim, ir budžets . Labajā pusē šeit nav daudz ierakstu, skaitot 55. Mēs to cenšamies salīdzināt ar aktualitātēm. Faktiskie dati nāk no rēķinu reģistra, tāpēc mums ir reģions, produkts un ieņēmumi, taču tie ir atsevišķi rēķini, šeit ir daudz vairāk datu, mēs esam jau pusgadā, un man jau ir 423 ieraksti. Labi, tad kā jūs šos 55 kartējat uz šiem 423? Varētu būt grūti izdarīt ar VLOOKUP, vispirms jums ir jāapkopo kopsavilkums, bet par laimi programmā Excel 2013 datu modelis to padara ļoti, ļoti viegli. Tas, kas mums ir nepieciešams, lai ļautu šim lielajam masīvajam galdam sazināties ar šo mazo galdu, ir starpnieki, es tos saucu par galdniekiem.Nelieli mazi galdiņi, produkts, reģions un kalendārs. Mēs pievienosim budžetu šīm trim tabulām, pievienosimies faktiskajām šīm trim tabulām, un brīnumainā kārtā darbosies tabula Pivot. Labi, tāpēc mēs to darām šādi.
Vispirms man ir jāizveido savienotāji, tāpēc es ņemu šo lauku Produkts no A slejas un kopēju to uz F sleju, un pēc tam noklikšķiniet uz Dati, Noņemt dublikātus, noklikšķiniet uz Labi, un mums paliek maza maza tabula, 1 virsraksts 3 rindas. Tas pats, kas attiecas uz reģionu, paņemiet reģionus, Ctrl + C, pārejiet uz kolonnu G, Ielīmēt, Noņemt dublikātus, noklikšķiniet uz Labi, 3 rindas 1 galvenē, labi. Tagad attiecībā uz datumiem datumi nav vienādi, tie ir mēneša beigu datumi, tie faktiski tiek saglabāti kā mēneša beigu datumi, un šīs ir darba dienas. Es ņemšu abus sarakstus, Ctrl + C otro sarakstu un ielīmējiet to šeit, Ctrl + V, tad es ņemšu īsāko sarakstu, nokopēšu to un ielīmējiet to zemāk, labi. Un tas ir patiešām kaitinoši, ka, lai arī tie tiek glabāti kā datumi, tie tiek parādīti mēnešos, un, noņemot dublikātus, tie netiks rādīti vienādi.Tāpēc, pirms lietoju Noņemt dublikātus, tas ir jāmaina uz īsu datumu. Izvēlieties šos datus, Dati, Noņemt dublikātus, noklikšķiniet uz Labi un pēc tam nedaudz kārtojiet šeit, lai tie darbotos.
Labi, tagad es nevēlos ziņot pēc dienas datuma, tāpēc es šeit pievienošu kolonnu, uzmeklēšanas kolonnu ar mēnesi, un tas būs vienāds ar EOMONTH šajā datumā,, 0, kas mūs novedīs pie mēneša beigas. Tas formatēs to kā īsu datumu un nokopēs to uz leju, labi. Tagad mums katram no tiem ir jāveido Ctrl + T tabula, tāpēc no šejienes Ctrl + T, manam galdam ir skaistas galvenes. Mazie, tas neapzinās, ka tie ir galvenes tur augšā, tāpēc mums ir jāpārliecinās, ka atzīmējat to un Ctrl + T, labi, un viņi sauc šīs tabulas Table1, Table2, Table3, tiešām garlaicīgi nosaukumi, vai ne? Tāpēc es tos pārdēvēšu un saukšu to par BudTable, ProdTable, RegTable, manu CalTable un pēc tam par ActTable.
Mēs sākam no pašas pirmās tabulas, un, starp citu, mēs šodien neizmantosim PowerPivot, mēs to visu darīsim ar datu modeli. Tātad, Excel 2013 vai jaunāka versija, jums ir šī ieliktne, rakurstabula. Mēs atzīmēsim izvēles rūtiņu “Pievienot šos datus datu modelim”, noklikšķiniet uz Labi, un mēs saņemsim mūsu lauku sarakstu ar burvju pogu Visi, kas ļauj es izvēlos no visām piecām darbgrāmatas tabulām, Faktiskais, Budžets, Kalendārs, Produkts, Reģions. Labi, tāpēc skaitļi nāks no budžeta tabulas, es tur ievietošu budžetu un no tabulas Faktiskais es ievietošu faktisko, bet tad šeit ir viss pārējais tabulas rakurss. Jebkuriem citiem teksta laukiem, kurus mēs ievietosim rindu vai kolonnu zonā vai kā sagriezējus, tiem jābūt no galdniekiem, tiem jānāk no šīm tabulām starp tabulām.
Labi, tāpēc no kalendāra tabulas mēs ņemsim šo mēnesi lauku un novietosim to augšpusē, mēs tagad ignorēsim citas attiecības. Es veidošu attiecības, bet es gribu tās izveidot visas uzreiz. Un reģiona tabulu, ielieciet reģionus malā. Es varētu nolikt produktus malā, bet es faktiski izmantošu tabulu Produkti kā sagriezēju, tāpēc Analizējiet, Ievietojiet šķēlēju, atkal jādodas uz sadaļu Visi, ja vēl neesat izmantojis tabulu Produkti. Pārejiet uz sadaļu Visi, un jūs redzēsiet, ka Produkts ir pieejams, lai izveidotu no šiem produktiem līdzīgu šķēlēju. Labi tagad, šajā brīdī mēs neesam izveidojuši attiecības, tāpēc visi šie skaitļi ir nepareizi. Attiecības, kas mums jāizveido, mums ir jāizveido 3 tabulas no šīs mazās budžeta tabulas: viena - produktiem, viena - reģioniem, viena - kalendāram,tas ir 3 attiecības. Un tad mums kalendārā ir jāizveido sakari no tabulas Faktiskais līdz Produkta reģionam, tātad kopā 6 tabulas. Jā, tas noteikti būtu vieglāk, ja mums būtu PowerPivot, taču mums tas nav vai pieņemsim, ka mums tā nav.
Tāpēc es izmantošu vecmodīgi veidoto dialogu Izveidot šeit, kur kreisajā pusē ir tabula Budžets, un mēs izmantosim lauku Reģions un saistīsim to ar tabulu Reģions, lauku Reģions . Labi, 1/6 tiek izveidoti. Es izvēlēšos Izveidot, atkal no tabulas Budžets mēs ejam uz Produktu un pēc tam saistīšu to ar Produktu tabulu, ar Produktu, noklikšķiniet uz Labi. No tabulas Budžets lauka Datums dodamies uz tabulu Kalendārs un laukā Liktenis noklikšķiniet uz Labi, mēs esam pusceļā, labi. No tabulas Faktiskie dati dodamies uz Reģions, uz tabulu Reģions, noklikšķiniet uz Labi, no tabulas Faktiskie uz Produkts un no Faktiskie dati uz Kalendāru. Es patiesībā ņemšu Vērtības un likšu tam iet pa labi, labi. Noformējums, Pārskatu izkārtojums, Rādīt tabulas veidā, lai iegūtu priekšroku, Atkārtojiet visas vienumu etiķetes, labi,tas ir pilnīgi pārsteidzoši! Tagad mums ir šī mazā mazā tabula, 50-daži ieraksti šajā tabulā ar simtiem ierakstu, un mēs esam izveidojuši vienu rakurstabulu, pateicoties datu modelim. Katram, kur mēs varam redzēt budžetu, mēs varam redzēt ieņēmumus, tie ir sadalīti pa reģioniem, tie ir sadalīti pa mēnešiem un ir sadalāmi pa produktiem.
Tagad šis jēdziens man radās no Roba Kolija, kurš vada Power Pivot Pro, un Robs ir izveidojis daudz grāmatu, viņa jaunākā ir “Power Pivot un Power BI”. Es domāju, ka šis faktiski bija “Power Pivot Alchemy” grāmatā, es to redzēju, un es teicu “Nu, kaut arī man nav miljonu rindu, par kurām ziņot, izmantojot Power Pivot, šī būtu ir radījušas MILZĪGAS pārmaiņas manā dzīvē, jo ir divas neatbilstošu izmēru datu kopas un ir jāziņo no abiem. ” Šis piemērs un daudzi citi ir šajā grāmatā. Es galu galā saņemšu visu grāmatu aplādi, šķiet, ka tas prasīs divus ar pusi mēnešus. Bet jūs varat saņemt visu grāmatu šodien, tajā pašā laikā, iet tur, nopirkt grāmatu, 10 USD par e-grāmatu, 25 USD par drukāto grāmatu, un jums var būt visi šie padomi uzreiz.
Labi, šeit ir patiešām gara epizode: mums ir mazs budžets no augšas uz leju un dibens uz augšu, tie ir dažāda lieluma, taču, izmantojot datu modeli programmā Excel 2013 … Un, starp citu, ja jūs esat 2010. gadā, jūs varētu , teorētiski, dariet to, iegūstot pievienojumprogrammu Power Pivot, un veiciet visas šīs darbības 2010. gadā. Abas datu kopas izveidojiet Ctrl + T tabulā un pēc tam pievienojiet savas tabulas par visu, par ko vēlaties ziņot. rindas etiķete, kolonnas etiķete vai sagriezēji, tāpēc kopējiet šīs vērtības un noņemiet datumu dublikātus. Es faktiski paņēmu vērtības no abām tabulām, jo katrā bija dažas unikālas vērtības, un pēc tam es izmantoju EOMONTH, lai tur izkļūtu, padarot šīs galdnieku tabulas par kontrolējamām tabulām. Tas nav obligāti, taču es nosaucu visas 5 tabulas, jo, izveidojot šīs attiecības, ir vieglāk, nekā saukt par 1. tabulu,2. tabula, 3. tabula.
Tātad, sāciet no tabulas Budžets, Ievietot, PivotTable, atzīmējiet izvēles rūtiņu Datu modelis un pēc tam izveidojiet rakurstabulu, izmantojot Budžets un Faktiskais. Viss pārējais nāk no galdnieku tabulām, tāpēc reģiona un mēneša rindu un kolonnu apgabalā šķēlēji nāca no tabulas Produkts. Un pēc tam mums bija jāizveido 3 attiecības no Budžeta līdz galdniekiem, 3 attiecības no Faktiskās līdz galdniekiem, un mums ir pārsteidzoša rakurstabula. Tagad rīt mēs apskatīsim cilni Power Pivot un izveidosim dažus papildu aprēķinus. Tātad tas viss ir iespējams, tas ir, kad mēs vēlamies ievietot aprēķinātu lauku, tas ir, kad jums jāmaksā papildu 2 ASV dolāri mēnesī, lai iegūtu Office 365 Pro Plus versiju.
Nu hei, paldies Robam Kolijam no Power Pivot Pro par šo padomu un paldies jums par apstāšanos, mēs tiksimies nākamreiz uz citu netcast no!
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Podcast2016.xlsx
