Kysely (Query)

Päivitetty 24.3.2016

Excel 2016 sisältää datojen hakuun ja mutoiluun tarkoitetun Query (Kysely) -työkalun. (Excel 2013: voit ladata vastaavan PowerQuery-työkalun osoitteesta https://www.microsoft.com/fi-FI/download/details.aspx?id=39379). Seuraavat ohjeet olen kirjoittanut Excel 2016 mukaisesti.

Yhteyden muodostaminen

Valittuani Data (Tiedot) -välilehdeltä New Query, voin muodostaa yhteyden muiden muassa

  • Excel-taulukoihin
  • Tekstitiedostoihin
  • Nettisivuilla sijaitseviin taulukoihin
  • Relaatiotietokantoihin.

Yhteyden muodostumisen jälkeen voin valita tuotavat tiedot. Esimerkiksi seuraavassa olen valinnut tietokannan taulut Sales_SalesOrderDetail ja Sales_SalesOrderHeader. Parhaillaan näen esikatselukuvan Sales_SalesOrderHeader taulun sisällöstä:

kysely1

Tuotavien tietojen valitsemisen jälkeen valitsen Navigator-ikkunasta Load, Load To tai Edit:

Valitsen Load, jos haluan ladata valitun datan. Jos valitsin vain yhden taulukon, niin taulukko ladataan Exceliin. Jos valitsin useita taulukoita, niin tiedot ladataan tietomalliin.

Valitsen Load To, jos haluan määrittää mihin data ladataan. Seuraavassa olen valinnut tiedot ladattavaksi tietomalliin:

kysely2

Tietomalli

Excel-työkirjassa on kaksi merkittävää rajoitetta:

  • Datan rivimäärä voi olla korkeintaan 1 048 576.
  • Työkirja ei tue relaatiotietokantojen mukaista taulujen linkittämistä toisiinsa (esimerkiksi tilaustietoja sisältävään tauluun on tallennettu tilaajasta ainoastaan asiakasnumero ja muut asiakastiedot löytyvät asiaskastietoja sisältävästä taulusta asiakasnumeron perusteella).

Excel 2016 ratkaisee rajoitteet niin kutsutun tietomallin avulla (myös Excel 2013 osaa tietomallin erikseen käyttöönotettavan lisäosan avulla). Voin tallentaa isot datat ja toisiinsa yhdistetyt taulut tietomalliin, joka on eri asia kuin Excelin perinteinen työkirja. Tietomalli kuitenkin tallentuu Excel-työkirjan mukana. Pääsen katsomaan tietomallia Power Pivot -välilehden Manage -toiminnolla tai Data-välilehden Manage Data Model -toiminnolla.

Datan muotoilu

Jos data vaatii muotoilua, niin valitsen Navigator-ikkunasta Edit. Edit vie Kyselyeditoriin (Query Editor), jossa voin muiden muassa

  • Poistaa dataan kuulumattomia rivejä ja sarakkeita.
  • Poistaa kaksoiskappaleita.
  • Jakaa sarakkeiden tietoja useampaan sarakkeeseeen joko erotinmerkkien tai tiedon pituuden perusteella.
  • Vaihtaa sarakkeen tietotyyppiä.
  • Korvata tietoja (esimerkiksi desimaalipisteet desimaalipilkuilla).
  • Täydentää tietoja (jos esimerkiksi vuosiluku on vain ensimmäisellä kyseistä vuotta käsittelevällä rivillä, niin voin nopeasti täydentää vuosiluvut kaikille riveille).
  • Lisätä laskettuja sarakkeita.
  • Vaihtaa rivit sarakkeiksi (transponointi).
  • Yhdistellä eri tietoläheistä haettuja tietoja (Merge, Append).

Kyselyeditorissa muotoillun datan lataan lopuksi Excel-taulukkoon tai Excelin tietomalliin kyselyeditorin Home-välilehden Close & Load tai Close & Load To -toiminnolla.

Datan myöhempi päivittäminen ja muotoilu

Excel-työkirjan kyselyt (Query) näkyvät oikean reunan Workbook Queries -paneelissa. Jos Workbook Queries -paneeli ei ole näkyvillä, niin valitse Data-välilehdeltä Show Queries. Voin päivittää datan napsauttamalla hiiren kakkospainiketta kyselyn nimen päällä ja valitsemalla Refresh. Tiedot noudetaan alkuperäisestä tietolähteestä, jos tietolähde on edelleen olemassa samannimisenä ja samassa paikassa. Samalla toteutetaan kyselyeditorissa aiemmin määritellyt datan muotoilutoimet.

Pääsen kyselyeditoriin napsauttamalla hiiren kakkospainiketta kyselyn nimen päällä ja valitsemalla Edit.

kysely3

Kyselyeditorissa on tallella kaikki datalle tekemäni muotoilut.

Näen muotoilut vaiheina (Steps) kyselyeditorin oikeassa reunassa. Voin poistaa vaiheita ja muotoilla vaiheita, joiden vieressä on hammasratas-kuvake.

Jos valitsen tietyn vaiheen, niin näen kyselyeditorissa datan sellaisena kuin se oli kyseisen vaiheen jälkeen.

Mahdollisten muutosten jälkeen poistun kyselyeditorista valitsemalla Home-välilehdeltä Close & Load.

Yhteenveto

Kysely (Query) toimii välittäjänä lähdetiedon ja Excelissä sijaitsevan datan välillä. Päivityksen (Refresh) yhteydessä Kysely (Query) hoitaa tarvittavat muotoilut automaattisesti.

Lisätietoa

Tämän oppii vain tekemällä itse. Harjoittele Kyselyn (Query) käyttöä hakemalla jääkiekon SM-liigan otteluohjelma Exceliin.

Opi kaikki tarpeellinen Heidi Enhon Kyselyoppaasta.

Mainokset