Динамические массивыПродвинутый8 мин чтенияОбновлено 18 сентября 2026 г.

ФИЛЬТР по части текста

Практический разбор: сделать поисковую выдачу по фрагменту названия. Таблица под задачу, контрольный результат, ошибки конкретной формулы, самопроверка и самостоятельное задание.

Русская версия Excel=ФИЛЬТР(A2:D100;ЕЧИСЛО(ПОИСК(H2;A2:A100));"Нет совпадений")

Английская версия Excel=FILTER(A2:D100,ISNUMBER(SEARCH(H2,A2:A100)),"No matches")

В русской локализации Excel аргументы чаще разделяются точкой с запятой, а в английской — запятой. При копировании формул учитывайте региональные настройки.

Короткий ответ

В этой статье разберем, как в Excel сделать поисковую выдачу по фрагменту названия. Сначала — готовая формула, затем подробная логика, рабочий пример, проверка результата, типовые ошибки и варианты применения в реальной таблице.

Зачем использовать этот прием

Практическая задача этой статьи — сделать поисковую выдачу по фрагменту названия. Именно поэтому пример ниже использует поля «Товар» и «Категория», а не универсальную таблицу с абстрактными «Значение 1 / Значение 2».

Контрольный результат для примера: При поиске `HP` выборка возвращает «Тонер HP 85A» и «Принтер HP Laser 107». Сначала получите его вручную по данным таблицы, затем используйте формулу. Так вы проверяете не только синтаксис Excel, но и саму бизнес-логику расчёта.

Пример исходных данных

Товар	Категория	Цена, ₽	Остаток
Тонер HP 85A	Расходники	3150	12
Картридж Canon 725	Расходники	2890	8
Принтер HP Laser 107	Техника	16400	3
Бумага A4	Канцтовары	420	55
Мышь Logitech	Техника	1900	14

Контроль: При поиске HP выборка возвращает «Тонер HP 85A» и «Принтер HP Laser 107».

Таблица для «ФИЛЬТР по части текста» подобрана так, чтобы результат можно было проверить вручную. При переносе в свой файл сохраняйте смысл ключевых полей, даже если рабочие названия столбцов отличаются.

Подготовка данных

Свободную зону разлива.
Одинаковые размеры массивов условий.
Ожидаемое поведение при пустом результате.
Отдельно проверьте контрольную строку: При поиске HP выборка возвращает «Тонер HP 85A» и «Принтер HP Laser 107».
ПОИСК работает по подстроке и не учитывает регистр; это может давать как полезные, так и ложные совпадения.

Разбираем формулу пошагово

1. Сформулируйте задачу словами

Запишите правило без Excel:

> «Для каждой строки нужно сделать поисковую выдачу по фрагменту названия.»

Для «ФИЛЬТР по части текста» сначала сформулируйте правило однозначно и получите ожидаемый ответ словами. Формулу стоит вводить только после того, как бизнес-логика не допускает двух разных трактовок.

2. Определите входные данные

Отметьте:

откуда берется исходное значение;
какой диапазон участвует;
какие критерии обязательны;
какой результат ожидается;
что делать при пустых данных;
что делать при ошибке.

3. Введите формулу

Русская версия:

=ФИЛЬТР(A2:D100;ЕЧИСЛО(ПОИСК(H2;A2:A100));"Нет совпадений")

Английская версия:

=FILTER(A2:D100,ISNUMBER(SEARCH(H2,A2:A100)),"No matches")

Формулу для «ФИЛЬТР по части текста» сначала проверьте на одной понятной строке; только после совпадения с ручным ответом переносите её на весь диапазон.

4. Проверьте вручную

Для контрольной строки «ФИЛЬТР по части текста» получите ожидаемый результат без Excel: поисковый ответ найдите в таблице глазами, а расчётный пересчитайте отдельно. Затем сравните ручной ответ с формулой.

5. Проверьте ссылки

В Excel:

A2 — относительная ссылка;
$A$2 — полностью абсолютная;
$A2 — фиксирован столбец;
A$2 — фиксирована строка.

В «ФИЛЬТР по части текста» неверно закреплённая ссылка может не дать ошибки Excel, но сместить диапазон после копирования. Поэтому сравните адреса ссылок в первой и последней формуле.

---

Ключевой нюанс именно этой задачи

Пустая строка поиска может совпасть с большим числом значений, поэтому при необходимости добавьте отдельную проверку H2.

