Один з моїх друзів працює в медичній аналітичній компанії. Він часто спілкується зі мною щодо деяких реальних проблем, з якими він стикається під час роботи з даними в Excel.
Я часто перетворюю його запити в підручники Excel на цьому сайті, оскільки це може бути корисним і для інших моїх читачів.
Це також один з таких підручників.
Минулого тижня мені зателефонувала подруга з такою проблемою:
У стовпці Excel є дані про адреси, і я хочу ідентифікувати/відфільтрувати клітинки, де адреса містить повторювані текстові рядки (слова).
Ось подібний набір даних, у якому він хотів відфільтрувати клітинки, у яких є повторюваний текстовий рядок (ті, що мають червоні стрілки):
Тепер це ускладнює те, що ці дані не узгоджуються. Оскільки це компіляція набору даних, створеного вручну представниками відділу продажів, у наборі даних можуть бути різні варіанти.
Подумайте про це:
- Будь -який текстовий рядок може повторюватися в цьому наборі даних. Наприклад, це може бути назва району або назва міста, або обидва.
- Слова розділені символом пробілу, і немає узгодженості в тому, чи буде назва міста після шести слів або восьми слів.
- Таких записів є тисячі, і потрібно відфільтрувати ті записи, де є повторювані текстові рядки.
Розглянувши багато варіантів (наприклад, текст у стовпці та формули), я нарешті вирішив використати VBA, щоб це зробити.
Тому я створив власну функцію VBA ("IdDuplicate"), щоб проаналізувати ці клітинки і дати мені ІСТИНУ, якщо в текстовому рядку є повторюване слово, і ЛОЖЬ, якщо немає повторень (як показано нижче):
Ця спеціальна функція аналізує кожне слово в текстовому рядку та перевіряє, скільки разів воно зустрічається у тексті. Якщо кількість більше 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, щоб отримати всі клітинки, у яких є повторювані текстові рядки.
Примітка: Я створив код лише для розгляду тих слів, які мають довжину більше трьох символів. Це гарантує, що якщо у текстовому рядку є 1, 2 або 3 символьні слова (наприклад, 12 A, K G M або L D A), вони ігноруються під час підрахунку дублікатів. Якщо ви хочете, ви можете легко змінити це в коді.
Ця функція буде доступна лише у книзі, де ви скопіювали код у модулі. Якщо ви хочете, щоб це було доступно і в інших книгах, вам потрібно скопіювати та вставити цей код у ці книги. Крім того, ви також можете створити надбудову (включення якої зробить цю функцію доступною у всіх робочих книгах у вашій системі).
Також не забудьте зберегти цю книгу у розширенні .xlsm (оскільки в ній є код макросу).
