Как в Microsoft Excel суммировать ячейки по цвету

Написал admin. Опубликовано в рубрике Офисные приложения. 20.01.2014

В Microsoft Excel, при создании таблиц, очень удобно и наглядно помечать ячейки цветом. Но что делать, если нам понадобиться суммировать значения ячеек одного цвета? По умолчанию, данной функции в Excel до сих пор нет. Решить эту проблему можно написав макрос в Visual Basic.

Для этого в Excel версии 2007-2013 переходим на вкладку Разработчик и нажимаем Visual Basic.

Если такой вкладки нет, то включаем ее следующим образом: Файл/Параметры/Настройка ленты и ставим галочку напротив слова Разработчик.

В открывшемся окне редактора нажимаем Insert/Module и копируем туда следующий текст:

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. Надеюсь, она пригодится Вам при составлении отчетов.

Оцените статью 1 Балл

(19 проголосовало, средняя оценка: 4,79 из 5)

Загрузка...

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

Анастасия

08.01.2015 в 17:31 | #

Спасибо! работает) и благодарю за содействие при споре. я выиграла золотое колечко )

Reply
Виктор

12.02.2015 в 16:08 | #

Спасибо, но…макрос работает если вручную были отмечены ячейки цветами. В случае использования условного форматирования данный макрос не работает по цветам, а считает всю сумму в выбранном диапазоне, не зависимо от цвета ячейки присвоенного условиями форматирования.

Reply
DONNIEL

21.03.2015 в 14:28 | #

Не получается. После вставки выплывает окно: у данной функции нет аргументов. Как быть?

Reply
Анна

23.04.2015 в 13:04 | #

Спасибо большое!!! Очень Очень помогли)) все ясно и подробно)

Reply
Евгений

29.04.2015 в 13:58 | #

А как сделать, чтобы он не суммировал то, что находится в ячейках, а добавлял единичку, если ячейка закрашена данным цветом? Заранее спасибо.

Reply
Иван

06.07.2015 в 22:12 | #

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

Reply
Олеся

04.08.2015 в 17:05 | #

Благодарю! Очень помогли!

Reply
Светлнала

27.09.2015 в 20:54 | #

Спасибо огромное!!!

Reply
иван

09.10.2015 в 00:53 | #

вопрос, а как посчитать количество ячеек с одним цветом шрифта?
Спасибо

Reply
- Николай
  
  13.03.2018 в 20:03 | #
  
  Чтобы подсчитать только лколичество ячеек определенного цвета, надо в программе заменить «cell.Value» на «1»
  
  Reply
Макс Ф.

22.01.2016 в 17:16 | #

Спасибо Человек! Очень выручил при подготовке отчета.

Reply
Дмитрий

20.02.2016 в 15:46 | #

Ребят, спасибо. Все работает. Вопрос, а можно сделать чтобы считалось если используешь фильтр? В смысле…выделил 100 ячеек..разукрасил по цветам. Но нужно посчитать допустим первые 25 ячеек.

Reply
Наталия

13.04.2016 в 14:50 | #

Спасибо за функцию 🙂
А как нужно ее изменить, чтобы функция проверяла один диапазон чисел (ячейки с цветом), а считала другой диапазон чисел

Reply
Алексей

31.03.2017 в 00:46 | #

Спасибо большое! Очень нужно было именно такое решение проблемы, и я рад, что наткнулся на данную инструкцию 🙂

Reply
Анжела

10.08.2017 в 23:32 | #

Спасибо за статью, очень полезна. Но она у меня работает не полностью. То есть когда пробовала на новом чистом файле, то все функции работали. Но когда начала делать нужной мне таблице (со всеми заполоненными данными., при написании формулы нет строчки «Определенные пользователем»

Reply
Александр

24.12.2017 в 21:57 | #

Здравствуйте!
Подскажите пожалуйста, почему после работы в экселе, сохраняю данные, закрываю файл, потом снова открываю а формулы нет и в помине, приходится всё делать заново и при нажатии F9 всё считает но сохранить не даёт.

Reply
Кристина

28.05.2018 в 15:24 | #

Супер! все работает. Отлично ускорило время обработки документов. От души — СПАСИБО!

Reply
Таня

05.06.2018 в 15:27 | #

Добрый день! А как подсчитать сумму значений, выделенных одним цветом?

Reply
Елена

23.10.2018 в 17:34 | #

Спасибо, очень удобно!

Reply
Александр

10.10.2019 в 14:51 | #

Добрый день!!
Как сделать чтоб считались в диапазоне выделения только количество ячеек одним цветом, а не содержимое ячейки??
Спасибо

Reply

Как в Microsoft Excel суммировать ячейки по цвету

По этой теме также читают:

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

Оставить комментарий