Oktatóprogram: Kimutatás adatainak elemzése adatmodell használatával az Excel 2013 programban

Alig egy óra múltával képes lesz arra, hogy olyan kimutatásos jelentést hozzon létre az Excelben, amelynek adatai több táblából származnak. Az oktatóprogram első részében az adatok importálásának és felkutatásának műveletével ismerkedhet meg. A második részben a Power Pivot beépülő modullal fogja finomítani a jelentés alapjául szolgáló adatmodellt, és közben megtanulja, hogyan tud számításokat, hierarchiákat és optimalizálásokat felvenni Power View nézetet tartalmazó jelentések készítéséhez.

Első lépésként importálnia kell néhány adatot.

  1. Töltsön le mintaadatokat az oktatóprogramhoz. Lásd: Mintaadatok beszerzése a DAX és az adatmodellezés oktatóprogramjaihoz. Bontsa ki és mentse az adatfájlokat egy könnyen elérhető helyre, például a Letöltések vagy a Dokumentumok mappába.

  2. Nyisson meg egy üres munkafüzetet az Excelben.

  3. Az Excelben kattintson az Adatok > Külső adatok átvétele > Access-fájlból elemre.

  4. Keresse meg azt a mappát, ahová a mintaadatfájlokat letöltötte, és jelölje ki a ContosoSales fájlt.

  5. Kattintson a Megnyitás gombra. Mivel olyan adatbázisfájlhoz csatlakozik, amely több táblát tartalmaz, megjelenik a Táblaválasztás párbeszédpanel, hogy ki tudja választani az importálni kívánt táblákat.

    Táblaválasztás párbeszédpanel

  6. A Táblaválasztás párbeszédpanelen jelölje be a Több tábla kijelölésének engedélyezése jelölőnégyzetet.

  7. Válassza ki az összes táblát, és kattintson az OK gombra.

  8. Az Adatimportálás párbeszédpanelen kattintson a Kimutatás beállításra, majd az OK gombra.

    Megjegyzések: 

    • Talán még nem is tudja, de éppen most hozott létre egy adatmodellt. A modell egy olyan adatintegrációs réteg, amely automatikusan létrejön, ha több táblát importál, illetve egyszerre több táblával dolgozik.

    • A modell a legjobban az Excelben tekinthető át, de közvetlenül a Power Pivot bővítményben is megtekintheti, illetve módosíthatja. Az Excelben az adatmodell jelenléte akkor válik nyilvánvalóvá, amikor a kimutatás mezőlistájában egy sor tábla látszik. A modellek létrehozására többféle módszer is van. További tudnivalókat az Adatmodell létrehozása az Excelben című témakörben talál.

Az adatok felkutatása kimutatás használatával

Az adatok felkutatása egyszerű: húzza a mezőket a kimutatás mezőlistájának Értékek, Oszlopok és Sorok területére.

  1. Görgesse lefelé a mezőlistát, amíg meg nem találja a FactSales táblát.

  2. Kattintson a SalesAmount mezőre. Mivel ezek numerikus adatok, az Excel automatikusan az Értékek területen helyezi el a SalesAmount mezőt.

  3. A DimDate tábla CalendarYear mezőjét húzza a Sorok területre.

  4. A DimProductSubcategory tábla ProductSubcategoryName mezőjét húzza a Sorok területre.

  5. A DimProduct tábla BrandName mezőjét húzza a Sorok területre, és helyezze az alkategória alá.

A kimutatásnak a következő képernyőn láthatóhoz hasonlóan kell kinéznie.

Mintaadatokat megjelenítő kimutatás

Így minimális munkával létrehozott egy olyan alapszintű kimutatást, amely négy különböző táblából tartalmaz mezőket. Ez a feladat attól volt ennyire egyszerű, hogy a táblák között már voltak adatkapcsolatok. Mivel a táblakapcsolatok már megvoltak a forrás oldalán, és mivel az összes táblát egyetlen művelettel importálta, az Excel újra létre tudta hozni ezeket a kapcsolatokat a modellben.

De mi a helyzet akkor, ha az adatok különböző forrásokból származnak, vagy ha csak később importálja őket? Általában lehetőség van az új adatok beépítésére olyan módon, hogy egyező oszlopok alapján hoz létre kapcsolatokat. A következő lépésben további táblákat fog importálni, valamint megismeri az új kapcsolatok létrehozására vonatkozó követelményeket és lépéseket.

További táblák hozzáadása

Ahhoz, hogy megtanulja, hogyan kell beállítani a táblakapcsolatokat, néhány további, egymáshoz nem kapcsolt táblára van szüksége, amelyekkel dolgozhat. Ebben a lépésben az oktatóprogramban használt további adatokat olyan módon éri el, hogy egy további adatbázist importál, illetve másik két munkafüzetből illeszt be adatokat.

Termékkategóriák hozzáadása

  1. A munkafüzetben nyisson meg egy új munkalapot. Ezen a lapon tárolja majd azokat az adatokat, amelyeket most fog importálni.

  2. Az Excelben kattintson az Adatok > Külső adatok átvétele > Access-fájlból elemre.

  3. Keresse meg azt a mappát, ahová a mintaadatfájlokat letöltötte, és jelölje ki a ProductCategories fájlt. Kattintson a Megnyitás gombra.

  4. Az Adatimportálás párbeszédpanelen kattintson a Táblázat elemre, majd az OK gombra.

Földrajzi adatok hozzáadása

  1. Szúrjon be még egy munkalapot.

  2. A mintaadatok fájljai közül nyissa meg a Geography.xlsx fájlt, vigye a mutatót az A1 cellába, és a Ctrl+Shift+End billentyű megnyomásával jelölje ki az összes adatot.

  3. Másolja a vágólapra az adatokat.

  4. Illessze be az adatokat az imént beszúrt üres munkalapra.

  5. Kattintson a Formázás táblázatként gombra, és válasszon egy tetszőleges stílust. Ha táblázatként formázza az adatokat, elnevezheti a táblázatot, aminek jó hasznát veheti majd, amikor kapcsolatokat kell definiálnia egy későbbi lépésben.

  6. Ellenőrizze, hogy a Formázás táblázatként párbeszédpanelen be van-e jelölve a Táblázat rovatfejekkel jelölőnégyzet. Kattintson az OK gombra.

  7. Adja meg a Geography nevet a földrajzi adatokat tartalmazó táblázathoz. A Táblázateszközök > Tervezés lapon írja be a Geography nevet a Táblázat neve mezőbe.

  8. Zárja be a Geography.xlsx fájlt, hogy ne legyen túlzsúfolt a munkaterülete.

Az üzletek adatainak hozzáadása

  • Hajtsa végre a fenti lépéseket a Stores.xlsx fájlra vonatkozóan is, és illessze be a tartalmát egy üres munkalapra. Adja meg a Stores nevet az üzleteket tartalmazó táblázathoz.

Így már négy munkalapot kell tartalmaznia a munkafüzetnek. A Munka1 lap tartalmazza a kimutatást, a Munka2 lap tartalmazza a termékkategóriák ProductCategories nevű táblázatát, a Munka3 lap tartalmazza a földrajzi helyek Geography nevű táblázatát és a Munka4 lap tartalmazza az üzletek Stores nevű táblázatát. Mivel korábban már elnevezte a táblázatokat, a következő lépésben könnyebb dolga lesz a kapcsolatok létrehozásánál.

Az újonnan importált táblázatok mezőinek használata

Az imént importált táblázatok mezőit azonnal elkezdheti használni. Ha az Excel nem tudja megállapítani, hogyan tud beépíteni egy mezőt a kimutatásba, olyan táblakapcsolat létrehozását kéri, amely az új táblázatot társítja egy, a modellben már meglévő táblázathoz.

  1. A Kimutatásmezők ablaktábla tetején kattintson a Mind parancsra a rendelkezésre álló táblák teljes listájának megtekintéséhez.

  2. Görgessen a lista aljára, itt találja az imént felvett új táblák listáját.

  3. Bontsa ki a Stores bejegyzést.

  4. Húzza a StoreName mezőt a Szűrők területre.

  5. Látni fogja, hogy az Excel kapcsolat létrehozását kéri. Ez az értesítés azért érkezik, mert olyan táblából használt mezőket, amely nincs összekapcsolva a modellel.

  6. A Kapcsolat létrehozása párbeszédpanel megnyitásához kattintson a Létrehozás parancsra.

  7. A Tábla listán válassza a FactSales táblát. A mintaadatokban a FactSales tábla tartalmazza a Contoso vállalat üzleteinek részletes értékesítési és költségadatait, valamint a többi tábla kulcsait, beleértve az üzletkódokat, amelyek az előző lépésben importált Stores.xlsx fájlban is szerepelnek.

  8. Az Oszlop (külső) listán válassza a StoreKey elemet.

  9. A Kapcsolódó tábla listán válassza a Stores elemet.

  10. A Kapcsolódó oszlop (elsődleges) listán válassza a StoreKey elemet.

  11. Kattintson az OK gombra.

A színfalak mögött az Excel egy adatmodellt állít össze, amely a teljes munkafüzetben használható tetszőleges számú kimutatáshoz, kimutatásdiagramhoz vagy Power View nézetet tartalmazó jelentéshez. A modell alapvető fontosságú elemei a táblázatkapcsolatok, amelyek meghatározzák a kimutatásos jelentésekben a navigálás és a számítások útját. A következő feladatban manuálisan fog kapcsolatokat létrehozni az előbb importált adatok összekapcsolása végett.

Kapcsolatok hozzáadása

Szisztematikusan haladva létrehozhat táblázatkapcsolatokat az összes újonnan importált táblázathoz. Ha munkatársaival közösen használja a munkafüzetet, az előre megadott kapcsolatokért hálásak lesznek a kollégák, ha ők nem olyan jól ismerik az adatokat, mint Ön.

Amikor manuálisan hoz létre táblázatkapcsolatokat, egyszerre két táblázattal fog dolgozni. Mindkét táblázat esetében ki kell majd választania azokat az oszlopokat, amelyek meghatározzák, hogy az Excel hogyan fogja megkeresni a kapcsolódó sorokat a másik táblázatban.

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

A ProductSubcategory oszlop és a ProductCategory oszlop összekapcsolása

  1. Az Excelben válassza az Adatok > Kapcsolatok > Új lehetőséget.

  2. A Tábla listán válassza a DimProductSubcategory elemet.

  3. Az Oszlop (külső) listán válassza a ProductCategoryKey elemet.

  4. A Kapcsolódó tábla listán válassza a Table_ProductCategory.accdb elemet.

  5. A Kapcsolódó oszlop (elsődleges) listán válassza a ProductCategoryKey elemet.

  6. Kattintson az OK gombra.

  7. Zárja be a Kapcsolatok kezelése párbeszédpanelt.

Kategóriák felvétele a kimutatásba

Bár az adatmodell frissült, és tartalmazza a további táblázatokat és a kapcsolatokat, a kimutatás még nem használja őket. Ebben a feladatban a ProductCategory táblázatot felveszi a kimutatás mezőlistájára.

  1. A Kimutatásmezők ablaktáblában kattintson a Mind elemre az adatmodellben található összes táblázat megjelenítéséhez.

  2. Görgessen a lista aljára.

  3. A Sorok területről távolítsa el a BrandName mezőt.

  4. Bontsa ki a Table_DimProductCategories.accdb bejegyzést.

  5. Húzza a ProductCategoryName mezőt a Sorok területre, és helyezze a ProductSubcategory mező fölé.

  6. A Kimutatásmezők ablaktáblában kattintson az Aktív elemre, és ellenőrizze, hogy a kimutatás aktívan használja-e az imént használt táblázatokat.

1. ellenőrzés: A tanultak áttekintése

Létrehozott egy kimutatást, amelyben több táblázat adatai szerepelnek. Ezek közül néhány táblázatot egy későbbi lépésben importált. Ahhoz, hogy az adatok együttesen használhatók legyenek, a táblázatok között kapcsolatokat kellett létrehoznia, amelyeket az Excel a sorok összekapcsolásához használ. Megtanulta, hogy az egyeztethető adatokat szolgáltató oszlopok alapvető fontosságúak a kapcsolódó sorok kereséséhez. A mintaadatokat tartalmazó fájlokban mindegyik táblázat tartalmaz olyan oszlopot, amely alkalmas erre a célra.

Bár a kimutatás már működőképes, valószínűleg észrevette, hogy több dolog is tökéletesítésre szorul még. A kimutatás mezőlistáján szerepelnek olyan táblázatok (DimEntity) és oszlopok (ETLLoadID), amelyek látszólag nem kapcsolódnak a Contoso vállalathoz. Ezenkívül a földrajzi adatok sincsenek még beépítve a kimutatásba.

Következő téma: A modell megjelenítése és bővítése a Power Pivot beépülő modullal

A következő feladatokban az Excel 2013 Microsoft Office Power Pivot a Microsoft Excel 2013 programban beépülő moduljával fogja bővíteni a modellt. Tapasztalni fogja majd, hogy a beépülő modul Diagram nézetével egyszerűbben hozhatja létre a kapcsolatokat. Ezenkívül a beépülő modullal számításokat és hierarchiákat fog létrehozni, el fogja rejteni azokat az elemeket, amelyeknek nem kell szerepelniük a mezőlistán, és optimalizálni fogja az adatokat a további jelentéskészítési lehetőségekhez.

Megjegyzés :  A Power Pivot a Microsoft Excel 2013 programban beépülő modul elérhető az Office Professional Plus csomagban. További információk: Power Pivot a Microsoft Excel 2013 programban beépülő modul.

Vegye fel a Power Pivot lapot az Excel menüszalagjára a Power Pivot bővítmény engedélyezésével.

  1. Válassza sorban a Fájl > Beállítások > Bővítmények lehetőséget.

  2. Kattintson a Kezelés legördülő menü COM-bővítmények elemére, majd az Ugrás gombra.

  1. Jelölje be a Microsoft Office Power Pivot a Microsoft Excel 2013 programban jelölőnégyzetet, majd kattintson az OK gombra.

A menüszalagon megjelenik a Power Pivot fül.

Kapcsolat hozzáadása a Power Pivot diagramnézetében

  1. Jelenítse meg a Munka3 munkalapot úgy, hogy az Excelben a lapfülére kattint. A Munka3 lap tartalmazza a korábban importált Geography táblázatot.

  2. Kattintson a menüszalagon a Power Pivot > Hozzáadás az adatmodellhez parancsra. Ezzel a lépéssel a Geography táblázatot hozzáadja a modellhez, és egyben megnyitja a Power Pivot bővítményt, amellyel végrehajtja majd a feladat hátralévő lépéseit.

  3. Láthatja, hogy a Power Pivot ablak megjeleníti a modell összes táblázatát, köztük a Geography táblázatot is. Kattintson rá néhány táblázatra. A bővítményben a modellbe foglalt összes adatot megtekintheti.

  4. Kattintson a Power Pivot-ablak Nézet részén található Diagramnézet gombra.

  5. A csúszka húzásával méretezze át a diagramot úgy, hogy az összes objektum látható legyen a diagramon. Vegye észre, hogy a következő két táblázat nem kapcsolódik a diagram többi részéhez: DimEntity és Geography.

  6. Kattintson a jobb gombbal a DimEntity táblára, és válassza a Törlés parancsot. Ez a tábla az eredeti adatbázis mellékterméke, a modellben nincs rá szükség.

  7. Nagyítsa fel a nézetet úgy, hogy lássa a Geography táblázat összes mezőjét. A táblázat diagramját a csúszkával állíthatja nagyobb méretűre.

  8. Láthatja, hogy a Geography táblázatban szerepel a GeographyKey oszlop. Itt olyan értékek szerepelnek, amelyek a Geography táblázat mindegyik sorát egyedi módon azonosítják. Nézzük meg, hogy a modellben szereplő többi táblázat is használja-e ezt a kulcsot. Ha igen, létre tudunk hozni egy olyan kapcsolatot, amely a Geography táblázatot a modell többi részéhez kapcsolja.

  9. Kattintson a Keresés elemre.

  10. A Metaadatok keresése mezőbe írja be a GeographyKey nevet.

  11. Kattintson néhányszor a Következő keresése gombra. Látni fogja, hogy a GeographyKey mező a Geography táblázatban és a Stores táblázatban is előfordul.

  12. Helyezze úgy át a Geography táblázatot, hogy a Stores táblázat mellé kerüljön.

  13. Húzza a Stores táblázat GeographyKey oszlopát a Geography táblázat GeographyKey oszlopára. A Power Pivot egy vonalat húz a két oszlop közé. Ez a vonal jelképezi a kapcsolatot.

Ebben a feladatban megismert egy új módszert a táblázatok felvételére és a kapcsolatok létrehozására. Most már van egy teljes modellje, amelyben össze vannak kapcsolva a táblázatok, és amely elérhető a Munka1 lapon található kimutatás számára.

Tipp :  A Diagramnézetben több tábla diagramja is teljesen ki van bontva, és láthatók az olyan oszlopok, mint például az ETLLoadID, a LoadDate és az UpdateDate oszlop. Ezek a konkrét mezők az eredeti Contoso adattárház melléktermékei, amelyek az adatkinyerési és -betöltési műveletek megkönnyítése érdekében kerültek az adatok közé. A modellben ezekre nincs szükség. Törlésükhöz jelölje ki őket, majd kattintson a jobb gombbal a mezőre, és válassza a Törlés parancsot.

Számított oszlop létrehozása

A Power Pivot bővítményben a DAX nyelven adhat meg számításokat. Ebben a feladatban kiszámítja a teljes nyereséget, valamint olyan számított oszlopokat vesz fel, amelyek más táblázatokból hivatkoznak adatértékekre. A későbbiekben azt is látni fogja, hogyan használhat hivatkozott oszlopokat a modell egyszerűsítésére.

  1. Váltson vissza adatnézetre a Power Pivot ablakában.

  2. Nevezze át a Table_ProductCategories accdb táblázatot valamilyen könnyebben kezelhető névre. A következő lépésekben hivatkozni fog erre a táblázatra, és rövidebb név esetén jobban áttekinthetők a számítások. Kattintson a jobb gombbal a táblázat nevére, és válassza az Átnevezés parancsot, írja be a ProductCategories nevet, majd nyomja le az Enter billentyűt.

  3. Jelölje ki a FactSales táblát.

  4. Kattintson a Tervezés > Oszlopok > Hozzáadás gombra.

  5. A táblázat feletti szerkesztőlécen írja be az alábbi képletet. Az automatikus kiegészítési funkció megkönnyíti az oszlopok és táblázatok teljes nevének beírását, és megjeleníti a használható függvények listáját. Arra is lehetősége van, hogy egyszerűen csak rákattintson az oszlopra, és a Power Pivot felveszi az oszlop nevét a képletbe.

    = [SalesAmount] - [TotalCost] - [ReturnAmount]

  6. Amikor befejezte a képlet összeállítását, fogadja el a képletet az Enter billentyű lenyomásával.

    A számított oszlop összes sorát értékekkel tölti ki a rendszer. Ha lefelé görgeti a táblázatot, láthatja, hogy az egyes sorokban eltérő értékek szerepelhetnek ennél az oszlopnál, az egyes sorokban levő adatoktól függően.

  7. Nevezze át az oszlopot: kattintson a jobb gombbal a CalculatedColumn1 címkére, és válassza az Oszlop átnevezése parancsot. Írja be a Profit nevet, majd nyomja le az Enter billentyűt.

  8. Most jelölje ki a DimProduct táblázatot.

  9. Kattintson a Tervezés > Oszlopok > Hozzáadás gombra.

  10. A táblázat feletti szerkesztőlécen adja meg a következő képletet.

    = RELATED(ProductCategories[ProductCategoryName])

    A RELATED függvény kapcsolódó táblából ad eredményül értéket. Ebben az esetben a ProductCategories táblában a termékkategóriák neve található, ez jól jön a DimProduct táblában, amikor kategóriaadatokat tartalmazó hierarchiát alakít ki. A függvénnyel kapcsolatban további tudnivalókat a RELATED függvény (DAX) című témakörben talál.

  11. Amikor befejezte a képlet összeállítását, fogadja el a képletet az Enter billentyű lenyomásával.

    A számított oszlop összes sorát értékekkel tölti ki a rendszer. Ha lefelé görgeti a táblázatot, láthatja, hogy most már mindegyik sor tartalmaz egy termékkategória-nevet.

  12. Nevezze át az oszlopot: kattintson a jobb gombbal a CalculatedColumn1 címkére, és válassza az Oszlop átnevezése parancsot. Írja be a ProductCategory nevet, majd nyomja le az Enter billentyűt.

  13. Kattintson a Tervezés > Oszlopok > Hozzáadás gombra.

  14. A tábla feletti szerkesztőlécen írja be a következő képletet, majd fogadja el a képletet az Enter billentyű lenyomásával.

    = RELATED(DimProductSubcategory[ProductSubcategoryName])

  15. Nevezze át az oszlopot: kattintson a jobb gombbal a CalculatedColumn1 címkére, és válassza az Oszlop átnevezése parancsot. Írja be a ProductSubcategory nevet, majd nyomja le az Enter billentyűt.

