{"id":6706,"date":"2025-01-13T05:06:04","date_gmt":"2025-01-13T05:06:04","guid":{"rendered":"https:\/\/howtogeek.blog\/lt\/?p=6706"},"modified":"2025-01-13T05:06:04","modified_gmt":"2025-01-13T05:06:04","slug":"cleaning-and-importing-data-with-power-query-in-excel-a-stepbystep-guide","status":"publish","type":"post","link":"https:\/\/howtogeek.blog\/lt\/cleaning-and-importing-data-with-power-query-in-excel-a-stepbystep-guide\/","title":{"rendered":"Duomen\u0173 valymas ir importavimas naudojant \u201ePower Query\u201c programoje \u201eExcel\u201c: \u017eingsnis po \u017eingsnio vadovas"},"content":{"rendered":"<h3>Greitos nuorodos<\/h3>\n<p>Po debiuto programoje \u201eExcel\u201c, \u201ePower Query\u201c pakeit\u0117 profesional\u0173 duomen\u0173 tvarkymo b\u016bd\u0105. I\u0161 prad\u017ei\u0173 2010 m. pristatytas kaip priedas, \u201ePower Query\u201c nuo 2016 m. tapo nepakei\u010diama integruota \u201eExcel\u201c funkcija. Jo pagrindin\u0117 funkcija? Supaprastinkite duomen\u0173 valymo, importavimo ir pertvarkymo proces\u0105, kad sutaupytum\u0117te ne tik laiko, bet ir padidintum\u0117te nusivylim\u0105. Sutelkiant d\u0117mes\u012f \u012f efektyv\u0173 duomen\u0173 paruo\u0161im\u0105, \u201ePower Query\u201c leid\u017eia sutelkti d\u0117mes\u012f \u012f analiz\u0119 ir \u012f\u017evalgas.<\/p>\n<h2 id=\"what-is-power-query\">Kas yra Power Query?<\/h2>\n<p>\u201ePower Query\u201c i\u0161 esm\u0117s yra \u201eExcel\u201c \u012frankis, skirtas duomen\u0173 transformavimui. Tai paprastas, bet galingas b\u016bdas gauti duomenis i\u0161 \u012fvairi\u0173 \u0161altini\u0173, juos i\u0161valyti ir paruo\u0161ti analizei. \u201ePower Query\u201c yra skirtuko \u201eDuomenys\u201c skiltyje \u201eGauti ir transformuoti duomenis\u201c, tod\u0117l naudotojai gali lengvai pasiekti vis\u0173 \u012fg\u016bd\u017ei\u0173 lygius.<\/p>\n<figure class=\"wp-block-image\"><img alt=\"\u201ePower Query\u201c \u012franki\u0173 vieta \u201eExcel\u201c skirtuke Duomenys\" 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=\"\u201ePower Query\u201c \u012franki\u0173 vieta \u201eExcel\u201c skirtuke Duomenys\" width=\"750\"\/><\/figure>\n<p>Prisijunkite, kai pasineriame \u012f praktin\u012f \u201ePower Query\u201c naudojim\u0105, suteikdami jums \u017eini\u0173, kaip tiksliai i\u0161valyti, importuoti ir tvarkyti duomenis.<\/p>\n<h2 id=\"cleaning-excel-data-using-power-query\">\u201eExcel\u201c duomen\u0173 valymas naudojant \u201ePower Query\u201c.<\/h2>\n<p>Viena i\u0161 mano naudojam\u0173 \u201ePower Query\u201c program\u0173 yra duomen\u0173 valymas. \u012esivaizduokite, kad \u012f \u201eExcel\u201c lap\u0105 \u012fklijavote siautuling\u0105 el. pa\u0161to adres\u0173 s\u0105ra\u0161\u0105 i\u0161 \u201eOutlook\u201c el. lai\u0161ko CC lauko. J\u016bs\u0173 misija? Paversti tuos netvarkingus duomenis \u012f tvarking\u0105 lentel\u0119, kurioje yra vardai, pavard\u0117s ir el. pa\u0161to adresai. Nors \u201eExcel\u201c funkcijos gali tai padaryti, \u201ePower Query\u201c suteikia skland\u017ei\u0105 patirt\u012f, reikalaujan\u010di\u0105 minimali\u0173 pastang\u0173.<\/p>\n<figure class=\"wp-block-image\"><img alt=\"El. pa\u0161to adres\u0173, atskirt\u0173 kabliata\u0161kiais, s\u0105ra\u0161as programoje \u201eExcel\u201c.\" 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=\"El. pa\u0161to adres\u0173, atskirt\u0173 kabliata\u0161kiais, s\u0105ra\u0161as programoje \u201eExcel\u201c.\" width=\"750\"\/><\/figure>\n<p>Nor\u0117dami prad\u0117ti, atlikite \u0161iuos veiksmus:<\/p>\n<ul>\n<li><strong>Atidarykite \u201ePower Query\u201c:<\/strong> spustel\u0117kite skirtuk\u0105 \u201eDuomenys\u201c, tada pasirinkite \u201eI\u0161 lentel\u0117s\/diapazono\u201c. \u0160is langas paragins sukurti lentel\u0119 i\u0161 pasirinkt\u0173 duomen\u0173.<\/li>\n<li><strong>Pasirinkite savo duomenis:<\/strong> patvirtinkite, kad pasirinktas teisingas diapazonas, ir, jei j\u016bs\u0173 duomenys neturi antra\u0161\u010di\u0173, palikite antra\u0161t\u0117s parinkt\u012f nepa\u017eym\u0117t\u0105 prie\u0161 spustel\u0117dami \u201eGerai\u201c.<\/li>\n<\/ul>\n<p>Spustel\u0117jus \u201eGerai\u201c, atsidaro \u201ePower Query\u201c rengykl\u0117, nustatanti duomen\u0173 transformavimo etap\u0105.<\/p>\n<p>J\u016bs\u0173 kita u\u017eduotis? Padalinkite el. pa\u0161to adresus \u012f atskiras eilutes:<\/p>\n<ul>\n<li><strong>Stulpelio padalijimas:<\/strong> De\u0161iniuoju pel\u0117s mygtuku spustel\u0117kite stulpelio antra\u0161t\u0119, u\u017eveskite pel\u0117s \u017eymekl\u012f ant \u201eSplit Column\u201c ir pasirinkite \u201eBy Limiter\u201c. Pasirinkite \u201eKabliata\u0161kis\u201c kaip skirtuk\u0105 ir dialogo lange I\u0161pl\u0117stin\u0117s parinktys pasirinkite \u201eEil\u0117s\u201c.<\/li>\n<\/ul>\n<p>Patvirtinus kiekvien\u0105 el. pa\u0161to adres\u0105 matysite tvarkingai suskirstyt\u0105 \u012f atskiras eilutes.<\/p>\n<figure class=\"wp-block-image\"><img alt=\"El. pa\u0161to adresai, atskirti \u012f kelias eilutes Power Query redaktoriuje\" 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=\"El. pa\u0161to adresai, atskirti \u012f kelias eilutes Power Query redaktoriuje\" width=\"750\"\/><\/figure>\n<p>Dabar at\u0117jo laikas i\u0161gauti vardus ir pavardes. Nor\u0117dami tai padaryti, naudokite funkcij\u0105 \u201ePrid\u0117ti stulpel\u012f i\u0161 pavyzd\u017ei\u0173\u201c:<\/p>\n<ul>\n<li><strong>I\u0161skleisti pavadinimus:<\/strong> dukart spustel\u0117kite stulpelio antra\u0161t\u0119 ir \u012fveskite vard\u0105 arba pavadinim\u0105, kad atkartotum\u0117te \u0161ablon\u0105. \u201ePower Query\u201c automati\u0161kai pasi\u016blys likusius \u012fra\u0161us!<\/li>\n<li><strong>Pervardyti stulpelius:<\/strong> b\u016btinai pervardykite stulpelius tinkamai (pvz., \u201eVardas\u201c, \u201ePavard\u0117\u201c, \u201eEl. pa\u0161to adresas\u201c).<\/li>\n<li><strong>Paskutiniai palietimai:<\/strong> pakoreguokite stulpeli\u0173 tvark\u0105, vilkdami juos \u012f norimas vietas.<\/li>\n<\/ul>\n<p>Kadangi j\u016bs\u0173 duomenys atrodo tvarkingi ir tvarkingi, nepamir\u0161kite u\u017eklausai priskirti prasming\u0105 pavadinim\u0105 prie\u0161 u\u017edarydami ir v\u0117l \u012fkeldami duomenis \u012f darbaknyg\u0119.<\/p>\n<figure class=\"wp-block-image\"><img alt=\"U\u017eklausos pavadinimo suteikimas Power Query redaktoriuje\" 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=\"U\u017eklausos pavadinimo suteikimas Power Query redaktoriuje\" width=\"750\"\/><\/figure>\n<h2 id=\"importing-and-reorganizing-data-with-power-query\">Duomen\u0173 importavimas ir tvarkymas naudojant \u201ePower Query\u201c.<\/h2>\n<p>\u201ePower Query\u201c puikiai tinka importuojant duomenis i\u0161 \u012fvairi\u0173 \u0161altini\u0173, ne tik i\u0161 skai\u010diuokli\u0173, pavyzd\u017eiui, PDF fail\u0173 ar svetaini\u0173. \u0160iame kitame skyriuje parodyta, kaip efektyviai i\u0161gauti ir pertvarkyti konkre\u010dius duomenis i\u0161 esamos \u201eExcel\u201c darbaknyg\u0117s.<\/p>\n<p>Pirmiausia prad\u0117kite dar kart\u0105 nar\u0161ydami \u012f skirtuk\u0105 \u201eDuomenys\u201c:<\/p>\n<ul>\n<li><strong>Prad\u0117ti importavim\u0105:<\/strong> spustel\u0117kite \u201eGauti duomenis\u201c&gt; \u201eI\u0161 failo\u201c&gt; \u201eI\u0161 Excel darbaknyg\u0117s\u201c.<\/li>\n<li><strong>Pasirinkite savo fail\u0105:<\/strong> suraskite fail\u0105, i\u0161 kurio norite importuoti duomenis, ir spustel\u0117kite \u201eImportuoti\u201c, o po to spustel\u0117kite \u201eTransformuoti duomenis\u201c, kad pasiektum\u0117te \u201ePower Query\u201c rengykl\u0119.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"Navigatoriaus langas, kuriame rodomi importuotini duomenys\" 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=\"Navigatoriaus langas, kuriame rodomi importuotini duomenys\" width=\"880\"\/><\/figure>\n<p>\u201ePower Query\u201c rengykl\u0117je:<\/p>\n<ul>\n<li><strong>Perkelti eilut\u0119 \u012f antra\u0161tes:<\/strong> spustel\u0117kite \u201eNaudoti pirm\u0105j\u0105 eilut\u0119 kaip antra\u0161tes\u201c, kad i\u0161 karto nurodytum\u0117te pirm\u0105j\u0105 eilut\u0119 kaip stulpelio antra\u0161tes.<\/li>\n<li><strong>At\u0161aukti duomenis:<\/strong> pasirinkite stulpelius, kurie atspindi j\u016bs\u0173 unikalius kintamuosius, ir de\u0161iniuoju pel\u0117s mygtuku spustel\u0117kite, kad pasirinktum\u0117te \u201eUnpivot Other Columns\u201c. Taip pakei\u010diamas j\u016bs\u0173 i\u0161d\u0117stymas, kiekviena unikali reik\u0161m\u0117 pateikiama atskiroje eilut\u0117je.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"Nesukami duomenys, rodantys tvarking\u0105 i\u0161d\u0117stym\u0105\" 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=\"Nesukami duomenys, rodantys tvarking\u0105 i\u0161d\u0117stym\u0105\" width=\"749\"\/><\/figure>\n<p>Prie\u0161 \u012fkeldami pertvarkyt\u0105 lentel\u0119 atgal \u012f darbaknyg\u0119, nepamir\u0161kite tiksliai sureguliuoti stulpeli\u0173 pavadinim\u0173 ir apibr\u0117\u017eti teisingus duomen\u0173 tipus, kad i\u0161laikytum\u0117te duomen\u0173 vientisum\u0105.<\/p>\n<figure class=\"wp-block-image\"><img alt=\"M\u0117nesio stulpelio duomen\u0173 tipo nustatymas Power Query redaktoriuje\" 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=\"M\u0117nesio stulpelio duomen\u0173 tipo nustatymas Power Query redaktoriuje\" width=\"750\"\/><\/figure>\n<p>Baig\u0119 transformacijas paspauskite \u201eU\u017edaryti ir \u012fkelti\u201c&gt; \u201eU\u017edaryti ir \u012fkelti\u201c, kad nustatytum\u0117te, kur j\u016bs\u0173 duomenys bus rodomi \u201eExcel\u201c.<\/p>\n<p>\u201ePower Query\u201c galia yra jos geb\u0117jimas palaikyti dinaminius ry\u0161ius su savo duomen\u0173 \u0161altiniais. Pavyzd\u017eiui, jei pasikei\u010dia pradinis duomen\u0173 rinkinys, tiesiog de\u0161iniuoju pel\u0117s mygtuku spustel\u0117kite nauj\u0105 lentel\u0119 ir pasirinkite \u201eAtnaujinti\u201c, kad j\u0105 skland\u017eiai atnaujintum\u0117te.<\/p>\n<figure class=\"wp-block-image\"><img alt=\"Duomen\u0173 atnaujinimas Power Query lentel\u0117je\" 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=\"Duomen\u0173 atnaujinimas Power Query lentel\u0117je\" width=\"750\"\/><\/figure>\n<p>D\u0117l \u0161ios funkcijos \u201ePower Query\u201c yra \u017eaidim\u0173 keitiklis, skirtas dinaminei duomen\u0173 analizei \u2013 duomen\u0173 rinkini\u0173 atnaujinimas neautomatinio kopijavimo ir \u012fklijavimo metu.<\/p>\n<p>Pasiruo\u0161\u0119 perkelti savo duomen\u0173 valdymo \u012fg\u016bd\u017eius \u012f kit\u0105 lyg\u012f? Toliau nar\u0161ykite \u201ePower Query\u201c dirbdami su lentel\u0117mis i\u0161 \u017einiatinklio ir patirdami \u012fvairias jos importavimo galimybes. Rasite \u0161\u012f \u012frank\u012f, kuris greitai padidins j\u016bs\u0173 produktyvum\u0105 ir tikslum\u0105!<\/p>\n<h3>Papildomos \u012f\u017evalgos<\/h3>\n<h4><strong>1. Koki\u0173 tip\u0173 duomen\u0173 \u0161altinius galiu naudoti su Power Query?<\/strong><\/h4>\n<p>\u201ePower Query\u201c palaiko \u012fvairius duomen\u0173 \u0161altinius, \u012fskaitant \u201eExcel\u201c failus, CSV failus, tinklalapius, duomen\u0173 bazes ir kt. D\u0117l to jis yra labai universalus duomen\u0173 importavimo ir transformavimo u\u017eduotims atlikti.<\/p>\n<h4><strong>2. Ar galiu automati\u0161kai atnaujinti duomenis, importuotus naudojant Power Query?<\/strong><\/h4>\n<p>Taip, galite nustatyti automatin\u012f \u201ePower Query\u201c duomen\u0173 rinkini\u0173 atnaujinim\u0105. \u201eExcel\u201c leid\u017eia konfig\u016bruoti atnaujinimo intervalus, kad j\u016bs\u0173 duomenys b\u016bt\u0173 atnaujinami be rankinio \u012fsiki\u0161imo.<\/p>\n<h4><strong>3. Ar yra apribojimas, kiek duomen\u0173 galiu importuoti naudojant \u201ePower Query\u201c?<\/strong><\/h4>\n<p>Nors \u201ePower Query\u201c gali apdoroti didelius duomen\u0173 rinkinius, na\u0161umas gali suma\u017e\u0117ti naudojant pernelyg didelius rinkinius, ypa\u010d jei jie vir\u0161ija \u201eExcel\u201c eilu\u010di\u0173 limit\u0105 \u2013 1 048 576. Norint pagerinti apdorojimo na\u0161um\u0105, prie\u0161 importuojant patartina filtruoti arba apibendrinti duomenis.<\/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\">\u0160altinis ir vaizdai<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Greitos nuorodos Po debiuto programoje \u201eExcel\u201c, \u201ePower Query\u201c pakeit\u0117 profesional\u0173 duomen\u0173 tvarkymo b\u016bd\u0105. I\u0161 prad\u017ei\u0173 2010 m. pristatytas kaip priedas, \u201ePower Query\u201c nuo 2016 m. tapo nepakei\u010diama integruota \u201eExcel\u201c funkcija. Jo pagrindin\u0117 funkcija? Supaprastinkite duomen\u0173 valymo, importavimo ir pertvarkymo proces\u0105, kad sutaupytum\u0117te ne tik laiko, bet ir padidintum\u0117te nusivylim\u0105. Sutelkiant d\u0117mes\u012f \u012f efektyv\u0173 duomen\u0173 paruo\u0161im\u0105, [&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":[123,282,9],"class_list":["post-6706","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\/lt\/wp-json\/wp\/v2\/posts\/6706","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/comments?post=6706"}],"version-history":[{"count":1,"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/posts\/6706\/revisions"}],"predecessor-version":[{"id":6707,"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/posts\/6706\/revisions\/6707"}],"wp:attachment":[{"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/media?parent=6706"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/categories?post=6706"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/tags?post=6706"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}