Поради Excel: підрахунок/сума клітинок за кольором (фон, шрифт, умовне форматування)
У щоденних завданнях кольорове маркування є популярним методом для швидкого розрізнення та виділення важливих даних. Але як підрахувати або підсумувати дані клітинки на основі певного кольору (колір заливки, колір шрифту, умовне форматування)? За замовчуванням Excel не пропонує пряму функцію підрахунку або сумування за кольором. Тим не менш, за допомогою деяких хитрощів і непрямих методів ми все ще можемо досягти цього. У цій статті ми розглянемо, як підраховувати або підсумовувати дані за кольором.
Підрахувати та підсумувати комірки на основі кольору тла
- За допомогою функції, визначеної користувачем
- Використовуючи потужну функцію – Kutools для Excel
- За допомогою фільтра та SUBTOTAL
Підрахунок і підсумовування клітинок на основі кольору шрифту
Підрахунок і підсумовування клітинок на основі кольору умовного форматування
Підрахувати та підсумувати комірки на основі кольору тла
Наприклад, якщо у вас є діапазон даних, де значення заповнені різними кольорами фону, як показано на знімку екрана нижче. Щоб підрахувати або підсумувати клітинки на основі певного кольору, Excel не пропонує пряму функцію підрахунку або підсумовування клітинок на основі кольору фону. Однак, проявивши трохи винахідливості та використовуючи кілька зручних прийомів, ви можете виконати це завдання. Давайте розглянемо деякі корисні методи в цьому розділі.
Підраховуйте та підсумовуйте клітинки за кольором фону за допомогою функції, визначеної користувачем
Тут ми покажемо вам, як створити та використовувати таку визначену користувачем функцію для вирішення цього завдання в Excel. Виконайте наведені нижче дії.
Крок 1. Відкрийте редактор модуля VBA та скопіюйте код
- прес Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.
- У вікні, що відкрилося, натисніть Insert > Модулі щоб створити новий порожній модуль.
- Потім скопіюйте та вставте наведений нижче код у порожній модуль.
Код 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 І вибирай Підрахувати за кольором.
У Підрахувати за кольором у діалоговому вікні вкажіть операції:
- Select Стандартне форматування від Колірний метод випадаючий список;
- Вказувати фон від Тип підрахунку спадний список, і ви можете попередньо переглянути статистичні результати для кожного кольору фону в діалоговому вікні;
- Нарешті клацніть Створити звіт щоб експортувати обчислені результати до нової книги.
Результат:
Тепер ви отримаєте нову книгу зі статистикою. Дивіться знімок екрана:
- Команда Підрахувати за кольором функція також підтримує підрахунок і підсумовування комірок на основі стандартного кольору шрифту, фону або кольору шрифту з умовного форматування, а також комбінації кольорів заливки й умовного форматування.
- Цікавить ця функція, будь ласка натисніть, щоб завантажити, щоб отримати безкоштовну пробну версію протягом 30 днів.
Підрахуйте та підсумуйте клітинки за кольором фону за допомогою фільтра та функції SUBTOTAL
Припустімо, що у нас є таблиця продажів фруктів, як показано на зображенні нижче, і ми підрахуємо або підсумуємо кольорові клітинки в сума колонка.
Крок 1. Застосуйте функцію SUBTOTAL
Виберіть порожні клітинки, щоб ввести функцію SUBTOTAL.
- Щоб підрахувати всі клітинки з однаковим кольором фону, введіть формулу:
=SUBTOTAL(102, F2:F16)
- Щоб підсумувати всі клітинки з однаковим кольором фону, введіть формулу;
=SUBTOTAL(109, F2:F16)
- примітки: у наведених вище формулах 102 представляє для підрахунку числових значень у відфільтрованому списку, виключаючи приховані клітинки; 109 представляє для підсумовування значень у відфільтрованому списку, виключаючи приховані клітинки; F2: F16 це діапазон, у якому обчислюватиметься кількість або сума.
Крок 2. Фільтруйте клітинки за певним кольором
- Виберіть заголовок таблиці та натисніть дані > фільтр. Дивіться знімок екрана:
- Натисніть фільтр іконка у комірці заголовка сума і натисніть Фільтр за кольором і вказаний колір ви будете підраховувати послідовно. Дивіться знімок екрана:
Результат:
Після фільтрації формули SUBTOTAL автоматично підраховують і підсумовують кольорові клітинки в сума стовпець. Дивіться знімок екрана:
Підрахунок і підсумовування клітинок на основі кольору шрифту
Хочете підрахувати або підсумувати клітинки на основі кольору їх шрифту в Excel? Припустимо, у вас є дані, як на наведеному скріншоті, з комірками, що містять тексти червоного, синього, оранжевого та чорного кольорів. Excel не робить це легко за замовчуванням. Але не переживай! У цьому розділі ми покажемо вам кілька простих трюків, як це зробити.
Підраховуйте та підсумовуйте клітинки на основі кольору шрифту за допомогою функції, визначеної користувачем
Щоб підрахувати та підсумувати клітинки з певними кольорами шрифту, наступна функція, визначена користувачем, може допомогти вам вирішити це завдання. Виконайте наведені нижче дії.
Крок 1. Відкрийте редактор модуля VBA та скопіюйте код
- прес Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.
- У вікні, що відкрилося, натисніть Insert > Модулі щоб створити новий порожній модуль.
- Потім скопіюйте та вставте наведений нижче код у порожній модуль.
Код 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 > Підрахувати за кольором відкрити Підрахувати за кольором діалогове вікно.
У Підрахувати за кольором у діалоговому вікні вкажіть операції:
- Select Стандартне форматування від Колірний метод випадаючий список;
- Вказувати шрифт від Тип підрахунку спадний список, і ви можете попередньо переглянути статистичні результати для кожного кольору шрифту в діалоговому вікні;
- Нарешті клацніть Створити звіт щоб експортувати обчислені результати до нової книги.
Результат:
Тепер у вас є нова робоча книга, яка відображає детальну статистику на основі кольору шрифту. Перегляньте скріншот:
Підрахунок і підсумовування клітинок на основі кольору умовного форматування
В Excel ви можете зазвичай використовувати умовне форматування, щоб застосувати певний колір до клітинок, які відповідають певним критеріям, що робить візуалізацію даних інтуїтивно зрозумілою. Але що, якщо вам потрібно порахувати або підсумувати ці спеціально відформатовані клітинки? Хоча Excel не пропонує прямого способу для цього, ось способи обійти це обмеження.
Підрахунок і підсумовування умовно відформатованих клітинок за допомогою коду VBA
Підрахувати та підсумовувати умовно відформатовані клітинки в Excel непросто за допомогою вбудованих функцій. Однак ви можете виконати це завдання за допомогою коду VBA. Давайте розглянемо, як для цього можна використовувати VBA:
Крок 1. Відкрийте редактор модуля VBA та скопіюйте код
- прес Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.
- У вікні, що відкрилося, натисніть Insert > Модулі щоб створити новий порожній модуль.
- Потім скопіюйте та вставте наведений нижче код у порожній модуль.
Код 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
- Вставивши код, натисніть F5 щоб запустити цей код, з’явиться вікно підказки, будь ласка, виберіть діапазон даних, у якому потрібно підрахувати та підсумувати клітинки на основі умовного форматування. Потім натисніть OK, Див. Скріншот:
- В іншому вікні підказки виберіть певний колір умовного форматування, який потрібно підрахувати та підсумувати, і натисніть OK , див. знімок екрана:
Результат:
Тепер результат, який включає як кількість, так і суму комірок із вказаним кольором умовного форматування, буде відображено у спливаючому вікні. Перегляньте скріншот:
Рахуйте та підсумовуйте умовно відформатовані клітинки за допомогою розумної функції
Якщо ви шукаєте інші швидкі та прості методи підрахунку та підсумовування умовно відформатованих клітинок, Kutools для Excel це ваше найкраще рішення. Його Підрахувати за кольором функція може вирішити це завдання всього за кілька кліків. Зануртеся, щоб дізнатися про ефективність і точність, яку Kutools може привнести у ваш робочий процес.
після завантаження та встановлення Kutools для Excel, спочатку виберіть діапазон даних, який потрібно підрахувати або підсумувати клітинки на основі певного кольору умовного форматування. Потім натисніть Kutools Plus > Підрахувати за кольором відкрити Підрахувати за кольором діалогове вікно.
У Підрахувати за кольором у діалоговому вікні вкажіть операції:
- Select Умовне форматування від Колірний метод випадаючий список;
- Вказувати фон від Тип підрахунку спадний список, і ви можете попередньо переглянути статистичні результати для кожного кольору форматування умови в діалоговому вікні;
- Нарешті клацніть Створити звіт щоб експортувати обчислені результати до нової книги.
Результат:
Тепер у вас є нова книга, яка відображає детальну статистику на основі кольору умовного форматування. Перегляньте скріншот:
Статті по темі:
- Якщо колір шрифту червоний, повертається певний текст
- Як ви можете повернути певний текст, якщо колір шрифту червоний в іншій клітинці, як показано на знімку екрана нижче? У цій статті я розповім про деякі прийоми для виконання деяких операцій на основі тексту червоного шрифту в Excel.
- Фільтруйте дані за кількома кольорами
- Зазвичай в Excel можна швидко фільтрувати рядки лише одним кольором, але чи замислювались ви коли-небудь про фільтрацію рядків з кількома кольорами одночасно? У цій статті я розповім про швидкий трюк для вирішення цієї проблеми.
- Додайте колір до випадаючого списку
- Створення розкривного списку в Excel може вам дуже допомогти, і іноді вам потрібно позначити колірним кодом значення розкривного списку залежно від відповідного вибраного. Наприклад, я створив розкривний список назв фруктів, коли я вибираю Яблуко, мені потрібно, щоб клітинка автоматично забарвлювалася червоним кольором, а коли я вибираю Оранжевий, клітинка може бути забарвлена помаранчевим.
- Зафарбуйте чергові рядки для об’єднаних комірок
- Це дуже корисно форматувати альтернативні рядки з іншим кольором у великих даних, щоб ми могли сканувати дані, але іноді у ваших даних можуть бути об’єднані клітинки. Щоб по черзі виділяти рядки іншим кольором для об’єднаних комірок, як показано на знімку екрана нижче, як можна вирішити цю проблему в Excel?
Найкращі інструменти продуктивності офісу
Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
Зміст
- Відео
- Підрахувати та підсумувати комірки на основі кольору тла
- За допомогою функції, визначеної користувачем
- Використовуючи потужну функцію – Kutools для Excel
- За допомогою фільтра та SUBTOTAL
- Підрахунок і підсумовування клітинок на основі кольору шрифту
- За допомогою функції, визначеної користувачем
- Використовуючи просту функцію – Kutools для Excel
- Підрахунок і підсумовування клітинок на основі кольору умовного форматування
- За допомогою коду VBA
- Використовуючи розумну функцію – Kutools для Excel
- Статті по темі
- Найкращі інструменти для підвищення продуктивності офісу
- Коментарі