Как посчитать количество закрашенных ячеек в excel

Определение количества ячеек с заливкой

Добавление заливки ячеек делает ваш документ Excel более понятным, добавляя наглядности для анализа данных. Часто возникает потребность посчитать количество ячеек с заданным цветом заливки.

К сожалению, Excel не имеет встроенной функции, которая использует цвета заливки в качестве условий в формулах. Альтернативой является использование собственных функций (написанный на VBA). Надстройка VBA-Excel содержит функцию СЧЁТЗАЛИВКА, с помощью которой можно это реализовать.

Описание функции

Функция =СЧЁТЗАЛИВКА(ДИАПАЗОН, ЯЧЕЙКА) имеет два обязательных аргумента:

• ДИАПАЗОН ячеек, где необходимо произвести подсчет ячеек с определенной заливкой.
• ЯЧЕЙКА ячейка с цветом заливки которые необходимо посчитать.

Если вы забыли синтаксис данной функции, вы всегда сможете вызвать формулу из выпадающего списка функций в надстройке.

Замечание об автоматическом вычислении. Так как пересчет формул в Excel автоматически не происходит при изменении заливки ячеек, то данная функция не пересчитывается автоматически. Для того, чтобы принудительно пересчитать формулы активного листа используйте сочетание клавиш Ctrl+Alt+F9.

Код на VBA

Public Function СЧЁТЗАЛИВКА(ДИАПАЗОН As Range, ЯЧЕЙКА) As Long Dim S As Double Dim rCell As Range Dim ColCell As Long ColCell = ЯЧЕЙКА.Interior.Color S = 0 For Each rCell In ДИАПАЗОН If rCell.Interior.Color = ColCell Then S = S + 1 End If Next СЧЁТЗАЛИВКА = S End Function

Надстройка
VBA-Excel

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

Подсчет количества ячеек с определенным цветом ячеек с помощью VBA

На вкладке «Формулы Microsoft Excel» мы знаем, что в категории «Дополнительные статистические функции» есть функция с именем СЧЁТЕСЛИ, которая подсчитывает количество ячеек в диапазоне, > соответствующих заданному условию. Условия для этой функции ограничены текстом или числами. Однако с помощью VBA можно создать функцию для подсчета количества ячеек с другими критериями, такими как цвет ячеек.

Дополнительные сведения

С помощью VBA можно создать определяемую пользователем функцию (UDF) и сохранить ее в файле надстройки, чтобы ее можно было использовать в других книгах и передавать на другие компьютеры.

Создание определяемой пользователем функции

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

1. Откройте Microsoft Excel, а затем нажмите клавиши ALT+F11, чтобы отобразить окно редактора Visual Basic (VBE).
2. В меню «Вставка » выберите «Модуль «, чтобы создать модуль. Затем введите следующий скрипт:

Function CountCcolor(range_data As range, criteria As range) As Long Dim datax As range Dim xcolor As Long xcolor = criteria.Interior.ColorIndex For Each datax In range_data If datax.Interior.ColorIndex = xcolor Then CountCcolor = CountCcolor + 1 End If Next datax End Function 

=CountCcolor(range_data,criteria) 

• В аргументе «range_data» выберите ячейку C2 –C51.
• В аргументе criteria выберите ячейку F1.

Примечание. Вы можете сохранить Add-In в любом месте. Но чтобы он был указан в качестве Add-In в программе Excel, сохраните его в расположении по умолчанию. В Windows 7 расположение по умолчанию для любой версии Microsoft Excel: C:\Users\RADDINI\AppData\Roaming\Microsoft\AddIns

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

=CountCcolor(range_data,criteria) 

Отказ от ответственности за содержимое общедоступных решений

КОРПОРАЦИЯ МАЙКРОСОФТ И (ИЛИ) СООТВЕТСТВУЮЩИЕ ПОСТАВЩИКИ НЕ ДЕЛАЮТ ПРЕДСТАВЛЕНИЙ О ПРИГОДНОСТИ, НАДЕЖНОСТИ ИЛИ ТОЧНОСТИ СВЕДЕНИЙ И СВЯЗАННЫХ ГРАФИЧЕСКИХ ЭЛЕМЕНТОВ, СОДЕРЖАЩИХСЯ В ЭТОМ РАЗДЕЛЕ. ВСЯ ТАКАЯ ИНФОРМАЦИЯ И СВЯЗАННАЯ ГРАФИКА ПРЕДОСТАВЛЯЮТСЯ «КАК ЕСТЬ» БЕЗ КАКИХ-ЛИБО ГАРАНТИЙ. КОРПОРАЦИЯ МАЙКРОСОФТ И (ИЛИ) СООТВЕТСТВУЮЩИЕ ПОСТАВЩИКИ ТЕМ САМЫМ ОБЯЗЫВАТЬ ВСЕ ГАРАНТИИ И УСЛОВИЯ, СВЯЗАННЫЕ С ЭТОЙ ИНФОРМАЦИЕЙ И СВЯЗАННОЙ ГРАФИКОЙ, ВКЛЮЧАЯ ВСЕ ПОДРАЗУМЕВАЕМЫЕ ГАРАНТИИ И УСЛОВИЯ ТОВАРНОЙ ПРИГОДНОСТИ, ПРИГОДНОСТИ ДЛЯ ОПРЕДЕЛЕННОЙ ЦЕЛИ, ТРУДОЗАТРАТ, ДОЛЖНОСТЬ И ОТСУТСТВИЕ НАРУШЕНИЯ ПРАВ. ВЫ СОГЛАШАЕТЕСЬ С ТЕМ, ЧТО НИ В КОЕМ СЛУЧАЕ КОРПОРАЦИЯ МАЙКРОСОФТ И (ИЛИ) ЕЕ ПОСТАВЩИКИ НЕ НЕСУТ ОТВЕТСТВЕННОСТИ ЗА ЛЮБЫЕ ПРЯМЫЕ, КОСВЕННЫЕ, НЕПРЯМЫЕ, СЛУЧАЙНЫЕ, ОСОБЫЕ, КОСВЕННЫЕ И КОСВЕННЫЕ УБЫТКИ ИЛИ ЛЮБЫЕ УБЫТКИ, ВКЛЮЧАЯ БЕЗ ОГРАНИЧЕНИЙ УБЫТКИ ЗА ПОТЕРЮ ИСПОЛЬЗОВАНИЯ, ДАННЫХ ИЛИ ПРИБЫЛИ, ВОЗНИКАЮЩИЕ ИЗ-ЗА ИСПОЛЬЗОВАНИЯ СВЕДЕНИЙ И СВЯЗАННЫХ ГРАФИЧЕСКИХ ЭЛЕМЕНТОВ, СОДЕРЖАЩИХСЯ В ЭТОМ ДОКУМЕНТЕ, ИЛИ ИХ НЕВОЗМОЖНОСТИ ИСПОЛЬЗОВАТЬ ИХ. НА ОСНОВЕ КОНТРАКТА, TORT, НЕОСТОРОЖНОСТИ, СТРОГОЙ ОТВЕТСТВЕННОСТИ ИЛИ ИНЫМ ОБРАЗОМ, ДАЖЕ ЕСЛИ КОРПОРАЦИЯ МАЙКРОСОФТ ИЛИ ЛЮБОЙ ИЗ ЕЕ ПОСТАВЩИКОВ БЫЛИ ОПОВЕТИВЫ О ВОЗМОЖНОСТИ УБЫТКОВ.

Способы подсчета количества ячеек в диапазоне с данными

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

1. Щелкните ячейку, в которой должен выводиться результат.
2. На вкладке Формулы щелкните Другие функции, наведите указатель мыши на пункт Статистические и выберите одну из следующих функции:
3. СЧЁТЗ: подсчитывает количество непустых ячеек.
4. СЧЁТ: подсчитывает количество ячеек, содержащих числа.
5. СЧИТАТЬПУСТОТЫ: подсчитывает количество пустых ячеек.
6. СЧЁТЕСЛИ: подсчитывает ячейки, отвечающие заданным условиям.

Совет: Чтобы ввести нескольких условий, используйте вместо этого функцию СЧЁТЕСЛИМН.

1. Щелкните ячейку, в которой должен выводиться результат.
2. На вкладке Формулы щелкните Вставить, наведите указатель мыши на пункт Статистические и выберите одну из следующих функции:
3. СЧЁТЗ: подсчитывает количество непустых ячеек.
4. СЧЁТ: подсчитывает количество ячеек, содержащих числа.
5. СЧИТАТЬПУСТОТЫ: подсчитывает количество пустых ячеек.
6. СЧЁТЕСЛИ: подсчитывает ячейки, отвечающие заданным условиям.

Совет: Чтобы ввести нескольких условий, используйте вместо этого функцию СЧЁТЕСЛИМН.

Как посчитать сумму цветных ячеек в Excel, не используя фильтры?

В своей работе мы практически ежедневно сталкиваемся с таблицами Excel. Для их анализа, зачастую, используем разноцветные заливки ячеек. Визуализация данных, безусловно, помогает пользователю легче усваивать содержимое таблицы. Но, что делать, когда наступает момент подсчета результата? Для этого мы предлагаем не использовать стандартный подход применения фильтров по цвету, а воспользоваться редактором Microsoft Visual Basic (далее VBA), который есть в любом стандартном пакете от Microsoft Office.

Запускать его будем в Microsoft Excel по следующему алгоритму:

• запускаем MS Excel;
• входим во вкладку «Вид»;
• нажимаем «Макросы»;
• в отрывшемся окне вводим название нашей будущей программы (разрешены английские буквы и символы);
• нажимаем «Создать».

Теперь мы в редакторе Visual Basic, и чтобы осуществить расчет по количеству залитых ячеек используем следующий код:

Public Function SumColour(DataRange As Range, ColorSample As Range) As Double Dim cell As Range Dim SumAll As Long Application.Volatile True SumAll = 0 For Each cell In DataRange If cell.Interior.Color = ColorSample.Interior.Color Then SumAll = SumAll+ 1 End If Next cell SumColour = SumAll End Function

Так, с помощью функции SumColour, можно пересчитать количество ячеек.

Для пересчета суммы окрашенных ячеек берем вышеописанный код, меняем название функции (например, SumByColour) и строку кода «SumAll = SumAll+ 1» на «SumAll = SumAll+ cell.Value».

На практике использование редактора VBA помогает пересчитывать большие объемы данных не опасаясь ошибок, связанных с сортировкой и фильтрацией в Excel.

А какой способ используете вы? Делитесь в комментариях!

19K открытий
5 комментариев
Теперь мы в редакторе Visual Basic.

Скажите, а вы не пробовали проделать то же самое в Google Docs (Spreadsheets)?
Работает?

Попробовал бы сам, но к сожалению, не в курсе как там запускать подобные скрипты.

Развернуть ветку

Добрый день. Для Google Sheets есть такие же скрипты . Как-то писал скрипт для 8 цветов. Например, в настройках вы выбираете комфортные для себя цвета и скрипт потом суммирует то что выделено цветом

Развернуть ветку

Добрый день! Спасибо за ваш вопрос.
Запускали только на локальном ресурсе, используя Excel.
В Google Docs пока не реализовывали.

Развернуть ветку

Как-то это нехорошо, считать цветных отдельно от белых.

Развернуть ветку

На небольшом объеме алгоритм ОК, но будет очень медленным на больших. Как вариант ускорения — использовать UDF для вывода кода цвета заливки в доп столбце и делать расчет по этому столбцу штатной функцией с условим