Условное форматирование ячеек, которые содержат элемент списка

Ссылка на это место страницы: #zadacha

Ссылка на это место страницы: #formula

Ссылка на это место страницы: #kak

03 Как работает формула

В процессе вычисления эта часть формулы ищет каждую ячейку диапазона B4:B11 для всех значений в именованном диапазоне «список»:

=СУММПРОИЗВ(—ЕЧИСЛО(ПОИСК(список;B4)))>0

=SUMPRODUCT(—ISNUMBER(SEARCH(things,B4)))>0

Функция ПОИСК возвращает позицию найденного значения, а также ошибку #ЗНАЧ, если значение не найдено.

Для B4 результаты возвращаются в массив, подобный этому:

{8; # ЗНАЧ;! # ЗНАЧ!}

Функция ЕЧИСЛО изменяет все результаты на ИСТИНА или ЛОЖЬ:

{ИСТИНА; ЛОЖЬ; ЛОЖЬ}

Двойное отрицание перед ЕЧИСЛО вычислят ИСТИНА / ЛОЖЬ как 1/0:

{1; 0; 0}

Затем функция СУММПРОИЗВ добавляет результаты, которые проверены на ноль:

=СУММПРОИЗВ({1;0;0})>0

=SUMPRODUCT({1;0;0})>0

Любой ненулевой результат означает, что было найдено хотя бы одно значение, поэтому формула возвращает ИСТИНА, применяя правило.

Вариант, чувствительный к регистру

Функция ПОИСК не чувствительна к регистру. Если вам нужно также проверить регистр, просто замените ПОИСК с НАЙТИ следующим образом:

=СУММПРОИЗВ(—ЕЧИСЛО(НАЙТИ(список;A1)))>0

=SUMPRODUCT(—ISNUMBER(FIND(things,A1)))>0

Ссылка на это место страницы: #file

Автор проекта

Дмитрий Якушев

Привет! Меня зовут Дмитрий. С 2014 года Microsoft Cretified Trainer. Вместе с командой управляем этим сайтом. Наша цель - помочь вам эффективнее работать в Excel.

Изучайте наши статьи с примерами формул, сводных таблиц, условного форматирования, диаграмм и макросов. Записывайтесь на наши курсы или заказывайте обучение в корпоративном формате.

Дмитрий Якушев

Чт 15 Декабря 15:04

Для того чтобы оставить комментарий —
авторизуйтесь

EXCEL Текст УФ

Связанные статьи

Сайт https://akademia-excel.ru/

ИП Якушев Дмитрий Александрович

ОГРНИП: 314501721200022

ИНН: 501706813807

Образовательная лицензия № Л035-01255-50/01194039

Почта: info@akademia-excel.ru

Телефон для связи: + 7 (958) 697-73-88, + 7 (495) 145-23-86

Написать в тех.поддержку

Регистрация на сайте означает согласие с пользовательским соглашением и на получение рассылки и рекламных материалов.

Политика в отношении обработки и защиты персональных данных.

Публичная оферта

Сведения об образовательной организации

Банковские реквизиты:
Расчетный счет 40802810500000003597
Банк АО «Тинькофф Банк»
Юридический адрес Банка Москва, 123060,1-й Волоколамский проезд, д. 10, стр. 1
Корр. счет Банка 30101810145250000974
ИНН Банка 7710140679
БИК Банка 044525974

Подписывайтесь на нас в соц.сетях: