Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

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

Задача подсчитать ячейки по цвету заливки перестала быть даже редкостью - данный вопрос постоянно появляется на форумах. Решил выложить текст пользовательской функции, которая суммирует данные ячеек на основе цвета заливки. В чем отличие от остальных функций в интернете - функция может работать только с видимыми ячейками. Т.е. если отфильтровать диапазон, то функция подсчитает данные только отфильтрованных ячеек.
Если не знаете что такое функция пользователя советую сначала прочитать статью: Что такое функция пользователя(UDF)?

Синтаксис функции:
без учета скрытых строк и столбцов:
=SumByInteriorColor($A$1:$A$10;B1)
все ячейки:
=SumByInteriorColor($A$1:$A$10;B1;1)

rRange($A$1:$A$10) - ссылка на диапазон с ячейками для суммирования.
rColorCell(B1) - ссылка на ячейка-образец с цветом заливки.
bSumHide - Если указано ИСТИНА или 1 учитывает скрытые ячейки. ЛОЖЬ, 0 или опущен(по умолчанию) - скрытые ячейки не суммируются.


 

Чтобы подсчитывалось количество ячеек, а не их сумма, то функцию надо изменить самую малость:

Синтаксис и аргументы полностью идентичны с функцией SumByInteriorColor.

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

Так же функции не будут автоматически обновлять значения сразу после смены заливки ячеек - это особенность Excel. Поэтому при изменении заливки в вычисляемом диапазоне необходимо вручную пересчитать функцию(F2 -Enter).


Статья помогла? Сделай твит, поделись ссылкой с друзьями!

Поиск по меткам

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: оставлено 6 коммент.
  1. Art:

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

  2. Можно. Для этого и выложил код - посмотрели принцип и при желании доработали под свои нужды.

  3. Отлично! Две формулы - и все по делу. Большое спасибо!

  4. HVV10:

    Добрый день. А как добавить второе условие? например =SumByInteriorColor($A$1:$A$10;B1;$A$1:$A$10;<50)или как в СУММЕСЛИМН(диапазон_суммирования, диапазон_условий1, условие1,[диапазон_условий2, условие2], …) добавить условие по цвету ячейки

  5. Ренат:

    Добрый день! Подскажите пожалуйста. Как прописать код так, чтобы объединенные ячейки одного цвета он считал как одну, а не как количество объединенных?

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

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


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

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти