Jeden z mojich priateľov pracuje v zdravotníckej analytickej spoločnosti. Často sa so mnou spája v niektorých skutočných problémoch, s ktorými sa stretáva pri práci s údajmi v programe Excel.
Jeho dotazy veľakrát prevádzam na tomto webe do tutoriálov pre program Excel, pretože by to mohlo byť užitočné aj pre ostatných čitateľov.
Toto je tiež jeden z týchto návodov.
Môj priateľ mi minulý týždeň zavolal s nasledujúcim problémom:
V stĺpci v programe Excel sú údaje o adrese a ja chcem identifikovať/filtrovať bunky tam, kde má adresa duplicitné textové reťazce (slová).
Tu je podobný súbor údajov, v ktorom chcel filtrovať bunky, v ktorých je duplicitný textový reťazec (tie s červenými šípkami):
Teraz je to ťažké, pretože tieto údaje nie sú konzistentné. Pretože sa jedná o kompiláciu súboru údajov, ktorý bol ručne vytvorený obchodnými zástupcami, v množine údajov môžu existovať odchýlky.
Zváž toto:
- V tejto množine údajov sa môže opakovať ľubovoľný textový reťazec. Môže to byť napríklad názov oblasti alebo názov mesta alebo oboje.
- Slová sú oddelené medzerou a neexistuje zhoda v tom, či by tam názov mesta bol po šiestich slovách alebo po ôsmich slovách.
- Existujú tisíce záznamov, ako je tento, a je potrebné filtrovať tie záznamy, kde sú duplicitné textové reťazce.
Po zvážení mnohých možností (napríklad textu do stĺpcov a vzorcov) som sa nakoniec rozhodol použiť VBA.
Preto som vytvoril vlastnú funkciu VBA („IdDuplicate“), ktorá tieto bunky analyzuje a dá mi hodnotu TRUE, ak je v textovom reťazci duplicitné slovo, a FALSE v prípade, že nedochádza k žiadnym opakovaniam (ako je uvedené nižšie):
Táto vlastná funkcia analyzuje každé slovo v textovom reťazci a kontroluje, koľkokrát sa v texte vyskytuje. Ak je počet vyšší ako 1, vráti hodnotu PRAVDA; inak vráti FALSE.
Tiež bol vytvorený tak, aby počítal iba slová viac ako tri znaky.
Keď mám údaje PRAVDIVÉ/NEPRAVDIVÉ, môžem jednoducho filtrovať všetky záznamy, ktoré sú PRAVDIVÉ.
Teraz vám ukážem, ako to urobiť v programe Excel.
Kód VBA 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 programe Excel.
Tu je kód VBA:
Funkcia IdDuplikáty (rng ako rozsah) Ako reťazec Dim StringtoAnalyze Ako variant Dim Dim ako Integer Dim j ako Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split (UCase (rng.Value), "") For i = UBound (StringtoAnalyze) To 0 Krok -1 Ak Len (StringtoAnalyze (i)) <minWordLen Then 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: Koniec funkcie
Ďakujeme Walterovi za návrh lepšieho prístupu k tomuto kódu!
Ako používať tento kód VBA
Teraz, keď máte kód VBA, musíte ho umiestniť do backendu programu Excel, aby mohol fungovať ako bežná funkcia pracovného hárka.
Nasledujú kroky na vloženie kódu VBA na server:
- Prejdite na kartu Vývojár.
- Kliknite na položku Visual Basic (môžete tiež použiť klávesovú skratku ALT + F11)
- Na zadnom konci editora VB, ktorý sa otvorí, kliknite pravým tlačidlom myši na ľubovoľný objekt zošita.
- Prejdite na „Vložiť“ a kliknite na „Modul“. Tým sa vloží objekt modulu do zošita.
- V okne Kód modulu skopírujte a prilepte vyššie uvedený kód VBA.
Akonáhle budete mať kód VBA na zadnom konci, môžete použiť funkciu - „IdDuplicates“ ako akúkoľvek inú bežnú funkciu pracovného hárka.
Táto funkcia používa jeden jediný argument, ktorým je odkaz na bunku bunky, v ktorej máte text.
Výsledkom funkcie je TRUE (ak sú v nej duplicitné slová) alebo FALSE (ak neexistujú žiadne duplikáty). Akonáhle budete mať tento zoznam PRAVDIVÝ/NEPRAVDIVÝ, môžete filtrovať tie s PRAVDU, aby ste získali všetky bunky, v ktorých sú duplicitné textové reťazce.
Poznámka: Kód som vytvoril iba na zváženie tých slov, ktoré majú viac ako tri znaky. To zaisťuje, že ak sú v textovom reťazci 1, 2 alebo 3 slová (napríklad 12 A, K G M alebo L D A), budú tieto slová pri počítaní duplikátov ignorované. Ak chcete, môžete to v kóde ľahko zmeniť.
Táto funkcia bude k dispozícii iba v zošite, kam ste skopírovali kód v module. V prípade, že chcete, aby to bolo k dispozícii aj v iných zošitoch, musíte tento kód skopírovať a vložiť do týchto zošitov. Alternatívne môžete tiež vytvoriť doplnok (povolením, ktoré by sprístupnilo túto funkciu vo všetkých zošitoch vo vašom systéme).
Nezabudnite tiež uložiť tento zošit v prípone .xlsm (pretože obsahuje kód makra).
