n vriend van myEk is gereeld met hom in kontak oor sommige van die werklike probleme waarmee hy te kampe het wanneer hy met data in Excel werk.
Baie keer vertaal ek sy navraag in 'n Excel-tutoriaal op hierdie webwerf, want dit kan ook nuttig wees vir my ander lesers.
Dit is ook een so 'n tutoriaal.
My vriend het my verlede week gebel met die volgende vrae:
Ek het adresdata in 'n kolom in Excel en ek wil selle identifiseer/filtreer wat herhaalde teksstringe (woorde) in die adres bevat.
Hier is 'n soortgelyke datastel waar hy selle (selle met rooi pyle) wil filter wat herhalende teksstringe in het:
Wat dit nou moeilik maak, is dat die data nie konsekwent is nie.Aangesien dit 'n samestelling is van datastelle wat met die hand deur verkoopsverteenwoordigers geskep is, kan daar verskille in die datastelle wees.
dink daaroor:
- Enige teksstring kan in hierdie datastel herhaal word.Dit kan byvoorbeeld 'n streeknaam of 'n stadnaam wees, of albei.
- Woorde word deur spasie-karakters geskei, en daar is geen konsekwentheid of stadname na ses of agt karakters is nie.
- Daar is duisende sulke rekords en moet diegene wat enige herhaalde teksstringe het, filtreer.
Nadat ek baie opsies oorweeg het, soos teks na kolomme en formules, het ek uiteindelik besluit om VBA te gebruik om die werk te doen.
So ek het 'n pasgemaakte VBA-funksie ('IdDuplicate') geskep om hierdie selle te ontleed en vir my WAAR te gee as daar duplikaatwoorde in die teksstring is en ONWAAR as daar geen duplikate is nie (soos hieronder getoon):
Hierdie pasgemaakte funksie ontleed elke woord in die teksstring en kontroleer hoeveel keer dit in die teks voorkom.Wys WAAR as die telling groter as 1 is;Stuur anders ONWAAR terug.
Dit is ook geskep om slegs woorde met meer as drie karakters te tel.
Sodra ek die WAAR/ONWAAR data het, kan ek maklik alle rekords filter wat WAAR is.
Laat ek jou nou wys hoe om dit in Excel te doen.
VBA-kode vir persoonlike funksie
Dit word gedoen deur 'n pasgemaakte funksie in VBA te skep.Hierdie funksie kan dan as enige ander werkbladfunksie in Excel gebruik word.
Hier is sy VBA-kode:
Funksie IdDuplicates(rng As Range) As String Dim StringtoAnalize As Variant Dim i As Heelgetal Dim j As Heelgetal Konst minWordLen As Heelgetal = 4 StringtoAnalyze = Split(UCase(rng.Value), " ") Vir i = UBound(StringtoAnalyze) To 0 Stap -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 = "ONWAAR" SkipB: Eindig funksie
Hoe om hierdie VBA-kode te gebruik
Noudat jy die VBA-kode het, moet jy dit in Excel se backend sit sodat dit as 'n gewone werkbladfunksie kan werk.
Hier is die stappe om die VBA-kode in die agterkant te plaas:
- Gaan na die ontwikkelaaroortjie.
- Klik op Visual Basic (jy kan ook die sleutelbordkortpad ALT+F11 gebruik)
- In die oop VB-redigeerder-agterkant, regskliek op enige werkboekvoorwerp.
- Gaan na "Voeg in" en klik op "Module".Dit sal die module-objek van die werkboek invoeg.
- Kopieer en plak die VBA-kode hierbo genoem in die modulekodevenster.
Sodra jy die VBA-kode in die agterkant het, kan jy die funksie – 'IdDuplicates' soos enige ander gewone werkbladfunksie gebruik.
Hierdie funksie neem een parameter, die selverwysing van die sel waar jy die teks het.
Die resultaat van hierdie funksie is WAAR (as daar duplikaatwoorde in is) of ONWAAR (as daar geen duplikate is nie).Sodra jy hierdie WAAR/ONWAAR-lys het, kan jy dié met WAAR filtreer om alle selle te kry wat herhaalde teksstringe in het.
Let wel: Die kode wat ek geskep het, is slegs om rekening te hou met woorde wat langer as drie karakters is.Dit verseker dat indien daar woorde in die teksstring is wat 1, 2 of 3 karakters lank is (bv. 12 A, KGM of LDA), daardie woorde geïgnoreer word wanneer duplikate getel word.Jy kan dit maklik in kode verander indien nodig.
Hierdie kenmerk is slegs beskikbaar in die werkboek waar jy die kode in die module gekopieer het.As jy wil hê dat hierdie kode ook in ander werkboeke beskikbaar moet wees, moet jy hierdie kode in daardie werkboeke kopieer en plak.Alternatiewelik kan jy 'n byvoeging skep (as jy dit aktiveer, sal die kenmerk in alle werkboeke op jou stelsel beskikbaar wees).
Onthou ook om hierdie werkboek met 'n .xlsm-uitbreiding te stoor (omdat dit makro-kode bevat).
