Сумма цифр по цвету

Описание проблемы

Добавление заливки ячеек делает ваш документ Excel более понятным и удобочитаемым. Иногда приходится производить суммирование ячеек с выбранным цветом заливки.

К сожалению, Excel не имеет встроенной функции, которая использует цвета заливки в качестве условий в формулах. К счастью, просуммировать ячейки вы можете с помощью функции из надстройки VBA-Excel.

Описание функции

Функция =СУММЗАЛИВКА(ДИАПАЗОН; ЯЧЕЙКА) имеет два обязательных аргумента:

  • ДИАПАЗОН – ссылка на диапазон ячеек, где необходимо произвести суммирование ячеек с определенной заливкой.
  • ЯЧЕЙКА – ссылка на ячейку с цветом заливки, по которому необходимо провести суммирование.

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

Замечание об автоматическом вычислении. Так как пересчет формул в Excel автоматически не происходит при изменении заливки ячеек, то данная функция не пересчитывается автоматически. Для того, чтобы принудительно пересчитать формулы используйте сочетание клавиш Ctrl + Alt +F9.

Помечать ячейки цветом, используя заливку или цвет шрифта, очень удобно и наглядно. Если вы не дальтоник, конечно 🙂 Трудности возникают тогда, когда по такой раскрашенной таблице возникает необходимость сделать отчет. И если фильтровать и сортировать по цвету Excel в последних версиях научился, то суммировать по цвету до сих пор не умеет.

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

Читайте также:  Как открыть файлы ppt

Откройте редактор Visual Basic:

  • В Excel 2003 и старше для этого нужно выбрать в меню Сервис – Макрос – Редактор Visual Basic (Tools – Macro – Visual Basic Editor)
  • В новых версиях Excel 2007-2013 перейти на вкладку Разработчик (Developer) и нажать кнопку Visual Basic. Если такой вкладки у вас не видно, то включите ее в настройках Файл – Параметры – Настройка ленты (File – Options – Customize Ribbon)

В окне редактора вставьте новый модуль через меню Insert – Module и скопируйте туда текст вот такой функции:

Если теперь вернуться в Excel, то в Мастере функций (Вставка – Функция) в появившейся там категории Определенные пользователем (User Defined) можно найти нашу функцию и вставить ее на лист:

У нее два аргумента:

  • DataRange – диапазон раскрашенных ячеек с числами
  • ColorSample – ячейка, цвет которой принимается как образец для суммирования

Цвет шрифта

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

Количество вместо суммы

Если вам нужно подсчитывать не сумму покрашенных определенным цветом ячеек, а всего лишь их количество, то наша функция будет еще проще. Замените в ней 7-ю строку на:

Нюансы пересчета

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

Полностью решить эту проблему невозможно, но можно ее существенно облегчить. Для этого в третьей строке нашей функции используется команда Application.Volatile True. Она заставляет Excel пересчитывать результаты нашей функции при изменении любой ячейки на листе (или по нажатию F9).

Читайте также:  Трек номер это где

И помните о том, что наша функция перебирает все (и пустые тоже) ячейки в диапазоне DataRange и не задавайте в качестве первого аргумента целый столбец – "думать" будет долго 🙂

Для выполнения данной операции необходим пакет утилит Excel под названием ASAP Utilities.

Вообще, подсчет по цветам удобен в том случае, если вы работаете с цветной таблицей.

Как подсчитать и просуммировать количество ячеек по цвету фона или шрифта в Excel

Asap Utilities представляет собой программный пакет, который расширяет возможности Excel. Данный программный пакет действительно удобен, позволяя сэкономить драгоценное время пользователей. Несмотря на то, что это платное программное обеспечение, но для использования дома и в образовательных целях пакет бесплатный.

Надстройку довольно легко использовать, она добавляется как вкладка в окне Excel, где вы можете найти все функции и утилиты услуги, предлагаемые ею. Интерфейс выглядит следующим образом:

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

Шаг 1: Откройте лист, содержащий цветные ячейки. Нажмите на любую пустую ячейку, в которую нужно поместить результат.

Шаг 2: Теперь откройте вкладку ASAP Utilities. Из выпадающего списка Формулы выберите 12. Вставить функцию из библиотеки ASAP Utilities:

Шаг 3: После этого появится диалоговое окно, содержащее множество формул. Из того же списка, выберите функцию ASAPCOUNTBYCELLCOLOR и нажмите кнопку OK.

Шаг 4: Теперь появится диалоговое окно с аргументами функции. Укажите их и нажмите кнопку ОК.

В поле Reference укажите диапазон, в котором необходимо произвести вычисления, Color_index_nr – адрес ячейки, цвет которой необходимо учитывать.

После нажатия на ОК вам будет показан результат:

Таким образом в Excel легко подсчитать количество ячеек по цвету фона.

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

Процесс подсчета в значительной степени похож на тот , что описан выше. Только в данном случае среди формул необходимо выбрать ASAPCOUNTBYFONTCOLOR.

Появится диалоговое окно с аргументами функции, где также нужно указать диапазон, в котором необходимо произвести вычисления, и адрес ячейки, цвет шрифта который нужно учитывать при подсчете в формуле. Нажмите кнопку OK. Перед вами – количество ячеек по цвету шрифта.

Читайте также:  Телепрограмма жди меня на нтв

Как просуммировать ячейки по цвету фона или шрифта в Excel

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

Для суммирования ячеек нужно выполнить следующее:

Шаг 1: Откройте лист Excel, имеющий цветные ячейки со значениями в них. Выберите конкретную ячейку, в которой нужно отобразить результат.

Шаг 2: Перейдите на вкладку ASAP Utilities, в разделе формул опять-таки выберите раздел 12. Вставить функцию из библиотеки ASAP Utilities .

Шаг 3:Перечень функций прокрутите список вниз и выберите функцию ASAPSUMBYCELLCOLOR и нажмите кнопку OK.

Шаг 4: Откроется диалоговое окно с аргументами функции. Здесь также нужно указать диапазон и адрес ячейки, фон которой учитывается при суммировании остальных ячеек. Нажмите кнопку OK и порадуйтесь результату.

Вот таким образом можно просуммировать ячейки по цвету фона. И весь процесс вряд ли занимает несколько секунд.

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

Процесс подсчета аналогичен вышеописанному, но в перечне функция нужно выбрать ASAPSUMBYFONTCOLOR.

Появится диалоговое окно, где вы должны указать диапазон ячеек и адрес ячейки с образцом цвета шрифта. Нажмите OK.

Вывод

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

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *