Lost your password?


MulTEx »

СчетЯчеек_Заливка

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

MulTEx

Подсчет ячеек по цвету заливки

 

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

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

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

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

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

Синтаксис:
=СчетЯчеек_Заливка($E$2:$E$20;$E$7;I13;$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) - ячейка-образец с цветом заливки. Ячейки с этим цветом будут подсчитаны.

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

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

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

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

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

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

Функция подсчитывает любые ячейки, заливка которых равна заливке ячейки-образца. Даже если ячейка будет пустая, но заливка будет равна указанной - ячейка будет подсчитана. Чтобы подсчитать только заполненные ячейки в качестве критерия следует указать - "*?*", а ДиапазонКритерия не указывать.

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

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


Расскажи друзьям, если статья оказалась полезной:
  Плейлист   Видеоинструкции по использованию надстройки MulTEx
Обсуждение: 38 комментариев
  1. Приведите текст формулы, которую вводите. Т.к. все она считает верно - с того листа, диапазон на котором указан. И диапазон и критерий могут быть на разных листах, притом текущий это или нет - совершенно не имеет значения.

    Или можете мне на личную почту(The-Prist@yandex.ru) направить файл с функцией, которая не считает - я посмотрю, в чем может быть проблема.

  2. Arkady:

    Дмитрий, добрый день!

    Всё сделал, я использовал другую формулу. Цвет заливки :)

    Но стало очень дико тормозить, это из-за широкого диапазона ячеек?

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

  3. Arkady:

    Дмитрий, спасибо! Всё работает! Но увы процесс вычисления очень замедляет процесс работы, вынужден отказаться от использования данной формулы. И использовать фильтр, что очень неудобно :(

  4. Светлана:

    Доброго дня!
    MulTEx для Mac не подходит?

  5. Анастасия:

    Добрый!
    Не могу додумать, помогите)
    Формула игнорирует ячейки с текстом внутри.а у меня во всех стоит время в формате [h]:mm:ss Т.е. формула ничего не считает...
    Как исправить?.. Что-то я про критерий не поняла... Время везде разное...
    Спасибо!

    • Анастасия, время хоть и может быть посчитано, но игнорируется, т.к. это все же не число и его подсчет будет неверным.
      Если у Вас там действительно нормальные даты - поставьте в ячейках формат "Общий" и функция подсчитает. Только числа в любом случае запредельные получатся.

      P.S. В скором времени выпущу обновление этих функций и использование критериев и диапазонов будет более логичным и понятным.
      Спасибо.

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти