En af mine venner arbejder i et sundhedsanalysefirma. Han forbinder ofte med mig om nogle af de virkelige problemer, han står over for, mens han arbejder med data i Excel.
Mange gange konverterer jeg hans forespørgsler til Excel -tutorials på dette websted, da det også kunne være nyttigt for mine andre læsere.
Dette er også en sådan tutorial.
Min ven ringede til mig i sidste uge med følgende problem:
Der er adressedata i en kolonne i Excel, og jeg vil identificere/filtrere celler, hvor adressen har dublerede tekststrenge (ord) i den.
Her er det lignende datasæt, hvor han ønskede at filtrere celler, der har en dubleret tekststreng i den (dem med røde pile):
Det, der gør dette svært, er, at der ikke er nogen konsistens i disse data. Da dette er en samling af datasæt, der er blevet oprettet manuelt af salgsrepræsentanter, kan der være variationer i datasættet.
Overvej dette:
- Enhver tekststreng kan gentages i dette datasæt. Det kan for eksempel være områdets navn eller byens navn eller begge dele.
- Ordene adskilles af et mellemrumstegn, og der er ingen konsistens i, om bynavnet ville være der efter seks ord eller otte ord.
- Der er tusindvis af poster som denne, og behovet er at filtrere de poster, hvor der er dublerede tekststrenge.
Efter at have overvejet mange muligheder (f.eks. Tekst til kolonner og formler), besluttede jeg endelig at bruge VBA til at få dette gjort.
Så jeg oprettede en brugerdefineret VBA -funktion ('IdDuplicate') for at analysere disse celler og give mig TRUE, hvis der er et duplikeret ord i tekststrengen, og FALSK hvis der ikke er gentagelser (som vist nedenfor):
Denne brugerdefinerede funktion analyserer hvert ord i tekststrengen og kontrollerer, hvor mange gange det forekommer i teksten. Hvis tallet er mere end 1, returneres det SANDT; ellers returnerer den FALSK.
Det er også blevet skabt til kun at tælle ord med mere end tre tegn.
Når jeg har dataene TRUE/FALSE, kan jeg let filtrere alle de poster, der er SANDE.
Lad mig nu vise dig, hvordan du gør dette i Excel.
VBA -kode til brugerdefineret funktion
Dette gøres ved at oprette en brugerdefineret funktion i VBA. Denne funktion kan derefter bruges som enhver anden regnearksfunktion i Excel.
Her er VBA -koden til det:
Funktion IdDuplikater (rng Som Range) Som 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 Trin -1 Hvis Len (StringtoAnalyze (i)) <minWordLen Så gå til SkipA For j = 0 til i - 1 Hvis StringtoAnalyze (j) = StringtoAnalyze (i) Så IdDuplicates = "TRUE" GoTo SkipB Slut hvis næste j SkipA: Næste i IdDuplicates = "FALSE" SkipB: Slutfunktion
Tak Walter for at foreslå en bedre tilgang til denne kode!
Sådan bruges denne VBA -kode
Nu hvor du har VBA -koden, skal du placere den i bagenden af Excel, så den kan fungere som en almindelig regnearksfunktion.
Nedenfor er trinene til at sætte VBA -koden på backend:
- Gå til fanen Udvikler.
- Klik på Visual Basic (du kan også bruge tastaturgenvejen ALT + F11)
- I VB Editor-backenden, der åbnes, skal du højreklikke på et af projektmappeobjekterne.
- Gå til 'Indsæt' og klik på 'Modul'. Dette indsætter modulobjektet til projektmappen.
- I vinduet Modulkode skal du kopiere og indsætte den ovenfor nævnte VBA -kode.
Når du har VBA -koden i bagenden, kan du bruge funktionen - 'IdDuplicates' som enhver anden almindelig regnearksfunktion.
Denne funktion tager et enkelt argument, som er cellereferencen i cellen, hvor du har teksten.
Resultatet af funktionen er SANDT (hvis der er dublerede ord i det) eller FALSKT (hvis der ikke er dubletter). Når du har denne liste med SAND/FALSK, kan du filtrere dem med SAND for at få alle de celler, der har dublerede tekststrenge i den.
Bemærk: Jeg har kun oprettet koden for at overveje de ord, der er mere end tre tegn lange. Dette sikrer, at hvis der er 1, 2 eller 3 tegnlange ord (f.eks. 12 A, K G M eller L D A) i tekststrengen, ignoreres disse, mens dubletterne tælles. Hvis du vil, kan du nemt ændre dette i koden.
Denne funktion er kun tilgængelig i den projektmappe, hvor du har kopieret koden i modulet. Hvis du også ønsker, at dette også skal være tilgængeligt i andre projektmapper, skal du kopiere og indsætte denne kode i disse projektmapper. Alternativt kan du også oprette et tilføjelsesprogram (hvilket gør denne funktion tilgængelig i alle projektmapper på dit system).
Husk også at gemme denne projektmappe i .xlsm -udvidelse (da den har en makrokode i den).
