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

Importovať zdroje údajov

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 položku Získať údaje>.

   

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

3. V dialógovom okne Výber zdroja údajov 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.

Pracujte s údajmi prostredníctvom Editora Power Query

Postup

1. Vyberte položku Data>Get Data (Power Query)).

2. Ak chcete otvoriť Editor Power Query, vyberte položku 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. Po dokončení vyberte položku Domov>Zavrieť & Načítať.

Výsledok

Novo importované dáta sa zobrazia na novom liste.

Aktualizovať zdroje dát

Môžete obnoviť nasledujúce zdroje údajov: sharepointové súbory, sharepointové zoznamy, sharepointové priečinky, OData, textové súbory/súbory CSV, excelové zošity (.xlsx), súbory XML a JSON, miestne tabuľky a rozsahy, databáza Microsoft SQL Server a priečinky.

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 položku Získať údaje.
4. Vyberte položku Zavrieť.

Aktualizovať nasledujúce časy

Postup aktualizácie:

• Všetky zdroje údajov v zošite, vybertepoložku Obnoviť údaje> 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 Kontingenčná tabuľka Analyzovať>obnoviť údaje.

Zadajte a vymažte prihlasovacie ú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 Zadanie poverení .

Príklad:

• Prihlasovacie údaje služby SharePoint:

  

• SQL Server prihlasovacie údaje:

  

Vymazať prihlasovacie údaje

1. Vyberte>nastavenia zdroja údajovData Get>.
2. V dialógovom okne Nastavenia zdroja dátvyberte požadované pripojenie.
3. V dolnej časti vyberte položku Vymazať povolenia.
4. Potvrďte, že to chcete urobiť, a potom vyberte Odstrániť.

Vytvorenie a prenos Power Query kódu jazyka VBA

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 VBA, ktorý používa nasledujúce entity v objektovom modeli Excelu, funguje aj v Exceli pre Mac: objekt Dotazy, objekt WorkbookQuery, vlastnosť Workbook.Queries. Ď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 položky Nástroje>Makrá>Editor jazyka Visual Basic. 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

Import a aktualizácia súborov TXT alebo CSV

1. Otvorte excelový zošit.

2. Ak sa zobrazí upozornenie zabezpečenia o vypnutí pripojení externých údajov, vyberte položku 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. Výber údajov>z textu (staršia verzia). Zobrazí sa dialógové okno Findera .

5. Vyhľadajte súbor .txt alebo .csv a vyberte položku Otvoriť. Zobrazí sa Sprievodca importom textu .

   Tip Opakovaným začiarknutím položky Náhľad vybratej tably údajov potvrďte svoje voľby.

6. Na prvej stránke postupujte takto:
   Typ súboru Ak chcete vybrať typ textového súboru, vyberte položku 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ť.

   Znaková sada V časti Pôvod 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 vybrali položku 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 (").
   Pevná šírka

   Ak ste na prvej strane zvolili pevnú šírku , postupujte podľa pokynov na vytvorenie, odstránenie alebo premiestnenie čiary zlomu v poli Ukážka vybratých údajov .

8. Na tretej stránke postupujte takto:

   Pre každý stĺpec v časti Ukážka vybraných údajov ho vyberte a potom ho v prípade potreby zmeňte na iný formát stĺpca. Môžete ďalej nastaviť formát dátumu a vybrať Rozšírené, ak chcete zmeniť nastavenie číselných dát. Dáta môžete previesť aj po importe.

   Vyberte tlačidlo 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é údaje a potom vyberte položku Obnoviť pripojenia>.

Pripojenie k SQL Database (ODBC)

1. Vyberte údaje>z rozhrania 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 poverenia.

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 pripojenia na kartách Použitie a Definícia v dialógovom okne Vlastnosti , vyberte položku Vlastnosti. Po importe údajov môžete tiež vybrať položkuPripojeniaúdajov> a potom v dialógovom okne Vlastnosti pripojenia vybrať položku Vlastnosti.

12. Vyberte tlačidlo OK.

13. Ak chcete zaistiť, aby pripojenie fungovalo, zadajte nejaké údaje a potom vyberte položkuObnoviť údaje> všetko.

Pripojenie k inej databáze (Microsoft Query)

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. Výber údajov>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 tlačidlo OK.

11. Ak chcete zaistiť, aby pripojenie fungovalo, zadajte nejaké údaje a potom vyberte položkuObnoviť údaje> všetko.

Vymazať prihlasovacie údaje

Ak vaše oprávnenia nefungujú, je možné, že ich najskôr nevymažete a potom sa prihlásite.

1. Vyberte položkuPripojeniaúdajov>. Zobrazí sa dialógové okno Pripojenia zošita.

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