Adatmodell létrehozása az Excel programban

Az adatmodell lehetővé teszi több táblázatból származó adatok integrálását, így hatékonyan hozhat létre relációs adatforrást egy Excel-munkafüzetben. Az Excelben az adatmodellek használata transzparens módon, a kimutatásokban és kimutatásdiagramokban használt táblázatos adatokkal. Az adatmodellek táblagyűjteményként vannak vizualizálva egy mezőlistában, és legtöbbször soha nem is fogja tudni, hogy ott van.

Mielőtt elkezdené használni az adatmodellt, be kell szereznie néhány adatot. Ehhez a Get & Transform (Power Query) felületet fogjuk használni, ezért érdemes lehet visszalépni és watch egy videót, vagy követni a & átalakítás és a Power Pivot beszerzéséről szóló útmutatónkat.

Hol található a PowerPivot?

Excel 2016 & Microsoft 365 Excel – A Power Pivot megjelenik a menüszalagon.
Excel 2013 – A Power Pivot az Excel 2013 Office Professional Plus kiadásának része, de alapértelmezés szerint nincs engedélyezve. További információ az Excel 2013-hoz készült Power Pivot bővítmény elindításáról.
Excel 2010 – Töltse le a Power Pivot bővítményt, majd telepítse a Power Pivot bővítményt.

Hol található a Get & Transform (Power Query)?

Excel 2016 & Microsoft 365 Excel – A Get & Transform (Power Query) integrálva van az Excellel az Adatok lapon.
Excel 2013 – Power Query az Excel részét képező bővítmény, de aktiválni kell. Lépjen a Fájl > Beállítások > bővítmények elemre, majd a panel alján található Kezelés legördülő menüben válassza a COM-bővítmények > Ugrás lehetőséget. Ellenőrizze az Excelhez készült Microsoft Power Query, majd az OK gombra az aktiválásához. Ekkor megjelenik egy Power Query lap a menüszalagon.
Excel 2010 – Töltse le és telepítse a Power Query bővítményt.. Az aktiválást követően egy Power Query lap lesz hozzáadva a menüszalaghoz.

Első lépések

Először is be kell szereznie néhány adatot.

A Excel 2016 és a Microsoft 365 Excel az Adatok > Adatok beolvasása & Adatok átalakítása > Adatok lekérése lehetőséggel importálhat adatokat tetszőleges számú külső adatforrásból, például szövegfájlból, Excel-munkafüzetből, webhelyről, Microsoft Accessből, SQL Server vagy más, több kapcsolódó táblát tartalmazó relációs adatbázisból.

Az Excel 2013-ban és 2010-ben nyissa meg Power Query >Külső adatok lekérése lehetőséget, és válassza ki az adatforrást.
Az Excel megkéri, hogy válasszon ki egy táblázatot. Ha több táblát szeretne lekérni ugyanabból az adatforrásból, jelölje be a Több tábla kijelölésének engedélyezése jelölőnégyzetet. Ha több táblázatot jelöl ki, az Excel automatikusan létrehoz egy adatmodellt.

Megjegyzés: Ezekhez a példákhoz egy Excel-munkafüzetet használunk, amely fiktív diákadatokat tartalmaz az osztályokról és az osztályzatokról. Letöltheti a Tanulói adatmodell mintamunkafüzetet, és követheti a lépéseket. Egy befejezett adatmodellt tartalmazó verziót is letölthet..
Jelöljön ki egy vagy több táblát, majd kattintson a Betöltés gombra.

Ha szerkesztenie kell a forrásadatokat, válassza a Szerkesztés lehetőséget. További részletek: A Lekérdezésszerkesztő bemutatása (Power Query).

Most már rendelkezik egy adatmodellel, amely az összes importált táblát tartalmazza, és azok megjelennek a kimutatás mezőlistájában.

Megjegyzések:

A modellek implicit módon jönnek létre, amikor két vagy több táblázatot importál egyszerre az Excelben.
A modellek kifejezetten akkor jönnek létre, ha a Power Pivot bővítményt használja az adatok importálásához. A bővítményben a modell az Excelhez hasonló lapelrendezésben jelenik meg, ahol minden lap táblázatos adatokat tartalmaz. Az adatok SQL Server-adatbázissal történő importálásának alapjait az Adatok lekérése a Power Pivot bővítmény használatávalcímű témakörben tekintheti meg.
A modellek egyetlen táblát tartalmazhatnak. Ha csak egy táblán alapuló modellt szeretne létrehozni, jelölje ki a táblát, és kattintson a Hozzáadás az adatmodellhez elemre Power Pivot. Ezt akkor teheti meg, ha Power Pivot szolgáltatásokat szeretne használni, például szűrt adathalmazokat, számított oszlopokat, számított mezőket, KPI-ket és hierarchiákat.
A táblakapcsolatok automatikusan hozhatók létre, ha elsődleges és idegenkulcs-kapcsolattal rendelkező kapcsolódó táblákat importál. Az Excel általában az importált kapcsolatadatokat használja az adatmodellben lévő táblakapcsolatok alapjaként.
Az adatmodellek méretének csökkentésére vonatkozó tippekért lásd: Memóriahatékony adatmodell létrehozása az Excel és a Power Pivot használatával.
További információ : Oktatóanyag: Adatok importálása az Excelbe és Adatmodell létrehozása.

Tipp: Hogyan állapítható meg, hogy a munkafüzet rendelkezik-e adatmodellel? Lépjen a Power Pivot > Kezelése elemre. Ha munkalapszerű adatokat lát, akkor létezik modell. További információt a munkafüzet-adatmodellben használt adatforrásokat ismertető cikkben talál.

Kapcsolatok létrehozása a táblák között

A következő lépés a táblák közötti kapcsolatok létrehozása, hogy bármelyikből lekérhesse az adatokat. Minden táblának rendelkeznie kell egy elsődleges kulccsal vagy egyedi mezőazonosítóval, például tanulóazonosítóval vagy osztályszámmal. A legegyszerűbben húzással kapcsolhatja össze ezeket a mezőket a Power Pivot Diagram nézetében.

Lépjen a Power Pivot > Kezelése elemre.
A Kezdőlapon válassza a Diagramnézet lehetőséget.
Ekkor megjelenik az összes importált tábla, és érdemes lehet egy kis időt igénybe venni az átméretezésükhöz attól függően, hogy hány mező van az egyes táblákban.
Ezután húzza az elsődleges kulcs mezőjét az egyik táblából a másikba. Az alábbi példa a tanulói táblák diagramnézete:

A következő hivatkozásokat hoztuk létre:
- tbl_Students | Tanulóazonosító > tbl_Grades | Tanuló azonosítója
  
  Más szóval húzza a Diákazonosító mezőt a Tanulók táblából a Diák azonosítója mezőre az Osztályzatok táblában.
- tbl_Semesters | Félévazonosító > tbl_Grades | Félévben
- tbl_Classes | Osztályszám > tbl_Grades | Osztály száma
Megjegyzések:
- A mezőneveknek nem kell azonosnak lenniük a kapcsolat létrehozásához, de azonos adattípusúnak kell lenniük.
- A Diagram nézetben az összekötők egyik oldalán egy "1", a másikon "*" látható. Ez azt jelenti, hogy a táblák között egy-a-többhöz kapcsolat áll fenn, amely meghatározza az adatok kimutatásokban való használatát. További információ: Táblák közötti kapcsolatok adatmodellben .
- Az összekötők csak azt jelzik, hogy kapcsolat van a táblák között. Valójában nem jelenítik meg, hogy mely mezők vannak egymáshoz csatolva. A hivatkozások megtekintéséhez lépjen a Power Pivot > >Tervezés > Kapcsolatok kezelése > Kapcsolatok kezelése. Az Excelben az Adatkapcsolatok > lehetőséget választhatja.

Kimutatás vagy kimutatásdiagram létrehozása adatmodell használatával

Az Excel-munkafüzetek csak egy adatmodellt tartalmazhatnak, de ez a modell több táblázatot is tartalmazhat, amelyek a teljes munkafüzetben ismételten felhasználhatók. Egy meglévő adatmodellhez bármikor hozzáadhat további táblákat.

A Power Pivotlépjen a Kezelés elemre.
A Kezdőlapon válassza a Kimutatás lehetőséget.
Válassza ki, hogy hová szeretné helyezni a kimutatást: egy új munkalapot vagy az aktuális helyet.
Kattintson az OK gombra, és az Excel egy üres kimutatást ad hozzá a jobb oldalon látható Mezőlista panellel.

Ezután hozzon létre egy kimutatást, vagy hozzon létre egy kimutatásdiagramot. Ha már létrehozott kapcsolatokat a táblák között, bármelyik mezőt használhatja a kimutatásban. Már létrehoztunk kapcsolatokat a Tanulói adatmodell mintamunkafüzetben.

Meglévő, nem kapcsolódó adatok hozzáadása adatmodellhez

Tegyük fel, hogy sok olyan adatot importált vagy másolt, amelyet egy modellben használni szeretne, de még nem adta hozzá az adatmodellhez. Az új adatok modellbe való küldése egyszerűbb, mint gondolná.

Először jelölje ki a modellhez hozzáadni kívánt adatok bármelyik celláját. Bármilyen adattartomány lehet, de az Excel-táblázatként formázott adatok a legjobbak.
Az adatok hozzáadásához használja az alábbi módszerek egyikét:
Kattintson Power Pivot > Hozzáadás az adatmodellhez elemre.
Kattintson a Kimutatás beszúrása> elemre, majd jelölje be az Adatok hozzáadása az adatmodellhez jelölőnégyzetet a Kimutatás létrehozása párbeszédpanelen.

A tartomány vagy tábla mostantól csatolt táblaként lesz hozzáadva a modellhez. Ha többet szeretne megtudni a csatolt táblák modellben való használatáról, olvassa el az Adatok hozzáadása Excel csatolt táblák használatával a Power Pivotban című témakört.

Adatok hozzáadása Power Pivot táblához

A Power Pivot nem adhat hozzá sort a táblázathoz úgy, hogy közvetlenül beír egy új sort, mint egy Excel-munkalapon. Sorokat azonban másolhat és beilleszthet, illetve frissítheti a forrásadatokat, és frissítheti a Power Pivot-modellt.