Как сделать формулу сравнивающую входные данные со значениями из колонки другого листа?
У меня есть excel документ с двумя листами. В первом листе три колонки: Категория, Описание и Анализ Категории.
Во втором листе колонки две: Фраза Вхождения, Категория
Мне нужна следующая формула, которая заполняла бы колонку Анализ Категории первого листа:
- формула принимает два аргумента: 1) Категория, 2) Описание
- Берётся Описание и сравнивается с каждым значением второго листа колонки Фраза Вхождения
- Если Фраза Вхождения есть в описании, то мы прекращаем итерацию и возвращаем значение из столбика Категории второго листа (того же ряда на котором закончилась итерация).
- Если мы прошли по всем Фразам Вхождения и ни одной нет в описании, то возвращаем первый входной параметр - Категорию (из первого листа).
Я пытался написать функцию, но она даже близко не работает. Пробовал сделать тоже через макрос - всё хорошо, но мне нужна именно функция.
Ответы (2 шт):
Надо показывать пример, так легче понять задачу.
Как понял из текста (Фраза Вхождения есть в описании) описание - это текст, в котором встречается искомая фраза, т.е. неполное совпадение двух значений. В этом случае нужно немного постараться )
Вариант_1
=ЕСЛИ(СЧЁТ(1/ПОИСК(Лист2!$A$2:$A$20;B2));ИНДЕКС(Лист2!$B$2:$B$20;МИН(ЕСЛИ(ЕЧИСЛО(ПОИСК(Лист2!$A$2:$A$20;B2));СТРОКА(Лист2!$A$2:$A$20)-1)));A2)
Это формула массива. Ввод такой формулы: не выходя из редактирования ячейки, нажать три клавиши - Ctrl+Shift+Enter (формула должна обрамиться фигурными скобками).
Вариант_2. Если допускается извлечение значения не с первой найденной строки, а с последней (если найдена только одна, то разницы нет - она и первая, и последняя), можно применить "немассивную" формулу (ввод, как обычно, без трех клавиш):
=ЕСЛИОШИБКА(ИНДЕКС(Лист2!$B$2:$B$20;ПРОСМОТР(2;1/ПОИСК(Лист2!$A$2:$A$20;B2);СТРОКА(Лист2!$A$2:$A$20)-1));A2)
Вариант_3. Для облегчения вычислений в таблицу на втором листе добавить столбец с формулой,определяющей строку на первом листе, где в описании найдена фраза:
=ЕСЛИОШИБКА(ПОИСКПОЗ("*"&A2&"*";Лист1!$B$2:$B$15;)+1;)
Формула столбца Анализ для этого варианта:
=ЕСЛИ(СЧЁТЕСЛИ(Лист2!C:C;СТРОКА());ИНДЕКС(Лист2!$B$2:$B$20;ПОИСКПОЗ(СТРОКА();Лист2!$C$2:$C$20;));A2)
или
=ЕСЛИ(СЧЁТЕСЛИ(Лист2!C:C;СТРОКА());ПРОСМОТР(СТРОКА();Лист2!$C$2:$C$20;Лист2!$B$2:$B$20);A2)
Вариант_Х. Если искать полное совпадение (Фраза Вхождения = описание), то достаточно простой формулы:
=ЕСЛИОШИБКА(ВПР(B2;Лист2!$A$2:$B$20;2;);A2)
=ЕСЛИОШИБКА(ЕСЛИ(B2=ВПР(B2;Лист2!$A$2:$B$14;1;0);ВПР(B2;Лист2!$A$2:$B$14;2;0);A2);A2)