Tietojen tuominen ja muotoileminen Excel for Macissa (Power Query)

Tuo tietolähteet

Voit tuoda tietoja Exceliin käyttämällä Power Querya useista eri tietolähteistä: Excel-työkirja, teksti/CSV, XML, JSON, SQL Server Database, SharePoint Online -luettelo, OData, tyhjä taulukko ja tyhjä kysely.

1. Valitse Tietojen>noutaminen.

   

2. Valitse haluamasi tietolähde valitsemalla Nouda tiedot (Power Query).

3. Valitse Valitse tietolähde -valintaikkunassa jokin käytettävissä olevista tietolähteistä.

   

4. Muodosta yhteys tietolähteeseen. Lisätietoja yhteyden muodostamisesta kuhunkin tietolähteeseen on artikkelissa Tietojen tuominen tietolähteistä.

5. Valitse tuotavat tiedot.

6. Lataa tiedot napsauttamalla Lataa-painiketta .

Tulos

Tuodut tiedot näkyvät uudessa taulukossa.

Seuraavat vaiheet

Jos haluat muotoilla ja muuntaa tietoja Power Query -editorin avulla, valitse Muunna tiedot. Lisätietoja on artikkelissaTietojen muotoileminen Power Query -editorin avulla.

Tietojen muotoileminen Power Query -editorin avulla

Menettely

1. Valitse Tietojen>noutaminen (Power Query).

2. Avaa Kyselyeditori valitsemalla Käynnistä Power Query -editori.

   

   Vihje

   Voit käyttää Kyselyeditoria myös valitsemalla Nouda tiedot (Power Query), valitsemalla tietolähteen ja valitsemalla sitten Seuraava.

3. Muotoile ja muunna tietoja käyttämällä Kyselyeditoria samalla tavalla kuin Excel for Windowsissa.

   

   Lisätietoja on Power Queryä käsittelevissä Excel-ohjeissa.

4. Kun olet valmis, valitse Aloitus>Sulje & Lataa.

Tulos

Juuri tuodut tiedot näkyvät uudessa taulukossa.

Päivitä tietolähteet

Voit päivittää seuraavat tietolähteet: SharePoint-tiedostot, SharePoint-luettelot, SharePoint-kansiot, OData-, teksti-/CSV-tiedostot, Excel-työkirjat (.xlsx), XML- ja JSON-tiedostot, paikalliset taulukot ja alueet, Microsoft SQL Server -tietokanta sekä kansiot.

Päivitä ensimmäisen kerran

Kun yrität päivittää tiedostopohjaisia tietolähteitä työkirjakyselyissä ensimmäistä kertaa, sinun on ehkä päivitettävä tiedostopolku.

1. Valitse Tiedot, Nouda tiedot -kohdanvieressä oleva nuoli ja sitten Tietolähdeasetukset. Tietolähteen asetukset -valintaikkuna tulee näkyviin.
2. Valitse yhteys ja valitse sitten Muuta tiedostopolkua.
3. Valitse Tiedostopolku-valintaikkunassa uusi sijainti ja valitse sitten Nouda tiedot.
4. Valitse Sulje.

Päivitä seuraavat ajat

Jos haluat päivittää:

• Kaikki työkirjan tietolähteet, valitse Tietojen>päivittäminen.
• Tietyn tietolähteen, napsauta taulukon kyselytaulukkoa hiiren kakkospainikkeella ja valitse sitten Päivitä.
• Pivot-taulukko, valitse solu Pivot-taulukosta ja valitse sitten Pivot-taulukko Analysoi>päivitystiedot.

Anna ja tyhjennä tunnistetiedot

Kun käytät ensimmäistä kertaa SharePointia, SQL Serveriä, ODataa tai muuta käyttöoikeuksia edellyttävää tietolähdettä, sinun on annettava asianmukaiset tunnistetiedot. Voit myös halutessasi tyhjentää tunnistetiedot, jotta voit kirjoittaa uusia tunnistetietoja.

Anna tunnistetiedot

Kun päivität kyselyn ensimmäistä kertaa, sinua saatetaan pyytää kirjautumaan sisään. Valitse todentamismenetelmä ja määritä kirjautumisen tunnistetiedot, jotta voit muodostaa yhteyden tietolähteeseen ja jatkaa päivittämistä.

Jos kirjautuminen edellyttää kirjautumista, näkyviin tulee Anna tunnistetiedot -valintaikkuna.

Esimerkki:

• SharePoint-tunnistetiedot:

  

• SQL Server -tunnistetiedot:

  

Tyhjennä tunnistetiedot

1. Valitse Tietojen>noutamisen>tietolähteen asetukset.
2. Valitse Tietolähteen asetukset -valintaikkunassa haluamasi yhteys.
3. Valitse alareunasta Tyhjennä käyttöoikeudet.
4. Vahvista, että haluat tehdä tämän, ja valitse sitten Poista.

Power Query VBA -koodin laatiminen ja siirtäminen

