Использовать формулу для определения форматируемых ячеек

СРАВНЕНИЕ СО ЗНАЧЕНИЕМ В ЯЧЕЙКЕ (АБСОЛЮТНАЯ ССЫЛКА)

Чуть усложним предыдущую задачу: вместо ввода в качестве критерия непосредственно значения (4), введем ссылку на ячейку, в которой содержится значение 4.

Задача2. Сравним значения из диапазона A1:D1 с числом из ячейки А2.

  • введем в ячейку А2 число 4;
  • выделим диапазон A1:D1;
  • применим к выделенному диапазону Условное форматирование на значение Меньше (Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше);
  • в левом поле появившегося окна введем ссылку на ячейку A2 нажав на кнопочку, расположенную в правой части окна (EXCEL по умолчанию использует абсолютную ссылку $А$2).

Нажмите ОК.

В результате, все значения из выделенного диапазона A1:D1 будут сравниваться с одной ячейкой $А$2. Те значения из A1:D1, которые меньше A2 будут выделены заливкой фона ячейки.

Результат можно увидеть в файле примера на листе Задача2.

Чтобы увидеть как настроено правило форматирования, которое Вы только что создали, нажмите Главная/ Стили/ Условное форматирование/ Управление правилами; затем дважды кликните на правиле или нажмите кнопку Изменить правило. В результате увидите диалоговое окно, показанное ниже.

ПОПАРНОЕ СРАВНЕНИЕ СТРОК/ СТОЛБЦОВ (ОТНОСИТЕЛЬНЫЕ ССЫЛКИ)

Теперь будем производить попарное сравнение значений в строках 1 и 2.

Задача3. Сравнить значения ячеек диапазона A1:D1 со значениями из ячеек диапазона A2:D2. Для этого будем использовать относительную ссылку.

  • введем в ячейки диапазона A2:D2 числовые значения (можно считать их критериями);
  • выделим диапазон A1:D1;
  • применим к выделенному диапазону Условное форматирование на значение Меньше (Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше)
  • в левом поле появившегося окна введем относительную ссылку на ячейку A2 (т.е. просто А2 или смешанную ссылку А$2). Убедитесь, что знак $ отсутствует перед названием столбца А.

Теперь каждое значение в строке 1 будет сравниваться с соответствующим ему значением из строки 2 в том же столбце! Выделены будут значения 1 и 5, т.к. они меньше соответственно 2 и 6, расположенных в строке 2.

Результат можно увидеть в файле примера на листе Задача3.

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

Примечание-отступление: О важности фиксирования активной ячейки при создании правил Условного форматирования с относительными ссылками

При создании относительных ссылок в правилах Условного форматирования, они «привязываются» к ячейке, которая является активной в момент вызова инструмента Условное форматирование.

СОВЕТ: Чтобы узнать адрес активной ячейки (она всегда одна на листе) можно посмотреть в поле Имя (находится слева от Строки формул). В задаче 3, после выделения диапазона A1:D1 (клавиша мыши должна быть отпущена), в поле Имя, там будет отображен адрес активной ячейки A1 или D1. Почему возможно 2 вырианта и в чем разница для правил условного форматирования?

Посмотрим внимательно на второй шаг решения предыдущей задачи3 — выделение диапазона A1:D1. Указанный диапазон можно выделить двумя способами: выделить ячейку А1, затем, не отпуская клавиши мыши, выделить весь диапазон, двигаясь вправо к D1; либо, выделить ячейку D1, затем, не отпуская клавиши мыши, выделить весь диапазон, двигаясь влево к А1. Разница между этими двумя способами принципиальная: в первом случае, после завершения выделения диапазона, активной ячейкой будет А1, а во втором D1!

Теперь посмотрим как это влияет на правило условного форматирования с относительной ссылкой.

Если мы выделили диапазон первым способом, то, введя в правило Условного форматирования относительную ссылку на ячейку А2, мы тем самым сказали EXCEL сравнивать значение активной ячейки А1 со значением в А2. Т.к. правило распространяется на диапазон A1:D1, то B1 будет сравниваться с В2 и т.д. Задача будет корректно решена.

