Applies ToExcel pre Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016 Excel 2013

V tomto kurze môžete použiť Editor Power Query Power Query na import údajov z lokálneho excelového súboru, ktorý obsahuje informácie o produkte, a z informačného kanála OData, ktorý obsahuje informácie o objednávke produktu. Vykonáte kroky transformácie a agregácie a skombinujete údaje z oboch zdrojov a vytvoríte zostavu Celkový predaj na produkt a rok.   

Na vykonanie tohto kurzu potrebujete zošit Produkty. V dialógovom okne Uložiť ako pomenujte súbor Produkty a objednávky.xlsx.

V tejto úlohe importujete produkty zo súboru Produkty a Orders.xlsx (stiahnuté a premenované vyššie) do excelového zošita, zvýšite úroveň riadkov na hlavičky stĺpcov, odstránite niektoré stĺpce a načítate dotaz do hárka.

Krok 1: Pripojenie k excelovému zošitu

  1. Otvorte excelový zošit.

  2. Vyberte položku Údaje > Získať údaje > zo súboru > zo zošita.

  3. V dialógovom okne Import údajov vyhľadajte a vyhľadajte Products.xlsx súbor, ktorý ste stiahli, a potom vyberte položku Otvoriť.

  4. Na table Navigátor dvakrát kliknite na tabuľku Produkty . Zobrazí sa power Editor Power Query.

Krok 2: Preskúmanie krokov dotazu

Podľa predvoleného nastavenia Power Query automaticky pridá niekoľko krokov ako pohodlie pre vás. Ďalšie informácie nájdete v časti Použité kroky na table Nastavenia dotazu .

  1. Kliknite pravým tlačidlom myši na krok Zdroj a vyberte položku Upraviť nastavenia. Tento krok bol vytvorený pri importovaní zošita.

  2. Kliknite pravým tlačidlom myši na krok Navigácia a vyberte položku Upraviť nastavenia. Tento krok bol vytvorený, keď ste vybrali tabuľku v dialógovom okne Navigácia .

  3. Kliknite pravým tlačidlom myši na krok Zmenený typ a vyberte položku Upraviť nastavenia. Tento krok vytvoril Power Query, ktorý odvodil typy údajov každého stĺpca. Výberom šípky nadol napravo od riadka vzorcov zobrazíte úplný vzorec.

Krok 3: Odstránenie ostatných stĺpcov, aby sa zobrazovali iba požadované stĺpce

V tomto kroku odstránite všetky stĺpce okrem stĺpcov ProductID (IDProduktu), ProductName (NázovProduktu), CategoryID (IDKategórie) a QuantityPerUnit (MnožstvoNaJednotku).

  1. V ukážke údajov vyberte stĺpce ProductID (ID produktu), ProductName (NázovProduktu), CategoryID (IDKategórie) a QuantityPerUnit (použite kombináciu klávesov Ctrl + kliknutie alebo Shift + kliknutie).

  2. Vyberte položku Odstrániť stĺpce > odstrániť ostatné stĺpce.

    Skrytie ostatných stĺpcov

Krok 4: Načítanie dotazu na produkty

V tomto kroku načítate dotaz Produkty do excelového hárka.

  • Vyberte položku Domov > Zavrieť & načítať. Dotaz sa zobrazí v novom excelovom hárku.

Súhrn: Power Query kroky vytvorené v úlohe 1

Počas vykonávania aktivít dotazu v Power Query sa vytvoria kroky dotazu a zobrazia sa na table Nastavenia dotazu v zozname Použité kroky. Každý krok dotazu obsahuje zodpovedajúci Power Query vzorec, známy aj ako jazyk M. Ďalšie informácie o Power Query vzorcoch nájdete v téme Vytvorenie Power Query vzorcov v Exceli.

Úloha

Krok dotazu

Vzorec

Import excelového zošita

Zdrojová mena

= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)

Vyberte tabuľku Produkty

Navigácia

= Source{[Item="Products",Kind="Table"]}[Údaje]

Power Query automaticky zisťuje typy údajov stĺpcov

Changed Type (Zmenený typ)

= Table.TransformColumnTypes(Products_Table,{{"ProductID", Int64.Type}, {"ProductName", type text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", type text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued", type logical}})

Odstránenie ostatných stĺpcov, aby sa zobrazovali iba požadované stĺpce

Odstránené ostatné stĺpce

= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

V tejto úlohe importujete údaje do excelového zošita zo vzorového informačného kanála OData Northwind na http://services.odata.org/Northwind/Northwind.svc,rozbaľte tabuľku Order_Details, odstránite stĺpce, vypočítate súčet riadka, transformujete dátum objednávky, zoskupíte riadky podľa idproduktu a roka, premenujete dotaz a vypnete sťahovanie dotazu do excelového zošita.

Krok 1: Pripojenie k informačnému kanálu OData

  1. Vyberte položku Údaje > Získať údaje > z iných zdrojov > z informačného kanála OData.

  2. V dialógovom okne Informačný kanál OData zadajte URL informačného kanála Northwind OData.

  3. Vyberte tlačidlo OK.

  4. Na table Navigátor dvakrát kliknite na tabuľku Objednávky .

Krok 2: Rozbalenie tabuľky Podrobnosti_objednávky

V tomto kroku rozbalíte tabuľku Podrobnosti_objednávky, ktorá súvisí s tabuľkou Objednávky, a skombinujete stĺpce ProductID (IDProduktu), UnitPrice (JednotkováCena) a Quantity (Množstvo) z tabuľky Podrobnosti_objednávky s tabuľkou Objednávky. Operáciou Rozbaliť sa skombinujú stĺpce zo súvisiacej tabuľky s objektovou tabuľkou. Po spustení dotazu sa riadky zo súvisiacej tabuľky (Order_Details) skombinujú do riadkov s primárnou tabuľkou (Objednávky).

V Power Query má stĺpec obsahujúci súvisiacu tabuľku v bunke hodnotu Záznam alebo Tabuľka. Nazývajú sa štruktúrované stĺpce. Záznam označuje jeden súvisiaci záznam a predstavujevzťah "one-to-one" s aktuálnymi údajmi alebo primárnou tabuľkou. Tabuľka označuje súvisiacu tabuľku a predstavuje vzťah "one-to-many" s aktuálnou alebo primárnou tabuľkou. Štruktúrovaný stĺpec predstavuje vzťah v zdroji údajov, ktorý má relačný model. Štruktúrovaný stĺpec napríklad označuje entitu s priradením cudzieho kľúča v informačnom kanáli OData alebo vzťah cudzieho kľúča v databáze SQL Server.

Po rozbalení tabuľky Podrobnosti_objednávky sa do tabuľky Objednávky pridajú tri nové stĺpce a ďalšie riadky, pričom každý sa priradí k riadku vnorenej alebo súvisiacej tabuľky.

  1. V ukážke údajov sa posuňte vodorovne na stĺpec Order_Details .

  2. V stĺpci Order_Details vyberte ikonu rozbalenia (Rozbaliť).

  3. V rozbaľovacom zozname Rozbaliť:

    1. Výberom položky (Vybrať všetky stĺpce) vymažete všetky stĺpce.

    2. Vyberte položky IdProduktu, JednotkováCena a Množstvo.

    3. Vyberte tlačidlo OK.

      Rozbalenie prepojenia na tabuľku Podrobnosti_objednávky

      Poznámka: V Power Query môžete rozbaliť tabuľky prepojené zo stĺpca a agregovať stĺpce prepojenej tabuľky pred rozbalením údajov v predmete tabuľky. Ďalšie informácie o vykonaní operácií agregácie nájdete v téme Agregácia údajov zo stĺpca.

Krok 3: Odstránenie ostatných stĺpcov, aby sa zobrazovali iba požadované stĺpce

