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

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

Функции для суммирования значений по цвету ячеек в EXCEL не существует (по крайней мере, в EXCEL 2016 и в более ранних версиях). Вероятно, подавляющему большинству пользователей это не требуется.

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

Необходимо сложить значения ячеек в зависимости от цвета фона. Основная задача: Как нам «объяснить» функции сложения, что нужно складывать значения, например, только зеленых ячеек?

Это можно сделать разными способами, приведем 3 из них: с помощью

Автофильтра

, Макрофункции ПОЛУЧИТЬ.ЯЧЕЙКУ() и VBA.

С помощью Автофильтра (ручной метод)

• Добавьте справа еще один столбец с заголовком
  
  Код цвета
  
  .
• Выделите заголовки и нажмите CTRL+SHIFT+L, т.е. вызовите
  
  Автофильтр
  
  (

  подробнее здесь

  )

• Вызовите меню
  
  Автофильтра
  
  , выберите зеленый цвет

• Будут отображены только строки с зелеными ячейками
• Введите напротив каждого «зеленого» значения число 1

• Сделайте тоже для всех цветов

Введите формулу

=СУММЕСЛИ(B7:B17;E7;A7:A17)

как показано в

файле примера (лист Фильтр)

.

Для

подсчета значений

используйте функцию

СЧЕТЕСЛИ()

.

С помощью Макрофункции ПОЛУЧИТЬ.ЯЧЕЙКУ()

Сразу предупрежу, что начинающему пользователю EXCEL будет сложно разобраться с этим и следующим разделом.

Идея заключается в том, чтобы автоматически вывести в соседнем столбце числовой код фона ячейки (в MS EXCEL все цвета имеют соответствующий числовой код). Для этого нам потребуется функция, которая может вернуть этот код. Ни одна обычная функция этого не умеет. Используем макрофункцию ПОЛУЧИТЬ.ЯЧЕЙКУ(), которая возвращает код цвета заливки ячейки (она может много, но нам потребуется только это ее свойство).

Примечание:

Макрофункции — это набор функций к EXCEL 4-й версии, которые нельзя напрямую использовать на листе EXCEL современных версий, а можно использовать только в качестве

Именованной формулы

. Макрофункции — промежуточный вариант между обычными функциями и функциями VBA. Для работы с этими функциями требуется сохранить файл в формате с макросами *.xlsm

• Сделайте активной ячейку
  
  В7
  
  (это важно, т.к. мы будем использовать

  относительную адресацию

  в формуле)

• В

  Диспетчере имен

  введите формулу =ПОЛУЧИТЬ.ЯЧЕЙКУ(63;Макрофункция!A7)

• Назовите ее Цвет

• Закройте Диспетчер имен
• Введите в ячейку
  
  В7
  
  формулу =Цвет и скопируйте ее вниз.

Сложение значений организовано так же как и в предыдущем разделе.

Макрофункция работает кривовато:

• если вы измените цвет ячейки, то макрофункция не обновит значения кода (для этого нужно опять скопировать формулу из
  
  В7
  
  вниз или выделить ячейку, нажать клавишу
  
  F2
  
  и затем
  
  ENTER
  
  )
• функция возвращает только 56 цветов (так называемая палитра EXCEL), т.е. если цвета близки, например, зеленый и светло зеленый, то коды этих цветов могут совпасть. Подробнее об этом
  
  см. лист файла примера Colors
  
  . Как следствие, будут сложены значения из ячеек с разными цветами.

С помощью VBA

В

файле примера на листе VBA

приведено решение с помощью VBA. Решений может быть множество:

• можно создать кнопку, после нажатия она будет вводить код цвета в соседний столбец (реализован этот вариант).
• можно написать пользовательскую функцию, которая будет автоматически обновлять код цвета при изменении цвета ячейки (реализовать несколько сложнее);
• можно написать программу, которая будет анализировать диапазон цветных ячеек, определять количество различных цветов, вычислять в отдельном диапазоне суммы для каждого цвета (реализовать не сложно, но у каждого пользователя свои требования: ячейки с суммами должны быть в определенном месте, необходимо учесть возможность дополнения диапазона новыми значениями и пр.).

Вычисления по цвету ячеек

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

Чтобы исправить этот существенный недостаток можно использовать несложные пользовательские макрофункции на Visual Basic, которые позволят нам суммировать/подсчитывать количество/среднее арифметическое ячеек с определенным цветом заливки/шрифта.

На вкладке Разработчик (Developer) нажмите кнопку Visual Basic или сочетание клавиш Alt+F11, чтобы открыть редактор макросов. Если такой вкладки у вас не видно, то включите ее в настройках Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon).

В окне редактора вставьте новый модуль через меню Insert — Module и скопируйте туда текст следующих функций:

Function CountByColor(DataRange As Range, ColorSample As Range) As Long
    Dim cell As Range, n As Long
    
    For Each cell In DataRange
        If cell.Interior.Color = ColorSample.Interior.Color Then n = n + 1
    Next cell
    CountByColor = n
End Function

Function SumByColor(DataRange As Range, ColorSample As Range) As Double
    Dim cell As Range, total As Double
    
    For Each cell In DataRange
        If IsNumeric(cell) And cell.Interior.Color = ColorSample.Interior.Color Then total = total + cell.Value
    Next cell
    SumByColor = total
End Function

Function AverageByColor(DataRange As Range, ColorSample As Range) As Double
    Dim cell As Range, total As Double, n As Long
    
    For Each cell In DataRange
        If IsNumeric(cell) And cell.Interior.Color = ColorSample.Interior.Color Then
            total = total + cell.Value
            n = n + 1
        End If
    Next cell
    AverageByColor = total / n
End Function
 

Как легко сообразить, первая функция здесь вычисляет количество ячеек с заданным цветом заливки, вторая — сумму, а третья — среднее арифметическое. У всех функций два аргумента:

• DataRange — диапазон исходных ячеек с числами, раскрашенных в разные цвета
• ColorSample — ячейка, цвет заливки которой мы берём за образец

Если теперь вернуться в Excel, то в Мастере функций (вкладка Формулы — кнопка Вставить функцию) в появившейся там категории Определенные пользователем (User Defined) можно найти наши функции и вставить их на лист. Либо напрямую ввести их в строку формул, как любые другие функции Excel:

Добавление условий

Аналогичный подход можно легко масштабировать, добавляя, при необходимости, дополнительные условия в проверку (команда if … then…). Так, например, если нам нужно при вычислении среднего арифметического учитывать не только цвет заливки, но и цвет шрифта (т.е. считать не просто жёлтые, а именно жёлто-красные ячейки), то код нашей макро-функции будет выглядеть так:

 Function AverageByColor2(DataRange As Range, ColorSample As Range) As Double
    Dim cell As Range, total As Double, n As Long
    
    For Each cell In DataRange
        If IsNumeric(cell) And cell.Interior.Color = ColorSample.Interior.Color And cell.Font.Color = ColorSample.Font.Color Then
            total = total + cell.Value
            n = n + 1
        End If
    Next cell
    AverageByColor2 = total / n
End Function

Разница только в добавленном через логическую связку «И» (and) условии на проверку соответствия цвета шрифта очередной проверяемой ячейки cell.Font.Color цвету шрифта ячейки-образца ColorSample.Font.Color.

Ограничения и нюансы пересчёта

У созданных нами макрофункций есть 2 важных нюанса.

Во-первых, эти функции «не видят» заливку, созданную с помощью условного форматирования, т.е. работают только с цветом, который был задан для ячеек вручную.

Во-вторых, к сожалению, изменение цвета заливки или цвета шрифта ячейки Excel не считает изменением её содержимого, поэтому не запускает пересчет формул. То есть при перекрашивании исходных ячеек с числами в другие цвета итоговая сумма/среднее/количество по нашим функциям автоматически пересчитываться пересчитываться не будет.

Полностью решить эту проблему невозможно, но есть несколько способов её обойти:

