Teksti hulgiasendus Power Querys funktsiooniga List.Accumulate

Kuidas kiiresti ja hulgi asendada teksti viidete loetelu järgi valemitega – oleme selle juba ära sorteerinud. Nüüd proovime seda teha Power Queryga.

Nagu sageli juhtub täitma see ülesanne on palju lihtsam kui seletamine miks see toimib, aga proovime teha mõlemat 🙂

Seega on meil kaks "nutikat" dünaamilist tabelit, mis on loodud tavalistest vahemikest kiirklahviga Ctrl+T või meeskond Avaleht – vorminda tabelina (Avaleht — vorminda tabelina):

Helistasin esimesse lauda kuupäev, teine ​​tabel - kataloogvälja kasutamine Tabeli nimi (tabeli nimi) tab Ehitaja (Kujundus).

Ülesanne: asendage tabelis olevad aadressid kuupäev kõik esinemised veerust Leidma käsiraamat vastavatele õigetele kolleegidele veerust Asendaja. Ülejäänud tekst lahtrites peaks jääma puutumata.

Samm 1. Laadige kataloog Power Querysse ja muutke see loendiks

Olles määranud aktiivse lahtri mis tahes kohta viitetabelis, klõpsake vahekaarti kuupäev (Kuupäev)või vahekaardil Toite päring (kui teil on Exceli vana versioon ja installisite Power Query lisandmoodulina eraldi vahekaardile) Tabelist/vahemikust (Tabelist/vahemikust).

Viitetabel laaditakse Power Query päringuredaktorisse:

Et mitte segada, automaatselt lisatud samm muudetud tüüp (Muudetud tüüp) parempoolses paneelis saab rakendatud samme turvaliselt kustutada, jättes alles ainult sammu allikas (Allikas):

Nüüd peame edasiste teisenduste ja asenduste tegemiseks muutma selle tabeli loendiks (loendiks).

Lüüriline kõrvalepõige

• Tabel on kahemõõtmeline massiiv, mis koosneb mitmest reast ja veerust.
• Salvestus (salvestis) – ühemõõtmeline massiiv-string, mis koosneb mitmest väljast-elemendist koos nimedega, näiteks [Nimi = "Maša", sugu = "f", vanus = 25]
  
• nimekiri – ühemõõtmeline massiiv-veerg, mis koosneb näiteks mitmest elemendist {1, 2, 3, 10, 42} or { "Usk lootus armastus" }
  

Probleemi lahendamiseks huvitab meid eelkõige tüüp nimekiri.

Siin on trikk selles, et Power Query loendiüksused võivad olla mitte ainult banaalsed numbrid või tekst, vaid ka muud loendid või kirjed. Just sellises keerulises loendis (loendis), mis koosneb kirjetest (kirjetest), peame oma kataloogi pöörama. Power Query süntaktilises tähistuses (kanded nurksulgudes, loendid lokkis sulgudes) näeks see välja järgmine:

{

    [ Leia = "St. Peterburi", Asenda = "St. Peterburi”] ,

    [ Leia = "St. Peterburi", Asenda = "St. Peterburi”] ,

    [ Leia = "Peeter", Asenda = "St. Peterburi”] ,

ja nii edasi

}

Selline teisendus viiakse läbi Power Querysse sisseehitatud M-keele erifunktsiooni abil - Tabel.ToRecords. Selle otse valemiribal rakendamiseks lisage see funktsioon seal olevale sammukoodile allikas.

See oli:

Pärast:

Peale funktsiooni Table.ToRecords lisamist muutub meie tabeli välimus – sellest saab kirjete loend. Üksikute kirjete sisu näete vaatepaani allosas, klõpsates mis tahes sõna kõrval lahtri taustal Rekord (aga mitte ühe sõnaga!)

Lisaks eelnevale on mõttekas lisada veel üks tõmme – meie loodud nimekirja vahemällu (puhverdamiseks). See sunnib Power Queryt meie otsinguloendi üks kord mällu laadima ja mitte uuesti arvutama, kui me sellele hiljem selle asendamiseks juurde pääseme. Selleks mähkige meie valem teise funktsiooni - Nimekiri.puhver:

Selline vahemällu salvestamine suurendab kiirust väga märgatavalt (mitu korda!) Suure hulga algandmete kustutamisega.

See lõpetab käsiraamatu ettevalmistamise.

Jääb üle klõpsata Avaleht – sulge ja laadi – sulge ja laadi… (Avaleht — Sulge&laadi — Sule&laadi...), valige suvand Looge lihtsalt ühendus (Loo ainult ühendus) ja naaske Excelisse.

Samm 2. Andmetabeli laadimine

Siin on kõik tühine. Nagu varem teatmeteose puhul, jõuame tabeli suvalisesse kohta, klõpsake vahekaarti kuupäev nupp Tabelist/vahemikust ja meie laud kuupäev siseneb Power Querysse. Automaatselt lisatud samm muudetud tüüp (Muudetud tüüp) võite ka eemaldada:

Sellega pole vaja mingeid erilisi ettevalmistavaid toiminguid teha ja liigume edasi kõige olulisema juurde.

Samm 3. Tehke asendused funktsiooni List.Accumulate abil

Lisame oma andmetabelisse käsu abil arvutatud veeru Veeru lisamine – kohandatud veerg (Lisa veerg – kohandatud veerg): ja sisestage avanevas aknas lisatud veeru nimi (näiteks parandatud aadress) ja meie maagiline funktsioon Nimekiri.Kogune:

Jääb üle klõpsata OK – ja saame veeru tehtud asendustega:

Pange tähele, et:

• Kuna Power Query on tõstutundlik, siis eelviimasel real asendust ei tehtud, sest kataloogis on meil “SPb”, mitte “SPb”.
• Kui lähteandmetes on korraga mitu asendatavat alamstringi (näiteks 7. real tuleb asendada nii “S-Pb” kui ka “Prospekt”), siis see probleeme ei tekita (erinevalt valemitega asendamisest eelmine meetod).
• Kui lähtetekstis (9. rida) pole midagi asendada, siis vigu ei teki (erinevalt jällegi valemitega asendamisest).

Sellise taotluse kiirus on väga-väga korralik. Näiteks 5000-realise algandmete tabeli puhul värskendati seda päringut vähem kui sekundiga (muide, ilma puhverdamiseta umbes 3 sekundiga!)

Funktsiooni List.Accumulate toimimine

Põhimõtteliselt võiks see olla selle artikli lõpp (mina kirjutan ja teie lugesite). Kui soovite mitte ainult osata, vaid ka mõista, kuidas see "kapoti all" töötab, siis peate sukelduma veidi sügavamale jäneseauku ja tegelema funktsiooniga List.Accumulate, mis tegi kogu hulgiasendamise. meie heaks töötama.

Selle funktsiooni süntaks on:

=List.Accumulate(nimekiri, seeme, akumulaator)

kus

• nimekiri on loend, mille elemente me kordame. 
• seeme - algseisund
• akumulaator – funktsioon, mis sooritab mõne toimingu (matemaatika, tekst vms) loendi järgmisel elemendil ja akumuleerib töötlemise tulemuse spetsiaalsesse muutujasse.
  

Üldiselt näeb Power Query kirjutamisfunktsioonide süntaks välja järgmine:

(argument1, argument2, … argumentN) => mõned toimingud argumentidega

Näiteks võib liitmisfunktsiooni esitada järgmiselt:

(a, b) => a + b

List.Accumulate jaoks on sellel akumulaatorifunktsioonil kaks nõutavat argumenti (neid võib nimetada ükskõik milleks, kuid tavalised nimed on riik и praegune, nagu selle funktsiooni ametlikus spikris, kus:

• riik – muutuja, kuhu tulemus akumuleeritakse (selle algväärtus on ülalmainitud seeme)
  
• praegune – loendist järgmine korduv väärtus nimekiri
  

Vaatame näiteks järgmise konstruktsiooni loogika etappe:

=List.Accumulate({3, 2, 5}, 10, (olek, vool) => olek + vool)

See on viimane kogutud riik väärtus on meie List.Accumulate funktsioon ja väljundid selle tulemusena:

Kui natukene fantaseerida, siis funktsiooni List.Accumulate abil saab simuleerida näiteks Exceli funktsiooni CONCATENATE (Power Querys on selle analoog nn. Tekst.Kombineeri) kasutades väljendit:

Või isegi otsige maksimaalset väärtust (Exceli funktsiooni MAX imitatsioon, mida Power Querys nimetatakse Nimekiri.Max):

List.Accumulate'i põhiomaduseks on aga võimalus töödelda argumentidena mitte ainult lihtsat teksti või numbrilisi loendeid, vaid ka keerukamaid objekte – näiteks loendeid loenditest või kirjetest loendeid (tere, kataloog!)

Vaatame uuesti konstruktsiooni, mis meie probleemi puhul asendas:

List.Accumulate(kataloog, [Aadress], (olek, praegune) => Tekst.Asenda(olek, praegune[Leia], praegune[Asenda]) )

Mis siin tegelikult toimub?

1. Algväärtusena (seeme) võtame veerust esimese kohmaka teksti [Aadress] meie laud: 199034, Peterburi, str. Beringa, el. 1
2. Seejärel kordab List.Accumulate loendi elemendid ükshaaval – käsiraamat. Iga selle loendi element on kirje, mis koosneb paarist väljadest “Mida otsida – millega asendada” ehk teisisõnu kataloogi järgmisest reast.
3. Akumulaatori funktsioon paneb muutuja riik algväärtus (esimene aadress 199034, Peterburi, str. Beringa, el. 1) ja täidab sellel akufunktsiooni – asendusoperatsiooni standardse M-funktsiooni abil Tekst.Asenda (analoogselt Exceli funktsiooniga SUBSTITUTE). Selle süntaks on:

   Text.Replace (algtekst, mida otsime, millega asendame )

   ja siin on meil:

• riik on meie räpane aadress, mis asub riik (sinna jõudes seeme)
  
• praegune[otsing] – välja väärtus Leidma loendi järgmisest itereeritud kirjest kataloog, mis asub muutujas praegune
• praegune[Asenda] – välja väärtus Asendaja loendi järgmisest itereeritud kirjest katalooglamades praegune

Seega käivitatakse iga aadressi puhul iga kord kataloogi kõigi ridade loendustsükkel, asendades väljal [Otsi] oleva teksti väärtusega väljal [Asenda].

Loodetavasti saite ideest aru 🙂

• Teksti hulgi asendamine loendis valemite abil
  
• Regulaaravaldised (RegExp) Power Querys