Importovanie a tvarovanie údajov v Exceli pre Mac (Power Query)

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.

1. Vyberte Údaje > Získať údaje.
   
   

2. Ak chcete vybrať požadovaný zdroj údajov, vyberte Získať údaje (Power Query).

3. V dialógovom okne Vybrať zdroj údajov a vyberte jeden z dostupných zdrojov údajov. 
   
   

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

5. Vyberte údaje, ktoré chcete importovať.

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

Postup

1. Vyberte Údaje > Načítať údaje (Power Query).

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

3. Dáta môžete tvarovať a transformovať pomocou Editora dotazov rovnako ako v Exceli pre Windows.
   
   
   
   Ďalšie informácie nájdete v Power Query pomocníka programu Excel.

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

1. Vyberte údaje, šípku vedľa položky Získať dáta, a potom nastavenie zdroja údajov. Zobrazí sa dialógové okno nastavenie zdroja údajov.

2. Vyberte pripojenie a potom vyberte Zmeniť cestu k súboru.

3. V dialógovom okne cesta k súboru vyberte nové umiestnenie a potom vyberte Získať údaje.

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

1. Vyberte údaje > Načítať údaje > Nastavenie zdroja údajov.

2. V dialógovom okne Nastavenia zdroja dátvyberte požadované pripojenie.

3. V dolnej časti vyberte Vymazať Permissionna.

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

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

2. V Exceli sa stlačením kombinácie klávesov ALT+F11 uistite, že je Visual Basic Editor otvorený.

3. Kliknite pravým tlačidlom na modul a potom vyberte Exportovať súbor. Zobrazí sa dialógové okno Exportovať.

4. Zadajte názov súboru, uistite sa, že prípona súboru je .bas, a potom vyberte Uložiť.

5. Nahrajte súbor VBA do online služby, aby bol súbor prístupný z Macu.
   
   Môžete použiť Microsoft OneDrive. Ďalšie informácie nájdete v téme Synchronizácia súborov s OneDrivom na Mac OS X.

Krok 2: Excel pre Mac

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

2. V Exceli pre Mac vyberte Nástroje > makro > Visual Basic Editor. Zobrazí sa okno Visual Basic Editor.

3. V okne Projektu kliknite pravým tlačidlom na objekt a potom vyberte Importovať súbor. Zobrazí sa dialógové okno Zvoliť súbor.

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

1. Otvorte excelový zošit.

2. Ak sa zobrazí upozornenie zabezpečenia týkajúce sa zakázania externých dátových pripojení, vyberte Povoliť obsah .

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

4. Vyberte Dáta > dať z textu (staršia verzia). Zobrazí sa dialógové okno Finder .

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

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

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

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

9. Zvoľte, kam chcete dáta pridať: buď na existujúci list, na nový list, alebo v kontingenčnej tabuľke.

10. Vyberte tlačidlo OK.

    Ak chcete zaistiť, aby pripojenie fungovalo, zadajte nejaké dáta a potom vyberte Pripojenie > Aktualizovať.

1. Vyberte Údaje > z SQL Server ODBC. Zobrazí sa dialógové okno Pripojenie k zdroju údajov ODBC servera SQL.
   
   

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

3. V časti Overenievyberte zo zoznamu metódu: meno používateľa/heslo, Kerberos alebo NTLM.

4. Do polí Meno používateľa a Heslo zadajte prihlasovacie údaje.

5. Vyberte položku Pripojiť. Zobrazí sa dialógové okno Navigátor.

6. Na ľavej table prejdite na požadovanú tabuľku a vyberte ju.

7. Potvrďte príkaz SQL na pravej table. Príkaz SQL môžete podľa potreby zmeniť.

8. Ak si chcete pozrieť náhľad údajov, vyberte Spustiť.

9. Keď budete pripravení, vyberte Vrátiť údaje. Zobrazí sa dialógové okno Import dát. 
   
   

10. Zvoľte, kam chcete dáta pridať: buď na existujúci list, na nový list, alebo v kontingenčnej tabuľke.

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

12. Vyberte tlačidlo OK.

13. 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:

1. Vyberte data > z databázy (Microsoft Query).

2. Pridajte zdroj údajov pre databázu a potom vyberte OK.

3. Na príkazovom riadku SQL Server prihlasovacích údajov zadajte metódu overovania, užívateľské meno a heslo.
   
   

4. Vľavo vyberte šípku vedľa servera a zobrazte databázy.

5. Vyberte šípku vedľa požadovanej databázy.

6. Vyberte požadovanú tabuľku.

7. Ak chcete zobraziť zobrazenie údajov, vyberte Spustiť.

8. Keď budete pripravení, vyberte Vrátiť údaje.

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

10. Vyberte položku OK.

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

1. Vyberte Údaje> Pripojenia. Zobrazí sa dialógové okno Pripojenia zošita.

2. V zozname vyberte požadované pripojenie a potom vyberte Vymazať povolenia.