Общая формула
=И(A1=условие;B1<= НАИМЕНЬШИЙ(ЕСЛИ(условие;значения);3))
=AND(A1=criteria,B1<=SMALL(IF(criteria,values),3))
Объяснение
Чтобы выделить 3 наименьших значения, которые соответствуют определенному условию, вы можете использовать формулу с массивом, основанную на функциях И и НАИМЕНЬШИЙ.
В показанном примере формула, используемая для условного форматирования:
=И($B3=$G$3;$C3<=НАИМЕНЬШИЙ(ЕСЛИ(цвета=$G$3;суммы);3))
=AND($B3=$G$3,$C3<=SMALL(IF(color=$G$3,amount),3))
Где «цвета» — именованный диапазон B3:B12, а «суммы» — именованный диапазон C3:C12.
Как работает формула
Для функции И указаны два логических критерия. Первый прост и обеспечивает, что выделены только ячейки, соответствующие цвету в 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 как формула массива, введенную непосредственно на листе.
