Вычисляйте СЧЁТ, СУММ, СРЗНАЧ, МИН, МАКС на основе цвета ячеек или цвета шрифта
Работая с таблицами Excel, часто прибегают к заливке фона или цветному шрифту. Выделение ячеек обращает на себя внимание и служит своего рода цветовым кодом. Напр., можно применить зелёный фон как знак успешности показателей, или назначить значениям красный шрифт как сигнал тревоги. Тем не менее, такая очевидная задача, как посчитать и суммировать значения с одинаковым цветом, нередко превращается в часы программирования макросов или формул.
Инструмент «Счёт по цвету» мгновенно и без VBA считает значения в ячейках, исходя из их цвета:
- Вычисление СЧЁТ, СУММ, СРЗНАЧ, МИН, МАКС для каждого цвета
- Агрегация по цвету заливки и/или цвету условного форматирования
- Cчёт по цвету фона ячеек или цвету шрифта
- Предпросмотр и вставка таблицы результата на рабочий лист
Перед началом работы добавьте «Счёт по цвету» в Excel
«Счёт по цвету» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.
Как посчитать значения ячеек на основе цвета заливки
Excel по-разному обрабатывает два типа цветного фона ячеек: заливка сплошным цветом, заданная пользователем (когда вы сами назначаете цвет) и условный цвет (при применении условного форматирования). С надстройкой вы можете агрегировать значения по каждому типу цветного фона.
- Нажмите кнопку Счёт по цвету на панели XLTools
Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически. Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:
- Заданные цвета – чтобы учитывать только ячейки со сплошной заливкой фона
- Условные цвета – чтобы учитывать только ячейки с условным форматированием
- Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
- Из следующего выпадающего списка выберите «Вычислять по цвету» Фона.
- Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.Внимание: надстройка автоматически распознает и произведет расчёт по всем цветам в диапазоне. Ячейки без фона, без заливки и ячейки с белой заливкой обрабатываются вместе. Так, вы сможете сравнить результаты вычислений по цветным и бесцветным ячейкам.
- Выберите поместить результаты на новый или существующий лист.
- Нажмите OK
Готово!
Как посчитать значения ячеек на основе цвета шрифта
Excel по-разному обрабатывает два типа цветного шрифта: цвет шрифта, заданный пользователем (когда вы сами назначаете цвет) и условный цвет шрифта (при применении условного форматирования). С надстройкой вы можете агрегировать значения по каждому типу цветного шрифта.
- Нажмите кнопку Счёт по цвету на панели XLTools
Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически. Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:
- Заданные цвета – чтобы учитывать только ячейки заданным цветом шрифта
- Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
- Из следующего выпадающего списка выберите «Вычислять по цвету» Шрифта.
- Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.
- Выберите поместить результаты на новый или существующий лист.
- Нажмите OK
Готово!
Как посчитать значения ячеек на основе цвета условного форматирования
Excel по-разному обрабатывает два типа цветного фона ячеек: заливка сплошным цветом, заданная пользователем (когда вы сами назначаете цвет) и условный цвет (при применении условного форматирования). Условное форматирование может применятся как к шрифту, так и к фону фчейки. С надстройкой вы можете агрегировать значения по любому типу условного цвета.
- Нажмите кнопку Счёт по цвету на панели XLTools
Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически. Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:
- Условные цвета – чтобы учитывать только ячейки с условным форматированием
- Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
- Из следующего выпадающего списка выберите «Вычислять по цвету» Фона или Шрифта, в зависимости от типа условного форматирования.
- Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.
- Выберите поместить результаты на новый или существующий лист.
- Нажмите OK
Готово!
Доступные агрегатные вычисления (Count, Sum, Average, Minimum, Maximum)
Надстройка производит расчёт самых частых агрегатных функций, на основе цвета шрифта или фона ячейки:
- СЧЁТ (COUNT) – подсчёт количества всех значений в диапазоне по цвету
- СУММ (SUM) – сумма всех значений в диапазоне по цвету
- СРЗНАЧ (AVERAGE) – среднее (арифметическое среднее) всех значений в диапазоне по цвету
- МИН (MIN) – наименьшее значение в диапазоне по цвету
- МАКС (MAX) – наибольшее значение в диапазоне по цвету
Какие ячейки и значения учитываются при вычислениях
Надстройка автоматически распознает и произведет расчет по всем цветам в диапазоне. Включая чёрный цвет по умолчанию — так, вы сможете сравнить результаты значений с цветным и чёрным цветом шрифта.
- В расчёт принимаются: числовые значения, а также формулы, функции, ссылки на ячейки, которые возвращают числовое значение.Все пустые ячейки и ячейки, которые содержат текст, даты, ошибки игнорируются.
- Надстройка игнорирует скрытые строки или столбцы, т.е. в вычислениях учитываются только видимые ячейки. Если вы хотите провести вычисления по всему диапазону, пожалуйста, отобразите строки/столбцы и очистите фильтры.
- Вставленная на лист, сводная таблица с результатами вычислений содержит значения (не формулы и не ссылки).
- Объединённые ячейки обрабатываются как одна ячейка.
Вычисления по цвету ячеек
Помечать ячейки цветом, используя заливку или цвет шрифта, очень удобно и наглядно. Если вы не дальтоник, конечно :) Трудности возникают тогда, когда по такой раскрашенной таблице возникает необходимость сделать отчет. И если фильтровать и сортировать по цвету Excel в последних версиях научился, то суммировать по цвету до сих пор не умеет.
Чтобы исправить этот существенный недостаток можно использовать несложные пользовательские макрофункции на Visual Basic, которые позволят нам суммировать/подсчитывать количество/среднее арифметическое ячеек с определенным цветом заливки/шрифта.
На вкладке Разработчик (Developer) нажмите кнопку Visual Basic или сочетание клавиш Alt+F11, чтобы открыть редактор макросов. Если такой вкладки у вас не видно, то включите ее в настройках Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon).В окне редактора вставьте новый модуль через меню Insert - Module и скопируйте туда текст следующих функций:
Function CountByColor(DataRange As Range, ColorSample As Range) As Long Dim cell As Range, n As Long For Each cell In DataRange If cell.Interior.Color = ColorSample.Interior.Color Then n = n + 1 Next cell CountByColor = n End Function Function SumByColor(DataRange As Range, ColorSample As Range) As Double Dim cell As Range, total As Double For Each cell In DataRange If IsNumeric(cell) And cell.Interior.Color = ColorSample.Interior.Color Then total = total + cell.Value Next cell SumByColor = total End Function Function AverageByColor(DataRange As Range, ColorSample As Range) As Double Dim cell As Range, total As Double, n As Long For Each cell In DataRange If IsNumeric(cell) And cell.Interior.Color = ColorSample.Interior.Color Then total = total + cell.Value n = n + 1 End If Next cell AverageByColor = total / n End FunctionКак легко сообразить, первая функция здесь вычисляет количество ячеек с заданным цветом заливки, вторая - сумму, а третья - среднее арифметическое. У всех функций два аргумента:
- DataRange - диапазон исходных ячеек с числами, раскрашенных в разные цвета
- ColorSample - ячейка, цвет заливки которой мы берём за образец
Если теперь вернуться в Excel, то в Мастере функций (вкладка Формулы - кнопка Вставить функцию) в появившейся там категории Определенные пользователем (User Defined) можно найти наши функции и вставить их на лист. Либо напрямую ввести их в строку формул, как любые другие функции Excel:
Добавление условий
Аналогичный подход можно легко масштабировать, добавляя, при необходимости, дополнительные условия в проверку (команда if ... then...). Так, например, если нам нужно при вычислении среднего арифметического учитывать не только цвет заливки, но и цвет шрифта (т.е. считать не просто жёлтые, а именно жёлто-красные ячейки), то код нашей макро-функции будет выглядеть так: Function AverageByColor2(DataRange As Range, ColorSample As Range) As Double Dim cell As Range, total As Double, n As Long For Each cell In DataRange If IsNumeric(cell) And cell.Interior.Color = ColorSample.Interior.Color And cell.Font.Color = ColorSample.Font.Color Then total = total + cell.Value n = n + 1 End If Next cell AverageByColor2 = total / n End FunctionРазница только в добавленном через логическую связку "И" (and) условии на проверку соответствия цвета шрифта очередной проверяемой ячейки cell.Font.Color цвету шрифта ячейки-образца ColorSample.Font.Color.Ограничения и нюансы пересчёта
У созданных нами макрофункций есть 2 важных нюанса.
Во-первых, эти функции "не видят" заливку, созданную с помощью условного форматирования, т.е. работают только с цветом, который был задан для ячеек вручную.
Во-вторых, к сожалению, изменение цвета заливки или цвета шрифта ячейки Excel не считает изменением её содержимого, поэтому не запускает пересчет формул. То есть при перекрашивании исходных ячеек с числами в другие цвета итоговая сумма/среднее/количество по нашим функциям автоматически пересчитываться пересчитываться не будет.Полностью решить эту проблему невозможно, но есть несколько способов её обойти:
- Сделать двойной щелчок левой кнопкой мыши по ячейки с нашей формулой и нажать на Enter, т.е. имитировать повторный ввод функции в ячейку - Excel её заново пересчитает и выдаст обновленный результат.
- Можно использовать сочетание клавиш Ctrl+Alt+F9, которое принудительно заставит Excel пересчитать всё формулы и функции независимо от того, изменились ли для них исходные данные. Но это сочетание нужно будет не забывать нажимать каждый раз при изменении исходных данных.
- Добавить в код наших макрофункций (в любое место) команду Application.Volatile True. Эта команда языка Visual Basic заставляет Excel пересчитывать результаты нашей функции при изменении любой ячейки на листе (или по нажатию F9). Однако, в этом случае, скорость работы наших функций заметно снизится, т.к. их пересчёт будет происходить постоянно - даже когда мы не меняли цвета или значения ячеек в исходных данных. Пользуйтесь этим методом осторожно.
И помните о том, что наша функция перебирает все (и пустые тоже) ячейки в диапазоне DataRange и не задавайте в качестве первого аргумента целый столбец - "думать" будет долго :)
Ссылки по теме
Подсчет количества ячеек с определенным цветом ячейки с помощью VBA
- Чтение занимает 3 мин
- Применяется к:
- Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Microsoft Office Excel 2007, Microsoft Office Excel 2003
Сводка
На вкладке формулы 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 установлен и готов к использованию. Вы сможете получить доступ к этой функции в любое время, поместив курсор в любую ячейку листа и введя текст:
Заявление об отказе от контента решений сообщества
КОРПОРАЦИЯ МАЙКРОСОФТ И/ИЛИ ЕЕ ПОСТАВЩИКИ НЕ СОДЕРЖАТ НИКАКИХ ПРЕДСТАВЛЕНИЙ О ПРИГОДНОСТИ, НАДЕЖНОСТИ ИЛИ ТОЧНОСТИ ИНФОРМАЦИИ И СВЯЗАННОЙ С НЕЙ ГРАФИКИ. ВСЯ ТАКАЯ ИНФОРМАЦИЯ И СВЯЗАННАЯ С НЕЙ ГРАФИКА ПРЕДОСТАВЛЯЮТСЯ БЕЗ КАКИХ-ЛИБО ГАРАНТИЙ. КОРПОРАЦИЯ МАЙКРОСОФТ И/ИЛИ ЕЕ ПОСТАВЩИКИ НАСТОЯЩИМ СНИМАТЬИ ВСЕ ГАРАНТИИ И УСЛОВИЯ, ОТНОСЯЩИЕСЯ К ЭТОЙ ИНФОРМАЦИИ И СВЯЗАННЫМ ГРАФИЧЕСКИМ ГРАФИКАМ, ВКЛЮЧАЯ ВСЕ ПОДРАЗУМЕВАЕМЫЕ ГАРАНТИИ И УСЛОВИЯ ДЛЯ ОБЕСПЕЧЕНИЯ СООТВЕТСТВИЯ ТРЕБОВАНИЯМ, ПРИГОДНОСТИ ДЛЯ ОПРЕДЕЛЕННЫХ ЦЕЛЕЙ, ВОРКМАНЛИКЕ УСИЛИЯ, ДОЛЖНОСТИ И НЕНАРУШЕНИЯ ПРАВ. ВЫ ЯВНО СОГЛАСНЫ, ЧТО В СЛУЧАЕ ОТСУТСТВИЯ У КОРПОРАЦИИ МАЙКРОСОФТ И/ИЛИ ЕЕ ПОСТАВЩИКОВ НЕ НЕСЕТ ОТВЕТСТВЕННОСТИ ЗА ЛЮБЫЕ ПРЯМЫЕ, НЕПРЯМЫЕ, ПУНИТИВЕ, СЛУЧАЙНЫЙ, ОСОБЫЙ, КОСВЕННЫЙ УЩЕРБ ИЛИ ЛЮБОЙ УЩЕРБ, ВКЛЮЧАЯ, БЕЗ ОГРАНИЧЕНИЙ, УЩЕРБ ОТ ПОТЕРИ ИСПОЛЬЗОВАНИЯ, ДАННЫХ ИЛИ ПРИБЫЛИ, КОТОРЫЕ СВЯЗАНЫ С ИСПОЛЬЗОВАНИЕМ ИЛИ НЕВОЗМОЖНОСТЬЮ ИСПОЛЬЗОВАТЬ ИНФОРМАЦИЮ И СВЯЗАННУЮ С НИМИ ГРАФИКУ, В ЗАВИСИМОСТИ ОТ ДОГОВОРА, НАРУШЕНИЯМ, НЕБРЕЖНОСТЬЮ, ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТИ ИЛИ ИНЫМ СПОСОБОМ, ДАЖЕ ЕСЛИ У КОРПОРАЦИИ МАЙКРОСОФТ ИЛИ ЕЕ ПОСТАВЩИКА БЫЛО РЕКОМЕНДОВАНО ВЕРОЯТНОСТЬ УЩЕРБА.
Были ли сведения на этой странице полезными?
Excel как посчитать цветные ячейки в
Как посчитать в Excel ячейки в определенных строках.
Смотрите также рассматривался считалось, потому и: Вы издеваетесь?! виде стандартный зеленый ячейках которые залиты ячеек с числами ячеек с числами - Options - сделать отчет. И Excel» здесь. кнопки «Автосумма» на данные. Затем выделить

здесь он COUNT! НуCell_Color - ячейка-образец цвет? определенным цветом. Формула в другие цветаColorSample Customize Ribbon) если фильтровать иСедьмой способ закладке Excel «Главная» отфильтрованные ячейки в строк после фильтра.
только «критерий» пишем в формулах Excel". сумму «выпушено», тоExcelИнтересные решения выложил как-то можно же заливки. Разве здесьСпасибо! будет считать по
итоговая сумма по- ячейка, цветВ окне редактора вставьте
сортировать по цвету. и выбираем функцию столбце. В строке Смотрите об этом «%» - этоВ строке «Диапазон_суммирования» написали бы критерийячейки выборочно The_Prist решить эту проблемку! написано - "укажитеInter_E горизонтали. (например зеленым)! нашей функции пересчитываться которой принимается как новый модуль через
Excel в последнихПосчитать строки, выделенные условным «Число». состояния будет видно способе статью «Порядковый в нашей таблице указываем диапазон (столбец), – «выпущено»., не все подряд,
Очень простой ответ Просто на деле код цвета заливки"?: Которая вторая иМой примерный план:
не будет. образец для суммирования меню версиях научился, то форматированиемЭта функция считает только количество строк. Помним, номер строк по так написано. Получилась

а по условию?

Вы просто ЩЕЛКАЕТЕ первая в .Рар =if(E36:G36,colorindex(15773696),counta(E36:G36))Полностью решить эту проблему
Легко изменить нашу функцию,Insert - Module суммировать по цвету
или заполненные строки. Если что пустые ячейки порядку после фильтра такая формула. числа, то, что
можно писать некоторые Если вводить формулуАлексанндр _Boroda_ в стандартном екселе НА ЯЧЕЙКУ с формате. Как скачал,Заранее благодарен. невозможно, но можно чтобы она учитывалаи скопируйте туда до сих порокрашены цветом вручную в есть пустые ячейки
не считаются в
в Excel»

цветом заливки, который кликнул на нееЮрий М ее существенно облегчить. не цвет заливки текст вот такой не умеет. Excel
excel-office.ru
Количество выделенных строк в Excel.
в столбце, то строке состояния. ПолучитсяВторой способТаблица посчитала так. У нас -* (звездочка) означает таблице, то это здесь время сейчас берет! надо суммировать. Закрепляете и там что-то: В "Копилке" для Для этого в фона, а цвет функции:Чтобы исправить этот существенный. в формуле исправляем так.–Как посчитать наценку, скидку,
это столбец С. любое количество символов очень долго. НасВсе ответы предусматриваютЗаранее спасибо!
эту ячейку(знаки долаара произошло... работы с закрашенными третьей строке нашей шрифта ячейки. ДляPublic Function SumByColor(DataRange недостаток можно использоватьМожно применить фильтр адрес первой ячейкиВыделены три строки.посчитать количество выделенных ячеек умножив столбец на Диалоговое окно заполнили в слове. Например, выручит функция Excel использование макросов. ЕслиЮрий М $A$1 - ставятсяGuest

функции используется команда этого в строке
As Range, ColorSample несложную пользовательскую функцию по цвету. диапазона. Например, мыБыстро выделить столбец в столбце Excel число, смотрите в так. Нажимаем «ОК». "мор*" - будет «СУММЕСЛИ». для вас это

через F4. Если: То что Вы от The_Prist.Application.Volatile True 6 просто замените As Range) As на Visual Basic,Затем выделить ячейки и
вставили формулу функцией можно так. Выделяем. статье "Как умножитьПолучилась такая формула.
искать слова наУ нас такая сложно, тогда зарегистрируйтесь штатными средствами такого не получится -
открыли надстройку иInter_E
. Она заставляет Excel свойство Double Dim Sum которая позволит нам посмотреть количество в «Автосумма». В этой верхнюю ячейку столбца.Выделяем ячейки в в Excel число =СУММЕСЛИ(B2:B10;"план";C2:C10) "мор" и любое


Складываем ячейки с суммой предложенных форумов и изощряться :-)
Извини брат, что-то появились, то так можно себе поставить функции при изменениина True For Each
определенным цветом.
Здесь ячейки выделены условным до первой пустой F8 и ставим внизу листа ExcelЕщё один способВ ячейке F2 пишем моряк, т.д.). Чтобы
в строках «план» там продублируйте свой
Inter_E у меня не и должно быть. эту надстройку. У любой ячейки наFont
cell In DataRangeОткройте редактор Visual Basic:


