{"id":6937,"date":"2025-01-13T05:05:15","date_gmt":"2025-01-13T05:05:15","guid":{"rendered":"https:\/\/howtogeek.blog\/cs\/?p=6937"},"modified":"2025-01-13T05:05:15","modified_gmt":"2025-01-13T05:05:15","slug":"cleaning-and-importing-data-with-power-query-in-excel-a-stepbystep-guide","status":"publish","type":"post","link":"https:\/\/howtogeek.blog\/cs\/cleaning-and-importing-data-with-power-query-in-excel-a-stepbystep-guide\/","title":{"rendered":"\u010ci\u0161t\u011bn\u00ed a import dat pomoc\u00ed Power Query v Excelu: Pr\u016fvodce krok za krokem"},"content":{"rendered":"<h3>Rychl\u00e9 odkazy<\/h3>\n<p>Od sv\u00e9ho uveden\u00ed v Excelu Power Query zm\u011bnil zp\u016fsob, jak\u00fdm profesion\u00e1lov\u00e9 zach\u00e1zej\u00ed se spr\u00e1vou dat. Power Query, p\u016fvodn\u011b p\u0159edstaven\u00fd jako dopln\u011bk v roce 2010, se od roku 2016 stal nepostradatelnou vestav\u011bnou funkc\u00ed Excelu. Jeho prim\u00e1rn\u00ed funkce? Zefektivn\u011bn\u00ed procesu \u010di\u0161t\u011bn\u00ed, importu a reorganizace dat v\u00e1m u\u0161et\u0159\u00ed nejen \u010das, ale tak\u00e9 dal\u0161\u00ed frustraci. T\u00edm, \u017ee se Power Query zam\u011b\u0159\u00ed na efektivn\u00ed p\u0159\u00edpravu dat, umo\u017e\u0148uje v\u00e1m soust\u0159edit se na anal\u00fdzu a statistiky.<\/p>\n<h2 id=\"what-is-power-query\">Co je Power Query?<\/h2>\n<p>Power Query je v podstat\u011b n\u00e1stroj v Excelu ur\u010den\u00fd pro transformaci dat. Nab\u00edz\u00ed jednoduch\u00fd, ale v\u00fdkonn\u00fd zp\u016fsob, jak z\u00edskat data z r\u016fzn\u00fdch zdroj\u016f, vy\u010distit je a p\u0159ipravit k anal\u00fdze. Power Query je um\u00edst\u011bn v sekci \u201eZ\u00edskat a transformovat data\u201c na kart\u011b Data, co\u017e zaji\u0161\u0165uje snadn\u00fd p\u0159\u00edstup pro u\u017eivatele na v\u0161ech \u00farovn\u00edch dovednost\u00ed.<\/p>\n<figure class=\"wp-block-image\"><img alt=\"Um\u00edst\u011bn\u00ed n\u00e1stroj\u016f Power Query na kart\u011b Data v Excelu\" class=\"wp-image\" decoding=\"async\" height=\"243\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/the-data-tab-in-excel-with-the-get-and-transform-data-group-highlighted.webp\" title=\"Um\u00edst\u011bn\u00ed n\u00e1stroj\u016f Power Query na kart\u011b Data v Excelu\" width=\"750\"\/><\/figure>\n<p>P\u0159ipoutejte se, kdy\u017e se pono\u0159\u00edme do praktick\u00e9ho vyu\u017eit\u00ed Power Query a vybav\u00edme v\u00e1s znalostmi pro jemn\u00e9 \u010di\u0161t\u011bn\u00ed, import a organizaci va\u0161ich dat.<\/p>\n<h2 id=\"cleaning-excel-data-using-power-query\">\u010ci\u0161t\u011bn\u00ed dat aplikace Excel pomoc\u00ed Power Query<\/h2>\n<p>Jednou z m\u00fdch obl\u00edben\u00fdch aplikac\u00ed Power Query je \u010di\u0161t\u011bn\u00ed dat. P\u0159edstavte si, \u017ee jste do sv\u00e9ho listu Excelu vlo\u017eili nesourod\u00fd seznam e-mailov\u00fdch adres z pole CC v Outlooku. Va\u0161e posl\u00e1n\u00ed? Chcete-li prom\u011bnit tato chaotick\u00e1 data na uklizenou tabulku obsahuj\u00edc\u00ed k\u0159estn\u00ed jm\u00e9na, p\u0159\u00edjmen\u00ed a e-mailov\u00e9 adresy. Zat\u00edmco funkce Excelu toho mohou dos\u00e1hnout, Power Query poskytuje bezprobl\u00e9mov\u00e9 prost\u0159ed\u00ed, kter\u00e9 vy\u017eaduje minim\u00e1ln\u00ed \u00fasil\u00ed.<\/p>\n<figure class=\"wp-block-image\"><img alt=\"Seznam e-mailov\u00fdch adres odd\u011blen\u00fdch st\u0159edn\u00edkem v Excelu\" class=\"wp-image\" decoding=\"async\" height=\"147\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/a-list-of-email-addresses-in-cell-a1-of-an-excel-spreadsheet-each-separated-by-a-semicolon.webp\" title=\"Seznam e-mailov\u00fdch adres odd\u011blen\u00fdch st\u0159edn\u00edkem v Excelu\" width=\"750\"\/><\/figure>\n<p>Chcete-li za\u010d\u00edt, postupujte takto:<\/p>\n<ul>\n<li><strong>Otev\u0159\u00edt Power Query:<\/strong> Klikn\u011bte na kartu \u201eData\u201c a pot\u00e9 vyberte \u201eZ tabulky\/rozsahu\u201c. Toto okno v\u00e1s vyzve k vytvo\u0159en\u00ed tabulky z vybran\u00fdch dat.<\/li>\n<li><strong>Vyberte sv\u00e1 data:<\/strong> Potvr\u010fte, \u017ee je vybr\u00e1n spr\u00e1vn\u00fd rozsah, a pokud va\u0161e data nemaj\u00ed z\u00e1hlav\u00ed, ponechte mo\u017enost z\u00e1hlav\u00ed neza\u0161krtnutou, ne\u017e kliknete na \u201eOK\u201c.<\/li>\n<\/ul>\n<p>Po kliknut\u00ed na \u201eOK\u201c se otev\u0159e Power Query Editor, kter\u00fd nastavuje f\u00e1zi pro transformaci dat.<\/p>\n<p>V\u00e1\u0161 dal\u0161\u00ed \u00fakol? Rozd\u011blte e-mailov\u00e9 adresy do jednotliv\u00fdch \u0159\u00e1dk\u016f:<\/p>\n<ul>\n<li><strong>Rozd\u011blit sloupec:<\/strong> Klikn\u011bte prav\u00fdm tla\u010d\u00edtkem na z\u00e1hlav\u00ed sloupce, naje\u010fte my\u0161\u00ed na \u201eRozd\u011blit sloupec\u201c a vyberte \u201ePodle odd\u011blova\u010de\u201c. Jako odd\u011blova\u010d vyberte \u201eSt\u0159edn\u00edk\u201c a v dialogov\u00e9m okn\u011b Pokro\u010dil\u00e9 mo\u017enosti vyberte \u201e\u0158\u00e1dky\u201c.<\/li>\n<\/ul>\n<p>Po potvrzen\u00ed uvid\u00edte ka\u017edou e-mailovou adresu \u00fahledn\u011b uspo\u0159\u00e1danou do samostatn\u00fdch \u0159\u00e1dk\u016f.<\/p>\n<figure class=\"wp-block-image\"><img alt=\"E-mailov\u00e9 adresy rozd\u011blen\u00e9 do v\u00edce \u0159\u00e1dk\u016f v editoru Power Query\" class=\"wp-image\" decoding=\"async\" height=\"254\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/a-list-of-email-addresses-in-excel-s-power-query-editor.webp\" title=\"E-mailov\u00e9 adresy rozd\u011blen\u00e9 do v\u00edce \u0159\u00e1dk\u016f v editoru Power Query\" width=\"750\"\/><\/figure>\n<p>Nyn\u00ed je \u010das vyt\u00e1hnout jm\u00e9no a p\u0159\u00edjmen\u00ed. K tomu pou\u017eijte funkci \u201eP\u0159idat sloupec z p\u0159\u00edklad\u016f\u201c:<\/p>\n<ul>\n<li><strong>Extrahovat jm\u00e9na:<\/strong> Poklepejte na z\u00e1hlav\u00ed sloupce a zadejte k\u0159estn\u00ed jm\u00e9no nebo titul, aby se vzor replikoval. Power Query automaticky navrhne zb\u00fdvaj\u00edc\u00ed polo\u017eky!<\/li>\n<li><strong>P\u0159ejmenovat sloupce:<\/strong> Ujist\u011bte se, \u017ee jste sloupce p\u0159ejmenovali spr\u00e1vn\u011b (nap\u0159\u00edklad \u201eJm\u00e9no\u201c, \u201eP\u0159\u00edjmen\u00ed\u201c, \u201eE-mailov\u00e1 adresa\u201c).<\/li>\n<li><strong>Z\u00e1v\u011bre\u010dn\u00e9 \u00fapravy:<\/strong> Upravte po\u0159ad\u00ed sloupc\u016f jejich p\u0159eta\u017een\u00edm do po\u017eadovan\u00fdch pozic.<\/li>\n<\/ul>\n<p>Aby va\u0161e data vypadala \u00fahledn\u011b a uspo\u0159\u00e1dan\u011b, nezapome\u0148te p\u0159ed zav\u0159en\u00edm a na\u010dten\u00edm dat zp\u011bt do se\u0161itu p\u0159i\u0159adit dotazu smyslupln\u00fd n\u00e1zev.<\/p>\n<figure class=\"wp-block-image\"><img alt=\"Pojmenov\u00e1n\u00ed dotazu v editoru Power Query\" class=\"wp-image\" decoding=\"async\" height=\"218\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/a-table-in-excel-s-power-query-editor-being-given-the-name-heroicemails.webp\" title=\"Pojmenov\u00e1n\u00ed dotazu v editoru Power Query\" width=\"750\"\/><\/figure>\n<h2 id=\"importing-and-reorganizing-data-with-power-query\">Import a reorganizace dat pomoc\u00ed Power Query<\/h2>\n<p>Power Query zaz\u00e1\u0159\u00ed, pokud jde o import dat z r\u016fzn\u00fdch zdroj\u016f mimo tabulky, jako jsou soubory PDF nebo webov\u00e9 str\u00e1nky. Tato dal\u0161\u00ed \u010d\u00e1st ukazuje, jak efektivn\u011b extrahovat a reorganizovat konkr\u00e9tn\u00ed data z existuj\u00edc\u00edho se\u0161itu aplikace Excel.<\/p>\n<p>Nejprve za\u010dn\u011bte op\u011btovn\u00fdm p\u0159echodem na kartu \u201eData\u201c:<\/p>\n<ul>\n<li><strong>Zah\u00e1jen\u00ed importu:<\/strong> Klikn\u011bte na \u201eZ\u00edskat data\u201c &gt; \u201eZe souboru\u201c &gt; \u201eZ se\u0161itu Excelu\u201c.<\/li>\n<li><strong>Vyberte sv\u016fj soubor:<\/strong> Vyhledejte soubor, ze kter\u00e9ho chcete importovat data, a pokra\u010dujte kliknut\u00edm na \u201eImportovat\u201c, pot\u00e9 \u201eTransformovat data\u201c, abyste z\u00edskali p\u0159\u00edstup k Editoru Power Query.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"Okno navig\u00e1toru zobrazuj\u00edc\u00ed data dostupn\u00e1 pro import\" class=\"wp-image\" decoding=\"async\" height=\"700\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/excel-s-navigator-window-that-shows-the-data-it-will-import-from-another-worksheet-and-the-transform-data-button-is-selected.webp\" title=\"Okno navig\u00e1toru zobrazuj\u00edc\u00ed data dostupn\u00e1 pro import\" width=\"880\"\/><\/figure>\n<p>V editoru Power Query:<\/p>\n<ul>\n<li><strong>Pov\u00fd\u0161it \u0159\u00e1dek na z\u00e1hlav\u00ed:<\/strong> Kliknut\u00edm na \u201ePou\u017e\u00edt prvn\u00ed \u0159\u00e1dek jako z\u00e1hlav\u00ed\u201c okam\u017eit\u011b ur\u010d\u00edte prvn\u00ed \u0159\u00e1dek jako z\u00e1hlav\u00ed sloupc\u016f.<\/li>\n<li><strong>Unpivot Data:<\/strong> Vyberte sloupce, kter\u00e9 p\u0159edstavuj\u00ed va\u0161e jedine\u010dn\u00e9 prom\u011bnn\u00e9, a kliknut\u00edm prav\u00fdm tla\u010d\u00edtkem vyberte \u201eUnpivot Other Columns\u201c. T\u00edm se zm\u011bn\u00ed va\u0161e rozvr\u017een\u00ed a ka\u017ed\u00e1 jedine\u010dn\u00e1 hodnota bude prezentov\u00e1na na samostatn\u00e9m \u0159\u00e1dku.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"Neoto\u010dn\u00e1 data ukazuj\u00edc\u00ed uklizen\u00e9 rozvr\u017een\u00ed\" class=\"wp-image\" decoding=\"async\" height=\"311\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/a-table-in-power-query-editor-in-excel-containing-five-columns-that-have-been-transformed-using-the-tool-s-mechanisms.webp\" title=\"Neoto\u010dn\u00e1 data ukazuj\u00edc\u00ed uklizen\u00e9 rozvr\u017een\u00ed\" width=\"749\"\/><\/figure>\n<p>P\u0159ed na\u010dten\u00edm reorganizovan\u00e9 tabulky zp\u011bt do se\u0161itu nezapome\u0148te doladit n\u00e1zvy sloupc\u016f a definovat spr\u00e1vn\u00e9 datov\u00e9 typy, abyste zachovali integritu dat.<\/p>\n<figure class=\"wp-block-image\"><img alt=\"Nastaven\u00ed datov\u00e9ho typu pro sloupec M\u011bs\u00edc v editoru Power Query\" class=\"wp-image\" decoding=\"async\" height=\"413\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/a-table-in-excel-s-power-query-editor-with-a-column-being-renamed-month-and-the-data-type-symbol-representing-a-calendar.webp\" title=\"Nastaven\u00ed datov\u00e9ho typu pro sloupec M\u011bs\u00edc v editoru Power Query\" width=\"750\"\/><\/figure>\n<p>Po dokon\u010den\u00ed transformac\u00ed klikn\u011bte na \u201eZav\u0159\u00edt a na\u010d\u00edst\u201c&gt; \u201eZav\u0159\u00edt a na\u010d\u00edst do\u201c, abyste ur\u010dili, kde se va\u0161e data v Excelu objev\u00ed.<\/p>\n<p>S\u00edla Power Query spo\u010d\u00edv\u00e1 v jeho schopnosti udr\u017eovat dynamick\u00e1 propojen\u00ed se sv\u00fdmi datov\u00fdmi zdroji. Pokud se nap\u0159\u00edklad zm\u011bn\u00ed va\u0161e p\u016fvodn\u00ed datov\u00e1 sada, jednodu\u0161e klikn\u011bte prav\u00fdm tla\u010d\u00edtkem my\u0161i na novou tabulku a vyberte \u201eObnovit\u201c, abyste ji plynule aktualizovali.<\/p>\n<figure class=\"wp-block-image\"><img alt=\"Obnoven\u00ed dat v tabulce Power Query\" class=\"wp-image\" decoding=\"async\" height=\"390\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/the-refresh-option-in-excel-for-updating-a-table-created-through-a-power-query.webp\" title=\"Obnoven\u00ed dat v tabulce Power Query\" width=\"750\"\/><\/figure>\n<p>Tato funkce d\u011bl\u00e1 z Power Query zm\u011bnu hry pro dynamickou anal\u00fdzu dat \u2013 udr\u017euje va\u0161e datov\u00e9 sady aktu\u00e1ln\u00ed bez ru\u010dn\u00edho kop\u00edrov\u00e1n\u00ed a vkl\u00e1d\u00e1n\u00ed.<\/p>\n<p>Jste p\u0159ipraveni posunout sv\u00e9 dovednosti v oblasti spr\u00e1vy dat na dal\u0161\u00ed \u00farove\u0148? Prozkoumejte Power Query d\u00e1le d\u00edky pr\u00e1ci s tabulkami z webu a vyzkou\u0161ejte si jeho v\u0161estrann\u00e9 mo\u017enosti importu z prvn\u00ed ruky. Zjist\u00edte, \u017ee tento n\u00e1stroj b\u011bhem okam\u017eiku zv\u00fd\u0161\u00ed va\u0161i produktivitu a p\u0159esnost!<\/p>\n<h3>Dal\u0161\u00ed statistiky<\/h3>\n<h4><strong>1. Jak\u00e9 typy zdroj\u016f dat mohu pou\u017e\u00edvat s Power Query?<\/strong><\/h4>\n<p>Power Query podporuje r\u016fzn\u00e9 zdroje dat, v\u010detn\u011b soubor\u016f Excel, soubor\u016f CSV, webov\u00fdch str\u00e1nek, datab\u00e1z\u00ed a dal\u0161\u00edch. D\u00edky tomu je vysoce univerz\u00e1ln\u00ed pro \u00falohy importu a transformace dat.<\/p>\n<h4><strong>2. Mohu automaticky aktualizovat data importovan\u00e1 prost\u0159ednictv\u00edm Power Query?<\/strong><\/h4>\n<p>Ano, m\u016f\u017eete nastavit automatick\u00e9 obnovov\u00e1n\u00ed datov\u00fdch sad Power Query. Excel umo\u017e\u0148uje konfigurovat intervaly obnovov\u00e1n\u00ed, co\u017e umo\u017e\u0148uje, aby va\u0161e data z\u016fstala aktu\u00e1ln\u00ed bez ru\u010dn\u00edho z\u00e1sahu.<\/p>\n<h4><strong>3. Existuje n\u011bjak\u00e9 omezen\u00ed pro mno\u017estv\u00ed dat, kter\u00e1 mohu importovat pomoc\u00ed Power Query?<\/strong><\/h4>\n<p>Power Query sice dok\u00e1\u017ee zpracovat velk\u00e9 datov\u00e9 sady, ale v\u00fdkon m\u016f\u017ee klesat s p\u0159\u00edli\u0161 velk\u00fdmi sadami, zejm\u00e9na pokud p\u0159ekra\u010duj\u00ed limit \u0159\u00e1dk\u016f Excelu 1 048 576. P\u0159ed importem je vhodn\u00e9 data filtrovat nebo sumarizovat, abyste zv\u00fd\u0161ili v\u00fdkon zpracov\u00e1n\u00ed.<\/p>\n<p><a class=\"xiaomi\" href=\"https:\/\/www.howtogeek.com\/how-to-clean-up-and-import-data-using-power-query-in-excel\/\" rel=\"noopener noreferrer nofollow\" target=\"_blank\">Zdroj a obr\u00e1zky<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Rychl\u00e9 odkazy Od sv\u00e9ho uveden\u00ed v Excelu Power Query zm\u011bnil zp\u016fsob, jak\u00fdm profesion\u00e1lov\u00e9 zach\u00e1zej\u00ed se spr\u00e1vou dat. Power Query, p\u016fvodn\u011b p\u0159edstaven\u00fd jako dopln\u011bk v roce 2010, se od roku 2016 stal nepostradatelnou vestav\u011bnou funkc\u00ed Excelu. Jeho prim\u00e1rn\u00ed funkce? Zefektivn\u011bn\u00ed procesu \u010di\u0161t\u011bn\u00ed, importu a reorganizace dat v\u00e1m u\u0161et\u0159\u00ed nejen \u010das, ale tak\u00e9 dal\u0161\u00ed frustraci. T\u00edm, \u017ee [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[126,283,5],"class_list":["post-6937","post","type-post","status-publish","format-standard","hentry","category-how-to","tag-microsoft","tag-microsoft-excel","tag-windows"],"acf":[],"_links":{"self":[{"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/posts\/6937","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/comments?post=6937"}],"version-history":[{"count":1,"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/posts\/6937\/revisions"}],"predecessor-version":[{"id":6938,"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/posts\/6937\/revisions\/6938"}],"wp:attachment":[{"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/media?parent=6937"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/categories?post=6937"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/tags?post=6937"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}