Условное форматирование – удобный инструмент для анализа данных и наглядного представления результатов. Умение им пользоваться сэкономит массу времени и сил. Достаточно бегло взглянуть на документ – нужная информация получена.
Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».
При нажатии на стрелочку справа открывается меню для условий форматирования.
Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».
Введем в диапазон А1:А11 ряд чисел:
Выделим диапазон значений. Открываем меню «Условного форматирования». Выбираем «Правила выделения ячеек». Зададим условие, например, «больше».
Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:
Выходим из меню нажатием кнопки ОК.
Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.
Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» - «Меньше»).
Результат форматирования сразу виден на листе Excel.
Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.
Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.
Выделим исходный диапазон (А1:А11). Нажмем «УФ» - «Правила выделения ячеек» - «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ! , а не абсолютная.
Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.
Внимание! При использовании относительных ссылок нужно следить, какая ячейка была активна в момент вызова инструмента «Условного формата». Так как именно к активной ячейке «привязывается» ссылка в условии.
В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.
Сравните:
Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.
Проверить правильность заданного условия можно следующим образом:
В открывшемся окне видно, какое правило и к какому диапазону применяется.
Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.
Заполняем параметры форматирования по первому условию:
Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.
Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».
То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).
Выделяем диапазон с датами.
Применим к нему «УФ» - «Дата».
В открывшемся окне появляется перечень доступных условий (правил):
Выбираем нужное (например, за последние 7 дней) и жмем ОК.
Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).
Если стандартных правил недостаточно, пользователь может применить формулу. Практически любую: возможности данного инструмента безграничны. Рассмотрим простой вариант.
Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.
Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:
Для закрытия окна и отображения результата – ОК.
Задача: выделить цветом строку, содержащую ячейку с определенным значением.
Таблица для примера:
Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).
Выделяем диапазон со значениями таблицы. Нажимаем «УФ» - «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.
Порядок заполнения условий для форматирования «завершенных проектов»:
Аналогично задаем правила форматирования для незавершенных проектов.
В «Диспетчере» условия выглядят так:
Получаем результат:
Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».
«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.
Условное форматирование помогает обеспечивать наглядность при изучении и анализе данных, обнаружении критических проблем, выявлении шаблонов и тенденций.
С помощью условного форматирования можно легко выделять необходимые ячейки или диапазоны, подчеркивать необычные значения и визуализировать данные с помощью гистограмм, цветовых шкал и наборов значков.
При применении условного форматирования внешний вид ячеек изменяется на основании указанных вами условий. Если условия выполнены, то форматирование применяется к диапазону ячеек. Если условия не выполнены, форматирование не применяется. Существует множество встроенных условий, а также вы можете создавать собственные условия (в том числе и с помощью формулы, результатом которой становится значение "Истина" или "Ложь").
|
Условное форматирование месячной записи данных высокой температуры для разных мест, цвета интуитивно соответствуют значениям (значения более высокой температуры - оранжевые и красные, значения низкой температуры - желтые и зеленые) |
|
|
Условное форматирование с использованием цвета фона ячеек для выделения различных категорий продуктов; значок с тремя стрелками показывает тенденции стоимости (повышение, сохранение, снижение), а гистограммы отображают разницу в повышении стоимости продуктов. |
|
Условное форматирование можно применить к диапазону ячеек (выделенному или именованному), к таблице Excel и даже к отчету сводной таблицы в Excel для Windows. Обратите внимание на ряд дополнительных факторов, действующих на условное форматирование в отчете сводной таблицы - см. раздел Применение условного форматирования в отчете сводной таблицы ниже на вкладке Windows.
Скачивание образца книги
Вы можете скачать образец книги, содержащий различные примеры применения условного форматирования с помощью стандартных правил, таких как сверху и снизу, повторяющиеся данные, гистограммы, наборы значков и цветовые шкалы, а также собственных правил, созданных вручную.
Форматирование ячеек с помощью двухцветной шкалы
Цветовые шкалы - это визуальные элементы, которые помогают понять распределение и разброс данных. Двухцветная шкала помогает сравнить диапазон ячеек с помощью градации двух цветов. Уровень яркости цвета соответствует высоким, средним или низким значениям. Например, в желто-зеленой шкале, показанной ниже, можно указать, что ячейки с высокими значениями будут более зелеными, а ячейки с низкими значениями - более желтыми.
Совет: ЕСТЬ или ЕСЛИОШИБКА для возврата значения, отличного от ошибки.
Быстрое форматирование
Параметры форматирования
Расширенное форматирование
На вкладке Главная в группе Стили и выберите пункт Управление правилами . Откроется диалоговое окно .
Создать правило . Откроется диалоговое окно .
Свернуть диалоговое окно в поле Применяется к Развернуть диалоговое окно .
Изменить правило . Откроется диалоговое окно .
В разделе Применить правило
к выделенным ячейкам , чтобы выбрать ячейки по выделению;
<метка значения> , чтобы выбрать ячейки с определенной меткой.
ко всем ячейкам, содержащим значения <метка значения> для <название строки> , чтобы выбрать ячейки с определенной меткой, кроме промежуточных и общих итогов.
В разделе Выберите тип правила выберите пункт (по умолчанию).
В группе Измените описание правила в поле со списком Стиль формата выберите пункт Двухцветная шкала .
В полях Тип параметров Минимальное значение и Максимальное значение выберите их типы.
Выберите минимальное значение и максимальное значение .
В этом случае не вводите минимальное и максимальное значения .
Выберите элемент Число , а затем введите минимальное и максимальное значения .
Введите минимальное и максимальное значения .
Форматирование процентиля. Выберите элемент Процентиль Минимальное значение и Максимальное значение
Используйте процентиль, если необходимо визуализировать группу высоких значений (например, верхнюю 20ю процентиль) с одним оттенком, а группу низких значений (например, нижнюю 20ю процентиль) - с другим, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.
Выберите элемент Формула , а затем введите минимальное и максимальное значения.
Примечания:
Убедитесь, что минимальное значение меньше, чем максимальное .
Для параметров Минимальное значение и Максимальное значение минимального значения и процентный тип для максимального значения .
Чтобы выбрать параметры Минимальное значение и Максимальное значение цветовой шкалы, щелкните элемент Цвет для каждого значения, а затем выберите цвет.
Другие цвета . Выбранная цветовая шкала появится в окне предварительного просмотра .
Форматирование ячеек с помощью трехцветной шкалы
Цветовые шкалы - это визуальные элементы, которые помогают понять распределение и разброс данных. Трехцветная шкала помогает сравнить диапазон ячеек путем использования градации трех цветов. Уровень яркости цвета соответствует высоким, средним или низким значениям. Например, для красно-желто-зеленой шкалы можно указать, что ячейки с высокими значениями будут зелеными, ячейки со средними значениями - желтыми, а ячейки с низкими значениями - красными.
Быстрое форматирование
Вы можете изменить способ определения области для полей из области "Значения" в отчете сводной таблицы с помощью кнопки Параметры форматирования рядом с полем сводной таблицы, к которому применено условное форматирование.
Расширенное форматирование
Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой и выберите пункт Управление правилами . Откроется диалоговое окно Диспетчер правил условного форматирования .
Выполните одно из указанных ниже действий.
Чтобы добавить условное форматирование, нажмите кнопку Создать правило . Откроется диалоговое окно .
Для изменения условного форматирования выполните указанные ниже действия.
Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно .
Выберите правило, а затем нажмите кнопку Изменить правило . Откроется диалоговое окно Изменение правила форматирования .
В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
Выберите только эти ячейки ;
Выберите все ячейки <поле значения> с теми же полями ;
Выберите все ячейки <поле значения> .
В группе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений .
В группе Измените описание правила в поле со списком Стиль формата выберите пункт Трехцветная шкала .
Выберите тип параметров Минимальное значение , Среднее значение и Максимальное значение
Форматирование минимального и максимального значений. Выберите элемент Среднее значение .
В этом случае не вводите минимальное и максимальное значения .
Форматирование числового значения, значения даты или времени. Выберите элемент Число , а затем введите минимальное , среднее и максимальное значения.
Форматирование процентного значения. Выберите элемент Процент , а затем введите минимальное , среднее и максимальное значения. Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента.
Форматирование процентиля. Выберите элемент Процентиль , а затем введите Минимальное значение , Среднее значение и Максимальное значение .
Используйте процентиль, если необходимо визуализировать группу высоких значений (например, верхнюю 20юпроцентиль) с одним оттенком, а группу низких значений (например, нижнюю 20юпроцентиль) - с другим, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.
Форматирование результата формулы. Выберите элемент Формула , а затем введите минимальное , среднее и максимальное значения.
Формула должна возвращать число, дату или время. Начинайте ввод формулы со знака равенства (= ). Недопустимая формула не позволит применить форматирование. Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.
Примечания:
Можно задать минимальное, среднее и максимальное значения для диапазона ячеек. Убедитесь, что минимальное значение меньше среднего , а оно, в свою очередь, меньше максимального .
Для параметров Минимальное значение , Среднее значение и Максимальное значение можно выбрать разные типы. Например, можно выбрать числовой тип для минимального значения , процентиль для среднего значения и процентный тип для максимального значения .
Во многих случаях среднее значение по умолчанию, равное 50 процентам, оптимально, но его можно изменить с учетом конкретных требований.
Для выбора цветовой шкалы Минимальное значение , Среднее значение и Максимальное значение щелкните элемент Цвет для каждого из них, а затем выберите цвет.
Чтобы выбрать дополнительные цвета или создать пользовательский цвет, нажмите кнопку Другие цвета .
Выбранная цветовая шкала появится в окне предварительного просмотра .
Форматирование ячеек с помощью гистограмм
Гистограммы помогают рассмотреть значение в ячейке относительно других ячеек. Длина гистограммы соответствует значению в ячейке. Чем она длиннее - тем больше значение. Гистограммы оптимальны при определении основных показателей, особенно в больших объемах данных - например, максимального и минимального объема продаж игрушек в отчете о праздничных продажах.
В показанном здесь примере используются гистограммы для выделения крайних положительных и отрицательных значений. Гистограммы можно отформатировать, чтобы они начинались с середины ячейки и растягивались влево до отрицательных значений.
Совет: Если какая-либо из ячеек в диапазоне содержит формулу, возвращающую ошибку, условное форматирование к ней не применяется. Чтобы гарантировать применение условного форматирования к этим ячейкам, воспользуйтесь функцией ЕСТЬ или ЕСЛИОШИБКА
Быстрое форматирование
Расширенное форматирование
Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой и выберите пункт Управление правилами . Откроется диалоговое окно Диспетчер правил условного форматирования .
Выполните одно из указанных ниже действий.
Чтобы добавить условное форматирование, нажмите кнопку Создать правило . Откроется диалоговое окно .
Для изменения условного форматирования выполните указанные ниже действия.
Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно .
Выберите правило, а затем нажмите кнопку Изменить правило . Откроется диалоговое окно Изменение правила форматирования .
В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
чтобы выбрать поля по выделению, выберите только эти ячейки ;
чтобы выбрать поля по соответствующему полю, выберите все ячейки <поле значения> с теми же полями ;
чтобы выбрать поля по полю значения, выберите все ячейки <поле значения> .
В группе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений .
В группе Измените описание правила в поле со списком Стиль формата выберите пункт Гистограмма .
Выберите значения Минимальное и Максимальное для типа . Выполните одно из указанных ниже действий.
Форматирование минимального и максимального значений. Выберите Минимальное значение и Максимальное значение .
В этом случае не вводите Минимальное значение и Максимальное значение .
Форматирование числового значения, значения даты или времени. Выберите элемент Число , а затем введите минимальное и максимальное значения .
Форматирование процентного значения. Выберите элемент Процент , а затем введите минимальное и максимальное значения.
Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента.
Используйте процентные значения, если необходимо визуализировать все значения пропорционально, поскольку распределение значений в этом случае является пропорциональным.
Форматирование процентиля. Выберите элемент Процентиль , а затем введите значения параметров Минимальное значение и Максимальное значение .
Допустимыми являются значения процентилей от 0 (нуля) до 100.
Используйте процентили, если необходимо визуализировать группу высоких значений (например, верхнюю 20ю процентиль) в одной пропорции гистограммы и группу низких значений (например, нижнюю 20ю процентиль) - в другой, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.
Форматирование результата формулы. Выберите элемент Формула , а затем введите значения параметров Минимальное значение и Максимальное значение .
Формула должна возвращать число, дату или время.
Начинайте ввод формулы со знака равенства (= ).
Недопустимая формула не позволит применить форматирование.
Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.
Примечания:
Убедитесь, что минимальное значение меньше, чем максимальное .
Для параметров Минимальное значение и Максимальное значение можно выбрать разные типы. Например, можно выбрать числовой тип для минимального значения и процентный тип для максимального значения .
Для выбора цветовых шкал Минимальное значение и Максимальное значение выберите пункт Цвет столбца .
Если необходимо выбрать дополнительные цвета или создать пользовательский цвет, нажмите кнопку Другие цвета . Выбранный цвет столбца появится в окне предварительного просмотра .
Для отображения только гистограммы, но не значения в ячейке установите флажок Показывать только столбец .
Чтобы применить к столбцам гистограммы сплошную границу, выберите в списке Граница вариант Сплошная граница , а затем выберите цвет границы.
Чтобы сделать столбцы сплошными или градиентными, выберите в списке Заливка вариант Сплошная заливка или Градиентная заливка .
Чтобы задать формат столбцов для отрицательных значений, щелкните элемент Отрицательные значения и ось , а затем в диалоговом окне Настройка отрицательных значений и оси настройте параметры заливки и цвета границы для столбцов, соответствующих отрицательным значениям. Для оси можно настроить расположение и цвет. Выбрав параметры, нажмите кнопку ОК .
Направление гистограммы можно задать в поле Направление столбца . По умолчанию там выбрано значение по контексту , однако можно выбрать направление слева направо или справа налево в зависимости от того, как требуется представить данные.
Форматирование ячеек с помощью набора значков
Набор значков используется для аннотирования и классификации данных по трем-пяти категориям, разделенным пороговым значением. Каждый значок соответствует диапазону значений. Например, в наборе значков 3 стрелки красная стрелка вверх соответствует высоким значениям, желтая стрелка, направленная в сторону, соответствует средним значениям, а зеленая стрелка вниз соответствует низким значениям.
В показанном здесь примере в условном форматировании используются несколько наборов значков.
Вы можете настроить показ значков только для ячеек, соответствующих определенным условиям (например, показ значка предупреждения для ячеек, значения которых находятся ниже критического уровня, и отсутствие значка для ячеек, превышающих этот уровень). Чтобы сделать это, при настройке условий скройте ячейки, выбрав вариант Нет значка ячейки в раскрывающемся списке напротив нужного значка. Кроме того, можно создавать собственные наборы значков, например зеленую "галочку", желтый "светофор" и красный "флажок".
Совет: Если какие-либо из выделенных ячеек содержат формулу, возвращающую ошибку, условное форматирование не применяется к этим ячейкам. Чтобы гарантировать применение условного форматирования к этим ячейкам, воспользуйтесь функцией ЕСТЬ или ЕСЛИОШИБКА для возврата значения (например, 0 или "Н/Д"), отличного от ошибки.
Быстрое форматирование
Вы можете изменить способ определения области для полей из области "Значения" в отчете сводной таблицы с помощью переключателя Применить правило форматирования к .
Расширенное форматирование
На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой и выберите пункт Управление правилами . Откроется диалоговое окно Диспетчер правил условного форматирования .
Выполните одно из указанных ниже действий.
Чтобы добавить условное форматирование, нажмите кнопку Создать правило . Откроется диалоговое окно .
Для изменения условного форматирования выполните указанные ниже действия.
Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно .
Выберите правило, а затем нажмите кнопку Изменить правило . Откроется диалоговое окно Изменение правила форматирования .
В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
чтобы выбрать поля по выделению, выберите только эти ячейки ;
чтобы выбрать поля по соответствующему полю, выберите все ячейки <поле значения> с теми же полями ;
чтобы выбрать поля по полю значения, выберите все ячейки <поле значения> .
В группе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений .
В разделе Измените описание правила в списке Формат стиля выберите пункт Набор значков .
Выберите набор значков. По умолчанию используется набор 3 сигнала светофора без обрамления . Число значков, операторы сравнения, заданные по умолчанию, и пороговые значения для каждого значка могут быть различны для разных наборов значков.
Вы можете изменить операторы сравнения и пороговые значения. Размеры диапазонов значений по умолчанию для всех значков одинаковы, но их можно изменить в соответствии с конкретными требованиями. Убедитесь, что пороговые значения находятся в логической последовательности - от высших к низшим, сверху вниз.
Выполните одно из указанных ниже действий.
Форматирование числового значения, значения даты или времени. Выберите элемент Число .
Форматирование процентного значения. Выберите элемент Процент .
Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента.
Используйте процентные значения, если необходимо визуализировать все значения пропорционально, поскольку распределение значений в этом случае является пропорциональным.
Форматирование процентиля. Выберите элемент Процентиль . Допустимыми являются значения процентилей от 0 (нуля) до 100.
Используйте процентили, если необходимо визуализировать группу максимальных значений (например, верхнюю 20ю процентиль) с помощью одного значка, а группу минимальных значений (например, нижнюю 20ю процентиль) - с помощью другого, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.
Форматирование результата формулы. Выберите элемент Формула , а затем введите формулы в каждое поле Значение .
Формула должна возвращать число, дату или время.
Начинайте ввод формулы со знака равенства (=).
Недопустимая формула не позволит применить форматирование.
Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.
Чтобы первый значок соответствовал меньшим значениям, а последний - большим, выберите параметр Обратный порядок значков .
Для отображения только значка, но не значения в ячейке, выберите параметр Показать только значок .
Примечания:
Может потребоваться скорректировать ширину столбцов, чтобы они могли вместить значки.
Отображаемый размер значка зависит от размера шрифта, используемого в данной ячейке. При увеличении размера шрифта пропорционально увеличивается и размер значка.
Форматирование ячеек, содержащих текст, числовые значения, дату или время
Чтобы удобнее находить определенные ячейки, можно отформатировать их с помощью оператора сравнения. Например, в инвентарном перечне, отсортированном по категориям, можно выделить желтым цветом товары, которых осталось менее 10 штук. В сводном листе розничного магазина можно выделить все магазины с доходом более 10 %, с объемом продаж менее 100 000 долларов и с регионом "Юго-восток".
В приведенных здесь примерах демонстрируются встроенные критерии условного форматирования, такие как "больше чем" и "%_лучших". Города с численностью населения более 2 000 000 выделяются зеленым цветом фона, а 30 % лучших (самых высоких) показателей среднемесячной максимальной температуры - оранжевым.
Примечание: Невозможно применить условное форматирование к полям в области "Значение" отчета сводной таблицы по тексту или по дате (можно только по числам).
Быстрое форматирование
Вы можете изменить способ определения области для полей из области "Значения" в отчете сводной таблицы с помощью переключателя Применить правило форматирования к .
Если вы хотите посмотреть видео об этих методах, см. Видео: условное форматирование текста и Видео: условное форматирование дат .
Расширенное форматирование
Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования .
Выполните одно из указанных ниже действий.
Чтобы добавить условное форматирование, нажмите кнопку Создать правило . Откроется диалоговое окно .
Для изменения условного форматирования выполните указанные ниже действия.
Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к Развернуть диалоговое окно .
Выберите правило, а затем нажмите кнопку Изменить правило . Откроется диалоговое окно Изменение правила форматирования .
В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
чтобы выбрать поля по выделению, выберите только эти ячейки ;
чтобы выбрать поля по соответствующему полю, выберите все ячейки <поле значения> с теми же полями ;
чтобы выбрать поля по полю значения, выберите все ячейки <поле значения> .
В списке Выберите тип правила выберите пункт Форматировать только ячейки, которые содержат .
В группе Измените описание правила в поле со списком Форматировать только ячейки, для которых выполняется следующее условие выполните одно из указанных ниже действий.
Чтобы задать формат, нажмите кнопку Формат . Отобразится диалоговое окно Формат ячеек .
Выберите формат чисел, шрифта, рамки или заполнения, который необходимо применять, если значение в ячейке соответствует условию, а затем нажмите кнопку ОК .
Можно выбрать несколько форматов. Выбранные форматы появятся в окне предварительного просмотра .
Быстрое форматирование
Вы можете изменить способ определения области для полей из области "Значения" в отчете сводной таблицы с помощью переключателя Применить правило форматирования к .
Расширенное форматирование
Быстрое форматирование
Вы можете изменить способ определения области для полей из области "Значения" в отчете сводной таблицы с помощью переключателя Применить правило форматирования к .
Расширенное форматирование
Форматирование только уникальных или повторяющихся значений
Примечание: Применить условное форматирование к полям в области "Значения" отчета сводной таблицы по уникальным или повторяющимся значениям невозможно.
В приведенном здесь примере условное форматирование применяется в столбце "Преподаватель" для выделения преподавателей, ведущих более одного предмета (повторяющиеся имена выделяются розовым цветом). Значения оценок, которые встречаются в столбце "Оценка" лишь один раз, выделяются зеленым цветом.
Быстрое форматирование
Расширенное форматирование
Создание собственных правил условного форматирования
Если ни один из перечисленных выше вариантов вам не подходит, вы можете создать собственное правило условного форматирования с помощью нескольких простых действий.
Выбор ячеек для форматирования с помощью формулы
Если при создании собственного правила условного форматирования отсутствуют нужные варианты, можно использовать логическую формулу, чтобы задать условия форматирования. Например, можно сравнить значения в выбранных ячейках с результатом, возвращенным функцией, или оценить данные в ячейках за пределами выделенного диапазона, которые могут находиться на другом листе этой же книги. Формула должна возвращать значение Истина или Ложь (1 или 0), но вы можете использовать условную логику для объединения набора соответствующих условных форматов, таких как различные цвета для каждого из небольших наборов текстовых значений (например, названия категорий товаров).
Примечание: В формулу можно вводить ссылки на ячейки. Для этого выделите ячейки непосредственно на листе или других листах. При выделении ячеек на листах создаются абсолютные ссылки на ячейки. Чтобы приложение Excel корректировало ссылки на ячейки в выделенном диапазоне, используйте относительные ссылки. Дополнительные сведения см. в разделах Создание и изменение ссылки на ячейку и Изменение типа ссылки: относительная, абсолютная, смешанная .
Совет: Если какие-либо ячейки содержат формулу, возвращающую ошибку, условное форматирование не применяется к таким ячейкам. Для решения этой проблемы используйте в формуле функции ЯВЛЯЕТСЯ или функцию ЕСЛИОШИБКА , чтобы возвращать указанное вами значение (например, 0 или "Н/П") вместо значения ошибки.
На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой и выберите пункт Управление правилами .
Откроется диалоговое окно Диспетчер правил условного форматирования .
Выполните одно из указанных ниже действий.
Чтобы добавить условное форматирование, нажмите кнопку Создать правило . Откроется диалоговое окно .
Для изменения условного форматирования выполните указанные ниже действия.
Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно, а затем выделите новый диапазон ячеек на этом или других листах и нажмите кнопку Развернуть диалоговое окно .
Выберите правило, а затем нажмите кнопку Изменить правило . Откроется диалоговое окно Изменение правила форматирования .
В разделе Применить правило выполните одно из следующих действий, чтобы изменить выбор полей в области значений отчета сводной таблицы:
Чтобы определить область по выделенному фрагменту , щелкните Выделенные ячейки .
Чтобы определить область по соответствующему полю , щелкните ко всем ячейкам, содержащим значения <поле значения> .
Чтобы определить область по полю "Значение" , выберите ко всем ячейкам, содержащим значения <поле значения> для <строка> .
В группе Выберите тип правила нажмите кнопку Использовать формулу для определения форматируемых ячеек .
В следующем видео показаны основы использования формул с условным форматированием.
Копирование и вставка условного форматирования
Если хотите применить стиль условного форматирования к новым или другим данным на листе, можно скопировать его с помощью команды Формат по образцу .
Примечание: Если вы использовали формулу в правиле, которое применяет условное форматирование, после вставки такого форматирования может потребоваться настроить ссылки на ячейки в этой формуле. Дополнительные сведения об этом см. в статье Переключение между относительными, абсолютными и смешанными ссылками .
Поиск ячеек с условным форматированием
Если лист содержит условное форматирование, можно быстро найти эти ячейки для копирования, изменения или удаления условного формата. Для поиска ячеек с определенным условным форматированием или всех ячеек с условным форматированием используйте команду Выделить группу ячеек .
Поиск всех ячеек с условным форматированием
Поиск ячеек с одинаковым условным форматированием
Управление правилами условного форматирования
При использовании условного форматирования вы задаете правила, с помощью которых Excel определяет, когда следует применять условное форматирование. Для управления этими правилами следует понимать последовательность, в которой эти правила проверяются, и действия в случае конфликта нескольких из них. Также следует понимать, как копирование и вставка влияют на проверку правил, следует знать, как изменять последовательность этой проверки и когда ее следует останавливать.
Диспетчер правил условного форматирования . (Для этого на вкладке Главная выберите команду и выберите пункт Управление правилами .)
Откроется диалоговое окно Диспетчер правил условного форматирования .
При применении нескольких правил условного форматирования приоритет их проверки определяется порядком их расположения в этом диалоговом окне (сверху вниз).
СЕГОДНЯ =B2<СЕГОДНЯ () Применяется к =B2<СЕГОДНЯ()+60
Вверх и Вниз .
Иногда к диапазону ячеек можно применить несколько правил условного форматирования. Вот примеры применения правил: сначала тех, которые не конфликтуют друг с другом, а затем - конфликтующих.
Если какое-либо правило условного форматирования проверяется с результатом "Истина", оно имеет приоритет перед любым установленным вручную форматом для этих же выделенных ячеек. Это означает, что в случае конфликта условное форматирование применяется, а ручное форматирование не применяется. При удалении правила условного форматирования форматирование, вручную примененное к диапазону ячеек, остается.
Диспетчер правил условного форматирования
Управление правилами установить флажок Остановить, если истина
Остановить, если истина
Остановить, если истина для первого правила;
Остановить, если истина для второго правила;
Установить или снять флажок Остановить, если истина
Если нужно посмотреть видео об управлении правилами условного форматирования, см. Видео: управление условным форматированием .
Очистка условного форматирования
Очистка условного форматирования на листе
На вкладке Главная щелкните > Удалить правила > .
Чтобы очистить условное форматирование на листе, следуйте приведенным ниже инструкциям.
На всем листе
На вкладке Главная щелкните > Удалить правила > Удалить правила со всего листа .
В диапазоне ячеек
Поиск и удаление одинакового условного форматирования на листе
Щелкните ячейку с условным форматированием, которое вы хотите удалить со всего листа.
На вкладке Главная щелкните стрелку рядом с кнопкой Найти и выделить и выберите команду Выделить группу ячеек .
Выберите параметр Условные форматы .
Выберите пункт этих же в группе Проверка данных . Будут выделены все ячейки с одинаковыми правилами условного форматирования.
На вкладке Главная нажмите кнопку , выберите пункт Удалить правила , а затем - .
Форматирование только первых или последних значений
Можно найти максимальное и минимальное значения в диапазоне ячеек на основе указанного порогового значения. Например, можно найти 5 самых популярных продуктов в региональном отчете, 15 % наименее популярных продуктов в клиентском опросе или 25 самых высоких зарплат в отчете по персоналу отдела.
Выделите ячейки, к которым необходимо применить условное форматирование.
На вкладке Главная в группе Таблицы выберите .
Выберите нужную команду, например Первые 10 элементов или Последние 10 % .
Введите значения, которые вы хотите использовать, затем выберите формат (цвет заливки, текста или границы) и нажмите кнопку ОК .
Форматирование только значений, находящихся выше или ниже среднего
Можно найти значения выше или ниже среднего значения или стандартного отклонения в диапазоне ячеек. Например, можно найти исполнителей с производительностью выше средней в ежегодном обзоре эффективности работы или определить произведенные материалы, качество которых находятся ниже удвоенного значения стандартного отклонения.
Выделите ячейки, к которым необходимо применить условное форматирование.
На вкладке Главная в группе Таблицы выберите пункт Правила отбора первых и последних значений .
При использовании условного форматирования вы задаете правила, с помощью которых Excel определяет, когда следует применять условное форматирование. Для управления этими правилами важно понимать последовательность, в которой эти правила проверяются, и действия в случае конфликта нескольких из них. Также следует понимать, как копирование и вставка влияют на проверку правил, следует знать, как изменять последовательность этой проверки и когда ее следует останавливать.
Сведения о приоритете обработки правил условного форматирования
Создание, редактирование, удаление и просмотр всех правил условного форматирования в книге выполняются в диалоговом окне Диспетчер правил условного форматирования . (Для этого на вкладке Главная выберите команду и выберите пункт Управление правилами .)
Откроется диалоговое окно Диспетчер правил условного форматирования .
Когда к диапазону ячеек применяются несколько правил условного форматирования, приоритет обработки (сверху вниз) определяется порядком их расположения в этом диалоговом окне.
Ниже приведен пример с датами окончания срока действия для удостоверений личности. Необходимо изменить фон удостоверений, срок действия которых завершается через 60 дней, но еще не завершился, на желтый цвет, а цвет фона удостоверений с завершенным сроком действия - на красный.
В данном примере ячейки с идентификационными номерами сотрудников, у которых даты сертификации истекают через 60 дней, отформатированы желтым, а идентификационные номера сотрудников с истекшим сроком сертификации отформатированы красным (см. рис. ниже).
Первое правило (которое задает красный цвет фона ячейки, если значение - "истина") проверяет значение даты в столбце B напротив текущей даты (вычисленной с помощью функции СЕГОДНЯ в формуле). Формула для этого правила назначается первому значению в столбце B - B2 и выглядит так: =B2<СЕГОДНЯ () . Эта формула проверяет ячейки в столбце B (ячейки B2:B15). Если формула для какой-либо ячейки в столбце B оценивается как "истина", ее соответствующая ячейка в столбце A (например, A5 соответствует B5, а A11 - B11) форматируется красным цветом фона. После оценивания с помощью этого первого правила всех ячеек, указанных в группе Применяется к , проверяется второе правило. Данная формула проверяет, отстают ли значения в столбце B на 60 дней от текущей даты (предположим, сегодняшняя дата - 11.08.2010 г.). Ячейка в столбце B4 "4.10.2010" отстает от сегодняшней даты более чем на 60 дней, поэтому оценивается как "истина" и форматируется желтым цветом фона. Формула для данного правила выглядит так: =B2<СЕГОДНЯ()+60 . Все ячейки, сначала отформатированные красным по правилу с наивысшим приоритетом в списке, остаются без изменений.
Правило, расположенное выше по списку, имеет более высокий приоритет, чем правило, расположенное ниже по списку. По умолчанию новые правила всегда добавляются в начало списка и поэтому обладают более высоким приоритетом, поэтому вам нужно следить за их порядком. Порядок правил можно изменить в диалоговом окне при помощи кнопок со стрелками Вверх и Вниз .
Что происходит при одновременном использовании нескольких правил условного форматирования
К диапазону ячеек можно применить несколько правил условного форматирования. Вот примеры применения правил: сначала тех, которые не конфликтуют друг с другом, а затем - конфликтующих.
Когда конфликта между правилами нет Например, если по одному правилу шрифт в ячейке должен быть полужирным, а по другому правилу цвет фона этой же ячейки должен быть красным, то шрифт в ячейке будет отформатирован как полужирный, а цвет фона ячейки будет красным. В данном случае между двумя форматами нет конфликта, поэтому применяются оба правила.
Когда есть конфликт между правилами Например, согласно одному правилу шрифт в ячейке должен быть красным, а согласно другому правилу - зеленым. В данном случае два правила противоречат друг другу, и применить можно только одно из них. Будет применено правило с более высоким приоритетом (находящееся выше по списку в диалоговом окне).
Влияние операций вставки, заполнения и применения формата по образцу на правила условного форматирования
В процессе редактирования листа проводится копирование и вставка значений ячеек с условными форматами, заполнение диапазона ячеек с условными форматами и применение формата по образцу. Эти действия могут оказывать влияние на приоритет правил условного форматирования: для конечных ячеек создается новое правило условного форматирования на основе исходных ячеек.
При копировании и вставке значений ячеек с условным форматированием в лист, открытый в другом экземпляре Excel (другой процесс Excel.exe одновременно выполняется на компьютере), правило условного форматирования не создается в другом экземпляре и форматирование не копируется в этот экземпляр.
Что происходит при конфликте условного форматирования и форматирования вручную
Если к диапазону ячеек применимо правило форматирования, то оно имеет приоритет над форматированием вручную. Форматирование вручную можно выполнить с помощью кнопок из группы Шрифт на вкладке Главная в Excel 2016 или 2013 либо из группы Ячейки на вкладке Главная в Excel 2010 или 2007. При удалении правила условного форматирования форматирование, вручную примененное к диапазону ячеек, остается.
Форматирование вручную не указывается в диалоговом окне Диспетчер правил условного форматирования и не используется для установки приоритета правил.
Контроль остановки обработки правил с помощью флажка "Остановить, если истина"
Для обеспечения обратной совместимости с более ранними версиями, чем Excel 2007, можно в диалоговом окне Управление правилами установить флажок Остановить, если истина , чтобы имитировать отображение условного форматирования в более ранних версиях Excel, которые не поддерживают применения более трех правил условного форматирования к одному диапазону.
Например, если в более ранних версиях Excel, предшествующих версии Excel 2007, к диапазону ячеек применяется более трех правил условного форматирования, в данной версии Excel:
выполняется оценивание только трех первых правил;
применяется первое истинное правило в последовательности;
пропускаются истинные правила с более низким приоритетом.
В представленной ниже таблице собраны все возможные условия для первых трех правил.
Можно установить или снять флажок Остановить, если истина для изменения поведения по умолчанию:
для обработки только первого правила установите флажок Остановить, если истина для первого правила;
для обработки только первого и второго правил установите флажок Остановить, если истина для второго правила;
Установить или снять флажок Остановить, если истина невозможно в том случае, если правило выполняет форматирование посредством гистограммы, цветовой шкалы или набора значков.
Изменение порядка проверки правил условного форматирования
Порядок проверки правил условного форматирования - их приоритет - зависит от их относительной важности: чем выше правило в списке правил условного форматирования, тем оно важнее. Это означает, что в случае, если два правила условного форматирования конфликтуют друг с другом, правило, которое находится выше в списке, применяется, а то, что ниже, не применяется.
Удаление условного форматирования
Вы можете удалить условное форматирование в выделенных ячейках или со всего листа.
Чтобы удалить условное форматирование в выделенных ячейках:
Выделите ячейки на листе.
Щелкните на вкладке Главная > > Удалить правила > Удалить правила из выделенных ячеек .
Чтобы удалить условное форматирование со всего листа, щелкните на вкладке Главная > > Удалить правила > Удалить правила со всего листа .
Примечание: Использовать условное форматирование с внешними ссылками на другие книги невозможно.
Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте
Этот урок с примерами и видео мы посвятим условному форматированию – одному из самых интересных и полезных средств Excel.
Итак, перейдем к делу. Условное форматирование – это способ максимально упростить программы Excel. Такой метод обработки информации позволяет сэкономить уйму времени и облегчить все вычисления. Вы можете заставить программу автоматически выполнять множество задач, которые раньше проделывали вручную, убивая на это целые дни.
Кроме этого, для вашего удобства, вы можете настроить работу Excel так, чтобы он сразу же выделял нужную или важную информацию в документах. К тому же такое форматирование поможет более наглядно отображать информацию, быстро и эффективно создавать отчетности без использования сложных графических моделей, как, например, диаграмм или графиков.
Давайте рассмотрим более конкретные примеры использования условного форматирования. Для того чтобы применить его в Excel 10, в разделе «Главная» на верхней панели программы нужно найти кнопку «Условное форматирование». Она нигде не прячется, поэтому найти ее не составит никакого труда. Для того чтобы активировать это форматирование, нам нужно выделить на рабочем листе зону, с которой мы будем работать. Иметься ввиду, что перед тем, как нажимать кнопку «Условное форматирование» и приступать к нему, нужно выделить столбик, рядок или несколько таких элементов, для которых вы хотите использовать форматирование.
Итак, зона работы выделена, кнопка нажата – что дальше? Перед вами откроется меню условного форматирования, где будут такие пункты:
Что с этим делать? Давайте по порядку. Этот пункт, в свою очередь, вмещает в себя такие стандартные функции, как
Работа с этими моделями форматирования совсем несложная. Нажав на любую из них, вы откроете небольшое окно, где нужно будет ввести нужные вам данные и выбрать цвет для подсветки подходящих вам ячеек таблицы.
Все это совсем несложно, когда на практике приступить к работе с программой.
Все методы форматирования в меню «Правила выделения ячеек» работают примерно так же, поэтому останавливаться здесь мы не будем.
Следующий пункт перед нами. Как это работает? Если вам нужно выделить несколько первых или последних ячеек по введенных данных, то вы именно там, где надо. Объяснять тут больше нечего, поэтому перейдем к примеру.
Поехали дальше. Здесь все очень просто. Нужно лишь выбрать нужный нам столбец или строчку и нажать на соответствующую кнопку. Тогда мы увидим, как все ячейки больше или меньше заполняются цветом, в зависимости от значений внутри них. Это как настоящая гистограмма.
Позволяют разукрасить наши ячейки по возрастанию или убыванию значений в них. Вам нужно только выбрать в какой цветовой гамме это будет происходить (например, максимальное значение – зеленый, минимальное – красный, а все промежуточные значения будут окрашиваться в соответствующие переходные оттенки). Здесь даже пример приводить не будем.
Значки нужны для того, чтобы указывать на разницу между значениями в нашем столбце или строчке. Теоретически объяснять немного сложно, поэтому сразу же перейдем к примерам.
Процентное соотношение или весь диапазон можно настроить в меню «Управление правилами», здесь же можно поиграться с настройками остальных правил.
Условное форматирование – удобный инструмент для анализа данных и наглядного представления результатов. Умение им пользоваться сэкономит массу времени и сил. Достаточно бегло взглянуть на документ – нужная информация получена.
Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».
При нажатии на стрелочку справа открывается меню для условий форматирования.
Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».
Введем в диапазон А1:А11 ряд чисел:
Выделим диапазон значений. Открываем меню «Условного форматирования». Выбираем «Правила выделения ячеек». Зададим условие, например, «больше».
Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:
Выходим из меню нажатием кнопки ОК.
Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.
Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» - «Меньше»).
Результат форматирования сразу виден на листе Excel.
Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.
Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.
Выделим исходный диапазон (А1:А11). Нажмем «УФ» - «Правила выделения ячеек» - «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ!, а не абсолютная.
Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.
Внимание! При использовании относительных ссылок нужно следить, какая ячейка была активна в момент вызова инструмента «Условного формата». Так как именно к активной ячейке «привязывается» ссылка в условии.
В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.
Сравните:
Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.
Проверить правильность заданного условия можно следующим образом:
В открывшемся окне видно, какое правило и к какому диапазону применяется.
Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.
Заполняем параметры форматирования по первому условию:
Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.
Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».
То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).
Выделяем диапазон с датами.
Применим к нему «УФ» - «Дата».
В открывшемся окне появляется перечень доступных условий (правил):
Выбираем нужное (например, за последние 7 дней) и жмем ОК.
Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).
Если стандартных правил недостаточно, пользователь может применить формулу. Практически любую: возможности данного инструмента безграничны. Рассмотрим простой вариант.
Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.
Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:
Для закрытия окна и отображения результата – ОК.
Задача: выделить цветом строку, содержащую ячейку с определенным значением.
Таблица для примера:
Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).
Выделяем диапазон со значениями таблицы. Нажимаем «УФ» - «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.
Порядок заполнения условий для форматирования «завершенных проектов»:
Аналогично задаем правила форматирования для незавершенных проектов.
В «Диспетчере» условия выглядят так:
Получаем результат:
Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».
«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.
Каждый менеджер, бухгалтер или даже самый обычный пользователь не раз сталкивался с великолепными возможностями Excel 2010, позволяющей быстро и удобно работать с таблицами. Данная версия программы отличается расширенными возможностями условного форматирования сводных таблиц, что улучшает весь рабочий процесс.
Мы имеем функционал визуализации, предоставляющий возможность использовать в ячейках гистограммы, разнообразные значки и всевозможные цветовые шкалы. Теперь можно легко создать окошко в стиле управляющей консоли, что облегчает поиск информации. Также теперь этот тип форматирования начал успешно применятся в сводных таблицах, точнее говоря – к их структуре (а не только к данным). Описанный ниже способ подойдет даже тем, кто просто собирается купить новую стиральную машинку и выбирает модель.
Приведем простейший пример сводной таблицы, описывающий объемы продаж какого-то товара по регионам:
Теперь, опираясь на эти данные, создадим графический отчет объемов продаж за каждый временной период, чтобы облегчить восприятие информации. Естественно, можно воспользоваться сводной диаграммой, но хорошо подходит и условное форматирование в Excel 2010.
Самый простой вариант – использовать цветовые шкалы. Для этого выделяем поле «Объем продаж», охватывая все периоды. Осталось открыть вкладку «Главная», где нажимаем кнопку «Условное форматирование» (если вы вдруг используете английскую версию, то данная функция называется «Conditional Formatting»). Наведите курсор на «Гистограммы».
Имеющийся набор градиентной и сплошной заливки, по сути своей, напоминает стандартную горизонтальную гистограмму. И если сейчас использовать фильтрацию данных любой графы в пределах области действия фильтра, то мы заметим динамическое изменение гистограмм.
Excel 2010 уже имеет значительное количество полностью готовых сценариев форматирования, значительно экономящих рабочее время. Для создания описанных выше гистограмм, кстати, программа применяет алгоритм, самостоятельно определяющий максимальное и минимальное значения диапазона выбранных ячеек и только потом форматирует их, опираясь на обработанную информацию.
Итак – готовые сценарии, способные помочь в большинстве ситуаций:
Первые 10 элементов;
Последние 10 элементов;
Первые 10%;
Последние 10%;
Больше среднего;
Меньше среднего.
Удаление уже используемого условного форматирования в Excel 2010 происходит по следующей схеме: в сводной таблице переходим на вкладку «Главная», нажимаем на «Условное форматирование», далее «Стили», и в выпадающем меню используем команду «Удалить правила» - «Удалить правила из этой сводной таблицы» (в английском варианте – «Clear Rules» и «Clear Rules from this PivotTable»).
Впрочем, можно легко создавать собственные правила форматирования.
Данная таблица – усложненный вариант первой, так что сразу переходим к примеру. Давайте отследим объем продаж и выручку за час. Мы будем использовать условное форматирование Excel 2010 для ускорения поиска совпадений и различий. Выделяем «Объем продаж». Далее по стандартной процедуре активируем сценарий («Главная» - «Условное форматирование»), но выбираем не готовый вариант, а функцию «Создать правило» (или «New Rule»).
Именно здесь можно определить ячейки, на которые будете применяться условное форматирование в Excel 2010, тип используемого правила и, собственно, параметры форматирования. Сначала задаются ячейки, и здесь есть достаточно простой выбор:
Выделенные («Selected Cells»);
Входящие в столбец «Объем продаж» («All Cells Showing «Sales_Amount» Values»), включая промежуточные и общие итоги. Данный вариант, кстати, хорошо подходит для анализа тех данных, которые требуют определения среднего, процентного соотношения или иными величинами, так или иначе являющимися разными уровнями одной величины;
Входящие в категорию «Объем продаж» только для «Рынка сбыта» («All Cells Showing «Sales_Amount» Values for «Market»»). Данный вариант полностью исключает общие и промежуточные итоги, что удобно для анализа некоторых отдельных значений.
Отметим, что команды «Объем продаж», «Рынок сбыта» при создании правил меняются в зависимости от имеющихся рабочих таблиц.
По нашему примеру самым выгодным является вариант «3», поэтому используется такой вариант:
При выборе правила (раздел «Выбрать правило» или «Select a Rule Туре») указываем именно то, которое и отвечает нашим требованиям.
Это может быть:
- «Форматирование ячеек на основании значений» («Format All Cells Based on Their Values»). Используется для форматирования ячеек, которые соответствуют используемому диапазону значений. Лучше всего подходит для определения самых разных отклонений, если приходиться работать с огромным набором данных.
- «Форматирование ячеек содержащих» («Format Only Cells That Contain»). Форматирует ячейки, отвечающие подходящим условиям. В данном случае сравнение значений форматированных ячеек с обычными не происходит. Используется для сравнения общего набора данных с указанной ранее характеристикой.
- «Форматирование первых и последних значений» («Format Only Top or Bottom Ranked Values»).
- «Форматировать значения ниже или выше среднего» («Format Only Values That Are Above or Below the Average»).
- «Использовать формулу определения форматируемых ячеек» («Use a Formula to Determine Which Cells to Format»). Здесь уже условия условного форматирования опираются на формулу, заданную самим пользователем. Если значение ячейки (из подставленных в формулу) приходит со значением «true», то к ячейке применяют форматирование. В случае со значением «false» форматирование не применяется.
Применение гистограмм, наборов значков и цветовых шкал возможно только тогда, когда форматирование выделенных ячеек происходит на основании значений, занесенных в них. Для этого устанавливаем первый переключатель на «Форматирование всех ячеек на основании значений» («Format All Cells Based on Their Values»). Для обозначения проблемных областей можно использовать набор значков, что также хорошо подходит для данного сценария.
Ну и осталось определить точные параметры нашего форматирования. Здесь пригодится раздел «Изменения описания правила» («Edit the Ruie Description»). Для добавления значков в проблемные ячейки, мы используем выпадающее меню «Стиль формата» («Format Style») и выбираем «Наборы значков» («Icon Sets»).
В списке «Стиля значка» остается выбрать значение «3 знака» Это хорошо подойдет, если имеющуюся таблицу невозможно полностью раскрасить. В результате в окошке у нас должно получиться следующее:
При такой конфигурации Excel самостоятельно будет добавлять в ячейки значки, при этом следуя функции:
>=67, >=33 и
Смотря на сухие цифры таблиц, трудно с первого взгляда уловить общую картину, которую они представляют. Но, в программе Microsoft Excel имеется инструмент графической визуализации, с помощью которого можно наглядно представить данные, содержащиеся в таблицах. Это позволяет более легко и быстро усвоить информацию. Данный инструмент называется условным форматированием. Давайте разберемся, как использовать условное форматирование в программе Microsoft Excel.
Для того, чтобы произвести форматирование определенной области ячеек, нужно выделить эту область (чаще всего столбец), и находясь во вкладке «Главная», кликнуть по кнопке «Условное форматирование», которая расположена на ленте в блоке инструментов «Стили».
После этого, открывается меню условного форматирования. Тут представляется три основных вида форматирования:
Для того, чтобы произвести условное форматирование в виде гистограммы, выделяем столбец с данными, и кликаем по соответствующему пункту меню. Как видим, представляется на выбор несколько видов гистограмм с градиентной и сплошной заливкой. Выберете ту, которая, на ваш взгляд, больше всего соответствует стилю и содержанию таблицы.
Как видим, гистограммы появились в выделенных ячейках столбца. Чем большее числовое значение в ячейках, тем гистограмма длиннее. Кроме того, в версиях Excel 2010, 2013 и 2016 годов, имеется возможность корректного отображения отрицательных значений в гистограмме. А вот, у версии 2007 года такой возможности нет.
При использовании вместо гистограммы цветовой шкалы, также существует возможность выбрать различные варианты данного инструмента. При этом, как правило, чем большее значение расположено в ячейке, тем насыщеннее цвет шкалы.
Наиболее интересным и сложным инструментом среди данного набора функций форматирования являются значки. Существует четыре основные группы значков: направления, фигуры, индикаторы и оценки. Каждый выбранный пользователем вариант предполагает использование разных значков при оценке содержимого ячейки. Вся выделенная область сканируется Excel, и все значения ячеек разделяются на части, согласно величинам, указанным в них. К самым большим величинам применяются значки зеленого цвета, к величинам среднего диапазона – желтого, и величины, располагающиеся в самой меньшей трети – помечаются значками красного цвета.
При выборе стрелок, в качестве значков, кроме цветового оформления, используется ещё сигнализирование в виде направлений. Так, стрелка, повернутая указателем вверх, применяется к большим величинам, влево – к средним, вниз – к малым. При использовании фигур, кругом помечаются самые большие величины, треугольником – средние, ромбом – малые.
По умолчанию, используется правило, при котором все ячейки выделенного фрагмента обозначаются определенным цветом или значком, согласно расположенным в них величинам. Но, используя меню, о котором мы уже говорили выше, можно применять и другие правила обозначения.
Кликаем по пункту меню «Правила выделения ячеек». Как видим, существует семь основных правил:
Рассмотрим применение этих действий на примерах. Выделим диапазон ячеек, и кликнем по пункту «Больше…».
Открывается окно, в котором нужно установить, значения больше какого числа будут выделяться. Делается это в поле «Форматировать ячейки, которые больше». По умолчанию, сюда автоматически вписывается среднее значение диапазона, но можно установить любое другое, либо же указать адрес ячейки, в которой содержится это число. Последний вариант подойдёт для динамических таблиц, данные в которых постоянно изменяются, или для ячейки, где применяется формула. Мы для примера установили значение в 20000.
В следующем поле, нужно определиться, как будут выделяться ячейки: светло-красная заливка и темно-красный цвет (по умолчанию); желтая заливка и темно-желтый текст; красный текст, и т.д. Кроме того, существует пользовательский формат.
При переходе на этот пункт, открывается окно, в котором можно редактировать выделения, практически, как угодно, применяя различные варианты шрифта, заливки, и границы.
После того, как мы определились, со значениями в окне настройки правил выделения, жмём на кнопку «OK».
Как видим, ячейки выделены, согласно установленному правилу.
По такому же принципу выделяются значения при применении правил «Меньше», «Между» и «Равно». Только в первом случае, выделяются ячейки меньше значения, установленного вами; во втором случае, устанавливается интервал чисел, ячейки с которыми будут выделяться; в третьем случае задаётся конкретное число, а выделяться будут ячейки только содержащие его.
Правило выделения «Текст содержит», главным образом, применяется к ячейкам текстового формата. В окне установки правила следует указать слово, часть слова, или последовательный набор слов, при нахождении которых, соответствующие ячейки будут выделяться, установленным вами способом.
Правило «Дата» применяется к ячейкам, которые содержат значения в формате даты. При этом, в настройках можно установить выделение ячеек по тому, когда произошло или произойдёт событие: сегодня, вчера, завтра, за последние 7 дней, и т.д.
Применив правило «Повторяющиеся значения» можно настроить выделение ячеек, согласно соответствию размещенных в них данных одному из критериев: повторяющиеся это данные или уникальные.
Кроме того, в меню условного форматирования имеется ещё один интересный пункт – «Правила отбора первых и последних значений». Тут можно установить выделение только самых больших или самых маленьких значений в диапазоне ячеек. При этом, можно использовать отбор, как по порядковым величинам, так и по процентным. Существуют следующие критерии отбора, которые указаны в соответствующих пунктах меню:
Но, после того, как вы кликнули по соответствующему пункту, можно немного изменить правила. Открывается окно, в котором производится выбор типа выделения, а также, при желании, можно установить другую границу отбора. Например, мы, перейдя по пункту «Первые 10 элементов», в открывшемся окне, в поле «Форматировать первые ячейки» заменили число 10 на 7. Таким образом, после нажатия на кнопку «OK», будут выделяться не 10 самых больших значений, а только 7.
Выше мы говорили о правилах, которые уже установлены в программе Excel, и пользователь может просто выбрать любое из них. Но, кроме того, при желании, пользователь может создавать свои правила.
Для этого, нужно нажать в любом подразделе меню условного форматирования на пункт «Другие правила…», расположенный в самом низу списка». Или же кликнуть по пункту «Создать правило…», который расположен в нижней части основного меню условного форматирования.
Открывается окно, где нужно выбрать один из шести типов правил:
Согласно выбранному типу правил, в нижней части окна нужно настроить изменение описания правил, установив величины, интервалы и другие значения, о которых мы уже говорили ниже. Только в данном случае, установка этих значений будет более гибкая. Тут же задаётся, при помощи изменения шрифта, границ и заливки, как именно будет выглядеть выделение. После того, как все настройки выполнены, нужно нажать на кнопку «OK», для сохранения проведенных изменений.
В программе Excel можно применять сразу несколько правил к одному и тому же диапазону ячеек, но отображаться на экране будет только последнее введенное правило. Для того, чтобы регламентировать выполнение различных правил относительно определенного диапазона ячеек, нужно выделить этот диапазон, и в основном меню условного форматирования перейти по пункту управление правилами.
Открывается окно, где представлены все правила, которые относятся к выделенному диапазону ячеек. Правила применяются сверху вниз, так как они размещены в списке. Таким образом, если правила противоречат друг другу, то по факту на экране отображается выполнение только самого последнего из них.
Чтобы поменять правила местами, существуют кнопки в виде стрелок направленных вверх и вниз. Для того, чтобы правило отображалось на экране, нужно его выделить, и нажать на кнопку в виде стрелки направленной вниз, пока правило не займет самую последнюю строчу в списке.
Есть и другой вариант. Нужно установить галочку в колонке с наименованием «Остановить, если истина» напротив нужного нам правила. Таким образом, перебирая правила сверху вниз, программа остановится именно на правиле, около которого стоит данная пометка, и не будет опускаться ниже, а значит, именно это правило будет фактически выполнятся.
В этом же окне имеются кнопки создания и изменения выделенного правила. После нажатия на эти кнопки, запускаются окна создания и изменения правил, о которых мы уже вели речь выше.
Для того, чтобы удалить правило, нужно его выделить, и нажать на кнопку «Удалить правило».
Кроме того, можно удалить правила и через основное меню условного форматирования. Для этого, кликаем по пункту «Удалить правила». Открывается подменю, где можно выбрать один из вариантов удаления: либо удалить правила только на выделенном диапазоне ячеек, либо удалить абсолютно все правила, которые имеются на открытом листе Excel.
Как видим, условное форматирование является очень мощным инструментом для визуализации данных в таблице. С его помощью, можно настроить таблицу таким образом, что общая информация на ней будет усваиваться пользователем с первого взгляда. Кроме того, условное форматирование придаёт большую эстетическую привлекательность документу.
Мы рады, что смогли помочь Вам в решении проблемы.
Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.
Условное форматирование в Excel – прекрасный инструмент для быстрого визуального анализа данных. Таким способом оценивать информацию намного удобнее и проще. Более того, всё это происходит в автоматическом режиме. Пользователю не нужно думать и сравнивать значения самостоятельно. Редактор всё сделает сам. Ни в одной формуле вы не сможете сделать такое, что может данный инструмент.
Для того чтобы воспользоваться данной возможностью, нужно перейти на вкладку «Главная» и кликнуть на кнопку «Условное форматирование».
К основным разделам данного меню относятся:
Рассмотрим эти пункты более внимательно. Для этого создадим какую-нибудь таблицу, в которой можно будет сравнивать числовые значения.
В этом разделе также очень много различных вариантов форматирования. Разберем каждый из них.
Если вам не нравится цвет заливки ячеек, вы всегда можете изменить ее. Для этого нужно выбрать любой другой вариант раскраски на стадии указания цифры для сравнения.
Если ничто из предложенных вариантов вам не нравится, можно кликнуть на пункт «Пользовательский формат…».
Сразу после этого появится окно, в котором можно указать нужный вам формат ячеек.
Мы видим, что фон ячеек меняется полностью в автоматическом режиме.
Описанные выше действия подходят только для числовых значений. Для работы с текстовой информацией нужно выбрать другой инструмент.
Подобные манипуляции можно проделать и с временными значениями.
Для демонстрации этого условного форматирования желательно использовать таблицу без других правил сравнения. Далее нужно будет выполнить следующие действия.
В каждом случае будет доступен предварительный просмотр, чтобы вы могли точно определиться, что именно вам нужно. Для сохранения нужно кликнуть на кнопку «OK».
Кроме обычного выделения конкретных чисел, существует возможность маркировки определенного количества элементов в процентном или количественном соотношении. Для этого необходимо сделать следующее.
Рассмотрим каждый из них.
Выбрав этот пункт, вы увидите окно, в котором предложат указать количество первых ячеек. Для сохранения кликните на «OK».
Отсчет происходит от большего значения к меньшему.
Это значит, что если вам необходимо выделить первые 10 клеток, в которых находятся самые маленькие цифры, то нужно выбрать пункт «Последние 10 элементов».
Во время ввода количества ячеек вам будет доступен предварительный просмотр. Если указать число 1, то останется только 1 максимальное значение.
Обратите внимание на то, что если будет две ячейки с одинаковым наибольшим числом, то выделятся оба!
В данном случае всё работает практически по такому же принципу, только на этот раз подсвечивается не конкретное фиксированное количество клеток, а лишь определенный процент из них.
Если указать число 10 (оно используется по умолчанию), то вы увидите следующее.
Если данное правило форматирования вам понравилось, нужно нажать на кнопку «OK». В противном случае кликните на «Отмена».
Как было сказано выше, в данном случае выделяются те ячейки, в которых содержатся минимальные данные. Принцип ввода тот же самый – указываете нужное количество и кликаете на кнопку «OK».
Если вы укажете всего 1 клетку, но при этом минимальных цифр будет несколько, то произойдет выделение всех (в нашем случае – две).
Тот же самый принцип, только на этот раз подсвечивается определенный процент информации, а не абсолютное количество.
Данный инструмент весьма удобен, когда нужно отсортировать информацию по отношению к ней самой. То есть редактор Excel сам посчитает среднее число среди выделенной информации и пометит всё то, что выше этой величины. Всё происходит автоматически.
Аналогичный принцип действия и в этом случае. Только на этот раз маркируются клетки, в которых хранится информация меньше среднестатистической величины.
В описанных выше методах сравнения данных использовался метод сплошной заливки элементов. Иногда это не совсем удобно.
Для более продвинутого анализа информации используется другой инструмент – гистограммы. При этом заливка может быть двух видов:
Рассмотрим каждый из предложенных вариантов.
К значениям по умолчанию относятся:
При наведении на каждый из вариантов вам будет доступен предварительный просмотр.
Данный тип маркировки сильно не отличается от вышеописанного и находится он в том же разделе.
Цвета используются те же самые.
Если ничего из предложенных пунктов вам не понравилось, вы можете указать свой вариант форматирования.
Здесь вы сможете настроить:
Образец того, что вы настроили, можно увидеть в правом нижнем углу.
Если вы хотите чего-то более контрастного, нужно сделать следующие действия.
При наведении на каждую из иконок, вам будет доступен предварительный просмотр. Так вы сможете выбрать ту цветовую гамму, которая вам нравится больше всего.
Если ничего из предложенного редактором Excel вам не понравилось, вы всегда можете создать что-то своё. Для этого нужно в этом же разделе меню кликнуть на пункт «Другие правила».
Сразу после этого вы увидите следующее окно. Здесь можно указать начальный и конечный цвет. Для сохранения достаточно нажать на кнопку «OK».
Если цветовое форматирование вам не нравится, вы можете использовать графический метод. Для этого необходимо сделать следующие действия.
Стоит отметить, что редактор сам автоматически делит данные на несколько групп: минимальные, средние и максимальные.
К возможным вариантам относятся (каждый раз при наведении на любую иконку вы будете видеть предварительный просмотр без сохранения правила форматирования):
Если ни одна из иконок вам не понравилась, вы можете создать своё правило заполнения клеток.
В этом случае вы сможете самостоятельно указать следующие параметры:
Для сохранения нажмите на кнопку «OK».
Если ваш эксперимент не удался и проделанные вами манипуляции только испортили внешний вид таблицы, то всё это можно отменить довольно простым способом.
Набор способов форматирования можно менять по собственному желанию. Это делается следующим образом.
Для того чтобы удалить что-нибудь, достаточно выбрать что-то из списка и нажать на кнопку «Удалить правило».
Необходимо быть очень внимательным при выполнении таких действий, поскольку вас дополнительно не спросят, уверены ли вы в своем выборе.
Редактировать правила довольно просто. Это делается следующим образом.
