Contents [show]
Hoe om die teks vinnig en in grootmaat te vervang volgens die verwysingslys met formules – ons het dit reeds uitgesorteer. Kom ons probeer dit nou in Power Query doen.
Soos dikwels gebeur perform hierdie taak is baie makliker as om te verduidelik hoekom dit werk, maar kom ons probeer albei doen 🙂
So, ons het twee "slim" dinamiese tabelle wat uit gewone reekse geskep is met 'n sleutelbordkortpad Ctrl+T of span Tuis – Formateer as 'n tabel (Tuis — Formateer as tabel):
Ek het die eerste tafel gebel data, die tweede tafel – Gidsveld gebruik Tafelnaam (Tabel naam) tab konstruktor (Ontwerp).
Taak: vervang in adresse in die tabel data alle gebeurtenisse uit 'n kolom Om te vind Handboek na hul ooreenstemmende korrekte eweknieë uit die kolom plaasvervanger. Die res van die teks in die selle moet onaangeraak bly.
Stap 1. Laai die gids in Power Query en verander dit in 'n lys
Nadat u die aktiewe sel op enige plek in die verwysingstabel gestel het, klik op die oortjie data (Datum)of op die blad Kragnavraag (as jy 'n ou weergawe van Excel het en jy Power Query as 'n byvoeging op 'n aparte oortjie geïnstalleer het) op die knoppie Van tafel/reeks (Vanaf tabel/reeks).
Die verwysingstabel sal in die Power Query-navraagredigeerder gelaai word:
Om nie in te meng nie, 'n outomaties bygevoegde stap gewysigde tipe (Veranderde tipe) in die regterpaneel kan die toegepaste stappe veilig uitgevee word, wat net die stap oorbly Bron (Bron):
Nou, om verdere transformasies en vervangings uit te voer, moet ons hierdie tabel in 'n lys (lys) verander.
Liriese afwyking
- Tabel is 'n tweedimensionele skikking wat uit verskeie rye en kolomme bestaan.
- Rekord (rekord) – eendimensionele skikkingstring, wat byvoorbeeld uit verskeie veldelemente met name bestaan [Naam = "Masha", Geslag = "v", Ouderdom = 25]
- lys – 'n eendimensionele skikking-kolom, wat byvoorbeeld uit verskeie elemente bestaan {1, 2, 3, 10, 42} or { "Geloof hoop liefde" }
Om ons probleem op te los, sal ons hoofsaaklik in die tipe belangstel lys.
Die truuk hier is dat lysitems in Power Query nie net banale getalle of teks kan wees nie, maar ook ander lyste of rekords. Dit is in so 'n moeilike lys (lys), bestaande uit rekords (rekords) dat ons ons gids moet omdraai. In Power Query sintaktiese notasie (inskrywings tussen vierkantige hakies, lyste tussen krullerige hakies) sal dit soos volg lyk:
{
[ Vind = "St. Petersburg", Vervang = "St. Petersburg”] ,
[ Vind = "St. Petersburg", Vervang = "St. Petersburg”] ,
[ Soek = "Peter", Vervang = "St. Petersburg”] ,
ens.
}
So 'n transformasie word uitgevoer met behulp van 'n spesiale funksie van die M-taal wat in Power Query ingebou is - Table.ToRecords. Om dit direk in die formulebalk toe te pas, voeg hierdie funksie by die stapkode daar Bron.
Dit was:
Na:
Nadat die Table.ToRecords-funksie bygevoeg is, sal die voorkoms van ons tabel verander – dit sal in 'n lys rekords verander. Die inhoud van individuele rekords kan onderaan die aansigvenster gesien word deur op die selvergrond langs enige woord te klik rekord (maar nie in 'n enkele woord nie!)
Benewens bogenoemde, maak dit sin om nog een slag by te voeg - om ons geskepte lys te kas (buffer). Dit sal Power Query dwing om ons opsoeklys een keer in die geheue te laai en dit nie weer te herbereken wanneer ons later toegang daartoe kry om dit te vervang nie. Om dit te doen, draai ons formule in 'n ander funksie - Lys.buffer:
Sulke caching sal 'n baie merkbare toename in spoed gee (met 'n paar keer!) met 'n groot hoeveelheid aanvanklike data wat skoongemaak moet word.
Dit voltooi die voorbereiding van die handboek.
Dit bly om op te klik Tuis – Maak toe en laai – Maak toe en laai na … (Tuis — Maak toe en laai — Maak toe en laai na..), kies 'n opsie Skep net 'n verbinding (Skep net verbinding) en keer terug na Excel.
Stap 2. Laai die datatabel
Alles is afgesaag hier. Soos voorheen met die naslaanboek, kom ons op enige plek in die tabel, klik op die blad data knoppie Van tabel/reeks en ons tafel data kom in Power Query. Stap outomaties bygevoeg gewysigde tipe (Veranderde tipe) jy kan ook verwyder:
Geen spesiale voorbereidende aksies word vereis om daarmee gedoen te word nie, en ons gaan aan na die belangrikste ding.
Stap 3. Voer vervangings uit deur die List.Accumulate-funksie te gebruik
Kom ons voeg 'n berekende kolom by ons datatabel deur die opdrag te gebruik Voeg 'n kolom by - Pasgemaakte kolom (Voeg kolom by – Pasgemaakte kolom): en tik die naam van die bygevoegde kolom in die venster wat oopmaak (byvoorbeeld, gekorrigeer adres) en ons towerfunksie Lys. Akkumuleer:
Dit bly om op te klik OK – en ons kry 'n kolom met die vervangings wat gemaak is:
Let daarop dat:
- Aangesien Power Query hooflettersensitief is, was daar geen vervanging in die voorlaaste reël nie, want in die gids het ons "SPb", nie "SPb".
- As daar verskeie substringe is om gelyktydig in die brondata te vervang (byvoorbeeld, in die 7de reël moet jy beide “S-Pb” en “Prospektus” vervang), dan skep dit geen probleme nie (anders as om te vervang met formules van die vorige metode).
- As daar niks in die bronteks is om te vervang nie (9de reël), dan vind geen foute plaas nie (anders as weereens van vervanging deur formules).
Die spoed van so 'n versoek is baie, baie ordentlik. Byvoorbeeld, vir 'n tabel met aanvanklike data met 'n grootte van 5000 rye, is hierdie navraag in minder as 'n sekonde opgedateer (sonder om te buffer, terloops, ongeveer 3 sekondes!)
Hoe die List.Accumulate-funksie werk
In beginsel kan dit die einde wees (vir my om te skryf, en vir jou om te lees) hierdie artikel. As jy nie net wil kan nie, maar ook wil verstaan hoe dit "onder die enjinkap" werk, dan sal jy 'n bietjie dieper in die konyngat moet duik en die List.Accumulate-funksie hanteer, wat al die grootmaatvervanging gedoen het. werk vir ons.
Die sintaksis vir hierdie funksie is:
=Lys.Accumulate(lys, saad, akkumulator)
waar
- lys is die lys oor wie se elemente ons herhaal.
- saad - aanvanklike toestand
- akkumulator – 'n funksie wat een of ander bewerking (wiskundig, teks, ens.) op die volgende element van die lys uitvoer en die resultaat van verwerking in 'n spesiale veranderlike versamel.
Oor die algemeen lyk die sintaksis vir die skryf van funksies in Power Query soos volg:
(argument1, argument2, … argumentN) => 'n paar aksies met argumente
Die somfunksie kan byvoorbeeld voorgestel word as:
(a, b) => a + b
Vir List.Accumulate het hierdie akkumulatorfunksie twee vereiste argumente (hulle kan enigiets genoem word, maar die gewone name is was и huidige, soos in die amptelike hulp vir hierdie funksie, waar:
- was – 'n veranderlike waar die resultaat opgehoop word (die aanvanklike waarde daarvan is die een hierbo genoem saad)
- huidige – die volgende herhaalde waarde uit die lys lys
Kom ons kyk byvoorbeeld na die stappe van die logika van die volgende konstruksie:
=Lys.Accumulate({3, 2, 5}, 10, (toestand, stroom) => toestand + stroom)
- Veranderlike waarde was is gelyk aan die aanvanklike argument gestel saadIe toestand = 10
- Ons neem die eerste element van die lys (huidige = 3) en voeg dit by die veranderlike was (tien). Ons kry toestand = 13.
- Ons neem die tweede element van die lys (huidige = 2) en plus dit na die huidige opgehoopte waarde in die veranderlike was (tien). Ons kry toestand = 15.
- Ons neem die derde element van die lys (huidige = 5) en plus dit na die huidige opgehoopte waarde in die veranderlike was (tien). Ons kry toestand = 20.
Dit is die jongste opgehoopte was die waarde is ons Lys. Akkumuleer funksie en uitsette as gevolg:
As jy 'n bietjie fantaseer, dan met behulp van die List.Accumulate-funksie, kan jy byvoorbeeld die Excel-funksie CONCATENATE simuleer (in Power Query word sy analoog genoem Teks.Kombineer) met behulp van die uitdrukking:
Of soek selfs vir die maksimum waarde (nabootsing van Excel se MAX-funksie, wat in Power Query genoem word Lys.Maks):
Die hoofkenmerk van List.Accumulate is egter die vermoë om nie net eenvoudige teks of numeriese lyste as argumente te verwerk nie, maar meer komplekse objekte – byvoorbeeld lyste-van-lyste of lyste-van-rekords (hallo, Directory!)
Kom ons kyk weer na die konstruksie wat die vervanging in ons probleem uitgevoer het:
Lys.Accumulate(Gids, [-Adres], (toestand, huidige) => Teks.Vervang(toestand, huidige[Vind], huidige[Vervang]) )
Wat gaan regtig hier aan?
- As beginwaarde (saad) neem ons die eerste lomp teks uit die kolom [-Adres] ons tafel: 199034, St Petersburg, str. Beringa, d. 1
- Dan herhaal List.Accumulate oor die elemente van die lys een vir een – Handboek. Elke element van hierdie lys is 'n rekord wat bestaan uit 'n paar velde "Wat om te vind - Waarmee om te vervang" of, met ander woorde, die volgende reël in die gids.
- Die akkumulatorfunksie plaas in 'n veranderlike was aanvanklike waarde (eerste adres 199034, St Petersburg, str. Beringa, d. 1) en voer 'n akkumulatorfunksie daarop uit - die vervangingsoperasie met behulp van die standaard M-funksie Teks.Vervang (analoog aan Excel se PLAASVERVANGER-funksie). Die sintaksis daarvan is:
Text.Replace (oorspronklike teks, waarna ons soek, waarmee ons vervang)
en hier het ons:
- was is ons vuil adres, wat in lê was (om daar vandaan te kom saad)
- huidige[Soek] – veldwaarde Om te vind vanaf die volgende herhaalde inskrywing van die lys Gids, wat in die veranderlike lê huidige
- huidige[Vervang] – veldwaarde plaasvervanger vanaf die volgende herhaalde inskrywing van die lys Gidslê in huidige
Dus, vir elke adres, word 'n volledige siklus van opsomming van alle reëls in die gids elke keer uitgevoer, wat die teks uit die [Vind]-veld vervang met die waarde van die [Vervang]-veld.
Hoop jy het die idee gekry 🙂
- Grootmaat vervang teks in 'n lys deur formules te gebruik
- Gereelde uitdrukkings (RegExp) in Power Query