Meer recent bespraken we het gebruik van de functie FILTER.XML om XML-gegevens van internet te importeren - de belangrijkste taak waarvoor deze functie in feite is bedoeld. Gaandeweg is er echter een ander onverwacht en mooi gebruik van deze functie opgedoken - voor het snel verdelen van plakkerige tekst in cellen.
Laten we zeggen dat we een gegevenskolom hebben zoals deze:
Voor het gemak wil ik het natuurlijk in aparte kolommen verdelen: bedrijfsnaam, plaats, straat, huis. Je kunt dit op verschillende manieren doen:
- Te gebruiken Tekst per kolom van het tabblad Data (Gegevens — Tekst naar kolommen) en ga drie stappen Tekst parser. Maar als de gegevens morgen veranderen, moet je het hele proces opnieuw doen.
- Laad deze gegevens in Power Query en verdeel ze daar, upload ze vervolgens weer naar het werkblad en werk de query vervolgens bij wanneer de gegevens veranderen (wat al gemakkelijker is).
- Als u on-the-fly moet bijwerken, kunt u een aantal zeer complexe formules schrijven om komma's te vinden en de tekst ertussen te extraheren.
En je kunt het eleganter doen en de FILTER.XML-functie gebruiken, maar wat heeft het ermee te maken?
De functie FILTER.XML ontvangt als eerste argument een XML-code - tekst gemarkeerd met speciale tags en attributen, en ontleedt deze vervolgens in zijn componenten, waarbij de gegevensfragmenten worden geëxtraheerd die we nodig hebben. De XML-code ziet er meestal ongeveer zo uit:
In XML moet elk data-element worden ingesloten in tags. Een tag is wat tekst (in het bovenstaande voorbeeld is dat manager, naam, winst) tussen punthaken. Tags komen altijd in paren - openen en sluiten (met een schuine streep aan het begin).
De functie FILTER.XML kan eenvoudig de inhoud van alle tags extraheren die we nodig hebben, bijvoorbeeld de namen van alle managers, en (het allerbelangrijkste) ze allemaal tegelijk in één lijst weergeven. Het is dus onze taak om tags toe te voegen aan de brontekst en deze om te zetten in XML-code die geschikt is voor latere analyse door de FILTER.XML-functie.
Als we het eerste adres uit onze lijst als voorbeeld nemen, dan zullen we het in deze constructie moeten veranderen:
Ik noemde de globale opening en sluiting van alle teksttag t, en de tags die elk element omlijsten zijn s., maar u kunt andere aanduidingen gebruiken - het maakt niet uit.
Als we inspringingen en regeleinden uit deze code verwijderen - overigens volledig optioneel en alleen voor de duidelijkheid toegevoegd, dan wordt dit allemaal een regel:
En het kan al relatief eenvoudig worden verkregen uit het bronadres door komma's erin te vervangen door een paar tags de functie gebruiken: PLAATSVERVANGER (VERVANGING) en lijmen met het symbool & aan het begin en einde van de openings- en sluitingstags:
Om het resulterende bereik horizontaal uit te breiden, gebruiken we de standaardfunctie TRANSP (TRANSPONEREN), onze formule erin wikkelen:
Een belangrijk kenmerk van dit hele ontwerp is dat er in de nieuwe versie van Office 2021 en Office 365 met ondersteuning voor dynamische arrays geen speciale gebaren nodig zijn voor invoer - voer gewoon in en klik op Enter - de formule zelf neemt het aantal cellen in beslag dat het nodig heeft en alles werkt met een knal. In eerdere versies, waar er nog geen dynamische arrays waren, moet u eerst een voldoende aantal lege cellen selecteren voordat u de formule invoert (u kunt met een marge), en nadat u de formule hebt gemaakt, drukt u op de sneltoets Ctrl+Shift+Enterom het in te voeren als een matrixformule.
Een soortgelijke truc kan worden gebruikt bij het scheiden van aan elkaar geplakte tekst in één cel door middel van een regeleinde:
Het enige verschil met het vorige voorbeeld is dat we hier in plaats van een komma het onzichtbare Alt + Enter regeleindeteken, dat in de formule kan worden gespecificeerd met de CHAR-functie, vervangen door code 10.
- De subtiliteiten van het werken met regeleinden (Alt + Enter) in Excel
- Verdeel tekst door kolommen in Excel
- Tekst vervangen door SUBSTITUTE