Если вы активно используете различные заливки и цвета шрифта на листах Excel для выделения различных типов ячеек или значений, вам, скорее всего, захочется узнать, сколько ячеек выделено определенным цветом. Если в ячейках хранятся числа, вы, вероятно, захотите вычислить сумму всех ячеек с одинаковым заполнением, например сумму всех красных кровяных телец.
Как известно, Microsoft Excel предоставляет ряд функций для различных целей, и логично предположить, что существуют формулы для подсчета ячеек по цвету. Но, к сожалению, нет формулы, позволяющей складывать или считать по цвету на обычном листе Excel.
Если вы не используете сторонние надстройки, есть только одно решение: создать пользовательскую функцию (UDF). Если вы не знакомы с этой технологией или никогда не слышали этот термин, не пугайтесь, вам не нужно программировать себя. Здесь вы найдете отличный готовый код (написанный нашим гуру по Excel), и все, что вам нужно сделать, это скопировать и вставить его в свою книгу.
Предположим, у нас есть таблица бизнес-заказов, в которой ячейки в столбце «Доставка» окрашены в соответствии со своими значениями: «Просрочено через X дней» — оранжевым, «Доставлено» — зеленым, «Просрочено» — красным.
Теперь мы хотим автоматически подсчитывать количество ячеек в зависимости от их цвета, то есть подсчитывать количество красных, зеленых и оранжевых ячеек на листе. Как я уже сказал выше, прямого решения этой проблемы нет. Но, к счастью, в нашей команде есть очень опытные и знающие гуру Excel, и один из них написал безупречный код для Excel 2010 и 2013. Итак, просто выполните 5 простых шагов, описанных ниже, и через несколько минут вы узнаете количество и сумму ячеек вам нужны цвета.
Функция 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
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, как показано ранее в этой статье.
= ColorCellCount (F2: F14, A17)
где F2: F14 — выбранный диапазон, A17 — ячейка с желаемым цветом заливки.
Все формулы, перечисленные ниже, работают по одному принципу.
Итак, подсчитать количество ячеек по их цвету и вычислить сумму значений в цветных ячейках совсем не сложно, верно? Но что, если вы не раскрашиваете ячейки вручную, а предпочитаете использовать условное форматирование, как мы это делали в статьях «Как изменить цвет заливки ячеек» и «Как изменить цвет заливки строки в зависимости от значения ячейки?
Если вы применили условное форматирование, чтобы установить цвет заливки ячеек на основе их значений, и теперь вы хотите подсчитать количество ячеек определенного цвета или сумму значений в них, то у меня плохие новости для вы — не существует универсальной пользовательской функции, которая будет суммировать по цвету или подсчитывать количество ячеек и возвращать результат в определенные ячейки. По крайней мере, я не слышал о таких функциях, что жаль
Конечно, вы можете найти массу кода 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
В результате вы увидите следующее сообщение:
В этом примере мы выбрали столбец Qty и получили следующие числа:
