Как подсчитать и суммировать ячейки на основе цвета фона в Excel?

Предположим, у нас есть таблица продаж фруктов, как показано на скриншоте ниже, и мы будем подсчитывать или суммировать цветные ячейки в столбце Amount. В этой ситуации мы можем отфильтровать столбец Amount по цвету, а затем легко подсчитать или суммировать отфильтрованные цветные ячейки с помощью функции SUBTOTAL в Excel.

1 . Выберите пустые ячейки, чтобы ввести функцию ПРОМЕЖУТОЧНЫЙ ИТОГ.

1. Чтобы подсчитать все ячейки с одинаковым цветом фона, введите формулу = ПРОМЕЖУТОЧНЫЙ ИТОГ (102, E2: E20 ) ;
2. Чтобы суммировать все ячейки с одинаковым цветом фона, введите формулу = ПРОМЕЖУТОЧНЫЙ ИТОГ (109, E2: E20) ;

Примечание : в обоих формул, E2: E20 – это столбец «Сумма», содержащий цветные ячейки, и вы можете изменить их по своему усмотрению.

2 . Выберите заголовок таблицы и нажмите Данные > Фильтр . См. Снимок экрана:

3 . Щелкните значок фильтра в ячейке заголовка столбца “Сумма”, затем щелкните Фильтр по цвету и указанный цвет вы будете считать по порядку. См. Снимок экрана:

После фильтрации обе формулы ПРОМЕЖУТОЧНОГО ИТОГА подсчитывают и суммируют весь отфильтрованный цвет ячеек в столбце Сумма автоматически. См. Снимок экрана:

Примечание : это метод требует, чтобы цветные ячейки, которые вы будете подсчитывать или суммировать, находятся в одном столбце.

Один щелчок для подсчета, суммирования и усреднения цветных ячеек в Excel

С превосходным Count by Цвет в Kutools for Excel , вы можете быстро подсчитывать, суммировать и усреднять ячейки по указанному цвету заливки или цвету шрифта всего одним щелчком мыши в Excel. Кроме того, эта функция также будет определять максимальное и минимальное значения ячеек по цвету заливки или цвету шрифта. 30-дневная бесплатная пробная версия полнофункциональной версии!

Kutools for Excel – включает более 300 удобных инструментов для Excel. Полнофункциональная 30-дневная бесплатная пробная версия, кредитная карта не требуется! Получить сейчас

В этом методе мы создадим именованный диапазон с помощью функции GET.CELL, получим цвет код ячеек, а затем легко подсчитать или суммировать по цветовому коду в Excel. Пожалуйста, сделайте следующее:

1 . Нажмите Формулы > Определить имя . См. Снимок экрана:

2 . В диалоговом окне «Новое имя» выполните следующие действия:
(1) Введите имя в поле «Имя»;
(2) Введите формулу = GET.CELL ( 38, Sheet4! $ E2) в поле “Ссылается на” ( примечание : в формуле 38 означает возврат кода ячейки, а Sheet4! $ E2 – первая ячейка в столбце “Сумма”, за исключением заголовка столбца, который необходимо изменить в зависимости от данных таблицы.)
(3) Щелкните значок Кнопка OK .

3 . Теперь добавьте новый столбец «Цвет» прямо в исходную таблицу. Затем введите формулу = NumColor и перетащите маркер автозаполнения, чтобы применить формулу к другим ячейкам в столбце «Цвет». См. Снимок экрана:
Примечание . В формуле NumColor – это именованный диапазон, который мы указали на первых двух шагах. Вам нужно изменить его на указанное вами имя.

Теперь цвет код каждой ячейки в столбце «Сумма» возвращается в столбце «Цвет». См. Снимок экрана:

4 . Скопируйте и укажите цвет заливки в пустом диапазоне на активном листе и введите формулы рядом с ним, как показано на скриншоте ниже:
A. Чтобы подсчитать ячейки по цвету, введите формулу = COUNTIF ($ F $ 2: $ F $ 20, NumColor) ;
Б. Чтобы суммировать ячейки по цвету, введите формулу = СУММЕСЛИ ($ F $ 2: $ F $ 20, NumColor , $ E $ 2: $ E $ 20) .

Примечание . В обеих формулах $ F $ 2: $ F $ 20 – это столбец цвета, NumColor – указанный именованный диапазон, $ E $ 2: $ E $ 20 – столбец суммы, и вы можете изменить их по своему усмотрению.

Теперь вы увидите, что ячейки в столбце «Сумма» подсчитываются и суммируются по цветам заливки.

Сэкономьте 50% своего времени и уменьшите для себя тысячи кликов мышкой каждый день!

