Трюк №88. Подсчет или суммирование ячеек таблицы Excel с определенным цветом заливки при помощи кода VBA

При помощи небольшого фрагмента кода можно легко применять функции СУММ (SUM) и СЧЁТ (COUNT) к ячейкам, цвет заливки которых был указан вручную.

Очень часто бывает удобно суммировать или подсчитывать ячейки с определенным цветом заливки, который вы или другой пользователь выбрали вручную, так как пользователям зачастую удобнее работать с цветовыми заливками, чем с именованными диапазонами. Чтобы сделать это, сначала откройте рабочую книгу, в которой хотите суммировать или подсчитать ячейки в зависимости от цвета заливки. Перейдите в VBE, выбрав команду на ленте Разработчик > Редактор Visual Basic (Developer > Visual Basic Editor) (сочетание клавиш Alt/Option+Fll), и выберите команду Insert > Module, чтобы вставить стандартный модуль. В этом модуле введите код из листинга 7.13.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

// Листинг 7.13
Function ColorFunction(rColor As Range. rRange As Range, Optional SUM _ As Boolean)
 
Dim rCell As Range
Dim ICol As Long
Dim vResult
 
lCol = rColor.Interior.Colorlndex
 
	If SUM = True Then
		For Each rCell In rRange
			If rCell.Interior.Colorlndex = ICol Then
				vResult = WorksheetFunction.SUM(rCell) + vResult
			End If
		Next rCell
	Else
		For Each rCell In rRange
		If rCell.Interior.Colorlndex = lCol Then
			vResult = 1 + vResult
		End If
	Next rCell
End If
 
ColorFunction = vResult
End Function

Теперь при помощи пользовательской функции ColorFunction можно суммировать значения в ячейках диапазона $А$1:$А$12, имеющих такой же цвет заливки, что и ячейка $С$1: =ColorFunction($C$1;$A$l:$A$12;TRUE), в русской версии Excel =ColorFunction($C$1;$А$1:$А$12;ИСТИНА). Эта функция будет выполнять суммирование, так как в качестве последнего аргумента пользовательской функции указано ИСТИНА (TRUE).

Рис. 7.5. Подсчет ячеек по цвету при помощи пользовательской функции ColorFunction

Чтобы подсчитать ячейки с такой же цветовой заливкой, что и ячейка $С$1, можно воспользоваться функцией =ColorFunct1on($С$1;$А$1:$А$12;FALSE), в русской версии Excel =ColorFunction($C$1;$A$l:$A$12;ЛОЖЬ) или =ColorFunction($C$1;$А$1:$А$12).

Так как последний аргумент не указан, функция по умолчанию использует значение ЛОЖЬ (FALSE) в качестве последнего аргумента. Теперь можно легко суммировать и подсчитывать ячейки с указанным цветом заливки (рис. 7.5).