Duomenų importavimas ir formavimas programoje "Excel", skirtoje "Mac" ("„Power Query“")

Duomenų šaltinių importavimas

Naudodami "„Power Query“" galite importuoti duomenis į "Excel" iš įvairių duomenų šaltinių: "Excel" darbaknygės, teksto/CSV, XML, JSON, „SQL Server“ duomenų bazės, "SharePoint Online" sąrašo, "OData", tuščios lentelės ir tuščios užklausos.

1. Pasirinkite Duomenys>Gauti duomenis.

   

2. Norėdami pasirinkti norimą duomenų šaltinį, pasirinkite Gauti duomenis („Power Query“).

3. Dialogo lange Duomenų šaltinio pasirinkimas pasirinkite vieną iš galimų duomenų šaltinių.

   

4. Prisijunkite prie duomenų šaltinio. Norėdami sužinoti daugiau, kaip prisijungti prie kiekvieno duomenų šaltinio, žr. Duomenų importavimas iš duomenų šaltinių.

5. Pasirinkite duomenis, kuriuos norite importuoti.

6. Įkelkite duomenis spustelėdami mygtuką Įkelti .

Rezultatas

Importuoti duomenys rodomi naujame lape.

Kiti veiksmai

Norėdami formuoti ir transformuoti duomenis naudodami „Power Query“ rengyklė, pasirinkite Transformuoti duomenis. Daugiau informacijos rasite Duomenų formavimas naudojant "Power Query" rengyklę „Power Query“ rengyklė.

Duomenų formavimas naudojant „Power Query“ rengyklė

Procedūra

1. Pasirinkite Duomenų>gavimo duomenys ("„Power Query“").

2. Norėdami atidaryti Užklausų rengyklę, pasirinkite Paleisti „Power Query“ rengyklę.

   

   Patarimas

   Taip pat galite pasiekti Užklausų rengyklę pasirinkę Gauti duomenis („Power Query“), pasirinkdami duomenų šaltinį ir spustelėdami Pirmyn.

3. Formuokite ir transformuokite duomenis naudodami Užklausų rengyklę, kaip tai darytumėte "Excel", skirtoje "Windows".

   

   Daugiau informacijos žr. "„Power Query“ for Excel" žinyne.

4. Kai tai padarysite, pasirinkite Namų>uždarymas & Įkelti.

Rezultatas

Naujai importuoti duomenys rodomi naujame lape.

Duomenų šaltinių atnaujinimas

Galite atnaujinti šiuos duomenų šaltinius: "SharePoint" failus, "SharePoint" sąrašus, "SharePoint" aplankus, "OData", teksto / CSV failus, "Excel" darbaknyges (.xlsx), XML ir JSON failus, vietines lenteles ir diapazonus, "Microsoft „SQL Server“" duomenų bazę ir aplankus.

Atnaujinti pirmą kartą

Pirmą kartą bandant atnaujinti failais pagrįstus duomenų šaltinius darbaknygės užklausose, gali reikėti atnaujinti failo kelią.

1. Pasirinkite Duomenys, rodyklę šalia Gauti duomenis, tada – Duomenų šaltinio parametrai. Rodomas dialogo langas Duomenų šaltinio parametrai .
2. Pasirinkite ryšį, tada pasirinkite Keisti failo kelią.
3. Dialogo lange Failo kelias pasirinkite naują vietą, tada pasirinkite Gauti duomenis.
4. Pasirinkite Uždaryti.

Atnaujinti vėlesnius laikus

Norėdami atnaujinti:

• Visi darbaknygės duomenų šaltiniai, pasirinkite Atnaujinti duomenis>viską.
• konkretų duomenų šaltinį, dešiniuoju pelės mygtuku spustelėkite užklausos lentelę lape, tada pasirinkite Atnaujinti.
• "PivotTable", pasirinkite langelį "PivotTable", tada pasirinkite "PivotTable" analizuoti>atnaujinti duomenis.

Įveskite ir išvalykite kredencialus

Pirmą kartą bandydami pasiekti "SharePoint", "„SQL Server“", "OData" ar kitus duomenų šaltinius, kuriems reikia leidimo, turite pateikti atitinkamus kredencialus. Taip pat galite išvalyti kredencialus, kad įvestumėte naujus.

Įveskite kredencialus

Kai atnaujinate užklausą pirmą kartą, jūsų gali paprašyti prisijungti. Pasirinkite autentifikavimo metodą ir nurodykite prisijungimo kredencialus, kad galėtumėte prisijungti prie duomenų šaltinio ir tęsti atnaujinimą.

Jei prisijungimas būtinas, rodomas dialogo langas Įveskite kredencialus .

Pavyzdžiui:

• "SharePoint" kredencialai:

  

• „SQL Server“ kredencialai:

  

Išvalyti kredencialus

1. Pasirinkite Duomenų>gavimo duomenų>šaltinio parametrai.
2. Dialogo lange Duomenų šaltinio parametrais pasirinkite norimą ryšį.
3. Apačioje pasirinkite Išvalyti teises.
4. Patvirtinkite, ką norite daryti, ir pasirinkite Naikinti.

"„Power Query“" VBA kodo autorizavimas ir perdavimas

Nors kūrimas naudojant "Power Query" „Power Query“ rengyklę negalimas programoje "Excel", skirtoje "Mac", VBA palaiko "„Power Query“" kūrimą. VBA kodo modulio faile perkėlimas iš "Excel", skirtos "Windows", į "Excel", skirtą "Mac", yra dviejų veiksmų procesas. Šio skyriaus pabaigoje pateikiamas programos pavyzdys.

Pirmas veiksmas: "Excel", skirta "Windows"

1. "Excel Windows" kurkite užklausas naudodami VBA. VBA kodas, kuris naudoja šiuos objektus "Excel" objekto modelyje, taip pat veikia "Excel", skirtoje "Mac": užklausų objektas, objektas WorkbookQuery, ypatybė Workbook.Queries. Daugiau informacijos ieškokite "Excel" VBA nuorodoje.

2. Programoje "Excel" paspausdami ALT+F11 įsitikinkite, kad atidaryta "Visual Basic" rengyklė.

3. Dešiniuoju pelės mygtuku spustelėkite modulį, tada pasirinkite Eksportuoti failą. Rodomas eksportavimo dialogo langas.

4. Įveskite failo vardą, įsitikinkite, kad failo plėtinys yra .bas, tada pasirinkite Įrašyti.

5. Nusiųskite VBA failą į internetinę tarnybą, kad failas būtų pasiekiamas iš "Mac".

   Galite naudoti "„Microsoft OneDrive“". Daugiau informacijos rasite Failų sinchronizavimas su "„OneDrive“" sistemoje "Mac OS X".

Antras veiksmas: "Excel", skirta "Mac"

1. Atsisiųskite VBA failą į vietinį failą, VBA failą, kurį įrašėte atlikdami "pirmąjį veiksmą: "Excel", skirta "Windows" ir nusiuntėte jį į internetinę tarnybą.
2. Programoje "Excel", skirtoje "Mac", pasirinkite Įrankiai>Makrokomanda>"Visual Basic" rengyklė. Rodomas "Visual Basic" rengyklės langas.
3. Dešiniuoju pelės mygtuku spustelėkite objektą projekto lange ir pasirinkite Importuoti failą. Pasirodo dialogo langas Failo importavimas .
4. Raskite VBA failą ir pasirinkite Atidaryti.

Kodo pavyzdys

Štai keletas pagrindinių kodų, kuriuos galite pritaikyti ir naudoti. Tai užklausos pavyzdys, kuris sukuria sąrašą su reikšmėmis nuo 1 iki 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 arba CSV failų importavimas ir naujinimas

1. Atidarykite "Excel" darbaknygę.

2. Jei gaunate saugos įspėjimą apie išjungtus išorinių duomenų ryšius, pasirinkite Įgalinti turinį.

3. Jei rodomas dialogo langas Suteikti prieigą prie failų , pasirinkite Pasirinkti, tada pasirinkite Suteikti prieigą prie aukščiausio lygio aplanko, kuriame yra duomenų šaltinio failai.

4. Pasirinkti duomenis>iš teksto (senstelėjęs). Rodomas dialogo langas Ieškiklis .

5. Raskite .txt arba .csv failą ir pasirinkite Atidaryti.Rodomas teksto importavimo vediklis .

   Patarimas Keletą kartų patikrinkite sritį Pasirinktų duomenų peržiūra , kad patvirtintumėte savo pasirinkimus.

6. Pirmajame puslapyje atlikite šiuos veiksmus:
   Failo tipas Norėdami pasirinkti teksto failo tipą, pasirinkite Atskirtieji failai arba Fiksuotas plotis.

   Eilutės numeris Dalyje Pradėti importuoti nuo eilutės pasirinkite eilutės numerį, kad nurodytumėte pirmąją duomenų eilutę, kurią norite importuoti.

   Simbolių rinkinysDalyje Failo kilmė pasirinkite simbolių rinkinį, naudojamą teksto faile. Daugeliu atvejų galite palikti numatytąjį šį parametrą.

7. Antrame puslapyje atlikite šiuos veiksmus:

   Atskirtieji failai

   Jei pirmajame puslapyje pasirinkote Atskirtieji skyrikliai , dalyje Skyriklius pasirinkite skyriklio simbolį arba naudodami žymės langelį Kiti įveskite nenurodytą simbolį.

   Pasirinkite Iš eilės einančius skyriklius traktuoti kaip vieną , jei jūsų duomenyse yra daugiau nei vieno simbolio skyriklis tarp duomenų laukų arba jei jūsų duomenyse yra keli pasirinktiniai skyrikliai.

   Teksto kvalifikatoriuje pažymėkite simbolį, kuriame yra teksto failo reikšmės, kuris dažniausiai būna kabutės (") simbolis.
   Fiksuotas plotis

   Jei pirmajame puslapyje pasirinkote Fiksuotas plotis , vykdykite instrukcijas, kad sukurtumėte, panaikintumėte arba perkeltumėte lūžio liniją lauke Pasirinktų duomenų peržiūra .

8. Trečiame puslapyje atlikite šiuos veiksmus:

   Kiekvienam stulpeliui dalyje Pasirinktų duomenų peržiūra pažymėkite jį, tada, jei norite, pakeiskite į kitą stulpelio formatą. Galite toliau nustatyti datos formatą ir pasirinkti Išplėstinis , kad pakeistumėte skaitinių duomenų parametrus. Importavus duomenis juos taip pat galima konvertuoti.

   Pasirinkite Baigti. Rodomas dialogo langas Duomenų importavimas .

9. Pasirinkite, kur norite įtraukti duomenis: esamame lape, naujame lape ar "PivotTable".

10. Pažymėkite Gerai.
    Norėdami užtikrinti, kad ryšys veikia, įveskite kai kuriuos duomenis ir pasirinkite Ryšių>atnaujinimas.

Prisijungimas prie SQL duomenų bazės (ODBC)

1. Pasirinkite duomenis>iš „SQL Server“ ODBC. Rodomas dialogo langas Prisijungimas prie „SQL Server“ ODBC duomenų šaltinio.

   

2. Lauke Serverio vardas įveskite serverį ir pasirinktinai lauke Duomenų bazės pavadinimas įveskite duomenų bazę.

   Gaukite šią informaciją iš duomenų bazės administratoriaus.

3. Dalyje Autentifikavimas iš sąrašo pasirinkite metodą: Vartotojo vardas / slaptažodis, "Kerberos" arba NTLM.

4. Laukuose Vartotojo vardas ir slaptažodis įveskite kredencialus.

5. Pasirinkite Prisijungti. Rodomas dialogo langas Naršyklė .

6. Kairiojoje srityje pereikite į norimą lentelę ir ją pasirinkite.

7. Patvirtinkite SQL sakinį dešiniojoje srityje. SQL sakinį galite keisti savo nuožiūra.

8. Norėdami peržiūrėti duomenis, pasirinkite Vykdyti.

9. Kai būsite pasirengę, pasirinkite Grąžinti duomenis. Rodomas dialogo langas Duomenų importavimas .

   

10. Pasirinkite, kur norite įtraukti duomenis: esamame lape, naujame lape ar "PivotTable".

11. Norėdami nustatyti ryšio ypatybes, dialogo lango Ypatybės skirtukuose Naudojimas ir Apibrėžimas pasirinkite Ypatybės. Importavę duomenis, taip pat galite pasirinkti Duomenų> ryšiai, tada dialogo lange Ryšio ypatybės pasirinktiYpatybės.

12. Pažymėkite Gerai.

13. Norėdami užtikrinti, kad ryšys veikia, įveskite kai kuriuos duomenis ir pasirinkite Atnaujinti duomenis>viską.

Prisijungimas prie kitos duomenų bazės ("„Microsoft“ Query")

Jei norite naudoti išorinį šaltinį, kuris nėra SQL duomenų bazė (pvz., "FileMaker Pro"), galite naudoti "Mac" įdiegtą "Open Database Connectivity" (ODBC) tvarkyklę. Informaciją apie tvarkykles rasite šiame tinklalapyje. Įdiegę duomenų šaltinio tvarkyklę atlikite toliau nurodytus veiksmus.

1. Duomenų pasirinkimas>iš duomenų bazės ("„Microsoft“ Query").

2. Įtraukite duomenų bazės duomenų šaltinį, tada pasirinkite Gerai.

3. "„SQL Server“" kredencialų raginime įveskite autentifikavimo metodą, vartotojo vardą ir slaptažodį.

   

4. Norėdami peržiūrėti duomenų bazes, kairėje pusėje pasirinkite rodyklę šalia serverio.

5. Pasirinkite rodyklę šalia norimos duomenų bazės.

6. Pasirinkite norimą lentelę.

7. Norėdami peržiūrėti duomenis, pasirinkite Vykdyti.

8. Kai būsite pasirengę, pasirinkite Grąžinti duomenis.

9. Dialogo lange Duomenų importavimas pasirinkite, kur norite laikyti duomenis: esamame lape, naujame lape ar "PivotTable".

10. Pažymėkite Gerai.

11. Norėdami užtikrinti, kad ryšys veikia, įveskite kai kuriuos duomenis ir pasirinkite Atnaujinti duomenis>viską.

Išvalykite savo prisijungimo duomenis

Jei jūsų teisės neveikia, galbūt pirmiausia jų nepašalinsite, o tada prisijunkite.

1. Pasirinkite Duomenų>ryšiai. Atsiranda dialogo langas Darbaknygės ryšiai .

2. Sąraše pasirinkite norimą ryšį, tada pasirinkite Valyti teises.