Innehåll
Att söka efter nyckelord i källtext är en av de vanligaste uppgifterna när man arbetar med data. Låt oss titta på dess lösning på flera sätt med följande exempel:
Låt oss anta att du och jag har en nyckelordslista – namnen på bilmärken – och en stor tabell över alla typer av reservdelar, där beskrivningar ibland kan innehålla ett eller flera sådana märken samtidigt, om reservdelen passar mer än en. bilmärke. Vår uppgift är att hitta och visa alla upptäckta nyckelord i angränsande celler genom ett givet separatortecken (till exempel ett kommatecken).
Metod 1. Power Query
Naturligtvis gör vi först våra tabeller till dynamiska ("smarta") med hjälp av en kortkommando ctrl+T eller kommandon Hem – Formatera som en tabell (Hem — Formatera som tabell), ge dem namn (till exempel Frimärkenи Reservdelar) och ladda en efter en i Power Query-redigeraren genom att välja på fliken Data – Från tabell/intervall (Data – från tabell/intervall). Om du har äldre versioner av Excel 2010-2013, där Power Query är installerat som ett separat tillägg, kommer den önskade knappen att finnas på fliken Strömfråga. Om du har en helt ny version av Excel 365, då knappen Från tabell/sortiment ringde dit nu Med löv (Från ark).
Efter att ha laddat varje tabell i Power Query återgår vi till Excel med kommandot Hem — Stäng och ladda — Stäng och ladda till... — Skapa endast anslutning (Hem — Stäng & Ladda — Stäng & Ladda till... — Skapa bara anslutning).
Låt oss nu skapa en dubblettförfrågan Reservdelargenom att högerklicka på den och välja Dubblettförfrågan (Duplicerad fråga), döp sedan om den resulterande kopieringsbegäran till Resultaten och vi kommer att fortsätta arbeta med honom.
Handlingslogiken är följande:
- På fliken Avancerat Lägger till en kolumn välja ett lag Anpassad kolumn (Lägg till kolumn – Anpassad kolumn) och ange formeln = Varumärken. Efter att ha klickat på OK vi kommer att få en ny kolumn, där det i varje cell kommer att finnas en kapslad tabell med en lista över våra sökord – biltillverkare:
- Använd knappen med dubbla pilar i rubriken i den tillagda kolumnen för att expandera alla kapslade tabeller. Samtidigt kommer raderna med beskrivningar av reservdelar att multipliceras med en multipel av antalet märken, och vi kommer att få alla möjliga par-kombinationer av "reservdelsmärke":
- På fliken Avancerat Lägger till en kolumn välja ett lag Villkorlig kolumn (Villkorlig kolumn) och ställ in ett villkor för att kontrollera förekomsten av ett nyckelord (varumärke) i källtexten (delbeskrivning):
- För att göra sökningen okänslig för skiftläge, lägg till det tredje argumentet manuellt i formelfältet Compare.OrdinalIgnoreCase till funktionen för händelsekontroll Text.Innehåller (om formelfältet inte är synligt kan det aktiveras på fliken översyn):
- Vi filtrerar den resulterande tabellen, lämnar bara en i den sista kolumnen, dvs matchar och tar bort den onödiga kolumnen förekomster.
- Gruppera identiska beskrivningar med kommandot Grupp av fliken Transformation (Omvandla — Gruppera efter). Välj som en aggregeringsoperation Alla rader (Alla rader). Vid utgången får vi en kolumn med tabeller, som innehåller alla detaljer för varje reservdel, inklusive märkena av biltillverkare vi behöver:
- För att extrahera betyg för varje del, lägg till ytterligare en beräknad kolumn på fliken Lägga till en kolumn – Anpassad kolumn (Lägg till kolumn – Anpassad kolumn) och använd en formel som består av en tabell (de finns i vår kolumn Detaljer) och namnet på den extraherade kolumnen:
- Vi klickar på knappen med dubbla pilar i rubriken i den resulterande kolumnen och väljer kommandot Extrahera värden (Extrahera värden)för att skriva ut stämplar med valfritt avgränsningstecken du vill ha:
- Ta bort en onödig kolumn Detaljer.
- För att lägga till de delar som försvann från den resulterande tabellen, där inga varumärken hittades i beskrivningarna, utför vi proceduren för att kombinera frågan Resultat med ursprunglig begäran Reservdelar Knappen Kombinera fliken Hem (Hem — Sammanfoga frågor). Kopplingstyp - Yttre fog höger (Höger yttre sammanfogning):
- Allt som återstår är att ta bort de extra kolumnerna och byta namn-flytta de återstående – och vår uppgift är löst:
Metod 2. Formler
Om du har en version av Excel 2016 eller senare kan vårt problem lösas på ett mycket kompakt och elegant sätt med den nya funktionen KOMBINERA (TEXTJOIN):
Logiken bakom denna formel är enkel:
- Funktion SÖK (HITTA) söker efter förekomsten av varje märke i tur och ordning i den aktuella beskrivningen av delen och returnerar antingen serienumret på symbolen, med början från vilket märket hittades, eller felet #VALUE! om märket inte finns i beskrivningen.
- Använd sedan funktionen IF (OM) и EOSHIBKA (FEL) vi ersätter felen med en tom textsträng "", och ordningsnumren på tecknen med själva märkesnamnen.
- Den resulterande matrisen av tomma celler och hittade varumärken sätts samman till en enda sträng genom ett givet separatortecken med hjälp av funktionen KOMBINERA (TEXTJOIN).
Prestandajämförelse och Power Query Query Buffering för Speedup
För prestandatestning, låt oss ta en tabell med 100 reservdelsbeskrivningar som initiala data. På den får vi följande resultat:
- Omräkningstid med formler (metod 2) – 9 sek. när du först kopierar formeln till hela kolumnen och 2 sek. vid upprepad (förmodligen buffring påverkar).
- Uppdateringstiden för Power Query-frågan (metod 1) är mycket sämre – 110 sekunder.
Mycket beror förstås på hårdvaran på en viss PC och den installerade versionen av Office och uppdateringar, men helhetsbilden tycker jag är tydlig.
För att påskynda en Power Query-fråga, låt oss buffra uppslagstabellen Frimärken, eftersom det inte ändras i processen för exekvering av frågor och det är inte nödvändigt att ständigt räkna om det (som Power Query de facto gör). För detta använder vi funktionen Tabell.buffert från det inbyggda Power Query-språket M.
För att göra detta, öppna en fråga Resultaten och på fliken översyn tryck på knappen Avancerad redaktör (Visa — Avancerad redigerare). Lägg till en rad med en ny variabel i fönstret som öppnas Marky 2, som kommer att vara en buffrad version av vår biltillverkares katalog, och använd denna nya variabel senare i följande frågekommando:
Efter en sådan förfining ökar uppdateringshastigheten för vår begäran med nästan 7 gånger – upp till 15 sekunder. En helt annan sak 🙂
- Luddrig textsökning i Power Query
- Masstextersättning med formler
- Bulktextersättning i Power Query med List.Accumulate-funktion