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

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

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

Запускать его будем в Microsoft Excel по следующему алгоритму:

запускаем MS Excel;
входим во вкладку «Вид»;
нажимаем «Макросы»;
в отрывшемся окне вводим название нашей будущей программы (разрешены английские буквы и символы);
нажимаем «Создать».

Теперь мы в редакторе Visual Basic, и чтобы осуществить расчет по количеству залитых ячеек используем следующий код:

Public Function SumColour(DataRange As Range, ColorSample As Range) As Double Dim cell As Range Dim SumAll As Long Application.Volatile True SumAll = 0 For Each cell In DataRange If cell.Interior.Color = ColorSample.Interior.Color Then SumAll = SumAll+ 1 End If Next cell SumColour = SumAll End Function

Так, с помощью функции SumColour, можно пересчитать количество ячеек.

Для пересчета суммы окрашенных ячеек берем вышеописанный код, меняем название функции (например, SumByColour) и строку кода «SumAll = SumAll+ 1» на «SumAll = SumAll+ cell.Value».

На практике использование редактора VBA помогает пересчитывать большие объемы данных не опасаясь ошибок, связанных с сортировкой и фильтрацией в Excel.

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

22K открытий
5 комментариев
Теперь мы в редакторе Visual Basic.

Скажите, а вы не пробовали проделать то же самое в Google Docs (Spreadsheets)?
Работает?

Попробовал бы сам, но к сожалению, не в курсе как там запускать подобные скрипты.

Развернуть ветку

Добрый день. Для Google Sheets есть такие же скрипты . Как-то писал скрипт для 8 цветов. Например, в настройках вы выбираете комфортные для себя цвета и скрипт потом суммирует то что выделено цветом

Развернуть ветку

Добрый день! Спасибо за ваш вопрос.
Запускали только на локальном ресурсе, используя Excel.
В Google Docs пока не реализовывали.

Развернуть ветку

Как-то это нехорошо, считать цветных отдельно от белых.

Развернуть ветку

На небольшом объеме алгоритм ОК, но будет очень медленным на больших. Как вариант ускорения — использовать UDF для вывода кода цвета заливки в доп столбце и делать расчет по этому столбцу штатной функцией с условим

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

Запускать его будем в Microsoft Excel по следующему алгоритму:

запускаем MS Excel;
входим во вкладку «Вид»;
нажимаем «Макросы»;
в отрывшемся окне вводим название нашей будущей программы (разрешены английские буквы и символы);
нажимаем «Создать».

Так, с помощью функции SumColour, можно пересчитать количество ячеек.

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

22K открытий
5 комментариев
Теперь мы в редакторе Visual Basic.

Скажите, а вы не пробовали проделать то же самое в Google Docs (Spreadsheets)?
Работает?

Попробовал бы сам, но к сожалению, не в курсе как там запускать подобные скрипты.

Развернуть ветку

Как-то это нехорошо, считать цветных отдельно от белых.

Развернуть ветку

Вычисляйте СЧЁТ, СУММ, СРЗНАЧ, МИН, МАКС на основе цвета ячеек или цвета шрифта

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

Инструмент «Счёт по цвету» мгновенно и без VBA считает значения в ячейках, исходя из их цвета:

Вычисление СЧЁТ, СУММ, СРЗНАЧ, МИН, МАКС для каждого цвета
Агрегация по цвету заливки и/или цвету условного форматирования
Cчёт по цвету фона ячеек или цвету шрифта
Предпросмотр и вставка таблицы результата на рабочий лист

Перед началом работы добавьте «Счёт по цвету» в Excel

«Счёт по цвету» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

Начните работу с инструментами XLTools

Скачать XLTools для Excel
– пробный период дает 14 дней полного доступа ко всем инструментам.

Как посчитать значения ячеек на основе цвета заливки

Excel по-разному обрабатывает два типа цветного фона ячеек: заливка сплошным цветом, заданная пользователем (когда вы сами назначаете цвет) и условный цвет (при применении условного форматирования). С надстройкой вы можете агрегировать значения по каждому типу цветного фона.

Нажмите кнопку Счёт по цвету на панели XLTools Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
Заданные цвета – чтобы учитывать только ячейки со сплошной заливкой фона
Условные цвета – чтобы учитывать только ячейки с условным форматированием
Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
Из следующего выпадающего списка выберите «Вычислять по цвету» Фона .

Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.

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

Выберите поместить результаты на новый или существующий лист.
Нажмите OK Готово!

Счет по цвету заливки ячеек

Как посчитать значения ячеек на основе цвета шрифта

Excel по-разному обрабатывает два типа цветного шрифта: цвет шрифта, заданный пользователем (когда вы сами назначаете цвет) и условный цвет шрифта (при применении условного форматирования). С надстройкой вы можете агрегировать значения по каждому типу цветного шрифта.

Нажмите кнопку Счёт по цвету на панели XLTools Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
Заданные цвета – чтобы учитывать только ячейки заданным цветом шрифта
Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
Из следующего выпадающего списка выберите «Вычислять по цвету» Шрифта .

Выберите поместить результаты на новый или существующий лист.
Нажмите OK Готово!

Счет знчений по цвету шрифта в Excel

Как посчитать значения ячеек на основе цвета условного форматирования

Excel по-разному обрабатывает два типа цветного фона ячеек: заливка сплошным цветом, заданная пользователем (когда вы сами назначаете цвет) и условный цвет (при применении условного форматирования). Условное форматирование может применятся как к шрифту, так и к фону фчейки. С надстройкой вы можете агрегировать значения по любому типу условного цвета.

Нажмите кнопку Счёт по цвету на панели XLTools Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
Условные цвета – чтобы учитывать только ячейки с условным форматированием
Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.

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

Выберите поместить результаты на новый или существующий лист.
Нажмите OK Готово!

Счет по цвету условного форматирования в Excel

Доступные агрегатные вычисления (Count, Sum, Average, Minimum, Maximum)

Надстройка производит расчёт самых частых агрегатных функций, на основе цвета шрифта или фона ячейки:

СЧЁТ (COUNT) – подсчёт количества всех значений в диапазоне по цвету
СУММ (SUM) – сумма всех значений в диапазоне по цвету
СРЗНАЧ (AVERAGE) – среднее (арифметическое среднее) всех значений в диапазоне по цвету
МИН (MIN) – наименьшее значение в диапазоне по цвету
МАКС (MAX) – наибольшее значение в диапазоне по цвету

Какие ячейки и значения учитываются при вычислениях

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

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

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

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

Сумма по цвету ячейки в excel формула

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

Смотрите такжеКазанскийМарияи попробовать сложитьVaska это очень хорошо,If c.Interior.Color =F9 вкладкуВ7 их заливки. Здесь sumRes) End If в зависимости от получить количество игде

sumRes = 0 команде есть оченьИз этой статьи Вы: Теперь можно вводить, читаем Правила форума, сумму ячеек всех: Люди ))) а когда форум жив. ObrazecCvet Then).Разработчик (Developer)формулу =Цвет и же покажем, как Next MsgBox «Count с» в реальности ктоVaskaSumma1 = Summa1И помните о том,и нажать кнопку скопируйте ее вниз. подсчитать такие ячейки.

& cntRes & теперь хотите посчитать используйте вот такие– диапазон, 1).Interior.Color For Each Excel гуру, и Excel посчитать количествоTikr и прикладываем файл (10:00с*24*130) сможет сделать, хотя: Доброго времени суток, + 1 что наша функция Visual Basic. ЕслиСложение значений организовано такФункции для суммирования значений vbCrLf & «Sum= количество ячеек определённого формулы:A17

cellCurrent In rData один из них и сумму ячеек
- : C UDF чтото с примеромбудет ли работать
Как считать и суммировать по цвету на листе Excel

такой вкладки у же как и по цвету ячеек » & sumRes цвета или сумму=WbkCountCellsByColor()– ячейка с If indRefColor = написал безупречный код определенного цвета. Этот не получаетсяkiramiD при задаче определённого

два дня голову проблеме.Next пустые тоже) ячейки вас не видно, в предыдущем разделе. в EXCEL не & vbCrLf & значений в них,=WbkSumCellsByColor() образцом цвета. cellCurrent.Interior.Color Then sumRes для Excel 2010 способ работает какМожно ли попробовать: Подскажите, можно ли стиля ячейке. ломаю. файл сюдаЕсть табель посещаемостиСчетЕслиЦвет = Summa1 в диапазоне то включите ееМакрофункция работает кривовато: существует (по крайней vbCrLf & _
1. то у меняПросто введите одну изТаким же образом Вы = WorksheetFunction.Sum(cellCurrent, sumRes) и 2013. Итак, для ячеек, раскрашенных
2. какие-нибудь Excel формулы сделать условие кNic70y не помещается.(( с выводом з/п,End FunctionDataRange в настройкахесли вы измените цвет мере, в EXCEL «Color=» & Left(«000000», для Вас плохие этих формул в
3. можете посчитать и End If Next
выполните 5 простых вручную, так и ?! цвету ячейки и: Формат на суммуNic70y (кол-во часов *Ребят, не получаетсяи не задавайтеФайл — Параметры - ячейки, то макрофункция 2016 и в 6 — Len(Hex(indRefColor))) новости – не любую пустую ячейку просуммировать ячейки по cellCurrent SumCellsByColor = шагов, описанных далее, для ячеек сvikttur какой формулой? не повлияет!: А файлообменники для ставку = сумм) доработать функцию, пример в качестве первого Настройка ленты (File не обновит значения более ранних версиях). & _ Hex(indRefColor) существует универсальной пользовательской на любом листе цвету шрифта при sumRes End Function и через несколько правилами условного форматирования.: Вам формулы показали.Например: получить суммуNic70y кого изобретали?нужно как то в файле. аргумента целый столбец — Options - кода (для этого Вероятно, подавляющему большинству пользователей & vbCrLf, , функции, которая будет Excel. Диапазон указывать помощи функций Function CountCellsByFontColor(rData As минут Вы узнаете Кроме того, Вы Тоже не получается? столбца с цифрами,: А где все-такиANik высчитать количество днейnilem — «думать» будет Customize Ribbon) нужно опять скопировать это не требуется. «Count & Sum по цвету суммировать не нужно, ноCountCellsByFontColor Range, cellRefColor As количество и сумму научитесь настраивать фильтрTikr но складывать только ошибка, я не: А Вас никто стажа (отмеченных другим: попробуйте так долго :)В окне редактора вставьте формулу изПусть дан диапазон ячеек by Conditional Format или считать количество необходимо в скобкахи Range) As Long ячеек нужного цвета. по нескольким цветам: У меня не те цифры из могу найти у не заставляет выкладывать цветом) по другой200?’200px’:»+(this.scrollHeight+5)+’px’);»>Function СчетЕслиЦвет(Oblast As Range,julytt новый модуль черезВ7 в столбце А. color» End Sub ячеек и выводить указать любую ячейкуSumCellsByFontColor Dim indRefColor AsОткройте книгу Excel и в Excel 2010 получается UDF формула столбца, ячейки которого меня отнимется 200р все данные целиком, ставке Obrazec As Range,: Доброго дня всем!
1. менювниз или выделить ячейку, Пользователь выделил цветомДобавьте код, приведённый выше, результат в определённые с заливкой нужногосоответственно. Long Dim cellCurrent нажмите и 2013.Показанная формула работает не имеют допустимVaska возможно ещё и
2. есть вариант стандартный: FIO As String)Пытаюсь усовершеннствовать таблицы,Insert — Module нажать клавишу ячейки, чтобы разбить на Ваш лист, ячейки. По крайней цвета, например,
Замечание: As Range DimAlt+F11Если Вы активно используете только на одном синий цвет фона,: извиняюсь,это я попробовал для служебного пользования=(СУММ(А1:А3)*24*130)+(СУММ(А4:А10)*24*150) As Long с которыми работаю.и скопируйте тудаF2

значения по группам. как мы делали мере, я не=WbkSumCellsByColor(A1)Если после применения cntRes As Long

, чтобы запустить редактор разнообразные заливки и листе а у т.е. ячейки синего строку со ставкойОбозначьте 10-15 человек,но когда сотрудниковDim Summa1 As Возникает множество вопросов. текст вот такойи затем

Необходимо сложить значения ячеек

это в первом слышал о таких, и формула вернет

выше описанного кода

Application.Volatile cntRes =Visual Basic for Applications цвет шрифта на меня несколько листов цвета не плюсовать. 200/ч

расположите основные данные, много каждого отдельно Long, c As Некоторые из них функции:ENTER в зависимости от примере. функциях, а жаль

 сумму всех ячеек VBA Вам вдруг 0 indRefColor =(VBA). листах Excel, чтобыСам пробую у Привязать ячейки ка с 150 необходимые для расчётов, считать проблематично. Range, ObrazecCvet As решаю сама, а

Public Function SumByColor(DataRange) цвета фона. ОсновнаяВыберите диапазон (или диапазоны),Конечно, Вы можете найти в книге, окрашенных потребуется раскрасить ещё cellRefColor.Cells(1, 1).Font.Color ForПравой кнопкой мыши кликните выделять различные типы меня не получается другому условию не работает ))) приведите образец того,хотелось бы так Long вот с некоторыми As Range, ColorSampleфункция возвращает только 56 задача: Как нам в которых нужно тонны кода VBA в этот же несколько ячеек вручную, Each cellCurrent In по имени Вашей ячеек или значений,Tikr

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

получается, т.к. этиСпасибо огромное что хотите получить то одной формулойObrazecCvet = Obrazec.Interior.Color совсем немогу справиться. As Range) As цветов (так называемая «объяснить» функции сложения, сосчитать цветные ячейки в интернете, который цвет. сумма и количество rData If indRefColor рабочей книги в

то, скорее всего,: Раз не получается ячейки в разныеANik и в какой для всех. типа:For Each c Поэтому обнаружив такой Double Dim Sum палитра EXCEL), т.е. что нужно складывать или просуммировать по пытается сделать это,Здесь Вы найдёте самые ячеек не будут = cellCurrent.Font.Color Then области захотите узнать, сколько в этом, может моменты задачи могут: Да, интересно придумали ячейке, а дальше=СУММ(ЦВЕТ;А1)+СУММ(ЦВЕТ;А4) In Oblast.Cells замечательный форум, решила As Double Application.Volatile если цвета близки, значения, например, только цвету, если в

но все эти важные моменты по пересчитаны автоматически после cntRes = cntResProject – VBAProject ячеек выделено определённым мне кто поможет

менять цвет и
Единственное что, можгно

— добровольцы найдутсягде цвет А1If c.Interior.Color = обратиться к Вам True For Each например, зеленый и зеленых ячеек? них содержатся числовые коды (по крайней всем функциям, использованным этих изменений. Не + 1 End, которая находится в цветом. Если же в этом!? в зависимости от

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

несколько упростить вашуVaska = /ставка_1/, а ObrazecCvet Then за помощью. cell In DataRange светло зеленый, тоЭто можно сделать разными

 данные. мере, те экземпляры, нами в этом ругайте нас, это If Next cellCurrent левой части экрана, в ячейках хранятсяВот формула с этого то могут формулу до такой

Функции, которые считают количество по цвету:
- : Пароль:03022013 цвет А4 =If c.Value =есть образцы цвета If cell.Interior.Color = коды этих цветов способами, приведем 3Нажмите и удерживайте которые попадались мне) примере, а также не погрешности кода CountCellsByFontColor = cntRes далее в появившемся числа, то, вероятно, повторяющимся циклом у складываться, то нет. (в ячейке АО5):Nic70y /ссылка на ячейку FIO Then ячеек (В1,В2), в
- ColorSample.Interior.Color Then Sum могут совпасть. Подробнее из них: сCtrl
Функции, которые суммируют значения по цвету ячейки:
- не обрабатывают правила пару новых функций,На самом деле, это End Function Function
- контекстном меню нажмите Вы захотите вычислить меня не какКазанский
Функции, которые возвращают код цвета:
- =СУММ(F5:AJ5)*AL5*24+(СУММПРОИЗВ(—F5:AJ5)-СУММ(F5:AJ5))*130*24-AM5 : Обычные формулы не со ставкой_2/Summa1 = Summa1
- таблице ниже необходимо = Sum + об этом см. помощью Автофильтра, Макрофункции
, кликните по одной условного форматирования, такие которые определяют коды нормальное поведение макросов SumCellsByFontColor(rData As Range,Insert сумму всех ячеек не получается её: Формула в зависимостиVaska распознают цвет, аесть у кого + 1 посчитать количество ячеек cell.Value End If лист файла примера ПОЛУЧИТЬ.ЯЧЕЙКУ() и VBA. ячейке нужного цвета,

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

как: цветов. в Excel, скриптов cellRefColor As Range)> с одинаковой заливкой, укоротить от форматирования ячейки: извиняюсь за глупость, необычные да! какие варианты?End If определенного цвета по Next cell SumByColor Colors. Как следствие,Добавьте справа еще один затем отпуститеFormat all cells basedЗамечание: VBA и пользовательских Dim indRefColor AsModule

