Как в 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

После того как откроется редактор 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 (файл с поддержкой макросов)

Поделиться:
25 Комментариев
  • Avtor_daun says:

    Сука, какой утырок это писал? Начал гайд в excel 2003, закончил в excel 2016. Кто ты, животное?

  • Ольга says:

    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

  • Евгений says:

    У вас ошибка и в файле и на сайте. Два раза считаете красный, а желтый не считаете

  • Валерий says:

    Вложение  -во-ОСГ.xlsx

    Всем привет.
    Подскажите как подсчитать ячейки определенного цвета если есть ограничения.
    Файл прикрепил

  • Костя says:

    Подскажите, если мне нужно посчитать кол-во ячеек с другого листа? Например красный «отказы» и вывести формулу так, чтобы она мне считала, допустим из 1500 строк 343 «отказа, то есть ячейки которые помечены красной заливкой. Это возможно и так допустим до 5 цветов?

  • Андрей says:

    По-моему в этом нет никакого смысла.
    Зачем заморачиваться с формулами, кодами цветов и в конце концов все равно будешь считать ячейки не по цветам, а по числам.
    Гораздо проще ставить в ячейках «1», а в конце или в отдельно выведенной итоговой таблице прописать =СУММ(выделить требуемый столбец от начала до конца) и все автоматически будет суммироваться, а цвет в ячейку можно добавить чисто для лучшего визуального восприятия.

    • Андрей says:

      Смысл в этом есть, если в ячейке, например, содержание замечания заказчика, а цветом показано состояние: отклонено, выполнено, в работе.

  • Светлана says:

    Вложение  _1.docx

    Добрый день! Подскажите, пжл., почему при повторном использовании данного макроса выдает ошибку? проделала все заново, выдает ошибку на моменте написания макроса (прилагаю ошибку). Заранее огромное спасибо!

    • excel says:

      А вы сохраняли файл с поддержкой макросов?

      • A says:

        Причем здесь поддержка макросов??? У вас элементарно синтаксическая ошибка!
        Public Function пишется через «u», а не через «a». VBA даже цветом не выделил, потому что не знает такой команды.

  • Станислав says:

    Как бы сделать так, чтоб шел подсчет допустим по нарядах. Например: Наряд — Ликероводочный — в строке напротив пишем две фамилии. В итоге вычисляется автоматически число два — т.е. два человека.

    • Silvia Shmidt says:

      В прикреплённом файле образец считает до 6 «фамилий» и тд. Из ячейки… Думаю этого достаточно, при желании можно предел увеличить

    • Сильвия Шмидт says:

      Приветик

      Это можно легко сделать, допустим если у вас до 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, если нет то даст количество «фамилий» указанных через пробел, если нужно могу доработать чтобы в случае если после запятой пробел опущен то его добавляло автоматом и считало корректно, ну а так вполне достаточно полагаю.

      Сильвия Шмидт

    • excel says:

      Непонятен вопрос

  • Temo4kin says:

    Добрый день!
    А как быть, если используется условное форматирование? Можно ли как-то в этом случае подсчитать ячейки по цветам?

  • Игрик says:

    Подскажите, почему пишет в ячейке #имя? , до этого все чётко работало, ( по 1 примеру) excel 2007.

  • Николай says:

    Подскажите пожалуйста, как подсчитать ячейки в строчки с разным цветом?

    • excel says:

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

  • Тина says:

    Все понятно, спасибо!

  • Айрат says:

    Статья о том, как посчитать индексы, а не закрашенные ячейки!

    • excel says:

      Только так и можно подсчитать — через индексы. Каждый цвет имеет свой уникальный индекс. Считаем кол-во этих определенных индекс — считаем кол-во ячеек определенного цвета

  • Анастасия says:

    Статья о том, как посчитать индексы, а не закрашенные ячейки! На cyberforum есть код, который на самом деле отвечает на вопрос в теме статьи.

  • Ирина says:

    Не совсем понятна данная статья. На вопрос «Как в Excel посчитать количество ячеек по цвету ячейки» до конца не ответили.Как сделать сам подсчет? В функции =СЧЁТЕСЛИМН непонятно какое условие должно быть А1 или В1, или другое? Так должно быть(A1:A8; B1)?

    • excel says:

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

Добавить комментарий

Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.

×
Рекомендуем посмотреть