Общая формула
=AND(A1=criteria,B1<=SMALL(IF(criteria,values),3))
Резюме
Чтобы выделить 3 наименьших значения, соответствующих определенным критериям, вы можете использовать формулу массива, основанную на функциях AND и SMALL. В показанном примере для условного форматирования используется следующая формула:
=AND($B5=$E$5,$C5<=SMALL(IF(color=$E$5,amount),3))
Где «цвет» - именованный диапазон B5: B12, а «количество» - именованный диапазон C5: C12.
Объяснение
Внутри функции И есть два логических критерия. Первый простой и гарантирует, что будут выделены только ячейки, соответствующие цвету в E5:
$B3=$E$5
Второй тест более сложный:
$C3<=SMALL(IF(color=$E$5,amount),3)
Здесь мы фильтруем суммы, чтобы убедиться, что сохраняются только значения, связанные с цветом в E5 (синий). Фильтрация выполняется с помощью функции ЕСЛИ следующим образом:
IF(color=$E$5,amount)
Результирующий массив выглядит так:
(FALSE;100;FALSE;200;FALSE;300;FALSE;400;FALSE;500)
Обратите внимание, что значение из столбца суммы сохраняется только в том случае, если цвет - «синий». Остальные суммы теперь ЛОЖЬ.
Затем этот массив переходит в функцию SMALL со значением k, равным 3, и SMALL возвращает "третье наименьшее" значение, 300. Логика для второго логического теста сводится к:
$C3<=300
Когда оба логических условия возвращаются ИСТИНА, запускается условное форматирование и выделяются ячейки.
Примечание: это формула массива, но она не требует Ctrl + Shift + Enter.
