Мы с вами уже рассматривали вопрос о том как посчитать в 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
Рассмотрим вышеуказанный пример с перечнем фруктов. Мы определили код ячеек и отобразили его напротив каждой ячейки.
Далее для удобства мы создадим вспомогательную таблицу из всех существующих цветов заливки. В нашем случае это красный, зеленый и желтый. Рядом с помощью все той же формулы определим код цвета.
В третьем столбце мы уже будет считать количество ячеек определенного цвета по условию, использую код цвета.
Считать количество мы будем с помощью функции СЧЁТЕСЛИ
Вот так выглядят аргументы данной функции
=СЧЁТЕСЛИ(диапазон;критерий)
Пропишем формулу:
=СЧЁТЕСЛИ($B$1:$B$8;E2)
Диапазон мы указали со знаком доллара, чтобы он был закреплен и можно было протянуть формулу. Критерия у нас встречается всего три и они указаны в нашей вспомогательной таблице. Протянем формулу и получим количество ячеек по цветам.
Скачать пример файла: Цвет_Ячеек.xlsm (файл с поддержкой макросов)
Не совсем понятна данная статья. На вопрос «Как в Excel посчитать количество ячеек по цвету ячейки» до конца не ответили.Как сделать сам подсчет? В функции =СЧЁТЕСЛИМН непонятно какое условие должно быть А1 или В1, или другое? Так должно быть(A1:A8; B1)?
Там есть ссылка на статью, где рассматривается данный вопрос. Но вы не первый человек, который спрашивает, поэтому дописал статью с примером.
Статья о том, как посчитать индексы, а не закрашенные ячейки! На cyberforum есть код, который на самом деле отвечает на вопрос в теме статьи.
Статья о том, как посчитать индексы, а не закрашенные ячейки!
Только так и можно подсчитать — через индексы. Каждый цвет имеет свой уникальный индекс. Считаем кол-во этих определенных индекс — считаем кол-во ячеек определенного цвета
Все понятно, спасибо!
Подскажите пожалуйста, как подсчитать ячейки в строчки с разным цветом?
рядом можно сделать дубль ячеек с отображение кода ячеек, а далее уже считаете одинаковый код нужного цвета
Подскажите, почему пишет в ячейке #имя? , до этого все чётко работало, ( по 1 примеру) excel 2007.
Добрый день!
А как быть, если используется условное форматирование? Можно ли как-то в этом случае подсчитать ячейки по цветам?
Нет, нельзя
Сells(1,1).DisplayFormat.Color
Сells(1,1).DisplayFormat.Font.Color
Как бы сделать так, чтоб шел подсчет допустим по нарядах. Например: Наряд — Ликероводочный — в строке напротив пишем две фамилии. В итоге вычисляется автоматически число два — т.е. два человека.
Непонятен вопрос
Приветик
Это можно легко сделать, допустим если у вас до 6-и человек то при помощи формулы;
=ЕСЛИ(СЖПРОБЕЛЫ(B3)=»»;0;СУММ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(» «;СЖПРОБЕЛЫ(B3);1)=0;0;1);0);ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(» «;СЖПРОБЕЛЫ(B3);ПОИСК(» «;СЖПРОБЕЛЫ(B3);1)+1)=0;0;1);0);ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(» «;СЖПРОБЕЛЫ(B3);ПОИСК(» «;СЖПРОБЕЛЫ(B3);ПОИСК(» «;СЖПРОБЕЛЫ(B3);1)+1)+1)=0;0;1);0);ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(» «;СЖПРОБЕЛЫ(B3);ПОИСК(» «;СЖПРОБЕЛЫ(B3);ПОИСК(» «;СЖПРОБЕЛЫ(B3);ПОИСК(» «;СЖПРОБЕЛЫ(B3);1)+1)+1)+1)=0;0;1);0);ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(» «;СЖПРОБЕЛЫ(B3);ПОИСК(» «;СЖПРОБЕЛЫ(B3);ПОИСК(» «;СЖПРОБЕЛЫ(B3);ПОИСК(» «;СЖПРОБЕЛЫ(B3);ПОИСК(» «;СЖПРОБЕЛЫ(B3);1)+1)+1)+1)+1)=0;0;1);0);1))
Если пусто она даст 0, если нет то даст количество «фамилий» указанных через пробел, если нужно могу доработать чтобы в случае если после запятой пробел опущен то его добавляло автоматом и считало корректно, ну а так вполне достаточно полагаю.
Сильвия Шмидт
В прикреплённом файле образец считает до 6 «фамилий» и тд. Из ячейки… Думаю этого достаточно, при желании можно предел увеличить
Добрый день! Подскажите, пжл., почему при повторном использовании данного макроса выдает ошибку? проделала все заново, выдает ошибку на моменте написания макроса (прилагаю ошибку). Заранее огромное спасибо!
А вы сохраняли файл с поддержкой макросов?
Причем здесь поддержка макросов??? У вас элементарно синтаксическая ошибка!
Public Function пишется через «u», а не через «a». VBA даже цветом не выделил, потому что не знает такой команды.
По-моему в этом нет никакого смысла.
Зачем заморачиваться с формулами, кодами цветов и в конце концов все равно будешь считать ячейки не по цветам, а по числам.
Гораздо проще ставить в ячейках «1», а в конце или в отдельно выведенной итоговой таблице прописать =СУММ(выделить требуемый столбец от начала до конца) и все автоматически будет суммироваться, а цвет в ячейку можно добавить чисто для лучшего визуального восприятия.
Смысл в этом есть, если в ячейке, например, содержание замечания заказчика, а цветом показано состояние: отклонено, выполнено, в работе.
Подскажите, если мне нужно посчитать кол-во ячеек с другого листа? Например красный «отказы» и вывести формулу так, чтобы она мне считала, допустим из 1500 строк 343 «отказа, то есть ячейки которые помечены красной заливкой. Это возможно и так допустим до 5 цветов?
Всем привет.
Подскажите как подсчитать ячейки определенного цвета если есть ограничения.
Файл прикрепил
У вас ошибка и в файле и на сайте. Два раза считаете красный, а желтый не считаете
Public Function CountByColor(DataRange As Range, ColorCell As Range) As Long
Dim kolvo As Long
Application.Volatile True
kolvo = 0
For Each Cell In DataRange
If Cell.Interior.Color = ColorCell.Interior.Color Then
kolvo = kolvo + 1
End If
Next Cell
CountByColor = kolvo
End Function
Сука, какой утырок это писал? Начал гайд в excel 2003, закончил в excel 2016. Кто ты, животное?