Használta már az FKERES függvényt arra, hogy oszlopot vigyen át az egyik táblázatból a másikba? Mivel az Excel beépített adatmodellel rendelkezik, az FKERES elavult. Két adattáblázat között kapcsolatot létesíthet, ha vannak egymásnak megfelelő adatok a táblázatokban. Ezután a táblázatok mezői alapján Power View-lapokat, illetve kimutatásokat és más jelentéseket hozhat létre akkor is, ha a táblázatok eltérő forrásból származnak. Ha például ügyfelekkel kapcsolatos értékesítési adatai vannak, előfordulhat, hogy időintelligencia-adatokat is importálna és kapcsolna, mert elemezni szeretné az értékesítési trendeket évek és hónapok szerint.
A kimutatások és a Power View nézetek mezőlistájában a munkafüzet összes táblázata látható.
Ha kapcsolódó táblákat importál egy relációs adatbázisból, az Excel gyakran létre tudja hozni a kapcsolatokat az adatmodellben, amelyet a háttérben állít össze. Minden más esetben manuálisan kell létrehoznia a kapcsolatokat.
-
Győződjön meg arról, hogy a munkafüzet legalább két táblázatot tartalmaz, és hogy mindegyik táblázatban szerepel olyan oszlop, amely egy másik táblázatban levő oszlophoz csatolható.
-
Tegye a következők egyikét: Formázza az adatokat táblázatként, vagy importálja a külső adatokat táblázatként egy új munkalapon.
-
Adjon a táblázatoknak értelmes nevet: A név megadásához a Táblázateszközök eszközcsoportban kattintson a Tervezés > Táblázat neve mezőbe.
-
Ellenőrizze, hogy az egyik táblázat megfelelő oszlopában nincsenek-e duplikált értékek. Az Excel csak úgy tud létrehozni kapcsolatot, ha az egyik oszlop csak egyedi értékeket tartalmaz.
Ha például az ügyfelekkel kapcsolatos értékesítési adatokat időbeli adatokkal szeretné összekapcsolni, mindkét táblázatban azonos formátumú dátumértékeknek kell szerepelniük (például 2012.01.01.), valamint legalább az egyik táblázatban (időintelligencia) egy dátumnak csak egyszer szabad szerepelnie az oszlopban.
-
Kattintson az Adatok > Kapcsolatok gombra.
Ha a Kapcsolatok gomb szürkén jelenik meg, a munkafüzet csak egy táblázatot tartalmaz.
-
A Kapcsolatok kezelése párbeszédpanelen kattintson az Új gombra.
-
Kattintson a Kapcsolat létrehozása párbeszédpanel Táblázat mezője melletti nyílra, és válasszon ki egy táblázatot a listáról. Ennek a táblázatnak „egy-a-többhöz” kapcsolat esetén a „több” oldalon kell szerepelnie. Az ügyféladatok és időbeli adatok példájánál maradva, előbb az ügyfelekkel kapcsolatos értékesítési táblázatot jelöli ki, mert valószínűleg minden napon több értékesítés is történik.
-
Az Oszlop (külső) listáról válassza ki azt az oszlopot, amely a Kapcsolódó oszlop (elsődleges) beállításnál megadott oszlophoz kapcsolódó adatokat tartalmazza. Ha mindkét táblában volt dátumoszlop, most válassza ki azt az oszlopot.
-
A Kapcsolódó tábla listáról válasszon ki egy olyan táblát, amelynek legalább egy adatoszlopa kapcsolatban áll a Tábla mezőben kiválasztott táblával.
-
A Kapcsolódó oszlop (elsődleges) listáról válasszon ki egy olyan oszlopot, amelynek egyedi értékei megfelelnek az Oszlop listáról kiválasztott oszlop értékeinek.
-
Kattintson az OK gombra.
További információ az Excelben lévő táblázatok közötti kapcsolatokról
Megjegyzések a kapcsolatokról
-
Látni fogja, hogy léteznek-e kapcsolatok, amikor különböző táblázatokból származó mezőket húz az egérrel egy kimutatás mezőlistájára. Ha a program nem szólítja fel kapcsolat létrehozására, akkor az Excel már rendelkezik azokkal az információkkal a kapcsolatokról, amelyekre szüksége van az adatok összekapcsolásához.
-
A kapcsolatok létrehozása az FKERES függvényhez hasonló: egyező adatokat tartalmazó oszlopokra van szüksége, hogy az Excel kereszthivatkozásokat hozzon létre egy tábla sorai és egy másik tábla sorai között. Az időintelligenciát tartalmazó példában az Ügyfél táblázatban olyan dátumértékeknek kell szerepelniük, amelyek egy időintelligencia-táblázatban is megtalálhatóak.
-
Az adatmodellekben a táblakapcsolatok lehetnek egy-az-egyhez (minden utas egy beszállókártyával rendelkezik) vagy egy-a-többhöz (minden járat sok utassal rendelkezik), de nem több-a-többhöz. A több-a-többhöz kapcsolatok körkörös függőségi hibákat eredményeznek, például "Körkörös függőséget észleltünk". Ez a hiba akkor fordul elő, ha közvetlen kapcsolatot hoz létre két több-a-többhöz vagy közvetett kapcsolatú tábla között (az egyes kapcsolatokon belüli egy-a-többhöz típusú táblakapcsolatok láncolata, de végpontok közötti megtekintés esetén több-a-többhöz). További információ az adatmodellben lévő táblák közötti kapcsolatokról.
-
A két oszlop adattípusainak kompatibilisnek kell lenniük egymással. További információt az Adattípusok az Excel-adatmodellekben című témakörben találhat.
-
A kapcsolatok létrehozásának más, intuitívabb módjai is vannak, különösen akkor, ha nem tudja, hogy mely oszlopokat használja. További információ: Kapcsolatok létrehozása Diagramnézetben a Power Pivot beépülő modulban.
Példa: Időintelligencia-adatok összekapcsolása légitársaság járatadataival
A táblázatok közötti kapcsolatokról és az időintelligencia-adatokról további ismereteket szerezhet a Microsoft Azure Piactér ingyenes adatainak használatával. Némelyik ilyen adathalmaz nagyon nagy méretű, így gyors internetkapcsolat szükséges az adatok ésszerű időn belüli letöltéséhez.
-
Indítsa el a Power Pivot Microsoft Excel-bővítményt, és nyissa meg a Power Pivot-ablakot.
-
Kattintson a Külső adatok beolvasása > Adatszolgáltatásból > A Microsoft Azure piactérről parancsra. Ekkor a táblázatimportáló varázslóban megnyílik a Microsoft Azure Piactér kezdőlapja.
-
A Price (Ár) csoportban kattintson a Free (Ingyenes) szűrőre.
-
A Category (Kategória) csoportban kattintson a Science & Statistics (Tudomány és statisztika) szűrőre.
-
Keresse meg a DateStream adatcsatornát, és kattintson a Subscribe (Előfizetés) elemre.
-
Lépjen be Microsoft-fiókjába, és kattintson a Sign in (Bejelentkezés) gombra. Az ablakban meg kell jelennie az adatok előnézetének.
-
Görgessen le legalulra, és kattintson a Select Query (Lekérdezés kiválasztása) gombra.
-
Kattintson a Tovább gombra.
-
Válassza a BasicCalendarUS lehetőséget, majd az adatok importálásához kattintson a Befejezés gombra. Gyors internetkapcsolat esetén az importálás körülbelül egy percig tart. Amikor befejeződött a művelet, egy állapotjelentés látható, amely közli, hogy 73 414 sor átvitele történt meg. Kattintson a Bezárás gombra.
-
Második adatkészlet importálásához kattintson a Külső adatok átvétele > Adatszolgáltatásból > A Microsoft Azure piactérről parancsra.
-
A Type (Típus) csoportban válassza a Data (Adatok) lehetőséget.
-
A Price (Ár) csoportban kattintson a Free (Ingyenes) szűrőre.
-
Keresse meg a US Air Carrier Flight Delays (Amerikai légitársaságok késései) adatkészletet, és kattintson a Select (Kiválasztás) gombra.
-
Görgessen le legalulra, és kattintson a Select Query (Lekérdezés kiválasztása) gombra.
-
Kattintson a Tovább gombra.
-
Az adatok importálásához kattintson a Befejezés gombra. Gyors internetkapcsolat esetén az importálás úgy 15 percig tarthat. Amikor befejeződött a művelet, egy állapotjelentés látható, amely közli, hogy 2 427 284 sor átvitele történt meg. Kattintson a Bezárás gombra. Ekkor az adatmodellben két táblázatnak kell szerepelnie. Összekapcsolásukhoz mindkét táblázatban kompatibilis oszlopoknak kell szerepelniük.
-
Láthatja, hogy a BasicCalendarUS adatkészletben a DateKey kulcs 1/1/2012 12:00:00 AM formátumú. A On_Time_Performance táblázatban szintén szerepel egy dátum/idő adatokat tartalmazó oszlop, a FlightDate, amelynek értékei ugyanebben a formátumban vannak megadva: 1/1/2012 12:00:00 AM. A két oszlop ugyanolyan típusú, egyező adatokat tartalmaz, és legalább az egyik oszlop (a DateKey oszlop) kizárólag egyedi értékeket tartalmaz. A következő néhány lépés során ezeket az oszlopokat fogja használni a táblázatok összekapcsolására.
-
A Power Pivot-ablakban kattintson a Kimutatás gombra, ezzel új vagy meglévő munkalapon hoz létre kimutatást.
-
A mezőlistában bontsa ki az On_Time_Performance csomópontot, és az ArrDelayMinutes mezőre kattintva vegye fel az Értékek területre. A kimutatásban a késő járatok összesített késését kell látnia percben kifejezve.
-
Bontsa ki a BasicCalendarUS csomópontot, és a MonthInCalendar mezőre kattintva vegye fel a Sorok területre.
-
Láthatja, hogy a kimutatás most már hónapokat listáz, de az összesített percek száma minden hónapnál ugyanaz. Az ismétlődő, azonos értékek azt mutatják, hogy kapcsolat létrehozása szükséges.
-
A mezőlista „Szükség lehet a táblák közötti kapcsolatokra” csoportjában kattintson a Létrehozás gombra.
-
A Kapcsolódó tábla listában válassza az On_Time_Performance, míg a Kapcsolódó oszlop (elsődleges) listában válassza a FlightDate adatokat.
-
A Tábla listában válassza a BasicCalendarUS, az Oszlop (külső) listában válassza a DateKey oszlopot. A kapcsolat létrehozásához kattintson az OK gombra.
-
Láthatja, hogy ettől kezdve minden hónapnál eltérő a késett percek összesített értéke.
-
A BasicCalendarUS táblában húzza a YearKey címkét a Sorok területre, a MonthInCalendar adatok fölé.
Ettől kezdve a késéseket hónap és év, illetve a naptárban szereplő egyéb értékek szerint szeletelheti.
Tippek: Alapértelmezés szerint a hónapok betűrendben szerepelnek a felsorolásban. A Power Pivot bővítmény használatával megváltoztathatja a rendezést, hogy a hónapok időrendben jelenjenek meg.
-
Legyen a Power Pivot-ablakban megnyitva a BasicCalendarUS tábla.
-
Kattintson a Kezdőlap > Rendezés és szűrés gombra, majd a Rendezés más oszlop alapján parancsra.
-
A Rendezés csoportban válassza a MonthInCalendar értéket.
-
Az E szerint csoportban válassza a MonthOfYear értéket.
A kimutatás mostantól minden hónap-év kombinációt (2011. október, 2011. november) egy éven belüli hónapszám szerint (10, 11) rendez. A rendezési sorrend módosítása egyszerű, mert a DateStream-hírcsatorna biztosítja az összes szükséges oszlopot a forgatókönyv működéséhez. Ha másik időintelligencia-táblát használ, a lépés más lesz.
„Szükség lehet a táblák közötti kapcsolatokra”
Miközben felveszi a mezőket a kimutatásba, a program tájékoztatja, ha a kimutatásban kiválasztott mezők értelmezéséhez táblázatkapcsolat szükséges.
Bár az Excel képes jelezni, ha kapcsolat szükséges, azt nem tudja megmondani, hogy mely táblákat és oszlopokat kell használni, vagy hogy egyáltalán lehetséges-e a táblák közötti kapcsolatokat kialakítani. Ezekre a kérdésekre az alábbi lépések végrehajtásával próbálhat választ kapni.
1. lépés: A kapcsolatban megadandó táblázatok megállapítása
Ha a modell csupán néhány táblázatot tartalmaz, akár azonnal is nyilvánvaló lehet, hogy melyeket kell használni. Nagyobb modellek esetében azonban előfordulhat, hogy elkél némi segítség. Az egyik megközelítés a Diagramnézet használata a Power Pivot bővítményben. A Diagramnézet vizuális formában jeleníti meg az adatmodellben található összes táblázatot. A Diagramnézet használatával gyorsan meg tudja állapítani, hogy mely táblázatok választhatók külön a modell többi részétől.
Megjegyzés: Létre lehet hozni olyan nem egyértelmű kapcsolatokat is, amelyek kimutatásban vagy nézetet tartalmazó jelentésben érvénytelenek. Például az összes tábla valamilyen módon kapcsolódik a modellben szereplő többi táblához, de amikor a különböző táblákból megpróbál mezőket egyesíteni, a „Szükség lehet a táblák közötti kapcsolatokra” üzenetet kapja. Ennek a legvalószínűbb oka, hogy „több-a-többhöz” típusú kapcsolatba futott bele. Ha végigköveti a használni kívánt táblákat összekapcsoló táblázatkapcsolatok láncolatát, valószínűleg azt látja, hogy két vagy több „egy-a-többhöz” típusú táblakapcsolattal van dolga. Erre nincs minden helyzetben használható egyszerű kerülő megoldás, de megpróbálhatja számított oszlopok létrehozásával egyetlen táblában egyesíteni a használni kívánt oszlopokat.
2. lépés: Az egyik táblázattól a másikhoz vezető logikai útvonal kialakítására felhasználható oszlopok megkeresése
Miután azonosította, hogy melyik táblázat nem kapcsolódik az adatmodell többi részéhez, nézze meg a benne szereplő oszlopokat, hogy szerepel-e benne olyan oszlop, amely egyező értékeket tartalmaz a modell többi helyéhez képest.
Tegyük fel például, hogy olyan modellt használ, amelyben területek szerint szerepelnek az értékesítések, és aztán demográfiai adatokat importál, hogy megnézze, van-e kapcsolat az egyes területek értékesítési adatai és demográfiai trendjei között. Mivel a demográfiai adatok más adatforrásból származnak, táblázatai kezdetben elkülönülnek a modell többi részétől. Ahhoz, hogy a demográfiai adatokat a modell többi részével integrálni tudja, a demográfiai oszlopokban egy olyan oszlopot kell találnia, amely megfeleltethető a már használt oszlopok valamelyikének. Ha például a demográfiai adatok régió szerint vannak strukturálva, és az értékesítési adatok megadják, hogy melyik régióban történt az eladás, a két adatkészletet egy olyan közös oszlopon keresztül kapcsolhatja össze, mint például Megye, Irányítószám vagy Régió.
Kapcsolat létrehozásához az egyező értékek mellett további követelményeknek is teljesülniük kell:
-
A keresési oszlop adatértékeinek egyedinek kell lenniük. Más megfogalmazásban: az oszlop nem tartalmazhat ismétlődő értékeket. Az adatmodellekben a null értékek és az üres karakterláncok egyenértékűek az üres értékkel, amely egy önálló adatérték. Ez azt jelenti, hogy a keresési oszlop nem tartalmazhat több null értéket.
-
A forrás- és a keresőoszlopban szereplő adatoknak egymással kompatibilis típusúnak kell lenniük. Az adattípusokról további tudnivalókat Az adatmodellekben használt adattípusok című témakörben talál.
A táblakapcsolatokról további tudnivalókat az Adatmodellben szereplő táblázatok közötti kapcsolatok című témakörben talál.
