Как в Excel выделить ячейки цветом по условию

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

Естественно это задание для Экселя. Программка обязана автоматом отыскать таковых контрагентов и соответственно выделить их цветом. Для этих критерий будем употреблять условное форматирование.

Автоматическое наполнение ячеек датами

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

Юзеру лишь нужно указать если клиент совершал заказ в текущем месяце, то в подобающую ячейку следует вводить текстовое значение «заказ». Основное условие для выделения: если в протяжении 3-х месяцев контрагент не сделал ни 1-го заказа, его номер автоматом выделяется красноватым цветом.

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

Автоматическое наполнение ячеек животрепещущими датами

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

Как работает формула для автоматической генерации уходящих месяцев?

На рисунке формула возвращает период уходящего времени начиная даты написания статьи: 17.09.2017. В первом аргументе в функции DATA – вложена формула, которая постоянно возвращает текущий год на нынешнюю дату благодаря функциям: ГОД и СЕГОНЯ. Во 2-м аргументе указан номер месяца (-1). Отрицательное число означает, что нас интересует какой был месяц в прошедшем времени. Пример критерий для второго аргумента со значением:

• 1 – означает 1-ый месяц (январь) в году обозначенном в первом аргументе;
• 0 – это 1 месяца вспять;
• -1 – это 2 мес. вспять от начала текущего года (другими словами: 01.10.2016).

Крайний аргумент – это номер денька месяца обозначено во 2-м аргументе. В итоге функция ДАТА собирает все характеристики в одно значение и формула возвращает соответственную дату.

Дальше перейдите в ячейку C1 и введите последующую формулу:

Как видно сейчас функция ДАТА употребляет значение из ячейки B1 и наращивает номер месяца на 1 по отношению к предшествующей ячейки. В итоге получаем 1 – число последующего месяца.

Сейчас скопируйте эту формулу из ячейки C1 в другие заглавия столбцов спектра D1:L1.

Выделите спектр ячеек B1:L1 и изберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Формат ячеек» либо просто нажмите комбинацию кнопок CTRL+1. В показавшемся диалоговом окне, на вкладке «Число», в разделе «Числовые форматы:» изберите опцию «(все форматы)». В поле «Тип:» введите значение: МММ.ГГ (непременно буковкы в верхнем регистре). Благодаря этому мы получим укороченное отображение значения дат в заголовках регистра, что упростит зрительный анализ и сделает его наиболее удобным за счет наилучшей читабельности.

Направьте внимание! При пришествии января месяца (D1), формула автоматом меняет в дате год на последующий.

Как выделить столбец цветом в Excel по условию

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

1. Выделите спектр ячеек B2:L15 и изберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Сделать правило». А в показавшемся окне «Создание правила форматирования» изберите опцию: «Применять формулу для определения форматируемых ячеек»
2. В поле ввода введите формулу:
3. Щелкните на клавишу «Формат» и укажите на вкладке «Заливка» каким цветом будут выделены ячейки животрепещущего месяца. К примеру – зеленоватый. Опосля что на всех окнах для доказательства нажмите на клавишу «ОК».

Столбец под подходящим заголовком регистра автоматом подсвечивается зеленоватым цветом соответственно с нашими критериями:

Как работает формула выделения столбца цветом по условию?

Благодаря тому, что перед созданием правила условного форматирования мы охватили всю табличную часть для введения данных регистра, форматирование будет интенсивно для каждой ячейки в этом спектре B2:L15. Смешанная ссылка в формуле B$1 (абсолютный адресок лишь для строк, а для столбцов – относительный) обусловливает, что формула будет постоянно относиться к первой строке всякого столбца.

Автоматическое выделение цветом столбца по условию текущего месяца

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

Направьте внимание! В критериях данной нам формулы, для крайнего аргумента функции ДАТА обозначено значение 1, так же, как и для формул в определении дат для заголовков столбцов регистра.

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

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

Как выделить ячейки красноватым цветом по условию

Сейчас нам нужно выделить красноватым цветом ячейки с номерами клиентов, которые в протяжении 3-х месяцев не сделали ни 1-го заказа. Для этого:

1. Выделите спектр ячеек A2:A15 (другими словами перечень номеров клиентов) и изберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Сделать правило». А в показавшемся окне «Создание правила форматирования» изберите опцию: «Применять формулу для определения форматируемых ячеек»
2. В сей раз в поле ввода введите формулу:
3. Щелкните на клавишу «Формат» и укажите красноватый цвет на вкладке «Заливка». Опосля что на всех окнах нажмите «ОК».
4. Наводните ячейки текстовым значением «заказ» как на рисунке и поглядите на итог:

Номера клиентов подсвечиваются красноватым цветом, если в их строке нет значения «заказ» в крайних 3-х ячейках к текущему месяцу (включительно).

Анализ формулы для выделения цветом ячеек по условию:

Поначалу займемся средней частью нашей формулы. Функция СМЕЩ возвращает ссылку на спектр смещенного по отношении к области базисного спектра определенной числом строк и столбцов. Возвращаемая ссылка быть может одной ячейкой либо целым спектром ячеек. Добавочно можно найти количество возвращаемых строк и столбцов. В нашем примере функция возвращает ссылку на спектр ячеек для крайних 3-х месяцев.

Принципиальная часть для нашего условия выделения цветом находиться в первом аргументе функции СМЕЩ. Он описывает, с какого месяца начать смещение. В данном примере – это ячейка D2, другими словами начало года – январь. Естественно для других ячеек в столбце номер строчки для базисной ячейки будет соответствовать номеру строчки в котором она находиться. Последующие 2 аргумента функции СМЕЩ определяют на сколько строк и столбцов обязано быть выполнено смещение. Потому что вычисления для всякого клиента будем делать в той же строке, значение смещения для строк указываем –¬ 0.

В тоже время для вычисления значения третьего аргумента (смещение по столбцам) используем вложенную формулу МЕСЯЦ(СЕГОДНЯ()), Которая в согласовании с критериями возвращает номер текущего месяца в текущем году. От вычисленного формулой номера месяца отнимаем число 4, другими словами в случаи Ноября получаем смещение на 8 столбцов. А, к примеру, для Июня – лишь на 2 столбца.

Крайнее два аргумента для функции СМЕЩ определяют высоту (в количестве строк) и ширину (в количестве столбцов) возвращаемого спектра. В нашем примере – это область ячеек с высотой на 1-ну строчку и шириной на 4 столбца. Этот спектр обхватывает столбцы 3-х прошлых месяцев и текущий.

1-ая функция в формуле СЧЕТЕСЛИ инспектирует условия: сколько раз в возвращаемом спектре при помощи функции СМЕЩ встречается текстовое значение «заказ». Если функция возвращает значение 0 – означает от клиента с таковым номером в протяжении 3-х месяцев не было ни 1-го заказа. А в согласовании с нашими критериями, ячейка с номером данного клиента выделяется красноватым цветом заливки.

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

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

Excel works!

Excel выделение цветом ячеек по условиям. Условное форматирование

Необходимо выделить повторяющиеся значения в столбце? Нужно избрать 1-ые 5 наибольших ячеек? Нужно создать термальную шкалу для наглядности (цвет изменяется в зависимости от роста/уменьшения значения ячеек)? В Excel выделение цветом ячеек по условиям можно создать весьма стремительно и просто. За выделение цветом ячеек отвечает особая функция «Условное форматирование». Настоятельно рекомендую! Подробнее читаем далее:

Главные способности я обрисовал в начале статьи, но по сути их масса. Подробнее о самых нужных

Условное форматирование, где отыскать?

Для начала, на ленте задач в главном меню найдите раздел Стили и нажмите на клавишу Условное форматирование.

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

Сейчас подробнее о самых нужных:

Excel выделение цветом ячеек по условиям. Обыкновенные условия

Для этого зайдите в пункт Правила выделения ячеек. Если например, для вас необходимо выделить все ячейки больше 100, нажмите клавишу Больше. В окне:

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

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

Чтоб выделить все повторяющиеся значения изберите соответственное меню Повторяющиеся значения.

Дальше опять показаться окошко с форматированием. Настройте как для вас комфортно. Можно выделить, к примеру, лишь неповторимые. Значения и курсивом (пользовательский формат)

Что созодать если нужно отыскать повторения по двум и наиболее столбцам, к примеру когда ФИО в различных столбцах? Сделайте очередной столбец и соедините значения формулой =СЦЕПИТЬ() , т.е. в отдельной ячейке у вас будет написано ИвановИванИваныч. По такому столбцу вы уже просто можете выделить повторяющиеся значения. Принципиально осознавать, что если порядок слов будет различаться, то Excel сочтет такие строчки неповторяющимися (к примеру, ИванИванычИванов).

Выделение цветом первых/крайних значений. Снова же условное форматирование

Для этого зайдите в пункт Правила отбора первых и крайних ячеек и изберите подходящий пункт. Кроме того, что можно выделить 1-ые/крайние значения (в том числе и по процентам), можно употреблять возможность выделить данные выше и ниже среднего (пользуюсь даже почаще). Весьма комфортно для просмотра результатов различающихся от нормы либо среднего!

Построение термальной диаграммы и гистограммы

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

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

Рекомендую. Для презентаций и аналитики — гистограммы в ячейках и термальные диаграммы база обычной визуализации с помощью Excel.

Выделение цветом ячеек, содержащих определенный текст

Весьма нередко необходимо отыскать ячейки, которые содержат определенный набор знаков, можно естественно пользоваться функцией = ПОИСК() , но проще и резвее применить условное форматирование, пройдите — Правила отбора ячеек — Текст содержит

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

Excel выделение цветом. Фильтр по цвету

Кроме перечисленных выше способностей вы сможете отфильтровать выделенные ячейки по цвету обыкновенном фильтром. К моему удивлению о этом весьма не достаточно кто понимает — видимо отголоски версии 2003 — там данной нам способности не было.

Подробнее о фильтрах в данной нам статье .

Проверка критерий форматирования

Чтоб проверить какие условные форматирования у Вас заданы, пройдите Основная — Условное форматирование — Управление правилами. Тут вы можете отредактировать уже данные условия, спектр внедрения, также избрать ценность данного форматирования (кто выше, тот главнее, поменять можно клавишами — стрелками).

Неправильный спектр условного форматирования

Принципиально! Условное форматирование при неверном использовании часто является предпосылкой мощных тормозов Excel . Происходит задвоение форматирований, для примера если вы много раз копируете ячейки с выделением цветом. Тогда у вас покажется огромное количество критерий с цветом. Я сам лицезрел наиболее 3 тыщ критерий — тормозил файл отвратительно. Также файл может тормозить, когда задан спектр как на картинке выше, лучше, указывать A:A — для всего спектра.

Подробнее о тормозах Excel и их причинах читайте тут . Эта статья посодействовала не одной сотке людей 😉

Как в excel окрасить ячейку по условию

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

Изберите ячейки, которые необходимо выделить.

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

Щелкните Основная > стрелку рядом с клавишей Цвет заливки либо нажмите клавиши ALT+H, H.

Изберите подходящий цвет в группе Цвета темы либо Обычные цвета.

Чтоб употреблять доп цвет, изберите команду Остальные цвета, а потом в диалоговом окне Цвета изберите подходящий цвет.

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

Применение узора либо методов заливки

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

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

На вкладке Основная нажмите клавишу вызова диалогового окна Формат ячеек либо просто нажмите клавиши CTRL+SHIFT+F.

На вкладке Заливка изберите в разделе Цвет фона подходящий цвет.

Чтоб употреблять двуцветный узор, изберите цвет в поле Цвет узора, а потом изберите сам узор в поле Узор.

Чтоб сделать узор со особыми эффектами, нажмите клавишу Методы заливки и изберите нужные характеристики.

Совет: В поле Эталон можно просмотреть избранный фон, узор и метод заливки.

Удаление цвета, узора и метода заливки из ячеек

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

Цветная печать ячеек, включая цвет фона, узор и метод заливки

Если заданы характеристики печати черно-белая либо предварительная (целенаправленно либо поэтому, что книжка содержит огромные либо сложные листы и диаграммы, вследствие что предварительный режим врубается автоматом), заливку ячеек нереально вывести на печать в цвете. Ах так можно это поправить:

Откройте вкладку Разметка странички и нажмите клавишу вызова диалогового окна Характеристики странички.

На вкладке Лист в группе Печать снимите флажки черно-белая и предварительная.

Примечание: Если цвета на листе не показываются, может быть, избран высококонтрастный режим. Если цвета не показываются при подготовительном просмотре, может быть, не избран цветной принтер.

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

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

На вкладке Основная нажмите клавишу Цвет заливкии изберите подходящий цвет.

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

Удаление цвета заливки

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

Чтоб позднее удалить цвет заливки, вы изберите ячейку либо спектр ячеок, которые вы желаете поменять, и нажмите клавишу Очистить > Очистить форматы.

Доп сведения

Вы постоянно сможете задать вопросец спецу Excel Tech Community либо попросить помощи в обществе Answers community.

Сложение значений в зависимости от цвета ячеек в EXCEL

Как в Excel динамически изменять цвет ячейки, основываясь на её значении

Цвет заливки будет изменяться в зависимости от значения ячейки.

Задачка: Имеется таблица либо спектр данных, и Вы желаете поменять цвет заливки ячеек, основываясь на их значениях. Наиболее того, нужно, чтоб этот цвет изменялся динамически, отражая конфигурации данных в ячейках.

Решение: Используйте условное форматирование в Excel, чтоб выделить значения больше X, меньше Y либо меж X и Y.

Представим, есть перечень цен на бензин в различных штатах, и Вы желаете, чтоб цены, превосходящие $3.7, были выделены красноватым, а наименьшие либо равные $3.45 – зелёным.