Если при создании правила Условного форматирования активной была ячейка D1, то именно ее значение будет сравниваться со значением ячейки А2. А значение из A1 будет теперь сравниваться со значением из ячейки XFB2 (не найдя ячеек левее A2, EXCEL выберет самую последнюю ячейку XFD для С1, затем предпоследнюю для B1 и, наконец XFB2 для А1). Убедиться в этом можно, посмотрев созданное правило:

  • выделите ячейку A1;
  • нажмите Главная/ Стили/ Условное форматирование/ Управление правилами;
  • теперь видно, что применительно к диапазону $A$1:$D$1 применяется правило Значение ячейки <XFB2 (или <XFB$2).

EXCEL отображает правило форматирования (Значение ячейки <XFB2) применительно к активной ячейке, т.е. к A1. Правильно примененное правило, в нашем случае, выглядит так:

ПРИОРИТЕТ ПРАВИЛ

Для проверки примененных к диапазону правил используйте Диспетчер правил условного форматирования (Главная/ Стили/ Условное форматирование/ Управление правилами).

Когда к одной ячейке применяются два или более правил Условного форматирования, приоритет обработки определяется порядком их перечисления в Диспетчере правил условного форматирования. Правило, расположенное в списке выше, имеет более высокий приоритет, чем правило, расположенное в списке ниже. Новые правила всегда добавляются в начало списка и поэтому обладают более высоким приоритетом, однако порядок правил можно изменить в диалоговом окне при помощи кнопок со стрелками Вверх и Вниз.

Например, в ячейке находится число 9 и к ней применено два правила Значение ячейки >6 (задан формат: красный фон) и Значение ячейки >7 (задан формат: зеленый фон), см. рисунок выше. Т.к. правило Значение ячейки >6 (задан формат: красный фон) располагается выше, то оно имеет более высокий приоритет, и поэтому ячейка со значением 9 будет иметь красный фон. На Флажок Остановить, если истина можно не обращать внимание, он устанавливается для обеспечения обратной совместимости с предыдущими версиями EXCEL, не поддерживающими одновременное применение нескольких правил условного форматирования. Хотя его можно использовать для отмены одного или нескольких правил при одновременном использовании нескольких правил, установленных для диапазона (когда между правилами нет конфликта). Подробнее можно ]]>]]>.

Если к диапазону ячеек применимо правило форматирования, то оно обладает приоритетом над форматированием вручную. Форматирование вручную можно выполнить при помощи команды Формат из группы Ячейки на вкладке Главная. При удалении правила условного форматирования форматирование вручную остается.

ДРУГИЕ ПРЕДОПРЕДЕЛЕННЫЕ ПРАВИЛА

В меню Главная/ Стили/ Условное форматирование/ Правила выделения ячеек разработчиками EXCEL созданы разнообразные правила форматирования.

Чтобы заново не изобретать велосипед, посмотрим на некоторые их них внимательнее.

  • Текст содержит… Приведем пример. Пусть в ячейке имеется слово Дрель. Выделим ячейку и применим правило Текст содержит…Если в качестве критерия запишем ре (выделить слова, в которых содержится слог ре), то слово Дрель будет выделено.

Теперь посмотрим на только что созданное правило через меню Главная/ Стили/ Условное форматирование/ Управление правилами…

Как видно из рисунка выше, Условное форматирование можно настроить выделять не только ячейки, содержащие определенный текст, но и не содержащие, начинающиеся с и заканчивающиеся на определенный текст. Кроме того, в случае условий содержит и не содержит возможно применение подстановочных знаков ? и *.

