счет закрашенных ячеек в excel

Excel посчитать ячейки определенного цвета

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

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

К сожалению, Excel не имеет встроенной функции, которая использует цвета заливки в качестве условий в формулах. Альтернативой является использование собственных функций (написанный на VBA). Надстройка VBA-Excel содержит функцию СЧЁТЗАЛИВКА, с помощью которой можно это реализовать.

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

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

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

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

Мы с вами уже рассматривали вопрос о том как посчитать в Excel количество ячеек/значений в подробном видео уроке. Сегодня мы бы хотели немного расширить данную статью для решения более узкой задачи. Допустим, вам необходимо посчитать количество ячеек в зависимости от цвета ячеек или цвета текста.

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

Для этих целей необходимо использовать очень простенький макрос, а точнее пользовательскую функцию, назовем ее ColorNom, она позволит нам вытягивать числовой код цвета заливки и далее по этому коду мы и будет считать общее количество ячеек, используя приемы, описываемые в статье как посчитать в Excel количество ячеек/значений

Итак, приступим. Зайдите в редактор Visual Basic, для этого:

в Excel 2003 нажмите на Сервис, далее Макрос и затем Редактор Visual Basic.

в Excel 2007, 2010 и 2013 это делается по-другому. Зайдите в раздел Разработчик, далее выберите Visual Basic

Внимание! Раздел панели инструментов Разработчик в Excel 2007 доступен по умолчанию, а в Excel 2010 и 2013 его необходимо включить. Это особенно полезно сделать тем пользователям, которые будут часто работать с макросами. Чтобы включить панель инструментов Разработчик в Excel 2010 или 2013 необходимо запустить Файл | Параметры | Настройка ленты после этого необходимо с правой стороны необходимо поставить галочку напротив надписи Разработчик

После того как откроется редактор Visual Basic, вставьте пустой модуль, для этого выберите меню Insert и далее Module

и скопируйте туда текст простой функции:

Public Function ColorNom (Cell As Range)
ColorNom = Cell.Interior.ColorIndex
End Function

После этого закройте редактор Visual Basic и можно вернуться к нашему файлу. В любой пустой ячейки введите пользовательскую функцию, которую мы ввели раннее. В нашем случае это функция ColorNom, ее можно вызвать либо через меню Вставка, Функция — категория Определенные пользователем, либо просто можно напечатать ее в самой ячейке =ColorNom ( A1 ), где A1 — это наша ячейка, в которой нам необходимо определить индекс цвета.

После этого уже не составит труда посчитать количество ячеек или значений в зависимости от цвета ячейки. Используйте нашу статью как посчитать в Excel количество ячеек/значений

Если вам необходимо посчитать количество значений или сумму в зависимости от цвета текста, то необходимо немного изменить код пользовательской функции.

Public Function ColorNom (Cell As Range)
ColorNom = Cell.Font.ColorIndex
End Function

Важно! Вы не сможете находить с помощью данной функции номер цвета ячейки при использовании условного форматирования. Кроме того, при изменении цвета ячейки Excel не пересчитывает значения, необходимо это делать в ручную, нажимая Ctrl+Alt+F9, либо изменения будут происходить при новом открытии данного файла. Это происходит из-за того, что Excel не считает изменение цвета ячейки редактированием формулы. В связи с этим, если это критично, то можно внести изменение в саму формулу, просто добавив функцию, которая постоянно пересчитывается и при этом не повлияет на определение цвета ячейки. Например, указать функцию определения текущей даты, умноженную на ноль.
В нашем случае функция будет выглядеть следующем образом.

=ColorNom (A1)+Сегодня()*0

Пример подсчета количества значений по цвету цвету заливки ячеек в Excel

Рассмотрим вышеуказанный пример с перечнем фруктов. Мы определили код ячеек и отобразили его напротив каждой ячейки.

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

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

Считать количество мы будем с помощью функции СЧЁТЕСЛИ

Вот так выглядят аргументы данной функции

=СЧЁТЕСЛИ( диапазон ; критерий )

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

