Exceli andmemudel (Power Pivot) võimaldab teil liituda tabelite abil ühendada suure üksikasjaliku tegelike andmekogumi tipptaseme eelarvega.
Eelarved tehakse tipptasemel - tulud tooteridade kaupa piirkonniti kuude kaupa. Tegelikud kogunevad aja jooksul aeglaselt - arve arvete kaupa, rida üksuste kaupa. Väikese eelarvefaili võrdlemine mahukate tegelike andmetega on olnud igavesti piin. Mulle meeldib see Rob Collie ehk PowerPivotPro.com trikk.
Näite seadistamiseks on teil 54-realine eelarvetabel: üks rida kuus ühe piirkonna ja toote kohta.
Arvefail on detailide tasemel: tänavu on seni 422 rida.
Maailmas pole ühtegi VLOOKUP-i, mis võimaldaks teil kunagi nende kahe andmekogumi sobitada. Kuid tänu Power Pivotile (ehk ka andmemudelile Excel 2013+) muutub see lihtsaks.
Kahe suurema andmekogumi linkimiseks peate looma pisikesed väikesed tabelid, mida ma kutsun liitjateks. Minu puhul on toode, piirkond ja kuupäev kahe tabeli vahel ühised. Toote tabel on pisike neljarakuline tabel. Sama piirkond. Looge kõik neist, kopeerides andmed ühest tabelist ja kasutades käsku Eemalda duplikaadid.
Parempoolse kalendritabeli loomine oli tegelikult karmim. Eelarve andmetel on üks rida kuus, langedes alati kuu lõpule. Arve andmed näitavad päevakuupäevi, tavaliselt tööpäevi. Nii et pidin kopeerima välja Kuupäev mõlemast andmekogumist ühte veergu ja seejärel duplikaadid eemaldama, et veenduda, et kõik kuupäevad oleksid esindatud. Seejärel =TEXT(J4,"YYYY-MM")lõin igapäevaste kuupäevade põhjal veeru Kuu.
Kui teil pole täielikku Power Pivoti lisandmoodulit, peate looma tabelist Eelarve pöördtabeli ja märkige ruut Lisa need andmed andmemudelisse.
Nagu eelmises näpunäites räägiti, peate pöördtabelisse väljade lisamisel määratlema kuus seost. Kuigi saate seda teha dialoogi Suhte loomine kuue külastusega, käivitasin oma Power Pivoti lisandmooduli ja kasutasin kuue seose määratlemiseks skeemivaadet.
Selle töö tegemise võti on järgmine: võite kasutada eelarve ja tegeliku arvuvälju. Kuid kui soovite pöördetabelis näidata piirkonda, toodet või kuud, peavad need tulema tisleritabelitest!
Siin on pöördtabel, mille andmed pärinevad viiest tabelist. Veerg A on pärit piirkonna liitujalt. 2. rida tuleb Kalendri tislerilt. Toote viilutaja on pärit toote liitmikust. Eelarve numbrid pärinevad tabelist Eelarve ja tegelikud numbrid tabelist Arve.
See töötab, sest liidetabelid rakendavad tabelile Eelarve ja Tegelik filtreid. See on ilus tehnika ja näitab, et Power Pivot pole mõeldud ainult suurandmete jaoks.
Vaata videot
- Teil on väike ülalt alla eelarve andmekogum
- Soovite võrrelda tegeliku tegeliku andmehulgaga
- Tegelikud andmed võivad pärineda arvete registrist
- Andmemudel võimaldab teil võrrelda neid erineva suurusega andmekogumeid
- Tehke mõlemad andmekogumid tabelisse Ctrl + T
- Looge iga tekstivälja jaoks, mille kohta soovite aru anda, liitumistabel
- Kopeerige väärtused ja eemaldage duplikaadid
- Kuupäevade jaoks saate lisada kuupäevad mõlemast tabelist ja teisendada kuu lõpuks
- Pange liitujad olema Ctrl + T tabelid
- Valikuline, kuid kasulik kõigi viie tabeli nimetamiseks
- Looge eelarvest liigendtabel ja valige andmemudel
- Ehitage pöördtabel, kasutades algsetest tabelitest eelarvet ja tegelikku
- Kõik muud väljad peavad tulema tisleritabelitest
- Lisage viilutajad toote järgi
- Looge kolm suhet alates eelarvest kuni liitujateni
- Looge kolm suhet tegelikust liitujani
- Homme: kuidas on Power Pivoti ja DAX-vormelite abil suhete loomine lihtsam
Video ärakiri
Õppige Exceli podcastist, 2016. aasta episoodist - ülalt alla eelarve vs tegelikud alt üles üles!
Hei, podcastin kogu seda raamatut, klõpsake paremas ülanurgas oleval tähel „i” ja järgige esitusloendit.
Hei, katkestan selle, siin on Bill Jelen 15 minuti pärast. Mõistan, et nüüd on see uskumatult pikk taskuhäälingusaade ja teil on kiusatus lihtsalt sellest otse läbi klõpsata, kuid lubage mul teile sellest lihtsalt anda. Kui olete rakenduses Excel 2013 ja teil on kunagi olnud väike eelarvetabel ja tohutu tegelike andmete tabel ning peate need koos kaardistama, on see hämmastav uus võime, mis meil on Excelis 2013 ja mida pole paljud inimesed selgitanud ja te ilmselt ei tea sellest. Kui see olete sina, siis olete 2013. aastal ja peate kaardistama need kaks andmekogumit, leidke aeg, võib-olla täna, võib-olla homme, võib-olla lisage see jälgimisnimekirja, see on seda väärt, see on hämmastav tehnika.
Hästi, siin on see, mis meil on, vasakul pool on eelarve, see eelarve, seda tehakse ülemisel tasemel, ülalt alla, paremal iga tootesarja, piirkonna ja kuu jaoks, eelarve . Siin pole palju kirjeid, kokku 55, paremal pool, proovime seda tegelikega võrrelda. Tegelikud andmed pärinevad arvete registrist, nii et meil on piirkond, toode ja tulud, kuid need on üksikud arved, siin on palju rohkem andmeid, oleme juba aasta poole peal ja mul on juba 423 kirjet. Hästi, siis kuidas kaardistada need 55 nende 423-ga? VLOOKUP-iga võib olla raske teha, peate kõigepealt kokku võtma, kuid õnneks teeb Excel 2013-s andmemudel selle tõesti väga lihtsaks. See, mida peame lubama sellel suurel massiivsel laual selle pisikese lauaga suhelda, on vahendajad, ma nimetan neid tisleriteks.Väikesed väikesed tabelid, toode, piirkond ja kalender, ühendame eelarve nende kolme tabeliga, liidame tegeliku nende kolme tabeliga ja imekombel töötab Pivoti tabel. Hästi, nii et kuidas me seda teeme.
Esmalt pean looma liitujad, nii et võtan selle veeru A veerust ja kopeerin selle veergu F ning seejärel klõpsake nuppu Data, Remove Duplicates, klõpsake nuppu OK ja meile jääb väike väike tabel, 1 pealkiri 3 rida. Sama asi piirkonnas, võtke piirkonnad, Ctrl + C, minge veergu G, Kleebi, Eemalda duplikaadid, klõpsake nuppu OK, 3 rida 1 päis, olgu. Kuupäevade puhul pole kuupäevad samad, need on kuu lõpu kuupäevad, need on tegelikult salvestatud kuude lõpu kuupäevadena ja need on tööpäevad. Võtan mõlemad loendid, Ctrl + C teise loendi ja kleepin selle siia, Ctrl + V, siis võtan lühema loendi, kopeerin selle ja kleepin selle allapoole, korras. Ja see on tõesti tüütu, et kuigi need on salvestatud kuupäevadena, kuvatakse need kuudena ja eemaldades duplikaadid ei näe neid samadena.Nii et enne kui eemaldan duplikaadid, pean selle muutma lühikeseks kuupäevaks. Valige need andmed, Data, Remove Duplicates, klõpsake nuppu OK ja seejärel toimige selle saamiseks veidi.
Hästi, nüüd ei taha ma aru anda päevakuupäeva järgi, seega lisan siia veeru, otsingu veeru, kus on kiri Kuu, ja see on võrdne EOMONTH sellel kuupäeval, 0, mis viib meid välja kuu lõpp. See vormistab selle lühikese kuupäevana ja kopeerib selle alla, olgu. Nüüd peame need kõik muutma Ctrl + T tabeliks, nii et siit Ctrl + T, Minu tabelis on päised, ilusad. Väikesed ei mõista, et need on seal päised, nii et me peame kindlasti märkima selle ja Ctrl + T, olgu, ja nad kutsuvad neid tabeleid Tabelid1, Tabel2, Tabel3, tõesti igavad nimed, eks? Nii nimetan need ümber ja nimetan seda BudTable'iks, ProdTable'iks, RegTable'iks, minu CalTable'iks ja siis ActTable'iks.
Alustame kõige esimesest tabelist ja muide, me ei hakka täna PowerPivoti kasutama, seda kõike teeme andmemudeliga. Nii et teil on Excel 2013 või uuem, teil on see Lisa, PivotTable-kaart, me märkime ruudu „Lisa need andmed andmemudelisse”, klõpsake nuppu OK ja saame oma väljade loendi maagilise nupuga Kõik, mis võimaldab Valin töövihiku kõigi viie tabeli vahel: Tegelik, Eelarve, Kalender, Toode, Piirkond. Hästi, nii et numbrid tulevad tabelist Eelarve, panen eelarve sinna ja tabelist Tegelik panen tegeliku sinna, kuid siis on siin ülejäänud Pivoti tabeli asi. Kõik muud tekstiväljad, mille me paneme rea- või veerualale või viilutajatena, peavad need tulema liitjatelt, need peavad tulema nendelt tabelite vahel tabelite vahel.
Hästi, nii et võtame tabelist Kalender selle välja välja Kuu ja asetame selle ülaossa, ignoreerime praegu muid suhteid. Ma loon suhteid, kuid tahan need kõik korraga luua. Ja tabel Piirkond pange piirkonnad alla. Ma võiksin tooteid küljele panna, kuid tegelikult hakkan tootetabelit viilutajana kasutama, nii et Analüüsige, lisage viilutaja, peate uuesti minema jaotisse Kõik, kui te pole veel tootetabelit kasutanud. Nii et minge jaotisse Kõik ja näete, et toodet on võimalik sellistest toodetest viilutajana luua. Olgu, praegu pole me suhteid loonud, nii et kõik need arvud on valed. Ja seosed, mida peame looma, peame sellest väikesest eelarvetabelist looma 3 tabelit, üks toodetele, üks piirkondadele, üks kalendrile,see on 3 suhet. Ja siis peame looma seosed kalendrisse Tegelik tabel ja Toote piirkond, seega kokku 6 tabelit. Ja jah, see oleks kindlasti lihtsam, kui meil oleks PowerPivot, kuid meil pole või oletame, et meil pole.
Ja seega hakkan kasutama vanamoodsat moodi, dialoogi Loo siin, kus vasakul on tabel Eelarve, ja kasutame välja Regioon ning seostame selle tabeliga Regioon, väljaga Piirkond . Hästi, 1/6 on loodud. Ma valin Loo, jälle läheme tabelist Eelarve me toote juurde ja linkin selle tabeli Toote juurde, tootele ja klõpsake nuppu OK. Eelarve tabelist Kuupäev väljalt läheme tabelisse Kalender ja väljal Saatus klõpsake nuppu OK, oleme poole peal, olgu. Tabelist Tegelikud liigume regioon, tabelisse Piirkond klõpsake nuppu OK, tabelist Tegelikud tootele ja tabelist Tegelikud kalendrisse. Ma võtan tegelikult Väärtused ja panen selle mööda külgi minema, olgu. Kujundus, aruande paigutus, kuva tabelivormis, et saada eelistatud vaade, korrake kõiki üksuste silte, olgu,see on täiesti hämmastav! Nüüd on meil see pisike väike tabel, 50-mõned kirjed selles sadade kirjetega tabelis ja tänu andmemudelile oleme loonud ühe Pivoti-tabeli. Igaühe kohta, kus näeme eelarvet, näeme ka tulusid, need on jaotatud piirkondade kaupa, jaotatud kuude kaupa ja jaotatavad toodete kaupa.
Nüüd jõudis see kontseptsioon minult Rob Collielt, kes juhib Power Pivot Pro-d, ja Rob on loonud seal palju raamatuid, tema viimane neist on “Power Pivot ja Power BI”. Ma arvan, et see üks oli tegelikult raamatus „Power Pivot Alchemy”, see oli see, mida ma nägin ja ütlesin: „Noh, kuigi mul pole miljoneid ridu Power Pivoti kaudu teatamiseks, oleks see on teinud minu elus tohutut muutust, kuna mul on kaks sobimatu suurusega andmekogumit ja peate mõlemalt aru andma. " Noh, see näide ja paljud teised on selles raamatus, ma saan lõpuks kogu raamatu taskuhäälingusaate, tundub, et see võtab kaks ja pool kuud. Kuid saate kogu raamatu samal ajal hankida, minna sinna, osta raamat, 10 dollarit e-raamatu eest, 25 dollarit trükitud raamatu eest ja teil on kõik need näpunäited korraga.
Hästi, siin on tõesti väga pikk episood: meil on väike ülalt alla eelarve ja põhi ülespoole. Tegelikud, need on erineva suurusega, kuid kasutades andmemudelit rakenduses Excel 2013 … Ja muide, kui olete 2010. aastal, võiksite tehke seda teoreetiliselt Power Pivoti pistikprogrammi hankimisega ja tehke kõik need toimingud tagasi 2010. aastal. Tehke mõlemad andmekogumid tabelisse Ctrl + T ja seejärel ühendage oma tabelid kõigega, millest soovite aru anda. rea silt või veeru silt või viilutajad, nii et kopeerige need väärtused üle ja eemaldage kuupäevade duplikaadid. Tegelikult võtsin väärtused mõlemast tabelist, sest mõlemas oli mõned unikaalsed väärtused, ja siis kasutasin EOMONTHi selleks, et sealt välja tulla, muutes need tisleritabelid kontrollitavateks tabeliteks. See on valikuline, kuid nimetasin kõik 5 tabelit, sest nende suhete loomisel on lihtsam kui tabel1,Tabel2, tabel3.
Ja alustage tabelist Eelarve, Lisa, PivotTable, märkige ruut Data Model ja seejärel ehitage Pivoti tabel, kasutades eelarvet ja tegelikku. Kõik muu pärineb tisleritabelitest, nii et rea ja veeru piirkonnas olevad regioon ja kuu, viilutajad tulid tabelist Product. Ja siis pidime looma 3 suhet eelarvest liitujate juurde, 3 suhet tegelikust liitujate juurde ja meil on hämmastav Pivoti tabel. Nüüd homme vaatame vahekaarti Power Pivot ja loome täiendavaid arvutusi. Nii et see kõik on võimalik, see on siis, kui me tahame sisestada arvutatud välja, siis peate maksma täiendavad 2 dollarit kuus, et saada Office 365 Pro Plusi versioon.
Hei hei, tänu Rob Collie'le Power Pivot Pro'st selle näpunäite eest ja tänu teile, et peatusite, näeme järgmisel korral järgmise netisaate jaoks!
Laadige fail alla
Laadige näidisfail alla siit: Podcast2016.xlsx
