Перейти к содержимому

Глава 20. Условное форматирование и формулы массива

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава                          Оглавление                               Следующая глава

Несколько важных фактов об условном форматировании на основе формул:

  • Проверка условия возвращает значения ИСТИНА/ЛОЖЬ и применяется к диапазону ячеек.
  • Условие проверяется для каждой ячейки в диапазоне. Если условие выполнено, ячейка получает форматирование. Если условие не выполнено, ячейка не форматируется.
  • Для создания условия подходит любая формула, возвращающая значения ИСТИНА/ЛОЖЬ (если формула возвращает число, то любое число, кроме нуля приравнивается к значению ИСТИНА, а значение ноль – ЛОЖЬ).
  • Условное форматирование можно использовать, как с обычными формулами, так и с формулами массива.
  • Условное форматирование является волатильным (летучим): оно пересчитывается довольно часто, даже, когда вы напрямую не задействуете диапазон, в отношении которого применено условное форматирование (см. главу 13). Это замедляет работу.

Скачать заметку в формате Word или pdf, примеры в формате Excel

Чтобы создать условное форматирование с помощью формул:

  1. Выделите диапазон ячеек. Обратите внимание, активна левая верхняя ячейка диапазона (она светлее других).
  2. Пройдите по меню ГЛАВНАЯ –> Условное форматирование –> Создать правило, или нажав, и удерживая клавишу Alt, последовательно нажмите Я, Л, С. Откроется диалоговое окно Создание правила форматирования.
  3. Выберите опцию Использовать формулу для определения форматируемых ячеек.
  4. Поместите курсор в поле Форматировать значения, для которых следующая формула является истинной.
  5. Наберите формулу, которая будет применяться к активной ячейке выделенного диапазона. Примените абсолютные и относительные ссылки, как если бы вы копировали формулу на весь диапазон (подробнее см. Относительные, абсолютные и смешанные ссылки на ячейки в Excel). Помните, что условие, которое вы создаете, будет оцениваться отдельно для каждой ячейки, чтобы определить, должна ли эта ячейка получить форматирование.
  6. Нажмите кнопку Формат и выберите любую комбинацию форматирования на четырех вкладках: Число, Шрифт, Граница и Заливка.
  7. Нажмите несколько раз кнопку ОК, закрывая все диалоговые окна.

Рассмотрим два примера условного форматирования на основе формул массива. Первый пример (рис. 20.1) показывает, как выделить строку, содержащую наименьшее значение для города, введенного в ячейку A11. Выделимте диапазон А3:В8 и откройте диалоговое окно Создание правила форматирования, как описано выше. (Если вы выполняете пример в приложенном файле Excel, у вас откроется окно Изменение правила форматирования, так как правило уже создано). Обратите внимание на смешанную ссылку на ячейку $В3 в формуле. Она позволяет каждой ячейки в диапазоне при поиске минимального значения для города, оставаться в столбце В. Обратите внимание, что формула массива возвращает одно и тоже значение для каждой ячейки в диапазоне. Но только в ячейках А5 и В5 это значение равно проверяемому. Если ячеек много волатильный характер вычислений может замедлить работу. Второй пример (рис. 20.2), обходит эту трудность, используя вспомогательную ячейку.

Рис. 20.1. Формула массива в диалоговом окне Изменение правила форматирования

Рис. 20.1. Формула массива в диалоговом окне Изменение правила форматирования; Чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Цель форматирования (рис. 20.2) – выделить строки с N лучшими результатами гонщика, указанного в ячейке А17. Число N указано в ячейке В17. Формулы массива, вычисляющие второе лучшее время для гонщика, приведены в ячейках D18 и D19 (используйте любую из них). Формула =И($A3=$A$17;$B3<=$D$18) проверяет два условия: равно ли имя гонщика выбранному критерию (в ячейке А17), и входит ли время в два наилучших результата (т.е., меньше или равно значению в D18). Вспомогательная ячейке (D18 или D19) предотвращают многократное повторение расчетов этого времени внутри формулы условного форматирования.

Рис. 20.2. Вспомогательная ячейка избавляет от многократного расчета формулы массива

Рис. 20.2. Вспомогательная ячейка избавляет от многократного расчета формулы массива

7 комментариев для “Глава 20. Условное форматирование и формулы массива”

  1. Василий

    Спасибо за статью! Нашел еще хороший материал на тему условного форматирования в Excel… (часть комментария удалена, поскольку автор блога счел, что она несет рекламный характер)

  2. Помогите, есть 5 столбцов. А, В, Cи X Y Z (к примеру) и нужно форматирование построчно… Если x = a ; у = в ; с <0, то z красный… Как сделать?

  3. Добрый день. Нужна помощь. Есть диапазон Лист1!B1:B1000, с набором значений в столбце от 1 до 1000 строк, но не все прописаны, допустим 1, 2, 7, 15, 127…1000. Есть диапазон Лист2!A3:AM41 (34 строки и 39 столбцов) с набором идентичных значений по порядку 1,2,3,4…1000. Как сделать чтобы при записи значения в Лист1!B1:B1000, в Лист2 такое же значение закрашивалось? Формула массива в условное форматирование не вставляется. СПАСИБО.

  4. Благодарю за полезную статью. Хорошо прописана технология УФ.

  5. Анастасия Мирская

    Добрый день. Подскажите, как поставить условное форматирование цветом или флажком, если значение а каждой ячейке столбца b больше значения в каждой ячейке столбца а. Как я понимаю, здесь надо применить формулу массива, но какую…мне нужно чтобы это b2 сравнивалась с а2; b3 с a3 и т.д

  6. Сергей Багузин

    Анастасия, условное форматирование само понимает про массивы))
    Если я правильно понял, вам нужно выделить ячейки в столбце В, если значения больше, чем в столбце А.
    Можно для диапазона В1:В20 использовать форматирование на основе формулы = В1 > А1

    См. также Excel-файл.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *