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

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


Приветствую всех, дорогие читатели блога TutorExcel.Ru.

Мы часто при работе в 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!

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

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

  1. Владимир

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

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

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

  2. петр

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

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

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

  3. Иван

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

  4. Маке

    добрый день. у меня такая проблема. создал модуль, вставил формулу (сумма ячеек по цвету), ввел её в ячейку, всё работает прекрасно. создаю второй модуль, вставляю другую формулу (количество ячеек по цвету) и всё, ни одна формула не работает. при попытке вставить формулу выдаёт ошибку — у данной функции нет аргументов.

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

      Такого по идее быть не должно. Их без проблем можно поместить в один модуль (как в файле-примере).
      Но если все же нужно поместить их в разные модули, то перепроверьте, что функции в разных модулях не дублируются.
      Если это никак не поможет, то можете прислать мне файл на почту — смогу посмотреть.

  5. Галина

    Огромное спасибо за такое решение проблемы. Все работает отлично. Подскажите пожалуйста, как загрузить макрос в рабочую книгу макросов для дальнейшей работы с файлами?

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

      Галина, если вкратце, то нужно добавить макрос в личную книгу макросов.
      Если ее еще не создавали, то макрорекордером запишите новый макрос (можно пустой, главное просто создать) и при его создании укажите место сохранения «Личная книга макросов».
      Теперь в VBA появится новый файл «Personal.xlsb» (как раз это и есть личная книга макросов), в модуль которого и можно добавлять ваши макросы (и использовать в дальнейшем при работе с любыми файлами Excel).

  6. Сергей

    Подскажите куда в макрос надо тыкнуть, чтоб считал ячейки определенного цвета выше нуля???

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

      Можно добавить проверку на значение суммируемой ячейки, т.е. вместо «If cell.Interior.Color = MyCell.Interior.Color» в макросе пишем «If cell.Interior.Color = MyCell.Interior.Color And cell.Value > 0», в этом случае будут считаться только положительные ячейки (>0).

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

Ваш адрес email не будет опубликован.

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

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

Закрыть