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

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

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

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

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

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

• С помощью кода VBA
• Используя интеллектуальную функцию – Kutools for 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; НЕПРАВДА используется для подсчета ячеек соответствующего цвета.
  
• Суммируйте ячейки на основе определенного цвета фона:
  Скопируйте или введите приведенную ниже формулу в нужную ячейку для получения результата. Затем перетащите маркер заполнения вниз, чтобы получить другие результаты. Смотрите скриншот:
  

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

  Внимание: В этой формуле G2 — это ссылочная ячейка с определенным цветом фона, который вы хотите сопоставить; $B$2:$E$12 — это диапазон, в котором вы хотите подсчитать количество ячеек цвета G2; ИСТИНА используется для суммирования ячеек соответствующего цвета.
  

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

Тем, кто не знаком с программированием, VBA может показаться довольно сложным. Здесь мы представим мощный инструмент - Kutool для Excel, Его Считать по цвету Функция позволяет легко выполнять вычисления (подсчет, сумму, среднее значение и т. д.) на основе цвета фона всего за несколько кликов. Впечатляюще, Считать по цвету Эта функция выходит за рамки просто цвета фона — она также может различать и рассчитывать на основе цветов шрифта и условного форматирования.

После загрузка и установка Kutools для ExcelСначала выберите диапазон данных, который вы хотите подсчитать, или суммировать ячейки на основе определенного цвета фона. Далее перейдите к Кутулс Плюс , а затем выбрать Считать по цвету.

В Считать по цвету диалоговом окне укажите операции:

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 for Excel's Считать по цвету особенность! С помощью этого интеллектуального инструмента подсчет и суммирование ячеек по определенному цвету шрифта становится проще простого. Узнайте, как Кутулс может изменить ваш опыт работы с Excel.

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

В Считать по цвету диалоговом окне укажите операции:

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

Результат:

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

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

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

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

В Считать по цвету диалоговом окне укажите операции:

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

Результат:

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

Статьи по теме: