01 Какую задачу решаем
Чтобы выделить 3 наименьших значения, которые соответствуют определенному условию, вы можете использовать формулу с массивом, основанную на функциях И и НАИМЕНЬШИЙ.
В показанном примере формула, используемая для условного форматирования:
=И($B3=$G$3;$C3<=НАИМЕНЬШИЙ(ЕСЛИ(цвета=$G$3;суммы);3))
=AND($B3=$G$3,$C3<=SMALL(IF(color=$G$3,amount),3))
Где «цвета» — именованный диапазон B3:B12, а «суммы» — именованный диапазон C3:C12.
02 ОБЩАЯ ФОРМУЛА
=И(A1=условие;B1<= НАИМЕНЬШИЙ(ЕСЛИ(условие;значения);3))
=AND(A1=criteria,B1<=SMALL(IF(criteria,values),3))
03 Как работает формула
Для функции И указаны два логических критерия. Первый прост и обеспечивает, что выделены только ячейки, соответствующие цвету в G3:
$B3=$G$3
Второй логический критерий сложнее. Это формула массива, которая фильтрует все суммы, чтобы удостовериться, что выделены только суммы, связанные с цветом в G3:
$C3<=НАИМЕНЬШИЙ (ЕСЛИ(цвета=$G$3;суммы);3)
$C3<=SMALL(IF(color=$G$3,amount),3)
Фильтрация выполняется с помощью функции ЕСЛИ:
ЕСЛИ(цвета=$G$3;суммы)
IF(color=$G$3,amount)
Значение из столбца сумм сохраняется только в том случае, если цвет в столбце B соответствует значению в G3. Получившийся массив выглядит так:
{ЛОЖЬ; 100; ЛОЖЬ; 200; ЛОЖЬ; 300; ЛОЖЬ; 400; ЛОЖЬ; 500}
и переходит в функцию НАИМЕНЬШИЙ со значением k равным 3.
НАИМЕНЬШИЙ возвращает значение «3-го наименьшего» и только значения, меньшие или равные этому значению, возвращают истина.
Когда оба логических условия возвращают ИСТИНА, правило условного форматирования выполняется и ячейки выделяются. Примечание: это формула с массивом, но для нее не требуется ctrl + shift + enter как формула массива, введенную непосредственно на листе.
04 Скачать файл
Автор проекта
Дмитрий Якушев
Привет! Меня зовут Дмитрий. С 2014 года Microsoft Cretified Trainer. Вместе с командой управляем этим сайтом. Наша цель - помочь вам эффективнее работать в 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
Подписывайтесь на нас в соц.сетях:
Сайт https://akademia-excel.ru/
ИП Якушев Дмитрий Александрович
ОГРНИП: 314501721200022
ИНН: 501706813807
Образовательная лицензия № Л035-01255-50/01194039
Банковские реквизиты:
Расчетный счет 40802810500000003597
Банк АО «Тинькофф Банк»
Юридический адрес Банка Москва, 123060,1-й Волоколамский проезд, д. 10, стр. 1
Корр. счет Банка 30101810145250000974
ИНН Банка 7710140679
БИК Банка 044525974
Регистрация на сайте означает согласие с пользовательским соглашением и на получение рассылки и рекламных материалов.
Политика в отношении обработки и защиты персональных данных.
Почта: info@akademia-excel.ru
Телефон для связи: + 7 (958) 697-73-88, + 7 (495) 145-23-86
Написать в тех.поддержку
Подписывайтесь:
авторизуйтесь