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

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

• За допомогою функції, визначеної користувачем
• Використовуючи потужну функцію – Kutools для Excel
• За допомогою фільтра та SUBTOTAL

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

• За допомогою функції, визначеної користувачем
• Використовуючи просту функцію – Kutools для Excel

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

• За допомогою коду VBA
• Використовуючи розумну функцію – Kutools для 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. Нарешті клацніть Створити звіт щоб експортувати обчислені результати до нової книги.

Результат:

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

Підрахуйте та підсумуйте клітинки за кольором фону за допомогою фільтра та функції 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. Нарешті клацніть Створити звіт щоб експортувати обчислені результати до нової книги.

Результат:

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

Статті по темі: