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 proračunske datoteke s opsežnim stvarnim podacima zauvijek je bila muka. Volim ovaj trik Roba Colliea, zvanog PowerPivotPro.com.
Da biste postavili primjer, imate proračunsku tablicu od 54 retka: 1 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, morate stvoriti zaokretnu tablicu iz proračunske tablice i označiti potvrdni 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.