• Сделать двойной щелчок левой кнопкой мыши по ячейки с нашей формулой и нажать на Enter, т.е. имитировать повторный ввод функции в ячейку — Excel её заново пересчитает и выдаст обновленный результат.
• Можно использовать сочетание клавиш Ctrl+Alt+F9, которое принудительно заставит Excel пересчитать всё формулы и функции независимо от того, изменились ли для них исходные данные. Но это сочетание нужно будет не забывать нажимать каждый раз при изменении исходных данных.
• Добавить в код наших макрофункций (в любое место) команду Application.Volatile True. Эта команда языка Visual Basic заставляет Excel пересчитывать результаты нашей функции при изменении любой ячейки на листе (или по нажатию  F9). Однако, в этом случае, скорость работы наших функций заметно снизится, т.к. их пересчёт будет происходить постоянно — даже когда мы не меняли цвета или значения ячеек в исходных данных. Пользуйтесь этим методом осторожно.

И помните о том, что наша функция перебирает все (и пустые тоже) ячейки в диапазоне DataRange и не задавайте в качестве первого аргумента целый столбец — «думать» будет долго

Ссылки по теме

• Сортировка строк по цвету
• Функции подсчета количества и суммы ячеек по цвету из надстройки PLEX
• Что такое «ад условного форматирования» и как его победить

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

• Подсчет и сумма ячеек на основе определенного цвета заливки с помощью фильтра и промежуточного итога
• Подсчет и суммирование ячеек на основе определенного цвета заливки функцией GET.CELL
• Подсчет и суммирование ячеек на основе определенного цвета заливки с помощью функции, определяемой пользователем
• Подсчет и сумма ячеек на основе определенного цвета заливки с помощью функций Kutools
• Подсчет и суммирование ячеек на основе определенного цвета заливки (или цвета условного форматирования) с Kutools for Excel

Подсчет и сумма цветных ячеек с помощью фильтра и ПРОМЕЖУТОЧНОГО ИТОГА

Подсчет или сумма цветных ячеек с помощью функции GET.CELL

Подсчет и суммирование ячеек на основе определенного цвета заливки с помощью функции, определяемой пользователем

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

Подсчет и сумма ячеек на основе определенного цвета заливки с помощью функций Kutools

Подсчет и суммирование ячеек на основе определенного цвета заливки с помощью Kutools for Excel

Связанная статья:

Демонстрация: подсчет и суммирование ячеек на основе фона, цвет условного форматирования:

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

​Смотрите также​ ячеек с определенным​ посчитать сумму ячеек​ Некоторые из них​- диапазон раскрашенных​ таблице возникает необходимость​В7​ помощью Автофильтра, Макрофункции​ которым выделены ячейки​ indRefColor As Long​ функции, которая будет​ показано ранее в​ определённого цвета на​ используя аналогичную функцию​ Each cellCurrent In​

​ To xlRange.Rows.Count For​ ячейкам применены правила​Из этой статьи Вы​ форматом;​ выделенных цветом​ решаю сама, а​ ячеек с числами​ сделать отчет. И​формулу =Цвет и​ ПОЛУЧИТЬ.ЯЧЕЙКУ() и VBA. ​ со значением​ Dim cellCurrent As​ по цвету суммировать​ этой статье.​ всех листах данной​SumCellsByColor​ rData If indRefColor​

​ indColumn = 1​ условного форматирования​ узнаете, как в​Count_CellColor — подсчет​Michael_S​ вот с некоторыми​ColorSample​ если фильтровать и​ скопируйте ее вниз.​Добавьте справа еще один​Past Due​ Range Dim cntRes​ или считать количество​

​CountCellsByColor(диапазон, код_цвета)​ книги. Итак, вот​:​ = cellCurrent.Font.Color Then​ To xlRange.Columns.Count arResults(indRow,​Предположим, у Вас есть​ Excel посчитать количество​ ячеек с определенным​: Смотря как они​ совсем немогу справиться.​- ячейка, цвет​ сортировать по цвету​Сложение значений организовано так​ столбец с заголовком​.​ As Long Dim​ ячеек и выводить​– считает ячейки​ этот код:​SumCellsByColor(диапазон, код_цвета)​ cntRes = cntRes​

• ​ indColumn) = xlRange(indRow,​ таблица заказов компании,​ и сумму ячеек​
  • ​ цветом заливки, включая​ выделены. Если вручную​ Поэтому обнаружив такой​
• ​ которой принимается как​ Excel в последних​ же как и​ Код цвета.​

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

​Sum​ sumRes Dim cntCells​ результат в определённые​ с заданным цветом​​Function WbkCountCellsByColor(cellRefColor As​​Как показано на снимке​ + 1 End​​ indColumn).Interior.Color Next Next​​ в которой ячейки​​ определенного цвета. Этот​​ условное форматирование;​​ — то только​​ замечательный форум, решила​

​ образец для суммирования​ версиях научился, то​ в предыдущем разделе.​Выделите заголовки и нажмите​– это сумма​ As Long Dim​ ячейки. По крайней​ заливки.В примере, рассмотренном​ Range) Dim vWbkRes​ экрана ниже, мы​ If Next cellCurrent​ GetCellColor = arResults​ в столбце​ способ работает как​Cell_Color — для​ макросом, если применялось​ обратиться к Вам​Легко изменить нашу функцию,​ суммировать по цвету​Макрофункция работает кривовато:​ CTRL+SHIFT+L, т.е. вызовите​ значений всех ячеек​ indCurCell As Long​ мере, я не​ выше, мы использовали​

1. ​ Dim wshCurrent As​ использовали формулу:​​ CountCellsByFontColor = cntRes​​ Else GetCellColor =​​Delivery​​ для ячеек, раскрашенных​
2. ​ определения числового кода​ УФ — то​ за помощью.​ чтобы она учитывала​​ до сих пор​​если вы измените цвет​ Автофильтр (подробнее здесь)​ красного цвета в​ cntRes = 0​​ слышал о таких​​ вот такую формулу​​ Worksheet Application.ScreenUpdating =​​=SumCellsByColor(D2:D14,A17)​
3. ​ End Function Function​ xlRange.Interior.Color End If​

