Коллеги, на днях меня попросили сделать файл для поиска списка определенных слов в другом списке. Самом собой автоматизированно. Причем во втором списке значения слов не полностью совпадают со значениям в первом списке….. Стоп. Сложное пояснение, нужен пример: найти слова Иван, Петр и далее по списку в столбце ФИО Иванов Сергей, Петр Сергеев и так далее (ФИО может быть записано в любой последовательности, т.е. осуществить поиск по всей длине значения/строки). Итак, как организовать Поиск слов в Excel да еще и по матрице строк и с заменой? Циклом вмакросе? Правильно, но задача была сделать именно формулой. Дополнительно в найденном значение провести перестановку (имя должно быть на первом месте) и заменить искомое значение на правильное. Делюсь решением этой интересной задачей.
Начнем с самого начала.
Содержание
Пример задачи. Поиск слов в Excel
Дан список имен, 10 штук
И список фамилий с именем (до 1000 строк), назовем ФИ
Необходимо
- Найти строки ФИ где присутствуют заданные слова
- Отредактировать найденные строки ФИ, чтобы имя стояло на первом месте.
Поиск слов в Excel. Решение
Сначала немного теории. Чтобы найти слово в какой-либо строке нужно использовать формулы ПОИСК или НАЙТИ. Подробнее можно прочитать здесь
Excel без проблем может найти слово в строке, но вот проверить группу слов в группе значений, уже сложновато из-за двумерной структуры (матрицы).
Т.е на отдельный лист создаем матрицу, где построчно будут ФИ (т.к. их больше), а по столбцам имена. Причем имена необходимо транспонировать, но не просто так, а еще и с формулой массива :))
Пример матрицы показан на рисунке.
Формула для расчета матрицы и замены
Затем сводим данные в отдельный столбец, в нашем случае L и передаем их ВПР на лист ФИ.
Итого
Получилось, то что вы видите на картинке в самом начале. А так же прикладываю файл с полными расчетами.
В общем, я нашел такой нетривиальный способ. Кому есть что сказать на этот счет, прошу не молчать! 😉 Макросы не предлагать, задача не в этом 😉
