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

 

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

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

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

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

Сумма/Поиск/Функции - Математические - СуммаЯчеек_Шрифт

Синтаксис:
=СуммаЯчеек_Шрифт($E$2:$E$20;I6;I6;$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) - диапазон значений для суммирования. Можно указать несколько столбцов. Столбец с критерием(если планируется суммировать еще и по критерию) не обязательно должен входит в диапазон. По умолчанию цвет шрифта проверяется именно в этом столбце. Суммируются только числовые значения, остальные игнорируются.

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

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

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

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

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

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

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

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

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

10 комментариев

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

  2. Это первое, что я сделал. Увы не помогло.
    Установил Inventor он считает все как надо. Но тут возникла другая потребность, теперь надо суммировать ячейки с определенным чветом шрифта и с определенным цветом заливки, а вот как это сделать я вообще не понимаю.
    Гуглёж ничего не дал, выскакивает вариан либо-либо)))

  3. Если честно - совершенно не понимаю. Только что попробовал - все суммирует правильно. А что еще интереснее - автор Инвентора основывался именно на этих кодах.
    Вас не затруднит выслать мне вариант файла, в котором неверно суммирует моя функция? Текст функции лучше закомментировать(убрать равно). Буду Вам признателен за понимание.

  4. Не получается вывести сумму ячеек на другом листе. То есть таблица с данными находиться на одном Листе Excel, а суммы нужны на другом. Выдает ошибку #ЗНАЧ! Подскажите, пожалуйста, правильный порядок ввода данных, чтобы информация о сумме отображалась на другом листе.

  5. Формула не работает, в сумме ячеек выдает #ИМЯ?! Наверное делаю что то не так. Если не сложно, вышлете небольшой пример таблички с посчетом нескольких суммированых ячеек по цвету шрифта.

    С Уваженим!
    Сергей В.

  6. Формула работает. Судя по ошибке, Вы просто не скачали и не установили надстройку MyAddin, в которой эта функция расположена. Поэтому сколько Вам файлы ни присылай ничего не измениться пока не установите себе данную надстройку.

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.