Excelov podatkovni model (Power Pivot) vam omogoča, da z uporabo mizarskih tabel povežete velik podroben podatkovni niz dejanskih podatkov s proračunom najvišje ravni.
Proračuni so narejeni na najvišji ravni - prihodki po izdelkih po regijah po mesecih. Dejansko se sčasoma kopičijo počasi - račun za račun, vrstica za postavko. Primerjava majhne proračunske datoteke z obsežnimi dejanskimi podatki je bila za vedno bolečina. Všeč mi je ta trik Roba Collieja, znan tudi kot PowerPivotPro.com.
Če želite nastaviti primer, imate 54-vrsticno proračunsko tabelo: ena vrstica na mesec na regijo na izdelek.
Datoteka z računom je na ravni podrobnosti: letos doslej 422 vrstic.
Na svetu ni nobenega VLOOKUP-a, ki bi vam kdaj dovolil, da se ujemate s tema dvema naboroma podatkov. Toda zahvaljujoč Power Pivot (znan tudi kot podatkovni model v programu Excel 2013+) to postane enostavno.
Ustvariti morate majhne majhne tabele, ki jih imenujem "mizarji", da povežete dva večja nabora podatkov. V mojem primeru so med tabelama skupni izdelek, regija in datum. Tabela izdelkov je majhna miza s štirimi celicami. Tudi za regijo. Vsako od njih ustvarite tako, da kopirate podatke iz ene tabele in uporabite Odstrani dvojnike.
Koledarsko tabelo na desni je bilo dejansko težje ustvariti. Podatki o proračunu imajo eno vrstico na mesec, ki vedno pade na konec meseca. Podatki na računu prikazujejo dnevne datume, običajno delovnike. Tako sem moral kopirati polje Datum iz obeh naborov podatkov v en stolpec in nato odstraniti dvojnike, da se prepričam, da so predstavljeni vsi datumi. Nato sem =TEXT(J4,"YYYY-MM")iz dnevnih datumov ustvaril stolpec Mesec.
Če nimate celotnega dodatka Power Pivot, morate iz tabele proračuna ustvariti vrtilno tabelo in potrditi polje Dodaj te podatke v podatkovni model.
Kot smo že omenili v prejšnjem nasvetu, boste morali med dodajanjem polj v vrtilno tabelo definirati šest razmerij. Medtem ko ste to lahko storili s šestimi obiski pogovornega okna Ustvari razmerje, sem sprožil svoj dodatek Power Pivot in s pomočjo diagrama določil šest razmerij.
Tu je ključ vsega tega: lahko uporabljate številčna polja iz proračuna in iz dejanskega stanja. Če pa želite v vrtilni tabeli prikazati Regijo, Izdelek ali Mesec, morajo izvirati iz mizarskih miz!
Tu je vrtilna tabela s podatki iz petih tabel. Stolpec A prihaja od mizarja regije. Vrstica 2 prihaja iz mizarja v Koledarju. Rezalnik izdelka je od mizarja izdelka. Številke proračuna prihajajo iz tabele proračun, dejanske številke pa iz tabele račun.
To deluje, ker mizarske tabele uporabljajo filtre za tabelo Proračun in Dejansko. To je čudovita tehnika, ki kaže, da Power Pivot ni namenjen samo velikim podatkom.
Oglejte si video
- Na voljo imate majhen niz proračunskih podatkov od zgoraj navzdol
- Želite primerjati z nizom dejanskih podatkov
- Dejansko stanje lahko izvira iz registra računov
- Podatkovni model vam omogoča primerjavo teh nizov podatkov različnih velikosti
- Naredite oba nabora podatkov v tabelo Ctrl + T
- Za vsako besedilno polje, iz katerega želite poročati, ustvarite mizarsko tabelo
- Kopirajte vrednosti in odstranite dvojnike
- Za datume lahko vključite datume iz obeh tabel in jih pretvorite v konec meseca
- Naj bodo mizarji tabeli Ctrl + T
- Neobvezno, vendar koristno, če poimenujete vseh pet tabel
- Iz proračuna ustvarite vrtilno tabelo in izberite podatkovni model
- Iz prvotne tabele zgradite vrtilno tabelo z uporabo proračuna in dejanskega stanja
- Vsa druga polja morajo izvirati iz mizarskih tabel
- Dodajte rezalnike po izdelku
- Ustvarite tri razmerja od proračuna do mizarjev
- Ustvarite tri razmerja od dejanskih do mizarjev
- Jutri: kako je lažje graditi odnose s formulama Power Pivot in DAX
Video zapis
Naučite se Excel iz podcasta, epizoda 2016 - Proračun od zgoraj navzdol proti dejanskim pogojem od spodaj navzgor!
Hej, podkastim celotno to knjigo, v zgornjem desnem kotu kliknite tisto "i" in sledite seznamu predvajanja.
Hej, to bom prekinil, to je Bill Jelen od 15 minut naprej. Zdaj se zavedam, da je to neverjetno dolg podcast in vas mika, da ga preprosto kliknete, vendar vam dovolite, da vam dam le to kratko. Če ste v Excelu 2013 in ste kdaj imeli majhno proračunsko tabelo in ogromno dejansko tabelo in jih morate sestaviti skupaj, je to neverjetna nova sposobnost, ki jo imamo v Excelu 2013, ki je ni pojasnilo veliko ljudi , in verjetno ne veste o tem. Če ste to vi, ste v letu 2013 in morate ta dva nabora podatkov preslikati, si vzemite čas, morda danes, morda jutri, ga morda dodajte na seznam za nadzor, splača se, to je neverjetna tehnika.
V redu, evo, kar imamo, na levi strani imamo proračun, ta proračun, narejen je na najvišji ravni, od zgoraj navzdol, desno za vsako linijo izdelkov, za vsako regijo, za vsak mesec, obstaja proračun . Tu ni veliko zapisov, šteje jih 55, na desni strani to poskušamo primerjati z dejanskimi. Dejanski podatki prihajajo iz registra računov, tako da imamo regijo, izdelek in prihodek, vendar so to posamezni računi, tukaj je veliko več podatkov, že smo na polovici leta in imam že 423 zapisov. V redu, kako torej preslikati teh 55 na teh 423? Morda bi bilo težko narediti z VLOOKUP-om, najprej bi morali povzetke, toda na srečo v Excelu 2013 podatkovni model to resnično olajša. Kaj moramo omogočiti tej veliki masivni mizi, da komunicira s to majhno mizo, smo posredniki, imenujem jih mizarji.Majhne majhne tabele, Product, Region in Calendar, proračun bomo pridružili tem trem tabelam, dejanskim pa bomo pridružili tem trem tabelam, čudežna miza pa bo delovala. V redu, takole to naredimo.
Najprej moram ustvariti mizarje, zato vzamem to polje Product iz stolpca A in ga kopiram v stolpec F, nato pa Podatki, Odstrani dvojnike, kliknite V redu in ostane nam majhna mizica, 1 naslov 3 vrstice. Enako kot za Regija, vzemite regije, Ctrl + C, pojdite na stolpec G, Prilepi, Odstrani podvojene, kliknite V redu, 3 vrstice 1 glava, v redu. Zdaj za datume datumi niso enaki, to so datumi konca meseca, dejansko so shranjeni kot datumi konca meseca in to so delavniki. Vzel bom oba seznama, Ctrl + C drugi seznam in ga prilepil sem, Ctrl + V, nato bom vzel krajši seznam, ga kopiral in prilepil spodaj, v redu. In prav moteče je, da čeprav so ti shranjeni kot datumi, se prikažejo kot meseci in jih Odstrani dvojnike ne bo videl enakih.Torej, preden uporabim Odstrani dvojnike, ga moram spremeniti na kratek datum. Izberite podatke, Podatki, Odstrani dvojnike, kliknite V redu in nato malo razvrstite tukaj, da začne delovati.
V redu, zdaj ne želim poročati po dnevnem datumu, zato bom tu dodal stolpec, iskalni stolpec, ki piše mesec, in to bo enako EOMONTH tega datuma,, 0, ki nas bo pripeljal do konec meseca. To bo formatiral kot kratek datum in ga kopiral, prav. Zdaj moramo vsako od teh narediti v tabelo Ctrl + T, tako da od tu Ctrl + T, Moja tabela ima glave, lepo. Majhne se ne zavedajo, da so to glave zgoraj, zato moramo obvezno označiti to in Ctrl + T, v redu, in te tabele imenujejo Tabela1, Tabela2, Tabela3, res dolgočasna imena, kajne? Torej jih bom preimenoval in poimenoval BudTable, ProdTable, RegTable, my CalTable in nato ActTable, v redu.
Začnemo že od prve tabele in mimogrede, danes ne bomo uporabljali PowerPivota, vse to bomo storili z podatkovnim modelom. Torej, Excel 2013 ali novejši, imate to Vstavi, vrtilno tabelo, označili bomo polje »Dodaj te podatke v podatkovni model«, kliknite V redu in dobili bomo seznam polj s čarobnim gumbom Vse, ki omogoča jaz izbiram med petimi tabelami v delovnem zvezku, dejansko, proračun, koledar, izdelek, regija. Torej, številke bodo torej prišle iz proračunske tabele, proračun bom vstavil tja, iz dejanske pa dejansko, toda tukaj je stvar za preostalo vrtilno tabelo. Vsa druga besedilna polja, ki jih bomo postavili v območje vrstic ali stolpcev ali kot rezalniki, morajo izvirati iz mizarjev, iz tabel med tabelami.
Torej, iz tabele Koledarja bomo vzeli polje Mesec in ga postavili na vrh, zdaj bomo druge odnose prezrli. Ustvarjal bom odnose, vendar jih želim ustvariti naenkrat. In tabela Regija, postavite regije na stran. Izdelke bi lahko postavil na stran, vendar bom dejansko uporabil tabelo Product kot rezalnik, zato Analyze, Insert Slicer, spet morate iti na All, če še niste uporabili tabele Product. Torej, pojdite na Vse in videli boste, da je izdelek na voljo za ustvarjanje kot rezalnik iz izdelkov, kot je ta. V tem trenutku še nismo ustvarili odnosov, zato so vse te številke napačne. In razmerja, ki jih moramo ustvariti, moramo iz te majhne proračunske tabele ustvariti 3 tabele, eno za izdelke, eno za regije, eno za koledar,to so 3 razmerja. Nato moramo ustvariti razmerja iz dejanske tabele do območja izdelkov v koledarju, torej skupaj 6 tabel. In ja, to bi bilo vsekakor lažje, če bi imeli PowerPivot, vendar ga ne, ali domnevamo, da ga ne.
In zato bom uporabil staromoden način, tukaj ustvarite dialog, kjer imamo na levi tabelo Proračun, uporabili bomo polje Regija in to povezali s tabelo Regija, polje Regija . V redu, ustvari se 1/6. Izbral bom Ustvari, spet v tabeli Proračun gremo na Izdelek in ga nato povežem s tabelo Izdelek, z Izdelkom, kliknite V redu. Iz tabele Proračun v polje Datum vstopimo v tabelo Koledar in v polje Usoda kliknite V redu, že smo na pol poti. Iz tabele Dejansko gremo Regija, v tabelo Regija, kliknite V redu, iz tabele Dejansko v izdelek in iz tabele Dejansko v Koledar. Pravzaprav bom vzel vrednote in spustil stran, v redu. Oblikovanje, postavitev poročila, prikaz v tabelarni obliki, da dobite pogled, ki mi je ljubši, Ponovi vse oznake elementov, v redu,to je popolnoma neverjetno! Zdaj imamo to majhno majhno tabelo, 50-nekaj zapisov v tej tabeli s stotinami zapisov in zahvaljujoč podatkovnemu modelu smo ustvarili eno vrtilno tabelo. Za vsako, kjer lahko vidimo proračun, lahko vidimo prihodek, razdeljen je po regijah, razdeljen po mesecih in razdeljen po izdelkih.
Zdaj se mi je ta koncept zdel Rob Collie, ki vodi Power Pivot Pro, in Rob je ustvaril veliko knjig, njegova zadnja pa je "Power Pivot in Power BI". Mislim, da je bil ta dejansko v knjigi "Power Pivot Alchemy", to je tisti, ki sem ga videl in rekel: "No to, čeprav nimam milijonov vrstic za poročanje prek Power Pivota, je to tisto, ki bi so v mojem življenju naredili VELIKO spremembo, saj imam dva nabora podatkov, ki se ne ujemata, in moram poročati iz obeh. " No, ta primer in mnogi drugi so v tej knjigi, sčasoma bom dobil celoten podcast knjige, kot da bo trajalo dva meseca in pol. Toda celo knjigo lahko dobite danes, istočasno, pojdite tja, kupite knjigo, 10 dolarjev za e-knjigo, 25 dolarjev za tiskano knjigo in lahko dobite vse te nasvete hkrati.
V redu, res dolga epizoda tukaj: imamo majhen proračun od zgoraj navzdol in spodaj dejansko, so različnih velikosti, vendar z uporabo podatkovnega modela v Excelu 2013 … In mimogrede, če ste v letu 2010, bi lahko teoretično to storite tako, da dobite dodatek Power Pivot in vse te korake opravite že leta 2010. Naredite oba nabora podatkov v tabelo Ctrl + T in nato združite tabeli za vse, o čemer želite poročati, v oznaka vrstice ali oznaka stolpca ali rezalniki, zato kopirajte te vrednosti in za datume odstranite dvojnike. Dejansko sem vzel vrednosti iz obeh tabel, ker je bilo v vsaki nekaj edinstvenih vrednosti, nato pa sem uporabil EOMONTH, da sem prišel ven in naredil te mizarske tabele nadzorovane tabele. Neobvezno, vendar sem poimenoval vseh 5 tabel, ker je lažje, ko nastavljate te odnose, namesto da bi se imenovali Table1,Tabela2, Tabela3.
Torej, začnite s proračunsko tabelo, Vstavi, Vrtilna tabela, potrdite polje Podatkovni model in nato zgradite vrtilno tabelo s proračunom in dejansko. Vse ostalo prihaja iz mizarskih tabel, tako Regija in Mesec v območju vrstic in stolpcev, rezalniki prihajajo iz tabele Izdelki. In potem smo morali ustvariti 3 razmerja iz proračuna do mizarjev, 3 razmerja od dejanskega do mizarjev in imamo neverjetno vrtilno tabelo. Zdaj si bomo jutri ogledali uporabo zavihka Power Pivot in ustvarili nekaj dodatnih izračunov. Vse to je torej mogoče, ko želimo vstaviti izračunano polje, takrat morate plačati dodatnih 2 USD na mesec, da dobite različico Office Plus Pro Plus.
No hej, hvala Robu Collieju iz Power Pivot Pro za ta namig in hvala vam, da ste se ustavili, se vidimo naslednjič za novo oddajo od!
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2016.xlsx
