Panig-ingnan nga Column – Artipisyal nga Kaalam sa Power Query

Usa sa labing gitan-aw nga mga video sa akong channel sa YouTube mao ang usa ka video bahin sa Flash Fill sa Microsoft Excel. Ang esensya sa kini nga himan mao nga kung kinahanglan nimo nga usbon ang imong gigikanan nga datos, nan kinahanglan nimo nga magsugod sa pag-type sa resulta nga gusto nimo makuha sa kasikbit nga kolum. Pagkahuman sa daghang mga manu-mano nga gi-type nga mga selyula (kasagaran 2-3 igo na), ang Excel "makasabut" sa lohika sa mga pagbag-o nga kinahanglan nimo ug awtomatiko nga ipadayon ang imong gi-type, pagkompleto sa tanan nga monotonous nga trabaho alang kanimo:

Ang quintessence sa efficiency. Ang salamangka nga "buhata kini sa husto" nga buton nga gihigugma kaayo natong tanan, di ba?

Sa tinuud, adunay usa ka analogue sa ingon nga himan sa Power Query - didto kini gitawag Kolum gikan sa mga pananglitan (Kolum gikan sa mga Ehemplo). Sa tinuud, kini usa ka gamay nga artificial intelligence nga gitukod sa Power Query nga dali nga makakat-on gikan sa imong data ug dayon mabag-o kini. Atong tan-awon pag-ayo ang mga kapabilidad niini sa daghang praktikal nga mga senaryo aron masabtan kung asa kini mahimong mapuslanon kanato sa tinuod nga mga buluhaton.

Pananglitan 1. Pagpapilit/pagputol sa teksto

Ingnon ta nga kami adunay usa ka "smart" nga lamesa sa Excel nga adunay datos sa mga empleyado:

I-load kini sa Power Query sa standard nga paagi - gamit ang buton Gikan sa Table/Range tab Data (Data — Gikan sa Table/Range).

Ibutang ta nga kinahanglan natong idugang ang usa ka kolum nga adunay mga apelyido ug inisyal alang sa matag empleyado (Ivanov SV alang sa unang empleyado, ug uban pa). Aron masulbad kini nga problema, mahimo nimong gamiton ang usa sa duha nga mga pamaagi:

• pag-klik sa tuo sa ulohan sa kolum nga adunay gigikanan nga datos ug pilia ang mando Idugang ang kolum gikan sa mga pananglitan (Idugang ang kolum gikan sa mga pananglitan);

• pagpili og usa o daghang mga kolum nga adunay data ug sa tab Pagdugang ug kolum pagpili og team Kolum gikan sa mga pananglitan. Dinhi, sa drop-down nga lista, mahimo nimong ipiho kung ang tanan o gipili ra nga mga kolum kinahanglan analisahon.

Unya ang tanan yano ra - sa kolum nga makita sa tuo, nagsugod kami sa pagsulod sa mga pananglitan sa gitinguha nga mga sangputanan, ug ang artipisyal nga paniktik nga gitukod sa Power Query naningkamot nga masabtan ang among lohika sa pagbag-o ug magpadayon sa iyang kaugalingon:

Pinaagi sa dalan, mahimo nimong isulod ang husto nga mga kapilian sa bisan unsang mga selyula niini nga kolum, ie dili kinahanglan nga top-down ug sa usa ka laray. Usab, dali ka makadugang o makatangtang sa mga kolum gikan sa pagtuki sa ulahi gamit ang mga checkbox sa title bar.

Hatagi'g pagtagad ang pormula sa ibabaw sa bintana - kini ang gimugna sa smart Power Query aron makuha ang mga resulta nga atong gikinahanglan. Kini, sa paagi, mao ang sukaranan nga kalainan tali sa kini nga himan ug Pagpuno dayon sa Excel. Ang dali nga pagpuno molihok sama sa usa ka "itom nga kahon" - wala nila gipakita kanamo ang lohika sa mga pagbag-o, apan naghatag ra nga andam nga mga sangputanan ug gidawat namon kini nga wala’y bayad. Dinhi ang tanan transparent ug kanunay nimo nga klaro nga masabtan kung unsa gyud ang nahitabo sa datos.

