Как отфильтровать данные, разделённые запятыми, в Excel?
Во многих листах Excel вы можете столкнуться с ситуациями, когда содержимое ячейки состоит из нескольких элементов, разделённых запятыми. Например, одна ячейка может содержать «Tom, Jerry, Sam» — это список имён. При использовании утилиты Автофильтр в Excel можно фильтровать только по полному значению ячейки, а не по отдельным элементам внутри неё, как показано на снимке экрана ниже. Это ограничение создаёт трудности, если вам нужно найти или работать со строками, содержащими определённое значение из списка, разделённого запятыми. В этой статье вы узнаете несколько практических методов фильтрации и извлечения строк на основе одного значения из таких ячеек, а также получите рекомендации по устранению неполадок и советы по выбору наиболее подходящего метода для ваших задач.
Фильтрация данных, разделённых запятыми, в Excel с помощью Установить условия фильтрации
Фильтрация данных, разделённых запятыми, в Excel с помощью Kutools для Excel
Фильтрация данных, разделённых запятыми, с помощью формулы Excel (вспомогательный столбец)
Фильтрация данных, разделённых запятыми, с помощью макроса VBA
Фильтрация данных, разделённых запятыми, в Excel с помощью Установить условия фильтрации
Функция Автофильтр с опцией Установить условия фильтрации позволяет быстро отображать только те строки, в которых ячейки со списками, разделёнными запятыми, содержат конкретное значение. Например, если вы хотите отфильтровать строки, где столбец B содержит имя «Tom», выполните следующие действия:
1. Выберите столбец с данными, разделёнными запятыми, который нужно отфильтровать.
2. Перейдите на вкладку Данные и нажмите Фильтр. Рядом с заголовком выбранного столбца появится кнопка со стрелкой — это активирует фильтр для ваших данных:
3. Нажмите кнопку со стрелкой, затем выберите Текстовые фильтры — откроется список опций. Выберите Установить условия фильтрации, чтобы открыть диалоговое окно «Условия фильтрации»:
4. В диалоговом окне Пользовательский автофильтр выберите в раскрывающемся меню пункт содержит. Введите нужный поисковый запрос (например, «Tom»). Эта настройка выполняет поиск указанного значения в любом месте ячейки:
5. Нажмите ОК. Все строки, содержащие «Tom» в данных, разделённых запятыми, будут немедленно отображены:
Этот подход отлично подходит для простых поисков. Однако учтите: фильтр ищет вхождение «Tom» во всём содержимом ячейки, поэтому может случайно находить значения вроде «Tommy» или «Tomson». Всегда убедитесь, что ваш запрос однозначно идентифицирует нужные записи. Если требуется точное совпадение с чёткими границами (например, только «Tom»), могут понадобиться дополнительные шаги или специализированные инструменты.
Фильтрация данных, разделённых запятыми, в Excel с помощью Kutools для Excel
Функция Супер фильтр в Kutools для Excel предлагает мощное решение для фильтрации сложных данных, включая ячейки со списками, разделёнными запятыми. Она поддерживает множественные критерии, чувствительность к регистру, фильтрацию по длине текста и многое другое, что делает её идеальной для продвинутых сценариев фильтрации.
После установки Kutools для Excelвыполните следующие шаги для применения фильтра:
1. Нажмите KUTOOLS PLUS → Супер фильтр на ленте. На правой стороне окна Excel откроется панель «Супер фильтр»:
2. В панели «Супер фильтр» нажмите кнопку 
, чтобы выбрать диапазон данных для фильтрации. Убедитесь, что он включает все столбцы и строки, которые вы хотите проанализировать. Нажмите горизонтальную линию, чтобы развернуть раздел с критериями и задать подробные условия:
3. В поле критериев выберите заголовок — укажите столбец, по которому будет выполняться фильтрация. Затем во втором списке выберите Текст, а в третьем — Содержит. После этого введите искомое значение (например, «Tom») в последнее поле:
4. Затем нажмите кнопку Фильтр. Kutools немедленно отфильтрует строки, отображая только те ячейки, значения которых, разделённые запятыми, соответствуют вашему условию:
Функция Супер фильтр от Kutools предлагает удобный интерфейс для сложной фильтрации. Она идеально подходит, если вы регулярно работаете с большими и сложными наборами данных или вам нужно применять несколько критериев одновременно. Совет: если требуется более продвинутое сопоставление по шаблону (например, фильтрация только точных значений с игнорированием частичных совпадений), используйте параметры Дополнительное условие.
Скачайте Kutools для Excel бесплатно прямо сейчас!
Фильтрация данных, разделённых запятыми, с помощью формулы Excel (вспомогательный столбец)
Использование вспомогательного столбца с формулами Excel — практичный способ помечать строки, содержащие заданное значение в данных, разделённых запятыми. Этот метод идеально подходит, если вы стремитесь точно контролировать критерии сопоставления и эффективно применять последующие фильтры Excel.
Подходит для пользователей, которые:
- Предпочитаете точное или частичное совпадение при использовании формул?
- Необходимо настроить критерии для работы со сложными наборами данных.
- Работаете с большими таблицами и хотите быстро фильтровать данные с помощью встроенных инструментов Excel?
Недостатки:
- Требует дополнительных столбцов и базовых навыков работы с формулами.
- Может не справляться с некоторыми вариантами форматирования (например, пробелами до или после запятых) без корректировки формулы.
1. Вставьте пустой столбец рядом с вашими данными (например, вставьте столбец C справа от исходных данных в столбце B).
2. В ячейку C2 (предполагая, что ваши данные начинаются с B2) введите следующую формулу, чтобы проверить, встречается ли «Tom» как отдельное значение в списке, разделённом запятыми:
=ISNUMBER(SEARCH(",Tom,",","&,B2&,","))Эта формула добавляет запятые до и после содержимого ячейки, чтобы обеспечить точное совпадение «Tom» как отдельного слова, а не его частичное вхождение в другое слово (например, «Tommy»).
3. Нажмите Enter, чтобы подтвердить, затем перетащите маркер заполнения вниз и скопируйте формулу в другие строки.
4. Теперь примените Автофильтр Excel к вспомогательному столбцу и отфильтруйте значения «ИСТИНА», чтобы отобразить только те строки, где «Tom» встречается как отдельный элемент в списке, разделённом запятыми.
Советы и рекомендации:
- Если в ваших ячейках используется другой разделитель (например, точка с запятой), замените запятую (,) в формуле на ваш собственный разделитель.
- Всегда проверяйте наличие лишних пробелов; при необходимости используйте функцию
ПСТРв своей формуле. - Это решение полностью динамично при будущих правках: обновлённые значения ячеек автоматически обновят результаты фильтрации после пересчёта.
Фильтрация данных, разделённых запятыми, с помощью макроса VBA
Для автоматизированной пакетной обработки можно использовать макрос VBA, который сканирует лист и фильтрует строки, содержащие заданное значение в данных, разделённых запятыми. Такой подход особенно эффективен при работе с большими объёмами данных, выполнении частой повторяющейся фильтрации или необходимости интеграции фильтрации в пользовательский рабочий процесс.
Подходит для пользователей, которые:
- Требуется автоматизировать фильтрацию множества строк или нескольких листов.
- Предпочитаете решения, которые можно использовать в скриптах и легко повторять многократно.
- Уверенно владеете редактором VBA.
Недостатки:
- Требует включения макросов и базовых знаний VBA.
- Может потребовать адаптации под другие разделители или критерии.
1. Откройте Excel, затем нажмите Инструменты разработчика > Visual Basic. В окне Microsoft Visual Basic для приложений нажмите Вставка > Модуль. Вставьте следующий код в окно модуля:
Sub FilterCommaSeparatedData()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim criteria As String
Dim outputRow As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
criteria = Application.InputBox("Enter value to filter (exact match):", xTitleId, "Tom", Type:=2)
Set rng = Application.InputBox("Select column with comma separated data:", xTitleId, Selection.Address, Type:=8)
outputRow = 1
ws.Rows.Hidden = False
For Each cell In rng
If InStr(1, "," & cell.Value & ",", "," & criteria & ",", vbTextCompare) > 0 Then
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If
Next cell
End Sub2. После ввода кода нажмите кнопку 
«Выполнить». Появится диалоговое окно, в котором нужно указать искомое значение и выбрать целевой столбец. После этого Excel скроет строки, не содержащие указанного значения в выбранном столбце.
Если макрос работает не так, как ожидалось, убедитесь, что значения ячеек отформатированы единообразно и не содержат неожиданных символов. Чтобы отобразить все строки после фильтрации, просто выделите их, щелкните правой кнопкой мыши и выберите «Показать». Если нужно адаптировать код под другие разделители, замените запятую в макросе на требуемый символ.
Эти методы обеспечивают гибкость при работе со списками с разделителями-запятыми в Excel — независимо от того, фильтруете ли вы данные вручную, используете формулы, автоматизируете задачи через VBA или применяете Kutools для Excel. При выборе подхода учитывайте объём данных, частоту использования и сложность требований к сопоставлению. Если результаты кажутся неожиданными, внимательно проверьте наличие скрытых пробелов, разрывов строк или нестандартных символов, способных повлиять на точность сопоставления, и при необходимости скорректируйте формулу или макрос.
См. также:
Как фильтровать данные по нескольким цветам в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек