Привет. Сегодня покажу, как автоматически выделить три ячейки с наименьшими значениями, соответствующими критерию.

Вот пример. Есть список продаж по регионам (Север и Юг). Для упрощения формулы, мы будем использовать именованные диапазоны:

  • B3:B20 – «регионы»
  • C3:C20 – «значения»

В ячейке E3 указан регион, для которого выделяем наименьшие величины.

Для автоматического выделения, используем условное форматирование. Выполняем в такой последовательности:

  1. Выделим все ячейки, которые должны быть подсвечены. В моем случае, это B3:C20
  2. Нажмем на ленте Главная – Условное форматирование – Создать правило
  3. Выберем пункт «Использовать формулу для определения форматируемых ячеек», и в строке формулы запишем:

    =И($B3=$E$3;$C3<=НАИМЕНЬШИЙ(ЕСЛИ(регионы=$E$3;значения);3))

  4. В этом же окне нажмем «Формат» и определим параметры выделения. Я выбрал только жёлтый цвет заливки
  5. Жмём Ок и получаем результат, как на картинке выше

Принцип работы формулы

Логическая формула, которую мы применили в условном форматировании, работает так:

  • Функция И следит за тем, чтобы выполнились сразу два условия
  • Первое условие: $B3=$E$3. То есть, чтобы регион соответствовал заданному
  • Второе условие более сложное: $C3<=НАИМЕНЬШИЙ(ЕСЛИ(регионы=$E$3;значения);3)
    • ЕСЛИ(регионы=$E$3;значения) – получаем массив значений, для которых регион равен указанному
    • Функцией НАИМЕНЬШИЙ отбираем 3-й самый маленький чек в этом списке
    • Условием $C3<=НАИМЕНЬШИЙ… получаем все чеки, которые меньше или равны третьему наименьшему

Далее инструмент «Условное форматирование» получает список отобранных ячеек, и форматирует так, как вы указали.

Уточню:

  • Чтобы выделить другой регион – просто запишите его название в ячейке E3
  • Чтобы «подсветить» больше/меньше элементов, чем 3, замените тройку на нужное число в конце этого блока: НАИМЕНЬШИЙ(ЕСЛИ(регионы=$E$3;значения);3)
  • Если следует определить не минимальные значения, а максимальные, используйте функцию НАИБОЛЬШИЙ вместо НАИМЕНЬШИЙ