Excel pre Mac zahŕňa technológiu Power Query (označovanú tiež ako Získať a transformovať), ktorá poskytuje väčšie možnosti pri importe, aktualizácii a overovaní zdrojov dát, správe Power Query zdrojov dát, vymazaní prihlasovacích údajov, zmene umiestnenia súborových zdrojov dát a tvarovaní dát do tabuľky, ktorá vyhovuje vašim požiadavkám. Môžete tiež vytvoriť Power Query pomocou jazyka VBA.
Poznámka: SQL Server Zdroj údajov databázy je možné importovať iba v programe Insider Beta.
Dáta môžete do Excelu importovať pomocou Power Query z najrôznejších zdrojov dát: Excel Workbook, Text/CSV, XML, JSON, SQL Server Database, SharePoint Online List, OData, Blank Table a Blank Query.
-
Vyberte Údaje > Získať údaje.
-
Ak chcete vybrať požadovaný zdroj údajov, vyberte Získať údaje (Power Query).
-
V dialógovom okne Vybrať zdroj údajov a vyberte jeden z dostupných zdrojov údajov.
-
Pripojte sa k zdroju údajov. Ďalšie informácie o tom, ako sa pripojiť k jednotlivým zdrojom údajov, nájdete v téme Import údajov zo zdrojov údajov.
-
Vyberte údaje, ktoré chcete importovať.
-
Načítajte údaje kliknutím na tlačidlo Načítať.
Výsledok
Importované údaje sa zobrazia na novom hárku.
Ďalšie kroky
Ak chcete údaje tvarovať a transformovať pomocou editora Power Query, vyberte Transformovať údaje. Ďalšie informácie nájdete v téme Vlastnosti tvaru pomocou editora Power Query.
Poznámka: Táto funkcia je všeobecne dostupná pre predplatiteľov Microsoft 365 verzie 16.69 (23010700) alebo novšia v Exceli pre Mac. Ak ste predplatiteľom služieb Microsoft 365, uistite sa, že používate najnovšiu verziu balíka Office.
Postup
-
Vyberte Údaje > Načítať údaje (Power Query).
-
Editor otázok otvoríte tak, že vyberiete Spustiť editor Power Query.
Tip: K Editoru dotazov sa dostanete tiež tak, že vyberiete Získať dáta (Power Query), zvolíte zdroj dát a kliknete na Ďalší.
-
Dáta môžete tvarovať a transformovať pomocou Editora dotazov rovnako ako v Exceli pre Windows.Power Query pomocníka programu Excel.
Ďalšie informácie nájdete v -
Keď budete hotoví, vyberte Domovská stránka > Zavrieť a Načítať.
Výsledok
Novo importované dáta sa zobrazia na novom liste.
Môžete aktualizovať nasledujúce zdroje údajov: sharepointové súbory, sharepointové zoznamy, sharepointové zložky, OData, textové súbory/súbory CSV, excelové zošity (.xlsx), súbory XML a JSON, miestne tabuľky a oblasti a Microsoft SQL Server databázu.
Aktualizovať prvýkrát
Pri prvom pokuse o aktualizáciu súborových zdrojov údajov v dotazoch zošita bude pravdepodobne potrebné aktualizovať cestu k súboru.
-
Vyberte údaje, šípku vedľa položky Získať dáta, a potom nastavenie zdroja údajov. Zobrazí sa dialógové okno nastavenie zdroja údajov.
-
Vyberte pripojenie a potom vyberte Zmeniť cestu k súboru.
-
V dialógovom okne cesta k súboru vyberte nové umiestnenie a potom vyberte Získať údaje.
-
Vyberte položku Zavrieť.
Aktualizovať nasledujúce časy
Postup aktualizácie:
-
Všetky zdroje údajov v zošite vyberte Data > Aktualizovať všetko.
-
Konkrétny zdroj údajov, kliknite pravým tlačidlom myši na tabuľku dotazu na liste a potom vyberte Aktualizovať.
-
Kontingenčná tabuľka, vyberte bunku v kontingenčnej tabuľke a potom vyberte Analýza kontingenčnej tabuľky > Aktualizovať údaje.
Pri prvom prístupe k SharePointu, SQL Serveru, OData alebo iným zdrojom údajov, ktoré vyžadujú oprávnenie, musíte zadať príslušné prihlasovacie údaje. Môžete tiež vymazať prihlasovacie údaje a zadať nové.
Zadajte prihlasovacie údaje.
Pri prvej aktualizácii dotazu sa môže zobraziť výzva na prihlásenie. Vyberte metódu overovania a zadajte prihlasovacie údaje pre pripojenie k zdroju dát a pokračujte v aktualizácii.
Ak sa vyžaduje prihlásenie, zobrazí sa dialógové okno Zadajte prihlasovacie údaje .
Príklad:
-
Prihlasovacie údaje služby SharePoint:
-
SQL Server prihlasovacie údaje:
Vymazať prihlasovacie údaje
-
Vyberte údaje > Načítať údaje > Nastavenie zdroja údajov.
-
V dialógovom okne Nastavenia zdroja dátvyberte požadované pripojenie.
-
V dolnej časti vyberte Vymazať Permissionna.
-
Potvrďte, že to chcete urobiť, a potom vyberte Odstrániť.
Aj keď vytváranie obsahu v editore Power Query nie je v Exceli pre Mac k dispozícii, jazyk VBA podporuje vytváranie Power Query. Prenos modulu kódu VBA v súbore z Excelu pre Windows do Excelu pre Mac je dvojstupňový proces. Na konci tejto časti vám poskytneme ukážkový program.
Krok 1: Excel pre Windows
-
V Exceli pre Windows vyvíjajte otázky pomocou jazyka VBA. Kód jazyka VBA, ktorý používa nasledujúce entity v objektovom modeli Excelu, funguje aj v Exceli pre Mac: objekt Dotazy, Objekt WorkbookQuery, Workbook.Queries – vlastnosť.Ďalšie informácie nájdete v referenčných informáciách k jazyku VBA programu Excel.
-
V Exceli sa stlačením kombinácie klávesov ALT+F11 uistite, že je Visual Basic Editor otvorený.
-
Kliknite pravým tlačidlom na modul a potom vyberte Exportovať súbor. Zobrazí sa dialógové okno Exportovať.
-
Zadajte názov súboru, uistite sa, že prípona súboru je .bas, a potom vyberte Uložiť.
-
Nahrajte súbor VBA do online služby, aby bol súbor prístupný z Macu.Synchronizácia súborov s OneDrivom na Mac OS X.
Môžete použiť Microsoft OneDrive. Ďalšie informácie nájdete v téme
Krok 2: Excel pre Mac
-
Stiahnite si súbor VBA do miestneho súboru, do súboru VBA, ktorý ste si uložili v kroku 1: Excel pre Windows, a nahrajte ho do online služby.
-
V Exceli pre Mac vyberte Nástroje > makro > Visual Basic Editor. Zobrazí sa okno Visual Basic Editor.
-
V okne Projektu kliknite pravým tlačidlom na objekt a potom vyberte Importovať súbor. Zobrazí sa dialógové okno Zvoliť súbor.
-
Vyhľadajte súbor VBA a potom vyberte Otvoriť.
Ukážkový kód
Tu je niekoľko základných kódov, ktoré môžete prispôsobiť a použiť. Toto je ukážkový dotaz, ktorý vytvorí zoznam s hodnotami 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
-
Otvorte excelový zošit.
-
Ak sa zobrazí upozornenie zabezpečenia týkajúce sa zakázania externých dátových pripojení, vyberte Povoliť obsah .
-
Ak sa zobrazí dialógové okno Udeliť prístup k súboru, vyberte Vybrať, a potom vyberte Udeliť prístup do zložky najvyššej úrovne obsahujúcej súbory zdroja údajov.
-
Vyberte Dáta > dať z textu (staršia verzia). Zobrazí sa dialógové okno Finder .
-
Vyhľadajte súbor .txt alebo .csv a vyberte Otvoriť. Zobrazí sa Sprievodca importom textu.
Tip Možnosti potvrďte opakovaným začiarknutím table náhľad vybraných dát. -
Na prvej stránke postupujte takto:
Typ súboru Ak chcete zvoliť typ textového súboru, vyberte Oddeľovač alebo Pevná šírka.
Číslo riadka V začať import na riadku vyberte číslo riadka a zadajte prvý riadok dát, ktoré chcete importovať. Tabuľka znakov V pôvode súboru vyberte tabuľku znakov, ktorá sa používa v textovom súbore. Vo väčšine prípadov môžete toto nastavenie ponechať v predvolenom nastavení. -
Na druhej stránke postupujte takto:
Oddelené Ak ste na prvej stránke zvolili oddelené v časti Oddeľovače, vyberte znak oddeľovača alebo pomocou začiarkavacieho políčka Iné zadajte znak, ktorý nie je uvedený v zozname. Vyberte Považovať po sebe idúce oddeľovače za jednu, ak dáta obsahujú oddeľovač viac ako jedného znaku medzi dátovými poľami alebo ak dáta obsahujú viac vlastných oddeľovačov. V textovom kvalifikátore vyberte znak, ktorý uzavrie hodnoty do textového súboru, čo je najčastejšie znak úvodzoviek (").S pevnou šírkou
Ak ste na prvej stránke zvolili Pevná šírka , postupujte podľa pokynov na vytvorenie, odstránenie alebo presunutie zalomenia riadka v poli Náhľad vybraných údajov. -
Na tretej stránke postupujte takto:
Pre každý stĺpec v časti Náhľad vybraných dát ho vyberte av prípade potreby ho zmeňte na iný formát stĺpca. Môžete ďalej nastaviť formát dátumu a vybrať Upresniť a zmeniť nastavenie číselných dát. Dáta môžete previesť aj po importe. Vyberte Dokončiť. Zobrazí sa dialógové okno Import dát. -
Zvoľte, kam chcete dáta pridať: buď na existujúci list, na nový list, alebo v kontingenčnej tabuľke.
-
Vyberte tlačidlo OK.
Ak chcete zaistiť, aby pripojenie fungovalo, zadajte nejaké dáta a potom vyberte Pripojenie > Aktualizovať.
-
Vyberte Údaje > z SQL Server ODBC. Zobrazí sa dialógové okno Pripojenie k zdroju údajov ODBC servera SQL.
-
Do poľa názov servera zadajte server a voliteľne zadajte databázu do poľa názov databázy.
Tieto informácie získate od správcu databázy. -
V časti Overenievyberte zo zoznamu metódu: meno používateľa/heslo, Kerberos alebo NTLM.
-
Do polí Meno používateľa a Heslo zadajte prihlasovacie údaje.
-
Vyberte položku Pripojiť. Zobrazí sa dialógové okno Navigátor.
-
Na ľavej table prejdite na požadovanú tabuľku a vyberte ju.
-
Potvrďte príkaz SQL na pravej table. Príkaz SQL môžete podľa potreby zmeniť.
-
Ak si chcete pozrieť náhľad údajov, vyberte Spustiť.
-
Keď budete pripravení, vyberte Vrátiť údaje. Zobrazí sa dialógové okno Import dát.
-
Zvoľte, kam chcete dáta pridať: buď na existujúci list, na nový list, alebo v kontingenčnej tabuľke.
-
Ak chcete nastaviť vlastnosti Použitie a Definícia na kartách Vlastnosti v dialógovom okne vyberte Vlastnosti. Po importe dát môžete tiež vybrať data > pripojenia, a potom v dialógovom okne Vlastnosti pripojenia vyberte Vlastnosti.
-
Vyberte tlačidlo OK.
-
Ak chcete zaistiť, aby pripojenie fungovalo, zadajte nejaké dáta a potom vyberte Data > Aktualizovať všetko.
Ak chcete použiť externý zdroj, ktorý nenie SQL Database (napríklad FileMaker Pro), môžete použiť ovládač ODBC (Open Database Connectivity) nainštalovaný na Macu. Informácie o ovládačoch sú k dispozícii na tejto webovej stránke. Po inštalácii ovládača zdroja dát postupujte takto:
-
Vyberte data > z databázy (Microsoft Query).
-
Pridajte zdroj údajov pre databázu a potom vyberte OK.
-
Na príkazovom riadku SQL Server prihlasovacích údajov zadajte metódu overovania, užívateľské meno a heslo.
-
Vľavo vyberte šípku vedľa servera a zobrazte databázy.
-
Vyberte šípku vedľa požadovanej databázy.
-
Vyberte požadovanú tabuľku.
-
Ak chcete zobraziť zobrazenie údajov, vyberte Spustiť.
-
Keď budete pripravení, vyberte Vrátiť údaje.
-
V dialógovom okne Importovať dáta zvoľte umiestnenie dát: buď na existujúcom liste, na novom liste, alebo v kontingenčnej tabuľke.
-
Vyberte položku OK.
-
Ak chcete zaistiť, aby pripojenie fungovalo, zadajte nejaké dáta a potom vyberte Data > Aktualizovať všetko.
Ak vaše oprávnenia nefungujú, je možné, že ich najskôr nevymažete a potom sa prihlásite.
-
Vyberte Údaje> Pripojenia. Zobrazí sa dialógové okno Pripojenia zošita.
-
V zozname vyberte požadované pripojenie a potom vyberte Vymazať povolenia.
Pozrite tiež
Pomocník doplnku Power Query pre Excel