Здравствуйте, друзья. Сегодня практикуемся в автоматизации оформления. Я покажу вам, как за одну минуту сделать выделение наибольших элементов в таблице. При этом, количество наибольших элементов можно изменять, и программа будет пересчитывать форматы.

К примеру, есть таблица с продажами по месяцам. Требуется в ячейку J3 ввести количество наибольших значений продаж, которые должны выделиться. Задам изначально там 5 наибольших.

список продаж

Для выполнения задания, мы будем использовать условное форматирование. Если вы не знаете, что это – прочтите эту статью.

Далее – в такой последовательности:

  1. Выделяем весь диапазон данными о продажах (B3:G21). Я сразу для удобства дам ему имя «продажи»
  2. Кликаем на ленте Главная – Условное форматирование – Создать правило
  3. В верхней части окна выбираем пункт «Использовать формулу для определения форматируемых ячеек»
  4. В появившейся строке формул запишите формулу: =B3>=НАИБОЛЬШИЙ(продажи;$J$3)

    настройка условного форматирования

  5. Нажмите Формат и задайте подходящее форматирование ячеек
  6. Нажмите Ок, чтобы закрыть окно «Создание правила форматирования»

наибольшие выделены

Теперь Excel будет выделять нужное количество значений. Как это работает? Функция НАИБОЛЬШИЙ найдёт пятое наибольшее число в таблице, а инструмент «Условное форматирование» найдёт ячейки, в которых продажи больше, или равны этому числу. К отобранным ячейкам применится тот формат, что вы указали.

Теперь вы можете изменять число в ячейке J3, форматирование будет изменяться исходя из этой величины.

Бонус 1. Если вы всегда выделяете одно и то же количество значений, или изменяете его редко, можно использовать более простой подход. Выделите массив с данными, кликните на ленте Главная – Условное Форматирование – Правила отбора первых и последних значений – Другие правила. Здесь задайте необходимое количество, форматирование, и нажмите Ок.

выделение без формул

Делается это быстрее, но следует помнить: чтобы изменить заданное количество выделяемых цифр, нужно менять настройки условного форматирования, а не просто указать цифру в ячейке J3.

Бонус 2. Вы можете обезопасить себя от введения в клетку J3 неподходящего значения, используя инструмент «Проверка данных».

Для этого выделите ячейку J3 и нажмите на ленте Данные – Работа с данными – Проверка данных. На рис. ниже я указал, что в ячейку можно вводить только целое число от нуля до 114 (столько значений в исходной таблице).

проверка данных

Когда вводимые данные не будут удовлетворять этим условиям – Excel покажет сообщение об ошибке и не даст подтвердить ввод.

Это всё, что я хотел рассказать. Жду ваших вопросов и комментариев!