Совет: Други јазици се Google-преведени. Можете да ја посетите English верзија на оваа врска.
Логирај Се
x
or
x
x
Регистрирај се
x

or

Како да се бројат и збијат клетките врз основа на бојата на позадина во Excel?

Претпоставувајќи дека имате голем број на ќелии со различни бои во позадина, како што се црвена, зелена, сина и така натаму, но сега треба да се пресмета колку клетки во тој опсег имаат одредена боја на позадината и да ги сумира обоените ќелии со иста одредена боја . Во Excel, не постои директна формула за пресметување на Sum и Count на клетките во боја, тука ќе ве запознаам неколку начини за решавање на овој проблем.

Count и Sum клетки врз основа на специфични бои за пополнување со филтер и SUBTOTAL

Грофот и сума клетки врз основа на специфични бои на пополнување со функцијата GET.CELL

Брои и збирни ќелии врз основа на специфична боја на пополнување со функцијата дефинирана за корисникот

Count и Sum клетки базирани на специфична боја на полнење со функциите Kutools

Count и Sum клетки базирани на специфични бои за пополнување (или условна боја за форматирање) со Kutools за Excel

Еден клик за броење, сума и просечни обоени клетки во Excel

Со одличен Брои по боја карактеристика на Kutools за Excel, можете брзо да ги броите, сумирате и просечните ќелии со одредена боја на боја или боја на фонт со само еден клик во Excel. Освен тоа, оваа функција исто така ќе ги дознае максималните и минните вредности на клетките со боја на полнење или боја на фонт. Комплетна бесплатен пробен период 60-ден!
реклама брои по боја 2


Count и Sum боја обоени клетки од филтер и SUBTOTAL

Претпоставувајќи дека имаме табела за продажба на овошје како што е прикажано на екранот подолу, и ние ќе ги броиме или ќе ги сумираме обоените ќелии во колоната Amount. Во оваа ситуација, можеме да ја филтрираме колоната Amount по боја, а потоа лесно да ги броиме или збиеме филтрираните обоени клетки од функцијата SUBTOTAL лесно во Excel.

1. Изберете празно клетки за да влезете во функцијата SUBTOTAL.

  1. За да ги броите сите клетки со иста боја на позадина, ве молиме внесете ја формулата = SUBTOTAL (102, E2: E20);
  2. За да ги сумираме сите клетки со иста боја на позадина, ве молиме внесете ја формулата = SUBTOTAL (109, E2: E20);


Забелешка: Во обете формули, E2: E20 е колоната Amount која ги содржи обоените клетки, и можете да ги промените колку што ви требаат.

2. Изберете го насловот на табелата и кликнете податоци > филтри. Погледнете го екранот:

3. Кликнете на иконата Filter во насловот ќелија на колоната Износ, и кликнете Филтер по боја и одредената боја ќе се брои со сукцесивно. Погледнете го екранот:

По филтрирањето, двете SUBTOTAL формули ги бројат и сумираат сите филтрирани бои клетки во колоната Amount автоматски. Погледнете го екранот:

Забелешка: Овој метод бара обоени клетки што ќе ги броите или сумите се во истата колона.


Грофот или Сум обоени клетки со GET.CELL функција

Во овој метод, ќе создаде име со опсег со функцијата GET.CELL, ќе го добиете кодот на бојата на ќелиите, а потоа лесно ќе се брои или збие со кодот на бојата во Excel. Ве молиме направете го следното:

1. Кликнете Формула > Дефинирајте име. Погледнете го екранот:

2. Во дијалогот за ново име, направете го како прикажано на екранот подолу:
(1) Внесете име во полето Име;
(2) Внесете ја формулата = GET.CELL (38, Sheet4! $ E2) во полето Refers to (се напомене: во формулата, 38 значи враќање на ќелискиот код, и Sheet4! $ E2 е првата ќелија во колоната Amount освен заглавието на колоната што треба да се промени врз основа на вашите податоци во табелата.)
(3) Кликнете на OK копче.

3. Сега додајте нова колона колона десно до оригиналната табела. Следна тип на формула = NumColor , и повлечете ја рачката за автоматско полнење за да ја примените формулата со другите ќелии во колоната Боја. Погледнете го екранот:
Забелешка: Во формулата, NumColor е именуваниот опсег што го назначивме во првите 2 чекори. Треба да го промените до одреденото име што го поставивте.

Сега кодот на бојата на секоја ќелија во колоната Amount се враќа во Column Column. Погледнете го екранот:

4. Копирајте ја и наведете ја бојата за пополнување во празен опсег на активниот работен лист и напишете формули до неа како што е прикажано подолу прикажаното на екранот:
A. За да ги броите клетките со боја, ве молиме внесете ја формулата = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
За да ги избришете клетките со боја, ве молиме внесете ја формулата = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Забелешка: Во двете формули, $ F $ 2: $ F $ 20 е колона Боја, NumColor е наведениот именски опсег, $ E $ 2: $ E $ 20 е износ колона, и можете да ги промените колку што ви треба.

Сега ќе видите дека ќелиите во колоната Amount ќе се бројат и ќе се пресметаат со нивните бои за пополнување.


Брои и збирни ќелии врз основа на специфична боја на пополнување со функцијата дефинирана за корисникот


Сметајќи дека обоените клетки се расфрлаат во опсег како што е прикажано подолу прикажаното екранот, и двата горенаведени методи не можат да ги бројат или сумираат обоените клетки. Еве, овој метод ќе воведе VBA за решавање на проблемот.

1. Држете го ALT + F11 клучеви, и го отвора Microsoft Visual Basic за апликации прозорец.

2. Кликнете Вметнете > Модули, и внесете го следниов код во Прозорецот за модули.

VBA: Брои и збирни клетки врз основа на бојата на позадина:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

3. Потоа зачувајте го кодот и применувајте ја следнава формула:
A. Избројте ги обоените клетки: = бојафункција (A, B: C, FALSE)
Б. Сумира обоени клетки: = боифункција (A, B: C, ИСТИНА)

Забелешка: Во горните формули, A е келијата со одредена боја на позадина која сакате да ја пресметате броењето и сумата, и Б: Ц е опсегот на ќелии каде што сакате да го пресметате бројот и сумата.

4. На пример, следете ја следнава слика на сликата: внесете ја формулата= бојафункција (A1, A1: D11, FALSE) да ги брои жолтите клетки. И користете ја формулата = бојафункција (A1, A1: D11, ИСТИНА) да ги сумира жолтите клетки. Погледнете го екранот:

5. Ако сакате да ги броите и сумите другите обоени клетки, повторете го чекорот 4. Потоа ќе ги добиете следниве резултати:


Count и Sum клетки базирани на специфична боја на полнење со функциите Kutools

Kutools за Excel, исто така, поддржува некои корисни функции за да им помогне на Excel корисниците да направат специјални пресметки, вели брои по бојата на позадината на клетката, збирот по боја на фонт итн.

Kutools за Excel - Вклучува повеќе од 300 практични алатки на Excel. Целосна функција бесплатен пробен период 60-ден, без кредитна картичка се бара! Земете го сега!

1. Изберете ја празната ќелија која ги поставувате резултатите од броењето и кликнете Kutools > Функции на Kutools > Статистика и математика > COUNTBYCELLCOLOR. Погледнете го екранот:

2. Во дијалогот Функции аргументи, ве молиме наведете опсег во кој ќе броите обоени клетки во рамките на Суд кутија, изберете ја ќелијата која е исполнета со одредената боја на позадина во Color_index_nr поле, и кликнете на OK копче. Погледнете го екранот:

Забелешки:
(1) Исто така можете да ја напишете одредената функција Kutools = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) во празно ќелија или формула бар директно да се добие броење резултати;
(2) Кликнете Kutools > Функции на Kutools > Статистика и математика > SUMBYCELLCOLOR или тип = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) во празно клетка директно до збир на клетки врз основа на одредената боја на позадината.
применуваат COUNTBYCELLCOLOR и SUMBYCELLCOLOR функции за секоја боја на позадината одделно, и ќе ги добиете резултатите како што е прикажано на екранот подолу:

Функции на Kutools содржи голем број на вградени функции за да им помогне на корисниците на Excel лесно да пресметаат, вклучувајќи Број / сума / Просечна видливи клетки, Грофот / сума по клеточна боја, Грофот / сума по боја на фонт, Број карактери, Грофот по задебелен фонт, Итн Имајте бесплатен пробен период!


Count и Sum клетки врз основа на специфични бои за пополнување со Kutools за Excel

Со горната функција дефинирана за корисници, треба да ја внесете формулата еден по еден, ако има многу различни бои, овој метод ќе биде досаден и одзема многу време. Но, ако имаш Kutools за Excel"S Брои по боја алатка, можете брзо да генерирате извештај за обоени клетки. Вие не само што може да сметате и да ги сумирате обоените клетки, туку исто така може да ги добиете просечните, макс и мин вредностите на обоениот опсег.

Kutools за Excel - Вклучува повеќе од 300 практични алатки на Excel. Целосна функција бесплатен пробен период 60-ден, без кредитна картичка се бара! Земете го сега!

1. Изберете го опсегот што сакате да го користите и кликнете Kutools More > Брои по боја, видете го екранот:

2. И во Брои по боја дијалог-кутија, ве молиме да направите како што е прикажано на екранот подолу
(1) Одберете Стандардно форматирање од Боја метод паѓачка листа;
(2) Одберете Позадина од Грофот тип паѓачката листа.
(3) Кликнете на копчето Генерирај отчет.

Забелешка: За да ги броите и измените обоените клетки со одредена условна боја за форматирање, ве молиме изберете Условно форматирање од Боја метод паѓачката листа во дијалогот погоре или изберете Стандардно и условено форматирање од паѓачката листа да ги брои сите клетки полни со одредената боја.

Сега ќе добиете нова работна книга со статистиката. Погледнете го екранот:

на Брои по боја функција ги пресметува (брои, суми, просечни, макс, итн.) клетки по боја на боја или боја на фонт. Имајте бесплатен пробен период!


Поврзани написи:

Како да се бројат / збираат ќелии врз основа на боите на фонт во Excel?


Демо: Грофот и збирните клетки врз основа на позадина, условно форматирање боја:

Во ова видео, на Kutools јазичето и Kutools More јазичето се додава од Kutools за Excel. Ако е потребно, ве молиме кликнете овде да имате бесплатен пробен период без ограничување на 60-ден!



Препорачани алатки за продуктивност

Канцеларија јазиче

златна ѕвезда1 Донеси практични табулатори за Excel и друг канцелариски софтвер, исто како Хром, Firefox и нов Internet Explorer.

Kutools за Excel

златна ѕвезда1 Неверојатни! Зголемете ја вашата продуктивност во 5 минути. Не треба посебни вештини, освен два часа секој ден!

златна ѕвезда1 300 Нови функции за Excel, Направете Excel многу лесен и моќен:

  • Спој ќелии / редови / колони без губење на податоци.
  • Комбинирајте и консолидирајте повеќе листови и работни книги.
  • Споредете ги опсезите, копирајте ги повеќекратните граници, претворете го текстот во датум, единица и конверзија на валути.
  • Сметајте по бои, подредени страни, напредна сортирачка и супер филтер,
  • Повеќе Избери / Вметни / Избриши / Текст / Формат / Линк / Коментар / Учебник / Алатки за работни листови ...

Екран за Kutools за Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mori · 1 months ago
    I copied and paste but calculation result is "0" why?? I am using Office 2016.
  • To post as a guest, your comment is unpublished.
    Troy Havard · 2 months ago
    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.
    • To post as a guest, your comment is unpublished.
      Jku · 1 months ago
      #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
    • To post as a guest, your comment is unpublished.
      Troy Havard · 2 months ago
      I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
  • To post as a guest, your comment is unpublished.
    Karolin · 3 months ago
    works fine, except it does not update the value in the totals when i change a cell to a diffrent colour. i have to click in the totals cell again to trigger a recount. Any Ideas how to fix this?
    • To post as a guest, your comment is unpublished.
      Arnier Hamz · 2 months ago
      Same here, i tried Ctrl+Alt+F9 or Ctrl+Shift+Alt+F9 to recalculate all formula and it works
  • To post as a guest, your comment is unpublished.
    Aaron · 4 months ago
    This doesn't work at all. It's total BS.
  • To post as a guest, your comment is unpublished.
    Schmingy · 6 months ago
    I was experiencing issues when attempting to use this function as-is to count/sum the lighter (pastel?) colors, at least in Excel 2016. Some debugging led me to realize that the range of "ColorIndex" values is quite limited, and the function above fails to distinguish between light yellow and light orange, reading both as ColorIndex = 19.

    Using the "Color" attribute, instead (as in "lCol = rColor.Interior.Color" and the two instances of "If rCell.Interior.Color = lCol Then", per the sample code above), resolved the issue.