Az Excel adatmodell (Power Pivot) lehetővé teszi a tényezők nagy, részletes adatkészletének összekapcsolását egy felső szintű költségkerettel az asztalos táblák használatával.
A költségvetés legfelső szinten történik - bevétel termékenként, régiónként, hónaponként. A tényleges idővel lassan halmozódik fel - számla számlánként, soronként soronként. A kis költségvetési fájl és a terjedelmes tényadatok összehasonlítása örökre fájdalmat okozott. Szeretem ezt a trükköt Rob Collie-tól, más néven PowerPivotPro.com-tól.
A példa felállításához rendelkeznie kell egy 54 soros költségvetési táblázattal: régiónként és termékenként havonta egy sor.
A számlafájl részletességi szinten van: eddig 422 sor volt ebben az évben.
A világon nincs olyan VLOOKUP, amely valaha is lehetővé tenné ennek a két adatsornak az egyeztetését. De a Power Pivot-nak (más néven az adatmodellnek az Excel 2013+ alkalmazásban) köszönhetően ez könnyűvé válik.
Létre kell hoznia apró kis táblázatokat, amelyeket „csatlakozóknak” hívok a két nagyobb adathalmaz összekapcsolására. Esetemben a Termék, a Régió és a Dátum közös a két táblázat között. A Termék táblázat egy apró négy cellás asztal. Ugyanez a régió számára. Hozzon létre mindegyiket úgy, hogy adatokat másol egy táblából, és használja az Elismétlődő elemek eltávolítását.
A jobb oldali naptár táblázatot valójában nehezebb létrehozni. A költségvetési adatoknak havonta egy soruk van, mindig a hónap végére esik. A számlaadatok a napi dátumokat mutatják, általában hétköznapokat. Tehát át kellett másolnom a Dátum mezőt mindkét adatsorból egyetlen oszlopba, majd el kellett távolítanom a duplikátumokat, hogy megbizonyosodjak arról, hogy az összes dátum szerepel-e. Ezután =TEXT(J4,"YYYY-MM")létrehoztam egy Hónap oszlopot a napi dátumokból.
Ha nem rendelkezik a teljes Power Pivot-bővítménnyel, létre kell hoznia egy kimutatótáblát a Költségvetés táblázatból, és be kell jelölnie az Ezen adatok hozzáadása az adatmodellhez négyzetet.
Az előző tippben leírtak szerint, amikor mezőket ad hozzá a kimutató táblához, hat kapcsolatot kell meghatároznia. Noha ezt megtehette a Kapcsolat létrehozása párbeszédpanel hat látogatásával, lőttem a Power Pivot-bővítményemet, és a diagram nézet segítségével definiáltam a hat kapcsolatot.
Itt van a kulcsa ennek a munkának: Ön szabadon használhatja a Költségvetés és a Tényleges mezők numerikus mezőit. De ha meg szeretné jeleníteni a Régiót, a Terméket vagy a Hónapot az elforduló táblázatban, akkor azoknak az asztalos táblákból kell származniuk!
Itt van egy pivot tábla, amely öt táblázatból származó adatokat tartalmaz. Az A oszlop a Region csatlakozótól származik. A 2. sor a Naptár csatlakozójától származik. A Product szeletelő a Product Asztalostól származik. A költségvetési számok a Költségvetés táblázatból származnak, a tényleges számok pedig a Számla táblázatból származnak.
Ez azért működik, mert az asztalos táblák szűrőket alkalmaznak a Költségvetés és a Tényleges táblára. Ez egy gyönyörű technika, amely megmutatja, hogy a Power Pivot nem csak a nagy adatok számára készült.
Videót nézni
- Van egy kis felülről lefelé kerülő költségvetési adatkészlete
- Összehasonlítani szeretné egy alulról felépülő tényadatokkal
- A tények egy számlanyilvántartásból származhatnak
- Az adatmodell segítségével összehasonlíthatja ezeket a különböző méretű adatsorokat
- Tegye mindkét adatsort Ctrl + T táblává
- Hozzon létre minden egyes szövegmezőhöz asztaltáblát
- Másolja le az értékeket és távolítsa el az ismétlődéseket
- A dátumok esetében mindkét táblázatból beillesztheti a dátumokat, és átválthat hónap végére
- Tegye a csatlakozókat Ctrl + T táblákká
- Opcionális, de hasznos mind az öt táblázat megnevezése
- Hozzon létre egy kimutatótáblát a Költségvetésből, és válassza ki az adatmodellt
- Hozzon létre egy forgatótáblát az eredeti táblázatok költségvetési és tényleges használatával
- Az összes többi mezőnek az asztalos táblákból kell származnia
- Adjon hozzá szeletelőket termékenként
- Hozzon létre három kapcsolatot a Budgettől az Asztalosokig
- Hozzon létre három kapcsolatot az Aktuális és az Asztalos között
- Holnap: hogyan könnyebb a kapcsolatok kiépítése a Power Pivot és a DAX képletekkel
Videó átirat
Ismerje meg az Excel alkalmazást a podcastból, a 2016-os epizódból - Felülről lefelé kerülő költségvetés vs Alulról felfelé aktuális adatok!
Hé, ezt az egész könyvet podcastolom, kattints az „i” gombra a jobb felső sarokban, és kövesd a lejátszási listát.
Hé, ezt megszakítom, ő 15 perc múlva Bill Jelen. Rájöttem, hogy ez egy hihetetlenül hosszú podcast, és kísértésbe esik, hogy csak átkattintasz rajta, de hadd adjam meg ezt a rövidt. Ha az Excel 2013-ban jár, és valaha volt egy kis költségvetési táblázata és egy hatalmas tényleges táblázata, és ezeket együtt kell feltérképeznie, ez egy csodálatos új képesség, amelyet az Excel 2013-ban használhatunk, és amelyet nem sokan magyaráztak , és valószínűleg nem tud róla. Ha ez vagy te, akkor 2013-ban vagy, és fel kell térképezned ezt a két adatsort, szánj rá időt, talán ma, talán holnap, esetleg add hozzá a figyelőlistához, megéri, elképesztő technika.
Rendben, íme, mi van, a bal oldalon van egy költségvetés, ez a költségvetés, ez felső szinten, felülről lefelé, jobbra történik az egyes termékcsaládokhoz, az egyes régiókhoz, minden hónaphoz, van egy költségvetés . Itt nincs sok rekord, 55-ös szám, a jobb oldalon próbáljuk ezt összehasonlítani a tényekkel. A tényadatok egy számlanyilvántartásból származnak, tehát megvan a Region, Product és Revenue, de ezek egyedi számlák, itt sokkal több adat van, már az év felénél járunk, és máris 423 rekordom van. Rendben, akkor hogyan lehet ezeket az 55-öseket feltérképezni e 423-ra? Nehéz lehet a VLOOKUP használatával, először összefoglalnod kell, de az Excel 2013-ban szerencsére az adatmodell ezt nagyon-nagyon egyszerűvé teszi. Amit meg kell engednünk, hogy ez a nagy masszív asztal kommunikáljon ezzel az apró asztallal, az közvetítők, nevezem őket asztalosoknak.Apró kis táblák, Termék, Régió és Naptár, ehhez a három táblához csatlakozunk a költségvetéshez, ehhez a három táblához csatlakozunk a ténylegeshez, és csodás módon a Pivot táblázat működni fog. Rendben, így tesszük ezt.
Először is létre kell hoznom a csatlakozókat, ezért átveszem ezt a Termék mezőt az A oszlopból, és átmásolom az F oszlopba, majd az Adatok, Ismétlődések eltávolítása elemre, kattintsunk az OK gombra, és marad egy apró kis táblázat, címsor 3 sor. Ugyanez a helyzet a Régióval: vegye be a régiókat, Ctrl + C, menjen át a G oszlopba, Beillesztés, Ismétlődések eltávolítása, kattintson az OK gombra, 3 sor 1 fejléc, rendben. Ami a dátumokat illeti, a dátumok nem ugyanazok, ezek hónap végi dátumok, valójában hónap végi dátumként vannak tárolva, ezek hétköznapok. Fogom mindkét listát, a Ctrl + C második listát, és ide illesszem be, Ctrl + V, majd a rövidebb listát veszem, átmásolom és beillesztem alulra, rendben. És nagyon bosszantó, hogy bár ezeket dátumként tárolják, hónapokban jelennek meg, és az Eltávolítja a duplikátumokat nem fogja őket azonosnak tekinteni.Tehát mielőtt eltávolítanám az Ismétlődéseket, rövid időre át kell állítanom. Válassza ki az adatokat: Adatok, Ismétlődések eltávolítása, kattintson az OK gombra, majd egy kicsit rendezze ide, hogy működjön.
Rendben, most nem akarok napi dátum szerint jelenteni, ezért hozzáadok egy oszlopot, egy keresési oszlopot, amely a hónapot írja, és ez egyenlő lesz a HOMONTH dátummal,, 0, amivel kijutunk a hónap vége. Ezt rövid dátumként formázza, és jól lemásolja. Most mindegyiket Ctrl + T táblává kell tennünk, így innen a Ctrl + T, A táblázatom fejlécek gyönyörűek. A kicsik nem veszik észre, hogy ezek fejlécek vannak fent, ezért mindenképpen ellenőriznünk kell ezt és a Ctrl + T, rendben, és ezeket a táblákat Table1, Table2, Table3-nak hívják, igazán unalmas nevek, igaz? Tehát át fogom nevezni ezeket, és BudTable-nek, ProdTable-nek, RegTable-nek, CalTable-omnak, majd az ActTable-nek hívom.
A legelső táblából indulunk ki, és egyébként nem ma használjuk a PowerPivot-ot, mindezt az adatmodellel fogjuk megtenni. Tehát, Excel 2013 vagy újabb, megvan ez a Beszúrás, PivotTable, bejelöljük az „Adatok hozzáadása az adatmodellhez” jelölőnégyzetet, kattintsunk az OK gombra, és megkapjuk a mezőlistánkat a varázslatos Minden gombbal, amely lehetővé teszi én a munkafüzet mind az öt táblázata közül választhatok: Tényleges, Költségvetés, Naptár, Termék, Régió. Rendben, tehát a számok a Költségvetés táblázatból fognak származni, odaadom a költségvetést, és a Tényleges táblázatból a ténylegeset teszem oda, de akkor itt van a dolog a Pivot-tábla többi részével. Bármely más szövegmezőnek, amelyet a sorterületre vagy az oszlopterületre vagy szeletelőként fogunk elhelyezni, azoknak az asztalosoktól kell származniuk, azoktól az asztalok közötti tábláktól kell származniuk.
Rendben, így a Naptár táblázatból átvesszük a Hónap mezőt, és a tetejére tesszük, a többi kapcsolatot most figyelmen kívül hagyjuk. Létrehozom a kapcsolatokat, de mindet egyszerre szeretném létrehozni. A Régió táblázat pedig tegye le a régiókat. Le tudnám tenni a termékeket az oldalra, de valójában a Product táblázatot fogom használni szeletelőként, ezért elemezzen, helyezzen be szeletelőt, ismét meg kell mennie az All menüpontra, ha még nem használta a Product táblázatot. Tehát menjen az Összes oldalra, és látni fogja, hogy a Termék szeletelőként készíthető el a termékekből, mint ez. Rendben, ezen a ponton még nem hoztunk létre kapcsolatokat, ezért ezek a számok tévesek. És a kapcsolatokat, amelyeket létre kell hoznunk, 3 táblázatot kell létrehoznunk ebből a kis költségvetési táblázatból, egyet a termékekhez, egyet a régiókhoz, egyet a naptárhoz,ez 3 kapcsolat. Ezután kapcsolatokat kell létrehoznunk a Tényleges táblától a Termék régióig a Naptárban, tehát összesen 6 táblázatot. És igen, ez mindenképpen könnyebb lenne, ha rendelkeznénk PowerPivot-tal, de nem, vagy tegyük fel, hogy nincs.
Ezért a régimódi módot, a Létrehozás párbeszédet fogom használni, ahol a Költségvetés táblázat található a bal oldalon, és a Régió mezőt fogjuk használni, és ezt a Régió táblához, a Régió mezőhöz fogjuk kapcsolni. . Rendben, 1/6-os rész jön létre. Kiválasztom a Létrehozást, a Költségvetés táblázatból ismét a Termékhez lépünk, majd összekapcsolom a Termék táblával, a Termékkel, és kattintsunk az OK gombra. A Költségvetés táblából a Dátum mezőből a Naptár táblázatba lépünk, és a Sors mezőbe kattintsunk az OK gombra, félúton vagyunk, rendben. A Tényleges táblázatból megyünk a Régió, a Régió táblázatba, kattintsunk az OK gombra, a Tényleges táblázatról a Termékre, és a Tényleges táblázatról a Naptárra. Valójában el fogom venni az Értékeket, és az oldalra megyek, rendben. Tervezés, Jelentés elrendezése, Megjelenítés táblázatos formában az általam kedvelt nézet megszerzéséhez, Ismételje meg az összes elem címkét, rendben,ez teljesen elképesztő! Most megvan ez a kis apró táblázat, egy 50-néhány rekord ebben a több száz rekordot tartalmazó táblázatban, és az Adatmodellnek köszönhetően egyetlen Pivot-táblázatot hoztunk létre. Minden olyan esetében, ahol láthatjuk a költségkeretet, láthatjuk a bevételt, régiónként, hónaponként és termékenként osztható fel.
Ez a koncepció Rob Collie-tól származik hozzám, aki a Power Pivot Pro-t futtatja, és Rob rengeteg könyvet készített ott, a legújabb a „Power Pivot és Power BI”. Azt hiszem, ez valójában a „Power Pivot Alchemy” könyvben szerepelt, ezt láttam, és azt mondtam, hogy „Nos ezt, bár nincs több millió sorom a Power Pivot-on keresztüli jelentésre, ez az, ami Hatalmas változást hoztak az életemben, két, egymással nem egyező méretű adatsorom volt, és mindkettőjüktől jelentést kellett tennem. Nos, ez a példa és még sokan mások ebben a könyvben vannak, végül megkapom az egész könyv podcastot, úgy tűnik, két és fél hónapba fog telni. De ma megkaphatja az egész könyvet, ugyanabban az időben, odamegy, megvásárolja a könyvet, 10 dollárt az e-könyvért, 25 dollárt a nyomtatott könyvért, és egyszerre megkapja ezeket a tippeket.
Rendben, itt egy nagyon hosszú epizód: van egy kis felülről lefelé kerülő költségvetésünk és egy alulról felfelé való. Ezek különböző méretűek, de az Excel 2013 adatmodelljét használva … És mellesleg, ha 2010-ben jársz, akkor , elméletileg ezt úgy teheti meg, hogy megszerzi a Power Pivot bővítményt, és 2010-ben hajtsa végre ezeket a lépéseket. Mindkét adatkészletet készítse el egy Ctrl + T táblává, majd csatlakozzon a tábláihoz bárhonnan, amelyről jelentést szeretne készíteni sorcímke, oszlopcímke vagy szeletelők, ezért másolja át ezeket az értékeket, és távolítsa el a dátumok duplikátait. Valójában mindkét táblából vettem értékeket, mert mindegyikben volt néhány egyedi érték, majd az EOMONTH-t arra használtam, hogy kijussak oda, hogy az asztalos táblákat vezérelt táblákká tegyem. Ez opcionális, de mind az 5 táblát megneveztem, mert könnyebb, ha létrehozza ezeket a kapcsolatokat, ahelyett, hogy 1. táblázatnak hívnák,2. táblázat, 3. táblázat.
Tehát kezdje a Költségvetés, Beszúrás, kimutatás táblázatból, jelölje be az Adatmodell jelölőnégyzetet, majd készítsen egy kimutatótáblát a Költségvetés és a Tényleges használatával. Minden más az asztalos táblákból származik, így a Region and Month a sor- és oszlopterületen, a szeletelők a Product táblából kerültek elő. Ezután 3 kapcsolatot kellett létrehoznunk a Költségvetéstől a csatlakozókig, 3 kapcsolatot a Ténylegestől a csatlakozókig, és van egy csodálatos Pivot-táblázatunk. Holnap megnézzük a Power Pivot lap használatát, és létrehozunk néhány további számítást. Tehát mindez lehetséges, amikor számított mezőt akarunk beszúrni, akkor kell fizetnie a havi 2 dollárt, hogy megszerezhesse az Office 365 Pro Plus verzióját.
Nos, hála, köszönöm Rob Collie-nak a Power Pivot Pro-tól ezt a tippet, és köszönöm, hogy betértél, legközelebb találkozunk egy újabb netcast-lal!
Fájl letöltése
Töltse le a minta fájlt innen: Podcast2016.xlsx
