Cuir an àite teacsa mòr ann an Power Query le gnìomh List.Accumulate

Mar as urrainn dhut foirmlean a chuir an àite an teacsa gu sgiobalta agus gu mòr a rèir an liosta iomraidh - tha sinn air a rèiteach mu thràth. A-nis feuchaidh sinn ri dhèanamh ann an Power Query.

Mar a thachras gu tric cluich tha an obair seo tòrr nas fhasa na bhith ga mhìneachadh carson bidh e ag obair, ach feuchaidh sinn ris an dà chuid a dhèanamh 🙂

Mar sin, tha dà chlàr fiùghantach “smart” againn air an cruthachadh bho raointean àbhaisteach le ath-ghoirid meur-chlàr Ctrl+T no sgioba Dachaigh - Cruth mar chlàr (Dachaigh - Cruth mar Clàr):

Dh'ainmich mi a 'chiad chlàr Dàta, an dàrna clàr - Directorya ’cleachdadh achadh Ainm clàr (Ainm clàr) tab Neach-togail (Dealbhadh).

Gnìomh: cuir an àite na seòlaidhean sa chlàr Dàta a h-uile tachartas bho cholbh Gus a lorg Leabhar-làimhe chun an co-aoisean ceart bhon cholbh Neach-ionaid. Bu chòir an còrr den teacsa anns na ceallan fuireach gun suathadh.

Ceum 1. Luchdaich an eòlaire a-steach Power Query agus tionndaidh e ann an liosta

Às deidh dhut an cealla gnìomhach a shuidheachadh gu àite sam bith sa chlàr iomraidh, cliog air an taba Dàta (Ceann-latha)no air an taba Ceist cumhachd (ma tha seann dhreach agad de Excel agus chuir thu Power Query a-steach mar thuilleadan air taba air leth) air a’ phutan Bho bhòrd/raon (Bho Clàr/Raon).

Thèid an clàr fiosrachaidh a luchdachadh a-steach don deasaiche ceist Power Query:

Gus nach cuir thu bacadh air, thèid ceum a chuir ris gu fèin-ghluasadach seòrsa atharraichte (Seòrsa air atharrachadh) anns a ’phannal cheart, faodar na ceumannan gnìomhaichte a dhubhadh às gu sàbhailte, a’ fàgail dìreach a ’cheum Stòr (Stòr):

A-nis, gus tuilleadh atharrachaidhean agus ath-chuiridhean a dhèanamh, feumaidh sinn an clàr seo a thionndadh gu liosta (liosta).

Digression dig

• Clàr Is e sreath dà-mheudach a th’ ann le grunn shreathan is cholbhan.
• Clàr (Clàr) - sreang eagrachaidh aon-thaobhach, anns a bheil grunn eileamaidean achaidhean le ainmean, mar eisimpleir [Ainm = “Masha”, Gnè = “f”, Aois = 25]
  
• List - colbh sreath aon-thaobhach, anns a bheil grunn eileamaidean, mar eisimpleir {1, 2, 3, 10, 42} or { “Faith Hope Love” }
  

Gus fuasgladh fhaighinn air an duilgheadas againn, bidh sinn gu sònraichte ùidh ann an seòrsa List.

Is e an cleas an seo gum faod nithean liosta ann an Power Query a bhith chan e a-mhàin àireamhan banal no teacsa, ach cuideachd liostaichean no clàran eile. Tha e ann an liosta cho duilich (liosta), air a dhèanamh suas de chlàran (clàran) a dh’ fheumas sinn ar n-eòlaire a thionndadh. Ann an comharradh syntactic Power Query (inntrigidhean eadar camagan ceàrnach, liostaichean eadar camagan lùbach) bhiodh seo coltach:

{

    [ Lorg = “St. Petersburg", Cuir an àite = "St. Petersburg"] ,

    [ Lorg = “St. Petersburg", Cuir an àite = "St. Petersburg"] ,

    [ Lorg = “Peadar”, Cuir na àite = “St. Petersburg"] ,

msaa

}

Tha cruth-atharrachadh mar seo air a dhèanamh a’ cleachdadh gnìomh sònraichte den chànan M a chaidh a thogail a-steach do Power Query - Clàr.ToRecords. Gus a chuir an sàs gu dìreach anns a’ bhàr fhoirmeil, cuir a ’ghnìomh seo ris a’ chòd ceum an sin Stòr.

Bha e:

Às deidh:

Às deidh dhuinn an gnìomh Table.ToRecords a chuir ris, atharraichidh coltas a’ bhùird againn - tionndaidhidh e gu liosta chlàran. Chithear susbaint chlàran fa leth aig bonn a’ phana seallaidh le bhith a’ briogadh air cùl na cealla ri taobh facal sam bith Clàr (ach chan ann ann an aon fhacal!)

A bharrachd air na tha gu h-àrd, tha e ciallach aon stròc eile a chuir ris - gus an liosta chruthaichte againn a thasgadh (bufair). Bheir seo air Power Query an liosta sgrùdaidh againn a luchdachadh aon uair na chuimhne agus gun a bhith ag ath-àireamhachadh a-rithist nuair a gheibh sinn cothrom air nas fhaide air adhart gus a chuir na àite. Gus seo a dhèanamh, paisg am foirmle againn ann an gnìomh eile - List.Buffer:

Bheir an leithid de thasgadh àrdachadh gu math follaiseach ann an astar (grunn thursan!) Le tòrr dàta tùsail ri ghlanadh.

Bidh seo a 'crìochnachadh ullachadh an leabhair-làimhe.

Tha e fhathast ri briogadh air Dachaigh - Dùin is Luchdaich - Dùin is Luchdaich gu… (Dachaigh - Dùin & Luchdaich - Dùin & Luchdaich gu ..), tagh roghainn Dìreach cruthaich ceangal (Cruthaich ceangal a-mhàin) agus till air ais gu Excel.

Ceum 2. A 'luchdachadh a' chlàr dàta

Tha a h-uile dad trite an seo. Mar a bha roimhe leis an leabhar fiosrachaidh, gheibh sinn suas gu àite sam bith sa chlàr, cliog air an taba Dàta 'phutan Bhon chlàr / raon agus ar bòrd Dàta a-steach Power Query. Ceum air a chur ris gu fèin-ghluasadach seòrsa atharraichte (Seòrsa air atharrachadh) faodaidh tu cuideachd a thoirt air falbh:

Chan eil feum air gnìomhan ullachaidh sònraichte leis, agus gluaisidh sinn air adhart chun an rud as cudromaiche.

Ceum 3. Dèan ath-chuiridhean a 'cleachdadh an gnìomh List.Accumulate

Nach cuir sinn colbh àireamhaichte ris a’ chlàr dàta againn a’ cleachdadh an àithne A' cur colbh ris - Custom Colbh (Cuir colbh ris - Colbh gnàthaichte): agus cuir a-steach ainm a’ cholbh a bharrachd san uinneig a tha a’ fosgladh (mar eisimpleir, seòladh ceartaichte) agus ar gnìomh draoidheil List.Accumulate:

Tha e fhathast ri briogadh air OK - agus gheibh sinn colbh leis na h-àitean a chaidh a dhèanamh:

Thoir an aire:

• Leis gu bheil Power Query mothachail air cùisean, cha deach fear eile a chuir air an loidhne mu dheireadh, oir tha “SPb” againn san eòlaire, chan e “SPb”.
• Ma tha grunn fo-thiotalan ri chur an àite aig an aon àm anns an stòr dàta (mar eisimpleir, anns an loidhne 7th feumaidh tu an dà chuid “S-Pb” agus “Prospectus” a chuir an àite), chan eil seo a’ cruthachadh duilgheadas sam bith (eu-coltach ri bhith a’ cur an àite foirmlean bho an dòigh roimhe).
• Mura h-eil dad ri chur na àite anns an teacsa tùsail (9mh loidhne), chan eil mearachdan a’ tachairt (eu-coltach, a-rithist, bho bhith a’ cur an àite foirmlean).

Tha astar iarrtas mar sin glè, glè reusanta. Mar eisimpleir, airson clàr de dhàta tùsail le meud 5000 sreathan, chaidh a’ cheist seo ùrachadh ann an nas lugha na diog (gun buffering, co-dhiù, mu 3 diogan!)

Mar a tha an gnìomh List.Accumulate ag obair

Ann am prionnsabal, dh'fhaodadh gur e seo an deireadh (dhomhsa a sgrìobhadh, agus dhutsa a leughadh) an artaigil seo. Ma tha thu airson chan e a-mhàin a bhith comasach, ach cuideachd tuigsinn mar a tha e ag obair “fon chochall”, feumaidh tu dàibheadh ​​​​beagan nas doimhne a-steach don toll coineanach agus dèiligeadh ris a’ ghnìomh List.Accumulate, a rinn a h-uile càil ùr. obair dhuinne.

Is e an co-chòrdadh airson an gnìomh seo:

=Liosta.Cruinn(liosta, sìol, càrnadh)

far a bheil

• liosta tha an liosta leis na h-eileamaidean air a bheil sinn ag ath-aithris. 
• sìol - staid tùsail
• càrnadh - gnìomh a choileanas cuid de ghnìomhachd (matamataigeach, teacsa, msaa) air an ath eileamaid den liosta agus a chruinnicheas toradh giollachd ann an caochladair sònraichte.
  

San fharsaingeachd, tha an co-chòrdadh airson gnìomhan sgrìobhaidh ann an Power Query a’ coimhead mar seo:

(argamaid1, argamaid2, …argamaidN) => cuid de ghnìomhan le argamaidean

Mar eisimpleir, dh’ fhaodadh an gnìomh cunntais a bhith air a riochdachadh mar:

(a, b) => a + b

Airson List.Accumulate , tha dà argamaid riatanach aig a’ ghnìomh cruinneachaidh seo (faodar rud sam bith ainmeachadh, ach tha na h-ainmean àbhaisteach stàite и -dràsta, mar a tha sa chuideachadh oifigeil airson na dreuchd seo, far a bheil:

• stàite - caochladair far a bheil an toradh air a chruinneachadh (is e a luach tùsail an tè a chaidh ainmeachadh gu h-àrd sìol)
  
• -dràsta - an ath luach ath-aithris bhon liosta liosta
  

Mar eisimpleir, leig dhuinn sùil a thoirt air na ceumannan ann an loidsig an togail a leanas:

=Liosta.Cruinn({3, 2, 5}, 10, (state, current) => staid + sruth)

Is e seo an cruinneachadh as ùire stàite is e an luach ar gnìomh List.Accumulate agus toraidhean mar thoradh air:

Ma nì thu fantasachadh beagan, an uairsin a’ cleachdadh an gnìomh List.Accumulate, faodaidh tu atharrais, mar eisimpleir, an gnìomh Excel CONCATENATE (ann an Power Query, canar ris an analogue aige Teacs.Combine) a’ cleachdadh an abairt:

No eadhon lorg an luach as àirde (aithris air gnìomh MAX Excel, ris an canar ann an Power Query Liosta.Max):

Ach, is e prìomh fheart List.Accumulate an comas a bhith a’ làimhseachadh chan e a-mhàin teacsa sìmplidh no liostaichean àireamhach mar argamaidean, ach nithean nas iom-fhillte - mar eisimpleir, liostaichean bho liostaichean no liostaichean-bho chlàran (hello, Directory!)

Bheir sinn sùil a-rithist air an togail a rinn an t-àite nar duilgheadas:

Liosta.Cruinn (Directory, [Seòladh], (staid, gnàthach) => Text.Replace (staid, làithreach [Lorg], làithreach [Ath-àite]) )

Dè dha-rìribh a tha a’ dol an seo?

1. Mar luach tùsail (sìol) bheir sinn a’ chiad teacsa cliobach às a’ cholbh [Seòladh] ar bòrd: 199034, St. Petersburg, str. Beringa, d. 1
2. An uairsin bidh List.Accumulate ag ath-aithris thairis air na h-eileamaidean den liosta aon ri aon - Leabhar-làimhe. Tha gach eileamaid den liosta seo na chlàr anns a bheil paidhir raointean “Dè a lorgar - Dè a thèid a chuir na àite” no, ann am faclan eile, an ath loidhne san eòlaire.
3. Bidh gnìomh cruinneachaidh a’ cur a-steach caochladair stàite luach tùsail (a 'chiad seòladh 199034, St. Petersburg, str. Beringa, d. 1) agus a’ coileanadh gnìomh cruinneachaidh air - an obair ùr a’ cleachdadh a’ ghnìomh àbhaisteach M Teacs.Replace (co-chosmhail ri gnìomh SUBSTITUTE Excel). Is e a cho-chòrdadh:

   Text.Replace (teacsa tùsail, na tha sinn a’ sireadh, na tha sinn a’ cur nan àite)

   agus seo againn:

• stàite is e ar seòladh salach, a tha na laighe ann stàite (a 'faighinn ann bho sìol)
  
• làithreach[Lorg] - luach fearainn Gus a lorg bhon ath inntrigeadh ath-aithris den liosta Directory, a tha na laighe anns a’ chaochladair -dràsta
• an-dràsta[Cuir na àite] - luach fearainn Neach-ionaid bhon ath inntrigeadh ath-aithris den liosta Directoryna laighe a-staigh -dràsta

Mar sin, airson gach seòladh, bithear a’ ruith làn chearcall àireamhachd de na loidhnichean uile san eòlaire gach turas, a’ cur luach bhon raon [Lorg] an àite an teacsa bhon raon [Lorg].

An dòchas gun d'fhuair thu am beachd 🙂

• Cuir an àite teacsa mòr ann an liosta a’ cleachdadh foirmlean
  
• Abairtean Cunbhalach (RegExp) ann an Ceist Cumhachd