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

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

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

2. На вкладке Главная в группе Стили нажмите кнопку Условное Форматирование… (Conditional Formatting…).

3. В появившемся меню выберите Правила отбора первых и последних значений (Рисунок 4.14).

4. Выберите нужную команду, например Выше среднего или Ниже среднего.

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

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

Вы можете разработать дополнительные правила для визуализации данных, для этого выполните следующие действия:

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

2. На вкладке Главная в группе Стили нажмите кнопку Условное Форматирование… (Conditional Formatting…).

3. Выберите команду Создать правило…. Откроется окно диалога Создание правила форматирования.

4. В том случае, если в качестве критерия оформления ячеек должна выступать формула в появившемся ОД Создание правила форматирования выберите Использовать формулу для определения форматируемых ячеек (Рисунок 4.16).

Рисунок 4.16. ОД Создание правила форматирования по критерию Формула

 

5. В первое поле введите формулу. Формула должна принимать логическое значение Истина (TRUE) или Ложь (FALSE).

6. Используя кнопку Формат… (Format…) задайте необходимое форматное оформление. Оформление применяется только в случае выполнения условий.

7. Для закрытия окна нажмите кнопку OK.

Средства визуализации данных

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

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

· Цветовые шкалы — это визуальные элементы, которые помогают понять распределение и разброс данных. Трехцветная шкала помогает сравнить диапазон ячеек путем использования градации трех цветов. Уровень яркости цвета соответствует высоким, средним или низким значениям. Например, в красно-желто-зеленой шкале можно указать, что ячейки с высокими значениями будут зелеными, ячейки со средними значениями — желтыми, а ячейки с низкими значениями — красными.

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

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

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

2. На вкладке Главная в группе Стили нажмите кнопку Условное Форматирование… (Conditional Formatting…).

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

Ø Для применения гистограмм выберите команду Гистограммы, а затем выберите нужный вариант Гистограммы.

Ø Для применения цветовых шкал выберите команду Цветовые шкалы, а затем выберите требуемый вид отображения.

Ø Для применения наборов значков выберите команду Наборы значков, а затем выберите подходящий набор значков.

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

Рисунок 4.17. Отображение диапазона с помощью средств визуализации

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

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

2. На вкладке Главная в группе Стили нажмите кнопку Условное Форматирование… (Conditional Formatting…).

3. Выберите команду Управление правилами. Откроется окно диалога Диспетчер правил условного форматирования

Рисунок 4.18. Окно диалога Диспетчер правил условного форматирования

 

4. Выполните одно из указанных ниже действий.

Ø Для добавления Правила условного форматирования нажмите кнопку Создать правило. Появится диалоговое окно Создание правила форматирования (Рисунок 4.19).

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

Рисунок 4.19. Окно диалога Создание правила форматирования

 

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

6. В группе Измените описание правила в поле со списком Стиль формата выберите необходимый вариант оформления: Двухцветная шкала, Трехцветная шкала, Гистограмма или Набор значков.

! Набор доступных параметров зависит от выбранного стиля (Рисунок 4.19, Рисунок 4.20)

Рисунок 4.20. Окно диалога Создание правила форматирования

 

7. В поле Тип выберите Тип параметров:

Ø Для форматирования минимального и максимального значений выберите Минимальное значение и Максимальное значение.

Ø Для форматирования числового значения, значения даты или времени выберите элемент Число, а затем введите значения соответствующих параметров.

Ø Для форматирования процентного значения выберите элемент Процент, а затем введите значения соответствующих параметров. Допустимыми являются значения от 0 (нуль) до 100. Не вводите знак процента.

Ø Для форматирования процентилей выберите элемент Процентиль, а затем введите значения соответствующих параметров. Допустимыми являются значения процентилей от 0 (нуль) до 100.

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

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

8. Задайте необходимый вариант оформления.

9. Нажмите кнопку ОК.