Условное форматирование в Excel 2016

При работе в Excel часто бывает нужно быстро проанализировать данные в таблицах. Например, найти события, произошедшие после указанной даты или найти текстовые повторения в диапазоне ячеек. Или более «сложный случай»: быстро проанализировать таблицу однородных данных.

Как это сделать? В Excel имеется набор инструментов, который позволит это выполнить быстро – буквально за пару-тройку щелчков мыши. Самый простой из таких инструментов – условное форматирование. Давайте разберем ситуации, в которых пригодятся условные форматы.

1. Выделение ячеек с определенными значениями

Предположим, у нас есть три столбца с данными. В первом нужно найти значения больше 150 тысяч, во втором – текст с указанным словом, в третьем – повторения.

Сумма более 150 тыс.

image

Cлово «материал»

image

Повторения

Если начать искать нужные ячейки, выделяя их мышкой, то потратим время и, скорее всего, допустим ошибки. Да и в работе обычно используются таблицы гораздо больших размеров. Для решения такой задачи в Условных форматах есть специальная опция – Правила выделения ячеек. Чтобы настроить форматирование ячеек, перейдите в Excel на вкладку меню Главная → выберите Условное форматирование.

Пример 1. Найти значения больше заданного числа. Итак, чтобы найти все ячейки с цифрами более 150 тысяч, достаточно:

  • выделить столбец с данными;
  • перейти в меню Главная → Условное форматирование → Правила выделения ячеек → Больше…
  • в открывшемся окне ввести число, данные больше которого хотим увидеть. И указать, каким цветом должны быть выделены ячейки.

Пример 2. Выделить текст с указанными словами. Для поиска необходимого текста поступаем точно так же:

  • выделить столбец с данными;
  • перейти в меню Главная → Условное форматирование → Правила выделения ячеек → Текст содержит
  • в появившемся окне указать искомый текст (слово «материал») и, если нужно, задать формат.

Пример 3. Найти повторения. Такой же принцип и с повторяющимися значениями:

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

В итоге за несколько щелчков мышкой получаем уйму сэкономленного времени и результат – маркированные данные!

Сумма более 150 тыс.

Cлово «материал»

Повторения

2. Анализ числовых данных

Работая с большими таблицами, не всегда получается их данные «схватить на лету». Ниже пример простой таблицы. Глядя на нее не очень-то понятно, когда было хорошо, когда плохо и по каким подразделениям.

Пример 1. Анализ данных с помощью цветовых шкал. Для того, чтобы проанализировать числовые данные, нам потребуется условное форматирование:

  • выделите ячейки с цифрами, кроме строки итогов (чтобы исходные данные на фоне итогов не смотрелись заниженными);
  • перейдите в меню Главная → Условное форматирование → Цветовые шкалы и выберите правило форматирования, например «зеленый-белый-красный».

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

Кстати, в меню Главная → Условное форматирование → Управление правилами можно поменять правила форматирования. Например, цвета в таблице получились слишком яркими, их можно сделать менее насыщенными.

Еще в меню или в окне «Диспетчер правил условного форматирования» можно добавить новые правила или очистить условные форматы с помощью кнопки Удалить правила.

Пример 2. Добавить гистограммы в таблицу. Теперь можно проанализировать структуру продаж по направлениям:

  • выделить столбец с данными по полугодию;
  • перейти на вкладку Главная → Условное форматирование → Гистограммы, выберите любую понравившуюся;
  • если нужно, поменять цвет гистограмм в окне «Диспетчер правил условного форматирования».

Пример 3. Анализ данных с помощью наборов значков. Теперь строка итогов. Ее мы разметим с помощью значков. Путь тот же:

  • выделяем строку итогов по месяцам (без полугодия);
  • меню Главная → Условное форматирование → Наборы значков → любой подходящий;
  • чтобы не перегружать таблицу значками, в меню Главная → Условное форматирование → Управление правилами задаем, чтобы в строке выводился только один значок рядом с наибольшими значениями.

В результате получится таблица, как на рисунке ниже:

Инструменты быстрого анализа

Настроить условное форматирование в Excel можно также в меню Быстрого анализа (или экспресс-анализа). Чтобы перейти к этому меню, выделите таблицу с данными и нажмите на появившийся внизу справа значок. В меню Быстрого анализа на вкладке Форматирование настраиваются условные форматы, а на других вкладках доступны другие инструменты – диаграммы, спарклайны, строка итогов и сводные таблицы.

3. Визуальный анализ в таблицах

С помощью условного форматирования в Excel можно настроить вид таблиц, удобный для анализа данных. Например, чтобы проанализировать структуру показателей по нескольким различным параметрам.

Пример 1. Анализ структуры показателей.

Предположим, у нас есть данные по группам покупателей – количество заказов, выручка и дебиторская задолженность. Используя условное форматирование, гистограммы и значки, можно быстро провести анализ.

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

  • выделить столбец «% к итогу» по количеству заказов, без итоговой строки;
  • меню Главная → Условное форматирование → Гистограммы, выберите любую понравившуюся;
  • в окне настройки правил форматирования выберите не слишком яркий цвет и поставьте галочку «Показывать только столбец» — после этого в столбце останутся только гистограммы, а цифры «исчезнут».

Таким же образом настраиваются гистограммы для каждого столбца «% к итогу». Готово! Структура показателей выглядит понятнее, чем в предыдущем варианте таблицы.

Точно так же, с помощью формул и условного форматирования, в таблице можно найти покупателей, у которых большая оборачиваемость дебиторской задолженности, например, более 30 дней. Чтобы обратить внимание на такую задолженность, рядом с ней можно поставить красный кружок и выделить число цветом. Для удобства красные кружки вынесены в отдельный столбец.

Пример 2. Анализ рейтингов.

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

Такое форматирование настраивается с помощью двухцветной шкалы, как на рисунке:

Еще один вариант настройки форматов в такой таблице – задать несколько правил форматирования для группы ячеек. В этом случае можно дополнительно указать цвет шрифта, чтобы при заполнении ячеек цветом высокой интенсивности шрифт был более светлым. Это улучшит читаемость цифр в таблице.

Так, используя простые инструменты Excel, за 3–5 кликов мышкой можно проанализировать большие объемы информации и сделать отчеты интуитивно понятными.

Как сделать условное форматирование в Excel

Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».

При нажатии на стрелочку справа открывается меню для условий форматирования.

Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».

Введем в диапазон А1:А11 ряд чисел:

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

Читайте также:  Что делать если ноутбук выключается сам по себе

Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:

Выходим из меню нажатием кнопки ОК.

Формат даты в Excel

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

В выпадающем меню можно увидеть все возможные варианты.

Примечательно то, что при открытии документа значения даты сравниваются с текущим, и если какое-либо правило изменилось (например, перестало быть «Вчера»), то стиль для него изменяется. Это очень удобно при повторной работе с документом.

Например, выбрав условие «В текущем месяце» для ноября мы получим выделение красным только тех строк, которые попадают под это условие.

Условное форматирование по значению другой ячейки

Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.

Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» — «Меньше»).

В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.

Результат форматирования сразу виден на листе Excel.

Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.

Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.

Выделим исходный диапазон (А1:А11). Нажмем «УФ» — «Правила выделения ячеек» — «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ!, а не абсолютная.

Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.

Читайте также:  Восстановление системы Windows 10 с загрузочной флешки

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

В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.

Сравните:

Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.

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

  1. Выделите первую ячейку диапазона с условным форматированим.
  2. Откройте меню инструмента, нажмите «Управление правилами».

Если вы передумали применять условное форматирование к ячейке, диапазону, таблице или сводной таблице, вы можете быстро очистить формат при помощи какой-либо из команд меню Удалить правила. Вы можете удалить правила из выделенных ячеек, со всего рабочего листа или таблицы или из сводной таблицы newd. При удалении условного форматирования очищается только формат, а содержимое ячейки, диапазона, таблицы или сводной таблицы не изменяется.

Чтобы удалить правила условного форматирования:

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

Нажмите вкладку Главная.

Нажмите кнопку Условное форматирование, а затем выберите Удалить правила.

Выберите желаемый параметр удаления:

Удалить правила из выделенных ячеек

Удалить правила со всего листа

Удалить правила из этой таблицы

Читайте также:  Как подключить к ноутбуку аудиосистему, колонки

Удалить правила из этой сводной таблицы

Newer news items:

  • 31/10/2007 22:44 — Вы можете изменить цвет чисел и текста в рабочем листе. Изменение цвета шрифта является эффективным способом выделения совпадающих данных. Например, н…

    «>Изменение цвета данных

  • 31/10/2007 22:39 — Длина метки не всегда может соответствовать ширине столбца. Если расположенная справа ячейка не заполнена, текст перейдет на нее, однако если эта ячей…

    «>Управление размещением текста

  • 31/10/2007 20:42 — При вводе данных в ячейку Excel выравнивает метки по левому краю ячейки, а значения и формулы — по правому краю. Горизонтальное выравнивание означает,…

    «>Изменение способа выравнивания данных

  • 31/10/2007 18:20 — Если какие-либо ячейки вашего рабочего листа содержат условный формат, вы можете быстро найти их при помощи функции Найти и выделить, а затем отредакт…

    «>Поиск элементов условного форматирования

  • 31/10/2007 14:52 — Если вы применяете условное форматирование к какой-либо ячейке или диапазону ячеек. Excel сохраняет все правила, связанные с условным форматом, в Дисп…

    «>Управление правилами условного форматирования

Older news items:

  • 30/10/2007 13:49 — Вместо того, чтобы использовать какое-либо из встроенных правил условного форматирования, вы можете создать свои собственные правила. При помощи диало…

    «>Создание условного формата

  • 30/10/2007 12:04 — Благодаря Excel 27 вы можете применять различные условные форматы при помощи гистограмм, цветовых шкал и наборов значков. Цветная гистограмма newij по…

    «>Применение специального условного форматирования

  • 30/10/2007 08:05 — Форматирование рабочего листа
  • 30/10/2007 05:34 — Microsoft Excel предлагает вашему вниманию набор шаблонов, разработанных специально для вас. Увидеть эти шаблоны можно в папке Установленные шаблоны, …

    «>Изменение шаблона

  • 30/10/2007 04:31 — Возможно, вы этого пока на осознаете, но любая создаваемая вами рабочая книга основана на шаблоне. Когда вы запускаете новую рабочую книгу без указани…

    «>Открытие шаблона

Next page >>

Условное форматирование – несколько условий

Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.

  • 1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» — «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
  • 2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».

Заполняем параметры форматирования по первому условию:

Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.

Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».

То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).

Гистограммы

Они показывают информацию в блоке в виде гистограммы. Ячейка принимается за 100%, которому соответствует максимальное число в выбранном диапазоне. Если значение в блоке будет отрицательное – гистограмма делится на половину, имеет другую направленность и цвет.

Пример

Отобразим число для каждого выделенного блока в виде гистограммы. Выбираем любой из предложенных способов заливки.

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

Откроется следующее окно. В нем можно создать новое правило для выделенных ячеек, изменить или удалить нужное из списка. Выбираем «Изменить правило».

Читайте также:  Как выбрать операционную систему для нетбука? какую систему выбрать?

Внизу окна «Изменение правила форматирования» можно изменить описание для него. Ставим «Минимальное значение» – «Число», и в поле «Значение» пишем «5». Если Вы не хотите, чтобы в ячейках отображались числа, поставьте галочку в пункте «Показывать только столбец». Здесь же можно изменить цвет и тип заливки.

В результате минимальное число для выделенных ячеек «5», а максимальное выбирается автоматически. Как видно в примере, в блоках, где число меньше пяти: 4, -7, -8, или равно ему гистограмма просто не отображается.

Условное форматирование в Excel с использованием формул

Если стандартных правил недостаточно, пользователь может применить формулу. Практически любую: возможности данного инструмента безграничны. Рассмотрим простой вариант.

Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.

Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:

Для закрытия окна и отображения результата – ОК.

Формат числа в Excel

В конце расскажем, как быстро установить числовой формат в Excel. Иногда ваше число вдруг превращается в текст или дату. Если это произошло, преобразуйте значение в нужный формат.

Установите указатель на ячейку с числом и нажмите квадратик в правом нижнем углу панели «Число» вкладки «Главная».

В окне выберите нужное значение и установите число десятичных знаков. Также можно выбрать, как будет отображаться отрицательное число.

Галочка «Разделитель групп разрядов» позволит устанавливать пробел между разрядами числа.

Также вы можете установить денежный или финансовый формат.

Используйте условное форматирование для придания наглядности вашим таблицам. Это ускорит анализ и обработку данных.

Отличного Вам дня!

Как выделить все ячейки листа Excel

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

  • Кликнуть мышью в том месте листа, где сходятся номера строк и координаты столбцов (см. рисунок). Весь лист сразу же будет выделен
  • Нажать Ctrl+A. Правда, здесь нужно быть внимательными. Если активная ячейка находится в группе данных, то сначала будет выделена только эта группа. Чтобы выделить весь лист — нажмите Ctrl+A повторно!

Выделить строки и столбцы полностью

Если нужно выделить весь столбец или строку – вот несколько вариантов выполнения:

  • Кликните мышью на номере строки или имени столбца. Если нужно выбрать несколько смежных строк – зажмите левую кнопку мыши на номере строки и тяните выделение на те строки, которые нужно выделить. Аналогично поступаем со столбцами
  • Поставьте курсор в любую ячейку строки, которую нужно выделить и нажмите комбинацию Shift+Пробел. Для выделения столбца пользуйтесь комбинацией Ctrl+Пробел
  • Чтобы выделить несмежные строки и столбцы – зажмитеCtrl и кликайте по именам столбцов и номерам строк. После завершения – отпустите Ctrl.

Добавление условного форматирования в Excel позволяет применять различные параметры форматирования к ячейке или диапазону ячеек, которые соответствуют определенным условиям, которые вы установили. Параметры форматирования применяются только тогда, когда выбранные ячейки отвечают этим установленным условиям.

Параметры форматирования, которые могут быть применены, включают изменения цвета шрифта и фона, стили шрифтов, границы ячеек и добавление форматирования чисел к данным.

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

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

01 из 04

Применение нескольких правил в Excel

Для одних и тех же данных можно применять более одного правила для проверки различных условий. Например, в бюджетных данных могут быть установлены условия, которые применяют форматирование изменений, когда расходуются определенные уровни, такие как 50%, 75% и 100% от общего бюджета.

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

Поиск данных, превышающих 25% и 50%

В следующем примере два специальных правила условного форматирования будут применяться к диапазону ячейки B2 в B5.

  • Первое правило проверяет, были ли данные в ячейки A2 в A5 больше соответствующего значения в Би 2 в B5 более чем на 25%.
  • Второе правило проверяет, не совпадают ли те же данные в A2: A5 превышает соответствующее значение в B2: B5 более чем на 50%.

Как видно на изображении выше, если выполнено одно из указанных выше условий, цвет фона ячейки или клеток в диапазоне B1: B4 изменится.

  • Для данных, где разница превышает 25%, цвет фона ячейки изменится на зеленый.
  • Если разница превышает 50%, цвет фона ячейки изменится на красный.

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

в третий шаг учебника добавят формулы к ячейкам С2: С4 которые показывают точную процентную разницу между значениями в ячейках A2: A5 а также B2: B5 чтобы проверить точность условных правил форматирования.

02 из 04

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

Как уже упоминалось, условные правила форматирования, проверяющие два условия, будут введены с использованием условного форматирования Новое правило форматирования диалоговое окно. Начнем с установки пользовательского условного форматирования:

Условное форматирование для поиска более 25 процентов увеличения

= (A2-B2) / A2> 25%

  1. основной момент ячейки B2 в B5 на листе.
  2. Нажми на Главная вкладка из лента.
  3. Нажми на Условное форматирование значок в лента чтобы открыть раскрывающийся список.
  4. выберите Новое правило для открытия Новое правило форматирования диалоговое окно.
  5. От Стиль: , выберите классический вариант.
  6. Во втором раскрывающемся списке нажмите на последнюю опциюИспользуйте формулу, чтобы определить, какие ячейки нужно форматировать.
  7. Введите формула приведенные выше в предоставленное пространство.
  8. Нажми на Формат с: выпадающий список, затем выберите Светло-красный наполнить темно-красным текстом вариант.
  9. Нажмите Хорошо закрыть диалоговые окна и вернуться на рабочий лист.
  10. На этом этапе цвет фона ячейки B3 а также B5 должен быть зеленым.

Условное форматирование для поиска более 50 процентов увеличения

= (A2-B2) / A2> 50%

основной момент ячейки B2 в B5 на листе.

  1. Нажми на Главная вкладка из лента.
  2. Нажми на Условное форматирование значок в лента чтобы открыть раскрывающийся список.
  3. выберите Новое правило для открытия Новое правило форматирования диалоговое окно.
  4. От Стиль: , выберите классический вариант.
  5. Во втором раскрывающемся списке нажмите на последнюю опциюИспользуйте формулу, чтобы определить, какие ячейки нужно форматировать.
  6. Введите формула приведенные выше в предоставленное пространство.
  7. Нажми на Формат с: выпадающий список, затем выберите Светло-зеленый Заполнить темным зеленым текстом вариант.
  8. Нажмите Хорошо закрыть диалоговые окна и вернуться на рабочий лист.

Цвет фона ячейка B3 должен все же быть зеленым, указывая, что процентная разница между числами в ячейки A3 а также B3 составляет более 25 процентов, но меньше или равно 50 процентам. Цвет фона ячейка B5 следует изменить на красный, указав, что процентная разница между числами в ячейки A5 а также B5 составляет более 50 процентов.

03 из 04

Проверка правил условного форматирования

Чтобы проверить правильность введенных правил форматирования, мы можем вводить формулы в ячейки С2: С5 который рассчитает точную процентную разницу между числами в диапазонах A2: A5 а также B2: B5.

= (A2-B2) / A2

  1. Нажмите на ячейка C2 чтобы сделать его активной ячейкой.
  2. Введите приведенную выше формулу и нажмите Войти на клавиатуре.
  3. Ответ 10% должен появиться в ячейка C2, указывая, что число в ячейка A2 на 10% больше, чем число в ячейка B2.
  4. Возможно, потребуется изменить форматирование на ячейка C2 чтобы отобразить ответ в процентах.
  5. Использовать ручка заполнения скопировать формулу из ячейка C2 в ячейки C3 в C5.
  6. Ответы для ячейки C3 в C5 должен составлять 30%, 25% и 60%.

Ответы в этих ячейках показывают, что созданные условные правила форматирования верны, поскольку разница между ячейки A3 а также B3 превышает 25 процентов, а разница между ячейки A5 а также B5 составляет более 50 процентов.

Cell B4 не изменил цвет, поскольку разница между ячейки A4 а также B4 равен 25 процентам, и в нашем правиле условного форматирования указано, что для цвета фона для зеленого цвета требуется процент больше 25%.

04 из 04

Порядок приоритета для условного форматирования

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

В примере, используемом в этом руководстве, конфликты правил, поскольку оба правила используют один и тот же параметр форматирования — изменение цвета фона.

В ситуации, когда второе правило истинно (разница в значении больше 50 процентов между двумя ячейками), то верно и первое правило (разница в значении больше 25 процентов).

Приказ Excel о приоритете

Поскольку ячейка не может одновременно иметь как красный, так и зеленый фон, Excel должен знать, какое условное правило форматирования должно применяться.

Применяемое правило определяется порядком приоритета Excel, в котором указано, что правило, которое выше в списке в диалоговом окне «Диспетчер правил условного форматирования» имеет приоритет.

Как показано на рисунке выше, второе правило, используемое в этом руководстве, выше в списке и, следовательно, имеет приоритет над первым правилом. В результате цвет фона ячейки B5 изменяется на красный.

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

Применение неконфликтных правил

Если два или более условных правила форматирования не конфликтуют, оба применяются, когда условие, которое каждое правило тестирует, становится истинным.

Если первое правило условного форматирования в нашем примере отформатировало диапазон ячеек B2: B5 с зеленой рамкой вместо зеленого цвета фона, два условных правила форматирования не будут конфликтовать, так как оба формата могут применяться без вмешательства в другое.

В следствии, ячейка B5 будет иметь как зеленую границу, так и красный цвет фона, поскольку разница между числами в ячейки A5 а также B5 больше, чем 25 и 50 процентов.

Условное форматирование и регулярное форматирование

В случае конфликтов между условными правилами форматирования и параметрами форматирования вручную применяются правила условного форматирования всегда имеют приоритет и будут применяться вместо любых добавленных вручную параметров форматирования.

Если желтый цвет фона был первоначально применен к ячейки B2 в B5 в примере, как только были добавлены условные правила форматирования, только ячейки B2 а также B4 будет оставаться желтым. Поскольку введенные правила условного форматирования применяются к ячейкам B3 а также B5, их цвета фона будут меняться от желтого до зеленого и красного соответственно.

Рассмотрим условное форматирование в Excel 2016 на примерах

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

Курсором выбираем диапазон всех 10 ячеек, затем на вкладке Главная переходим Условное форматирование -> Правила выделения ячеек -> Текст содержит

Выбираем ячейку с искомым текстом, а также из списка выбираем цвет красный.

Теперь рассмотрим ещё один пример условного форматирования чисел. Для удобства воспользуемся формулой датчика случайных целых чисел от 1 до 100.

=ЦЕЛОЕ(СЛЧИС()*100)

Опять же выделим курсором диапазон чисел.

На вкладке Главная переходим Условное форматирование -> Создать правило

Появляется окно создание правила форматирование, выбираем формировать все ячейки на основании их значений, выбираем стиль формата — двухцветная шкала, затем выбираем цвет от темно-зелёного для минимальных значений до светло-зелёного для максимальных значений.

В результате получается следующая таблица.

Также можно создать правило для значений больше или меньше какой-нибудь величины, например возьмём столбец с разными значениями чисел и пометим желтым цветом числа, которые больше 50.

Переходим Условное форматирование -> Правила выделения ячеек -> Больше

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

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

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

Рассмотрим 2 примера.

1. Выделение цветом всей строки

Необходимо выделить цветом те строки таблицы, в которых в столбце «В» значения больше 5000.

Для этого:

1.Выделите таблицу, к которой будет применяться Условное форматирование

2. Выберите в меню Формат — Условное форматирование

3. В открывшемся Окне задайте условие:

  • Выберите — «формула»

4. Выберите необходимый формат для выделения строки

5. Нажмите ОК

Теперь зеленым цветом будут выделены все строки, в которых в столбце «В» стоит значение большее 5000.

Главный нюанс заключается в знаке доллара ($) перед буквой столбца в адресе — он фиксирует столбец, оставляянезафиксированной ссылку на строку- проверяемые значения берутся из столбца указанного в формуле, по очереди из каждой последующей строки.

2. Проверка дат и сроков

Поскольку даты в Excel представляют собой те же числа (один день = 1), то можно легко использовать условное форматирование для проверки сроков оплат или выполнения задач.

Например, необходимо выделить даты просроченных оплат красным цветом, а тех, что предстоят в ближайшую неделю – желтым.

Для этого:

1. Выделите таблицу, к которой будет применяться Условное форматирование

2. Выберите в меню Формат — Условное форматирование

3. Задайте условие 1:

  • выберите — «формула»
  • выберите необходимый формат

4. Нажмите «А также >>» для ввода второго условия

5. Задайте условие 2:

  • выберите — «формула»
  • введите формулу =(B2-$D$1) < 7 , (разница между датами меньше 7 дней)
  • выберите необходимый формат

6. Нажмите ОК

Внимание! В формуле адрес ячейки с текущей датой нужно зафиксировать, т.е. поставить знаки $ до и после буквы обозначающей столбец (в данном примереэто $D$).

—> —> —> —> Доступ к документам КонсультантПлюс на 3 дня БЕСПЛАТНО Заказать

Ближайшее обучение

29июня Вебинар: КонсультантПлюс для юриста

все обучение

Ближайшие семинары

22июля Банковский контроль: «почему именно мне и так больно»?VIP-семинар (Онлайн)

Климова Марина Аркадьевна

25июня Увольняем работника: по собственному желанию и без Вебинар-тренинг

Чернышева Людмила Борисовна

все семинары и вебинары

Советы по работе с программой Excel

Оцените статью
Рейтинг автора
5
Материал подготовил
Илья Коршунов
Наш эксперт
Написано статей
134
А как считаете Вы?
Напишите в комментариях, что вы думаете – согласны
ли со статьей или есть что добавить?
Добавить комментарий