Power Query Excel darbā: kā pāriet no manuālas datu apstrādes uz atkārtojamu procesu
Updated: 3. sept.
Ar Excel strādāju vairāk nekā 25 gadus, un viena no situācijām, ko uzņēmumos redzu ļoti bieži, ir pārsteidzoši vienkārša: cilvēki nedēļu pēc nedēļas vai mēnesi pēc mēneša atkārto vienas un tās pašas darbības ar datiem.
Tiek atvērti faili, kopētas rindas, pārsauktas kolonnas, laboti datumu formāti, apvienotas tabulas, pārbaudīti tukšie lauki un tikai pēc tam sākas pati analīze.
Šāds process ar laiku kļūst tik ierasts, ka mēs to vairs neuztveram kā problēmu. Tā vienkārši ir daļa no ikmēneša atskaites sagatavošanas.
Tieši šādās situācijās Power Query var dot vienu no būtiskākajiem produktivitātes ieguvumiem darbā ar Excel.
Tā galvenā vērtība nav tikai iespēja kādu atsevišķu darbību paveikt ātrāk. Daudz būtiskāk ir tas, ka Power Query ļauj vienreiz izveidot datu sagatavošanas procesu un pēc tam šo pašu darbību secību izmantot atkārtoti ar jauniem datiem.
Kas ir Power Query?
Power Query ir Excel un Power BI vidē pieejams rīks datu iegūšanai un sagatavošanai.
Ar to var:
ielasīt datus no Excel, CSV un citiem avotiem;
atlasīt vajadzīgās kolonnas;
izdzēst liekās rindas;
mainīt kolonnu nosaukumus;
sakārtot datu tipus;
sadalīt vai apvienot kolonnas;
filtrēt datus;
savienot vairākas tabulas;
apvienot vairākus līdzīgas struktūras failus;
sagatavot datus PivotTable, Power Pivot vai Power BI analīzei.
Taču šo funkciju saraksts vēl nepasaka svarīgāko.
Power Query saglabā darbību secību.
Ja šodien esmu definējis, ka no faila jāizņem pirmās trīs rindas, jāpārsauc piecas kolonnas, datuma kolonnai jāpiešķir datuma tips un rezultāts jāsavieno ar klientu tabulu, nākamreiz Excel var atkārtot šo pašu procesu ar jauniem datiem.
Tieši šeit Power Query būtiski atšķiras no manuālas datu labošanas.
Power Query vērtība sākas ar atkārtošanos
Kad konsultācijās vai mācībās vērtēju kādu Excel procesu, viens no pirmajiem jautājumiem ir:
“Cik bieži Tu šo dari?”
Ja atbilde ir:
katru dienu;
katru nedēļu;
katru mēnesi;
katru reizi, kad saņemu jaunu failu,
tad es jau sāku skatīties, vai daļu procesa iespējams padarīt atkārtojamu.
Iedomāsimies ļoti tipisku situāciju.
Katru mēnesi grāmatvedības, pārdošanas vai noliktavas sistēma izveido jaunu Excel vai CSV failu.
Darbinieks:
atver failu;
izdzēš nevajadzīgās rindas;
pārsauc kolonnas;
izlabo datumus;
sakārto skaitļu formātus;
pievieno papildu informāciju;
pārkopē datus uz kopējo failu;
atjauno PivotTable;
pārbauda rezultātu.
Ja katru mēnesi tiek veiktas gandrīz vienas un tās pašas darbības, es to vairs neuztveru kā deviņus atsevišķus uzdevumus.
Es redzu vienu procesu, kuru vajadzētu sakārtot.
Kāpēc Power Query bieži dod lielāku ieguvumu nekā vēl viena formula?
Excel lietotāji ļoti bieži mēģina nākamo problēmu atrisināt ar nākamo formulu.
Tas ir saprotami. Formulas ir spēcīgs instruments, un daudzās situācijās tās ir tieši pareizā izvēle.
Taču ir svarīgi atšķirt divus dažādus uzdevumus.
Aprēķināt datus un sagatavot datus aprēķinam nav viens un tas pats.
Ja man vajag aprēķināt pārdošanas summu noteiktam klientam, SUMIFS var būt lieliska izvēle.
Ja man katru mēnesi jāatver 15 faili, jāizņem liekās kolonnas, jāvienādo struktūra un jāsaliek viss vienā tabulā, formula vairs nerisina galveno problēmu.
Te daudz piemērotāks kļūst Power Query.
Tāpēc, izvēloties rīku, es iesaku vispirms pajautāt:
Kur tieši rodas darbs?
Aprēķinos?
Datu sagatavošanā?
Failu apvienošanā?
Pārskatā?
Kad problēma ir precīzi nosaukta, arī piemērotākais rīks kļūst daudz skaidrāks.
Situācijas, kurās Power Query izmantoju visbiežāk
1. Katru mēnesi tiek saņemts jauns fails
Šī ir Power Query klasika.
Piemēram:
janvāris.xlsx
februāris.xlsx
marts.xlsx
aprīlis.xlsx
Manuāli šos failus var atvērt un kopēt vienā kopējā tabulā.
Taču brīdī, kad failu kļūst vairāk un process atkārtojas regulāri, daudz vērtīgāk ir izveidot vaicājumu, kas datus paņem no mapes un apvieno automātiski.
Tad nākamajā mēnesī process var būt ļoti vienkāršs:
ieliec jauno failu mapē → Refresh → pārbaudi rezultātu.
2. Dati tiek eksportēti no citas sistēmas
Daudzi sistēmu eksporti ir paredzēti datu izdošanai, nevis ērtai Excel analīzei.
Rezultātā varam saņemt:
liekas rindas augšpusē;
nevajadzīgas kolonnas;
neveiklus kolonnu nosaukumus;
skaitļus teksta formātā;
datumus, kurus Excel neatpazīst kā datumus;
tukšas vērtības;
informāciju, kas jāsadala vairākās kolonnās.
Power Query ļauj definēt, kā šis eksports jāpārveido, pirms sākas analīze.
Un, ja sistēma nākamajā nedēļā izveido tādas pašas struktūras failu, iepriekš izveidotos soļus iespējams izmantot atkārtoti.
3. Jāapvieno informācija no vairākām tabulām
Piemēram, vienā tabulā ir:
pārdošanas darījumi
citā:
klienti
vēl citā:
produktu kategorijas
un vēl vienā:
budžets.
Protams, daļu no tā iespējams risināt ar XLOOKUP vai citām formulām.
Taču regulārā datu sagatavošanas procesā Power Query Merge operācija var būt pārskatāmāks risinājums.
Īpaši tad, ja savienošanas process atkārtojas.
4. Dati jāsagatavo PivotTable
PivotTable ļoti labi strādā ar pareizi strukturētu datu tabulu.
Ideālā gadījumā:
viena rinda ir viens ieraksts;
katrai kolonnai ir viens datu lauks;
ir viena virsrakstu rinda;
datu tipi ir konsekventi;
tabulas vidū nav starprezultātu.
Power Query var būt ļoti labs posms starp avota datiem un PivotTable.
Tas sagatavo vienotu datu tabulu, bet PivotTable pēc tam dara to, ko prot vislabāk - apkopo un analizē.
5. Vienas un tās pašas datu labošanas darbības atkārtojas atkal un atkal
Kolonnas pārdēvēšana pati par sevi aizņem dažas sekundes.
Datuma formāta labošana - varbūt minūti.
Nevajadzīgu rindu dzēšana - vēl pāris minūšu.
Bet, ja šīs darbības veic:
katru dienu;
vairākos failos;
vairāki kolēģi;
gadu no gada,
mazās darbības pārvēršas ievērojamā darba apjomā.
Power Query produktivitātes ieguvums bieži slēpjas tieši šeit.
Nevis vienā milzīgā automatizācijā, bet daudzu mazu atkārtotu darbību aizstāšanā ar vienu definētu procesu.
Praktisks piemērs: ikmēneša pārdošanas atskaite
Pieņemsim, ka uzņēmumā katru mēnesi jāgatavo pārdošanas atskaite.
Pārdošanas sistēma izveido failu ar šādām kolonnām:
datums;
klienta kods;
produkta kods;
daudzums;
ieņēmumi.
Vadības atskaitei papildus vajag:
klienta nosaukumu;
klienta grupu;
produkta kategoriju;
budžeta salīdzinājumu;
pārdošanas rezultātu pa mēnešiem.
Manuālais process
Katru mēnesi darbinieks:
eksportē failu;
pārkopē datus kopējā Excel failā;
izlabo kolonnu nosaukumus;
pārbauda datumu formātu;
ar formulām pievieno klientu nosaukumus;
ar vēl vienu formulu pievieno produktu kategorijas;
pārbauda, vai formulas ir novilktas līdz pēdējai rindai;
atjauno PivotTable;
salīdzina kopsummas.
Process strādā, taču katrā atjaunošanas reizē tas joprojām ir atkarīgs no cilvēka uzmanības un precīzas darbību secības.
Process ar Power Query
Power Query var ielasīt pārdošanas failu un automātiski:
izvēlēties vajadzīgās kolonnas;
noteikt datu tipus;
savienot klientu tabulu;
savienot produktu tabulu;
pievienot budžeta informāciju;
sagatavot gala tabulu analīzei.
Nākamajā mēnesī ir jauni darījumi, bet datu sagatavošanas loģika paliek tā pati.
Tad darba process kļūst:
jauni dati → Refresh → kontroles pārbaude → analīze.
Šī ir ļoti būtiska pārmaiņa.
Cilvēks pavada mazāk laika datu mehāniskā sagatavošanā un vairāk laika var veltīt jautājumiem:
kas šomēnes mainījās?
kuri klienti auguši?
kuri produkti zaudējuši apgrozījumu?
kur ir lielākās novirzes pret budžetu?
ko mums ar šo informāciju darīt?
Tas jau ir daudz vērtīgāks darbs.
Ko Power Query nozīmē “automatizācija”?
Vārds automatizācija reizēm rada nepareizu priekšstatu.
Var šķist, ka Power Query pats visu izdarīs fonā bez cilvēka iesaistes.
Es to skaidroju vienkāršāk.
Power Query automatizē iepriekš definētu darbību secību.
Tu nosaki:
no kurienes ņemt datus;
kuras kolonnas vajadzīgas;
ko pārsaukt;
ko izdzēst;
kā mainīt datu tipus;
ko ar ko savienot;
kādam jābūt gala rezultātam.
Power Query šo secību saglabā.
Kad dati mainās, izvēlamies Refresh, un rīks atkārto definētos soļus ar jaunajiem datiem.
Tieši tā es visbiežāk lietoju vārdu “automatizēt” Power Query kontekstā:
nevis izņemt cilvēku no procesa, bet izņemt no procesa atkārtotas mehāniskas darbības.
Power Query, formulas, Power Pivot vai Power BI?
Šos rīkus ir viegli sajaukt, jo tie visi strādā ar datiem.
Es tos nošķirtu pēc uzdevuma.
Excel formulas → aprēķina
Izmantoju, kad vajag:
veikt aprēķinus;
salīdzināt vērtības;
izveidot nosacījumus;
atrast informāciju;
izveidot darba tabulas.
Formula atbild uz aprēķina jautājumu.
Power Query → sagatavo datus
Izmantoju, kad vajag:
ielasīt datus;
tīrīt datus;
mainīt struktūru;
apvienot failus;
savienot tabulas;
atkārtot datu sagatavošanas procesu.
Power Query sagatavo datus.
Power Pivot → veido datu modeli un aprēķinus
Power Pivot kļūst vērtīgs, kad jāveido:
vairāku tabulu datu modelis;
attiecības starp tabulām;
centralizēti aprēķini;
DAX mēri.
Power Pivot organizē datu modeli un aprēķinus tajā.
Power BI → analizē un vizualizē
Power BI izmantoju, kad nepieciešams:
plašāks datu modelis;
interaktīvi pārskati;
analītika;
vizualizācijas;
pārskatu publicēšana un koplietošana.
Power BI palīdz pārvērst datu modeli interaktīvā analītikas risinājumā.
Šie rīki viens otru neaizvieto.
Tie ļoti bieži strādā kopā.
Excel formulas → aprēķina.
Power Query → sagatavo datus.
Power Pivot → veido datu modeli un aprēķinus.
Power BI → analizē un vizualizē.
Ja vēlies saprast, kur Power Query atrodas kopējā Excel un datu analīzes prasmju attīstības ceļā, izlasi arī manu ceļvedi par Excel un datu analīzes prasmēm darbā - no Excel līdz Power BI.
Kad vienkāršāks Excel risinājums būs piemērotāks?
Labs profesionāls ieradums ir nevis izmantot modernāko rīku, bet izvēlēties vienkāršāko rīku, kas labi atrisina konkrēto uzdevumu.
Ja man ir neliela tabula un jāizveido viens aprēķins, formula var būt daudz ātrāka.
Ja vajag vienreiz apkopot datus, PivotTable var pilnībā atrisināt situāciju.
Power Query īpaši vērtīgs kļūst tad, kad parādās:
atkārtošanās + datu sagatavošana + paredzama struktūra.
Es arī nesteigtos automatizēt procesu, kamēr nav skaidrs:
kādi ir avota dati;
kādam jābūt gala rezultātam;
kuras darbības tiešām atkārtojas;
kā pārbaudīsim rezultāta pareizību.
Labs Power Query risinājums sākas ar labu procesa izpratni.
Biežākās kļūdas, sākot strādāt ar Power Query
Mēģināt automatizēt haosu
Ja katru mēnesi fails izskatās pilnīgi citādi, kolonnu nosaukumi mainās un dati tiek ievietoti nejaušās vietās, arī automatizēts process kļūst trausls.
Tāpēc ļoti bieži mans pirmais solis ir:
vispirms stabilizējam datu struktūru.
Tikai pēc tam automatizējam.
Būvēt procesu, vēl nesaprotot gala rezultātu
Power Query ļauj izveidot ļoti daudz transformāciju.
Taču pirms tam vajag atbildēt:
Ko es gribu iegūt beigās?
Vienu darījumu tabulu?
Kopsavilkumu?
Datu avotu PivotTable?
Datu modeli?
Power BI ievades tabulu?
Jo skaidrāks gala rezultāts, jo vienkāršāks kļūst vaicājums.
Automatizēt visu uzreiz
Sākot Power Query, bieži rodas vēlme pārtaisīt visu esošo Excel sistēmu.
Es parasti iesaku pretēju pieeju.
Izvēlies vienu atkārtotu procesu.
Piemēram:
ikmēneša piecu failu apvienošanu.
Sakārto to.
Pārbaudi.
Izmanto dažus mēnešus.
Tad ķeries pie nākamā procesa.
Tā tiek uzbūvēta daudz stabilāka sistēma.
Aizmirst par pārbaudi
Refresh poga ir ļoti ērta.
Taču Refresh nenozīmē:
“rezultāts automātiski ir pareizs.”
Es vienmēr iesaku izveidot kontroles punktus.
Piemēram:
ierakstu skaits;
kopējā summa;
minimālais un maksimālais datums;
tukšo vērtību skaits;
kļūdu skaits;
salīdzinājums ar avotu.
Automatizācija un kontrole iet kopā.
Kā saprast, vai Power Query ir Tavs nākamais solis?
Paskaties uz savu tipisko darba nedēļu un atbildi uz šiem jautājumiem.
Vai regulāri kopē datus no viena Excel faila uz citu?
Vai katru nedēļu vai mēnesi saņem līdzīgas struktūras eksportu?
Vai apvieno vairākus Excel vai CSV failus vienā tabulā?
Vai pirms atskaites sagatavošanas vienmēr labo vienas un tās pašas kolonnas?
Vai daudz laika pavadi datu tīrīšanā?
Vai formulas galvenokārt izmanto, lai savilktu kopā vairākas tabulas?
Vai katru mēnesi jāatceras viena un tā pati darbību secība?
Vai atskaites sagatavošanu pilnībā pārzina tikai viens cilvēks?
Vai vēlētos, lai liela daļa sagatavošanas procesa notiktu ar Refresh?
Ja vairākos no šiem punktiem atpazīsti savu ikdienas darbu, Power Query, visticamāk, var palīdzēt pārvērst daļu manuālo darbību atkārtojamā un pārskatāmākā datu sagatavošanas procesā.
Power Query un mākslīgais intelekts
Arī Power Query darbā arvien lielāku lomu ieņem mākslīgais intelekts.
AI var palīdzēt:
izskaidrot M kodu;
uzrakstīt sarežģītāku transformāciju;
atrast kļūdu;
ieteikt datu pārveidošanas pieeju;
izskaidrot, kāpēc konkrēts vaicājums nestrādā.
Tas ir ļoti vērtīgs palīgs.
Taču arī šeit galvenā prasme paliek cilvēkam: saprast savu datu procesu.
Ja zinu, kādam rezultātam jābūt, kā izskatās avota dati un kā jāpārbauda iznākums, AI var ievērojami paātrināt darbu.
Ja procesa loģika ir neskaidra, arī ļoti labs ģenerēts M kods problēmu līdz galam neatrisinās.
Tāpēc es Power Query apguvi joprojām sāktu nevis ar M valodu, bet ar datu struktūru un transformāciju loģiku.
Kā es ieteiktu sākt apgūt Power Query?
Pirmajam uzdevumam izvēlies kaut ko, ko jau ļoti labi pazīsti.
Piemēram:
ikmēneša pārdošanas failu.
Tad soli pa solim:
ielādē failu Power Query;
izdzēs liekās kolonnas;
maini kolonnu nosaukumus;
pārbaudi datu tipus;
filtrē nevajadzīgās rindas;
ielādē rezultātu atpakaļ Excel;
nomaini avota faila datus;
nospied Refresh;
pārbaudi rezultātu.
Kad redzi, ka tas darbojas, pievieno nākamo soli.
Piemēram:
vairāku failu apvienošanu;
tabulu savienošanu;
nosacījumu kolonnas;
grupēšanu.
Šādā veidā Power Query ļoti ātri kļūst saprotams.
Ko Power Query maina darba ikdienā?
Man Power Query galvenais ieguvums nav pati tehnoloģija.
Tas ir cits veids, kā domāt par atkārtotu darbu.
Manuālā procesā mēs katru reizi domājam:
Tagad izdaru pirmo soli.Tad otro.Tad trešo.
Power Query procesā mēs domājam:
Kāda ir pareizā darbību secība, kuru vēlos izmantot katru reizi?
Tā ir būtiska atšķirība.
Vienā gadījumā mēs izpildām procesu. Otrā - uzbūvējam procesu.
Un tieši šī domāšanas maiņa bieži ir vērtīgākais, ko cilvēks iegūst, sākot strādāt ar Power Query.
Secinājums: labs Power Query risinājums sākas ar procesu, nevis rīku
Ja ikdienas Excel darbs prasa regulāru datu kopēšanu, failu apvienošanu, kolonnu tīrīšanu un vienu un to pašu darbību atkārtošanu, es vispirms skatītos uz procesu.
Kuras darbības atkārtojas?
Kuras iespējams standartizēt?
Kādi ir avota dati?
Kādam jābūt gala rezultātam?
Kā pārbaudīsim, ka rezultāts ir pareizs?
Kad uz šiem jautājumiem ir atbildes, Power Query kļūst ļoti loģisks.
Tā lielākā vērtība ir iespēja pārvērst manuālu darbību virkni atkārtojamā, pārskatāmā un pārbaudāmā datu sagatavošanas procesā.
Un brīdī, kad datu sagatavošana aizņem mazāk laika, mēs varam vairāk uzmanības veltīt pašai analīzei.
Manā skatījumā tas arī ir galvenais mērķis.
Vēlies apgūt Power Query praktiski?
Ja Power Query izklausās pēc prasmes, kas varētu būt noderīga arī Tavā darbā, nākamais solis ir sākt ar vienu konkrētu procesu un apgūt rīku praktiski.
Excel Know How mācībās Power Query apgūstam kopā ar datu struktūru, Power Pivot un datu modeļa veidošanu - ar praktiskiem piemēriem un situācijām, kas līdzinās ikdienas darbam.
Ja vēl neesi pārliecināts, vai Tavs nākamais solis ir Power Query, Excel prasmes vai Power BI, iesaku izlasīt arī:
Ja Power Query ir Tavs nākamais solis un vēlies to apgūt praktiski klātienē, apskati arī LIFT Power Query, Power Pivot un datu analīzes kursus.
Biežāk uzdotie jautājumi par Power Query
Kas ir Power Query vienkāršiem vārdiem?
Power Query ir Excel un Power BI rīks datu iegūšanai un sagatavošanai. Tas ļauj definēt datu tīrīšanas, pārveidošanas un apvienošanas soļus un pēc tam tos izmantot atkārtoti ar jauniem datiem.
Kam visbiežāk izmanto Power Query?
Īpaši bieži to izmanto, lai apvienotu failus, sagatavotu sistēmu eksportus, sakārtotu datu formātus, savienotu vairākas tabulas un sagatavotu datus PivotTable, Power Pivot vai Power BI.
Vai Power Query aizstāj Excel formulas?
Katram rīkam ir cita loma. Power Query galvenokārt izmanto datu sagatavošanai, savukārt formulas ļoti labi piemērotas aprēķiniem un darba tabulu loģikai. Praktiskos risinājumos abus bieži izmanto kopā.
Vai Power Query ir tas pats, kas Power BI?
Power Query ir datu iegūšanas un transformēšanas rīks, kas pieejams gan Excel, gan Power BI. Power BI savukārt ir plašāka datu analīzes un interaktīvu pārskatu platforma.
Kāda ir atšķirība starp Power Query un Power Pivot?
Power Query sagatavo datus. Power Pivot palīdz veidot savstarpēji saistītu tabulu datu modeli un aprēķinus ar DAX. Tos bieži izmanto secīgi vienā risinājumā.
Vai Power Query var apvienot vairākus Excel failus?
Jā. Viens no praktiskākajiem Power Query pielietojumiem ir vairāku līdzīgas struktūras failu apvienošana no mapes vienā kopējā datu tabulā.
Vai Power Query ir piemērots ikmēneša atskaitēm?
Jā, īpaši tad, ja katru mēnesi saņem līdzīgas struktūras datus un atkārto vienus un tos pašus sagatavošanas soļus. Šādā situācijā Power Query var pārvērst manuālu darbību virkni atkārtojamā procesā.
Vai, izmantojot Power Query, joprojām jāpārbauda dati?
Jā. Automatizēts process padara darbības konsekventākas, taču datu kvalitātes un rezultāta pārbaude joprojām ir būtiska darba daļa.
Vai Power Query apguvei jāzina programmēšana?
Sākuma līmenī Power Query var ļoti labi izmantot ar grafisko saskarni. M valodas zināšanas kļūst vērtīgas sarežģītākos risinājumos, bet tās nav priekšnoteikums, lai sāktu praktiski strādāt ar Power Query.
Komentāri