Excel-tietomallin (Power Pivot) avulla voit liittää suuren yksityiskohtaisen tosiasiallisen tietojoukon ylätason budjettiin puuseppätaulukoiden avulla.
Budjetit tehdään ylimmällä tasolla - tuotot tuotekohtaisesti alueittain kuukausittain. Tosiasiat kertyvät hitaasti ajan myötä - lasku laskeittain, rivi rivikohtaisesti. Pienen budjettitiedoston vertaaminen laajaan Actuals-tietoon on ollut ikuisesti tuskaa. Rakastan tätä Rob Collien, alias PowerPivotPro.comin, temppua.
Esimerkin luomiseksi sinulla on 54-rivinen budjettitaulukko: yksi rivi kuukaudessa aluetta ja tuotetta kohden.
Laskutustiedosto on yksityiskohtaisella tasolla: 422 riviä tähän mennessä.
Maailmassa ei ole VLOOKUPia, joka koskaan antaisi sinun yhdistää nämä kaksi tietojoukkoa. Mutta Power Pivotin (alias Data Model in Excel 2013+) ansiosta tästä tulee helppoa.
Sinun on luotava pienet pienet taulukot, joita kutsun "liittäjiksi", jotta voit linkittää kaksi suurempaa tietojoukkoa. Minun tapauksessani Tuote, Alue ja Päivämäärä ovat yhteisiä kahden taulukon välillä. Tuotetaulukko on pieni nelisoluinen taulukko. Sama alueelle. Luo kukin näistä kopioimalla tiedot yhdestä taulukosta ja käyttämällä Poista kopiot -toimintoa.
Oikealla olevan kalenteritaulukon luominen oli todella vaikeaa. Budjettitiedoissa on yksi rivi kuukaudessa, aina kuukauden loppuun. Laskutustiedot näyttävät päivät, yleensä arkipäivät. Joten minun piti kopioida Päivämäärä-kenttä molemmista tietojoukoista yhteen sarakkeeseen ja poistaa sitten kaksoiskappaleet varmistaaksesi, että kaikki päivämäärät ovat edustettuina. Sitten tapasin =TEXT(J4,"YYYY-MM")luoda kuukausisarakkeen päivittäisistä päivämääristä.
Jos sinulla ei ole täydellistä Power Pivot -apuohjelmaa, sinun on luotava pivot-taulukko Budjetti-taulukosta ja valittava Lisää nämä tiedot tietomalliin -valintaruutu.
Kuten edellisessä vihjeessä keskusteltiin, kun lisäät kenttiä pivot-taulukkoon, sinun on määritettävä kuusi suhdetta. Vaikka voit tehdä tämän kuudella käynnillä Luo suhde -valintaikkunaan, käynnistin Power Pivot -lisäosan ja käytin kaavionäkymää kuuden suhteen määrittämiseen.
Tässä on avain kaiken tämän tekemiseen: Voit vapaasti käyttää Budjetin ja Todellisen numerokenttiä. Mutta jos haluat näyttää alueen, tuotteen tai kuukauden pivot-taulukossa, niiden on oltava peräisin pöytätaulukoista!
Tässä on pivot-taulukko, jossa on tietoja viidestä taulukosta. Sarake A tulee alueen puuseppäiltä. Rivi 2 tulee Kalenterin puuseppäiltä. Tuotteen leikkuri on tuotesarjasta. Budjettinumerot tulevat Budjetti-taulukosta ja Todelliset numerot Lasku-taulukosta.
Tämä toimii, koska puuseppätaulukot käyttävät suodattimia Budjetti- ja Todellinen-taulukoihin. Se on kaunis tekniikka ja osoittaa, että Power Pivot ei ole tarkoitettu vain isoille tiedoille.
Katso video
- Sinulla on pieni ylhäältä alas budjettitietojoukko
- Haluat verrata alhaalta ylös toteutuneiden tietojen tietojoukkoon
- Tosiasiat saattavat tulla laskurekisteristä
- Tietomallin avulla voit verrata näitä erikokoisia tietojoukkoja
- Tee molemmista tietojoukoista Ctrl + T-taulukko
- Luo liitintaulukko jokaiselle tekstikentälle, jonka haluat raportoida
- Kopioi arvot ja poista kaksoiskappaleet
- Päivämääriä varten voit sisällyttää päivämäärät molemmista taulukoista ja muuntaa kuukauden loppuun
- Tee liittimistä Ctrl + T-taulukoita
- Valinnainen, mutta hyödyllinen kaikkien viiden taulukon nimeämiseksi
- Luo pivot-taulukko Budgetista ja valitse tietomalli
- Luo pivot-taulukko alkuperäisistä taulukoista käyttämällä Budjetti ja Todellinen
- Kaikkien muiden kenttien tulee olla puusepäntaulukoista
- Lisää viipaleet tuotteittain
- Luo kolme suhdetta Budgetista Joinersiin
- Luo kolme suhdetta Actualista Joinersiin
- Huomenna: kuinka suhteiden luominen on helpompaa Power Pivot- ja DAX-kaavojen avulla
Videon transkriptio
Opi Excel podcastista, jakso 2016 - ylhäältä alas -budjetti vs alhaalta ylöspäin -tilanne!
Hei, lähetän podcasting tätä koko kirjaa, napsauta oikeaa yläkulmaa ”i” ja seuraa soittolistaa.
Hei, aion keskeyttää tämän, tämä on Bill Jelen 15 minuutin päästä. Ymmärrän nyt, että tämä on uskomattoman pitkä podcast, ja sinulla on kiusaus vain napsauttaa sitä läpi, mutta anna minun vain antaa sinulle tämä lyhyt. Jos olet Excel 2013: ssa ja sinulla on koskaan ollut pieni budjettitaulukko ja massiivinen tosiasiataulukko, ja sinun on kartoitettava ne yhteen, tämä on hämmästyttävä uusi kyky, joka meillä on Excel 2013: ssa, jota monet ihmiset eivät ole selittäneet. , ja et todennäköisesti tiedä siitä. Jos tämä olet sinä, olet vuonna 2013, ja sinun on kartoitettava nämä kaksi tietojoukkoa, vie aikaa, ehkä tänään, ehkä huomenna, ehkä lisää se tarkkailulistalle, se on sen arvoista, se on hämmästyttävä tekniikka.
Selvä, tässä on mitä meillä on, vasemmalla puolella on budjetti, tämä budjetti, se tehdään ylimmällä tasolla, ylhäältä alas, oikealle jokaiselle tuotelinjalle, jokaiselle alueelle, kuukaudelle, on budjetti . Oikealla puolella ei ole paljon levyjä, lukumäärä 55, yritämme verrata tätä todellisuuteen. Tosiasiat tulevat laskurekisteristä, joten meillä on alue, tuote ja liikevaihto, mutta ne ovat yksittäisiä laskuja, paljon enemmän tietoja täällä, olemme jo vuoden puolivälissä, ja minulla on jo 423 tietuetta. Selvä, niin miten kartoitat nämä 55 näihin 423: een? VLOOKUPin kanssa voi olla vaikeaa, sinun on ensin tehtävä yhteenveto, mutta onneksi Excel 2013: ssa tietomalli tekee siitä todella, todella helppoa. Mitä meidän on sallittava tämän suuren massiivisen pöydän kommunikoida tämän pienen pöydän kanssa, ovat välittäjiä, kutsun heitä liittäjiksi.Pienet pienet pöydät, Tuote, Alue ja Kalenteri, aiomme liittää budjetin näihin kolmeen taulukkoon, aiomme liittyä varsinaiseen näihin kolmeen taulukkoon, ja ihmeen mukaan Pivot-taulukko toimii. Selvä, joten näin teemme.
Ensinnäkin minun on luotava liittimet, joten otan tämän tuotekentän sarakkeesta A ja kopioin sen sarakkeeseen F ja sitten Data, Poista kaksoiskappaleet, napsauta OK, ja meille jää pieni pieni taulukko, 1 otsikko 3 riviä. Sama asia alueelle: ota alueet, Ctrl + C, siirry sarakkeeseen G, Liitä, Poista kaksoiskappaleet, napsauta OK, 3 riviä 1 otsikko, ok. Nyt päivämäärät, päivämäärät eivät ole samat, nämä ovat kuukauden päättymispäiviä, ne tosiasiallisesti tallennetaan kuukauden päättymispäivinä ja nämä ovat arkipäiviä. Otan molemmat luettelot, Ctrl + C toisen luettelon ja liitän sen tähän, Ctrl + V, sitten aion ottaa lyhyemmän luettelon, kopioida sen ja liittää sen alla, okei. Ja on todella ärsyttävää, että vaikka nämä tallennetaan päivämäärinä, ne näkyvät kuukausina, ja Poista kopiot eivät näe niitä samoina.Joten ennen kuin käytän Poista kopiot, minun on vaihdettava se lyhyeksi päivämääräksi. Valitse kyseiset tiedot, Data, Poista kaksoiskappaleet, napsauta OK ja lajittele sitten hieman täällä saadaksesi ne toimimaan.
Selvä, nyt en halua raportoida päivittäisen päivämäärän mukaan, joten aion lisätä tähän sarakkeen, hakusarakkeen, jossa lukee Kuukausi, ja tämä on yhtä suuri EOMONTH kyseisenä päivänä, 0, joka saa meidät kuukauden lopussa. Se muotoilee sen lyhyeksi päivämääräksi ja kopioi sen alas, kunnossa. Nyt meidän on tehtävä jokaisesta näistä Ctrl + T-taulukko, joten täältä Ctrl + T, taulukossa on otsikot, kauniit. Pienet, se ei ymmärrä, että nämä ovat ylätunnisteita, joten meidän on varmistettava, että merkitsemme tämän ja Ctrl + T, ok, ja he kutsuvat näitä taulukoita Taulukot1, Taulukot2, Taulukot3, todella tylsät nimet, eikö? Joten aion nimetä nämä uudelleen ja kutsua sitä BudTable, ProdTable, RegTable, minun CalTable ja sitten ActTable, ok.
Aloitamme aivan ensimmäisestä taulukosta, ja muuten emme aio käyttää PowerPivotia tänään, aiomme tehdä kaiken tämän tietomallin kanssa. Joten, Excel 2013 tai uudempi, sinulla on tämä Lisää, pivot-taulukko, aiomme tarkistaa "Lisää nämä tiedot tietomalliin" -valintaruudun, napsauta OK ja saamme kenttäluettelomme maagisella Kaikki-painikkeella, jonka avulla minä valitsen työkirjan kaikista viidestä taulukosta, Todellinen, Budjetti, Kalenteri, Tuote, Alue. Selvä, joten numerot tulevat Budjettitaulukosta, laitan budjetin sinne ja Todellinen-taulukosta laitan todellisen sinne, mutta sitten tässä on asia muulle Pivot-taulukolle. Muiden tekstikenttien, jotka aiomme sijoittaa rivialueelle tai sarakealueelle tai viipaleina, niiden on oltava peräisin liittimistä, niiden on oltava peräisin pöytien välissä olevista taulukoista.
Selvä, joten Kalenteri-taulukosta otamme kyseisen kuukauden kentän ja laitamme sen alkuun, jätämme muut suhteet huomiotta nyt. Luon suhteet, mutta haluan luoda ne kaikki kerralla. Ja Alue-taulukko, laita alueet sivulle. Voisin laittaa tuotteita sivuun, mutta aion itse asiassa käyttää Tuotetaulukkoa viipaleina, joten Analysoi, Lisää viipale, sinun on jälleen mentävä kohtaan Kaikki, jos et ole vielä käyttänyt Tuotetaulukkoa. Joten mene All-kohtaan ja näet, että Tuote on mahdollista luoda viipaleina tuotteista, kuten tuollainen. Okei, tässä vaiheessa emme ole luoneet suhteita, joten kaikki nämä luvut ovat väärät. Ja suhteet, jotka meidän on luotava, meidän on luotava 3 taulukkoa tästä pienestä budjettitaulukosta, yksi tuotteille, yksi alueille, yksi kalenterille,se on 3 suhdetta. Ja sitten meidän on luotava suhteet Todellinen-taulukosta Tuote-alueeseen Kalenterissa, joten yhteensä 6 taulukkoa. Ja kyllä, tämä olisi varmasti helpompaa, jos meillä olisi PowerPivot, mutta emme ole tai oletetaan, ettei meillä ole.
Joten aion käyttää vanhanaikaista tapaa, Luo-valintaikkunaa täällä, jossa meillä on Budjetti-taulukko vasemmalla, ja aiomme käyttää Alue-kenttää ja liittää sen Alue-taulukkoon, Alue-kenttään . Selvä, 1/6 luodaan. Valitsen Luo, jälleen Budjettitaulukosta siirrymme Tuotteeseen ja linkitän sen Tuotetaulukkoon, Tuotteeseen ja napsauta OK. Budjetti-taulukosta Päiväys-kenttä, siirrymme Kalenteri-taulukkoon ja kohtalokenttä, napsauta OK, olemme puolivälissä, okei. Siirry Tosiasiat-taulukosta Alue, Alue-taulukkoon, napsauta OK, Tosiasiat-taulukosta Tuote ja Tosiasiat-taulukosta Kalenteri. Aion todella ottaa arvot ja saada sen menemään alas, okei. Suunnittelu, Raportin asettelu, Näytä taulukkomuodossa saadaksesi haluamani näkymän, Toista kaikki tuotetarrat, ok,tämä on aivan uskomatonta! Nyt meillä on tämä pieni pieni taulukko, 50-tietueet tässä taulukossa, jossa on satoja tietueita, ja olemme luoneet yhden Pivot-taulukon tietomallin ansiosta. Jokaisesta, josta voimme nähdä budjetin, voimme nähdä tulot, se on jaoteltu alueittain, se on jaoteltu kuukausittain ja se on leikattu tuotekohtaisesti.
Nyt tämä konsepti tuli minulle Rob Collielta, joka johtaa Power Pivot Prota, ja Rob on luonut siellä paljon kirjoja, viimeisin niistä on “Power Pivot and Power BI”. Luulen, että tämä oli itse asiassa "Power Pivot Alchemy" -kirjassa, minä näin tämän ja sanoin "No tämä, vaikka minulla ei ole miljoonia rivejä raportoitavaksi Power Pivotin kautta, tämä olisi minulla on ollut valtava ero elämässäni, koska minulla on kaksi erilaista kokoista tietojoukkoa ja heidän on raportoitava molemmilta. No, tämä esimerkki ja monet muut ovat tässä kirjassa, saan lopulta koko kirjan podcastin, joka näyttää siltä, että se kestää kaksi ja puoli kuukautta. Mutta voit saada koko kirjan tänään, samaan aikaan, mennä sinne, ostaa kirja, 10 dollaria e-kirjasta, 25 dollaria painotuotteesta, ja voit saada kaikki nämä vinkit kerralla.
Selvä, todella pitkä jakso täällä: meillä on pieni ylhäältä alas budjetti ja pohja ylös Todellinen, ne ovat erikokoisia, mutta käyttämällä tietomallia Excel 2013: ssa … Ja muuten, jos olet vuonna 2010, voit , teoreettisesti, tee tämä hankkimalla Power Pivot -apuohjelma ja käymällä kaikki nämä vaiheet läpi vuonna 2010. Tee molemmista tietojoukoista Ctrl + T-taulukko ja liitä sitten taulukkoihisi kaikki mitä haluat raportoida, rivin otsikko tai sarake otsikko tai viipaleet, joten kopioi nämä arvot ja poista päivämäärien kaksoiskappaleet. Otin oikeastaan arvot molemmista taulukoista, koska jokaisessa oli joitain ainutlaatuisia arvoja, ja sitten käytin EOMONTHia päästäkseen sinne, jotta nämä puuseppätaulukot olisivat kontrolloituja taulukoita. Se on valinnainen, mutta nimitin kaikki 5 taulukkoa, koska helpompaa, kun asetat näitä suhteita sen sijaan, että niitä kutsutaan taulukoksi 1,Taulukko2, taulukko3.
Joten, aloita Budjetti-taulukosta, Lisää, PivotTable, tarkista valintaruutu Tietomalli ja rakenna sitten Pivot-taulukko Budjetilla ja Todellisilla. Kaikki muu tulee puuseppätaulukoista, joten alue ja kuukausi rivin ja sarakkeen alueella, viipaleet tulivat Tuotetaulukosta. Ja sitten meidän piti luoda 3 suhdetta Budjetista liittyjiin, 3 suhdetta Todellisista liittäjiin, ja meillä on upea Pivot-taulukko. Nyt huomenna katsomme Power Pivot -välilehden käyttöä ja luomme lisää laskelmia. Joten kaikki tämä on mahdollista, kun haluamme lisätä lasketun kentän, silloin sinun on maksettava ylimääräinen 2 dollaria kuukaudessa saadaksesi Office 365: n Pro Plus -version.
Hei, kiitos Rob Collie Power Pivot Prosta tästä vihjeestä ja kiitos, että pysähdyit, näemme sinut seuraavan kerran uudelle Netcastille!
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Podcast2016.xlsx
