Saturs
Problēmas formulēšana
Apskatīsim skaistu risinājumu vienai no ļoti standarta situācijām, ar kurām agrāk vai vēlāk saskaras lielākā daļa Excel lietotāju: jums ir ātri un automātiski jāapkopo dati no liela skaita failu vienā gala tabulā.
Pieņemsim, ka mums ir šāda mape, kurā ir vairāki faili ar datiem no filiāļu pilsētām:
Failu skaitam nav nozīmes, un tas var mainīties nākotnē. Katram failam ir lapa ar nosaukumu Atlaideskur atrodas datu tabula:
Rindu (pasūtījumu) skaits tabulās, protams, ir atšķirīgs, bet kolonnu komplekts visur ir standarta.
Uzdevums: apkopot datus no visiem failiem vienā grāmatā ar sekojošu automātisku atjaunināšanu, pievienojot vai dzēšot pilsētas failus vai tabulas rindas. Pēc galīgās konsolidētās tabulas, tad būs iespējams veidot jebkādas atskaites, pivot tabulas, filtrēt-kārtot datus utt. Galvenais, lai varētu savākt.
Mēs izvēlamies ieročus
Risinājumam mums ir nepieciešama jaunākā Excel 2016 versija (vajadzīgā funkcionalitāte tajā jau ir iebūvēta pēc noklusējuma) vai iepriekšējās Excel 2010–2013 versijas ar instalētu bezmaksas pievienojumprogrammu. Jaudas vaicājums no Microsoft (lejupielādējiet to šeit). Power Query ir īpaši elastīgs un jaudīgs rīks datu ielādei programmā Excel no ārpasaules, pēc tam to noņemšanai un apstrādei. Power Query atbalsta gandrīz visus esošos datu avotus — no teksta failiem līdz SQL un pat Facebook 🙂
Ja jums nav Excel 2013 vai 2016, tad jūs nevarat lasīt tālāk (vienkārši jokoju). Vecākajās Excel versijās šādu uzdevumu var paveikt, tikai ieprogrammējot makro programmā Visual Basic (kas ir ļoti grūti iesācējiem) vai monotonu manuālu kopēšanu (kas aizņem ilgu laiku un rada kļūdas).
1. darbība. Importējiet vienu failu kā paraugu
Vispirms kā piemēru importēsim datus no vienas darbgrāmatas, lai programma Excel “paņemtu ideju”. Lai to izdarītu, izveidojiet jaunu tukšu darbgrāmatu un…
- ja jums ir Excel 2016, atveriet cilni Datums un tad Izveidot vaicājumu - no faila - no grāmatas (Dati — jauns vaicājums — no faila — no Excel)
- ja jums ir programma Excel 2010–2013 ar instalētu pievienojumprogrammu Power Query, atveriet cilni Jaudas vaicājums un atlasiet to No faila – No grāmatas (No faila — no Excel)
Pēc tam atvērtajā logā dodieties uz mūsu mapi ar atskaitēm un atlasiet kādu no pilsētas failiem (nav svarīgi, kuru, jo tie visi ir tipiski). Pēc pāris sekundēm vajadzētu parādīties Navigator logam, kurā kreisajā pusē jāizvēlas mums vajadzīgā lapa (Pārdošana), un tās saturs tiks parādīts labajā pusē:
Noklikšķinot uz pogas šī loga apakšējā labajā stūrī Download (Ielādēt), tad tabula tiks nekavējoties importēta uz lapu tās sākotnējā formā. Vienam failam tas ir labi, taču mums ir jāielādē daudzi šādi faili, tāpēc mēs rīkosimies nedaudz savādāk un noklikšķiniet uz pogas Labojums (Edit). Pēc tam Power Query vaicājumu redaktors ir jāparāda atsevišķā logā ar mūsu datiem no grāmatas:
Šis ir ļoti jaudīgs rīks, kas ļauj “pabeigt” tabulu līdz vajadzīgajam skatam. Pat virspusējs visu tā funkciju apraksts aizņemtu apmēram simts lappuses, bet, ja ļoti īsi, izmantojot šo logu, jūs varat:
- filtrējiet nevajadzīgos datus, tukšas rindas, rindas ar kļūdām
- kārtot datus pēc vienas vai vairākām kolonnām
- atbrīvoties no atkārtošanās
- sadaliet lipīgo tekstu pa kolonnām (pēc norobežotājiem, rakstzīmju skaita utt.)
- sakārtojiet tekstu (noņemiet papildu atstarpes, labojiet reģistru utt.)
- pārveidot datu tipus visos iespējamos veidos (pārvērst skaitļus, piemēram, tekstu, parastos skaitļos un otrādi)
- transponēt (pagriezt) tabulas un izvērst divdimensiju krusteniskās tabulas plakanās
- pievienojiet tabulai papildu kolonnas un izmantojiet tajās formulas un funkcijas, izmantojot Power Query iebūvēto M valodu.
- ...
Piemēram, pievienosim tabulai kolonnu ar mēneša teksta nosaukumu, lai vēlāk būtu vieglāk veidot rakurstabulas atskaites. Lai to izdarītu, ar peles labo pogu noklikšķiniet uz kolonnas virsraksta datiun atlasiet komandu Dublēt kolonnu (Dublēt kolonnu)un pēc tam ar peles labo pogu noklikšķiniet uz parādītās kolonnas dublikāta galvenes un atlasiet Komandas Transformācija – Mēnesis – Mēneša nosaukums:
Katrai rindai jāveido jauna kolonna ar mēneša teksta nosaukumiem. Veicot dubultklikšķi uz kolonnas virsraksta, varat to pārdēvēt no Kopēt datumu uz ērtāku MĒNESĪ, piem.
Ja dažās kolonnās programma nav īsti pareizi atpazinusi datu tipu, varat tai palīdzēt, noklikšķinot uz formāta ikonas katras kolonnas kreisajā pusē:
Varat izslēgt rindas ar kļūdām vai tukšām rindām, kā arī nevajadzīgus vadītājus vai klientus, izmantojot vienkāršu filtru:
Turklāt visas veiktās transformācijas tiek fiksētas labajā panelī, kur tās vienmēr var atvilkt atpakaļ (krusts) vai mainīt to parametrus (pārnesums):
Viegli un eleganti, vai ne?
2. solis. Pārveidosim savu pieprasījumu par funkciju
Lai pēc tam atkārtotu visas datu transformācijas, kas veiktas katrai importētajai grāmatai, mums ir jāpārvērš mūsu izveidotais pieprasījums par funkciju, kas pēc tam tiks lietota visiem mūsu failiem. To izdarīt patiesībā ir ļoti vienkārši.
Vaicājumu redaktorā atveriet cilni Skats un noklikšķiniet uz pogas Uzlabots redaktors (Skatīt — uzlabotais redaktors). Jāatveras logs, kurā visas mūsu iepriekšējās darbības tiks ierakstītas koda formā M valodā. Lūdzu, ņemiet vērā, ka ceļš uz failu, kuru importējām piemērā, ir kodēts kodā:
Tagad veiksim dažus pielāgojumus:
To nozīme ir vienkārša: pirmā rinda (failu ceļš)=> pārvērš mūsu procedūru par funkciju ar argumentu faila ceļš, un tālāk mēs mainām fiksēto ceļu uz šī mainīgā lieluma vērtību.
Visi. Klikšķiniet uz apdare un vajadzētu redzēt šo:
Nebaidieties, ka dati ir pazuduši – patiesībā viss ir kārtībā, visam vajadzētu izskatīties šādi 🙂 Esam veiksmīgi izveidojuši savu pielāgoto funkciju, kur viss datu importēšanas un apstrādes algoritms tiek iegaumēts, nesaistot to ar konkrētu failu . Atliek piešķirt tai saprotamāku nosaukumu (piemēram getData) paneļa labajā pusē Vārds un tu vari novākt Sākums — aizveriet un lejupielādējiet (Sākums — aizvērt un ielādēt). Lūdzu, ņemiet vērā, ka kodā ir iekodēts ceļš uz failu, kuru importējām piemērā. Jūs atgriezīsities galvenajā Microsoft Excel logā, bet labajā pusē jāparādās panelim ar izveidoto savienojumu ar mūsu funkciju:
3. darbība. Visu failu apkopošana
Viss grūtākais ir aiz muguras, patīkamā un vieglā paliek. Dodieties uz cilni Dati - Izveidot vaicājumu - No faila - No mapes (Dati — jauns vaicājums — no faila — no mapes) vai, ja jums ir Excel 2010–2013, līdzīgi kā cilnē Jaudas vaicājums. Parādītajā logā norādiet mapi, kurā atrodas visi mūsu avota pilsētas faili, un noklikšķiniet uz OK. Nākamajā darbībā jāatver logs, kurā tiks uzskaitīti visi šajā mapē (un tās apakšmapēs) atrastie Excel faili un informācija par katru no tiem:
Noklikšķiniet Mainīt (Edit) un atkal mēs nokļūstam pazīstamajā vaicājumu redaktora logā.
Tagad mums tabulai jāpievieno vēl viena kolonna ar mūsu izveidoto funkciju, kas “izvilks” datus no katra faila. Lai to izdarītu, dodieties uz cilni Pievienot kolonnu — pielāgota kolonna (Pievienot kolonnu — pievienot pielāgotu kolonnu) un parādītajā logā ievadiet mūsu funkciju getData, norādot tam kā argumentu pilnu ceļu uz katru failu:
Pēc noklikšķināšanas uz OK izveidotā kolonna jāpievieno mūsu tabulai labajā pusē.
Tagad izdzēsīsim visas nevajadzīgās kolonnas (kā programmā Excel, izmantojot peles labo pogu - Izņemt), atstājot tikai pievienoto kolonnu un kolonnu ar faila nosaukumu, jo šo nosaukumu (precīzāk, pilsētu) noderēs katras rindas kopējos datos.
Un tagad "wow moment" - noklikšķiniet uz ikonas ar savām bultiņām pievienotās kolonnas augšējā labajā stūrī ar mūsu funkciju:
… noņemiet atzīmi Izmantojiet sākotnējo kolonnas nosaukumu kā prefiksu (Izmantojiet sākotnējo kolonnas nosaukumu kā prefiksu)un noklikšķiniet OK. Un mūsu funkcija ielādēs un apstrādās datus no katra faila, ievērojot ierakstīto algoritmu un apkopojot visu kopējā tabulā:
Lai iegūtu pilnīgu skaistumu, varat arī noņemt .xlsx paplašinājumus no pirmās kolonnas ar failu nosaukumiem — standarta aizstājot ar "neko" (ar peles labo pogu noklikšķiniet uz kolonnas galvenes - Aizstājējs) un pārdēvējiet šo kolonnu uz Pilsēta. Un arī izlabojiet datu formātu kolonnā ar datumu.
Visi! Klikšķiniet uz Sākums — aizvērt un ielādēt (Sākums — aizvērt un ielādēt). Visi vaicājumā savāktie dati par visām pilsētām tiks augšupielādēti pašreizējā Excel lapā “viedās tabulas” formātā:
Izveidotais savienojums un mūsu montāžas funkcija nekādā veidā nav jāsaglabā atsevišķi – tie tiek saglabāti kopā ar pašreizējo failu parastajā veidā.
Nākotnē, veicot izmaiņas mapē (pievienojot vai noņemot pilsētas) vai failos (mainot rindu skaitu), pietiks ar peles labo pogu noklikšķināt tieši uz tabulas vai vaicājuma labajā panelī un atlasīt komandu Atjaunināt un saglabāt (Atjaunot) - Power Query pēc dažām sekundēm atkal "atjaunos" visus datus.
PS
Grozījumi. Pēc 2017. gada janvāra atjauninājumiem Power Query iemācījās pats savākt Excel darbgrāmatas, proti, vairs nav jāveido atsevišķa funkcija – tas notiek automātiski. Tādējādi otrais solis no šī raksta vairs nav vajadzīgs, un viss process kļūst ievērojami vienkāršāks:
- izvēlēties Izveidot pieprasījumu - no faila - no mapes - atlasiet mapi - Labi
- Kad tiek parādīts failu saraksts, nospiediet Mainīt
- Vaicājumu redaktora logā paplašiniet kolonnu Binārā ar dubulto bultiņu un atlasiet lapas nosaukumu, kas jāņem no katra faila
Un tas arī viss! Dziesma!
- Šķērssijas pārveidošana par plakanu, kas ir piemērota šarnīra galdu veidošanai
- Animētas burbuļu diagrammas izveide programmā Power View
- Makro, lai apkopotu lapas no dažādiem Excel failiem vienā