Как я уже писал ранее, профессиональная работа в Excel рано или поздно приведет вас к необходимости написания собственных функций. Довольно часто пользователи «раскрашивают» ячейки в разные цвета. Если потом возникает необходимость просуммировать значения в выделенных ячейках, то, к сожалению, в Excel нет такой стандартной функции. Тем не менее, сумма по цвету ячеек в Excel может быть найдена!
Небольшой код VBA решит ваши проблемы. Для облегчения восприятия последующего материала откройте Excel-файл (он запакован в Zip, так как на сайте размещение файлов, содержащих макросы запрещено).
1. Убедитесь, что среди вкладок на ленте Excel, присутствует «Разработчик»:
Скачать заметку Сумма по цвету в формате Word
Скачать заметку Сумма по цвету в формате pdf
Скачать файл примера в формате Excel с поддержкой макросов Сумма по цвету. На основании комментариев добавил в Excel-файл код функции КолЦвет — определяет число ячеек выделенного цвета, СумНеЦвет — определяет сумму значений в ячейках выделенных любым цветом (не белого цвета)
2. Если такой закладки вы не видите, щелкните на кнопке Officeв левом верхнем углу и затем на кнопке «Параметры Excel»:
3. В открывшемся окне «Параметры Excel» перейдите на вкладку «Основные» и поставьте галочку в строке «Показывать меню Разработчик на ленте». Нажмите Ok
4. Создайте на листе Excel диапазон со значениями; несколько ячеек раскрасьте:
5. Перейдите на вкладку Разработчик и щелкните на VisualBasic:
6. У вас откроется окно VBA, содержащее окно VBAProject:
7. Если окна VBAProjectнет на экране
щелкните на меню View — Project Explorer:
8. Если вы хотите использовать создаваемый код VBA в любом Excel-файле, вам следует его записать в VBAProject, относящийся к Personal.xlsb. Если вы хотите применять код только в одном файле, с которым вы сейчас работаете, сохраните код в VBAProject`е именно этого файла (в нашем случае Сумма по цвету.xlsm):
9. Допустим, вы решили, что создаваемая функция будет использоваться в дальнейшем в различных файлах. Щелкните правой кнопкой мыши на VBAProject(Personal.xlsb) и выберите Insert — Module
Появится окно нового модуля, в которое следует перенести код:
|
1 2 3 4 5 6 7 8 9 10 11 |
Function СумЦвет(диапазон As Range, критерий As Range) As Double ' Определяет сумму значений в ячейках "диапазона", ' цвет которых совпадает с цветом в ячейке "критерий" Application.Volatile True Dim i As Range For Each i In диапазон If i.Interior.Color = критерий.Interior.Color Then СумЦвет = СумЦвет + i End If Next End Function |
Номер вашего модуля (у меня он 5) будет зависеть от числа ранее созданных модулей.
Несколько слов о коде:
Function СумЦвет (диапазон AsRange, критерий AsRange) AsDouble/ Задает пользовательскую функция под названием СумЦвет с двумя параметрами: диапазоном суммирования и критерием – ячейкой, по цвету которой определяется, значения в каких ячейках суммировать.
Application.Volatile True/ Заставит нашу функцию пересчитываться при любом изменении значения в любой из ячеек на листе
Dim i AsRange/ Определяет переменную i, как диапазон ячеек
Далее следует цикл:
For Eachi In диапазон / Для всех ячеек из выбранного диапазона
If i.Interior.Color = критерий.Interior.Color Then/ Если цвет ячейки совпадает с критерием
СумЦвет = СумЦвет + i.Value/ то добавляем значение, хранящееся в ячейке в сумму
End If
Next
10. Вы создали пользовательскую функцию СумЦвет, которую можно найти в категории «Определенные пользователем»
11. Окно мастера функции выглядит также, как и для стандартной функции Excel
Хочу обратить ваше внимание на две особенности функции СумЦвет:
- При изменении значения в одной из ячеек происходит автоматический пересчет значения функции СумЦвет. Если вы поменяли только цвет ячейки, автоматический пересчет не произойдет. Нажмите F9.
- К сожалению, функция не работает, если ячейки раскрашены с помощью условного форматирования. Это ограничение можно преодолеть, если применить правила, использованные в условном форматировании, в функциях СУММЕСЛИ и СУММЕСЛИМН. Подробнее см. Подсчет и суммирование ячеек, отвечающих критерию условного форматирования
На основании комментариев, появившихся после первой публикации заметки, добавил в Excel-файл код функций:
- КолЦвет – определяет число ячеек выделенного цвета
- СумНеЦвет – определяет сумму значений в ячейках выделенных любым цветом (не белого цвета)
- КолНеЦвет – определяет число выделенных ячеек (не белых)
Добрый день! Спасибо большое за подробное описание этой очень нужной функции. Ваш код добавила, все работает, но при суммировании почему-то округляются все десятичные разряды после запятой (в моем случае копейки, счет в рублях) и в итоге сумма получается неверная на несколько рублей…..не подскажете как в данной ситуации скорректировать функцию?
Наталья, Вы нашли неточность в коде. Я указал, что функция возвращает данные по типу «целые», поэтому она и округляла числа. Замените последнее слово в первой строчке кода и будет Вам счастье:
Function СумЦвет (диапазон As Range, критерий As Range) As Single
Спасибо за замечание! 🙂
Уведомление: Как суммировать ячейки в excel, выделенные цветом | Все о Excel для трейдера и не только….
День добрый, спасибо. Вопросы: 1. как сделать так, чтобы шел расчет не суммы выделенных ячеек, а счет количества ячеек, соотв. данному критерию, т.е. выделенный цветом. 2. можно ли убрать обязательность критерия «цвет», и задать общее кол-во выделенных разными цветами, т.е. если выделен вообще цветом (отличающегося от белого, значит значение «Правда»).Спасибо.
Игорь, для подсчета числа ячеек определенного цвета воспользуйтесь функцией КолЦвет
Вот её код:
Function КолЦвет(диапазон As Range, критерий As Range) As Single
Application.Volatile True
Dim i As Range
For Each i In диапазон
If i.Interior.Color = критерий.Interior.Color Then
КолЦвет = КолЦвет + 1
End If
Next
End Function
Для подсчета суммы в ячейках любого цвета отличающегося от заданного (белого), воспользуйтесь функцией СумНеЦвет; при этом в качестве критерия выберите ячейку с цветом, который считать не надо:
Function СумНеЦвет(диапазон As Range, критерий As Range) As Single
Application.Volatile True
Dim i As Range
For Each i In диапазон
If i.Interior.Color <> критерий.Interior.Color Then
СумНеЦвет = СумНеЦвет + i.Value
End If
Next
End Function
Успехов!
Добрый день !!!
Хотелось как лучше, получилось как всегда….
Происходят непонятные мне вещи:
при работе с мастером функций все срабатывает, вроде бы, нормально: и перечень ячеек формируется, и критерий как у Вас в примере («4»); а вот результат (третье равенство) не отображается и в ячейке результата выскакивает «#ЗНАЧ!», хотя формула там =PERSONAL.XLSB!СумЦвет(A2:E8;4).
Все, вроде, делаю по Вашей рекомендации.
А все мимо.
Будьте добры, подскажите в чем дело …
Спасибо.
Как ни стоял на ушах:
и
Sub SumColor()
Function СумЦвет(…….)
…..
End Function
End Sub
и просто
Function СумЦвет(…….)
…..
End Function
пробовал, — результат один …
Дмитрий, не знаю, где Вы нашли 4-ку в параметрах функции:
=PERSONAL.XLSB!СумЦвет(A2:E8;4)
Там должна быть не 4-ка, а ссылка на ячейку, которая задает цвет суммируемых ячеек, например, =PERSONAL.XLSB!СумЦвет(A2:E8;B4)
Здравствуйте! Большое спасибо за данную помощь в работе с екселем! Подскажите, пожалуйста, как можно изменить код VBA, чтобы суммировались ячейки с выделенным цветом шрифтом, а не самой ячейкой. Заранее спасибо за ваш ответ
Посмотрите заметку: Excel. Подсчет числа ячеек, содержащих текст определенного цвета
спасибо за подсказку, но вопрос был не про количество ячеек, а про сумму в ячейках, где шрифт выделен другим цветом. Но в итоге я поменяла немного код. В итоге все работает))
Function СумЦветТст(диапазон As Range, критерий As Range) As Single
Application.Volatile True
Dim i As Range
For Each i In диапазон
If i.Font.Color = критерий.Font.Color Then
СумЦветТст = СумЦветТст + i.Value
End If
Next
End Function
Не понимаю почему, но у меня по цвету не суммирует, а показывает общий итог по столбцу, хотя цвет определяю. Все сделала по инструкции и формула работает все как у Вас, а итог неверный.
Igor — 24-02-2012
День добрый, спасибо. Вопросы: 1. как сделать так, чтобы шел расчет не суммы выделенных ячеек, а счет количества ячеек, соотв. данному критерию, т.е. выделенный цветом. 2. можно ли убрать обязательность критерия «цвет», и задать общее кол-во выделенных разными цветами, т.е. если выделен вообще цветом (отличающегося от белого, значит значение «Правда»).Спасибо.
Добрый день, Вы не совсем корректно ответили на этот вопрос, и меня ответ тоже интересует. Речь идет о создании функции КолНеЦвет, аналогичной по свойствам функции СумНеЦвет. Подскажите, пожалуйста, как это сделать.
Даша, вот Ваша функция КолНеЦвет
Function КолНеЦвет(диапазон As Range, критерий As Range) As Single
‘ Определяет число ячеек «диапазона»,
‘ цвет которых не совпадает с цветом в ячейке «критерий»
Application.Volatile True
Dim i As Range
For Each i In диапазон
If i.Interior.Color <> критерий.Interior.Color Then
КолНеЦвет = КолНеЦвет + 1
End If
Next
End Function
Большое Вам спасибо, сейчас попробую!
Сергей! Можно ли суммировать ячейки, содержащие определенный символ (букву)?
Света, спасибо за вопрос 🙂 Отвечая на него, я написал заметку Excel. Подсчет числа ячеек, содержащих символ (букву)
Спасибо, Сергей. Очень жаль что функция не работает с условным форматированием. 🙁
Сергей, подскажите, пожалуйста, как сделать, чтобы эта функция работала на иностранных компьютерах (с англ. яз)? На моем компе работает, а у коллеги не считает, видимо из-за КолЦвет или чего еще в формуле.
Подскажите пожалуйста почему у меня в поле где указана формула высвечивается код ошибки #Имя?
Сергей, пришлите пример файла Excel с добавленной Вами пользовательской функцией, выдающей ошибку #Имя?, попробую помочь…
Сергей, спасибо за помощь, разобрался сам. В Excel была отключена функция макроса. включил и все заработало. Спасибо еще раз за Вашу статью. Очень помогла.
Добрый день. Есть ли возможность, чтобы Ваша функция работала при использовании фильтра, как функция ПРОМЕЖУТОЧНЫЕ ИТОГИ? Спасибо
К сожалению, этого моя функция не умеет… 🙁
Огромное спасибо! Очень пригодилось, очень удобно! Еще раз спасибо!
Здравствуйте. Специально искал суммирование содержимого ячеек по цвету (в Excel). Очень понравилась Ваша программка.Только вот не пойму:почему-то сумма содержимого ячеек одного цвета выполненная автосуммой и с помощью функции СумЦвет при большом количестве ячеек (примерно 100) расходятся на 0,3-0,4. В бухгалтерских делах не катит. Помогите,пожалуйста, разобраться.
Это мой «косяк». Хотел сэкономить на копейку, а получилось, как всегда… Надо изменить тип функции (в файлах поправил и загрузил на сайт новые).
Было: Function СумЦвет(диапазон As Range, критерий As Range) As Single
Заменил на: Function СумЦвет(диапазон As Range, критерий As Range) As Double
Огромное спасибо! Всё работает. И вообще, зайдя случайно на Ваш сайт, был очень сильно поражен: такое обилие информации различных аспектов и направлений… ГОЛОВА!
Сергей, добрый день!
Столкнулся с проблемкой и набрел на ваш сайт. Если сможете помочь, буду весьма благодарен.
Дано: есть два столбца — первый с текстом, выделенный цветом, второй — с числовыми значениями, тоже выделенный цветом.
Задача: посчитать сумму значений во втором столбце при выполнении двух условий: ячейка соответствует заданному цвету и текстовое значение смежной ячейки в первом столбце соответствует заданному текстовому значению.
Предлагаю использовать третий столбец, в котором с помощью функции КолЦвет отразить 1 или 0, в зависимости от того, «правильного» ли цвета ячейка в столбце 2. А далее использовать стандартную функцию Excel СУММЕСЛИМН с двумя условиями: в столбце 3 стоит единица, в столбце 1 «правильный» текст. Если объяснение путанное, пришлите Excel-файл по почте.
День добрый.
Подскажите, почему в вашем примере происходит некоторая задержка во времени после ввода значения в ячейку?
Компьютер не старый. Такая же задержка происходит и в больших файлах.
А макрос очень понравился!
Спасибо.
приогромнейшее спасибо за разъяснение данной функции. Давно искал. Существенно облегчило жизнь. Спасибо.
А как посчитать число ячеек с определенным символом-текст, и к тому же определенного цвета? Подскажите пожалуйста
Евгения, возможно, вам пригодятся идеи, содержащиеся в заметках Подсчет числа ячеек, содержащих текст определенного цвета и Число вхождений символа в текст
Спасибо!
Добрый день!
Не подскажите функцию для подчета количества ячеек по ячейкам залитым цветом и содержащим числовое значение.
Одно из возможных решений… Если данные содержатся в столбце 1, предлагаю использовать два дополнительных столбца. В столбце 2 с помощью функции КолЦвет отразить 1 или 0, в зависимости от того, «правильного» ли цвета ячейка в столбце 1. В столбце 3 использовать формулу =ЕЧИСЛО(A1)*1, которая вернет 1, если содержится число, и 0, если не число. А далее использовать функцию =СУММПРОИЗВ по столбцам 2 и 3. Если объяснение путанное, пришлите Excel-файл по почте, я вышлю в ответ решение.
Все работает идеально! Спасибо огромное!
Народ подскажите чайнику!!!!!
Как можно реализовать функцию суммирования.
Есть таблица где в строке по месяцам последовательно идёт дата, некая сумма и баллы.
ввиду того что что балы менее 20 отсечь их можно через «если», а как сделать чтобы при суммировании строки не учитывалась дата
Сергей, ответ на ваш вопрос вы найдете в заметке Excel. Суммирование значений в четных (или кратных) строках (столбцах)
Спасибо попробую.
Спасибо большое!
Привет! Спасибо за тему — Суммирование по ячейкам, выделенным цветом…. очень помогла. Но есть вопросы, на которые не могу найти ответ…
Таблица большая, около 60 столбцов и 70 строк с разными цветами ячеек, которые меняют цвет по мере моей необходимости (требуется для работы). Воспользовался вашей подсказкой, все настроил и все работало, но стоит закрыть файл или открыть его на другом компе и все надо делать заново. Как это исправить?
В окне VBA, есть только строка VBAProject (Сумма по цвету.xlsm), а VBAProject (Personal.xlsb), как ее настроить или добавить? Спасибо!
Вячеслав, проверьте несколько моментов:
1) код VBA сохранен в модуле относящемся к вашему файлу — VBAProject (Сумма по цвету.xlsm); в этом случае именно в этом файле код будет работать на любом ПК;
2) вы сохраняете файл с расширением .xlsm, то есть файл Excel с поддержкой макросов;
3) при открытии файла вы разрешаете использовать макросы; если окно запрашивающее разрешение на использование макросов не всплывает, пройдите по меню: параметры Excel — Центр управления безопасностью — закладка «Параметры макросов»; возможно у вас выбрана опция «Отключить все макросы без уведомления»; замените ее на «Отключить все макросы с уведомлением»
Спасибо! Все работает…
Здравствуйте! Спасибо за помощь… С цветными ячейками я разобрался и все работает. С полученным файлом работаю на 5 компах и все удачно… Осталась нерешенной маленькая проблема — при СумЦвет выбираются только окрашенные ячейки заложенного цвета, а ячейки без цвета (нет заливки) не считаются. Как сделать подсчет суммы в ячейках без цвета (нет заливки)
Немножко другая задача. На плане цветом отмечены ячейки. Сверху в строку указаны даты. Нужно определить столбец ячейки с цветом по образцу.
Здравствуйте! Подскажите пожалуйста, как правильно сделать? Лист 1 — список людей 180 человек. Лист 2 — требование на получение спец. средств. Список людей меняется в большую или меньшую сторону, также меняются фамилии людей в списке (принят на работу или уволился).
Меня интересует, можно сделать так, чтоб при изменении численности людей в списке происходили изменения и в требовании? Спасибо!
Добрый день Baguzin!
Скажите пожалуйста, почему при изменений количества закрашенных ячеек в выделенном диапазоне сумма не меняеется
Ильдар, Excel не пересчитывает значения формул на листе после изменения форматирования. Нажмите F9, чтобы обновить формулы принудительно.
Добрый вечер! Очень пригодилась Ваша функция!!! Почти то, что нужно. Единственное…как сделать, чтобы при вычислении суммы значений окрашенных ячеек в расчет не брались буквенные значения? Т.е. в одной строке занесено 1 2 в в 1 2 в в 8 8 8 8 8 и т.д. (график работы) Необходимо посчитать сумму значений этого диапазона (он весь в цвете) при условии, что 1=11,5 и 2=11,5, а 8=8. Ваша функция замечательно работает с числами, а при появлении буквы выдает #ЗНАЧ. Помогите, пожалуйста! Очень нужно для работы!
Ольга, попробуйте видоизменить функцию на:
Function СумЦвет(диапазон As Range, критерий As Range) As Double
Application.Volatile True
Dim i As Range
For Each i In диапазон
If i.Interior.Color = критерий.Interior.Color Then
Select Case i.Value
Case 1
СумЦвет = СумЦвет + 11.5
Case 2
СумЦвет = СумЦвет + 11.5
Case 8
СумЦвет = СумЦвет + 8
End Select
End If
Next
End Function
Подскажите чайнику: как добавить код функции КолЦвет в свой комп?
Добрый день!
При работе с функцией «СумЦвет» столкнулся с тем, что функция не отличает оттенки цветов.
Ячейки выделенные красным и бордовым цветами для нее одно и тоже.
Возможно ли сделать так, чтобы функция научилась различать оттенки?
С уважением,
Дмитрий.
Дмитрий, классное замечание! 🙂 В Excel имеется стандартная палитра, которая содержит 56 цветов. Ее можно увидеть, если вызвать «Цвет заливки» или «Цвет текста» в меню Главное — Шрифт. Если вы используете оттенки (выбранные в палитре «Другие цвета»), то Excel подбирает параметр ColorIndex ближайший к «нестандартному» цвету.
Заменил в коде строку
If i.Interior.ColorIndex = критерий.Interior.ColorIndex Then
на строку
If i.Interior.Color = критерий.Interior.Color Then
Пробуйте! Теперь функция СумЦвет должна понимать все оттенки…
Добрый день.
Спасибо вам большое. Сняли кучу ручной работы. Добавил ваш сайт в закладки. Вы просто четко все расписали.
Я всегда знал, что с помощью Excel можно вторить чудеса )))
Сергей, установите на сайте номера кошельков, куда можно сбросить денюшку по webmoney (так сказать на развитие проекта….).
Воспользовался Вашим ответом для подсчета выделенных цветом ячеек. Большое спасибо! Все получилось!
Подскажите, пожалуйста! есть 2 столбца, в первом столбце идут занчения — карандаш, ручка, пенал и т. п., во втором их стоимостное значение — 10 р., 15 р., и т.д., как сделать формулу, чтобы автоматически считалось на какую сумму всего карандашей, ручек и т.д.???
Вита, воспользуйтесь стандартной функцией Excel =СУММЕСЛИ()
Спасибо, очень пригодилось в работе. Подскажите пожалуйста чайнику как упростить формулу для суммирования ячеек по шрифту без применения т.е. курсив, жирный и т.д. Можно ли обойтись в формуле без ячейки-критерия
Сергей, если я правильно понял, вас интересуют функции, позволяющие находить число ячеек полужирного и курсивного начертания. Вот они:
Function КолЖирн(диапазон As Range) As Long
Application.Volatile True
Dim i As Range
For Each i In диапазон
If i.Font.Bold = True Then
КолЖирн = КолЖирн + 1
End If
Next
End Function
Function КолКурс(диапазон As Range) As Long
Application.Volatile True
Dim i As Range
For Each i In диапазон
If i.Font.Italic = True Then
КолКурс = КолКурс + 1
End If
Next
End Function
Baguzin, СПАСИБО!!!
Уважаемый Сергей!
Большое спасибо за Вашу статью.
Но мне нужно количество ячеек подсчитать, но не с жирным шрифтом, а с зачеркнутым.
Как он называется, т.е. что вместо Bold указывать?
Вроде такая мелочь, а нигде не могу найти.
Заранее благодарю.
Здравствуйте,подскажите пожалуйста как сделать следующее: имеется два столбца(в первом цифры,во втором фамилии),ячейки одинакого залиты(то что было сделано в июне залито желтым,то что в июле красным и тд…),так вот,Иванов есть залитый и желтым,и красным,и зеленым цветом,как сделать чтоб суммировалось все по отдельности,т.е. по цвету,то сколько он сделал в июне,июле,и т.д.?
Спасибо!
Вячеслав, насколько я понял, Вам нужно суммировать не по цвету, а по Иванову. Воспользуйтесь функцией =СУММЕСЛИ()
См. Excel-файл
Диана, вот Ваш код:
Function КолЗачеркн(диапазон As Range) As Long
Application.Volatile True
Dim i As Range
For Each i In диапазон
If i.Font.Strikethrough = True Then
КолЗачеркн = КолЗачеркн + 1
End If
Next
End Function
Добрый день. Спасибо за хорошую функцию с суммированием по ячейкам, с этим я разобрался, но у меня задачка немного сложней, которую никак не получается решить, подскажите пожалуйста решение:
Пример:
У меня есть две строки:
Первая строка (B2:M2)– это даты, формата: 01.01.2013;
Вторая строка (B3:M3)– это числа.
Мне необходим, скажем в ячейку А1 вывести сумму по второй строчке только тех ячеек, которые выделены определенным цветом и за определенный период времени согласно первой строчке (то есть, к примеру за определенный месяц).
Прошу помочь с такой задачей, а то я уже закипел)). Благодарю!
Антон, воспользуйтесь функцией =ЕСЛИ(И(КолЦвет(B3;$A$1)=1;МЕСЯЦ(B2)=$A$1);B3;0)
см. также zip-файл (в виде архива, так как провайдер не позволяет загружать на сайт Excel-файлы с поддержкой макросов)
Доброе день Сергей.
Спасибо за ответ в такой короткий срок. Но задачка стоит немного сложней, всех кого не спрашивал пока не смогли, помочь. Самое близко к решению это ваша функция.
В приложении я скинул пример.
Краткое описание:
1 лист – это исходные данные.
2 лист- это свод, в котором нужно посчитать построчно :
1. Общую сумму по каждой строке (это сделано 🙂 ).
2. Общую сумму по каждой строке по определенному цвету и за определенный месяц. Вот здесь мы с коллегами зависли полностью.
Надеемся на вашу помощь. Спасибо.
Антон, воспользуйтесь кодом:
Function СумЦветМес(д_сум As Range, дата As Range, цвет As Range, месяц As Integer) As Double
Application.Volatile True
Dim myCount As Integer
For myCount = 1 To д_сум.Columns.Count
If д_сум(myCount).Interior.Color = цвет.Interior.Color And Month(дата(myCount)) = месяц Then
СумЦветМес = СумЦветМес + д_сум(myCount).Value
End If
Next myCount
End Function
См. также файл
БОЛЬШОЕ ВАМ ЧЕЛОВЕЧЕСКОЕ СПАСИБО, от меня и моих коллег!!!
Здравствуйте! Не могли бы Вы мне помочь в следующем:
имеется диапазон B2:B30, в который вставляются данные. Эти данные могут быть залиты красным цветом, но не все и каждый раз разные ячейки этого диапазона могут быть залиты. А может быть и ни одна из ячеек не залита.
Так вот, нужно, чтобы макрос определял, если какая-нибудь ячейка диапазона B2:B30 залита красным, то в соответствующей ячейке диапазона F2:F30 проставлялось число 100.
Андрей, в ячейке F2 запишите формулу =ЕСЛИ(КолЦвет(B2;$F$1)=1;100;
"") и «протащите» ее по диапазону F2:F30. Естественно код функции КолЦвет должен быть доступен в вашей книге Excel. В ячейке F1 залейте цвет образцаСм. также файл
Спасибо большое, очень помогли!
Здравствуйте! Подскажите, пожалуйста, как изменить Вашу функцию, чтобы в диапазоне с числовыми и буквенными значениями посчиталась сумма только числовых значений и не выдавалась ошибка #ЗНАЧ!
Галина, добавьте проверку, что значение в ячейке является числом – IsNumeric(i). Получится код:
Function СумЦвет(диапазон As Range, критерий As Range) As Double
Application.Volatile True
Dim i As Range
For Each i In диапазон
If IsNumeric(i) And i.Interior.Color = критерий.Interior.Color Then
СумЦвет = СумЦвет + i
End If
Next
End Function
Спасибо Вам! Вы мне очень помогли!
Здравствуйте!
Подскажите, пожалуйста.
Дана таблица из 4 столбцов и n-строк.
Есть закрашенные ячейки.
Если в строке <=2 закрашенных ячеек, то их числовые значения надо сложить и разделить на 2, к этому частному затем прибавить сумму числовых значений незакрашенных ячеек.
Если закрашенных ячеек меньше 2, то найти общую сумму числовых значений в во всех ячейках данной строки.
Маргарита, думаю, что в условии задачи Вы допустили ошибку, так как один раз указали меньше или равно 2, а второй раз — меньше 2. Я решил задачу в предположении, что первый раз надо читать больше или равно 2.
Подробности см. в зазипованном Excel-файле
Респект, респект, респект!!!
Здравствуйте. Буду признательна за помощь. Дано 5 проектов производственных комплексов (ПК), даны необходимые объёмы финансирования на каждый год (за 5 лет) по каждому ПК, дана ожидаемая прибыль. Как вычислить инвестиционную привлекательность проектов? Следует лишь ответить инвестиционно привлекателен?: да/нет.
Наташа, почитайте заметку Оценка эффективности инвестиций. Надеюсь, найдете в ней ответы на свои вопросы.
Здравствуйте. Перечитал все комментарии и вопросы выше и вроде не нашел ничего подобного… Можно ли функцию «СумЦвет» как-нибудь не много доделать, чтобы «Критерий» был не один цвет а несколько? (Например, у меня в диапазоне 5 цветов и надо просуммировать не 1 цвет, а 2).
MrDevid, воспользуйтесь следующим кодом, который позволяет ввести любое число критериев (спасибо за помощь EducatedFool с форума клуба программистов).
Function СумЦвет(диапазон As Range, ParamArray критерии() As Variant) As Double
Application.Volatile True
Dim res As Boolean, cell As Range
For Each cell In диапазон.Cells
res = False
For Each ra In критерии
res = res Or ra.Interior.Color = cell.Interior.Color
Next
If res Then СумЦвет = СумЦвет + Val(Replace(cell,
",","."))Next cell
End Function
См. также зазипованный Excel-файл
Огромное Вам Спасибо!
Добрый день! С большой радостью нашла вашу статью, сделала все как надо. Создала таблицу с подсчетом материалов, зеленое — приход, красное — расход. Соответственно, в строке две формулы: СумЦвет для зеленых и красных. Потом высчитывается остаток и все хорошо. Но((( Возникла проблема( Долго прогружается. Когда входишь в файл, сначала долго снова прописывает атвоматом формулы. При этом внизу идет прогрузка в процентах. Даже когда все прогружено, ставишь число, из-за которого должно все пересчитаться (итого и соответственно остаток) и снова приходиться ждать, пока «пролагает». Неужели это из-за когда Excel стал так тупить? Подскажите(((
Анна, в принципе, я использовал довольно простой код, без дополнительных проверок, так что он должен работать быстро. Но… похоже, что Вы используете очень много формул СумЦвет. Естественно, код VBA — это относительно медленный механизм, поэтому, если у Вас сотни или тысячи формул, то вполне может тормозить. Попробуйте, сохранить файл с расширением xlsx (а не xlsm), т.е. без поддержки макросов. Если перестанет тормозить, значит проблема в макросах. Если тормоза сохранятся, значит проблема в другом. Посмотрите заметку Excel «тормозит». Что делать?
Добрый день подскажите пожалуйста ответ на такой опрос. У меня есть таблица с месяцем и прописаны некоторые функции. как сделать так что бы при выделении ячеек выходных (красным) цветом считала одна группа функций, при обесцвечивании другая группа функции??!!
Михаил, не вполне уверен, что понял Ваш вопрос. Пришлите в личку Excel-файл, и подробнее укажите, что требуется получить…
Добрый день. Большое спасибо за функцию. Подскажите, пожалуйста. Ваша функция КолЦвет почему то не хочет работать с условным форматирование. Возможно ли сделать так что бы считалась заливка которая распространяется через условное форматирование?
Спасибо!
Maksim, к сожалению мои функции не работают с условным форматированием. Код VBA для решения такой задачи слишком [для меня] сложный. Может быть, вам пригодятся идеи заметки Excel. Подсчет и суммирование ячеек, отвечающих критерию условного форматирования
Добрый вечер. Подскажите как нужно изменить вашу формулу, что бы подсчитывалась сумма цветных ячеек с разных листов. Т.е. на разных вкладках (в процессе работы могут создаваться новые) есть итоговые суммы выделенные вручную желтым цветом. Как сделать чтобы на первой странице считалась их сумма?
Спасибо!
Евгений, воспользуйтесь кодом:
Function СумЦвет(критерий As Range) As Double
‘ Определяет сумму значений во всех ячейках на всех листах, кроме исходного,
‘ цвет которых совпадает с цветом в ячейке «критерий»
Application.Volatile True
Dim wSheet As Worksheet
Dim i As Range
For Each wSheet In Worksheets
If wSheet.Name
"Лист1"ThenFor Each i In wSheet.UsedRange.Cells
If i.Interior.Color = критерий.Interior.Color Then
СумЦвет = СумЦвет + i
End If
Next
End If
Next
End Function
Обратите внимание, чтобы он сработал лист, на котором расположена формула, должен называться
"Лист1". При другом названии, поменяйте соответствующее место в коде.См. также Excel-файл (по правилам провайдера нельзя помещать файлы с кодом, поэтому файл заархивирован)
Baguzin — спасибо. Очень помогла Ваша формула.
У меня такая же проблема, не вижу VBAProject (Personal.xlsb). Проверила все пункты, но в списке только VBAProject («текущий файл»)
Ольга, скорее всего, вы никогда ранее не сохраняли макросы в Personal.xlsb. Файл Personal.xlsb по умолчанию отсутствует. Чтобы создать Personal.xlsb, в любой книге Excel запустите запись макроса. В открывшемся окне в поле «Сохранить в» выберите «Личная книга макросов». Сделайте что-нибудь в Excel, и остановите запись макроса. Вуаля)) Подробнее см.
Создание и обновление личной книгиP.S. После вопроса Ольги понял, что в предыдущем комментарии Вячеславу ответил не на тот вопрос…
Спасибо! Все получилось!!!
Спасибо за отличную помощь в подсчете раскрашенных ячеек, но почему-то функция не сохраняется в Excel, а работает тольтко в открытой книги. Может я что-то упустил???
Нужно сохранить книгу в формате .xlsm — книга Excel с поддержкой макросов
Большое спасибо за помощь!
Функция «КолЦвет» работает.
А как записать в VBAProject, относящийся к Personal.xlsb, у меня добавляется только VBAProject, относящийся только к этому файлу. Раскажите подробнее как исправить.
Сергей, см.
комментарийДобрый день, подскажите, пожалуйста, как посчитать количество цветных ячеек, содержащих число? По-моему такой же вопрос задавал Андрей, с ответом не разобралась.
Ирина, попробуйте следующий код
Function КолЦвет2(диапазон As Range, критерий As Range) As Double
Application.Volatile True
Dim i As Range
For Each i In диапазон
If IsNumeric(i) And i.Interior.Color = критерий.Interior.Color Then
КолЦвет2 = КолЦвет2 + 1
End If
Next
End Function
Не получилось, все равно считает общее колличество цветных ячеек
Пришлите файл в личку, посмотрю, что можно сделать.
Спасибо огромное, все работает
День добрый!
Подскажите как можно добавить в эту функцию чтоб считала количество ячеек которые выделены цветом и определенного месяца ?
Спасибо!
Камиль, посмотрите
комментарийк этой заметке. Возможно, решит ваши проблемы.Спасибо но немного не то ) или я просто торможу)
Мне просто нужно чтоб считала количество цветных ячеек выделенного диапазона и конкретного месяца
Здравствуйте.
Подскажите пожалуйста, как можно реализовать данное условие:
— Если в ячейке A1 прописано значение от 300 до 399, то закрасить серым 3и ячейки, а следующее значение писать в 4й.
— Если в ячейке А1 прописано значение от 500 до 599, то закрасить серым 5ь ячеек, а следующее значение писать в 6й
Sandro, для меня это довольно сложная задачка. Попробуйте обратиться на
форумКлуба ПрограммистовСпасибо за совет, но решил пока сам поскрипеть серыми клеточками. Если получится — поделюсь результатом…
Здравствуйте, просмотрела все комментарии, но что-то не нашла своего вопроса. Меня интересует можно ли исправить в диапазоне, чтобы включал не все значения. Например, у меня столбцы январь, февраль, …, дек. и каждый еще делится на цену, кол-во и сумму. Мне нужно, чтобы складывал только сумму по определенным месяцам, которые выделены цветом. По данной формуле у меня складывается все в кучу и цена, и количество, и сумма. Заранее спасибо!
Гульназ, пришлите пример (файл Excel) в личку и подробнее опишите, что вам нужно подсчитать. Попробую помочь.
Отправила вчера на baguzin@baguzin.ru
Огромное спасибо!
Гульназ, такого адреса нет. Направьте на s_bag@mail.ru.
Ок
Baguzin, просто, спасибо! 😉
Спасибо Вам огромное) Именно то что надо! В работе очень помогло. Еще раз спасибо!)
Заметил, что функция не работает, если в какой-нибудь из ячеек не число, а текст.
Есть ли возможность игнорировать такие ячейки?
Артем, я в одном из комментариев предложил решение; см. здесь
мне больше 50 лет, спец знаний по работе с компьютором нет, но всё получилось. Спасибо, всё очень понятно
Подскажите пожалуйста. Как прописать код так, чтобы объединенные ячейки одного цвета он считал как одну, а не как количество объединенных?
доброго времени суток. Помогите написать формулу, с условием если ячейка красного цвета то другая ячейка равна единице
Добрый вечер. все перечитал. ответа так и не нашел. необходимо посчитать сумму, например, зеленых ячеек с тестом: samsung
Попробуйте следующий код. У меня получилось…
Function КолЦвет(диапазон As Range, критерий As Range) As Double
‘ Определяет число ячеек «диапазона»,
‘ цвет которых совпадает с цветом в ячейке «критерий»
‘ а в ячейках содержится текст «samsung»
Application.Volatile True
Dim i As Range
For Each i In диапазон
If i.Interior.Color = критерий.Interior.Color And i =
"samsung"ThenКолЦвет = КолЦвет + 1
End If
Next
End Function
Работает! отлично. но нельзя ли сделать универсальный код? первое условие цвет. второе — текст. ???
Любой текст? Лишь бы текст, а не число?
да! вместо самсунга свой текст писать любой.
Может быть так?
Function КолЦвет(диапазон As Range, критерий As Range, текст As String) As Double
Application.Volatile True
Dim i As Range
For Each i In диапазон
If i.Interior.Color = критерий.Interior.Color And i = текст Then
КолЦвет = КолЦвет + 1
End If
Next
End Function
Отлично! вы как всегда на высоте. Советовал бы Вам объединить бы все выше сказанное в один универсальный код с, допустим, 5 условиями и было бы всем счастье.)))
какой алгоритм нужно прописать, что считал по двум критериям в двух диапазонах ?? на пример есть два города А и Б хочу посчитать количество ячеек одного цвета в городе А, где ячейки города А и Б находятся в одном столбце.
БЛАГОДАРЮ!
Большое СПАСИБО!)
Это проблема. Похоже, в этом случае тут потребуется гораздо больше кода…
Здравствуйте! 🙂
Мой вопрос: Есть ли возможность указать какое-то конкретное действие в коде, при котором начнётся цикл «For Each» в диапазоне в каждой формуле? Например если я поставлю значение какой-то ячейки в 1, а ещё лучше «зайду в книге1 в таблицу №2» то только тогда провести однократный перерасчёт в ~900 формулах?
Синопсис:
В конечном формате у меня ~900 формул с участием СумЦвет. Необходимость просчёта 1 раз в неделю. Других макросов сейчас нет и не планируется…
На 4-ядерном i5 4500 каждое изменение любой клетки таблицы сопровождается 3-секундным перерасчётом, при этом таблица пока пустая почти без формул. При постоянной работе в документе неприемлемо. Но VBA иметь хочется, спасибо Вам за код!
Алексар, я не знаю, можно ли прописать такое в коде. Могу рекомендовать иной метод. Превратите ваш массив данных в Таблицу, выбрав массив, и нажав Ctrl+T (T английское). Пройдите по меню Файл->Параметры->Формулы и в области Вычисления в книге выберите Автоматически, кроме таблиц данных. После этого формулы в Таблице не будут обновляться автоматически, а только по нажатию F9.
Спасибо за работу.
После переименования файла в xlsm и работе на примерах отсюда появились такие проблемы:
1. При открытии файла выдается надпись «ошибка направления команды приложению».
2. Никакую ячейку (даже пустую) нельзя скопировать, пишет «ошибка открытия буфера обмена».
Попробуйте не переименовывать файл, а всё-таки открыть штатно документ и «Сохранить как» с поддержкой макросов 🙂
Я так и делал, неправильно написал.
С вычислениями все получилось, но сохранила, как книгу с поддержкой макросов, при следующем открытии файла в ячейках с вычислениями ошибка #ИМЯ?
Что-то сделала неправильно?
Анна, возможно у вас одна из проблем, перечисленных
вышеСпасибо! Действительно отключены были все макросы.
Сергей, добрый день.
Огромное спасибо за Ваши помощь, советы и подсказки в борьбе с товарищем Exel-ем 🙂
Функция Сумма по цвету ячеек работает отлично!
А можно ли также посчитать «среднее значение» по цветным ячейкам с сохранением условия проверки, что значение в ячейке является числом – IsNumeric(i)?
Лариса, поскольку СумЦвет ведет себя, как обычная функция, можете составить формулу =СумЦвет/КолЦвет. Это и будет среднее значение))
Все гениальное просто )))
Сергей, Вы маг и волшебник 🙂 Огромное спаибо
Здравствуйте
помогите с формулой
есть 3 колонки с ценами А Б С разных цветов,
при учёте продаж (товар) 2 шт, цвет ячейки с количеством реагировал на цвет столбца с ценой такого же цвета,
тем самым учитывая сумму по которой был продан товар.
Спасибо
Здравствуйте
Очень внимательно прочитал, даже попробовал. получилось… Но для самостоятельного написания так и не дошло… Помогите написать код типа:
ЦветЯчейки(адрес_ячейки), а на выходе числовой код цвета, или какой-то другой идентификатор.
Заранее Благодарен!
Александр, я лишь начинающий программист VBA. У меня получилась следующая не очень изящная функция
Function ЦветЯчейки(ячейка As Range) As Integer
Dim i As Range
For Each i In ячейка
ЦветЯчейки = i.Interior.ColorIndex
Next
End Function
Огромное спасибо!! Всё работает!! Получилось с первого раза…
Спасибо! Сегодня буду пробовать!
Заработала!!!
Огромное спасибо!!!
BAGUZIN,
скажите пожалуйста, существует ли в Excel (меня интересует 2007) формула, которая считает количество объединённых ячеек в одной конкретной ?
Попробуйте следующую VBA-функцию:
Function КолОб(диапазон As Range) As Integer
Application.Volatile True
КолОб = диапазон.Columns.Count * диапазон.Rows.Count
End Function
При использовании функции вводите аргумент с клавиатуры, а не выбирайте мышкой. Так как при объединении ячеек Excel укажет только угловую ячейку.
Здравствуйте Сергей!
При помощи Вашего макроса задача решилась Изумительно! Но нет предела совершенству, Подскажите как в этот макрос вставить функцию, чтобы и ячейка в которой находится формула окрашивалась в цвет той ячейки на которую ссылка?
Спасибо!
Подскажите, пожалуйста, набор функций для определения среднего значения только среди выделенных цветом ячеек?
Регина, см.
комментарийвышеДа, действительно, проглядела. Спасибо огромное, очень нужные функции, благодаря Вам у нас оптимизировались потери времени.
у меня такая проблема. excel на английском. куда прислать файл?
Здравствуйте! Мне нужно подсчитать количество (в процентах) закрашеных ячеек (любыми цветами).
За сто процентов берётся столбец с ФИО РАБОТНИКОВ. их 39
Добрый день
Подскажите пожалуйста как в excel подсчитать количество ячеек только с измененным цветом именно текста и наоборот количество ячеек с неизмененным цветом текста (черным)
Андрей, на эту тему см.
Подсчет числа ячеек, содержащих текст определенного цветаОгромное спасибо за доступное объяснение «для чайников»! На других ресурсах ничего не получалось.
Один «чайниковский» вопрос — После внесения данных приходится сохранять файл, закрывать и открывать заново чтоб прошел пересчет. Это не удобно и не правильно (подозреваю).
— Что нужно нажать не закрывая файл, чтоб конечные ячейки пересчитались?
Спасибо.
F9
выручили, спасибо!!!
Как, все-таки, single или dooble
Игорь, single/double влияет на точность расчетов значений в ячейках. В double она выше. Для большинства случаев достаточно single.
Спасибо, буду пользоваться double (не dooble), а single — при десятичных
подскажите, а если нужно выделить несколько несмежных диапазонов? а то формула принимает только два аргумента
Наталия, в предложенной формуле выделить несмежные диапазоны нельзя. Нужно видоизменить формулу, добавив второй (третий и т.д.) диапазоны. Например, для двух диапазонов могу предложить следующий код:
Function СумЦвет2(диапазон1 As Range, диапазон2 As Range, критерий As Range) As Double
Application.Volatile True
Dim i As Range
Dim j As Range
For Each i In диапазон1
If IsNumeric(i) And i.Interior.Color = критерий.Interior.Color Then
СумЦвет2 = СумЦвет2 + i
End If
Next
For Each j In диапазон2
If IsNumeric(j) And j.Interior.Color = критерий.Interior.Color Then
СумЦвет2 = СумЦвет2 + j
End If
Next
End Function
спасибо что не ответили и удалили вопрос!
Спасибо за функцию 🙂
А как нужно ее изменить, чтобы функция проверяла один диапазон чисел (ячейки с цветом), а считала другой диапазон чисел
Здравствуйте! Подскажите пожалуйста, как в Excel представить формулу:
Скорректированная стоимость =
= Стоимость * (К1 + К2 + … + КN – (N — 1);
где:
К1, К2, КN — коэффициенты, отличные от 1
N – количество коэффициентов, отличных от 1.
Подскажите, как подсчитать сумму ячеек соседних с выделенными цветом
Добрый день. Очень понравилась эта функция — работает безупречно. Возможно ли усложнить её так, чтобы подсчёт данных вёлся по нескольким критериям? Приведу пример. Имеем диапазон (10 на 10 ячеек) с разными числовыми значениями в ячейках. В трёх ячейках из ста значение равно 5. Одна часть всех ячеек диапазона имеет заливку желтым цветом, оставшиеся ячейки — красным. Две из трёх ячеек со значением 5 залиты желтым цветом, и одна — красным. Задача 1 — подсчитать сумму значений ячеек по условию, — цвет которых является желтым, а значением ячейки равняется 5.
В результате должны получить значение 10.
Задача 2 — подсчитать сумму значений ячеек по условию, — цвет которых является красным, а значением ячейки равняется 5.
В результате должны получить значение 5.
Такой вариант возможно реализовать? Спасибо.
Алексей, вот такой код поможет решить вашу задачу
Function СумЦветМн(диапазон As Range, цвет As Range, значение As Double) As Double
‘ Определяет сумму значений в ячейках «диапазона»,
‘ цвет которых совпадает с цветом в ячейке «цвет»
‘ а число со «значением»
Application.Volatile True
Dim i As Range
For Each i In диапазон
If i.Interior.Color = цвет.Interior.Color And i = значение Then
СумЦветМн = СумЦветМн + i
End If
Next
End Function
Не подскажите правильный синтаксис команды?
=СумЦветМн(A1:E6;H1;I1) — так?
Диапазон — понятно (A1:E6), а критерии как указать?
Критерий H1 — это цвет ячейки для сравнения, критерий I1 — значение ячейки для сравнения.
Не совсем. Третий аргумент функции не ссылка, а значение:
=СумЦветМн(A1:E6;H1;5).
Догадаетесь, как изменить код, чтобы третий аргумент стал ссылкой? 🙂
После ввода функции появляется окно с ошибкой Compile error: Syntax Error. Нажав ОК первая строка кода окрашивается желтым цветом. В первой строке: Function СумЦветМн(диапазон As Range, цвет As Range, значение As Double) As Double
После ввода функции появляется окно с ошибкой Compile error: Syntax Error. Нажав ОК первая строка кода окрашивается желтым цветом. В первой строке: Function СумЦветМн(диапазон As Range, цвет As Range, значение As Double) As Double
Неверно переносятся апострофы из Интернета в код. Уберите три строчки примечаний.
Вы правы. Убрал три стоки примечаний — формула начала работать. И ещё одно уточнение. Как её доработать так, чтобы функция считала не сумму значений в ячейках, которые соответствуют двум критериям — цвет и значение, а количество ячеек, которые соответствуют двум этим критериям?
Прошу прощения. Этот вопрос был выше и Вы уже успешно на него отвечали.
Спасибо Вам огромное за помощь!
Огромное СПАСИБО! Даже мне, человеку далекому от єкселя, все понятно и все работает! ))
Простите. Я работаю в Excel давно, но на пользовательском уровне. Сейчас возник вопрос: создала большую таблицу с разными формулами, на 12 листах. Произошли изменения в таблице. Как теперь перенести формулы с одного листа на все остальные? ТОЛЬКО формулы. Ячеек много и в разброс… Мучаюсь третий день…
Мария, попробуйте выделить лист, с которого нужно скопировать формулы, целиком (Ctrl+ф). Скопируйте его (Ctrl+c). Перейдите на лист, на который хотите скопировать формулы. Выделите его целиком. Нажмите Вставить -> Специальная вставка -> Только формулы.
Dobroe vremja sutok. Izvenite za latinicu, rabotaju v Anglii, net russkoj klaviaturi. Problema takaja — ogromnaja tablica s imenami klientov, naprimer (diapason A1:A20), kazhdij klient imeet chasi raboti (diapason B1:W1 I t.d. dlja kazhdogo klienta), inogda oni poluchajut bonus za rabotu, no ne kazhdij denj, bonus mozhet bitj raznij, 0.25; 0.75; 1.15 I t.d I on dolzhen umnazhatsja na chasi. Kak poschitatj kazhdogo klienta otdeljno uchitivaja postojanno menjajuwijsja bonus v zavisimosti ot dnja? ja dumaju videlitj bonusi cvetami I sdelatj tak, chto bi kazdij cvet schitalsja otdeljno, naprimer krasnij sumirovalsja toljko s krasnim. zheltij s zheltim I t.d. U menja v jachejke ukazano kolichestvo chasov I ona okrashena v krasnij cvet, chto dolzhno oznachatj: chasi*bonus I takih jacheek naprimer 5, mne nado poluchitj summu vseh krasnih jacheek, tak zhe vseh zelenih I t.d. a potom mne nado poluchitj obwij rezuljtat vseh okrawennih jacheek (vse cveta). Slomala golovu, pomogite pozhalujsta.
Добрый день. Я работаю в Excel не очень давно и уже столкнулась с такой проблемой. Может вы мне сможете помочь. Есть таблица с товарами на складе и их стоимостью. Нужно узнать сумму только одного наименования (например,
"болты"). Но они могут быть записаны по разному"болты10","болты20","болты40". Т.е., мне нужно сложить ячейки не одинаковые, а содержащие слово"болты". Буду вам очень признательна за помощь.Алла, могу предложить изящное, но не очевидное решение:
Используется формула массива {=СУММ((B1:B13)*(ЕСЛИОШИБКА(
--(НАЙТИ(E1;A1:A13)>0);0)))}Наберите формулу, кроме фигурных скобок, и одновременно нажмите Ctrl+Shift+Enter.
См.
Excel-файл. Подробнее о формулах массива см. перевод книгиМайкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.Наталия, пришлите файл в личку, попробую помочь. Как определяется размер бонуса? От чего он зависит?
спасибо за помощь. у меня все получилось
Dobroe vremja sutok. Razmer bonusa obozna4aet kompanija, on ni ot chego ne zavisit.
P.S spasibo za otvet I vashe vremja
S Uvazheniem
Natalja
Наталия, на мой взгляд, поскольку размер бонуса нельзя задать формулой, лучше отразить его в таблице, и для расчета использовать функцию СУММПРОИЗВ.
Рис. 1. Попытка использовать цвет; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Рис. 2. Таблица бонусов
См. также
Excel-файл.Добрый день!
Прошу проконсультировать, возможно ли добавить в макрос дополнительное условие перед суммированием.
Например, в таблице указаны данные в разных валютах.
Цель: просуммировать значения выделенных определенным цветом ячеек, переведя все валютные в рублевый эквивалент по указанному курсу.
Буду крайне признательна!
Анастасия, пришлите Excel-файл в личку. Подробно опишите, что требуется получить. Попробую помочь.
Здравствуйте.Нашёл Ваш сайт.Спасибо за все комментарии с обьяснениями.Я попробовал формулу суммирования ячеек по цвету.Скажите пожалуйста,возможно ли чтоб от общего числа с формулой сумцвет ещё отнимать расход.
=SumByColor(Продажи!H129:H298;Продажи!H164)
Можно ли в эту формулу добавить автоотнимание определённого диапазона ячеек.Спасибо.
Дмитрий, я правильно понял ваш вопрос: вы хотите в едином диапазоне ячейки одного цвета складывать, а другого вычитать?
Багузин , здравствуйте , можете пожалуйста помочь , никак не можем решить всей группой. Условия похожи. Составить функцию определенную пользователем для подсчёта в заданном диапазоне ячеек суммы только отрицательных значений в ячейках с указанным цветом заливки . Помогите пожалуйста !!! Заранее спасибо .
Попробуйте код:
Function СумЦветОтр(диапазон As Range, критерий As Range) As Double
Application.Volatile True
Dim i As Range
For Each i In диапазон
If i.Interior.Color = критерий.Interior.Color And i.Value < 0 Then
СумЦветОтр = СумЦветОтр + i.Value
End If
Next
End Function
См. также
Excel-файлЗдравствуйте, такой вопрос, как сделать все точно так же, но чтобы excel реагировал на цифры от 1 до 4.
Если я правильно понял, вы хотите складывать только значения в ячейках, содержащих цифры 1, 2, 3 или 4. Тогда примените такой код:
Function СумЦвет14(диапазон As Range, критерий As Range) As Integer
Application.Volatile True
Dim i As Range
For Each i In диапазон
If i.Interior.Color = критерий.Interior.Color And (i = 1 Or i = 2 Or i = 3 Or i = 4) Then
СумЦвет14 = СумЦвет14 + i
End If
Next
End Function
Огромное спасибо, сейчас попробую, благодарен.
Супер! Спасибо!
Добрый день, подскажите пожалуйста, если в диапазоне, который нужно обсчитать по цвету, прописаны формулы, и в итоговой строке выдает ЗНАЧ, можно ли как-то помочь в этом случае? У меня прописана следующая формула
ЕСЛИ(ИЛИ(U267=0;O267=
"");0;U267*O267)Наталья, пришлите Excel-файл в личку.
Добрый день! Подскажите, к примеру есть два столбца, в первом столбце иванов, петров, сидоров, во втором суммы часть из которых окрашена в зеленый, часть белая. Что бы отдельно просчитать все суммы по иванову использую формулу суммесли((д29:д1215;
"иванов";з29:з1215) , можно ли как то просчитать сумму по иванову только окрашенных ячеек? Файл прислать к сожалению не смогу.Анастасия, предлагаю функцию VBA:
Чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке.
См. также
Excel-файлА не могла бы создать код, просто сумма в ячейках по цвету?
Мне вот этот понравился, но критерия оно у меня может накрасится или удалится)
Большое спасибо автору. Очень полезная и понятная статья. С уважением за подвижничество!
Здравствуйте, помогите написать код, чтобы при установлении фильтра по соседнему столбцу функция пересчитывала СумЦвет. То есть как промежуточный СумЦвет. Спасибо!
Анастасия, попробуйте так:
Подскажите пожалуйста, как найти среднее значение закрашенных ячеек, при условии что закрашена может быть ячейка без значения
(ф-ция КолЦвет, приведенная в начале статьи считает все закрашенные ячейки независимо от содержания)
Поэтому не получилось соединить 2 функции:
СумЦвет/КолЦвет
Спасибо за статью, очень полезна. Но она у меня работает не полностью. То есть когда пробовала на новом чистом файле, то все функции работали. Но когда начала делать нужной мне таблице (со всеми заполоненными данными., при написании формулы нет строчки «Определенные пользователем»
Анжела, если вам нужно, чтобы формула работала не в одном файле, а во всех файлах Excel, код следует разместить в модуле, принадлежащем файлу PERSONAL.XLSB. Если в редакторе VBA вы не видите такого файла, почитайте Создание личной книги макросов.
Function СумНеЦвет(диапазон As Range, критерий As Range) As Single
Application.Volatile True
Dim i As Range
For Each i In диапазон
If i.Interior.Color критерий.Interior.Color Then
СумНеЦвет = СумНеЦвет + i.Value
End If
Next
End Function
добрый день!
как подсчитать заполненные закрашенные ячейки (то же самое как в формуле сверху, только чтоб он считал заполненные ячейки а не цифры)?
Попробуйте так:
Function КолНеЦвет(диапазон As Range, критерий As Range) As Single
Application.Volatile True
Dim i As Range
For Each i In диапазон
If i.Interior.Color критерий.Interior.Color Then
КолНеЦвет = КолНеЦвет + 1
End If
Next
End Function
Добрый день.
Нашла вот такую функцию для суммирования ячеек, выделенных жирным шрифтом, а она не считает 🙁
У меня Excel 2007. Помогите, пожалуйста.
Function SumBold(R As Range) As Double
s = 0
On Error Resume Next
For Each c In R.Cells
If c.Font.Bold Then s = s + c
Next
SumBold = s
End Function
Хочу сказать большое спасибо за эту функцию, она прекрасно работает, СПАСИБО
Ольга, всего одна неточность)) Проверьте, теперь должна работать
Благодарю за внимание.
Нет, Ваш вариант считает не сумму, а количество ячеек, выделенных жирным шрифтом.
Функция в прежнем варианте заработала. Возможно, она как-то «не задружила» с ASAPUtilities.
Сейчас всё работает.
Здравствуйте!
Подскажите, почему, при вставке функции не отображается «Определение пользователем» в окне мастер функции?
Заранее спасибо!
Пришлите файл в личку s_bag@mail.ru
Дмитрий, вы вставили код в Лист1, а нужно в модуль.
Все работает! Спасибо большое!
Спасибо за функцию.
Какой же Вы молодец!!!
Добрый вечер!
Как сделать формулу которая будет суммировать ячейки по цвету в другом столбце. Заранее благодарен!
Подскажите пожалуйста. Как прописать код так, чтобы объединенные ячейки одного цвета он считал как одну, а не как количество объединенных?
подскажите пожалуйста, при повторном открытии в ячейке с формулой выбивает ошибку #ИМЯ?
при повторном открытии файла
Алексей, скорее всего, вы сохранили макрос (код VBA) не в личной книге макросов, а в самом файле. А файл сохранили с расширением xlsx. А надо сохранить файл с расширением xlsm. В первом случае макрос при закрытии книги не записывается.
Не понятно считает после запятой. Лишние тысячные откуда то берутся.
Здравствуйте , вы могли б объяснить у меня показывает только VBA project , как я могу использовать в других документов excel, программу создаваемая мной Сумцвет? И у меня Microsoft Excel 2010.
Если я правильно понял вопрос, то вы хотите использовать функцию СумЦвет в различных книгах Excel. Для этого макрос нужно сохранить в Personal.xlsb. См. п. 8 выше. Подробности в заметке Создание личной книги макросов.
Добрый день!
Подскажите как модернизировать Вашу функцию под мою задачу:
Нужно суммировать значения, например в диапазоне А1:А5, а проверять заливку ячейки на соответствие критерию в другом диапазоне, например Е1:Е5.
Заранее благодарен за помощь!
Егор, если можно использовать дополнительный столбец, предлагаю воспользоваться простой пользовательской функцией цвет(), возвращающей значение цвета, как целое число:
А уже затем на основании этого вспомогательного столбца применить функцию СУММЕСЛИ:
Такой вариант рассматривал, но хотелось бы не использовать дополнительный столбец.
Здравствуйте. Перечитал комментарии похожие вопросы есть, но попытки доделать код не дали результата. можно ли добавить в «СумЦвет» Критерий чтобы происходило суммирование ячеек определенного цвета имеющих положительное значение, а отрицательные и текстовые значения игнорировались? спасибо
Игорь, замените одну строчку кода, добавив еще одно условие в проверку If:
If i.Interior.Color = критерий.Interior.Color And i > 0 Then
Добрый день!
Подскажите пожалуйста, когда открыла окно VBA Project нет персональной VBA Project(Personal.xlsb). что делать в этом случае?
Ольга, почитайте Создание личной книги макросов.
Добрый день!
Спасибо Вам. использую ваши формулы для подсчета количества закрашенных ячеек. Все работает, но есть вопрос.
Если выделить цветом объеденную ячейку, то формула считает количество не за единицу, а общее количество объеденных ячеек. Можно как-нибудь сделать чтобы несколько объединенных ячеек считались как одна?
Добрый день! Задача такая: нужно в столбце найти ячейки определенного цвета, умножить их на другой столбец и посчитать общее количество. Можно это как-нибудь реализовать?
Спасибо
Пользуюсь вашей функцией уже несколько лет.
Спасибо вам огромное.
Очень выручает.
Здравствуйте, как изменить формулу, чтобы в диапазоне суммировались только значения, находящиеся в желтом и зеленом цвете, а значения в красных ячейках не считалась (в красных будет считаться в отдельной ячейке по предложенной вами формуле).
Заранее спасибо!