Условное форматирование: инструмент Microsoft Excel для визуализации данных

Глаза разбегаются, и найти что-то важное — как искать иголку в стоге сена. Условное форматирование в Excel — это как волшебный маркер, который сам подсвечивает эти самые «иголки». Он автоматически меняет цвет ячеек, шрифта или добавляет значки, основываясь на иx значениях.

Вaм не нужно вручную раскрашивать строки. Вы просто задаете правило. Например, «покрась все продажи ниже 100 000 в красный». И Excel мгновенно сделает это за вас. Самые важные числа, отклонения от плана или тенденции сразу станут заметны с первого взгляда.

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

Простейшие варианты условного форматирования

Для того, чтобы произвести форматирование определенной области ячеек, нужно выделить эту область (чаще всего столбец), и находясь во вкладке «Главная», кликнуть по кнопке «Условное форматирование», которая расположена на ленте в блоке инструментов «Стили».

Следующим шагом является открытие меню условного форматирования. В нем доступны три основных вида форматирования:

• Гистограммы;
• Цифровые шкалы;
• Значки.

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

Как видим, гистограммы появились в выделенных ячейках столбца. Чем большее числовое значение в ячейках, тем гистограмма длиннее. Кроме того, в версиях Excel 2010, 2013 и 2016 годов, имеется возможность корректного отображения отрицательных значений в гистограмме. А вот, у версии 2007 года такой возможности нет.

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

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

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

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

Правила выделения ячеек

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

Мы видим семь основных правил, когда выбираем пункт меню «Правила выделения ячеек»:

• Больше;
• Меньше;
• Равно;
• Между;
• Дата;
• Повторяющиеся значения.

Примеры применения этика действий

1. Выделим диапазон ячеек, и кликнем по пункту «Больше…».
2. 
3. Открывается окно, в котором нужно установить, значения больше какого числа будут выделяться. Делается это в поле «Форматировать ячейки, которые больше». По умолчанию, сюда автоматически вписывается среднее значение диапазона, но можно установить любое другое, либо же указать адрес ячейки, в которой содержится это число. Последний вариант подойдёт для динамических таблиц, данные в которых постоянно изменяются, или для ячейки, где применяется формула. Мы для примера установили значение в 20000.
4. 
5. В следующем поле, нужно определиться, как будут выделяться ячейки: светло-красная заливка и темно-красный цвет (по умолчанию); желтая заливка и темно-желтый текст; красный текст, и т.д. Кроме того, существует пользовательский формат.
6. 
7. При переходе на этот пункт, открывается окно, в котором можно редактировать выделения, практически, как угодно, применяя различные варианты шрифта, заливки, и границы.
8. 
9. После того, как мы определились, со значениями в окне настройки правил выделения, жмём на кнопку «OK».
10. 

Как мы можем видеть, ячейки выделены по установленному правилу.

При использовании правил «Меньше», «Между» и «Равно» значения выделяются в соответствии с тем же принципом. В первом случае ячейки выделяются меньше установленного значения; во втором — в интервале чисел, с которыми будут выделяться ячейки; и в третьем — ячейки выделяются только содержащие заданное число.

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

Правило «Дата» используется для ячеек, содержащих значения в формате даты. В настройках можно выделить ячейки по дате, например, сегодня, вчера, завтра, за последние 7 дней и т. д.

Правило «Повторяющиеся значения» позволяет регулировать выделение ячеек в зависимости от того, повторяющиеся ли данные или уникальные.

Правила отбора первых и последних значений

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

• Первые 10 элементов;
• Первые 10%;
• Последние 10 элементов;
• Последние 10%;
• Выше среднего;
• Ниже среднего.

Но после того, как вы кликнете по соответствующему пункту, вы можете немного изменить правила. Это откроет окно, в котором вы можете выбрать тип выделения и установить другую границу отбора, если вы хотите. Например, когда мы переходим по пункту «Первые 10 элементов», мы заменим число 10 в поле «Форматировать первые ячейки» на 7. Таким образом, когда вы нажмете кнопку «OK», вместо этого будут выделяться только

Создание правил

Выше мы обсуждали функции, которые уже установлены в Microsoft Excel, и пользователь может просто выбрать любую из них. Пользователь также может создавать собственные правила, если хочет.

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

Открывается окно, в котором необходимо выбрать один из шести различных типов правил:

1. Форматировать все ячейки на основании их значений;
2. Форматировать только ячейки, которые содержат;
3. Форматировать только первые и последние значения;
4. Форматировать только значения, которые находятся выше или ниже среднего;
5. Форматировать только уникальные или повторяющиеся значения;
6. Использовать формулу для определения форматируемых ячеек.

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

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

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

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