​раскрашены в зависимости​ вручную, так и​ цвета заливки;​ по условию УФ​есть образцы цвета​ не цвет заливки​ не умеет.​ ячейки, то макрофункция​Вызовите меню Автофильтра, выберите​ столбце​ sumRes = 0​ функциях, а жаль​ для подсчёта количества​ False Application.Calculation =​где​ SumCellsByFontColor(rData As Range,​ End Function Function​ от их значений:​ для ячеек с​Cell_Font — для​vikttur​ ячеек (В1,В2), в​ фона, а цвет​Чтобы исправить этот существенный​ не обновит значения​ зеленый цвет​Qty.​ cntCells = Selection.CountLarge​Конечно, Вы можете найти​ ячеек по их​ xlCalculationManual vWbkRes =​D2:D14​ cellRefColor As Range)​ GetCellFontColor(xlRange As Range)​Due in X Days​ правилами условного форматирования.​ определения числового кода​: Например, Надстройка для​ таблице ниже необходимо​ шрифта ячейки. Для​ недостаток можно использовать​ кода (для этого​Будут отображены только строки​, то есть общее​ indRefColor = ActiveCell.DisplayFormat.Interior.Color​ тонны кода VBA​ цвету:​ 0 For Each​– диапазон,​ Dim indRefColor As​ Dim indRow, indColumn​– оранжевые,​ Кроме того, Вы​ цвета шрифта.​ суммирования по цвету​ посчитать количество ячеек​ этого в строке​ несложную пользовательскую функцию​ нужно опять скопировать​ с зелеными ячейками​ количество элементов с​ For indCurCell =​ в интернете, который​=CountCellsByColor(F2:F14,A17)​ wshCurrent In Worksheets​A17​ Long Dim cellCurrent​ As Long Dim​Delivered​ научитесь настраивать фильтр​Но как этим​ заливки,шрифта, формату​ определенного цвета по​ 6 просто замените​ на Visual Basic,​ формулу из​Введите напротив каждого «зеленого»​ отметкой​ 1 To (cntCells​ пытается сделать это,​где​ wshCurrent.Activate vWbkRes =​– ячейка с​ As Range Dim​ arResults() Application.Volatile If​– зелёные,​ по нескольким цветам​ воспользоваться не знаю,​Tyron​ определенной строке. только​ свойство​ которая позволит нам​В7 ​ значения число 1​Past Due​ — 1) If​ но все эти​F2:F14​ vWbkRes + CountCellsByColor(wshCurrent.UsedRange,​ образцом цвета.​ sumRes Application.Volatile sumRes​ xlRange Is Nothing​Past Due​ в Excel 2010​ помогите !!!​: С надстройкой получилось​ не с помощью​Interior​ суммировать ячейки с​вниз или выделить ячейку,​Сделайте тоже для всех​

1. ​.​ indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color​​ коды (по крайней​​– это выбранный​ cellRefColor) Next Application.ScreenUpdating​Таким же образом Вы​ = 0 indRefColor​ Then Set xlRange​– красные.​ и 2013.​Или может знает​ подсчитать количество ячеек​ Надстройки MyAddin​на​
2. ​ определенным цветом.​ нажать клавишу​ цветов​Color​ Then cntRes =​ мере, те экземпляры,​​ диапазон,​​ = True Application.Calculation​

   ​ можете посчитать и​

​ = cellRefColor.Cells(1, 1).Font.Color​ = Application.ThisCell End​​Теперь мы хотим автоматически​​Если Вы активно используете​​ кто другой способ​​ по цветам.​Serge_007​Font​Откройте редактор Visual Basic:​​F2​​Введите формулу =СУММЕСЛИ(B7:B17;E7;A7:A17) как показано​– это шестнадцатеричный​ cntRes + 1​

​ которые попадались мне)​A17​ = xlCalculationAutomatic WbkCountCellsByColor​ просуммировать ячейки по​ For Each cellCurrent​ If If xlRange.Count​

​ сосчитать количество ячеек​ разнообразные заливки и​Abram pupkin​​А как сделать​​: Без макросов не​в обеих частях​В Excel 2003 и​и затем​ в файле примера​​ код цвета выделенной​​ sumRes = WorksheetFunction.Sum(Selection(indCurCell),​

​ не обрабатывают правила​

​– это ячейка​ = vWbkRes End​ цвету шрифта при​

​ In rData If​

​ > 1 Then​​ по их цвету,​​ цвет шрифта на​​: Если ячейки закрашивались​​ чтобы при изменении​ получится, формулы не​

​ выражения.​ старше для этого​ENTER​ (лист Фильтр).​ ячейки, в нашем​​ sumRes) End If​​ условного форматирования, такие​​ с нужным цветом​​ Function Function WbkSumCellsByColor(cellRefColor​

​ помощи функций​​ indRefColor = cellCurrent.Font.Color​ ReDim arResults(1 To​ то есть сосчитать​ листах Excel, чтобы​ вручную, тогда только​ цвета ячейки автоматически​ работают с цветом​Если вам нужно подсчитывать​ нужно выбрать в​)​Для подсчета значений  используйте​

​ случае​ Next MsgBox «Count=»​ как:​ заливки.​ As Range) Dim​CountCellsByFontColor​ Then sumRes =​ xlRange.Rows.Count, 1 To​ количество красных, зелёных​ выделять различные типы​ через макросы.​ пересчитывалось и их​julytt​ не сумму покрашенных​ меню​функция возвращает только 56​ функцию СЧЕТЕСЛИ().​D2​​ & cntRes &​​Format all cells based​​Все перечисленные далее формулы​​ vWbkRes Dim wshCurrent​и​ WorksheetFunction.Sum(cellCurrent, sumRes) End​ xlRange.Columns.Count) For indRow​ и оранжевых ячеек​ ячеек или значений,​То что вы​

Считаем сумму и количество ячеек по цвету во всей книге

​ количество?​: вот я и​ определенным цветом ячеек,​Сервис — Макрос -​ цветов (так называемая​Сразу предупрежу, что начинающему​.​ vbCrLf & «Sum=​ on their values​ работают по такому​ As Worksheet Application.ScreenUpdating​SumCellsByFontColor​ If Next cellCurrent​ = 1 To​ на листе. Как​

​ то, скорее всего,​ написали — это​З.Ы. Пример в​ хотеле с макросами,​ а всего лишь​ Редактор Visual Basic​ палитра EXCEL), т.е.​ пользователю EXCEL будет​Если у Вас возникли​ » & sumRes​(Форматировать все ячейки​ же принципу.​ = False Application.Calculation​соответственно.​ SumCellsByFontColor = sumRes​ xlRange.Rows.Count For indColumn​ я уже сказал​ захотите узнать, сколько​ и есть фрагменты​ файле. Подскажите кто​ а вот то​ их количество, то​ (Tools — Macro​ если цвета близки,​ сложно разобраться с​ трудности с добавлением​ & vbCrLf &​ на основании их​CountCellsByFontColor(диапазон, код_цвета)​

​ = xlCalculationManual vWbkRes​Замечание:​ End Function​ = 1 To​ выше, прямого решения​ ячеек выделено определённым​ от макросов.​

​ знает пожалуйста!​
​ что мне удалось​

​ наша функция будет​ — Visual Basic​ например, зеленый и​ этим и следующим​ скриптов в рабочую​ vbCrLf & _​ значений);​– считает ячейки​ = 0 For​Если после применения​​Сохраните рабочую книгу Excel​​ xlRange.Columns.Count arResults(indRow, indColumn)​ этой задачи не​ цветом. Если же​Если ячейки закрашивал​Hugo​

Пользовательские функции для определения кодов цвета заливки ячеек и цвета шрифта

​ нарыть в интеренте​ еще проще. Замените​ Editor)​ светло зеленый, то​ разделом.​ книгу Excel, например,​ «Color=» & Left(«000000»,​Format only top or​

​ с заданным цветом​​ Each wshCurrent In​ выше описанного кода​ в формате​ = xlRange(indRow, indColumn).Font.Color​ существует. Но, к​ в ячейках хранятся​ компьютер ( через​: Никак.​ не работает. если​

Функции, которые считают количество по цвету:

• ​ в ней 7-ю​​В новых версиях Excel​ коды этих цветов​Идея заключается в том,​ ошибки компиляции, не​ 6 — Len(Hex(indRefColor)))​ bottom ranked values​ шрифта.​ Worksheets wshCurrent.Activate vWbkRes​

  ​ VBA Вам вдруг​

  ​.xlsm​​ Next Next GetCellFontColor​​ счастью, в нашей​ числа, то, вероятно,​​ условное форматирование) ,​​Но можно сделать​ возможно окажите помощь​ строку на:​

  ​ 2007-2013 перейти на​ могут совпасть. Подробнее​ чтобы автоматически вывести​

• ​ работающие формулы и​​ & _ Hex(indRefColor)​(Форматировать только первые​SumCellsByColor(диапазон, код_цвета)​

Функции, которые суммируют значения по цвету ячейки:

• ​ = vWbkRes +​​ потребуется раскрасить ещё​(Книга Excel с​ = arResults Else​
• ​ команде есть очень​​ Вы захотите вычислить​ тогда можно воспользоваться​ по событию выделения​

Функции, которые возвращают код цвета:

• ​Serge_007​​Sum = Sum +​ вкладку​ об этом см.​
• ​ в соседнем столбце​​ так далее, Вы​ & vbCrLf, ,​ или последние значения);​

​– вычисляет сумму​ SumCellsByColor(wshCurrent.UsedRange, cellRefColor) Next​ несколько ячеек вручную,​ поддержкой макросов).Если Вы​ GetCellFontColor = xlRange.Font.Color​ умелые и знающие​ сумму всех ячеек​ формулами условного форматирования​ ячейки — хотя​:​ 1​Разработчик (Developer)​ лист файла примера​ числовой код фона​ можете скачать рабочую​ «Count & Sum​Format only values that​ ячеек с заданным​

Как посчитать количество и сумму ячеек по цвету, раскрашенных при помощи условного форматирования

​ Application.ScreenUpdating = True​ сумма и количество​ не слишком уверенно​ End If End​ Excel гуру, и​ с одинаковой заливкой,​ и найти нужную/​ это костыли, и​Quote​К сожалению изменение цвета​и нажать кнопку​ Colors. Как следствие,​ ячейки (в MS​ книгу Excel с​ by Conditional Format​ are above or​ цветом заливки.​ Application.Calculation = xlCalculationAutomatic​ ячеек не будут​ чувствуете себя с​ Function Function CountCellsByColor(rData​ один из них​

​ например, сумму всех​ сумму.​ ненадёжные, и тормозные​(julytt)200?’200px’:»+(this.scrollHeight+5)+’px’);»>олько не с​ заливки или цвета​ Visual Basic. Если​ будут сложены значения​ EXCEL все цвета​ примерами и с​ color» End Sub​ below average​

• ​SumCellsByFontColor(диапазон, код_цвета)​ WbkSumCellsByColor = vWbkRes​​ пересчитаны автоматически после​ VBA, то посмотрите​ As Range, cellRefColor​
• ​ написал безупречный код​ красных ячеек.​​Если цветные ячейки​ в целом.​
• ​ помощью Надстройки MyAddin​ шрифта ячейки Excel​ такой вкладки у​​ из ячеек с​ имеют соответствующий числовой​ готовыми к использованию​
• ​Добавьте код, приведённый выше,​(Форматировать только значения,​​– вычисляет сумму​ End Function​

​ этих изменений. Не​ подробную пошаговую инструкцию​ As Range) As​ для Excel 2010​Как известно, Microsoft Excel​ имеют какую-нибудь периодичность​Ставьте подсчёт на​Вы сами себе​ не считает изменением​ вас не видно,​ разными цветами. ​ код). Для этого​ функциями​ на Ваш лист,​ которые находятся выше​ ячеек с заданным​Добавьте этот макрос точно​ ругайте нас, это​

​ и массу полезных​ Long Dim indRefColor​ и 2013. Итак,​ предоставляет набор функций​ ( напр каждая​ кнопку.​ противоречите​ ее содержимого, поэтому​ то включите ее​В файле примера на​ нам потребуется функция,​CountCellsByColor​ как мы делали​ или ниже среднего);​ цветом шрифта.​ также, как и​

​ не погрешности кода​ советов в учебнике​ As Long Dim​ выполните 5 простых​ для различных целей,​ четвертая имеет синий​P.S. И кстати​Вот аналогичная тема:​ не запускает пересчет​ в настройках​ листе VBA приведено​ которая может вернуть​и​ это в первом​Format only unique or​GetCellFontColor(ячейка)​ предыдущий код. Чтобы​На самом деле, это​ Как вставить и​ cellCurrent As Range​ шагов, описанных далее,​ и логично предположить,​ цвет, а все​ у Вас в​Народ подскажите как​ формул. То есть​Файл — Параметры -​ решение с помощью​ этот код. Ни​SumCellsByColor​ примере.​ duplicate values​– возвращает код​

Как использовать код, чтобы посчитать количество цветных ячеек и просуммировать их значения

1. ​ получить количество и​ нормальное поведение макросов​ запустить код VBA​ Dim cntRes As​ и через несколько​
2. ​ что существуют формулы​ красные заполнялись только​ формуле баксов не​ посчитать сумму ячеек​ при перекрашивании исходных​ Настройка ленты (File​ VBA. Решений может​
3. ​ одна обычная функция​​, и испытать их​​Выберите диапазон (или диапазоны),​(Форматировать только уникальные​ цвета шрифта в​​ сумму цветных ячеек​​ в Excel, скриптов​
4. ​ в Excel.​​ Long Application.Volatile cntRes​​ минут Вы узнаете​ для подсчёта ячеек​ по четным дням​
5. ​ хватает — диапазон​​ выделенных цветом​​ ячеек с числами​​ — Options -​​ быть множество:​​ этого не умеет.​​ на своих данных.​ в которых нужно​

   

​ или повторяющиеся значения).​ выбранной ячейке.​​ используйте вот такие​​ VBA и пользовательских​Когда все закулисные действия​

• ​ = 0 indRefColor​​ количество и сумму​ по цвету. Но,​ календаря) то это​ сдвинулся, осторожно, может​Вот тема с​ в другие цвета​​ Customize Ribbon)​​можно создать кнопку, после​
• ​ Используем макрофункцию ПОЛУЧИТЬ.ЯЧЕЙКУ(),​​Урок подготовлен для Вас​ сосчитать цветные ячейки​Кроме того, практически все​GetCellColor(ячейка)​​ формулы:​​ функций (UDF). Дело​ будут выполнены, выберите​ = cellRefColor.Cells(1, 1).Interior.Color​​ ячеек нужного цвета.​​ к сожалению, не​
• ​ тоже реально посчитать.​​ наврать!​ форума Microsoft:​ итоговая сумма по​В окне редактора вставьте​​ нажатия она будет​​ которая возвращает код​

Рабочая книга с примерами для скачивания

​ командой сайта office-guru.ru​ или просуммировать по​ эти коды VBA​– возвращает код​=WbkCountCellsByColor()​ в том, что​ ячейки, в которые​ For Each cellCurrent​Откройте книгу Excel и​ существует формулы, которая​ Но для этого​Tyron​​Суммирование по ячейкам,​​ нашей функции пересчитываться​​ новый модуль через​​ вводить код цвета​ цвета заливки ячейки​

​Источник: https://www.ablebits.com/office-addins-blog/2013/12/12/count-sort-by-color-excel/​ цвету, если в​
​ имеют целый ряд​
​ цвета заливки в​

​=WbkSumCellsByColor()​

office-guru.ru

Сложение значений в зависимости от цвета ячеек в MS EXCEL

​ все подобные функции​ нужно вставить результат,​ In rData If​ нажмите​ позволила бы на​

​ надо видеть файл​: Спасибо!​ выделенным цветом​ не будет.​ меню​ в соседний столбец​ (она может много,​Перевел: Антон Андронов​ них содержатся числовые​

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

​ indRefColor = cellCurrent.Interior.Color​Alt+F11​ обычном листе Excel​Файл можно загрузить​Мария​julytt​Полностью решить эту проблему​Insert — Module​

​ (реализован этот вариант).​ но нам потребуется​Автор: Антон Андронов​ данные.​ из-за которых они​

С помощью Автофильтра (ручной метод)

• ​Итак, посчитать количество ячеек​ этих формул в​ данных на листе,​
• ​ них функцию​ Then cntRes =​, чтобы запустить редактор​
• ​ суммировать или считать​ сюда​

• ​: подскажите пожалуйста, могу​: спасибо за подсказку​
• ​ невозможно, но можно​и скопируйте туда​

• ​можно написать пользовательскую функцию,​ только это ее​

​Просуммируем значения ячеек в​Нажмите и удерживайте​ могут не работать​

​ по их цвету​ любую пустую ячейку​

С помощью Макрофункции ПОЛУЧИТЬ.ЯЧЕЙКУ()

​ но Excel не​CountCellsByColor​ cntRes + 1​Visual Basic for Applications​ по цвету.​

​http://files.mail.ru/​ ли сделать так,​ буду читать​ ее существенно облегчить.​ текст вот такой​ которая будет автоматически​ свойство). ​ зависимости от цвета​Ctrl​ корректно с какой-то​ и вычислить сумму​ на любом листе​ расценивает изменение цвета​:​ End If Next​(VBA).​Если не использовать сторонние​Денис​ чтобы в графике,​RAN​

​ Для этого в​​ функции:​ обновлять код цвета​Примечание:​ их заливки. Здесь​, кликните по одной​ конкретной книгой или​ значений в раскрашенных​ Excel. Диапазон указывать​ шрифта или заливки​CountCellsByColor(диапазон, код_цвета)​ cellCurrent CountCellsByColor =​Правой кнопкой мыши кликните​ надстройки, существует только​: условное форматирование, создать​ где проставлены все​: Почему так категорично?​

• ​ третьей строке нашей​​Public Function SumByColor(DataRange​​ при изменении цвета​Макрофункции — это набор​ же покажем, как​ ячейке нужного цвета,​
• ​ типами данных. Так​ ячейках оказалось совсем​
• ​ не нужно, но​

• ​ ячейки как изменение​
• ​В этом примере мы​​ cntRes End Function​​ по имени Вашей​ одно решение –​

​ правило. дальше не​ сроки платежей, считать​julytt​

​ функции используется команда​

• ​ As Range, ColorSample​ ячейки (реализовать несколько​ функций к EXCEL​ подсчитать такие ячейки.​ затем отпустите​ или иначе, Вы​​ не сложно, не​​ необходимо в скобках​ данных. Поэтому, после​​ используем формулу​​ Function SumCellsByColor(rData As​​ рабочей книги в​​ создать пользовательскую функцию​
• ​ помню. смотреть лень.​ только оплаченные, при​: сисадмины неразрешают ничего​Application.Volatile True​ As Range) As​ сложнее);​ 4-й версии, которые​Функции для суммирования значений​Ctrl​ можете попытать счастье​ так ли? Но​ указать любую ячейку​ изменения цвета ячеек​=CountCellsByColor(F2:F14,A17)​

С помощью VBA

​ Range, cellRefColor As​ области​ (UDF). Если Вы​ там ничего сложного​ выделении их цветом.​

• ​ устанавливать​. Она заставляет Excel​ Double Dim Sum​можно написать программу, которая​ нельзя напрямую использовать​
• ​ по цвету ячеек​.​ и google в​ что если Вы​ с заливкой нужного​ вручную, просто поставьте​
• ​, где​ Range) Dim indRefColor​Project – VBAProject​ мало знаете об​ — разберешься​ Т.К., нужна сумма​Serge_007​ пересчитывать результаты нашей​ As Double Application.Volatile​ будет анализировать диапазон​ на листе EXCEL​ в EXCEL не​Нажмите​ поисках идеального решения,​ не раскрашиваете ячейки​ цвета, например,​

excel2.ru

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

​ курсор на любую​F2:F14​ As Long Dim​, которая находится в​ этой технологии или​rasinv​ только оплаченных​: Надстройка — это​ функции при изменении​ True For Each​ цветных ячеек, определять​ современных версий, а​ существует (по крайней​Alt+F8​ и если Вам​ вручную, а предпочитаете​=WbkSumCellsByColor(A1)​

​ ячейку и кликните​– это диапазон,​ cellCurrent As Range​ левой части экрана,​ вообще никогда не​: есть несколько таблиц​Pelena​

​ просто книга Excel,​

• ​ любой ячейки на​ cell In DataRange​ количество различных цветов,​ можно использовать только​​ мере, в EXCEL​, чтобы открыть список​ удастся найти его,​ использовать условное форматирование,​, и формула вернет​
• ​F2​ содержащий раскрашенные ячейки,​ Dim sumRes Application.Volatile​​ далее в появившемся​​ слышали этого термина,​ одной и той​:​ в которой хранятся​ листе (или по​ If cell.Interior.Color =​​ вычислять в отдельном​ в качестве Именованной формулы.​ 2016 и в​ макросов в Вашей​

​ пожалуйста, возвращайтесь и​ как мы делали​ сумму всех ячеек​​, а затем​​ которые Вы хотите​ sumRes = 0​ контекстном меню нажмите​

​ не пугайтесь, Вам​ же длинны и​Мария​ макросы​ нажатию ​ ColorSample.Interior.Color Then Sum​ диапазоне суммы для​ Макрофункции — промежуточный​ более ранних версиях).​ рабочей книге.​ опубликуйте здесь свою​ это в статьях​ в книге, окрашенных​Enter​

​ посчитать. Ячейка​ indRefColor = cellRefColor.Cells(1,​Insert​​ не придётся писать​​ высоты​, читаем Правила форума,​​Как Вы будете​​F9​ = Sum +​ каждого цвета (реализовать​

​ вариант между обычными​

​ Вероятно, подавляющему большинству пользователей​

• ​Выберите макрос​​ находку!​ Как изменить цвет​
• ​ в этот же​​, сумма и количество​A17​ 1).Interior.Color For Each​

Цвет шрифта

​>​ код самостоятельно. Здесь​возможно подсчитать сумму​ создаём свою тему​ использовать ДРУГИЕ макросы,​).​ cell.Value End If​ не сложно, но​​ функциями и функциями​​ это не требуется. ​​Sum​​Код VBA, приведённый ниже,​ заливки ячеек и​

Количество вместо суммы

​ цвет.​ после этого обновятся.​– содержит определённый​ cellCurrent In rData​Module​ Вы найдёте отличный​ в ячейках с​ и прикладываем файл​ если макросы из​

​И помните о том,​ Next cell SumByColor​

Нюансы пересчета

​ у каждого пользователя​ VBA. Для работы​Пусть дан диапазон ячеек​CountByConditionalFormat​ преодолевает все указанные​ Как изменить цвет​Здесь Вы найдёте самые​ Так нужно сделать,​ цвет заливки, в​ If indRefColor =​.​ готовый код (написанный​ определенным цветом?​

​ с примером​ MyAddin ?​ что наша функция​ = Sum End​ свои требования: ячейки​ с этими функциями​​ в столбце А.​​и нажмите​ выше ограничения и​ заливки строки, основываясь​ важные моменты по​ работая с любым​ нашем случае красный.​​ cellCurrent.Interior.Color Then sumRes​​Вставьте на свой лист​

​ нашим гуру Excel),​будет ли сохранятся​Как в эксель сложить​RAN​ перебирает все (и​​ Function​​ с суммами должны​ требуется сохранить файл​ Пользователь выделил цветом​Run​ работает в таблицах​

planetaexcel.ru

Подсчет ячеек определенного цвета

​ на значении ячейки?​​ всем функциям, использованным​
​ макросом, который Вы​Точно таким же образом​ = WorksheetFunction.Sum(cellCurrent, sumRes)​ вот такой код:​ и всё, что​ суммирование при копирование​ ячейки одного цвета?​: Ну, если надстройку​ пустые тоже) ячейки​Если теперь вернуться в​ быть в определенном​
​ в формате с​ ячейки, чтобы разбить​(Выполнить).​ Microsoft Excel 2010​Если Вы применили условное​ нами в этом​ найдёте далее в​ Вы записываете формулу​

​ End If Next​​Function GetCellColor(xlRange As​ Вам потребуется сделать​ таблицы в одном​

​ Нашла в просторах​​ низзя, тогда отдельно​ в диапазоне​ Excel, то в​ месте, необходимо учесть​ макросами *.xlsm​ значения по группам.​В результате Вы увидите​

​ и 2013, с​​ форматирование, чтобы задать​​ примере, а также​​ этой статье.​ для других цветов,​
​ cellCurrent SumCellsByColor =​ Range) Dim indRow,​
​ – это скопировать​
​ листе?​ интернета вот это​ зти макросы здесь.​
​DataRange​ Мастере функций (​
​ возможность дополнения диапазона​Сделайте активной ячейку​

​Необходимо сложить значения ячеек​​ вот такое сообщение:​ любыми типами условного​

​ цвет заливки ячеек​​ пару новых функций,​

​Представленный ниже скрипт Visual​​ которые требуется посчитать​ sumRes End Function​

​ indColumn As Long​​ его и вставить​буду очень признателен​ :​Надстройка для суммирования​
​и не задавайте​Вставка — Функция​ новыми значениями и​В7​

​ в зависимости от​​Для этого примера мы​ форматирования (и снова​ в зависимости от​
​ которые определяют коды​ Basic был написан​ в таблице (жёлтый​

​ Function CountCellsByFontColor(rData As​​ Dim arResults() Application.Volatile​ в свою рабочую​ за помощь​Summ_CellColor — сумирование​

excelworld.ru

Народ подскажите как посчитать сумму ячеек выделенных цветом (Народ подскажите как посчитать сумму ячеек выделенных цветом)

​ по цвету заливки,шрифта,​​ в качестве первого​) в появившейся там​ пр.).​

​(это важно, т.к.​​ цвета фона. Основная​ выбрали столбец​ спасибо нашему гуру!).​ их значений, и​ цветов.​ в ответ на​

​ и зелёный).​​ Range, cellRefColor As​ If xlRange Is​ книгу.​

​skype: rasinv​​ ячеек с определенным​ формату​ аргумента целый столбец​
​ категории​Помечать ячейки цветом, используя​ мы будем использовать​ задача: Как нам​Qty.​
​ В результате он​ теперь хотите посчитать​Замечание:​

​ один из комментариев​​Если в раскрашенных ячейках​
​ Range) As Long​ Nothing Then Set​Считаем и суммируем по​e-mail: rasinv_inbox.ru​ цветом заливки, включая​julytt​
​ — «думать» будет​Определенные пользователем (User Defined)​
​ заливку или цвет​ относительную адресацию в​ «объяснить» функции сложения,​и получили следующие​ выводит количество раскрашенных​ количество ячеек определённого​

​Пожалуйста, помните, что​​ читателей (также нашим​

​ содержатся численные данные​​ Dim indRefColor As​ xlRange = Application.ThisCell​ цвету, когда ячейки​Все имена заняты​ условное форматирование;​: Всем спасибо з​ долго :)​можно найти нашу​ шрифта, очень удобно​

​ формуле)​​ что нужно складывать​​ цифры:​​ ячеек и сумму​ цвета или сумму​ все эти формулы​ гуру Excel) и​

excelworld.ru

Как в эксель сложить ячейки одного цвета ?

​ то у меня​​ Вы уже добавили​ действия, которые упомянул​в нашей таблице),​
​ cntRes As Long​ Then ReDim arResults(1​ ячеек по цвету​rasinv​
​ цветом шрифта, включая​ получиться.​Пытаюсь усовершеннствовать таблицы,​ ​ конечно Трудности​Назовите ее Цвет​Это можно сделать разными​
​ ячеек искомого цвета;​ типа условного форматирования,​ для Вас плохие​ в свою рабочую​ автор комментария, а​ Вы можете суммировать​ Application.Volatile cntRes =​ To xlRange.Rows.Count, 1​ во всей книге​: ГРОМАДНОЕ СПАСИБО​ условное форматирование;​
​Nikls2502​ с которыми работаю.​
​У нее два аргумента:​

​ возникают тогда, когда​​Закройте Диспетчер имен​ способами, приведем 3​ в нашем случае​ применённого на листе.​ новости – не​

Как подсчитать сумму в ячейках с определенным цветом

​ книгу Excel пользовательскую​​ именно считает количество​ значения на основе​ 0 indRefColor =​ To xlRange.Columns.Count) For​
​Считаем и суммируем по​ОЧЕНЬ ПОМОГ!!!​Summ_CellFormat — сумирование​
​: Народ подскажите как​ Возникает множество вопросов.​DataRange​ по такой раскрашенной​
​Введите в ячейку​ из них: с​
​ это красноватый цвет,​
​Sub SumCountByConditionalFormat() Dim​

​ существует универсальной пользовательской​​ функцию, как было​ и сумму ячеек​

​ выбранного цвета ячейки,​​ cellRefColor.Cells(1, 1).Font.Color For​
​ indRow = 1​

CyberForum.ru

​ цвету, когда к​

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

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

Мы часто при работе в Excel окрашиваем ячейки различными цветами для лучшей визуализации данных.
Однако, когда возникает необходимость произвести какие-либо расчеты с обработанными данными мы сталкиваемся с трудностями в связи с малыми возможностями стандартных средств Excel.
Рассмотрим две простые функции, которые дают возможность суммировать ячейки, и посчитать количество выделенных цветом ячеек.

Суммирование ячеек по цвету

Перейдем в редактор VBA, для этого в панели вкладок выбираем Разработчик -> Visual Basic (или нажимаем комбинацию клавиш Alt + F11).
Создаем новый модуль и добавляем в него следующий код (напротив каждой строчки дается пояснение к коду):

Функция СУММЦВЕТ содержит два аргумента:

• MyRange(обязательный аргумент) — диапазон ячеек для суммирования;
• MyCell(обязательный аргумент) — ячейка, по цвету заливки которой рассчитывается сумма.

Функция СУММЦВЕТ теперь будет определяться при вводе формулы в ячейку, переходим из редактора на лист Excel и воспользуемся новой функцией:

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

Подсчет количества ячеек по цвету

Чтобы посчитать ячейки одного цвета достаточно немного видоизменить функцию для подсчета суммы — вместо прибавления значения текущей ячейки (Sum = Sum + cell.Value) мы добавляем 1 (Sum = Sum + 1).

При работе с данными функциями обратите внимание на два важных момента:

• Если цвет выбранной ячейки определяется с помощью условного форматирования (т.е. цвет ячейки определяется не за счет заливки), то рассмотренные функции не сработают.
• В случае изменения раскраски ячейки в Excel формулы автоматически не пересчитываются, так как не изменяется содержимое ячейки, поэтому для корректного расчета необходимо произвести пересчет формул. Комбинация клавиш Shift + F9 пересчитает формулы на активном листе (F9 — для всей книги).

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

Мы с вами уже рассматривали вопрос о том как посчитать в Excel количество ячеек/значений в подробном видео уроке. Сегодня мы бы хотели немного расширить данную статью для решения более узкой задачи. Допустим, вам необходимо посчитать количество ячеек в зависимости от цвета ячеек или цвета текста.

Начиная с Excel 2007 в программе встроили возможность сортировки ячеек по цвету. Таким образом, можно отфильтровать нужный нам цвет, выделить оставшиеся на виду ячейки и визуально посмотреть общее количество ячеек. Но что делать, если нам требуется делать это часто и при этом нам необходимо, чтобы все считалось и пересчитывалось с помощью формул.

Для этих целей необходимо использовать очень простенький макрос, а точнее пользовательскую функцию, назовем ее ColorNom, она позволит нам вытягивать числовой код цвета заливки и далее по этому коду мы и будет считать общее количество ячеек, используя приемы, описываемые в статье как посчитать в Excel количество ячеек/значений

Итак, приступим. Зайдите в редактор Visual Basic, для этого:

в Excel 2003 нажмите на Сервис, далее Макрос и затем Редактор Visual Basic.

в Excel 2007, 2010 и 2013 это делается по-другому. Зайдите в раздел Разработчик, далее выберите Visual Basic

Внимание! Раздел панели инструментов Разработчик в Excel 2007 доступен по умолчанию, а в Excel 2010 и 2013 его необходимо включить. Это особенно полезно сделать тем пользователям, которые будут часто работать с макросами. Чтобы включить панель инструментов Разработчик в Excel 2010 или 2013 необходимо запустить Файл | Параметры | Настройка ленты после этого необходимо с правой стороны необходимо поставить галочку напротив надписи Разработчик

После того как откроется редактор Visual Basic, вставьте пустой модуль, для этого выберите меню Insert и далее Module

и скопируйте туда текст простой функции:

Public Function ColorNom (Cell As Range)
ColorNom = Cell.Interior.ColorIndex
End Function

После этого закройте редактор Visual Basic и можно вернуться к нашему файлу. В любой пустой ячейки введите пользовательскую функцию, которую мы ввели раннее. В нашем случае это функция ColorNom, ее можно вызвать либо через меню Вставка, Функция — категория Определенные пользователем, либо просто можно напечатать ее в самой ячейке =ColorNom ( A1 ), где A1 — это наша ячейка, в которой нам необходимо определить индекс цвета.

После этого уже не составит труда посчитать количество ячеек или значений в зависимости от цвета ячейки. Используйте нашу статью как посчитать в Excel количество ячеек/значений

Если вам необходимо посчитать количество значений или сумму в зависимости от цвета текста, то необходимо немного изменить код пользовательской функции.

Public Function ColorNom (Cell As Range)
ColorNom = Cell.Font.ColorIndex
End Function

Важно! Вы не сможете находить с помощью данной функции номер цвета ячейки при использовании условного форматирования. Кроме того, при изменении цвета ячейки Excel не пересчитывает значения, необходимо это делать в ручную, нажимая Ctrl+Alt+F9, либо изменения будут происходить при новом открытии данного файла. Это происходит из-за того, что Excel не считает изменение цвета ячейки редактированием формулы. В связи с этим, если это критично, то можно внести изменение в саму формулу, просто добавив функцию, которая постоянно пересчитывается и при этом не повлияет на определение цвета ячейки. Например, указать функцию определения текущей даты, умноженную на ноль.
В нашем случае функция будет выглядеть следующем образом.

=ColorNom (A1)+Сегодня()*0

Пример подсчета количества значений по цвету цвету заливки ячеек в Excel

Рассмотрим вышеуказанный пример с перечнем фруктов. Мы определили код ячеек и отобразили его напротив каждой ячейки.

Далее для удобства мы создадим вспомогательную таблицу из всех существующих цветов заливки. В нашем случае это красный, зеленый и желтый. Рядом с помощью все той же формулы определим код цвета.

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

Считать количество мы будем с помощью функции СЧЁТЕСЛИ

Вот так выглядят аргументы данной функции

=СЧЁТЕСЛИ( диапазон ; критерий )

=СЧЁТЕСЛИ( $B$1:$B$8 ; E2 )

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

