Külső adatok lekérése a Microsoft Query használatával

A Microsoft Query használatával csatlakozhat külső adatforrásokhoz, kiválaszthat adatokat ezekből a külső forrásokból, importálhatja ezeket az adatokat a munkalapra, és szükség szerint frissítheti az adatokat, hogy a munkalap adatai szinkronizálva legyenek a külső forrásokban lévő adatokkal.

Az elérhető adatbázistípusok     Több típusú adatbázisból is lekérhet adatokat, például a Microsoft Office Accessből, a Microsoft SQL Serverből és a Microsoft SQL Server OLAP Servicesből. Excel-munkafüzetekből és szövegfájlokból is lekérhet adatokat.

A Microsoft Office olyan illesztőprogramokat biztosít, amelyekkel az alábbi adatforrásokból kérdezhet le adatokat:

• Microsoft SQL Server Analysis Services (OLAP-szolgáltató )

• Microsoft Office Access

• dBASE

• Microsoft FoxPro

• Microsoft Office Excel

• Oracle

• Paradoxon

• Szövegfájl-adatbázisok

Más gyártók ODBC-illesztőit vagy adatforrás-illesztőprogramjait is használhatja az itt nem felsorolt adatforrásokból, beleértve az OLAP-adatbázisok más típusait is. Ha olyan ODBC-illesztőprogram vagy adatforrás-illesztőprogram telepítéséről van szó, amely nem szerepel a listán, tekintse meg az adatbázis dokumentációját, vagy forduljon az adatbázis gyártójához.

Adatok kiválasztása adatbázisból     Egy adatbázisból egy lekérdezés létrehozásával kérhet le adatokat, amely egy külső adatbázisban tárolt adatokkal kapcsolatos kérdés. Ha például az adatokat egy Access-adatbázisban tárolja, érdemes lehet tudni egy adott termék értékesítési adatait régiónként. Az adatok egy részét lekérheti, ha csak az elemezni kívánt termék és régió adatait választja ki.

A Microsoft Query segítségével kiválaszthatja a kívánt adatoszlopokat, és csak azokat az adatokat importálhatja az Excelbe.

Munkalap frissítése egyetlen művelettel     Miután külső adatokkal rendelkezik egy Excel-munkafüzetben, amikor az adatbázis megváltozik, frissítés az adatokat az elemzés frissítéséhez – anélkül, hogy újra létre kellene hoznia az összegző jelentéseket és diagramokat. Létrehozhat például egy havi értékesítési összegzést, és havonta frissítheti az új értékesítési adatok érkezésekor.

Hogyan használja a Microsoft Query az adatforrásokat?     Miután beállított egy adatforrást egy adott adatbázishoz, bármikor használhatja azt, amikor létre szeretne hozni egy lekérdezést az adatbázis adatainak kiválasztásához és lekéréséhez – anélkül, hogy újra be kellene írnia az összes kapcsolati adatot. A Microsoft Query az adatforrás használatával csatlakozik a külső adatbázishoz, és megjeleníti az elérhető adatokat. Miután létrehozta a lekérdezést, és visszaadta az adatokat az Excelnek, a Microsoft Query biztosítja az Excel-munkafüzetnek a lekérdezési és az adatforrás-információkat is, így az adatok frissítésekor újra csatlakozhat az adatbázishoz.

Adatok importálása a Microsoft Query használatával     Ha külső adatokat szeretne importálni az Excelbe a Microsoft Queryvel, kövesse ezeket az alapvető lépéseket, amelyekről részletesebben a következő szakaszokban olvashat.

Mi az az adatforrás?     Az adatforrások olyan tárolt információk, amelyek lehetővé teszik az Excel és a Microsoft Query számára, hogy külső adatbázishoz csatlakozzanak. Amikor a Microsoft Query használatával állít be egy adatforrást, adjon nevet az adatforrásnak, majd adja meg az adatbázis vagy kiszolgáló nevét és helyét, az adatbázis típusát, valamint a bejelentkezési és jelszóadatokat. Az adatok között szerepel egy OBDC-illesztő vagy egy adatforrás-illesztőprogram neve is, amely egy adott típusú adatbázishoz való kapcsolódást lehetővé tevő program.

Adatforrás beállítása a Microsoft Query használatával:

1. Az Adatok lap Külső adatok beolvasása csoportjában kattintson az Egyéb forrásokból, majd a Microsoft Queryből elemre.

   Megjegyzés: Az Excel 365 áthelyezte a Microsoft Queryt az Örökölt varázslók menücsoportba.  Ez a menü alapértelmezés szerint nem jelenik meg.  Az engedélyezéshez lépjen a Fájl, Beállítások, Adatok és engedélyezés elemre az Örökölt adatok importálása varázslók megjelenítése szakaszban.

2. Tegye a következők valamelyikét:

   • Ha adatforrást szeretne megadni egy adatbázishoz, szövegfájlhoz vagy Excel-munkafüzethez, kattintson az Adatbázisok fülre.

   • OLAP-kocka adatforrásának megadásához kattintson az OLAP-kockák fülre. Ez a lap csak akkor érhető el, ha a Microsoft Queryt az Excelből futtatta.

3. Kattintson duplán <Új adatforrás>.

   – vagy –

   Kattintson <Új adatforrás>elemre, majd kattintson az OK gombra.

   Megjelenik az Új adatforrás létrehozása párbeszédpanel.

4. Az 1. lépésben adjon meg egy nevet az adatforrás azonosításához.

5. A 2. lépésben kattintson az adatforrásként használt adatbázis típusának illesztőprogramjára.

   Megjegyzések: 

   • Ha az elérni kívánt külső adatbázist nem támogatják a Microsoft Queryvel telepített ODBC-illesztőprogramok, akkor be kell szereznie és telepítenie kell egy Microsoft Office-kompatibilis ODBC-illesztőprogramot egy külső gyártótól, például az adatbázis gyártójától. A telepítési utasításokért forduljon az adatbázis gyártójához.

   • Az OLAP-adatbázisokhoz nincs szükség ODBC-illesztőprogramokra. A Microsoft Query telepítésekor az illesztőprogramok a Microsoft SQL Server Analysis Services használatával létrehozott adatbázisokhoz lesznek telepítve. Más OLAP-adatbázisokhoz való csatlakozáshoz telepítenie kell egy adatforrás-illesztőprogramot és egy ügyfélszoftvert.

6. Kattintson a Csatlakozás gombra, majd adja meg az adatforráshoz való csatlakozáshoz szükséges információkat. Adatbázisok, Excel-munkafüzetek és szövegfájlok esetében a megadott információk a kiválasztott adatforrás típusától függenek. Előfordulhat, hogy meg kell adnia egy bejelentkezési nevet, egy jelszót, a használt adatbázis verzióját, az adatbázis helyét vagy az adatbázis típusára vonatkozó egyéb információkat.

   Fontos: 

   • Használjon nagy- és kisbetűket, számokat, valamint szimbólumokat tartalmazó erős jelszót. A gyenge jelszavak nem tartalmazzák ezeket az elemeket. Erős jelszó: Y6dh!et5. Gyenge jelszó: Szoba27. A jelszavaknak 8 vagy több karakterből kell állniuk. A 14 vagy több karaktert használó jelszavak jobbak.

   • Nagyon fontos, hogy ne felejtse el a jelszót. Ha mégis elfelejtené, a Microsoft nem tudja visszakeresni. Írja le és tárolja biztonságos helyen a jelszót, messze a védeni kívánt adatoktól.

7. Miután megadta a szükséges adatokat, kattintson az OK vagy a Befejezés gombra az Új adatforrás létrehozása párbeszédpanelre való visszatéréshez.

8. Ha az adatbázis táblákat tartalmaz, és azt szeretné, hogy egy adott tábla automatikusan megjelenjen a Lekérdezés varázslóban, kattintson a 4. lépés mezőjére, majd kattintson a kívánt táblára.

9. Ha nem szeretné beírni a bejelentkezési nevét és jelszavát az adatforrás használatakor, jelölje be a Felhasználóazonosító és jelszó mentése az adatforrás definíciójában jelölőnégyzetet. A mentett jelszó nincs titkosítva. Ha a jelölőnégyzet nem érhető el, forduljon az adatbázis rendszergazdájához, és állapítsa meg, hogy ez a beállítás elérhető-e.

   Biztonsági megjegyzés: Lehetőség szerint ne mentse a bejelentkezési adatokat az adatforrásokhoz való csatlakozáskor. A fájl esetleg kódolatlan formában tárolhatja ezeket az információkat, így az ártó szándékú felhasználók hozzáférhetnek a felhasználónévhez és a jelszóhoz, és jogosulatlanul érhetik el az adatforrást.

A lépések elvégzése után az adatforrás neve megjelenik az Adatforrás kiválasztása párbeszédpanelen.

A Lekérdezés varázsló használata a legtöbb lekérdezéshez     A Lekérdezés varázslóval egyszerűen kijelölheti és összehozhatja az adatbázis különböző tábláiból és mezőiből származó adatokat. A Lekérdezés varázslóval kiválaszthatja a felvenni kívánt táblákat és mezőket. Egy belső illesztés (egy lekérdezési művelet, amely meghatározza, hogy két tábla sorai azonos mezőértékek alapján vannak kombinálva) automatikusan létrejön, amikor a varázsló felismeri az egyik táblában az elsődleges kulcsmezőt, egy másik táblában pedig egy azonos nevű mezőt.

A varázslóval rendezheti is az eredményhalmazt, és elvégezheti az egyszerű szűrést. A varázsló utolsó lépésében dönthet úgy, hogy visszaadja az adatokat az Excelnek, vagy tovább finomítja a lekérdezést a Microsoft Queryben. A lekérdezés létrehozása után futtathatja az Excelben vagy a Microsoft Queryben.

A Lekérdezés varázsló elindításához hajtsa végre az alábbi lépéseket.

1. Az Adatok lap Külső adatok beolvasása csoportjában kattintson az Egyéb forrásokból, majd a Microsoft Queryből elemre.

2. Az Adatforrás kiválasztása párbeszédpanelen jelölje be a Lekérdezés varázsló használata lekérdezések létrehozásához/szerkesztéséhez jelölőnégyzetet.

3. Kattintson duplán a használni kívánt adatforrásra.

   – vagy –

   Kattintson a használni kívánt adatforrásra, majd az OK gombra.

Munkavégzés közvetlenül a Microsoft Queryben más típusú lekérdezésekhez     Ha a Lekérdezés varázsló által engedélyezettnél összetettebb lekérdezést szeretne létrehozni, közvetlenül a Microsoft Queryben dolgozhat. A Microsoft Query használatával megtekintheti és módosíthatja a Lekérdezés varázslóban létrehozott lekérdezéseket, vagy új lekérdezéseket is létrehozhat a varázsló használata nélkül. Közvetlenül a Microsoft Queryben dolgozhat, ha az alábbi lekérdezéseket szeretné létrehozni:

• Adott adatok kiválasztása egy mezőből     Nagy méretű adatbázisokban érdemes lehet kijelölni egy mező néhány adatát, és kihagyni a szükségtelen adatokat. Ha például két termék adataira van szüksége egy olyan mezőben, amely több termék adatait tartalmazza, a feltételek használatával csak a két kívánt termék adatait választhatja ki.

• Adatok lekérése különböző feltételek alapján a lekérdezés minden futtatásakor     Ha ugyanazt az Excel-jelentést vagy -összefoglalást kell létrehoznia ugyanazon külső adatok több területéhez – például minden régióhoz külön értékesítési jelentést –, létrehozhat egy paraméteres lekérdezés. Paraméteres lekérdezés futtatásakor a rendszer egy olyan értéket kér, amelyet feltételként használ, amikor a lekérdezés rekordokat választ ki. Előfordulhat például, hogy egy paraméteres lekérdezés egy adott régió megadását kéri, és ezt a lekérdezést felhasználhatja az egyes regionális értékesítési jelentések létrehozásához.

• Adatok csatlakoztatása különböző módokon     A Lekérdezés varázsló által létrehozott belső illesztések a lekérdezések létrehozásakor leggyakrabban használt illesztéstípusok. Előfordulhat azonban, hogy más típusú illesztéseket szeretne használni. Ha például egy termékértékesítési adatokat tartalmazó táblával és egy ügyfélinformációs táblával rendelkezik, a belső illesztés (a Lekérdezés varázsló által létrehozott típus) megakadályozza a vásárlást nem végző ügyfelek ügyfélrekordjainak lekérését. A Microsoft Query használatával összekapcsolhatja ezeket a táblákat, hogy az összes ügyfélrekord lekérhető legyen, valamint azoknak az ügyfeleknek az értékesítési adataival együtt, akik vásárlásokat hajtottak végre.

A Microsoft Query elindításához hajtsa végre a következő lépéseket.

1. Az Adatok lap Külső adatok beolvasása csoportjában kattintson az Egyéb forrásokból, majd a Microsoft Queryből elemre.

2. Az Adatforrás kiválasztása párbeszédpanelen győződjön meg arról, hogy a Lekérdezés varázsló használata lekérdezések létrehozásához/szerkesztéséhez jelölőnégyzet nincs bejelölve.

3. Kattintson duplán a használni kívánt adatforrásra.

   – vagy –

   Kattintson a használni kívánt adatforrásra, majd az OK gombra.

Lekérdezések újrafelhasználása és megosztása     A Lekérdezés varázslóban és a Microsoft Queryben is mentheti a lekérdezéseket .dqy fájlként, amelyet módosíthat, újra felhasználhat és megoszthat. Az Excel közvetlenül is megnyithat .dqy fájlokat, így Ön vagy más felhasználók további külső adattartományokat hozhatnak létre ugyanabból a lekérdezésből.

Mentett lekérdezés megnyitása az Excelből:

1. Az Adatok lap Külső adatok beolvasása csoportjában kattintson az Egyéb forrásokból, majd a Microsoft Queryből elemre. Megjelenik az Adatforrás kiválasztása párbeszédpanel.

2. Az Adatforrás kiválasztása párbeszédpanelen kattintson a Lekérdezések fülre.

3. Kattintson duplán a megnyitni kívánt mentett lekérdezésre. A lekérdezés megjelenik a Microsoft Queryben.

Ha meg szeretne nyitni egy mentett lekérdezést, és a Microsoft Query már meg van nyitva, kattintson a Microsoft Query-fájl menüre, majd a Megnyitás parancsra.

Ha duplán kattint egy .dqy fájlra, megnyílik az Excel, futtatja a lekérdezést, majd beszúrja az eredményeket egy új munkalapra.

Ha külső adatokon alapuló Excel-összefoglalást vagy -jelentést szeretne megosztani, megadhat más felhasználóknak egy külső adattartományt tartalmazó munkafüzetet, vagy létrehozhat egy sablon. A sablon lehetővé teszi, hogy a külső adatok mentése nélkül mentse az összegzést vagy jelentést, hogy a fájl kisebb legyen. A külső adatok akkor lesznek lekérve, amikor egy felhasználó megnyitja a jelentéssablont.

Miután létrehozott egy lekérdezést a Lekérdezés varázslóban vagy a Microsoft Queryben, visszaadhatja az adatokat egy Excel-munkalapra. Az adatok ezután formázható és frissíthető külső adattartomány vagy kimutatás lesznek.

Lekért adatok formázása     Az Excelben eszközök, például diagramok vagy automatikus részösszegek használatával jelenítheti meg és összegzheti a Microsoft Query által lekért adatokat. Formázhatja az adatokat, és a formázás megmarad a külső adatok frissítésekor. A mezőnevek helyett használhat saját oszlopcímkéket, és automatikusan hozzáadhat sorszámokat.

Az Excel automatikusan formázhatja a tartomány végén beírt új adatokat az előző soroknak megfelelően. Az Excel automatikusan átmásolhatja az előző sorokban ismétlődő képleteket, és kiterjesztheti őket további sorokra.

Ezt a beállítást bármikor bekapcsolhatja (vagy ismét ki is kapcsolhatja):

1. Válassza a Fájl>Beállítások>Speciális kategóriát.

2. A Szerkesztési beállítások szakaszban jelölje be az Adattartomány-formátumok és -képletek kiterjesztése jelölőnégyzetet . Ha ismét ki szeretné kapcsolni az adattartomány automatikus formázását, törölje a jelet ebből a jelölőnégyzetből.

Külső adatok frissítése     A külső adatok frissítésekor a lekérdezés futtatásával lekérheti a specifikációknak megfelelő új vagy módosított adatokat. A lekérdezéseket a Microsoft Queryben és az Excelben is frissítheti. Az Excel számos lehetőséget kínál a lekérdezések frissítésére, beleértve az adatok frissítését, amikor megnyitja a munkafüzetet, és automatikusan frissíti őket időben. Az adatok frissítése közben továbbra is dolgozhat az Excelben, és az adatok frissítése közben is ellenőrizheti az állapotot. További információ: Külső adatkapcsolat frissítése az Excelben.

Vissza a lap tetejére