Excel za Mac vključuje tehnologijo Power Query (imenovano tudi Pridobite & Transform) za zagotavljanje večje zmogljivosti pri uvažanju, osveževanju in omogočanju preverjanja pristnosti virov podatkov, upravljanju virov podatkov Power Query, čiščenju poverilnic, spreminjanju mesta virov podatkov, ki temeljijo na datotekah, in oblikovanju podatkov v tabeli, ki ustreza vašim zahtevam. Ustvarite lahko tudi poizvedbo Power Query s programom VBA.
Opomba: SQL Server podatkov zbirke podatkov je mogoče uvoziti le v različici Insider Beta.
Podatke lahko v Excel uvozite s programom Power Query iz številnih različnih virov podatkov: Excelov delovni zvezek, besedilo/CSV, XML, JSON, SQL Server zbirka podatkov, SharePoint Onlineov seznam, OData, prazna tabela in prazna poizvedba.
-
Izberite Data > Get Data( Pridobi podatke).
-
Če želite izbrati želeni vir podatkov, izberite Pridobi podatke (Power Query).
-
V pogovornem oknu Izbira vira podatkov izberite enega od razpoložljivih virov podatkov.
-
Vzpostavite povezavo z virom podatkov. Če želite izvedeti več o tem, kako vzpostavite povezavo z vsakim virom podatkov, glejte Uvoz podatkov iz virov podatkov.
-
Izberite podatke, ki jih želite uvoziti.
-
Naložite podatke tako, da kliknete gumb Naloži .
Rezultat
Uvoženi podatki se prikaže na novem listu.
Naslednji koraki
Če želite oblikovati in pretvoriti podatke z urejevalnik Power Query, izberite Pretvori podatke. Če želite več informacij, glejte Oblikovanje podatkov z urejevalnik Power Query.
Opomba: Ta funkcija je na splošno na voljo za naročnike na Microsoft 365, ki imajo nameščeno različico 16.69 (23010700) ali novejšo različico Excela za Mac. Če ste naročnik na Microsoft 365, preverite, ali imate najnovejšo različico sistema Office.
Postopek
-
Izberite Podatki > pridobi podatke (Power Query).
-
Če želite odpreti Urejevalnik poizvedb, izberite Zaženi urejevalnik Power Query.
Namig: Do seznama podatkov lahko dostopate Urejevalnik poizvedb tako, da izberete Pridobi podatke (Power Query), izberete vir podatkov in nato kliknete Naprej.
-
Oblikujte in pretvorite podatke z uporabo Urejevalnik poizvedb kot bi to naredili v Excelu za Windows.Power Query pomoč za Excel.
Če želite več informacij, glejte -
Ko končate, izberite Osnovno in > Zapri & naloži.
Rezultat
Novo uvoženi podatki se prikaže na novem listu.
Osvežite lahko te vire podatkov: SharePointove datoteke, SharePointove sezname, SharePointove mape, OData, besedilo/datoteke CSV, Excelove delovne zvezke (.xlsx), datoteke XML in JSON, lokalne tabele in obsege ter zbirko podatkov microsoft SQL Server.
Prvo osveževanje
Ko boste prvič poskusili osvežiti vire podatkov v datotekah v poizvedbah delovnega zvezka, boste morda morali posodobiti pot datoteke.
-
Izberite Podatki,puščico ob možnosti Pridobi podatke in nato Nastavitve vira podatkov. Prikaže se pogovorno okno Nastavitve vira podatkov.
-
Izberite povezavo in nato izberite Spremeni pot datoteke.
-
V pogovornem oknu Pot datoteke izberite novo mesto, nato pa izberite Pridobi podatke.
-
Izberite Zapri.
Osveževanje naslednjih časov
Osvežitev:
-
Vsi viri podatkov v delovnem zvezku izberite Podatki in> Osveži vse.
-
Določen vir podatkov, z desno tipko miške kliknite tabelo poizvedbe na listu in nato izberite Osveži.
-
Vrtilna tabela, izberite celico v vrtilni tabeli, nato pa izberite Analiza vrtilne tabele in > Osveževanje podatkov.
Ko prvič dostopate do SharePointa, SQL Server, OData ali drugih virov podatkov, ki zahtevajo dovoljenje, morate vnesti ustrezne poverilnice. Morda boste želeli počistiti tudi poverilnice in vnesti nove.
Vnesite poverilnice
Ko prvič osvežite poizvedbo, boste morda pozvani k prijavi. Izberite način preverjanja pristnosti in določite poverilnice za prijavo, da vzpostavite povezavo z virom podatkov, ter nadaljujte z osveževanjem.
Če je zahtevana prijava, se prikaže pogovorno okno Vnos poverilnic.
Primer:
-
Poverilnice za SharePoint:
-
SQL Server poverilnice:
Počisti poverilnice
-
Izberite Data > Get Data > Data Source Settings( Pridobi nastavitve vira podatkov).
-
V pogovornem oknuNastavitve vira podatkov izberite želeno povezavo.
-
Na dnu izberite Počisti permissions.
-
Potrdite, da želite narediti to, nato pa izberite Izbriši.
Čeprav avtorstvo v urejevalnik Power Query ni na voljo v Excelu za Mac, VBA ne podpira Power Query avtorstvo. Prenos modula kode VBA v datoteki iz Excela za Windows v Excel za Mac je proces z dvema korakoma. Na koncu tega razdelka je na voljo vzorčni program.
1. korak: Excel za Windows
-
V Excelu v sistemu Windows razvijajte poizvedbe s programom VBA. Koda VBA, ki uporablja te entitete v Excelovem predmetnem modelu, deluje tudi v Excelu za Mac: predmet Poizvedbe, predmet WorkbookQuery, lastnost Workbook.Queries.Če želite več informacij, glejte Sklic programa Excel VBA.
-
V Excelu se prepričajte, da je urejevalnik za Visual Basic odprt tako, da pritisnete ALT+F11.
-
Z desno tipko miške kliknite modul in nato izberite Izvozi datoteko. Prikaže se pogovorno okno Izvoz.
-
Vnesite ime datoteke, preverite, ali je datotečno pripono .bas, nato pa izberite Shrani.
-
Prenesite datoteko VBA v spletno storitev, da omogočite dostop do datoteke iz računalnika Mac.Sinhronizacija datotek s storitvijo OneDrive v sistemu Mac OS X.
Uporabite lahko Microsoft OneDrive. Če želite več informacij, glejte
2. korak: Excel za Mac
-
Prenesite datoteko VBA v lokalno datoteko, datoteko VBA, ki ste jo shranili v »Prvi korak: Excel za Windows« in prenesene v spletno storitev.
-
V Excelu za Mac izberite Orodja za> makrov > Urejevalnik za Visual Basic. Prikaže se okno urejevalnika za Visual Basic .
-
Z desno tipko miške kliknite predmet v oknu programa Project in nato izberite Uvozi datoteko. Prikaže se pogovorno okno Uvozi datoteko.
-
Poiščite datoteko VBA in izberite Odpri.
Vzorčna koda
Tukaj je nekaj osnovnih kod, ki jih lahko prilagodite in uporabljate. 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
-
Odprite Excelov delovni zvezek.
-
Če prejmete varnostno opozorilo o tem, da so zunanje podatkovne povezave onemogočene, izberite Omogoči vsebino.
-
Če se prikaže pogovorno okno Dovoli dostop do datotek, izberite Izberi in nato Dovoli dostop do mape najvišje ravni, ki vsebuje datoteke vira podatkov.
-
Izberite Podatkovni > iz besedila (podedovano). Prikaže se pogovornookno F inder .
-
Poiščite .txt ali .csv in izberite Odpri. Prikaže se čarovnik za uvoz besedila.
Nasvet Večkrat preverite podokno Predogled izbranih podatkov , da potrdite svoje izbire. -
Na prvi strani naredite to:
Vrsta datoteke Če želite izbrati vrsto besedilne datoteke, izberite Ločeno ali Nespremenljiva širina.
Številka vrstice V polju Začni uvozi 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 se uporablja v besedilni datoteki. V večini primerov lahko to nastavitev pustite privzeto. -
Na drugi strani naredite to:
Ločeno Če na prvi strani izberete Ločeno, v razdelku Ločila potrdite znak za ločilo ali pa potrdite polje Drugo, da vnesete znak, ki ni na seznamu. Izberite Obravnavaj zaporedna ločila kot ločila, če podatki vsebujejo ločilo več kot enega znaka med podatkovnimi polji ali če podatki vsebujejo več ločil po meri. V kvalifikatoru besedila izberite znak, ki obdaja vrednosti v besedilni datoteki, ki je najpogosteje narekovaj (").Nespremenljiva širina
Če ste na prvi strani izbrali Nespremenljiva širina, upoštevajte navodila za ustvarjanje, brisanje ali premikanje prelomne vrstice v polju Predogled izbranih podatkov. -
Na tretji strani naredite to:
Za vsak stolpec v razdelku Predogled izbranih podatkov jih izberite in nato po potrebi spremenite v drugo obliko stolpca. Dodatno 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 Uvoz podatkov. -
Izberite mesto, kamor želite dodati podatke: na obstoječem listu, na novem listu ali v vrtilni tabeli.
-
Izberite V redu.
Če želite zagotoviti, da povezava deluje, vnesite nekaj podatkov in nato izberite Povezave,> Osveži.
-
Izberite Podatkovni > odSQL Server ODBC. Prikaže se pogovorno SQL Server Poveži z virom podatkov ODBC.
-
Vnesite strežnik v polje Ime strežnika in po želji vnesite zbirko podatkov v polje Ime zbirke podatkov.
Te informacije dobite pri skrbniku zbirke podatkov. -
V razdelku Preverjanje pristnosti izberite način s seznama: Uporabniško ime/geslo, Kerberos ali NTLM.
-
Vnesite poverilnice v polji Uporabniško imein Geslo.
-
Izberite Poveži. Prikaže se pogovorno okno Krmar.
-
V levem podoknu se premaknite do želene tabele in jo izberite.
-
Potrdite izjavo SQL v desnem podoknu. Izjavo SQL lahko spremenite po svoje.
-
Če si želite ogledati predogled podatkov, izberite Zaženi.
-
Ko ste pripravljeni, izberite Vrni podatke. Prikaže se pogovorno okno Uvoz podatkov.
-
Izberite mesto, kamor želite dodati podatke: na obstoječem listu, na novem listu ali v vrtilni tabeli.
-
Če želite na zavihkih Uporaba in definicija v pogovornem oknu Lastnosti nastaviti lastnosti povezave, izberite Lastnosti. Ko uvozite podatke, lahko izberete tudi podatkovne > povezave in nato v pogovornem oknu Lastnosti povezave izberete Lastnosti.
-
Izberite V redu.
-
Če želite zagotoviti, da povezava deluje, vnesite nekaj podatkov in nato izberite Možnost> Osveži vse.
Č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 namestite gonilnik za vir podatkov, sledite tem korakom:
-
Izberite Podatkovni > iz zbirke podatkov (Microsoft Query).
-
Dodajte vir podatkov za zbirko podatkov in nato izberite V redu.
-
V poziv SQL Server vnesite način preverjanja pristnosti, uporabniško ime in geslo.
-
Na levi strani izberite puščico ob strežniku, da si ogledate zbirke podatkov.
-
Izberite puščico zraven želene zbirke podatkov.
-
Izberite želeno tabelo.
-
Če si želite ogledati predogled podatkov, izberite Zaženi.
-
Ko ste pripravljeni, izberite Vrni podatke.
-
V pogovornem oknu Uvoz podatkov izberite mesto, kjer želite imeti podatke: na obstoječem listu, na novem listu ali v vrtilni tabeli.
-
Izberite V redu.
-
Če želite zagotoviti, da povezava deluje, vnesite nekaj podatkov in nato izberite Možnost> Osveži vse.
Če vaša dovoljenja ne delujejo, jih morda ne boste morali najprej počistiti in se nato prijaviti.
-
Izberite Podatkovne > povezave. Prikaže se pogovorno okno Povezave delovnega zvezka.
-
Na seznamu izberite želeno povezavo in nato počisti dovoljenja.
Glejte tudi
Gonilniki ODBC, ki so združljivi s programom Excel for Mac
Ustvarjanje vrtilne tabele za analizo podatkov na delovnem listu