Kung nakita nimo nga ang Power Query "nakuha ang ideya", nan mahimo nimong luwas nga ipadayon ang buton OK o keyboard shortcut Ctrl+Pagsulod – usa ka naandan nga kolum nga adunay pormula nga giimbento sa Power Query ang pagabuhaton. Pinaagi sa dalan, kini dali nga ma-edit sa ulahi ingon usa ka regular nga mano-mano nga gihimo nga kolum (uban ang command Pagdugang ug Column – Custom Column) pinaagi sa pag-klik sa gear icon sa tuo sa ngalan sa lakang:

Pananglitan 2: Kaso sama sa mga sentence

Kung nag-right-click ka sa ulohan sa kolum nga adunay teksto ug pilia ang mando kausaban (Pagbag-o), unya imong makita ang tulo ka mga sugo nga responsable sa pagbag-o sa rehistro:

Sayon ug bugnaw, apan sa kini nga lista, pananglitan, ako sa personal kanunay nga kulang sa usa pa nga kapilian - kaso sama sa mga tudling-pulong, kung ang pag-capital (kapital) dili ang una nga letra sa matag pulong, apan ang una nga letra sa cell, ug ang nahabilin nga teksto kung kini gipakita sa gamay (gagmay) nga mga letra.

Kini nga nawala nga bahin dali nga ipatuman gamit ang artificial intelligence Mga kolum gikan sa mga pananglitan – pagsulod lang sa duha ka kapilian para sa Power Query nga magpadayon sa samang espiritu:

Ingon usa ka pormula dinhi, ang Power Query naggamit usa ka hugpong sa mga gimbuhaton Text.Upper и Text.Ubos, pag-convert sa teksto ngadto sa upper ug lower case, matag usa, ug mga function Text.Sugod и Text.Mid - mga analogue sa Excel nga naglihok sa LEFT ug PSTR, makahimo sa pagkuha sa usa ka substring gikan sa teksto gikan sa wala ug gikan sa tunga.

Pananglitan 3. Permutasyon sa mga pulong

Usahay, kung giproseso ang nadawat nga datos, kinahanglan nga usbon ang paghan-ay sa mga pulong sa mga selyula sa gihatag nga pagkasunod-sunod. Siyempre, mahimo nimong bahinon ang kolum ngadto sa lain nga mga kolum sa pulong pinaagi sa separator ug dayon ipapilit kini balik sa gitakda nga han-ay (ayaw kalimti ang pagdugang og mga luna), apan sa tabang sa himan Kolum gikan sa mga pananglitan ang tanan mahimong mas sayon:

Pananglitan 4: Mga numero lamang

Laing hinungdanon kaayo nga buluhaton mao ang pagkuha sa mga numero lamang (mga numero) gikan sa sulud sa selyula. Sama kaniadto, pagkahuman sa pagkarga sa datos sa Power Query, adto sa tab Pagdugang usa ka kolum - Kolum gikan sa mga pananglitan ug pun-a ang usa ka pares nga mga selyula nga mano-mano aron masabtan sa programa kung unsa gyud ang gusto namon makuha:

Bingo!

Pag-usab, angay nga tan-awon ang ibabaw sa bintana aron masiguro nga ang Query nakamugna sa pormula sa husto - sa kini nga kaso kini adunay usa ka function Text. Pagpili, nga, ingon sa imong pagtag-an, nagkuha sa gihatag nga mga karakter gikan sa gigikanan nga teksto sumala sa lista. Pagkahuman, kini nga lista, siyempre, dali nga ma-edit sa formula bar kung kinahanglan.

Pananglitan 5: Text lang

Susama sa miaging pananglitan, mahimo nimong ibira ug vice versa - ang teksto lamang, pagtangtang sa tanan nga mga numero, mga marka sa punctuation, ug uban pa.

Sa kini nga kaso, gigamit ang usa ka function nga kaatbang sa kahulogan - Text.Remove, nga nagtangtang sa mga karakter gikan sa orihinal nga hilo sumala sa gihatag nga lista.

Pananglitan 6: Pagkuha sa datos gikan sa alphanumeric nga lugaw

Makatabang usab ang Power Query sa mas lisud nga mga kaso, kung kinahanglan nimo nga makuha ang mapuslanon nga kasayuran gikan sa alphanumeric nga lugaw sa usa ka cell, pananglitan, pagkuha sa numero sa account gikan sa paghulagway sa katuyoan sa pagbayad sa usa ka pahayag sa bangko:

Timan-i nga ang pormula sa pagkakabig nga gihimo sa Power Query mahimong komplikado kaayo:

Alang sa kasayon ​​sa pagbasa ug pagsabot, mahimo kini nga mabag-o sa usa ka labi ka maayo nga porma gamit ang usa ka libre nga serbisyo sa online. Power Query Formatter:

Magamit kaayo nga butang - pagtahod sa mga tiglalang!

Pananglitan 7: Pag-convert sa mga petsa

Galamiton Kolum gikan sa mga pananglitan mahimong magamit sa petsa o datetime nga mga kolum usab. Kung mosulod ka sa unang mga numero sa usa ka petsa, ang Power Query makatabang sa pagpakita sa usa ka lista sa tanan nga posible nga mga kapilian sa pagkakabig:

Aron dali nimo mabag-o ang orihinal nga petsa sa bisan unsang exotic nga format, sama sa "adlaw-buwan nga adlaw":

Pananglitan 8: Pagkategorya

Kon gamiton nato ang himan Kolum gikan sa mga pananglitan sa usa ka kolum nga adunay numeric data, lahi ang pagtrabaho niini. Ibutang ta nga adunay mga resulta sa pagsulay sa empleyado nga gikarga sa Power Query (kondisyonal nga mga marka sa han-ay nga 0-100) ug among gigamit ang mosunod nga kondisyonal nga gradasyon:

• Mga agalon – kadtong nakapuntos og labaw sa 90
• Mga eksperto - nakapuntos gikan sa 70 hangtod 90
• Mga tiggamit - gikan sa 30 hangtod 70
• Mga nagsugod – kadtong nakapuntos og ubos sa 30

Kung magdugang kami usa ka kolum gikan sa mga pananglitan sa lista ug magsugod sa paghan-ay sa kini nga mga gradasyon nga mano-mano, unya sa dili madugay kuhaon sa Power Query ang among ideya ug magdugang usa ka kolum nga adunay pormula, diin ang mga operator nagsalag sa usag usa if ipatuman ang lohika, susama kaayo sa atong gikinahanglan:

Pag-usab, dili nimo mapadayon ang kahimtang hangtod sa katapusan, apan pag-klik OK ug dayon itul-id ang mga kantidad sa threshold nga naa na sa pormula - mas paspas kini nga paagi:

konklusyon

Sigurado nga usa ka himan Kolum gikan sa mga pananglitan dili usa ka "magic pill" ug, sa madugay o sa madali, adunay mga dili standard nga mga sitwasyon o labi na nga napasagdan nga mga kaso sa usa ka "collective farm" sa datos, kung ang Power Query mapakyas ug dili makahimo sa kung unsa ang gusto namon. sakto para nato. Apan, isip usa ka auxiliary nga himan, kini maayo kaayo. Dugang pa, pinaagi sa pagtuon sa mga pormula nga iyang namugna, mahimo nimong mapalapad ang imong kahibalo sa mga gimbuhaton sa M nga pinulongan, nga kanunay nga magamit sa umaabot.

• Pag-parse sa Teksto nga adunay Regular nga Ekspresyon (RegExp) sa Power Query
• Fuzzy nga pagpangita sa teksto sa Power Query
• Pagpuno sa Flash sa Microsoft Excel