Печыва дапамагчы нам даставіць нашы паслугі. Карыстаючыся нашымі паслугамі, Вы згаджаецеся на выкарыстанне намі печыва.
Рада: Іншыя мовы Google-Перакладзеныя. Вы можаце наведаць English версія гэтай спасылкі.
Увайсці
x
or
x
x
рэгістрацыя
x

or

Як палічыць і сума вочак на аснове колеру фону ў Excel?

Выказаўшы здагадку ў вас ёсць дыяпазон вочак з розным колерам фону, такія як чырвоныя, зялёны, сінія і гэтак далей, але цяпер вам трэба падлічыць, колькі вочак у гэтым дыяпазоне маюць пэўны колер фону і прасумаваць каляровыя ячэйкі з тым жа вызначаным колерам , У Excel, няма прамой формулы для разліку сумы і колькасці каляровых вочак, тут я прадстаўлю вам некалькі спосабаў вырашэння гэтай праблемы.

Колькасць і Сума суполкі на аснове пэўнага колеру залівання з дапамогай фільтра і субтотальных

Колькасць і Сума вочак на аснове пэўнага колеру залівання з дапамогай функцыі GET.CELL

Граф і сума элементаў на аснове пэўнага колеру залівання з User Defined Function

Колькасць і Сума суполкі на аснове пэўнага колеру залівання (або ўмоўнае фарматаванне колеру) з Kutools для Excel

Адзін пстрычка, каб палічыць, суму, і сярэднія каляровыя вочка ў Excel

З выдатным Граф па колеры асаблівасць Kutools для ExcelВы можаце хутка падлічыць, суму, і сярэднія ячэйкі па зададзеным колеры залівання або колеры шрыфта толькі з адным пстрычкай мышы ў Excel. Акрамя таго, гэтая функцыя будзе таксама даведацца максімальныя і мінімальныя значэння вочак колеру залівання ці колерам шрыфта. Поўнафункцыянальнае Free Trial 60 дзень!
Колькасць аб'яў па колеры 2


Колькасць і сума каляровых клетак з дапамогай фільтра і субтотальных

Выказаўшы здагадку у нас ёсць табліца продажаў садавіны, як паказана ніжэй скрыншоце паказана на малюнку, і мы будзем разлічваць або сумаваць каляровыя вочкі ў слупку Сума. У гэтай сітуацыі мы можам адфільтраваным слупок Сумы па колеры, а затым падлічыць або суму фільтруюцца каляровыя клеткі з дапамогай функцыі субтотальных лёгка ў Excel.

1, Выберыце пустыя вочкі для ўводу функцыі субтотальных.

  1. Падлічыць усе вочкі з аднолькавым колерам фону, калі ласка, увядзіце формулу = РАЗАМ (102, E2: E20);
  2. Сумуючы усе вочкі з аднолькавым колерам фону, калі ласка, увядзіце формулу = РАЗАМ (109, E2: E20);


НататкаУ абедзвюх формулах E2: E20 з'яўляецца слупок Сума, якая змяшчае каляровыя клеткі, і вы можаце змяніць іх, як вам трэба.

2, Выберыце загаловак табліцы і націсніце Дата > фільтры, Глядзіце скрыншот:

3, Націсніце на значок фільтра у вочку загалоўку слупка Сума і націсніце Фільтр па колеры і названы колер вы будзеце лічыць паслядоўным. Глядзіце скрыншот:

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

НататкаГэты метад патрабуе каляровыя клеткі вы будзеце разлічваць або сума знаходзяцца ў адной і той жа калонцы.


Граф або Сумаваць каляровыя клеткі ад функцыі GET.CELL

У гэтым метадзе мы створым найменны дыяпазон з функцыяй GET.CELL, атрымаць код колеру вочак, а затым падлічыць або сумаваць па каляроваму коду лёгка ў Excel. Калі ласка, зрабіце наступнае:

1, націсніце формулы > вызначыць імя, Глядзіце скрыншот:

2, У дыялогавым акне New Name, калі ласка, як паказана ніжэй скрыншоце паказана ніжэй:
(1) Увядзіце імя ў поле Імя;
(2) Увядзіце формулу = GET.CELL (38, Sheet4! $ E2) у Пазначае поле (увагу: У формуле, 38 азначае вяртаць код ячэйкі, і Sheet4! $ E2 гэта першая вочка ў Стоўбцах Сума, за выключэннем загалоўку слупка, які трэба змяніць на аснове дадзеных табліцы.)
(3) Націсніце OK кнопкі.

3, Зараз дадайце новы слупок Color права на зыходную табліцу. Наступная формула тыпу = NumColor І перацягнуць ручку аўтазапаўнення прымяніць формулу ў іншыя вочкі ў слупку Color. Глядзіце скрыншот:
Нататка: У формуле, NumColor гэта найменны дыяпазон мы паказалі на першых этапах 2. Вам трэба змяніць яго з паказаным імем, якое вы ўстанавілі.

Цяпер код колеру кожнага вочка ў Стоўбцах Сума вяртаецца ў калонцы Color. Глядзіце скрыншот:

4, Капіраванне і пералічваюць колер залівання ва ўпор ў актыўным аркушы, і тыпу формулы побач з ім, як паказана ніжэй скрыншоце паказана:
A. Для падліку клетак па колеры, калі ласка, увядзіце формулу = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Абагульняючы клеткі па колеры, калі ласка, увядзіце формулу = СУММЕСЛИ ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

НататкаУ абедзвюх формулах, $ F $ 2: $ F $ 20 гэта слупок Колер, NumColor зададзены найменны дыяпазон, $ E $ 2: $ E $ 20 гэта сума калонкі, і вы можаце змяніць іх, як вам трэба.