например, сумму всехПример =суммпроизвд((. )*(. +(‘3’!$D$20:$D$94=I5)+(‘4’!$D$20:$D$94=I5)+(‘5’!$D$20:$D$94=I5)+. ))Как подсчитать сумму а как растянутьШучу!заранее благодарен!End If определенной строке. только = Sum End будут сложены значения столбец с заголовком
- Ctrl on their valuesПожалуйста, помните, что функций (UDF). Дело Long Dim cellCurrent
- . красных ячеек.Учитывая что листов в ячейках с
-  эту формулу наСамые обычные формулыЮрий МNext не с помощью Function
-  из ячеек с Код цвета..(Форматировать все ячейки
все эти формулы в том, что As Range DimВставьте на свой листКак известно, Microsoft Excel у меня много определенным цветом 150 человек. и макрорекордер (только: В «Копилку» заглядывали?СчетЕслиЦвет = Summa1 Надстройки MyAddinЕсли теперь вернуться в разными цветами. Выделите заголовки и нажмитеНажмите на основании их будут работать, если

все подобные функции sumRes Application.Volatile sumRes вот такой код: предоставляет набор функций формула длинновата выходит,Тигоработает на 15 обычной заливкой чурZEnd FunctionSerge_007 Excel, то вВ файле примера на CTRL+SHIFT+L, т.е. вызовитеAlt+F8 значений);

Вы уже добавили вызываются только изменением = 0 indRefColorFunction GetCellColor(xlRange As для различных целей, там меняются только: Доброго вечера Всем. только ((( не пользоваться: Их есть уформула, например, в: Без макросов не Мастере функций ( листе VBA приведено Автофильтр (подробнее здесь), чтобы открыть списокFormat only top or в свою рабочую данных на листе, = cellRefColor.Cells(1, 1).Font.Color Range) Dim indRow, и логично предположить, листы. Подскажите пожалуйста какVaska) нас: Р3 будет такая получится, формулы неВставка — Функция решение с помощьюВызовите меню Автофильтра, выберите макросов в Вашей

Как использовать код, чтобы посчитать количество цветных ячеек и просуммировать их значения
1. bottom ranked values книгу Excel пользовательскую но Excel не For Each cellCurrent indColumn As Long
2. что существуют формулыКазанский суммировать каждую вторую: Всё ок, разобрался,ANikа если ещеКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СчетЕслиЦвет($E$2:$L$2;P2;N3)
3. работают с цветом) в появившейся там VBA. Решений может зеленый цвет рабочей книге.(Форматировать только первые функцию, как было
4. расценивает изменение цвета In rData If Dim arResults() Application.Volatile для подсчёта ячеек: > У меня
5. или третью ячейку спасибо всем огромное!: Мне кажется, у и это, тоivz4surejulytt категории быть множество:
Будут отображены только строкиВыберите макрос или последние значения); показано ранее в шрифта или заливки
-  indRefColor = cellCurrent.Font.Color If xlRange Is по цвету. Но, не получается UDF в столбце такVaska вас там что-то -:
- : вот я иОпределенные пользователем (User Defined)можно создать кнопку, после с зелеными ячейкамиSumFormat only values that этой статье. ячейки как изменение Then sumRes = Nothing Then Set к сожалению, не
-  формула чтоб если в: Единственный минус тут напутано в вычислениях. . nilem хотеле с макросами,
Рабочая книга с примерами для скачивания

можно найти нашу нажатия она будетВведите напротив каждого «зеленого»CountByConditionalFormat are above orCountCellsByColor(диапазон, код_цвета) данных. Поэтому, после WorksheetFunction.Sum(cellCurrent, sumRes) End xlRange = Application.ThisCell существует формулы, котораяПоказанная формула работает других ячейках написан в том, что Просто сделал так,Vaska, формула работает, только а вот то

функцию и вставить вводить код цвета
значения число 1
и нажмите

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

– считает ячейки изменения цвета ячеек If Next cellCurrent End If If позволила бы на

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

Сделайте тоже для всехRun(Форматировать только значения, с заданным цветом вручную, просто поставьте

SumCellsByFontColor = sumRes xlRange.Count > 1 обычном листе Excel листе а у он его игнорировал включается в счёт Вариант без макроса, это, проблема в

строке цвет коричневый нарыть в интеренте (реализован этот вариант). цветов

С помощью Автофильтра (ручной метод)
- (Выполнить). которые находятся выше заливки.В примере, рассмотренном
- курсор на любую End Function Then ReDim arResults(1
- суммировать или считать меня несколько листов
- а то он этой строки.
- но жутко некрасивый. том, что я
- 1, и добавляешь не работает. если
У нее два аргумента:можно написать пользовательскую функцию,Введите формулу =СУММЕСЛИ(B7:B17;E7;A7:A17) как показано

В результате Вы увидите или ниже среднего);

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

выше, мы использовали ячейку и кликнитеСохраните рабочую книгу Excel To xlRange.Rows.Count, 1 по цвету.

Покажите пример, на у меня пишетNikls2502 Промежуточные столбцы, разумеется, с макросами не ещё 1 коричневый, возможно окажите помощьDataRange которая будет автоматически в файле примера вот такое сообщение:Format only unique or вот такую формулуF2 в формате To xlRange.Columns.Count) ForЕсли не использовать сторонние котором не получается. #ЗНАЧ. Вот формула: Народ подскажите как

 можно скрыть. дружу. ни разу автоматом не считает,Serge_007- диапазон раскрашенных обновлять код цвета (лист Фильтр).Для этого примера мы duplicate values для подсчёта количества, а затем.xlsm indRow = 1 надстройки, существует толькоTikr=СУММПРОИЗВ((ОСТАТ(СТРОКА(B1:B40);2)=0)*(B1:B40))) посчитать сумму ячеек
- Vaska не делал такое.  приходится всё делать: ячеек с числами при изменении цвета
- Для подсчета значений используйте выбрали столбец
- (Форматировать только уникальные
- ячеек по их
- Enter(Книга Excel с To xlRange.Rows.Count For одно решение –
: Ваша формула удаляетIgor67 выделенных цветом

: Спасибо огромное, сейчас
- и ещё, не вручную, можно этоQuoteColorSample ячейки (реализовать несколько функцию СЧЕТЕСЛИ().Qty. или повторяющиеся значения). цвету:, сумма и количество поддержкой макросов).Если Вы indColumn = 1 создать пользовательскую функцию
- значения которые не: СУММ() игнорирует текстMichael_S попробую разобраться. просто сложение ячеек как-то автоматизировать? Хотя(julytt)200?’200px’:»+(this.scrollHeight+5)+’px’);»>олько не с- ячейка, цвет сложнее);Сразу предупрежу, что начинающемуи получили следующиеКроме того, практически все=CountCellsByColor(F2:F14,A17) после этого обновятся.
С помощью VBA

не слишком уверенно To xlRange.Columns.Count arResults(indRow, (UDF). Если Вы находятся в искомомВводим с одновременным
- : Смотря как ониНа самом деле с разными цветами Формулы->Параметры вычислений->Автоматически помощью Надстройки MyAddin
- которой принимается какможно написать программу, которая пользователю EXCEL будет цифры: эти коды VBAгде
- Так нужно сделать, чувствуете себя с indColumn) = xlRange(indRow, мало знаете об диапазоне (они не нажатием Ctrl+Shift+Enter выделены. Если вручную там не напутано. нужно, а сnilemВы сами себе образец для суммирования будет анализировать диапазон сложно разобраться сCount имеют целый ряд
Сумма ячеек по цвету

F2:F14 работая с любым VBA, то посмотрите indColumn).Interior.Color Next Next этой технологии или должны удалятся) +=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(B7:B40);2)=0;(B7:B40);0)) — то только На первом листе умножением этой суммы: попробуйте формулу изменить: противоречитеЛегко изменить нашу функцию, цветных ячеек, определять этим и следующим– это число особенностей и ограничений,

– это выбранный макросом, который Вы подробную пошаговую инструкцию GetCellColor = arResults вообще никогда не повторюсь листов неТиго

макросом, если применялось
- отмечаю время прихода цвета на определённое200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СчетЕслиЦвет($E$2:$L$2;P2;N3)+СЕГОДНЯ()*0Вот аналогичная тема: чтобы она учитывала количество различных цветов, разделом. ячеек искомого цвета; из-за которых они
- диапазон, найдёте далее в и массу полезных Else GetCellColor = слышали этого термина, мало, тогда надо: Спасибо Игорь за УФ — то — ухода, число.теперь будет пересчитыватьсяНарод подскажите как не цвет заливки вычислять в отдельном
Идея заключается в том, в нашем случае могут не работатьA17 этой статье. советов в учебнике xlRange.Interior.Color End If

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

Представленный ниже скрипт Visual Как вставить и End Function Function не придётся писать в UDF ? второй вопрос послеvikttur на второй лист ячеек умножается на (изменении значений ячеек)

выделенных цветом

шрифта ячейки. Для
-  каждого цвета (реализовать в соседнем столбце которым выделены ячейки
-  конкретной книгой или с нужным цветом Basic был написан запустить код VBA
Цвет шрифта

GetCellFontColor(xlRange As Range) код самостоятельно. ЗдесьКазанский того как входиш: Например, Надстройка для в другую форму, 130, на листеВот тема с этого в строке не сложно, но числовой код фона со значением

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

типами данных. Так заливки. в ответ на в Excel. Dim indRow, indColumn Вы найдёте отличный: Формула ничего не в строку функция суммирования по цвету

где умножается наа сумма оставшихся

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

ivz4sure форума Microsoft: 6 просто замените у каждого пользователя ячейки (в MSPast Due или иначе, ВыВсе перечисленные далее формулы один из комментариевКогда все закулисные действия As Long Dim готовый код (написанный удаляет. 26.10.2011, 08:37

то фигурные скобки заливки,шрифта, формату ставку/час и выводится белых на 150.:Суммирование по ячейкам, свойство свои требования: ячейки EXCEL все цвета. можете попытать счастье работают по такому читателей (также нашим будут выполнены, выберите arResults() Application.Volatile If

нашим гуру Excel), Вас она, как теряются я какTyron сумма з/п.Znilem выделенным цветомInterior с суммами должны имеют соответствующий числовой

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

Sum и google в
же принципу. гуру Excel) и ячейки, в которые xlRange Is Nothing и всё, что я понял, устроила, понял они и: С надстройкой получилосьТолько у стажеров: Какие наши годы. , мы наверное не
julyttна быть в определенном код). Для этого– это сумма поисках идеального решения,CountCellsByFontColor(диапазон, код_цвета) выполняет именно те

 нужно вставить результат, Then Set xlRange Вам потребуется сделать за исключением возможности

 игнорируют текст и подсчитать количество ячеек ставка чуть ниже. «Мы все учились поняли друг друга,: спасибо за подсказкуFont месте, необходимо учесть

 нам потребуется функция, значений всех ячеек и если Вам– считает ячейки действия, которые упомянул
и введите в = Application.ThisCell End
– это скопировать
брать диапазоны с сразу получается сбой по цветам.
поэтому и приходится понемногу, чему-нибудь. » Может
с Вашей формулой буду читать

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

 красного цвета в удастся найти его,

 с заданным цветом автор комментария, а них функцию

 If If xlRange.Count его и вставить нескольких листов. столбец не просчитываетсяА как сделать
мудрить ))) попробуете — не хоть с первой,RAN

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

