Ulkoisten tietojen noutaminen Microsoft Queryn avulla

Voit noutaa tietoja ulkoisista lähteistä Microsoft Queryn avulla. Kun haet tiedot yrityksen tietokannoista ja tiedostoista Microsoft Queryn avulla, sinun ei tarvitse kirjoittaa tietoja Excelissä analysoitavaksi uudelleen. Voit myös päivittää Excel-raportit ja yhteenvedot automaattisesti alkuperäisestä lähdetietokannasta aina, kun tietokantaan päivitetään uusia tietoja.

Lisätietoja Microsoft Querystä

Microsoft Queryn avulla voit muodostaa yhteyden ulkoisiin tietolähteisiin, valita tietoja ulkoisista lähteistä, tuoda tiedot laskentataulukkoon ja päivittää tietoja tarpeen mukaan, jotta laskentataulukon tiedot pysyvät synkronoituina ulkoisten tietolähteiden tietojen kanssa.

Käytettävissä olevat tietokantatyypit Voit hakea tietoja erityyppisistä tietokannoista, kuten Microsoft Office Accessista, Microsoft SQL Server- ja Microsoft SQL Server OLAP Services -tietokannoista. Voit hakea tietoja myös Excel-työkirjoista ja tekstitiedostoista.

Microsoft Officessa on ohjaimia, joiden avulla voit hakea tietoja seuraavista tietolähteistä:

• Microsoft SQL Server Analysis Services (OLAP-palvelu)
• Microsoft Office Access
• dBASE
• Microsoft FoxPro
• Microsoft Office Excel
• Oracle
• Paradoksi
• Tekstitiedostotietokannat

Voit myös käyttää muiden valmistajien ODBC-ohjaimia tai tietolähdeohjaimia tietojen hakemiseen tietolähteistä, joita ei ole lueteltu tässä, mukaan lukien muuntyyppiset OLAP-tietokannat. Saat lisätietoja sellaisen ODBC-ohjaimen tai tietolähdeohjaimen asentamisesta, jota ei ole lueteltu tässä, tietokannan käyttöohjeista tai tietokannan toimittajalta.

Tietojen valitseminen tietokannasta Tiedot haetaan tietokannasta luomalla kysely. Kysely on ulkoiseen tietokantaan tallennetuista tiedoista. Jos tiedot on esimerkiksi tallennettu Access-tietokantaan, haluat ehkä tietää tietyn tuotteen myyntiluvut alueittain. Voit noutaa osan tiedoista valitsemalla vain analysoitavan tuotteen ja alueen tiedot.

Microsoft Queryn avulla voit valita haluamasi tietosarakkeet ja tuoda vain kyseiset tiedot Exceliin.

Laskentataulukon päivittäminen yhdellä toiminnolla Kun Excel-työkirjassa on ulkoisia tietoja, voit päivittää analyysia päivittämällä tietokannan tiedot ilman, että sinun tarvitsee luoda yhteenvetoraportteja ja kaavioita uudelleen. Voit esimerkiksi luoda kuukausittaisen myyntiyhteenvedon ja päivittää sen joka kuukausi, kun saat uusia myyntilukuja.

Miten Microsoft Query käyttää tietolähteitä Kun olet määrittänyt tietolähteen tiettyä tietokantaa varten, voit käyttää sitä aina, kun haluat luoda kyselyn, jolla voit valita ja hakea tietoja kyseisestä tietokannasta ilman, että sinun tarvitsee kirjoittaa kaikkia yhteystietoja uudelleen. Microsoft Query muodostaa tietolähteen avulla yhteyden ulkoiseen tietokantaan ja näyttää, mitä tietoja on saatavilla. Kun olet luonut kyselyn ja palauttanut tiedot Exceliin, Microsoft Query toimittaa Excel-työkirjaan sekä kyselyn että tietolähteen tiedot, jotta voit muodostaa yhteyden tietokantaan, kun haluat päivittää tiedot.

Kun käytät Microsoft Queryä tietojen tuomiseen Ulkoisten tietojen tuominen Exceliin Microsoft Queryn avulla tapahtuu seuraavasti. Seuraa näitä perusvaiheita, jotka on kuvattu tarkemmin seuraavissa osioissa.

Yhdistäminen tietolähteeseen

Mikä tietolähde on?  Tietolähde on tallennettu tietojoukko, jonka avulla Excel ja Microsoft Query voivat muodostaa yhteyden ulkoiseen tietokantaan. Kun määrität tietolähteen Microsoft Queryn avulla, annat tietolähteelle nimen ja sitten tietokannan tai palvelimen nimen ja sijainnin, tietokannan tyypin ja kirjautumis- ja salasanatiedot. Tiedot sisältävät myös OBDC-ohjaimen tai tietolähdeohjaimen nimen, joka on ohjelma, joka muodostaa yhteyksiä tietyntyyppiseen tietokantaan.

