Как посчитать количество и сумму ячеек по цвету в Excel 2010 и 2013

Из этой статьи Вы узнаете, как в Excel посчитать количество и сумму ячеек определенного цвета. Этот способ работает как для ячеек, раскрашенных вручную, так и для ячеек с правилами условного форматирования. Кроме того, Вы научитесь настраивать фильтр по нескольким цветам в Excel 2010 и 2013.

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

Как известно, Microsoft Excel предоставляет набор функций для различных целей, и логично предположить, что существуют формулы для подсчёта ячеек по цвету. Но, к сожалению, не существует формулы, которая позволила бы на обычном листе Excel суммировать или считать по цвету.

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

Как считать и суммировать по цвету на листе Excel

Предположим, у Вас есть таблица заказов компании, в которой ячейки в столбце Delivery раскрашены в зависимости от их значений: Due in X Days – оранжевые, Delivered – зелёные, Past Due – красные.

Подсчет и суммирование ячеек по цвету в Excel

Теперь мы хотим автоматически сосчитать количество ячеек по их цвету, то есть сосчитать количество красных, зелёных и оранжевых ячеек на листе. Как я уже сказал выше, прямого решения этой задачи не существует. Но, к счастью, в нашей команде есть очень умелые и знающие Excel гуру, и один из них написал безупречный код для Excel 2010 и 2013. Итак, выполните 5 простых шагов, описанных далее, и через несколько минут Вы узнаете количество и сумму ячеек нужного цвета.

  1. Откройте книгу Excel и нажмите Alt+F11, чтобы запустить редактор Visual Basic for Applications (VBA).
  2. Правой кнопкой мыши кликните по имени Вашей рабочей книги в области Project – VBAProject, которая находится в левой части экрана, далее в появившемся контекстном меню нажмите Insert > Module.

    Подсчет и суммирование ячеек по цвету в Excel

  3. Вставьте на свой лист вот такой код:
Function GetCellColor(xlRange As Range)
    Dim indRow, indColumn As Long
    Dim arResults()
 
    Application.Volatile
 
    If xlRange Is Nothing Then
        Set xlRange = Application.ThisCell
    End If
 
    If xlRange.Count > 1 Then
      ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
       For indRow = 1 To xlRange.Rows.Count
         For indColumn = 1 To xlRange.Columns.Count
           arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color
         Next
       Next
     GetCellColor = arResults
    Else
     GetCellColor = xlRange.Interior.Color
    End If
End Function
 
Function GetCellFontColor(xlRange As Range)
    Dim indRow, indColumn As Long
    Dim arResults()
 
    Application.Volatile
 
    If xlRange Is Nothing Then
        Set xlRange = Application.ThisCell
    End If
 
    If xlRange.Count > 1 Then
      ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
       For indRow = 1 To xlRange.Rows.Count
         For indColumn = 1 To xlRange.Columns.Count
           arResults(indRow, indColumn) = xlRange(indRow, indColumn).Font.Color
         Next
       Next
     GetCellFontColor = arResults
    Else
     GetCellFontColor = xlRange.Font.Color
    End If
 
End Function
 
Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
 
    Application.Volatile
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.Color Then
            cntRes = cntRes + 1
        End If
    Next cellCurrent
 
    CountCellsByColor = cntRes
End Function
 
Function SumCellsByColor(rData As Range, cellRefColor As Range)
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim sumRes
 
    Application.Volatile
    sumRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.Color Then
            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
        End If
    Next cellCurrent
 
    SumCellsByColor = sumRes
End Function
 
Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
 
    Application.Volatile
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Font.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Font.Color Then
            cntRes = cntRes + 1
        End If
    Next cellCurrent
 
    CountCellsByFontColor = cntRes
End Function
 
Function SumCellsByFontColor(rData As Range, cellRefColor As Range)
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim sumRes
 
    Application.Volatile
    sumRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Font.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Font.Color Then
            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
        End If
    Next cellCurrent
 
    SumCellsByFontColor = sumRes
End Function
  1. Сохраните рабочую книгу Excel в формате .xlsm (Книга Excel с поддержкой макросов).

    Если Вы не слишком уверенно чувствуете себя с VBA, то посмотрите подробную пошаговую инструкцию и массу полезных советов в учебнике Как вставить и запустить код VBA в Excel.

  2. Когда все закулисные действия будут выполнены, выберите ячейки, в которые нужно вставить результат, и введите в них функцию CountCellsByColor:

    CountCellsByColor(диапазон, код_цвета)

В этом примере мы используем формулу =CountCellsByColor(F2:F14,A17), где F2:F14 – это диапазон, содержащий раскрашенные ячейки, которые Вы хотите посчитать. Ячейка A17 – содержит определённый цвет заливки, в нашем случае красный.

Точно таким же образом Вы записываете формулу для других цветов, которые требуется посчитать в таблице (жёлтый и зелёный).

Подсчет и суммирование ячеек по цвету в Excel

Если в раскрашенных ячейках содержатся численные данные (например, столбец Qty. в нашей таблице), Вы можете суммировать значения на основе выбранного цвета ячейки, используя аналогичную функцию SumCellsByColor:

SumCellsByColor(диапазон, код_цвета)

Подсчет и суммирование ячеек по цвету в Excel

Как показано на снимке экрана ниже, мы использовали формулу:

=SumCellsByColor(D2:D14,A17)

где D2:D14 – диапазон, A17 – ячейка с образцом цвета.

Таким же образом Вы можете посчитать и просуммировать ячейки по цвету шрифта при помощи функций CountCellsByFontColor и SumCellsByFontColor соответственно.

Подсчет и суммирование ячеек по цвету в Excel

Замечание: Если после применения выше описанного кода VBA Вам вдруг потребуется раскрасить ещё несколько ячеек вручную, сумма и количество ячеек не будут пересчитаны автоматически после этих изменений. Не ругайте нас, это не погрешности кода :)

На самом деле, это нормальное поведение макросов в Excel, скриптов VBA и пользовательских функций (UDF). Дело в том, что все подобные функции вызываются только изменением данных на листе, но Excel не расценивает изменение цвета шрифта или заливки ячейки как изменение данных. Поэтому, после изменения цвета ячеек вручную, просто поставьте курсор на любую ячейку и кликните F2, а затем Enter, сумма и количество после этого обновятся. Так нужно сделать, работая с любым макросом, который Вы найдёте далее в этой статье.

Считаем сумму и количество ячеек по цвету во всей книге

Представленный ниже скрипт Visual Basic был написан в ответ на один из комментариев читателей (также нашим гуру Excel) и выполняет именно те действия, которые упомянул автор комментария, а именно считает количество и сумму ячеек определённого цвета на всех листах данной книги. Итак, вот этот код:

Function WbkCountCellsByColor(cellRefColor As Range)
    Dim vWbkRes
    Dim wshCurrent As Worksheet
 
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    vWbkRes = 0
    For Each wshCurrent In Worksheets
       wshCurrent.Activate
       vWbkRes = vWbkRes + CountCellsByColor(wshCurrent.UsedRange, cellRefColor)
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
 
    WbkCountCellsByColor = vWbkRes
End Function
 
Function WbkSumCellsByColor(cellRefColor As Range)
    Dim vWbkRes
    Dim wshCurrent As Worksheet
 
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    vWbkRes = 0
    For Each wshCurrent In Worksheets
       wshCurrent.Activate
       vWbkRes = vWbkRes + SumCellsByColor(wshCurrent.UsedRange, cellRefColor)
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
 
    WbkSumCellsByColor = vWbkRes
End Function

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

=WbkCountCellsByColor()
=WbkSumCellsByColor()

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

Пользовательские функции для определения кодов цвета заливки ячеек и цвета шрифта

Здесь Вы найдёте самые важные моменты по всем функциям, использованным нами в этом примере, а также пару новых функций, которые определяют коды цветов.

Замечание: Пожалуйста, помните, что все эти формулы будут работать, если Вы уже добавили в свою рабочую книгу Excel пользовательскую функцию, как было показано ранее в этой статье.

Функции, которые считают количество по цвету:

  • CountCellsByColor(диапазон, код_цвета) – считает ячейки с заданным цветом заливки.

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

    =CountCellsByColor(F2:F14,A17)

    где F2:F14 – это выбранный диапазон, A17 – это ячейка с нужным цветом заливки.

    Все перечисленные далее формулы работают по такому же принципу.

  • CountCellsByFontColor(диапазон, код_цвета) – считает ячейки с заданным цветом шрифта.

