Перейти до основного матеріалу

Поради Excel: підрахунок/сума клітинок за кольором (фон, шрифт, умовне форматування)

У щоденних завданнях кольорове маркування є популярним методом для швидкого розрізнення та виділення важливих даних. Але як підрахувати або підсумувати дані клітинки на основі певного кольору (колір заливки, колір шрифту, умовне форматування)? За замовчуванням Excel не пропонує пряму функцію підрахунку або сумування за кольором. Тим не менш, за допомогою деяких хитрощів і непрямих методів ми все ще можемо досягти цього. У цій статті ми розглянемо, як підраховувати або підсумовувати дані за кольором.

Підрахувати та підсумувати комірки на основі кольору тла

Підрахунок і підсумовування клітинок на основі кольору шрифту

Підрахунок і підсумовування клітинок на основі кольору умовного форматування


Відео: підрахунок і підсумовування клітинок за кольором


Підрахувати та підсумувати комірки на основі кольору тла

Наприклад, якщо у вас є діапазон даних, де значення заповнені різними кольорами фону, як показано на знімку екрана нижче. Щоб підрахувати або підсумувати клітинки на основі певного кольору, Excel не пропонує пряму функцію підрахунку або підсумовування клітинок на основі кольору фону. Однак, проявивши трохи винахідливості та використовуючи кілька зручних прийомів, ви можете виконати це завдання. Давайте розглянемо деякі корисні методи в цьому розділі.


Підраховуйте та підсумовуйте клітинки за кольором фону за допомогою функції, визначеної користувачем

Тут ми покажемо вам, як створити та використовувати таку визначену користувачем функцію для вирішення цього завдання в Excel. Виконайте наведені нижче дії.

Крок 1. Відкрийте редактор модуля VBA та скопіюйте код

  1. прес Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.
  2. У вікні, що відкрилося, натисніть Insert > Модулі щоб створити новий порожній модуль.
  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, спочатку виберіть діапазон даних, який потрібно підрахувати або підсумувати клітинки на основі певного кольору фону. Далі перейдіть до Kutools Plus І вибирай Підрахувати за кольором.

У Підрахувати за кольором у діалоговому вікні вкажіть операції:

  1. Select Стандартне форматування від Колірний метод випадаючий список;
  2. Вказувати фон від Тип підрахунку спадний список, і ви можете попередньо переглянути статистичні результати для кожного кольору фону в діалоговому вікні;
  3. Нарешті клацніть Створити звіт щоб експортувати обчислені результати до нової книги.

Результат:

Тепер ви отримаєте нову книгу зі статистикою. Дивіться знімок екрана:

Tips :
  1. Команда Підрахувати за кольором функція також підтримує підрахунок і підсумовування комірок на основі стандартного кольору шрифту, фону або кольору шрифту з умовного форматування, а також комбінації кольорів заливки й умовного форматування.
  2. Цікавить ця функція, будь ласка натисніть, щоб завантажити, щоб отримати безкоштовну пробну версію протягом 30 днів.

Підрахуйте та підсумуйте клітинки за кольором фону за допомогою фільтра та функції SUBTOTAL

Припустімо, що у нас є таблиця продажів фруктів, як показано на зображенні нижче, і ми підрахуємо або підсумуємо кольорові клітинки в сума колонка.

Крок 1. Застосуйте функцію SUBTOTAL

Виберіть порожні клітинки, щоб ввести функцію SUBTOTAL.

  • Щоб підрахувати всі клітинки з однаковим кольором фону, введіть формулу:
    =SUBTOTAL(102, F2:F16)
  • Щоб підсумувати всі клітинки з однаковим кольором фону, введіть формулу;
    =SUBTOTAL(109, F2:F16)
  • примітки: у наведених вище формулах 102 представляє для підрахунку числових значень у відфільтрованому списку, виключаючи приховані клітинки; 109 представляє для підсумовування значень у відфільтрованому списку, виключаючи приховані клітинки; F2: F16 це діапазон, у якому обчислюватиметься кількість або сума.

Крок 2. Фільтруйте клітинки за певним кольором

  1. Виберіть заголовок таблиці та натисніть дані > фільтр. Дивіться знімок екрана:
  2. Натисніть фільтр іконка  у комірці заголовка сума і натисніть Фільтр за кольором і вказаний колір ви будете підраховувати послідовно. Дивіться знімок екрана:

Результат:

Після фільтрації формули SUBTOTAL автоматично підраховують і підсумовують кольорові клітинки в сума стовпець. Дивіться знімок екрана:

примітки: Цей метод вимагає, щоб кольорові клітинки, які ви порахуєте або підсумували, знаходились в одному стовпці.

Підрахунок і підсумовування клітинок на основі кольору шрифту

Хочете підрахувати або підсумувати клітинки на основі кольору їх шрифту в Excel? Припустимо, у вас є дані, як на наведеному скріншоті, з комірками, що містять тексти червоного, синього, оранжевого та чорного кольорів. Excel не робить це легко за замовчуванням. Але не переживай! У цьому розділі ми покажемо вам кілька простих трюків, як це зробити.


Підраховуйте та підсумовуйте клітинки на основі кольору шрифту за допомогою функції, визначеної користувачем

Щоб підрахувати та підсумувати клітинки з певними кольорами шрифту, наступна функція, визначена користувачем, може допомогти вам вирішити це завдання. Виконайте наведені нижче дії.

Крок 1. Відкрийте редактор модуля VBA та скопіюйте код

  1. прес Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.
  2. У вікні, що відкрилося, натисніть Insert > Модулі щоб створити новий порожній модуль.
  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 Підрахувати за кольором функція! За допомогою цього розумного інструменту підрахунок і підсумовування клітинок за певним кольором шрифту стає легким. Дізнайтеся, як Кутулс може змінити ваш досвід роботи з Excel.

після завантаження та встановлення Kutools для Excel, спочатку виберіть діапазон даних, який потрібно підрахувати або підсумувати клітинки на основі певного кольору шрифту. Потім натисніть Kutools Plus > Підрахувати за кольором відкрити Підрахувати за кольором діалогове вікно.

У Підрахувати за кольором у діалоговому вікні вкажіть операції:

  1. Select Стандартне форматування від Колірний метод випадаючий список;
  2. Вказувати шрифт від Тип підрахунку спадний список, і ви можете попередньо переглянути статистичні результати для кожного кольору шрифту в діалоговому вікні;
  3. Нарешті клацніть Створити звіт щоб експортувати обчислені результати до нової книги.

Результат:

Тепер у вас є нова робоча книга, яка відображає детальну статистику на основі кольору шрифту. Перегляньте скріншот:


Підрахунок і підсумовування клітинок на основі кольору умовного форматування

В Excel ви можете зазвичай використовувати умовне форматування, щоб застосувати певний колір до клітинок, які відповідають певним критеріям, що робить візуалізацію даних інтуїтивно зрозумілою. Але що, якщо вам потрібно порахувати або підсумувати ці спеціально відформатовані клітинки? Хоча Excel не пропонує прямого способу для цього, ось способи обійти це обмеження.


Підрахунок і підсумовування умовно відформатованих клітинок за допомогою коду VBA

Підрахувати та підсумовувати умовно відформатовані клітинки в Excel непросто за допомогою вбудованих функцій. Однак ви можете виконати це завдання за допомогою коду VBA. Давайте розглянемо, як для цього можна використовувати VBA:

Крок 1. Відкрийте редактор модуля VBA та скопіюйте код

  1. прес Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.
  2. У вікні, що відкрилося, натисніть Insert > Модулі щоб створити новий порожній модуль.
  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 Plus > Підрахувати за кольором відкрити Підрахувати за кольором діалогове вікно.

У Підрахувати за кольором у діалоговому вікні вкажіть операції:

  1. Select Умовне форматування від Колірний метод випадаючий список;
  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