Из этой статьи Вы узнаете, как в Excel посчитать количество и сумму ячеек определенного цвета. Этот способ работает как для ячеек, раскрашенных вручную, так и для ячеек с правилами условного форматирования. Кроме того, Вы научитесь настраивать фильтр по нескольким цветам в Excel 2010 и 2013.
Как известно, 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(диапазон, код_цвета)
Как показано на снимке экрана ниже, мы использовали формулу:
=SumCellsByColor(D2:D14,A17)
Таким же образом Вы можете посчитать и просуммировать ячейки по цвету шрифта при помощи функций CountCellsByFontColor и SumCellsByFontColor соответственно.
Замечание: Если после применения выше описанного кода VBA Вам вдруг потребуется раскрасить ещё несколько ячеек вручную, сумма и количество ячеек не будут пересчитаны автоматически после этих изменений. Не ругайте нас, это не погрешности кода
