Подсчет цветных ячеек в Excel (Содержание)
- Введение в подсчет цветных ячеек в Excel
- Как считать цветные клетки в Excel?
Введение в подсчет цветных ячеек в Excel
Нам всем хорошо известен тот факт, что функции COUNT и COUNTIF - это те, которые могут использоваться для подсчета ячеек с номерами и ячеек, которые соответствуют любым определенным критериям. Разве не модно иметь функцию, которая считает цветные клетки? Я имею в виду что-то, что считает разные клетки в зависимости от их цвета? К сожалению, в Excel нет такой функции, доступной напрямую. Тем не менее, мы можем использовать комбинацию различных функций для достижения того же. Благодаря универсальности Microsoft Excel. Поэтому вот методы, с помощью которых мы можем подсчитать количество цветных ячеек в Excel.
- Использование СУБТОТАЛЬНОЙ формулы и цветовых фильтров.
- Использование COUNTIF и GET.CELL для подсчета цветных ячеек.
Как считать цветные клетки в Excel?
Давайте разберемся, как считать цветные ячейки в Excel, на нескольких примерах.
Вы можете скачать этот шаблон Excel Count Count Cells здесь - Шаблон Excel Count Colored Cells ExcelПример № 1 - Использование СУБТОТАЛЬНОЙ формулы и цветовых фильтров
Предположим, у нас есть данные о 1000 цветных строках, как показано ниже, и все, что нам нужно, - это определить количество каждого цвета во всех данных. Смотрите частичное расположение данных, приведенных ниже:
Шаг 1: Перейдите в ячейку F1002, где мы можем использовать формулу SUBTOTAL для захвата промежуточного итога.
Шаг 2: Начните вводить формулу для SUBTOTAL внутри ячейки F1002. Эта формула имеет несколько опций функции. Вы можете увидеть список, как только формула активна.
Шаг 3: Из списка необходимо выбрать функцию номер 102, которая связана с функцией COUNT. Используйте мышь, чтобы перетащить к функции 102, и дважды щелкните по ней, чтобы выбрать.
Шаг 4: Используйте ячейки с F1 по F10001 в качестве эталонного массива, внутри которого будет использоваться функция COUNT для подсчета ячеек. Исправьте ссылку на ячейку со знаком доллара, как показано на скриншоте ниже.
Шаг 5: Закройте скобки, чтобы завершить формулу, и нажмите клавишу Enter, чтобы увидеть результат. Вы можете видеть 1000 как число в ячейке F1002.
Шаг 6: Теперь примените фильтр ко всем данным и используйте опцию Filter by Cell Color, чтобы отфильтровать данные по цветам ячеек. Как только вы отфильтруете определенный цвет, значение ячейки F1002 изменится на количество ячеек с этим конкретным цветом. Смотрите скриншоты ниже.
Вы можете видеть, что есть 419 ячеек, которые имеют тот же цвет, что и фильтрованный, т.е. светло-оранжевый. Вы также можете проверить количество других цветов.
Это прекрасно работает из-за использования функции COUNT (102) в SUBTOTAL в качестве аргумента. Функция COUNT подсчитывает все непустые ячейки в заданном диапазоне, а промежуточный итог дает общее количество подсчетов. Если мы применяем фильтр с какими-либо конкретными критериями, из-за характера SUBTOTAL мы получаем только общее количество, связанное с этими критериями (в данном случае цвет).
Пример № 2 - Использование COUNTIF и GET.CELL
Мы также можем использовать функции COUNTIF и GET.CELL в комбинации, чтобы получить количество цветных ячеек. GET.CELL - это старая функция макроса, которая не работает с большинством функций. Однако он совместим с именованными диапазонами. Эта функция может извлечь дополнительную часть информации, которую обычная функция CELL не смогла извлечь. Мы используем эту функцию, чтобы получить информацию о цвете ячейки. Следуйте инструкциям, чтобы узнать, как мы можем подсчитать ячейки с цветом, используя эти две функции.
Шаг 1. Создайте новый именованный диапазон ColorCode, используя параметр именованных диапазонов на вкладке Формулы.
Перейдите на вкладку « Формулы »> в разделе « Определенные имена », нажмите « Определить имя», чтобы создать именованный диапазон.
Шаг 2: появится всплывающее окно с « новым именем ». Упомяните имя как ColorCode и в разделе « Относится к: » используйте формулу как = GET.CELL (38, «Пример # 2»! $ A2) . Нажмите ОК после внесения изменений. Ваш названный диапазон будет завершен и готов к использованию.
38 здесь номер операции, связанный с GET.CELL. Это связано с операцией, которая захватывает уникальные номера цветов в Excel.
Переименуйте столбец G в ColorCode, где мы будем захватывать числовые коды, связанные с каждым цветом. Эти коды уникальны для каждого цвета.
Шаг 3: В ячейке G2 начните вводить = ColorCode . Это именованный диапазон, который мы только что создали на предыдущем шаге. Нажмите клавишу Enter, чтобы получить уникальный числовой код, связанный с цветом ячейки A2 (помните, что при определении именованного диапазона мы использовали A2 как ссылку на лист).
40 - код, связанный с розовым цветом в Excel.
Шаг 4. Перетащите формулу в ячейку G3: G1001, и вы увидите разные числа, которые представляют собой не что иное, как цветовые коды, связанные с каждым цветом. Смотрите частичный скриншот ниже.
Шаг 5: Теперь нам нужно посчитать, сколько стоит каждый цвет на листе. Для этого мы будем использовать функцию COUNTIF. Начните вводить = COUNTIF в ячейке B1003 данного листа Excel.
Обратите внимание, что мы закрасили ячейку 1003: 1006 цветами, которые мы использовали соответственно в нашем примере.
Шаг 6: Используйте столбец G в качестве аргумента диапазона для этой функции и ColorCode назвал диапазон в качестве критерия. Завершите формулу, закрыв скобки, и просмотрите результат в ячейке B1003.
После использования формулы вывод показан ниже.
Перетащите эту формулу через оставшиеся ячейки и посмотрите количество оставшихся цветов.
В этой статье мы увидели различные методы, которые можно использовать для определения количества цветных клеток. Давайте завернем вещи с некоторыми пунктами, которые будут помнить.
Что нужно помнить о подсчете цветных ячеек в Excel
- У нас нет встроенной функции, которая может подсчитывать цветные ячейки в листе Excel. Однако мы можем использовать другую комбинацию формул, чтобы получить одно и то же.
- Функция CELL, которую мы используем во втором примере, дает больше информации, связанной с ячейкой, которую не может предоставить общая функция CELL.
- Числовой код 102, который мы используем в функции SUBTOTAL, представляет собой числовой код функции COUNT под ним.
Рекомендуемые статьи
Это руководство по подсчету цветных ячеек в Excel. Здесь мы обсуждаем, как использовать Count Colored Cells в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
- Модель данных в Excel
- Защитить лист в Excel
- Подсчет ячеек с текстом в Excel
- Excel Показать формулу