Разберем простые способы как посчитать количество, и как суммировать ячейки по цвету в Excel.
Приветствую всех, дорогие читатели блога TutorExcel.Ru.
Мы часто при работе в Excel окрашиваем ячейки различными цветами для лучшей визуализации данных.
Однако, когда возникает необходимость произвести какие-либо расчеты с обработанными данными мы сталкиваемся с трудностями в связи с малыми возможностями стандартных средств Excel.
Если сделать сортировку и фильтр ячеек по цвету мы стандартными способами Excel можем, то вот посчитать сумму или количество пока еще нет.
Поэтому давайте рассмотрим две достаточно простые функции, которые дают возможность суммировать ячейки, и посчитать количество выделенных цветом ячеек.
Суммирование ячеек по цвету
Для начала перейдем в редактор VBA, для этого в панели вкладок выбираем Разработчик -> Visual Basic (или нажимаем комбинацию клавиш Alt + F11).
Создаем новый модуль и добавляем в него следующий код функции для суммы (напротив каждой строчки дается пояснение к коду):
|
1 2 3 4 5 6 7 8 9 10 11 |
Public Function СУММЦВЕТ(MyRange As Range, MyCell As Range) As Double Dim Sum As Double 'Ввод переменной Sum для подсчета суммы Sum = 0 'Приравнивание переменной Sum к нулю Application.Volatile True 'Пересчет функции при каком-либо изменении значений ячеек листа For Each cell In MyRange 'Цикл по всем ячейкам диапазона If cell.Interior.Color = MyCell.Interior.Color Then 'Проверка текущей ячейки на условие по цвету Sum = Sum + cell.Value 'Значение текущей ячейки прибавляется к промежуточной сумме End If Next СУММЦВЕТ = Sum 'Приравнивание возвращаемому результату значения конечной суммы End Function |
Функция СУММЦВЕТ содержит два аргумента:
- MyRange (обязательный аргумент) — диапазон ячеек для суммирования;
- MyCell (обязательный аргумент) — ячейка, по цвету заливки которой рассчитывается сумма.
Функция СУММЦВЕТ теперь будет определяться при вводе формулы в ячейку, переходим из редактора на лист Excel и, чтобы просуммировать ячейки воспользуемся новой функцией:
Как мы видим, в итоге для каждого определенного цвета ячейки мы получили различный результат.
При этом, если выбранная ячейка не имеет заливки, то функция суммы и подсчета количества ячеек по выбранному цвету также будет работать.
Подсчет количества ячеек по цвету
Чтобы посчитать ячейки одного цвета достаточно немного видоизменить функцию для подсчета суммы — вместо прибавления значения текущей ячейки (Sum = Sum + cell.Value) мы добавляем 1 (Sum = Sum + 1).
Замечания
При работе с данными функциями обратите внимание на два важных момента:
- Если цвет выбранной ячейки определяется с помощью условного форматирования (т.е. цвет ячейки определяется не за счет заливки), то рассмотренные функции для суммирования и подсчета ячеек не сработают.
- В случае изменения раскраски ячейки в Excel формулы автоматически не пересчитываются, так как не изменяется содержимое ячейки, поэтому для корректного расчета необходимо произвести пересчет формул. Комбинация клавиш Shift + F9 пересчитает формулы на активном листе (F9 — для всей книги).
Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — пишите в комментариях.
Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!
не работает с формулами. То бишь в ячейке результат формулы этой ячейки. Из-за этого, данная функция не определяет в ней число, по всей видимости.
А можете более подробно описать ошибку?
В чем конкретно проблема?
Громадное, спасибо, всё работает.
НА mac работает на ура но в windows даже после разбора с кириллицей бред, при редактировании формулы ссылается верно на ячейки но считать отказывается выделяет желтым первую строчку и просит выбрать библиотеку.
(Public Function SUMMCVET(MyRange As Range, MyCell As Range) As Double)
на изменение названия не гляди это устранения проблемы с кириллицей двух ОС, каманду СУММЦВЕТ везде заменил на эту.
Супер, все работает!!! Все никак не могу научится самому писать макросы, то времени нет, то лень(( Спасибо.
добрый день. у меня такая проблема. создал модуль, вставил формулу (сумма ячеек по цвету), ввел её в ячейку, всё работает прекрасно. создаю второй модуль, вставляю другую формулу (количество ячеек по цвету) и всё, ни одна формула не работает. при попытке вставить формулу выдаёт ошибку — у данной функции нет аргументов.
Такого по идее быть не должно. Их без проблем можно поместить в один модуль (как в файле-примере).
Но если все же нужно поместить их в разные модули, то перепроверьте, что функции в разных модулях не дублируются.
Если это никак не поможет, то можете прислать мне файл на почту — смогу посмотреть.
Скачал ваш файл, открыл, а он вышлет ошибку: #имя?
Проверьте включены ли макросы в книге, такие ошибки могут возникать если не включены.
Спасибо большое! С настройкой макросов разобрался. Ваш файл работает, все считает, а вот с моим все та же проблема
Огромное спасибо за такое решение проблемы. Все работает отлично. Подскажите пожалуйста, как загрузить макрос в рабочую книгу макросов для дальнейшей работы с файлами?
Галина, если вкратце, то нужно добавить макрос в личную книгу макросов.
Если ее еще не создавали, то макрорекордером запишите новый макрос (можно пустой, главное просто создать) и при его создании укажите место сохранения «Личная книга макросов».
Теперь в VBA появится новый файл «Personal.xlsb» (как раз это и есть личная книга макросов), в модуль которого и можно добавлять ваши макросы (и использовать в дальнейшем при работе с любыми файлами Excel).
Подскажите куда в макрос надо тыкнуть, чтоб считал ячейки определенного цвета выше нуля???
Можно добавить проверку на значение суммируемой ячейки, т.е. вместо «If cell.Interior.Color = MyCell.Interior.Color» в макросе пишем «If cell.Interior.Color = MyCell.Interior.Color And cell.Value > 0», в этом случае будут считаться только положительные ячейки (>0).
Добрый день. Спасибо за информацию. Можете помочь, нужно добавить дополнительные значения в виде подсчёта только видимых по цвету ячеек
Огромное спасибо!
Всё работает!
Моему счастью и восторгу нет предела🤗
Добрый день! Спасибо большое, материал изложен очень доступно.
НО, как быть в том случае, если необходимо посчитать сумму ячеек определенного цвета, при том, что цвета ячеек определяется с помощью условного форматирования????
С условным форматированием, к сожалению, все непросто и нормального и готового решения нет 🙁
Проверить условие на цвет ячейки с условным форматированием через VBA не получится, так как такой возможности в VBA попросту нет.
Т.е. функции работают только для ячеек с классическим способом заливки.
Разобрался, спасибо! Вот только считать начинает (изменяет числа) только после закрытия файла и открытия. Можно ли сделать что-то типа обновления или кнопки запуск.
Да, такая проблема может быть, я в пункте с замечаниями про это пишу — в случае изменения раскраски формулы в Excel не пересчитываются.
Можно нажать F9 или Shift + F9 для пересчета формул.
Да, спасибо! В конце видео вы об этом сказали. Очень полезные вещи делаете для общества. Спасибо ещё раз!
Добрый день.
Вопрос. Всё работает хорошо, за исключением нюанса: при внесении изменений цветовой раскладки в таблице автоматически данные сразу не меняются. Приходится повторно активировать формулу и нажимать «enter». Можете подсказать возможную причину?
А так от души спасибо за помощь, очень нужная функция.
Причина в том, что изменение заливки ячейки не меняет содержимого ячейки, а Excel делает пересчет формул именно при изменении содержимого. Поэтому если просто изменить цвет заливки, то ничего не произойдет, но если войти в ячейку и нажать Enter, то Excel посчитает, что поменялось содержимое ячейки и все пересчитает.
К сожалению такая проблема существует, как вариант решения — можно нажать F9 или Shift + F9 для пересчета формул.