Результату нужно свободное пространство для разлива. Любая занятая ячейка внутри будущего массива может вызвать #РАЗЛИВ! / #SPILL!.

---

Как читать формулу, а не заучивать ее

Для «ФИЛЬТР по части текста» читайте формулу через задачу сделать поисковую выдачу по фрагменту названия, а не по названиям функций. Сначала найдите в выражении входное значение или ключ, затем диапазон/порог, после этого — возвращаемый или вычисляемый результат. Контрольная точка для текущего примера: При поиске `HP` выборка возвращает «Тонер HP 85A» и «Принтер HP Laser 107».

ПОИСК работает по подстроке и не учитывает регистр; это может давать как полезные, так и ложные совпадения. Ключевой смысл из исходного материала: Пустая строка поиска может совпасть с большим числом значений, поэтому при необходимости добавьте отдельную проверку H2.

Рабочий сценарий №1: ежедневная выгрузка

Используйте приём в процессе, где действительно нужно сделать поисковую выдачу по фрагменту названия. Не начинайте с массового копирования формулы: возьмите одну понятную строку из примера и подтвердите результат: При поиске `HP` выборка возвращает «Тонер HP 85A» и «Принтер HP Laser 107». После этого перенесите логику на 5–10 реальных строк с разными исходными значениями.

Особенность именно этой темы: Пустая строка поиска может совпасть с большим числом значений, поэтому при необходимости добавьте отдельную проверку H2.

Рабочий сценарий №2: контроль качества

Сделайте рядом с основным результатом контроль качества. Для статьи «ФИЛЬТР по части текста» минимум один контроль должен проверять следующую вещь: #РАЗЛИВ! из-за занятой ячейки. Второй контроль должен показывать строки, где исходные данные не позволяют уверенно применить правило.

Проверочный сценарий: Проверьте другой регистр, отсутствие слова и слово как часть более длинного слова.

Рабочий сценарий №3: подготовка к сводной таблице

После расчёта не прячьте результат внутри исходного листа. Выведите его в отчёт в том разрезе, в котором принимается решение. Для задачи «сделать поисковую выдачу по фрагменту названия» рядом полезно показывать исходные значения, из которых получился результат, а также число строк/объектов в базе расчёта.

Сводная лучше для агрегатов и срезов, ФИЛЬТР/УНИК/СОРТ — когда нужен сам динамический список строк или справочник.

Частые ошибки

Ошибка 1. #разлив! из-за занятой ячейки

В этой теме это особенно опасно, потому что результат может выглядеть правдоподобно. Проверьте другой регистр, отсутствие слова и слово как часть более длинного слова.

Ошибка 2. Массив условий другой длины

Сверьте результат с контрольной точкой: При поиске HP выборка возвращает «Тонер HP 85A» и «Принтер HP Laser 107».

Ошибка 3. Пустой критерий неожиданно возвращает весь набор

Если структура данных изменилась, заново сопоставьте аргументы формулы со столбцами, а не просто протягивайте старое выражение.

Ошибка 4. Игнорируется нюанс конкретной функции

Профессиональная проверка формулы

Для «ФИЛЬТР по части текста» одной обычной строки недостаточно. Прогоните отдельные тесты на корректный пример, изменение входа, качество данных и пограничное значение:

Контрольный пример: При поиске HP выборка возвращает «Тонер HP 85A» и «Принтер HP Laser 107».
Изменение входа: Проверьте другой регистр, отсутствие слова и слово как часть более длинного слова.
Качество данных: специально создайте проблему «#РАЗЛИВ! из-за занятой ячейки» и убедитесь, что вы её замечаете.
Граница правила: проверьте значение непосредственно на пороге/границе, если она есть в формуле.
Массовое копирование: сравните первую, вторую и последнюю формулу после протягивания.
Независимый итог: пересчитайте хотя бы одну строку вручную или альтернативным способом.

Чек-лист аудита

Самопроверка0 из 6 пунктов

Я могу объяснить задачу «сделать поисковую выдачу по фрагменту названия» без чтения формулы.
Я получил(а) контрольный результат вручную: При поиске `HP` выборка возвращает «Тонер HP 85A» и «Принтер HP Laser 107».
Я проверил(а) риск: #РАЗЛИВ! из-за занятой ячейки.
Я протестировал(а): Проверьте другой регистр, отсутствие слова и слово как часть более длинного слова.
Я могу объяснить ключевой нюанс: Пустая строка поиска может совпасть с большим числом значений, поэтому при необходимости добавьте отдельную проверку H2.
Я понимаю, когда эту задачу лучше перенести в Power Query или сводную.

Советы для больших таблиц

Для статьи «ФИЛЬТР по части текста» при росте файла особенно важно сохранить проверяемость задачи «сделать поисковую выдачу по фрагменту названия». Динамические массивы удобны для интерактивной выдачи, но не размещайте рядом ручные данные в зоне возможного разлива. На очень больших источниках контролируйте объём возвращаемых строк. ПОИСК работает по подстроке и не учитывает регистр; это может давать как полезные, так и ложные совпадения. Контроль качества не должен исчезать после оптимизации: оставьте хотя бы одну независимую строку, где ожидается результат «При поиске HP выборка возвращает «Тонер HP 85A» и «Принтер HP Laser 107».».

Когда лучше использовать Power Query

Для задачи «сделать поисковую выдачу по фрагменту названия» Power Query становится предпочтительнее, когда обработка повторяется с каждой новой выгрузкой. Power Query лучше, если выборку нужно материализовать как отдельную таблицу при обновлении, а не пересчитывать интерактивно по пользовательскому критерию. В случае «ФИЛЬТР по части текста» формулу имеет смысл оставить на листе, если пользователь должен менять параметры и сразу видеть пересчёт.

Когда лучше сводная таблица

В контексте «ФИЛЬТР по части текста» сводная нужна не вместо формулы, а после подготовки корректного поля/показателя. Сводная лучше для агрегатов и срезов, ФИЛЬТР/УНИК/СОРТ — когда нужен сам динамический список строк или справочник. Для проверки всегда возвращайтесь к исходным строкам, на которых подтверждается: При поиске HP выборка возвращает «Тонер HP 85A» и «Принтер HP Laser 107».

Практическое задание

Создайте таблицу минимум из 20 строк и примените:

=ФИЛЬТР(A2:D100;ЕЧИСЛО(ПОИСК(H2;A2:A100));"Нет совпадений")

После этого специально добавьте:

пустое значение в тесте «ФИЛЬТР по части текста»;
дубликат ключа или строки;
нулевое значение там, где оно допустимо;
граничный сценарий правила;
строку с неправильным типом данных.

Запишите, как меняется результат и почему.

---

Задание повышенной сложности

Возьмите практическое задание именно по теме «ФИЛЬТР по части текста» и добавьте исключение «#РАЗЛИВ! из-за занятой ячейки». Сделайте так, чтобы пользователь либо получил корректный результат, либо увидел понятный контроль качества, а не правдоподобное неверное число.

Затем выполните тест: Проверьте другой регистр, отсутствие слова и слово как часть более длинного слова. Финальная проверка должна по-прежнему объяснять контрольный результат: При поиске HP выборка возвращает «Тонер HP 85A» и «Принтер HP Laser 107».

Что запомнить

Что решаем: сделать поисковую выдачу по фрагменту названия.
Контрольный результат: При поиске HP выборка возвращает «Тонер HP 85A» и «Принтер HP Laser 107».
Ключевой нюанс: Пустая строка поиска может совпасть с большим числом значений, поэтому при необходимости добавьте отдельную проверку H2.
Главный риск: #РАЗЛИВ! из-за занятой ячейки.
Практический принцип: ПОИСК работает по подстроке и не учитывает регистр; это может давать как полезные, так и ложные совпадения.
Следующий шаг: примените формулу на собственных данных и сохраните независимую контрольную строку.

Что изучить дальше

После темы «ФИЛЬТР по части текста» сравните два способа решить близкую задачу: формулой на листе и преобразованием данных. Для сценария «сделать поисковую выдачу по фрагменту названия» ориентир по Power Query такой: Power Query лучше, если выборку нужно материализовать как отдельную таблицу при обновлении, а не пересчитывать интерактивно по пользовательскому критерию. Если же нужен групповой отчёт по периодам/сегментам, используйте следующий ориентир: Сводная лучше для агрегатов и срезов, ФИЛЬТР/УНИК/СОРТ — когда нужен сам динамический список строк или справочник.

Не знаете, что учить дальше?

Проверьте свой уровень Excel за 7–10 минут

18 рабочих ситуаций покажут сильные стороны и темы, которые дадут вам самый заметный прирост.

Пройти бесплатно

Практика по системе

Закрепите формулы на рабочих задачах

В курсах Tablicus теория связана с практикумами, таблицами и автоматической проверкой результата.

Посмотреть курсы