Пусть снова в ячейке имеется слово Дрель. Выделим ячейку и применим правило Текст содержит… Если в качестве критерия запишем р?, то слово Дрель будет выделено. Критерий означает: выделить слова, в которых содержатся слога ре, ра, ре и т.д. Надо понимать, что также будут выделены слова с фразами р2, рм, рQ, т.к. знак ? означает любой символ. Если в качестве критерия запишем ?????? (выделить слова, в которых не менее 6 букв), то, соответственно, слово Дрель не будет выделено. Можно, конечно подобного результата добиться с помощью формул с функциями ПСТР(), ЛЕВСИМВ(), ДЛСТР(), но этот подход, согласитесь, быстрее.

  • Повторяющиеся значения… Это правило позволяет быстро настроить Условное форматирование для отображения уникальных и повторяющихся значений. Под уникальным значением Условное форматирование подразумевает неповторяющееся значение, т.е. значение которое встречается единственный раз в диапазоне, к которому применено правило. Чтобы выделить уникальные значения (т.е. все значения без их повторов), то см. эту статью.
  • Дата… На рисунке ниже приведены критерии отбора этого правила. Для того, чтобы добиться такого же результата с помощью формул потребуется гораздо больше времени.
  • Значение ячейки. Это правило доступно через меню Главная/ Стили/ Условное форматирование/ Создать правило. В появившемся окне выбрать пункт форматировать ячейки, которые содержат. Выбор опций позволит выполнить большинство задач, связанных с выделением числовых значений.

Советую также обратить внимание на следующие правила из меню Главная/ Стили/ Условное форматирование/ Правила отбора первых и последних значений.

  • Последние 10 элементов.

Задача4. Пусть имеется 21 значение, для удобства отсортированных по возрастанию. Применим правило Последние 10 элементов и установим, чтобы было выделено 3 значения (элемента). См. файл примера, лист Задача4.

Слова «Последние 3 значения» означают 3 наименьших значения. Если в списке есть повторы, то будут выделены все соответствующие повторы. Например, в нашем случае 3-м наименьшим является третье сверху значение 10. Т.к. в списке есть еще повторы 10 (их всего 6), то будут выделены и они.

Соответственно, правила, примененные к нашему списку: «Последнее 1 значение», «Последние 2 значения», … «Последние 6 значений» будут приводить к одинаковому результату — выделению 6 значений равных 10.

К сожалению, в правило нельзя ввести ссылку на ячейку, содержащую количество значений, можно ввести только значение от 1 до 1000.

Применение правила «Последние 7 значений» приведет к выделению дополнительно всех значений равных 11, .т.к. 7-м минимальным значением является первое сверху значение 11.

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

  • Последние 10%

Рассмотрим другое родственное правило Последние 10%.

Обратите внимание, что на картинке выше не установлена галочка «% от выделенного диапазона». Эта галочка устанавливается либо в ручную или при применении правила Последние 10%.

В этом правиле задается процент наименьших значений от общего количества значений в списке. Например, задав 20% последних, будет выделено 20% наименьших значений.

Попробуем задать 20% последних в нашем списке из 21 значения: будет выделено шесть значений 10 (См. файл примера, лист Задача4). 10 — минимальное значение в списке, поэтому в любом случае будут выделены все его повторы.

Задавая проценты от 1 до 33% получим, что выделение не изменится. Почему? Задав, например, 33%, получим, что необходимо выделить 6,93 значения. Т.к. можно выделить только целое количество значений, Условное форматирование округляет до целого, отбрасывая дробную часть. А вот при 34% уже нужно выделить 7,14 значений, т.е. 7, а с учетом повторов следующего за 10-ю значения 11, будет выделено 6+3=9 значений.

ПРАВИЛА С ИСПОЛЬЗОВАНИЕМ ФОРМУЛ

Создание правил форматирования на основе формул ограничено только фантазией пользователя. Здесь рассмотрим только один пример, остальные примеры использования Условного форматирования можно найти в этих статьях: Условное форматирование Дат; Условное форматирование Чисел; Условное форматирование Текстовых значений; другие задачи.

Предположим, что необходимо выделять ячейки, содержащие ошибочные значения:

  • Выделите ячейки, к которым нужно применить Условное форматирование (пусть это ячейка А1).
  • Вызовите инструмент Условное форматирование (Главная/ Стили/ Условное форматирование/ Создать правило)
  • Выберите Использовать формулу для определения форматируемых ячеек
  • В поле «Форматировать значения, для которых следующая формула является истинной» введите =ЕОШ(A1) – если хотим, чтобы выделялись ячейки, содержащие ошибочные значения, т.е. будут выделены #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО! (кроме #Н/Д)
  • Выберите требуемый формат, например, красный цвет заливки.

