Uvoz in oblikovanje podatkov v Excelu za Mac (Power Query)

Uvoz virov podatkov

Podatke lahko uvozite v Excel z dodatkom Power Query iz številnih različnih virov podatkov: Excelov delovni zvezek, besedilo/CSV, XML, JSON, zbirka podatkov SQL Server, seznam SharePoint Online, OData, prazna tabela in prazna poizvedba.

1. Izberite »Dobi podatke«>.

   

2. Če želite izbrati želeni vir podatkov, izberite »Pridobi podatke« (Power Query).

3. V pogovornem oknu » Izbira vira podatkov « izberite enega od razpoložljivih virov podatkov.

   

4. Ustvarite povezavo do vira podatkov. Če želite izvedeti več o tem, kako vzpostavite povezavo s posameznimi viri podatkov, glejte »Uvoz podatkov iz virov podatkov«.

5. Izberite podatke, ki jih želite uvoziti.

6. Podatke naložite tako, da kliknete gumb »Naloži« .

Rezultat

Uvoženi podatki so prikazani na novem listu.

Naslednji koraki

Če želite oblikovati in pretvoriti podatke z urejevalnikom Power Query, izberite »Pretvorba podatkov«. Če želite več informacij, glejte »Oblikovanje podatkov z urejevalnikom Power Query.

Oblikovanje podatkov z urejevalnik Power Query

procedura

1. Izberite možnost »Dobi podatke>« (Power Query)).

2. Če želite odpreti Urejevalnik poizvedb, izberite »Zaženi urejevalnik Power Query«.

   

   Namig

   Urejevalnik poizvedb lahko odprete tudi tako, da izberete »Pridobi podatke« (Power Query), izberete vir podatkov in nato kliknete »Naprej«.

3. Oblikujte in pretvorite podatke z Urejevalnikom poizvedb, kot bi to naredili v Excelu za Windows.

   

   Če želite več informacij, glejte pomoč za Power Query za Excel.

4. Ko končate, izberite »Osnovno>«Zapri & naloži.

Rezultat

Novo uvoženi podatki so prikazani na novem listu.

Osveževanje virov podatkov

Osvežite lahko te vire podatkov: SharePointove datoteke, SharePointove sezname, SharePointove mape, OData, besedilne datoteke ali datoteke CSV, Excelove delovne zvezke (.xlsx), datoteke XML in JSON, lokalne tabele in obsege, zbirko podatkov Microsoft SQL Server in mape.

Prvo osveževanje

Ko boste prvič poskusili osvežiti datotečne vire podatkov v poizvedbah delovnega zvezka, boste morda morali posodobiti pot datoteke.

1. Izberite »Podatki«, puščico ob možnosti »Pridobi podatke« in nato »Nastavitve vira podatkov«. Odpre se pogovorno okno »Nastavitve vira podatkov «.
2. Izberite povezavo in nato možnost » Spremeni pot datoteke«.
3. V pogovornem oknu »Pot datoteke « izberite novo mesto in nato izberite »Pridobi podatke«.
4. Izberite Zapri.

Naknadno osveževanje

Osvežitev:

• Vsi viri podatkov v delovnem zvezku, izberite»Osveživse«>.
• določen vir podatkov. Z desno tipko miške kliknite tabelo poizvedbe na listu in izberite »Osveži«.
• vrtilno tabelo, izberite celico v vrtilni tabeli in nato izberite »Analiza vrtilne tabele«Osveži> podatke.

Vnesite in počistite poverilnice

Ob prvem dostopu do SharePointa, strežnika SQL Server, povezave OData ali drugih virov podatkov, za katere je potrebno dovoljenje, morate vnesti ustrezne poverilnice. Morda boste želeli tudi počistiti poverilnice za vnos novih.

Vnos poverilnic

Ko prvič osvežite poizvedbo, boste morda pozvani, da se vpišete. Izberite način preverjanja pristnosti in določite poverilnice za prijavo, da vzpostavite povezavo z virom podatkov in nadaljujete z osveževanjem.

Če se morate vpisati, se prikaže pogovorno okno za vnos poverilnic .

Primer:

• SharePointove poverilnice:

  

• Poverilnice za SQL Server:

  

Počistite poverilnice

1. Izberitenastavitve vira podatkovza pridobivanje podatkov>>.
2. V pogovornem oknu » Nastavitve vira podatkov« izberite želeno povezavo.
3. Na dnu izberite »Počisti dovoljenja«.
4. Potrdite, da želite to narediti, in nato izberite »Izbriši«.

Ustvarjanje in prenos kode VBA za Power Query

Čeprav avtorstvo v urejevalnik Power Query ni na voljo v Excelu za Mac, VBA podpira avtorstvo v dodatku Power Query. Prenos modula kode VBA v datoteki iz Excela za Windows v Excel za Mac poteka v dveh korakih. Vzorčni program je na voljo na koncu tega razdelka.

1. korak: Excel za Windows

1. V sistemu Excel Windows lahko razvijate poizvedbe s programom VBA. Koda VBA, ki uporablja te entitete v Excelovem predmetnem modelu, deluje tudi v Excelu za Mac: predmet Queries, predmet WorkbookQuery, lastnost Workbook.Queries. Če želite več informacij, glejte referenco za Excel VBA.

2. V Excelu se prepričajte, da je urejevalnik za Visual Basic odprt, tako da pritisnete ALT+F11.

3. Z desno tipko miške kliknite modul in nato izberite »Izvozi datoteko«. Odpre se pogovorno okno za izvoz .

4. Vnesite ime datoteke, prepričajte se, da je datotečna pripona .bas, in nato izberite Shrani.

5. Prenesite datoteko VBA v spletno storitev, da omogočite dostop do datoteke iz računalnika Mac.

   Uporabite lahko Microsoft OneDrive. Če želite več informacij, glejte »Sinhronizacija datotek s storitvijo OneDrive v sistemu Mac OS X«.

2. korak: Excel za Mac

1. Prenesite datoteko VBA v lokalno datoteko. To je datoteka VBA, ki ste jo shranili v »1. koraku: Excel za Windows« in prenesli v spletno storitev.
2. V Excelu za Mac izberite »Orodja>«Urejevalnik za makro> Visual Basic. Odpre se okno urejevalnika za Visual Basic .
3. Z desno tipko miške kliknite predmet v oknu projekta in nato izberite »Uvozi datoteko«. Prikaže se pogovorno okno za uvoz datoteke .
4. Poiščite datoteko VBA in izberite »Odpri«.

Vzorčna koda

Tukaj je nekaj osnovnih kod, ki jih lahko prilagodite in uporabite. To je vzorčna poizvedba, ki ustvari seznam z vrednostmi od 1 do 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

Uvoz in osveževanje datotek TXT ali CSV

1. Odprite Excelov delovni zvezek.

2. Če prejmete varnostno opozorilo, da so zunanje podatkovne povezave onemogočene, izberite »Omogoči vsebino«.

3. Če se prikaže pogovorno okno »Dovoli dostop do datoteke «, izberite »Izberi« in nato izberite »Dovoli dostop « do mape najvišje ravni, ki vsebuje datoteke z viri podatkov.

4. Izberite podatke>iz besedila (podedovano). Odpre se pogovorno okno Finder (Iskalnik ).

5. Poiščite datoteko .txt ali .csv in izberite Odpri. Prikaže se čarovnik za uvoz besedila .

   Namig Večkrat preverite podokno »Predogled izbranih podatkov «, da potrdite svoje izbire.

6. Na prvi strani naredite to:
   Vrsta datoteke Če želite izbrati vrsto besedilne datoteke, izberite Ločeno ali Nespremenljiva širina.

   Številka vrstice V razdelku » Začni uvoz v vrstico« izberite številko vrstice, da določite prvo vrstico s podatki, ki jih želite uvoziti.

   Nabor znakov V razdelku »Izvor datoteke« izberite nabor znakov, ki je uporabljen v besedilni datoteki. V večini primerov lahko to nastavitev pustite privzeto.

7. Na drugi strani naredite to:

   Ločeno

   Če ste na prvi strani izbrali Ločeno na prvi strani, v razdelku »Ločila« izberite znak ločila ali v polje »Drugo« vnesite znak, ki ga ni na seznamu.

   Če vaši podatki vsebujejo ločilo z več kot enim znakom med podatkovnimi polji ali če podatki vsebujejo več ločil po meri ali če podatki vsebujejo več ločil po meri, izberite možnost »Zaporedna ločila po meri« obravnavajte kot eno.

   V kvalifikatorju besedila izberite znak, ki obdaja vrednosti v besedilni datoteki, ki je najpogosteje znak za narekovaj (").
   Nespremenljiva širina

   Če ste na prvi strani izbrali nespremenljivo širino , sledite navodilom za ustvarjanje, brisanje ali premikanje črte preloma v polju »Predogled izbranih podatkov «.

8. Na tretji strani naredite to:

   Za vsak stolpec v razdelku »Predogled izbranih podatkov« ga izberite in po želji spremenite obliko stolpca. Nadalje lahko nastavite obliko zapisa datuma in izberete »Dodatno «, da spremenite nastavitve številskih podatkov. Podatke lahko pretvorite tudi po uvozu.

   Izberite Dokončaj. Prikaže se pogovorno okno za uvoz podatkov .

9. Izberite mesto, kamor želite dodati podatke: na obstoječi list, na nov list ali v vrtilno tabelo.

10. Izberite V redu.
    Če se želite prepričati, da povezava deluje, vnesite nekaj podatkov in nato izberite »Osvežipovezave>«.

Povezovanje z zbirko podatkov SQL (ODBC)

1. Izberite podatke>iz SQL Server ODBC. Odpre se pogovorno okno »Vzpostavljanje povezave z virom podatkov ODBC« v strežniku SQL Server.

   

2. V polje » Ime strežnika « vnesite strežnik in po želji v polje » Ime zbirke podatkov « vnesite tudi zbirko podatkov.

   Pridobite te informacije pri skrbniku zbirke podatkov.

3. V razdelku »Preverjanje pristnosti« izberite metodo na seznamu: uporabniško ime/geslo, protokol Kerberos ali NTLM.

4. V polji »Uporabniško ime « in »Geslo « vnesite poverilnice.

5. Izberite Poveži. Odpre se pogovorno okno »Krmar «.

6. V levem podoknu se premaknite do želene tabele in jo izberite.

7. Potrdite izjavo SQL v desnem podoknu. Izjavo SQL lahko po želji spremenite.

8. Če si želite ogledati predogled podatkov, izberite »Zaženi«.

9. Ko boste pripravljeni, izberite »Vračilo podatkov«. Prikaže se pogovorno okno za uvoz podatkov .

   

10. Izberite mesto, kamor želite dodati podatke: na obstoječi list, na nov list ali v vrtilno tabelo.

11. Če želite nastaviti lastnosti povezave na zavihkih » Uporaba « in »Definicija « pogovornega okna »Lastnosti «, izberite »Lastnosti«. Ko uvozite podatke, lahko izberete tudi »Podatkovne>povezave« in nato v pogovornem oknu »Lastnosti povezave « izberete »Lastnosti«.

12. Izberite V redu.

13. Če se želite prepričati, da povezava deluje, vnesite nekaj podatkov in nato izberite»Osveživse«>.

Povezovanje z drugo zbirko podatkov (Microsoft Query)

Če želite uporabiti zunanji vir, ki ni zbirka podatkov SQL (na primer FileMaker Pro), lahko uporabite gonilnik ODBC (Open Database Connectivity), ki je nameščen v računalniku Mac. Informacije o gonilnikih so na voljo na tej spletni strani. Ko je gonilnik za vaš vir podatkov nameščen, sledite tem korakom:

1. Izberite podatke>iz zbirke podatkov (Microsoft Query).

2. Dodajte vir podatkov za zbirko podatkov in izberite »V redu«.

3. V poziv za vnos poverilnic za SQL Server vnesite način preverjanja pristnosti, uporabniško ime in geslo.

   

4. Če si želite ogledati zbirke podatkov, na levi strani izberite puščico ob strežniku.

5. Izberite puščico ob želeni zbirki podatkov.

6. Izberite želeno tabelo.

7. Če si želite ogledati predogled podatkov, izberite »Zaženi«.

8. Ko boste pripravljeni, izberite »Vračilo podatkov«.

9. V pogovornem oknu »Uvoz podatkov « izberite, kje naj bodo podatki: na obstoječem listu, na novem listu ali v vrtilni tabeli.

10. Izberite V redu.

11. Če se želite prepričati, da povezava deluje, vnesite nekaj podatkov in nato izberite»Osveživse«>.

Počistite poverilnice za prijavo

Če vaša dovoljenja ne delujejo, jih morda najprej ne počistite in se nato vpišite.

1. Izberite »Podatkovne>povezave«. Odpre se pogovorno okno »Povezave delovnega zvezka «.

2. Na seznamu izberite želeno povezavo in nato izberite »Počisti dovoljenja«.