een vriend van mijIk heb vaak contact met hem over enkele van de echte problemen waarmee hij wordt geconfronteerd bij het werken met gegevens in Excel.
Vaak vertaal ik zijn vraag naar een Excel-zelfstudie op deze site, omdat het ook nuttig kan zijn voor mijn andere lezers.
Dit is ook zo'n tutorial.
Mijn vriend belde me vorige week met de volgende vragen:
Ik heb adresgegevens in een kolom in Excel en ik wil cellen identificeren/filteren die herhaalde tekstreeksen (woorden) in het adres bevatten.
Hier is een vergelijkbare dataset waarin hij cellen (cellen met rode pijlen) wil filteren die herhalende tekstreeksen bevatten:
Wat dit nu moeilijk maakt, is dat de gegevens niet consistent zijn.Aangezien dit een compilatie is van datasets die handmatig door verkopers zijn gemaakt, kunnen er verschillen in de datasets zijn.
denk er over na:
- Elke tekenreeks kan in deze dataset worden herhaald.Het kan bijvoorbeeld een regionaam of een plaatsnaam zijn, of beide.
- De woorden worden gescheiden door spatietekens en er is geen consistentie in het feit of de plaatsnaam na zes of acht tekens komt.
- Er zijn duizenden van dergelijke records en die met herhaalde tekstreeksen moeten worden gefilterd.
Nadat ik veel opties had overwogen, zoals tekst naar kolommen en formules, besloot ik uiteindelijk VBA te gebruiken om het werk te doen.
Dus ik heb een aangepaste VBA-functie ('IdDuplicate') gemaakt om deze cellen te ontleden en me TRUE te geven als er dubbele woorden in de tekstreeks staan en ONWAAR als er geen duplicaten zijn (zoals hieronder weergegeven):
Deze aangepaste functie analyseert elk woord in de tekenreeks en controleert hoe vaak het in de tekst voorkomt.Retourneert TRUE als het aantal groter is dan 1;Geef anders ONWAAR terug.
Het is ook gemaakt om alleen woorden met meer dan drie tekens te tellen.
Zodra ik de TRUE/FALSE-gegevens heb, kan ik gemakkelijk alle records filteren die WAAR zijn.
Ik zal u nu laten zien hoe u dit in Excel kunt doen.
VBA-code voor aangepaste functie
Dit wordt gedaan door een aangepaste functie in VBA te maken.Deze functie kan vervolgens worden gebruikt als elke andere werkbladfunctie in Excel.
Hier is de VBA-code:
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 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 = "FALSE" SkipB: functie beëindigen
Hoe deze VBA-code te gebruiken?
Nu u de VBA-code hebt, moet u deze in de backend van Excel plaatsen, zodat deze werkt als een gewone werkbladfunctie.
Hier zijn de stappen om de VBA-code in de backend te plaatsen:
- Ga naar het tabblad Ontwikkelaars.
- Klik op Visual Basic (u kunt ook de sneltoets ALT+F11 gebruiken)
- Klik in de geopende backend van de VB-editor met de rechtermuisknop op een willekeurig werkmapobject.
- Ga naar "Invoegen" en klik op "Module".Hiermee wordt het moduleobject van de werkmap ingevoegd.
- Kopieer en plak in het modulecodevenster de hierboven genoemde VBA-code.
Zodra u de VBA-code in de backend hebt, kunt u de functie - 'IdDuplicates' gebruiken zoals elke andere reguliere werkbladfunctie.
Deze functie heeft één parameter nodig, de celverwijzing van de cel waar je de tekst hebt.
Het resultaat van deze functie is TRUE (als er dubbele woorden in staan) of FALSE (als er geen duplicaten zijn).Zodra u deze TRUE/FALSE-lijst hebt, kunt u deze filteren met TRUE om alle cellen te krijgen die herhaalde tekstreeksen bevatten.
Opmerking: de code die ik heb gemaakt, is alleen voor woorden die langer zijn dan drie tekens.Dit zorgt ervoor dat als er woorden in de tekstreeks staan die 1, 2 of 3 tekens lang zijn (bijv. 12 A, KGM of LDA), die woorden worden genegeerd bij het tellen van duplicaten.U kunt dit indien nodig eenvoudig in code wijzigen.
Deze functie is alleen beschikbaar in de werkmap waar u de code in de module hebt gekopieerd.Als u wilt dat deze code ook in andere werkmappen beschikbaar is, moet u deze code kopiëren en in die werkmappen plakken.U kunt ook een invoegtoepassing maken (als u deze inschakelt, wordt de functie beschikbaar in alle werkmappen op uw systeem).
Vergeet ook niet om deze werkmap op te slaan met de extensie .xlsm (omdat deze macrocode bevat).
