Советы по Excel: Подсчет/суммирование ячеек по цвету (фон, шрифт, условное форматирование)
В повседневных задачах цветовая маркировка является популярным методом для быстрого различения и выделения важных данных. Но как же подсчитать или суммировать данные ячеек на основе определенного цвета (цвет заливки, цвет шрифта, условное форматирование)? По умолчанию Excel не предоставляет прямой функции для подсчета или суммирования по цвету. Тем не менее, с помощью некоторых хитростей и косвенных методов мы все же можем этого добиться. В этой статье мы рассмотрим, как подсчитывать или суммировать данные по цвету.
Подсчет и суммирование ячеек на основе цвета фона
- Использование пользовательской функции
- Использование мощной функции – Kutools для Excel
- Использование фильтра и функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Подсчет и суммирование ячеек на основе цвета шрифта
Подсчет и суммирование ячеек на основе цвета условного форматирования
Подсчет и суммирование ячеек на основе цвета фона
Например, если у вас есть диапазон данных, где значения заполнены разными цветами фона, как показано на скриншоте ниже. Чтобы подсчитать или суммировать ячейки на основе определенного цвета, Excel не предлагает прямой функции для подсчета или суммирования ячеек на основе их цвета фона. Однако, немного изобретательности и несколько удобных техник помогут вам выполнить эту задачу. Давайте рассмотрим некоторые полезные методы в этом разделе.
Подсчет и суммирование ячеек по цвету фона с помощью пользовательской функции
Здесь мы покажем вам, как создать и использовать такую пользовательскую функцию для решения этой задачи в Excel. Пожалуйста, следуйте указанным шагам:
Шаг 1: Откройте редактор модуля VBA и скопируйте код
- Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.
- В открывшемся окне нажмите Insert > Module, чтобы создать новый пустой модуль.
- Затем скопируйте и вставьте приведенный ниже код в пустой модуль.
Код 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; FALSE используется для подсчета ячеек с совпадающим цветом. - Суммирование ячеек на основе конкретного цвета фона:
Скопируйте или введите указанную ниже формулу в нужную ячейку для получения результата. Затем перетащите маркер заполнения вниз, чтобы получить другие результаты. См. скриншот:
=colorfunction(G2,$B$2:$E$12,TRUE)
Примечание: В этой формуле G2 — это ссылочная ячейка с конкретным цветом фона, который вы хотите сопоставить; $B$2:$E$12 — это диапазон, где вы хотите подсчитать количество ячеек цвета G2; TRUE используется для суммирования ячеек с совпадающим цветом.
Подсчет и суммирование ячеек по цвету фона с помощью мощной функции
Для тех, кто не знаком с программированием, VBA может показаться довольно сложным. Здесь мы представляем мощный инструмент - Kutools для Excel, его функция Count by Color позволяет легко выполнять расчеты (подсчет, суммирование, среднее значение и т.д.) на основе цвета фона всего за несколько кликов. Впечатляюще то, что функция Count by Color выходит за рамки простых цветов фона – она также может различать и рассчитывать на основе цвета шрифта и условного форматирования.
После загрузки и установки Kutools для Excel сначала выберите диапазон данных, по которому вы хотите подсчитать или суммировать ячейки на основе определенного цвета фона. Затем перейдите в Kutools Plus и выберите Count by Color.
В диалоговом окне Count by Color укажите операции:
- Выберите Standard formatting из выпадающего списка Color method;
- Укажите Background из выпадающего списка Count type, и вы можете просмотреть статистические результаты для каждого цвета фона в диалоговом окне;
- Наконец, нажмите Generate report, чтобы экспортировать рассчитанные результаты в новую рабочую книгу.
Результат:
Теперь вы получите новую рабочую книгу со статистикой. См. скриншот:
- Функция Count by Color также поддерживает подсчет и суммирование ячеек на основе стандартного цвета шрифта, цвета фона или цвета шрифта из условного форматирования, а также комбинации обоих цветов заливки и условного форматирования.
- Заинтересованы в этой функции? Пожалуйста, нажмите, чтобы скачать бесплатную пробную версию на 30 дней.
Подсчет и суммирование ячеек по цвету фона с помощью фильтра и функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Предположим, у нас есть таблица продаж фруктов, как показано на скриншоте ниже, и мы будем подсчитывать или суммировать цветные ячейки в столбце Сумма .
Шаг 1: Применение функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Выберите пустые ячейки для ввода функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
- Чтобы подсчитать все ячейки с одинаковым цветом фона, введите формулу:
=SUBTOTAL(102, F2:F16)
- Чтобы суммировать все ячейки с одинаковым цветом фона, введите формулу:
=SUBTOTAL(109, F2:F16)
- Примечание: в приведенных выше формулах 102 представляет собой подсчет числовых значений в отфильтрованном списке, исключая скрытые ячейки; 109 представляет собой сумму значений в отфильтрованном списке, исключая скрытые ячейки; F2:F16 — это диапазон, по которому будет выполнен либо подсчет, либо суммирование.
Шаг 2: Фильтрация ячеек на основе конкретного цвета
- Выберите заголовок таблицы и нажмите Данные > Фильтр. См. скриншот:
- Нажмите Фильтр значок
в ячейке заголовка столбца Сумма и нажмите Фильтр по цвету и указанный цвет, по которому вы будете последовательно подсчитывать. См. скриншот:
Результат:
После фильтрации формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ автоматически подсчитывают и суммируют цветные ячейки в столбце Сумма . См. скриншот:
Подсчет и суммирование ячеек на основе цвета шрифта
Хотите подсчитать или суммировать ячейки на основе их цвета шрифта в Excel? Предположим, у вас есть данные, как на приведенном скриншоте, с ячейками, содержащими тексты красного, синего, оранжевого и черного цветов. Excel не делает это простым по умолчанию. Но не волнуйтесь! В этом разделе мы покажем вам несколько простых приемов, чтобы сделать именно это.
Подсчет и суммирование ячеек на основе цвета шрифта с помощью пользовательской функции
Для подсчета и суммирования ячеек с определенными цветами шрифтов следующая пользовательская функция может помочь вам решить эту задачу. Пожалуйста, следуйте указанным шагам:
Шаг 1: Откройте редактор модуля VBA и скопируйте код
- Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.
- В открывшемся окне нажмите Insert > Module, чтобы создать новый пустой модуль.
- Затем скопируйте и вставьте приведенный ниже код в пустой модуль.
Код 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 на основе цвета шрифта? Познакомьтесь с функцией Count by Color из Kutools для Excel! С этим умным инструментом подсчет и суммирование ячеек по определенному цвету шрифта становится проще простого. Узнайте, как Kutools может преобразить ваш опыт работы с Excel.
После загрузки и установки Kutools для Excel сначала выберите диапазон данных, по которому вы хотите подсчитать или суммировать ячейки на основе определенного цвета шрифта. Затем нажмите Kutools Plus > Count by Color, чтобы открыть диалоговое окно Count by Color.
В диалоговом окне Count by Color укажите операции:
- Выберите Standard formatting из выпадающего списка Color method;
- Укажите Font из выпадающего списка Count type, и вы можете просмотреть статистические результаты для каждого цвета шрифта в диалоговом окне;
- Наконец, нажмите Generate report, чтобы экспортировать рассчитанные результаты в новую рабочую книгу.
Результат:
Теперь у вас есть новая рабочая книга, отображающая подробную статистику на основе цвета шрифта. См. скриншот:
Подсчет и суммирование ячеек на основе цвета условного форматирования
В Excel вы часто используете условное форматирование для применения определенного цвета к ячейкам, соответствующим определенным критериям, что делает визуализацию данных интуитивно понятной. Но что, если вам нужно подсчитать или суммировать эти специально отформатированные ячейки? Хотя Excel не предлагает прямого способа для этого, вот способы обойти это ограничение.
Подсчет и суммирование ячеек с условным форматированием с помощью кода VBA
Подсчет и суммирование ячеек с условным форматированием в Excel не является прямолинейным с использованием встроенных функций. Однако вы можете выполнить эту задачу с помощью кода VBA. Давайте рассмотрим, как вы можете использовать VBA для этого:
Шаг 1: Откройте редактор модуля VBA и скопируйте код
- Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications.
- В открывшемся окне нажмите Insert > Module, чтобы создать новый пустой модуль.
- Затем скопируйте и вставьте приведенный ниже код в пустой модуль.
Код 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 — это ваше решение. Его функция Count by Color может решить эту задачу всего за несколько кликов. Погрузитесь в работу, чтобы узнать, какую эффективность и точность Kutools может привнести в ваш рабочий процесс.
После загрузки и установки Kutools для Excel сначала выберите диапазон данных, по которому вы хотите подсчитать или суммировать ячейки на основе определенного цвета условного форматирования. Затем нажмите Kutools Plus > Count by Color, чтобы открыть диалоговое окно Count by Color.
В диалоговом окне Count by Color укажите операции:
- Выберите Conditional formatting из выпадающего списка Color method;
- Укажите Background из выпадающего списка Count type, и вы можете просмотреть статистические результаты для каждого цвета условного форматирования в диалоговом окне;
- Наконец, нажмите Generate report, чтобы экспортировать рассчитанные результаты в новую рабочую книгу.
Результат:
Теперь у вас есть новая рабочая книга, отображающая подробную статистику на основе цвета условного форматирования. См. скриншот:
Связанные статьи:
- Если цвет шрифта красный, то вернуть определенный текст
- Как можно вернуть определенный текст, если цвет шрифта красный в другой ячейке, как показано на скриншоте ниже? В этой статье я представлю несколько приемов для выполнения некоторых операций на основе красного текста шрифта в Excel.
- Фильтрация данных по нескольким цветам
- Обычно в Excel вы можете быстро фильтровать строки только с одним цветом, но задумывались ли вы когда-нибудь о фильтрации строк с несколькими цветами одновременно? В этой статье я расскажу о быстром трюке, который поможет вам справиться с этой проблемой.
- Добавление цвета к выпадающему списку
- В Excel создание выпадающего списка может помочь вам во многом, и иногда вам нужно закодировать цвета значений выпадающего списка в зависимости от выбранного. Например, я создал выпадающий список названий фруктов, и когда я выбираю Яблоко, мне нужно, чтобы ячейка автоматически окрашивалась в красный цвет, а когда я выбираю Апельсин, ячейка окрашивается в оранжевый цвет.
- Цветовые альтернативные строки для объединенных ячеек
- Очень полезно форматировать альтернативные строки разным цветом в больших данных для их сканирования, но иногда в ваших данных могут быть объединенные ячейки. Чтобы выделить строки поочередно разным цветом для объединенных ячеек, как показано на скриншоте ниже, как можно решить эту проблему в Excel?
Лучшие инструменты для повышения продуктивности работы с Office
Ускорьте работу в Excel с Kutools для Excel и ощутите новую степень эффективности. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени. Нажмите здесь, чтобы выбрать нужную вам функцию...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите редактирование и чтение с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна, а не в отдельных окнах.
- Увеличьте свою продуктивность на50% и сократите сотни лишних кликов мышью каждый день!
Содержание
- Видео
- Подсчет и суммирование ячеек на основе цвета фона
- Использование пользовательской функции
- Использование мощной функции – Kutools для Excel
- Использование фильтра и функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ
- Подсчет и суммирование ячеек на основе цвета шрифта
- Использование пользовательской функции
- Использование простой функции – Kutools для Excel
- Подсчет и суммирование ячеек на основе цвета условного форматирования
- Использование кода VBA
- Использование умной функции – Kutools для Excel
- Связанные статьи
- Лучшие инструменты для повышения производительности офиса
- Комментарии