CountCellsByColor > 1 Then в свою рабочуюДавайте пример, и и в ячейке

Подсчёт ячеек по цвету, и сумма их колличества (Макросы/Sub)

 чтобы при измененииVaska боги горшки. хоть со второй,: Почему так категорично?
Если вам нужно подсчитывать пр.). одна обычная функцияQty.
опубликуйте здесь свою
SumCellsByColor(диапазон, код_цвета) и сумму ячеек
: ReDim arResults(1 To
книгу. что нужно получить.
показывает «0» или
цвета ячейки автоматически
: или может отдельно
ЦитатаVaska пишет: проблема
работает как иjulytt не сумму покрашенных

Помечать ячейки цветом, используя этого не умеет.
, то есть общее находку!– вычисляет сумму определённого цвета на
CountCellsByColor(диапазон, код_цвета) xlRange.Rows.Count, 1 ToСчитаем и суммируем поКазанский
же если нужно
пересчитывалось и их вынести в столбец
в том, что работало, только при
: сисадмины неразрешают ничего определенным цветом ячеек,
заливку или цвет Используем макрофункцию ПОЛУЧИТЬ.ЯЧЕЙКУ(),
количество элементов с
Код VBA, приведённый ниже,
ячеек с заданным
всех листах данной
В этом примере мы
xlRange.Columns.Count) For indRow цвету, когда ячейки
: А, Вам нужен

 поменять диапазон количество? ставку стажеров (т.к. я с макросами изменении значений! А устанавливать а всего лишь шрифта, очень удобно которая возвращает код отметкой преодолевает все указанные цветом заливки. книги. Итак, вот

 используем формулу = 1 To
раскрашены вручную
один и тотDophinЗ.Ы. Пример в она не постоянная

 не дружу. И эту возможно как-то сделатьSerge_007 их количество, то и наглядно. Если цвета заливки ячейкиPast Due выше ограничения иSumCellsByFontColor(диапазон, код_цвета) этот код:=CountCellsByColor(F2:F14,A17) xlRange.Rows.Count For indColumnСчитаем сумму и количество же диапазон на: формулу массива нужно файле. Подскажите кто у них) и — ВАШУ - так, что и: Надстройка — это наша функция будет вы не дальтоник, (она может много,. работает в таблицах– вычисляет суммуFunction WbkCountCellsByColor(cellRefColor As, где = 1 To ячеек по цвету нескольких листах?

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

 при изменении цвета просто книга Excel, еще проще. Замените

 конечно 🙂 Трудности но нам потребуетсяColor Microsoft Excel 2010 ячеек с заданным Range) Dim vWbkResF2:F14 xlRange.Columns.Count arResults(indRow, indColumn)

Сумма ячеек с разными цветами и данными

 во всей книгеЛисты идут подряд, массива всегда.Hugo
к обычной ставке, решать нам за ячейки (пример: диапазон в которой хранятся
в ней 7-ю возникают тогда, когда только это ее– это шестнадцатеричный и 2013, с
цветом шрифта.
Dim wshCurrent As
– это диапазон, = xlRange(indRow, indColumn).Font.ColorСчитаем и суммируем по
или будете указыватьТиго: Никак.
а выделенные -
вас. Каким же, A1:С3, в нем макросы строку на: по такой раскрашенной
свойство). код цвета выделенной
любыми типами условного

GetCellFontColor(ячейка) Worksheet Application.ScreenUpdating =

 содержащий раскрашенные ячейки, Next Next GetCellFontColor цвету, когда к
список листов?: СпасибоНо можно сделать
пониженная . .

 подскажите, макаром.  две ячейки коричневые,Как Вы будетеSum = Sum + таблице возникает необходимостьПримечание: ячейки, в нашем
форматирования (и снова– возвращает код False Application.Calculation = которые Вы хотите = arResults Else ячейкам применены правилаTikr
Tikr по событию выделения . .
Vaska одна оранжевая, я

 использовать ДРУГИЕ макросы, 1 сделать отчет. ИМакрофункции — это набор случае спасибо нашему гуру!).
цвета шрифта в xlCalculationManual vWbkRes = посчитать. Ячейка GetCellFontColor = xlRange.Font.Color условного форматирования: Диапазон один и: Заранее выражаю благодарность ячейки — хотяNic70y

: ))) да не меняю цвет одной если макросы изК сожалению изменение цвета если фильтровать и

 функций к EXCELD2 В результате он выбранной ячейке. 0 For Each

A17 End If End

Предположим, у Вас есть тот же, листа всем создателям и это костыли, и: Можно обойтись совсем предлагаю, просто ищу, оранжевой на коричневый, MyAddin ? заливки или цвета сортировать по цвету

 4-й версии, которые. выводит количество раскрашенныхGetCellColor(ячейка) wshCurrent In Worksheets

– содержит определённый Function Function CountCellsByColor(rData таблица заказов компании, идут по порядку авторам сообщений!
ненадёжные, и тормозные без всяких макросов. может есть какой

 и хочется, чтобыRAN шрифта ячейки Excel

 Excel в последних нельзя напрямую использоватьЕсли у Вас возникли ячеек и сумму– возвращает код wshCurrent.Activate vWbkRes = цвет заливки, в As Range, cellRefColor в которой ячейки 1,2,3Собственно вопрос, как в целом.Копируем серую ячейку другой доступный вариант.

 сразу всё считалось,: Ну, если надстройку

 не считает изменением версиях научился, то на листе EXCEL трудности с добавлением
значений в этих
цвета заливки в vWbkRes + CountCellsByColor(wshCurrent.UsedRange, нашем случае красный. As Range) As в столбце

Но раз уж определить сколько человекСтавьте подсчёт на и вставляем кудаЮрий М правда если это низзя, тогда отдельно ее содержимого, поэтому суммировать по цвету современных версий, а

 скриптов в рабочую ячейках, независимо от выбранной ячейке.
cellRefColor) Next Application.ScreenUpdatingТочно таким же образом Long Dim indRefColorDelivery вопрос на эту
встречаются в таблице кнопку. нужно.: «Обычные» формулы не возможно) а с зти макросы здесь.
не запускает пересчет до сих пор можно использовать только книгу Excel, например,

 типа условного форматирования,Итак, посчитать количество ячеек = True Application.Calculation Вы записываете формулу As Long Dimраскрашены в зависимости тему, я думаю через каждые 3P.S. И кстатиVaska умеют распознавать цвет,

 этими формулами считаетсяНадстройка для суммирования формул. То есть
не умеет. в качестве Именованной формулы. ошибки компиляции, не

 применённого на листе. по их цвету = xlCalculationAutomatic WbkCountCellsByColor
для других цветов, cellCurrent As Range
от их значений: будет интересно увидеть ячейки? у Вас в

: Идея хорошая, но поэтому воспользуйтесь одним только тогда, когда по цвету заливки,шрифта,

 при перекрашивании исходныхЧтобы исправить этот существенный Макрофункции — промежуточный работающие формулы иSub SumCountByConditionalFormat() Dim и вычислить сумму = vWbkRes End
которые требуется посчитать Dim cntRes AsDue in X Days различные вариации,(различные диапазоны,
GIG_ant формуле баксов не не совсем работает.

 из предложенных вариантов. я вставлю ячейку формату

 ячеек с числами недостаток можно использовать вариант между обычными так далее, Вы indRefColor As Long

 значений в раскрашенных Function Function WbkSumCellsByColor(cellRefColor в таблице (жёлтый Long Application.Volatile cntRes
– оранжевые, листы содержащие текст)
: как то так:

 хватает — диапазонпопробовал на однойZ или поменяю фамилииjulytt в другие цвета
несложную пользовательскую функцию

 функциями и функциями можете скачать рабочую Dim cellCurrent As ячейках оказалось совсем As Range) Dim
и зелёный). = 0 indRefColor

DeliveredКак вариант из=СУММПРОИЗВ(НЕ(ОСТАТ(СТРОКА($B$2:$B$22)-1;3))*($B$2:$B$22=E2))

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

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

 на Visual Basic, VBA. Для работы книгу Excel с Range Dim cntRes

 не сложно, не vWbkRes Dim wshCurrentЕсли в раскрашенных ячейках = cellRefColor.Cells(1, 1).Interior.Color– зелёные, чего должна состоятьКазанский

 наврать!из 10 часовANik её) но на

 участие и помощь. нашей функции пересчитываться которая позволит нам с этими функциями
примерами и с As Long Dim так ли? Но As Worksheet Application.ScreenUpdating содержатся численные данные
For Each cellCurrentPast Due формула: диапазон x,

