en venn av megJeg er ofte i kontakt med ham om noen av de virkelige problemene han møter når han jobber med data i Excel.
Mange ganger oversetter jeg søket hans til en Excel-opplæring på dette nettstedet fordi det kan være nyttig for mine andre lesere også.
Dette er også en slik opplæring.
Vennen min ringte meg forrige uke med følgende spørsmål:
Jeg har adressedata i en kolonne i Excel og jeg ønsker å identifisere/filtrere celler som inneholder gjentatte tekststrenger (ord) i adressen.
Her er et lignende datasett der han vil filtrere celler (celler med røde piler) som har repeterende tekststrenger:
Det som gjør dette vanskelig nå er at dataene ikke er konsistente.Siden dette er en sammenstilling av datasett manuelt opprettet av selgere, kan det være avvik i datasettene.
Tenk på det:
- Enhver tekststreng kan gjentas i dette datasettet.Det kan for eksempel være et regionnavn eller et bynavn, eller begge deler.
- Ordene er atskilt med mellomrom, og det er ingen konsistens i om bynavnet er etter seks eller åtte tegn.
- Det er tusenvis av slike poster og må filtrere de som har gjentatte tekststrenger.
Etter å ha vurdert mange alternativer som tekst til kolonner og formler, bestemte jeg meg til slutt for å bruke VBA for å gjøre jobben.
Så jeg opprettet en tilpasset VBA-funksjon ('IdDuplicate') for å analysere disse cellene og gi meg TRUE hvis det er dupliserte ord i tekststrengen og FALSE hvis det ikke er duplikater (som vist nedenfor):
Denne egendefinerte funksjonen analyserer hvert ord i tekststrengen og sjekker hvor mange ganger det forekommer i teksten.Returnerer TRUE hvis antallet er større enn 1;Ellers returner FALSE.
Den ble også laget for å kun telle ord med mer enn tre tegn.
Når jeg har SANN/USANN-dataene, kan jeg enkelt filtrere alle poster som er SANN.
La meg nå vise deg hvordan du gjør dette i Excel.
VBA-kode for tilpasset funksjon
Dette gjøres ved å lage en egendefinert funksjon i VBA.Denne funksjonen kan deretter brukes som en hvilken som helst annen regnearkfunksjon i Excel.
Her er VBA-koden:
Funksjon 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 Trinn -1 If Len(StringtoAnalyze(i)) < minWordLen Then GoTo 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
Slik bruker du denne VBA-koden
Nå som du har VBA-koden, må du legge den i Excels backend slik at den kan fungere som en vanlig regnearkfunksjon.
Her er trinnene for å sette VBA-koden i backend:
- Gå til utviklerfanen.
- Klikk på Visual Basic (du kan også bruke hurtigtasten ALT+F11)
- Høyreklikk på et hvilket som helst arbeidsbokobjekt i den åpne VB-editorens backend.
- Gå til "Sett inn" og klikk "Modul".Dette vil sette inn modulobjektet til arbeidsboken.
- I modulkodevinduet kopierer og limer du inn VBA-koden nevnt ovenfor.
Når du har VBA-koden i backend, kan du bruke funksjonen – 'IdDuplicates' som enhver annen vanlig regnearkfunksjon.
Denne funksjonen tar én parameter, cellereferansen til cellen der du har teksten.
Resultatet av denne funksjonen er TRUE (hvis det er dupliserte ord i den) eller FALSE (hvis det ikke er duplikater).Når du har denne SANN/USANN-listen, kan du filtrere dem med SANN for å få alle celler som har gjentatte tekststrenger.
Merk: Koden jeg opprettet er kun for å ta hensyn til ord som er lengre enn tre tegn.Dette sikrer at hvis det er ord i tekststrengen som er 1, 2 eller 3 tegn lange (f.eks. 12 A, KGM eller LDA), blir disse ordene ignorert når du teller duplikater.Du kan enkelt endre denne innstillingen i kode om nødvendig.
Denne funksjonen er kun tilgjengelig i arbeidsboken der du kopierte koden i modulen.Hvis du vil at denne koden også skal være tilgjengelig i andre arbeidsbøker, må du kopiere og lime inn denne koden i disse arbeidsbøkene.Alternativt kan du opprette et tillegg (ved å aktivere dette vil funksjonen gjøres tilgjengelig i alle arbeidsbøker på systemet ditt).
Husk også å lagre denne arbeidsboken med en .xlsm-utvidelse (fordi den inneholder makrokode).
