Testuaren ordezkapena Power Query-n List.Acumulate funtzioarekin

Nola ordezkatu azkar eta masiboki testua erreferentzia-zerrendaren arabera formulekin - dagoeneko konpondu dugu. Orain saia gaitezen Power Query-n egiten.

Askotan gertatzen den bezala egiteko zeregin hori azaltzea baino askoz errazagoa da zergatik funtzionatzen du, baina saia gaitezen biak egiten 🙂

Beraz, barruti arruntetatik sortutako bi taula dinamiko "adimentsu" ditugu teklatu lasterbide batekin Ctrl+T edo taldea Hasiera - Taula gisa formatu (Hasiera — Taula gisa formatu):

Lehenengo mahaira deitu nuen Data, bigarren taula - Directoryeremua erabiliz Taularen izena (Taularen izena) fitxa Constructor (Diseinua).

Ataza: ordezkatu taulako helbideak Data zutabe bateko agerraldi guztiak Aurkitu eskuliburua zutabetik dagozkion parekide zuzenetara Ordezko. Gelaxketako gainerako testuak ukitu gabe egon behar du.

1. urratsa. Kargatu direktorioa Power Query-n eta bihurtu zerrenda batean

Gelaxka aktiboa erreferentzia-taularen edozein lekutan ezarri ondoren, egin klik fitxan Data (Data)edo fitxan Power kontsulta (Excel-en bertsio zahar bat baduzu eta Power Query gehigarri gisa instalatu baduzu aparteko fitxa batean) botoian Taula/barrutitik (Taula/Barrutitik).

Erreferentzia-taula Power Query kontsulta-editorean kargatuko da:

Oztopatzeko, automatikoki gehitutako urratsa aldatutako mota (Mota aldatua) eskuineko panelean, aplikatutako urratsak segurtasunez ezabatu daitezke, urratsa bakarrik utziz Iturria (Iturria):

Orain, eraldaketa eta ordezkapen gehiago egiteko, taula hau zerrenda (zerrenda) bihurtu behar dugu.

Digresio lirikoa

• Taula hainbat errenkada eta zutabez osatutako bi dimentsioko array bat da.
• Grabatu (Grabatu) – dimentsio bakarreko array-katea, izenak dituzten hainbat eremu-elementuz osatua, adibidez [Izena = “Masha”, Generoa = “f”, Adina = 25]
  
• zerrenda – dimentsio bakarreko array-zutabe bat, hainbat elementuz osatua, adibidez {1, 2, 3, 10, 42} or { "Faith Hope Love" }
  

Gure arazoa konpontzeko, batez ere mota interesatuko zaigu zerrenda.

Hemen trikimailua da Power Query-ko zerrendako elementuak zenbaki edo testu hutsalak ez ezik, beste zerrenda edo erregistro batzuk ere izan daitezkeela. Halako zerrenda (zerrenda) delikatua da, erregistroz (erregistroz) osatutakoa, gure direktorioari buelta eman behar dioguna. Power Query idazkera sintaktikoan (sarrerak kortxete artean, zerrendak kortxete artean) honela izango litzateke:

{

    [ Aurkitu = “St. Petersburg", Ordezkatu = "St. Petersburg"] ,

    [ Aurkitu = “St. Petersburg", Ordezkatu = "St. Petersburg"] ,

    [ Aurkitu = “Peter”, Ordezkatu = “St. Petersburg"] ,

eta abar.

}

Eraldaketa hori Power Query-n integratutako M hizkuntzaren funtzio berezi bat erabiliz egiten da. Taula.Erregistroetara. Formula-barran zuzenean aplikatzeko, gehitu funtzio hau urrats-kodean bertan Iturria.

Zen:

Ondoren:

Table.ToRecords funtzioa gehitu ondoren, gure taularen itxura aldatuko da - erregistroen zerrenda bihurtuko da. Erregistro indibidualen edukia ikuspegi panelaren behealdean ikus daiteke edozein hitz ondoan dagoen gelaxka atzeko planoan klik eginez Grabatu (baina ez hitz bakarrean!)

Aurrekoaz gain, zentzuzkoa da trazu bat gehiago gehitzea, gure sortutako zerrenda cachean (bufferean). Honek Power Query-k gure bilaketa-zerrenda behin memorian kargatzera behartuko du eta ez berriro kalkulatuko du geroago hura ordezkatzeko atzitzen dugunean. Horretarako, bildu gure formula beste funtzio batean - Zerrenda.Buffer:

Horrelako cacheak abiaduraren igoera oso nabarmena emango du (hainbat aldiz!) hasierako datu kopuru handiarekin garbitu behar diren.

Honek eskuliburuaren prestaketa osatzen du.

Klik egiteko geratzen da Hasiera – Itxi eta kargatu – Itxi eta kargatu… (Hasiera — Itxi&Kargatu — Itxi&Kargatu hona..), hautatu aukera bat Konexio bat sortu besterik ez dago (Sortu konexioa soilik) eta itzuli Excel-era.

2. urratsa. Datuen taula kargatzea

Hemen dena hutsala da. Erreferentzia liburuarekin lehen bezala, taulako edozein tokitara igotzen gara, fitxan klik egin Data botoia Taula/Barrutitik eta gure mahaia Data Power Query-n sartzen da. Automatikoki gehitutako urratsa aldatutako mota (Mota aldatua) ere kendu dezakezu:

Ez da berarekin prestatzeko ekintza berezirik egin behar, eta garrantzitsuenera pasatzen gara.

3. urratsa. Egin ordezkapenak Zerrenda.Pilatu funtzioa erabiliz

Gehitu dezagun kalkulatutako zutabe bat gure datu-taulari komandoa erabiliz Zutabe bat gehitzea - ​​Zutabe pertsonalizatua (Gehitu zutabea — Zutabe pertsonalizatua): eta idatzi gehitutako zutabearen izena irekitzen den leihoan (adibidez, helbidea zuzendua) eta gure funtzio magikoa Zerrenda.Pilatu:

Klik egiteko geratzen da OK – eta zutabe bat jasoko dugu egindako ordezkapenekin:

Apuntatu hori:

• Power Query-k maiuskulak eta minuskulak bereizten dituenez, azkenaurreko lerroan ez zen ordezkorik egon, direktorioan “SPb” dugulako, ez “SPb”.
• Sorburuko datuetan aldi berean ordezkatzeko hainbat azpikate badaude (adibidez, 7. lerroan "S-Pb" eta "Prospectus" biak ordezkatu behar dituzu), orduan horrek ez du arazorik sortzen (erantzukizuneko formulekin ordezkatzeak ez bezala. aurreko metodoa).
• Sorburuko testuan (9. lerroan) ordezkatzeko ezer ez badago, ez dago errorerik (formulekin ordezkatzean ez bezala).

Halako eskaera baten abiadura oso-oso duina da. Esate baterako, 5000 errenkadako tamainako hasierako datuen taula baterako, kontsulta hau segundo batean baino gutxiagoan eguneratu da (buffering gabe, bide batez, 3 segundo inguru!)

Zerrenda.Pilatu funtzioak nola funtzionatzen duen

Printzipioz, hau izan daiteke artikulu honen amaiera (nik idazteko, eta zuk irakurtzeko). Gai izateaz gain, "kanpaiaren azpian" nola funtzionatzen duen ulertu nahi baduzu, untxi-zuloan apur bat sakondu eta Zerrenda.Pilatu funtzioari aurre egin beharko diozu, ontziratu gabeko ordezkapen guztiak egin zituena. guretzat lan egin.

Funtzio honen sintaxia hau da:

=Zerrenda.Pilatu(zerrenda, hazia, metagailu)

non

• zerrenda zeinen elementuak errepikatzen ari garen zerrenda da. 
• hazia – hasierako egoera
• metagailu – Zerrendako hurrengo elementuan eragiketa bat (matematika, testua, etab.) egiten duen eta prozesatzeko emaitza aldagai berezi batean metatzen duen funtzioa.
  

Orokorrean, Power Query-n funtzioak idazteko sintaxia honelakoa da:

(argument1, argument2, … argumentN) => argumentuekin ekintza batzuk

Adibidez, batuketa funtzioa honela irudikatu daiteke:

(a, b) => a + b

Zerrenda.Pilatu rako, metagailu-funtzio honek beharrezko bi argumentu ditu (edozein izena eman daiteke, baina ohiko izenak dira egoera и egungo, funtzio honen laguntza ofizialean bezala, non:

• egoera – emaitza metatzen den aldagai bat (haren hasierako balioa goian aipatutakoa da hazia)
  
• egungo – zerrendako hurrengo balio errepikatua zerrenda
  

Adibidez, ikus ditzagun eraikuntza honen logikaren urratsak:

=Zerrenda.Pilatu({3, 2, 5}, 10, (egoera, korrontea) => egoera + korronte)

Hau da pilatutako azkena egoera balioa gure Zerrenda da. Metatu funtzioa eta irteerak ondorioz:

Pixka bat fantasiatzen baduzu, orduan Zerrenda.Pilatu funtzioa erabiliz, adibidez, Excel funtzioa KATATEA simulatu dezakezu (Power Query-n, bere analogikoa deitzen da. Testua.Konbinatu) esamoldea erabiliz:

Edo gehienezko balioa bilatu ere (Excel-en MAX funtzioaren imitazioa, Power Query-n deitzen dena Zerrenda.Max):

Hala ere, List.Accumulate-ren ezaugarri nagusia testu- edo zenbaki-zerrenda soilak argumentu gisa ez ezik, objektu konplexuagoak ere prozesatzeko gaitasuna da, adibidez, zerrendetako zerrendak edo erregistroetako zerrendak (kaixo, Directory!)

Ikus dezagun berriro gure arazoan ordezkapena egin zuen eraikuntza:

Zerrenda.Pilatu(Directory, [Helbidea], (egoera,unekoa) => Testua.Ordezkatu(egoera, unekoa[Aurkitu], unekoa[Ordezkatu]) )

Zer gertatzen da benetan hemen?

1. Hasierako balio gisa (hazia) zutabetik lehenengo testu traketsa hartzen dugu [Helbidea] gure mahaia: 199034, San Petersburgo, str. Beringa, d. 1
2. Ondoren, Zerrenda.Pilatu iteraketak zerrendako elementuen gainean banan-banan - eskuliburua. Zerrenda honetako elementu bakoitza "Zer aurkitu - Zer ordezkatu" eremu pare batez osatutako erregistro bat da, edo, bestela esanda, direktorioko hurrengo lerroa.
3. Metagailu funtzioak aldagai batean jartzen du egoera hasierako balioa (lehen helbidea 199034, San Petersburgo, str. Beringa, d. 1) eta metagailu-funtzio bat egiten du - ordezkapen-eragiketa M-funtzio estandarra erabiliz Testua.Ordezkatu (Excel-en ORDEZKO funtzioaren analogoa). Bere sintaxia hau da:

   Testua. Ordezkatu (jatorrizko testua, zer bilatzen ari garen, zerekin ordezkatzen ari garen)

   eta hemen daukagu:

• egoera gure helbide zikina da, bertan dagoena egoera (Hara iristen hazia)
  
• unekoa[Bilatu] – eremuaren balioa Aurkitu zerrendaren hurrengo iteratutako sarreratik Directory, aldagaian dagoena egungo
• unekoa[Ordezkatu] – eremuaren balioa Ordezko zerrendaren hurrengo iteratutako sarreratik Directoryetzanda egungo

Horrela, helbide bakoitzeko, direktorioko lerro guztien zenbaketa-ziklo osoa exekutatzen da aldi bakoitzean, [Aurkitu] eremuko testua [Ordeztu] eremuko balioarekin ordezkatuz.

Ideia atera izana 🙂

• Ordeztu multzoan testua zerrenda batean formulak erabiliz
  
• Adierazpen erregularrak (RegExp) Power Query-n