Перейти к основному содержанию

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

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

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

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

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

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

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

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

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

Результат:

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

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

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

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

Результат:

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


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

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