Andmete importimine ja kujundamine rakenduses Excel for Mac (Power Query)

Power Query abil saate andmeid Excelisse importida mitmesugustest andmeallikatest: Exceli töövihik, tekst/CSV, XML, JSON, SQL Server andmebaas, SharePoint Online'i loend, OData, tühi tabel ja tühi päring.

1. Valige Andmed > Too andmed.
   
   

2. Soovitud andmeallika valimiseks valige Too andmed (Power Query).

3. Valige dialoogiboksis Andmeallika valimine üks saadaolevatest andmeallikatest. 
   
   

4. Looge ühendus andmeallikaga. Lisateavet iga andmeallikaga ühenduse loomise kohta leiate teemast Andmete importimine andmeallikatest.

5. Valige andmed, mida soovite importida.

6. Andmete laadimiseks klõpsake nuppu Laadi .

Tulem

Imporditud andmed kuvatakse uuel lehel.

Järgmised toimingud

Andmete kujundamiseks ja teisendamiseks Power Query redaktor abil valige Teisenda andmed. Lisateavet leiate teemast Andmete kujundamine Power Query redaktor abil.

Protseduur

1. Valige Andmed > Too andmed (Power Query).

2. Päringuredaktor avamiseks valige Käivita Power Query redaktor.
   
   

   Näpunäide.: Samuti pääsete Päringuredaktor juurde, kui valite Too andmed (Power Query), valite andmeallika ja klõpsate nuppu Edasi.

3. Andmete kujundamiseks ja teisendamiseks saate kasutada Päringuredaktor nagu Windowsi excelis.
   
   
   
   Lisateavet leiate Power Query Exceli spikrist.

4. Kui olete lõpetanud, valige Avaleht > Sule & Laadi.

Tulem

Äsja imporditud andmed kuvatakse uuel lehel.

Värskendada saate järgmisi andmeallikaid: SharePointi failid, SharePointi loendid, SharePointi kaustad, OData, teksti- ja CSV-failid, Exceli töövihikud (.xlsx), XML- ja JSON-failid, kohalikud tabelid ja vahemikud ning Microsofti SQL Server andmebaas.

Värskenda esimest korda

Kui proovite töövihiku päringutes failipõhiseid andmeallikaid esimest korda värskendada, peate võib-olla failitee värskendama.

1. Valige Andmed,  nupu Too andmed kõrval olev nool ja seejärel Andmeallika sätted. Kuvatakse dialoogiboks Andmeallika sätted .

2. Valige ühendus ja seejärel valige Muuda failiteed.

3. Valige dialoogiboksis Faili tee uus asukoht ja seejärel valige Too andmed.

4. Valige Sulge.

Värskenda järgmised kellaajad

Värskendamiseks tehke järgmist.

• Töövihiku kõik andmeallikad valige Andmed > Värskenda kõik.

• Paremklõpsake konkreetset andmeallikat, paremklõpsake lehel päringutabelit ja seejärel valige Värskenda.

• PivotTable-liigendtabel, valige PivotTable-liigendtabelis lahter ja seejärel valige PivotTable-liigendtabeli analüüs > Värskenda andmed.

Kui kasutate SharePointi, SQL Server, ODatat või muid õigusi nõudvaid andmeallikaid esimest korda, peate sisestama vastava identimisteabe. Samuti võite uute identimisteabe sisestamiseks identimisteabe tühjendada.

Sisestage identimisteave

Päringu esmakordsel värskendamisel võidakse teil paluda sisse logida. Valige autentimismeetod ja määrake andmeallikaga ühenduse loomiseks ja värskendamise jätkamiseks sisselogimisteave.

Kui sisselogimine on nõutav, kuvatakse dialoogiboks Identimisteabe sisestamine .

Siin on mõned näited.

• SharePointi identimisteave:
  
  

• SQL Server identimisteave:
  
  

Eemalda identimisteave

1. Valige Andmed > Too andmed > andmeallika sätted.

2. Valige dialoogiboksis Andmeallika sättedsoovitud ühendus.

3. Valige allservas Tühjenda permissions.

4. Kinnitage, et soovite seda teha, ja seejärel valige Kustuta.

Kuigi Power Query redaktor autorlus pole rakenduses Excel for Mac saadaval, toetab VBA Power Query autorlusteenust. VBA-koodi mooduli ülekandmine failis rakendusest Excel for Windows rakendusse Excel for Mac on kaheetapiline protsess. Selle jaotise lõpus kuvatakse teile näidisprogramm.

Esimene juhis: Excel for Windows

1. Arendage Exceli Windowsis päringuid VBA abil. VBA-kood, mis kasutab Exceli objektimudelis järgmisi olemeid, töötab ka rakenduses Excel for Mac: päringuobjekt, objekt WorkbookQuery, atribuut Workbook.Queries.Lisateavet leiate teemast Exceli VBA teatmematerjalid.

2. Veenduge, et Excelis oleks Visual Basic Editor avatud, vajutades klahvikombinatsiooni ALT+F11.

3. Paremklõpsake moodulit ja seejärel valige Ekspordi fail. Kuvatakse dialoogiboks Eksport .

4. Sisestage failinimi, veenduge, et faililaiend oleks .bas, ja seejärel valige Salvesta.

5. Laadige VBA-fail üles veebiteenusesse, et muuta fail Mac-arvutist juurdepääsetavaks.
   
   Saate kasutada Microsoft OneDrive'i. Lisateavet leiate teemast Failide sünkroonimine OneDrive'iga Mac OS X-is.

Teine juhis: Excel for Mac

1. Laadige VBA-fail kohalikku faili, salvestatud "Esimene etapp: Excel For Windows" salvestatud ja veebiteenusesse üles laaditud VBA-fail.

2. Rakenduses Excel for Mac valige Tööriistad > Makro > Visual Basic Editor. Kuvatakse Visual Basic Editori aken.

3. Paremklõpsake projektiaknas objekti ja seejärel valige Impordi fail. Kuvatakse dialoogiboks Faili importimine .

4. Otsige üles VBA-fail ja seejärel valige Ava.

Näidiskood

Siin on mõned põhikoodid, mida saate kohandada ja kasutada. See on näidispäring, mis loob loendi väärtustega 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

1. Avage Exceli töövihik.

2. Kui teile kuvatakse turbehoiatus väliste andmeühenduste keelamise kohta, valige Luba sisu.

3. Kui kuvatakse dialoogiboks Failile juurdepääsu andmine , valige Vali ja seejärel valige Anna juurdepääs andmeallikafaile sisaldavale ülataseme kaustale.

4. Valige > Andmed tekstist (pärand). Kuvatakse dialoogiboks F-der.

5. Otsige üles .txt- või .csv-fail ja valige Ava. Kuvatakse tekstiimpordiviisard .
   
   Otsa     Valikute kinnitamiseks kontrollige korduvalt valitud andmepaani eelvaadet .

6. Tehke esimesel lehel järgmist.

   Failitüüp    Tekstifaili tüübi valimiseks valige Eraldajaga või Kindla laiusega.
   
   Reanumber    Valige väljal Alusta importimist realt reanumber, et määrata esimene imporditav andmerida.
   
   Märgistik     Valige väljal Faili lähtekoht tekstifailis kasutatav märgistik. Enamasti saate selle sätte vaikesätteks jätta.

7. Teisel lehel tehke järgmist:
   
   Eraldajaga
   
   Kui valisite esilehekülje eraldajatega, märkige jaotises Eraldajad eraldajamärk või sisestage loendist puuduv märk märkeruudu Muu abil.
   
   Valige Kohtle järjestikusi eraldajaid ühena , kui teie andmed sisaldavad andmeväljade vahel rohkem kui ühe märgi eraldajat või kui andmed sisaldavad mitut kohandatud eraldajat.
   
   Valige väljal Tekstitäpsusti märk, mis ümbritseb väärtused teie tekstifaili, mis on kõige sagedamini jutumärk (").

   Kindla laiusega
   
   Kui valisite esimesel lehel kindla laiusega rea, järgige piirjoone loomiseks, kustutamiseks või teisaldamiseks juhiseid väljal Valitud andmete eelvaade .

8. Kolmandal lehel tehke järgmist
   
   . Valige iga veeru jaoks jaotises Valitud andmete eelvaade need ja muutke need soovi korral erinevasse veeruvormingusse. Numbriliste andmete sätete muutmiseks saate kuupäevavormingut täpsemalt määrata ja valida Täpsemad . Samuti saate andmed pärast importimist teisendada.
   
   Valige Valmis. Kuvatakse dialoogiboks Andmete importimine .

9. Valige, kuhu soovite andmed lisada: kas olemasoleval lehel, uuel lehel või PivotTable-liigendtabelis.

10. Klõpsake nuppu OK.

    Ühenduse toimimise tagamiseks sisestage mõned andmed ja seejärel valige Ühendused > Värskenda.

1. Valige Andmed > saatjaSQL Server ODBC.  Kuvatakse dialoogiboks SQL Server ODBC-andmeallikaga ühenduse loomine.
   
   

2. Sisestage server väljale Serveri nimi ja soovi korral sisestage andmebaas väljale Andmebaasi nimi .
   
   Hankige see teave andmebaasi administraatorilt.

3. Valige jaotises Autentimine loendist soovitud meetod: Kasutajanimi/parool, Kerberos või NTLM.

4. Sisestage identimisteave väljadele Kasutajanimi ja Parool.

5. Valige Ühenda. Kuvatakse dialoogiboks Navigaator .

6. Liikuge vasakul paanil soovitud tabelile ja valige see.

7. Kinnitage SQL-lause parempoolsel paanil. SQL-lauset saate muuta vastavalt vajadusele.

8. Andmete eelvaate kuvamiseks valige Käivita.

9. Kui olete valmis, valige Tagastusandmed. Kuvatakse dialoogiboks Andmete importimine . 
   
   

10. Valige, kuhu soovite andmed lisada: kas olemasoleval lehel, uuel lehel või PivotTable-liigendtabelis.

11. Ühenduse atribuutide määramiseks dialoogiboksi Atribuudid vahekaartidel Kasutus ja Määratlus valige Atribuudid. Pärast andmete importimist saate valida ka andmed > ühendused ja seejärel dialoogiboksis Ühenduse atribuudid käsu Atribuudid.

12. Klõpsake nuppu OK.

13. Ühenduse toimimise tagamiseks sisestage mõned andmed ja seejärel valige Andmed > Värskenda kõik.

Kui soovite kasutada välist allikat, mis pole SQL-andmebaas (nt FileMaker Pro), saate kasutada Oma Mac-arvutisse installitud ODBC-draiverit. Draiverite teave on saadaval sellel veebilehel. Kui andmeallika draiver on installitud, tehke järgmist.

1. Valige Data > From Database (Microsoft Query).

2. Lisage oma andmebaasi andmeallikas ja seejärel valige OK.

3. Sisestage SQL Server identimisteabe viibale autentimisviis, kasutajanimi ja parool.
   
   

4. Andmebaaside kuvamiseks valige vasakul serveri kõrval olev nool.

5. Valige soovitud andmebaasi kõrval olev nool.

6. Valige soovitud tabel.

7. Andmete eelvaate kuvamiseks valige Käivita.

8. Kui olete valmis, valige Tagastusandmed.

9. Valige dialoogiboksis Andmete importimine koht, kuhu soovite andmed paigutada: kas olemasoleval lehel, uuel lehel või PivotTable-liigendtabelis.

10. Klõpsake nuppu OK.

11. Ühenduse toimimise tagamiseks sisestage mõned andmed ja seejärel valige Andmed > Värskenda kõik.

Kui teie õigused ei tööta, ei pruugi te neid esmalt tühjendada ja seejärel sisse logida.

1. Valige Data > Connections (Andmeühendused). Kuvatakse dialoogiboks Töövihiku ühendused .

2. Valige loendist soovitud ühendus ja seejärel valige Tühjenda õigused.