Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск
Найдем текстовые значения, удовлетворяющие заданному пользователем критерию. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для их вывода в отдельный диапазон удобно использовать формулы массива.
Пусть Исходный список значений (например, перечень инструментов) находится в диапазоне A10:A19 (см. Файл примера).
Задача
Выведем в отдельный диапазон значения, которые удовлетворяют критерию. Рассмотрим различные варианты поиска.
А. Найти значения, которые содержат критерий
Для удобства создадим именованный диапазон Список.
Диапазон может охватить в том числе и незаполненные ячейки перечня. В дальнейшем пользователь может расширить перечень инструментов, указанные ниже формулы автоматически учтут новые значения.
Выведем в отдельный диапазон все значения Исходного списка, в которых содержится текст-критерий (например, слово дрель). Критерий вводится в ячейку С6.
Для создания списка, содержащего найденные значения, воспользуемся формулой массива:
=ИНДЕКС(Список; НАИМЕНЬШИЙ(
ЕСЛИ(ЕСЛИОШИБКА(ПОИСК($C$6;Список);0)>0;СТРОКА(Список)-СТРОКА($A$9);30);
СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(Список)))))
Алгоритм работы формулы следующий (для просмотра промежуточных шагов работы формулы воспользуйтесь клавишей F9):
- Функция ПОИСК(), перебирая элементы исходного списка, определяет, содержится ли в нем значение-критерий. Если значение не содержится, то возвращается ошибка #ЗНАЧ! В противном случае возвращается числовое значение, соответствующее номеру начальной позиции вхождения критерия в значение из списка (здесь нам не важен номер позиции, важно, что это число);
- Функция ЕСЛИОШИБКА() используется для подавления ошибки #ЗНАЧ! заменяя ее на число 0;
- Функция ЕСЛИ() заменяет числовые значения, возвращенные функцией ПОИСК(), на номер позиции значения в списке. Если значение =0 (соответствует ошибке #ЗНАЧ!), то возвращается число 30. В принципе, вместо 30 можно указать любое число, которое больше номера последней заполненной позиции Исходного списка (это нужно для правильной сортировки функцией НАИМЕНЬШИЙ());
- Функция НАИМЕНЬШИЙ() сортирует массив номеров строк по возрастанию;
- Функция ДВССЫЛ() возвращает массив последовательных чисел;
- Функция ИНДЕКС() возвращает текстовые значения из Исходного списка, из строк, номера которых были получены на предыдущем шаге.
В предельном случае м.б. найдено столько же значений, сколько содержится в исходном списке (когда все значения удовлетворяют критерию). Поэтому формулу массива нужно распространять на диапазон той же размерности, что и исходный список. Вышеуказанная формула массива будет возвращать несколько значений, поэтому перед вводом формулы нужно выделить сразу весь диапазон, т.е. ячейки С10:С19, ввести формулу в Строке формул и нажать CRTL+SHIFT+ENTER.
Для скрытия ошибок #ССЫЛКА!, возвращаемой формулой массива, к диапазону С10:С19 применено правило Условного форматирования.
Б. Найти значения, которые совпадают с критерием (точное совпадение)
В этом случае будут выведены все значения, которые совпадают с критерием (без учета РЕгиСТра). Критерий вводится в ячейку E6.
Для создания списка, содержащего найденные значения, воспользуемся формулой массива:
=ИНДЕКС(Список; НАИМЕНЬШИЙ(
ЕСЛИ($E$6=Список;СТРОКА(Список)-СТРОКА($A$9);30);
СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(Список)))))
B. Найти значения, которые начинаются с критерия
В этом случае будут выведены все значения, которые начинаются или совпадают с критерием. Критерий вводится в ячейку G6.
Для создания списка, содержащего найденные значения, воспользуемся формулой массива:
=ИНДЕКС(Список;НАИМЕНЬШИЙ(
ЕСЛИ(ЕСЛИОШИБКА(ПОИСК($G$6;Список);0)=1;СТРОКА(Список)-СТРОКА($A$9);30);
СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(Список)))))
Г. Найти значения, которые заканчиваются на критерий
В этом случае будут выведены все значения, которые заканчиваются или совпадают с критерием. Критерий вводится в ячейку I6.
Для создания списка, содержащего найденные значения, воспользуемся формулой массива:
=ИНДЕКС(Список;НАИМЕНЬШИЙ(
ЕСЛИ($I$6=ПРАВСИМВ(Список;ДЛСТР($I$6));СТРОКА(Список)-СТРОКА($A$9);30);
СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(Список)))))
СОВЕТ:
О поиске текстовых значений с использованием подстановочных знаков читайте в статье Поиск текстовых значений в списках. Часть2. Подстановочные знаки. В статье Выделение ячеек c ТЕКСТом с применением Условного форматирования приведено решение аналогичной задачи с использованием Условного форматирования.
Комментарии
Добрый день. Спасибо за хорошую подборку. А где можно скачать "Файл примера" ?
Файл примера загружен, ссылка внизу статьи.