Tietolähteen määrittäminen Microsoft Queryn avulla:

1. Valitse Tiedot-välilehdenHae ulkoiset tiedot -ryhmästä Muista lähteistä ja valitse sitten Microsoft Querystä.

   Huomautus

   Excel 365 on siirtänyt Microsoft Querynvanhojen ohjattujen toimintojen valikkoryhmään .  Tätä valikkoa ei oletusarvoisesti näytetä.  Ota toiminto käyttöön valitsemalla Tiedosto,Asetukset, Tiedot ja ota käyttöön Näytä vanhat ohjatut tietojen tuontitoiminnot .

2. Toimi seuraavasti:

   • Määritä tietokannan, tekstitiedoston tai Excel-työkirjan tietolähde valitsemalla Tietokannat-välilehti .
   • Määritä OLAP-kuution tietolähde valitsemalla OLAP-kuutiot-välilehti . Tämä välilehti on käytettävissä vain, jos suoritit Microsoft Queryn Excelistä.

3. Kaksoisnapsauta <Uusi tietolähde>.
   -tai-
   Valitse <Uusi tietolähde> ja valitse sitten OK.
   Luo uusi tietolähde -valintaikkuna tulee näkyviin.

4. Kirjoita vaiheessa 1 tietolähteen tunnistenimi.

5. Valitse vaiheessa 2 tietolähteenä käyttämäsi tietokantatyypin ohjain.

   Huomautus

   • Jos Microsoft Queryn mukana asennetut ODBC-ohjaimet eivät tue ulkoista tietokantaa, jota haluat käyttää, sinun on hankittava ja asennettava Microsoft Officen kanssa yhteensopiva ODBC-ohjain joltain kolmannen osapuolen toimittajalta, kuten tietokannan valmistajalta. Pyydä asennusohjeet tietokannan toimittajalta.
   • OLAP-tietokannat eivät edellytä ODBC-ohjaimia. Kun asennat Microsoft Queryn, asennetaan Microsoft SQL Server Analysis Services -ohjelmalla luotujen tietokantojen ohjaimet. Jos haluat muodostaa yhteyden muihin OLAP-tietokantoihin, sinun on asennettava tietolähteen ohjain ja asiakasohjelmisto.

6. Valitse Yhdistä ja anna tiedot, joita tarvitaan yhteyden muodostamisessa tietolähteeseen. Tietokantojen, Excel-työkirjojen ja tekstitiedostojen tapauksessa annettavat tiedot vaihtelevat valitun tietolähteen tyypin mukaan. Ohjelma voi pyytää sinua antamaan kirjautumisnimesi, salasanasi, käyttämäsi tietokannan version, tietokannan sijainnin tai muita tietokantatyyppiin liittyviä tietoja.

   Tärkeää

   • Käytä vahvoja salasanoja, jotka sisältävät isoja ja pieniä kirjaimia sekä numeroita ja muita merkkejä. Heikoissa salasanoissa ei ole näitä kaikkia elementtejä. Vahva salasana: Y6dh!et5. Heikko salasana: Talo27. Salasanassa on hyvä olla vähintään kahdeksan merkkiä. Vielä parempi on käyttää vähintään 14 merkin pituista tunnuslausetta.
   • Salasanan muistaminen on kuitenkin tärkeää. Jos salasana unohtuu, Microsoft ei voi sitä palauttaa. Tallenna muistiin merkitsemäsi salasana sellaiseen turvalliseen paikkaan, ettei se ole samassa paikassa salasanalla suojattavaa sisältöä koskevien tietojen kanssa.

7. Kun olet lisännyt tarvittavat tiedot, palaa takaisin Luo uusi tietolähde -valintaikkunaan valitsemalla OK tai Valmis.

8. Jos tietokannassa on taulukoita ja haluat tietyn taulukon näkyvän automaattisesti ohjatussa kyselyn luomisessa, napsauta vaiheen 4 ruutua ja valitse sitten haluamasi taulukko.

9. Jos et halua kirjoittaa käyttäjänimeäsi ja salasanaasi, kun käytät tietolähdettä, valitse Tallenna käyttäjätunnus ja salasana tietolähteen määritykseen -valintaruutu. Tallennettua salasanaa ei ole salattu. Jos valintaruutu ei ole käytettävissä, pyydä tietokannan järjestelmänvalvojaa tarkistamaan, voidaanko asetus ottaa käyttöön.

   Huomautus

   Vältä kirjautumistietojen tallentamista, kun luot yhteyden tietolähteisiin. Nämä tiedot voidaan tallentaa pelkkänä tekstinä, ja tunkeilija voi käyttää tietoja ja vaarantaa tietolähteen turvallisuuden.

Kun olet suorittanut nämä vaiheet, tietolähteen nimi tulee näkyviin Valitse tietolähde -valintaikkunaan.

Kyselyn määrittäminen ohjatun kyselyn luomisen avulla

Käytä ohjattua kyselyn luomista useimpiin kyselyihin Ohjatun kyselyn luomisen avulla on helppo valita ja yhdistää tietoja tietokannan eri taulukoista ja kentistä. Ohjatun kyselyn luomisen avulla voit valita sisällytettävät taulukot ja kentät. Sisäliitos (kyselytoiminto, joka määrittää, että kahden taulukon rivit yhdistetään identtisten kenttäarvojen perusteella) luodaan automaattisesti, kun ohjattu toiminto tunnistaa perusavainkentän yhdessä taulukossa ja samannimisen kentän toisessa taulukossa.

Ohjatun toiminnon avulla voit myös lajitella tulosjoukon ja käyttää yksinkertaista suodatusta. Ohjatun toiminnon viimeisessä vaiheessa voit valita, palautetaanko tiedot Exceliin, tai voit edelleen tarkentaa kyselyä Microsoft Queryssä. Kun olet luonut kyselyn, voit suorittaa sen joko Excelissä tai Microsoft Queryssä.

Käynnistä ohjattu kyselyn luominen seuraavasti.

1. Valitse Tiedot-välilehdenHae ulkoiset tiedot -ryhmästä Muista lähteistä ja valitse sitten Microsoft Querystä.
2. Varmista Tietolähteen valitseminen -valintaikkunassa, että Käytä ohjattua kyselyn luomista kyselyjen luomiseen ja muokkaamiseen -valintaruutu on valittu.
3. Kaksoisnapsauta tietolähdettä, jota haluat käyttää.
   -tai-
   Valitse haluamasi tietolähde ja valitse sitten OK.

Muuntyyppisten kyselyiden käyttäminen suoraan Microsoft Queryssä Jos haluat luoda monimutkaisemman kyselyn ohjatun toiminnon sallimat kyselyt, voit käyttää kyselyä suoraan Microsoft Queryssä. Microsoft Queryn avulla voit tarkastella ja muuttaa kyselyjä, joita alat luoda ohjatussa kyselyn luomisessa, tai voit luoda uusia kyselyjä käyttämättä ohjattua toimintoa. Voit käyttää suoraan Microsoft Queryä kun haluat luoda kyselyitä, jotka tekevät seuraavat asiat:

• Tiettyjen tietojen valitseminen kentästä Suuressa tietokannassa haluat ehkä valita osan kentän tiedoista ja jättää pois tiedot, joita et tarvitse. Jos esimerkiksi tarvitset kahden tuotteen tiedot kentässä, joka sisältää tietoja useista tuotteista, voit valita ehtojen avulla vain kahden haluamasi tuotteen tiedot.
• Tietojen noutaminen eri ehtojen perusteella aina, kun kysely suoritetaan Jos haluat luoda saman Excel-raportin tai yhteenvedon useista ulkoisten tietojen alueista – esimerkiksi erillisen myyntiraportin kullekin alueelle – voit luoda parametrikyselyn. Kun parametrikysely suoritetaan, ohjelma pyytää arvoa, jota käytetään ehtona tietueita valittaessa. Parametrikysely voi esimerkiksi pyytää sinua syöttämään tietyn alueen, ja voit käyttää tätä kyselyä uudelleen kunkin alueellisen myyntiraportin luomiseen.
• Tietojen yhdistäminen eri tavoilla Ohjatun kyselyn luomisen luomat sisäliitokset ovat yleisimmin kyselyjä luotaessa käytettävä liitostyyppi. Joskus on kuitenkin tarpeen käyttää erityyppistä liitosta. Jos käytössä on esimerkiksi tuotemyyntitietojen taulukko ja asiakastietojen taulukko, sisäliitos (ohjatun kyselyn luomisen luoma tyyppi) estää sellaisten asiakkaiden asiakastietueiden noutamisen, jotka eivät ole tehneet ostoja. Microsoft Queryn avulla voit liittää nämä taulukot siten, että kaikki asiakastietueet ja ostoja tehneiden asiakkaiden myyntitiedot noudetaan.

Käynnistä Microsoft Query seuraavasti.

1. Valitse Tiedot-välilehdenHae ulkoiset tiedot -ryhmästä Muista lähteistä ja valitse sitten Microsoft Querystä.
2. Varmista Valitse tietolähde -valintaikkunassa, että Käytä ohjattua kyselyn luomista kyselyn luontiin ja muokkaamiseen -valintaruutua ei ole valittu.
3. Kaksoisnapsauta tietolähdettä, jota haluat käyttää.
   -tai-
   Valitse haluamasi tietolähde ja valitse sitten OK.

Kyselyjen uudelleenkäyttö ja jakaminen Sekä ohjatussa kyselyn luomisessa että Microsoft Queryssä voit tallentaa kyselyt .dqy-tiedostona, jota voit muokata, käyttää uudelleen ja jakaa. Excel voi avata .dqy-tiedostoja suoraan, minkä ansiosta sinä tai muut käyttäjät voitte luoda ulkoisia tietoalueita samasta kyselystä.

Tallennetun kyselyn avaaminen Excelissä:

1. Valitse Tiedot-välilehdenHae ulkoiset tiedot -ryhmästä Muista lähteistä ja valitse sitten Microsoft Querystä. Näyttöön tulee Valitse tietolähde -valintaikkuna.
2. Valitse Valitse tietolähde -valintaikkunassa Kyselyt-välilehti .
3. Kaksoisnapsauta tallennettua kyselyä, jonka haluat avata. Kysely näkyy Microsoft Queryssä.

Jos haluat avata tallennetun kyselyn ja Microsoft Query on jo avattuna, napsauta Microsoft Query File -valikkoa ja valitse sitten Avaa.

Jos kaksoisnapsautat .dqy-tiedostoa, Excel avautuu, suorittaa kyselyn ja lisää sitten tulokset uuteen laskentataulukkoon.

Jos haluat jakaa ulkoisiin tietoihin perustuvan Excel-yhteenvedon tai -raportin, voit antaa muille käyttäjille työkirjan, joka sisältää ulkoisen tietoalueen, tai voit luoda työkirjan. Mallin avulla voit tallentaa yhteenvedon tai raportin tallentamatta ulkoisia tietoja, jolloin tiedosto on pienempi. Ulkoiset tiedot noudetaan, kun käyttäjä avaa raporttimallin.

Tietojen käyttäminen Excelissä

Kun olet luonut kyselyn joko ohjatussa kyselyn luomisessa tai Microsoft Queryssä, voit palauttaa tiedot Excel-laskentataulukkoon. Tiedot muunnetaan ulkoiseksi tietoalueeksi tai Pivot-taulukkoraportiksi, jota voit muotoilla ja päivittää.

Haettujen tietojen muotoileminen Excelissä voit käyttää kaavioiden ja automaattisten välisummien kaltaisia työkaluja Microsoft Queryn avulla noudettujen tietojen esittämiseen ja yhteenvetojen tekemiseen. Voit muotoilla tietoja, ja ne säilyvät, kun päivität ulkoisia tietoja. Voit käyttää omia sarakeotsikoita kenttien nimien asemesta ja lisätä rivinumerot automaattisesti.

Excel voi automaattisesti muotoilla alueen loppuun kirjoitetut uudet tiedot vastaamaan edeltäviä rivejä. Excel voi myös kopioida automaattisesti edellisillä riveillä toistuneet kaavat ja laajentaa ne lisäriveille.

Voit ottaa tämän asetuksen käyttöön (tai poistaa sen käytöstä) milloin tahansa:

1. Valitse Tiedoston>asetukset>Lisäasetukset.
2. Valitse Muokkausasetukset-osastaLaajenna tietoalueen muotoiluja ja kaavoja -valintaruutu. Jos haluat poistaa tietoalueen automaattisen muotoilun uudelleen käytöstä, poista tämän valintaruudun valinta.

Ulkoisten tietojen päivittäminen Kun päivität ulkoisia tietoja, suoritat kyselyn hakeaksesi uudet tai muuttuneet tiedot, jotka vastaavat määrityksiäsi. Voit päivittää kyselyn sekä Microsoft Queryssä että Excelissä. Excelissä on useita tapoja päivittää kyselyjä, kuten tiedot päivittyvät aina, kun työkirja avataan, ja ne päivittyvät automaattisesti tietyin väliajoin. Voit jatkaa työskentelyä Excelissä, kun tietoja päivitetään, ja voit tarkistaa tilan tietojen päivittämisen aikana. Lisätietoja on artikkelissa Ulkoisen tietoyhteyden päivittäminen Excelissä.

Sivun alkuun