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

Поделиться:
11 Комментариев
  • Temo4kin:

    Добрый день!

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

  • Игрик:

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

  • Николай:

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

    • excel:

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

  • Тина:

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

  • Айрат:

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

    • excel:

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

  • Анастасия:

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

  • Ирина:

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

    • excel:

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

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

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