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

Для того, чтоб создать ваши расчеты наиболее ординарными для восприятия можно выделить цветом шрифт части данных.

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

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

Функция =СУММШРИФТ(ДИАПАЗОН, ЯЧЕЙКА) имеет два неотклонимых аргумента:

ДИАПАЗОН ячеек, в каком нужно произвести суммирование ячеек с определенным цветом шрифта.
ЯЧЕЙКА — ссылка на ячейку с цветом шрифта по которому нужно провести суммирование.

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

Код на VBA

Дальше представлен код на VBA? который вы сможете применять в собственных файлах. Или просто установите надстройку.

Для выполнения данной операции нужен пакет утилит 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, что очень комфортно для юзера. Данный метод работы с цветными ячейки намного проще, нежели написание особых макросов.

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

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

Откройте редактор 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 2010 и 2013

Из данной для нас статьи Вы узнаете, как в Excel посчитать количество и сумму ячеек определенного цвета. Этот метод работает как для ячеек, раскрашенных вручную, так и для ячеек с правилами условного форматирования. Не считая того, Вы научитесь настраивать фильтр по нескольким цветам в Excel 2010 и 2013.

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

Как понятно, Microsoft Excel предоставляет набор функций для разных целей, и разумно представить, что есть формулы для подсчёта ячеек по цвету. Но, к огорчению, не существует формулы, которая дозволила бы на обыкновенном листе Excel суммировать либо считать по цвету.

Если не применять посторонние надстройки, существует лишь одно решение – сделать пользовательскую функцию (UDF). Если Вы не достаточно понимаете о данной для нас технологии либо совершенно никогда не слышали этого термина, не пугайтесь, Для вас не придётся писать код без помощи других. Тут Вы найдёте хороший готовый код (написанный нашим гуру Excel), и всё, что Для вас будет нужно создать – это скопировать его и вставить в свою рабочую книжку.

Считаем и суммируем по цвету, когда ячейки раскрашены вручную

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

Представим, у Вас есть таблица заказов компании, в какой ячейки в столбце Delivery раскрашены зависимо от их значений: Due in X Days – оранжевые, Delivered – зелёные, Past Due – красноватые.

Сейчас мы желаем автоматом сосчитать количество ячеек по их цвету, другими словами сосчитать количество бардовых, зелёных и оранжевых ячеек на листе. Как я уже произнес выше, прямого решения данной для нас задачки не существует. Но, к счастью, в нашей команде есть весьма опытные и понимающие Excel гуру, и один из их написал идеальный код для Excel 2010 и 2013. Итак, сделайте 5 обычных шагов, обрисованных дальше, и через пару минут Вы узнаете количество и сумму ячеек подходящего цвета.

Откройте книжку Excel и нажмите Alt+F11, чтоб запустить редактор Visual Basic for Applications (VBA).
Правой клавишей мыши кликните по имени Вашей рабочей книжки в области Project – VBAProject, которая находится в левой части экрана, дальше в показавшемся контекстном меню нажмите Insert > Module.

Сохраните рабочую книжку Excel в формате .xlsm (Книжка Excel с поддержкой макросов).

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

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

CountCellsByColor( спектр , код_цвета )

В этом примере мы используем формулу =CountCellsByColor(F2:F14,A17), где F2:F14 – это спектр, содержащий раскрашенные ячейки, которые Вы желаете посчитать. Ячейка A17 – содержит определённый цвет заливки, в нашем случае красноватый.

Буквально таковым же образом Вы записываете формулу для остальных цветов, которые требуется посчитать в таблице (жёлтый и зелёный).

Если в раскрашенных ячейках содержатся численные данные (к примеру, столбец Qty. в нашей таблице), Вы сможете суммировать значения на базе избранного цвета ячейки, используя аналогичную функцию SumCellsByColor:

SumCellsByColor( спектр , код_цвета )

Как показано на скриншоте ниже, мы употребляли формулу:

где D2:D14 – спектр, A17 – ячейка с прототипом цвета.

Таковым же образом Вы сможете посчитать и просуммировать ячейки по цвету шрифта с помощью функций CountCellsByFontColor и SumCellsByFontColor соответственно.

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

По сути, это обычное поведение макросов в Excel, скриптов VBA и пользовательских функций (UDF). Дело в том, что все подобные функции вызываются лишь конфигурацией данных на листе, но Excel не расценивает изменение цвета шрифта либо заливки ячейки как изменение данных. Потому, опосля конфигурации цвета ячеек вручную, просто поставьте курсор на всякую ячейку и кликните F2, а потом Enter, сумма и количество опосля этого обновятся. Так необходимо создать, работая с хоть каким макросом, который Вы найдёте дальше в данной для нас статье.

Считаем сумму и количество ячеек по цвету во всей книжке

