Как извлечь все частичные совпадения в Excel?
При работе с большими наборами данных в Excel нередко возникает задача извлечения строк, ячеек или значений, содержащих частичные совпадения с определённым ключевым словом или фразой. Это особенно актуально при очистке, фильтрации и анализе данных. Хотя Excel не предлагает встроенного инструмента для прямого извлечения таких совпадений, добиться нужного результата можно несколькими эффективными способами. В этой статье мы подробно покажем, как быстро и точно находить все частичные совпадения в Excel.
- Извлечение всех частичных совпадений вертикально с помощью формулы
- Извлечение всех частичных совпадений в одну ячейку с помощью пользовательской функции
- Извлечение всех частичных совпадений вертикально или в одну ячейку с помощью KUTOOLS AI Aide
Извлечение всех частичных совпадений вертикально с помощью формулы
Чтобы извлечь все совпадения вертикально в столбце на основе частичного текста, воспользуйтесь следующей формулой массива:
Шаг 1: Скопируйте и вставьте приведённую ниже формулу в пустую ячейку, куда вы хотите поместить результат:
=INDEX($A$2:$A$14,SMALL(IF(ISNUMBER((SEARCH($C$2,$A$2:$A$14))),MATCH(ROW($A$2:$A$14),ROW($A$2:$A$14)), ""),ROWS($E$1:E1)),COLUMNS($E$1:E1))Примечание: В приведённой выше формуле диапазон A2:A14 — это ячейки, из которых нужно вернуть совпадения, C2 содержит ключевое слово, а E1 — ячейка над формулой.
Шаг 2: Одновременно нажмите клавиши Ctrl + Shift + Enter, чтобы получить первый результат, затем перетащите маркер заполнения вниз до появления ошибки и удалите ячейки с ошибками (см. скриншот):
Извлечение всех частичных совпадений в одну ячейку с помощью пользовательской функции
Если вам нужно извлечь все совпадения в одну ячейку с разделителем, стандартная формула не справится с этой задачей. В таком случае воспользуйтесь приведённой ниже пользовательской функцией (User Defined Function):
Шаг 1: Нажмите клавиши «Alt + F11», чтобы открыть окно Microsoft Visual Basic for Applications.
Шаг 2: В окне «Microsoft Visual Basic for Applications» выберите «Вставка» > «Модуль», а затем скопируйте приведённый ниже код в модуль.
Код VBA: Извлечение всех частичных совпадений в одну ячейку
Function ExtractPartMatch(rngInput As Range, rngSource As Range, Optional sDelimiter As String)
'Updateby ExtendOffice
Dim rng As Range
If sDelimiter = "" Then sDelimiter = ", "
For Each rng In rngSource
If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ExtractPartMatch = ExtractPartMatch & sDelimiter & rng.Value
Next
If Len(ExtractPartMatch) > 0 Then ExtractPartMatch = Mid(ExtractPartMatch, 2, Len(ExtractPartMatch))
End Function
Шаг 3: После вставки кода вернитесь на лист, где должен появиться результат, и введите следующую формулу:=ExtractPartMatch(C2,$A$2:$A$14), затем нажмите клавишу Enter, чтобы получить результат, как показано на скриншоте ниже:
Извлечение всех частичных совпадений вертикально или в одну ячейку с помощью KUTOOLS AI Aide
С помощью KUTOOLS AI Aide вы можете упростить этот процесс и извлекать все частичные совпадения всего за несколько кликов. Этот интеллектуальный инструмент помогает находить и извлекать строки или ячейки, содержащие конкретные ключевые слова или фразы, экономя ваше время и обеспечивая точность.
После установки Kutools для Excel перейдите в меню «Kutools» > «AI Ассистент», чтобы открыть панель «KUTOOLS AI Aide».
- В области «KUTOOLS AI Aide» введите запрос в поле чата, например:
Извлечь все частичные совпадения вертикально:
Извлечь ячейки, содержащие «Wood», из столбца A и поместить их в столбец E, начиная с ячейки E2
Извлечь все частичные совпадения в одну ячейку:
Извлечь ячейки, содержащие «Wood», из столбца A и поместить их в одну ячейку E2 - Затем нажмите клавишу Enter или кнопку «Отправить». После завершения анализа запроса KUTOOLS AI нажмите кнопку «Выполнить», чтобы получить результат.
Таким образом, извлекать частичные совпадения в Excel можно разными способами — всё зависит от ваших задач. Выберите метод, который лучше всего подходит вашему рабочему процессу, и оптимизируйте свою работу в Excel! Хотите освоить ещё больше полезных советов и приёмов?На нашем сайте доступны тысячи обучающих материалов.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек