Excel works!

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

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

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

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

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

Необходимо

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

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

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

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

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

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

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

=ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(B$1;$A:$A)>1;B$1&" "&ЛЕВСИМВ($A:$A;ДЛСТР($A:$A)-ДЛСТР(B$1)-1);$A:$A);"")

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

Итого

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

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