Absztrakt: Ez az első oktatóanyag abban a sorozatban, amely célja, hogy megismerkedjen az Excellel és annak beépített adategyesítési és -elemzési funkcióival. Ezek az oktatóanyagok bemutatják egy teljesen új Excel-munkafüzet létrehozását és finomítását, adatmodell felépítését, majd lenyűgöző interaktív jelentések létrehozását a Power View segítségével. Ezek az oktatóanyagok a Microsoft üzletiintelligencia-funkcióinak és képességeinek bemutatását mutatják be az Excelben, a kimutatásokban, a Power Pivotban és a Power View-ban.
Ezekből az oktatóanyagokból megtudhatja, hogy miként importálhat és vizsgálhat fel adatokat az Excelben, hogyan hozhat létre és finomíthat adatmodelleket a Power Pivot segítségével, és hogyan készíthet közzé, védelemmel elláthatja és megosztható interaktív jelentéseket a Power View segítségével.
A sorozatban az alábbi oktatóanyagok találhatók:
- Adatok importálása az Excel 2016-ba és adatmodell létrehozása
- Adatmodell-kapcsolatok bővítése az Excel, a Power Pivot és a DAX használatával
- Térképalapú Power View-jelentések létrehozása
- Internetes adatok beépítése és Power View-jelentések alapértelmezéseinek beállítása
- A Power Pivot súgója
- Lenyűgöző Power View-jelentések létrehozása – 2. rész
Ebben az oktatóanyagban egy üres Excel-munkafüzetből indulunk ki.
Az oktatóanyag az alábbi részekből áll:
- Adatok importálása adatbázisból
- Adatok importálása számolótáblából
- Adatok importálása másolással és beillesztéssel
- Kapcsolat létrehozása az importált adatok között
- Ellenőrzés és teszt
Az oktatóanyag végén található tesztet elvégezve ellenőrizheti, hogy mennyit tanult.
Ez az oktatóanyag-sorozat olimpiai érmeket, vendéglátó országokat és különféle olimpiai sporteseményeket leíró adatokat használ. Azt javasoljuk, hogy a sorozat gyakorlatait sorban egymás után végezze el.
Adatok importálása adatbázisból
Az oktatóanyagban üres munkafüzetből indulunk ki. Ebben a szakaszban a célunk, hogy külső adatforráshoz csatlakozva részletesebb elemzés céljából adatokat importáljunk az Excelbe.
Kezdésként töltsünk le adatokat az internetről. Az adatok olimpiai érmekre vonatkoznak, és egy Microsoft Access-adatbázis tartalmazza őket.
Az alábbi hivatkozásokra kattintva töltse le az oktatóanyag-sorozathoz szükséges fájlokat. Töltse le mind a négy fájlt egy könnyen elérhető helyre, például a Letöltések vagy a Dokumentumok mappába, vagy hozzon létre egy újonnan létrehozott mappába:
> OlympicMedals.accdb Access adatbázis
> OlympicSports.xlsx Excel-munkafüzet
> Population.xlsx Excel-munkafüzet
> DiscImage_table.xlsx Excel-munkafüzetNyisson meg egy üres munkafüzetet az Excelben.
Kattintson az Adatok > beolvasása > Microsoft Access-adatbázisból > parancsra. A menüszalag dinamikusan igazodik a munkafüzet szélességéhez, ezért a rajta megjelenő parancsok némileg eltérhetnek az alábbi képernyőképen láthatóktól.
Jelölje ki a letöltött OlympicMedals.accdb fájlt, és kattintson az Importálás gombra. Megjelenik a következő Kezelő ablak az adatbázis tábláival. Az adatbázisok táblái hasonlóak az Excel munkalapjaihoz vagy táblázataihoz. Jelölje be a Több tábla kijelölése jelölőnégyzetet, és jelölje ki az összes táblát. Ezután kattintson a Betöltés > és betöltés ide elemre.
Megjelenik az Adatimportálás ablak.
Megjegyzés
Figyelje meg az ablak alján található jelölőnégyzetet, amellyel hozzáadhatja ezeket az adatokat az adatmodellhez (lásd az alábbi képen). Ha egyszerre két vagy több táblázatot importál vagy kezel, automatikusan létrejön az adatmodell. Az adatmodell integrálja a táblákat, lehetővé téve a kimutatások, a Power Pivot és a Power View segítségével történő kiterjedt elemzést. Amikor táblákat importál egy adatbázisból, a táblák között meglévő adatbázis-kapcsolatokat használja az Excel adatmodelljének létrehozására. Az adatmodell áttetsző, de közvetlenül megtekintheti és módosíthatja a Power Pivot beépülő modul használatával. Az adatmodellt az oktatóanyag későbbi részében tárgyaljuk részletesebben.
Válassza a Kimutatás lehetőséget, amely importálja a táblákat az Excelbe, és az importált táblák elemzésére szolgáló kimutatást készít. Ezután kattintson az OK gombra.
Az adatok importálását követően a program az importált táblák alapján kimutatást hoz létre.
Most, hogy importálta az adatokat az Excelbe, és az adatmodell automatikusan létrejött, készen áll az adatok vizsgálatára.
Az adatok vizsgálata kimutatás használatával
A kimutatások megkönnyítik az importált adatok vizsgálatát. A kimutatásokban az adatok megjelenítését az Excel-táblázatok oszlopaihoz hasonló mezőknek a táblákból (például az imént importált Access-adatbázis tábláiból) a kimutatás különböző területeire való húzásával állíthatja be. A kimutatásoknak négy területük van: SZŰRŐK, OSZLOPOK, SOROK és ÉRTÉKEK.
Némi kísérletezésre lehet szükség annak megállapításához, hogy az egyes mezőket melyik területre érdemes húzni. A táblákból tetszőleges számú mezőt húzhat a különböző területekre, hogy a kimutatásban úgy jelenjenek meg az adatok, ahogy szeretné. Nyugodtan kísérletezzen azzal, hogy a mezőket a kimutatás különböző területeire húzza – a kimutatás alapjául szolgáló adatokat a mezők elrendezése nem módosítja.
Vizsgáljuk meg a kimutatás olimpiai érmekre vonatkozó adatait, kezdve az olimpiai éremnyertesek szakág, éremtípus és a sportoló országa vagy régiója szerinti rendezésével.
A Kimutatásmezők területen a mellette lévő nyílra kattintva bontsa ki a Medals táblázatot . A kibontott Medals táblában keresse meg a NOC_CountryRegion mezőt, és húzza az OSZLOPOK területre. A NOC a Nemzeti Olimpiai Bizottságok rövidítése, amely egy ország vagy régió szervezeti egysége.
Ezután a Disciplines (Szakágak) táblából húzza a Discipline (Szakág) mezőt a SOROK területre.
Állítsunk be olyan szűrőt a Disciplines táblára, amely csak az alábbi öt sportágat jeleníti meg: Archery (Íjászat), Diving (Műugrás), Fencing (Vívás), Figure Skating (Műkorcsolya) és Speed Skating (Gyorskorcsolya). Ezt a Kimutatásmezők ablaktábláról és a kimutatásban található Sorcímkék szűrő használatával egyaránt megteheti.
- A kimutatás tetszőleges pontjára kattintva győződjön meg arról, hogy az Excel-kimutatás ki van jelölve. A kimutatás mezőlistájában , ahol a Disciplines (Szakágak) táblázat ki van bontva, mutasson a Discipline mezőre, és a mező jobb oldalán megjelenik egy legördülő nyíl. Kattintson a legördülő menüre, kattintson **(Az összes kijelölése)** elemre az összes kijelölés eltávolításához, majd görgessen lefelé, és válassza az Archery, a Diving, a Fencing, a Figure Skating és a Speed Skating elemet. Kattintson az OK gombra.
- Vagy a kimutatás Sorcímkék csoportjában kattintson a kimutatás Sorcímkék elem melletti nyílra, kattintson (Az összes kijelölése) elemre az összes kijelölés eltávolításához, majd görgessen lefelé, és válassza az Íjászat, a Műugrás, a Vívás, a Műkorcsolya és a Gyorskorcsolya elemet. Kattintson az OK gombra.
A Kimutatásmezők ablaktáblában a Medals táblából húzza a Medal (Érem) mezőt az ÉRTÉKEK területre. Mivel az értékek csak numerikusak lehetnek, az Excel a Medal mezőt automatikusan Elemszám – Medal mezőre módosítja.
A Medals táblában jelölje ki ismét a Medal mezőt, és húzza a SZŰRŐK területre.
Alkalmazzunk olyan szűrőt a kimutatásra, amely csak az összesen 90-nél több érmet nyert országokat és régiókat jeleníti meg. Kövesse az alábbi lépéseket:
- A kimutatásban kattintson az Oszlopcímkék cella jobb oldalán lévő nyílra.
- Válassza az Értékszűrők, majd pedig a Nagyobb, mint… lehetőséget.
- Írja be a 90 értéket a jobb szélső mezőbe. Kattintson az OK gombra.
A kimutatás így az alábbi képernyőképen látható módon néz ki.
Így kevés munkával létrehozott egy olyan alapszintű kimutatást, amely három 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ásadatbázisban, és mivel az összes táblát egyetlen művelettel importálta, az Excel újra létre tudta hozni a táblák közötti kapcsolatokat az adatmodellben.
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 kapcsolatainak létrehozására 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ának módját.
Adatok importálása számolótáblából
Importáljunk adatokat másik forrásból is – ezúttal egy meglévő számolótáblából –, és adjuk meg a meglévő és az új adatok közötti kapcsolatokat. Kapcsolatok segítségével Excel-adatok csoportjait elemezheti, és érdekes, lebilincselő megjelenítési módokat adhat meg az importált adatokhoz.
Elsőként hozzunk létre egy üres munkalapot, majd importáljunk adatokat egy Excel-munkafüzetből.
Szúrjon be egy új Excel-munkalapot, és adja neki a Sports (Sportágak) nevet.
Keresse meg a letöltött mintaadatfájlokat tartalmazó mappát, és nyissa meg az OlympicSports.xlsx fájlt.
Jelölje ki, majd másolja a Sheet1 (Munka1) munkalapon lévő adatokat. Ha kijelöl egy adatokat tartalmazó cellát (például az A1-et), a Ctrl + A billentyűkombinációval kijelölheti az összes környező adatot. Zárja be az OlympicSports.xlsx munkafüzetet.
A Sports munkalapon helyezze a kurzort az A1 cellára, és illessze be az adatokat.
Miközben az adatok ki vannak jelölve, a Ctrl + T billentyűkombinációval formázza táblázatként az adatokat. Az adatokat táblázatként is formázhatja a menüszalagon a KEZDŐLAP > Formázás táblázatként lehetőség kiválasztásával. Mivel az adatok rovatfejeket is tartalmaznak, jelölje be a Táblázat rovatfejekkel jelölőnégyzetet a megjelenő Táblázat létrehozása ablakban, ahogy az az alábbi képen is látható.
Az adatok táblázatként való formázásának számos előnye van. A táblázatnak nevet adhat, ami megkönnyíti az azonosítását. Ezenkívül kapcsolatokat létesíthet táblázatok között, lehetővé téve a kimutatások, valamint a Power Pivot és a Power View használatával történő vizsgálatukat és elemzésüket.
Nevezze el a táblázatot. A TABLE DESIGN > Properties (Táblatervező tulajdonságai) ablakban keresse meg a Tábla neve mezőt, és írja be a Sports nevet. A munkafüzet az alábbi képernyőképen látható módon néz ki.
Mentse a munkafüzetet.
Adatok importálása másolással és beillesztéssel
Miután végeztünk az Excel-munkafüzetből való importálással, importáljunk adatokat weblapon talált táblázatból, vagy bármely más olyan forrásból, amelyből másolhatjuk és beilleszthetjük őket az Excelbe. A következő lépésekben az olimpiai játékok vendéglátó városainak listáját vesszük fel egy táblázatból.
- Szúrjon be egy új Excel-munkalapot, és adja neki a Hosts (Vendéglátók) nevet.
- Jelölje ki, majd másolja az alábbi táblázatot a rovatfejekkel együtt.
- Az Excelben helyezze a kurzort a Hosts munkalap A1 cellájába, és illessze be az adatokat.
- Formázza az adatokat táblázatként. Az oktatóanyag korábbi szakaszában leírtak szerint a Ctrl + T billentyűkombinációt lenyomva formázza az adatokat táblázatként, illetve a KEZDŐLAPRÓL > a Formázás táblázatként lehetőséggel. Mivel az adatok rovatfejeket is tartalmaznak, jelölje be a Táblázat rovatfejekkel jelölőnégyzetet a megjelenő Táblázat létrehozása ablakban.
- Nevezze el a táblázatot. A TABLE DESIGN > Properties (Táblatervező tulajdonságai) ablakban keresse meg a Tábla neve mezőt, és írja be a Hosts nevet.
- Jelölje ki a Megrendezés oszlopot, és a KEZDŐLAP lapon állítsa Szám formátumúra 0 tizedesjeggyel.
- Mentse a munkafüzetet. A munkafüzet az alábbi képernyőképen látható módon néz ki.
Miután felvette a táblákat az Excel-munkafüzetbe, kapcsolatokat hozhat létre közöttük. Ha kapcsolatokat hoz létre a táblák között, egyesítheti az adataikat.
Kapcsolat létrehozása az importált adatok között
Akár azonnal is elkezdheti felhasználni az importált táblák mezőit a kimutatásban. Ha az Excel nem tudja eldönteni, hogy hogyan építsen be egy adott mezőt a kimutatásba, kapcsolatot kell létrehozni a meglévő adatmodellel. A következő lépések során megtudhatja, hogyan kell kapcsolatot létrehozni a különböző forrásokból importált adatok között.
A Munka1 lapon, aKimutatásmezők lista tetején kattintson aMind elemre az elérhető táblák teljes listájának megjelenítéséhez, az alábbi képen látható módon.
Görgessen végig a listán, és figyelje meg az imént hozzáadott új táblákat.
Bontsa ki a Sports táblát, és adja hozzá a Sport elemet a kimutatáshoz a jelölőnégyzete bejelölésével. Az Excel ekkor az alábbi képernyőképen látható módon kapcsolat létrehozását kéri.
Az értesítés azért jelenik meg, mert az alapul szolgáló adatmodellben nem szereplő táblákból származó mezőket is felhasznált. A táblák adatmodellhez való hozzáadásának egyik módja, ha kapcsolatot hoz létre egy, már az adatmodell részét képező táblával. A kapcsolat létrehozásának feltétele, hogy a táblák egyikének egyedi, nem ismétlődő értékeket tartalmazó oszloppal kell rendelkeznie. A mintaadatok között az adatbázisból importált Disciplines (Szakágak) táblában szerepel egy SportID (Sportág-azonosító) nevű, sportágkódokat tartalmazó mező. Ugyanezek a sportágkódok szerepelnek mezőként az importált Excel-adataink között is. Hozzuk létre a kapcsolatot.A Kimutatásmezők ablaktábla megjelölt részén látható LÉTREHOZÁS gombra kattintva nyissa meg az alábbi képernyőképen látható Kapcsolat létrehozása párbeszédpanelt.
A Táblázat területen válassza az Adatmodell táblázat: Disciplines (Adatmodell-táblázat: Disciplines) lehetőséget a legördülő listából.
Az Oszlop (külső) legördülő menüből válassza a SportID elemet.
A Kapcsolódó tábla listában válassza az Adatmodell-tábla: Sportágak lehetőséget.
A Kapcsolódó oszlop (elsődleges) legördülő menüből válassza a SportID elemet.
Kattintson az OK gombra.
A kimutatás az új kapcsolatnak megfelelően megváltozik. A kimutatás azonban még mindig nem teljesen úgy néz ki, ahogy szeretnénk, hiszen a SOROK területen lévő mezők sorrendje nem megfelelő. A szakág a sportág egy alkategóriája, mi viszont a SOROK területen a Discipline (Szakág) mezőt a Sport (Sportág) mező felett helyeztük el, tehát a sorrend nem megfelelő. Az alábbi képernyőképen ez a nem kívánt sorrend látható.
A SOROK területen húzza a Sport mezőt a Discipline mező fölé. Így már sokkal jobb – a kimutatás megfelelően jeleníti meg az adatokat, ahogy az az alábbi képernyőképen is látható.
A színfalak mögött az Excel egy adatmodellt állít össze, amely a teljes munkafüzetben, bármely kimutatásban, kimutatásdiagramban, a Power Pivotban vagy bármelyik Power View nézetet tartalmazó jelentésben használható. A táblák közötti kapcsolatok képezik az adatmodell alapját, és ezek határozzák meg a navigációs és számítási útvonalakat is.
A következő, " Adatmodell-kapcsolatok bővítése az Excel, a Power Pivot** és a DAX** segítségével" című oktatóanyagban az itt tanultakra építve részletesen kibővítheti az adatmodellt a Power Pivot nevű hatékony és vizuális Excel-bővítménnyel. Azt is megtudhatja, hogy miként számíthat oszlopokat egy táblázatban, és a számított oszlop használatával egy egyébként nem kapcsolódó táblázatot adhat hozzá az adatmodellhez.
Ellenőrzés és teszt
A tanultak áttekintése
A gyakorlat során Excel-munkafüzetet hozott létre, azon belül pedig egy több tábla adataihoz hozzáférő kimutatást, amely táblák közül többet külön importált. Megtanulta, hogyan kell adatbázisból, egy másik Excel-munkafüzetből, illetve az adatok másolása és Excelbe való beillesztése útján adatokat importálni.
Ahhoz, hogy az adatok használhatók legyenek egymással, kapcsolatot kellett létrehoznia a táblák között, amely alapján az Excel egymáshoz rendelte a sorokat. Megtanulta ezenkívül, hogy a kapcsolatok létrehozásához és az összefüggő sorok kereséséhez alapvető fontosságú, hogy az egyik tábla tartalmazzon a másik tábla adataihoz kapcsolódó oszlopokat.
Ezzel készen áll a sorozat következő oktatóanyagára. Itt van egy hivatkozás:
Oktatóprogram: Adatmodell-kapcsolatok bővítése az Excel, a Power Pivot és a DAX használatával
TESZT
Szeretné ellenőrizni, hogy mennyire emlékszik a tanultakra? Itt a lehetőség. A következő teszt az oktatóanyagban megismert funkciókra, képességekre és követelményekre vonatkozik. A válaszokat a lap alján találja. Sok sikert!
1. kérdés: Miért fontos táblává alakítani az importált adatokat?
A: Nem szükséges táblává alakítani őket, mert minden importált adat automatikusan táblává alakul.
B: Ha az importált adatokat táblává alakítom, nem fognak szerepelni az adatmodellben. Csak az adatmodellben nem szereplő adatok használhatók fel kimutatásokban, valamint a Power Pivot és a Power View bővítményben.
C: Ha az importált adatokat táblázattá alakítom, beépíthetők az adatmodellbe, és felhasználhatók kimutatásokban, valamint a Power Pivotban és a Power View nézetben.
D: Az importált adatok nem alakíthatók táblává.
2. kérdés: Az alábbi adatforrások közül melyek importálhatók az Excelbe és vehetők fel az adatmodellbe?
A: Access-adatbázisok és számos más adatbázistípus.
B: Meglévő Excel-fájlok.
C: Bármilyen adat, amely másolható, beilleszthető az Excelbe, majd táblaként formázható, beleértve a webhelyeken, dokumentumokban és bármely más, Excelbe beilleszthető adatokat tartalmazó forrásokban található adattáblákat.
D: A fentiek mindegyike igaz
3. kérdés: Mi történik, ha a kimutatásban átrendezi a Kimutatásmezők ablaktábla négy területén található mezőket?
A: Semmi – a mezők nem rendezhetők át, miután elhelyeztem őket a Kimutatásmezők ablaktábla valamelyik területén.
B: A kimutatás formátuma az elrendezésnek megfelelően módosul, de az alapjául szolgáló adatok nem változnak.
C: A kimutatás formátuma az elrendezésnek megfelelően módosul, és az alapjául szolgáló összes adat visszavonhatatlanul megváltozik.
D: A kimutatás alapjául szolgáló adatok módosulnak, így új adatkészletek jönnek létre.
4. kérdés: Mik a táblák közötti kapcsolatok létrehozásának feltételei?
A: Egyik táblában sem lehetnek egyedi, nem ismétlődő értékeket tartalmazó oszlopok.
B: Az egyik tábla nem lehet az Excel-munkafüzet része.
C: Az oszlopok nem lehetnek táblává alakítva.
D: A fentiek egyike sem helyes.
Válaszok a tesztkérdésekre
- A helyes válasz: C
- A helyes válasz: D
- A helyes válasz: B
- A helyes válasz: D
Megjegyzés
Az oktatóanyag-sorozatban felhasznált adatok és képek forrása:
- Olimpiai adatkészlet: Guardian News & Media Ltd.
- Zászlóképek: CIA Factbook (cia.gov)
- Népességadatok: Világbank (worldbank.org)
- Olimpiai sportágak piktogramjai: Thadius856 és Parutakupiu