Анализ данных в Excel с помощью условного форматирования
При работе в Excel часто бывает нужно быстро проанализировать данные в таблицах. Например, найти события, произошедшие после указанной даты или найти текстовые повторения в диапазоне ячеек. Или более «сложный случай»: быстро проанализировать таблицу однородных данных.
Как это сделать? В Excel имеется набор инструментов, который позволит это выполнить быстро – буквально за пару-тройку щелчков мыши. Самый простой из таких инструментов – условное форматирование. Давайте разберем ситуации, в которых пригодятся условные форматы.
1. Выделение ячеек с определенными значениями
Предположим, у нас есть три столбца с данными. В первом нужно найти значения больше 150 тысяч, во втором – текст с указанным словом, в третьем – повторения.
Excel works!
Excel выделение цветом ячеек по условиям. Условное форматирование
Октябрь 5, 2015 / Написал Izotov / 9 комментариев
Нужно выделить повторяющиеся значения в столбце? Надо выбрать первые 5 максимальных ячеек? Необходимо сделать термальную шкалу для наглядности (цвет меняется в зависимости от увеличения/уменьшения значения ячеек)? В Excel выделение цветом ячеек по условиям можно сделать очень быстро и просто. За выделение цветом ячеек отвечает специальная функция «Условное форматирование». Настоятельно рекомендую! Подробнее читаем дальше:
Основные возможности я описал в начале статьи, но на самом деле их масса. Подробнее о самых полезных
Условное форматирование, где найти?
Для начала, на ленте задач в главном меню найдите раздел Стили и нажмите на кнопку Условное форматирование.
При нажатии откроется меню, с разными вариантами этого редактирования. Как вы видите, возможностей здесь действительно много.
Теперь подробнее о самых полезных:
Excel выделение цветом ячеек по условиям. Простые условия
Для этого зайдите в пункт Правила выделения ячеек. Если к примеру, вам нужно выделить все ячейки больше 100, нажмите кнопку Больше. В окне:
по умолчанию условия, предлагается выделить красным цветом, но вы можете задать нужное форматирование ячеек нажав в правом окошке и выбрав необходимы вариант.
Выделение повторяющихся значений, в т.ч. по нескольким столбцам
Чтобы выделить все повторяющиеся значения выберите соответствующее меню Повторяющиеся значения.
Далее снова появиться окошко с форматированием. Настройте как вам удобно. Можно выделить, например, только уникальные. Значения и курсивом (пользовательский формат)
Что делать если необходимо найти повторения по двум и более столбцам, например когда ФИО в разных столбцах? Сделайте еще один столбец и объедините значения формулой =СЦЕПИТЬ() , т.е. в отдельной ячейке у вас будет написано ИвановИванИваныч. По такому столбцу вы уже легко сможете выделить повторяющиеся значения. Важно понимать, что если порядок слов будет различаться, то Excel сочтет такие строки неповторяющимися (например, ИванИванычИванов).
Выделение цветом первых/последних значений. Опять же условное форматирование
Для этого зайдите в пункт Правила отбора первых и последних ячеек и выберите нужный пункт. Помимо того, что можно выделить первые/последние значения (в том числе и по процентам), можно использовать возможность выделить данные выше и ниже среднего (пользуюсь даже чаще). Очень удобно для просмотра результатов отличающихся от нормы или среднего!
Построение термальной диаграммы и гистограммы
Классная функция для визуализации данных — термальная/температурная диаграмма. Суть в том, что в зависимости от величины значения в столбце или строке, ячейка подсвечивается определенным оттенком цвета, чем больше, тем краснее, например. Таблицы воспринимаются гораздо лучше на глаз, а принимать решение становится проще. Ведь один из лучших анализаторов зачастую — это наш глаз, соответственно, мозг, а не машина!
Гистограмма в ячейке (голубым на рисунке ниже) тоже крайне полезная функция, для выявления изменения значений и сравнения их.
Рекомендую. Для презентаций и аналитики — гистограммы в ячейках и термальные диаграммы основа простой визуализации при помощи Excel.
Выделение цветом ячеек, содержащих определенный текст
Очень часто нужно найти ячейки, которые содержат определенный набор символов, можно конечно воспользоваться функцией = ПОИСК() , но проще и быстрее применить условное форматирование, пройдите — Правила отбора ячеек — Текст содержит
Очень полезно при работе с текстом. Пример, когда в столбце у вас записаны ФИО сотрудников, а надо отобрать всех коллег Ивановых. Выделяем ячейки заходим в нужный пункт и выделяем содержащий текст Иванов, после чего фильтруем таблицу по цвету
Excel выделение цветом. Фильтр по цвету
Помимо вышеперечисленных возможностей вы можете отфильтровать выделенные ячейки по цвету обычном фильтром. К моему удивлению об этом очень мало кто знает — видимо отголоски версии 2003 — там этой возможности не было.
Подробнее о фильтрах в этой статье .
Проверка условий форматирования
Чтобы проверить какие условные форматирования у Вас заданы, пройдите Главная — Условное форматирование — Управление правилами. Здесь вы сможете отредактировать уже заданные условия, диапазон применения, а также выбрать приоритет заданного форматирования (кто выше, тот главнее, изменить можно кнопками — стрелками).
Неверный диапазон условного форматирования
Важно! Условное форматирование при неправильном использовании зачастую является причиной сильных тормозов Excel . Происходит задвоение форматирований, для примера если вы много раз копируете ячейки с выделением цветом. Тогда у вас появится множество условий с цветом. Я сам видел более 3 тысяч условий — тормозил файл безобразно. Также файл может тормозить, когда задан диапазон как на картинке выше, лучше, указывать A:A — для всего диапазона.
Подробнее о тормозах Excel и их причинах читайте здесь . Эта статья помогла не одной сотне людей 😉
Надеюсь был полезен, не прощаюсь!
Похожие статьи
- 07.08.2015Табель рабочего времени в ExcelPosted in Готовые работы
- 17.05.2016Счет-фактура в ExcelPosted in Готовые работы
- 25.10.2016Альтернатива Excel. Чем его заменить?Posted in Excel, Онлайн
- 12.05.2016Онлайн Excel бесплатно. Office 365 online. Excel на Яндекс.Диске и Облаке MailPosted in Онлайн
- 28.03.2016Бланк Счета в Excel. Использование документа СчетPosted in Готовые работы
- 10.08.2016PDF в Excel и в Word онлайн бесплатноPosted in Работа с текстом, Онлайн, Word
- 25.03.2015Как cчитать НДС в Excel? Что такое НДС?Posted in Готовые работы, Мануалы
- 26.01.2017Как добавить фигуру в Excel? Применение автофигурPosted in Оформление
Как в Excel выделить ячейку цветом при определенном условии: примеры и методы
Не все фирмы покупают специальные программы для ведения дел. Многие пользуются MS Excel, ведь эта хо.
Не все фирмы покупают специальные программы для ведения дел. Многие пользуются MS Excel, ведь эта хорошо приспособлена для больших информационных баз. Практика показала, что дальше заполнения таблиц доходит редко. Таблица растет, информации становится больше и возникает необходимость быстро выбрать только нужную. В подобной ситуации встает вопрос как в Excel выделить ячейку цветом при определенном условии, применить к строкам цветовые градиенты в зависимости от типа или наименования поставщика, сделать работу с информацией быстрой и удобной? Подробнее читаем ниже.
Где находится условное форматирование
Как в экселе менять цвет ячейки в зависимости от значения – да очень просто и быстро. Для выделения ячеек цветом предусмотрена специальная функция «Условное форматирование», находящаяся на вкладке «Главная»:
Условное форматирование включает в себя стандартный набор предусмотренных правил и инструментов. Но главное, разработчик предоставил пользователю возможность самому придумать и настроить необходимый алгоритм. Давайте рассмотрим способы форматирования подробно.
Правила выделения ячеек
С помощью этого набора инструментов делают следующие выборки:
- находят в таблице числовые значения, которые больше установленного;
- находят значения, которые меньше установленного;
- находят числа, находящиеся в пределах заданного интервала;
- определяют значения равные условному числу;
- помечают в выбранных текстовых полях только те, которые необходимы;
- отмечают столбцы и числа за необходимую дату;
- находят повторяющиеся значения текста или числа;
- придумывают правила, необходимые пользователю.
Посмотрите, как ищется выбранный текст: в первом поле задается условие, а во втором указывают, каким образом выделить полученный результат. Обратите внимание, выбрать можно цвет фона и текста из предложенных в списке. Если хочется применить иные оттенки – сделать это можно перейдя в «Пользовательский формат». Аналогичным образом реализуются все «Правила выделения ячеек».
Очень творчески реализуются «Другие правила»: в шести вариантах сценария придумывайте те, которые наиболее удобны для работы, например, градиент:
Устанавливаете цветовые сочетания для минимальных, средних и максимальных величин – получаете на выходе градиентную окраску значений. Пользоваться градиентом во время анализа информации комфортно.
Правила отбора первых и последних значений.
Рассмотрим вторую группу функций «Правила отбора первых и последних значений». В ней вы сможете:
- выделить цветом первое или последнее N-ое количество ячеек;
- применить форматирование к заданному проценту ячеек;
- выделить ячейки, содержащие значение выше или ниже среднего в массиве;
- во вкладке «Другие правила» задать необходимый функционал.
Гистограммы
Если заливка ячейки цветом вас не устраивает – применяйте инструмент «Гистограмма». Предлагаемая окраска легче воспринимается на глаз в большом объеме информации, функциональные правила подстраиваются под требования пользователя.
Цветовые шкалы
Этот инструмент быстро формирует градиентную заливку показателей по выбору от большего к меньшему или наоборот. При работе с ним устанавливаются необходимые процентные отношения, либо текстовые значения. Предусмотрены готовые образцы градиента, но пользовательский подход опять же реализуется в «Других правилах».
Наборы значков
Если вы любитель смайликов и эмодзи, воспринимаете картинки лучше, чем цвета – разработчиками предусмотрены наборы значков в соответствующем инструменте. Картинок немного, но для полноценной работы хватает. Изображения стилизованы под светофор, знаки восклицания, галочки-крыжики, крестики для того, чтобы пометить удаление – несложный и интуитивный подход.
Создание, удаление и управление правилами
Функция «Создать правило» полностью дублирует «Другие правила» из перечисленных выше, создает выборку изначально по требованию пользователя.
С помощью вкладки «Удалить правило» созданные сценарии удаляются со всего листа, из выбранного диапазона значений, из таблицы.
Вызывает интерес инструмент «Управление правилами» – своеобразная история создания и изменения проведенных форматирований. Меняйте подборки, делайте правила неактивными, возвращайте обратно, чередуйте порядок применения. Для работы с большим объемом информации это очень удобно.
Отбор ячеек по датам
Чтобы разобраться, как в excel сделать цвет ячейки от значения установленной даты, рассмотрим пример с датами закупок у поставщиков в январе 2019 года. Для применения такого отбора нужны ячейки с установленным форматом «Дата». Для этого перед внесением информации выделите необходимый столбец, щелкните правой кнопкой мыши и в меню «Формат ячеек» найдите вкладку «Число». Установите числовой формат «Дата» и выберите его тип по своему усмотрению.
Для отбора нужных дат применяем такую последовательность действий:
- выделяем столбцы с датами (в нашем случае за январь);
- находим инструмент «Условное форматирование»;
- в «Правилах выделения ячеек» выбираем пункт «Дата»;
- в правой части форматирования открываем выпадающее окно с правилами;
- выбираем подходящее правило (на примере выбраны даты за предыдущий месяц);
- в левом поле устанавливаем готовый цветовой подбор «Желтая заливка и темно-желтый текст»
- выборка окрасилась, жмем «ОК».
С помощью форматирования ячеек, содержащих дату, можно выбрать значения по десяти вариантам: вчера/сегодня/завтра, на прошлой/текущей/следующей неделе, в прошлом/текущем/следующем месяце, за последние 7 дней.
Выделение цветом столбца по условию
Для анализа деятельности фирмы с помощью таблицы разберем на примере как поменять цвет ячейки в excel в зависимости от условия, заданного работником. В качестве примера возьмем таблицу заказов за январь 2019 года по десяти контрагентам.
Нам необходимо пометить синим цветом тех поставщиков, у которых мы купили товара на сумму большую, чем 100 000 рублей. Чтобы сделать такую выборку воспользуемся следующим алгоритмом действий:
- выделяем столбец с январскими закупками;
- кликаем инструмент «Условное форматирование»;
- переходим в «Правила выделения ячеек»;
- пункт «Больше…»;
- в правой части форматирования устанавливаем сумму 100 000 рублей;
- в левом поле переходим на вкладку «Пользовательский формат» и выбираем синий цвет;
- необходимая выборка окрасилась в синий цвет, жмем «ОК».
Инструмент «Условное форматирование» применяется для решения ежедневных задач бизнеса. С его помощью анализируют информацию, подбирают необходимые компоненты, проверяют сроки и условия взаимодействия поставщика и клиента. Пользователь сам придумывает нужные для него комбинации.
Немаловажную роль играет цветовое оформление, ведь в белой таблице с большим объемом данных сложно ориентироваться. Если придумать последовательность цветов и знаков, то информативность сведений будет восприниматься почти интуитивно. Скрины с таких таблиц будут наглядно смотреться в отчетах и презентациях.
Как выделить цветом числа в эксель больше определенного значения в excel
Подсветить (подкрасить) ячейку в зависимости от значения в другой
Для того, чтобы настроить подкрашивание (или любое другое изменение формата) определенной ячейки, в зависимости от значения в ДРУГОЙ ячейке,
необходимо воспользоваться специальными настройками условного форматирования.
В примере – настроим автоматическое подкрашивание ячейки с цифрой фактического значения, если выполнение плана окажется ниже 80%.
-
Выделим ячейку, которую будем подкрашивать в зависимости от другой ячейки.
В открывшемся окне выберем «Использовать формулу для определения форматируемых ячеек».
В поле «Форматировать значения, для которых…» введем условие так, как если бы вставляли его в функцию ЕСЛИ, только вначале вставляем «=». (=$C$6<80%) Обратите внимание, что EXCELавтоматически фиксирует ячейку, учитывайте это, если планируете «протягивать» условное форматирование в ваших задачах.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)