Как посчитать закрашенные ячейки в 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 посчитать количество ячеек по цвету ячейки или цвету текста

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

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

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

Итак, приступим. Зайдите в редактор Visual Basic, для этого:

в Excel 2003 нажмите на Сервис, далее Макрос и затем Редактор Visual Basic.

в Excel 2007, 2010 и 2013 это делается по-другому. Зайдите в раздел Разработчик, далее выберите Visual Basic

Внимание! Раздел панели инструментов Разработчик в Excel 2007 доступен по умолчанию, а в Excel 2010 и 2013 его необходимо включить. Это особенно полезно сделать тем пользователям, которые будут часто работать с макросами. Чтобы включить панель инструментов Разработчик в Excel 2010 или 2013 необходимо запустить Файл | Параметры | Настройка ленты после этого необходимо с правой стороны необходимо поставить галочку напротив надписи Разработчик

После того как откроется редактор Visual Basic, вставьте пустой модуль, для этого выберите меню Insert и далее Module

и скопируйте туда текст простой функции:

Public Function ColorNom(Cell As Range)
ColorNom = Cell.Interior.ColorIndex
End Function

После этого закройте редактор Visual Basic и можно вернуться к нашему файлу. В любой пустой ячейки введите пользовательскую функцию, которую мы ввели раннее. В нашем случае это функция ColorNom, ее можно вызвать либо через меню Вставка, Функция — категория Определенные пользователем, либо просто можно напечатать ее в самой ячейке =ColorNom( A1 ), где A1 — это наша ячейка, в которой нам необходимо определить индекс цвета.

После этого уже не составит труда посчитать количество ячеек или значений в зависимости от цвета ячейки. Используйте нашу статью как посчитать в Excel количество ячеек/значений

Если вам необходимо посчитать количество значений или сумму в зависимости от цвета текста, то необходимо немного изменить код пользовательской функции.

Public Function ColorNom(Cell As Range)
ColorNom = Cell.Font.ColorIndex
End Function

Важно! Вы не сможете находить с помощью данной функции номер цвета ячейки при использовании условного форматирования. Кроме того, при изменении цвета ячейки Excel не пересчитывает значения, необходимо это делать в ручную, нажимая Ctrl+Alt+F9, либо изменения будут происходить при новом открытии данного файла. Это происходит из-за того, что Excel не считает изменение цвета ячейки редактированием формулы. В связи с этим, если это критично, то можно внести изменение в саму формулу, просто добавив функцию, которая постоянно пересчитывается и при этом не повлияет на определение цвета ячейки. Например, указать функцию определения текущей даты, умноженную на ноль.
В нашем случае функция будет выглядеть следующем образом.

=ColorNom(A1)+Сегодня()*0

Пример подсчета количества значений по цвету цвету заливки ячеек в Excel

Рассмотрим вышеуказанный пример с перечнем фруктов. Мы определили код ячеек и отобразили его напротив каждой ячейки.

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

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

Считать количество мы будем с помощью функции СЧЁТЕСЛИ

Вот так выглядят аргументы данной функции

=СЧЁТЕСЛИ( диапазон ; критерий )

=СЧЁТЕСЛИ( $B$1:$B$8 ; E2 )

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

Скачать пример файла: Цвет_Ячеек.xlsm (файл с поддержкой макросов)

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

Добавление заливки ячеек делает ваш документ 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 содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.

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

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

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

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

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

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