Jeden z mých přátel pracuje ve zdravotnické analytické společnosti. Často se se mnou spojuje ohledně některých skutečných problémů, se kterými se potýká při práci s daty v Excelu.
Mnohokrát převádím jeho dotazy na tutoriály k Excelu na tomto webu, protože by to mohlo být užitečné i pro mé další čtenáře.
Toto je také jeden takový tutoriál.
Můj přítel mi minulý týden zavolal s následujícím problémem:
Ve sloupci v aplikaci Excel jsou data adres a já chci identifikovat/filtrovat buňky, kde má adresa duplicitní textové řetězce (slova).
Zde je podobná datová sada, ve které chtěl filtrovat buňky, které obsahují duplicitní textový řetězec (ty s červenými šipkami):
Nyní je to obtížné, protože v těchto datech neexistuje konzistence. Protože se jedná o kompilaci datové sady, která byla ručně vytvořena obchodními zástupci, mohou v datové sadě existovat variace.
Zvaž toto:
- V této datové sadě se může opakovat libovolný textový řetězec. Může to být například název oblasti nebo název města nebo obojí.
- Slova jsou oddělena mezerou a neexistuje konzistence v tom, zda tam bude název města po šesti slovech nebo osmi slovech.
- Existují tisíce záznamů, jako je tento, a je potřeba je filtrovat tam, kde jsou duplicitní textové řetězce.
Po zvážení mnoha možností (například textu do sloupců a vzorců) jsem se nakonec rozhodl použít VBA.
Vytvořil jsem tedy vlastní funkci VBA („IdDuplicate“), která tyto buňky analyzuje a dá mi hodnotu TRUE, pokud je v textovém řetězci duplicitní slovo, a FALSE v případě, že nedochází k žádnému opakování (jak je uvedeno níže):
Tato vlastní funkce analyzuje každé slovo v textovém řetězci a kontroluje, kolikrát se v textu vyskytuje. Pokud je počet větší než 1, vrátí hodnotu PRAVDA; jinak vrátí FALSE.
Byl také vytvořen tak, aby počítal pouze slova s více než třemi znaky.
Jakmile mám data PRAVDA/NEPRAVDA, mohu snadno filtrovat všechny záznamy, které jsou PRAVDA.
Nyní vám ukážu, jak to udělat v aplikaci Excel.
Kód VBA pro vlastní funkci
To se provádí vytvořením vlastní funkce ve VBA. Tuto funkci pak lze použít jako jakoukoli jinou funkci listu v aplikaci Excel.
Zde je kód VBA:
Funkce IdDuplikáty (rng jako rozsah) jako řetězec Dim StringtoAnalyze jako varianta Dim i jako celé číslo Dim j jako celé číslo Const minWordLen jako celé číslo = 4 StringtoAnalyze = Split (UCase (rng.Value), "") For i = UBound (StringtoAnalyze) To 0 Krok -1 Pokud 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: Koncová funkce
Děkujeme Walterovi za návrh lepšího přístupu k tomuto kódu!
Jak používat tento kód VBA
Nyní, když máte kód VBA, musíte jej umístit do backendu aplikace Excel, aby mohl fungovat jako běžná funkce listu.
Níže jsou uvedeny kroky pro vložení kódu VBA na backend:
- Přejděte na kartu Vývojář.
- Klikněte na Visual Basic (můžete také použít klávesovou zkratku ALT + F11)
- V back-endu editoru VB, který se otevře, klikněte pravým tlačítkem na jakýkoli objekt sešitu.
- Přejděte na „Vložit“ a klikněte na „Modul“. Tím se vloží objekt modulu pro sešit.
- V okně Kód modulu zkopírujte a vložte výše uvedený kód VBA.
Jakmile budete mít kód VBA na zadním konci, můžete použít funkci - „IdDuplicates“ jako jakoukoli jinou běžnou funkci listu.
Tato funkce přebírá jeden jediný argument, kterým je odkaz na buňku buňky, kde máte text.
Výsledkem funkce je PRAVDA (pokud v ní jsou duplicitní slova) nebo NEPRAVDA (pokud neexistují duplikáty). Jakmile budete mít tento seznam PRAVDA/NEPRAVDA, můžete filtrovat ty s PRAVDU, abyste získali všechny buňky, ve kterých jsou duplicitní textové řetězce.
Poznámka: Kód jsem vytvořil pouze pro zvážení těch slov, která jsou delší než tři znaky. Tím je zajištěno, že pokud jsou v textovém řetězci 1, 2 nebo 3 znaková slova (například 12 A, K G M nebo L D A), jsou při počítání duplikátů ignorována. Pokud chcete, můžete to snadno změnit v kódu.
Tato funkce bude k dispozici pouze v sešitu, kam jste zkopírovali kód v modulu. V případě, že chcete, aby to bylo k dispozici i v jiných sešitech, musíte tento kód zkopírovat a vložit do těchto sešitů. Alternativně můžete také vytvořit doplněk (povolením, které by tuto funkci zpřístupnilo ve všech sešitech ve vašem systému).
Nezapomeňte také uložit tento sešit v příponě .xlsm (protože obsahuje kód makra).