Представленный ниже скрипт Visual Basic был написан в ответ на один из объяснений читателей (также нашим гуру Excel) и делает конкретно те деяния, которые упомянул создатель комментария, а конкретно считает количество и сумму ячеек определённого цвета на всех листах данной книжки. Итак, вот этот код:

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

Просто введите одну из этих формул в всякую пустую ячейку на любом листе Excel. Спектр указывать не надо, но нужно в скобках указать всякую ячейку с заливкой подходящего цвета, к примеру, =WbkSumCellsByColor(A1), и формула возвратит сумму всех ячеек в книжке, окрашенных в тот же цвет.

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

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

Замечание: Пожалуйста, помните, что все эти формулы будут работать, если Вы уже добавили в свою рабочую книжку Excel пользовательскую функцию, как было показано ранее в данной для нас статье.

Функции, которые считают количество по цвету:

CountCellsByColor( спектр , код_цвета ) – считает ячейки с данным цветом заливки.

В примере, рассмотренном выше, мы употребляли вот такую формулу для подсчёта количества ячеек по их цвету:

где F2:F14 – это избранный спектр, A17 – это ячейка с необходимым цветом заливки.

Все перечисленные дальше формулы работают по такому же принципу.

Функции, которые суммируют значения по цвету ячейки:

SumCellsByColor( спектр , код_цвета ) – вычисляет сумму ячеек с данным цветом заливки.
SumCellsByFontColor( спектр , код_цвета ) – вычисляет сумму ячеек с данным цветом шрифта.

Функции, которые возвращают код цвета:

GetCellFontColor( ячейка ) – возвращает код цвета шрифта в избранной ячейке.
GetCellColor( ячейка ) – возвращает код цвета заливки в избранной ячейке.

Итак, посчитать количество ячеек по их цвету и вычислить сумму значений в раскрашенных ячейках оказалось совершенно легко, не так ли? Но что если Вы не раскрашиваете ячейки вручную, а предпочитаете применять условное форматирование, как мы делали это в статьях Как поменять цвет заливки ячеек и Как поменять цвет заливки строчки, основываясь на значении ячейки?

Как посчитать количество и сумму ячеек по цвету, раскрашенных с помощью условного форматирования

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

Естественно, Вы сможете отыскать тонны кода VBA в вебе, который пробует создать это, но все эти коды (по последней мере, те экземпляры, которые попадались мне) не обрабатывают правила условного форматирования, такие как:

Format all cells based on their values (Форматировать все ячейки на основании их значений);
Format only top or bottom ranked values (Форматировать лишь 1-ые либо крайние значения);
Format only values that are above or below average (Форматировать лишь значения, которые находятся выше либо ниже среднего);
Format only unique or duplicate values (Форматировать лишь неповторимые либо повторяющиеся значения).

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

Код VBA, приведённый ниже, преодолевает все обозначенные выше ограничения и работает в таблицах Microsoft Excel 2010 и 2013, с хоть какими типами условного форматирования (и опять спасибо нашему гуру!). В итоге он выводит количество раскрашенных ячеек и сумму значений в этих ячейках, независимо от типа условного форматирования, применённого на листе.

Как применять код, чтоб посчитать количество цветных ячеек и просуммировать их значения

Добавьте код, приведённый выше, на Ваш лист, как мы делали это в первом примере.
Изберите спектр (либо спектры), в каких необходимо сосчитать цветные ячейки либо просуммировать по цвету, если в их содержатся числовые данные.
Нажмите и удерживайте Ctrl, кликните по одной ячейке подходящего цвета, потом отпустите Ctrl.
Нажмите Alt+F8, чтоб открыть перечень макросов в Вашей рабочей книжке.
Изберите макрос SumCountByConditionalFormat и нажмите Run (Выполнить).

В итоге Вы увидите вот такое сообщение:

Для этого примера мы избрали столбец Qty. и получили последующие числа:

Count – это число ячеек искомого цвета; в нашем случае это красный цвет, которым выделены ячейки со значением Past Due.
Sum – это сумма значений всех ячеек красноватого цвета в столбце Qty., другими словами полное количество частей с отметкой Past Due.
Color – это шестнадцатеричный код цвета выделенной ячейки, в нашем случае D2.

Рабочая книжка с примерами для скачки

Если у Вас появились трудности с добавлением скриптов в рабочую книжку Excel, к примеру, ошибки компиляции, не работающие формулы и так дальше, Вы сможете скачать рабочую книжку Excel с примерами и с готовыми к использованию функциями CountCellsByColor и SumCellsByColor, и испытать их на собственных данных.

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

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

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

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

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

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

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

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

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

Откройте редактор 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 – ячейка, цвет которой принимается как эталон для суммирования

Цвет шрифта

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

Аспекты пересчета

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

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

Совершенно, подсчет по цветам комфортен в этом случае, если вы работаете с цветной таблицей.

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

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

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

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

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

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

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

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