Подсчитать сумму ячеек по цвету заливки
Задача подсчитать ячейки по цвету заливки перестала быть даже редкостью - данный вопрос постоянно появляется на форумах. Решил выложить текст пользовательской функции, которая суммирует данные ячеек на основе цвета заливки. В чем отличие от остальных функций в интернете - функция может работать только с видимыми ячейками. Т.е. если отфильтровать диапазон, то функция подсчитает данные только отфильтрованных ячеек.
Если не знаете что такое функция пользователя советую сначала прочитать статью: Что такое функция пользователя(UDF)?
Option Explicit '--------------------------------------------------------------------------------------- ' Procedure : SumByInteriorColor ' Author : The_Prist(Щербаков Дмитрий) ' http://www.excel-vba.ru ' Purpose : Функция суммирования ячеек на основе цвета заливки. ' Аргументы: ' rRange - диапазон с ячейками для суммирования. ' rColorCell - ячейка-образец с цветом заливки. ' bSumHide - ИСТИНА или 1 учитывает скрытые ячейки. ' ЛОЖЬ, 0 или опущен(по умолчанию) - скрытые ячейки не суммируются. '--------------------------------------------------------------------------------------- Function SumByInteriorColor(rRange As Range, rColorCell As Range, Optional bSumHide As Boolean = False) 'Application.Volatile 'раскомментировать, чтобы функция обновляла свои значения по нажатию Shift+F9(пересчет листа) Dim lColor As Long, rCell As Range, dblSum As Double, vVal lColor = rColorCell.Interior.Color For Each rCell In rRange If rCell.Interior.Color = lColor Then vVal = rCell.Value If IsNumeric(vVal) Then If rCell.EntireRow.Hidden Or rCell.EntireColumn.Hidden Then If bSumHide Then dblSum = dblSum + vVal Else dblSum = dblSum + vVal End If End If End If Next rCell SumByInteriorColor = dblSum End Function |
Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA(
Синтаксис функции:
без учета скрытых строк и столбцов:
все ячейки(с учетом скрытых):
Чтобы подсчитывалось количество ячеек, а не их сумма, то следует применить другую функцию:
'--------------------------------------------------------------------------------------- ' Procedure : CountByInteriorColor ' Author : The_Prist(Щербаков Дмитрий) ' http://www.excel-vba.ru ' Purpose : Функция подсчета ячеек на основе цвета заливки. ' Аргументы: ' rRange - диапазон с ячейками для подсчета. ' rColorCell - ячейка-образец с цветом заливки. ' bSumHide - ИСТИНА или 1 учитывает скрытые ячейки. ' ЛОЖЬ, 0 или опущен(по умолчанию) - скрытые ячейки не подсчитываются. ' IsMissEmpty - если ИСТИНА или 1(по умолчанию) - пустые ячейки пропускаются ' ЛОЖЬ, 0 или опущен - пустые ячейки не суммируются '--------------------------------------------------------------------------------------- Function CountByInteriorColor(rRange As Range, rColorCell As Range, Optional bSumHide As Boolean = False, _ Optional IsMissEmpty As Boolean = True) 'Application.Volatile 'раскомментировать, чтобы функция обновляла свои значения по нажатию Shift+F9(пересчет листа) Dim lColor As Long, rCell As Range, lCnt As Long, vVal lColor = rColorCell.Interior.Color For Each rCell In rRange If rCell.Interior.Color = lColor Then vVal = 1 If rCell.EntireRow.Hidden Or rCell.EntireColumn.Hidden Then If Not bSumHide Then vVal = 0 End If End If If IsMissEmpty Then If Len(rCell.Value) = 0 Then vVal = 0 End If End If lCnt = lCnt + vVal End If Next rCell CountByInteriorColor = lCnt End Function |
Синтаксис и аргументы практически полностью идентичны с функцией SumByInteriorColor, за исключением последнего -
Синтаксис функции:
без учета скрытых строк и столбцов, только со значениями:
с учетом скрытых строк и столбцов, только со значениями:
все ячейки(и скрытые и без значений):
Так же функции не будут автоматически обновлять значения сразу после смены заливки ячеек - это особенность Excel. Поэтому при изменении заливки в вычисляемом диапазоне необходимо вручную пересчитать функцию(
Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Добрый вечер. Подскажите, возможно ли в Ваш макрос добавить вторую ячейку - образец для цвета? Причем таким образом, если не указана вторая ячейка-образец, то сумма ведется по первой, а если указана вторая, то по двум образцам?
Можно. Для этого и выложил код - посмотрели принцип и при желании доработали под свои нужды.
Отлично! Две формулы - и все по делу. Большое спасибо!
Добрый день. А как добавить второе условие? например =SumByInteriorColor($A$1:$A$10;B1;$A$1:$A$10;<50)или как в СУММЕСЛИМН(диапазон_суммирования, диапазон_условий1, условие1,[диапазон_условий2, условие2], …) добавить условие по цвету ячейки
Добавить можно только изменив код, внедрив туда еще один критерий и диапазон и, соответственно, сравнение по ним.
Добрый день! Подскажите пожалуйста. Как прописать код так, чтобы объединенные ячейки одного цвета он считал как одну, а не как количество объединенных?
Огромное Вам спасибо! Что только я ни читала и что не делала - результата "ноль"! И только Ваши рекомендации помогли. Это реально то, что мне нужно было. Все проверила, работает. Только я поменяла модификацию в формуле, так как мне нужно было скопировать на несколько строк с одним условием. СПАСИБО!
Ирина, спасибо за отзыв! Рад, что статья помогла Вам.
Нету функции для подсчета количества ячеек залитых при помощи условного форматирования?
Здесь я выкладывал готовую надстройку:Надстройка для суммирования ячеек по цвету заливки или шрифта