Привет, друзья. Форматирование ячеек в Эксель, наряду с форматированием диаграмм, по праву занимают львиную долю времени, которое мы проводим с отчетами. Чтобы сократить это время до минимума, читайте эту статью про условное форматирование. После прочтения, вы поймете, как много времени и усилий можно экономить, применяя такой инструментарий ежедневно.

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

Как это работает? Вкратце это делается так:

1. Выделите ячейку или массив ячеек, в которых нужно сделать условное форматирование;
2. На ленте найдите группу команд: Главная – Стили – Условное форматирование. В выпадающем меню собраны все возможные опции форматирования с условием;

3. Выбираете нужное правило, вводите дополнительные параметры, нажимаете ОК. Вот и все, теперь выделенным ячейкам задан условный формат, и при изменении значения в ней – формат тоже изменится.

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

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

В этой группе команд содержатся наиболее популярные правила, применяемые пользователями Microsoft Excel. Они проверяют значение в ячейке на следующие условия:

1. Больше заданного значения
2. Меньше заданного значения
3. Между заданными значениями
4. Содержит заданный текст
5. Дата соответствует заданной (или промежутку)
6. Повторяющиеся значения
7. Другие правила – откроет окно «Создание правил форматирования», о котором я расскажу чуть ниже в этой статье

Если в этом списке есть подходящее вам правило – кликните по нему, откроется окно параметров, где можно задать условие для форматирования и формат ячеек, когда это условие выполняется.

Когда вы определились с условием для форматирования, вам предложат выбрать один из стандартных стилей форматирования, или создать свой собственный. Чтобы создать свой — выберите в списке форматов «Пользовательский формат…». Откроется уже известное нам окно «Формат ячеек», в котором и задается собственное оформление.

На картинке ниже массиву чисел задано условное форматирование «Между 50 и 70». Как видим, соответствующие ячейки выделены красной цветовой палитрой.

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

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

1. Первые N элементов (N – задаете сами)
2. Первые N процентов
3. Последние N элементов
4. Последние N процентов
5. Значения выше среднего
6. Значения ниже среднего

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

Гистограммы

Гистограммы – очень удобный способ оценить соотношение данных без построения диаграммы. Ячейки будут залиты цветом в пропорции, соответствующей числу в ней. Полностью будет залита ячейка с наибольшим числом, наименьшая заливка – в ячейке с минимальным числом. Посмотрите, как это выглядит на моем примере (столбец С):

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

По умолчанию, программа предлагает 6 цветовых тем со сплошной заливкой и еще шесть – с градиентной. Нажав кнопку «Другие правила», вы можете настроить свою уникальную цветовую схему.

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

Такое форматирование очень похоже на гистограммы из предыдущего пункта, но изменяется не залитый сегмент ячейки, а цвет заливки (градиентом от одного цвета к другому). Вот, как это выглядит на предыдущем примере (от красного к синему):

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

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

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

Еще одна похожая группа форматов. Здесь в ячейке отображается значок, соответствующий значению в ней:

По умолчанию программа предлагает несколько наборов значков, а более гибкие настройки можно сделать, нажав «Другие правила».

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

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

Очень удобно это применять при оценке персонала, когда значение показателя не имеет значение, а нужно лишь понимать – справился человек, или нет.

Создать правило

Чтобы создать правило условного форматирования с наибольшим числом настроек – используем пункт «Создать правило».

Водите в это меню и выберите тип правила:

1. Форматировать все ячейки на основании их значений. Название пункта говорит само за себя. Отформатированы будут все ячейки выделенного диапазона в зависимости от их значения.

В раскрывающемся списке «Стиль формата» можно выбрать нужный вид оформления:

• Двухцветная шкала – задайте цвет для минимального и максимального значения, получите свой уникальный формат

• Трехцветная шкала – задайте уже три цвета: минимальное значение, максимальное и промежуточное

• Гистограмма. Здесь есть множество настроек, в том числе, можно выбрать свои цвета, тип и направление заливки, способ заливки отрицательных чисел. Поставьте галку «Показывать только столбец», чтобы скрыть числовые данные

• Наборы значков – выбирайте виды значков и пороги их переключения, отключайте значки для бесполезных данных и отображение числовых данных

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

3. Форматировать только первые или последние значения. Позволяет форматировать только заданное количество наименьших или наибольших значений в массиве
4. Форматировать только значения, которые находятся выше, или ниже среднего. Вычисляет среднее значение в выделенном массиве и форматирует только те значения, которые больше или меньше среднего (на ваш выбор)
5. Форматировать только уникальные или повторяющиеся значения. Думаю, название пункта отлично характеризует его функционал
6. Использовать формулу для определения форматируемых значений. Запишите логическую формулу сами, будут отформатированы лишь те значения, для которых результат формулы – истина. В моем примере отформатированы будут лишь те ячейки, для которых первое значение столбца больше 50.

Удалить правила

Чтобы удалить условное форматирование из выделенного диапазона, или со всего листа – нажмите на ленте Главная – Стили – Условное форматирование – Удалить правила, и выберите где именно нужно удалять

Управление правилами

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

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

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

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