Предположим, что цветные ячейки разбросаны в диапазоне, как показано на скриншоте ниже, оба вышеуказанных метода не могут подсчитать или суммировать цветные ячейки. re, этот метод представит VBA для решения проблемы.

1 . Удерживая нажатыми клавиши ALT + F11 , откроется окно Microsoft Visual Basic для приложений .

2 . Щелкните Insert > Module и вставьте следующий код в окно модуля.

VBA: Count и суммируйте ячейки на основе цвета фона:

3 . Затем сохраните код и примените следующую формулу:
A. Подсчитайте цветные ячейки: = colorfunction (A, B: C, FALSE)
B. Суммируйте цветные ячейки: = colorfunction (A, B: C, TRUE)

Примечание. В формулах выше A – это ячейка с определенным цветом фона, для которого вы хотите вычислить счетчик и сумму, а B: C – это диапазон ячеек, в котором вы хотите рассчитать счет и сумму.

4 . Например, сделайте следующий снимок экрана и введите формулу = colorfunction (A1, A1: D11, FALSE) для подсчета желтых ячеек. И используйте формулу = colorfunction (A1, A1: D11, TRUE) для суммирования желтых ячеек. См. Снимок экрана:

5 . Если вы хотите подсчитать и суммировать другие цветные ячейки, повторите шаг 4. Тогда вы получите следующие результаты:

Kutools for Excel также поддерживает некоторые полезные функции, помогающие пользователям Excel выполнять специальные вычисления, например, количество по цвету фона ячейки, сумма по цвету шрифта и т. д.

Kutools for Excel – включает более 300 удобных инструментов для Excel.. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия!

Kutools for Excel – объединяет более 300 дополнительных функций и инструментов для Microsoft Excel

->

1 . Выделите пустую ячейку, в которую поместите результаты подсчета, и нажмите Kutools > Kutools Functions > Статистические и математические данные > COUNTBYCELLCOLOR . См. Снимок экрана:

2 . В диалоговом окне «Аргументы функции» укажите диапазон, в котором будут подсчитываться цветные ячейки, в поле Ссылка , выберите ячейку, заполненную указанным цветом фона в Color_index_nr и нажмите кнопку ОК . См. Снимок экрана:

Примечания:
(1) Вы также можете ввести указанную функцию Kutools = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) непосредственно в пустую ячейку или строку формул, чтобы получить результаты подсчета ;
(2) Нажмите Kutools > Функции Kutools > Статистика и математика > SUMBYCELLCOLOR или введите = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) в пустую ячейку напрямую, чтобы суммировать ячейки на основе указанного цвета фона.
Примените функции COUNTBYCELLCOLOR и SUMBYCELLCOLOR для каждого цвета фона отдельно, и вы получите результаты, как показано на скриншоте ниже:

Функции Kutools содержат ряд встроенных функций, которые помогают пользователям Excel вычислять легко, включая Count/Sum/Average Visible cells, Count/S по цвету ячеек, Подсчет/Сумма по цвету шрифта, Подсчет символов, Подсчет по полужирному шрифту и т. д. Бесплатная пробная версия!

С Для указанной выше функции, определяемой пользователем, вам нужно вводить формулу один за другим, если есть много разных цветов, этот метод будет утомительным и трудоемким. Но если у вас есть утилита Count by Color в Kutools for Excel , вы можете быстро создать отчет о цветных ячейках. Вы не только можете подсчитывать и суммировать цветные ячейки, но также можете получать средние, максимальные и минимальные значения цветного диапазона.

Kutools for Excel – Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия!

Kutools for Excel – объединяет более 300 дополнительных функций и инструментов для Microsoft Excel

->

1 . Выберите диапазон, который вы хотите использовать, и нажмите Kutools Plus > Подсчет по цвету , см. Снимок экрана:

2 . И в диалоговом окне Подсчет по цвету выполните следующие действия:
(1) Выберите Стандартное форматирование из Метод цвета раскрывающегося списка;
(2) Выберите Фон из раскрывающегося списка Тип подсчета .
(3) Нажмите кнопку «Создать отчет».

Примечание : чтобы подсчитать и суммировать цветные ячейки по определенному условному цвету форматирования, выберите Условное форматирование из раскрывающегося списка Color method в диалоговом окне выше, или выберите Стандартное и условное форматирование из раскрывающегося списка, чтобы подсчитать все ячейки, заполненные указанным цветом.

Теперь вы получите новую книгу со статистикой. См. Снимок экрана:

Подсчет по цвету функция вычисляет ячейки (количество, сумма, среднее, максимальное и т. д.) по цвету фона или цвету шрифта. Бесплатная пробная версия!