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

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

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

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

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

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

Предположим, у нас есть таблица бизнес-заказов, в которой ячейки в столбце «Доставка» окрашены в соответствии со своими значениями: «Просрочено через X дней» — оранжевым, «Доставлено» — зеленым, «Просрочено» — красным.

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

1. Откройте книгу Excel и нажмите Alt + F11, чтобы запустить редактор Visual Basic для приложений (VBA).
2. Щелкните правой кнопкой мыши имя книги в области «Проект — проект VBA», которая находится в левой части экрана, а затем выберите «Вставить»> «Модуль» в появившемся контекстном меню.
3. Вставьте следующий код в свой лист:

Функция GetCellColor (xlRange As Range) Dim indRow, indColumn As Long Dim arResults () Application.Volatile Если xlRange — ничего, установите xlRange = Application.ThisCell End If If xlRange.Count> 1 Then ReDim arResults (1 To xlRange.Rows.Count , 1 в xlRange.Columns.Count) Для indRow = 1 в xlRange.Rows.Count Для indColumn = 1 В xlRange.Columns.Count arResults (indRow, indColumn) = xlRange (indRow, indColumn) .GetInterior.Color = Next arResults El GetCellColor = xlRange.Interior.Color End If End Функция Функция GetCellFontColor (xlRange As Range) Dim indRow, indColumn As Long Dim arResults () Application.Volatile Если xlRange Is Nothing, Установите xlRange = Application.ThisCell. EndCount Если xlRange> If xlRange 1, то ReDim arResults (1 в xlRange.Rows.Count, 1 в xlRange.Columns.Count) для indRow = 1 в xlRange.Rows.Count для indColumn = 1 в xlRange.Columns.Count arResults (indRow , index cellCurrent As Range Dim cntRes As Long Application.Volatile cntRes = 0 indRefColor = cellCRefsColor 1) .Interiors. Цвет для каждой ячейки Текущий в rData Если indRefColor = cellCurrent.Interior.Color Тогда cntRes = cntRes + 1 Конец Если Следующая ячейка Текущий CountCellsByColor = cntRes Конечная функция Функция SumCellsByColor (rData As Range, RangeRimRim As long cellimCurrent As Range. = cellRefColor.Cells (1, 1) .Interior.Color Для каждой ячейкиCurrent In rData If indRefColor = cellCurrent.Interior.Color Then sumRes = WorksheetFunction.Sum (cell) sumRrent If Next cellCurrent SumCellsByColor = sumRes End Function As CountCellvalsByCounter Range) As Long Dim indRefColor As Long Dim cell Current As Range Dim cntRes As Long Application. Volatile cntRes = 0 indRefColor = cellRefColor.Cells (1, 1) .Font.Color Для каждой ячейкиCurrent In rData If indRefColor = cellCurrent.Font.Color Then cntRes = cntRes + 1 End If Next cellCurrent Range CountCellsByFontColor Function) 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 indRefColorF = cell.Current. Затем 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: содержит определенный цвет заливки, в нашем случае красный.

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

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

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

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

= SumCellsByColor (D2: D14, A17)

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

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

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

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

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

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

WbkCountCellsByColor Функция (cellRefColor Как Range) Dim Dim vWbkRes wshCurrent Как Worksheet Application.ScreenUpdating = False Application.Calculation = xlCalculationManual vWbkRes = 0 Для каждого wshCurrent В Worksheets wshCurrentWolkbActivate xBsbSfKD Dimension ApplicationCurrent В Worksheets wshCurrentWolkbActivate xBsbSfK Ложные ApplicationCalculation SumCellsByColor (wshCurrent.UsedRange, cellRefColor) Далее Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic WbkSumCellsByColor = vWbbk

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

= WbkCountCellsByColor()
= WbkSumCellsByColor()

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

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

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

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

Функции, которые считают количество по цвету:

• CountCellsByColor (range, color_code) — подсчитывает ячейки с определенным цветом заливки. В приведенном выше примере мы использовали следующую формулу для подсчета количества ячеек в зависимости от их цвета:

  = ColorCellCount (F2: F14, A17)

  где F2: F14 — выбранный диапазон, A17 — ячейка с желаемым цветом заливки.

  Все формулы, перечисленные ниже, работают по одному принципу.

• CountCellsByFontColor (range, color_code) — подсчитывает ячейки с указанным цветом шрифта.

Функции, которые суммируют значения по цвету ячейки:

• SumCellsByColor (range, color_code) — вычисляет сумму ячеек с заданным цветом заливки.
• SumCellsByFontColor (range, color_code) — вычисляет сумму ячеек с заданным цветом шрифта.

Функции, которые возвращают код цвета:

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

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

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

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

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

• Отформатируйте все ячейки на основе их значений
• Форматировать только значения, расположенные вверху или внизу
• Форматировать только значения выше или ниже среднего);
• Форматировать только уникальные или повторяющиеся значения

Кроме того, почти все эти коды 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 indRefColorCell indRefColorCell). = WorksheetFunction.Sum (Selection (indCurCell), sumRes) End If Next MsgBox «Count =» & cntrLes & vbC & «Sum =» & sumRes & vbCrLf & & _ «Color =» & Left («000000», 6 — Len (Hex (indRefColor))) & _ Hex (indRefColor) & vbCrLf ,, «Подсчет и сумма по цвету условного формата» End Sub

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

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

   

В этом примере мы выбрали столбец Qty и получили следующие числа:

• Count — количество ячеек желаемого цвета; в нашем случае это красноватый цвет, используемый для выделения ячеек со значением Expired.
• Сумма — это сумма значений всех эритроцитов в столбце «Кол-во», которая представляет собой общее количество элементов, отмеченных как «Просроченные.
• Цвет — это шестнадцатеричный код цвета выбранной ячейки, в нашем случае D2.