Разберем простые способы как посчитать количество, и как суммировать ячейки по цвету в Excel.
Мы часто при работе в Excel окрашиваем ячейки различными цветами для лучшей визуализации данных.
Однако, когда возникает необходимость произвести какие-либо расчеты с обработанными данными мы сталкиваемся с трудностями в связи с малыми возможностями стандартных средств Excel.
Рассмотрим две простые функции, которые дают возможность суммировать ячейки, и посчитать количество выделенных цветом ячеек.
Суммирование ячеек по цвету
Перейдем в редактор VBA, для этого в панели вкладок выбираем Разработчик -> Visual Basic (или нажимаем комбинацию клавиш Alt + F11).
Создаем новый модуль и добавляем в него следующий код (напротив каждой строчки дается пояснение к коду):
|
1 2 3 4 5 6 7 8 9 10 11 |
Public Function СУММЦВЕТ(MyRange As Range, MyCell As Range) As Double Dim Sum As Double 'Ввод переменной Sum для подсчета суммы Sum = 0 'Приравнивание переменной Sum к нулю Application.Volatile True 'Пересчет функции при каком-либо изменении значений ячеек листа For Each cell In MyRange 'Цикл по всем ячейкам диапазона If cell.Interior.Color = MyCell.Interior.Color Then 'Проверка текущей ячейки на условие по цвету Sum = Sum + cell.Value 'Значение текущей ячейки прибавляется к промежуточной сумме End If Next СУММЦВЕТ = Sum 'Приравнивание возвращаемому результату значения конечной суммы End Function |
Функция СУММЦВЕТ содержит два аргумента:
- MyRange (обязательный аргумент) — диапазон ячеек для суммирования;
- MyCell (обязательный аргумент) — ячейка, по цвету заливки которой рассчитывается сумма.
Функция СУММЦВЕТ теперь будет определяться при вводе формулы в ячейку, переходим из редактора на лист Excel и воспользуемся новой функцией:
При этом, если выбранная ячейка не имеет заливки, то функция суммы ячеек по выбранному цвету также будет работать.
Подсчет количества ячеек по цвету
Чтобы посчитать ячейки одного цвета достаточно немного видоизменить функцию для подсчета суммы — вместо прибавления значения текущей ячейки (Sum = Sum + cell.Value) мы добавляем 1 (Sum = Sum + 1).
Замечания
При работе с данными функциями обратите внимание на два важных момента:
- Если цвет выбранной ячейки определяется с помощью условного форматирования (т.е. цвет ячейки определяется не за счет заливки), то рассмотренные функции не сработают.
- В случае изменения раскраски ячейки в Excel формулы автоматически не пересчитываются, так как не изменяется содержимое ячейки, поэтому для корректного расчета необходимо произвести пересчет формул. Комбинация клавиш Shift + F9 пересчитает формулы на активном листе (F9 — для всей книги).
Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!
не работает с формулами. То бишь в ячейке результат формулы этой ячейки. Из-за этого, данная функция не определяет в ней число, по всей видимости.
А можете более подробно описать ошибку?
В чем конкретно проблема?
Громадное, спасибо, всё работает.
НА mac работает на ура но в windows даже после разбора с кириллицей бред, при редактировании формулы ссылается верно на ячейки но считать отказывается выделяет желтым первую строчку и просит выбрать библиотеку.
(Public Function SUMMCVET(MyRange As Range, MyCell As Range) As Double)
на изменение названия не гляди это устранения проблемы с кириллицей двух ОС, каманду СУММЦВЕТ везде заменил на эту.
Супер, все работает!!! Все никак не могу научится самому писать макросы, то времени нет, то лень(( Спасибо.