Съвет: Други езици са преведени от Google. Можете да посетите English версия на тази връзка.
Влизане
x
or
x
x
Регистрирам
x

or

Как да броим и сумираме клетки въз основа на цвета на фона в Excel?

Да предположим, че имате набор от клетки с различни цветове на фона, като червено, зелено, синьо и т.н., но сега трябва да преброите колко клетки в този диапазон имат определен цвят на фона и сумират цветните клетки със същия определен цвят. , В Excel няма пряка формула за изчисляване на сумата и броя на цветните клетки, тук ще ви представя някои начини за решаване на този проблем.

Броят и сумата на клетките въз основа на конкретен цвят на запълване по Filter и SUBTOTAL

Броят и сумата на клетките въз основа на специфичен цвят на запълване чрез GET.CELL функция

Брой и сума клетки въз основа на специфичен цвят на запълване с потребителски дефинирани функции

Броят и сумата на клетките въз основа на специфичен цвят на запълване с Kutools функции

Броят и сумата на клетките въз основа на специфичен цвят на запълване (или цвят с условно форматиране) с Kutools за Excel

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

С отличното Брой по цвят функция на Kutools за Excel, можете бързо да преброите, сумите и средните клетки по зададен цвят или цвят на шрифта само с едно щракване в Excel. Освен това, тази функция ще открие максималните и минималните стойности на клетките по цвета на запълване или цвета на шрифта. Безплатна пробна версия 60-ден!
броя на рекламите по цвят 2


Бройте и сумирайте цветните клетки по филтъра и SUBTOTAL

Да предположим, че имаме таблица с продажбите на плодове, както е показано по-долу на екрана, и ще преброим или съберем цветните клетки в колоната Сума. В тази ситуация можем да филтрираме колоната Amount по цвят и след това да преброим или сумираме цветни клетки от функцията SUBTOTAL лесно в Excel.

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

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


Забележка: И в двете формули E2: E20 е колоната Amount, съдържаща цветните клетки, и можете да ги променяте, както ви е необходимо.

2, Изберете заглавието на таблицата и кликнете върху Дата > филтър, Вижте екранна снимка:

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

След филтрирането и двете формули SUBTOTAL преброяват и сумират всички филтрирани цветни клетки в колоната Amount автоматично. Вижте екранната снимка:

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


Брой или сума цветни клетки по GET.CELL функция

В този метод ще създадем имената с функцията GET.CELL, ще получим цветовия код на клетките и след това ще преброим или обобщим лесно кода на цветовете в Excel. Моля, направете следното:

1. Щракнете формули > Дефиниране на име, Вижте екранна снимка:

2, В диалоговия прозорец „Ново име“ направете следното, както е показано по-долу:
(1) Въведете име в полето Име;
(2) Въведете формулата = GET.CELL (38, Sheet4! $ E2) в полето Отнася се за (бележки: във формулата, 38 означава връщане на кода на клетката, и Sheet4! $ E2 е първата клетка в графата Сума, с изключение на заглавката на колоната, която трябва да промените въз основа на данните в таблицата ви.)
(3) Кликнете върху OK бутон.

3, Сега добавете нова колона за цвят право към оригиналната таблица. След това въведете формулата = NumColor и плъзнете дръжката за автоматично попълване, за да приложите формулата към други клетки в колоната Цвят. Вижте екранната снимка:
Забележка: Във формулата, NumColor е посоченият диапазон, който посочихме в първите стъпки 2. Трябва да го промените на определеното от вас име.

Сега цветният код на всяка клетка в колоната Сума се връща в колона Цвят. Вижте екранната снимка:

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 е колона Сума и можете да ги променяте, когато имате нужда.

Сега ще видите клетките в колоната Сума се преброяват и сумират по цветовете им за запълване.


Брой и сума клетки въз основа на специфичен цвят на запълване с потребителски дефинирани функции


Да предположим, че оцветените клетки разпръскват в диапазона, както е показано по-долу на екрана, и двата по-горе метода не могат да преброят или сумират цветните клетки. Тук този метод ще въведе 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, След това запаметете кода и прилагайте следната формула:
А. Пребройте оцветените клетки: = Colorfunction (А, В: С, невярно)
Б. Обобщава оцветените клетки: = Colorfunction (А, В: С, вярно)

Забележка: В горните формули, A е клетката с определен цвят на фона, който искате да изчислите броя и сумата, и Б: С е диапазонът на клетките, където искате да изчислите броя и сумата.

4, Вземете следния екран, например, въведете формулата= Colorfunction (A1, A1: D11 фалшиво) за да преброите жълтите клетки. И използвайте формулата = Colorfunction (A1, A1: D11, вярно) за да сумирате жълтите клетки. Вижте екранна снимка:

5, Ако искате да броите и сумирате други цветни клетки, моля, повторете стъпката 4. Тогава ще получите следните резултати:


Броят и сумата на клетките въз основа на специфичен цвят на запълване с Kutools функции

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

Kutools за Excel - Включва повече от удобни инструменти на Excel. Пълна безплатна пробна версия 300-ден, не се изисква кредитна карта! Махни го сега!

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Е Брой по цвят полезност, можете бързо да генерирате отчет за цветните клетки. Можете не само да броите и сумирате оцветените клетки, но и да получите средните, максималните и минутните стойности на цветния диапазон.

Kutools за Excel - Включва повече от удобни инструменти на Excel. Пълна безплатна пробна версия 300-ден, не се изисква кредитна карта! Махни го сега!

1, Изберете диапазона, който искате да използвате, и щракнете върху Kutools Още > Брой по цвят, вижте екранната снимка:

2, И в Брой по цвят диалоговия прозорец.
(1) Изберете Стандартен формат от Цветен метод падащ списък;
(2) Изберете Заден план от Тип граф падащ списък.
(3) Щракнете върху бутона Генериране на отчет.

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

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

Най- Брой по цвят функция изчислява (граф, сума, средна стойност, макс. и т.н.) клетки по цвета на фона или цвета на шрифта. Имате безплатен пробен период!


Свързана статия:

Как да брои / суми клетки въз основа на цветовете на шрифта в Excel?


Демо: Брой и сума клетки на базата на фон, условен форматиран цвят:

В това видео Kutools и раздела Kutools Още се добавят от Kutools за Excel, Ако е необходимо, моля, кликнете върху тук да имате безплатен пробен период без ограничение 60!



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

Таб на Office

златна звезда1 Носете удобни раздели в Excel и друг софтуер на Office, също като Chrome, 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 · 1 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 · 1 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.