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

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

Описание проблемы

Добавление заливки ячеек делает ваш документ Excel более понятным и удобочитаемым. Иногда приходится производить суммирование ячеек с выбранным цветом заливки.

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

Описание функции

Функция =СУММЗАЛИВКА(ДИАПАЗОН; ЯЧЕЙКА) имеет два обязательных аргумента:

  • ДИАПАЗОН — ссылка на диапазон ячеек, где необходимо произвести суммирование ячеек с определенной заливкой.
  • ЯЧЕЙКА — ссылка на ячейку с цветом заливки, по которому необходимо провести суммирование.

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

Замечание об автоматическом вычислении. Так как пересчет формул в Excel автоматически не происходит при изменении заливки ячеек, то данная функция не пересчитывается автоматически. Для того, чтобы принудительно пересчитать формулы используйте сочетание клавиш Ctrl + Alt +F9.

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

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

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

Как часто Вы при работе с таблицами окрашиваете ячейки в тот или иной цвет? Желтый — расходы Транспортного отдела, Красный — Экономического, Зеленый — Администрация и т.п. А потом хочется все эти расходы просуммировать, и не просто просуммировать ВСЕ расходы, а только расходы в ячейках с определенным цветом заливки. Это еще одна нерешенная проблема Excel. Разработчики категорически не хотят встраивать в него хоть какую-то функцию для суммирования данных в ячейках с определенным цветом заливки. Именно это делает данная функция — СуммаЯчеек_Заливка.

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

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

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

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

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

В принципе, данная функция аналогична по сути стандартной СУММЕСЛИ, только в качестве основного критерия здесь ячейка с заливкой. Но можно указать и привычный критерий — значение ячейки, в таком случае суммироваться будут ячейки с указанным цветом и критерием.

Читайте также:  Как ввести пароль электронной почты

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

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

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

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

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

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

Читайте также:  Бесплатная программа для обработки фото на документы

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

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

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

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

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

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

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

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

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

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

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

Цвет шрифта

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

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

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

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

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

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

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

Ссылка на основную публикацию
Adblock detector