Того же результата можно добиться по другому:

  • Вызовите инструмент Условное форматирование (Главная/ Стили/ Условное форматирование/ Создать правило)
  • Выделите пункт Форматировать только ячейки, которые содержат;
  • В разделе Форматировать только ячейки, для которых выполняется следующее условие: в самом левом выпадающем списке выбрать Ошибки.

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

Excel-Office

Рассмотрим, как посчитать количество цветных ячеек в Excel. Закрасить ячейки в таблице можно самим, можно установить условное форматирование, чтобы ячейки окрашивались по условию. Как настроить таблицу, чтобы ячейки окрашивались по условию, смотрите в статье «Условное форматирование в Excel».
У нас такая таблица.
Как закрасить ячейку самим, смотрите в статье «Текст Excel. Формат.» .
Можно написать формулу, с помощью которой, будут окрашиваться определенные ячейки. О таком способе окрасить ячейки, читайте в статье «Закрасить ячейку по условию в Excel формулой».
Как закрасить ячейки, разобрались. Теперь, разберем, как считать цветные ячейки.
Первый вариант.
Сумма цветных ячеек в Excel.
Чтобы быстро посчитать данные в желтых ячейках, установим фильтр. Какими способами установить фильтр в таблице, смотрите в статье «Фильтр в Excel».
Получилось так.
В ячейке A1 появилась стрелка фильтра (треугольник). Нажимаем на эту стрелку. В появившемся диалоговом окне, нажимаем на функцию «Фильтр по цвету». Появится окно, в котором будут все цвета ячеек, которые присутствуют в столбце. В нашем примере – это желтый цвет ячеек и ячейки без заливки (без цвета). Нажимаем на желтый прямоугольник.
В таблице останутся только желтые ячейки. В ячейку A8 устанавливаем функцию «Автосумма» или любую другую формулу пишем.Получилось так. Посчитались, только видимые ячейки желтого цвета.
Как установить формулу автосуммы, смотрите в статье «Сложение, вычитание, умножение, деление в Excel» .
Чтобы убрать фильтр, снова нажимаем на функцию «Фильтр». Тогда таблица раскроется, а функция «Автосумма» посчитает сумму всех ячеек, разного цвета.
Второй вариант.
Если в таблице цветных ячеек мало, то можно их выделить. В строке состояния будет видно их сумма. Строку состояния можно настроить – смотрите ниже.
Как посчитать цветные ячейки в Excel.
Можно посчитать количество цветных ячеек в Excel. Например, у нас такая таблица.
Первый способ.
Сортировкой собираем все цветные ячейки в одну сторону таблицы. Как работать с сортировкой, читайте в статье «Сортировка в Excel».
Выделяем цветные ячейки. В строке состояния (внизу окна) смотрим, сколько ячеек выделено. Видим – в таблице три цветные ячейки.
Если не показывает количество выделенных ячеек, то нажимаем правой кнопкой мыши на строку состояния и ставим галочки у нужных функций. Так можно быстро посчитать выделенные ячейки – сумму, среднее значение, т.д.
Второй способ.
Подсчет цветных ячеек в Excel.
Несколько разных способов описано в статье «Количество выделенных строк в Excel» . Как посчитать цветные ячейки в Excel, если ячейки окрашены в несколько разных цветов. Как посчитать количество цветных ячеек формулой, фильтром, т.д.
Третий способ.
Можно настроить таблицу так, что после фильтра порядковый номер цветных строк будет по порядку. По нему можно узнать количество цветных ячеек. Смотрите статью «Порядковый номер строк по порядку после фильтра в Excel».
Бывает нужно округлить числа до кратного числа или все числа в столбце привести к кратному числу. Например, все числа в столбце сделать кратными трем, т.е, чтобы числа столбца делились на три без остатка, т.д. Как работать с кратными числами, смотрите в статье «Сумма чисел кратных 3 в Excel».