15. Условное форматирование.

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

Быстрое форматирование

1.  Выделите диапазон ячеек или убедитесь, что активная ячейка находится в таблице или отчете сводной таблице.

2.    На вкладке Начальная страница в группе Стили щелкните стрелку рядом с пунктом Условное форматирование, а затем выберите команду Цветовые шкалы 

3.    Выберите двухцветную шкалу.

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

    –  После применения стиля выделите данные, нажмите на ленте вкладка Главная кнопку Условное форматирование и выберите пункт Управление правилами, чтобы вручную настроить правила и форматирование.

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

Расширенное форматирование

1.     Выделите диапазон ячеек или убедитесь, что активная ячейка находится в таблице или отчете сводной таблице.

  

2.  На вкладке Главная в группе Стили щелкните стрелку рядом с пунктом Условное форматирование, а затем выберите пункт 

Будет отображено диалоговое окно Диспетчер правил условного форматирования.

3.       Выполните одно из следующих действий:

–      Для добавления условного форматирования нажмите кнопку Создать правило.

Будет отображено диалоговое окно Создание правила форматирования.

–      Для изменения условного форматирования выполните следующие действия.

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

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

5.    В группе Измените описание правила в поле со списком Стиль формата выберите пункт Двухцветная шкала.

6.   Выберите Минимальное значение и Максимальное значение параметра Тип. Выполните одно из следующих действий.

- Форматирование минимального и максимального значений Выберите Минимальное значение и Максимальное значение.

В этом случае не вводите Минимальное значение и Максимальное значение параметра Значение.

- Форматирование числового значения, значения даты или времени   Выберите пункт Число, а затем введите Минимальное значение и Максимальное значение параметра Значение.

- Форматирование процентного значения   Выберите пункт Процент, а затем введите Минимальное значение и Максимальное значение параметра Значение.

Допустимыми являются значения от 0 до 100. Не вводите знак процента.

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

- Форматирование процентили   Выберите пункт Процентиль, а затем введите Минимальное значение и Максимальное значение параметра Значение.

Допустимыми являются значения процентили от 0 до 100. Процентиль нельзя использовать, если диапазон ячеек содержит более 8191 точек данных.

Используйте процентиль, когда необходимо визуализировать группу высоких значений (например, верхнюю 20^ую процентиль) в одной пропорции цветового оттенка и группу низких значений (например, нижнюю 20^ую процентиль) в другой пропорции цветового оттенка, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.

- Форматирование результата формулы   Выберите пункт Формула, а затем введите Минимальное значение и Максимальное значение параметра Значение.

Формула должна возвращать число, дату или время. Начните формулу со знака равенства (=). Недопустимые формулы приведут к отсутствию форматирования. Проверьте формулу на листе, чтобы убедиться, что она не возвращает ошибочное значение.

- Минимальное значение и Максимальное значение являются минимальным и максимальным значениями для диапазона ячеек. Убедитесь, что Минимальное значение меньше чем Максимальное значение.

- Можно выбрать другие Минимальное значение и Максимальное значение параметра Тип. Например, можно выбрать Минимальное значение параметра Число и Максимальное значение параметра Процент.

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

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

Выбранная цветовая шкала отобразится в поле Образец .

Форматирование всех ячеек с использованием гистограммы

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

Форматирование всех ячеек с использованием набора значков

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

Форматирование ячеек с текстовыми, числовыми значениями или значениями даты или времени

Чтобы было проще искать конкретные ячейки в диапазоне ячеек, можно отформатировать эти ячейки с помощью оператора сравнения. Например, в листе запасов, отсортированном по категориям, можно выделить желтым цветом продукты, которых в наличии менее 10 позиций. Или в сводном листе магазина розничной торговли можно указать все магазины с доходом более 10%, объемом продаж менее 100 000 р. и районом «Юго-Восток».

Форматирование только первых или последних значений

Можно найти максимальное и минимальное значения в диапазоне ячеек на основе указанного порогового значения. Например, можно найти 5 самых продающихся продуктов в региональном отчете, нижние 15% продуктов в клиентском опросе или 25 высших зарплат в отчете по персоналу отдела.

Форматирование значений выше или ниже среднего значения

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

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

Выполните одно из следующих действий.

Лист

1.  На вкладке Главная в группе Стили щелкните стрелку рядом с пунктом Условное форматирование, а затем выберите команду Очистить правила.

2.      Нажмите кнопку Весь лист.

Диапазон ячеек, таблица или сводная таблица  

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

2.  На вкладке Главная в группе Стили щелкните стрелку рядом с пунктом Условное форматирование, а затем выберите команду Удаление правила.

3.    В зависимости от того, какой элемент выделен, нажмите кнопку Выделенные ячейки, Эта таблица или Эта сводная таблица.