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

Преброяване и сумиране на клетки въз основа на цвета на фона

• Чрез използване на дефинирана от потребителя функция
• С помощта на мощна функция – Kutools за Excel
• Чрез използване на филтър и SUBTOTAL

Преброяване и сумиране на клетки въз основа на цвета на шрифта

• Чрез използване на дефинирана от потребителя функция
• С помощта на лесна функция – 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 може да изглежда доста сложно. Тук ще представим мощен инструмент - Kutool за Excel, неговата Брой по цвят функция ви позволява лесно да изчислявате (броене, сума, средна стойност и т.н.) въз основа на цвета на фона само с няколко кликвания. Впечатляващо, Брой по цвят функцията надхвърля просто цветовете на фона – тя може също да диференцира и изчислява въз основа на цветовете на шрифта и условното форматиране.

След изтегляне и инсталиране на Kutools за Excel, първо изберете диапазона от данни, който искате да преброите или да сумирате клетки въз основа на конкретен цвят на фона. След това отворете Kutools плюс И изберете Брой по цвят.

в Брой по цвят диалогов прозорец, моля, посочете операциите:

1. Изберете Стандартно форматиране от Цветен метод падащ списък;
2. Посочете История от Тип брой падащ списък и можете да прегледате статистическите резултати за всеки цвят на фона в диалоговия прозорец;
3. Най-накрая щракнете Генериране на отчет за експортиране на изчислените резултати в нова работна книга.

Резултат:

Сега ще получите нова работна книга със статистиката. Вижте екранна снимка:

Преброяване и сумиране на клетки по цвят на фона с функцията Filter и SUBTOTAL

Да предположим, че имаме таблица с продажби на плодове, както е показано на екранната снимка по-долу, и ще преброим или сумираме цветните клетки в Количество колона.

Стъпка 1: Приложете функцията SUBTOTAL

Изберете празни клетки, за да въведете функцията SUBTOTAL.

• За да преброите всички клетки с еднакъв цвят на фона, моля, въведете формулата:

  =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's Брой по цвят особеност! С този интелигентен инструмент преброяването и сумирането на клетките по определен цвят на шрифта става лесно. Открийте как Kutools може да трансформира вашето изживяване в Excel.

След изтегляне и инсталиране на Kutools за Excel, първо изберете диапазона от данни, който искате да преброите или да сумирате клетки въз основа на конкретен цвят на шрифта. След това щракнете Kutools плюс > Брой по цвят за да отворите Брой по цвят диалогов прозорец.

в Брой по цвят диалогов прозорец, моля, посочете операциите:

1. Изберете Стандартно форматиране от Цветен метод падащ списък;
2. Посочете Font от Тип брой падащ списък и можете да прегледате статистическите резултати за всеки цвят на шрифта в диалоговия прозорец;
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 за Excel е вашето решение. Това е Брой по цвят може да реши тази задача само с няколко щраквания. Потопете се, за да откриете ефективността и прецизността, които Kutools може да донесе на вашия работен процес.

След изтегляне и инсталиране на Kutools за Excel, първо изберете диапазона от данни, който искате да преброите или да сумирате клетки въз основа на конкретен цвят на условно форматиране. След това щракнете Kutools плюс > Брой по цвят за да отворите Брой по цвят диалогов прозорец.

в Брой по цвят диалогов прозорец, моля, посочете операциите:

1. Изберете Условно форматиране от Цветен метод падащ списък;
2. Посочете История от Тип брой падащ списък и можете да прегледате статистическите резултати за всеки цвят за форматиране на условие в диалоговия прозорец;
3. Най-накрая щракнете Генериране на отчет за експортиране на изчислените резултати в нова работна книга.

Резултат:

Сега имате нова работна книга, показваща подробна статистика въз основа на цвета на условното форматиране. Вижте екранна снимка:

Свързани членове: