Importování a vlastnosti obrazce v Excelu pro Mac (Power Query)

Importovat zdroje dat

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.

1. Vyberte Data>Get Data.

   

2. Pokud chcete vybrat požadovaný zdroj dat, vyberte Získat data (Power Query).

3. V dialogovém okně Zvolit zdroj dat vyberte jeden z dostupných zdrojů dat.

   

4. 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.

5. Zvolte data, která chcete importovat.

6. 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.

Tvarovat data pomocí editoru Power Query

Procedura:

1. Vyberte Data>získat data (Power Query).

2. Pokud chcete otevřít Editor Power Query, vyberte 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ší.

3. Data můžete tvarovat a transformovat pomocí Editoru dotazů stejně jako v Excelu pro Windows.

   

   Další informace najdete v Power Query nápovědy aplikace Excel.

4. Až budete hotovi, vyberte Domů>Zavřít & Načíst.

Výsledek

Nově importovaná data se zobrazí na novém listu.

Aktualizovat zdroje dat

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, databáze Microsoft SQL Server a složky.

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.

1. Vyberte data, šipku vedle položky Získat data, a pak nastavení zdroje dat. Zobrazí se dialogové okno nastavení zdroje dat.
2. Vyberte připojení a pak vyberte Změnit cestu k souboru.
3. V dialogovém okně Cesta k souboru vyberte nové umístění a pak vyberte Získat data.
4. 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 Kontingenční tabulka Analyzovat>,Aktualizovat data.

Zadejte a vymažte přihlašovací údaje.

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 pro zadání přihlašovacích údajů .

Příklady:

• Přihlašovací údaje služby SharePoint:

  

• SQL Server přihlašovací údaje:

  

Vymazat přihlašovací údaje

1. Vyberte Nastavenízdroje datpro získání dat>>.
2. V dialogovém okně Nastavení zdroje datvyberte požadované připojení.
3. Dole vyberte Vymazat oprávnění.
4. Potvrďte, že to chcete udělat, a pak vyberte Odstranit.

Vytvoření a přenos Power Query kódu jazyka VBA

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

1. 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 aplikaci Excel pro Mac: objekt Dotazy, objekt WorkbookQuery, vlastnost Workbook.Queries. Další informace najdete v referenčních informacích k jazyku VBA Excelu.

2. V Excelu se stisknutím kombinace kláves ALT+F11 ujistěte, že je Visual Basic Editor otevřený.

3. Klikněte pravým tlačítkem na modul a pak vyberte Exportovat soubor. Zobrazí se dialogové okno Exportovat .

4. Zadejte název souboru, ujistěte se, že přípona souboru je .bas, a pak vyberte Uložit.

5. Nahrajte soubor VBA do online služby, aby byl soubor přístupný z Macu.

   Můžete použít Microsoft OneDrive. Další informace najdete v tématu Synchronizace souborů s OneDrivem na Mac OS X.

Krok 2: Excel pro Mac

1. 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.
2. V aplikaci Excel pro Mac vyberte Nástroje>– Visual>Basic Editor. Zobrazí se okno Visual Basic Editor.
3. V okně Projektu klikněte pravým tlačítkem na objekt a pak vyberte Importovat soubor. Zobrazí se dialogové okno Zvolit soubor.
4. 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

Import a aktualizace souborů TXT nebo CSV

1. Otevřete excelový sešit.

2. Pokud se zobrazí upozornění zabezpečení týkající se zakázání externích datových připojení, vyberte Povolit obsah.

3. 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.

4. Vyberte Data>z textu (starší verze). Zobrazí se dialogové okno Finder .

5. Vyhledejte .txt nebo .csv soubor a vyberte Otevřít. Zobrazí se Průvodce importem textu .

   Tip Volby potvrďte opakovaným zaškrtnutím podokna náhledu vybraných dat .

6. Na první stránce postupujte takto:
   Typ souboru Pokud chcete zvolit typ textového souboru, vyberte možnost s oddělovači nebo pevnou šířkou.

   Číslo řádku V zahájit import na řádku vyberte číslo řádku a zadejte první řádek dat, která chcete importovat.

   Znaková sada V poli Původ 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í.

7. Na druhé stránce postupujte takto:

   S oddělovači

   Pokud jste na první stránce vybrali možnost Oddělovače , vyberte v části Oddělovače 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 (").
   Pevná délka

   Pokud jste na první stránce vybrali možnost Pevná šířka , postupujte podle pokynů pro vytvoření, odstranění nebo přesunutí čáry konce v poli Náhled vybraných dat .

8. Na třetí stránce postupujte takto:

   Vyberte každý sloupec v náhledu vybraných dat a v případě potřeby změňte formát na jiný sloupec. Můžete dále nastavit formát data a vybrat Upřesnit pro změnu nastavení číselných dat. Data můžete převést i po importu.

   Vyberte Dokončit. Zobrazí se dialogové okno Import dat.

9. Zvolte, kam chcete data přidat: buď na existující list, na nový list, nebo v kontingenční tabulce.

10. Vyberte OK.
    Pokud chcete zajistit, aby připojení fungovalo, zadejte nějaká data a pak vyberte Aktualizovat připojení>.

Připojení k SQL Database (ODBC)

1. Vyberte data>z rozhraní SQL Server ODBC. Otevře se dialogové okno Připojit ke zdroji dat ODBC serveru SQL.

   

2. 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.

3. V části ověřovánívyberte ze seznamu metodu: uživatelské jméno/heslo, Kerberos nebo NTLM.

4. Zadejte přihlašovací údaje do polí Uživatelské jméno a Heslo .

5. Vyberte Připojit. Zobrazí se dialogové okno Navigátor .

6. V levém podokně přejděte na požadovanou tabulku a vyberte ji.

7. Potvrďte příkaz SQL v pravém podokně. Příkaz SQL můžete podle potřeby změnit.

8. Pokud chcete zobrazit náhled dat, vyberte Spustit.

9. Až budete připravení, vyberte Vrátit data. Zobrazí se dialogové okno Import dat.

   

10. Zvolte, kam chcete data přidat: buď na existující list, na nový list, nebo v kontingenční tabulce.

11. Chcete-li nastavit vlastnosti spojení na kartách Použití a Definice v dialogovém okně Vlastnosti , vyberte Vlastnosti. Po importu dat můžete také vybrat datová>připojení a potom v dialogovém okně Vlastnosti připojení vybrat Vlastnosti.

12. Vyberte OK.

13. Pokud chcete zajistit, aby připojení fungovalo, zadejte nějaká data a pak vyberteData Update All (Aktualizovat> vše).

Připojení k jiné databázi (Microsoft Query)

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:

1. Výběr dat>z databáze (Microsoft Query)

2. Přidejte zdroj dat pro databázi a pak vyberte OK.

3. Na příkazovém řádku SQL Server přihlašovacích údajů zadejte metodu ověřování, uživatelské jméno a heslo.

   

4. Vlevo vyberte šipku vedle serveru a zobrazte databáze.

5. Vyberte šipku vedle požadované databáze.

6. Vyberte požadovanou tabulku.

7. Pokud chcete zobrazit náhled dat, vyberte Spustit.

8. Až budete připravení, vyberte Vrátit data.

9. 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.

10. Vyberte OK.

11. Pokud chcete zajistit, aby připojení fungovalo, zadejte nějaká data a pak vyberteData Update All (Aktualizovat> vše).

Vymazat přihlašovací údaje

Pokud vaše oprávnění nefungují, je možné, že je nejdříve nevymažete a pak se přihlásíte.

1. Vyberte možnost Datová>připojení. Zobrazí se dialogové okno Připojení sešitu.

2. V seznamu vyberte požadované připojení a pak vyberte Vymazat oprávnění.