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: havonta 1 sor régiónként és termékenként.
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ázatot a Költségvetés táblázatból, és be kell jelölnie az Adatok hozzáadása az adatmodellhez jelölő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.
