Tietojen tuominen ja muotoileminen Excel for Macissa (Power Query)

Excel for the Mac sisältää Power Query -tekniikan (kutsutaan myös haku- ja muuntotekniikaksi), joka tarjoaa entistä parempia ominaisuuksia tietolähteiden tuomiseen, päivittämiseen ja todentamiseen, Power Query -tietolähteiden hallintaan, tunnistetietojen tyhjentämiseen, tiedostopohjaisten tietolähteiden sijainnin muuttamiseen ja tietojen muotoilemiseen tarpeisiisi sopivaksi taulukoksi. Voit myös luoda Power Query kyselyn VBA:n avulla.

Microsoft 365 Vanha

Huomautus: SQL Server Database -tietolähde voidaan tuoda vain Insiders-beetaversiossa.

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.

Valitse Tiedot > Nouda tiedot.
Valitse haluamasi tietolähde valitsemalla Nouda tiedot (Power Query).
Valitse Valitse tietolähde -valintaikkunassa jokin käytettävissä olevista tietolähteistä.
Muodosta yhteys tietolähteeseen. Lisätietoja yhteyden muodostamisesta kuhunkin tietolähteeseen on artikkelissa Tietojen tuominen tietolähteistä.
Valitse tuotavat tiedot.
Lataa tiedot napsauttamalla Lataa -painiketta.

Tulos

Tuodut tiedot näkyvät uudessa taulukossa.

Kyselyn tyypilliset tulokset

Seuraavat vaiheet

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

Huomautus: Tämä ominaisuus on yleisesti saatavilla Microsoft 365 -tilaajille, jotka käyttävät Excel for Macin versiota 16.69 (23010700) tai uudempaa versiota. Jos olet Microsoft 365-tilaaja, varmista, että käytössäsi on Officen uusin versio.

Toimenpide

Valitse Tiedot > Nouda tiedot (Power Query).
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.
Muotoile ja muunna tietoja käyttämällä Kyselyeditoria samalla tavalla kuin Excel for Windowsissa. Lisätietoja on Power Queryä käsittelevissä Excel-ohjeissa.
Kun olet valmis, valitse Aloitus > Sulje ja lataa.

Tulos

Juuri tuodut tiedot näkyvät uudessa taulukossa.

Kyselyn tyypilliset tulokset

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 sekä Microsoft SQL Server -tietokanta.

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.

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

Päivitä seuraavat ajat

Jos haluat päivittää:

Kaikki työkirjan tietolähteet, valitse Tiedot > Päivitä kaikki.
Tietyn tietolähteen, napsauta taulukon kyselytaulukkoa hiiren kakkospainikkeella ja valitse sitten Päivitä.
Pivot-taulukon, valitse solu Pivot-taulukosta ja valitse sitten Pivot-taulukon analysointi > Päivitä tiedot.

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 sisäänkirjautuminen vaaditaan, näyttöön tulee Anna tunnistetiedot -valintaikkuna.

Esimerkki:

SharePoint-tunnistetiedot:
SQL Server -tunnistetiedot:

Tyhjennä tunnistetiedot

Valitse Tiedot > Nouda tiedot > Tietolähteen asetukset.
Valitse Tietolähteen asetukset -valintaikkunassa haluamasi yhteys.
Valitse alareunassa Tyhjennä käyttöoikeudet.
Vahvista, että haluat tehdä tämän, ja valitse sitten Poista.

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

Excel Windowsissa voit kehittää kyselyitä VBA:n avulla. VBA-koodi, joka käyttää seuraavia entiteettejä Excelin objektimallissa, toimii myös Excel for Macissa: Kyselyt-objekti, WorkbookQuery-objekti, Työkirja.Kyselyt-ominaisuus. Lisätietoja on artikkelissa Excelin VBA-viittaus.
Varmista Excelissä, että Visual Basic Editor on auki painamalla ALT+F11.
Napsauta moduulia hiiren kakkospainikkeella ja valitse sitten Vie tiedosto. Näyttöön tulee Vienti -valintaikkuna.
Kirjoita tiedostonimi, varmista, että tiedostotunniste on .bas, ja valitse sitten Tallenna.
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

Lataa VBA-tiedosto paikalliseen tiedostoon, VBA-tiedosto, jonka tallensit vaiheessa 1: Excel for Windows ja latasit verkkopalveluun.
Valitse Excel for Macissa Työkalut > Makro > Visual Basic Editor. Näyttöön tulee Visual Basic Editor -ikkuna.
Napsauta objektia hiiren kakkospainikkeella Projekti-ikkunassa ja valitse sitten Tuo tiedosto. Näyttöön tulee Tuo tiedosto -valintaikkuna.
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

Avaa Excel-työkirja.
Jos saat suojausvaroituksen ulkoisten tietoyhteyksien poistamisesta käytöstä, valitse Ota sisältö käyttöön.
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.
Valitse Tiedot > Tekstistä (vanha). Näyttöön tulee Finder -valintaikkuna.
Etsi .txt- tai .csv-tiedosto ja valitse Avaa. Ohjattu tekstin tuominen tulee näkyviin.Vinkki Vahvista valintasi tarkistamalla valittujen tietojen esikatselu -ruutu.
Toimi ensimmäisellä sivulla seuraavasti:

Tiedostotyyppi Jos haluat valita tekstitiedoston tyypin, valitse Erotinmerkeillä eroteltu tai Kiinteä leveys.Rivinumero Aloita 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.
Toimi toisella sivulla seuraavasti:Erotinmerkeillä eroteltu Jos valitsit erotinmerkeillä eroteltu ensimmäisellä sivulla Erottimet-kohdassa, valitse erotinmerkki tai kirjoita Muut-kohtaan merkki, joka ei ole luettelossa. 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 , noudata ohjeita, jotta voit luoda, poistaa tai siirtää rivinvaihto valittujen tietojen esikatselu -ruudussa.
Toimi kolmannella sivulla seuraavasti: Valitse Valittujen tietojen esikatselu-kohdan jokaiselle sarakkeelle se ja muuta se sitten halutessasi eri sarakemuotoon. Voit määrittää päivämäärämuodon edelleen ja muuttaa numeerisia tietoasetuksia valitsemalla Lisäasetukset . Voit myös muuntaa tiedot tuonnin jälkeen. Valitse Valmis. Näyttöön tulee Tuo tiedot -valintaikkuna.
Valitse, haluatko lisätä tiedot nykyiseen laskentataulukkoon, uuteen laskentataulukkoon vai Pivot-taulukkoon.
Valitse OK.

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

Valitse Tiedot > SQLServer ODBC:stä. Näkyviin tulee Muodosta yhteys SQL Server ODBC -tietolähteeseen -valintataulu.
Kirjoita palvelin Palvelimen nimi -ruutuun ja voit halutessasi kirjoittaa tietokannan Tietokannan nimi -ruutuun. Hanki nämä tiedot tietokannan järjestelmänvalvojalta.
Valitse Todennus-kohdassa menetelmä luettelosta: Käyttäjänimi/Salasana, Kerberos tai NTLM.
Kirjoita tunnistetiedot Käyttäjänimi- ja Salasana -ruutuihin.
Valitse Yhdistä. Näyttöön tulee Siirtymistoiminnon valintaikkuna.
Siirry vasemmassa ruudussa haluamaasi taulukkoon ja valitse se.
Vahvista oikeanpuoleisessa ruudussa oleva SQL-lauseke. Voit muuttaa SQL-lauseketta haluamallasi tavalla.
Jos haluat esikatsella tietoja, valitse Suorita.
Kun olet valmis, valitse Palauta tiedot. Näyttöön tulee Tuo tiedot -valintaikkuna.
Valitse, haluatko lisätä tiedot nykyiseen laskentataulukkoon, uuteen laskentataulukkoon vai Pivot-taulukkoon.
Jos haluat määrittää Ominaisuudet -valintaikkunan Käyttö - ja Määritys -välilehtien yhteysominaisuudet, valitse Ominaisuudet. Kun olet tuonut tiedot, voit myös valita Tiedot > Yhteydet ja valita sitten Yhteyden ominaisuudet -valintaikkunassa Ominaisuudet.
Valitse OK.
Varmista, että yhteys toimii, kirjoittamalla joitakin tietoja ja valitsemalla sitten Tiedot > Päivitä kaikki.

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:

Valitse Tiedot > Tietokannasta (Microsoft Query).
Lisää tietokannan tietolähde ja valitse sittenOK.
Anna SQL Server -tunnistetietokehotteeseen todennusmenetelmä, käyttäjänimi ja salasana.
Tuo tietokannat näkyviin valitsemalla vasemmassa laidassa palvelimen nimen vieressä oleva nuoli.
Valitse haluamasi tietokannan vieressä oleva nuoli.
Valitse haluamasi taulukko.
Jos haluat esikatsella tietoja, valitse Suorita.
Kun olet valmis, valitse Palauta tiedot.
Valitse Tuo tietoja -valintaikkunassa, haluatko sijoittaa tiedot nykyiseen laskentataulukkoon, uuteen laskentataulukkoon vai Pivot-taulukkoon.
Valitse OK.
Varmista, että yhteys toimii, kirjoittamalla joitakin tietoja ja valitsemalla sitten Tiedot > Päivitä kaikki.

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

Valitse Tiedot > Yhteydet. Näyttöön tulee Työkirjan yhteydet -valintaikkuna.
Valitse luettelosta haluamasi yhteys ja valitse sitten Tyhjennä käyttöoikeudet.