Excel если то цвет ячейки
Закрасить ячейку по условию или формуле
Для выполнения этой задачи будем использовать возможности условного форматирования.
Возьмем таблицу, содержащую список заказов, сроки их исполнения, текущий статус и стоимость. Попробуем сделать так, чтобы ее ячейки раскрашивались сами, в зависимости от их содержимого.
Инструкция для Excel 2010
ВКЛЮЧИТЕ СУБТИТРЫ!
Как это сделать в Excel 2007
ВКЛЮЧИТЕ СУБТИТРЫ!
Выделим ячейки с ценами заказов и, нажав на стрелочку рядом с кнопкой «Условное форматирование», выберем «Создать правило». 
Выберем четвертый пункт, позволяющий сравнивать текущие значения со средним. Нас интересуют значения выше среднего. Нажав кнопку «Формат», зададим цвет ячеек. 
Подтверждаем наш выбор, и ячейки с ценой выше средней окрасились в голубой цвет, привлекая наше внимание к дорогим заказам. 
Выделим ячейки со статусами заказов и создадим новое правило. На этот раз используем второй вариант, позволяющий проверять содержимое ячейки. Выберем «Текст», «содержит» и введем слово «Выполнен». Зададим зеленый цвет, подтверждаем, и выполненные работы у нас позеленели. 
Ну и сделаем еще одно правило, окрашивающее просроченные заказы в красный цвет. Выделяем даты выполнения заказов. При создании правила снова выбираем второй пункт, но на этот раз задаем «Значение ячейки», «меньше», а в следующем поле вводим функцию, возвращающую сегодняшнюю дату. 
«ОК», и мы получили весело разукрашенную таблицу, позволяющую наглядно отслеживать ход выполнения заказов. 
Обратили внимание, что статусы задаются выбором из выпадающего списка значений? Как делать такие списки, мы рассказывали в инструкции «Как в Excel сделать выпадающий список».
Как это сделать в Excel 2003
ВКЛЮЧИТЕ СУБТИТРЫ!
«Условное форматирование» в меню «Формат». Тут понадобится немного больше ручной работы. Вот так будут выглядеть настройки для нашей первой задачи – закрасить ячейки со значениями больше средних. 
Придется вручную ввести функцию «=СРЗНАЧ()», поставить курсор между скобками, нажать на кнопочку рядом и мышкой указать нужный диапазон.
Но принцип действий тот же самый.
Покоряйте Excel и до новых встреч!
Комментарии:
- Svetlana — 27.06.2015 21:28
наконец-то узнала, как это можно сделать!
Виктор — 14.04.2016 17:23
Здравствуйте, а можно сделать условное форматирование столбца А с фразами по условию «Текст —- содержит» по нескольким словам, а лучше по столбцу В, состоящего из слов?
salam — 19.05.2016 16:24
Подскажите как подсвечивать ячеку В2 при условии если ячейка А2 не пустая?
Федя — 16.11.2016 14:39
Как задать цвет определенному значению в одной ячейки, например — вожу 5 — она будет красным цветом, вожу 4 — она станет зелёным цветом
Оля — 03.05.2017 12:12
подскажите, как заливать в гамме одного цвета с разными оттенками в столбике, если напр., если 100% — зеленый, 95- зеленый но светлее, 75 — еще светлее и т.д. заранее спасибо
Источник: myblaze.ru
Значение функции ЕСЛИ в зависимости от цвета ячейки
Кто поправит, уже пару часов облазил все форумы, вроде сделал всё как по книжке а истину не выводит.
Хочу чтоб если у ячейки Б1 был залит цвет(и желательно именно синий), истина 1 а если не залит(или залит но другой) то 0.
был бы рад помощи без макросов =)
подскажите пожалуйста.
Вложения
 |
Книга Microsoft Office Excel 2007.xlsx (10.5 Кб, 47 просмотров) |
Добавить текст ячейки в зависимости от цвета ячейки
Здравствуйте. Я не могу разобраться в написании формулы или макроса. В определенном диапазоне.
Изменение цвета ячейки в зависимости от даты
Здравствуйте! Помогите пожалуйста. У меня есть список сотрудников, которым нужно раз в год.
Изменение значения ячейки в зависимости от цвета
Здравствуйте, каким условием проверить диапазон ячеек зеленого цвета, для того, чтобы изменить в.
Прописывание имени столбца в зависимости от цвета ячейки
Привет все. Интересует как сделать так, чтобы прописывалось автоматически имя столбца в котором.
Решение
Первый раз вижу книгу с расположением столбцов справа налево и “лист” на иврите 🙂
Вложения
|
Книга Microsoft Office Excel 2007.xlsx (10.7 Кб, 76 просмотров) |
Решение
Вложения
 |
Книга Microsoft Office Excel 2007.xls (28.0 Кб, 29 просмотров) |
Казанский,
Спасибо за ответ! Если сможешь объяснить что значит больше, ровно и два минуса “>=–”
и Зачем использовать И и ИЛИ условного форматирования?
А по поводу иврита, я ж в Израиле живу =)
Добавлено через 1 минуту
AlexM,
Спасибо так же и тебе ) если могу уточнить, что значит (B1<>“”) и почему на это умножаем? 
а можно вопрос по существу но не связан с темой?
скажем есть работник начал работать в 23 закончил в 10 утра. можно ли построить формулу что если из рабочей смены, хотя бы 2 часа попадают в интервал между 22:00-6:00 то эта ячейка окрашивается скажем синим.
как не писал это в если все время выходит что он принимает 2 часа как за 2 часа ночи а не просто 2 часа в целом как 120 минут.
привёл пример в формате екселя.
начало в 20:00 конец в 8:00 из 12 часов смены поскольку хотя бы 2 часа входят в интервал между 22:00-6:00 это считается ночной сменой, тогда 100% ставки будет 7 часов а остальных как по формуле. но если бы он отработал с 8:00 lj 20:00 то 100% ставки часов было бы 8 а не 7.
Источник: www.cyberforum.ru
Тема: EXcel. Есть ли формула: “Если цвет ячейки-такой-то, то другая ячейка такого же цвета”
Опции темы
Поиск по теме
EXcel. Есть ли формула: “Если цвет ячейки-такой-то, то другая ячейка такого же цвета”
Вообщем, возник такой вопрос.
Делаю табели учета раб. времени
Закрашиваю выходные и праздничные дни в другой цвет.
Необходимо, поставить формулу(или усл. фрматирование),
что от цвета определенной ячейки, данная ячейка тоже будет
в этом же цвете.
Возможно такое осуществить?
Ну так и попробуйте его. Что-то типа для воскресенья:
[Формула] :: =ДЕНЬНЕД(ЯчейкаСлева)=1
ФорматВидЦветЗаливки
Спасибо за ответ.
Не совсем подходит.
“Данная функция Возвращает день недели, соответствующий аргументу дата_в_числовом_формате. День недели определяется как целое в интервале от 1 (воскресенье) до 7 (суббота).”
У меня данные от 1 до 31 (дни), цветом я закрашиваю нерабочие дни.
А ниже напротив этих дней проставляются часы работы или вых. дни.
Просто, ради интереса, (ну и облегчения работы), хотела узнать,
возможно ли, чтобы от цвета ячейки наверху внизу тоже перекрашивалось в тот же цвет и проставлялось “В”.
В принципе, можно просто скопировать формат-цвета ячеек,
буквы уж думаю, что сама поставлю.
Спасибо.
можно составить сложную формулу. У вас вверху дни недели пишутся или нет? Просто я пишу и от этого пляшу использую формулу с если и тогда все что вам нужно там будет.
Да, вверху.
Могу на мыло прислать образец.
можно, но это и не нужно. Проставляйте значение формулой, а закрашивайте от значения в ячейке. Больше элементов, но проще реализовать.
Анализировать цвет можно только макросами по значениям атрибутов ячейки. Но это уже VBA.
Без МАКРОСА
Например, если нужно, чтобы в том случае, когда в первой колонке листа Excel стояло число, кратное 7, цвет соседней ячейки во второй колонке был бы красный, а если кратное 6, то желтый, то порядок действий будет выглядеть так:
1) поставить курсор мыши на верхнюю ячейку во второй колонке;
2) вызвать диалоговое окно “Формат” – “Условное форматирование”;
3) в этом диалоговом окне в качестве первого условия ввести формулу “=ОСТАТ(A1;7)=0” (она возвращает True, если остаток от деления числа в А1 на 7 равен 0);
4) указать кнопкой “Формат” для первого условия, что при его выполнении требуется заливать ячейку красным;
5) нажав кнопку “А также”, добавить еще одно условие;
6) вторым условием ввести формулу “=ОСТАТ(A1;6)=0”;
7) указать кнопкой “Формат” для второго условия, что при его выполнении требуется заливать ячейку желтым.
8) путем копирования и вставки распространить это форматирование на весь второй столбец. Указанный алгоритм верен, если в настройках Excel (вкладка “Общие” диалогового окна “Сервис – Параметры”) выключен “стиль ссылок R1C1” – в противном случае координаты ячеек следует соответственно изменить.
В диалоговом окне “Условное форматирование” можно указать до трех условий, если же вам нужно больше, то придется либо проявить максимум изобретательности при составлении формул.
Помогите, пожалуйста, решить задачу в excel: даны 2 списка – страны и столицы, в ячейке А1 ввести название страны, в ячейке В1 – появляется столица. Я сделала раскрывающийся список, но незнаю какое сделать условие. Спасибо.
Источник: forum.klerk.ru
Сложение значений в зависимости от цвета ячеек в EXCEL
Просуммируем значения ячеек в зависимости от цвета их заливки. Здесь же покажем, как подсчитать такие ячейки.
Функции для суммирования значений по цвету ячеек в EXCEL не существует (по крайней мере, в EXCEL 2016 и в более ранних версиях). Вероятно, подавляющему большинству пользователей это не требуется.
Пусть дан диапазон ячеек в столбце А. Пользователь выделил цветом ячейки, чтобы разбить значения по группам.
Необходимо сложить значения ячеек в зависимости от цвета фона. Основная задача: Как нам “объяснить” функции сложения, что нужно складывать значения, например, только зеленых ячеек?
Это можно сделать разными способами, приведем 3 из них: с помощью Автофильтра , Макрофункции ПОЛУЧИТЬ.ЯЧЕЙКУ() и VBA.
С помощью Автофильтра (ручной метод)
- Добавьте справа еще один столбец с заголовком Код цвета .
- Выделите заголовки и нажмите CTRL+SHIFT+L, т.е. вызовите Автофильтр ( подробнее здесь )
- Вызовите меню Автофильтра , выберите зеленый цвет
- Будут отображены только строки с зелеными ячейками
- Введите напротив каждого “зеленого” значения число 1
- Сделайте тоже для всех цветов
Введите формулу =СУММЕСЛИ(B7:B17;E7;A7:A17) как показано в файле примера (лист Фильтр) .
Для подсчета значений используйте функцию СЧЕТЕСЛИ() .
С помощью Макрофункции ПОЛУЧИТЬ.ЯЧЕЙКУ()
Сразу предупрежу, что начинающему пользователю EXCEL будет сложно разобраться с этим и следующим разделом.
Идея заключается в том, чтобы автоматически вывести в соседнем столбце числовой код фона ячейки (в MS EXCEL все цвета имеют соответствующий числовой код). Для этого нам потребуется функция, которая может вернуть этот код. Ни одна обычная функция этого не умеет. Используем макрофункцию ПОЛУЧИТЬ.ЯЧЕЙКУ(), которая возвращает код цвета заливки ячейки (она может много, но нам потребуется только это ее свойство).
Примечание: Макрофункции – это набор функций к EXCEL 4-й версии, которые нельзя напрямую использовать на листе EXCEL современных версий, а можно использовать только в качестве Именованной формулы . Макрофункции – промежуточный вариант между обычными функциями и функциями VBA. Для работы с этими функциями требуется сохранить файл в формате с макросами *.xlsm
- Сделайте активной ячейку В7 (это важно, т.к. мы будем использовать относительную адресацию в формуле)
- В Диспетчере имен введите формулу =ПОЛУЧИТЬ.ЯЧЕЙКУ(63;Макрофункция!A7)
- Назовите ее Цвет
- Закройте Диспетчер имен
- Введите в ячейку В7 формулу =Цвет и скопируйте ее вниз.
Сложение значений организовано так же как и в предыдущем разделе.
Макрофункция работает кривовато:
- если вы измените цвет ячейки, то макрофункция не обновит значения кода (для этого нужно опять скопировать формулу из В7 вниз или выделить ячейку, нажать клавишу F2 и затем ENTER )
- функция возвращает только 56 цветов (так называемая палитра EXCEL), т.е. если цвета близки, например, зеленый и светло зеленый, то коды этих цветов могут совпасть. Подробнее об этом см. лист файла примера Colors . Как следствие, будут сложены значения из ячеек с разными цветами.
С помощью VBA
В файле примера на листе VBA приведено решение с помощью VBA. Решений может быть множество:
- можно создать кнопку, после нажатия она будет вводить код цвета в соседний столбец (реализован этот вариант).
- можно написать пользовательскую функцию, которая будет автоматически обновлять код цвета при изменении цвета ячейки (реализовать несколько сложнее);
- можно написать программу, которая будет анализировать диапазон цветных ячеек, определять количество различных цветов, вычислять в отдельном диапазоне суммы для каждого цвета (реализовать не сложно, но у каждого пользователя свои требования: ячейки с суммами должны быть в определенном месте, необходимо учесть возможность дополнения диапазона новыми значениями и пр.).
Источник: excel2.ru
Как в Excel выделить ячейки цветом по условию
Допустим, что одним из наших заданий является ввод информации о том, делал ли заказ клиент в текущем месяце. После чего на основе полученной информации необходимо выделить цветом ячейки по условию: какой из клиентов не совершил ни одного заказа на протяжении последних 3-х месяцев. Для таких клиентов нужно будет повторно выслать предложение.
Естественно это задание для Экселя. Программа должна автоматически найти таких контрагентов и соответственно выделить их цветом. Для этих условий будем использовать условное форматирование.
Автоматическое заполнение ячеек датами
Сначала подготовим структуру для заполнения регистра. В первую очередь условно рассмотрим готовый пример автоматизированного регистра, который изображен ниже на рисунке:
Пользователю только необходимо указать если клиент совершал заказ в текущем месяце, то в соответствующую ячейку следует вводить текстовое значение «заказ». Главное условие для выделения: если на протяжении 3-х месяцев контрагент не сделал ни одного заказа, его номер автоматически выделяется красным цветом.
Представленное данное решение должно автоматизировать некоторые рабочие процессы и упростить визуальный анализ данных.
Автоматическое заполнение ячеек актуальными датами
В первую очередь для регистра с номерами клиентов создадим заголовки столбцов с зеленым цветом и актуальными месяцами, которые будут автоматически отображать периоды времени. Для этого в ячейку B1 введите следующую формулу:
Как работает формула для автоматической генерации уходящих месяцев?
На рисунке формула возвращает период уходящего времени начиная даты написания статьи: 17.09.2017. В первом аргументе в функции DATA – вложена формула, которая всегда возвращает текущий год на сегодняшнюю дату благодаря функциям: ГОД и СЕГОНЯ. Во втором аргументе указан номер месяца (-1). Отрицательное число значит, что нас интересует какой был месяц в прошлом времени. Пример условий для второго аргумента со значением:
- 1 – значит первый месяц (январь) в году указанном в первом аргументе;
- 0 – это 1 месяца назад;
- -1 – это 2 мес. назад от начала текущего года (то есть: 01.10.2016).
Последний аргумент – это номер дня месяца указано во втором аргументе. В результате функция ДАТА собирает все параметры в одно значение и формула возвращает соответственную дату.
Далее перейдите в ячейку C1 и введите следующую формулу:
Как видно теперь функция ДАТА использует значение из ячейки B1 и увеличивает номер месяца на 1 по отношению к предыдущей ячейки. В результате получаем 1 – число следующего месяца.
Теперь скопируйте эту формулу из ячейки C1 в остальные заголовки столбцов диапазона D1:L1.
Выделите диапазон ячеек B1:L1 и выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Формат ячеек» или просто нажмите комбинацию клавиш CTRL+1. В появившемся диалоговом окне, на вкладке «Число», в разделе «Числовые форматы:» выберите опцию «(все форматы)». В поле «Тип:» введите значение: МММ.ГГ (обязательно буквы в верхнем регистре). Благодаря этому мы получим укороченное отображение значения дат в заголовках регистра, что упростит визуальный анализ и сделает его более комфортным за счет лучшей читабельности.
Обратите внимание! При наступлении января месяца (D1), формула автоматически меняет в дате год на следующий.
Как выделить столбец цветом в Excel по условию
Теперь необходимо выделить ячейки цветом, касающиеся текущего месяца. Благодаря этому мы с легкостью найдем столбец, в который нужно вводить актуальные данные в этом месяце. Для этого:
- Выделите диапазон ячеек B2:L15 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». А в появившемся окне «Создание правила форматирования» выберите опцию: «Использовать формулу для определения форматируемых ячеек»
- В поле ввода введите формулу:
- Щелкните на кнопку «Формат» и укажите на вкладке «Заливка» каким цветом будут выделены ячейки актуального месяца. Например – зеленый. После чего на всех окнах для подтверждения нажмите на кнопку «ОК».
Столбец под соответствующим заголовком регистра автоматически подсвечивается зеленым цветом соответственно с нашими условиями:
Как работает формула выделения столбца цветом по условию?
Благодаря тому, что перед созданием правила условного форматирования мы охватили всю табличную часть для введения данных регистра, форматирование будет активно для каждой ячейки в этом диапазоне B2:L15. Смешанная ссылка в формуле B$1 (абсолютный адрес только для строк, а для столбцов – относительный) обусловливает, что формула будет всегда относиться к первой строке каждого столбца.
Автоматическое выделение цветом столбца по условию текущего месяца
Главное условие для заполнения цветом ячеек: если в диапазоне B1:L1 находиться та же дата, что и первого дня текущего месяца, тут же ячейки в целом столбце изменяют свой цвет на указанный в условном форматировании.
Обратите внимание! В условиях этой формулы, для последнего аргумента функции ДАТА указано значение 1, так же, как и для формул в определении дат для заголовков столбцов регистра.
В нашем случаи — это зеленая заливка ячеек. Если мы откроем наш регистр в следующем месяце, то уже ему соответствующий столбец будет выделен зеленым цветом в независимости от текущего дня.
Табличная часть сформатирована, теперь заполним ее текстовым значением «заказ» в смешанном порядке клиентов для текущего и прошлых месяцев.
Как выделить ячейки красным цветом по условию
Теперь нам необходимо выделить красным цветом ячейки с номерами клиентов, которые на протяжении 3-х месяцев не совершили ни одного заказа. Для этого:
- Выделите диапазон ячеек A2:A15 (то есть список номеров клиентов) и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». А в появившемся окне «Создание правила форматирования» выберите опцию: «Использовать формулу для определения форматируемых ячеек»
- В этот раз в поле ввода введите формулу:
- Щелкните на кнопку «Формат» и укажите красный цвет на вкладке «Заливка». После чего на всех окнах нажмите «ОК».
- Заполоните ячейки текстовым значением «заказ» как на рисунке и посмотрите на результат:
Номера клиентов подсвечиваются красным цветом, если в их строке нет значения «заказ» в последних трех ячейках к текущему месяцу (включительно).
Анализ формулы для выделения цветом ячеек по условию:
Сначала займемся средней частью нашей формулы. Функция СМЕЩ возвращает ссылку на диапазон смещенного по отношении к области базового диапазона определенной числом строк и столбцов. Возвращаемая ссылка может быть одной ячейкой или целым диапазоном ячеек. Дополнительно можно определить количество возвращаемых строк и столбцов. В нашем примере функция возвращает ссылку на диапазон ячеек для последних 3-х месяцев.
Важная часть для нашего условия выделения цветом находиться в первом аргументе функции СМЕЩ. Он определяет, с какого месяца начать смещение. В данном примере – это ячейка D2, то есть начало года – январь. Естественно для остальных ячеек в столбце номер строки для базовой ячейки будет соответствовать номеру строки в котором она находиться. Следующие 2 аргумента функции СМЕЩ определяют на сколько строк и столбцов должно быть выполнено смещение. Так как вычисления для каждого клиента будем выполнять в той же строке, значение смещения для строк указываем –¬ 0.
В тоже время для вычисления значения третьего аргумента (смещение по столбцам) используем вложенную формулу МЕСЯЦ(СЕГОДНЯ()), Которая в соответствии с условиями возвращает номер текущего месяца в текущем году. От вычисленного формулой номера месяца отнимаем число 4, то есть в случаи Ноября получаем смещение на 8 столбцов. А, например, для Июня – только на 2 столбца.
Последнее два аргумента для функции СМЕЩ определяют высоту (в количестве строк) и ширину (в количестве столбцов) возвращаемого диапазона. В нашем примере – это область ячеек с высотой на 1-ну строку и шириной на 4 столбца. Этот диапазон охватывает столбцы 3-х предыдущих месяцев и текущий.
Первая функция в формуле СЧЕТЕСЛИ проверяет условия: сколько раз в возвращаемом диапазоне с помощью функции СМЕЩ встречается текстовое значение «заказ». Если функция возвращает значение 0 – значит от клиента с таким номером на протяжении 3-х месяцев не было ни одного заказа. А в соответствии с нашими условиями, ячейка с номером данного клиента выделяется красным цветом заливки.
Если мы хотим регистрировать данные по клиентам, Excel идеально приспособлен для этой цели. С легкостью можно записывать в соответствующие категории число заказанных товаров, а также даты реализации транзакций. Проблема постепенно начинает возникать с ростом объема данных.
Если их так много, что тратим несколько минут на поиск конкретной позиции регистра и анализ введенной информации. В таком случае стоит добавить в таблицу регистра механизмы, для автоматизации некоторых рабочих процессов пользователя. Что мы и сделали.
Источник: exceltable.com
Excel если то цвет ячейки
| Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как задать формулу в зависимости от цвет ячейки (Условное Форматирование/Conditional Formattings) |
Как задать формулу в зависимости от цвет ячейки
