Руководство по условному форматированию в Excel

Как

ПоРайан Дубе

Последнее обновление15 января 2020 г.

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

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

Изменение цвета ячейки с условным форматированием

Один из наиболее распространенных способов использования условного форматирования в Excel - это Highlight Cell Rules. В качестве примера, скажем, учитель использует электронную таблицу, чтобы вести учет оценок за тест.

Как простая электронная таблица, учитель может просто просмотреть лист, чтобы увидеть, какие учащиеся сдали или не прошли тест. Или, творческий учитель может включить правило «выделение ячеек», которое выделяет проходные или неуспешные оценки с соответствующим цветом - красным или зеленым.

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

В этом примере учитель хочет выделить ячейки зеленым, если они превышают проходной балл, который находится в ячейке D2. Выделите все ячейки в столбце B (кроме заголовка) и выберите Больше чем от Выделите правила ячеек меню.

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

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

Но как насчет плохих оценок? Для этого вам нужно выбрать те же ячейки и повторить процесс, описанный выше, но выбрать правило «меньше чем». Выберите ту же ячейку прохождения класса и сделайте цвет Light Red с темно-красным текстом.

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

Использование верхних / нижних правил в Excel

Еще одно очень полезное правило условного форматирования в Excel - это правила «сверху / снизу». Это позволяет анализировать любой длинный список данных и ранжировать этот список с точки зрения любого из следующего:

• Топ 10 предметов
• Нижние 10 пунктов
• Топ 10%
• Нижняя 10%
• Выше среднего
• Ниже среднего

Например, допустим, у вас есть список книг бестселлеров New York Times вместе с оценками рецензентов в электронной таблице. Вы можете использовать правила Top / Bottom, чтобы увидеть, какие книги оцениваются как одни из 10 лучших или 10 худших в списке.

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

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

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

Когда вы закончите, вы сразу увидите список с самым высоким и самым низким рейтингом в списке.

Выделение самых высоких или самых низких элементов позволяет вам сортировать список так, как вам нравится, но вы по-прежнему можете видеть сортировку (самую высокую или самую низкую) с первого взгляда. Это также чрезвычайно полезно, когда вы используете правило выше среднего или ниже среднего.

Использование условного форматирования панели данных

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

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

Для этого выделите весь столбец данных и выберите Панели данных от Условное форматирование меню.

Вы можете выбрать один из двух вариантов форматирования панели данных.

• Градиентная заливка: заполнит ячейки затененным градиентным узором.
• Сплошная заливка: заполнение ячеек сплошным цветом.

Чтобы настроить это, просто выберите столбец данных, к которому вы хотите применить заливку, и выберите параметр градиентной заливки или сплошной заливки в меню «Строки условного форматирования данных».

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

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

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

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

Чтобы применить это форматирование, просто выберите диапазон ячеек, к которым вы хотите применить форматирование, а затем выберите цвет из Цветовые шкалы в Условное форматирование меню.

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

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

Как использовать наборы иконок в Excel

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

в Наборы иконок меню в Форматирование условий, вы можете выбирать из широкого спектра наборов иконок.

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

Эти цвета полностью настраиваемые, если вы решите. Чтобы настроить, просто выберите столбец данных и выберите Управлять правилами в меню условного форматирования.

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