Един от приятелите ми работи в компания за здравни анализи. Той често се свързва с мен по някои от реалните проблеми, с които се сблъсква, докато работи с данни в Excel.
Много пъти преобразувам запитванията му в уроци по Excel на този сайт, тъй като може да бъде полезно и за другите ми читатели.
Това също е един такъв урок.
Моят приятел ми се обади миналата седмица със следния проблем:
Има данни за адрес в колона в Excel и искам да идентифицирам/филтрирам клетки, където адресът има дублирани текстови низове (думи) в него.
Ето подобния набор от данни, в който той искаше да филтрира клетки, които имат дублиран текстов низ (тези с червени стрелки):
Това, което прави това трудно, е, че няма последователност в тези данни. Тъй като това е компилация от набор от данни, създаден ръчно от търговски представители, може да има вариации в набора от данни.
Помислете за това:
- Всеки текстов низ може да се повтори в този набор от данни. Например, това може да бъде името на района или името на града или и двете.
- Думите са разделени с интервал и няма съгласуваност дали името на града ще бъде там след шест думи или осем думи.
- Има хиляди такива записи и е необходимо да се филтрират тези записи, където има дублирани текстови низове.
След като разгледах много опции (като текст към колони и формули), най -накрая реших да използвам VBA, за да направя това.
Затова създадох персонализирана VBA функция („IdDuplicate“), за да анализирам тези клетки и да ми даде TRUE, ако в текстовия низ има дублирана дума, и FALSE, в случай че няма повторения (както е показано по -долу):
Тази персонализирана функция анализира всяка дума в текстовия низ и проверява колко пъти се среща в текста. Ако броят е повече от 1, той връща TRUE; иначе връща FALSE.
Също така е създаден да брои само думи с повече от три знака.
След като разполагам с TRUE/FALSE данни, мога лесно да филтрирам всички записи, които са TRUE.
Сега нека ви покажа как да направите това в Excel.
VBA код за персонализирана функция
Това става чрез създаване на персонализирана функция във VBA. След това тази функция може да се използва като всяка друга функция на работен лист в Excel.
Ето кода на VBA за него:
Функция 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 0 Стъпка -1 Ако Len (StringtoAnalyze (i)) <minWordLen Тогава GoTo SkipA For j = 0 To i - 1 If StringtoAnalyze (j) = StringtoAnalyze (i) Тогава IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = "FALSE" SkipB: Крайна функция
Благодаря на Уолтър, че предложи по -добър подход към този код!
Как да използвате този VBA код
Сега, когато имате кода VBA, трябва да го поставите в задната част на Excel, така че да може да работи като обикновена функция на работен лист.
По -долу са описани стъпките за поставяне на VBA кода в задната част:
- Отидете в раздела Разработчик.
- Кликнете върху Visual Basic (можете да използвате и клавишната комбинация ALT + F11)
- В задната част на редактора на VB, който се отваря, щракнете с десния бутон върху някой от обектите на работната книга.
- Отидете на „Вмъкване“ и кликнете върху „Модул“. Това ще вмъкне обекта на модула за работната книга.
- В прозореца с код на модул копирайте и поставете VBA кода, споменат по -горе.
След като имате VBA кода в задната част, можете да използвате функцията - „IdDuplicates“ като всяка друга обикновена функция на работен лист.
Тази функция приема един единствен аргумент, който е препратката към клетката на клетката, където имате текста.
Резултатът от функцията е TRUE (ако в нея има дублирани думи) или FALSE (ако няма дубликати). След като имате този списък с TRUE/FALSE, можете да филтрирате тези с TRUE, за да получите всички клетки, които имат дублирани текстови низове в него.
Забележка: Създадох кода само за да разгледам тези думи, които са с повече от три знака. Това гарантира, че ако в текстовия низ има думи с дължина 1, 2 или 3 (като например 12 A, K G M или L D A), те се игнорират при преброяване на дубликатите. Ако искате, можете лесно да промените това в кода.
Тази функция ще бъде налична само в работната книга, където сте копирали кода в модула. В случай, че искате това да е достъпно и в други работни книги, трябва да копирате и поставите този код в тези работни книги. Като алтернатива можете също да създадете добавка (активиране, което би направило тази функция достъпна във всички работни книги във вашата система).
Също така не забравяйте да запишете тази работна книга в .xlsm разширение (тъй като в нея има макро код).
