Как посчитать цветные ячейки в Excel?

В этом посте мы покажем вам, как подсчитывать цветные ячейки в Microsoft Excel.

При работе с Excel мы часто выделяем ячейки цветом, чтобы отличить их от остальных данных. Это хорошо, потому что единственная забота — привлечь чье-то внимание. Но когда мы окрашиваем ячейки, чтобы указать какое-то значение (например, «зеленый» для утвержденных и «красный» для не утвержденных), нам может потребоваться подсчет этих значений в какой-то более поздний момент времени.

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

Как посчитать цветные ячейки в Excel?

Для подсчета цветных ячеек в Microsoft Excel вы можете использовать Инструмент «Найти и выбрать» или Эксель Фильтр и ПРОМЕЖУТОЧНЫЕ ИТОГО() функция. Давайте рассмотрим оба этих метода подробно.

1) Используйте инструмент «Найти и выделить».

Инструмент «Найти и выбрать» используется для поиска и выбора ячеек, отвечающих определенным требованиям. Он позволяет выполнять команды «Найти», «Найти и заменить» и «Перейти» в Excel. С помощью команды «Найти» вы можете подсчитать количество ячеек, имеющих определенный цвет фона. Вот как:

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

Выберите диапазон данных (A2:A6). Щелкните раскрывающийся список «Найти и выбрать» в разделе «Редактирование» (в правом верхнем углу). Нажмите на опцию «Найти».

Появится диалоговое окно «Найти и заменить». Нажмите кнопку Параметры>>, чтобы развернуть диалоговое окно.

Ничего не вводите в поле «Найти». Щелкните стрелку раскрывающегося списка на кнопке «Формат» и выберите параметр «Выбрать формат из ячейки…».

Когда вы выберете этот параметр, диалоговое окно «Найти и заменить» исчезнет, ​​а указатель мыши превратится в символ «плюс» с палитрой цветов рядом с ним.

Примечание. Если вы нажмете кнопку «Формат» вместо стрелки раскрывающегося списка, появится окно «Найти формат». Затем вы можете нажать кнопку «Выбрать формат из ячейки» в нижней части окна, чтобы открыть палитру цветов.

Теперь наведите палитру цветов на ячейку желтого цвета и щелкните по ней.

Диалоговое окно «Найти и заменить» снова появится (если оно исчезло) и отобразит выбранный цвет в поле «Предварительный просмотр». Нажмите кнопку «Найти все» внизу.

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

2) Используйте фильтр Excel и функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ()

Функцию ПРОМЕЖУТОЧНЫЕИТОГИ() можно использовать для выполнения вычислений в заданном диапазоне ячеек. Его можно использовать для применения других функций к диапазону ячеек, игнорируя при этом ячейки, которые не должны участвовать в вычислении. В общей сложности 11 функций поддерживаются функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ().

Он имеет следующий синтаксис:

ПРОМЕЖУТОЧНЫЙ ИТОГ(номер_функции,ссылка1,(ссылка2),…)

Где,

• номер_функции — это числовая ссылка на функцию, которую следует использовать в расчетах. В качестве значения принимает от 1 до 11 или от 101 до 111. Например, 1 или 101 для AVERAGE(), 2 или 102 для COUNT(), 3 или 103 для COUNTA() и т. д. Изменение числовой ссылки определяет, следует ли включать (1–11) или исключать (101–111). ) значения скрытых ячеек в функции.
• ref1 относится к диапазону ячеек, промежуточный итог которого требуется.
• ref2 относится к другому диапазону ячеек для расчета. Это может подняться до ref254.

Взяв тот же пример, что и выше, мы можем подсчитать общее количество запланированных сообщений с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ() следующим образом:

Поместите курсор в ячейку A11 и введите следующую функцию в строке формул сверху:

=ПРОМЕЖУТОЧНЫЙ ИТОГ(103;A2:A10)

Здесь значение «103» указывает на то, что функция СЧЕТЧИК() упоминалась в функции ПРОМЕЖУТОЧНЫЕИТОГИ(). Кроме того, это указывает на то, что скрытые значения будут исключены, если таковые имеются. Если на вашем рабочем листе есть числовые данные, вы можете обратиться к функции COUNT() (2/102).

Поскольку на данный момент скрытых строк нет, количество получается равным 9. Когда вы вручную скрываете строки с зелеными ячейками, функция ПРОМЕЖУТОЧНЫЕИТОГИ() автоматически показывает обновленные результаты (8, 7 и т. д.).

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

Нажмите на инструмент «Сортировка и фильтр» непосредственно перед инструментом «Найти и выбрать». Выберите параметр «Фильтр» в раскрывающемся списке.

В заголовке каждого столбца появится значок со стрелкой вниз. Нажмите на этот значок и выберите «Фильтр по цвету» > «Желтый».

Это отфильтрует клетки желтого цвета. Результат функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ() также будет обновляться на основе видимых данных.

Примечание. Когда функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() используется для отфильтрованных данных, скрытые значения всегда игнорируются независимо от аргумента номер_функции.

Вот как вы можете считать цветные ячейки в Microsoft Excel. Я надеюсь, что вы найдете это полезным.

Читайте также: Как выделить ячейку или строку с флажком в Excel.

Может ли Excel считать по цветным ячейкам?

В Excel нет встроенного способа подсчета цветных ячеек, но есть хитрость. Вы можете применить цветовой фильтр к значениям ячеек, а затем подсчитать только видимые ячейки, передав функцию СЧЁТ() или СЧЕТЧИК() в качестве аргумента в функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ(). Функцию ПРОМЕЖУТОЧНЫЕИТОГИ() можно использовать для выполнения различных арифметических операций (сумма, подсчет, среднее и т. д.) в заданном диапазоне ячеек.

Можете ли вы использовать Countif с цветами?

Функцию СЧЁТЕСЛИ() нельзя использовать для проверки цвета фона, цвета переднего плана или любого другого форматирования в Excel. Это статистическая функция, которая работает только со значениями. Если вы хотите подсчитать цветные ячейки на листе, вы можете использовать функцию поиска. Нажмите Ctrl+F, чтобы открыть диалоговое окно «Найти и заменить». Нажмите «Формат» > «Выбрать формат из ячейки». Переместите указатель, повернутый к цветной палитре, на цветную ячейку и щелкните мышью. Нажмите на кнопку «Найти все». Счетчик будет показан в нижней части окна «Найти и заменить».

Читать далее . Как создать раскрывающийся список с цветом в Excel и Google Sheets.