backtotop

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

Поиск слов в Excel

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

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

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

Поиск слов в Excel 1

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

Поиск слов в Excel 2

Необходимо

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

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

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

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

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

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

Поиск слов в Excel 3

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

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

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

Итого

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

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

 

Поделитесь нашей статьей в ваших соцсетях:


Комментарии:

( 2 Comments )

ShAM:

Здравствуйте, Сергей. Хороший пример. Но, перестает работать если фамилия содержит имя. Например, Иванов, Петров…
С уважением, Алишер.

Алишер, согласен в таком случае после имени можно добавить пробел (в столбце имен). Если не поможет в Вашем случае — пишите, чем смогу, помогу!

Оставьте ваш комментарий здесь:

You have to agree to the comment policy.

4 × четыре =