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

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

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

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

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

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

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

• Использование кода VBA
• Использование умной функции – Kutools для Excel

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

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

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

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

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

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

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

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

Для тех, кто не знаком с программированием, 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, чтобы экспортировать рассчитанные результаты в новую рабочую книгу.

Результат:

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

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

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

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

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

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

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

Хотите без усилий подсчитывать или суммировать значения ячеек в 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, чтобы экспортировать рассчитанные результаты в новую рабочую книгу.

Результат:

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

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

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

Результат:

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

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

Если вы ищете другие быстрые и простые методы для подсчета и суммирования ячеек с условным форматированием, 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, чтобы экспортировать рассчитанные результаты в новую рабочую книгу.

Результат:

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

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