Перейти к содержимому

Советы по Excel: Подсчет/суммирование ячеек по цвету (фон, шрифт, условное форматирование)

Author: Xiaoyang Last Modified: 2025-06-05

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

count sum colored cells example

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

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

Подсчет и суммирование ячеек на основе цвета условного форматирования


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

 


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

Например, если у вас есть диапазон данных, где значения заполнены разными цветами фона, как показано на скриншоте ниже. Чтобы подсчитать или суммировать ячейки на основе определенного цвета, Excel не предлагает прямой функции для подсчета или суммирования ячеек на основе их цвета фона. Однако, немного изобретательности и несколько удобных техник помогут вам выполнить эту задачу. Давайте рассмотрим некоторые полезные методы в этом разделе.
count sum colored cells data


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

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

Шаг 1: Откройте редактор модуля VBA и скопируйте код

  1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.
  2. В открывшемся окне нажмите Insert > Module, чтобы создать новый пустой модуль.
  3. Затем скопируйте и вставьте приведенный ниже код в пустой модуль.
    Код VBA: Подсчет и суммирование ячеек на основе цвета фона
    Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
    'Updateby Extendoffice
        Dim rCell As Range
        Dim lCol As Long
        Dim vResult As Double
        lCol = rColor.Interior.ColorIndex
        vResult = 0
        If SUM Then
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + rCell.Value
                End If
            Next rCell
        Else
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + 1
                End If
            Next rCell
        End If
        ColorFunction = vResult
    End Function
    

Шаг 2: Создайте формулы для подсчета и суммирования ячеек по цвету фона

После вставки вышеуказанного кода закройте окно модуля, затем примените следующие формулы:

  • Подсчет ячеек на основе конкретного цвета фона:
    Скопируйте или введите указанную ниже формулу в нужную ячейку для получения результата. Затем перетащите маркер заполнения вниз, чтобы получить другие результаты. См. скриншот:
    =colorfunction(G2,$B$2:$E$12,FALSE) 
    Примечание: В этой формуле G2 — это ссылочная ячейка с конкретным цветом фона, который вы хотите сопоставить; $B$2:$E$12 — это диапазон, где вы хотите подсчитать количество ячеек цвета G2; FALSE используется для подсчета ячеек с совпадающим цветом.
    count sum background color formula
  • Суммирование ячеек на основе конкретного цвета фона:
    Скопируйте или введите указанную ниже формулу в нужную ячейку для получения результата. Затем перетащите маркер заполнения вниз, чтобы получить другие результаты. См. скриншот:
    =colorfunction(G2,$B$2:$E$12,TRUE)  
    Примечание: В этой формуле G2 — это ссылочная ячейка с конкретным цветом фона, который вы хотите сопоставить; $B$2:$E$12 — это диапазон, где вы хотите подсчитать количество ячеек цвета G2; TRUE используется для суммирования ячеек с совпадающим цветом.
    count sum background color result

Подсчет и суммирование ячеек по цвету фона с помощью мощной функции

Для тех, кто не знаком с программированием, VBA может показаться довольно сложным. Здесь мы представляем мощный инструмент - Kutools для Excel, его функция Count by Color позволяет легко выполнять расчеты (подсчет, суммирование, среднее значение и т.д.) на основе цвета фона всего за несколько кликов. Впечатляюще то, что функция Count by Color выходит за рамки простых цветов фона – она также может различать и рассчитывать на основе цвета шрифта и условного форматирования.

После загрузки и установки Kutools для Excel сначала выберите диапазон данных, по которому вы хотите подсчитать или суммировать ячейки на основе определенного цвета фона. Затем перейдите в Kutools Plus и выберите Count by Color.

В диалоговом окне Count by Color укажите операции:

  1. Выберите Standard formatting из выпадающего списка Color method;
  2. Укажите Background из выпадающего списка Count type, и вы можете просмотреть статистические результаты для каждого цвета фона в диалоговом окне;
  3. Наконец, нажмите Generate report, чтобы экспортировать рассчитанные результаты в новую рабочую книгу.

