Ulkoisten tietojen noutaminen Microsoft Queryn avulla

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

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

Microsoft Officessa on ohjaimia, 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

• Paradoksi

• Tekstitiedostotietokannat

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

Tietojen valitseminen tietokannasta     Voit noutaa tietoja tietokannasta luomalla kyselyn, joka on kysymys 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 sinulla on ulkoisia tietoja Excel-työkirjassa, voit aina tietokannan muuttuessa päivitä tietoja analyysin päivittämiseksi ilman, että sinun tarvitsee 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 kyseisestä tietokannasta tarvitsematta kirjoittaa kaikkia yhteystietoja uudelleen. Microsoft Query käyttää tietolähdettä yhteyden muodostamiseen ulkoiseen tietokantaan ja käytettävissä olevien tietojen näyttämiseen. Kun olet luonut kyselyn ja palauttanut tiedot Exceliin, Microsoft Query tarjoaa Excel-työkirjaan sekä kyselyn että tietolähteen tiedot, jotta voit muodostaa yhteyden tietokantaan uudelleen, kun haluat päivittää tiedot.

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

Mikä on tietolähde?     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 annat sitten tietokannan tai palvelimen nimen ja sijainnin, tietokannan tyypin sekä kirjautumis- ja salasanatietosi. 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älilehdenNouda 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ämä valikko ei näy oletusarvoisesti.  Voit ottaa sen käyttöön siirtymällä Kohtaan Tiedosto, Asetukset, Tiedot ja ottamalla käyttöön Näytä vanhat tietojen tuontitoiminnot -osassa.

2. Toimi seuraavasti:

   • Jos haluat määrittää tietolähteen tietokantaan, tekstitiedostoon tai Excel-työkirjaan, valitse Tietokannat-välilehti .

   • Jos haluat määrittää OLAP-kuution tietolähteen, valitse 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.

   Näyttöön tulee Luo uusi tietolähde -valintaikkuna.

4. Kirjoita vaiheessa 1 nimi tietolähteen tunnistamiseksi.

5. Napsauta vaiheessa 2 tietolähteenä käytettävän tietokantatyypin ohjainta.

   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 toimittajalta, kuten tietokannan valmistajalta. Pyydä asennusohjeet tietokannan toimittajalta.

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

6. Valitse Yhdistä ja anna sitten tiedot, joita tarvitaan yhteyden muodostamiseen tietolähteeseen. Tietokannoissa, Excel-työkirjoissa ja tekstitiedostoissa antamasi tiedot määräytyvät valitsemasi tietolähteen tyypin mukaan. Sinua saatetaan pyytää antamaan kirjautumisnimi, salasana, käyttämäsi tietokannan versio, tietokannan sijainti tai muita tietokannan tyyppiin 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 kirjoittanut tarvittavat tiedot, palaa 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 kirjautumisnimeäsi ja salasanaasi tietolähdettä käytettäessä, valitse Tallenna käyttäjätunnus ja salasana tietolähteen määrityksessä -valintaruutu. Tallennettua salasanaa ei salata. Jos valintaruutu ei ole käytettävissä, ota yhteyttä tietokannan järjestelmänvalvojaan ja selvitä, voidaanko tämä asetus 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 näkyy Valitse tietolähde -valintaikkunassa.

Ohjatun kyselyn luomisen käyttäminen useimmissa kyselyissä     Ohjattu kyselyn luominen helpottaa tietokannan eri taulukoiden ja kenttien tietojen valitsemista ja yhdistämistä. Ohjatun kyselyn luomisen avulla voit valita taulukot ja kentät, jotka haluat sisällyttää. 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.

Ohjatun toiminnon avulla voit myös lajitella tulosjoukon ja tehdä yksinkertaisen suodatuksen. Ohjatun toiminnon viimeisessä vaiheessa voit palauttaa tiedot Exceliin tai tarkentaa kyselyä Microsoft Queryssä. Kun olet luonut kyselyn, voit suorittaa sen joko Excelissä tai Microsoft Queryssä.

Voit käynnistää ohjatun kyselyn luomisen seuraavasti.

1. Valitse Tiedot-välilehdenNouda 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 tai muokkaamiseen -valintaruutu on valittuna.

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

   -tai-

   Napsauta tietolähdettä, jota haluat käyttää, ja valitse sitten OK.

Työskentele suoraan Microsoft Queryssä muuntyyppisissä kyselyissä     Jos haluat luoda monimutkaisemman kyselyn kuin ohjattu kyselyn luominen sallii, voit työskennellä suoraan Microsoft Queryssä. Microsoft Queryn avulla voit tarkastella ja muuttaa kyselyjä, jotka alat luoda ohjatussa kyselyn luonnissa, tai voit luoda uusia kyselyjä ilman ohjattua toimintoa. Työskentele suoraan Microsoft Queryssä, kun haluat luoda kyselyjä, jotka toimivat seuraavasti:

• Tiettyjen tietojen valitseminen 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 valita ehto tietoja vain kahdelle haluamallesi tuotteelle.

• Tietojen noutaminen eri ehtojen perusteella aina, kun suoritat kyselyn     Jos haluat luoda saman Excel-raportin tai yhteenvedon useille samoissa ulkoisissa tiedoissa oleville alueille , kuten kullekin alueelle erillisen myyntiraportin, voit luoda parametrikysely. Kun suoritat parametrikyselyn, sinua pyydetään käyttämään ehtona arvoa, kun kysely valitsee tietueet. Esimerkiksi parametrikysely saattaa pyytää sinua antamaan tietyn alueen, ja voit luoda kunkin alueellisen myyntiraportin käyttämällä tätä kyselyä uudelleen.

• Tietojen yhdistäminen eri tavoin     Ohjatun kyselyn luomisen luomat sisäliitokset ovat yleisin liitostyyppi, jota käytetään kyselyjen luonnissa. Joskus haluat kuitenkin käyttää toisenlaista liitosta. Jos sinulla on esimerkiksi tuotemyyntitietojen taulukko ja asiakastietoja sisältävä taulukko, sisäliitos (ohjatun kyselyn luomisen luoma tyyppi) estää asiakastietueiden noutamisen asiakkailta, jotka eivät ole tehneet ostosta. Microsoft Queryn avulla voit liittää nämä taulukot niin, että kaikki asiakastietueet noudetaan, sekä ostosten tehneiden asiakkaiden myyntitiedot.

Käynnistä Microsoft Query seuraavasti.

1. Valitse Tiedot-välilehdenNouda 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 tai muokkaamiseen -valintaruutu ei ole valittuna.

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

   -tai-

   Napsauta tietolähdettä, jota haluat käyttää, 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, jolloin sinä tai muut käyttäjät voitte luoda samasta kyselystä lisää ulkoisia tietoalueita.

Tallennetun kyselyn avaaminen Excelistä:

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

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 auki, 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 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. Tiedoista tulee ulkoinen tietoalue tai Pivot-taulukkoraportti, joita voit muotoilla ja päivittää.

Noudetun tiedon muotoilu     Excelissä voit käyttää työkaluja, kuten kaavioita tai automaattisia välisummia, Microsoft Queryn noutamien tietojen esittämiseen ja yhteenvedon esittämiseen. Voit muotoilla tietoja, ja muotoilu säilyy, kun päivität ulkoiset tiedot. Voit käyttää omia sarakeotsikoita kenttien nimien sijaan ja lisätä rivinumerot automaattisesti.

Excel voi muotoilla alueen loppuun kirjoitettavat uudet tiedot automaattisesti vastaamaan edellisiä rivejä. Excel voi myös kopioida automaattisesti kaavoja, jotka on toistettu edellisillä riveillä, ja laajentaa ne lisäriveille.

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

1. Valitse Tiedosto > Asetukset > Lisäasetukset.

2. Valitse Muokkausasetukset-osassaLaajenna tietoaluemuotoja ja kaavoja - tarkistus. 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 ulkoisia tietoja, suoritat kyselyn noutaaksesi kaikki uudet tai muutetut tiedot, jotka vastaavat määrityksiäsi. Voit päivittää kyselyn sekä Microsoft Queryssä että Excelissä. Excelissä on useita vaihtoehtoja kyselyjen päivittämiseen, kuten tietojen päivittämiseen aina, kun avaat työkirjan ja päivittämiseen automaattisesti säännöllisin väliajoin. Voit jatkaa työskentelyä Excelissä, kun tietoja päivitetään, ja voit myös tarkistaa tilan, kun tietoja päivitetään. Lisätietoja on artikkelissa Ulkoisen tietoyhteyden päivittäminen Excelissä.

Sivun alkuun