„Excel“ duomenų modelis („Power Pivot“) leidžia prijungti didelį išsamų faktinių duomenų rinkinį prie aukščiausio lygio biudžeto naudojant stalių lenteles.
Biudžetai atliekami aukščiausiu lygiu - pajamos pagal produktus pagal regionus pagal mėnesį. Faktai laikui bėgant kaupiasi lėtai - sąskaita faktūra pagal sąskaitą faktūra, eilutė po eilutės. Palyginti nedidelį biudžeto failą su gausiais „Actuals“ duomenimis buvo amžinai skaudu. Man patinka šis Robo Collie, dar žinomo kaip „PowerPivotPro.com“, triukas.
Norėdami nustatyti pavyzdį, turite 54 eilučių biudžeto lentelę: viena eilutė per mėnesį vienam regionui vienam produktui.
Sąskaitos faktūros byla yra išsamios informacijos: šiais metais iki šiol buvo 422 eilutės.
Pasaulyje nėra VLOOKUP, kuris kada nors leistų jums suderinti šiuos du duomenų rinkinius. Tačiau dėka „Power Pivot“ (dar žinomo kaip duomenų modelis programoje „Excel 2013+“) tai tampa lengva.
Norėdami susieti du didesnius duomenų rinkinius, turite sukurti mažas mažas lenteles, kurias vadinu „prisijungėjais“. Mano atveju, produktas, regionas ir data yra bendri tarp dviejų lentelių. Produktų lentelė yra mažytė keturių langelių lentelė. Tas pats regionui. Sukurkite kiekvieną iš jų, nukopijuodami duomenis iš vienos lentelės ir naudodami Pašalinti dublikatus.
Dešinėje esančią kalendoriaus lentelę iš tikrųjų buvo sunkiau kurti. Biudžeto duomenys turi vieną eilutę per mėnesį, visada patenka į mėnesio pabaigą. Sąskaitos faktūros duomenys rodo dienos datas, paprastai darbo dienas. Taigi turėjau nukopijuoti lauką Data iš abiejų duomenų rinkinių į vieną stulpelį ir pašalinti dublikatus, kad įsitikinčiau, jog visos datos yra pateiktos. Tada aš =TEXT(J4,"YYYY-MM")sukūriau mėnesio stulpelį iš dienos datų.
Jei neturite viso papildinio „Power Pivot“, iš biudžeto lentelės turite sukurti suvestinę lentelę ir pažymėti langelį Pridėti šiuos duomenis prie duomenų modelio.
Kaip aptarta ankstesniame patarime, pridedant laukus prie suvestinės lentelės, turėsite apibrėžti šešis ryšius. Nors tai galite padaryti šešis kartus aplankę dialogo langą „Kurti ryšį“, aš suaktyvinau savo „Power Pivot“ priedą ir naudodamasis diagramos rodiniu apibrėžiau šešis ryšius.
Čia yra raktas į šį darbą: galite laisvai naudoti skaitinius laukus iš biudžeto ir iš faktinio. Bet jei norite „Regioną“, „Produktą“ arba „Mėnesį“ rodyti suvestinėje lentelėje, jie turi būti iš stalių lentelių!
Čia yra suvestinė lentelė su duomenimis iš penkių lentelių. A stulpelis ateina iš Regiono staliaus. 2 eilutė ateina iš „Calendar“ staliaus. Produkto pjaustyklė yra iš „Product“ staliaus. Biudžeto numeriai gaunami iš biudžeto lentelės, o faktiniai skaičiai - iš sąskaitos faktūros lentelės.
Tai veikia, nes stalių lentelės taiko filtrus lentelėje „Biudžetas“ ir „Faktinis“. Tai puiki technika ir parodo, kad „Power Pivot“ nėra skirta tik didiesiems duomenims.
Žiūrėti video
- Turite nedidelį biudžeto duomenų rinkinį iš viršaus į apačią
- Norite palyginti su apatinio faktinių duomenų rinkiniu
- Faktiniai duomenys gali būti gaunami iš sąskaitų faktūrų registro
- Duomenų modelis leis palyginti šiuos skirtingo dydžio duomenų rinkinius
- Padarykite abu duomenų rinkinius į „Ctrl + T“ lentelę
- Kiekvienam teksto laukui, apie kurį norite pranešti, sukurkite stalių lentelę
- Nukopijuokite vertes ir pašalinkite dublikatus
- Į datas galite įtraukti datas iš abiejų lentelių ir konvertuoti į mėnesio pabaigą
- Padarykite, kad staliai būtų „Ctrl + T“ lentelės
- Neprivaloma, bet naudinga įvardyti visas penkias lenteles
- Sukurkite suvestinę lentelę iš biudžeto ir pasirinkite duomenų modelį
- Sukurkite suvestinę lentelę naudodami „Budget“ ir „Real“ iš pradinių lentelių
- Visi kiti laukai turi būti iš stalių lentelių
- Pridėkite pjaustyklių pagal gaminį
- Sukurkite tris santykius nuo „Budget“ iki „Joiners“
- Sukurkite tris santykius nuo „Actual“ iki „Joiners“
- Rytoj: kaip lengviau užmegzti santykius naudojant „Power Pivot“ ir „DAX Formulas“
Vaizdo įrašo nuorašas
Sužinokite „Excel“ iš podcast'o, 2016 m. Serijos - biudžetas „iš viršaus į apačią“ ir „iš apačios į viršų“!
Ei, aš transliuoju visą šią knygą, viršutiniame dešiniajame kampe spustelėkite tą „i“ ir sekite grojaraštį.
Ei, aš tai nutrauksiu, tai Billas Jelenas nuo 15 minučių. Suprantu, kad dabar tai yra nepaprastai ilgas tinklalaidė, ir jums kyla pagunda tiesiog spustelėti tiesiai per ją, bet leiskite man jums tai pateikti. Jei naudojatės „Excel 2013“ ir kada nors turėjote mažą biudžeto lentelę ir didžiulę faktinių duomenų lentelę ir turite jas susieti kartu, tai yra nuostabus naujas „Excel 2013“ galimybė, kurią nedaug kas paaiškino , ir jūs tikriausiai apie tai nežinote. Jei tai jūs, jūs 2013 m., Ir jums reikia susieti šiuos du duomenų rinkinius, skirti laiko, galbūt šiandien, gal rytoj, galbūt įtraukti jį į stebimųjų sąrašą, tai verta, tai nuostabi technika.
Gerai, štai, ką mes turime, kairėje pusėje turime biudžetą, šį biudžetą, jis atliekamas aukščiausiu lygiu, iš viršaus į apačią, dešinėn kiekvienai produktų linijai, kiekvienam regionui, kiekvienam mėnesiui, yra biudžetas . Dešinėje pusėje nėra daug įrašų, skaičiuojant 55, bandome palyginti tai su aktualia. Faktai gaunami iš sąskaitų faktūrų registro, taigi turime regioną, produktą ir pajamas, tačiau tai yra atskiros sąskaitos faktūros, čia daug daugiau duomenų, mes jau įpusėjome metus, o aš jau turiu 423 įrašus. Gerai, tai kaip susieti šiuos 55 su šiais 423? Gali būti sunku padaryti su „VLOOKUP“, pirmiausia turėtumėte apibendrinti, bet, laimei, „Excel 2013“, duomenų modelis tai daro tikrai labai lengva. Tai, ko mums reikia, kad šis didelis ir didelis stalas galėtų bendrauti su šia maža lentele, yra tarpininkai, aš juos vadinu staliais.Mažos mažos lentelės, „Product“, „Region“ ir „Calendar“, mes sujungsime biudžetą su šiomis trimis lentelėmis, prie šių trijų lentelių prisidėsime ir stebuklingai veiks „Pivot“ lentelė. Gerai, todėl štai kaip mes tai darome.
Pirmiausia turiu sukurti jungiamuosius elementus, todėl šį lauką paimsiu iš A stulpelio ir nukopijuosiu į F stulpelį, tada - Duomenys, Pašalinti dublikatus, spustelėkite Gerai, ir mums liks maža maža lentelė, 1 antraštė 3 eilutės. Tas pats dalykas, susijęs su regionu, paimkite regionus, „Ctrl“ + C, pereikite prie G stulpelio „Įklijuoti“, „Pašalinti dublikatus“, spustelėkite Gerai, 3 eilutės 1 antraštė, gerai. Datos, datos nėra vienodos, tai yra mėnesio pabaigos datos, jos iš tikrųjų saugomos kaip mėnesio pabaigos datos ir tai yra darbo dienos. Paimsiu abu sąrašus, „Ctrl“ + C antrąjį sąrašą ir įklijuosiu jį čia, „Ctrl“ + V, tada paimsiu trumpesnį sąrašą, nukopijuosiu jį ir įklijuosiu žemyn, gerai. Ir tikrai erzina tai, kad, nors jos ir saugomos kaip datos, jos rodomos mėnesiais, o „Pašalinti dublikatus“ jos nematys tokios pačios.Taigi prieš naudodamas „Pašalinti dublikatus“, turiu jį pakeisti į trumpą datą. Pasirinkite tuos duomenis, Duomenys, Pašalinti dublikatus, spustelėkite Gerai, tada šiek tiek rūšiuokite čia, kad jie veiktų.
Gerai, dabar nenoriu pranešti pagal dienos datą, todėl čia pridėsiu stulpelį, paieškos stulpelį, kuriame parašyta Mėnuo, ir tai bus lygi EOMONTH tą datą,, 0, kuri mus išves mėnesio pabaigos. Tai suformatuos kaip trumpą datą ir nukopijuos ją žemyn, gerai. Dabar kiekvieną iš jų turime padaryti „Ctrl + T“ lentele, taigi iš čia „Ctrl + T“, mano lentelėje yra gražios antraštės. Maži, nesupranta, kad tai yra antraštės, todėl turime įsitikinti, kad pažymėjote tai ir „Ctrl + T“, gerai, ir jie šias lenteles vadina „Table1“, „Table2“, „Table3“, tikrai nuobodžiais pavadinimais, tiesa? Taigi aš pervardysiu tai ir pavadinsiu „BudTable“, „ProdTable“, „RegTable“, savo „CalTable“ ir tada „ActTable“.
Mes pradedame nuo pačios pirmos lentelės ir, beje, šiandien nenaudosime „PowerPivot“, visa tai atliksime naudodami duomenų modelį. Taigi, „Excel 2013“ ar naujesnės versijos, turite šį įterpimą, „PivotTable“. Pažymėsime langelį „Pridėti šiuos duomenis prie duomenų modelio“, spustelėkite Gerai ir gausime savo laukų sąrašą su stebuklingu mygtuku Viskas, kuris leidžia aš renkuosi iš visų penkių darbaknygės lentelių, Faktinis, Biudžetas, Kalendorius, Produktas, Regionas. Gerai, todėl skaičiai bus pateikti iš biudžeto lentelės, aš ten įdėsiu biudžetą, o iš faktinės lentelės aš įdėsiu faktinį, bet tada čia yra likusios „Pivot“ lentelės dalykas. Bet kokie kiti teksto laukai, kuriuos ketiname įdėti į eilutės ar stulpelio sritį arba kaip pjaustyklės, jie turi būti gaunami iš stalių, jie turi būti iš tų lentelių tarp lentelių.
Gerai, todėl iš „Kalendoriaus“ lentelės paimsime tą mėnesio lauką ir įdėsime jį į viršų, dabar nepaisysime kitų santykių. Aš kursiu santykius, bet noriu juos sukurti vienu metu. Regiono lentelėje padėkite regionus žemyn. Galėčiau įdėti produktus į šoną, bet aš iš tikrųjų naudosiu „Product“ lentelę kaip pjaustyklę, taigi, „Analyze“, „Insert Slicer“, vėl turite eiti į „All“, jei dar nenaudojote „Product“ lentelės. Taigi eikite į „Visi“ ir pamatysite, kad produktą galima sukurti kaip pjaustyklę iš tokių produktų. Gerai, šiuo metu mes nesukūrėme santykių, todėl visi šie skaičiai yra neteisingi. Santykius, kuriuos turime sukurti, turime sukurti 3 lenteles iš šios mažos biudžeto lentelės, vieną prie produktų, vieną į regionus, vieną į kalendorių,tai 3 santykiai. Tada kalendoriuje turime sukurti ryšius nuo „Actual“ lentelės iki „Product“ regiono, taigi iš viso 6 lenteles. Taip, tai tikrai būtų lengviau, jei turėtume „PowerPivot“, bet mes to neturime arba tarkime, kad neturime.
Taigi aš naudosiu senamadišką būdą „Sukurti dialogą čia“, kur kairėje pusėje yra lentelė „Biudžetas“, o mes naudosime lauką „Regionas“ ir susiesime tai su lentele „Regionas“, su „Region“ lauku. . Gerai, 1/6 yra sukurta. Pasirinksiu Sukurti, vėl iš biudžeto lentelės einame prie Produkto, tada susiesiu jį su Produkto lentele, su Produktu, spustelėkite Gerai. Iš biudžeto lentelės „Data“ lauko einame į lentelę „Kalendorius“, o lauke „Likimas“ spustelėkite Gerai, mes jau pusiaukelėje, gerai. Iš lentelės „Faktiniai duomenys“ einame į „Region“, į „Regiono“ lentelę, spustelėkite Gerai, iš lentelės „Faktiniai duomenys“ į „Produktą“ ir iš „Faktiniai duomenys“ į kalendorių. Aš iš tikrųjų imsiuosi Vertybių ir priversiu jas eiti šonu, gerai. Dizainas, Ataskaitos maketas, Rodyti lentelės forma, kad gautumėte norimą vaizdą, Pakartokite visas elementų etiketes, gerai,tai yra visiškai nuostabu! Dabar turime šią mažą mažą lentelę, 50-kai įrašų šioje lentelėje su šimtais įrašų, o duomenų modelio dėka sukūrėme vieną „Pivot“ lentelę. Kiekvienam, kur matome biudžetą, galime pamatyti pajamas, jos suskirstytos pagal regionus, suskirstytos pagal mėnesius ir skirstomos pagal produktus.
Ši idėja man pasirodė iš Robo Collie'o, kuris valdo „Power Pivot Pro“, ir Robas sukūrė daug knygų, naujausias - „Power Pivot and Power BI“. Manau, kad šis iš tikrųjų buvo „Power Pivot Alchemy“ knygoje. Aš mačiau tai ir pasakiau „Na tai, nors aš neturiu milijonų eilučių, kurias galėčiau pranešti per„ Power Pivot “, tai būtų padarė didžiulį pasikeitimą mano gyvenime, turėdamas du neatitinkančių dydžių duomenų rinkinius ir turėdamas ataskaitą iš jų abiejų. “ Šis pavyzdys ir daugelis kitų yra šioje knygoje. Aš galų gale gausiu visą knygų tinklalaidę, panašu, kad tai užtruks du su puse mėnesio. Bet jūs galite gauti visą knygą šiandien, tuo pačiu metu, nuvykti ten, nusipirkti knygą, 10 USD už elektroninę knygą, 25 USD už spausdintą knygą ir visus tuos patarimus galite gauti vienu metu.
Gerai, čia tikrai ilgas epizodas: mes turime mažą biudžetą iš viršaus į apačią ir faktiškai, jie yra skirtingo dydžio, tačiau naudojant duomenų modelį programoje „Excel 2013“ … Beje, jei esate 2010 m., Galėtumėte , teoriškai, atlikite tai gaudami „Power Pivot“ priedą ir atlikite visus šiuos veiksmus 2010 m. Padarykite abu duomenų rinkinius į „Ctrl + T“ lentelę ir sujunkite savo lenteles, kad rastumėte viską, apie ką norite pranešti. eilutės etiketė, stulpelio etiketė arba pjaustyklės, todėl nukopijuokite šias reikšmes ir pašalinkite datas. Aš iš tikrųjų paėmiau vertes iš abiejų lentelių, nes kiekvienoje buvo keletas unikalių reikšmių, tada aš panaudojau EOMONTH, kad ten išeitų, kad tos stalių lentelės būtų valdomos lentelės. Tai neprivaloma, bet aš pavadinau visas 5 lenteles, nes kuriant tuos santykius lengviau, o ne vadinti 1 lentele,2 lentelė, 3 lentelė.
Taigi, pradėkite nuo biudžeto lentelės, įterpimo, „PivotTable“, pažymėkite langelį „Duomenų modelis“, tada sukurkite „Pivot“ lentelę naudodami biudžetą ir faktinį. Visa kita gaunama iš stalių lentelių, todėl eilutės ir stulpelio srityje Regionas ir mėnuo, pjaustyklės atkeliavo iš lentelės Produktas. Tada mes turėjome sukurti 3 santykius nuo „Budget“ iki prisijungusių, 3 santykius nuo „Actual“ iki prisijungusių žmonių, ir mes turime nuostabią „Pivot“ lentelę. Dabar rytoj apžvelgsime „Power Pivot“ skirtuką ir sukursime keletą papildomų skaičiavimų. Taigi visa tai įmanoma, būtent tada, kai norime įterpti apskaičiuotą lauką, tai yra, kai jūs turite sumokėti papildomus 2 USD per mėnesį, kad gautumėte „Office Plus“ „Pro Plus“ versiją.
Na, ačiū Robui Collie'ui iš „Power Pivot Pro“ už šį patarimą ir ačiū jums, kad užsukote, pamatysime kitą kartą, kai pasirodys dar viena transliacija iš!
Atsisiųsti failą
Atsisiųskite failo pavyzdį čia: Podcast2016.xlsx
