Duomenų valymas ir importavimas naudojant „Power Query“ programoje „Excel“: žingsnis po žingsnio vadovas

Greitos nuorodos

Po debiuto programoje „Excel“, „Power Query“ pakeitė profesionalų duomenų tvarkymo būdą. Iš pradžių 2010 m. pristatytas kaip priedas, „Power Query“ nuo 2016 m. tapo nepakeičiama integruota „Excel“ funkcija. Jo pagrindinė funkcija? Supaprastinkite duomenų valymo, importavimo ir pertvarkymo procesą, kad sutaupytumėte ne tik laiko, bet ir padidintumėte nusivylimą. Sutelkiant dėmesį į efektyvų duomenų paruošimą, „Power Query“ leidžia sutelkti dėmesį į analizę ir įžvalgas.

Kas yra Power Query?

„Power Query“ iš esmės yra „Excel“ įrankis, skirtas duomenų transformavimui. Tai paprastas, bet galingas būdas gauti duomenis iš įvairių šaltinių, juos išvalyti ir paruošti analizei. „Power Query“ yra skirtuko „Duomenys“ skiltyje „Gauti ir transformuoti duomenis“, todėl naudotojai gali lengvai pasiekti visų įgūdžių lygius.

„Power Query“ įrankių vieta „Excel“ skirtuke Duomenys

Prisijunkite, kai pasineriame į praktinį „Power Query“ naudojimą, suteikdami jums žinių, kaip tiksliai išvalyti, importuoti ir tvarkyti duomenis.

„Excel“ duomenų valymas naudojant „Power Query“.

Viena iš mano naudojamų „Power Query“ programų yra duomenų valymas. Įsivaizduokite, kad į „Excel“ lapą įklijavote siautulingą el. pašto adresų sąrašą iš „Outlook“ el. laiško CC lauko. Jūsų misija? Paversti tuos netvarkingus duomenis į tvarkingą lentelę, kurioje yra vardai, pavardės ir el. pašto adresai. Nors „Excel“ funkcijos gali tai padaryti, „Power Query“ suteikia sklandžią patirtį, reikalaujančią minimalių pastangų.

El. pašto adresų, atskirtų kabliataškiais, sąrašas programoje „Excel“.

Norėdami pradėti, atlikite šiuos veiksmus:

Atidarykite „Power Query“: spustelėkite skirtuką „Duomenys“, tada pasirinkite „Iš lentelės/diapazono“. Šis langas paragins sukurti lentelę iš pasirinktų duomenų.
Pasirinkite savo duomenis: patvirtinkite, kad pasirinktas teisingas diapazonas, ir, jei jūsų duomenys neturi antraščių, palikite antraštės parinktį nepažymėtą prieš spustelėdami „Gerai“.

Spustelėjus „Gerai“, atsidaro „Power Query“ rengyklė, nustatanti duomenų transformavimo etapą.

Jūsų kita užduotis? Padalinkite el. pašto adresus į atskiras eilutes:

Stulpelio padalijimas: Dešiniuoju pelės mygtuku spustelėkite stulpelio antraštę, užveskite pelės žymeklį ant „Split Column“ ir pasirinkite „By Limiter“. Pasirinkite „Kabliataškis“ kaip skirtuką ir dialogo lange Išplėstinės parinktys pasirinkite „Eilės“.

Patvirtinus kiekvieną el. pašto adresą matysite tvarkingai suskirstytą į atskiras eilutes.

El. pašto adresai, atskirti į kelias eilutes Power Query redaktoriuje

Dabar atėjo laikas išgauti vardus ir pavardes. Norėdami tai padaryti, naudokite funkciją „Pridėti stulpelį iš pavyzdžių“:

Išskleisti pavadinimus: dukart spustelėkite stulpelio antraštę ir įveskite vardą arba pavadinimą, kad atkartotumėte šabloną. „Power Query“ automatiškai pasiūlys likusius įrašus!
Pervardyti stulpelius: būtinai pervardykite stulpelius tinkamai (pvz., „Vardas“, „Pavardė“, „El. pašto adresas“).
Paskutiniai palietimai: pakoreguokite stulpelių tvarką, vilkdami juos į norimas vietas.

Kadangi jūsų duomenys atrodo tvarkingi ir tvarkingi, nepamirškite užklausai priskirti prasmingą pavadinimą prieš uždarydami ir vėl įkeldami duomenis į darbaknygę.

Užklausos pavadinimo suteikimas Power Query redaktoriuje

Duomenų importavimas ir tvarkymas naudojant „Power Query“.

„Power Query“ puikiai tinka importuojant duomenis iš įvairių šaltinių, ne tik iš skaičiuoklių, pavyzdžiui, PDF failų ar svetainių. Šiame kitame skyriuje parodyta, kaip efektyviai išgauti ir pertvarkyti konkrečius duomenis iš esamos „Excel“ darbaknygės.

Pirmiausia pradėkite dar kartą naršydami į skirtuką „Duomenys“:

Pradėti importavimą: spustelėkite „Gauti duomenis“> „Iš failo“> „Iš Excel darbaknygės“.
Pasirinkite savo failą: suraskite failą, iš kurio norite importuoti duomenis, ir spustelėkite „Importuoti“, o po to spustelėkite „Transformuoti duomenis“, kad pasiektumėte „Power Query“ rengyklę.

Navigatoriaus langas, kuriame rodomi importuotini duomenys

„Power Query“ rengyklėje:

Perkelti eilutę į antraštes: spustelėkite „Naudoti pirmąją eilutę kaip antraštes“, kad iš karto nurodytumėte pirmąją eilutę kaip stulpelio antraštes.
Atšaukti duomenis: pasirinkite stulpelius, kurie atspindi jūsų unikalius kintamuosius, ir dešiniuoju pelės mygtuku spustelėkite, kad pasirinktumėte „Unpivot Other Columns“. Taip pakeičiamas jūsų išdėstymas, kiekviena unikali reikšmė pateikiama atskiroje eilutėje.

Nesukami duomenys, rodantys tvarkingą išdėstymą

Prieš įkeldami pertvarkytą lentelę atgal į darbaknygę, nepamirškite tiksliai sureguliuoti stulpelių pavadinimų ir apibrėžti teisingus duomenų tipus, kad išlaikytumėte duomenų vientisumą.

Mėnesio stulpelio duomenų tipo nustatymas Power Query redaktoriuje

Baigę transformacijas paspauskite „Uždaryti ir įkelti“> „Uždaryti ir įkelti“, kad nustatytumėte, kur jūsų duomenys bus rodomi „Excel“.

„Power Query“ galia yra jos gebėjimas palaikyti dinaminius ryšius su savo duomenų šaltiniais. Pavyzdžiui, jei pasikeičia pradinis duomenų rinkinys, tiesiog dešiniuoju pelės mygtuku spustelėkite naują lentelę ir pasirinkite „Atnaujinti“, kad ją sklandžiai atnaujintumėte.

Duomenų atnaujinimas Power Query lentelėje

Dėl šios funkcijos „Power Query“ yra žaidimų keitiklis, skirtas dinaminei duomenų analizei – duomenų rinkinių atnaujinimas neautomatinio kopijavimo ir įklijavimo metu.

Pasiruošę perkelti savo duomenų valdymo įgūdžius į kitą lygį? Toliau naršykite „Power Query“ dirbdami su lentelėmis iš žiniatinklio ir patirdami įvairias jos importavimo galimybes. Rasite šį įrankį, kuris greitai padidins jūsų produktyvumą ir tikslumą!

Papildomos įžvalgos

1. Kokių tipų duomenų šaltinius galiu naudoti su Power Query?

„Power Query“ palaiko įvairius duomenų šaltinius, įskaitant „Excel“ failus, CSV failus, tinklalapius, duomenų bazes ir kt. Dėl to jis yra labai universalus duomenų importavimo ir transformavimo užduotims atlikti.

2. Ar galiu automatiškai atnaujinti duomenis, importuotus naudojant Power Query?

Taip, galite nustatyti automatinį „Power Query“ duomenų rinkinių atnaujinimą. „Excel“ leidžia konfigūruoti atnaujinimo intervalus, kad jūsų duomenys būtų atnaujinami be rankinio įsikišimo.

3. Ar yra apribojimas, kiek duomenų galiu importuoti naudojant „Power Query“?

Nors „Power Query“ gali apdoroti didelius duomenų rinkinius, našumas gali sumažėti naudojant pernelyg didelius rinkinius, ypač jei jie viršija „Excel“ eilučių limitą – 1 048 576. Norint pagerinti apdorojimo našumą, prieš importuojant patartina filtruoti arba apibendrinti duomenis.

Šaltinis ir vaizdai