Përmbajtje
Një nga videot më të shikuara në kanalin tim në YouTube është një video rreth Flash Fill në Microsoft Excel. Thelbi i këtij mjeti është se nëse keni nevojë të transformoni disi të dhënat tuaja burimore, atëherë thjesht duhet të filloni të shkruani rezultatin që dëshironi të merrni në kolonën ngjitur. Pas disa qelizave të shtypura manualisht (zakonisht 2-3 janë të mjaftueshme), Excel do të "kuptojë" logjikën e transformimeve që ju nevojiten dhe do të vazhdojë automatikisht atë që keni shtypur, duke përfunduar të gjithë punën monotone për ju:
Kuintesenca e efikasitetit. Butoni magjik "bëje siç duhet" që të gjithë e duam kaq shumë, apo jo?
Në fakt, ekziston një analog i një mjeti të tillë në Power Query - atje quhet Kolona nga shembujt (Kollona nga Shembujt). Në fakt, kjo është një inteligjencë e vogël artificiale e ndërtuar në Power Query që mund të mësojë shpejt nga të dhënat tuaja dhe më pas t'i transformojë ato. Le t'i hedhim një vështrim më të afërt aftësive të tij në disa skenarë praktikë për të kuptuar se ku mund të jetë i dobishëm për ne në detyra reale.
Shembull 1. Ngjitja/prerja e tekstit
Le të themi se kemi një tabelë kaq "të zgjuar" në Excel me të dhëna për punonjësit:
Ngarkoni atë në Power Query në mënyrën standarde - me butonin Nga tabela/gama tab Data (Të dhënat - nga tabela/vargu).
Supozoni se duhet të shtojmë një kolonë me mbiemrat dhe inicialet për secilin punonjës (Ivanov SV për punonjësin e parë, etj.). Për të zgjidhur këtë problem, mund të përdorni një nga dy metodat:
- klikoni me të djathtën në titullin e kolonës me të dhënat burimore dhe zgjidhni komandën Shtoni një kolonë nga shembujt (Shto kolonë nga shembujt);
- zgjidhni një ose më shumë kolona me të dhëna dhe në skedën Shtimi i një kolone zgjidhni një ekip Kolona nga shembujt. Këtu, në listën rënëse, mund të specifikoni nëse të gjitha ose vetëm kolonat e zgjedhura duhet të analizohen.
Atëherë gjithçka është e thjeshtë - në kolonën që shfaqet në të djathtë, ne fillojmë të fusim shembuj të rezultateve të dëshiruara, dhe inteligjenca artificiale e ndërtuar në Power Query përpiqet të kuptojë logjikën tonë të transformimit dhe të vazhdojë më tej vetë:
Meqë ra fjala, mund të futni opsionet e sakta në çdo qelizë të kësaj kolone, pra jo domosdoshmërisht nga lart-poshtë dhe në një rresht. Gjithashtu, mund të shtoni ose hiqni me lehtësi kolona nga analiza më vonë duke përdorur kutitë e kontrollit në shiritin e titullit.
Kushtojini vëmendje formulës në krye të dritares - kjo është ajo që krijon Smart Query për të marrë rezultatet që na duhen. Ky, nga rruga, është ndryshimi thelbësor midis këtij mjeti dhe Mbushje e menjëhershme në Excel. Mbushja e menjëhershme funksionon si një "kuti e zezë" - ato nuk na tregojnë logjikën e transformimeve, por thjesht japin rezultate të gatshme dhe ne i marrim si të mirëqenë. Këtu gjithçka është transparente dhe gjithmonë mund të kuptoni absolutisht qartë se çfarë saktësisht po ndodh me të dhënat.
Nëse shihni se Power Query "kapi idenë", atëherë mund të shtypni me siguri butonin OK ose shkurtore të tastierës Ctrl+Shkruani – do të krijohet një kolonë e personalizuar me një formulë të shpikur nga Power Query. Nga rruga, më vonë mund të modifikohet lehtësisht si një kolonë e rregullt e krijuar manualisht (me komandën Shtimi i një kolone - Kolona e personalizuar) duke klikuar ikonën e ingranazhit në të djathtë të emrit të hapit:
Shembulli 2: Rasti si në fjali
Nëse klikoni me të djathtën në titullin e kolonës me tekst dhe zgjidhni komandën Transformim (Transformo), atëherë mund të shihni tre komanda përgjegjëse për ndryshimin e regjistrit:
I përshtatshëm dhe i lezetshëm, por në këtë listë, për shembull, mua personalisht më ka munguar gjithmonë një opsion më shumë - rast si në fjali, kur shkronja e madhe (kapitali) bëhet jo shkronja e parë në çdo fjalë, por vetëm shkronja e parë në qelizë, dhe pjesa tjetër e tekstit kur kjo shfaqet me shkronja të vogla (të vogla).
Kjo veçori që mungon është e lehtë për t'u zbatuar me inteligjencën artificiale Kolonat nga shembujt – thjesht futni disa opsione që Power Query të vazhdojë në të njëjtën frymë:
Si formulë këtu, Power Query përdor një sërë funksionesh Teksti.E sipërme и Teksti.Poshtë, duke konvertuar tekstin në shkronja të mëdha dhe të vogla, përkatësisht, dhe funksionet Teksti.Fillimi и Teksti.Mes – analoge të funksioneve Excel LEFT dhe PSTR, në gjendje të nxjerrin një nënvarg nga teksti nga e majta dhe nga mesi.
Shembulli 3. Ndërrimi i fjalëve
Ndonjëherë, kur përpunohen të dhënat e marra, bëhet e nevojshme të riorganizoni fjalët në qeliza në një sekuencë të caktuar. Sigurisht, mund ta ndani kolonën në kolona të veçanta fjalësh nga ndarësi dhe më pas ta ngjisni përsëri në rendin e specifikuar (mos harroni të shtoni hapësira), por me ndihmën e mjetit Kolona nga shembujt gjithçka do të jetë shumë më e lehtë:
Shembulli 4: Vetëm numrat
Një detyrë tjetër shumë jetike është nxjerrja e vetëm numrave (numrave) nga përmbajtja e qelizës. Si më parë, pasi të keni ngarkuar të dhënat në Power Query, shkoni te skeda Shtimi i një kolone - Kolona nga shembujt dhe plotësoni disa qeliza me dorë në mënyrë që programi të kuptojë se çfarë saktësisht duam të marrim:
Bingo!
Përsëri, ia vlen të shikoni në krye të dritares për t'u siguruar që Query ka krijuar formulën në mënyrë korrekte - në këtë rast ajo përmban një funksion Teksti. Zgjidhni, i cili, siç mund ta merrni me mend, i nxjerr karakteret e dhëna nga teksti burimor sipas listës. Më pas, kjo listë, natyrisht, mund të modifikohet lehtësisht në shiritin e formulës nëse është e nevojshme.
Shembulli 5: Vetëm tekst
Ngjashëm me shembullin e mëparshëm, mund të tërhiqni dhe anasjelltas - vetëm tekstin, duke fshirë të gjithë numrat, shenjat e pikësimit, etj.
Në këtë rast, përdoret një funksion që tashmë është i kundërt në kuptim - Text.Remove, i cili heq karakteret nga vargu origjinal sipas një liste të caktuar.
Shembulli 6: Nxjerrja e të dhënave nga një qull alfanumerik
Power Query mund të ndihmojë gjithashtu në raste më të vështira, kur duhet të nxirrni informacione të dobishme nga qulli alfanumerik në një qelizë, për shembull, merrni numrin e llogarisë nga përshkrimi i qëllimit të pagesës në një deklaratë bankare:
Vini re se formula e konvertimit e krijuar nga Power Query mund të jetë mjaft komplekse:
Për lehtësinë e leximit dhe të kuptuarit, ai mund të shndërrohet në një formë shumë më të arsyeshme duke përdorur një shërbim falas në internet. Formatuesi i pyetjes së energjisë:
Gjë shumë e dobishme - respekt për krijuesit!
Shembulli 7: Konvertimi i datave
Mjet Kolona nga shembujt mund të aplikohet edhe në kolonat e datës ose datës. Kur futni shifrat e para të një date, Power Query do të shfaqë një listë të të gjitha opsioneve të mundshme të konvertimit:
Kështu që ju mund ta konvertoni me lehtësi datën origjinale në çdo format ekzotik, si p.sh. "vit-muaj-ditë":
Shembulli 8: Kategorizimi
Nëse përdorim mjetin Kolona nga shembujt në një kolonë me të dhëna numerike, funksionon ndryshe. Supozoni se kemi rezultatet e testit të punonjësve të ngarkuara në Power Query (pikat e kushtëzuara në rangun 0-100) dhe përdorim gradimin e kushtëzuar vijues:
- Masters - ata që shënuan më shumë se 90
- Ekspertët - shënuan nga 70 në 90
- Përdorues - nga 30 në 70
- Fillestarët - ata që shënuan më pak se 30
Nëse shtojmë një kolonë nga shembujt në listë dhe fillojmë t'i rregullojmë këto shkallëzime me dorë, atëherë shumë shpejt Power Query do të marrë idenë tonë dhe do të shtojë një kolonë me një formulë, ku operatorët ndërlidhen me njëri-tjetrin. if logjika do të zbatohet, shumë e ngjashme me atë që na nevojitet:
Përsëri, nuk mund ta shtypni situatën deri në fund, por klikoni mbi OK dhe më pas korrigjoni vlerat e pragut tashmë në formulë - është më shpejt në këtë mënyrë:
Konkluzione
Sigurisht një mjet Kolona nga shembujt nuk është një "pilulë magjike" dhe, herët a vonë, do të ketë situata jo standarde ose raste veçanërisht të neglizhuara të një "fermë kolektive" në të dhëna, kur Power Query do të dështojë dhe nuk do të jetë në gjendje të punojë atë që duam. saktë për ne. Megjithatë, si një mjet ndihmës, është shumë i mirë. Plus, duke studiuar formulat që ai gjeneroi, ju mund të zgjeroni njohuritë tuaja për funksionet e gjuhës M, e cila do të jetë gjithmonë e dobishme në të ardhmen.
- Analizimi i tekstit me shprehje të rregullta (RegExp) në Power Query
- Kërkimi i tekstit fuzzy në Power Query
- Flash Plotësoni Microsoft Excel