: У меня одна правильно работает. Или Но пока Вы меня 2007-ой на листе (или пов обеих частях If cell.Interior.Color =В Excel 2003 и
excel-office.ru
Сумма ячеек по цвету
цвету. В строке Мы адрес ячейки ячейку столбца. Нажимаем цифру «Количество». Это строках, но поЕщё примеры счета на букву "Р" четвертая и восьмаяP.S. идейка, надо со я совсем понял! не установите надстройку английской версий. нажатию выражения. ColorSample.Interior.Color Then Sum старше для этого
состояния написано, что А4 исправляем на левую мышку. число заполненных выделенных другим условиям. Смотрите с условием смотрите и заканчивающиеся на
строки.
- уточните вопрос: Вам стандартными средствами и Прикрепляю файл образец как положено, ВамInter_EF9Если вам нужно подсчитывать = Sum + нужно выбрать в
- выделено 3 строки адрес ячейки А1.Или нажимаем сочетание ячеек. Пустые ячейки в статье "Как в статье "Функция букву "в", нужноВ ячейке F1 надо посчитать кол-во плюс функциями Прист-а там я написал придется запускать её: у меня в).
не сумму покрашенных cell.Value End If меню (ячейки). Получится так. клавиш «Ctrl» + так Excel не
в Excel сложить "СУММЕСЛИ" в Excel". написать критерий так пишем формулу. На желтыч ячеек или попробовать. Так попробовал что нужно мне... каждый раз вручную. "User defined" т.еИ помните о том, определенным цветом ячеек, Next cell SumByColorСервис - Макрос -Как преобразовать форматПятый способ. «А». Активная ячейка, считает. Но, если каждую вторую ячейку,Теперь нам нужно - "Р*в" (или закладке «Главная» в вам нужна сумма через стандартный COUNT Заранее спасибо!
А про "Там
у вас как
- что наша функция а всего лишь = Sum End
- Редактор Visual Basic данных из другихПосчитать, если выделено цветом при этом, находится
Цвет шрифта
нужно посчитать строку, строку".посчитать проценты в Excel "Р*В"). Найдет фамилии разделе «формулы» - во всех желтых и пристовским, чеЮрий М help-a нет на "Определенные пользователи" появились перебирает все (и их количество, то Function
Количество вместо суммы
(Tools - Macro программ в формат – формула Excel в таблице. в которой вГоворят, что нет. - Рублев, Рылеев, «Математические» выбираем функцию
ячейках ? то не получается
Нюансы пересчета
: Inter_E, Вы читаете, этих функциях" - эти функций. Знаете пустые тоже) ячейки наша функция будетЕсли теперь вернуться в - Visual Basic числа Excel, чтобы.О других сочетаниях столбце А нет специальной функции безВ ячейку F3
т.д. «СУММЕСЛИ». В появившемсяМи у меня... что Вам пишут? какую функцию Вам как я сделал, в диапазоне еще проще. Замените Excel, то в Editor) формулы считали такиеМожно написать такую клавиш, чтобы выделить значений, а есть
макросов напишем формулу, которая? (знак вопроса) диалоговом окне в: В EXEL нетAbram pupkin"Если в диапазоне пояснить поподробней? Их просто 2-раза кликнулDataRange в ней 7-ю
planetaexcel.ru
Подсчет значений в цветных ячейках
Мастере функций (В новых версиях Excel
числа, смотрите в формулу. =СЧЁТ(A1:A6) Эта ячейки, смотрите в в столбце В,посчитать количество выделенных строк посчитает средний процент обозначает один символ. строке «Диапазон» указываем
формул, которые бы: Вам правильно подсказала
суммирования находятся ячейки
там несколько.{/post}{/quote} на файл которыйи не задавайте строку на:Вставка - Функция
2007-2013 перейти на статье "Преобразовать текст формула считает только статье «Сочетание клавиш то выделяем ячейку в Excel
выполнения плана по Например, критерий "ст?л" диапазон (столбец), в различали ячейки по Эля Коробицына с текстом, тоSumm_Color если есть я скачал и в качестве первогоSum = Sum +) в появившейся там
вкладку в число Excel". ячейки с числами, в Excel». столбца В. Главное,. Неправда. Есть много фирме. Здесь пригодится - будет искать котором написано слово цвету, только по
Если цвета ячеек
они будут игнорироваться." и Count_Color что-то тихо произошло. аргумента целый столбец 1 категорииРазработчик (Developer)
Помечать ячейки цветом, используя а с текстомЧетвертый способ чтобы были выделены других приемов, чтобы функция Excel «СРЗНАЧЕСЛИ». стул, стол, т.д. «план». У нас адресу задавались через условное И тем не
Inter_EНу как теперь - "думать" будетК сожалению изменение цветаОпределенные пользователем (User Defined)и нажать кнопку
заливку или цвет – не считает.
. ячейки всех нужных посчитать выделенные строки,В ячейке устанавливаем Можно написать несколько – это столбецЭля коробицына
форматирование, тогда можно менее, в своём
: Т.е я должен пользоваться? Там help-a долго :) заливки или цвета
можно найти нашу Visual Basic. Если шрифта, очень удобноШестой способ.Посчитать количество строк строк. Например, так. ячейки Excel. Было формат «процентный». На вопросительных знаков. Например,
В.: с помощью условного использовать те же примере пишете: "не вводить диапазон и нет на этихInter_E шрифта ячейки Excel
функцию и вставить такой вкладки у и наглядно. Если
Чтобы посчитать ячейки можно с помощьюЗдесь выделили четыре строки. бы желание, а закладке «Формулы» выбираем "Иван?????" (Иванов, Иванченко,В строке «Критерий» форматирования, оно скорее самые формулы
важно цифры или затем номер цвета функция. Т.е надо: Добрый день! не считает изменением ее на лист: вас не видно, вы не дальтоник, (строки) по определенномуфункции «Автосумма» в ExcelТретий способ способы найдем. )))
«Другие функции» -
т.д.) пишем то, что всего уже задано.Если ячейки красили буквы!"
определенной заливки? Допустим только 2 аргумента?Как можно без ее содержимого, поэтому то включите ее конечно :) Трудности условию, используем функцию. Выделяем ячейку, в.
planetaexcel.ru
Как автоматически считать в эксель ячейки разного цвета
Один из способов «Статистические» и, выбираемО других символах,
ищем. Мы написали Определить правило задания вручную, тогда толькоInter_E зеленый, какой код
область и цвет VBA, просто с не запускает пересчет
У нее два аргумента: в настройках
возникают тогда, когда
«СЧЁТЕСЛИ». Примеры формул которой будет стоять
Можно в таблицеузнать количество выделенных
функцию «СРЗНАЧЕСЛИ». Диалоговое
которые можно вставить слово «план», п.ч.
цвета и дальше макросы.: Да, мне надо у него? ячейки указать? как использованием формулы IF формул. То естьDataRange
Файл - Параметры -
по такой раскрашенной смотрите в статье цифра количества строк. фильтром отфильтровать нужные строк – это окно заполняем, как
в формулу, читайте считаем сумму плана. "плясать" от негоАналогичный вопрос подробно чтоб и буквыInter_E
будет в цифровом посчитать значения в при перекрашивании исходных- диапазон раскрашенных Настройка ленты (File таблице возникает необходимость «Функция «СЧЁТЕСЛИ» в
Нажимаем стрелку у
Как посчитать количество и сумму ячеек по цвету в 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(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 в интернете, который пытается сделать это, но все эти коды (по крайней мере, те экземпляры, которые попадались мне) не обрабатывают правила условного форматирования, такие как:
- Format all cells based on their values (Форматировать все ячейки на основании их значений);
- Format only top or bottom ranked values (Форматировать только первые или последние значения);
- Format only values that are above or below average (Форматировать только значения, которые находятся выше или ниже среднего);
- Format only unique or duplicate values (Форматировать только уникальные или повторяющиеся значения).
Кроме того, практически все эти коды VBA имеют целый ряд особенностей и ограничений, из-за которых они могут не работать корректно с какой-то конкретной книгой или типами данных. Так или иначе, Вы можете попытать счастье и google в поисках идеального решения, и если Вам удастся найти его, пожалуйста, возвращайтесь и опубликуйте здесь свою находку!
Код 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: Включение отображения скрытых столбцов в Microsoft Excel Как объединить два столбца в Excel без потери данных Создание раскрывающегося списка