egy barátomGyakran tartom vele a kapcsolatot azokkal a valós problémákkal kapcsolatban, amelyekkel az Excelben végzett adatokkal való munka során szembesül.
Sokszor lefordítom a lekérdezését Excel oktatóanyaggá ezen az oldalon, mert hasznos lehet a többi olvasómnak is.
Ez is egy ilyen oktatóanyag.
Múlt héten hívott a barátom a következő kérdésekkel:
Az Excelben egy oszlopban vannak címadatok, és szeretném azonosítani/szűrni azokat a cellákat, amelyek a címben ismétlődő szöveges karakterláncokat (szavakat) tartalmaznak.
Íme egy hasonló adatkészlet, ahol ki akarja szűrni azokat a cellákat, amelyekben ismétlődő szöveges karakterláncok vannak (piros nyilakkal ellátott cellák):
Ezt most az nehezíti, hogy az adatok nem konzisztensek.Mivel ez az értékesítési képviselők által manuálisan létrehozott adatkészletek összeállítása, eltérések lehetnek az adatkészletekben.
gondolkozz el róla:
- Ebben az adatkészletben bármilyen szöveges karakterlánc megismételhető.Ez lehet például egy régió vagy egy város neve, vagy mindkettő.
- A szavakat szóköz karakter választja el, és nincs egységesség abban, hogy a város neve hat vagy nyolc karakter után van-e.
- Több ezer ilyen rekord létezik, és ki kell szűrni azokat, amelyekben ismétlődő szöveges karakterláncok vannak.
Miután számos lehetőséget mérlegeltem, például a szöveget oszlopokba és a képleteket, végül úgy döntöttem, hogy a VBA-t használom a feladat elvégzéséhez.
Ezért létrehoztam egy egyéni VBA-függvényt ('IdDuplicate'), hogy elemezze ezeket a cellákat, és TRUE-t adjon meg, ha ismétlődő szavak vannak a szövegben, és FALSE-t, ha nincsenek ismétlődések (lásd alább):
Ez az egyéni függvény elemzi a szöveges karakterlánc minden egyes szavát, és ellenőrzi, hogy hányszor fordulnak elő a szövegben.IGAZ értéket ad vissza, ha a szám nagyobb, mint 1;Ellenkező esetben adja vissza a FALSE-t.
Ezenkívül úgy hozták létre, hogy csak háromnál több karakterből álló szavakat számoljon.
Miután megvan az IGAZ/HAMIS adatok, könnyen kiszűrhetem az összes IGAZ rekordot.
Most pedig hadd mutassam meg, hogyan kell ezt megtenni Excelben.
VBA kód az egyéni funkcióhoz
Ez egy egyéni függvény létrehozásával történik a VBA-ban.Ez a függvény ezután az Excel bármely más munkalapfüggvényéhez hasonlóan használható.
Itt van a VBA kódja:
Függvény 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 To()String(String to Anaound) -0. lépés If Len(StringtoAnalyze(i)) < minWordLen Akkor Ugrás A SkipA For j = 1 To i - 0 If StringtoAnalyze(j) = StringtoAnalyze(i) then IdDuplicates = "TRUE" Ugrás a Kihagyáshoz B Vége Ha Következő j Kihagyás A: Következő i IdDuplicates = "FALSE" SkipB: Funkció befejezése
Hogyan kell használni ezt a VBA-kódot
Most, hogy megvan a VBA kód, el kell helyeznie azt az Excel háttérprogramjába, hogy normál munkalapfüggvényként működjön.
Íme a lépések a VBA-kód háttérprogramba helyezéséhez:
- Lépjen a fejlesztői lapra.
- Kattintson a Visual Basic elemre (az ALT+F11 billentyűkombinációt is használhatja)
- A megnyitott VB-szerkesztő háttérprogramjában kattintson a jobb gombbal bármelyik munkafüzet-objektumra.
- Lépjen a "Beszúrás" elemre, és kattintson a "Modul" gombra.Ezzel beszúrja a munkafüzet modulobjektumát.
- A modulkód ablakban másolja ki és illessze be a fent említett VBA-kódot.
Miután megvan a VBA kód a háttérben, használhatja az „IdDuplicates” függvényt, mint bármely más szokásos munkalapfüggvényt.
Ez a függvény egy paramétert vesz fel, annak a cellának a cellahivatkozását, ahol a szöveg található.
Ennek a függvénynek az eredménye IGAZ (ha ismétlődő szavak vannak benne) vagy FALSE (ha nincsenek ismétlődések).Ha megvan az IGAZ/HAMIS lista, szűrheti az IGAZ értékkel rendelkezőket, hogy megkapja az összes olyan cellát, amelyben ismétlődő szöveges karakterláncok találhatók.
Megjegyzés: Az általam létrehozott kód csak a három karakternél hosszabb szavakra szolgál.Ez biztosítja, hogy ha a szövegben vannak olyan szavak, amelyek 1, 2 vagy 3 karakter hosszúak (pl. 12 A, KGM vagy LDA), akkor ezeket a szavakat a program figyelmen kívül hagyja az ismétlődések számlálásánál.Ezt könnyen módosíthatja a kódban, ha szükséges.
Ez a funkció csak abban a munkafüzetben érhető el, ahová a kódot másolta a modulban.Ha azt szeretné, hogy ez a kód más munkafüzetekben is elérhető legyen, ki kell másolnia és be kell illesztenie ezt a kódot ezekbe a munkafüzetekbe.Alternatív megoldásként létrehozhat egy bővítményt (ennek engedélyezésével a szolgáltatás elérhető lesz a rendszer összes munkafüzetében).
Ezenkívül ne felejtse el menteni ezt a munkafüzetet .xlsm kiterjesztéssel (mivel makrókódot tartalmaz).
