Inhoud
Hoe u snel en massaal de tekst kunt vervangen volgens de referentielijst met formules - we hebben het al uitgezocht. Laten we het nu proberen in Power Query.
Zoals vaak gebeurt uitvoeren deze taak is veel gemakkelijker dan uitleggen Waarom het werkt, maar laten we het allebei proberen
We hebben dus twee "slimme" dynamische tabellen gemaakt van gewone reeksen met een sneltoets Ctrl+T of team Home – Opmaak als tabel (Home - Opmaken als tabel):
Ik belde de eerste tafel Data, de tweede tafel - Directorymet behulp van veld Tafel naam (Tafel naam) tab Constructeur (Ontwerp).
Taak: vervang in adressen in de tabel Data alle exemplaren uit een kolom Vinden Handboek naar hun corresponderende juiste tegenhangers uit de kolom Plaatsvervanger. De rest van de tekst in de cellen moet onaangeroerd blijven.
Stap 1. Laad de map in Power Query en maak er een lijst van
Nadat u de actieve cel op een willekeurige plaats in de referentietabel hebt ingesteld, klikt u op de tab Data (Datum)of op het tabblad Power Query (als je een oude versie van Excel hebt en je hebt Power Query als invoegtoepassing op een apart tabblad geïnstalleerd) op de knop Van tafel/reeks (Van tafel/bereik).
De referentietabel wordt in de Power Query-queryeditor geladen:
Om niet te storen, een automatisch toegevoegde stap gewijzigd type: (Gewijzigd type) in het rechterpaneel kunnen de toegepaste stappen veilig worden verwijderd, zodat alleen de stap overblijft bron (Bron):
Om nu verdere transformaties en vervangingen uit te voeren, moeten we deze tabel in een lijst (lijst) veranderen.
Lyrische uitweiding
- tafel is een tweedimensionale array die bestaat uit verschillende rijen en kolommen.
- Opnemen (opnemen) – eendimensionale array-string, bestaande uit verschillende velden-elementen met namen, bijvoorbeeld [Naam = “Masha”, Geslacht = “v”, Leeftijd = 25]
- Lijst – een eendimensionale array-kolom, bestaande uit bijvoorbeeld meerdere elementen {1, 2, 3, 10, 42} or { "Geloof hoop liefde" }
Om ons probleem op te lossen, zijn we vooral geïnteresseerd in het type: Lijst.
De truc hier is dat lijstitems in Power Query niet alleen banale getallen of tekst kunnen zijn, maar ook andere lijsten of records. Het staat in zo'n lastige lijst (lijst), bestaande uit records (records) dat we onze directory moeten omdraaien. In Power Query syntactische notatie (vermeldingen tussen vierkante haken, lijsten tussen accolades) zou dit er als volgt uitzien:
{
[ Zoek = "St. Petersburg", Vervang = "St. Petersburg” ] ,
[ Zoek = "St. Petersburg", Vervang = "St. Petersburg” ] ,
[ Zoek = "Peter", Vervang = "St. Petersburg” ] ,
enz.
}
Een dergelijke transformatie wordt uitgevoerd met behulp van een speciale functie van de M-taal die is ingebouwd in Power Query: Tabel.NaarRecords. Om het direct in de formulebalk toe te passen, voegt u deze functie toe aan de stapcode daar bron.
Het was:
Na:
Na het toevoegen van de functie Table.ToRecords, verandert het uiterlijk van onze tabel - het wordt een lijst met records. De inhoud van individuele records kan onder aan het weergavevenster worden bekeken door in de celachtergrond naast een woord te klikken Record (maar niet in één woord!)
Naast het bovenstaande is het logisch om nog een slag toe te voegen - om onze gemaakte lijst te cachen (bufferen). Hierdoor wordt Power Query gedwongen onze opzoeklijst één keer in het geheugen te laden en niet opnieuw te berekenen wanneer we deze later openen om deze te vervangen. Om dit te doen, wikkelt u onze formule in een andere functie - Lijst.Buffer:
Een dergelijke caching zal een zeer merkbare snelheidstoename geven (met meerdere keren!) met een grote hoeveelheid initiële gegevens die moeten worden gewist.
Hiermee is de voorbereiding van het handboek voltooid.
Het blijft om op te klikken Home – Sluiten en laden – Sluiten en laden naar… (Home — Sluiten&Laden — Sluiten&Laden naar..), kies een optie Maak gewoon een verbinding (Alleen verbinding maken) en keer terug naar Excel.
Stap 2. De gegevenstabel laden
Alles is hier banaal. Zoals eerder met het naslagwerk, gaan we naar elke plaats in de tabel, klik op het tabblad Data Van tafel/bereik en onze tafel Data komt in Power Query. Automatisch toegevoegde stap gewijzigd type: (Gewijzigd type) je kunt ook verwijderen:
Er zijn geen speciale voorbereidende handelingen voor nodig en we gaan door naar het belangrijkste.
Stap 3. Voer vervangingen uit met behulp van de List.Accumulate-functie
Laten we een berekende kolom toevoegen aan onze gegevenstabel met behulp van de opdracht Een kolom toevoegen – Aangepaste kolom (Kolom toevoegen — Aangepaste kolom): en voer de naam van de toegevoegde kolom in het geopende venster in (bijvoorbeeld gecorrigeerd adres) en onze magische functie Lijst.Acculeren:
Het blijft om op te klikken OK – en we krijgen een kolom met de gemaakte vervangingen:
Merk op dat:
- Omdat Power Query hoofdlettergevoelig is, was er geen vervanging in de voorlaatste regel, omdat we in de directory "SPb" hebben, niet "SPb".
- Als er in de brongegevens meerdere substrings tegelijk moeten worden vervangen (in de 7e regel moet u bijvoorbeeld zowel "S-Pb" als "Prospectus" vervangen), dan levert dit geen problemen op (in tegenstelling tot het vervangen door formules uit de vorige methode).
- Als er niets te vervangen is in de brontekst (9e regel), dan treden er geen fouten op (in tegenstelling tot, nogmaals, bij vervanging door formules).
De snelheid van een dergelijk verzoek is zeer, zeer behoorlijk. Voor een tabel met initiële gegevens met een grootte van 5000 rijen, werd deze query bijvoorbeeld in minder dan een seconde bijgewerkt (zonder buffering overigens ongeveer 3 seconden!)
Hoe de List.Accumulate-functie werkt
In principe zou dit het einde kunnen zijn (voor mij om te schrijven, en voor jou om te lezen) dit artikel. Als je niet alleen wilt kunnen, maar ook wilt begrijpen hoe het "onder de motorkap" werkt, dan zul je wat dieper in het konijnenhol moeten duiken en omgaan met de lijst. Accumuleren-functie, die alle bulkvervanging deed werk voor ons.
De syntaxis voor deze functie is:
=Lijst.Acculeren(lijst, zaad, accumulator)
WAAR
- lijst is de lijst waarvan we de elementen herhalen.
- zaad - oorspronkelijke toestand
- accumulator – een functie die een bewerking uitvoert (wiskundig, tekst, enz.) op het volgende element van de lijst en het resultaat van de verwerking ophoopt in een speciale variabele.
Over het algemeen ziet de syntaxis voor het schrijven van functies in Power Query er als volgt uit:
(argument1, argument2, … argumentN) => enkele acties met argumenten
De sommatiefunctie kan bijvoorbeeld worden weergegeven als:
(a, b) => a + b
Voor List.Accumulate heeft deze accumulatorfunctie twee vereiste argumenten (ze kunnen van alles worden genoemd, maar de gebruikelijke namen zijn staat и actueel, zoals in de officiële help voor deze functie, waar:
- staat – een variabele waarbij het resultaat wordt geaccumuleerd (de initiële waarde is de hierboven genoemde) zaad)
- actueel – de volgende herhaalde waarde uit de lijst lijst
Laten we bijvoorbeeld eens kijken naar de stappen van de logica van de volgende constructie:
=Lijst.Acculeren({3, 2, 5}, 10, (staat, stroom) => staat + stroom)
- Variabele waarde staat is gelijk aan het initiële argument zaadIe staat = 10
- We nemen het eerste element van de lijst (stroom = 3) en voeg het toe aan de variabele staat (tien). We krijgen staat = 13.
- We nemen het tweede element van de lijst (stroom = 2) en vermeerderd met de huidige geaccumuleerde waarde in de variabele staat (tien). We krijgen staat = 15.
- We nemen het derde element van de lijst (stroom = 5) en vermeerderd met de huidige geaccumuleerde waarde in de variabele staat (tien). We krijgen staat = 20.
Dit is de laatste verzamelde staat de waarde is onze List.Accumulate-functie en geeft als resultaat:
Als u een beetje fantaseert, kunt u met behulp van de List.Accumulate-functie bijvoorbeeld de Excel-functie CONCATENATE simuleren (in Power Query wordt de analoog ervan genoemd Tekst.Combineren) met behulp van de uitdrukking:
Of zoek zelfs naar de maximale waarde (imitatie van Excel's MAX-functie, die in Power Query wordt genoemd Lijst.Max):
Het belangrijkste kenmerk van List.Accumulate is echter de mogelijkheid om niet alleen eenvoudige tekst- of numerieke lijsten als argumenten te verwerken, maar ook complexere objecten - bijvoorbeeld lijsten-van-lijsten of lijsten-van-records (hallo, Directory!)
Laten we nog eens kijken naar de constructie die de vervanging in ons probleem heeft uitgevoerd:
Lijst.Acculeren(Directory, [Adres], (staat, huidig) => Tekst.Vervang (staat, huidig [Zoeken], huidig [Vervangen]) )
Wat is hier echt aan de hand?
- Als beginwaarde (zaad) nemen we de eerste onhandige tekst uit de kolom [Adres] onze tafel: 199034, St. Petersburg, str. Beringa, ged. 1
- Vervolgens herhaalt List.Accumulate de elementen van de lijst één voor één - Handboek. Elk element van deze lijst is een record dat bestaat uit een paar velden "Wat te vinden - Wat te vervangen door" of, met andere woorden, de volgende regel in de directory.
- De accumulatorfunctie zet in een variabele staat beginwaarde (eerste adres 199034, St. Petersburg, str. Beringa, ged. 1) en voert er een accumulatorfunctie op uit - de vervangingsbewerking met behulp van de standaard M-functie Tekst.Vervangen (analoog aan de SUBSTITUTE-functie van Excel). De syntaxis is:
Text.Replace (oorspronkelijke tekst, wat we zoeken, wat we vervangen door)
en hier hebben we:
- staat is ons vuile adres, dat in . ligt staat (vanaf zaad)
- huidig[Zoeken] – veldwaarde Vinden vanaf de volgende herhaalde invoer van de lijst Directory, die in de variabele . ligt actueel
- huidige [vervangen] – veldwaarde Plaatsvervanger vanaf de volgende herhaalde invoer van de lijst Directoryliggen in actueel
Dus voor elk adres wordt elke keer een volledige cyclus van opsomming van alle regels in de directory uitgevoerd, waarbij de tekst uit het [Find]-veld wordt vervangen door de waarde uit het [Replace]-veld.
Hoop dat je het idee hebt
- Tekst in een lijst in bulk vervangen met formules
- Reguliere expressies (RegExp) in Power Query