Excel pro Mac zahrnuje technologii Power Query (označovanou také jako Získat a transformovat), která poskytuje větší možnosti při importu, aktualizaci a ověřování zdrojů dat, správě Power Query zdrojů dat, vymazání přihlašovacích údajů, změně umístění souborových zdrojů dat a tvarování dat do tabulky, která vyhovuje vašim požadavkům. Dotaz Power Query můžete vytvořit také pomocí jazyka VBA.
Poznámka: SQL Server Zdroj dat databáze je možné importovat pouze v programu Insider Beta.
Data můžete do Excelu importovat pomocí Power Query z nejrůznějších zdrojů dat: Excel Workbook, Text/CSV, XML, JSON, SQL Server Database, SharePoint Online List, OData, Blank Table a Blank Query.
-
Vyberte Data > Získat data.
-
Pokud chcete vybrat požadovaný zdroj dat, vyberte Získat data (Power Query).
-
V dialogovém okně Zvolte zdroj dat a vyberte jeden z dostupných zdrojů dat.
-
Připojte se ke zdroji dat. Další informace o tom, jak se připojit k jednotlivým zdrojům dat, najdete v tématu Import dat ze zdrojů dat.
-
Zvolte data, která chcete importovat.
-
Načtěte data kliknutím na tlačítko Načíst .
Výsledek
Importovaná data se zobrazí na novém listu.
Další kroky
Pokud chcete data tvarovat a transformovat pomocí editoru Power Query, vyberte Transformovat data. Další informace najdete v tématu data obrazců pomocí editoru Power Query.
Poznámka: Tato funkce je obecně dostupná pro předplatitele Microsoft 365 verze 16.69 (23010700) nebo novější v Excelu pro Mac. Pokud jste předplatitelem Microsoft 365, ujistěte se, že máte nejnovější verzi Office.
Postup
-
Vyberte Data > Načíst data (Power Query).
-
Editor dotazů otevřete tak, že vyberete Spustit editor Power Query.
Tip: K Editoru dotazů se dostanete také tak, že vyberete Získat data (Power Query), zvolíte zdroj dat a kliknete na Další.
-
Data můžete tvarovat a transformovat pomocí Editoru dotazů stejně jako v Excelu pro Windows.Power Query nápovědy aplikace Excel.
Další informace najdete v -
Až budete hotovi, vyberte Domovská stránka > Zavřít a Načíst.
Výsledek
Nově importovaná data se zobrazí na novém listu.
Můžete aktualizovat následující zdroje dat: sharepointové soubory, sharepointové seznamy, sharepointové složky, OData, textové soubory/soubory CSV, excelové sešity (.xlsx), soubory XML a JSON, místní tabulky a oblasti a Microsoft SQL Server databázi.
Aktualizovat poprvé
Při prvním pokusu o aktualizaci souborových zdrojů dat v dotazech sešitu bude pravděpodobně nutné aktualizovat cestu k souboru.
-
Vyberte data, šipku vedle položky Získat data, a pak nastavení zdroje dat. Zobrazí se dialogové okno nastavení zdroje dat.
-
Vyberte připojení a pak vyberte Změnit cestu k souboru.
-
V dialogovém okně cesta k souboru vyberte nové umístění a pak vyberte Získat data.
-
Vyberte Zavřít.
Aktualizovat následující časy
Postup aktualizace:
-
Všechny zdroje dat v sešitu vyberte Data > Aktualizovat vše.
-
Konkrétní zdroj dat, klikněte pravým tlačítkem myši na tabulku dotazu na listu a pak vyberte Aktualizovat.
-
Kontingenční tabulka, vyberte buňku v kontingenční tabulce a pak vyberte Analýza kontingenční tabulky > Aktualizovat data.
Při prvním přístupu k SharePointu, SQL Server, OData nebo jiným zdrojům dat, které vyžadují oprávnění, musíte zadat příslušné přihlašovací údaje. Můžete také vymazat přihlašovací údaje a zadat nové.
Zadejte přihlašovací údaje.
Při první aktualizaci dotazu se může zobrazit výzva k přihlášení. Vyberte metodu ověřování a zadejte přihlašovací údaje pro připojení ke zdroji dat a pokračujte v aktualizaci.
Pokud se vyžaduje přihlášení, zobrazí se dialogové okno Zadejte přihlašovací údaje .
Příklady:
-
Přihlašovací údaje služby SharePoint:
-
SQL Server přihlašovací údaje:
Vymazat přihlašovací údaje
-
Vyberte data > Načíst data > Nastavení zdroje dat.
-
V dialogovém okně Nastavení zdroje datvyberte požadované připojení.
-
V dolní části vyberte Vymazat Permissions.
-
Potvrďte, že to chcete udělat, a pak vyberte Odstranit.
I když vytváření obsahu v editoru Power Query není v Excelu pro Mac k dispozici, jazyk VBA podporuje vytváření Power Query. Přenos modulu kódu VBA v souboru z Excelu pro Windows do Excelu pro Mac je dvoustupňový proces. Na konci této části vám poskytneme ukázkový program.
Krok 1: Excel pro Windows
-
V Excelu pro Windows vyvíjejte dotazy pomocí jazyka VBA. Kód jazyka VBA, který používá následující entity v objektovém modelu Excelu, funguje také v Excelu pro Mac: objekt Dotazy, Objekt WorkbookQuery, Workbook.Queries – vlastnost.Další informace najdete v referenčních informacích k jazyku VBA aplikace Excel.
-
V Excelu se stisknutím kombinace kláves ALT+F11 ujistěte, že je Visual Basic Editor otevřený.
-
Klikněte pravým tlačítkem na modul a pak vyberte Exportovat soubor. Zobrazí se dialogové okno Exportovat.
-
Zadejte název souboru, ujistěte se, že přípona souboru je .bas, a pak vyberte Uložit.
-
Nahrajte soubor VBA do online služby, aby byl soubor přístupný z Macu.Synchronizace souborů s OneDrivem na Mac OS X.
Můžete použít Microsoft OneDrive. Další informace najdete v tématu
Krok 2: Excel pro Mac
-
Stáhněte si soubor VBA do místního souboru, do souboru VBA, který jste si uložili v kroku 1: Excel pro Windows, a nahrajte ho do online služby.
-
V Excelu pro Mac vyberte Nástroje > makro > Visual Basic Editor. Zobrazí se okno Visual Basic Editor.
-
V okně Projektu klikněte pravým tlačítkem na objekt a pak vyberte Importovat soubor. Zobrazí se dialogové okno Zvolit soubor.
-
Vyhledejte soubor VBA a pak vyberte Otevřít.
Ukázkový kód
Tady je několik základních kódů, které můžete přizpůsobit a použít. Toto je ukázkový dotaz, který vytvoří seznam 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
-
Otevřete excelový sešit.
-
Pokud se zobrazí upozornění zabezpečení týkající se zakázání externích datových připojení, vyberte Povolit obsah.
-
Pokud se zobrazí dialogové okno Udělit přístup k souboru, vyberte Vybrat, a pak vyberte Udělit přístup do složky nejvyšší úrovně obsahující soubory zdroje dat.
-
Vyberte Data > dat z textu (starší verze). Zobrazí se dialogové okno Finder .
-
Vyhledejte soubor .txt nebo .csv a vyberte Otevřít. Zobrazí se Průvodce importem textu.
Tip Možnosti potvrďte opakovaným zaškrtnutím podokna náhled vybraných dat. -
Na první stránce postupujte takto:
Typ souboru Pokud chcete zvolit typ textového souboru, vyberte Oddělovač nebo Pevná šířka.
Číslo řádku V zahájit import na řádku vyberte číslo řádku a zadejte první řádek dat, která chcete importovat. znakové sady V původu souboru vyberte znakovou sadu, která se používá v textovém souboru. Ve většině případů můžete toto nastavení ponechat ve výchozím nastavení. -
Na druhé stránce postupujte takto:
Oddělené Pokud jste na první stránce zvolili oddělené v části Oddělovače, vyberte znak oddělovače nebo pomocí zaškrtávacího políčka Jiné zadejte znak, který není uvedený v seznamu. Vyberte Považovat po sobě jdoucí oddělovače za jednu, pokud data obsahují oddělovač více než jednoho znaku mezi datovými poli nebo pokud data obsahují více vlastních oddělovačů. V textovém kvalifikátoru vyberte znak, který uzavře hodnoty do textového souboru, což je nejčastěji znak uvozovek (").s pevnou šířkou
Pokud jste na první stránce zvolili Pevná šířka , postupujte podle pokynů k vytvoření, odstranění nebo přesunutí zalomení řádku v poli Náhled vybraných dat . -
Na třetí stránce postupujte takto:
Pro každý sloupec v části Náhled vybraných datho vyberte a v případě potřeby ho změňte na jiný formát sloupce. Můžete dále nastavit formát data a vybrat Upřesnit a změnit nastavení číselných dat. Data můžete převést i po importu. Vyberte dokončit. Zobrazí se dialogové okno Import dat. -
Zvolte, kam chcete data přidat: buď na existující list, na nový list, nebo v kontingenční tabulce.
-
Vyberte OK.
Pokud chcete zajistit, aby připojení fungovalo, zadejte nějaká data a pak vyberte Připojení > Aktualizovat.
-
Vyberte data > z SQL Server ODBC. Otevře se dialogové okno Připojit ke zdroji dat ODBC serveru SQL.
-
Do pole název serveru zadejte server a volitelně zadejte databázi do pole název databáze.
Tyto informace získáte od správce databáze. -
V části ověřovánívyberte ze seznamu metodu: uživatelské jméno/heslo, Kerberos nebo NTLM.
-
Do polí Uživatelské jméno a Heslo zadejte přihlašovací údaje.
-
Vyberte Připojit. Zobrazí se dialogové okno Navigátor.
-
V levém podokně přejděte na požadovanou tabulku a vyberte ji.
-
Potvrďte příkaz SQL v pravém podokně. Příkaz SQL můžete podle potřeby změnit.
-
Pokud chcete zobrazit náhled dat, vyberte Spustit.
-
Až budete připravení, vyberte Vrátit data. Zobrazí se dialogové okno Import dat.
-
Zvolte, kam chcete data přidat: buď na existující list, na nový list, nebo v kontingenční tabulce.
-
Pokud chcete nastavit vlastnosti Použití a Definice na kartách Vlastnosti v dialogovém okně vyberte Vlastnosti. Po importu dat můžete také vybrat data > připojení, a potom v dialogovém okně Vlastnosti připojení vyberte Vlastnosti.
-
Vyberte OK.
-
Pokud chcete zajistit, aby připojení fungovalo, zadejte nějaká data a pak vyberte Data > Aktualizovat vše.
Pokud chcete použít externí zdroj, který nění SQL Database (například FileMaker Pro), můžete použít ovladač ODBC (Open Database Connectivity) nainstalovaný na Macu. Informace o ovladačích najdete na této webové stránce. Po instalaci ovladače zdroje dat postupujte takto:
-
Vyberte data > z databáze (Microsoft Query).
-
Přidejte zdroj dat pro databázi a pak vyberte OK.
-
Na příkazovém řádku SQL Server přihlašovacích údajů zadejte metodu ověřování, uživatelské jméno a heslo.
-
Vlevo vyberte šipku vedle serveru a zobrazte databáze.
-
Vyberte šipku vedle požadované databáze.
-
Vyberte požadovanou tabulku.
-
Pokud chcete zobrazit náhled dat, vyberte Spustit.
-
Až budete připravení, vyberte Vrátit data.
-
V dialogovém okně Importovat data zvolte umístění dat: buď na existujícím listu, na novém listu, nebo v kontingenční tabulce.
-
Vyberte OK.
-
Pokud chcete zajistit, aby připojení fungovalo, zadejte nějaká data a pak vyberte Data > Aktualizovat vše.
Pokud vaše oprávnění nefungují, je možné, že je nejdříve nevymažete a pak se přihlásíte.
-
Vyberte Data > Connections. Zobrazí se dialogové okno Připojení sešitu.
-
V seznamu vyberte požadované připojení a pak vyberte Vymazat oprávnění.
Viz také
Nápověda pro doplněk Power Query pro Excel