Загальна формула
=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.
Пояснення
Усередині функції AND є два логічні критерії. Перший є простим і забезпечує виділення лише комірок, які відповідають кольору в E5:
$B3=$E$5
Другий тест є більш складним:
$C3<=SMALL(IF(color=$E$5,amount),3)
Тут ми фільтруємо суми, щоб переконатися, що зберігаються лише значення, пов’язані з кольором у E5 (синій). Фільтрація виконується за допомогою функції IF, як це:
IF(color=$E$5,amount)
Отриманий масив виглядає так:
(FALSE;100;FALSE;200;FALSE;300;FALSE;400;FALSE;500)
Зверніть увагу, що значення зі стовпця суми зберігається, лише якщо колір "синій". Інші суми тепер НЕПРАВИЛЬНІ.
Далі цей масив переходить у функцію SMALL зі значенням ak 3, а SMALL повертає "3-те найменше" значення, 300. Логіка для другого логічного тесту зводиться до:
$C3<=300
Коли обидві логічні умови повертають TRUE, запускається умовне форматування і виділяються комірки.
Примітка: це формула масиву, але не вимагає управління + shift + enter.
