En av mina vänner arbetar på ett hälsoanalysföretag. Han har ofta kontakt med mig om några av de verkliga problem han står inför när han arbetar med data i Excel.
Många gånger konverterar jag hans frågor till Excel -självstudier på den här webbplatsen, eftersom det också kan vara till hjälp för mina andra läsare.
Detta är också en sådan handledning.
Min vän ringde mig förra veckan med följande fråga:
Det finns adressdata i en kolumn i Excel, och jag vill identifiera/filtrera celler där adressen har dubbla textsträngar (ord) i den.
Här är den liknande dataset där han ville filtrera celler som har en duplicerad textsträng i den (de med röda pilar):
Det som gör detta svårt är att det inte finns någon konsistens i dessa data. Eftersom detta är en sammanställning av datauppsättning som har skapats manuellt av säljare kan det finnas variationer i datamängden.
Tänk på detta:
- Alla textsträngar kan upprepas i denna dataset. Det kan till exempel vara namnet på området eller namnet på staden eller båda.
- Orden är åtskilda av ett mellanslagstecken, och det finns ingen överensstämmelse om stadens namn skulle finnas där efter sex ord eller åtta ord.
- Det finns tusentals poster som detta, och behovet är att filtrera de poster där det finns dubbletter av textsträngar.
Efter att ha övervägt många alternativ (till exempel text till kolumner och formler) bestämde jag mig slutligen för att använda VBA för att få det gjort.
Så jag skapade en anpassad VBA -funktion ('IdDuplicate') för att analysera dessa celler och ge mig SANT om det finns ett dubblettord i textsträngen, och FALSKT om det inte finns några repetitioner (som visas nedan):
Denna anpassade funktion analyserar varje ord i textsträngen och kontrollerar hur många gånger det förekommer i texten. Om räkningen är mer än 1 returnerar den SANT; annars returnerar det FALSKT.
Det har också skapats för att bara räkna ord med mer än tre tecken.
När jag väl har TRUE/FALSE -data kan jag enkelt filtrera alla poster som är TRUE.
Låt mig nu visa dig hur du gör detta i Excel.
VBA -kod för den anpassade funktionen
Detta görs genom att skapa en anpassad funktion i VBA. Denna funktion kan sedan användas som alla andra kalkylbladsfunktioner i Excel.
Här är VBA -koden för den:
Function IdDuplicates (rng As Range) As String Dim StringtoAnalyze As Variant Dim i As Integer Dim j As Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split (UCase (rng.Value), "") For i = UBound (StringtoAnalyze) To 0 Steg -1 Om Len (StringtoAnalyze (i)) <minWordLen Sedan Gå till SkipA För j = 0 Till i - 1 Om StringtoAnalyze (j) = StringtoAnalyze (i) Sedan IdDuplicates = "TRUE" Gå till SkipB Slut Om Nästa j SkipA: Nästa i IdDuplicates = "FALSE" SkipB: Slutfunktion
Tack Walter för att du föreslår ett bättre tillvägagångssätt för denna kod!
Hur man använder denna VBA -kod
Nu när du har VBA -koden måste du placera den i Excel -backend, så att den kan fungera som en vanlig kalkylbladsfunktion.
Nedan följer stegen för att sätta VBA -koden på backend:
- Gå till fliken Utvecklare.
- Klicka på Visual Basic (du kan också använda kortkommandot ALT + F11)
- I VB Editor-backend som öppnas, högerklicka på något av arbetsbokobjekten.
- Gå till "Infoga" och klicka på "Modul". Detta kommer att infoga modulobjektet för arbetsboken.
- I fönstret Modulkod, kopiera och klistra in VBA -koden som nämns ovan.
När du väl har VBA -koden i den bakre änden kan du använda funktionen - 'IdDuplicates' som alla andra vanliga kalkylbladsfunktioner.
Denna funktion tar ett enda argument, vilket är cellreferensen för cellen där du har texten.
Resultatet av funktionen är SANT (om det finns dubblettord i det) eller FALSKT (om det inte finns några dubbletter). När du har den här listan över SANT/FALSK kan du filtrera dem med SANT för att få alla celler som har dubbletter av textsträngar i den.
Obs! Jag har skapat koden endast för att beakta de ord som är mer än tre tecken långa. Detta säkerställer att om det finns 1, 2 eller 3 teckenlånga ord (t.ex. 12 A, K G M eller L D A) i textsträngen ignoreras dessa medan duplikaten räknas. Om du vill kan du enkelt ändra detta i koden.
Denna funktion är endast tillgänglig i arbetsboken där du har kopierat koden i modulen. Om du vill att detta ska finnas tillgängligt i andra arbetsböcker också, måste du kopiera och klistra in den här koden i dessa arbetsböcker. Alternativt kan du också skapa ett tillägg (vilket möjliggör denna funktion i alla arbetsböcker på ditt system).
Kom också ihåg att spara den här arbetsboken i .xlsm -tillägget (eftersom den har en makrokod i den).
