Excel podatkovni model (Power Pivot) omogućuje vam povezivanje velikog broja detaljnih podataka sa stvarnim podacima s proračunom najviše razine pomoću stolara za stolove.
Proračuni se vrše na najvišoj razini - prihod prema liniji proizvoda po regijama po mjesecima. Stvarnosti se polako akumuliraju - faktura po faktura, stavka po stavka. Usporedba male datoteke s proračunom i opsežnih podataka o stvarnosti zauvijek je bila muka. Volim ovaj trik Roba Colliea, zvanog PowerPivotPro.com.
Da biste postavili primjer, imate proračunsku tablicu od 54 retka: jedan redak mjesečno po regiji po proizvodu.
Datoteka s fakturom nalazi se na razini detalja: do sada je ove godine 422 redaka.
Ne postoji VLOOKUP na svijetu koji će vam omogućiti da podudarite ova dva skupa podataka. No, zahvaljujući Power Pivotu (poznat i kao podatkovni model u programu Excel 2013+) ovo postaje jednostavno.
Morate stvoriti male tablice koje nazivam "stolari" kako bi povezali dva veća skupa podataka. U mom su slučaju proizvod, regija i datum zajednički između dvije tablice. Tablica proizvoda je maleni stol s četiri stanice. Isto kao i za regiju. Stvorite svaku od njih kopiranjem podataka iz jedne tablice i upotrebom Ukloni duplikate.
Tablica kalendara s desne strane zapravo je bila teža za izradu. Podaci proračuna imaju jedan redak mjesečno, uvijek padaju na kraj mjeseca. Podaci na fakturi prikazuju dnevne datume, obično radne dane. Dakle, morao sam kopirati polje Datum iz oba skupa podataka u jedan stupac, a zatim ukloniti duplikate kako bih bio siguran da su predstavljeni svi datumi. Tada sam koristio =TEXT(J4,"YYYY-MM")izradu stupca Month iz dnevnih datuma.
Ako nemate puni programski dodatak Power Pivot, trebate stvoriti zaokretnu tablicu iz tablice Proračun i potvrditi okvir za Dodaj ove podatke u model podataka.
Kao što je rečeno u prethodnom savjetu, dok dodajete polja u zaokretnu tablicu, morat ćete definirati šest odnosa. Iako ste to mogli učiniti sa šest posjeta dijaloškom okviru Stvaranje odnosa, pokrenuo sam svoj dodatak Power Pivot i upotrijebio prikaz dijagrama za definiranje šest odnosa.
Evo ključa za sve ovo: Slobodno možete koristiti numerička polja iz proračuna i iz stvarnosti. Ali ako želite prikazati regiju, proizvod ili mjesec u zaokretnoj tablici, oni moraju dolaziti iz stolova za spajanje!
Evo pivot tablice s podacima koji dolaze iz pet tablica. Stupac A dolazi od stolara Regije. Red 2 dolazi od stolara za Kalendar. Rezač proizvoda je od stolara proizvoda. Brojevi proračuna dolaze iz tablice Proračun, a stvarni brojevi dolaze iz tablice Račun.
To djeluje jer stolovi za pridruživanje primjenjuju filtre na tablicu Proračun i Stvarno. To je prekrasna tehnika koja pokazuje da Power Pivot nije namijenjen samo velikim podacima.
Gledaj video
- Imate mali skup proračunskih podataka odozgo prema dolje
- Želite usporediti sa stvarnim nizom stvarnih podataka
- Stvarni podaci mogu doći iz registra računa
- Model podataka omogućit će vam usporedbu ovih skupova podataka različite veličine
- Učinite oba skupa podataka u tablici Ctrl + T
- Za svako tekstualno polje iz kojeg želite izvijestiti stvorite stolu stola
- Kopirajte vrijednosti i uklonite duplikate
- Za datume možete uključiti datume iz obje tablice i pretvoriti u kraj mjeseca
- Neka stolari budu Ctrl + T tablice
- Neobvezno, ali korisno za imenovanje svih pet tablica
- Iz proračuna stvorite zaokretnu tablicu i odaberite model podataka
- Izgradite zaokretnu tablicu pomoću proračuna i stvarnosti iz izvornih tablica
- Sva ostala polja moraju dolaziti iz stolova stolara
- Dodajte rezače po proizvodu
- Stvorite tri odnosa od proračuna do stolara
- Stvorite tri odnosa od stvarnih do stolara
- Sutra: kako je izgradnja odnosa lakša s Power Pivot i DAX formulama
Prijepis videozapisa
Naučite Excel iz podcasta, epizoda 2016. - Proračun od vrha prema dolje u odnosu na činjenice odozdo prema gore!
Hej, potkastim cijelu ovu knjigu, kliknite ono "i" u gornjem desnom kutu i slijedite popis pjesama.
Hej, prekinut ću ovo, ovdje je Bill Jelen od 15 minuta. Sad shvaćam da je ovo nevjerojatno dugačak podcast i u iskušenju ste samo da ga kliknete, ali dopustite mi da vam samo dam ovaj kratki prikaz. Ako ste u programu Excel 2013, a ikada ste imali malu proračunsku tablicu i masivnu tablicu stvarnih podataka, i trebate ih mapirati, ovo je nevjerojatna nova sposobnost koju imamo u programu Excel 2013, a koju nije objasnio mnogo ljudi , a vjerojatno ne znate za to. Ako ste ovo vi, u 2013. ste i trebate mapirati ova dva skupa podataka, odvojite vrijeme, možda danas, možda sutra, možda ga dodajte na popis za praćenje, vrijedi, nevjerojatna je tehnika.
Dobro, evo što imamo, s lijeve strane imamo proračun, ovaj proračun, radi se na najvišoj razini, odozgo prema dolje, desno za svaku liniju proizvoda, za svaku regiju, za svaki mjesec, postoji proračun . Ovdje nema puno zapisa, broji ih 55, s desne strane pokušavamo ovo usporediti sa stvarnim podacima. Stvarni podaci dolaze iz registra računa, tako da imamo regiju, proizvod i prihod, ali to su pojedinačne fakture, puno više podataka ovdje, već smo na pola godine i već imam 423 zapisa. Dobro, kako onda mapirati ovih 55 na ovih 423? Možda je teško raditi s VLOOKUP-om, prvo biste morali rezimirati, ali na sreću, u programu Excel 2013, podatkovni model ovo čini stvarno, jako jednostavnim. Ono što trebamo da bismo omogućili ovom velikom masivnom stolu da komunicira s ovim malenim stolom su posrednici, ja ih nazivam stolarima.Maleni mali stolovi, Proizvod, Regija i Kalendar, pridružit ćemo proračun za ove tri tablice, pridružit ćemo stvarni za ove tri tablice, i čudom će Pivot tablica raditi. Dobro, evo kako to radimo.
Prvo moram stvoriti stolare, tako da ovo polje proizvoda preuzmem iz stupca A i kopiram ga u stupac F, a zatim Podaci, Ukloni duplikate, kliknite U redu i ostaje nam mala tablica, 1 naslov 3 reda. Ista stvar za regiju, uzmite regije, Ctrl + C, prijeđite na stupac G, zalijepite, uklonite duplikate, kliknite U redu, 3 retka 1 zaglavlje, u redu. Sada za datume datumi nisu isti, to su datumi završetka mjeseca, oni su zapravo pohranjeni kao datumi završetka mjeseca, a to su radni dani. Uzet ću oba popisa, Ctrl + C drugi popis i zalijepiti ga ovdje, Ctrl + V, zatim ću uzeti kraći popis, kopirati ga i zalijepiti dolje, u redu. I stvarno je dosadno što se, iako su pohranjeni kao datumi, pojavljuju kao mjeseci, a Remove Duplicates ih neće vidjeti kao iste.Dakle, prije nego što upotrijebim Remove Duplicates, moram ga promijeniti na kratki datum. Odaberite podatke, Podaci, Ukloni duplikate, kliknite U redu, a zatim malo sortirajte ovdje kako bi to uspjelo.
U redu, sada ne želim izvještavati po dnevnom datumu, pa ću ovdje dodati stupac, pretraživački stupac koji kaže Mjesec, a ovo će biti jednako SATI tog datuma,, 0, koji će nas izvući na kraj mjeseca. To će formatirati kao kratki datum i kopirati ga, u redu. Sada, moramo svaku od njih pretvoriti u Ctrl + T tablicu, tako da odavde Ctrl + T, Moja tablica ima zaglavlja, prekrasno. Oni mali, ne shvaća da su to zaglavlja tamo, pa moramo paziti da to označite i Ctrl + T, u redu, a ove tablice nazivaju Tablica1, Tablica2, Tablica3, stvarno dosadna imena, zar ne? Dakle, preimenovat ću ih i nazvati BudTable, ProdTable, RegTable, my CalTable, a zatim ActTable, u redu.
Polazimo od prve tablice, a inače nećemo danas koristiti PowerPivot, sve ćemo to raditi s podatkovnim modelom. Dakle, Excel 2013 ili noviji, imate ovaj Umetak, zaokretnu tablicu, potvrdit ćemo okvir "Dodaj ove podatke u model podataka", kliknuti U redu i dobit ćemo popis polja s čarobnim gumbom Sve, koji omogućuje ja biram između svih pet tablica u radnoj knjizi, Stvarno, Proračun, Kalendar, Proizvod, Regija. U redu, dakle, brojevi će dolaziti iz proračunske tablice, stavit ću proračun unutra, a iz stvarne tablice stavit ću stvarni, ali onda je stvar za ostatak pivot tablice. Bilo koja druga tekstualna polja koja ćemo staviti u područje retka ili u područje stupaca ili kao rezači, oni moraju dolaziti od stolara, oni moraju dolaziti iz onih tablica između tablica.
U redu, pa ćemo iz tablice Kalendara uzeti to polje Mjesec i staviti ga na vrh, trenutno ćemo zanemariti druge odnose. Stvarat ću veze, ali želim ih stvoriti odjednom. I tablica Regije, odložite regije sa strane. Mogao bih staviti proizvode sa strane, ali zapravo ću koristiti tablicu proizvoda kao rezač, pa Analizirajte, umetnite rezač, opet morate otići na Sve ako još niste koristili tablicu proizvoda. Dakle, idite na Sve i vidjet ćete da je Proizvod dostupan za izradu kao rezač od proizvoda, poput toga. Dobro, u ovom trenutku nismo stvorili veze, pa su svi ti brojevi pogrešni. A odnosi koje moramo stvoriti, moramo stvoriti 3 tablice od ove male proračunske tablice, jednu prema proizvodima, jednu prema regijama, jednu prema kalendaru,to su 3 veze. A onda moramo stvoriti odnose iz stvarne tablice s proizvodnom regijom u kalendaru, dakle ukupno 6 tablica. I da, ovo bi definitivno bilo lakše da imamo PowerPivot, ali nemamo ili pretpostavimo da nemamo.
I tako ću upotrijebiti staromodni način, ovdje dijalog Create, gdje imamo tablicu proračuna s lijeve strane, a koristit ćemo polje Region i povezati to s tablicom Region, polje Region . U redu, stvoreno je 1/6. Odabrat ću Stvori, opet iz tablice Proračun idemo na Proizvod, a zatim povezati to s tablicom Proizvod, s Proizvodom, kliknuti U redu. Iz tablice proračuna polje Datum idemo na tablicu kalendara, a polje Sudbina kliknite U redu, na pola smo puta, u redu. Iz tablice Actual idemo Region, na tablicu Region, kliknite U redu, iz tablice Actual u proizvod, a iz tablice Actual u kalendar. Zapravo ću uzeti vrijednosti i natjerati ga da se spusti sa strane, u redu. Dizajn, izgled izvještaja, prikaz u tabličnom obliku da biste dobili prikaz koji preferiram, ponovite sve oznake stavki, u redu,ovo je apsolutno nevjerojatno! Sada imamo ovu majušnu malu tablicu, 50-tak zapisa u ovoj tablici od stotina zapisa, a stvorili smo jednu zaokretnu tablicu zahvaljujući podatkovnom modelu. Za svaku gdje možemo vidjeti proračun možemo vidjeti prihod, podijeljen je po regijama, podijeljen po mjesecima i raspoređen po proizvodima.
Sad mi je ovaj koncept došao od Roba Collieja koji vodi Power Pivot Pro, a Rob je stvorio puno knjiga, a njegova najnovija je "Power Pivot i Power BI". Mislim da je ovaj zapravo bio u knjizi "Power Pivot Alchemy", to sam vidio i rekao: "Pa ovo, iako nemam milijune redaka za izvještavanje putem Power Pivota, ovo je onaj koji bi su u mom životu napravili VELIKU razliku, imajući dva skupa podataka koji se ne podudaraju i trebaju oba izvješća. " Pa, ovaj primjer i mnogi drugi nalaze se u ovoj knjizi, na kraju ću dobiti cijeli podcast knjige, izgleda da će trebati dva i pol mjeseca. Ali danas možete dobiti cijelu knjigu, u isto vrijeme, otići tamo, kupiti knjigu, 10 dolara za e-knjigu, 25 dolara za tiskanu knjigu i možete dobiti sve te savjete odjednom.
Dobro, ovdje je stvarno duga epizoda: imamo mali proračun odozgo prema dolje i dno prema gore Stvarno, različitih su veličina, ali koristeći podatkovni model u programu Excel 2013 … I usput, ako ste u 2010. godini, mogli biste , u teoriji, to učinite dobivanjem programskog dodatka Power Pivot i prođite kroz sve ove korake još 2010. Uključite oba skupa podataka u tablicu Ctrl + T, a zatim pridružite tablice za sve o čemu želite izvještavati u oznaka retka ili oznaka stupca ili rezači, pa kopirajte te vrijednosti i uklonite duplikate za datume. Zapravo sam uzeo vrijednosti iz obje tablice, jer su u svakoj postojale neke jedinstvene vrijednosti, a zatim sam iskoristio EOMONTH da izađem vani, učinim da te stolarne tablice budu kontrolirane tablice. Nije obavezno, ali imenovao sam svih 5 tablica, jer je lakše kada postavljate te odnose, umjesto da vas zovu Table1,Tablica2, Tablica3.
I tako, krenite od proračunske tablice, Umetni, zaokretne tablice, označite okvir za model podataka, a zatim gradite zaokretnu tablicu pomoću proračuna i stvarnog. Sve ostalo dolazi iz stolova za stolare, pa su regija i mjesec u području redaka i stupaca, rezači dolazili iz tablice proizvoda. A onda smo morali stvoriti 3 odnosa iz proračuna prema stolarima, 3 odnosa od stvarnog prema stolarima i imamo nevjerojatnu zaokretnu tablicu. Sada ćemo sutra pogledati upotrebu kartice Power Pivot i stvaranje nekih dodatnih izračuna. Dakle, sve je to moguće, kada želimo umetnuti izračunato polje, tada morate platiti dodatnih 2 dolara mjesečno da biste dobili Pro Plus verziju sustava Office 365.
Pa, hej, hvala Robu Collieu iz Power Pivot Pro-a na ovom savjetu i hvala vam što ste navratili, vidimo se sljedeći put za još jedno emitiranje od!
Preuzmi datoteku
Preuzmite datoteku uzorka ovdje: Podcast2016.xlsx
