При работе в Excel часто бывает нужно быстро проанализировать данные в таблицах. Например, найти события, произошедшие после указанной даты или найти текстовые повторения в диапазоне ячеек. Или более «сложный случай»: быстро проанализировать таблицу однородных данных.
Как это сделать? В Excel имеется набор инструментов, который позволит это выполнить быстро – буквально за пару-тройку щелчков мыши. Самый простой из таких инструментов – условное форматирование. Давайте разберем ситуации, в которых пригодятся условные форматы.
1. Выделение ячеек с определенными значениями
Предположим, у нас есть три столбца с данными. В первом нужно найти значения больше 150 тысяч, во втором – текст с указанным словом, в третьем – повторения.
Сумма более 150 тыс.
Cлово «материал»
Повторения
Если начать искать нужные ячейки, выделяя их мышкой, то потратим время и, скорее всего, допустим ошибки. Да и в работе обычно используются таблицы гораздо больших размеров. Для решения такой задачи в Условных форматах есть специальная опция – Правила выделения ячеек. Чтобы настроить форматирование ячеек, перейдите в Excel на вкладку меню Главная → выберите Условное форматирование.
Пример 1. Найти значения больше заданного числа.
Итак, чтобы найти все ячейки с цифрами более 150 тысяч, достаточно:
- выделить столбец с данными;
- перейти в меню Главная → Условное форматирование → Правила выделения ячеек → Больше…
- в открывшемся окне ввести число, данные больше которого хотим увидеть. И указать, каким цветом должны быть выделены ячейки.
Пример 2. Выделить текст с указанными словами.
Для поиска необходимого текста поступаем точно так же:
- выделить столбец с данными;
- перейти в меню Главная → Условное форматирование → Правила выделения ячеек → Текст содержит
- в появившемся окне указать искомый текст (слово «материал») и, если нужно, задать формат.
Пример 3. Найти повторения.
Такой же принцип и с повторяющимися значениями:
- выделить ячейки, где нужно найти повторения;
- перейти в меню Главная → Условное форматирование → Правила выделения ячеек → Повторяющиеся значения
- выбрать формат.
В итоге за несколько щелчков мышкой получаем уйму сэкономленного времени и результат – маркированные данные!
Сумма более 150 тыс.
Cлово «материал»
Повторения
2. Анализ числовых данных
Работая с большими таблицами, не всегда получается их данные «схватить на лету». Ниже пример простой таблицы. Глядя на нее не очень-то понятно, когда было хорошо, когда плохо и по каким подразделениям.
Пример 1. Анализ данных с помощью цветовых шкал.
Для того, чтобы проанализировать числовые данные, нам потребуется условное форматирование:
- выделите ячейки с цифрами, кроме строки итогов (чтобы исходные данные на фоне итогов не смотрелись заниженными);
- перейдите в меню Главная → Условное форматирование → Цветовые шкалы и выберите правило форматирования, например «зеленый-белый-красный».
В итоге получится таблица с разметкой данных. Согласитесь, глазу такой светофор нагляднее. Появляется интуитивное понимание цифр.
Кстати, в меню Главная → Условное форматирование → Управление правилами можно поменять правила форматирования. Например, цвета в таблице получились слишком яркими, их можно сделать менее насыщенными.
Еще в меню или в окне «Диспетчер правил условного форматирования» можно добавить новые правила или очистить условные форматы с помощью кнопки Удалить правила.
Пример 2. Добавить гистограммы в таблицу.
Теперь можно проанализировать структуру продаж по направлениям:
- выделить столбец с данными по полугодию;
- перейти на вкладку Главная → Условное форматирование → Гистограммы, выберите любую понравившуюся;
- если нужно, поменять цвет гистограмм в окне «Диспетчер правил условного форматирования».
Пример 3. Анализ данных с помощью наборов значков.
Теперь строка итогов. Ее мы разметим с помощью значков. Путь тот же:
- выделяем строку итогов по месяцам (без полугодия);
- меню Главная → Условное форматирование → Наборы значков → любой подходящий;
- чтобы не перегружать таблицу значками, в меню Главная → Условное форматирование → Управление правилами задаем, чтобы в строке выводился только один значок рядом с наибольшими значениями.
В результате получится таблица, как на рисунке ниже:
Инструменты быстрого анализа
Настроить условное форматирование в Excel можно также в меню Быстрого анализа (или экспресс-анализа). Чтобы перейти к этому меню, выделите таблицу с данными и нажмите на появившийся внизу справа значок. В меню Быстрого анализа на вкладке Форматирование настраиваются условные форматы, а на других вкладках доступны другие инструменты – диаграммы, спарклайны, строка итогов и сводные таблицы.
3. Визуальный анализ в таблицах
С помощью условного форматирования в Excel можно настроить вид таблиц, удобный для анализа данных. Например, чтобы проанализировать структуру показателей по нескольким различным параметрам.
Пример 1. Анализ структуры показателей.
Предположим, у нас есть данные по группам покупателей – количество заказов, выручка и дебиторская задолженность. Используя условное форматирование, гистограммы и значки, можно быстро провести анализ.
Используем условные форматы, как в предыдущих примерах:
- выделить столбец «% к итогу» по количеству заказов, без итоговой строки;
- меню Главная → Условное форматирование → Гистограммы, выберите любую понравившуюся;
- в окне настройки правил форматирования выберите не слишком яркий цвет и поставьте галочку «Показывать только столбец» — после этого в столбце останутся только гистограммы, а цифры «исчезнут».
Таким же образом настраиваются гистограммы для каждого столбца «% к итогу». Готово! Структура показателей выглядит понятнее, чем в предыдущем варианте таблицы.
Точно так же, с помощью формул и условного форматирования, в таблице можно найти покупателей, у которых большая оборачиваемость дебиторской задолженности, например, более 30 дней. Чтобы обратить внимание на такую задолженность, рядом с ней можно поставить красный кружок и выделить число цветом. Для удобства красные кружки вынесены в отдельный столбец.
Пример 2. Анализ рейтингов.
Используя условное форматирование, можно настраивать цветовую разметку таблиц по аналогии с тепловыми картами, где значения показаны одним цветом, но разной интенсивности. Степень интенсивности цвета задается в зависимости от числовых значений – чем больше значение, тем выше интенсивность, и наоборот.
Такое форматирование настраивается с помощью двухцветной шкалы, как на рисунке:
Еще один вариант настройки форматов в такой таблице – задать несколько правил форматирования для группы ячеек. В этом случае можно дополнительно указать цвет шрифта, чтобы при заполнении ячеек цветом высокой интенсивности шрифт был более светлым. Это улучшит читаемость цифр в таблице.
Так, используя простые инструменты Excel, за 3–5 кликов мышкой можно проанализировать большие объемы информации и сделать отчеты интуитивно понятными.