В четверг ко мне обратились с запросом на составление сложной формулы, хочу поделиться с вами тем, как происходит такой процесс, надеюсь будет интересно :)
Сам запрос: есть ячейка, в которую нужно ввести текст. Затем формула должна проверить, упоминается ли в этом тексте какое-либо из слов или словосочетаний, которые перечислены в отдельном столбце. Это полезно, например, если вы хотите убедиться, что в вашем тексте не упоминаются неприличные слова
Допустим, проверяемая ячейка – B2, а проверочный диапазон – A2:A10
Итак, что же с этим делать?
1. Так как нужно искать слово или словосочетание из отдельного столбца внутри текста (а не проверять соответствие всего текста целиком), основной функцией здесь будет либо ПОИСК, либо НАЙТИ. Функция НАЙТИ не подойдет, так как она учитывает регистр искомой буквы, а для нас наоборот важно искать все слова без учета регистра
Получаем формулу =ПОИСК(A2:A10;B2)
1.1 Сразу же проверяем, как эта формула работает. В ячейке B2 введем какое-нибудь словосочетание, например "синий еж". А в диапазон A2:A10 – разные слова, одним из которых будет слово "еж". Видим, что, так как проверяется столбец, результатом этой формулы будет массив – несколько ячеек. При этом во всех ячейках массива, кроме пятой сверху, у нас выскакивает ошибка #ЗНАЧ!, а в пятой сверху - число 7. В слове "синий" 5 букв, плюс пробел – 6, получается "еж" у нас начинается с 7 символа ячейки
2. При большом количестве проверочных строк будет неудобно искать, где у нас есть соответствия в возвращаемом массиве. Гораздо удобней будет иметь 1 ячейку, в которой мы сразу видим, есть совпадения с текстом или нет. Здесь нам пригодится наша функция СУММПРОИЗВ, которая и суммирует значения массива. Но если мы составим формулу =СУММПРОИЗВ(ПОИСК(A2:A10;B2);0), то получим результатом ошибку #ЗНАЧ!, так как сумма ошибки и числа выдает ошибку
3. Поэтому сначала нам надо наложить функцию ЕСЛИОШИБКА. Получаем =ЕСЛИОШИБКА(ПОИСК(A2:A10;B2);0). В массиве ошибки заменились нулями. Теперь можем накладывать нашу функцию. Получаем формулу =СУММПРОИЗВ(ЕСЛИОШИБКА(ПОИСК(A2:A10;B2);0))
3.1 Функция возвращает число 7. Проверим, что будет, если удалить слово "еж" из столбца А. Функция возвращает 1. Не понимаем, откуда взялась 1, если в столбце нет ячеек с текстом из B2. Проверяем, что будет, если ввести другое слово на место ежа. Функция выдает 0, все в порядке. Пробуем удалить еще какую-нибудь ячейку, снова получаем 1. Так, видимо функция ПОИСК выдает 1 при поиске пустой ячейки в тексте. Идем чатджипитишить – так и есть, оказывается Excel рассматривает каждую строку как содержащую пустое значение в начале, поэтому если у нас в диапазоне А2:А10 будут пустые ячейки, формула будет считать, что у нас всегда есть совпадение
Что с этим делать? Первый вариант: задать ограниченный диапазон и написать примечание для пользователей о том, что из диапазона нельзя удалять ячейки. Но вы сами понимаете, как это неудобно. Второй вариант: у нас уже есть прекрасная функция СУММПРОИЗВ, а неделю назад мы научились с помощью нее проверять соответствие. Добавим в функцию проверку на то, что ячейка в диапазоне не равна 0 (не является пустой). Получаем: =СУММПРОИЗВ(ЕСЛИОШИБКА(ПОИСК(A2:A10;B2);0)*(A2:A10<>0))
3.1.1 Снова проверяем. Теперь пробуем удалять разные ячейки из диапазона А2:А10, больше такая проблема не возникает
3. С точки зрения внешнего вида будет более наглядно, если вместо чисел у нас будут появляться разные слова в зависимости от того, есть совпадения или нет. Слова могут быть какие угодно, лично мне удобно, чтобы при совпадении на меня кричали капсом, а без совпадения ячейка оставалась пустой
Все это делается с помощью функции ЕСЛИ. Мы помним, что наша формула выдает 0 при отсутствии совпадений и любое другое число в случае, если совпадение есть. Поэтому зададим условие, что при 0 наша функция будет выдавать "" (пустую ячейку), а в остальных случаях – "СОВПАЛО"
Получаем: =ЕСЛИ(СУММПРОИЗВ(ЕСЛИОШИБКА(ПОИСК(A2:A10;B2);0)*(A2:A10<>0))=0;"";"СОВПАЛО")
Готово, мы создали формулу, которая ищет совпадения в столбце. Как бы вы её ещё доработали?