Как сделать поиск в Excel

Коллеги, на днях меня попросили сделать файл для поиска списка определенных слов в другом списке. Самом собой автоматизированно. Причем во втором списке значения слов не полностью совпадают со значениям в первом списке..... Стоп. Сложное пояснение, нужен пример: найти слова Иван, Петр и далее по списку в столбце ФИО Иванов Сергей, Петр Сергеев и так далее (ФИО может быть записано в любой последовательности, т.е. осуществить поиск по всей длине значения/строки). Итак, как организовать Поиск слов в Excel да еще и по матрице строк и с заменой? Циклом вмакросе? Правильно, но задача была сделать именно формулой. Дополнительно в найденном значение провести перестановку (имя должно быть на первом месте) и заменить искомое значение на правильное. Делюсь решением этой интересной задачей.

Начнем с самого начала.

Содержание

Пример задачи. Поиск слов в Excel

Дан список имен, 10 штук

И список фамилий с именем (до 1000 строк), назовем ФИ

Необходимо

  1. Найти строки ФИ где присутствуют заданные слова
  2. Отредактировать найденные строки ФИ, чтобы имя стояло на первом месте.

Поиск слов в Excel. Решение

Сначала немного теории. Чтобы найти слово в какой-либо строке нужно использовать формулы ПОИСК или НАЙТИ. Подробнее можно прочитать здесь

Excel без проблем может найти слово в строке, но вот проверить группу слов в группе значений, уже сложновато из-за двумерной структуры (матрицы).

Т.е на отдельный лист создаем матрицу, где построчно будут ФИ (т.к. их больше), а по столбцам имена. Причем имена необходимо транспонировать, но не просто так, а еще и с формулой массива :))

Пример матрицы показан на рисунке.

Формула для расчета матрицы и замены

Затем сводим данные в отдельный столбец, в нашем случае L и передаем их ВПР на лист ФИ.

Итого

Получилось, то что вы видите на картинке в самом начале. А так же прикладываю файл с полными расчетами.

В общем, я нашел такой нетривиальный способ. Кому есть что сказать на этот счет, прошу не молчать! ;) Макросы не предлагать, задача не в этом ;)

Похожие статьи