Как считать и суммировать по цвету на листе Excel
Предположим, у Вас есть таблица заказов компании, в которой ячейки в столбце Delivery раскрашены в зависимости от их значений: Due in X Days – оранжевые, Delivered – зелёные, Past Due – красные.
Теперь мы хотим автоматически сосчитать количество ячеек по их цвету, то есть сосчитать количество красных, зелёных и оранжевых ячеек на листе. Как я уже сказал выше, прямого решения этой задачи не существует. Но, к счастью, в нашей команде есть очень умелые и знающие Excel гуру, и один из них написал безупречный код для Excel 2010 и 2013. Итак, выполните 5 простых шагов, описанных далее, и через несколько минут Вы узнаете количество и сумму ячеек нужного цвета.
В этом примере мы используем формулу =CountCellsByColor(F2:F14,A17), где F2:F14 – это диапазон, содержащий раскрашенные ячейки, которые Вы хотите посчитать. Ячейка A17 – содержит определённый цвет заливки, в нашем случае красный.
Точно таким же образом Вы записываете формулу для других цветов, которые требуется посчитать в таблице (жёлтый и зелёный).
Если в раскрашенных ячейках содержатся численные данные (например, столбец Qty. в нашей таблице), Вы можете суммировать значения на основе выбранного цвета ячейки, используя аналогичную функцию SumCellsByColor:
Как показано на снимке экрана ниже, мы использовали формулу:
где D2:D14 – диапазон, A17 – ячейка с образцом цвета.
Таким же образом Вы можете посчитать и просуммировать ячейки по цвету шрифта при помощи функций CountCellsByFontColor и SumCellsByFontColor соответственно.
Замечание: Если после применения выше описанного кода VBA Вам вдруг потребуется раскрасить ещё несколько ячеек вручную, сумма и количество ячеек не будут пересчитаны автоматически после этих изменений. Не ругайте нас, это не погрешности кода
На самом деле, это нормальное поведение макросов в Excel, скриптов VBA и пользовательских функций (UDF). Дело в том, что все подобные функции вызываются только изменением данных на листе, но Excel не расценивает изменение цвета шрифта или заливки ячейки как изменение данных. Поэтому, после изменения цвета ячеек вручную, просто поставьте курсор на любую ячейку и кликните F2, а затем Enter, сумма и количество после этого обновятся. Так нужно сделать, работая с любым макросом, который Вы найдёте далее в этой статье.
Считаем сумму и количество ячеек по цвету во всей книге
Представленный ниже скрипт Visual Basic был написан в ответ на один из комментариев читателей (также нашим гуру Excel) и выполняет именно те действия, которые упомянул автор комментария, а именно считает количество и сумму ячеек определённого цвета на всех листах данной книги. Итак, вот этот код:
Добавьте этот макрос точно также, как и предыдущий код. Чтобы получить количество и сумму цветных ячеек используйте вот такие формулы:
Просто введите одну из этих формул в любую пустую ячейку на любом листе Excel. Диапазон указывать не нужно, но необходимо в скобках указать любую ячейку с заливкой нужного цвета, например, =WbkSumCellsByColor(A1), и формула вернет сумму всех ячеек в книге, окрашенных в этот же цвет.
Пользовательские функции для определения кодов цвета заливки ячеек и цвета шрифта
Здесь Вы найдёте самые важные моменты по всем функциям, использованным нами в этом примере, а также пару новых функций, которые определяют коды цветов.
Замечание: Пожалуйста, помните, что все эти формулы будут работать, если Вы уже добавили в свою рабочую книгу Excel пользовательскую функцию, как было показано ранее в этой статье.
Функции, которые считают количество по цвету:
где F2:F14 – это выбранный диапазон, A17 – это ячейка с нужным цветом заливки.
Все перечисленные далее формулы работают по такому же принципу.
Функции, которые возвращают код цвета:
Итак, посчитать количество ячеек по их цвету и вычислить сумму значений в раскрашенных ячейках оказалось совсем не сложно, не так ли? Но что если Вы не раскрашиваете ячейки вручную, а предпочитаете использовать условное форматирование, как мы делали это в статьях Как изменить цвет заливки ячеек и Как изменить цвет заливки строки, основываясь на значении ячейки?
Суммирование ячеек по цвету
Для начала перейдем в редактор VBA, для этого в панели вкладок выбираем Разработчик -> Visual Basic (или нажимаем комбинацию клавиш Alt + F11).
Создаем новый модуль и добавляем в него следующий код функции для суммы (напротив каждой строчки дается пояснение к коду):
|
PublicFunctionСУММЦВЕТ(MyRange AsRange,MyCell AsRange)AsDouble DimSum AsDouble‘Ввод переменной Sum для подсчета суммы Sum=‘Приравнивание переменной Sum к нулю Application.Volatile True‘Пересчет функции при каком-либо изменении значений ячеек листа ForEachcell InMyRange‘Цикл по всем ячейкам диапазона Ifcell.Interior.Color=MyCell.Interior.Color Then‘Проверка текущей ячейки на условие по цвету Sum=Sum+cell.Value‘Значение текущей ячейки прибавляется к промежуточной сумме EndIf Next СУММЦВЕТ=Sum‘Приравнивание возвращаемому результату значения конечной суммы EndFunction |
Функция СУММЦВЕТ содержит два аргумента:
- MyRange (обязательный аргумент) — диапазон ячеек для суммирования;
- MyCell (обязательный аргумент) — ячейка, по цвету заливки которой рассчитывается сумма.
Функция СУММЦВЕТ теперь будет определяться при вводе формулы в ячейку, переходим из редактора на лист Excel и, чтобы просуммировать ячейки воспользуемся новой функцией:
Как мы видим, в итоге для каждого определенного цвета ячейки мы получили различный результат.
При этом, если выбранная ячейка не имеет заливки, то функция суммы и подсчета количества ячеек по выбранному цвету также будет работать.
Нулевые строки.
Также имейте в виду, что СЧЕТЕСЛИ и СЧИТАТЬПУСТОТЫ считают ячейки с пустыми строками, которые только на первый взгляд выглядят пустыми.
Что такое эти пустые строки? Они также часто возникают при импорте данных из других программ (например, 1С). Внешне в них ничего нет, но на самом деле это не так. Если попробовать найти такие “пустышки” (F5 -Выделить – Пустые ячейки) – они не определяются. Но фильтр данных при этом их видит как пустые и фильтрует как пустые.
Дело в том, что существует такое понятие, как «строка нулевой длины» (или «нулевая строка»). Нулевая строка возникает, когда программе нужно вставить какое-то значение, а вставить нечего.
Проблемы начинаются тогда, когда вы пытаетесь с ней произвести какие-то математические вычисления (вычитание, деление, умножение и т.д.). Получите сообщение об ошибке #ЗНАЧ!. При этом функции СУММ и СЧЕТ их игнорируют, как будто там находится текст. А внешне там его нет.
И самое интересное – если указать на нее мышкой и нажать Delete (или вкладка Главная – Редактирование – Очистить содержимое) – то она становится действительно пустой, и с ней начинают работать формулы и другие функции Excel без всяких ошибок.
Если вы не хотите рассматривать их как пустые, используйте для подсчета реально пустых клеток следующее выражение:
Откуда могут появиться нулевые строки в ячейках? Здесь может быть несколько вариантов:
- Он есть там изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе (вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет – они просто заполняются строкой нулевой длины.
- Была создана формула, результатом которой стал текст нулевой длины. Самый простой случай:
=ЕСЛИ(Е1=1;10;””)
В итоге, если в Е1 записано что угодно, отличное от 1, программа вернет строку нулевой длины. И если впоследствии формулу заменять значением (Специальная вставка – Значения), то получим нашу псевдо-пустую позицию.
Если вы проверяете какие-то условия при помощи функции ЕСЛИ и в дальнейшем планируете производить с результатами математические действия, то лучше вместо “” ставьте 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: Файл -Параметры -Дополнительно – Показывать нули в позициях, которые содержат нулевые значения.
Варианты условного форматирования
Работа с каждым из них будет рассмотрена далее. Во всех случаях вам придется выполнить некоторые действия, чтобы получить доступ к условному форматированию:
Перейдем к рассмотрению каждого из данных пунктов подробнее.
Настройка гистограммы
Выделив ячейки с данными откройте контекстное меню «Условное форматирование» и выберите там пункт «Гистограммы»
Обратите внимание, что вам предлагается несколько дизайн-вариантов оформления. Они разделены на две большие категории: с градиентной заливкой и сплошной
Выберите среди них тот вариант оформления, который вам больше нравится.
По умолчанию, чем больше числовое значение в ячейке, тем длиннее будет гистограмма. Примечательно, что если в выделенной части таблицы присутствуют отрицательные числа, то гистограмма тоже под них подстроится: появится разделение, которое продемонстрировано на скриншоте. К сожалению, Excel версии 2007 года и более ранние не способны корректно работать с отрицательными значениями.
Наборы значков
Это более интересный вариант внешнего оформления ячеек с данными в таблице, так как предоставляет большое количество вариантов значков и индикаторов для оформления ячеек в таблице. Всего их 4 группы:
Исключения бывают в том случае, если вы выбрали вариант, где не предусмотрена градация по цвету. Тогда она происходит в соответствии с указанным шаблоном, например, частичное заполнение фигуры.
При выборе индикаторов из блока «Направления» помимо цветового оформления, используется ещё сигнализирование в виде направлений. Так, стрелка, повернутая указателем вверх, применяется к большим величинам, влево – к средним, вниз – к малым.
В новых версиях Excel при выборе в качестве индикаторов геометрических фигур в основном используются круги, но есть и другие геометрический фигуры. В таком случае будет меняться не только цветовая заливка, но и сами фигурки в зависимости от числовых значений в ячейках. Например, круги для крупных значений, треугольники для средних и четырехугольники для наименьших.
Изменение правил выделения ячеек
Вы можете не только настроить отображение какого-то цвета или фигурки в ячейках в зависимости их значений, но и задать свои правила, если вам не нравятся те, что установлены по умолчанию. Чтобы это реализовать, сделайте следующее:
Правила отбора первых и последних значений
Это еще один пункт «Условного форматирования» в Excel. Здесь происходит настройка правил выделения только для максимальных или минимальных значений в выделенном диапазоне ячеек. Инструмент подразумевает возможность провести как отбор по порядковым величинам, так и по процентным.
- Первые 10 элементов. Выделяет только первые 10 элементов в указанном диапазоне;
- Первые 10%. Выделяет только первые 10% ячеек из указанного диапазона;
- Последние 10 элементов;
- Последние 10%;
- Выше среднего. Выделяет только первую половину диапазона;
- Ниже среднего. Выделяет нижнюю половину диапазона.
Примечательно, что вы можете настроить эти фильтры под себя. Например, сделать выделение не первых 10 элементов, а первых 5 или 3.
Создание пользовательских правил
Стоит обратить внимание, что в выпадающем меню «Условное форматирование» есть пункт «Создать правило». При нажатии на него открывается окошко из шести правил, которые вы можете менять на свое усмотрение:
- Форматировать все ячейки на основании их значений;
- Форматировать только ячейки, которые содержат;
- Форматировать только первые и последние значения;
- Форматировать только значения, которые находятся выше или ниже среднего;
- Форматировать только уникальные или повторяющиеся значения;
- Использовать формулу для определения форматируемых ячеек.
Выберите одно из представленных правил, чтобы провести его детальную настройку. В нижней части окна отобразятся возможные изменения: описание правил, установка величин, интервалов и других значений. Во многом это похоже на те шаблоны, которые мы уже рассматривали выше, только настройка здесь более гибкая и все параметры собраны в одном окне.
Дополнительно правила можно менять местами – по умолчанию они занимают место в зависимости от того, какое из них было раньше добавлено. Изменение места правила происходит с помощью кнопок со стрелками в верхней части окна.
Удаление «Условного форматирования»
Если вам нужно удалить условное форматирование для ячеек, то здесь нет ничего сложного:
Инструмент «Условное форматирование» является отличным решением для визуализации данных в таблицах Excel. Здесь есть как уже встроенные шаблоны, так и возможность создания собственных, в том числе и на основе имеющихся по умолчанию.
Сложение каждой ячейки
Для начала мы с вами научимся складывать просто несколько чисел, которые могут находиться в разных местах
Откройте документ excel, где у вас записаны какие-либо значения.
Теперь выберите какое-нибудь свободное место и поставьте туда знак «Равно» (=)
В этот момент у вас активировалась функция решения примеров, и неважно каких, сложения или вычитания.
Теперь нажмите на ячeйку, где у вас записано первое число, которое вы хотите использовать для сложения. В том месте, где мы ставили знак «=», должна появится координата этой цифры, т.е
C6 или D2, и т.д. Отлично. Это то, что нам нужно.
Теперь поставьте знак «+» и нажмите на следующую ячейкy с данными, которое вы хотите использовать для сложения. Видите, как меняется значение в том месте, где мы ставили «=»? Да. Теперь там стоят уже две координаты, объединенные плюсом.
Таким же образом продолжайте искать оставшиеся цифры и когда они закончатся, вам останется нажать Enter и значения, которые вы выбрали суммируются, а сама сумма покажется в ячейке, в которой мы изначально ставили знак равенства.
Подсчет количества ячеек с определенным цветом ячейки с помощью VBA
Сводка
На вкладке формулы Microsoft Excel мы знаем, чтов категории » Дополнительные функции > » существует функция СЧЁТЕСЛИ, которая подсчитывает количество ячеек в диапазоне, соответствующих заданному условию. Условия для этой функции ограничены текстом или числами. Однако с помощью VBA можно создать функцию для подсчета количества ячеек с другими критериями, такими как цвет ячейки.
Дополнительные сведения
С помощью VBA пользовательская функция (UDF) может быть создана и сохранена в файле надстройки, чтобы ее можно было использовать в других книгах и переносить на другие компьютеры.
Создание пользовательской функции
Ниже приведены действия по созданию функции UDF для подсчета цвета ячеек.
Откройте Microsoft Excel, а затем нажмите клавиши ALT + F11, чтобы открыть окно редактора Visual Basic (VBE).
В меню Вставка выберите модуль , чтобы создать модуль. Затем введите следующий скрипт:
Закройте окно VBE, чтобы вернуться в Excel.
Чтобы протестировать UDF, создайте примеры данных, которые содержат столбец ячеек, в различных цветах.
В ячейке D3 Запишите функцию:
В аргументе «range_data» выберите ячейку C2 для C51.
В аргументе «критерии» выберите элемент F1.
Нажмите клавишу ВВОД. Результат в ячейке F2 — 6. Это означает, что число ячеек, затененных синим цветом, равно 6.
Для тестирования можно использовать другие цвета. Замените цвет в ячейке F1 на любой цвет из данных, используя домашнюю > заливку.
Кроме того, можно заархивировать UDF, чтобы функция могла использоваться в другой книге или на другом компьютере. Для этого выполните указанные ниже действия.
Шаг 1: сохранение книги
а. Выберите файл, а затем Сохранить как. (При необходимости нажмите кнопку Обзор ).
б. Выберите надстройку Excel (. xlam) в виде формата и присвойте файлу имя, например каунткколор.
Вы можете сохранить файл надстройки в любом месте. Но для того чтобы она была указана в качестве надстройки в программе Excel, сохраните ее в расположении по умолчанию. В Windows 7 расположением по умолчанию для любой версии Microsoft Excel является: К:\усерс\раддини\аппдата\роаминг\микрософт\аддинс
Шаг 2: Установка надстройки
а. Откройте Microsoft Excel на компьютере, на котором необходимо установить надстройку.
б. Откройте диалоговое окно надстройки, выбрав надстройки Excel для Excel 2013 и более поздних версий на вкладке разработчик . (надстройки в Excel 2010.)
в. В диалоговом окне надстройки нажмите кнопку Обзор.
г. Перейдите к расположению файла, в котором сохраняется файл надстройки (например, на USB-диске или в облачной папке). Выберите файл и нажмите кнопку Открыть.
д. В диалоговом окне надстройки убедитесь, что флажок Надстройка снят. После этого нажмите кнопку ОК.
Набор UDF Color Cell Color установлен и готов к использованию. Вы сможете получить доступ к этой функции в любое время, поместив курсор в любую ячейку листа и введя текст:
Заявление об отказе от контента решений сообщества
КОРПОРАЦИЯ МАЙКРОСОФТ И/ИЛИ ЕЕ ПОСТАВЩИКИ НЕ СОДЕРЖАТ НИКАКИХ ПРЕДСТАВЛЕНИЙ О ПРИГОДНОСТИ, НАДЕЖНОСТИ ИЛИ ТОЧНОСТИ ИНФОРМАЦИИ И СВЯЗАННОЙ С НЕЙ ГРАФИКИ. ВСЯ ТАКАЯ ИНФОРМАЦИЯ И СВЯЗАННАЯ С НЕЙ ГРАФИКА ПРЕДОСТАВЛЯЮТСЯ БЕЗ КАКИХ-ЛИБО ГАРАНТИЙ. КОРПОРАЦИЯ МАЙКРОСОФТ И/ИЛИ ЕЕ ПОСТАВЩИКИ НАСТОЯЩИМ СНИМАТЬИ ВСЕ ГАРАНТИИ И УСЛОВИЯ, ОТНОСЯЩИЕСЯ К ЭТОЙ ИНФОРМАЦИИ И СВЯЗАННЫМ ГРАФИЧЕСКИМ ГРАФИКАМ, ВКЛЮЧАЯ ВСЕ ПОДРАЗУМЕВАЕМЫЕ ГАРАНТИИ И УСЛОВИЯ ДЛЯ ОБЕСПЕЧЕНИЯ СООТВЕТСТВИЯ ТРЕБОВАНИЯМ, ПРИГОДНОСТИ ДЛЯ ОПРЕДЕЛЕННЫХ ЦЕЛЕЙ, ВОРКМАНЛИКЕ УСИЛИЯ, ДОЛЖНОСТИ И НЕНАРУШЕНИЯ ПРАВ. ВЫ ЯВНО СОГЛАСНЫ, ЧТО В СЛУЧАЕ ОТСУТСТВИЯ У КОРПОРАЦИИ МАЙКРОСОФТ И/ИЛИ ЕЕ ПОСТАВЩИКОВ НЕ НЕСЕТ ОТВЕТСТВЕННОСТИ ЗА ЛЮБЫЕ ПРЯМЫЕ, НЕПРЯМЫЕ, ПУНИТИВЕ, СЛУЧАЙНЫЙ, ОСОБЫЙ, КОСВЕННЫЙ УЩЕРБ ИЛИ ЛЮБОЙ УЩЕРБ, ВКЛЮЧАЯ, БЕЗ ОГРАНИЧЕНИЙ, УЩЕРБ ОТ ПОТЕРИ ИСПОЛЬЗОВАНИЯ, ДАННЫХ ИЛИ ПРИБЫЛИ, КОТОРЫЕ СВЯЗАНЫ С ИСПОЛЬЗОВАНИЕМ ИЛИ НЕВОЗМОЖНОСТЬЮ ИСПОЛЬЗОВАТЬ ИНФОРМАЦИЮ И СВЯЗАННУЮ С НИМИ ГРАФИКУ, В ЗАВИСИМОСТИ ОТ ДОГОВОРА, НАРУШЕНИЯМ, НЕБРЕЖНОСТЬЮ, ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТИ ИЛИ ИНЫМ СПОСОБОМ, ДАЖЕ ЕСЛИ У КОРПОРАЦИИ МАЙКРОСОФТ ИЛИ ЕЕ ПОСТАВЩИКА БЫЛО РЕКОМЕНДОВАНО ВЕРОЯТНОСТЬ УЩЕРБА.
Подведем итоги.
На нашем занятии мы научились проводить суммирование указанных значений без учета условий, с учетом одного условия либо с учетом нескольких условий, проверяемых в разных колонках. В первом случае использовали функцию СУММ, во втором – СУММЕСЛИ, в третьем – СУММЕСЛИМН.
Однако могут встретится ситуации, когда непосредственно самих значений для суммирования нет, хотя имеется информация для вычисления этих значений. Например, в таблице указаны только количество и стоимость единицы каждого товара, а требуется узнать итоговую сумму затрат на изготовление каждой группы товаров. Или при расчете суммы необходимо учесть различных условий, расположенных как в разных, так и в одной колонке. К примеру, найти общую сумму по продажам всех молотков и отверток с ценой от 4000 и до 1000 соответственно в феврале. Или же необходимо найти итоговую сумму поступлений от реализации одежды и продуктов из таблиц, расположенных на разных листах.
В таких случаях используются формулы массива, а также функции БДСУММ, СУММПРОИЗВ, ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ. Функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ подробно рассмотрены на прошлых занятиях, а вот о работе с функциями БДСУММ, СУММПРОИЗВ и применении формул массива для суммирования в соответствии с условиями поговорим в наших следующих материалах.
Наше же текущее занятие подошло к концу, спасибо за внимание и успеха в работе. Надеемся, что каждый нашел в нашем уроке для себя что-то новое, интересное и необходимое для работы
Если вы хотите узнать больше о тонкостях работы в Excel, то мы приглашаем посетить наши тренинги и вебинары. Свяжитесь с нашими кураторами, используя контактную информацию, указанную справа на странице, или оправьте заявку на странице заказа. Вы также можете пройти опрос для выбора интересующей именно вас темы занятий