Функции, которые суммируют значения по цвету ячейки:

  • SumCellsByColor(диапазон, код_цвета) – вычисляет сумму ячеек с заданным цветом заливки.
  • SumCellsByFontColor(диапазон, код_цвета) – вычисляет сумму ячеек с заданным цветом шрифта.

Функции, которые возвращают код цвета:

  • GetCellFontColor(ячейка) – возвращает код цвета шрифта в выбранной ячейке.
  • GetCellColor(ячейка) – возвращает код цвета заливки в выбранной ячейке.

    Подсчет и суммирование ячеек по цвету в Excel

Итак, посчитать количество ячеек по их цвету и вычислить сумму значений в раскрашенных ячейках оказалось совсем не сложно, не так ли? Но что если Вы не раскрашиваете ячейки вручную, а предпочитаете использовать условное форматирование, как мы делали это в статьях Как изменить цвет заливки ячеек и Как изменить цвет заливки строки, основываясь на значении ячейки?

Как посчитать количество и сумму ячеек по цвету, раскрашенных при помощи условного форматирования

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

Конечно, Вы можете найти тонны кода VBA в интернете, который пытается сделать это, но все эти коды (по крайней мере, те экземпляры, которые попадались мне) не обрабатывают правила условного форматирования, такие как:

  • Format all cells based on their values (Форматировать все ячейки на основании их значений);
  • Format only top or bottom ranked values (Форматировать только первые или последние значения);
  • Format only values that are above or below average (Форматировать только значения, которые находятся выше или ниже среднего);
  • Format only unique or duplicate values (Форматировать только уникальные или повторяющиеся значения).

Кроме того, практически все эти коды VBA имеют целый ряд особенностей и ограничений, из-за которых они могут не работать корректно с какой-то конкретной книгой или типами данных. Так или иначе, Вы можете попытать счастье и google в поисках идеального решения, и если Вам удастся найти его, пожалуйста, возвращайтесь и опубликуйте здесь свою находку!

Код VBA, приведённый ниже, преодолевает все указанные выше ограничения и работает в таблицах Microsoft Excel 2010 и 2013, с любыми типами условного форматирования (и снова спасибо нашему гуру!). В результате он выводит количество раскрашенных ячеек и сумму значений в этих ячейках, независимо от типа условного форматирования, применённого на листе.

Sub SumCountByConditionalFormat()
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
    Dim sumRes
    Dim cntCells As Long
    Dim indCurCell As Long
 
    cntRes = 0
    sumRes = 0
 
    cntCells = Selection.CountLarge
    indRefColor = ActiveCell.DisplayFormat.Interior.Color
 
    For indCurCell = 1 To (cntCells - 1)
        If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color Then
            cntRes = cntRes + 1
            sumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes)
        End If
    Next
   MsgBox "Count=" & cntRes & vbCrLf & "Sum= " & sumRes & vbCrLf & vbCrLf & _
        "Color=" & Left("000000", 6 - Len(Hex(indRefColor))) & _
        Hex(indRefColor) & vbCrLf, , "Count & Sum by Conditional Format color"
End Sub

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

  1. Добавьте код, приведённый выше, на Ваш лист, как мы делали это в первом примере.
  2. Выберите диапазон (или диапазоны), в которых нужно сосчитать цветные ячейки или просуммировать по цвету, если в них содержатся числовые данные.
  3. Нажмите и удерживайте Ctrl, кликните по одной ячейке нужного цвета, затем отпустите Ctrl.
  4. Нажмите Alt+F8, чтобы открыть список макросов в Вашей рабочей книге.
  5. Выберите макрос SumCountByConditionalFormat и нажмите Run (Выполнить).

    Подсчет и суммирование ячеек по цвету в Excel

    В результате Вы увидите вот такое сообщение:

    Подсчет и суммирование ячеек по цвету в Excel

Для этого примера мы выбрали столбец Qty. и получили следующие цифры:

  • Count – это число ячеек искомого цвета; в нашем случае это красноватый цвет, которым выделены ячейки со значением Past Due.
  • Sum – это сумма значений всех ячеек красного цвета в столбце Qty., то есть общее количество элементов с отметкой Past Due.
  • Color – это шестнадцатеричный код цвета выделенной ячейки, в нашем случае D2.

Рабочая книга с примерами для скачивания

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

15.07.2015 20:00
31319

9 комментариев

20.10.2015 06:01
Спасибо большое за статью, очень помогла!
Подскажите, возможно ли переделать SumCountByConditionalFormat в функцию, для того, чтобы посчитать количество ячеек выделенных условным форматированием в указанном диапазоне?
Нужно посчитать количество ячеек, выделенных условным форматированием, в каждой строке. Строк очень много. Обработать каждую при помощи SumCountByConditionalFormat очень долго.
Добрый день!

Быстро переделать точно не получится, тем более под ваши условия. Условное форматирование и VBA очень плохо дружат, поэтому создать какую-то более или менее универсальную функцию вряд ли получится. Решения есть, но они не универсальны, к примеру, могут работать только с определенными правилами условного форматирования.

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

В общем здесь есть определенные трудности (кстати об этом указано в статье), и это ограничение в первую очередь идет от самих создателей Excel.

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

Как вариант, могу предложить платное решение. Существует довольно мощная надстройка, одним из инструментов которой является то, что вам нужно. Есть демо версия на 20 дней и небольшое видео по работе. Глубоко не разбирался, поэтому чего-то конкретного по ней сказать не могу. Но это опять же не пользовательская функция, а что-то похожее на макрос SumCountByConditionalFormat.
17.11.2015 11:10
Здравствуйте. Статья очень информативна. Очень помогла. Но у меня возникла сложность: есть ли возможность прописать макрос, чтобы считалась последняя часть в заданном диапазоне и обнулялась(выводился «0») при изменении цвета на любой другой отличимый от оранжевого(для примера. Диапазон выбран В5:Z5
Здравствуйте!

Не уверен, что понял правильно:
  • Если последняя ячейка в диапазоне не оранжевого цвета, то функция должна возвратить ноль?

  • Если последняя ячейка оранжевого цвета, то считаем сколько оранжевых ячеек на конце диапазона (начиная от последней оранжевой и до первой не оранжевой в левую сторону)?

Написать такой макрос можно. Но это уже абсолютно другая функция для решения другой задачи. Т.е. в первом случае вам нужно было пройтись по всему диапазону (просто от начала и до конца) и методом сравнения посчитать кол-во оранжевых ячеек. В данной же ситуации нужно начать с последней ячейки диапазона и прервать цикл, если попадется не оранжевая ячейка. Таким образом функция вернет нужное вам значение.
18.11.2015 09:49
Поняли Вы всё совершенно верно, именно это мне и необходимо. Не могли бы Вы помочь с написанием данного макроса? За ранее благодарен
Поймите меня правильно. Но я не занимаюсь написанием макросов на заказ (за деньги тоже). Если вам очень нужен такой макрос, то за символическую плату вы без труда найдете такого специалиста. Если же он вам нужен исключительно в учебных целях, то существует множество литературы по VBA. Там все написано.

Если сами начнете писать макрос и возникнут какие-то конкретные вопросы, например «Как лучше сделать так или вот так?» — то я с радостью на них отвечу. Хотя, если начнете разбираться, то все вопросы отпадут сами собой.

Может быть когда-нибудь я сделаю урок на эту тему, но учитывая низкую популярность вопроса — это будет не скоро :-)
26.01.2016 16:56
Добрый день! Подскажи, пожалуйста, как можно посчитать количество цветных ячеек, не суммируя их значения и не учитывая пустых цветных ячеек, в заданном диапазоне?
Заранее большое спасибо!
03.06.2016 13:06
Спасибо за вашу работу и лаконичность в объяснениях.

У меня возникла проблема при подсчете ячеек заданного цвета при условном форматировании.
Макрос «SumCountByConditionalFormat» выдает ошибку, см скриншот:

нажимая кнопку «debug» выдает следующее:

Может из — за версии Exel 2007 русс?
К сожалению, подсказать не могу. У меня в Excel 2016 все работает, по крайней мере рабочая книга с примерами из этой статьи.