Как в экселе считать только выделенные цветом ячейки
Перейти к содержимому

Как в экселе считать только выделенные цветом ячейки

  • автор:

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

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

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

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

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

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

Код на VBA

Public Function СУММЗАЛИВКА(ДИАПАЗОН As Range, ЯЧЕЙКА) As Double 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 + rCell.Value End If Next СУММЗАЛИВКА = S End Function

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

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

Вычисляйте СЧЁТ, СУММ, СРЗНАЧ, МИН, МАКС на основе цвета ячеек или цвета шрифта

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

Инструмент «Счёт по цвету» мгновенно и без VBA считает значения в ячейках, исходя из их цвета:

Вычисление СЧЁТ, СУММ, СРЗНАЧ, МИН, МАКС для каждого цвета
Агрегация по цвету заливки и/или цвету условного форматирования
Cчёт по цвету фона ячеек или цвету шрифта
Предпросмотр и вставка таблицы результата на рабочий лист

Перед началом работы добавьте «Счёт по цвету» в Excel

«Счёт по цвету» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

Начните работу с инструментами XLTools

Скачать XLTools для Excel
– пробный период дает 14 дней полного доступа ко всем инструментам.

Как посчитать значения ячеек на основе цвета заливки

Excel по-разному обрабатывает два типа цветного фона ячеек: заливка сплошным цветом, заданная пользователем (когда вы сами назначаете цвет) и условный цвет (при применении условного форматирования). С надстройкой вы можете агрегировать значения по каждому типу цветного фона.

Нажмите кнопку Счёт по цвету на панели XLTools Укажите диапазон.
Совет:нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
Заданные цвета – чтобы учитывать только ячейки со сплошной заливкой фона
Условные цвета – чтобы учитывать только ячейки с условным форматированием
Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
Из следующего выпадающего списка выберите «Вычислять по цвету» Фона .

Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.

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

Выберите поместить результаты на новый или существующий лист.
Нажмите OK Готово!

Счет по цвету заливки ячеек

Как посчитать значения ячеек на основе цвета шрифта

Excel по-разному обрабатывает два типа цветного шрифта: цвет шрифта, заданный пользователем (когда вы сами назначаете цвет) и условный цвет шрифта (при применении условного форматирования). С надстройкой вы можете агрегировать значения по каждому типу цветного шрифта.

Нажмите кнопку Счёт по цвету на панели XLTools Укажите диапазон.
Совет:нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
Заданные цвета – чтобы учитывать только ячейки заданным цветом шрифта
Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
Из следующего выпадающего списка выберите «Вычислять по цвету» Шрифта .

Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.

Выберите поместить результаты на новый или существующий лист.
Нажмите OK Готово!

Счет знчений по цвету шрифта в Excel

Как посчитать значения ячеек на основе цвета условного форматирования

Excel по-разному обрабатывает два типа цветного фона ячеек: заливка сплошным цветом, заданная пользователем (когда вы сами назначаете цвет) и условный цвет (при применении условного форматирования). Условное форматирование может применятся как к шрифту, так и к фону фчейки. С надстройкой вы можете агрегировать значения по любому типу условного цвета.

Нажмите кнопку Счёт по цвету на панели XLTools Укажите диапазон.
Совет:нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
Условные цвета – чтобы учитывать только ячейки с условным форматированием
Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.

Из следующего выпадающего списка выберите «Вычислять по цвету» Фона или Шрифта , в зависимости от типа условного форматирования.

Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.

Выберите поместить результаты на новый или существующий лист.
Нажмите OK Готово!

Счет по цвету условного форматирования в Excel

Доступные агрегатные вычисления (Count, Sum, Average, Minimum, Maximum)

Надстройка производит расчёт самых частых агрегатных функций, на основе цвета шрифта или фона ячейки:

СЧЁТ (COUNT) – подсчёт количества всех значений в диапазоне по цвету
СУММ (SUM) – сумма всех значений в диапазоне по цвету
СРЗНАЧ (AVERAGE) – среднее (арифметическое среднее) всех значений в диапазоне по цвету
МИН (MIN) – наименьшее значение в диапазоне по цвету
МАКС (MAX) – наибольшее значение в диапазоне по цвету

Какие ячейки и значения учитываются при вычислениях

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

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

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

Вставленная на лист, сводная таблица с результатами вычислений содержит значения (не формулы и не ссылки).

Как посчитать количество и сумму ячеек по цвету в Excel 2010 и 2013

Из этой статьи Вы узнаете, как в Excel посчитать количество и сумму ячеек определенного цвета. Этот способ работает как для ячеек, раскрашенных вручную, так и для ячеек с правилами условного форматирования. Кроме того, Вы научитесь настраивать фильтр по нескольким цветам в Excel 2010 и 2013.

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

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

