Inhoud
Zoeken naar trefwoorden in brontekst is een van de meest voorkomende taken bij het werken met gegevens. Laten we de oplossing op verschillende manieren bekijken aan de hand van het volgende voorbeeld:
Stel dat u en ik een lijst met trefwoorden hebben – de namen van automerken – en een grote tabel met allerlei reserveonderdelen, waarbij de beschrijvingen soms één of meerdere van dergelijke merken tegelijk kunnen bevatten, als het reserveonderdeel op meer dan één merk auto. Onze taak is om alle gedetecteerde trefwoorden in naburige cellen te vinden en weer te geven via een bepaald scheidingsteken (bijvoorbeeld een komma).
Methode 1. Power-query
Natuurlijk veranderen we eerst onze tabellen in dynamisch ("slim") met behulp van een sneltoets Ctrl+T of commando's Home – Opmaak als tabel (Home - Opmaken als tabel), geef ze namen (bijv Postzegelsи onderdelen) en laad één voor één in de Power Query-editor door op het tabblad te selecteren Gegevens – Uit tabel/bereik (Gegevens — Uit tabel/bereik). Als u oudere versies van Excel 2010-2013 hebt, waarbij Power Query als een aparte invoegtoepassing is geïnstalleerd, dan staat de gewenste knop op het tabblad Power Query. Als je een geheel nieuwe versie van Excel 365 hebt, dan is de knop Van tafel/bereik daar nu gebeld Met bladeren (Van Blad).
Nadat elke tabel in Power Query is geladen, keren we terug naar Excel met de opdracht Home — Sluiten en laden — Sluiten en laden naar… — Alleen verbinding maken (Home — Sluiten & Laden — Sluiten & Laden naar... — Alleen verbinding maken).
Laten we nu een duplicaatverzoek maken onderdelendoor er met de rechtermuisknop op te klikken en te selecteren Dubbel verzoek (Dubbele zoekopdracht)en hernoem vervolgens het resulterende kopieerverzoek naar De resultaten en we zullen met hem blijven samenwerken.
De logica van acties is de volgende:
- Op het tabblad Geavanceerd Een kolom toevoegen kies een team Aangepaste kolom (Kolom toevoegen — Aangepaste kolom) en voer de formule in = Merken. Na het klikken op OK we krijgen een nieuwe kolom, waar in elke cel een geneste tabel staat met een lijst met onze zoekwoorden - autofabrikantmerken:
- Gebruik de knop met dubbele pijlen in de kop van de toegevoegde kolom om alle geneste tabellen uit te vouwen. Tegelijkertijd zullen de regels met beschrijvingen van reserveonderdelen worden vermenigvuldigd met een veelvoud van het aantal merken, en krijgen we alle mogelijke paren-combinaties van "reserveonderdeel-merk":
- Op het tabblad Geavanceerd Een kolom toevoegen kies een team Voorwaardelijke kolom (Voorwaardelijke kolom) en stel een voorwaarde in voor het controleren van het voorkomen van een trefwoord (merk) in de brontekst (deelbeschrijving):
- Om het zoeken hoofdlettergevoelig te maken, voegt u handmatig het derde argument toe aan de formulebalk Vergelijk.OrdinalIgnoreCase naar de voorvalcontrolefunctie Tekst.Bevat (als de formulebalk niet zichtbaar is, kan deze op het tabblad worden ingeschakeld Beoordeling):
- We filteren de resulterende tabel, laten alleen degenen in de laatste kolom achter, dwz overeenkomsten en verwijderen de onnodige kolom Voorkomen.
- Identieke beschrijvingen groeperen met het commando Groeperen op tab Transformatie (Transformeren — Groeperen op). Kies als aggregatiebewerking Alle lijnen (Alle rijen). Aan de uitgang krijgen we een kolom met tabellen, die alle details bevat voor elk reserveonderdeel, inclusief de merken van autofabrikanten die we nodig hebben:
- Om cijfers voor elk onderdeel te extraheren, voegt u nog een berekende kolom toe aan het tabblad Een kolom toevoegen – Aangepaste kolom (Kolom toevoegen — Aangepaste kolom) en gebruik een formule bestaande uit een tabel (ze bevinden zich in onze kolom Details) en de naam van de geëxtraheerde kolom:
- We klikken op de knop met dubbele pijlen in de kop van de resulterende kolom en selecteren de opdracht Waarden extraheren (Waarden extraheren)om stempels uit te voeren met elk gewenst scheidingsteken:
- Een onnodige kolom verwijderen Details.
- Om aan de resulterende tabel de onderdelen toe te voegen die eruit zijn verdwenen, waar geen merken in de beschrijvingen zijn gevonden, voeren we de procedure uit voor het combineren van de zoekopdracht Resultaat met origineel verzoek onderdelen Combineren tab Home (Home — Zoekopdrachten samenvoegen). Connectie type - Buitenste verbinding rechts (Rechts buitenste join):
- Het enige dat overblijft is om de extra kolommen te verwijderen en de overgebleven kolommen een andere naam te geven - en onze taak is opgelost:
Methode 2. Formules
Als je een versie van Excel 2016 of hoger hebt, dan kan ons probleem op een zeer compacte en elegante manier worden opgelost met behulp van de nieuwe functie COMBINEREN (TEXTJOIN):
De logica achter deze formule is simpel:
- Functie ZOEKEN (VIND) zoekt achtereenvolgens naar het voorkomen van elk merk in de huidige beschrijving van het onderdeel en retourneert ofwel het serienummer van het symbool, vanaf waar het merk werd gevonden, ofwel de fout #VALUE! als het merk niet in de omschrijving staat.
- Gebruik dan de functie IF (ALS) и EOSHIBKA (ISFOUT) we vervangen de fouten door een lege tekststring “”, en de rangtelwoorden van de karakters door de merknamen zelf.
- De resulterende reeks lege cellen en gevonden merken wordt met behulp van de functie samengevoegd tot een enkele string via een bepaald scheidingsteken COMBINEREN (TEXTJOIN).
Prestatievergelijking en Power Query Query Buffering voor versnelling
Laten we voor prestatietests een tabel met 100 beschrijvingen van reserveonderdelen nemen als eerste gegevens. Hierop krijgen we de volgende resultaten:
- Herberekeningstijd met formules (methode 2) – 9 sec. wanneer u de formule voor het eerst naar de hele kolom kopieert en 2 sec. bij herhaalde (waarschijnlijk bufferende effecten).
- De updatetijd van de Power Query-query (methode 1) is veel slechter: 110 seconden.
Veel hangt natuurlijk af van de hardware van een bepaalde pc en de geïnstalleerde versie van Office en updates, maar het totaalplaatje is denk ik wel duidelijk.
Laten we de opzoektabel bufferen om een Power Query-query te versnellen Postzegels, omdat het niet verandert tijdens het uitvoeren van query's en het niet nodig is om het constant opnieuw te berekenen (zoals Power Query de facto doet). Hiervoor gebruiken we de functie Tabel.Buffer van de ingebouwde Power Query-taal M.
Open hiervoor een query De resultaten en op het tabblad Beoordeling druk op de knop Geavanceerde editor (Bekijken - Geavanceerde editor). Voeg in het geopende venster een regel toe met een nieuwe variabele Markie 2, wat een gebufferde versie van onze automaker-directory zal zijn, en gebruik deze nieuwe variabele later in de volgende queryopdracht:
Na een dergelijke verfijning neemt de updatesnelheid van ons verzoek bijna 7 keer toe - tot 15 seconden. Heel iets anders 🙂
- Fuzzy tekst zoeken in Power Query
- Bulkvervanging van tekst met formules
- Bulktekstvervanging in Power Query met List.Accumulate-functie