Скачать пример файла: Цвет_Ячеек.xlsm (файл с поддержкой макросов)

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

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

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

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

Как считать и суммировать по цвету на листе 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 в интернете, который пытается сделать это, но все эти коды (по крайней мере, те экземпляры, которые попадались мне) не обрабатывают правила условного форматирования, такие как:

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

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

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

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

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

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

Источник

Как в excel посчитать количество закрашенных ячеек

Как посчитать количество и сумму ячеек по цвету в Excel 2010 и 2013

​ indRefColor = cellCurrent.Font.Color​Правой кнопкой мыши кликните​Из этой статьи Вы​ формате. Как скачал,​ Некоторые из них​насколько я понимаю,​ только такие ячейки,​«Количество»​ числа 150.​(​ а в нашем​С помощью оператора​Можно применить фильтр​узнать количество выделенных​ cntCells = Selection.CountLarge​ цвета шрифта в​ в ответ на​

​ Then sumRes =​ по имени Вашей​ узнаете, как в​ кликнул на нее​ решаю сама, а​ подобную задачу надо​ в которых содержится​. После этого счетчик​Выделяем ячейку для вывода​>​ случае в столбце,​СЧЁТЗ​ по цвету.​

​ строк – это​ indRefColor = ActiveCell.DisplayFormat.Interior.Color​ выбранной ячейке.​ один из комментариев​ WorksheetFunction.Sum(cellCurrent, sumRes) End​ рабочей книги в​ Excel посчитать количество​ и там что-то​ вот с некоторыми​ решать посредством VBA,​ значение больше числа​ опять будет отображаться.​ результата и производим​),​ который содержит числовые​, как и в​Затем выделить ячейки и​ настроить порядковые номера​ For indCurCell =​GetCellColor(ячейка)​ читателей (также нашим​

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

​ значения. Синтаксис этой​ предыдущем случае, имеется​ посмотреть количество в​ строк после фильтра.​​ 1 To (cntCells​​– возвращает код​ гуру Excel) и​​ SumCellsByFontColor = sumRes​​Project – VBAProject​​ определенного цвета. Этот​​Guest​​ Поэтому обнаружив такой​​ видел в интернете,​

​ не раскрашиваете ячейки​

​ Range) Dim vWbkRes​ и массу полезных​​Function GetCellColor(xlRange As​​Если Вы активно используете​​А про «Там​​ Надстройки MyAddin​ которых совпадает с​.​Выделяем область, в которую​​Это означает, что программа​​Выделяем элемент на листе,​ синтаксисе заключается лишь​СЧЁТЗ​

​ формулы считали такие​ столбце и смотрим​ vbCrLf & _​ вручную, а предпочитаете​ Dim wshCurrent As​ советов в учебнике​

​ Range) Dim indRow,​ разнообразные заливки и​ help-a нет на​​Serge_007​​ цветом фона ячейки​Открывается окно аргументов. У​ будет выводиться результат​ будет подсчитывать только​ куда будет производиться​​ в наименовании самого​​, которая относится к​

​ внизу листа Excel​ «Color=» & Left(«000000»,​ использовать условное форматирование,​

​ как мы делали​​ False Application.Calculation =​ запустить код VBA​ Dim arResults() Application.Volatile​ листах Excel, чтобы​ какую функцию Вам​ получится, формулы не​ As Variant, c​ аргумента: диапазон, где​ значку​ которые содержат числа,​ Щелкаем по значку​

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

​julytt​ As Long ObrazecCvet​ и критерий, то​.​Далее, как всегда, жмем​.​ результат. Нажимаем уже​ ячеек. Поэтому мы​ пользователем ставится задача​ ячеек. Пустые ячейки​ «Count & Sum​ заливки ячеек и​ wshCurrent In Worksheets​ будут выполнены, выберите​ xlRange = Application.ThisCell​

​ то, скорее всего,​Summ_Color если есть​: вот я и​ = Obrazec.Interior.Color For​ есть, условие, о​Открывается окно Мастера функций.​ на кнопку​В​ знакомую нам иконку​ её с легкостью​ не подсчета суммы​ так Excel не​ by Conditional Format​ Как изменить цвет​ wshCurrent.Activate vWbkRes =​ ячейки, в которые​ End If If​ захотите узнать, сколько​ и Count_Color​ хотеле с макросами,​ Each c In​ котором мы говорили​ Ищем в представленном​«OK»​Мастере функций​«Вставить функцию»​ сможем приспособить для​ значений в столбце,​ считает. Но, если​

​ color» End Sub​ заливки строки, основываясь​ vWbkRes + CountCellsByColor(wshCurrent.UsedRange,​ нужно вставить результат,​ xlRange.Count > 1​ ячеек выделено определённым​Inter_E​

​ а вот то​
​ Oblast.Cells If c.Interior.Color​

​ выше. В поле​ списке элемент​.​совершаем переход в​.​ наших нужд, а​ а подсчета их​ нужно посчитать строку,​Добавьте код, приведённый выше,​ на значении ячейки?​​ cellRefColor) Next Application.ScreenUpdating​​ и введите в​ Then ReDim arResults(1​ цветом. Если же​: Т.е я должен​ что мне удалось​

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

​ = ObrazecCvet Then​«Диапазон»​«СЧЁТЗ»​После проведения подсчета Excel​ категорию​После запуска​ именно для подсчета​ количества. То есть,​

​ в которой в​​ на Ваш лист,​Если Вы применили условное​ = True Application.Calculation​ них функцию​ To xlRange.Rows.Count, 1​ в ячейках хранятся​ вводить диапазон и​ нарыть в интеренте​ Summa1 = Summa1​

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

​ форматирование, чтобы задать​

​ = xlCalculationAutomatic WbkCountCellsByColor​​CountCellsByColor​​ To xlRange.Columns.Count) For​ числа, то, вероятно,​​ затем номер цвета​​ не работает. если​ + 1 End​ области, а в​

​ это наименование выделено,​ обозначенную ячейку результат.​, выделяем название​

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

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

​ в столбце В,​ примере.​ в зависимости от​ Function Function WbkSumCellsByColor(cellRefColor​CountCellsByColor(диапазон, код_цвета)​ To xlRange.Rows.Count For​ сумму всех ячеек​ зеленый, какой код​Serge_007​ = Summa1 End​«Критерий»​«OK»​ столбец содержит 82​и щелкаем по​«Статистические»​=СЧЁТЗ(значение1;значение2;…)​ заполнено определенными числовыми​ то выделяем ячейку​

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

​Выберите диапазон (или диапазоны),​ их значений, и​ As Range) Dim​В этом примере мы​ indColumn = 1​ с одинаковой заливкой,​ у него?​:​ Function Function СумЕслиЦвет(Oblast​вписываем условия. После​.​ значения, которые превышают​ кнопке​. Затем выделяем наименование​Всего у оператора может​ или текстовыми данными.​ столбца В. Главное,​ в которых нужно​ теперь хотите посчитать​ vWbkRes Dim wshCurrent​ используем формулу​ To xlRange.Columns.Count arResults(indRow,​

​ например, сумму всех​Inter_E​Quote​ As Range, Obrazec​ этого жмем на​Запускается окно аргументов. Аргументами​ число 150.​«OK»​«СЧЁТ»​ насчитываться до 255​ В Экселе существует​

​ = xlCalculationManual vWbkRes​F2:F14​ GetCellColor = arResults​ предоставляет набор функций​ заливки. Разве здесь​Вы сами себе​ заданный диапазон ячеек​.​ Ссылку на диапазон​ существует целый ряд​ функции​После того, как было​. В качестве аргументов​ указанную проблему. Рассмотрим​Здесь выделили четыре строки.​ них содержатся числовые​

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

​ Excel​ фильтром отфильтровать нужные​​, кликните по одной​​ функции, которая будет​ SumCellsByColor(wshCurrent.UsedRange, cellRefColor) Next​

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

​ В этом окне,​Выделяем элемент листа, в​ количество строк в​ отфильтрованные ячейки в​ затем отпустите​ или считать количество​ Application.Calculation = xlCalculationAutomatic​ цвет заливки, в​ arResults() Application.Volatile If​ существует формулы, которая​ эту ячейку(знаки долаара​ форума Microsoft:​​ As Range, ObrazecCvet​​ соответствуют заданному условию,​​ соответствующую область на​​ состояния позволяет только​«Диапазон»​

​ как и в​ который будет выводиться​
​ Excel​
​ столбце. В строке​

Количество выделенных строк в Excel.

Подсчет количества значений в столбце в Microsoft Excel

​CountByConditionalFormat​ пытается сделать это,​=WbkCountCellsByColor()​ (например, столбец​ xlRange.Rows.Count For indColumn​ мало знаете об​ там я написал​ устанавливать​ Function​ для различных диапазонов.​«Значение2»​производит подсчет только​ туда слово​. Вводим в это​и выделяем наименование​ которые соответствуют определенному​ верхнюю ячейку столбца.​

​и нажмите​ но все эти​

​=WbkSumCellsByColor()​Qty.​ = 1 To​
​ этой технологии или​ что нужно мне. ​Serge_007​

Процедура подсчета значений в столбце

​Byronel​ Всего можно указать​,​ элементов, содержащих числовые​«Мясо»​ поле координаты столбца,​«СЧЁТЗ»​ заданному условию. Давайте​ Нажимаем на клавишу​Run​ коды (по крайней​

Способ 1: индикатор в строке состояния

​.​ над которым нам​. После этого производим​ рассмотрим, как решить​ F8 и ставим​(Выполнить).​ мере, те экземпляры,​ этих формул в​ Вы можете суммировать​ = xlRange(indRow, indColumn).Font.Color​ слышали этого термина,​​Юрий М​​ просто книга Excel,​ VBA работал давно​Обозначаем ячейку, в которую​и т.д. Когда​ помощью функции​После того, как вышеуказанные​ нужно выполнить операцию.​ щелчок по кнопке​ поставленные задачи различными​

​ курсор в нижнюю​В результате Вы увидите​ которые попадались мне)​ любую пустую ячейку​ значения на основе​ Next Next GetCellFontColor​ не пугайтесь, Вам​: Inter_E, Вы читаете,​ в которой хранятся​создал модуль, забил​ будет выводиться результат​ все данные введены.​СЧЁТЕСЛИ​​ настройки выполнены, жмем​​ Делаем это все​«OK»​ способами.​ ячейку столбца. Нажимаем​

​ вот такое сообщение:​ не обрабатывают правила​ на любом листе​ выбранного цвета ячейки,​ = arResults Else​ не придётся писать​ что Вам пишут?​ макросы​ туда этот код​ и запускаем Мастер​ Жмем на кнопку​можно задать более​ на кнопку​ тем же образом,​внизу данного окошка.​Данный способ самый простой​ левую мышку.​

Способ 2: оператор СЧЁТЗ

​Для этого примера мы​​ условного форматирования, такие​​ Excel. Диапазон указывать​ используя аналогичную функцию​ GetCellFontColor = xlRange.Font.Color​ код самостоятельно. Здесь​»Если в диапазоне​Как Вы будете​ и сохранил, выбрал​ функций. В нем​«OK»​ сложные условия подсчета​«OK»​ каким выполняли данную​

​Мы переходим к окну​​ и требующий минимального​​Или нажимаем сочетание​ выбрали столбец​ как:​ не нужно, но​SumCellsByColor​ End If End​ Вы найдёте отличный​ суммирования находятся ячейки​ использовать ДРУГИЕ макросы,​ имя модуля «test»​ ищем элемент​.​

​.​ процедуру для функции​ аргументов функции​​ количества действий. Он​​ клавиш «Ctrl» +​Qty.​Format all cells based​ необходимо в скобках​:​ Function Function CountCellsByColor(rData​

​: Ну, если надстройку​End Sub»​«OK»​ формул, придерживаясь следующего​ с таблицей бывает​ в выделенной колонке​ колонку таблицы. После​ Как и количество​​ Сделать это можно​​О других сочетаниях​– это число​ значений);​

​=WbkSumCellsByColor(A1)​ использовали формулу:​

Способ 3: оператор СЧЁТ

​ As Long Dim​​ – это скопировать​​ примере пишете: «не​ низзя, тогда отдельно​забил внутрь текст​.​ синтаксиса:​ необходимость произвести подсчет​ в 63 ячейках​ того, как адрес​ аргументов, они могут​ просто взглянув на​ клавиш, чтобы выделить​ ячеек искомого цвета;​Format only top or​, и формула вернет​=SumCellsByColor(D2:D14,A17)​ cellCurrent As Range​ его и вставить​ важно цифры или​ зти макросы здесь.​

​Происходит открытие окна аргументов.​​=СЧЁТЗ(значение1;значение2;…)​​ заполненных данными ячеек.​​ содержится слово​​ столбца был занесен​ достигать численности 255​ индикатор в строке​ ячейки, смотрите в​ в нашем случае​ bottom ranked values​ сумму всех ячеек​

Способ 4: оператор СЧЁТЕСЛИ

​ кнопку мыши и​.​​Past Due​​ are above or​Здесь Вы найдёте самые​– ячейка с​ For Each cellCurrent​ раскрашены вручную​ считалось, потому и​

​Считаем сумму и количество​​ он COUNT! Ну​​ участие и помощь.​ код цвета, насколько​и​ диапазона.​ этой программе.​

​ же колонке, которые​​ выведен в ячейку,​​. Устанавливаем в него​ в котором вы​ можно с помощью​Sum​(Форматировать только значения,​ всем функциям, использованным​​Таким же образом Вы​​ indRefColor = cellCurrent.Interior.Color​​ ячеек по цвету​​ как-то можно же​​ Разбираюсь. Надеюсь все​​ я понял именно​​«Условие»​​Кроме того, для подсчета​​Скачать последнюю версию​​ не содержат слово​​ которую мы определили​

​ курсор и после​ хотите произвести подсчет​​функции «Автосумма» в Excel​​– это сумма​ которые находятся выше​

​ том, что диапазонов​​ Экселе существует ещё​​В программе Excel количество​.​ Как видим, программа​ левой кнопкой мыши​ выделение будет произведено,​

​ которой будет стоять​​ красного цвета в​​Format only unique or​ пару новых функций,​ цвету шрифта при​​ End If Next​​ цвету, когда к​

​ сижу, самому найти​: Добрый день!​Heidegger​​ и соответствующих им​​ функция счет. В​

​ которые определяют коды​ помощи функций​ cellCurrent CountCellsByColor =​ ячейкам применены правила​ в стандартном екселе​​Как можно без​​: Текст функций не​

​ Нажимаем стрелку у​​Qty.​​(Форматировать только уникальные​ цветов.​CountCellsByFontColor​ cntRes End Function​

​ условного форматирования​​ через заводских надстройеек​​ VBA, просто с​

​ нужно никуда забивать​

​ много. Вводим адреса​ формулы, она считает​ счетчика на строке​ уже описанным ранее​ значения. Пустые ячейки​ в котором нужно​​ окна, около параметра​​ кнопки «Автосумма» на​​, то есть общее​​ или повторяющиеся значения).​​Замечание:​​и​

​ Function SumCellsByColor(rData As​Предположим, у Вас есть​ время сейчас берет!​​ использованием формулы IF​​ – они должны​

​Пожалуйста, помните, что​SumCellsByFontColor​ Range, cellRefColor As​ таблица заказов компании,​Заранее спасибо!​

​ текстовые данные, в​​ как координаты столбца​​будет отображаться число​ и выбираем функцию​

​ отметкой​​ эти коды VBA​​ все эти формулы​

​ Range) Dim indRefColor​ в которой ячейки​Юрий М​ ячейках которые залиты​ уровне.​

​ потом жмем на​Как и в предыдущем​​ из которых подсчитывает​​СЧЁТЕСЛИ​

​Замечание:​ As Long Dim​ в столбце​: Я полагаю, что​ определенным цветом. Формула​Нет, вторым аргументом​ кнопку​ случае, выделяем ячейку,​ элементы, заполненные определенным​.​Урок: Функция СЧЁТ в​ жмем на кнопку​ столбце. В подсчете​Эта функция считает только​​.​​ особенностей и ограничений,​ Вы уже добавили​Если после применения​​ cellCurrent As Range​​Delivery​ штатными средствами такого​ будет считать по​ функции идёт ячейка,​​«OK»​​ куда будут выводиться​ типом данных.​В поле​

Подсчет заполненных ячеек в Microsoft Excel

​«OK»​ будут участвовать ячейки,​ заполненные строки. Если​Color​ из-за которых они​ в свою рабочую​ выше описанного кода​ Dim sumRes Application.Volatile​раскрашены в зависимости​ не сделать. Нужно​ горизонтали. (например зеленым)!​

​ имеющая целевой цвет,​.​

Подсчет ячеек

​ данные и таким​Самый простой способ подсчитать​«Диапазон»​В отличие от предыдущих​в нижней части​ заполненные любыми данными​ есть пустые ячейки​– это шестнадцатеричный​ могут не работать​

Способ 1: счетчик на строке состояния

​ книгу Excel пользовательскую​ VBA Вам вдруг​ sumRes = 0​ от их значений:​ изощряться :-)​Мой примерный план:​ вхождения которого ищутся​Синтаксис у этой функции​ же способом запускаем​ ячейки, содержащие данные​вводим координаты все​ способов, использование оператора​ окна аргументов.​ (числовые, текстовые, дата​ в столбце, то​ код цвета выделенной​ корректно с какой-то​ функцию, как было​ потребуется раскрасить ещё​ indRefColor = cellRefColor.Cells(1,​Due in X Days​Inter_E​​ =if(E36:G36,colorindex(15773696),counta(E36:G36))​​ во входном диапазоне​

​ следующий:​ Мастер функций. В​ – это воспользоваться​ того же первого​СЧЁТЕСЛИ​Программа производит подсчет и​ и т.д.). Пустые​ в формуле исправляем​ ячейки, в нашем​ конкретной книгой или​ показано ранее в​ несколько ячеек вручную,​ 1).Interior.Color For Each​– оранжевые,​: У меня одна​Заранее благодарен.​​ (первый аргумент функции).​​=СЧЁТЕСЛИМН(диапазон_условия1;условие1; диапазон_условия2;условие2;…)​ нём выбираем оператора​

Способ 2: функция СЧЁТЗ

​ информацией со счетчика,​ столбца таблицы, который​позволяет задавать условия,​ выводит в ячейку,​ элементы при подсчете​ адрес первой ячейки​ случае​ типами данных. Так​ этой статье.​ сумма и количество​ cellCurrent In rData​Delivered​

Способ 3: функция СЧЁТ

​«OK»​ кнопок переключения режимов​вводим следующее выражение:​ остальные ячейки будут​ всех значений (как​ не высвечиваться в​ формуле указан диапазон​ трудности с добавлением​

​Запускается окно аргументов. Аргументы​ Пока на листе​

​То есть, данный критерий​

Способ 4: функция СЧЁТЕСЛИ

​ удастся найти его,​ вот такую формулу​На самом деле, это​ sumRes End Function​ по их цвету,​ то не получается​: Спасибо, но как​Byronel​Как видим, простейший подсчет​ те же самые,​ выделен диапазон, в​ задает условие, что​СЧЁТЕСЛИ​ целевом столбце.​ он, скорее всего,​ Мы адрес ячейки​

​ использовании предыдущего способа.​ пустые или лишь​

​ заполненные данными элементы,​

Способ 5: функция СЧЁТЕСЛИМН

​ Range) As Long​ и оранжевых ячеек​: Добрый день! Такое:​ меня 2007-ой на​ с ним.​ увидеть в строке​ В их роли​ один содержит какое-то​

​Inter_E​: To Heidegger :​

​Посчитать, если выделено цветом​ примерами и с​ выше ограничения и​F2:F14​ все подобные функции​ As Range Dim​ выше, прямого решения​ сумму только тех​: у меня в​Сознательно не вставлял​ вывести результат в​ диапазонов на листе,​ появляется тогда, когда​. Знак​ диапазоне, а в​

Посчитать количество цветных ячеек

​ нем нужно установить​​ – формула Excel​
​ готовыми к использованию​
​ работает в таблицах​– это выбранный​ вызываются только изменением​ cntRes As Long​ этой задачи не​ ячеек, где фон​
​ «User defined» т.е​ Application.Volatile, чтобы не​
​ отдельную область на​ в которых нужно​ выделено две или​
​«<>»​ нашем случае в​ сохранением там. Но,​ галочку около пункта​.​ функциями​ Microsoft Excel 2010​
​ диапазон,​

​ данных на листе,​​ Application.Volatile cntRes =​ существует. Но, к​ зеленый.​ у вас как​ вводить пользователя в​ листе, а тем​ подсчитать количество заполненных​ более не пустых​означает в Экселе​ столбце, которые отвечают​ к сожалению, функция​«Количество»​Можно написать такую​CountCellsByColor​ и 2013, с​A17​ но Excel не​ 0 indRefColor =​ счастью, в нашей​Это первое.​ «Определенные пользователи» появились​ заблуждение: да при​ более произвести подсчет​ ячеек числовыми данными.​ ячеек, и сразу​«не равно»​ заданному условию. Синтаксис​СЧЁТЗ​. После этого количество​ формулу. =СЧЁТ(A1:A6) Эта​и​ любыми типами условного​– это ячейка​ расценивает изменение цвета​ cellRefColor.Cells(1, 1).Font.Color For​ команде есть очень​Можно ли из​ эти функций. Знаете​ изменении значений в​ с учетом определенных​ Жмем кнопку​ показывает их число​

​.​​ у данного оператора​все-таки не позволяет​
​ заполненных данными ячеек​ формула считает только​SumCellsByColor​ форматирования (и снова​ с нужным цветом​ шрифта или заливки​
​ Each cellCurrent In​
​ умелые и знающие​
​ макроса обращаться к​ как я сделал,​ целевых ячейках функция​ условий, то в​«OK»​
​ после слова​После введения этих настроек​ заметно отличается от​ задавать условия отбора​ будет отображаться в​ ячейки с числами,​

​, и испытать их​​ спасибо нашему гуру!).​ заливки.​ ячейки как изменение​ rData If indRefColor​ Excel гуру, и​
​ фону ячейки, вытаскивать​ просто 2-раза кликнул​ пересчитается, но если​ этом случае придут​.​«Количество»​
​ в окне аргументов​​ предыдущих двух функций:​ значений.​ строке состояния.​ а с текстом​ на своих данных.​
​ В результате он​​Все перечисленные далее формулы​ данных. Поэтому, после​ = cellCurrent.Font.Color Then​

​ один из них​​ значение фона и​
​ на файл который​ не меняя значений​ на помощь специализированные​Для ручного введения формулы​.​ жмем на кнопку​=СЧЁТЕСЛИ(диапазон;критерий)​Урок: Мастер функций в​К недостаткам данного способа​ – не считает.​Урок подготовлен для Вас​ выводит количество раскрашенных​ работают по такому​ изменения цвета ячеек​

​ cntRes = cntRes​​ написал безупречный код​

​ менять его?​​ я скачал и​ поменять цвета ячеек​

Подсчет ячеек определенного цвета

​ функции.​​ придерживаемся следующего синтаксиса:​
​Но, хотя по умолчанию​«OK»​Аргумент​ Excel​ можно отнести то,​Шестой способ.​ командой сайта office-guru.ru​ ячеек и сумму​ же принципу.​ вручную, просто поставьте​ + 1 End​
​ для Excel 2010​sboy​ что-то тихо произошло.​ в целевом диапазоне​Автор: Максим Тютюшев​=СЧЁТ(значение1;значение2;…)​ этот счетчик включен,​.​

​«Диапазон»​​С помощью оператора​ что полученный результат​Чтобы посчитать ячейки​

​Источник: https://www.ablebits.com/office-addins-blog/2013/12/12/count-sort-by-color-excel/​​ значений в этих​CountCellsByFontColor(диапазон, код_цвета)​ курсор на любую​ If Next cellCurrent​ и 2013. Итак,​: И Вам здравствуйте!​Ну как теперь​

​: добрый день!​​ в которой находится​

​ того, когда пользователь​​ сразу же отображается​ ссылки на конкретный​

​можно произвести подсчет​​ То есть, как​ условию, используем функцию​Автор: Антон Андронов​ типа условного форматирования,​
​ с заданным цветом​F2​ End Function Function​ шагов, описанных далее,​

​: можно​​ нет на этих​ надо вводить ее​есть 2 столбца​
​ формула, отобразится количество​ выделит определенные элементы,​ результат. Он сообщает​

​ массив ячеек, а​​ только числовых значений​ только вы снимете​ «СЧЁТЕСЛИ». Примеры формул​Говорят, что нет​

Подсчет значений в цветных ячейках

​ применённого на листе.​​ шрифта.​
​, а затем​ SumCellsByFontColor(rData As Range,​ и через несколько​svd2017​ функция. Т.е надо​ заново.​ячейки в них​ ячеек, заполненных числовыми​
​ в некоторых случаях​ о том, что​
​ в нашем случае​

​ в выбранной колонке.​​ выделение, он исчезнет.​ смотрите в статье​ специальной функции без​Sub SumCountByConditionalFormat() Dim​

​SumCellsByColor(диапазон, код_цвета)​​Enter​ cellRefColor As Range)​ минут Вы узнаете​: ура​ только 2 аргумента?​

​Аргумент​​ значения и не​ его зафиксировать, придется​ Excel» здесь.​посчитать количество выделенных строк​ Dim cellCurrent As​ ячеек с заданным​

​ Так нужно сделать,​ As Range Dim​

​Откройте книгу Excel и​​ туда фон ячейки​ будет в цифровом​: Да, тогда пересчитает​ выбирал оттенки через​ ячеек, заполненных числовыми​ о его включении.​

​ выражениями, но только​ Для этого нужно​«Мясо»​ Это может быть​ функция также относится​ с помощью данного​Посчитать строки, выделенные условным​ других приемов, чтобы​

​ sumRes Dim cntCells​​– вычисляет сумму​ макросом, который Вы​
​ = 0 indRefColor​Alt+F11​buchlotnik​ цвет?​julytt​необходимо посчитать количество​ такие из них,​ кликнуть правой кнопкой​.​

​ как точное числовое​​ к категории статистических​ способа можно производить​ форматированием​ посчитать выделенные строки,​ As Long Dim​ ячеек с заданным​ найдёте далее в​ = cellRefColor.Cells(1, 1).Font.Color​, чтобы запустить редактор​: читаем правила форума​Спасибо!​
​: Доброго дня всем!​

​ ячеек определенного цвета​​ которые соответствуют определенному​ мыши по строке​Теперь давайте произведем в​ или текстовое значение,​

Посчитать значения в закрашенных ячейках. (Макросы/Sub)

​Inter_E​​Пытаюсь усовершеннствовать таблицы,​
​в ячейках значений​ условию. Например, если​ состояния и в​ третьей колонке данной​ так и значение,​
​ предыдущая. Её задачей​
​ заполненных значениями ячеек​окрашены цветом вручную в​ бы желание, а​ cntRes = 0​GetCellFontColor(ячейка)​

​Представленный ниже скрипт Visual​​ In rData If​

​(VBA).​​ получаем помощь​

​: Которая вторая и​​ с которыми работаю.​
​ нет, они просто​ задать условие «>50»,​ открывшемся списке установить​ таблицы подсчет всех​

​ заданное знаками​​ является подсчет ячеек​ и нельзя задавать​ Excel​

Источник