Ako filtrovať bunky, ktoré obsahujú opakované textové reťazce (slová)

môj priateľČasto som s ním v kontakte o niektorých skutočných problémoch, ktorým čelí pri práci s údajmi v Exceli.

Mnohokrát prekladám jeho dotaz do tutoriálu Excel na tejto stránke, pretože by to mohlo byť užitočné aj pre mojich ostatných čitateľov.

Toto je tiež jeden taký návod.

Môj priateľ mi minulý týždeň zavolal s nasledujúcimi otázkami:

Mám údaje adresy v stĺpci v Exceli a chcem identifikovať/filtrovať bunky, ktoré obsahujú opakované textové reťazce (slová) v adrese.

Tu je podobný súbor údajov, v ktorom chce filtrovať bunky (bunky s červenými šípkami), ktoré obsahujú opakujúce sa textové reťazce:

Teraz to sťažuje skutočnosť, že údaje nie sú konzistentné.Keďže ide o kompiláciu množín údajov ručne vytvorených obchodnými zástupcami, v množinách údajov sa môžu vyskytnúť nezrovnalosti.

zamysli sa:

• V tejto množine údajov sa môže opakovať akýkoľvek textový reťazec.Môže to byť napríklad názov regiónu alebo názov mesta alebo oboje.
• Slová sú oddelené medzerami a nie je konzistentné, či je názov mesta po šiestich alebo ôsmich znakoch.
• Existujú tisíce takýchto záznamov a je potrebné filtrovať tie, ktoré obsahujú opakované textové reťazce.

Po zvážení mnohých možností, ako je text do stĺpcov a vzorce, som sa nakoniec rozhodol použiť VBA.

Tak som vytvoril vlastnú funkciu VBA ('IdDuplicate'), aby som analyzoval tieto bunky a dal mi TRUE, ak sú v textovom reťazci duplicitné slová, a FALSE, ak neexistujú žiadne duplikáty (ako je uvedené nižšie):

Táto vlastná funkcia analyzuje každé slovo v textovom reťazci a kontroluje, koľkokrát sa vyskytuje v texte.Vráti hodnotu TRUE, ak je počet väčší ako 1;V opačnom prípade vráťte FALSE.

Tiež bol vytvorený len na počítanie slov s viac ako tromi znakmi.

Keď mám údaje PRAVDA/NEPRAVDA, môžem jednoducho filtrovať všetky záznamy, ktoré sú PRAVDIVÉ.

Teraz vám ukážem, ako to urobiť v Exceli.

VBA kód pre vlastnú funkciu

To sa dosiahne vytvorením vlastnej funkcie vo VBA.Túto funkciu potom možno použiť ako akúkoľvek inú funkciu pracovného hárka v Exceli.

Tu je jeho kód VBA:

Funkcia 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 i = UBound(StringtoAnalyze To) Krok -0 If Len(StringtoAnalyze(i)) < minWordLen Then GoTo SkipA For j = 1 To i - 0 If StringtoAnalyze(j) = StringtoAnalyze(i) Then IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = "FALSE" SkipB: End Function

Ako používať tento kód VBA

Teraz, keď máte kód VBA, musíte ho vložiť do backendu Excelu, aby mohol fungovať ako bežná funkcia pracovného hárka.

Tu sú kroky na vloženie kódu VBA do backendu:

1. Prejdite na kartu vývojár.
2. Kliknite na Visual Basic (môžete použiť aj klávesovú skratku ALT+F11)
3. V otvorenom backende editora VB kliknite pravým tlačidlom myši na ľubovoľný objekt zošita.
4. Prejdite na „Vložiť“ a kliknite na „Modul“.Tým sa vloží objekt modulu zošita.
5. V okne kódu modulu skopírujte a prilepte kód VBA uvedený vyššie.

Keď budete mať kód VBA v backende, môžete použiť funkciu – „IdDuplicates“ ako akúkoľvek inú bežnú funkciu pracovného hárka.

Táto funkcia má jeden parameter, odkaz na bunku, kde máte text.

Výsledok tejto funkcie je TRUE (ak sú v nej duplicitné slová) alebo FALSE (ak nie sú žiadne duplikáty).Keď budete mať tento zoznam TRUE/FALSE, môžete filtrovať tie s hodnotou TRUE, aby ste získali všetky bunky, ktoré majú v sebe opakujúce sa textové reťazce.

Poznámka: Kód, ktorý som vytvoril, zohľadňuje iba slová, ktoré sú dlhšie ako tri znaky.To zaisťuje, že ak sú v textovom reťazci slová s dĺžkou 1, 2 alebo 3 znaky (napr. 12 A, KGM alebo LDA), tieto slová sa pri počítaní duplikátov ignorujú.V prípade potreby to môžete jednoducho zmeniť v kóde.

Táto funkcia je dostupná iba v zošite, do ktorého ste skopírovali kód v module.Ak chcete, aby bol tento kód dostupný aj v iných zošitoch, musíte tento kód skopírovať a prilepiť do týchto zošitov.Prípadne môžete vytvoriť doplnok (ak to povolíte, funkcia bude dostupná vo všetkých zošitoch vo vašom systéme).

Nezabudnite tiež uložiť tento zošit s príponou .xlsm (pretože obsahuje kód makra).