Если не использовать сторонние надстройки, существует только одно решение – создать пользовательскую функцию (UDF). Если Вы мало знаете об этой технологии или вообще никогда не слышали этого термина, не пугайтесь, Вам не придётся писать код самостоятельно. Здесь Вы найдёте отличный готовый код (написанный нашим гуру Excel), и всё, что Вам потребуется сделать – это скопировать его и вставить в свою рабочую книгу.

  • Считаем и суммируем по цвету, когда ячейки раскрашены вручную
    • Считаем сумму и количество ячеек по цвету во всей книге

    Как считать и суммировать по цвету на листе Excel

    Предположим, у Вас есть таблица заказов компании, в которой ячейки в столбце Delivery раскрашены в зависимости от их значений: Due in X Days – оранжевые, Delivered – зелёные, Past Due – красные.

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

    Теперь мы хотим автоматически сосчитать количество ячеек по их цвету, то есть сосчитать количество красных, зелёных и оранжевых ячеек на листе. Как я уже сказал выше, прямого решения этой задачи не существует. Но, к счастью, в нашей команде есть очень умелые и знающие Excel гуру, и один из них написал безупречный код для Excel 2010 и 2013. Итак, выполните 5 простых шагов, описанных далее, и через несколько минут Вы узнаете количество и сумму ячеек нужного цвета.

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

    1. Откройте книгу Excel и нажмите Alt+F11, чтобы запустить редактор Visual Basic for Applications (VBA).
    2. Правой кнопкой мыши кликните по имени Вашей рабочей книги в области Project – VBAProject, которая находится в левой части экрана, далее в появившемся контекстном меню нажмите Insert >Module.
    3. Вставьте на свой лист вот такой код:
    Function GetCellColor(xlRange As Range) Dim indRow, indColumn As Long Dim arResults() Application.Volatile If xlRange Is Nothing Then Set xlRange = Application.ThisCell End If If xlRange.Count > 1 Then ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count) For indRow = 1 To xlRange.Rows.Count For indColumn = 1 To xlRange.Columns.Count arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color Next Next GetCellColor = arResults Else GetCellColor = xlRange.Interior.Color End If End Function Function GetCellFontColor(xlRange As Range) Dim indRow, indColumn As Long Dim arResults() Application.Volatile If xlRange Is Nothing Then Set xlRange = Application.ThisCell End If If xlRange.Count > 1 Then ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count) For indRow = 1 To xlRange.Rows.Count For indColumn = 1 To xlRange.Columns.Count arResults(indRow, indColumn) = xlRange(indRow, indColumn).Font.Color Next Next GetCellFontColor = arResults Else GetCellFontColor = xlRange.Font.Color End If End Function Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long Dim indRefColor As Long Dim cellCurrent As Range Dim cntRes As Long Application.Volatile cntRes = 0 indRefColor = cellRefColor.Cells(1, 1).Interior.Color For Each cellCurrent In rData If indRefColor = cellCurrent.Interior.Color Then cntRes = cntRes + 1 End If Next cellCurrent CountCellsByColor = cntRes End Function Function SumCellsByColor(rData As Range, cellRefColor As Range) Dim indRefColor As Long Dim cellCurrent As Range Dim sumRes Application.Volatile sumRes = 0 indRefColor = cellRefColor.Cells(1, 1).Interior.Color For Each cellCurrent In rData If indRefColor = cellCurrent.Interior.Color Then sumRes = WorksheetFunction.Sum(cellCurrent, sumRes) End If Next cellCurrent SumCellsByColor = sumRes End Function Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long Dim indRefColor As Long Dim cellCurrent As Range Dim cntRes As Long Application.Volatile cntRes = 0 indRefColor = cellRefColor.Cells(1, 1).Font.Color For Each cellCurrent In rData If indRefColor = cellCurrent.Font.Color Then cntRes = cntRes + 1 End If Next cellCurrent CountCellsByFontColor = cntRes End Function Function SumCellsByFontColor(rData As Range, cellRefColor As Range) Dim indRefColor As Long Dim cellCurrent As Range Dim sumRes Application.Volatile sumRes = 0 indRefColor = cellRefColor.Cells(1, 1).Font.Color For Each cellCurrent In rData If indRefColor = cellCurrent.Font.Color Then sumRes = WorksheetFunction.Sum(cellCurrent, sumRes) End If Next cellCurrent SumCellsByFontColor = sumRes End Function
    1. Сохраните рабочую книгу Excel в формате .xlsm (Книга Excel с поддержкой макросов).Если Вы не слишком уверенно чувствуете себя с VBA, то посмотрите подробную пошаговую инструкцию и массу полезных советов в учебнике Как вставить и запустить код VBA в Excel.
    2. Когда все закулисные действия будут выполнены, выберите ячейки, в которые нужно вставить результат, и введите в них функцию CountCellsByColor: CountCellsByColor( диапазон , код_цвета )

    В этом примере мы используем формулу =CountCellsByColor(F2:F14,A17), где F2:F14 – это диапазон, содержащий раскрашенные ячейки, которые Вы хотите посчитать. Ячейка A17 – содержит определённый цвет заливки, в нашем случае красный.

    Точно таким же образом Вы записываете формулу для других цветов, которые требуется посчитать в таблице (жёлтый и зелёный).

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

    Если в раскрашенных ячейках содержатся численные данные (например, столбец Qty. в нашей таблице), Вы можете суммировать значения на основе выбранного цвета ячейки, используя аналогичную функцию SumCellsByColor:

    SumCellsByColor( диапазон , код_цвета )

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

    Как показано на снимке экрана ниже, мы использовали формулу:

    где D2:D14 – диапазон, A17 – ячейка с образцом цвета.

    Таким же образом Вы можете посчитать и просуммировать ячейки по цвету шрифта при помощи функций CountCellsByFontColor и SumCellsByFontColor соответственно.

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

    Замечание: Если после применения выше описанного кода VBA Вам вдруг потребуется раскрасить ещё несколько ячеек вручную, сумма и количество ячеек не будут пересчитаны автоматически после этих изменений. Не ругайте нас, это не погрешности кода ��

    На самом деле, это нормальное поведение макросов в Excel, скриптов VBA и пользовательских функций (UDF). Дело в том, что все подобные функции вызываются только изменением данных на листе, но Excel не расценивает изменение цвета шрифта или заливки ячейки как изменение данных. Поэтому, после изменения цвета ячеек вручную, просто поставьте курсор на любую ячейку и кликните F2, а затем Enter, сумма и количество после этого обновятся. Так нужно сделать, работая с любым макросом, который Вы найдёте далее в этой статье.

    Считаем сумму и количество ячеек по цвету во всей книге

    Представленный ниже скрипт Visual Basic был написан в ответ на один из комментариев читателей (также нашим гуру Excel) и выполняет именно те действия, которые упомянул автор комментария, а именно считает количество и сумму ячеек определённого цвета на всех листах данной книги. Итак, вот этот код:

    Function WbkCountCellsByColor(cellRefColor As Range) Dim vWbkRes Dim wshCurrent As Worksheet Application.ScreenUpdating = False Application.Calculation = xlCalculationManual vWbkRes = 0 For Each wshCurrent In Worksheets wshCurrent.Activate vWbkRes = vWbkRes + CountCellsByColor(wshCurrent.UsedRange, cellRefColor) Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic WbkCountCellsByColor = vWbkRes End Function Function WbkSumCellsByColor(cellRefColor As Range) Dim vWbkRes Dim wshCurrent As Worksheet Application.ScreenUpdating = False Application.Calculation = xlCalculationManual vWbkRes = 0 For Each wshCurrent In Worksheets wshCurrent.Activate vWbkRes = vWbkRes + SumCellsByColor(wshCurrent.UsedRange, cellRefColor) Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic WbkSumCellsByColor = vWbkRes End Function

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

    Просто введите одну из этих формул в любую пустую ячейку на любом листе Excel. Диапазон указывать не нужно, но необходимо в скобках указать любую ячейку с заливкой нужного цвета, например, =WbkSumCellsByColor(A1), и формула вернет сумму всех ячеек в книге, окрашенных в этот же цвет.

    Пользовательские функции для определения кодов цвета заливки ячеек и цвета шрифта

    Здесь Вы найдёте самые важные моменты по всем функциям, использованным нами в этом примере, а также пару новых функций, которые определяют коды цветов.

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

    Функции, которые считают количество по цвету:
    • CountCellsByColor( диапазон , код_цвета ) – считает ячейки с заданным цветом заливки.В примере, рассмотренном выше, мы использовали вот такую формулу для подсчёта количества ячеек по их цвету: =CountCellsByColor(F2:F14,A17) где F2:F14 – это выбранный диапазон, A17 – это ячейка с нужным цветом заливки. Все перечисленные далее формулы работают по такому же принципу.
    • CountCellsByFontColor( диапазон , код_цвета ) – считает ячейки с заданным цветом шрифта.
    Функции, которые суммируют значения по цвету ячейки:
    • SumCellsByColor( диапазон , код_цвета ) – вычисляет сумму ячеек с заданным цветом заливки.
    • SumCellsByFontColor( диапазон , код_цвета ) – вычисляет сумму ячеек с заданным цветом шрифта.
    Функции, которые возвращают код цвета:

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

    • GetCellFontColor( ячейка ) – возвращает код цвета шрифта в выбранной ячейке.
    • GetCellColor( ячейка ) – возвращает код цвета заливки в выбранной ячейке.

    Итак, посчитать количество ячеек по их цвету и вычислить сумму значений в раскрашенных ячейках оказалось совсем не сложно, не так ли? Но что если Вы не раскрашиваете ячейки вручную, а предпочитаете использовать условное форматирование, как мы делали это в статьях Как изменить цвет заливки ячеек и Как изменить цвет заливки строки, основываясь на значении ячейки?

    Как посчитать количество и сумму ячеек по цвету, раскрашенных при помощи условного форматирования

    Если Вы применили условное форматирование, чтобы задать цвет заливки ячеек в зависимости от их значений, и теперь хотите посчитать количество ячеек определённого цвета или сумму значений в них, то у меня для Вас плохие новости – не существует универсальной пользовательской функции, которая будет по цвету суммировать или считать количество ячеек и выводить результат в определённые ячейки. По крайней мере, я не слышал о таких функциях, а жаль ��

    Конечно, Вы можете найти тонны кода VBA в интернете, который пытается сделать это, но все эти коды (по крайней мере, те экземпляры, которые попадались мне) не обрабатывают правила условного форматирования, такие как:

    • Format all cells based on their values (Форматировать все ячейки на основании их значений);
    • Format only top or bottom ranked values (Форматировать только первые или последние значения);
    • Format only values that are above or below average (Форматировать только значения, которые находятся выше или ниже среднего);
    • Format only unique or duplicate values (Форматировать только уникальные или повторяющиеся значения).

    Кроме того, практически все эти коды VBA имеют целый ряд особенностей и ограничений, из-за которых они могут не работать корректно с какой-то конкретной книгой или типами данных. Так или иначе, Вы можете попытать счастье и google в поисках идеального решения, и если Вам удастся найти его, пожалуйста, возвращайтесь и опубликуйте здесь свою находку!

    Код VBA, приведённый ниже, преодолевает все указанные выше ограничения и работает в таблицах Microsoft Excel 2010 и 2013, с любыми типами условного форматирования (и снова спасибо нашему гуру!). В результате он выводит количество раскрашенных ячеек и сумму значений в этих ячейках, независимо от типа условного форматирования, применённого на листе.

    Sub SumCountByConditionalFormat() Dim indRefColor As Long Dim cellCurrent As Range Dim cntRes As Long Dim sumRes Dim cntCells As Long Dim indCurCell As Long cntRes = 0 sumRes = 0 cntCells = Selection.CountLarge indRefColor = ActiveCell.DisplayFormat.Interior.Color For indCurCell = 1 To (cntCells - 1) If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color Then cntRes = cntRes + 1 sumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes) End If Next MsgBox "Count=" & cntRes & vbCrLf & "Sum= " & sumRes & vbCrLf & vbCrLf & _ "Color=" & Left("000000", 6 - Len(Hex(indRefColor))) & _ Hex(indRefColor) & vbCrLf, , "Count & Sum by Conditional Format color" End Sub

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

    1. Добавьте код, приведённый выше, на Ваш лист, как мы делали это в первом примере.
    2. Выберите диапазон (или диапазоны), в которых нужно сосчитать цветные ячейки или просуммировать по цвету, если в них содержатся числовые данные.
    3. Нажмите и удерживайте Ctrl, кликните по одной ячейке нужного цвета, затем отпустите Ctrl.
    4. Нажмите Alt+F8, чтобы открыть список макросов в Вашей рабочей книге.
    5. Выберите макрос SumCountByConditionalFormat и нажмите Run (Выполнить).Подсчет и суммирование ячеек по цвету в ExcelВ результате Вы увидите вот такое сообщение: Подсчет и суммирование ячеек по цвету в Excel

    Для этого примера мы выбрали столбец Qty. и получили следующие цифры:

    • Count – это число ячеек искомого цвета; в нашем случае это красноватый цвет, которым выделены ячейки со значением Past Due.
    • Sum – это сумма значений всех ячеек красного цвета в столбце Qty., то есть общее количество элементов с отметкой Past Due.
    • Color – это шестнадцатеричный код цвета выделенной ячейки, в нашем случае D2.

    Рабочая книга с примерами для скачивания

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *