Eden od mojih prijateljev dela v zdravstvenem analitičnem podjetju. Pogosto se poveže z mano o nekaterih resničnih težavah, s katerimi se srečuje pri delu s podatki v Excelu.
Njegove poizvedbe velikokrat na tem spletnem mestu spremenim v vaje programa Excel, saj bi to lahko pomagalo tudi drugim bralcem.
To je tudi ena takih vadnic.
Prejšnji teden me je poklical prijatelj z naslednjo težavo:
V stolpcu v Excelu so podatki o naslovu in želim identificirati/filtrirati celice, kjer ima naslov podvojene besedilne nize (besede).
Tu je podoben nabor podatkov, v katerem je želel filtrirati celice, ki imajo podvojen besedilni niz (tiste z rdečimi puščicami):
To otežuje dejstvo, da v teh podatkih ni doslednosti. Ker gre za zbirko podatkov, ki so jo ročno ustvarili prodajni predstavniki, se lahko v naboru podatkov razlikujejo.
Razmislite o tem:
- V tem naboru podatkov se lahko ponovi kateri koli besedilni niz. To je lahko na primer ime območja ali ime mesta ali oboje.
- Besede so ločene s presledkom in ni skladnosti v tem, ali bi ime mesta bilo tam po šestih besedah ali osmih besedah.
- Takšnih zapisov je na tisoče, zato jih je treba filtrirati, če obstajajo podvojeni besedilni nizi.
Po premisleku o številnih možnostih (na primer besedilo v stolpce in formule) sem se končno odločil za uporabo VBA.
Zato sem ustvaril funkcijo VBA po meri ('IdDuplicate') za analizo teh celic in mi dal TRUE, če je v besedilnem nizu podvojena beseda, in FALSE, če ni ponovitev (kot je prikazano spodaj):
Ta funkcija po meri analizira vsako besedo v besedilnem nizu in preveri, kolikokrat se pojavi v besedilu. Če je število večje od 1, vrne TRUE; drugače vrne FALSE.
Ustvarjen je bil tudi za štetje besed, ki imajo več kot tri znake.
Ko imam podatke TRUE/FALSE, lahko enostavno filtriram vse zapise, ki so TRUE.
Zdaj pa naj vam pokažem, kako to storiti v Excelu.
Koda VBA za funkcijo po meri
To naredite tako, da v VBA ustvarite funkcijo po meri. To funkcijo lahko nato uporabite kot katero koli drugo funkcijo delovnega lista v Excelu.
Tu je koda VBA:
Funkcija IdDuplicates (rng As Range) Kot String Dim StringtoAnalyze As Variant Dim i As Integer Dim j As Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split (UCase (rng.Value), "") Za i = UBound (StringtoAnalyze) Do 0 Korak -1 Če je Len (StringtoAnalyze (i)) <minWordLen Potem pojdi na SkipA Za j = 0 Na i - 1 Če je StringtoAnalyze (j) = StringtoAnalyze (i) Potem IdDuplicates = "TRUE" GoTo PreskočiB Konec Naprej i Naprej i IdDuplicates = "FALSE" SkipB: Končaj funkcijo
Hvala Walter, ker je predlagal boljši pristop k tej kodi!
Kako uporabljati to kodo VBA
Zdaj, ko imate kodo VBA, jo morate postaviti v ozadje Excela, da lahko deluje kot običajna funkcija delovnega lista.
Spodaj so navedeni koraki za postavitev kode VBA na zaledje:
- Pojdite na zavihek Razvijalec.
- Kliknite Visual Basic (uporabite lahko tudi bližnjico na tipkovnici ALT + F11)
- V zadnjem delu urejevalnika VB, ki se odpre, z desno tipko miške kliknite kateri koli predmet delovnega zvezka.
- Pojdite na "Vstavi" in kliknite na "Modul". To bo vstavilo predmet modula za delovni zvezek.
- V oknu Module code kopirajte in prilepite zgoraj omenjeno kodo VBA.
Ko imate kodo VBA na zadnji strani, lahko uporabite funkcijo - „IdDuplicates“ kot katero koli drugo običajno funkcijo delovnega lista.
Ta funkcija sprejme en sam argument, ki je sklic na celico celice, v kateri imate besedilo.
Rezultat 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 vsebujejo podvojene besedilne nize.
Opomba: Kodo sem ustvaril samo za upoštevanje tistih besed, ki so daljše od treh znakov. To zagotavlja, da če so v besedilnem nizu 1, 2 ali 3 znakovne besede (na primer 12 A, K G M ali L D A), se te med štetjem dvojnikov prezrejo. Če želite, lahko to preprosto spremenite v kodi.
Ta funkcija bo na voljo samo v delovnem zvezku, kjer ste kodo kopirali v modulu. Če želite, da je to na voljo tudi v drugih delovnih zvezkih, morate to kodo kopirati in prilepiti v te delovne zvezke. Lahko pa tudi ustvarite dodatek (omogočite, da bo ta funkcija na voljo v vseh delovnih zvezkih v vašem sistemu).
Ne pozabite shraniti tega delovnega zvezka v razširitvi .xlsm (ker ima v njem kodo makra).
