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

Советы 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. Выделите заголовок таблицы и нажмите кнопку Данные > ФИЛЬТР. Смотрите скриншот:
  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. Наконец, нажмите Создать отчет экспортировать рассчитанные результаты в новую книгу.

Результат:

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


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

  • Если цвет шрифта красный, верните определенный текст.
  • Как вы могли бы вернуть определенный текст, если цвет шрифта красный в другой ячейке, как показано ниже? В этой статье я познакомлю вас с некоторыми приемами выполнения некоторых операций на основе текста красного шрифта в Excel.
  • Фильтрация данных по нескольким цветам
  • Обычно в Excel вы можете быстро фильтровать строки только одним цветом, но задумывались ли вы когда-нибудь о фильтрации строк с несколькими цветами одновременно? В этой статье я расскажу о том, как быстро решить эту проблему.
  • Добавить цвет в раскрывающийся список
  • В Excel создание раскрывающегося списка может вам очень помочь, и иногда вам нужно закодировать значения раскрывающегося списка цветом в зависимости от соответствующего выбранного значения. Например, я создал раскрывающийся список названий фруктов: когда я выбираю «Яблоко», мне нужно, чтобы ячейка автоматически окрашивалась в красный цвет, а когда я выбираю «Оранжевый», ячейка может быть окрашена в оранжевый цвет.
  • Альтернативные цвета строк для объединенных ячеек
  • Очень полезно форматировать чередующиеся строки с другим цветом в больших данных, чтобы мы могли сканировать данные, но иногда в ваших данных могут быть некоторые объединенные ячейки. Чтобы выделить строки поочередно другим цветом для объединенных ячеек, как показано на снимке экрана ниже, как вы могли бы решить эту проблему в Excel?
Comments (237)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
嗨~版主好,我用了VBA 模塊,但是完全沒有動靜,沒有出現顏色儲存格的統計數量,Count欄一片空白~~請問是為什麼呢?跟office版本有關嗎?謝謝
This comment was minimized by the moderator on the site
嗨,MINA,
文章中的VBA代碼,微軟office版本基本上都可以適用,我這代碼可以正常使用。 如果你那邊還用不了,可以上傳你的文件,我們可以幫忙看看哪裡的問題,謝謝!
This comment was minimized by the moderator on the site
I am using =IF(D272>F272,D272-F272,if(F272>D272,F272-D272,"")) formula for subtraction, and I want it will coloured also??
This comment was minimized by the moderator on the site
Hi namrata,
Do you want to fill color for the result of your formula?
So, if the result is D272-F272, you want it, say, red; If the result is F272-D272, you want it, say, green; If blank, blank?
Amanda
This comment was minimized by the moderator on the site
I ran into problems when trying to run the function. Macro errors telling me: No RETURN() or HALT() function found on macro sheet. perhaps somebody could assist here. ThanksPaul
This comment was minimized by the moderator on the site
Hi, How to make a diagram based on the colors in the table? For example, I want to count all the red, green and yellow colors in the cells in a table and make a diagram. How to do this? Please
This comment was minimized by the moderator on the site
Is there a way to count different color backgrounds from conditional formatting? The current code as of 7/14/2020 counts them all as default yellow or not at all.
This comment was minimized by the moderator on the site
Hi Dusty,
You can try the Count by Color feature of Kutools for Excel. This feature will help you quickly calculate (count, sum, average, etc.) cells by cell background color or font color, no matter they are formatted by conditional formatting or solidly format.
This comment was minimized by the moderator on the site
Did anyone find a solution to auto-refresh? I have to manually refresh for it to update. Otherwise, it works great!
This comment was minimized by the moderator on the site
Hi Dennis,
By default, formulas are calculated in Excel until you are turning off the Automatic Formula Calculation. You can enable it by clicking Formulas > Calculation Options > Automatic.
This comment was minimized by the moderator on the site
Anyone have tips on a max by color VBA?
This comment was minimized by the moderator on the site
Hi Natasha,VBA is good but hard to apply. But below methods may solve your work easily too.
Method 1: Use Find & Replace feature to select and statistic the color cells(1) Press Ctrl + H keys to open the Find and Replace dialog, and then enable the Find tab.
(2) In the dialog, click Options to show advanced find options.
(3) Then click Format > Choose Format From Cell, and select one of the specified color cells.
(4) Click Find All. Now all cells with the same fill color are found out and listed at the bottom of the dialog.
(5) Select one of found cells, and press Ctrl + A to select all found cells, so that these cells are selected in the worksheet.
(6) Now you can get the count, average, sum, min, max, etc. of these cells in the task bar.
Note: If a certain statistic result cannot be found one the taskbar, you can right click the task bar, and then tick the specified item to show it.

Method 2: Kutools for Excel
Kutools for Excel supports 30-day free trial. Therefore, you can download it and try its Count by color feature to solve your problem with several clicks only.
This comment was minimized by the moderator on the site
awesome fix! count by color over an entire sheet was just what i was looking for and your VBA code was tighter than others that i have looked at. Works like a charm. Thank you, and again, well done.
This comment was minimized by the moderator on the site
I copied and paste but calculation result is "0" why?? I am using Office 2016.
This comment was minimized by the moderator on the site
I try the same you this command =COUNTBYCELLCOLOR is counting only fill color but is not count by condition formating. Please help to improve code thx.
This comment was minimized by the moderator on the site
I've copied and pasted as stated and have used this formula for over a year but recently saved the workbook to a new name and now the function doesn't work! I can't figure out what the deal is and I'm losing hair and sleep over it! Loading the original workbook, the formula works like a charm but going back to the new one, it doesn't! I've loaded VBA and tried re-creating the function but it doesn't work. Using Office 2019 - any help appreciated.
This comment was minimized by the moderator on the site
#NAME clearly indicates some keyword is not used in 2019, say, Interior.ColorIndex. Try to figure out changing the code from minimum lines to the full by adding one by one or search for the keywords in Excel/VBA in 2019
This comment was minimized by the moderator on the site
I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations