Цвет ячейки в excel в зависимости от значения

Сложение значений в зависимости от цвета ячеек в 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 менялся в зависимости от значения

Привет, уважаемые читатели. Когда-нибудь вам доводилось работать с огромными данными в таблице? Знаете, с ними гораздо удобнее будет работать, если знать, как выделить несколько ячеек Excel различным цветом при определенном условии. Хотели бы вы узнать, как это делается? В этом уроке мы сделаем так, чтобы менялся цвет ячейки в зависимости от значения Excel, а также окрасим все ячейки с помощью поиска.

Цвет заливки меняется вместе со значением

Для примера мы потренируемся на том, чтобы ячейка меняла цвет в данной таблице при определенном условии. Да ни одна, а все со значением в диапазоне от 60 до 90. Для этого мы воспользуемся функцией «Условное форматирование».

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

Далее находим на вкладке «Главная» кнопку «Условное форматирование» и в списке выбираем «Создать правило».

У нас открылось окно «Создание правил форматирования». В этом окне выбираем тип правила: «Форматировать только ячейки, которые содержат».

Далее, переходим к разделу «Измените описание правила», где нужно указать те условия, по которым будет выполнена заливка. В этом разделе можно выставить самые различные условия, при которых она будет меняться.

Читайте также:  Как защитить ячейку от изменений в excel

В нашем случае необходимо поставить следующие: «значения ячейки» и «между». Так же мы обозначаем диапазон, что при условии значения от 60 до 90 будет применена заливка. Посмотрите на скриншоте, как это сделал я.

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

Если вы заполнили, то не спешите кликать по кнопке «ОК». Прежде необходимо нажать на кнопку «Формат», как на скриншоте, и перейти к настройке заливки.

Хорошо, как видите, у вас открылось окно «Формат ячейки». Здесь вам нужно перейти на вкладку «Заливка», где вы выбираете нужную, и нажать на «ОК» в этом окне и в предыдущем. Я выбрал зеленую заливку.

Посмотрите на свой результат. Думаю, у вас все получилось. У меня точно получилось. Взгляните на скриншот:

Окрасим ячейку в определенный цвет, если она равна чему-то

Давайте вернемся к нашей таблице в изначальном виде. И теперь мы поменяем цвет там, где содержится цифра 40 на красный цвет, а с цифрой 50 на желтый. Конечно, для этого дела можно воспользоваться первым способом, но мы же хотим знать больше возможностей Excel.

В этот раз мы воспользуемся функцией «Найти и заменить».

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

Теперь время открыть окно поиска. На вкладке «Главная» в разделе «Редактирование» нажмите на кнопку «Найти и выделить».

Можно же и горячими клавишами пользоваться: CTRL + F

В поле «Найти» мы указываем то, что ищем. В данном случае пишем «40», а затем жмем кнопку «Найти все».

Теперь, когда ниже были показаны результаты поиска, выберите одно из них и нажмите на сочетание CTRL + A, чтобы выбрать их все сразу. А затем нажмите на «Закрыть», чтобы убрать окно «Найти и заменить».

Когда у нас выбраны все, содержащие цифру 40, на вкладке «Главная» в разделе «Шрифт» выберите окраску ячейки. У нас это красный. И, как вы видите у себя на экране, так и у меня на скриншоте, они окрасились в красный.

Теперь те же самые действия нужно выполнить, чтобы окрасить те, где указано число 50. Думаю, теперь вам понятно, как сделать это.

У вас получилось? А посмотрите, что вышло у меня.

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

Источник: v-ofice.ru

Применение цвета к чередующимся строкам или столбцам

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

Применив цвет к чередующимся строкам или столбцам (это часто называют цветными полосами), можно облегчить восприятие данных на листе. Чтобы отформатировать чередующиеся строки или столбцы, можно быстро применить один из встроенных форматов таблицы. Если воспользоваться этим методом, то при добавлении новых строк или столбцов к ним автоматически будет применяться соответствующая заливка.

Вот как это сделать:

