Условное форматирование ячеек Excel в зависимости от их значений

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

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

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

• Наборы значков
• Цветовые шкалы
• Гистограммы
• Управление правилами форматирования

Хорошие новости, условное форматирование – это просто. Плохие новости, Вы должны ввести свои данные в Excel. Поверьте мне, это только поможет Вам в долгосрочной перспективе! Вот как это работает:

• На вкладке Home (Главная) в разделе Styles (Стили) нажмите Conditional Formatting (Условное форматирование). Этот пример выполнен на базе Excel 2007.
• Далее выберите, какое правило нужно применить к выбранной ячейке: Highlight (Выделение), Top/Bottom (Первые и последние значения), Data Bars (Гистограммы), Color Scales (Цветовые шкалы) или Icon Sets (Наборы значков).

Чтобы визуально проанализировать данные или быстро выделить тренды, я люблю использовать готовые варианты условного форматирования: Color Scales (Цветовые шкалы), Data Bars (Гистограммы) и Icon Sets (Наборы значков). Все эти три варианта имеют пункт More Rules (Другие правила), как это видно на рисунке.

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

В следующих примерах за основу будет взят результат работы сотрудника из примера, приведённого выше. Одно из преимуществ использования Color Scales (Цветовые шкалы), Data Bars (Гистограммы) и Icon Sets (Наборы значков) – это то, что нужные данные сразу же бросаются в глаза.

Наборы значков

Наборы значков помогут Вам представить данные с помощью 3-5 категорий, и каждый значок будет соответствовать одному из диапазонов значений. Звучит немного запутанно? Представьте себе стрелки, галочки и флажки, наглядно представляющие Ваши данные. Красная стрелка обозначает наибольшее значение, жёлтые стрелки, направленные в стороны, – средние значения, а зелёная стрелка – наименьшее значение.

Цветовые шкалы

Цветовые шкалы позволяют форматировать ячейки при помощи двух или трёх цветов. Если Вы начинающий пользователь Excel и не хотите усложнять работу, то я советую Вам выбрать один из предложенных в Excel готовых вариантов цветовой шкалы. Если же Вы хотите поразить чьё-то воображение или это Ваша кульминационная презентация, попробуйте воспользоваться пунктом More Rules (Другие правила) и поиграть немного с цветами. Здесь Вы сможете выбрать цвета, привязать их к числовой величине, а также выбрать двух- или трёхцветную шкалу для использования в таблице.

Мы решили использовать шкалу из трех цветов, чтобы увидеть, как много времени требуется сотруднику для завершения (Submit Date) порученного (Assigned Date) задания. Если время будет улучшаться (Diff), значит сотрудник дольше продержится в нашей организации.

Assigned Date – дата выдачи задания, Submit Date – дата сдачи, Diff – время, затраченное на выполнение.

Гистограммы

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

Управление правилами форматирования

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

Все еще сомневаетесь, нужно ли Вам это?

Подумайте о том, как условное форматирование поможет Вам вот в чём:

1. Бюджет: Используйте цветовые шкалы или гистограммы, чтобы показать увеличение или уменьшение расходов.
2. Украшение презентации: Добавьте несколько фигур, используя наборы значков, чтобы научить Ваши данные говорить.
3. Показатели продаж: Используйте наборы значков, чтобы видеть, какие товары находятся впереди, в середине и в хвосте по показателям продаж.

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