един мой приятелЧесто общувам с него относно някои от реалните проблеми, с които се сблъсква, когато работи с данни в Excel.
Много пъти превеждам заявката му в урок за Excel на този сайт, защото може да бъде полезен и на другите ми читатели.
Това също е един такъв урок.
Моят приятел ми се обади миналата седмица със следните въпроси:
Имам адресни данни в колона в Excel и искам да идентифицирам/филтрирам клетки, които съдържат повтарящи се текстови низове (думи) в адреса.
Ето подобен набор от данни, където той иска да филтрира клетки (клетки с червени стрелки), които имат повтарящи се текстови низове в тях:
Това, което прави това трудно сега, е, че данните не са последователни.Тъй като това е компилация от набори от данни, създадени ръчно от търговски представители, може да има несъответствия в наборите от данни.
помисли за това:
- Всеки текстов низ може да се повтори в този набор от данни.Например, това може да бъде име на регион или име на град, или и двете.
- Думите са разделени с интервал и няма последователност в това дали имената на градовете са след шест или осем знака.
- Има хиляди такива записи и трябва да филтрирате тези, които имат повтарящи се текстови низове.
След като разгледах много опции като текст в колони и формули, най-накрая реших да използвам VBA, за да свърша работата.
Затова създадох персонализирана VBA функция ('IdDuplicate'), за да анализирам тези клетки и да ми даде TRUE, ако има дублиращи се думи в текстовия низ и FALSE, ако няма дубликати (както е показано по-долу):
Тази персонализирана функция анализира всяка дума в текстовия низ и проверява колко пъти се среща в текста.Връща TRUE, ако броят е по-голям от 1;В противен случай върнете 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(Stringto) ToA0 Стъпка -1 Ако Len(StringtoAnalyze(i)) < minWordLen Тогава отидете на SkipA За j = 0 до i - 1 Ако StringtoAnalyze(j) = StringtoAnalyze(i) Тогава IdDuplicates = "TRUE" Отидете до SkipB Край Ако Следващо j SkipA: Следващо 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, KGM или LDA), тези думи се игнорират при броене на дубликати.Можете лесно да промените това в код, ако е необходимо.
Тази функция е налична само в работната книга, където сте копирали кода в модула.Ако искате този код да е наличен и в други работни книги, трябва да копирате и поставите този код в тези работни книги.Като алтернатива можете да създадете добавка (активирането на това ще направи функцията достъпна във всички работни книги във вашата система).
Също така, не забравяйте да запазите тази работна книга с разширение .xlsm (тъй като съдържа макрокод).
