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

Andmeallikate importimine

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 DataGet Data (Andmete >toomine).

   

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 etapid

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

Andmete kujundamine Power Query redaktor

Protseduur

1. Valige Andmete>toomise 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 & Avakuva>.

Tulem

Äsja imporditud andmed kuvatakse uuel lehel.

Andmeallikate värskendamine

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, Microsofti SQL Server andmebaas ja kaustad.

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, klõpsake nupu Too andmed kõrval olevat noolt ja seejärel nuppu 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.

• Kõik töövihiku andmeallikad valige 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 andmeid.

Sisestage ja tühjendage identimisteave

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 DataGetData Source Settings (Andmeallika >toomisesätted).>
2. Valige dialoogiboksis Andmeallika sättedsoovitud ühendus.
3. Klõpsake allservas nuppu Tühjenda õigused.
4. Kinnitage, et soovite seda teha, ja seejärel valige Kustuta.

Power Query VBA-koodi koostamine ja ülekandmine

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 Tools Macro>>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

TXT- või CSV-failide importimine ja värskendamine

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 Otsija .

5. Otsige üles .txt- või .csv-fail ja valige Ava. Kuvatakse tekstiimpordiviisard .

   Näpunäide 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.

   Eraldajatega

   Kui valisite esileheküljeleraldajatega märgi, 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 (") mä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.
    Veendumaks, et ühendus töötab, sisestage mõned andmed ja seejärel valige Ühendused>värskenda.

Ühenduse loomine SQL-andmebaasiga (ODBC)

1. ValigeSQL Server ODBC-standmed>. 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 vastavalt vajadusele muuta.

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 kaandmeühendused> ja seejärel dialoogiboksis Ühenduse atribuudid käsu Atribuudid.

12. Klõpsake nuppu OK.

13. Veendumaks, et ühendus töötab, sisestage mõned andmed ja seejärel valige Andmed>Värskenda kõik.

Ühenduse loomine mõne muu andmebaasiga (Microsoft Query)

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 Andmed>andmebaasist (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. Veendumaks, et ühendus töötab, sisestage mõned andmed ja seejärel valige Andmed>Värskenda kõik.

Kustutage oma sisselogimisandmed

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

1. ValigeAndmeühendused>. Kuvatakse dialoogiboks Töövihiku ühendused .

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