Сумма по цвету ячеек в Excel

Как я уже писал ранее, профессиональная работа в Excel рано или поздно приведет вас к необходимости написания собственных функций. Довольно часто пользователи «раскрашивают» ячейки в разные цвета. Если потом возникает необходимость просуммировать значения в выделенных ячейках, то, к сожалению, в Excel нет такой стандартной функции.

Небольшой код VBA решит ваши проблемы. Для облегчения восприятия последующего материала откройте Excel-файл (он запакован в Zip, так как на сайте размещение файлов, содержащих макросы запрещено).

1. 
   Убедитесь, что среди вкладок на ленте Excel, присутствует «Разработчик»:
   

2. 
   Если такой закладки вы не видите, щелкните на кнопке Office в левом верхнем углу и затем на кнопке «Параметры Excel»:
   

В открывшемся окне «Параметры Excel» перейдите на вкладку «Основные» и поставьте галочку в строке «Показывать меню Разработчик на ленте». Нажмите Ok

3. 
   Создайте на листе Excel диапазон со значениями; несколько ячеек раскрасьте:
   

4. 
   Перейдите на вкладку Разработчик и щелкните на Visual Basic:
   

5. 
   У вас откроется окно VBA, содержащее окно VBAProject:
   

6. 
   Если окна VBAProject нет на экране
   

щелкните на меню View  Project Explorer:

7. 
   Если вы хотите использовать создаваемый код VBA в любом Excel-файле, вам следует его записать в VBAProject, относящийся к Personal.xlsb. Если вы хотите применять код только в одном файле, с которым вы сейчас работаете, сохраните код в VBAProject`е именно этого файла (в нашем случае Сумма по цвету.xlsm):
   

8. 
   Допустим, вы решили, что создаваемая функция будет использоваться в дальнейшем в различных файлах. Щелкните правой кнопкой мыши на VBAProject(Personal.xlsb) и выберите Insert  Module
   

9. 
   Появится окно нового модуля, в которое следует перенести код:
   

Function СумЦвет (диапазон As Range, критерий As Range) As Double

Application.Volatile True

Dim i As Range

For Each i In диапазон

If i.Interior.Color = критерий.Interior.Color Then

СумЦвет = СумЦвет + i.Value

End If

Next

End Function

Номер вашего модуля (у меня он 5) будет зависеть от числа ранее созданных модулей.

Несколько слов о коде:

Function СумЦвет (диапазон As Range, критерий As Range) As Double / Задает пользовательскую функция под названием СумЦвет с двумя параметрами: диапазоном суммирования и критерием – ячейкой, по цвету которой определяется, значения в каких ячейках суммировать.

Application.Volatile True / Заставит нашу функцию пересчитываться при любом изменении значения в любой из ячеек на листе

Dim i As Range / Определяет переменную i как диапазон ячеек

Далее следует цикл:

For Each i In диапазон / Для всех ячеек из выбранного диапазона

If i.Interior.Color = критерий.Interior.Color Then / Если цвет ячейки совпадает с критерием

СумЦвет = СумЦвет + i.Value / то добавляем значение, хранящееся в ячейке в сумму

End If

Next

10. 
    Вы создали пользовательскую функцию СумЦвет, которую можно найти в категории «Определенные пользователем»
    

11. 
    Окно мастера функции выглядит также, как и для стандартной функции Excel
    

Хочу обратить ваше внимание на две особенности функции СумЦвет:

• 
  При изменении значения в одной из ячеек происходит автоматический пересчет значения функции СумЦвет. Если вы поменяли только цвет ячейки, автоматический пересчет не произойдет. Нажмите F9.
  
• 
  Функция не работает, если ячейки раскрашены с помощью условного форматирования  См. Excel. Подсчет и суммирование ячеек, отвечающих критерию условного форматирования
  

На основании комментариев, появившихся после первой публикации заметки, добавил в Excel-файл код функций:

• 
  КолЦвет – определяет число ячеек выделенного цвета
  
• 
  СумНеЦвет – определяет сумму значений в ячейках выделенных любым цветом (не белого цвета)
  
• 
  КолНеЦвет – определяет число выделенных ячеек (не белых)