Забыли пароль?


MulTEx »

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

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

MulTEx

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

 

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

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

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

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

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

Синтаксис:
=СчетЯчеек_Заливка($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). По умолчанию принимает значение ИСТИНА. Если указан как ИСТИНА, то функция будет подсчитывать ячейки с учетом примененного к ним условного форматирования. Если указан как ЛОЖЬ, то функция будет подсчитывать ячейки без учета примененного условного форматирования, т.е. даже если условное форматирование применено и ячейка окрашена с его помощью, а реальный цвет заливки не соответствует цвету ЯчейкиОбразца - то она не будет подсчитана.

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

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

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


Расскажи друзьям, если статья оказалась полезной:
  Плейлист   Видеоинструкции по использованию надстройки MulTEx
Обсуждение: 38 комментариев
  1. Олег:

    @Дмитрий(Админ)
    Спасибо, Дмитрий.
    Я не ленивый.
    Просто функция не считает ячейки без закраски... А было бы неплохо.

  2. Олег, а у меня опять-таки считает.
    =Count_CellColor(Лист1!$A$1:$A$7;C1;"*?*";"1")
    В C1 - ссылка на ячейку без заливки.

    Поймите - функция считает ячейки с той заливкой, с которой будет ячейка-образец. Если она будет без заливки - значит функция и подсчитывать будет ячейки без заливки.

    Единственное, я Вам без проверки привел пример немного неверный в части указания подсчета непустых ячеек. В функции выше все указано верно.

  3. Олег:

    @Дмитрий(Админ)
    ваш вариант тоже пробовал, всячески комбинируя и подстраиваясь под формат чисел в ячейках, но что-то как-то все равно неточно. Все-таки цвет ячейки влияет :)

  4. Олег, Вы как-то отвечаете размыто...Причем здесь формат чисел вообще? Что Вы там комбинировали и как именно "неточно" может влиять цвет мне не ясно. У меня все точно реагирует. Если в указанном диапазоне не закрашено 4 ячейки и 2 из них пустые - то функция выдает мне результат 2. Как и должно. При изменении каких-либо исходных данных или условий - при пересчете функции все верно рассчитывается. Цвет ячейки естественно влияет - Вы указываете это как ключевой параметр для функции.
    Поэтому делаю вывод, что Вы просто неверно используете функцию.

  5. failen:

    Я "великий пользователь", не могу найти даже диспетчер функций-Категория:Функции MyAddin-Count_CellColor????

  6. failen, она появится только после установки надстройки MyAddin: http://www.excel-vba.ru/nadstrojka-myaddin/

  7. Роман:

    Здравствуйте, возможно у меня руки не от туда растут, но функция считает не правильно ячейки со сложным Условным форматированием. Могу отправить файл с примером.

  8. Роман, надстройка не распознает условия окраса градиентом, значками и прочей экзотики. Это не моя прихоть - Microsoft не дает программного доступа к распознаванию таких условий.

  9. Роман:

    @Дмитрий(Админ)
    Окрас не градиентом и не значками. это просто красный, но в условии УФ стоит 56 диапазонов и 56 ячеек с которыми он сравнивает.
    Если в условии УФ ставишь теже 56 диапазонов и конкретное число для сравнения, допустим >0, то функция работает нормально.

    Давайте отправлю Вам файл, возможно, это я что-то не так делаю, или удастся подправить функцию.

  10. Роман - попробуйте отправить: The-Prist@yandex.ru
    Только обязательно укажите в каких ячейках по Вашему неправильно считает и как именно Вы записываете функцию в ячейку.

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

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


Для оформления сообщений Вы можете использовать следующие тэги:
<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 Яндекс.Метрика
© 2020 Excel для всех   Войти