Vaikka kirjoittaminen Power Query -editorissa ei ole käytettävissä Excel for Macissa, VBA tukee Power Queryn kirjoittamista. Tiedoston VBA-koodimoduulin siirtäminen Excel for Windowsista Excel for Maciin on kaksivaiheinen prosessi. Tämän osan lopussa on esimerkkiohjelma.

Vaihe 1: Excel for Windows

1. Excel Windowsissa voit kehittää kyselyitä VBA:n avulla. VBA-koodi, joka käyttää seuraavia entiteettejä Excelin objektimallissa, toimii myös Excel for Mac: Kyselyt-objekti, WorkbookQuery-objekti, Työkirja.Kyselyt-ominaisuus. Lisätietoja on Excelin VBA-viitteessä.

2. Varmista Excelissä, että Visual Basic Editor on auki painamalla ALT+F11.

3. Napsauta moduulia hiiren kakkospainikkeella ja valitse sitten Vie tiedosto. Vie-valintaikkuna tulee näkyviin.

4. Kirjoita tiedostonimi, varmista, että tiedostotunniste on .bas, ja valitse sitten Tallenna.

5. Lataa VBA-tiedosto online-palveluun, jotta tiedosto on käytettävissä Macissa.

   Voit käyttää Microsoft OneDrivea. Lisätietoja on artikkelissa Tiedostojen synkronointi OneDriven avulla Mac OS X:ssä.

Vaihe 2: Excel for Mac

1. Lataa VBA-tiedosto paikalliseen tiedostoon, VBA-tiedosto, jonka tallensit vaiheessa 1: Excel for Windows ja latasit verkkopalveluun.
2. Valitse Excel for Mac -sovelluksessa Työkalut-makro>>Visual Basic Editor. Näyttöön tulee Visual Basic Editor -ikkuna.
3. Napsauta objektia hiiren kakkospainikkeella Projekti-ikkunassa ja valitse sitten Tuo tiedosto. Näyttöön tulee Tuo tiedosto -valintaikkuna.
4. Etsi VBA-tiedosto ja valitse sitten Avaa.

Mallikoodi

Tässä on peruskoodi, jota voit muokata ja käyttää. Tämä on esimerkkikysely, joka luo luettelon, jossa on arvot 1–100.

Sub CreateSampleList()
  ActiveWorkbook.Queries.Add Name:="SampleList", Formula:= _
    "let" & vbCr & vbLf & _
      "Source = {1..100}," & vbCr & vbLf & _
      "ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & vbCr & vbLf & _
      "RenamedColumns = Table.RenameColumns(ConvertedToTable,{{""Column1"", ""ListValues""}})" & vbCr & vbLf & _
    "in" & vbCr & vbLf & _
      "RenamedColumns"
  ActiveWorkbook.Worksheets.Add
  With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SampleList;Extended Properties=""""" _
    , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [SampleList]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "SampleList"
    .Refresh BackgroundQuery:=False
  End With
End Sub

TXT- tai CSV-tiedostojen tuominen ja päivittäminen

1. Avaa Excel-työkirja.

2. Jos saat suojausvaroituksen ulkoisten tietoyhteyksien poistamisesta käytöstä, valitse Ota sisältö käyttöön.

3. Jos Myönnä tiedostokäyttöoikeus -valintaikkuna tulee näkyviin, valitse Valitse ja valitse sitten Myönnä käyttöoikeus tietolähdetiedostot sisältävään ylimmän tason kansioon.

4. Valitse tiedot>tekstistä (vanha). Finder-valintaikkuna tulee näkyviin.

5. Etsi .txt tai .csv tiedosto ja valitse Avaa.Ohjattu tekstin tuominen tulee näkyviin.

   Vinkki Vahvista valintasi tarkistamalla valittujen tietojen esikatselu -ruutu toistuvasti.

6. Toimi ensimmäisellä sivulla seuraavasti:
   Tiedostotyyppi Valitse tekstitiedoston tyyppi valitsemalla Erotinmerkeillä eroteltu tai Kiinteä leveys.

   RivinumeroAloita tuonti riviltä-kohdassa valitse rivinumero ensimmäisen tuotavan tietorivin määrittämiseksi.

   Merkistö Valitse Tiedoston alkuperä -kohdassa tekstitiedostossa käytettävä merkistö. Useimmissa tapauksissa voit jättää tämän asetuksen oletusasetukselle.

7. Toimi toisella sivulla seuraavasti:

   Erotettu

   Jos valitsit ensimmäisellä sivulla Erotinmerkit-kohdassa, valitse erotinmerkki tai kirjoita erotinmerkki, jota ei ole luettelossa, käyttämällä Muut-valintaruutua.

   Valitse Käsittele peräkkäisiä erottimia yhtenä, jos tiedot sisältävät useamman kuin yhden merkin erottimen tietokenttien välissä tai jos tiedoissa on useita mukautettuja erottimia.

   Valitse Tekstierotin-kohdassa merkki, joka sisältää arvot tekstitiedostossa, joka on useimmiten lainausmerkki (").
   Kiinteä leveys

   Jos valitsit ensimmäisellä sivulla kiinteän leveyden , luo, poista tai siirrä katkaisuviiva noudattamalla Valittujen tietojen esikatselu -ruudun ohjeita.

8. Toimi kolmannella sivulla seuraavasti:

   Valitse Esikatselu valittuja tietoja -kohdassa olevasta sarakkeesta se ja muuta se sitten halutessasi eri sarakemuotoon. Voit määrittää päivämäärän muodon edelleen ja valita Lisäasetukset , jos haluat muuttaa numeeristen tietojen asetuksia. Voit myös muuntaa tiedot tuonnin jälkeen.

   Valitse Valmis. Näyttöön tulee Tuo tiedot -valintaikkuna.

9. Valitse, haluatko lisätä tiedot nykyiseen laskentataulukkoon, uuteen laskentataulukkoon vai Pivot-taulukkoon.

10. Valitse OK.
    Varmista, että yhteys toimii, kirjoittamalla joitakin tietoja ja valitsemalla sitten Yhteyksien>päivitys.

Yhteyden muodostaminen SQL-tietokantaan (ODBC)

1. Valitse tiedot>SQL Server ODBC:stä. Näkyviin tulee Muodosta yhteys SQL Server ODBC -tietolähteeseen -valintataulu.

   

2. Kirjoita palvelin Palvelimen nimi -ruutuun ja halutessasi voit kirjoittaa tietokannan Tietokannan nimi -ruutuun.

   Hanki nämä tiedot tietokannan järjestelmänvalvojalta.

3. Valitse Todennus-kohdassa menetelmä luettelosta: Käyttäjänimi/Salasana, Kerberos tai NTLM.

4. Kirjoita tunnistetiedot Käyttäjänimi- ja Salasana-ruutuihin .

5. Valitse Yhdistä. Etsintä-valintaikkuna tulee näkyviin.

6. Siirry vasemmassa ruudussa haluamaasi taulukkoon ja valitse se.

7. Vahvista oikeanpuoleisessa ruudussa oleva SQL-lauseke. Voit muuttaa SQL-lauseketta haluamallasi tavalla.

8. Jos haluat esikatsella tietoja, valitse Suorita.

9. Kun olet valmis, valitse Palauta tiedot. Näyttöön tulee Tuo tiedot -valintaikkuna.

   

10. Valitse, haluatko lisätä tiedot nykyiseen laskentataulukkoon, uuteen laskentataulukkoon vai Pivot-taulukkoon.

11. Voit määrittää yhteyden ominaisuudet valitsemalla Ominaisuudet-valintaikkunan Käyttö- ja Määritelmä-välilehdissä Ominaisuudet. Kun olet tuonut tiedot, voit myös valita Tietoyhteydet> ja valita sitten Yhteyden ominaisuudet -valintaikkunassa Ominaisuudet.

12. Valitse OK.

13. Varmista, että yhteys toimii, kirjoittamalla joitakin tietoja ja valitsemalla sitten Päivitä>kaikki.

Yhteyden muodostaminen toiseen tietokantaan (Microsoft Query)

Jos haluat käyttää ulkoista lähdettä, joka ei ole SQL-tietokanta (esimerkiksi FileMaker Pro), voit käyttää Mac-tietokoneeseen asennettua Open Database Connectivity (ODBC) -ohjainta. Tietoja ohjaimista on tässä sivustossa. Kun tietolähteen ohjain on asennettu, toimi seuraavasti:

1. Valitse tiedot>tietokannasta (Microsoft Query).

2. Lisää tietokannan tietolähde ja valitse sittenOK.

3. Anna SQL Server -tunnistetietokehotteeseen todennusmenetelmä, käyttäjänimi ja salasana.

   

4. Tuo tietokannat näkyviin valitsemalla vasemmassa laidassa palvelimen nimen vieressä oleva nuoli.

5. Valitse haluamasi tietokannan vieressä oleva nuoli.

6. Valitse haluamasi taulukko.

7. Jos haluat esikatsella tietoja, valitse Suorita.

8. Kun olet valmis, valitse Palauta tiedot.

9. Valitse Tuo tietoja -valintaikkunassa, haluatko sijoittaa tiedot nykyiseen laskentataulukkoon, uuteen laskentataulukkoon vai Pivot-taulukkoon.

10. Valitse OK.

11. Varmista, että yhteys toimii, kirjoittamalla joitakin tietoja ja valitsemalla sitten Päivitä>kaikki.

Tyhjennä kirjautumisen tunnistetiedot

Jos käyttöoikeutesi eivät toimi, sinun on ehkä tyhjennettävä ne ensin ja kirjauduttava sitten sisään.

1. Valitse Tietoyhteydet>. Näyttöön tulee Työkirjan yhteydet -valintaikkuna.

2. Valitse luettelosta haluamasi yhteys ja valitse sitten Tyhjennä käyttöoikeudet.