Excel. Подсчет числа ячеек, содержащих текст определенного цвета

Довольно часто пользователи «раскрашивают» ячейки в разные цвета. Если потом возникает необходимость просуммировать значения в выделенных ячейках, то, к сожалению, у Excel`я нет такой стандартной функции. Я написал небольшой код, позволяющий легко решить эту задачу при помощи пользовательской функции СумЦвет. Недавно у меня возникла похожая задача: требовалось определить число ячеек, цвет текста в которых был красным.

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

Скачать статью в формате Word2007 Количество по цвету текста

Для подсчета числа ячеек в диапазоне, содержащих текст эталонного цвета я написал пользовательскую функцию КолЦветТст (количество по цвету текста). Вот её код:

Function КолЦветТст(диапазон As Range, критерий As Range) As Long

Application.Volatile True

Dim i As Range

For Each i In диапазон

If i.Font.ColorIndex = критерий.Font.ColorIndex And i <> "" Then

КолЦветТст = КолЦветТст + 1

End If

Next

End Function

Скачайте пример в формате Excel2007, чтобы посмотреть, как работает функция Количество по цвету текста

Несколько слов о том, как работает код:

Function КолЦветТст(диапазон As Range, критерий As Range) As Long / Задает пользовательскую функция под названием КолЦветТст с двумя параметрами: диапазоном и критерием – ячейкой, по цвету текста в которой определяется, какие ячейки учитываются.

Application.Volatile True / Заставит нашу функцию пересчитываться при любом изменении значения в любой из ячеек на листе

Dim i As Range / Определяет переменную i как диапазон ячеек

Далее следует цикл:

For Each i In диапазон / Для всех ячеек из выбранного диапазона

If i.Font.ColorIndex = критерий.Font.ColorIndex And i <> "" Then / Если цвет текста ячейки совпадает с критерием и ячейка непустая

КолЦветТст = КолЦветТст + 1 / то учитываем эту ячейку

End If

Next

End Function

Вы создали пользовательскую функцию КолЦветТст, которую можно найти в категории «Определенные пользователем»

Окно мастера функции выглядит так же, как и для стандартной функции Excel

Хочу обратить ваше внимание на две особенности функции КолЦветТст:

• При изменении цвета текста образца или одной из ячеек не происходит автоматический пересчет значения функции КолЦветТст. Для пересчета нажмите F9.
• Функция не работает, если ячейки раскрашены с помощью условного форматирования 🙁 Пока не научился это делать…