Суммирование по цвету ячейки в excel. Как сделать так, чтобы цвет ячейки Excel менялся в зависимости от значения
Профессиональная работа в Excel рано или поздно приведет вас к необходимости написания собственных функций. Довольно часто пользователи «раскрашивают» ячейки в разные цвета. Если потом возникает необходимость просуммировать значения в выделенных ячейках, то, к сожалению, в Excel нет такой стандартной функции. Тем не менее, сумма по цвету ячеек в Excel может быть найдена!
Небольшой код VBA решит ваши проблемы. Для облегчения восприятия последующего материала откройте Excel-файл (он запакован в Zip, так как на сайте размещение файлов, содержащих макросы запрещено).
1. Убедитесь, что среди вкладок на ленте Excel, присутствует «Разработчик»:
Скачать заметку в формате Word
Скачать заметку в формате pdf
Скачать файл примера в формате Excel с поддержкой макросов . На основании комментариев добавил в Excel-файл код функции КолЦвет — определяет число ячеек выделенного цвета, СумНеЦвет — определяет сумму значений в ячейках выделенных любым цветом (не белого цвета)
2. Если такой закладки вы не видите, щелкните на кнопке Officeв левом верхнем углу и затем на кнопке «Параметры Excel»:
3. В открывшемся окне «Параметры Excel» перейдите на вкладку «Основные» и поставьте галочку в строке «Показывать меню Разработчик на ленте». Нажмите Ok
4. Создайте на листе Excel диапазон со значениями; несколько ячеек раскрасьте:
5. Перейдите на вкладку Разработчик и щелкните на VisualBasic:
6. У вас откроется окно VBA, содержащее окно VBAProject:
7. Если окна VBAProjectнет на экране
щелкните на меню View — Project Explorer:
8. Если вы хотите использовать создаваемый код VBA в любом Excel-файле, вам следует его записать в VBAProject, относящийся к Personal.xlsb. Если вы хотите применять код только в одном файле, с которым вы сейчас работаете, сохраните код в VBAProject`е именно этого файла (в нашем случае Сумма по цвету.xlsm):
9. Допустим, вы решили, что создаваемая функция будет использоваться в дальнейшем в различных файлах. Щелкните правой кнопкой мыши на VBAProject(Personal.xlsb) и выберите Insert — Module
Появится окно нового модуля, в которое следует перенести код:
Function СумЦвет(диапазон As Range, критерий As Range) As Double " Определяет сумму значений в ячейках "диапазона", " цвет которых совпадает с цветом в ячейке "критерий" Application.Volatile True Dim i As Range For Each i In диапазон If i.Interior.Color = критерий.Interior.Color Then СумЦвет = СумЦвет + i End If Next End Function
Function СумЦвет(диапазонAs Range , критерийAs Range ) As Double " Определяет сумму значений в ячейках "диапазона", " цветкоторыхсовпадаетсцветомвячейке"критерий" Application . Volatile True Dim i As Range For Each i In диапазон If i . Interior . Color = критерий. Interior . Color Then СумЦвет= СумЦвет+ i End If Next End Function |
Номер вашего модуля (у меня он 5) будет зависеть от числа ранее созданных модулей.
Несколько слов о коде:
Function СумЦвет (диапазон AsRange, критерий AsRange) AsDouble/ Задает пользовательскую функция под названием СумЦвет с двумя параметрами: диапазоном суммирования и критерием – ячейкой, по цвету которой определяется, значения в каких ячейках суммировать.
Application.Volatile True/ Заставит нашу функцию пересчитываться при любом изменении значения в любой из ячеек на листе
Dim i AsRange/ Определяет переменную i, как диапазон ячеек
For Eachi In диапазон / Для всех ячеек из выбранного диапазона
If i.Interior.Color = критерий.Interior.Color Then/ Если цвет ячейки совпадает с критерием
СумЦвет = СумЦвет + i.Value/ то добавляем значение, хранящееся в ячейке в сумму
10. Вы создали пользовательскую функцию СумЦвет, которую можно найти в категории «Определенные пользователем»
11. Окно мастера функции выглядит также, как и для стандартной функции Excel
Хочу обратить ваше внимание на две особенности функции СумЦвет:
- При изменении значения в одной из ячеек происходит автоматический пересчет значения функции СумЦвет. Если вы поменяли только цвет ячейки , автоматический пересчет не произойдет. Нажмите F9.
- К сожалению, функция не работает, если ячейки раскрашены с помощью условного форматирования. Это ограничение можно преодолеть, если применить правила, использованные в условном форматировании, в функциях СУММЕСЛИ и СУММЕСЛИМН. Подробнее см.
На основании комментариев, появившихся после первой публикации заметки, добавил в Excel-файл код функций:
- КолЦвет – определяет число ячеек выделенного цвета
- СумНеЦвет – определяет сумму значений в ячейках выделенных любым цветом (не белого цвета)
- КолНеЦвет – определяет число выделенных ячеек (не белых)
Привет, уважаемые читатели. Когда-нибудь вам доводилось работать с огромными данными в таблице? Знаете, с ними гораздо удобнее будет работать, если знать, как выделить несколько ячеек Excel различным цветом при определенном условии. Хотели бы вы узнать, как это делается? В этом уроке мы сделаем так, чтобы менялся цвет ячейки в зависимости от значения Excel, а также окрасим все ячейки с помощью поиска.
Цвет заливки меняется вместе со значением
Для примера мы потренируемся на том, чтобы ячейка меняла цвет в данной таблице при определенном условии. Да ни одна, а все со значением в диапазоне от 60 до 90. Для этого мы воспользуемся функцией «Условное форматирование».
Для начала выделите тот диапазон данных, который мы будем форматировать.
Далее находим на вкладке «Главная» кнопку «Условное форматирование» и в списке выбираем «Создать правило».
У нас открылось окно «Создание правил форматирования». В этом окне выбираем тип правила: «Форматировать только ячейки, которые содержат».
Далее, переходим к разделу «Измените описание правила», где нужно указать те условия, по которым будет выполнена заливка. В этом разделе можно выставить самые различные условия, при которых она будет меняться.
В нашем случае необходимо поставить следующие: «значения ячейки» и «между». Так же мы обозначаем диапазон, что при условии значения от 60 до 90 будет применена заливка. Посмотрите на скриншоте, как это сделал я.
Конечно же при работе с вашей таблицей может потребоваться заполнить совсем другими условиями, которые вы и будете указывать, ну, а сейчас мы всего лишь тренируемся.
Если вы заполнили, то не спешите кликать по кнопке «ОК». Прежде необходимо нажать на кнопку «Формат», как на скриншоте, и перейти к настройке заливки.
Хорошо, как видите, у вас открылось окно «Формат ячейки». Здесь вам нужно перейти на вкладку «Заливка», где вы выбираете нужную, и нажать на «ОК» в этом окне и в предыдущем. Я выбрал зеленую заливку.
Посмотрите на свой результат. Думаю, у вас все получилось. У меня точно получилось. Взгляните на скриншот:
Окрасим ячейку в определенный цвет, если она равна чему-то
Давайте вернемся к нашей таблице в изначальном виде. И теперь мы поменяем цвет там, где содержится цифра 40 на красный цвет, а с цифрой 50 на желтый. Конечно, для этого дела можно воспользоваться первым способом, но мы же хотим знать больше возможностей Excel.
В этот раз мы воспользуемся функцией «Найти и заменить».
Выделите тот участок таблицы, в который будем вносить изменения. Если это весь лист, то выделять нет смысла.
Теперь время открыть окно поиска. На вкладке «Главная» в разделе «Редактирование» нажмите на кнопку «Найти и выделить».
Можно же и горячими клавишами пользоваться: CTRL + F
В поле «Найти» мы указываем то, что ищем. В данном случае пишем «40», а затем жмем кнопку «Найти все».
Теперь, когда ниже были показаны результаты поиска, выберите одно из них и нажмите на сочетание CTRL + A, чтобы выбрать их все сразу. А затем нажмите на «Закрыть», чтобы убрать окно «Найти и заменить».
Когда у нас выбраны все, содержащие цифру 40, на вкладке «Главная» в разделе «Шрифт» выберите окраску ячейки. У нас это красный. И, как вы видите у себя на экране, так и у меня на скриншоте, они окрасились в красный.
Теперь те же самые действия нужно выполнить, чтобы окрасить те, где указано число 50. Думаю, теперь вам понятно, как сделать это.
У вас получилось? А посмотрите, что вышло у меня.
На этом все. Спасибо, друзья. Подписывайтесь, комментируйте, вступайте в группу, делитесь в соц сетях и будьте всегда в курсе новых статей. А также, не забывайте изучать и другие статьи на этом сайте.
Подсчет ячеек по цвету заливки
Функция подсчитывает количество ячеек, заливка которых имеет определенный цвет. Может пригодиться, если ведется учет каких-либо соревнований и каждое место в туре имеет свой цвет ячейки. После заполнения такая таблица может и выглядит очень наглядно, но подсчитать количество первых мест, вторых, третьих становится большой проблемой, ведь в Excel до сих пор нет функций, способных суммировать/подсчитывать ячейки по цвету.
Вызов команды через стандартный диалог:
Вызов с панели MulTEx:
Сумма/Поиск/Функции - Математические - СчетЯчеек_Заливка
Синтаксис:
=СчетЯчеек_Заливка($E$2:$E$20 ; $E$7 ; I13 ; $A$2:$A$20)
ДиапазонСчета ($E$2:$E$20) - диапазон значений для подсчета. Можно указать несколько столбцов. Столбец с критерием(если планируется считать еще и по критерию) не обязательно должен входит в диапазон.
ЯчейкаОбразец ($E$7) - ячейка-образец с цветом заливки. Ячейки с этим цветом будут подсчитаны.
Критерий
(I13) - необязательный аргумент. Если указан, то подсчитываются ячейки с указанным критерием и цветом заливки. Допускается применение в критерии символов подстановки - "*" и "?" . Например, для подсчета только ячеек, в которых содержится слово "мир" необходимо указать в качестве критерия - "*мир*" . Если необходимо посчитать количество непустых ячеек с указанным цветом заливки, то можно указать критерий: "*?*" . Если не указан, то подсчитываются все ячейки с указанным цветом заливки.
Так же данный аргумент может принимать в качестве критерия символы сравнения (, =,):
- ">0" - будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых больше нуля;
- ">=2" - будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых больше или равно двум;
- "0" - будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых не равно нулю;
- "" - будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых не пустые;
Вместо нуля может быть любое число или текст. Так же можно добавить ссылку на ячейку со значением: ""&D$1
ДиапазонКритерия ($A$2:$A$20) - Необязательный аргумент. Указывается диапазон, в котором следует искать критерий(если критерий указан) . ДиапазонКритерия должен быть равен по количеству ячеек ДиапазонуСчета . Если ДиапазонКритерия не указан, то критерий просматривается в ДиапазонеСчета .
ИспУФ () - Необязательный аргумент. Допускается указание логических значений ИСТИНА(TRUE) или ЛОЖЬ(FALSE). По умолчанию принимает значение ИСТИНА. Если указан как ИСТИНА, то функция будет подсчитывать ячейки с учетом примененного к ним условного форматирования. Если указан как ЛОЖЬ, то функция будет подсчитывать ячейки без учета примененного условного форматирования, т.е. даже если условное форматирование применено и ячейка окрашена с его помощью, а реальный цвет заливки не соответствует цвету ЯчейкиОбразца - то она не будет подсчитана.
Функция подсчитывает любые ячейки, заливка которых равна заливке ячейки-образца. Даже если ячейка будет пустая, но заливка будет равна указанной - ячейка будет подсчитана. Чтобы подсчитать только заполненные ячейки в качестве критерия следует указать - "*?*" , а ДиапазонКритерия не указывать.
Важно: Функция не вычисляется при изменении цвета заливки. Для пересчета функции после изменения параметров необходимо выделить ячейку и нажать F2 -Enter . Либо нажать сочетания клавиш Shift +F9 (пересчет функций активного листа) или клавишу F9 (пересчет функций всей книги)
Примечание: данная функция будет корректно работать даже при примененном к ячейке Условном форматировании. Однако если в ячейке/диапазоне присутствуют условия, формат для которых задан при помощи шкал, градиентов, гистограмм и значков - функция может вернуть некорректный результат. Связано это с тем, что Excel не предоставляет доступ к данным типам УФ извне.
Нужно выделить повторяющиеся значения в столбце? Надо выделить первые 5 максимальных ячеек? Необходимо сделать термальную шкалу для наглядности (цвет меняется в зависимости от увеличения/уменьшения значения ячеек)? В Excel выделение цветом ячеек по условиям можно сделать очень быстро. В Excel за выделение цветом ячеек отвечает специальная функция «Условное форматирование». Настоятельно рекомендую! Подробнее читаем ниже:
Для начала, на ленте задач в главном меню найдите раздел Стили и нажмите на кнопку Условное форматирование,
При нажатии откроется меню, с разными вариантами этого редактирования. Как вы видите возможностей здесь действительно много.
Теперь подробнее о самых полезных:
Excel выделение цветом ячеек по условиям. Простые условия
Для этого зайдите в пункт Правила выделения ячеек. Если к примеру, вам нужно выделить все ячейки больше 100, нажмите кнопку Больше. В окне:
по умолчанию условия, предлагается выделить красным цветом, но вы можете задать нужное форматирование ячеек нажав в правом окошке и выбрав необходимы вариант.
Выделение повторяющихся значений, в т.ч. по нескольким столбцам
Чтобы выделить все повторяющиеся значения выберите соответствующее меню (см. картинку в начале статьи). Далее снова появиться окошко с форматированием. Настройте как вам удобно.
Что делать если необходимо найти повторения по двум и более столбцам, например когда ФИО в разных столбцах? Сделайте еще один столбец и объедините значения формулой = , т.е. у в отдельной ячейке у вас будет написано ИвановИванИваныч, тогда по этому столбцу вы сможете выделить повторяющиеся значения. Важно понимать, что если порядок слов будет различаться Excel сочтет такие строки неповторяющимися.
Выделение цветом первых/последних значений. Опять же условное форматирование
Для этого зайдите в пункт Правила отбора первых и последних ячеек и выберите нужный пункт. Помимо того, что можно выделить первые/последние значения (в том числе и по процентам), можно использовать возможность выделить данные выше и ниже среднего (пользуюсь даже чаще). Очень удобно для просмотра результатов отличающихся от нормы или среднего!
Построение термальной диаграммы и гистограммы
Классная функция для визуализации данных — термальная/температурная диаграмма. Суть в том что, в зависимости от величины значения в столбце или строке, ячейка подсвечивается определенным оттенком цвета. Таблицы воспринимаются гораздо лучше на глаз, а принимать решение становится проще. Ведь один из лучших анализаторов — это наш глаз и соответственно мозг, а не машина!
Гистограмма в ячейке (рисунок ниже) тоже крайне полезная функция, для выявления изменения значений и сравнения их.
Выделение цветом ячеек содержащих определенный текст
Очень часто нужно найти ячейки, которые содержат определенный набор символов, можно конечно воспользоваться функцией = , но проще и быстрее применить в условное форматирование, пройдите — Правила отбора ячеек — Текст содержит (см. картинку 2).
Очень полезно при работе с текстом. Пример, когда в столбце у вас записаны ФИО сотрудников, а надо отобрать всех коллег Ивановых. Выделяем ячейки заходим в пункт меню и выделяем содержащий текст Иванов, после чего фильтруем таблицу по цвету:
Excel выделение цветом. Фильтр по цвету
Помимо вышеперечисленных возможностей вы можете отфильтровать выделенные ячейки по цвету обычном фильтром. К моему удивлению об этом очень мало кто знает — видимо отголоски версии 2003 — там этой возможности не было.
Работая с таблицами Excel, часто прибегают к заливке фона или цветному шрифту. Выделение ячеек обращает на себя внимание и служит своего рода цветовым кодом. Напр., можно применить зелёный фон как знак успешности показателей, или назначить значениям красный шрифт как сигнал тревоги. Тем не менее, такая очевидная задача, как посчитать и суммировать значения с одинаковым цветом, нередко превращается в часы программирования макросов или формул.
Надстройка «Счёт по цвету» мгновенно и без VBA агрегирует значения любого типа цветовой расцветки:
- Вычисление СЧЁТ, СУММ, СРЗНАЧ, МИН, МАКС для каждого цвета
- Агрегация по цвету заливки и/или цвету условного форматирования
- Cчёт по цвету фона ячеек или цвету шрифта
- Предпросмотр и вставка таблицы результата на рабочий лист
Добавить «Счёт по цвету» в Excel 2019, 2016, 2013, 2010, 2007
Подходит для: Microsoft Excel 2019 - 2007, desktop Office 365 (32-бит и 64-бит).
Как работать с надстройкой:
Как посчитать значения ячеек на основе цвета заливки
Excel по-разному обрабатывает два типа цветного фона ячеек: заливка сплошным цветом, заданная пользователем (когда вы сами назначаете цвет) и условный цвет (при применении условного форматирования). С надстройкой вы можете агрегировать значения по каждому типу цветного фона.
- Нажмите кнопку «Счёт по цвету» на панели XLTools > Укажите диапазон.
- Заданные цвета - чтобы учитывать только ячейки со сплошной заливкой фона
- Из следующего выпадающего списка выберите «Вычислять по цвету» «Фона».
Внимание: надстройка автоматически распознает и произведет расчёт по всем цветам в диапазоне. Ячейки без фона, без заливки и ячейки с белой заливкой обрабатываются вместе. Так, вы сможете сравнить результаты вычислений по цветным и бесцветным ячейкам.- Нажмите OK > Готово!
Как посчитать значения ячеек на основе цвета шрифта
Excel по-разному обрабатывает два типа цветного шрифта: цвет шрифта, заданный пользователем (когда вы сами назначаете цвет) и условный цвет шрифта (при применении условного форматирования). С надстройкой вы можете агрегировать значения по каждому типу цветного шрифта.
- Нажмите кнопку «Счёт по цвету» на панели XLTools > Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически. - Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:
- Заданные цвета - чтобы учитывать только ячейки заданным цветом шрифта
- Все цвета - чтобы учитывать все ячейки, как с заданными, так и условными цветами.
- Из следующего выпадающего списка выберите «Вычислять по цвету» «Шрифта».
- Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс - с разбивкой по цвету.
- Выберите поместить результаты на новый или существующий лист.
- Нажмите OK > Готово!
Как посчитать значения ячеек на основе цвета условного форматирования
Excel по-разному обрабатывает два типа цветного фона ячеек: заливка сплошным цветом, заданная пользователем (когда вы сами назначаете цвет) и условный цвет (при применении условного форматирования). Условное форматирование может применятся как к шрифту, так и к фону фчейки. С надстройкой вы можете агрегировать значения по любому типу условного цвета.
- Нажмите кнопку «Счёт по цвету» на панели XLTools > Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически. - Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:
- Условные цвета - чтобы учитывать только ячейки с условным форматированием
- Все цвета - чтобы учитывать все ячейки, как с заданными, так и условными цветами.
- Из следующего выпадающего списка выберите «Вычислять по цвету» «Фона» или «Шрифта», в зависимости от типа условного форматирования.
- Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс - с разбивкой по цвету.
- Выберите поместить результаты на новый или существующий лист.
- Нажмите OK > Готово!
Доступные агрегатные вычисления (Count, Sum, Average, Minimum, Maximum)
Надстройка производит расчёт самых частых агрегатных функций, на основе цвета шрифта или фона ячейки:
- СЧЁТ (COUNT) - подсчёт количества всех значений в диапазоне по цвету
- СУММ (SUM) - сумма всех значений в диапазоне по цвету
- СРЗНАЧ (AVERAGE) - среднее (арифметическое среднее) всех значений в диапазоне по цвету
- МИН (MIN) - наименьшее значение в диапазоне по цвету
- МАКС (MAX) - наибольшее значение в диапазоне по цвету
Как ячейки и значения учитываются при вычислениях
Надстройка автоматически распознает и произведет расчет по всем цветам в диапазоне. Включая чёрный цвет по умолчанию — так, вы сможете сравнить результаты значений с цветным и чёрным цветом шрифта.
- В расчёт принимаются: числовые значения, а также формулы, функции, ссылки на ячейки, которые возвращают числовое значение. Все пустые ячейки и ячейки, которые содержат текст, даты, ошибки игнорируются.
- Надстройка игнорирует скрытые строки или столбцы, т.е. в вычислениях учитываются только видимые ячейки. Если вы хотите провести вычисления по всему диапазону, пожалуйста, отобразите строки/столбцы и очистите фильтры.
- Вставленная на лист, сводная таблица с результатами вычислений содержит значения (не формулы и не ссылки).
- Объединённые ячейки обрабатываются как одна ячейка.