Замечание: Снимки экрана для этого примера были изготовлены в Excel 2010, но, в Excel 2007 и 2013 клавиши, диалоговые окна и опции будут буквально такие же либо с незначимыми отличиями.

Итак, вот, что необходимо создать по шагам:

1. Выделите таблицу либо спектр, в котором Вы желаете поменять цвет заливки ячеек. В этом примере мы выделяем $B$2:$H$10 (заглавия столбцов и 1-ый столбец, содержащий наименования штатов, не выделяем).
2. Откройте вкладку Home (Основная), в разделе Styles (Стили) нажмите Conditional Formatting (Условное форматирование) > New Rule (Сделать правило).
3. В высшей части диалогового окна New Formatting Rule (Создание правила форматирования) в поле Select a Rule Type (Изберите тип правила) изберите Format only cells that contain (Форматировать лишь ячейки, которые содержат).
4. В нижней части диалогового окна в поле Format Only Cells with (Форматировать лишь ячейки, для которых производится последующее условие) настройте условия для правила. Мы избираем форматировать лишь ячейки с условием: Cell Value (Значение ячейки) – greater than (больше) – 3.7, как показано на рисунке ниже.Дальше нажмите клавишу Format (Формат), чтоб избрать, какой цвет заливки должен быть применён, если производится данное условие.
5. В показавшемся диалоговом окне Format Cells (Формат ячеек) откройте вкладку Fill (Заливка) и изберите цвет (мы избрали красный) и нажмите ОК.
6. Опосля этого Вы вернетесь в окно New Formatting Rule (Создание правила форматирования), где в поле Preview (Эталон) будет показан эталон Вашего форматирования. Если всё устраивает, нажмите ОК.

Итог Ваших опций форматирования будет смотреться примерно так:

Потому что нам необходимо настроить ещё одно условие, позволяющее изменять цвет заливки на зелёный для ячеек со значениями наименьшими либо равными 3.45, то опять жмем клавишу New Rule (Сделать правило) и повторяем шаги с 3 по 6, устанавливая необходимое правило. Ниже виден эталон сделанного нами второго правила условного форматирования:

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

Совет: Таковым же методом Вы сможете изменять цвет шрифта в зависимости от значения ячейки. Для этого просто откройте вкладку Font (Шрифт) в диалоговом окне Format Cells (Формат ячеек), как мы это делали на шаге 5 , и изберите хотимый цвет шрифта.

Как настроить неизменный цвет ячейки, основываясь на её текущем значении

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

Задачка: Вы желаете настроить цвет ячейки, основываясь на её текущем значении, и желаете, чтоб цвет заливки оставался постоянным, даже когда значение ячейки меняется.

Решение: Отыскать все ячейки с определённым значением (либо значениями) с помощью инструмента Find All (Отыскать все), а потом поменять формат отысканных ячеек, используя диалоговое окно Format Cells (Формат ячеек).

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

Отыскать и выделить все ячейки, удовлетворяющие данному условию

Здесь может быть несколько сценариев, в зависимости от того, значения какого типа Вы отыскиваете.

Если Вы желаете раскрасить ячейки с определенным значением, к примеру, 50, 100 либо 3.4 – то на вкладке Home (Основная) в разделе Editing (Редактирование) нажмите Find Select (Отыскать и выделить) > Find (Отыскать).

Введите необходимое значение и нажмите Find All (Отыскать все).

Совет: В правой части диалогового окна Find and Replace (Отыскать и поменять) есть клавиша Options (Характеристики), нажав которую Вы получите доступ к ряду продвинутых опций поиска, таковых как Match Case (Учесть регистр) и Match entire cell content (Ячейка полностью). Вы сможете употреблять знаки подстановки, такие как звёздочка (*), чтоб отыскать всякую строчку знаков, либо символ вопросца (?), чтоб отыскать один хоть какой знак.

Что касается предшествующего примера, если нам необходимо отыскать все цены на бензин от 3.7 до 3.799, то мы зададим такие аспекты поиска:

Сейчас кликните хоть какой из отысканных частей в нижней части диалогового окна Find and Replace (Отыскать и поменять) и нажмите Ctrl+A, чтоб выделить все отысканные записи. Опосля этого нажмите клавишу Close (Закрыть).

Вот так можно выделить все ячейки с данным значением (значениями) с помощью функции Find All (Отыскать все) в Excel.

Но, в реальности нам необходимо отыскать все цены на бензин, превосходящие $3.7. К огорчению, инструмент Find and Replace (Отыскать и поменять) в этом не сумеет нам посодействовать.