Количество и сумма ячеек по цвету в Excel

Разберем простые способы как посчитать количество, и как суммировать ячейки по цвету в Excel.

Мы часто при работе в Excel окрашиваем ячейки различными цветами для лучшей визуализации данных.
Однако, когда возникает необходимость произвести какие-либо расчеты с обработанными данными мы сталкиваемся с трудностями в связи с малыми возможностями стандартных средств Excel.
Рассмотрим две простые функции, которые дают возможность суммировать ячейки, и посчитать количество выделенных цветом ячеек.

Суммирование ячеек по цвету

Перейдем в редактор VBA, для этого в панели вкладок выбираем Разработчик -> Visual Basic (или нажимаем комбинацию клавиш Alt + F11).
Создаем новый модуль и добавляем в него следующий код (напротив каждой строчки дается пояснение к коду):

Функция СУММЦВЕТ содержит два аргумента:

  • MyRange (обязательный аргумент) — диапазон ячеек для суммирования;
  • MyCell (обязательный аргумент) — ячейка, по цвету заливки которой рассчитывается сумма.

Функция СУММЦВЕТ теперь будет определяться при вводе формулы в ячейку, переходим из редактора на лист Excel и воспользуемся новой функцией:

Функции количества и суммы ячеек по цвету
При этом, если выбранная ячейка не имеет заливки, то функция суммы ячеек по выбранному цвету также будет работать.

Подсчет количества ячеек по цвету

Чтобы посчитать ячейки одного цвета достаточно немного видоизменить функцию для подсчета суммы — вместо прибавления значения текущей ячейки (Sum = Sum + cell.Value) мы добавляем 1 (Sum = Sum + 1).

Замечания

При работе с данными функциями обратите внимание на два важных момента:

  • Если цвет выбранной ячейки определяется с помощью условного форматирования (т.е. цвет ячейки определяется не за счет заливки), то рассмотренные функции не сработают.
  • В случае изменения раскраски ячейки в Excel формулы автоматически не пересчитываются, так как не изменяется содержимое ячейки, поэтому для корректного расчета необходимо произвести пересчет формул. Комбинация клавиш Shift + F9 пересчитает формулы на активном листе (F9 — для всей книги).

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

Поделиться с друзьями:
Поиск по сайту:

Комментарии (5)

  1. Владимир

    не работает с формулами. То бишь в ячейке результат формулы этой ячейки. Из-за этого, данная функция не определяет в ней число, по всей видимости.

    1. tutorexcel Автор поста

      А можете более подробно описать ошибку?
      В чем конкретно проблема?

  2. петр

    НА mac работает на ура но в windows даже после разбора с кириллицей бред, при редактировании формулы ссылается верно на ячейки но считать отказывается выделяет желтым первую строчку и просит выбрать библиотеку.

    (Public Function SUMMCVET(MyRange As Range, MyCell As Range) As Double)

    на изменение названия не гляди это устранения проблемы с кириллицей двух ОС, каманду СУММЦВЕТ везде заменил на эту.

  3. Иван

    Супер, все работает!!! Все никак не могу научится самому писать макросы, то времени нет, то лень(( Спасибо.

Добавить комментарий

Ваш e-mail не будет опубликован.

Читайте ранее:
Функция ДВССЫЛ в Excel

Рассмотрим примеры использования функции ДВССЫЛ в Excel, которая позволяет преобразовывать текст из ячейки в ссылку.

Закрыть