Анализ данных в Excel с помощью условного форматирования

При работе в Excel часто бывает нужно быстро проанализировать данные в таблицах. Например, найти события, произошедшие после указанной даты или текстовые повторения в диапазоне ячеек. Или более «сложный случай»: быстро проанализировать таблицу однородных данных.

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

А чтобы вам было удобнее, подготовили пример файла Excel: https://clck.ru/YThh9

1. Выделение ячеек с определенными значениями

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

Если начать искать нужные ячейки, выделяя их мышкой, то потратим время и, скорее всего, допустим ошибки. Да и в работе обычно используются таблицы гораздо больших размеров. Для решения такой задачи в Условных форматах есть специальная опция – Правила выделения ячеек. Чтобы настроить форматирование ячеек, перейдите в Excel на вкладку меню Главная -> выберите Условное форматирование.

Пример 1. Найти значения больше заданного числа.
Итак, чтобы найти все ячейки с цифрами более 150 тысяч, достаточно:

• выделить столбец с данными;
• перейти в меню Главная -> Условное форматирование -> Правила выделения ячеек -> Больше…
• в открывшемся окне ввести число, данные больше которого хотим увидеть. И указать, каким цветом должны быть выделены ячейки.

Пример 2. Выделить текст с указанными словами.
Для поиска необходимого текста поступаем точно так же:

• выделить столбец с данными;
• перейти в меню Главная -> Условное форматирование -> Правила выделения ячеек -> Текст содержит
• в появившемся окне указать искомый текст (слово «материал») и, если нужно, задать формат.

Пример 3. Найти повторения.
Такой же принцип и с повторяющимися значениями:

• выделить ячейки, где нужно найти повторения;
• перейти в меню Главная -> Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения
• выбрать формат.

В итоге за несколько щелчков мышкой получаем уйму сэкономленного времени и результат – маркированные данные!

2. Анализ числовых данных

Работая с большими таблицами, не всегда получается их данные «схватить на лету». Ниже пример простой таблицы. Глядя на неё не очень-то понятно, когда было хорошо, когда плохо и по каким подразделениям.

Пример 1. Анализ данных с помощью цветовых шкал.
Для того, чтобы проанализировать числовые данные, нам потребуется условное форматирование:

• выделите ячейки с цифрами, кроме строки итогов (чтобы исходные данные на фоне итогов не смотрелись заниженными);
• перейдите в меню Главная -> Условное форматирование -> Цветовые шкалы и выберите правило форматирования, например «зеленый-белый-красный».

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

Кстати, в меню Главная -> Условное форматирование -> Управление правилами можно поменять правила форматирования. Например, цвета в таблице получились слишком яркими, их можно сделать менее насыщенными.

Еще в меню или в окне «Диспетчер правил условного форматирования» можно добавить новые правила или очистить условные форматы с помощью кнопки Удалить правила.

Пример 2. Добавить гистограммы в таблицу.
Теперь можно проанализировать структуру продаж по направлениям:

• выделить столбец с данными по полугодию;
• перейти на вкладку Главная -> Условное форматирование -> Гистограммы, выберите любую понравившуюся;
• если нужно, поменять цвет гистограмм в окне «Диспетчер правил условного форматирования».

Пример 3. Анализ данных с помощью наборов значков.
Теперь строка итогов. Ее мы разметим с помощью значков. Путь тот же:

• выделяем строку итогов по месяцам (без полугодия);
• меню Главная -> Условное форматирование -> Наборы значков -> любой подходящий;
• чтобы не перегружать таблицу значками, в меню Главная -> Условное форматирование -> Управление правилами задаем, чтобы в строке выводился только один значок рядом с наибольшими значениями.

В результате получится таблица, как на рисунке ниже:

Инструменты быстрого анализа

Настроить условное форматирование в Excel можно также в меню Быстрого анализа (или экспресс-анализа). Чтобы перейти к этому меню, выделите таблицу с данными и нажмите на появившийся внизу справа значок. В меню Быстрого анализа на вкладке Форматирование настраиваются условные форматы, а на других вкладках доступны другие инструменты – диаграммы, спарклайны, строка итогов и сводные таблицы.

3. Визуальный анализ в таблицах

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

Пример 1. Анализ структуры показателей.

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

Используем условные форматы, как в предыдущих примерах:

• выделить столбец «% к итогу» по количеству заказов, без итоговой строки;
• меню Главная -> Условное форматирование -> Гистограммы, выберите любую понравившуюся;
• в окне настройки правил форматирования выберите не слишком яркий цвет и поставьте галочку «Показывать только столбец» — после этого в столбце останутся только гистограммы, а цифры «исчезнут».

Таким же образом настраиваются гистограммы для каждого столбца «% к итогу». Готово! Структура показателей выглядит понятнее, чем в предыдущем варианте таблицы.

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

Пример 2. Анализ рейтингов.

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

Такое форматирование настраивается с помощью двухцветной шкалы, как на рисунке:

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

Так, используя простые инструменты Excel, за 3–5 кликов мышкой можно проанализировать большие объемы информации и сделать отчеты интуитивно понятными.

Больше примеров и инструкций в Telegram: https://t.me/finalyticspro

Источник: m.vk.com

Комментарии: