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

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

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

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

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

• С помощью пользовательской функции
• С помощью простой функции – Kutools для Excel

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

• С помощью кода VBA
• С помощью интеллектуальной функции – Kutools для Excel

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

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

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

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

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

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

1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic для приложений.
2. В открывшемся окне нажмите Вставка > Модуль, чтобы создать новый пустой модуль.
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 указывает на подсчёт ячеек с совпадающим цветом.
  
• Суммирование ячеек по определённому цвету фона:
  Скопируйте или введите приведённую ниже формулу в нужную ячейку, чтобы получить результат. Затем перетащите маркер заполнения вниз для получения остальных результатов. См. снимок экрана:
  

  =colorfunction(G2,$B$2:$E$12,TRUE)  

  Примечание: в этой формуле G2 — это эталонная ячейка с конкретным цветом фона, который нужно найти; $B$2:$E$12 — диапазон, в котором подсчитываются ячейки того же цвета, что и G2; TRUE указывает на суммирование ячеек с совпадающим цветом.
  

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

Для тех, кто не знаком с программированием, VBA может показаться довольно сложным. Здесь мы представляем мощный инструмент — Kutools for Excel: его функция Подсчет по цвету позволяет легко выполнять вычисления (подсчёт, суммирование, среднее и т.д.) на основе цвета фона всего за несколько кликов. Впечатляет ещё больше: функция Подсчет по цвету выходит за рамки простого цвета фона — она также умеет распознавать и обрабатывать значения по цвету шрифта и при использовании условного форматирования.

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

В диалоговом окне Подсчет по цветуукажите необходимые операции:

1. Выберите Стандартное форматированиеиз раскрывающегося списка Режим цвета;
2. Укажите Фонв раскрывающемся списке Тип статистикии просмотрите статистические результаты для каждого Цвет фона в диалоговом окне;
3. Наконец, нажмите Создать отчет, чтобы экспортировать вычисленные результаты в новую рабочую книгу.

Результат:

Теперь у вас появится новая рабочая книга со статистикой. См. скриншот:

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

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

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

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

• Чтобы подсчитать все ячейки с одинаковым Цвет фона, введите формулу:

  =SUBTOTAL(102, F2:F16)

• Чтобы просуммировать все ячейки с одинаковым Цвет фона, введите формулу:

  =SUBTOTAL(109, F2:F16)

• Примечание: в приведённых выше формулах 102 означает подсчёт числовых значений в отфильтрованном списке с исключением скрытых ячеек; 109 — суммирование значений в отфильтрованном списке с исключением скрытых ячеек; F2:F16 — диапазон, по которому будет выполняться подсчёт или суммирование.
• 

Шаг 2: Отфильтруйте ячейки по определённому цвету

1. Выделите заголовок таблицы и выберите Данные > Фильтр. См. снимок экрана:
   
2. Нажмите значок Фильтр в заголовке столбца Сумма, затем последовательно выберите команды Фильтр по цвету и нужный цвет, по которому будет выполняться подсчёт. См. снимок экрана:
   

Результат:

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

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

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

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

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

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

1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic для приложений.
2. В открывшемся окне нажмите Вставка>Модуль, чтобы создать новый пустой модуль.
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.
  
• Суммирование ячеек по определённому цвету шрифта:
  Скопируйте или введите приведённую ниже формулу в нужную ячейку, чтобы получить результат. Затем перетащите маркер заполнения вниз для получения остальных результатов. См. снимок экрана:
  

  =ProcessByFontColor($B$2:$E$12,G2, "SUM")  

  Примечание: в этой формуле G2 — это эталонная ячейка с конкретным цветом шрифта, который нужно найти; $B$2:$E$12 — диапазон, в котором следует подсчитать сумму ячеек того же цвета, что и в G2.
  

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

Хотите без усилий подсчитывать или суммировать значения ячеек в Excel на основе цвета шрифта? Попробуйте функцию «Подсчёт по цвету» из Kutools для Excel! Благодаря этому умному инструменту подсчёт и суммирование ячеек по конкретному цвету шрифта станет лёгким делом. Узнайте, как Kutools может преобразить вашу работу в Excel.

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

В диалоговом окне Подсчет по цветуукажите необходимые операции:

1. Выберите Стандартное форматированиеиз раскрывающегося списка Режим цвета;
2. Укажите Шрифтв раскрывающемся списке Тип статистикии просмотрите статистические результаты для каждого Цвет шрифта в диалоговом окне;
3. Наконец, нажмите Создать отчет, чтобы экспортировать вычисленные результаты в новую рабочую книгу.

Результат:

Теперь у вас есть Новая рабочая книга, отображающий подробную статистику на основе Цвет шрифта. См. скриншот:

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

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

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

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

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

1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic для приложений.
2. В открывшемся окне нажмите Вставка>Модуль, чтобы создать новый пустой модуль.
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, чтобы запустить его. Появится диалоговое окно. Пожалуйста, выберите диапазон данных, в котором требуется подсчитать и просуммировать ячейки на основе условного форматирования, затем нажмите ОК. См. снимок экрана:
   
2. В следующем диалоговом окне выберите конкретный цвет, по которому нужно выполнить подсчёт и суммирование с помощью условного форматирования, и нажмите кнопку ОК. См. снимок экрана:
   

Результат:

Теперь результат, включающий как количество, так и сумму ячеек с указанным цветом Использовать условное форматирование, будет отображён во всплывающем окне. См. скриншот:

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

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

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

В диалоговом окне Подсчет по цветуукажите необходимые операции:

1. Выберите Использовать условное форматированиеиз раскрывающегося списка Режим цвета;
2. Укажите Фонв раскрывающемся списке Тип статистикии просмотрите статистические результаты для каждого цвета условного форматирования в диалоговом окне;
3. Наконец, нажмите Создать отчет, чтобы экспортировать вычисленные результаты в новую рабочую книгу.

Результат:

Теперь у вас есть Новая рабочая книга, отображающий подробную статистику на основе цвета Использовать условное форматирование. См. скриншот:

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