Как в Excel посчитать количество ячеек по цвету ячейки или цвету текста
Мы с вами уже рассматривали вопрос о том как посчитать в 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 2003, закончил в excel 2016. Кто ты, животное?
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
У вас ошибка и в файле и на сайте. Два раза считаете красный, а желтый не считаете
Вложение -во-ОСГ.xlsx
Всем привет.
Подскажите как подсчитать ячейки определенного цвета если есть ограничения.
Файл прикрепил
Подскажите, если мне нужно посчитать кол-во ячеек с другого листа? Например красный “отказы” и вывести формулу так, чтобы она мне считала, допустим из 1500 строк 343 “отказа, то есть ячейки которые помечены красной заливкой. Это возможно и так допустим до 5 цветов?
По-моему в этом нет никакого смысла.
Зачем заморачиваться с формулами, кодами цветов и в конце концов все равно будешь считать ячейки не по цветам, а по числам.
Гораздо проще ставить в ячейках “1”, а в конце или в отдельно выведенной итоговой таблице прописать =СУММ(выделить требуемый столбец от начала до конца) и все автоматически будет суммироваться, а цвет в ячейку можно добавить чисто для лучшего визуального восприятия.
Смысл в этом есть, если в ячейке, например, содержание замечания заказчика, а цветом показано состояние: отклонено, выполнено, в работе.
Вложение _1.docx
Добрый день! Подскажите, пжл., почему при повторном использовании данного макроса выдает ошибку? проделала все заново, выдает ошибку на моменте написания макроса (прилагаю ошибку). Заранее огромное спасибо!
А вы сохраняли файл с поддержкой макросов?
Причем здесь поддержка макросов??? У вас элементарно синтаксическая ошибка!
Public Function пишется через “u”, а не через “a”. VBA даже цветом не выделил, потому что не знает такой команды.
Как бы сделать так, чтоб шел подсчет допустим по нарядах. Например: Наряд – Ликероводочный – в строке напротив пишем две фамилии. В итоге вычисляется автоматически число два – т.е. два человека.
В прикреплённом файле образец считает до 6 “фамилий” и тд. Из ячейки… Думаю этого достаточно, при желании можно предел увеличить
Приветик
Это можно легко сделать, допустим если у вас до 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, если нет то даст количество “фамилий” указанных через пробел, если нужно могу доработать чтобы в случае если после запятой пробел опущен то его добавляло автоматом и считало корректно, ну а так вполне достаточно полагаю.
Сильвия Шмидт
Непонятен вопрос
Добрый день!
А как быть, если используется условное форматирование? Можно ли как-то в этом случае подсчитать ячейки по цветам?
Нет, нельзя
Подскажите, почему пишет в ячейке #имя? , до этого все чётко работало, ( по 1 примеру) excel 2007.
Подскажите пожалуйста, как подсчитать ячейки в строчки с разным цветом?
рядом можно сделать дубль ячеек с отображение кода ячеек, а далее уже считаете одинаковый код нужного цвета
Все понятно, спасибо!
Статья о том, как посчитать индексы, а не закрашенные ячейки!
Только так и можно подсчитать – через индексы. Каждый цвет имеет свой уникальный индекс. Считаем кол-во этих определенных индекс – считаем кол-во ячеек определенного цвета
Статья о том, как посчитать индексы, а не закрашенные ячейки! На cyberforum есть код, который на самом деле отвечает на вопрос в теме статьи.
Не совсем понятна данная статья. На вопрос “Как в Excel посчитать количество ячеек по цвету ячейки” до конца не ответили.Как сделать сам подсчет? В функции =СЧЁТЕСЛИМН непонятно какое условие должно быть А1 или В1, или другое? Так должно быть(A1:A8; B1)?
Там есть ссылка на статью, где рассматривается данный вопрос. Но вы не первый человек, который спрашивает, поэтому дописал статью с примером.