В Microsoft Excel вы можете использовать условное форматирование для визуализации данных. Вы должны указать форматирование для диапазона ячеек на основе содержимого диапазона ячеек. Ячейки, которые соответствуют указанным условиям, будут отформатированы так, как вы определили.
пример
В диапазоне, содержащем данные о продажах за последний квартал для группы продавцов, можно выделить те ячейки, представляющие, которые достигли определенной цели, скажем, 2500 долларов США.
Вы можете установить условие как общий объем продаж человека> = $ 2500 и указать зеленый цветовой код. Excel проверяет каждую ячейку в диапазоне, чтобы определить, удовлетворяется ли указанное вами условие, т. Е. Общие продажи человека> = 2500 долларов США.
Excel применяет выбранный формат, т. Е. Зеленый цвет, ко всем ячейкам, которые удовлетворяют условию. Если содержимое ячейки не удовлетворяет условию, форматирование ячейки остается неизменным. Результат, как и ожидалось, только для продавцов, которые достигли цели, ячейки выделены зеленым цветом — быстрая визуализация результатов анализа.
Вы можете указать любое количество условий для форматирования, указав Правила . Вы можете выбрать правила, которые соответствуют вашим условиям из
- Выделите правила ячеек
- Верхние / нижние правила
Вы также можете определить свои собственные правила. Вы можете —
- Добавить правило
- Очистить существующее правило
- Управлять определенными правилами
Кроме того, у вас есть несколько вариантов форматирования в Excel, чтобы выбрать те, которые подходят для вашей визуализации данных —
- Панели данных
- Цветовые шкалы
- Наборы иконок
Условное форматирование продвигалось в версиях Excel 2007, Excel 2010, Excel 2013. Примеры, которые вы найдете в этой главе, взяты из Excel 2013.
В следующих разделах вы поймете правила условного форматирования, параметры форматирования и порядок работы с правилами.
Выделите правила ячеек
Вы можете использовать правило Highlight Cells, чтобы назначить формат ячейкам, содержимое которых соответствует любому из следующих критериев:
- Числа в данном числовом диапазоне —
- Лучше чем
- Меньше, чем
- Между
- Равно
- Текст, который содержит данную текстовую строку.
- Дата, происходящая в заданном диапазоне дат относительно текущей даты —
- Вчера
- сегодня
- Завтра
- За последние 7 дней
- Прошлая неделя
- На этой неделе
- Следующая неделя
- Прошлый месяц
- Этот месяц
- Следующий месяц
- Значения, которые являются дублирующими или уникальными.
Выполните шаги для условного форматирования ячеек —
-
Выберите диапазон для условного форматирования.
-
Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».
-
Нажмите « Выделить правила ячеек» в раскрывающемся меню.
Выберите диапазон для условного форматирования.
Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».
Нажмите « Выделить правила ячеек» в раскрывающемся меню.
-
Нажмите « Больше чем» и укажите> 750. Выберите зеленый цвет.
-
Нажмите Less Than и укажите <500. Выберите красный цвет.
-
Нажмите Between и укажите 500 и 750. Выберите желтый цвет.
Нажмите « Больше чем» и укажите> 750. Выберите зеленый цвет.
Нажмите Less Than и укажите <500. Выберите красный цвет.
Нажмите Between и укажите 500 и 750. Выберите желтый цвет.
Данные будут выделены в зависимости от заданных условий и соответствующего форматирования.
Верхние / нижние правила
Вы можете использовать правила Top / Bottom, чтобы назначить формат ячейкам, содержимое которых соответствует любому из следующих критериев:
-
Топ 10 предметов — Клетки, которые ранжируются в верхнем N, где 1 <= N <= 1000.
-
Верхние 10% — ячейки, которые занимают место в верхнем n%, где 1 <= n <= 100.
-
Нижние 10 элементов — ячейки, которые ранжируются в нижней части N, где 1 <= N <= 1000.
-
Нижние 10% — ячейки, которые ранжируются в нижней части n%, где 1 <= n <= 100.
-
Выше среднего — клетки выше среднего для выбранного диапазона.
-
Ниже среднего — ячейки ниже среднего для выбранного диапазона.
Топ 10 предметов — Клетки, которые ранжируются в верхнем N, где 1 <= N <= 1000.
Верхние 10% — ячейки, которые занимают место в верхнем n%, где 1 <= n <= 100.
Нижние 10 элементов — ячейки, которые ранжируются в нижней части N, где 1 <= N <= 1000.
Нижние 10% — ячейки, которые ранжируются в нижней части n%, где 1 <= n <= 100.
Выше среднего — клетки выше среднего для выбранного диапазона.
Ниже среднего — ячейки ниже среднего для выбранного диапазона.
Следуйте приведенным ниже инструкциям, чтобы назначить верхние / нижние правила.
-
Выберите диапазон для условного форматирования.
-
Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».
-
Выберите Top / Bottom Rules в раскрывающемся меню. Появятся варианты верхних / нижних правил.
Выберите диапазон для условного форматирования.
Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».
Выберите Top / Bottom Rules в раскрывающемся меню. Появятся варианты верхних / нижних правил.
-
Нажмите Top Ten Items и укажите 5. Выберите зеленый цвет.
-
Нажмите Нижние десять пунктов и укажите 5. Выберите красный цвет.
Нажмите Top Ten Items и укажите 5. Выберите зеленый цвет.
Нажмите Нижние десять пунктов и укажите 5. Выберите красный цвет.
Данные будут выделены в зависимости от заданных условий и соответствующего форматирования.
-
Повторите первые три шага, приведенные выше.
-
Нажмите Top Ten% и укажите 5. Выберите зеленый цвет.
-
Нажмите Нижнюю Десять% и укажите 5. Выберите красный цвет.
Повторите первые три шага, приведенные выше.
Нажмите Top Ten% и укажите 5. Выберите зеленый цвет.
Нажмите Нижнюю Десять% и укажите 5. Выберите красный цвет.
Данные будут выделены в зависимости от заданных условий и соответствующего форматирования.
-
Повторите первые три шага, приведенные выше.
-
Нажмите выше среднего . Выберите зеленый цвет.
-
Нажмите ниже среднего . Выберите красный цвет.
Повторите первые три шага, приведенные выше.
Нажмите выше среднего . Выберите зеленый цвет.
Нажмите ниже среднего . Выберите красный цвет.
Данные будут выделены в зависимости от заданных условий и соответствующего форматирования.
Панели данных
Вы можете использовать цветные панели данных, чтобы увидеть значение в ячейке относительно значений в других ячейках. Длина строки данных представляет значение в ячейке. Более длинный столбец представляет более высокое значение, а более короткий столбец представляет более низкое значение. У вас есть шесть сплошных цветов на выбор для полос данных — синий, зеленый, красный, желтый, светло-синий и фиолетовый.
Панели данных помогают визуализировать верхние, нижние и промежуточные значения, когда у вас большие объемы данных. Пример — дневные температуры по регионам в конкретном месяце. Вы можете использовать цветные полосы градиентной заливки для визуализации значения в ячейке относительно значений в других ячейках. У вас есть шесть цветов градиента на выбор для панелей данных — синий, зеленый, красный, желтый, светло-синий и фиолетовый.
-
Выберите диапазон для условного форматирования.
-
Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».
-
Нажмите Панели данных в раскрывающемся меню. Появятся параметры градиентной заливки и заливки .
Выберите диапазон для условного форматирования.
Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».
Нажмите Панели данных в раскрывающемся меню. Появятся параметры градиентной заливки и заливки .
Нажмите на синюю строку данных в параметрах градиентной заливки .
-
Повторите первые три шага.
-
Нажмите на синюю строку данных в параметрах Solid Fill .
Повторите первые три шага.
Нажмите на синюю строку данных в параметрах Solid Fill .
Вы также можете отформатировать столбцы данных так, чтобы строка данных начиналась в середине ячейки и растягивалась влево для отрицательных значений и растягивалась вправо для положительных значений.
Цветовые шкалы
Вы можете использовать Цветовые шкалы, чтобы увидеть значение в ячейке относительно значений в других ячейках в данном диапазоне. Как и в случае с правилами выделения ячеек , цветовая шкала использует затенение ячеек для отображения различий в значениях ячеек. Цветовой градиент будет применен к диапазону ячеек. Цвет указывает, где значение каждой ячейки попадает в этот диапазон.
Вы можете выбрать из —
- Три — Цветовая шкала —
- Зеленый — Желтый — Красный Цвет Шкалы
- Красный — Желтый — Зеленый Цвет Шкалы
- Зеленый — Белый — Красный Цветовая шкала
- Красный — Белый — Зеленый Цвет Шкалы
- Синий — белый — красный цвет шкалы
- Красный — Белый — Синий Цветовая шкала
- Двухцветная шкала —
- Белый — красный цвет шкалы
- Красно-белая цветовая шкала
- Зелено-белая цветовая шкала
- Белый — Зеленый Цвет шкалы
- Зелено-желтая цветовая шкала
- Желто-зеленая шкала
Следуйте инструкциям ниже
-
Выберите диапазон для условного форматирования.
-
Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».
-
Нажмите Color Scales из выпадающего меню. Появятся параметры цветовой шкалы .
-
Нажмите на шкалу Зеленый — Желтый — Красный.
Выберите диапазон для условного форматирования.
Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».
Нажмите Color Scales из выпадающего меню. Появятся параметры цветовой шкалы .
Нажмите на шкалу Зеленый — Желтый — Красный.
Данные будут выделены на основе зеленой — желтой — красной цветовой шкалы в выбранном диапазоне.
- Повторите первые три шага.
- Нажмите Зелено — Белая цветовая гамма.
Данные будут выделены на основе зелено-белой цветовой шкалы в выбранном диапазоне.
Наборы иконок
Вы можете использовать наборы значков для визуализации числовых различий. Доступны следующие наборы значков:
Как вы заметили, набор иконок состоит из трех-пяти символов. Вы можете определить критерии, чтобы связать значок с каждым значением в диапазоне ячеек. Например, красная стрелка вниз для небольших чисел, зеленая стрелка вверх для больших чисел и желтая горизонтальная стрелка для промежуточных значений.
-
Выберите диапазон для условного форматирования.
-
Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».
-
Нажмите Наборы иконок в раскрывающемся меню. Появятся параметры « Наборы значков» .
-
Нажмите три цветные стрелки.
Выберите диапазон для условного форматирования.
Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».
Нажмите Наборы иконок в раскрывающемся меню. Появятся параметры « Наборы значков» .
Нажмите три цветные стрелки.
Цветные стрелки отображаются рядом с данными на основе значений в выбранном диапазоне.
-
Повторите первые три шага. Появятся параметры « Наборы значков» .
-
Выберите 5 оценок. Значки рейтинга отображаются рядом с данными на основе значений в выбранном диапазоне.
Повторите первые три шага. Появятся параметры « Наборы значков» .
Выберите 5 оценок. Значки рейтинга отображаются рядом с данными на основе значений в выбранном диапазоне.
Новое правило
Вы можете использовать Новое правило, чтобы создать собственную формулу в качестве условия для форматирования ячейки, которую вы определяете.
Есть два способа использовать новое правило —
-
С опцией New Rule из выпадающего меню
-
Кнопка « Новое правило» в диалоговом окне « Управление правилами »
С опцией New Rule из выпадающего меню
Кнопка « Новое правило» в диалоговом окне « Управление правилами »
С опцией New Rule из выпадающего меню
-
Выберите диапазон для условного форматирования.
-
Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».
-
Нажмите Новое правило в раскрывающемся меню.
Выберите диапазон для условного форматирования.
Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».
Нажмите Новое правило в раскрывающемся меню.
Откроется диалоговое окно « Новое правило форматирования ».
-
В поле «Выбрать тип правила» выберите «Использовать формулу», чтобы определить ячейки для форматирования. Откроется окно « Редактировать описание правила» .
-
В значениях формата, где эта формула верна: введите формулу.
-
Нажмите кнопку форматирования и нажмите ОК.
В поле «Выбрать тип правила» выберите «Использовать формулу», чтобы определить ячейки для форматирования. Откроется окно « Редактировать описание правила» .
В значениях формата, где эта формула верна: введите формулу.
Нажмите кнопку форматирования и нажмите ОК.
Ячейки, которые содержат значения с формулой TRUE, отформатированы как определено.
С помощью кнопки «Создать правило» в диалоговом окне «Управление правилами»
-
Выберите диапазон для условного форматирования.
-
Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».
-
Нажмите « Управление правилами» в раскрывающемся меню.
Выберите диапазон для условного форматирования.
Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».
Нажмите « Управление правилами» в раскрывающемся меню.
Откроется диалоговое окно диспетчера правил условного форматирования .
Нажмите кнопку « Новое правило» .
Откроется диалоговое окно « Новое правило форматирования ».
Повторите шаги, указанные выше, чтобы определить формулу и формат.
Откроется диалоговое окно диспетчера правил условного форматирования с выделенным определенным новым правилом . Нажмите кнопку Применить .
Ячейки, которые содержат значения с формулой TRUE, отформатированы как определено.
Очистить правила
Вы можете очистить правила, чтобы удалить все условные форматы, которые вы создали для
- Выбранные клетки
- Текущий рабочий лист
- Выбранная таблица
- Выбранная сводная таблица
Выполните данные шаги —
-
Выберите диапазон / щелкните рабочий лист / щелкните таблицу> сводную таблицу, где необходимо удалить правила условного форматирования.
-
Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».
-
Нажмите Clear Rules в раскрывающемся меню. Появятся параметры очистки правил.
Выберите диапазон / щелкните рабочий лист / щелкните таблицу> сводную таблицу, где необходимо удалить правила условного форматирования.
Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».
Нажмите Clear Rules в раскрывающемся меню. Появятся параметры очистки правил.
Выберите подходящий вариант. Условное форматирование очищается из диапазона / рабочего листа / таблицы / сводной таблицы.
Управлять правилами
Вы можете управлять правилами из окна диспетчера правил условного форматирования . Вы можете увидеть правила форматирования для текущего выбора, для всей текущей рабочей таблицы, для других рабочих таблиц в рабочей книге или таблиц или сводных таблиц в рабочей книге.
-
Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».
-
Нажмите « Управление правилами» в раскрывающемся меню.
Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».
Нажмите « Управление правилами» в раскрывающемся меню.
Откроется диалоговое окно диспетчера правил условного форматирования .
Щелкните стрелку в поле «Список» рядом с надписью «Показать правила форматирования для текущего выбора», «Этот рабочий лист и другие листы», «Таблицы», «Сводная таблица», если они существуют с правилами условного форматирования.
Выберите этот лист из раскрывающегося списка. Правила форматирования на текущем рабочем листе отображаются в том порядке, в котором они будут применены. Вы можете изменить этот порядок, используя стрелки вверх и вниз.
Вы можете добавить новое правило, отредактировать правило и удалить правило.
Вы уже видели Новое правило в предыдущем разделе. Вы можете удалить правило, выбрав Правило и нажав Удалить правило . Выделенное правило будет удалено.
Чтобы отредактировать правило, выберите ПРАВИЛО и нажмите « Изменить правило». Откроется диалоговое окно « Редактировать правило форматирования ».
Вы можете
Выберите тип правила
Изменить описание правила
Изменить форматирование
Как только вы закончите с изменениями, нажмите OK.
Изменения в правиле будут отражены в диалоговом окне диспетчера правил условного форматирования . Нажмите Применить .
Данные будут выделены на основе измененных правил условного форматирования .