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

Рубрика: 7. Полезняшки 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.
  • Функция не работает, если ячейки раскрашены с помощью условного форматирования 🙁 Пока не научился это делать…

Комментарии: 10 комментариев

Спасибо! Хорошая функция, помогла сэкономить кучу времени.

а вы еще не узнали как делать пересчет? очень помогла функция, но хотелось бы с пересчетом без F9

Аня, без F9 не получится… Excel автоматически пересчитывает лист только, если изменилось хоть одно значение… Изменение форматирования не влияет на пересчет листа…

Боже, да ведь это формула моей мечты! Скажите, пожалуйста, а как её изменить, чтобы она работала, если менять цвет не символов/шрифта, а самой ячейки (заливку)?

Антон, на эту тему у меня отдельная заметка Excel. Суммирование по ячейкам, выделенным цветом

Пересчет по итерации
В «Параметрах» поставить галочку против «Итерации» и количество (1 или 2 и т.д.).
Ячейка А1 —— 1 ИЛИ 0 — СБРОС СЧЕТЧИКА
Ячейка А2 (СЧЕТЧИК)—— =ЕСЛИ(А1=0;0;А2+1)
Пересчет клавишами в любой пустой ячейке:
1. «DEL»
2. «Ctrl+C» и «Enter»
3. «Пробел» и»Enter»
Ячейка А3 —- =ОСТАТОК(А2,2)

Функция считает пустые ячейки, как заполненные основным цветом.

Наталья, спасибо за замечание. Изменил одну строку кода
If i.Font.ColorIndex = критерий.Font.ColorIndex And i <> "" Then
Проверьте, должно работать.

Спасибо большое за функцию! Очень помогли!

Добрый день!
А можно ли создать эту формулу без привязки к конкретным ячейкам? Например подсчитывать только те ячейки в которых цвет текста красный или зеленый? А то у меня идет подсчет выходных и рабочих дней календаря, а они каждый месяц в разных ячейках(


Прокомментировать