Количество и сумма ячеек по цвету в 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!

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

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

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