В excel цвет ячейки в зависимости от значения 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. Для этого мы воспользуемся функцией «Условное форматирование».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Закрасить ячейку по условию или формуле

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

Инструкция для Excel 2010


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

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


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

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

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

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

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

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

Обратили внимание, что статусы задаются выбором из выпадающего списка значений? Как делать такие списки, мы рассказывали в инструкции «Как в 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

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

Не все фирмы покупают специальные программы для ведения дел. Многие пользуются MS Excel, ведь эта хо.

Не все фирмы покупают специальные программы для ведения дел. Многие пользуются MS Excel, ведь эта хорошо приспособлена для больших информационных баз. Практика показала, что дальше заполнения таблиц доходит редко. Таблица растет, информации становится больше и возникает необходимость быстро выбрать только нужную. В подобной ситуации встает вопрос как в Excel выделить ячейку цветом при определенном условии, применить к строкам цветовые градиенты в зависимости от типа или наименования поставщика, сделать работу с информацией быстрой и удобной? Подробнее читаем ниже.

Где находится условное форматирование

Как в экселе менять цвет ячейки в зависимости от значения – да очень просто и быстро. Для выделения ячеек цветом предусмотрена специальная функция «Условное форматирование», находящаяся на вкладке «Главная»:

Читайте также:  Excel число символов в ячейке excel

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

Правила выделения ячеек

С помощью этого набора инструментов делают следующие выборки:

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

Посмотрите, как ищется выбранный текст: в первом поле задается условие, а во втором указывают, каким образом выделить полученный результат. Обратите внимание, выбрать можно цвет фона и текста из предложенных в списке. Если хочется применить иные оттенки – сделать это можно перейдя в «Пользовательский формат». Аналогичным образом реализуются все «Правила выделения ячеек».

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

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

Правила отбора первых и последних значений.

Рассмотрим вторую группу функций «Правила отбора первых и последних значений». В ней вы сможете:

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

Гистограммы

Если заливка ячейки цветом вас не устраивает – применяйте инструмент «Гистограмма». Предлагаемая окраска легче воспринимается на глаз в большом объеме информации, функциональные правила подстраиваются под требования пользователя.

Цветовые шкалы

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

Наборы значков

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

Создание, удаление и управление правилами

Функция «Создать правило» полностью дублирует «Другие правила» из перечисленных выше, создает выборку изначально по требованию пользователя.

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

Вызывает интерес инструмент «Управление правилами» – своеобразная история создания и изменения проведенных форматирований. Меняйте подборки, делайте правила неактивными, возвращайте обратно, чередуйте порядок применения. Для работы с большим объемом информации это очень удобно.

Отбор ячеек по датам

Чтобы разобраться, как в excel сделать цвет ячейки от значения установленной даты, рассмотрим пример с датами закупок у поставщиков в январе 2019 года. Для применения такого отбора нужны ячейки с установленным форматом «Дата». Для этого перед внесением информации выделите необходимый столбец, щелкните правой кнопкой мыши и в меню «Формат ячеек» найдите вкладку «Число». Установите числовой формат «Дата» и выберите его тип по своему усмотрению.

Для отбора нужных дат применяем такую последовательность действий:

  • выделяем столбцы с датами (в нашем случае за январь);
  • находим инструмент «Условное форматирование»;
  • в «Правилах выделения ячеек» выбираем пункт «Дата»;
  • в правой части форматирования открываем выпадающее окно с правилами;
  • выбираем подходящее правило (на примере выбраны даты за предыдущий месяц);
  • в левом поле устанавливаем готовый цветовой подбор «Желтая заливка и темно-желтый текст»
  • выборка окрасилась, жмем «ОК».

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

Выделение цветом столбца по условию

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

Нам необходимо пометить синим цветом тех поставщиков, у которых мы купили товара на сумму большую, чем 100 000 рублей. Чтобы сделать такую выборку воспользуемся следующим алгоритмом действий:

  • выделяем столбец с январскими закупками;
  • кликаем инструмент «Условное форматирование»;
  • переходим в «Правила выделения ячеек»;
  • пункт «Больше…»;
  • в правой части форматирования устанавливаем сумму 100 000 рублей;
  • в левом поле переходим на вкладку «Пользовательский формат» и выбираем синий цвет;
  • необходимая выборка окрасилась в синий цвет, жмем «ОК».

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

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

Источник: freesoft.ru

Тема: EXcel. Есть ли формула: “Если цвет ячейки-такой-то, то другая ячейка такого же цвета”

Опции темы
Поиск по теме

EXcel. Есть ли формула: “Если цвет ячейки-такой-то, то другая ячейка такого же цвета”

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

Ну так и попробуйте его. Что-то типа для воскресенья:
[Формула] :: =ДЕНЬНЕД(ЯчейкаСлева)=1
ФорматВидЦветЗаливки

Читайте также:  Поиск значения в диапазоне ячеек excel

Спасибо за ответ.
Не совсем подходит.

“Данная функция Возвращает день недели, соответствующий аргументу дата_в_числовом_формате. День недели определяется как целое в интервале от 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

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

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

Хочу чтоб если у ячейки Б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