Выделите диапазон ячеек, которые нужно отформатировать.

На вкладке Главная выберите команду Форматировать как таблицу.

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

Чтобы вместо строк применить заливку к столбцам, выделите таблицу, откройте вкладку Конструктор, снимите флажок Чередующиеся строки и установите флажок Чередующиеся столбцы.

Совет: Если вы хотите сохранить стиль таблицы чередование без функциональность таблицы, можно Преобразовать таблицу в диапазон данных. Чередования цвет не продолжится автоматически добавить дополнительные строки или столбцы, но вы можете скопировать дополнительные цветовые форматы в новых строк с помощью формата по образцу.

Применение полос к строкам или столбцам с помощью условного форматирования

Применить особый формат к определенным строкам или столбцам можно также с помощью правила условного форматирования.

Вот как это сделать:

На листе выполните одно из указанных ниже действий.

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

Чтобы применить затенение ко всему листу, нажмите кнопку Выделить все.

На вкладке Главная нажмите кнопку Условное форматирование и выберите команду Создать правило.

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

Чтобы применить цвет к чередующимся строкам, в поле Форматировать значения, для которых следующая формула является истинной введите формулу =ОСТАТ(СТРОКА();2)=0.

Читайте также:  Как вставить ячейку в эксель

Чтобы применить цвет к чередующимся столбцам, введите формулу =ОСТАТ(СТОЛБЕЦ();2)=0.

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

Нажмите кнопку Формат.

В окне Формат ячеек откройте вкладку Заливка.

Выберите цвет и нажмите кнопку ОК.

Выбранный цвет показан в поле Образец. Нажмите кнопку ОК или выберите другой цвет.

Вот как можно изменить правило условного форматирования: щелкните одну из ячеек, к которым оно применяется, на вкладке Главная последовательно выберите команды Условное форматирование > Управление правилами, нажмите кнопку Изменить правило и внесите необходимые изменения.

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

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

Источник: support.office.com

Значение функции ЕСЛИ в зависимости от цвета ячейки

Кто поправит, уже пару часов облазил все форумы, вроде сделал всё как по книжке а истину не выводит.

Хочу чтоб если у ячейки Б1 был залит цвет(и желательно именно синий), истина 1 а если не залит(или залит но другой) то 0.

был бы рад помощи без макросов =)
подскажите пожалуйста.

Вложения

Книга Microsoft Office Excel 2007.xlsx (10.5 Кб, 47 просмотров)
02.11.2015, 02:38

Добавить текст ячейки в зависимости от цвета ячейки
Здравствуйте. Я не могу разобраться в написании формулы или макроса. В определенном диапазоне.

Изменение цвета ячейки в зависимости от даты
Здравствуйте! Помогите пожалуйста. У меня есть список сотрудников, которым нужно раз в год.

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

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

02.11.2015, 03:05 2

Решение

Первый раз вижу книгу с расположением столбцов справа налево и “лист” на иврите 🙂

Вложения

Книга Microsoft Office Excel 2007.xlsx (10.7 Кб, 76 просмотров)
02.11.2015, 08:50 3

Решение

Вложения

Книга Microsoft Office Excel 2007.xls (28.0 Кб, 29 просмотров)
02.11.2015, 10:02 [ТС] 4

Казанский,
Спасибо за ответ! Если сможешь объяснить что значит больше, ровно и два минуса “>=–”
и Зачем использовать И и ИЛИ условного форматирования?

А по поводу иврита, я ж в Израиле живу =)

Добавлено через 1 минуту
AlexM,

Спасибо так же и тебе ) если могу уточнить, что значит (B1<>“”) и почему на это умножаем?

02.11.2015, 10:42 5 02.11.2015, 10:49 [ТС] 6 02.11.2015, 10:50 7 02.11.2015, 11:17 8 02.11.2015, 11:26 [ТС] 9

а можно вопрос по существу но не связан с темой?

скажем есть работник начал работать в 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 2010


ВКЛЮЧИТЕ СУБТИТРЫ!

Как это сделать в Excel 2007


ВКЛЮЧИТЕ СУБТИТРЫ!
Выделим ячейки с ценами заказов и, нажав на стрелочку рядом с кнопкой «Условное форматирование», выберем «Создать правило».

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

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

Выделим ячейки со статусами заказов и создадим новое правило. На этот раз используем второй вариант, позволяющий проверять содержимое ячейки. Выберем «Текст», «содержит» и введем слово «Выполнен». Зададим зеленый цвет, подтверждаем, и выполненные работы у нас позеленели.

Ну и сделаем еще одно правило, окрашивающее просроченные заказы в красный цвет. Выделяем даты выполнения заказов. При создании правила снова выбираем второй пункт, но на этот раз задаем «Значение ячейки», «меньше», а в следующем поле вводим функцию, возвращающую сегодняшнюю дату.

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

Обратили внимание, что статусы задаются выбором из выпадающего списка значений? Как делать такие списки, мы рассказывали в инструкции «Как в Excel сделать выпадающий список».

Читайте также:  Как в excel добавить ячейку

Как это сделать в Excel 2003


ВКЛЮЧИТЕ СУБТИТРЫ!
«Условное форматирование» в меню «Формат». Тут понадобится немного больше ручной работы. Вот так будут выглядеть настройки для нашей первой задачи – закрасить ячейки со значениями больше средних.

Придется вручную ввести функцию «=СРЗНАЧ()», поставить курсор между скобками, нажать на кнопочку рядом и мышкой указать нужный диапазон.
Но принцип действий тот же самый.
Покоряйте Excel и до новых встреч!

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

  1. 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

Применение цвета к чередующимся строкам или столбцам

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

Применив цвет к чередующимся строкам или столбцам (это часто называют цветными полосами), можно облегчить восприятие данных на листе. Чтобы отформатировать чередующиеся строки или столбцы, можно быстро применить один из встроенных форматов таблицы. Если воспользоваться этим методом, то при добавлении новых строк или столбцов к ним автоматически будет применяться соответствующая заливка.

Вот как это сделать:

Выделите диапазон ячеек, которые нужно отформатировать.

На вкладке Главная выберите команду Форматировать как таблицу.

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

Чтобы вместо строк применить заливку к столбцам, выделите таблицу, откройте вкладку Конструктор, снимите флажок Чередующиеся строки и установите флажок Чередующиеся столбцы.

Совет: Если вы хотите сохранить стиль таблицы чередование без функциональность таблицы, можно Преобразовать таблицу в диапазон данных. Чередования цвет не продолжится автоматически добавить дополнительные строки или столбцы, но вы можете скопировать дополнительные цветовые форматы в новых строк с помощью формата по образцу.

Применение полос к строкам или столбцам с помощью условного форматирования

Применить особый формат к определенным строкам или столбцам можно также с помощью правила условного форматирования.

Вот как это сделать:

На листе выполните одно из указанных ниже действий.

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

Чтобы применить затенение ко всему листу, нажмите кнопку Выделить все.

На вкладке Главная нажмите кнопку Условное форматирование и выберите команду Создать правило.

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

Чтобы применить цвет к чередующимся строкам, в поле Форматировать значения, для которых следующая формула является истинной введите формулу =ОСТАТ(СТРОКА();2)=0.

Чтобы применить цвет к чередующимся столбцам, введите формулу =ОСТАТ(СТОЛБЕЦ();2)=0.

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

Нажмите кнопку Формат.

В окне Формат ячеек откройте вкладку Заливка.

Выберите цвет и нажмите кнопку ОК.

Выбранный цвет показан в поле Образец. Нажмите кнопку ОК или выберите другой цвет.

Вот как можно изменить правило условного форматирования: щелкните одну из ячеек, к которым оно применяется, на вкладке Главная последовательно выберите команды Условное форматирование > Управление правилами, нажмите кнопку Изменить правило и внесите необходимые изменения.

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

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

Источник: support.office.com