A Microsoft Query segítségével külső forrásokból is beolvashat adatokat. Ha a Microsoft Query segítségével olvassa be az adatokat a vállalati adatbázisokból és fájlokból, nem kell újra és újra begépelnie az Excelben elemezni kívánt adatokat. Azt is megteheti, hogy automatikusan frissíti az Excel-jelentéseket és -összefoglalókat az eredeti forrásadatbázisból, amikor az adatbázis új információkkal frissül.
További információ a Microsoft Queryről
A Microsoft Query használatával külső adatforrásokhoz csatlakozhat, adatokat választhat ki belőlük, importálhatja őket a munkalapra, és szükség szerint frissítheti az adatokat, hogy a munkalap adatai szinkronban legyenek a külső forrásban lévő adatokkal.
A hozzáférhető adatbázisok típusai Számos különböző típusú adatbázisból olvashat be adatokat, így például a Microsoft Office Access, a Microsoft SQL Server és a Microsoft SQL Server OLAP-szolgáltatások esetén. Excel-munkafüzetekben és szövegfájlokban is olvashat be adatokat.
A Microsoft Office az alábbi adatforrásokból származó adatok beolvasásához biztosít illesztőprogramokat:
- 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óktól származó ODBC-illesztőket vagy adatforrás-illesztőprogramokat is használhat az információk beolvasásához olyan adatforrásokból, amelyek itt nem szerepelnek, beleértve az egyéb típusú OLAP-adatbázisokat is. Ha a listában nem szereplő ODBC-illesztőprogram vagy adatforrás-illesztőprogram telepítésével kapcsolatban további információra van szüksége, olvassa el az adatbázis dokumentációját, vagy lépjen kapcsolatba az adatbázis szállítójával.
Adatok kijelölése adatbázisból Az adatbázisból adatokat lekérdezés létrehozásával nyerhet ki, amely egy külső adatbázisban tárolt adatokkal kapcsolatban feltett kérdés. Ha például az adatokat egy Access-adatbázisban tárolja, célszerű lehet egy adott termék régiónkénti értékesítési adataira kíváncsi. Az adatok egy részét lekérheti csak az elemezni kívánt termék és régió adatainak kijelölésével.
A Microsoft Query segítségével kijelölheti a kívánt adatoszlopokat, és importálhatja azokat az Excelbe.
A munkalap frissítése egyetlen művelettel Ha már vannak külső adatok egy Excel-munkafüzetben, az adatbázis minden változása esetén frissítheti az adatokat az elemzés frissítése érdekében 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 azt, amikor új értékesítési adatok érkeznek.
Hogyan használja a Microsoft Query az adatforrásokat? Miután beállított egy adatforrást egy adott adatbázishoz, bármikor felhasználhatja, amikor létre szeretne hozni egy lekérdezést az adatbázis adatainak kiválasztásához és beolvasásához anélkül, hogy újra be kellene gépelnie az összes kapcsolati információt. A Microsoft Query az adatforrás segítségével csatlakozik a külső adatbázishoz, és megjeleníti, hogy milyen adatok állnak rendelkezésre. Miután létrehozta a lekérdezést, és visszaküldte az adatokat az Excelnek, a Microsoft Query a lekérdezés és az adatforrás adatait is megadja az Excel-munkafüzetnek, hogy Ön újra csatlakozhasson az adatbázishoz, ha frissíteni szeretné az adatokat.
Adatok importálása a Microsoft Query használatával Ha a Microsoft Query segítségével külső adatokat szeretne importálni az Excelbe, kövesse ezeket az alapvető lépéseket, amelyeket a következő szakaszok részletesebben ismertetnek.
Csatlakozás adatforráshoz
Mi az adatforrás? Az adatforrás olyan tárolt információhalmaz, amely lehetővé teszi, hogy az Excel és a Microsoft Query csatlakozzon egy külső adatbázishoz. Amikor a Microsoft Query segítségével állít be egy adatforrást, meg kell adnia az adatforrás nevét, helyét és helyét, az adatbázis típusát, valamint a bejelentkezési adatokat és a jelszót. Az információ tartalmazza az OBDC-illesztőprogram vagy az adatforrás-illesztőprogram nevét is, amely egy olyan program, amely kapcsolatot létesít egy adott típusú adatbázissal.
Adatforrás beállítása a Microsoft Query használatával:
Az Adatok lap Külső adatok átvétele csoportjában kattintson az Egyéb adatforrásból, majd a Microsoft Queryből parancsra.
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 válassza a Fájl, Beállítások, Adatok lehetőséget, és engedélyezze az engedélyezést az Örökölt adatimportáló varázslók megjelenítése szakaszban.
Tegye a következők valamelyikét:
- Egy adatbázis, szövegfájl vagy Excel-munkafüzet adatforrásának megadásához 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 Query programot az Excelből futtatta.
Kattintson duplán az <Új adatforrás elemre>.
– vagy –
Kattintson az Új adatforrás> elemre<, majd az OK gombra.
Megjelenik az Új adatforrás létrehozása párbeszédpanel.Az 1. lépésben írjon be egy nevet, amely azonosítja az adatforrást.
A 2. lépésben kattintson az adatforrásként használt adatbázis típusának megfelelő illesztőprogramra.
Megjegyzés
- Ha az elérni kívánt külső adatbázist nem támogatják a Microsoft Queryvel telepített ODBC-illesztők, 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éssel kapcsolatos útmutatásé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 a rendszer a Microsoft SQL Server Analysis Services használatával létrehozott adatbázisokhoz telepíti az illesztőprogramokat. Ha más OLAP-adatbázisokhoz szeretne kapcsolódni, telepítenie kell egy adatforrás-illesztőprogramot és egy ügyfélszoftvert.
Kattintson a Csatlakozás gombra, majd adja meg az adatforráshoz való kapcsolódáshoz szükséges adatokat. Adatbázisok, Excel-munkafüzetek és szövegfájlok esetén a rendelkezésre álló információ a kiválasztott adatforrás típusától függ. A rendszer kérheti a bejelentkezési nevet, a jelszót, a használt adatbázis verzióját, az adatbázis helyét vagy más, az adatbázis típusára jellemző információt.
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.
Miután megadta a szükséges információkat, az OK vagy a Befejezés gombra kattintva térjen vissza az Új adatforrás létrehozása párbeszédpanelre.
Ha az adatbázis táblákat tartalmaz, és szeretné, hogy egy adott tábla automatikusan megjelenjen a Lekérdezés varázslóban, kattintson a 4. lépéshez tartozó mezőre, majd a kívánt táblára.
Ha az adatforrás használatakor nem szeretné beírni bejelentkezési nevét és jelszavát, jelölje be a Felhasználóazonosító és jelszó mentése az adatforrás-definícióba jelölőnégyzetet. A mentett jelszó nincs titkosítva. Ha a jelölőnégyzet nem érhető el, kérdezze meg az adatbázisgazdát, hogy ez a lehetőség elérhetővé tehető-e.
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 fenti lépések végrehajtása után az adatforrás neve megjelenik az Adatforrás kiválasztása párbeszédpanelen.
Lekérdezés definiálása a Lekérdezés varázslóval
A legtöbb lekérdezéshez használja a Lekérdezés varázslót A Lekérdezés varázslóval egyszerűen kiválaszthatja és összeállíthatja az adatbázis különböző tábláiból és mezőiből származó adatokat. A Lekérdezés varázsló segítségével kiválaszthatja a táblákat és a mezőket, amelyeket szerepeltetni szeretne. A belső illesztés (olyan lekérdezési művelet, amely azt adja meg, hogy két tábla sorait azonos mezőértékek alapján kombinálja a program) automatikusan létrejön, amikor a varázsló felismeri az egyik tábla elsődlegeskulcs-mezőjét, és egy másik tábla azonos nevű mezőjét.
A varázsló segítségével rendezheti az eredményhalmazt, és egyszerűbb szűrést is végezhet. A varázsló utolsó lépésében dönthet úgy, hogy visszaküldi-e az adatokat az Excelnek, vagy tovább finomítja a lekérdezést a Microsoft Queryben. Létrehozása után a lekérdezést az Excelben vagy a Microsoft Queryben futtathatja.
A Lekérdezés varázsló elindításához hajtsa végre az alábbi lépéseket.
- Az Adatok lap Külső adatok átvétele csoportjában kattintson az Egyéb adatforrásból, majd a Microsoft Queryből parancsra.
- Az Adatforrás kiválasztása párbeszédpanelen győződjön meg arról, hogy be van jelölve a Lekérdezés varázsló használata lekérdezések létrehozására és szerkesztésére jelölőnégyzet.
- 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.
Közvetlenül a Microsoft Queryben más típusú lekérdezések esetén is Ha a Lekérdezés varázsló által megengedettnél összetettebb lekérdezést szeretne létrehozni, közvetlenül a Microsoft Queryben dolgozhat. A Microsoft Query segítségével megtekintheti és módosíthatja a Lekérdezés varázslóban létrehozott lekérdezéseket, illetve újakat is létrehozhat a varázsló használata nélkül. Közvetlenül a Microsoft Queryben dolgozhat akkor, ha az alábbi funkciókra alkalmas lekérdezéseket szeretne létrehozni:
- Adott adatok kijelölése egy mezőből Egy nagy adatbázisban előfordulhat, hogy egy mező adatainak egy részét ki kell választania, és ki kell hagynia a szükségtelen adatokat. Ha például egy több termék adatait tartalmazó mezőben kettő termékre vonatkozóan van szüksége adatokra, akkor feltételeket alkalmazva kiválaszthatja az adatokat a kívánt két termékhez.
- Adatok beolvasása a lekérdezés minden futtatásakor más feltételek alapján Ha a külső adatok több területéről is létre kell hoznia ugyanazt az Excel-jelentést vagy -összesítést – például minden régióhoz külön értékesítési jelentést kell készítenie –, paraméteres lekérdezést kell létrehoznia. Paraméteres lekérdezés futtatásakor a program kéri azt az értéket, amelyet a lekérdezés feltételként használ a rekordok kiválasztásakor. A paraméteres lekérdezés például kérheti, hogy adjon meg egy adott régiót, és ezt a lekérdezést újra felhasználhatja az összes területi értékesítési jelentés elkészítéséhez.
- Adatok egyesítése különböző módokon A lekérdezések létrehozásakor leggyakrabban használt belső illesztések a Lekérdezés varázsló által létrehozott belső illesztések. Időnként azonban más típusú illesztést szeretne használni. Ha például van egy termékértékesítési adatokat tartalmazó tábla és egy vevőadatokat tartalmazó tábla, egy (a Lekérdezés varázsló által létrehozott típusú) belső illesztés megakadályozza azon vevők rekordjainak beolvasását, akik nem vásároltak. A Microsoft Query segítségével összekapcsolhatja ezeket a táblákat, így a program lekéri az összes vevőrekordot, a vásárlók értékesítési adataival együtt.
A Microsoft Query elindításához hajtsa végre az alábbi lépéseket.
- Az Adatok lap Külső adatok átvétele csoportjában kattintson az Egyéb adatforrásból, majd a Microsoft Queryből parancsra.
- Az Adatforrás kiválasztása párbeszédpanelen győződjön meg arról, hogy nincs bejelölve a Lekérdezés varázslóval lekérdezések létrehozása és szerkesztése jelölőnégyzet.
- 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 újbóli felhasználása és megosztása A lekérdezésvarázslóban és a Microsoft Queryben egyaránt .dqy fájlként mentheti a lekérdezéseket, melyet módosíthat, újra felhasználhat és megoszthat. Az Excel közvetlenül meg tudja nyitni a .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 Excelben:
- Az Adatok lap Külső adatok átvétele csoportjában kattintson az Egyéb adatforrásból, majd a Microsoft Queryből parancsra. Megjelenik az Adatforrás kiválasztása párbeszédpanel.
- Az Adatforrás kiválasztása párbeszédpanelen kattintson a Lekérdezések fülre.
- Kattintson duplán a megnyitni kívánt mentett lekérdezésre. A lekérdezés megjelenik a Microsoft Queryben.
Ha mentett lekérdezést szeretne megnyitni, é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, az Excel megnyílik, futtatja a lekérdezést, majd beszúrja az eredményeket egy új munkalapra.
Ha külső adatokon alapuló Excel-összegzést vagy -jelentést szeretne megosztani, adhat másoknak külső adattartományt tartalmazó munkafüzetet, vagy létrehozhat egy sablont. A sablonok segítségével a külső adatok mentése nélkül mentheti az összesítést vagy jelentést, így a fájl kisebb lesz. A külső adatok lekérése akkor történik, amikor a felhasználó megnyitja a jelentéssablont.
Adatok használata az Excelben
Miután létrehozott egy lekérdezést a Lekérdezés varázslóban vagy a Microsoft Queryben, visszaküldheti az adatokat egy Excel-munkalapra. Az adatok ezután külső adattartományt vagy formázható és frissíthető kimutatást alkotnak.
Lekért adatok formázása Az Excelben diagramok vagy automatikus részösszegek segítségével jelenítheti meg és összegezheti a Microsoft Query által beolvasott adatokat. Az adatok formázhatók, és a formázás megőrződik a külső adatok frissítésekor. A mezőnevek helyett használhatja saját oszlopcímkéit, és automatikusan hozzáadhatja a sorok számozását.
Az Excel a tartományok végére beírt új adatokat automatikusan formázni tudja úgy, hogy az megfeleljen az előző soroknak. Az Excel automatikusan másolhatja az előző sorokban ismétlődő képleteket, és kiterjesztheti őket további sorokra.
Megjegyzés
Ahhoz, hogy a formátumokat és a képleteket ki lehessen terjeszteni a tartomány új soraira, a megelőző öt sor közül legalább háromban meg kell jelennie.
Ezt a beállítást bármikor bekapcsolhatja (vagy újra kikapcsolhatja):
- Kattintson aSpeciális fájlbeállítások>> lehetőségre.
- A Szerkesztési beállítások csoportban 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ányok automatikus formázását, törölje a jelet 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 beolvassa a specifikációinak megfelelő új vagy módosult adatokat. A lekérdezéseket a Microsoft Queryben és az Excelben is frissítheti. Az Excel több lehetőséget is biztosít a lekérdezések frissítésére, beleértve az adatok frissítését a munkafüzet minden megnyitásakor, illetve adott időközönként történő automatikus frissítését. Az adatok frissítése közben az Excelben folytathatja a munkát, és az adatok frissítése közben ellenőrizheti az állapotot. További információt a Külső adatkapcsolatból származó adatok frissítése az Excelben című témakörben talál.