Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (14)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (38)
Баги и глюки Excel (2)

Подсчитать сумму ячеек по цвету заливки

Задача подсчитать ячейки по цвету заливки перестала быть даже редкостью - данный вопрос постоянно появляется на форумах. Решил выложить текст пользовательской функции, которая суммирует данные ячеек на основе цвета заливки. В чем отличие от остальных функций в интернете - функция может работать только с видимыми ячейками. Т.е. если отфильтровать диапазон, то функция подсчитает данные только отфильтрованных ячеек.
Если не знаете что такое функция пользователя советую сначала прочитать статью: Что такое функция пользователя(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

Синтаксис функции:
без учета скрытых строк и столбцов:
=SumByInteriorColor($A$1:$A$10;B1)
все ячейки:
=SumByInteriorColor($A$1:$A$10;B1;1)

rRange($A$1:$A$10) - ссылка на диапазон с ячейками для суммирования.
rColorCell(B1) - ссылка на ячейка-образец с цветом заливки.
bSumHide - Если указано ИСТИНА или 1 учитывает скрытые ячейки. ЛОЖЬ, 0 или опущен(по умолчанию) - скрытые ячейки не суммируются.


 

Чтобы подсчитывалось количество ячеек, а не их сумма, то функцию надо изменить самую малость:

'---------------------------------------------------------------------------------------
' Procedure : CountByInteriorColor
' Author    : The_Prist(Щербаков Дмитрий)
'             http://www.excel-vba.ru
' Purpose   : Функция подсчета ячеек на основе цвета заливки.
' Аргументы:
'             rRange     - диапазон с ячейками для подсчета.
'             rColorCell - ячейка-образец с цветом заливки.
'             bSumHide   - ИСТИНА или 1 учитывает скрытые ячейки.
'                          ЛОЖЬ, 0 или опущен(по умолчанию) - скрытые ячейки не подсчитываются.
'---------------------------------------------------------------------------------------
Function CountByInteriorColor(rRange As Range, rColorCell As Range, Optional bSumHide As Boolean = False)
    '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
            If rCell.EntireRow.Hidden Or rCell.EntireColumn.Hidden Then
                If bSumHide Then lCnt = lCnt + 1
            Else
                lCnt = lCnt + 1
            End If
        End If
    Next rCell
    CountByInteriorColor = lCnt
End Function

Синтаксис и аргументы полностью идентичны с функцией SumByInteriorColor.

Что следует учитывать: функции подсчитывают и суммируют ячейки на основании цвета заливки, установленного вручную с панели. Если заливка ячеек создана при помощи условного форматирования, то функции не определят цвет этой ячейки. Это связано с особенностями создания визуального изменения свойств ячейки при помощи условного форматирования.

Так же функции не будут автоматически обновлять значения сразу после смены заливки ячеек - это особенность Excel. Поэтому при изменении заливки в вычисляемом диапазоне необходимо вручную пересчитать функцию(F2 -Enter).


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки
Обсуждение: 8 комментариев
  1. Art:

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

  2. Можно. Для этого и выложил код - посмотрели принцип и при желании доработали под свои нужды.

  3. Отлично! Две формулы - и все по делу. Большое спасибо!

  4. HVV10:

    Добрый день. А как добавить второе условие? например =SumByInteriorColor($A$1:$A$10;B1;$A$1:$A$10;<50)или как в СУММЕСЛИМН(диапазон_суммирования, диапазон_условий1, условие1,[диапазон_условий2, условие2], …) добавить условие по цвету ячейки

  5. Ренат:

    Добрый день! Подскажите пожалуйста. Как прописать код так, чтобы объединенные ячейки одного цвета он считал как одну, а не как количество объединенных?

  6. Ирина:

    Огромное Вам спасибо! Что только я ни читала и что не делала - результата "ноль"! И только Ваши рекомендации помогли. Это реально то, что мне нужно было. Все проверила, работает. Только я поменяла модификацию в формуле, так как мне нужно было скопировать на несколько строк с одним условием. СПАСИБО!

Поделитесь своим мнением

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


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2018 Excel для всех   Войти