Hierarchia létrehozása

A legtöbb modell olyan adatokat tartalmaz, amelyek eleve hierarchikus kapcsolatban állnak egymással. Ilyenek például a naptári adatok és a termékkategóriák. A hierarchiák létrehozása azért hasznos, mert elegendő egy elemet (a hierarchiát) a jelentésre húzni, így nem kell több mezőt összegyűjteni és sorrendbe állítani többször is.

  1. A Power Pivotban váltson át diagramnézetre. Bontsa ki a DimDate táblázatot, hogy egyszerűbben megtekinthesse az összes mezőjét.

  2. Nyomja meg és tartsa lenyomva a Ctrl billentyűt, majd kattintson sorban a CalendarYear, a CalendarQuarter és a CalendarMonth oszlopra (lefelé kell majd görgetnie a táblázatot).

  3. Kattintson a három kijelölt oszlop egyikére a jobb gombbal, és válassza a Hierarchia létrehozása parancsot. A program létrehoz egy 1. hierarchia nevű szülő hierarchia-csomópontot a táblázat alján, és a kijelölt oszlopokat a hierarchia alá másolja gyermekcsomópontként.

  4. Írja be a Dátumok szöveget az új hierarchia neveként.

  5. Vegye fel a hierarchiába a FullDateLabel oszlopot. Kattintson a jobb gombbal a FullDateLabel címkére, és válassza a Hozzáadás hierarchiához parancsot. Válassza a Dátum típust. A FullDateLabel oszlopban teljes dátum szerepel az évvel, a hónappal és a nappal. Ellenőrizze, hogy a FullDateLabel oszlop a hierarchiában utolsóként jelenik-e meg. Így olyan többszintű hierarchiát alakított ki, amelyben szerepel az év, a negyedév, a hónap, illetve megtalálhatók az egyes naptári napok.

  6. Továbbra is a Diagramnézetben maradva mutasson az egérrel a DimProduct táblára, majd kattintson a tábla fejlécén látható Hierarchia létrehozása gombra. Megjelenik egy üres hierarchia szülőcsomópontja a tábla alján.

  7. Írja be a Termékkategóriák szöveget az új hierarchia neveként.

  8. A hierarchia gyermekcsomópontjainak létrehozásához húzza a ProductCategory és a ProductSubcategory oszlopot a hierarchiára.

  9. Kattintson az egér jobb oldali gombjával a ProductName elemre, és válassza a Hozzáadás hierarchiához menüpontot. Válassza a Termékkategóriák hierarchiát.

Most már ismeri a hierarchiák létrehozásának néhány módszerét, így a következő lépés a hierarchiák használata a kimutatásban.

  1. Térjen vissza az Excelbe.

  2. A Munka1 lapon (a kimutatást tartalmazó lapon) távolítsa el a Sorok területen lévő mezőket.

  3. Vegye fel helyettük a Termékkategóriák hierarchiát a DimProduct táblázatba.

  4. Hasonlóan cserélje le az Oszlopok területen lévő CalendarYear mezőt a Dates hierarchiára a DimDate táblázatban.

Ha most áttekinti az adatokat, észreveheti a hierarchiák használatának előnyeit. Egymástól függetlenül tudja kibontani és összecsukni a kimutatás különböző területeit, így pontosabban szabályozhatja a rendelkezésre álló hely felhasználását. Emellett azzal, hogy egyetlen hierarchiát vesz fel a Sorok és az Oszlopok területére, sokoldalú és azonnali részletezésre nyílik lehetősége anélkül, hogy több mezőt kellene felhalmoznia a hasonló eredmények eléréséhez.

Oszlopok elrejtése

Miután létrehozta a Termékkategóriák hierarchiát, és elhelyezte azt a DimProduct táblázatban, már nem lesz szüksége a DimProductCategory és a DimProductSubcategory táblázatra a kimutatás mezőlistáján. Ebben a feladatban megtanulhatja, hogyan rejtheti el a felesleges táblázatokat és oszlopokat, amelyek csak a helyet foglalják a kimutatás mezőlistáján. A táblázatok és az oszlopok elrejtésével kényelmesebbé teheti a jelentéskészítést úgy, hogy ez nincs hatással az adatkapcsolatokat és a számításokat szolgáltató modellre.

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Elrejthet meghatározott oszlopokat, oszloptartományt vagy akár egy egész táblát is. A táblázatnevek és az oszlopnevek ezt követően szürkén láthatók, ami azt jelzi, hogy el lesznek rejtve a modellt felhasználó jelentéskészítő ügyféleszközök elől. A rejtett oszlopok azért szürkék a modellben, hogy jelezzék az elrejtett állapotukat, de továbbra is láthatók maradnak Adat nézetben, hogy dolgozhasson velük.

  1. Győződjön meg arról, hogy a Power Pivot Adat nézete van kiválasztva.

  2. Az alul látható lapok között kattintson az egér jobb oldali gombjával a DimProductSubcategory elemre, és válassza az Elrejtés az ügyféleszközök elől menüpontot.

  3. Hajtsa végre ugyanezt a ProductCategories táblázatra vonatkozóan.

  4. Nyissa meg a DimProduct táblázatot.

  5. Kattintson az egér jobb oldali gombjával a következő oszlopokra, majd válassza az Elrejtés az ügyféleszközök elől menüpontot:

    • ProductKey

    • ProductLabel

    • ProductSubcategory

  6. Jelöljön ki több oszlopot a szomszédos oszlopok közül. Kezdje a ClassID oszloppal, és folytassa a ProductSubcategory oszlopig. Ha el szeretné rejteni az oszlopokat, kattintson rájuk a jobb gombbal.

  7. Ismételje meg ezt a műveletet a többi táblázatnál, eltávolítva az azonosítókat, illetve az egyéb adatokat, amelyeket nem fog használni a jelentésben.

Váltson vissza az Excelben az 1. munkalapra, a kimutatás mezőlistájára a különbség megtekintéséhez. A táblázatok száma csökkent, a DimProduct táblázat pedig csak azokat az elemeket tartalmazza, amelyekre valószínűleg szüksége lesz az értékesítések elemzésekor.

Power View nézetet tartalmazó jelentés létrehozása

Nem kizárólag a kimutatásos jelentéseknél vehetők igénybe az adatmodellek nyújtotta előnyök. Az imént létrehozott modell használatával felvehet egy Power View nézetet tartalmazó lapot, hogy kipróbálja a nézetben szereplő elrendezéseket.

  1. Az Excelben kattintson a Beszúrás > Power View gombra.

    Megjegyzés :  Ha ezen a számítógépen most használja először a Power View nézetet, a program előbb kéri a bővítmény engedélyezését és a Silverlight telepítését.

  2. Kattintson a Power View mezőlistájában a FactSales tábla melletti nyílra, és válassza a SalesAmount menüpontot.

  3. Bontsa ki a Geography táblázatot, és kattintson a RegionCountryName mezőre.

  4. Kattintson a menüszalagon levő Térkép gombra.

  5. Megjelenik egy térképes jelentés. Átméretezéséhez húzza az egyik sarkát. A térképen különböző méretű kék körök jelzik az egyes országok vagy régiók értékesítési eredményeit.

Optimalizálás a Power View nézetbeli jelentéskészítéshez

A modellen végzett csupán néhány apró változtatással jobban értelmezhető eredményeket érhet el a Power View nézetet tartalmazó jelentések tervezésekor. Ebben a feladatban több gyártó webhelyének URL-címét adja meg, majd ezeket az adatokat webes URL-címként sorolja kategóriába, hogy az URL-cím hivatkozásként jelenjen meg.

Első lépésként vegye fel a munkafüzetbe az URL-címeket.

  1. Nyisson meg egy új munkalapot az Excelben, és másolja be a következő értékeket:

ManufacturerURL

ManufacturerID

http://www.contoso.com

Contoso, LTD

http://www.adventure-works.com

Adventure Works

http://www.fabrikam.com

Fabrikam, Inc.

  1. Formázza a cellákat táblázatként, majd adja a táblázatnak az URL nevet.

  2. Hozzon létre kapcsolatot az URL táblázat, illetve a gyártók nevét tartalmazó DimProduct táblázat között:

    1. Kattintson az Adatok > Kapcsolatok elemre. Ekkor megjelenik a Kapcsolat létrehozása párbeszédpanel.

    2. Kattintson az Új lehetőségre.

    3. A Tábla listán válassza a DimProduct táblát.

    4. Az Oszlop listán válassza a Manufacturer táblát.

    5. A Kapcsolódó tábla listán válassza az URL táblát.

    6. A Kapcsolódó oszlop (elsődleges) listán válassza a ManufacturerID lehetőséget.

Az előző és a végrehajtás utáni állapot összevetése céljából nyisson meg egy új, Power View nézetet tartalmazó jelentést, és vegye fel jelentésbe a FactSales | SalesAmount, dimProduct | Manufacturer, valamint URL | ManufacturerURL táblákat. Láthatja, hogy az URL-címek statikus szövegként jelennek meg.

Ahhoz, hogy egy URL-cím aktív hivatkozásként jelenjen meg, kategóriába kell sorolni. Az oszlopok kategóriába sorolására a Power Pivot használható.

  1. A Power Pivotban nyissa meg az URL táblázatot.

  2. Válassza ki a ManufacturerURL mezőt.

  3. Kattintson a Speciális > Jelentéskészítési tulajdonságok > Adatkategória: Nem kategorizált elemre.

  4. Kattintson a lefelé mutató nyílra.

  5. Válassza a Webes URL kategóriát.

  6. Az Excelben kattintson a Beszúrás > Power View gombra.

  7. A Power View mezőlistájában válassza ki a FactSales | SalesAmount, dimProduct | Manufacturer, valamint URL | ManufacturerURL mezőket. Ekkor az URL-címek már tényleges hivatkozásként jelennek meg.

A Power View nézet további optimalizálásai közé tartozik az egyes táblázatok alapértelmezett mezőkészletének definiálása, valamint azon tulajdonságok beállítása, amelyek meghatározzák, hogy az ismétlődő adatokat tartalmazó sorokat a program összesítse-e vagy mindegyiket felsorolja-e. A további részleteket lásd: Alapértelmezett mezőkészlet konfigurálása Power View nézetet tartalmazó jelentésekhez és Táblázatviselkedési tulajdonságok konfigurálása Power View nézetet tartalmazó jelentésekhez.

Számított mezők létrehozása

Az adatok kimutatással történő felkutatásáról szóló második feladatban a kimutatás mezőlistájában a SalesAmount mezőre kattintott. Mivel a SalesAmount numerikus oszlop, automatikusan a kimutatás Értékek területére került. A SalesAmount oszlop összege így készen állt arra, hogy ennek alapján az alkalmazott szűrőkkel felhasználható legyen az értékesítési adatok számítására. Ebben az esetben nem volt szűrőhasználat az első lépésben, de később aztán a naptári évet, a termék-alkategória nevét és a márkanevet használta.

Tulajdonképpen nem tett mást, mint hogy implicit módon számított mezőt hozott létre, amely megkönnyíti az értékesítési adatok számítását a FactSales táblából az olyan más mezők szerint, mint a termékkategória, a régió vagy a dátum. Az implicit számított mezőket az Excel olyankor hozza létre, amikor a felhasználó egy mezőt az Értékek területre húz, vagy amikor numerikus mezőre kattint, ahogyan azt Ön is tette a SalesAmount mezővel. Az implicit számított mezők olyan képletek, amelyek szabványos összesítő függvények (például SZUM, DARAB vagy ÁTLAG) használatával automatikusan jönnek létre a programban.

Vannak másfajta számított mezők is. A Power Pivot használatával létrehozhat explicit számított mezőket is. Az implicit számított mezőkkel szemben (ezek csak abban a kimutatásban használhatók, amelyben létrehozták őket), az explicit számított mezők a munkafüzet bármelyik kimutatásában, illetve bármelyik olyan jelentésben felhasználhatók, amely adatforrásként az adatmodellt használja. A Power Pivot bővítményben létrehozott explicit számított mezőknél az AutoSzum funkcióval a szokványos összesítő függvények használatával automatikusan hozhat létre számított mezőket, illetve a DAX (Data Analysis Expressions) nyelven összeállított képlettel saját számított mezőket is létrehozhat.

Ahogyan azt el is lehet képzelni, a számított mezők létrehozása fantasztikus hatékonyságot nyújthat az adatok elemzésében, így érdemes belevágni létrehozásuk megtanulásába.

A Power Pivot bővítményben egyszerűen lehet számított mezőket létrehozni az AutoSzum funkció használatával.

  1. Kattintson a FactSales táblában a Profit oszlopra.

  2. Kattintson a Számítások > AutoSzum parancsra. Láthatja, hogy a számítási területen, közvetlenül a Profit oszlop alatt automatikusan létrejött egy új, Profit összege nevű számított mező.

  3. Kattintson az Excelben az 1. munkalap mezőlistájában, a FactSales oszlopban az Profit összege pontra.

Ennyi az egész. Mindössze ennyi kell ahhoz, hogy a Power Pivot bővítményben szabványos összesítő függvények használatával létrehozzon egy számított mezőt. Mint látja, alig néhány perc alatt létrehozta az ÖSSZEG – Profit számított mezőt, és felvette a kimutatásba. Ilyen módon az alkalmazott szűrőktől függően könnyebben elemezhető a nyereség. Ebben az esetben az Összeg – Profit mezőt a termékkategória és a dátum hierarchiája szűri.

Mi a helyzet azonban akkor, ha részletesebb elemzés szükséges, például egy adott értékesítési csatorna, termék vagy kategória értékesítési számait kell megvizsgálni? Ehhez egy újabb számított mezőt kell létrehozni, amely megszámolja a FactSales táblázatban minden értékesítéshez tartozóan szereplő sorokat (az alkalmazott szűrő függvényében).

  1. Kattintson a FactSales táblázatban a SalesKey oszlopra.

  2. Kattintson a Számítások területen az AutoSzum > Darabszám lefelé mutató nyilára.

  3. Nevezze át az új számított mezőt: kattintson a jobb gombbal a SalesKey darabszáma elemre, és válassza az Átnevezés menüpontot. Írja be a Darabszám függvényt, majd nyomja le az Enter billentyűt.

  4. Kattintson az Excelben az 1. munkalap mezőlistájában, a FactSales táblában a Darabszám függvényre.

Láthatja, hogy a kimutatásban megjelent egy új oszlop, a Darabszám, amely az értékesítések számát mutatja (az alkalmazott szűrők függvényében). A Profit összege számított mezőhöz hasonlóan a Darabszám oszlopot is a termékkategória és a dátum hierarchiája szerint szűrve láthatja.

Hozzunk létre még egyet. Ez alkalommal olyan számított mezőt fog létrehozni, amely egy adott kontextusra vagy szűrőre vonatkozóan számítja ki a teljes értékesítés megfelelő hányadát. A korábban az AutoSzum funkció használatával létrehozott számított mezőktől eltérő módon ezúttal manuálisan kell képletet megadnia.

  1. Kattintson egy üres cellára a FactSales táblázat számítási területén. Tipp: A bal felső cella kiváló kiindulópont a számított mezők elhelyezéséhez, így azok könnyebben megtalálhatóak lesznek. A számítási területen bármelyik számított mező áthelyezhető.

  2. A szerkesztőlécen az IntelliSense eszköz használatával írja be és hozza létre a következő képletet: Az összes termék százalékos hányada:=[Darab]/SZÁMOL([Darab], MIND(DimProduct))

  3. Az ENTER billentyű lenyomásával fogadja el a képletet.

  4. Kattintson az Excelben az 1. munkalap mezőlistájában, a FactSales oszlopban Az összes termék százalékos hányada pontra.

  5. A kimutatásban többszörös kijelöléssel jelölje ki Az összes termék százalékos hányada oszlopait.

  6. Kattintson a Kezdőlap lap Szám > Százalék parancsára. Az új oszlopok formázásához használjon két tizedeshelyet.

Ez az új számított mező egy adott szűrőkontextusra vonatkozóan számítja ki a teljes értékesítés százalékos hányadát. Ebben az esetben a szűrőkontextus továbbra is a termékkategória és a dátum hierarchiája. Így látható például, hogy a teljes értékesítésben hogyan növekedett a számítógépek százalékos aránya az eltelt évek alatt.

Ha jártas az Excel-képletek létrehozásában, a számított oszlopok és számított mezők képleteinek létrehozása viszonylag egyszerűen menni fog. Attól függetlenül, hogy mennyire ismeri az Excel-képletek létrehozását, a DAX-képletek létrehozásának alapjaival remekül megismerkedhet a Gyorsútmutató: DAX-alapismeretek 30 percben című cikkből.

A munka mentése

Mentse a munkafüzetet, hogy más oktatóanyagokkal vagy további ismerkedés céljából később is fel tudja használni.

Következő lépések

Bár az Excelből egyszerűen importálhatja az adatokat, ez a művelet gyakran gyorsabban és hatékonyabban elvégezhető a Power Pivot beépülő modullal. Az importált adatokat szűrheti, kizárhatja a nem szükséges oszlopokat, és választhat, hogy lekérdezéstervezőt vagy lekérdezési parancsot kíván-e használni az adatok beolvasásához. Következő lépésként tekintse át ezeket a módszereket: Adatok betöltése adatcsatornáról a Power Pivot programban és Adatok importálása az Analysis Services vagy a Power Pivot rendszerből.

A Power View jelentéskészítési funkciója együttműködik az imént létrehozotthoz hasonló adatmodellekkel. A következő témakörökben részletes ismertetést talál az adatmegjelenítésekről, amelyeket a Power View nézet tesz elérhetővé az Excel programban: A Power View nézet elindítása az Excel 2013 programban és Power View: Az adatok felkutatása, megjelenítése és bemutatása.

A Power View nézetet használó jelentések ügyesebb használatához próbálja meg továbbfejleszteni az adatmodellt az Oktatóprogram: Az adatmodell optimalizálása Power View nézetet tartalmazó jelentések készítéséhez használatával.

Ismeretek bővítése
Oktatóanyagok megismerése
Új szolgáltatások listájának lekérése
Részvétel az Office Insider programban

Hasznos volt az információ?

Köszönjük a visszajelzését!

Köszönjük visszajelzését. Jobbnak látjuk, ha az Office egyik támogatási szakemberéhez irányítjuk.

×