Egyik barátom egy egészségügyi elemző cégben dolgozik. Gyakran kapcsolatba lép velem néhány valós kérdésben, amelyekkel az Excelben végzett adatkezelés során szembesül.
Sokszor a lekérdezéseit Excel oktatóanyaggá konvertálom ezen az oldalon, mivel ez hasznos lehet a többi olvasóm számára is.
Ez is egy ilyen oktatóanyag.
A barátom hívott a múlt héten a következő problémával:
Az Excel oszlopában címadatok találhatók, és szeretném azonosítani/szűrni azokat a cellákat, ahol a címben ismétlődő szöveges karakterláncok (szavak) találhatók.
Itt található a hasonló adatkészlet, amelyben szűrni akarta azokat a cellákat, amelyekben ismétlődő szöveges karakterlánc található (a piros nyilakkal rendelkezők):
Most az teszi nehézzé, hogy ezekben az adatokban nincs következetesség. Mivel ez az értékesítési képviselők által manuálisan létrehozott adathalmaz összeállítása, az adatkészletben eltérések lehetnek.
Ezt fontold meg:
- Bármely szöveges karakterlánc megismétlődhet ebben az adatkészletben. Ez lehet például a terület vagy a város neve, vagy mindkettő.
- A szavakat szóköz választja el egymástól, és nincs következetesség abban, hogy a város neve hat szó vagy nyolc szó után szerepel -e.
- Több ezer ilyen rekord létezik, és szükség van azoknak a rekordoknak a szűrésére, ahol vannak ismétlődő szöveges karakterláncok.
Miután megvizsgáltam számos lehetőséget (például szöveget oszlopokba és képleteket), végül úgy döntöttem, hogy a VBA -t használom ennek megvalósításához.
Létrehoztam tehát egy egyéni VBA függvényt („IdDuplicate”) ezeknek a celláknak az elemzéséhez, és IGAZ értéket adok, ha ismétlődő szó található a szöveges karakterláncban, és hamis, ha nincs ismétlés (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 fordul elő a szövegben. Ha a szám több mint 1, akkor IGAZ értéket ad vissza; egyébként hamisat ad vissza.
Ezenkívül úgy jött létre, hogy csak három karakternél hosszabb szavakat számoljon.
Miután megkaptam az IGAZ/HAMIS adatokat, könnyen szűrhetem az IGAZ rekordokat.
Most hadd mutassam meg, hogyan kell ezt Excelben csinálni.
VBA kód az egyéni funkcióhoz
Ez egyéni funkció létrehozásával érhető el a VBA -ban. Ezt a funkciót az Excel bármely más munkalapfunkciójaként lehet használni.
Itt a VBA kód hozzá:
Funkció IdDuplicates (rng mint tartomány) karakterláncként Dim StringtoAnalyze Változatként Dim i As Integer Dim j Mint Integr Const minWordLen As Integer = 4 StringtoAnalyze = Split (UCase (rng.Value), "") For i = UBound (StringtoAnalyze) to 0 -1 lépés Ha Len (StringtoAnalyze (i)) <minWordLen Akkor GoTo SkipA For j = 0 To i - 1 If StringtoAnalyze (j) = StringtoAnalyze (i) then IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = "FALSE" SkipB: Funkció befejezése
Köszönjük Walternek, hogy jobb megközelítést javasolt ennek a kódnak!
Hogyan kell használni ezt a VBA kódot?
Most, hogy megvan a VBA -kód, el kell helyeznie az Excel hátsó rendszerébe, hogy normál munkalap -funkcióként működjön.
Az alábbiakban bemutatjuk a VBA -kód háttérképre helyezésének lépéseit:
- Lépjen a Fejlesztő fülre.
- Kattintson a Visual Basic elemre (használhatja az ALT + F11 billentyűparancsot is)
- A megnyíló VB Editor hátsó részén kattintson a jobb gombbal a munkafüzet bármelyik objektumára.
- Lépjen a „Beszúrás” elemre, és kattintson a „Modul” elemre. Ez beszúrja a munkafüzet modulobjektumát.
- A Modul kód ablakban másolja ki és illessze be a fent említett VBA kódot.
Ha megvan a VBA -kód a hátsó végén, akkor használhatja az „IdDuplicates” függvényt, mint bármely más szokásos munkalap -funkciót.
Ez a függvény egyetlen argumentumot tartalmaz, amely annak a cellának a cellahivatkozása, ahol a szöveg található.
A függvény eredménye IGAZ (ha ismétlődő szavak vannak benne) vagy HAMIS (ha nincsenek ismétlődések). Ha megvan az IGAZ/HAMIS lista, szűrheti az IGAZ értékűeket, hogy megkapja az összes cellát, amelyekben ismétlődő szöveges karakterláncok találhatók.
Megjegyzés: A kódot csak azért készítettem, hogy figyelembe vegyem a három karakternél hosszabb szavakat. Ez biztosítja, hogy ha 1, 2 vagy 3 karakter hosszú szó (például 12 A, K G M vagy L D A) van a szöveges karakterláncban, akkor ezeket figyelmen kívül hagyja az ismétlődések számolása közben. Ha szeretné, ezt könnyen megváltoztathatja a kódban.
Ez a funkció csak abban a munkafüzetben lesz elérhető, ahol a kódot másolta a modulban. Ha azt szeretné, hogy ez más munkafüzetekben is elérhető legyen, akkor ezt a kódot másolja be és illessze be a munkafüzetekbe. Alternatív megoldásként létrehozhat egy bővítményt is (amely lehetővé teszi, hogy ez a funkció elérhető legyen a rendszer összes munkafüzetében).
Ezenkívül ne felejtse el menteni ezt a munkafüzetet .xlsm kiterjesztésben (mivel makrókód van benne).
