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


Трюк №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

Рис. 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).

16.02.20137890

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




Читайте ранее:
Трюк №87. Как при помощи макросов помечать и сбрасывать пометку ячеек при выделении

Иногда бывает трудно делать выбор при помощи флажков. К счастью, этот процесс можно упростить, написав немного кода.

Закрыть