Efnisyfirlit
Eitt mest skoðaða myndbandið á YouTube rásinni minni er myndband um Flash Fill í Microsoft Excel. Kjarninn í þessu tóli er sá að ef þú þarft einhvern veginn að umbreyta upprunagögnunum þínum, þá þarftu bara að byrja að slá niðurstöðuna sem þú vilt fá í aðliggjandi dálk. Eftir nokkrar handvirkt innsláttar frumur (venjulega 2-3 eru nóg), mun Excel "skilja" rökfræði umbreytinganna sem þú þarft og halda sjálfkrafa áfram því sem þú hefur slegið inn og klára alla einhæfu vinnuna fyrir þig:
Kjarninn í skilvirkni. Töfra "gera það rétt" hnappinn sem við elskum öll svo mikið, ekki satt?
Reyndar er hliðstæða slíks tóls í Power Query - þar er það kallað Dálkur úr dæmum (Dálkur úr dæmum). Reyndar er þetta lítil gervigreind sem er innbyggð í Power Query sem getur fljótt lært af gögnunum þínum og síðan umbreytt þeim. Við skulum líta nánar á getu þess í nokkrum hagnýtum atburðarásum til að skilja hvar það getur verið gagnlegt fyrir okkur í raunverulegum verkefnum.
Dæmi 1. Líma/klippa texta
Segjum að við séum með svona „snjöll“ töflu í Excel með gögnum um starfsmenn:
Hladdu því inn í Power Query á hefðbundinn hátt - með hnappinum Frá borði/sviði flipi Gögn (Gögn — úr töflu/sviði).
Segjum að við þurfum að bæta við dálki með eftirnöfnum og upphafsstöfum fyrir hvern starfsmann (Ivanov SV fyrir fyrsta starfsmann, o.s.frv.). Til að leysa þetta vandamál geturðu notað eina af tveimur aðferðum:
- hægrismelltu á dálkfyrirsögnina með upprunagögnunum og veldu skipunina Bættu við dálki úr dæmum (Bæta við dálki úr dæmum);
- veldu einn eða fleiri dálka með gögnum og á flipann Að bæta við dálki velja lið Dálkur úr dæmum. Hér í fellilistanum er hægt að tilgreina hvort greina þurfi alla eða aðeins valda dálka.
Þá er allt einfalt - í dálknum sem birtist til hægri byrjum við að slá inn dæmi um tilætluðum árangri og gervigreindin sem er innbyggð í Power Query reynir að skilja umbreytingarrökfræði okkar og halda áfram á eigin spýtur:
Við the vegur, þú getur slegið inn rétta valkostina í hvaða reiti sem er í þessum dálki, þ.e. ekki endilega ofan frá og í röð. Einnig geturðu auðveldlega bætt við eða fjarlægt dálka úr greiningunni síðar með því að nota gátreitina á titilstikunni.
Gefðu gaum að formúlunni efst í glugganum - þetta er það sem snjall Power Query býr til til að fá þær niðurstöður sem við þurfum. Þetta, við the vegur, er grundvallarmunurinn á þessu tóli og Augnablik fylling í Excel. Augnabliksfylling virkar eins og „svartur kassi“ – þær sýna okkur ekki rökfræði umbreytinganna, heldur gefa einfaldlega tilbúnar niðurstöður og við tökum þær sem sjálfsögðum hlut. Hér er allt gagnsætt og þú getur alltaf skilið nákvæmlega hvað nákvæmlega er að gerast með gögnin.
Ef þú sérð að Power Query „fangaði hugmyndina“ þá geturðu örugglega ýtt á hnappinn OK eða flýtilykla Ctrl+Sláðu inn – sérsniðinn dálkur með formúlu sem Power Query hefur fundið upp verður búinn til. Við the vegur, það er hægt seinna auðveldlega breytt sem venjulegur handvirkt búinn dálki (með skipuninni Að bæta við dálki - Sérsniðinn dálkur) með því að smella á tannhjólstáknið hægra megin við nafn skrefsins:
Dæmi 2: Fall eins og í setningum
Ef þú hægrismellir á dálkafyrirsögnina með texta og velur skipunina Umbreyting (Breyta), þá geturðu séð þrjár skipanir sem bera ábyrgð á því að breyta skránni:
Þægilegt og flott, en í þessum lista, til dæmis, hefur mig persónulega alltaf vantað einn valmöguleika í viðbót – hástafi eins og í setningum, þegar hástafur (hástafur) verður ekki fyrsti stafurinn í hverju orði, heldur fyrsti stafurinn í hólfinu, og restin af textanum þegar Þetta er birt með litlum (smá) stöfum.
Þessi eiginleiki sem vantar er auðvelt að útfæra með gervigreind Dálkar úr dæmum - sláðu bara inn nokkra möguleika til að Power Query haldi áfram í sama anda:
Sem formúla hér notar Power Query fullt af aðgerðum Texti.Efri и Texti.Lærri, umbreyta texta í hástafi og lágstafi, í sömu röð, og aðgerðir Texti.Start и Texti.Mið – hliðstæður Excel aðgerðanna VINSTRI og PSTR, geta dregið út undirstreng úr textanum frá vinstri og frá miðjunni.
Dæmi 3. Umbreyting orða
Stundum, þegar unnið er úr mótteknum gögnum, verður nauðsynlegt að endurraða orðum í frumunum í ákveðinni röð. Auðvitað er hægt að skipta dálknum í aðskilda orðdálka með skiljunni og líma hann svo aftur í tilgreindri röð (ekki gleyma að bæta við bilum), en með hjálp tólsins Dálkur úr dæmum allt verður miklu auðveldara:
Dæmi 4: Aðeins tölur
Annað mjög mikilvægt verkefni er að draga aðeins út tölur (tölur) úr innihaldi frumunnar. Eins og áður, eftir að hafa hlaðið gögnum inn í Power Query, farðu í flipann Dálki bætt við – Dálkur úr dæmum og fylltu út nokkra reiti handvirkt svo að forritið skilji hvað nákvæmlega við viljum fá:
Bingó!
Aftur, það er þess virði að skoða efst í glugganum til að ganga úr skugga um að Query hafi búið til formúluna rétt - í þessu tilviki inniheldur hún fall Texti. Veldu, sem, eins og þú gætir giska á, dregur tiltekna stafi úr frumtextanum samkvæmt listanum. Í kjölfarið er auðvitað auðvelt að breyta þessum lista í formúlustikunni ef þörf krefur.
Dæmi 5: Aðeins texti
Líkt og í fyrra dæmi, geturðu dregið út og öfugt - aðeins textann, eytt öllum tölum, greinarmerkjum osfrv.
Í þessu tilviki er aðgerð sem þegar er andstæð í merkingu notað - Text.Remove, sem fjarlægir stafi úr upprunalega strengnum samkvæmt tilteknum lista.
Dæmi 6: Útdráttur gagna úr alfanumerískum graut
Power Query getur líka hjálpað í erfiðari tilfellum, þegar þú þarft að ná gagnlegum upplýsingum úr alfanumerísku grautnum í klefa, til dæmis, fáðu reikningsnúmerið úr lýsingu á greiðslutilgangi á bankayfirliti:
Athugaðu að Power Query myndað umbreytingarformúla getur verið nokkuð flókið:
Til að auðvelda lestur og skilning er hægt að breyta því í mun skynsamlegra form með því að nota ókeypis netþjónustu. Power Query Formater:
Mjög handlaginn hlutur - virðing fyrir höfundunum!
Dæmi 7: Umreikna dagsetningar
Tól Dálkur úr dæmum er einnig hægt að nota á dálka dagsetningar eða dagsetningartíma. Þegar þú slærð inn fyrstu tölustafi dagsetningar, mun Power Query birta lista yfir alla mögulega viðskiptavalkosti:
Þannig að þú getur auðveldlega umbreytt upprunalegu dagsetningunni í hvaða framandi snið sem er, eins og „ár-mánaðardagur“:
Dæmi 8: Flokkun
Ef við notum tólið Dálkur úr dæmum í dálk með tölulegum gögnum virkar það öðruvísi. Segjum sem svo að við höfum hlaðið niður niðurstöður starfsmannaprófa í Power Query (skilyrt stig á bilinu 0-100) og við notum eftirfarandi skilyrta stigbreytingu:
- Meistarar - þeir sem skoruðu meira en 90
- Sérfræðingar - skoruðu frá 70 til 90
- Notendur - frá 30 til 70
- Byrjendur – þeir sem skoruðu minna en 30
Ef við bætum dálki úr dæmunum við listann og byrjum að raða þessum stigunum handvirkt, þá mun Power Query mjög fljótlega taka upp hugmyndina okkar og bæta við dálki með formúlu, þar sem rekstraraðilar hreiður inn í annan if rökfræði verður útfærð, mjög svipuð því sem við þurfum:
Aftur, þú getur ekki þrýst á ástandið til enda, heldur smellt á OK og leiðréttu síðan þröskuldagildin þegar í formúlunni - það er hraðara á þennan hátt:
Ályktanir
Vissulega tæki Dálkur úr dæmum er ekki „töfrapilla“ og fyrr eða síðar verða óstaðlaðar aðstæður eða sérstaklega vanrækt tilvik um „sambýli“ í gögnunum, þegar Power Query mun mistakast og mun ekki geta fundið út það sem við viljum rétt hjá okkur. Hins vegar, sem hjálpartæki, er það mjög gott. Auk þess, með því að kynna þér formúlurnar sem hann bjó til, geturðu aukið þekkingu þína á virkni M tungumálsins, sem mun alltaf koma sér vel í framtíðinni.
- Að greina texta með reglulegum tjáningum (RegExp) í Power Query
- Óljós textaleit í Power Query
- Flash Fylltu í Microsoft Excel