Jedan od mojih prijatelja radi u zdravstvenoj analitičkoj tvrtki. Često se povezuje sa mnom oko nekih problema iz stvarnog života s kojima se susreće dok radi s podacima u Excelu.
Mnogo puta pretvaram njegove upite u Excel tutoriale na ovoj web stranici, jer bi to moglo biti korisno i mojim drugim čitateljima.
Ovo je također jedan od takvih vodiča.
Moj prijatelj me nazvao prošli tjedan sa sljedećim problemom:
U stupcu u Excelu postoje podaci o adresi, a ja želim identificirati/filtrirati ćelije u kojima adresa sadrži duple tekstualne nizove (riječi).
Evo sličnog skupa podataka u kojem je želio filtrirati ćelije koje imaju dupli tekstualni niz (one s crvenim strelicama):
Ono što ovo otežava je to što nema dosljednosti u ovim podacima. Budući da je ovo kompilacija skupa podataka koji su ručno stvorili prodajni predstavnici, mogu doći do varijacija u skupu podataka.
Uzmite u obzir ovo:
- Bilo koji tekstualni niz mogao bi se ponoviti u ovom skupu podataka. Na primjer, to može biti naziv područja ili naziv grada ili oboje.
- Riječi su odvojene razmakom i nema dosljednosti u tome bi li naziv grada bio tamo nakon šest ili osam riječi.
- Ovakvih zapisa ima na tisuće, a potrebno je filtrirati te zapise gdje postoje dupli tekstualni nizovi.
Nakon što sam razmotrio mnoge mogućnosti (poput teksta u stupce i formule), konačno sam odlučio upotrijebiti VBA za to.
Stoga sam stvorio prilagođenu VBA funkciju ('IdDuplicate') za analizu ovih ćelija i daj mi TRUE ako postoji dvostruka riječ u tekstualnom nizu, te FALSE u slučaju da nema ponavljanja (kao što je prikazano u nastavku):
Ova prilagođena funkcija analizira svaku riječ u tekstualnom nizu i provjerava koliko se puta pojavljuje u tekstu. Ako je broj veći od 1, vraća TRUE; inače vraća FALSE.
Također, stvoreno je da broji samo riječi veće od tri znaka.
Kad dobijem TRUE/FALSE podatke, mogu lako filtrirati sve zapise koji su TRUE.
Dopustite mi da vam pokažem kako to učiniti u Excelu.
VBA kôd za prilagođenu funkciju
To se postiže stvaranjem prilagođene funkcije u VBA -i. Ova se funkcija tada može koristiti kao bilo koja druga funkcija radnog lista u Excelu.
Evo VBA koda za njega:
Funkcija IdDuplikati (rng kao raspon) Kao niz Dim StringtoAnalyze kao varijanta Dim i Kao cijeli broj Dim j Kao cijeli broj Const minWordLen Kao cijeli broj = 4 StringtoAnalyze = Split (UCase (rng.Value), "") Za i = UBound (StringtoAnalyze) do 0 Korak -1 Ako je Len (StringtoAnalyze (i)) <minWordLen Zatim idite na SkipA Za j = 0 Na i - 1 Ako je StringtoAnalyze (j) = StringtoAnalyze (i) Zatim IdDuplicates = "TRUE" Idi na SkipB Kraj Ako Sljedeći SkipA: Sljedeći i IdDuplicates = "FALSE" SkipB: Kraj funkcije
Hvala Walteru što je predložio bolji pristup ovom kodu!
Kako koristiti ovaj VBA kôd
Sada kada imate VBA kôd, morate ga postaviti u pozadinu programa Excel kako bi mogao raditi kao redovna funkcija radnog lista.
U nastavku su navedeni koraci za stavljanje VBA koda na pozadinu:
- Idite na karticu Developer.
- Pritisnite Visual Basic (možete koristiti i tipkovnički prečac ALT + F11)
- U zadnjem dijelu VB uređivača koji se otvori desnom tipkom miša kliknite bilo koji objekt radne knjige.
- Idite na "Umetni" i kliknite na "Modul". Ovo će umetnuti objekt modula za radnu knjigu.
- U prozoru koda modula kopirajte i zalijepite gore navedeni VBA kôd.
Nakon što imate VBA kôd na stražnjoj strani, možete koristiti funkciju - ‘IdDuplicates’ kao i bilo koju drugu redovitu funkciju radnog lista.
Ova funkcija uzima jedan jedini argument, a to je referenca ćelije ćelije u kojoj imate tekst.
Rezultat funkcije je TRUE (ako u njoj ima dupliciranih riječi) ili FALSE (ako nema duplikata). Kad dobijete ovaj popis TRUE/FALSE, možete filtrirati one s TRUE da biste dobili sve ćelije koje sadrže duple tekstualne nizove.
Napomena: Kôd sam stvorio samo kako bih uzeo u obzir one riječi koje imaju više od tri znaka. Time se osigurava da, ako u tekstualnom nizu postoje 1, 2 ili 3 znakovne riječi (kao što je 12 A, K G M ili L D A), one se zanemaruju pri prebrojavanju duplikata. Ako želite, to možete lako promijeniti u kodu.
Ova funkcija bit će dostupna samo u radnoj knjizi u kojoj ste kopirali kôd u modulu. U slučaju da želite da ovo bude dostupno i u drugim radnim knjigama, morate kopirati i zalijepiti ovaj kôd u te radne knjige. Alternativno, možete stvoriti i dodatak (omogućujući tako da ova funkcija postane dostupna u svim radnim knjigama na vašem sustavu).
Također, ne zaboravite spremiti ovu radnu knjigu u .xlsm ekstenziju (budući da u njoj ima makro kôd).
