Как в Microsoft Excel суммировать ячейки по цвету
В Microsoft Excel, при создании таблиц, очень удобно и наглядно помечать ячейки цветом. Но что делать, если нам понадобиться суммировать значения ячеек одного цвета? По умолчанию, данной функции в Excel до сих пор нет. Решить эту проблему можно написав макрос в Visual Basic.
Для этого в Excel версии 2007-2013 переходим на вкладку Разработчик и нажимаем Visual Basic.
Если такой вкладки нет, то включаем ее следующим образом: Файл/Параметры/Настройка ленты и ставим галочку напротив слова Разработчик.
В открывшемся окне редактора нажимаем Insert/Module и копируем туда следующий текст:
1 2 3 4 5 6 7 8 9 10 11 | Public Function SumByColor(DataRange As Range, ColorSample As Range) As Double Dim Sum As Double Application.Volatile True For Each cell In DataRange If cell.Interior.Color = ColorSample.Interior.Color Then Sum = Sum + cell.Value End If Next cell SumByColor = Sum End Function |
Сохраняем изменения. Далее в Excel на вкладке Формулы выбираем пункт Вставить функцию. Необходимо выбрать категорию Определенные пользователем — тогда отобразится наша функция — SumByColor.
У этой функции 2 аргументы:
DataRange — диапазон ячеек с числами;
ColorSample — ячейка, цвет которой является образцом для суммирования.
Вот и все. Данная функция корректно работает на последних версиях Excel. Надеюсь, она пригодится Вам при составлении отчетов.
Подписывайся на канал MyFirstComp на YouTube!
По этой теме также читают:
Делаем ячейки одинакового размера в Microsoft Excel 2010
Как создать макрос в Excel?
Бесплатные вьюверы от Microsoft Office: Word, Excel, Power Point
Делаем автоматическую нумерацию строк в Microsoft Excel
Как зафиксировать строку в Excel ?
Как сделать сноски в Microsoft Word 2010?
Как поставить пароль на документ в Microsoft Word ?
(19 проголосовало, средняя оценка: 4,79 из 5)
Загрузка...
Анастасия
| #
Спасибо! работает) и благодарю за содействие при споре. я выиграла золотое колечко )
Reply
Виктор
| #
Спасибо, но…макрос работает если вручную были отмечены ячейки цветами. В случае использования условного форматирования данный макрос не работает по цветам, а считает всю сумму в выбранном диапазоне, не зависимо от цвета ячейки присвоенного условиями форматирования.
Reply
DONNIEL
| #
Не получается. После вставки выплывает окно: у данной функции нет аргументов. Как быть?
Reply
Анна
| #
Спасибо большое!!! Очень Очень помогли)) все ясно и подробно)
Reply
Евгений
| #
А как сделать, чтобы он не суммировал то, что находится в ячейках, а добавлял единичку, если ячейка закрашена данным цветом? Заранее спасибо.
Reply
Иван
| #
Подскажите пож. можно ли сделать запрос, поиск, чтобы все ячейки одного цвета из документа выстроились в один столбик, заранее спасибо.
Reply
Олеся
| #
Благодарю! Очень помогли!
Reply
Светлнала
| #
Спасибо огромное!!!
Reply
иван
| #
вопрос, а как посчитать количество ячеек с одним цветом шрифта?
Спасибо
Reply
Николай
| #
Чтобы подсчитать только лколичество ячеек определенного цвета, надо в программе заменить «cell.Value» на «1»
Reply
Макс Ф.
| #
Спасибо Человек! Очень выручил при подготовке отчета.
Reply
Дмитрий
| #
Ребят, спасибо. Все работает. Вопрос, а можно сделать чтобы считалось если используешь фильтр? В смысле…выделил 100 ячеек..разукрасил по цветам. Но нужно посчитать допустим первые 25 ячеек.
Reply
Наталия
| #
Спасибо за функцию 🙂
А как нужно ее изменить, чтобы функция проверяла один диапазон чисел (ячейки с цветом), а считала другой диапазон чисел
Reply
Алексей
| #
Спасибо большое! Очень нужно было именно такое решение проблемы, и я рад, что наткнулся на данную инструкцию 🙂
Reply
Анжела
| #
Спасибо за статью, очень полезна. Но она у меня работает не полностью. То есть когда пробовала на новом чистом файле, то все функции работали. Но когда начала делать нужной мне таблице (со всеми заполоненными данными., при написании формулы нет строчки «Определенные пользователем»
Reply
Александр
| #
Здравствуйте!
Подскажите пожалуйста, почему после работы в экселе, сохраняю данные, закрываю файл, потом снова открываю а формулы нет и в помине, приходится всё делать заново и при нажатии F9 всё считает но сохранить не даёт.
Reply
Кристина
| #
Супер! все работает. Отлично ускорило время обработки документов. От души — СПАСИБО!
Reply
Таня
| #
Добрый день! А как подсчитать сумму значений, выделенных одним цветом?
Reply
Елена
| #
Спасибо, очень удобно!
Reply
Александр
| #
Добрый день!!
Как сделать чтоб считались в диапазоне выделения только количество ячеек одним цветом, а не содержимое ячейки??
Спасибо
Reply