moj prijateljPogosto sem v stiku z njim glede nekaterih resničnih težav, s katerimi se sooča pri delu s podatki v Excelu.
Velikokrat prevedem njegovo poizvedbo v vadnico za Excel na tem mestu, ker bi lahko bila koristna tudi mojim drugim bralcem.
To je tudi ena taka vadnica.
Prijatelj me je prejšnji teden poklical z naslednjimi vprašanji:
Podatke o naslovu imam v stolpcu v Excelu in želim identificirati/filtrirati celice, ki vsebujejo ponavljajoče se besedilne nize (besede) v naslovu.
Tukaj je podoben nabor podatkov, kjer želi filtrirati celice (celice z rdečimi puščicami), ki imajo v sebi ponavljajoče se besedilne nize:
To zdaj otežuje to, da podatki niso dosledni.Ker je to zbirka podatkovnih nizov, ki so jih ročno ustvarili prodajni zastopniki, lahko pride do neskladij v naborih podatkov.
premisli:
- V tem nizu podatkov se lahko ponovi kateri koli besedilni niz.Na primer, lahko je ime regije ali ime mesta ali oboje.
- Besede so ločene s presledki in ni doslednosti glede tega, ali je ime mesta za šestimi ali osmimi znaki.
- Takšnih zapisov je na tisoče in treba je filtrirati tiste, ki imajo ponavljajoče se besedilne nize.
Po preučitvi številnih možnosti, kot so besedilo v stolpce in formule, sem se končno odločil, da bom za to opravilo uporabil VBA.
Zato sem ustvaril funkcijo VBA po meri ('IdDuplicate'), da razčlenim te celice in mi dam TRUE, če so v besedilnem nizu podvojene besede, in FALSE, če ni dvojnikov (kot je prikazano spodaj):
Ta funkcija po meri analizira vsako besedo v besedilnem nizu in preveri, kolikokrat se pojavi v besedilu.Vrne TRUE, če je število večje od 1;V nasprotnem primeru vrnite FALSE.
Prav tako je bil ustvarjen tako, da šteje samo besede z več kot tremi znaki.
Ko imam podatke TRUE/FALSE, lahko enostavno filtriram vse zapise, ki so TRUE.
Zdaj naj vam pokažem, kako to storiti v Excelu.
Koda VBA za funkcijo po meri
To se naredi z ustvarjanjem funkcije po meri v VBA.To funkcijo lahko nato uporabite kot katero koli drugo funkcijo delovnega lista v Excelu.
Tukaj je njegova koda VBA:
Funkcija 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(String)toA0 Korak -1 Če Len(StringtoAnalyze(i)) < minWordLen Potem Pojdi PreskočiA Za j = 0 do i - 1 Če StringtoAnalyze(j) = StringtoAnalyze(i) Potem IdDuplicates = "TRUE" Pojdi na SkipB Konec, če Next j SkipA: Naprej i IdDuplicates = "FALSE" Preskoči B: Končna funkcija
Kako uporabljati to kodo VBA
Zdaj, ko imate kodo VBA, jo morate vstaviti v Excelovo ozadje, da bo lahko delovala kot običajna funkcija delovnega lista.
Tu so koraki za namestitev kode VBA v ozadju:
- Pojdite na zavihek za razvijalce.
- Kliknite na Visual Basic (lahko uporabite tudi bližnjico na tipkovnici ALT+F11)
- V odprtem ozadju urejevalnika VB z desno tipko miške kliknite kateri koli predmet delovnega zvezka.
- Pojdite na "Vstavi" in kliknite "Modul".To bo vstavilo objekt modula delovnega zvezka.
- V oknu kode modula kopirajte in prilepite zgoraj omenjeno kodo VBA.
Ko imate kodo VBA v ozadju, lahko uporabite funkcijo – 'IdDuplicates' kot katero koli drugo običajno funkcijo delovnega lista.
Ta funkcija vzame en parameter, referenco celice, v kateri imate besedilo.
Rezultat te funkcije je TRUE (če so v njej podvojene besede) ali FALSE (če ni dvojnikov).Ko imate ta seznam TRUE/FALSE, lahko filtrirate tiste z TRUE, da dobite vse celice, ki imajo v sebi ponavljajoče se besedilne nize.
Opomba: koda, ki sem jo ustvaril, je namenjena samo upoštevanju besed, ki so daljše od treh znakov.To zagotavlja, da če so v besedilnem nizu besede, ki so dolge 1, 2 ali 3 znake (npr. 12 A, KGM ali LDA), se te besede pri štetju dvojnikov prezrejo.To lahko enostavno spremenite v kodi, če je potrebno.
Ta funkcija je na voljo samo v delovnem zvezku, kamor ste kopirali kodo v modulu.Če želite, da je ta koda na voljo tudi v drugih delovnih zvezkih, morate to kodo kopirati in prilepiti v te delovne zvezke.Lahko pa ustvarite dodatek (če to omogočite, bo funkcija na voljo v vseh delovnih zvezkih v vašem sistemu).
Prav tako ne pozabite shraniti tega delovnega zvezka z razširitvijo .xlsm (ker vsebuje kodo makra).
