В этой статье вы узнаете, как посчитать ячейки по цвету и получить сумму по цвету ячеек в Excel. Эти решения работают как для окрашенных вручную, так и с условным форматированием.
Если вы активно используете различные цвета заливки и шрифта на листах Excel, чтобы различать различные типы значений, вам может потребоваться узнать, сколько ячеек выделено определенным цветом. Если значения ваших ячеек являются числами, вы можете автоматически вычислить сумму ячеек, закрашенных одним цветом, например, сумму всех красных ячеек.
Как все мы знаем, Microsoft Excel предоставляет множество формул для разных целей, и было бы логично предположить, что для подсчета ячеек по цвету есть и такие. Но, к сожалению, нет стандартной функции, которая позволяла бы суммировать по цветам или считать по цветам в Excel.
Помимо использования сторонних надстроек, есть только одно возможное решение — использование пользовательских функций. Если вы очень мало знаете об этой технологии или никогда раньше не слышали этот термин, не пугайтесь, вам не придется писать код самостоятельно. Здесь вы найдете готовое решение, и все, что вам нужно сделать, это скопировать / вставить его в свою книгу.
Функции и макросы, которые мы рассмотрим в этой статье, помогут нам сделать следующее:
Как посчитать по цвету и суммировать по цвету на листе Excel
Предположим, у вас есть таблица со списком заказов, в которой ячейки в столбце «Количество» окрашены в зависимости от их значения в колонке «Выполнение» и даты: ячейки с выполняемыми заказами сроком до 30 дней от текущей даты — желтые, уже выполненные — зеленые, а просроченные заказы — красные.
Теперь нам нужно не вручную, а при помощи формул подсчитать ячейки определенного цвета, т.е. посчитать количество красных, зеленых и желтых ячеек в таблице. Как я объяснил выше, прямого решения этой задачи при помощи стандартных формул Excel не существует. Но, к счастью, есть код VBA для Excel. Выполните 5 быстрых шагов, которые перечислены ниже, и вы легко посчитаете количество и сумму ваших цветных ячеек всего за несколько минут.
- Откройте книгу Excel и нажмите
Alt+F11, чтобы открыть редактор Visual Basic (VBE). - Щелкните правой кнопкой мыши имя своей книги в разделе «Project–VBAProject» в правой части экрана, а затем выберите «Вставить» > «Модуль» в контекстном меню.
- Добавьте в вашу рабочую книгу следующий код:
Function GetCellColor(xlRange As Range)
Dim indRow, indColumn As Long
Dim arResults()
Dim colorVal As Variant
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
colorVal = xlRange(indRow, indColumn).Interior.Color
arResults(indRow, indColumn) = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)
Next
Next
GetCellColor = arResults
Else
colorVal = xlRange.Cells(1, 1).Interior.Color
GetCellColor = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)
End If
End Function
Function GetCellFontColor(xlRange As Range)
Dim indRow, indColumn As Long
Dim arResults()
Dim colorVal As Variant
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
colorVal = xlRange(indRow, indColumn).Font.Color
arResults(indRow, indColumn) = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)
Next
Next
GetCellFontColor = arResults
Else
colorVal = xlRange.Cells(1, 1).Font.Color
GetCellFontColor = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)
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- Сохраните свою книгу как «Книга Excel с поддержкой макросов (.xlsm)».
Если вы новичок и вам сложно работать с VBA, вы можете найти подробные пошаговые инструкции и несколько полезных советов в этом руководстве: Как вставить и запустить код VBA в Excel .
- Теперь, когда вся подготовительная работа сделана, выберите ячейку, в которой вы хотите получить результат, и введите в нее только что записанную нами пользовательскую функцию CountCellsByColor:
CountCellsByColor( диапазон ; код цвета )
здесь и далее эти аргументы означают:
диапазон – диапазон ячеек, в которых вы хотите произвести подсчеты по цвету,
код цвета – адрес ячейки-образца, цвет фона или шрифта которой соответствуют искомому цвету фона или шрифта.
В этом примере мы используем формулу, =CountCellsByColor(D2:D21;A24), где D2:D21— это диапазон, в котором вы хотите посчитать количество ячеек с цветом, а A24 — это ячейка, закрашенная нужным нам цветом, красным в нашем случае.
Аналогичным образом вы записываете формулы для других цветов, которые хотите посчитать, желтого и зеленого, в нашей таблице.
Если у вас есть числовые данные в закрашенных цветом ячейках (например, столбец Количество в нашей таблице), вы можете сложить значения на основе определенного цвета, используя аналогичную функцию SumCellsByColor:
SumCellsByColor( диапазон ; код цвета)
Ниже на скриншоте вы видите пример.
Мы использовали формулу суммы по цвету, =SumCellsByColor(D2:D21;A24), где D2:D21 — это диапазон, а A24 — ячейка с образцом цвета.
Аналогичным образом вы можете посчитать выделенные цветом ячейки и суммировать значения таких ячеек по цвету шрифта, используя функции CountCellsByFontColor и SumCellsByFontColor соответственно.
На скриншоте ниже вы видите, как можно подсчитать количество значений, написанных красным цветом.
=CountCellsByFontColor(D2:D21;A24)
Аналогично подсчитываем сумму чисел, имеющих красный или какой-то другой цвет шрифта, при помощи формулы:
=SumCellsByFontColor(D2:D21;A24)
Примечание. Если после применения вышеупомянутого кода VBA вам потребуется раскрасить еще несколько ячеек вручную, то сумма и количество окрашенных ячеек не будут пересчитаны автоматически, чтобы отразить произошедшие изменения. Пожалуйста, не сердитесь на нас, это не ошибка кода :)
На самом деле, это нормальное поведение всех макросов Excel, скриптов VBA и пользовательских функций. Дело в том, что все подобные функции вызываются только при изменении данных рабочего листа. Но Excel не воспринимает изменение цвета шрифта или цвета ячейки как изменение данных. Для него это просто изменение внешнего вида данных, которые на самом деле не меняются.
Итак, после раскрашивания ячеек вручную, просто поместите курсор в любую ячейку и нажмите F2, а затем Enter. То есть, сделайте вид, что изменяете содержимое какой-либо ячейки. Сумма и количество в пользовательской функции тут же будут обновлены. То же самое относится и к другим макросам, которые считают по цвету.
Как суммировать по цвету и сосчитать по цвету во всей рабочей книге
Приведенная ниже пользовательская функция подсчитывает и находит сумму ячеек по цвету заливки на всех листах рабочей книги. Итак, вот ее код:
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Вы используете этот макрос так же, как и предыдущий код, и выводите подсчитанные количество и сумму закрашенных определенным цветом ячеек с помощью следующих формул =WbkCountCellsByColor() и =WbkSumCellsByColor() соответственно.
Единственный аргумент, который нужен этим функциям, это адрес ячейки с нужным цветом.
Просто введите любую из этих формул в любую пустую ячейку на любом листе без указания диапазона, используйте в скобках адрес ячейки нужного цвета, например
=WbkSumCellsByColor(A1)
Формула отобразит сумму всех ячеек на всех листах в вашей рабочей книге, закрашенных тем же цветом, что и A1.
Пользовательские функции для получения цвета ячейки, цвета шрифта и цветового кода
Здесь вы найдете перечень всех функций, которые мы использовали ранее, а также несколько новых, которые извлекают цветовые коды.
Зачем они нам? Но ведь если ваша таблица окрашена не совсем стандартными цветами (например, светло-зеленым), то подобрать образец цвета для функций, которые мы рассматривали выше, будет весьма затруднительно. Если же у вас будет код нужного цвета, вы сможете использовать его при форматировании ячейки-образца, чтобы получить точное соответствие цвета.
Если вы вдруг забыли, как можно вручную раскрасить ячейку в нужный цвет, то напомню. Жмем Ctrl+1, затем Заливка – Другие цвета – Спектр – RGB формат. Вот туда и вставляем полученный код. Точное соответствие цвета будет обеспечено.
Примечание. Помните, что все эти формулы будут работать только в том случае, если вы добавили пользовательскую функцию в книгу Excel, как показано ранее в этой статье.
Функции для подсчета по цвету:
- CountCellsByColor(диапазон; код цвета) – считает ячейки с заданным цветом фона.
В приведенном выше примере мы использовали следующую формулу для подсчета ячеек по цвету
= CountCellsByColor (F2: F14, A17)
где F2: F14 — выбранный диапазон, а A17 — ячейка с нужным цветом фона. Вы можете использовать все остальные формулы, перечисленные ниже, аналогичным образом.
- CountCellsByFontColor(диапазон; код цвета) – подсчитывает ячейки с указанным цветом шрифта.
Формулы для суммирования по цветам:
- SumCellsByColor(диапазон; код цвета) – вычисляет сумму ячеек с определенным цветом фона.
- SumCellsByFontColor(диапазон; код цвета) – вычисляет сумму ячеек с определенным цветом шрифта.
Функции для получения кода цвета ячейки:
- GetCellFontColor(ячейка) – возвращает цветовой код цвета шрифта указанной ячейки.
- GetCellColor(ячейка) – возвращает цветовой код цвета фона указанной ячейки.
Вот примеры использования функции цвета ячейки:
А на этим скриншоте мы получаем цветовой RGB код шрифта.
Как считать по цвету и суммировать ячейки, окрашенные с использованием условного форматирования
Если вы применили условное форматирование к ячейкам на основе их значений и теперь хотите посчитать эти ячейки по цвету или просуммировать значения в закрашенных ячейках, то у меня плохие новости — не существует универсальной определяемой пользователем функции, которая бы суммировала по цвету или пересчитала закрашенные условным форматированием ячейки и вывела бы полученные числа прямо в указанные клетки таблицы. По крайней мере мне о такой функции не известно, увы :(
Конечно, вы можете найти в Интернете тонны кода VBA, который пытается это сделать, но все эти коды (по крайней мере, примеры, с которыми я сталкивался), не обрабатывают условное форматирование, такое как «Форматировать все ячейки на основе их значений», «Форматировать только наибольшие или наименьшие значения», «Форматировать только значения выше или ниже среднего», «Форматировать только уникальные или повторяющиеся значения». Кроме того, почти все эти коды VBA имеют ряд особенностей и ограничений, из-за которых они могут работать некорректно с определенными книгами или типами данных. В общем, вы можете испытать удачу и поискать в Google идеальное решение, и если вы его найдете, пожалуйста, вернитесь и опубликуйте свое открытие здесь!
Но если пользовательская функция не может выполнить эту задачу, то макрос VBA вполне может справиться. О различиях пользовательских функций и макросов VBA вы можете более подробно прочитать в этой статье.
Приведенный ниже макрос VBA преодолевает вышеупомянутые ограничения и работает в электронных таблицах Microsoft Excel со всеми типами условного форматирования. Он отображает количество выделенных определенным цветом ячеек и сумму значений в этих ячейках, независимо от того, какой тип условного форматирования используется на листе.
Sub SumCountByConditionalFormat()
Dim indRefColor As Long
Dim cellCurrent As Object
Dim cntRes As Long
Dim sumRes
Dim cntCells As Long
Dim indCurCell As Long
cntRes = 0
sumRes = 0
Set cellCurrent = Selection
adr = Mid(cellCurrent.Address, InStr(cellCurrent.Address, ",") + 1, 20)
adr1 = Left(adr, 4)
adr2 = Left(cellCurrent.Address, InStr(cellCurrent.Address, ",") - 1)
Range(adr2).Activate
indRefColor = ActiveCell.DisplayFormat.Interior.Color
Range(adr).Activate
cntCells = Selection.CountLarge
Range(adr).Select
Range(adr).Activate
Set cellCurrent = Selection
For indCurCell = 1 To (cntCells - 1)
If indRefColor = cellCurrent(indCurCell).DisplayFormat.Interior.Color Then
cntRes = cntRes + 1
sumRes = WorksheetFunction.Sum(cellCurrent(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Как использовать этот макрос для подсчета цветных ячеек и суммирования их значений.
Опишем процесс пошагово:
- Добавьте приведенный выше код на лист, как описано в первом параграфе статьи .
- Выберите ячейку с нужным цветом.
- Нажмите и удерживайте
Ctrl. - Выберите диапазон, в котором вы хотите подсчитать цветные ячейки и/или суммировать по цвету, если у вас есть числовые данные.
- Отпустите клавишу
Ctrl. - НажНажмите комбинацию
Alt+F8, чтобы открыть список макросов в вашей книге. - Выберите макрос SumCountByConditionalFormat и нажмите «Выполнить» .
Покажем эти действия на скриншотах. Используем пример данных, с которыми мы уже работали в первых параграфах этой статьи. Только теперь они окрашены в столбце В при помощи условного форматирования.
Сначала выбираем ячейку D5, поскольку хотим подсчитать ячейки красного цвета с просроченными заказами.
Затем дополнительно, удерживая Ctrl, выделяем диапазон ячеек в столбце D, по которым нужно выполнить подсчет ячеек определенного цвета.
Выполняем макрос, как показано на скриншоте ниже.
В результате вы увидите следующее сообщение с результатами:
Для этого примера мы выбрали столбец «Количество» и получили следующие цифры:
- Count — это количество ячеек определенного цвета, красного в нашем случае, который отмечает ячейки «Просрочен».
- Sum — это сумма значений всех красных ячеек в выбранной колонке, т.е. общее количество "Просроченных" заказов.
- Color — это шестнадцатеричный код цвета выбранной ячейки, в нашем случае D5.
Таким образом мы можем посчитать сумму и количество по цвету ячеек с условным форматированием.
Самый быстрый способ подсчета и суммирования ячеек по цвету в Excel
Я могу рекомендовать вам надстройку для Excel, которая бы считала и суммировала ячейки по указанному вами цвету или по всем цветам в выбранном диапазоне.
При этом не имеет значения, как установлены эти цвета – прямым форматированием ячейки либо при помощи условного форматирования.
Позвольте представить вам наш совершенно новый инструмент — «Счет и сумма по цвету» для Excel. Он имеет два варианта подсчета — «Один цвет» и «Все цвета», как видно на скриншоте ниже.
Подсчет и суммирование по одному цвету.
Вы нажимаете кнопку « Один цвет » на ленте, и в левой части рабочего листа открывается панель « Подсчет и сумма по цвету» . На панели вы выбираете:
- Диапазон, в котором вы хотите подсчитать и суммировать ячейки
- Любую ячейку с нужным цветом как образец
- Вариант - цвет фона или шрифта
После этого нажмите « Рассчитать » и сразу же увидите результат в нижней части панели! Помимо подсчета и суммы, надстройка вычисляет среднее значение и находит максимальное и минимальное значения. Никаких макросов, никаких формул, никакой боли :)
Обратите внимание, что подсчет возможен как по цвету фона, так и по цвету шрифта.
Подсчет и суммирование ячеек по всем цветам в выбранном диапазоне
Опция «Все цвета» работает в основном так же, за исключением того, что вам не нужно выбирать цвет. В разделе «Result for ..» вы можете выбрать любой из параметров: Количество, Сумма, Среднее, Максимальное или Минимальное значение и другие.
Если вы хотите скопировать результаты на свой рабочий лист, нажмите кнопку «Paste All…» в нижней части панели .
В настоящее время надстройка доступна как часть Ultimate Suite for Excel . Это коллекция отличных инструментов, специально разработанных для решения самых утомительных, кропотливых и подверженных ошибкам задач в Excel.
В дополнение к надстройке «Подсчет и суммирование по цвету», Ultimate Suite включает более 70 инструментов, которые помогут вам объединить данные из разных листов, удалить дубликаты, сравнить листы на совпадения и различия и многое другое.
Надеюсь, теперь сумма по цвету и подсчет ячеек по цвету для вас не будут сложными. Если же будут вопросы – не стесняйтесь задавать их в комментариях.
