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

Galite importuoti duomenis į "Excel" naudodami "Power Query" 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 žr. Duomenų formavimas naudojant "Power Query" rengyklė".

Procedūra

1. Pasirinkite Duomenų > Gauti duomenis ("Power Query").

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

   Patarimas: Taip pat galite pasiekti Užklausų rengyklė pasirinkdami Gauti duomenis ("Power Query"), pasirinkdami duomenų šaltinį ir spustelėdami Pirmyn.

3. Kurkite ir transformuokite duomenis naudodami Užklausų rengyklė kaip tai darytumėte programoje "Excel", skirtoje "Windows".
   
   
   
   Daugiau informacijos žr. "Excel" žinyno "Power Query".

4. Baigę pasirinkite Pagrindinis > Uždaryti & Įkelti.

Rezultatas

Naujai importuoti duomenys rodomi naujame lape.

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 bei "Microsoft "SQL Server"" duomenų bazę.

Atnaujinti pirmą kartą

Pirmą kartą bandant atnaujinti failu pagrįstus duomenų šaltinius darbaknygės užklausose, gali tekti 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:

• Visus darbaknygės duomenų šaltinius, pasirinkite Duomenys > Atnaujinti 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" analizė > Atnaujinti duomenis.

Pirmą kartą naudodami "SharePoint", "SQL Server", "OData" ar kitus duomenų šaltinius, kuriems reikia teisių, 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 prisijungtumėte prie duomenų šaltinio ir tęstumėte atnaujinimą.

Jei reikia prisijungti, rodomas dialogo langas Įvesti kredencialus .

Pavyzdžiui:

• "SharePoint" kredencialai:
  
  

• "SQL Server" kredencialus:
  
  

Išvalyti kredencialus

1. Pasirinkite Duomenų > Gauti duomenis > duomenų šaltinio parametrai.

2. Dialogo lange Duomenų šaltinio parametraipasirinkite norimą ryšį.

3. Apačioje pasirinkite Valyti permissions.

4. Patvirtinkite, ką norite daryti, tada pasirinkite Naikinti.

Nors kūrimas "Power Query" rengyklė negalimas programoje "Excel", skirtoje "Mac", VBA palaiko "Power Query" redagavimą. VBA kodo modulio perdavimas faile iš "Excel", skirtos "Windows", į "Excel", skirtą "Mac", yra dviejų veiksmų procesas. Programos pavyzdys pateikiamas šio skyriaus pabaigoje.

Pirmas veiksmas: "Excel", skirta "Windows"

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

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

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

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 žr. Failų sinchronizavimas naudojant "OneDrive" sistemoje "Mac OS X".

Antras veiksmas: "Excel", skirta "Mac"

1. Atsisiųskite VBA failą į vietinį failą, VBA failą, kurį įrašėte atlikdami "Step one: Excel for Windows" (pirmas veiksmas: "Excel", skirta "Windows") ir nusiųstą į internetinę tarnybą.

2. Programoje "Excel", skirtoje "Mac", pasirinkite Įrankiai > Makrokomandos > "Visual Basic" rengyklę. Rodomas langas "Visual Basic" rengyklė .

3. Projekto lange dešiniuoju pelės mygtuku spustelėkite objektą, tada pasirinkite Importuoti failą. Rodomas dialogo langas Failo importavimas .

4. Raskite VBA failą ir pasirinkite Atidaryti.

Kodo pavyzdys

Štai keli pagrindiniai kodai, 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

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ą aukščiausio lygio aplanke, kuriame yra duomenų šaltinio failai.

4. Pasirinkite Duomenų > iš teksto (senstelėjęs). Rodomas dialogo langas Finder .

5. Raskite .txt arba .csv failą ir pasirinkite Atidaryti. Rodomas teksto importavimo vediklis .
   
   Patarimas     Pakartotinai patikrinkite pasirinktų duomenų srities peržiūrą , kad patvirtintumėte savo pasirinkimus.

6. Pirmajame puslapyje atlikite šiuos veiksmus:

   Failo tipas    Norėdami pasirinkti teksto failo tipą, pasirinkite Atskirtasis arba Fiksuotas plotis.
   
   Eilutės numeris   Eilutėje Pradėti importuoti pasirinkite eilutės numerį, kad nurodytumėte pirmą duomenų, kuriuos norite importuoti, eilutę.
   
   Simbolių rinkinys     Dalyje Failo kilmė pasirinkite simbolių rinkinį, kuris naudojamas teksto faile. Daugeliu atvejų šį parametrą galite palikti numatytąjį.

7. Antrame puslapyje atlikite šiuos veiksmus:
   
   Atskirtasis
   
   Jei pirmajame puslapyje pasirinkote Atskirtasis , dalyje Skyrikliai pažymėkite skyriklio simbolį arba naudokite žymės langelį Kita, kad įvestumėte vieną, kurios nėra sąraše.
   
   Pasirinkite Iš eilės einančius skyriklius laikyti vienu , jei jūsų duomenyse yra daugiau nei vieno simbolio skyriklis tarp duomenų laukų arba jei jūsų duomenyse yra keli pasirinktiniai skyrikliai.
   
   Dalyje Teksto kvalifikatorius pasirinkite simbolį, kuris į teksto failą įdės reikšmes, kuris dažniausiai yra kabutės (") simbolis.

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

8. Trečiame puslapyje atlikite šiuos veiksmus:
   
   Pažymėkite kiekvieną stulpelį dalyje Pasirinktų duomenų peržiūra, tada pakeiskite į kitą stulpelio formatą, jei norite. Galite dar labiau nustatyti datos formatą ir pasirinkti Išsamiau , kad pakeistumėte skaitinių duomenų parametrus. Taip pat galite konvertuoti duomenis juos importavę.
   
   Pasirinkite Baigti. Rodomas dialogo langas Duomenų importavimas .

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

10. Pažymėkite Gerai.

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

1. Pasirinkite Duomenys > iš"SQL Server" ODBC.  Rodomas dialogo langas Prisijungimas prie "SQL Server" ODBC duomenų šaltinis.
   
   

2. Lauke Serverio vardas įveskite serverį ir pasirinktinai įveskite duomenų bazę lauke Duomenų bazės pavadinimas .
   
   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 prie norimos lentelės ir ją pažymėkite.

7. Patvirtinkite SQL sakinį dešiniojoje srityje. Galite keisti SQL sakinį, kaip jums atrodo tinkama.

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 arba "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 Duomenys > ryšiai, tada dialogo lange Ryšio ypatybės pasirinkite Ypatybės.

12. Pažymėkite Gerai.

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

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

1. Pasirinkite Duomenų > iš duomenų bazės (Microsoft Query).

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

3. Raginime "SQL Server" kredencialus įveskite autentifikavimo metodą, vartotojo vardą ir slaptažodį.
   
   

4. Kairėje pusėje pasirinkite rodyklę šalia serverio, kad pamatytumėte duomenų bazes.

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 turėtų būti duomenys: esamame lape, naujame lape arba "PivotTable".

10. Pasirinkite Gerai.

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

Jei jūsų teisės neveikia, pirmiausia galite jų neišvalyti ir prisijungti.

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

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