Ulkoisten tietojen noutaminen Microsoft Queryn avulla

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

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

Tietokantojen tyypit, joita voit käyttää     Voit noutaa tietoja useista tietokannoista, kuten Accessista, Microsoft Office Microsoft SQL Server olap-Microsoft SQL Server tietokannoista. Voit myös hakea tietoja Excel työkirjoista ja tekstitiedostoista.

Microsoft Office sisältää ohjaimet, joiden avulla voit noutaa tietoja seuraavista tietolähteistä:

  • Microsoft SQL Server Analysis Services (OLAP-toimittaja )

  • Microsoft Office Access

  • dBASE

  • Microsoft FoxPro

  • Microsoft Office Excel

  • Oracle

  • Paradox

  • Tekstitiedostotietokannat

Voit käyttää myös muiden valmistajien ODBC-ohjaimia tai tietolähteen ohjaimia tietojen hakemiseen tietolähteistä, joita ei ole lueteltu tässä, mukaan lukien muuntyyppiset OLAP-tietokannat. Lisätietoja odbc-ohjaimen tai tietolähteen ohjaimen asentamisesta, jota ei ole mainittu tässä, on tietokannan ohjeissa tai ottamalla yhteyttä tietokannan toimittajaan.

Tietojen valitseminen tietokannasta     Voit noutaa tietoja tietokannasta luomalla kyselyn, joka on ulkoiseen tietokantaan tallennetuista tiedoista kysyttävä kysymys. Jos tiedot on esimerkiksi tallennettu Access-tietokantaan, haluat ehkä tietää tietyn tuotteen myyntiluvut alueittain. Voit noutaa osan tiedoista valitsemalla vain analysoitavaa tuotetta ja aluetta koskevat tiedot.

Microsoft Queryn avulla voit valita haluamasi tietosarakkeet ja tuoda vain nämä tiedot Excel.

Laskentataulukon päivittäminen yhdellä operaatiolla     Kun Excel työkirjassa on ulkoisia tietoja, päivitä päivittää tietoja tarvitsematta luoda yhteenvetoraportteja ja kaavioita uudelleen. Voit esimerkiksi luoda kuukausittaisen myyntiyhteenvedon ja päivittää sen joka kuukausi, kun uudet myyntiluvut tulevat.

Miten Microsoft Query käyttää tietolähteitä     Kun olet määrittänyt tietolähteen tietylle tietokannalle, voit käyttää sitä aina, kun haluat luoda kyselyn, jonka avulla voit valita ja noutaa tietoja kyseestä tietokannasta tarvitsematta kirjoittaa kaikkia yhteystietoja uudelleen. Microsoft Query muodostaa yhteyden ulkoiseen tietokantaan tietolähteen avulla ja näyttää, mitä tietoja on käytettävissä. Kun olet luonut kyselyn ja palauttanut tiedot Excel, Microsoft Query tarjoaa Excel-työkirjan, jossa on sekä kyselyn että tietolähteen tiedot, jotta voit muodostaa uudelleen yhteyden tietokantaan, kun haluat päivittää tiedot.

Kaavio Queryn tietolähteiden käyttämisestä

Tietojen tuominen Microsoft Queryn avulla     Jos haluat tuoda ulkoisia Excel Microsoft Queryn avulla, noudata näitä perusvaiheita, joista jokainen on kuvattu tarkemmin seuraavissa osissa.

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

Voit määrittää tietolähteen Microsoft Queryn avulla seuraavasti:

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

  2. Toimi seuraavasti:

    • Jos haluat määrittää tietolähteen tietokannalle, tekstitiedostolle tai Excel, valitse Tietokannat-välilehti.

    • Määritä OLAP-kuution tietolähde valitsemalla OLAP-kuutiot-välilehti. Tämä välilehti on käytettävissä vain, jos microsoft Query on Excel.

  3. Kaksoisnapsauta kohtaa <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 tunnistamista varten nimi.

  5. Napsauta vaiheessa 2 ohjainta tietolähteenä käytössäsi olevaa tietokantatyyppiä varten.

    Huomautukset: 

    • Jos Microsoft Queryn mukana asennetut ODBC-ohjaimet eivät tue ulkoista tietokantaa, jota haluat käyttää, sinun on hankittava ja asennettava Microsoft Office-yhteensopiva ODBC-ohjain kolmannen osapuolen valmistajalta, kuten tietokannan valmistajalta. Pyydä asennusohjeet tietokannan toimittajalta.

    • OLAP-tietokannat eivät edellytä ODBC-ohjaimia. Kun asennat Microsoft Queryn, ohjaimia asennetaan tietokantoihin, jotka on luotu Microsoft SQL Server Analysis Services -palvelujen avulla. Jos haluat muodostaa yhteyden muihin OLAP-tietokantoihin, sinun on asennettava tietolähdeohjain ja asiakasohjelmisto.

  6. Valitse Näyttöyhteysja anna sitten tietolähteeseen yhteyden muodostamisessa tarvittavat tiedot. Tietokannoista, Excel työkirjoista ja tekstitiedostoista, tiedot määräytyvät valitsemasi tietolähteen tyypin mukaan. Sinua saatetaan pyytää antamaan kirjautumisnimi, salasana, käytössä olevan tietokannan versio, tietokannan sijainti tai muita tietokantatyyppiä koskevia 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 syöttänut tarvittavat tiedot, palaa Luo uusi tietolähde -valintaikkunaan valitsemalla OKtai Valmis.

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

  9. Jos et halua kirjoittaa kirjautumisnimeäsi ja salasanaasi, kun käytät tietolähdettä, valitse Tallenna käyttäjätunnukseni ja salasanasi tietolähteen määritykseen -valintaruutu. Tallennettua salasanaa ei salata. Jos valintaruutu ei ole käytettävissä, ota yhteyttä tietokannan järjestelmänvalvojaan ja tarkista, voidaanko tämä vaihtoehto ottaa käyttöön.

    Tietoturvahuomautus: 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.

Ohjatun kyselyn luomisen käyttäminen useimmissa kyselyissä     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 kenttien arvojen perusteella) luodaan automaattisesti, kun ohjattu toiminto tunnistaa yhden taulukon perusavainkentän ja samannimisen kentän toisessa taulukossa.

Voit käyttää ohjattua toimintoa myös tulosjoukon lajitteluun ja yksinkertaisiin suodatustoimintoihin. Ohjatun toiminnon viimeisessä vaiheessa voit palauttaa tiedot Excel tai tarkentaa kyselyä Microsoft Queryssä. Kun olet luonut kyselyn, voit suorittaa sen joko Excel Microsoft Queryssä.

Voit käynnistää ohjatun kyselyn luomisen seuraavasti.

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

  2. Varmista Valitse tietolähde -valintaikkunassa, että Luo tai muokkaa kyselyitä ohjatun kyselyn luomisen avulla -valintaruutu on valittuna.

  3. Kaksoisnapsauta tietolähdettä, jota haluat käyttää.

    -tai-

    Valitse tietolähde, jota haluat käyttää, ja valitse sitten OK.

Muuntyyppisten kyselyjen käyttäminen suoraan Microsoft Queryssä     Jos haluat luoda monimutkaisen kyselyn kuin ohjattu kyselyn luominen sallii, voit tehdä sen suoraan Microsoft Queryssä. Microsoft Queryn avulla voit tarkastella ja muuttaa kyselyjä, joita aloitat ohjatussa kyselyn luomisessa, tai voit luoda uusia kyselyjä ilman ohjattua toimintoa. Voit käyttää suoraan Microsoft Queryä, kun haluat luoda kyselyjä, jotka toimivat seuraavasti:

  • Tiettyjen tietojen valinta kentästä     Suuressa tietokannassa haluat ehkä valita joitakin kentän tietoja ja jättää pois tiedot, joita et tarvitse. Jos esimerkiksi tarvitset tietoja kahdesta tuotteesta kentässä, joka sisältää tietoja useista tuotteista, voit ehto vain kahden haluamasi tuotteen tiedot.

  • Tietojen noutaminen eri ehtojen perusteella aina, kun kysely suoritetaan     Jos haluat luoda saman raportin Excel yhteenvedon useille samojen ulkoisten tietojen alueille, kuten erillisen myyntiraportin kullekin alueelle, voit luoda raportin parametrikysely. Kun suoritat parametrikyselyn, sinua pyydetään antamaan ehtona käytettävä arvo, kun kysely valitsee tietueita. Parametrikysely voi esimerkiksi kehottaa sinua antamaan tietyn alueen, ja voit käyttää tätä kyselyä uudelleen kunkin alueellisen myyntiraporttisi luomiseen.

  • Tietojen liittyminen eri tavoilla     Ohjatun kyselyn luomisen luomat sisäliitokset ovat yleisin liitostyyppi, jota käytetään kyselyjen luomisessa. Joskus haluat kuitenkin käyttää erityyppistä liitosta. Jos sinulla on esimerkiksi tuotemyynnin tiedot ja asiakastietojen taulukko, sisäliitos (ohjatun kyselyn luomisen luoma tyyppi) estää asiakastietueiden noutamisen asiakkaille, jotka eivät ole tehneet ostoa. Microsoft Queryn avulla voit liittää nämä taulukot siten, että kaikki asiakastietueet noudetaan, sekä ostoja hankkineet asiakkaat myyntitiedot.

Käynnistä Microsoft Query seuraavasti.

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

  2. Varmista Valitse tietolähde -valintaikkunassa, että Käytä ohjattua kyselyn luomista kyselyjen luomiseen ja muokkaamiseen -valintaruudun valinta on tyhjä.

  3. Kaksoisnapsauta tietolähdettä, jota haluat käyttää.

    -tai-

    Valitse tietolähde, jota haluat käyttää, ja valitse sitten OK.

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

Voit avata tallennetun kyselyn Excel:

  1. Valitse Tiedot-välilehden Hae ulkoiset tiedot -ryhmästä Muista lähteistäja valitse sitten Microsoft Querystä. Valitse tietolähde -valintaikkuna tulee näkyviin.

  2. Valitse Valitse tietolähde -valintaikkunassaKyselyt-välilehti.

  3. Kaksoisnapsauta tallennettua kyselyä, jonka haluat avata. Kysely näkyy Microsoft Queryssä.

Jos haluat avata tallennetun kyselyn ja Microsoft Query on jo avoinna, valitse Microsoft Query -tiedosto -valikko ja valitse sitten Avaa.

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

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

Kun olet luonut kyselyn joko ohjatussa kyselyn luomisessa tai Microsoft Queryssä, voit palauttaa tiedot Excel laskentataulukkoon. Sen jälkeen tiedoista tulee ulkoinen tietoalue tai Pivot-taulukkoraportti, joita voit muotoilla ja päivittää.

Haettujen tietojen muotoilu     In Excel, you can use tools, such as charts or automatic subtotals, to present and to summarize the data retrieved by Microsoft Query. Voit muotoilla tietoja, ja muotoilut säilyvät, kun päivität ulkoiset tiedot. Voit käyttää omia sarakeotsikoita kenttien nimien sijaan ja lisätä rivinumerot automaattisesti.

Excel automaattisesti muotoilla alueen loppuun kirjoit haluamasi uudet tiedot edellisten rivien mukaan. Excel myös kopioida automaattisesti kaavoja, jotka on toistuva edellisillä riveillä, ja laajentaa ne lisäriveille.

Huomautus: Jotta muotoilut ja kaavat voidaan laajentaa alueen uusille riveille, niiden on oltava vähintään kolmessa viidestä edellistä rivistä.

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

  1. Valitse Tiedosto > Asetukset > Lisäasetukset.

    In Excel 2007: Click the Microsoft Office button Office-painikkeen kuva , click Excel Options, and then click the Advanced category.

  2. Valitse Muokkausasetukset-osassa Laajenna tietoalueen muotoiluja ja kaavoja -valinta. Jos haluat poistaa automaattisen tietoalueen muotoilun käytöstä uudelleen, poista tämän valintaruudun valinta.

Ulkoisten tietojen päivittäminen     Kun päivität ulkoiset tiedot, suoritat kyselyn hakeaksesi uudet tai muuttuneet tiedot, jotka vastaavat määrityksiäsi. Voit päivittää kyselyn sekä Microsoft Queryssä että Excel. Excel on useita vaihtoehtoja kyselyjen päivitystä varten. Voit esimerkiksi päivittää tiedot aina, kun avaat työkirjan ja päivität ne automaattisesti aikavälein. Voit jatkaa työskentelyä Excel tietoja päivitetään, ja voit myös tarkistaa tilan tietojen päivityksen aikana. Lisätietoja on kohdassa Ulkoisen tietoyhteyden päivittäminen Excel.

Sivun alkuun

Tarvitsetko lisäohjeita?

Kehitä Office-taitojasi
Tutustu koulutusmateriaaliin
Saat uudet ominaisuudet ensimmäisten joukossa
Liity Office Insider -käyttäjiin

Oliko näistä tiedoista hyötyä?

×