V tomto kroku môžete odstrániť všetky stĺpce okrem stĺpcov OrderDate (DátumObjednávky), ProductID (IDProduktu), UnitPrice (JednotkováCena) a Quantity (Množstvo)

  1. V ukážkeúdajov vyberte nasledujúce stĺpce: 

    1. Vyberte prvý stĺpec OrderID.

    2. Shift + Kliknutie na posledný stĺpec, Špeditér.

    3. Stlačte kláves Ctrl a kliknite na stĺpce OrderDate (DátumObjednávky), Order_Details.ProductID (Podrobnosti_objednávky.IDProduktu), Order_Details.UnitPrice (Podrobnosti_objednávky.JednotkováCena) a Order_Details.Quantity (Podrobnosti_objednávky.Množstvo).

  2. Kliknite pravým tlačidlom myši na vybratú hlavičku stĺpca a vyberte položku Odstrániť ostatné stĺpce.

Krok 4: Výpočet celkovej hodnoty pre riadok za každý riadok tabuľky Podrobnosti_objednávky

V tomto kroku môžete vytvoriť vlastný stĺpec na výpočet celkovej hodnoty pre riadok za každý riadok tabuľky Podrobnosti_objednávky.

  1. V ukážke údajov vyberte ikonu tabuľky (Ikona tabuľky) v ľavom hornom rohu ukážky.

  2. Kliknite na položku Pridať vlastný stĺpec.

  3. V dialógovom okne Vlastný stĺpec zadajte do poľa Vzorce vlastného stĺpca[Order_Details.JednotkováCena] * [Order_Details.Quantity].

  4. Do poľa Názov nového stĺpca zadajte súčet riadkov.

  5. Vyberte tlačidlo OK.

Výpočet celkovej hodnoty pre riadok za každý riadok tabuľky Podrobnosti_objednávky

Krok 5: Transformácia stĺpca roka dátumu objednávky

V tomto kroku transformujete stĺpec OrderDate (DátumObjednávky) tak, aby sa v ňom vykreslil rok dátumu objednávky.

  1. V ukážke údajov kliknite pravým tlačidlom myši na stĺpec DátumObjednávky a vyberte položku Transformovať > rok.

  2. Premenovanie stĺpca OrderDate (DátumObjednávky) na Rok:

    1. Dvakrát kliknite na stĺpec OrderDate (Dátum objednávky) a zadajte Rok alebo

    2. Right-Click v stĺpci OrderDate (DátumObjednávky ), vyberte položku Rename (Premenovať) a zadajte year (Rok).

Krok 6: Zoskupenie riadkov podľa stĺpcov ProductID (IDProduktu) a Year (Rok)

  1. V ukážke údajov vyberte položky Year (Rok ) a Order_Details.ProductID (IDProduktu).

  2. Right-Click niektorú z hlavičiek a vyberte položku Zoskupovať podľa.

  3. V dialógovom okne Zoskupenie podľa:

    1. V textovom poli Názov nového stĺpca zadajte Celkový predaj.

    2. V rozbaľovacom zozname Operácia vyberte možnosť Súčet.

    3. V rozbaľovacom zozname Stĺpec vyberte Celková hodnota pre riadok.

  4. Vyberte tlačidlo OK.

    Dialógové okno Zoskupenie podľa pre operácie agregácie

Krok 7: Premenovanie dotazu

Pred importovaním údajov o predaji do Excelu premenujte dotaz:

  • Na table Nastavenia dotazu zadajte do poľa Názovhodnotu Celkový predaj.

Výsledky: Konečný dotaz pre úlohu 2

Po vykonaní všetkých krokov získate dotaz Celkový predaj v informačnom kanáli Northwind OData.

Celkový predaj

Súhrn: Power Query kroky vytvorené v úlohe 2 

Počas vykonávania aktivít dotazu v Power Query sa vytvoria kroky dotazu a zobrazia sa na table Nastavenia dotazu v zozname Použité kroky. Každý krok dotazu obsahuje zodpovedajúci Power Query vzorec, známy aj ako jazyk M. Ďalšie informácie o Power Query vzorcoch nájdete v téme Informácie o Power Query vzorcoch.

Úloha

Krok dotazu

Vzorec

Pripojenie k informačnému kanálu OData

Zdroj

= OData.Feed("http://services.odata.org/Northwind/Northwind.svc"; null; [Implementácia="2.0"])

Výber tabuľky

Navigácia

= Source{[Name="Orders"]}[Údaje]

Rozbalenie tabuľky Podrobnosti_objednávky

Rozbalenie tabuľky Podrobnosti_objednávky

= Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})

Odstránenie ostatných stĺpcov, aby sa zobrazovali iba požadované stĺpce

RemovedColumns (OdstránenéStĺpce)

= Table.RemoveColumns(#"Rozbaliť Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})

Výpočet celkovej hodnoty pre riadok za každý riadok tabuľky Podrobnosti_objednávky

Pridané vlastné

= Table.AddColumn(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

= Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

Zmena na zmysluplnejší názov, Lne Total

Premenované stĺpce

= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})

Transformácia stĺpca OrderDate (DátumObjednávky) tak, aby vykreslil rok

Extrahovaný rok

= Table.TransformColumns(#"Zoskupené riadky";{{"Rok"; Dátum.Rok, Int64.Type}})

Zmeniť na 

zmysluplnejšie názvy, DátumObjednávky a Rok

Premenované stĺpce 1

Table.RenameColumns

(TransformedColumn,{{"OrderDate", "Year"}})

Zoskupenie riadkov podľa stĺpcov ProductID (IDProduktu) a Year (Rok)

GroupedRows (ZoskupenéRiadky)

= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

Power Query vám umožňuje skombinovať viaceré dotazy zlúčením alebo pripojením. Operácia Zlúčiť sa vykoná na každom dotaze Power Query s tabuľkovým tvarom, a to bez ohľadu na zdroj údajov, z ktorého tieto údaje pochádzajú. Ďalšie informácie o kombinovaní zdrojov údajov nájdete v téme Kombinovanie viacerých dotazov.

V tejto úlohe skombinujete dotazy Produkty a Celkový predaj pomocou dotazu Zlúčiť a rozbaliť a potom načítate dotaz Celkový predaj na produkt do dátového modelu Excelu.

Krok 1: Zlúčenie stĺpca ProductID (IDProduktu) s dotazom Celkový predaj

  1. V excelovom zošite prejdite na dotaz Produkty na karte Hárok Produkty .

  2. Vyberte bunku v dotaze a potom vyberte položku Dotaz > zlúčiť.

  3. V dialógovom okne Zlúčiť vyberte ako primárnu tabuľku položku Produkty a ako sekundárny alebo súvisiaci dotaz na zlúčenie vyberte možnosť Celkový predaj . Celkový predaj sa stane novým štruktúrovaným stĺpcom s ikonou rozbalenia.

  4. Ak chcete porovnať stĺpec Total Sales (Celkový predaj) so stĺpcom Products (Produkty) podľa stĺpca ProductID (IDProduktu), vyberte stĺpec ProductID (IDProduktu) z tabuľky Products (Produkty) a stĺpec Order_Details.ProductID (Podrobnosti_objednávky.IDProduktu) z tabuľky Celkový predaj.

  5. V dialógovom okne Úrovne ochrany osobných údajov:

    1. Pre oba zdroje údajov vyberte ako úroveň ochrany osobných údajov možnosť Organizačné.

    2. Vyberte položku Uložiť.

  6. Vyberte tlačidlo OK.

    Poznámka k zabezpečeniu:  Úrovne ochrany osobných údajov chránia používateľa pred neúmyselným kombinovaním údajov z viacerých zdrojov, ktoré môžu byť súkromné alebo organizačné. V závislosti od dotazu môže používateľ neúmyselne odoslať údaje zo súkromného zdroja do iného zdroja, ktorý môže byť škodlivý. Power Query analyzuje každý zdroj údajov a klasifikuje ho podľa definovanej úrovne ochrany osobných údajov: Verejné, Organizačné a Súkromné. Ďalšie informácie o úrovniach ochrany osobných údajov nájdete v téme Nastavenie úrovní ochrany osobných údajov.

    Dialógové okno Zlúčenie

Výsledok

Operácia zlúčenia vytvorí dotaz. Výsledok dotazu obsahuje všetky stĺpce z primárnej tabuľky (Produkty) a jeden štruktúrovaný stĺpec tabuľky do súvisiacej tabuľky (Celkový predaj). Výberom ikony Rozbaliť pridáte nové stĺpce do primárnej tabuľky zo sekundárnej alebo súvisiacej tabuľky.

Finálne zlúčenie

Krok 2: Rozbalenie zlúčeného stĺpca

V tomto kroku rozbalíte zlúčený stĺpec s názvom NovýStĺpec a vytvoríte dva nové stĺpce v dotaze Produkty : Rok a Celkový predaj.

  1. V ukážke údajov vyberte ikonu Rozbaliť (Rozbaliť) vedľa položky NovýStĺpc.

  2. V rozbaľovacom zozname Rozbaliť :

    1. Výberom položky (Vybrať všetky stĺpce) vymažete všetky stĺpce.

    2. Vyberte položky Rok a Celkový predaj.

    3. Vyberte tlačidlo OK.

  3. Premenujte tieto dva stĺpce na Rok a Celkový predaj.

  4. Ak chcete zistiť, ktoré produkty a v ktorých rokoch získali produkty najvyšší objem predaja, vyberte položku Zoradiť zostupne podľa celkového predaja.

  5. Premenujte dotaz na Celkový predaj podľa produktu.

Výsledok

Rozbalenie prepojenia tabuľky

Krok 3: Načítanie dotazu Celkový predaj za produkt do dátového modelu Excelu

V tomto kroku načítate dotaz do dátového modelu Excelu s cieľom vytvoriť zostavu pripojenú k výsledku dotazu. Po načítaní údajov do dátového modelu Excelu môžete použiť power pivot na ďalšiu analýzu údajov.

  1. Vyberte položku Domov > Zavrieť & načítať.

  2. V dialógovom okne Import údajov vyberte položku Pridať tieto údaje do dátového modelu. Ďalšie informácie o používaní tohto dialógového okna získate výberom otáznika (?).

Výsledok

Máte dotaz Celkový predaj na produkt , ktorý kombinuje údaje zo súboru Products.xlsx a informačného kanála OData spoločnosti Northwind. Tento dotaz sa použije na model doplnku Power Pivot. Okrem toho zmeny v dotaze upravujú a obnovujú výslednú tabuľku v dátovom modeli.

Súhrn: Power Query kroky vytvorené v úlohe 3

Pri vykonávaní činností zlúčenia dotazov v Power Query sa vytvoria kroky dotazu a zobrazia sa na table Nastavenia dotazu v zozname Použité kroky. Každý krok dotazu obsahuje zodpovedajúci Power Query vzorec, známy aj ako jazyk M. Ďalšie informácie o Power Query vzorcoch nájdete v téme Informácie o Power Query vzorcoch.

Úloha

Krok dotazu

Vzorec

Zlúčenie stĺpca ProductID (IDProduktu) s dotazom Celkový predaj

Zdroj (zdroj údajov pre operáciu Zlúčiť)

= Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)

Rozbalenie zlúčeného stĺpca

Rozbalený celkový predaj

= Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})

Premenovanie dvoch stĺpcov

Premenované stĺpce

= Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})

Zoradiť celkový predaj vo vzostupnom poradí

Zoradené riadky

= Table.Sort(#"Premenované stĺpce",{{"Celkový predaj"; Order.Ascending}})

Pozrite tiež

Pomocník pre Power Query pre Excel

Potrebujete ďalšiu pomoc?

Chcete ďalšie možnosti?

Môžete preskúmať výhody predplatného, prehľadávať školiace kurzy, naučiť sa zabezpečiť svoje zariadenie a ešte oveľa viac.

Komunity pomôžu s kladením otázok a odpovedaním na ne, s poskytovaním pripomienok a so získavaním informácií od odborníkov s bohatými znalosťami.