Saturs
Viens no skatītākajiem videoklipiem manā YouTube kanālā ir video par Flash Fill programmā Microsoft Excel. Šī rīka būtība ir tāda, ka, ja jums ir kaut kā jāpārveido avota dati, jums vienkārši jāsāk blakus kolonnā rakstīt rezultātu, kuru vēlaties iegūt. Pēc vairākām manuāli ievadītām šūnām (parasti pietiek ar 2-3) Excel “sapratīs” nepieciešamo transformāciju loģiku un automātiski turpinās ievadīto, pabeidzot visu monotono darbu jūsu vietā:
Efektivitātes kvintesence. Burvju poga “dari pareizi”, kas mums visiem tik ļoti patīk, vai ne?
Faktiski Power Query ir šāda rīka analogs - tur to sauc Kolonna no piemēriem (Sleja no piemēriem). Faktiski šis ir neliels mākslīgais intelekts, kas iebūvēts Power Query, kas var ātri mācīties no jūsu datiem un pēc tam tos pārveidot. Sīkāk apskatīsim tā iespējas vairākos praktiskos scenārijos, lai saprastu, kur tas var mums noderēt reālos uzdevumos.
Piemērs 1. Teksta līmēšana/griešana
Pieņemsim, ka mums programmā Excel ir tik “gudra” tabula ar datiem par darbiniekiem:
Ielādējiet to programmā Power Query standarta veidā – ar pogu No tabulas/diapazona tab Datums (Dati — no tabulas/diapazona).
Pieņemsim, ka mums jāpievieno kolonna ar uzvārdiem un iniciāļiem katram darbiniekam (Ivanov SV pirmajam darbiniekam utt.). Lai atrisinātu šo problēmu, varat izmantot vienu no divām metodēm:
- ar peles labo pogu noklikšķiniet uz kolonnas virsraksta ar avota datiem un atlasiet komandu Pievienojiet kolonnu no piemēriem (Pievienot kolonnu no piemēriem);
- atlasiet vienu vai vairākas kolonnas ar datiem un cilnē Kolonnas pievienošana izvēlēties komandu Kolonna no piemēriem. Šeit nolaižamajā sarakstā varat norādīt, vai jāanalizē visas vai tikai atlasītās kolonnas.
Tālāk viss ir vienkārši – labajā pusē redzamajā ailē mēs sākam ievadīt vēlamo rezultātu piemērus, un Power Query iebūvētais mākslīgais intelekts mēģina izprast mūsu transformācijas loģiku un turpināt patstāvīgi:
Starp citu, pareizās opcijas var ievadīt jebkurā šīs kolonnas šūnā, ti, ne vienmēr no augšas uz leju un pēc kārtas. Varat arī viegli pievienot vai noņemt analīzes kolonnas vēlāk, izmantojot virsrakstjoslas izvēles rūtiņas.
Pievērsiet uzmanību formulai loga augšdaļā — to viedā Power Query izveido, lai iegūtu vajadzīgos rezultātus. Tā, starp citu, ir būtiskā atšķirība starp šo rīku un Tūlītēja aizpildīšana programmā Excel. Tūlītēja pildīšana darbojas kā “melnā kaste” – tās mums neparāda pārvērtību loģiku, bet vienkārši dod gatavus rezultātus, un mēs tos uztveram kā pašsaprotamus. Šeit viss ir caurspīdīgs, un jūs vienmēr varat pilnīgi skaidri saprast, kas tieši notiek ar datiem.
Ja redzat, ka Power Query “uztvēra ideju”, varat droši nospiest pogu OK vai īsinājumtaustiņš Ctrl+ienākt – tiks izveidota pielāgota kolonna ar Power Query izgudrotu formulu. Starp citu, to vēlāk var viegli rediģēt kā parastu manuāli izveidotu kolonnu (ar komandu Kolonnas pievienošana — pielāgota kolonna), noklikšķinot uz zobrata ikonas pa labi no soļa nosaukuma:
2. piemērs: gadījums kā teikumos
Ja ar peles labo pogu noklikšķiniet uz kolonnas virsraksta ar tekstu un atlasiet komandu transformācija (Pārveidot), tad jūs varat redzēt trīs komandas, kas ir atbildīgas par reģistra maiņu:
Ērti un forši, bet šajā sarakstā, piemēram, man personīgi vienmēr ir pietrūcis vēl vienas iespējas – reģistrs kā teikumos, kad lielo burtu lietojums (lielais burts) kļūst nevis par pirmo burtu katrā vārdā, bet tikai par pirmo burtu šūnā, un pārējais teksts, kad Tas tiek rādīts ar mazajiem (mazajiem) burtiem.
Šo trūkstošo funkciju ir viegli ieviest ar mākslīgo intelektu Kolonnas no piemēriem — vienkārši ievadiet dažas opcijas, lai Power Query turpinātu tādā pašā garā:
Kā formula šeit Power Query izmanto virkni funkciju Teksts.Augšējais и Teksts.Apakšējā, pārvēršot tekstu attiecīgi lielos un mazajos burtos un funkcijas Teksts.Sākt и Teksts.Vid – Excel funkciju LEFT un PSTR analogi, kas spēj izvilkt apakšvirkni no teksta no kreisās puses un no vidus.
Piemērs 3. Vārdu permutācija
Dažreiz, apstrādājot saņemtos datus, rodas nepieciešamība pārkārtot vārdus šūnās noteiktā secībā. Protams, kolonnu var sadalīt atsevišķās vārdu kolonnās ar atdalītāju un pēc tam pielīmēt atpakaļ norādītajā secībā (neaizmirstiet pievienot atstarpes), taču ar rīka palīdzību Kolonna no piemēriem viss būs daudz vienkāršāk:
4. piemērs: tikai cipari
Vēl viens ļoti svarīgs uzdevums ir no šūnas satura izvilkt tikai skaitļus (skaitļus). Tāpat kā iepriekš, pēc datu ielādes programmā Power Query pārejiet uz cilni Kolonnas pievienošana — kolonna no piemēriem un manuāli aizpildiet pāris šūnas, lai programma saprastu, ko tieši mēs vēlamies iegūt:
Bingo!
Atkal ir vērts apskatīt loga augšdaļu, lai pārliecinātos, ka vaicājums formulu ģenerēja pareizi — šajā gadījumā tajā ir funkcija Teksts. Izvēlieties, kas, kā jau varētu nojaust, izvelk dotās rakstzīmes no avota teksta atbilstoši sarakstam. Pēc tam šo sarakstu, protams, var viegli rediģēt formulas joslā, ja nepieciešams.
5. piemērs: tikai teksts
Līdzīgi kā iepriekšējā piemērā, var izvilkt un otrādi – tikai tekstu, dzēšot visus ciparus, pieturzīmes utt.
Šajā gadījumā tiek izmantota jau pretēja nozīmes funkcija – Text.Remove, kas noņem rakstzīmes no sākotnējās virknes saskaņā ar doto sarakstu.
6. piemērs: datu iegūšana no burtciparu putras
Power Query var palīdzēt arī sarežģītākos gadījumos, kad nepieciešams no šūnā esošās burtciparu putras izvilkt noderīgu informāciju, piemēram, iegūt konta numuru no maksājuma mērķa apraksta bankas izrakstā:
Ņemiet vērā, ka Power Query ģenerētā konversijas formula var būt diezgan sarežģīta:
Lai atvieglotu lasīšanu un izpratni, to var pārvērst daudz saprātīgākā formā, izmantojot bezmaksas tiešsaistes pakalpojumu. Power Query formatētājs:
Ļoti parocīga lieta – cieņu veidotājiem!
7. piemērs: datumu konvertēšana
Instruments Kolonna no piemēriem var lietot arī datuma vai datuma un laika kolonnām. Ievadot datuma pirmos ciparus, Power Query lietderīgi parādīs visu iespējamo reklāmguvumu opciju sarakstu.
Tātad jūs varat viegli konvertēt sākotnējo datumu uz jebkuru eksotisku formātu, piemēram, "gads-mēnesis-diena":
8. piemērs. Kategorizācija
Ja mēs izmantojam rīku Kolonna no piemēriem kolonnai ar skaitliskiem datiem, tā darbojas citādi. Pieņemsim, ka Power Query ir ielādēti darbinieka pārbaudes rezultāti (nosacītie rādītāji diapazonā no 0 līdz 100) un mēs izmantojam šādu nosacījumu gradāciju:
- Meistari – tie, kuri ieguvuši vairāk par 90
- Eksperti – no 70 līdz 90
- Lietotāji – no 30 līdz 70
- Iesācēji – tie, kuri ieguvuši mazāk par 30
Ja sarakstam pievienosim kolonnu no piemēriem un sāksim kārtot šīs gradācijas manuāli, tad pavisam drīz Power Query paņems mūsu ideju un pievienos kolonnu ar formulu, kur operatori salikti viens otrā. if tiks ieviesta loģika, ļoti līdzīga tai, kas mums nepieciešama:
Atkal, jūs nevarat nospiest situāciju līdz galam, bet noklikšķiniet uz OK un pēc tam labojiet sliekšņa vērtības jau formulā - tas ir ātrāk šādi:
secinājumi
Noteikti instruments Kolonna no piemēriem nav "burvju tablete" un agri vai vēlu datos parādīsies nestandarta situācijas vai īpaši novārtā atstāti "kolhoza" gadījumi, kad Power Query neizdosies un nevarēs izstrādāt to, ko gribam. pareizi mums. Tomēr kā palīgrīks tas ir ļoti labs. Turklāt, izpētot viņa ģenerētās formulas, jūs varat paplašināt savas zināšanas par M valodas funkcijām, kas vienmēr noderēs nākotnē.
- Teksta parsēšana ar regulārām izteiksmēm (RegExp) programmā Power Query
- Izplūdusi teksta meklēšana programmā Power Query
- Flash Aizpildiet programmā Microsoft Excel