СуммаЯчеек_Заливка

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

 

Функция СуммаЯчеек_Заливка суммирует данные на основании цвета заливки. Так же есть возможность указать отдельный критерий(например, суммировать только ячейки с красным цветом заливки и напротив которых содержится слово "расход").
Для чего это нужно? Скорее всего Вы в работе с Excel уже сталкивались с таблицами, ячейки которых окрашены в тот или иной цвет заливки либо шрифта. Например, Желтый - расходы Транспортного отдела, Красный - Экономического, Зеленый - Администрация и т.п. И необходимо все эти расходы просуммировать/подсчитать, но опираясь на ячейки с определенным цветом заливки/шрифта. В Excel до сих пор нет ни одной функции для суммирования/подсчета данных в ячейках с определенным цветом заливки или шрифта.

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

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

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

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

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

---

 

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

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

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

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

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

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

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

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

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

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