Vienas iš mano draugų dirba sveikatos priežiūros analizės įmonėje. Jis dažnai susisiekia su manimi dėl kai kurių realių problemų, su kuriomis susiduria dirbdamas su „Excel“ duomenimis.
Daug kartų aš keičiu jo užklausas į „Excel“ vadovėlius šioje svetainėje, nes tai gali būti naudinga ir kitiems mano skaitytojams.
Tai taip pat yra viena iš tokių pamokų.
Mano draugas man paskambino praeitą savaitę su tokia problema:
„Excel“ stulpelyje yra adreso duomenų, ir aš noriu identifikuoti/filtruoti ląsteles, kuriose adresas turi pasikartojančias teksto eilutes (žodžius).
Čia yra panašus duomenų rinkinys, kuriame jis norėjo filtruoti ląsteles, kuriose yra pasikartojanti teksto eilutė (tos, kuriose yra raudonos rodyklės):
Dabar tai apsunkina tai, kad šie duomenys nėra nuoseklūs. Kadangi tai yra rinkinys duomenų rinkinio, kurį rankiniu būdu sukūrė pardavimo atstovai, duomenų rinkinyje gali būti skirtumų.
Apsvarstykite tai:
- Bet kokia teksto eilutė gali pasikartoti šiame duomenų rinkinyje. Pavyzdžiui, tai gali būti vietovės pavadinimas arba miesto pavadinimas arba abu.
- Žodžiai yra atskirti tarpo simboliu, ir nėra nuoseklumo, ar miesto pavadinimas būtų po šešių ar aštuonių žodžių.
- Tokių įrašų yra tūkstančiai, todėl reikia filtruoti tuos įrašus, kuriuose yra pasikartojančių teksto eilučių.
Apsvarstęs daugybę variantų (pvz., Tekstą į stulpelius ir formules), pagaliau nusprendžiau pasinaudoti VBA.
Taigi aš sukūriau pasirinktinę VBA funkciją („IdDuplicate“), kad galėčiau išanalizuoti šias ląsteles ir pateikti man TRUE, jei teksto eilutėje yra pasikartojantis žodis, ir FALSE, jei pasikartojimų nėra (kaip parodyta žemiau):
Ši pasirinktinė funkcija analizuoja kiekvieną žodį teksto eilutėje ir tikrina, kiek kartų jis pasitaiko tekste. Jei skaičius yra didesnis nei 1, jis grąžina TRUE; kitaip jis grąžina FALSE.
Be to, jis buvo sukurtas tik skaičiuoti žodžius, daugiau nei tris simbolius.
Kai turėsiu TRUE/FALSE duomenis, galėsiu lengvai filtruoti visus teisingus įrašus.
Dabar leiskite man parodyti, kaip tai padaryti „Excel“.
Individualizuotos funkcijos VBA kodas
Tai daroma sukuriant pasirinktinę funkciją VBA. Šią funkciją galima naudoti kaip bet kurią kitą „Excel“ darbalapio funkciją.
Čia yra VBA kodas:
Funkcija IdDuplicates (rng kaip diapazonas) Kaip eilutė Dim StringtoAnalyze Kaip variantas Dim i As Integer Dim j Kaip Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split (UCase (rng.Value), "") For i = UBound (StringtoAnalyze) to 0 -1 veiksmas Jei Len (StringtoAnalyze (i)) <minWordLen Tada GoTo SkipA For j = 0 To i - 1 If StringtoAnalyze (j) = StringtoAnalyze (i) then IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Kitas i IdDuplicates = "FALSE" PraleistiB: pabaigos funkcija
Ačiū Walteriui, kad pasiūlėte geresnį požiūrį į šį kodą!
Kaip naudotis šiuo VBA kodu
Dabar, kai turite VBA kodą, turite jį įdėti į „Excel“ užpakalinę dalį, kad jis galėtų veikti kaip įprasta darbalapio funkcija.
Žemiau yra žingsniai, kaip įdėti VBA kodą į galinę sistemą:
- Eikite į skirtuką Kūrėjas.
- Spustelėkite „Visual Basic“ (taip pat galite naudoti sparčiuosius klavišus ALT + F11)
- Atsidariusiame VB redaktoriaus gale dešiniuoju pelės mygtuku spustelėkite bet kurį darbaknygės objektą.
- Eikite į „Įterpti“ ir spustelėkite „Modulis“. Taip bus įterptas darbo knygos modulio objektas.
- Lange „Modulio kodas“ nukopijuokite ir įklijuokite aukščiau paminėtą VBA kodą.
Kai užpakalinėje dalyje yra VBA kodas, galite naudoti funkciją - „IdDuplicates“ kaip bet kurią kitą įprastą darbalapio funkciją.
Ši funkcija apima vieną argumentą, kuris yra langelio, kuriame yra tekstas, nuoroda.
Funkcijos rezultatas yra TRUE (jei joje yra pasikartojančių žodžių) arba FALSE (jei nėra dublikatų). Kai turėsite šį TRUE/FALSE sąrašą, galėsite filtruoti tuos, kuriuose yra TRUE, kad gautumėte visas ląsteles, kuriose yra pasikartojančių teksto eilučių.
Pastaba: kodą sukūriau tik atsižvelgdamas į tuos žodžius, kurie yra ilgesni nei trys simboliai. Tai užtikrina, kad jei teksto eilutėje yra 1, 2 arba 3 simbolių ilgio žodžiai (pvz., 12 A, K G M arba L D A), jie bus ignoruojami skaičiuojant dublikatus. Jei norite, tai galite lengvai pakeisti kodu.
Ši funkcija bus prieinama tik darbaknygėje, kurioje nukopijavote modulį. Jei norite, kad tai būtų prieinama ir kitose darbaknygėse, turite nukopijuoti ir įklijuoti šį kodą tose darbaknygėse. Arba taip pat galite sukurti priedą (įjungus šią funkciją ši funkcija būtų prieinama visose jūsų sistemos darbaknygėse).
Taip pat nepamirškite išsaugoti šios darbaknygės plėtinyje .xlsm (nes jame yra makrokomandos kodas).