Цяпер вы ўбачыце вочкі ў слупку Сума падлічваюцца і сума іх запаўнення кветак.


Граф і сума элементаў на аснове пэўнага колеру залівання з User Defined Function


Выказаўшы здагадку каляровыя клеткі рассейваюцца ў дыяпазоне як ніжэй скрыншоце паказана на малюнку, абодва гэтыя метады не могуць разлічваць ці сумаваць каляровыя клеткі. Тут гэты метад будзе ўвесці VBA вырашыць гэтую праблему.

1, утрымліваючы націснутай ALT + F11 ключы, і ён адкрывае Microsoft Visual Basic для прыкладанняў акна.

2, націсніце Уставіць > МодуліІ ўстаўце наступны код у акне модуля.

VBA: Count і сума элементаў на аснове колеру фону:

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. Падлічыце каляровыя клеткі: = Colorfunction (А, У, З, значэнне FALSE)
B. Сума каляровыя клеткі: = Colorfunction (A, B, C, TRUE),

Заўвага: У прыведзеных вышэй формулах, A з'яўляецца ячэйкай з вызначаным колерам фону вы хочаце разлічыць колькасць і суму, і У: З гэта дыяпазон вочак, дзе вы хочаце, каб разлічыць колькасць і суму.

4, Возьмем наступны скрыншот, напрыклад, увесці формулу= Colorfunction (A1, A1: D11, FALSE) разлічваць жоўтыя клеткі. І выкарыстоўваць формулу = Colorfunction (A1, A1: D11, TRUE) прасумаваць жоўтыя клеткі. Глядзіце скрыншот:

5, Калі вы хочаце, каб лічыць і сумаваць іншыя каляровыя клеткі, калі ласка, паўторыце крок 4. Тады вы атрымаеце наступныя вынікі:


Колькасць і Сума вочак на аснове канкрэтнага колеру залівання з Kutools для Excel

З Defined вышэй функцыі карыстальніка, вам неабходна ўвесці формулу адзін на адзін, калі ёсць шмат розных колераў, гэты метад будзе цяжка і адымае шмат часу. Але калі ў вас ёсць Kutools для Excel'S Граф па колеры ўтыліта, вы можаце хутка стварыць справаздачу з каляровых клетак. Вы не толькі можаце разлічваць і сумаваць каляровыя клеткі, але таксама могуць атрымаць сярэднюю, максімум і мінімум значэнняў каляровага дыяпазону.

1, Выберыце дыяпазон, які вы хочаце выкарыстоўваць, і націсніце Kutools падрабязна > Граф па колерыСм скрыншот:

2, І ў Граф па колеры дыялогавае акно, калі ласка, як паказана ніжэй скрыншоце паказана ніжэй:
(1) Выбраць стандартнае фарматаванне ад метад Color выпадальны спіс;
(2) Выбраць Фон ад тып Count выпадальны спіс.
(3) Націсніце кнопку Стварыць справаздачу.

Kutools для Excel - Уключае ў сябе больш за 300 зручных інструментаў Excel. Поўная функцыя бясплатнай пробнай версіі 60 дзень, няма крэдытнай карты патрабуецца! Атрымаць зараз!

Нататка: Для падліку і сумы каляровых вочак па канкрэтным ўмоўнага фарматаванні колеру, калі ласка, абярыце ўмоўнае фарматаванне ад метад Color выпадальны спіс у дыялогавым акне вышэй, або абярыце Стандартныя і Умоўнае фарматаванне з выпадальнага спісу, каб злічыць усе вочкі запоўненыя названым колерам.

Цяпер вы атрымаеце новую кнігу з статыстыкай. Глядзіце скрыншот:


Артыкулы па тэме:

Як падлічыць клеткі / Sum на аснове колеру шрыфта ў Excel?


Дэма: Колькасць і сума элементы на аснове фону, ўмоўнае фарматаванне колеру:

У гэтым відэа Kutools Закладка і Kutools падрабязна ўкладка ёсць Kutools для Excel, Калі трэба, калі ласка, націсніце тут мець 60-дзённую бясплатную пробную версію без абмежаванняў!



Рэкамендаваны інструменты для павышэння прадукцыйнасці

ўкладка Упраўленне

золата star1 Прынясіце зручныя укладкі ў Excel і іншыя офісныя праграмы, гэтак жа, як Chrome, Firefox і новы Internet Explorer.

Kutools для Excel

золата star1 Дзіўна! Павышэнне прадукцыйнасці ў 5 хвілін. Не трэба ніякіх спецыяльных навыкаў, захаваць абодва гадзіны кожны дзень!

золата star1 300 Новыя магчымасці для Excel, зрабіць Excel Шмат Лёгкі і магутны:

  • Зліццё клетак / радкоў / слупкоў без страты дадзеных.
  • Аб'яднанне і аб'яднанне некалькіх лістоў і працоўных кніг.
  • Параўнанне дыяпазонаў, капіраванне некалькіх дыяпазонаў, пераўтварэнне тэксту ў дату, групу і валютнай канверсію.
  • Граф па Colors, пэйджынгавай подытогами, Advanced Сартаванне і супер фільтр,
  • Больш Select / Insert / Delete / Тэкст / Фармат / Спасылка / каментар / Workbooks / Рабочыя лісты Інструменты ...

Здымак экрана 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 · 5 days 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 · 16 days 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 · 2 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 · 1 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 · 3 months ago
    This doesn't work at all. It's total BS.
  • To post as a guest, your comment is unpublished.
    Schmingy · 5 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.