Kaip naudoti „Power Query“ programoje „Excel“?
„Excel Power Query “ naudojama duomenų šaltiniams ieškoti, užmegzti ryšius su duomenų šaltiniais ir tada formuoti duomenis pagal mūsų analizės reikalavimus. Kai atliksime duomenų formavimą pagal mūsų poreikius, mes taip pat galime pasidalinti savo išvadomis ir sukurti įvairias ataskaitas naudodami daugiau užklausų.
Žingsniai
Iš esmės yra 4 veiksmai, o „Power Query“ šių 4 žingsnių tvarka yra tokia:
- Prisijungimas: pirmiausia mes prisijungiame prie duomenų, kurie gali būti kur nors, debesyje, tarnyboje ar vietoje.
- Transformuoti: Antras žingsnis būtų pakeisti duomenų formą pagal vartotojo reikalavimus.
- Sujungti: Šiame žingsnyje atliekame keletą transformavimo ir kaupimo veiksmų ir sujungiame abiejų šaltinių duomenis, kad gautume bendrą ataskaitą.
- Tvarkyti: tai sujungia ir prideda užklausos stulpelius su atitinkamais stulpeliais kitose darbaknygės užklausose.
Yra daug itin galingų „Excel Power Query“ funkcijų.
Tarkime, kad per pastaruosius 15 metų turime 180 failų pirkimo duomenų. Dabar organizacijos valdymui prieš juos analizuojant reikėtų susisteminti skaičius. Vadyba galėjo naudoti bet kurį iš šių būdų:
- Jie atidarydavo visus failus ir nukopijuodavo juos į vieną failą.
- Kita vertus, jie gali naudoti išmintingą sprendimą, t. Y. Taikyti formules, nes tai linkę į klaidas.
Nepriklausomai nuo pasirinkto metodo, jame atliekama daug rankų darbo, o po kelių mėnesių atsiras naujų pardavimo duomenų, susijusių su papildoma trukme. Jie vėl turės atlikti tą patį pratimą.
Tačiau „Power Query“ gali padėti jiems neatlikti šio varginančio ir pasikartojančio darbo. Leiskite mums suprasti šią „Excel“ užklausą su pavyzdžiu.
Pavyzdys
Tarkime, kad aplanke su pardavimo duomenimis turime tekstinių failų ir norime, kad šie duomenys būtų mūsų „Excel“ faile.
Kaip matome žemiau esančiame paveikslėlyje, aplanke yra dviejų tipų failai, tačiau norime gauti tik „Excel“ faile esančių tekstinių failų duomenis.
Norėdami tai padaryti, atlikite šiuos veiksmus:
1 žingsnis: Pirma, turime gauti „Power Query“ duomenis, kad galėtume atlikti reikiamus duomenų pakeitimus, kad galėtume juos importuoti į „Excel“ failą.
Daryti tą patį, mes pasirinkti "iš aplanko" parinktį "iš failo" meniu paspaudę komandą "Gauti duomenys" iš "Get & Transform" grupės į "duomenys" kortelėje.
2 žingsnis: Naršydami pasirinkite aplanko vietą.
Spustelėkite „Gerai“
3 žingsnis: Atsidarys dialogo langas, kuriame bus visų pasirinkto aplanko failų sąrašas su stulpelių antraštėmis „Turinys“, „Pavadinimas“, „Plėtinys“, „ Prieigos data“, „Pakeitimo data“, „Sukūrimo data“. „Atributai“ ir „Aplanko kelias“.
Yra 3 parinktys, ty Sujungti , Įkelti ir Transformuoti duomenis .
- Sujungti : ši parinktis naudojama norint pereiti į ekraną, kuriame galime pasirinkti, kuriuos duomenis derinti. Redagavimo veiksmas praleidžiamas naudojant šią parinktį ir mes negalime kontroliuoti, kuriuos failus derinti. „Combine“ funkcija konsoliduoja kiekvieną aplanko failą, o tai gali sukelti klaidų.
- Įkelti: ši parinktis tiesiog įkelia lentelę, kaip parodyta aukščiau paveikslėlyje, į „Excel“ darbalapį, o ne faktinius duomenis failuose.
- Transformuoti duomenis: skirtingai nei komandoje „Sujungti“ , jei mes naudojame šią komandą, galime pasirinkti, kuriuos failus derinti, ty galime sujungti tik vieno tipo failą (tą patį plėtinį).
Kaip ir mūsų atveju, mes norime sujungti tik tekstinius failus (.txt); pasirinksime komandą „Transformuoti duomenis“ .
Dešinėje lango pusėje galime pamatyti „Taikomus žingsnius“. Kol kas yra atliktas tik vienas veiksmas - išsami informacija apie failus iš aplanko.
4 žingsnis: Yra stulpelis pavadinimu „Plėtinys“, kuriame matome, kad stulpelio reikšmės parašytos abiem atvejais, ty didžiosiomis ir mažosiomis raidėmis.
Tačiau mes turime konvertuoti visas reikšmes į mažąsias raides, nes filtras atskiria abi. Daryti tą patį, mes turime pasirinkti stulpelį, tada pasirinkite "mažąją" iš "Format" komandą meniu.
5 žingsnis: Filtruosime duomenis naudodami tekstinių failų stulpelį „Plėtinys“ .
6 žingsnis: Dabar turime sujungti abiejų tekstinių failų duomenis naudodami pirmąjį stulpelį „Turinys“. Spustelėkite piktogramą, esančią dešinėje stulpelio pavadinimo pusėje.
7 žingsnis: Atsidarys dialogo langas su antrašte „Sujungti failus“ , kuriame teksto failams (failams su „.txt“ plėtiniu “) reikia pasirinkti skiriamąjį elementą kaip „ skirtukas “ ir pasirinkti duomenų tipo aptikimo bazę. Ir spustelėkite „Gerai“.
Paspaudę „Gerai “, lange „Power Query“ gausime bendrus tekstinių failų duomenis .
Pagal poreikį galime pakeisti stulpelių duomenų tipą. Už "Pajamos" skiltyje, mes pakeisti duomenų tipą "valiuta".
Duomenims pritaikytus veiksmus galime pamatyti naudodami maitinimo užklausą dešinėje lango pusėje.
Atlikę visus reikiamus pokyčius duomenimis, galime įkelti duomenis į Excel naudojant "Close & apkrova" komandą pagal "Uždaryti" grupę "Pagrindinis" kortelėje.
Turime pasirinkti, ar norime įkelti duomenis kaip lentelę, ar ryšį. Tada spustelėkite „Gerai“.
Dabar duomenis matome kaip lentelę darbalapyje.
Ir "darbaknygę užklausų" stiklas dešinėje pusėje, kuri mes galime naudoti redaguoti, dauginti, sujungimo, papildymo užklausas, ir daugeliui kitų tikslų.
„Excel Power Query“ yra labai naudinga, nes matome, kad per kelias minutes buvo įkelta 601 612 eilučių.
Ką reikia atsiminti
- „Power Query“ nepakeičia pirminių šaltinio duomenų. Užuot keisdamas pirminius šaltinio duomenis, jis įrašo kiekvieną veiksmą, kurį vartotojas atlieka sujungdamas ar transformuodamas duomenis, o vartotojui baigus formuoti duomenis, jis paima patobulintą duomenų rinkinį ir įtraukia juos į darbaknygę.
- „Power Query“ skiria didžiosios ir mažosios raidės.
- Konsoliduodami failus nurodytame aplanke, turime įsitikinti, kad naudodami stulpelį „Plėtinys“ turime atmesti laikinus failus (kurių plėtinys yra „.tmp“, o šių failų pavadinimas prasideda ženklu „~“) kaip „Power Query“ taip pat gali importuoti šiuos failus.