: Посчитать — да,Tyron
по 150р должно: А «необычные» умеют заливку нету реакции( Разбираюсь. Надеюсь все не будет. суммировать ячейки с
требуется сохранить файл готовыми к использованию
sumRes Dim cntCells что если Вы = False Application.Calculation (например, столбец In rData If– красные.

 лист x+n1, диапазон можно формулой, а

: Спасибо! было отняться 200р, распознавать цвет иivz4sure получиться.Полностью решить эту проблему определенным цветом. в формате с функциями As Long Dim

 не раскрашиваете ячейки = xlCalculationManual vWbkResQty. indRefColor = cellCurrent.Interior.ColorТеперь мы хотим автоматически y, лист y+n2, вывести имена -

Условие по цвету ячейки

Мария а отнялось 700. даже больше: Хотя, если пользоватьсяivz4sure
невозможно, но можноОткройте редактор Visual Basic: макросами *.xlsmCountCellsByColor indCurCell As Long вручную, а предпочитаете = 0 Forв нашей таблице), Then cntRes = сосчитать количество ячеек n кол-во(имя) листов проще с UDF:: подскажите пожалуйста, могуNic70yПосмотрите вот это кнопкой «Пересчёт» всё: ее существенно облегчить.

В Excel 2003 иСделайте активной ячейкуи
cntRes = 0 использовать условное форматирование, Each wshCurrent In

Сумма определенных ячеек в столбце

 Вы можете суммировать cntRes + 1 по их цвету, для диапазона, искомоеTikr ли сделать так,: В каком месте? сообщение: там же работает)200?’200px’:»+(this.scrollHeight+5)+’px’);»>Function СчетЕслиЦвет(Oblast As Range, Для этого в старше для этогоВ7
SumCellsByColor

 sumRes = 0 как мы делали
Worksheets wshCurrent.Activate vWbkRes значения на основе

 то есть сосчитать значение: Спасибо еще раз! чтобы в графике, (возможно где-то ошибся пример определения цветаRAN Obrazec As Range) третьей строке нашей нужно выбрать в(это важно, т.к., и испытать их cntCells = Selection.CountLarge это в статьях = vWbkRes + выбранного цвета ячейки,

 cellCurrent CountCellsByColor = количество красных, зелёныхИзвини если непонятно Очень помогаете!

 где проставлены все в формуле)

 фона формулой. Но: Нельзя никак. Совсем As Variant функции используется команда
меню мы будем использовать на своих данных. indRefColor = ActiveCell.DisplayFormat.Interior.Color Как изменить цвет

 SumCellsByColor(wshCurrent.UsedRange, cellRefColor) Next используя аналогичную функцию
cntRes End Function

 и оранжевых ячеек написано))Вопрос такой еще, сроки платежей, считатьVaska

 для этого варианта никак.Dim Summa1 As
Application.Volatile TrueСервис — Макрос - относительную адресацию вУрок подготовлен для Вас For indCurCell = заливки ячеек и Application.ScreenUpdating = True

SumCellsByColor Function SumCellsByColor(rData As на листе. Как

P.S. в файле а если такие только оплаченные, при
: Я вот чего требуется промежуточный столбец.ivz4sure

 Variant, c As. Она заставляет Excel Редактор Visual Basic

 формуле) командой сайта office-guru.ru 1 To (cntCells
Как изменить цвет Application.Calculation = xlCalculationAutomatic: Range, cellRefColor As
я уже сказал в конце «итого»

 таблицы расположены на выделении их цветом. подумал, если сделатьvikttur: Ребят, да и
Range, ObrazecCvet As пересчитывать результаты нашей (Tools — MacroВ Диспетчере имен введитеИсточник: https://www.ablebits.com/office-addins-blog/2013/12/12/count-sort-by-color-excel/
— 1) If
заливки строки, основываясь WbkSumCellsByColor = vWbkResSumCellsByColor(диапазон, код_цвета) Range) Dim indRefColor выше, прямого решения

Tikr нескольких листах, можно Т.К., нужна сумма стиль с форматом
: Это тоже из так пойдёт, лишний Long функции при изменении

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

Перевел: Антон Андронов indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color на значении ячейки? End FunctionКак показано на снимке As Long Dim этой задачи не: я так понимаю ли это учесть только оплаченных

 ячейки не просто разряда VBA, но раз кнопочку можноObrazecCvet = Obrazec.Interior.Color любой ячейки на Editor)Назовите ее ЦветАвтор: Антон Андронов

Then cntRes =Если Вы применили условное

Добавьте этот макрос точно экрана ниже, мы cellCurrent As Range существует. Но, к решение этого вопроса
всё в однойPelena время (10:00), а

 спрятанного. Недомакрос или нажать), всем большоеFor Each c листе (или поВ новых версиях Excel
Закройте Диспетчер именПросуммируем значения ячеек в cntRes + 1 форматирование, чтобы задать также, как и использовали формулу:
Dim sumRes Application.Volatile счастью, в нашей всё таки будет. ячейке? :): время»с» (10:00с) переформула спасибо за помощь,
In Oblast.Cells нажатию
2007-2013 перейти наВведите в ячейку

 зависимости от цвета sumRes = WorksheetFunction.Sum(Selection(indCurCell), цвет заливки ячеек предыдущий код. Чтобы
Похожие публикации: