En av vennene mine jobber i et helseundersøkelsesfirma. Han tar ofte kontakt med meg om noen av de virkelige problemene han står overfor mens han jobber med data i Excel.
Mange ganger konverterer jeg spørsmålene hans til Excel -opplæringer på dette nettstedet, da det også kan være nyttig for mine andre lesere.
Dette er også en slik opplæring.
Vennen min ringte meg i forrige uke med følgende problem:
Det er adressedata i en kolonne i Excel, og jeg vil identifisere/filtrere celler der adressen har dupliserte tekststrenger (ord).
Her er det lignende datasettet der han ønsket å filtrere celler som har en duplisert tekststreng i den (de med røde piler):
Det som gjør dette vanskelig er at det ikke er noen konsistens i disse dataene. Siden dette er en samling av datasett som er opprettet manuelt av salgsrepresentanter, kan det være variasjoner i datasettet.
Vurder dette:
- Enhver tekststreng kan gjenta seg i dette datasettet. Det kan for eksempel være navnet på området eller navnet på byen eller begge deler.
- Ordene er atskilt med et mellomromstegn, og det er ingen konsistens i om bynavnet ville være der etter seks ord eller åtte ord.
- Det er tusenvis av poster som dette, og behovet er å filtrere postene der det er dupliserte tekststrenger.
Etter å ha vurdert mange alternativer (for eksempel tekst til kolonner og formler), bestemte jeg meg endelig for å bruke VBA for å få dette gjort.
Så jeg opprettet en tilpasset VBA -funksjon ('IdDuplicate') for å analysere disse cellene og gi meg TRUE hvis det er et duplikatord i tekststrengen, og FALSE i tilfelle det ikke er gjentakelser (som vist nedenfor):
Denne egendefinerte funksjonen analyserer hvert ord i tekststrengen og sjekker hvor mange ganger det forekommer i teksten. Hvis tallet er mer enn 1, returnerer det TRUE; ellers returnerer den FALSK.
Det er også laget for å bare telle ord med mer enn tre tegn.
Når jeg har de TRUE/FALSE -dataene, kan jeg enkelt filtrere alle postene som er TRUE.
La meg nå vise deg hvordan du gjør dette i Excel.
VBA -kode for egendefinert funksjon
Dette gjøres ved å opprette en tilpasset funksjon i VBA. Denne funksjonen kan deretter brukes som en hvilken som helst annen regnearkfunksjon i Excel.
Her er VBA -koden for den:
Funksjon IdDuplikater (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 Trinn -1 Hvis Len (StringtoAnalyze (i)) <minWordLen Gå deretter til SkipA For j = 0 To i - 1 If StringtoAnalyze (j) = StringtoAnalyze (i) Then IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = "FALSE" SkipB: Sluttfunksjon
Takk Walter for at du foreslår en bedre tilnærming til denne koden!
Slik bruker du denne VBA -koden
Nå som du har VBA -koden, må du plassere den i bakenden av Excel, slik at den kan fungere som en vanlig regnearkfunksjon.
Nedenfor er trinnene for å sette VBA -koden på backend:
- Gå til kategorien Utvikler.
- Klikk på Visual Basic (du kan også bruke hurtigtasten ALT + F11)
- I VB Editor-bakenden som åpnes, høyreklikker du på et av arbeidsbokobjektene.
- Gå til "Sett inn" og klikk på "Modul". Dette setter inn modulobjektet for arbeidsboken.
- I modulkodevinduet, kopier og lim inn VBA -koden som er nevnt ovenfor.
Når du har VBA -koden i bakenden, kan du bruke funksjonen - 'IdDuplicates' som alle andre vanlige regnearkfunksjoner.
Denne funksjonen tar ett enkelt argument, som er cellereferansen til cellen der du har teksten.
Resultatet av funksjonen er TRUE (hvis det er dupliserte ord i det) eller FALSE (hvis det ikke er duplikater). Når du har denne listen over TRUE/FALSE, kan du filtrere de med TRUE for å få alle cellene som har dupliserte tekststrenger i den.
Merk: Jeg har laget koden bare for å vurdere ordene som er mer enn tre tegn lange. Dette sikrer at hvis det er 1, 2 eller 3 tegn lange ord (for eksempel 12 A, K G M eller L D A) i tekststrengen, blir disse ignorert mens duplikatene teller. Hvis du vil, kan du enkelt endre dette i koden.
Denne funksjonen vil bare være tilgjengelig i arbeidsboken der du har kopiert koden i modulen. Hvis du vil at dette også skal være tilgjengelig i andre arbeidsbøker, må du kopiere og lime inn denne koden i disse arbeidsbøkene. Alternativt kan du også opprette et tillegg (som gjør denne funksjonen tilgjengelig i alle arbeidsbøkene på systemet ditt).
Husk også å lagre denne arbeidsboken i .xlsm -utvidelsen (siden den har en makrokode i den).