Скачать пример файла: Цвет_Ячеек.xlsm (файл с поддержкой макросов)

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

Помечать ячейки цветом, используя заливку или цвет шрифта, очень удобно и наглядно. Если вы не дальтоник, конечно 🙂 Трудности возникают тогда, когда по такой раскрашенной таблице возникает необходимость сделать отчет. И если фильтровать и сортировать по цвету Excel в последних версиях научился, то суммировать по цвету до сих пор не умеет.

Чтобы исправить этот существенный недостаток можно использовать несложную пользовательскую функцию на Visual Basic, которая позволит нам суммировать ячейки с определенным цветом.

Откройте редактор Visual Basic:

• В Excel 2003 и старше для этого нужно выбрать в меню Сервис — Макрос — Редактор Visual Basic (Tools — Macro — Visual Basic Editor)
• В новых версиях Excel 2007-2013 перейти на вкладку Разработчик (Developer) и нажать кнопку Visual Basic. Если такой вкладки у вас не видно, то включите ее в настройках Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon)

В окне редактора вставьте новый модуль через меню Insert — Module и скопируйте туда текст вот такой функции:

Если теперь вернуться в Excel, то в Мастере функций (Вставка — Функция) в появившейся там категории Определенные пользователем (User Defined) можно найти нашу функцию и вставить ее на лист:

У нее два аргумента:

• DataRange — диапазон раскрашенных ячеек с числами
• ColorSample — ячейка, цвет которой принимается как образец для суммирования

Цвет шрифта

Легко изменить нашу функцию, чтобы она учитывала не цвет заливки фона, а цвет шрифта ячейки. Для этого в строке 6 просто замените свойство Interior на Font в обеих частях выражения.

Количество вместо суммы

Если вам нужно подсчитывать не сумму покрашенных определенным цветом ячеек, а всего лишь их количество, то наша функция будет еще проще. Замените в ней 7-ю строку на:

Нюансы пересчета

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

Полностью решить эту проблему невозможно, но можно ее существенно облегчить. Для этого в третьей строке нашей функции используется команда Application.Volatile True. Она заставляет Excel пересчитывать результаты нашей функции при изменении любой ячейки на листе (или по нажатию F9).

И помните о том, что наша функция перебирает все (и пустые тоже) ячейки в диапазоне DataRange и не задавайте в качестве первого аргумента целый столбец — «думать» будет долго 🙂

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

Из этой статьи Вы узнаете, как в Excel посчитать количество и сумму ячеек определенного цвета. Этот способ работает как для ячеек, раскрашенных вручную, так и для ячеек с правилами условного форматирования. Кроме того, Вы научитесь настраивать фильтр по нескольким цветам в Excel 2010 и 2013.

Если Вы активно используете разнообразные заливки и цвет шрифта на листах Excel, чтобы выделять различные типы ячеек или значений, то, скорее всего, захотите узнать, сколько ячеек выделено определённым цветом. Если же в ячейках хранятся числа, то, вероятно, Вы захотите вычислить сумму всех ячеек с одинаковой заливкой, например, сумму всех красных ячеек.

Как известно, Microsoft Excel предоставляет набор функций для различных целей, и логично предположить, что существуют формулы для подсчёта ячеек по цвету. Но, к сожалению, не существует формулы, которая позволила бы на обычном листе Excel суммировать или считать по цвету.

Если не использовать сторонние надстройки, существует только одно решение – создать пользовательскую функцию (UDF). Если Вы мало знаете об этой технологии или вообще никогда не слышали этого термина, не пугайтесь, Вам не придётся писать код самостоятельно. Здесь Вы найдёте отличный готовый код (написанный нашим гуру Excel), и всё, что Вам потребуется сделать – это скопировать его и вставить в свою рабочую книгу.

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

Предположим, у Вас есть таблица заказов компании, в которой ячейки в столбце Delivery раскрашены в зависимости от их значений: Due in X Days – оранжевые, Delivered – зелёные, Past Due – красные.

Теперь мы хотим автоматически сосчитать количество ячеек по их цвету, то есть сосчитать количество красных, зелёных и оранжевых ячеек на листе. Как я уже сказал выше, прямого решения этой задачи не существует. Но, к счастью, в нашей команде есть очень умелые и знающие Excel гуру, и один из них написал безупречный код для Excel 2010 и 2013. Итак, выполните 5 простых шагов, описанных далее, и через несколько минут Вы узнаете количество и сумму ячеек нужного цвета.

1. Откройте книгу Excel и нажмите Alt+F11, чтобы запустить редактор Visual Basic for Applications (VBA).
2. Правой кнопкой мыши кликните по имени Вашей рабочей книги в области Project – VBAProject, которая находится в левой части экрана, далее в появившемся контекстном меню нажмите Insert >Module.
3. Вставьте на свой лист вот такой код:

1. Сохраните рабочую книгу Excel в формате .xlsm (Книга Excel с поддержкой макросов).Если Вы не слишком уверенно чувствуете себя с VBA, то посмотрите подробную пошаговую инструкцию и массу полезных советов в учебнике Как вставить и запустить код VBA в Excel.
2. Когда все закулисные действия будут выполнены, выберите ячейки, в которые нужно вставить результат, и введите в них функцию CountCellsByColor:

CountCellsByColor( диапазон , код_цвета )

В этом примере мы используем формулу =CountCellsByColor(F2:F14,A17), где F2:F14 – это диапазон, содержащий раскрашенные ячейки, которые Вы хотите посчитать. Ячейка A17 – содержит определённый цвет заливки, в нашем случае красный.

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

Если в раскрашенных ячейках содержатся численные данные (например, столбец Qty. в нашей таблице), Вы можете суммировать значения на основе выбранного цвета ячейки, используя аналогичную функцию SumCellsByColor:

SumCellsByColor( диапазон , код_цвета )

Как показано на снимке экрана ниже, мы использовали формулу:

где D2:D14 – диапазон, A17 – ячейка с образцом цвета.

Таким же образом Вы можете посчитать и просуммировать ячейки по цвету шрифта при помощи функций CountCellsByFontColor и SumCellsByFontColor соответственно.

Замечание: Если после применения выше описанного кода VBA Вам вдруг потребуется раскрасить ещё несколько ячеек вручную, сумма и количество ячеек не будут пересчитаны автоматически после этих изменений. Не ругайте нас, это не погрешности кода

На самом деле, это нормальное поведение макросов в Excel, скриптов VBA и пользовательских функций (UDF). Дело в том, что все подобные функции вызываются только изменением данных на листе, но Excel не расценивает изменение цвета шрифта или заливки ячейки как изменение данных. Поэтому, после изменения цвета ячеек вручную, просто поставьте курсор на любую ячейку и кликните F2, а затем Enter, сумма и количество после этого обновятся. Так нужно сделать, работая с любым макросом, который Вы найдёте далее в этой статье.

Считаем сумму и количество ячеек по цвету во всей книге

Представленный ниже скрипт Visual Basic был написан в ответ на один из комментариев читателей (также нашим гуру Excel) и выполняет именно те действия, которые упомянул автор комментария, а именно считает количество и сумму ячеек определённого цвета на всех листах данной книги. Итак, вот этот код:

Добавьте этот макрос точно также, как и предыдущий код. Чтобы получить количество и сумму цветных ячеек используйте вот такие формулы:

Просто введите одну из этих формул в любую пустую ячейку на любом листе Excel. Диапазон указывать не нужно, но необходимо в скобках указать любую ячейку с заливкой нужного цвета, например, =WbkSumCellsByColor(A1), и формула вернет сумму всех ячеек в книге, окрашенных в этот же цвет.

Пользовательские функции для определения кодов цвета заливки ячеек и цвета шрифта

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

Замечание: Пожалуйста, помните, что все эти формулы будут работать, если Вы уже добавили в свою рабочую книгу Excel пользовательскую функцию, как было показано ранее в этой статье.

Функции, которые считают количество по цвету:

• CountCellsByColor( диапазон , код_цвета ) – считает ячейки с заданным цветом заливки.В примере, рассмотренном выше, мы использовали вот такую формулу для подсчёта количества ячеек по их цвету:

где F2:F14 – это выбранный диапазон, A17 – это ячейка с нужным цветом заливки.

Все перечисленные далее формулы работают по такому же принципу.

Функции, которые суммируют значения по цвету ячейки:

• SumCellsByColor( диапазон , код_цвета ) – вычисляет сумму ячеек с заданным цветом заливки.
• SumCellsByFontColor( диапазон , код_цвета ) – вычисляет сумму ячеек с заданным цветом шрифта.

Функции, которые возвращают код цвета:

• GetCellFontColor( ячейка ) – возвращает код цвета шрифта в выбранной ячейке.
• GetCellColor( ячейка ) – возвращает код цвета заливки в выбранной ячейке.

Итак, посчитать количество ячеек по их цвету и вычислить сумму значений в раскрашенных ячейках оказалось совсем не сложно, не так ли? Но что если Вы не раскрашиваете ячейки вручную, а предпочитаете использовать условное форматирование, как мы делали это в статьях Как изменить цвет заливки ячеек и Как изменить цвет заливки строки, основываясь на значении ячейки?

Как посчитать количество и сумму ячеек по цвету, раскрашенных при помощи условного форматирования

Если Вы применили условное форматирование, чтобы задать цвет заливки ячеек в зависимости от их значений, и теперь хотите посчитать количество ячеек определённого цвета или сумму значений в них, то у меня для Вас плохие новости – не существует универсальной пользовательской функции, которая будет по цвету суммировать или считать количество ячеек и выводить результат в определённые ячейки. По крайней мере, я не слышал о таких функциях, а жаль

Конечно, Вы можете найти тонны кода VBA в интернете, который пытается сделать это, но все эти коды (по крайней мере, те экземпляры, которые попадались мне) не обрабатывают правила условного форматирования, такие как:

• Format all cells based on their values (Форматировать все ячейки на основании их значений);
• Format only top or bottom ranked values (Форматировать только первые или последние значения);
• Format only values that are above or below average (Форматировать только значения, которые находятся выше или ниже среднего);
• Format only unique or duplicate values (Форматировать только уникальные или повторяющиеся значения).

Кроме того, практически все эти коды VBA имеют целый ряд особенностей и ограничений, из-за которых они могут не работать корректно с какой-то конкретной книгой или типами данных. Так или иначе, Вы можете попытать счастье и google в поисках идеального решения, и если Вам удастся найти его, пожалуйста, возвращайтесь и опубликуйте здесь свою находку!

Код VBA, приведённый ниже, преодолевает все указанные выше ограничения и работает в таблицах Microsoft Excel 2010 и 2013, с любыми типами условного форматирования (и снова спасибо нашему гуру!). В результате он выводит количество раскрашенных ячеек и сумму значений в этих ячейках, независимо от типа условного форматирования, применённого на листе.

Как использовать код, чтобы посчитать количество цветных ячеек и просуммировать их значения

1. Добавьте код, приведённый выше, на Ваш лист, как мы делали это в первом примере.
2. Выберите диапазон (или диапазоны), в которых нужно сосчитать цветные ячейки или просуммировать по цвету, если в них содержатся числовые данные.
3. Нажмите и удерживайте Ctrl, кликните по одной ячейке нужного цвета, затем отпустите Ctrl.
4. Нажмите Alt+F8, чтобы открыть список макросов в Вашей рабочей книге.
5. Выберите макрос SumCountByConditionalFormat и нажмите Run (Выполнить).В результате Вы увидите вот такое сообщение:

Для этого примера мы выбрали столбец Qty. и получили следующие цифры:

• Count – это число ячеек искомого цвета; в нашем случае это красноватый цвет, которым выделены ячейки со значением Past Due.
• Sum – это сумма значений всех ячеек красного цвета в столбце Qty., то есть общее количество элементов с отметкой Past Due.
• Color – это шестнадцатеричный код цвета выделенной ячейки, в нашем случае D2.

Рабочая книга с примерами для скачивания

Если у Вас возникли трудности с добавлением скриптов в рабочую книгу Excel, например, ошибки компиляции, не работающие формулы и так далее, Вы можете скачать рабочую книгу Excel с примерами и с готовыми к использованию функциями CountCellsByColor и SumCellsByColor, и испытать их на своих данных.

СчетЯчеек_Заливка

Данная функция является частью надстройки MulTEx

• Описание, установка, удаление и обновление
• Полный список команд и функций MulTEx
• Часто задаваемые вопросы по MulTEx
• Скачать MulTEx

Подсчет ячеек по цвету заливки

Функция подсчитывает количество ячеек, заливка которых имеет определенный цвет. Может пригодиться, если ведется учет каких-либо соревнований и каждое место в туре имеет свой цвет ячейки. После заполнения такая таблица может и выглядит очень наглядно, но подсчитать количество первых мест, вторых, третьих становится большой проблемой, ведь в Excel до сих пор нет функций, способных суммировать/подсчитывать ячейки по цвету.

Вызов команды через стандартный диалог:

Мастер функций—Категория «MulTEx»— СчетЯчеек_Заливка

Вызов с панели MulTEx:

Сумма/Поиск/Функции — Математические — СчетЯчеек_Заливка

Синтаксис:
=СчетЯчеек_Заливка( $E$2:$E$20 ; $E$7 ; I13 ; $A$2:$A$20 )

ДиапазонСчета( $E$2:$E$20 ) — диапазон значений для подсчета. Можно указать несколько столбцов. Столбец с критерием(если планируется считать еще и по критерию) не обязательно должен входит в диапазон.

ЯчейкаОбразец( $E$7 ) — ячейка-образец с цветом заливки. Ячейки с этим цветом будут подсчитаны.

Критерий( I13 ) — необязательный аргумент. Если указан, то подсчитываются ячейки с указанным критерием и цветом заливки. Допускается применение в критерии символов подстановки — «*» и «?» . Например, для подсчета только ячеек, в которых содержится слово «мир» необходимо указать в качестве критерия — «*мир*» . Если необходимо посчитать количество непустых ячеек с указанным цветом заливки, то можно указать критерий: «*?*» . Если не указан, то подсчитываются все ячейки с указанным цветом заливки.
Так же данный аргумент может принимать в качестве критерия символы сравнения ( , =, <>, ):

• «>0» — будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых больше нуля;
• «>=2» — будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых больше или равно двум;
• » — будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых меньше нуля;
• » — будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых меньше или равно 60;
• «<>0″ — будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых не равно нулю;
• «<>» — будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых не пустые;

Вместо нуля может быть любое число или текст. Так же можно добавить ссылку на ячейку со значением: «<>«&D$1

ДиапазонКритерия( $A$2:$A$20 ) — Необязательный аргумент. Указывается диапазон, в котором следует искать критерий(если критерий указан). ДиапазонКритерия должен быть равен по количеству ячеек ДиапазонуСчета. Если ДиапазонКритерия не указан, то критерий просматривается в ДиапазонеСчета.

ИспУФ() — Необязательный аргумент. Допускается указание логических значений ИСТИНА(TRUE) или ЛОЖЬ(FALSE). По умолчанию принимает значение ИСТИНА. Если указан как ИСТИНА, то функция будет подсчитывать ячейки с учетом примененного к ним условного форматирования. Если указан как ЛОЖЬ, то функция будет подсчитывать ячейки без учета примененного условного форматирования, т.е. даже если условное форматирование применено и ячейка окрашена с его помощью, а реальный цвет заливки не соответствует цвету ЯчейкиОбразца — то она не будет подсчитана.

Функция подсчитывает любые ячейки, заливка которых равна заливке ячейки-образца. Даже если ячейка будет пустая, но заливка будет равна указанной — ячейка будет подсчитана. Чтобы подсчитать только заполненные ячейки в качестве критерия следует указать — «*?*» , а ДиапазонКритерия не указывать.

Важно: Функция не вычисляется при изменении цвета заливки. Для пересчета функции после изменения параметров необходимо выделить ячейку и нажать F2—Enter. Либо нажать сочетания клавиш Shift+F9(пересчет функций активного листа) или клавишу F9(пересчет функций всей книги)

Примечание: данная функция будет корректно работать даже при примененном к ячейке Условном форматировании. Однако если в ячейке/диапазоне присутствуют условия, формат для которых задан при помощи шкал, градиентов, гистограмм и значков — функция может вернуть некорректный результат. Связано это с тем, что Excel не предоставляет доступ к данным типам УФ извне.