count sum background color kutools

Результат:

Теперь вы получите новую рабочую книгу со статистикой. См. скриншот:
count sum background color kutools result

Советы:
  1. Функция Count by Color также поддерживает подсчет и суммирование ячеек на основе стандартного цвета шрифта, цвета фона или цвета шрифта из условного форматирования, а также комбинации обоих цветов заливки и условного форматирования.
    count sum background color kutools option
  2. Заинтересованы в этой функции? Пожалуйста, нажмите, чтобы скачать бесплатную пробную версию на 30 дней.

Подсчет и суммирование ячеек по цвету фона с помощью фильтра и функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Предположим, у нас есть таблица продаж фруктов, как показано на скриншоте ниже, и мы будем подсчитывать или суммировать цветные ячейки в столбце Сумма .
count sum background color subtotal data

Шаг 1: Применение функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Выберите пустые ячейки для ввода функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

  • Чтобы подсчитать все ячейки с одинаковым цветом фона, введите формулу:
    =SUBTOTAL(102, F2:F16)
  • Чтобы суммировать все ячейки с одинаковым цветом фона, введите формулу:
    =SUBTOTAL(109, F2:F16)
  • Примечание: в приведенных выше формулах 102 представляет собой подсчет числовых значений в отфильтрованном списке, исключая скрытые ячейки; 109 представляет собой сумму значений в отфильтрованном списке, исключая скрытые ячейки; F2:F16 — это диапазон, по которому будет выполнен либо подсчет, либо суммирование.
  • count sum background color subtotal formula

Шаг 2: Фильтрация ячеек на основе конкретного цвета

  1. Выберите заголовок таблицы и нажмите Данные > Фильтр. См. скриншот:
    count sum background color filter
  2. Нажмите Фильтр значок count sum background color filter icon в ячейке заголовка столбца Сумма и нажмите Фильтр по цвету и указанный цвет, по которому вы будете последовательно подсчитывать. См. скриншот:
    count sum background color filter by color

Результат:

После фильтрации формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ автоматически подсчитывают и суммируют цветные ячейки в столбце Сумма . См. скриншот:
count sum background color filter subtotal result

Примечание: Этот метод требует, чтобы цветные ячейки, которые вы собираетесь подсчитывать или суммировать, находились в одном столбце.

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

Хотите подсчитать или суммировать ячейки на основе их цвета шрифта в Excel? Предположим, у вас есть данные, как на приведенном скриншоте, с ячейками, содержащими тексты красного, синего, оранжевого и черного цветов. Excel не делает это простым по умолчанию. Но не волнуйтесь! В этом разделе мы покажем вам несколько простых приемов, чтобы сделать именно это.
count sum font color data


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

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

Шаг 1: Откройте редактор модуля VBA и скопируйте код

  1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.
  2. В открывшемся окне нажмите Insert > Module, чтобы создать новый пустой модуль.
  3. Затем скопируйте и вставьте приведенный ниже код в пустой модуль.
    Код VBA: Подсчет и суммирование ячеек на основе цвета шрифта
    Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
    'Updateby Extendoffice
        Application.Volatile
        Dim rng As Range
        Dim xTotal As Double
        Dim xCount As Double
        xTotal = 0
        xCount = 0
        For Each rng In pRange1
            If rng.Font.Color = pRange2.Font.Color Then
                If UCase(FunctionType) = "SUM" Then
                    xTotal = xTotal + rng.Value
                ElseIf UCase(FunctionType) = "COUNT" Then
                    xCount = xCount + 1
                End If
            End If
        Next
        If UCase(FunctionType) = "SUM" Then
            ProcessByFontColor = xTotal
        ElseIf UCase(FunctionType) = "COUNT" Then
            ProcessByFontColor = xCount
        Else
            ProcessByFontColor = CVErr(xlErrValue)
        End If
    End Function
    

Шаг 2: Создайте формулы для подсчета и суммирования ячеек по цвету шрифта

После вставки вышеуказанного кода закройте окно модуля, затем примените следующие формулы:

  • Подсчет ячеек на основе конкретного цвета шрифта:
    Скопируйте или введите указанную ниже формулу в нужную ячейку для получения результата. Затем перетащите маркер заполнения вниз, чтобы получить другие результаты. См. скриншот:
    =ProcessByFontColor($B$2:$E$12,G2, "COUNT")
    Примечание: В этой формуле G2 — это ссылочная ячейка с конкретным цветом шрифта, который вы хотите сопоставить; $B$2:$E$12 — это диапазон, где вы хотите подсчитать количество ячеек цвета G2.
    count sum font color formula
  • Суммирование ячеек на основе конкретного цвета шрифта:
    Скопируйте или введите указанную ниже формулу в нужную ячейку для получения результата. Затем перетащите маркер заполнения вниз, чтобы получить другие результаты. См. скриншот:
    =ProcessByFontColor($B$2:$E$12,G2, "SUM")  
    Примечание: В этой формуле G2 — это ссылочная ячейка с конкретным цветом шрифта, который вы хотите сопоставить; $B$2:$E$12 — это диапазон, где вы хотите подсчитать количество ячеек цвета G2.
    count sum font color result

Подсчет и суммирование ячеек на основе цвета шрифта с помощью простой функции

Хотите без усилий подсчитывать или суммировать значения ячеек в Excel на основе цвета шрифта? Познакомьтесь с функцией Count by Color из Kutools для Excel! С этим умным инструментом подсчет и суммирование ячеек по определенному цвету шрифта становится проще простого. Узнайте, как Kutools может преобразить ваш опыт работы с Excel.

После загрузки и установки Kutools для Excel сначала выберите диапазон данных, по которому вы хотите подсчитать или суммировать ячейки на основе определенного цвета шрифта. Затем нажмите Kutools Plus > Count by Color, чтобы открыть диалоговое окно Count by Color.

В диалоговом окне Count by Color укажите операции:

  1. Выберите Standard formatting из выпадающего списка Color method;
  2. Укажите Font из выпадающего списка Count type, и вы можете просмотреть статистические результаты для каждого цвета шрифта в диалоговом окне;
  3. Наконец, нажмите Generate report, чтобы экспортировать рассчитанные результаты в новую рабочую книгу.

count sum font color kutools

Результат:

Теперь у вас есть новая рабочая книга, отображающая подробную статистику на основе цвета шрифта. См. скриншот:
count sum font color kutools result

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

Подсчет и суммирование ячеек на основе цвета условного форматирования

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


Подсчет и суммирование ячеек с условным форматированием с помощью кода VBA

Подсчет и суммирование ячеек с условным форматированием в Excel не является прямолинейным с использованием встроенных функций. Однако вы можете выполнить эту задачу с помощью кода VBA. Давайте рассмотрим, как вы можете использовать VBA для этого:

Шаг 1: Откройте редактор модуля VBA и скопируйте код

  1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.
  2. В открывшемся окне нажмите Insert > Module, чтобы создать новый пустой модуль.
  3. Затем скопируйте и вставьте приведенный ниже код в пустой модуль.
    Код VBA: Подсчет и суммирование ячеек на основе цвета условного форматирования
    Sub SumCountByConditionalFormat()
    'Updateby Extendoffice
        Dim sampleColor As Range
        Dim selectedRange As Range
        Dim cell As Range
        Dim countByColor As Long
        Dim sumByColor As Double
        Dim refColor As Long
        Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                 "Kutools for Excel", _
                                                 Type:=8)
        If selectedRange Is Nothing Then Exit Sub
        Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                               "Kutools for Excel", _
                                               Type:=8)
        If Not sampleColor Is Nothing Then
            refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
            For Each cell In selectedRange
                If cell.DisplayFormat.Interior.color = refColor Then
                    countByColor = countByColor + 1
                    sumByColor = sumByColor + cell.Value
                End If
            Next cell
            MsgBox "Count: " & countByColor & vbCrLf & _
                   "Sum: " & sumByColor, _
                   vbInformation, "Results based on Conditional Format Color"
        End If
    End Sub
    

Шаг 2: Выполните этот код VBA

  1. После вставки кода нажмите F5 , чтобы запустить этот код, появится окно запроса, пожалуйста, выберите диапазон данных, где вы хотите подсчитать и суммировать ячейки на основе условного форматирования. Затем нажмите OK, См. скриншот:
    count sum conditional formatting vba dialog 1
  2. В другом окне запроса выберите конкретный цвет условного форматирования, который вы хотите подсчитать и суммировать, и нажмите OK кнопку, см. скриншот:
    count sum conditional formatting vba dialog 2

Результат:

Теперь результат, который включает как количество, так и сумму ячеек с указанным цветом условного форматирования, будет отображен в всплывающем окне. См. скриншот:
count sum conditional formatting vba result


Подсчет и суммирование ячеек с условным форматированием с помощью умной функции

Если вы ищете другие быстрые и простые методы для подсчета и суммирования ячеек с условным форматированием, Kutools для Excel — это ваше решение. Его функция Count by Color может решить эту задачу всего за несколько кликов. Погрузитесь в работу, чтобы узнать, какую эффективность и точность Kutools может привнести в ваш рабочий процесс.

После загрузки и установки Kutools для Excel сначала выберите диапазон данных, по которому вы хотите подсчитать или суммировать ячейки на основе определенного цвета условного форматирования. Затем нажмите Kutools Plus > Count by Color, чтобы открыть диалоговое окно Count by Color.

В диалоговом окне Count by Color укажите операции:

  1. Выберите Conditional formatting из выпадающего списка Color method;
  2. Укажите Background из выпадающего списка Count type, и вы можете просмотреть статистические результаты для каждого цвета условного форматирования в диалоговом окне;
  3. Наконец, нажмите Generate report, чтобы экспортировать рассчитанные результаты в новую рабочую книгу.

count sum conditional formatting kutools

Результат:

Теперь у вас есть новая рабочая книга, отображающая подробную статистику на основе цвета условного форматирования. См. скриншот:
count sum conditional formatting kutools result

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

Связанные статьи:

  • Если цвет шрифта красный, то вернуть определенный текст
  • Как можно вернуть определенный текст, если цвет шрифта красный в другой ячейке, как показано на скриншоте ниже? В этой статье я представлю несколько приемов для выполнения некоторых операций на основе красного текста шрифта в Excel.
  • Фильтрация данных по нескольким цветам
  • Обычно в Excel вы можете быстро фильтровать строки только с одним цветом, но задумывались ли вы когда-нибудь о фильтрации строк с несколькими цветами одновременно? В этой статье я расскажу о быстром трюке, который поможет вам справиться с этой проблемой.
  • Добавление цвета к выпадающему списку
  • В Excel создание выпадающего списка может помочь вам во многом, и иногда вам нужно закодировать цвета значений выпадающего списка в зависимости от выбранного. Например, я создал выпадающий список названий фруктов, и когда я выбираю Яблоко, мне нужно, чтобы ячейка автоматически окрашивалась в красный цвет, а когда я выбираю Апельсин, ячейка окрашивается в оранжевый цвет.
  • Цветовые альтернативные строки для объединенных ячеек
  • Очень полезно форматировать альтернативные строки разным цветом в больших данных для их сканирования, но иногда в ваших данных могут быть объединенные ячейки. Чтобы выделить строки поочередно разным цветом для объединенных ячеек, как показано на скриншоте ниже, как можно решить эту проблему в Excel?