6 способов подсчета цветных ячеек в Excel

2 апреля 2022 г.

Цвета заливки могут помочь организовать и представить информацию в Excel. Если вы хотите преобразовать цветовое кодирование в количественные данные, существует множество методов, которые вы можете использовать для подсчета цветных клеток. Изучение того, как работает каждый из этих методов, поможет вам выбрать тот, который лучше всего соответствует вашим потребностям. В этой статье мы объясняем некоторые преимущества подсчета цветных ячеек и описываем шесть методов подсчета цветных ячеек в Excel.

Преимущества подсчета цветных ячеек в Excel

Вы можете использовать цвета заливки в электронной таблице, чтобы классифицировать информацию, добавить визуальный интерес или улучшить читаемость. Цветовое кодирование также может быть быстрым решением, когда вам нужно отразить изменение статуса. Например, у координатора отдела кадров может быть список новых сотрудников в электронной таблице. Когда человек завершает обучение, он может изменить цвет заливки своего имени с красного на зеленый. Если им нужно получить точное количество сотрудников, которым еще предстоит пройти обучение, они могут захотеть быстро подсчитать эритроциты, не добавляя их вручную.

Вот некоторые преимущества подсчета цветных ячеек в Excel:

• Сравнение: подсчет цветных ячеек может помочь вам сравнить категории данных.

• Отчетность: если вам нужно сообщить о показателях кому-то еще, подсчет цветных ячеек может упростить ваши данные.

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

• Специфика: Вы можете визуально оценить количество, но в некоторых ситуациях вам могут понадобиться точные числовые значения.

Как посчитать цветные ячейки в Excel с помощью «Найти и выделить»

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

1. Найдите «Найти и выбрать».

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

2. Откройте меню «Найти и заменить».

Выберите опцию «Найти». Обычно это вызывает всплывающее меню под названием «Найти и заменить». Вы также можете открыть это меню с помощью следующего сочетания клавиш:

Ctrl + Ф

3. Укажите цвет, который вы хотите посчитать

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

4. Предложите Excel подсчитать ячейки

Как только поле «Предварительный просмотр» удовлетворит ваши потребности, вы можете нажать «Найти все» в левом нижнем углу меню. Затем Excel перечисляет все ячейки с выбранным форматированием в белом поле внизу. В левом нижнем углу под списком отображается количество найденных ячеек. Это окончательный подсчет ваших цветных ячеек.

Как подсчитать цветные ячейки в Excel с помощью функции промежуточного итога

Вот как вы можете подсчитать цветные ячейки, используя функцию промежуточного итога:

1. Добавьте фильтры к своим данным

Выберите все данные в электронной таблице, чтобы Excel мог найти цветные ячейки. Затем откройте вкладку «Данные» на панели инструментов и найдите кнопку «Фильтр». Нажмите эту кнопку, чтобы добавить вкладки фильтров в каждый из ваших столбцов, которые отображаются в виде серых интерактивных стрелок в правом верхнем углу.

2. Добавьте функцию промежуточного итога

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

=ПРОМЕЖУТОЧНЫЙ ИТОГ([NUMBER OF VISIBLE CELLS],[CELL STARTING RANGE-CELL ENDING RANGE])

3. Отфильтруйте столбец по цвету

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

Как посчитать цветные ячейки в Excel с помощью таблицы

Вот как вы можете использовать таблицу для подсчета цветных ячеек:

1. Изолируйте цветные клетки

Чтобы использовать этот метод, полезно заранее изолировать данные. Это экономит ваше время, поскольку Excel может автоматически суммировать данные. Вы можете использовать фильтры, чтобы удалить все ячейки, кроме отслеживаемого цвета. Если вы хотите сохранить полный набор данных, вы можете выбрать все свои ячейки и скопировать их на новый лист.

2. Создайте таблицу

Выделите все соответствующие данные. Вы можете нажать «Ctrl» и «T» на клавиатуре, чтобы превратить ваши данные в таблицу. Если у вас есть заголовки в столбцах, установите флажок «В моей таблице есть заголовки», затем нажмите «ОК».

3. Добавьте строку «Итого».

Перейдите на вкладку «Конструктор таблиц» на ленте. Вы можете найти эту вкладку в правой части экрана. Справа от центра вы можете увидеть флажок «Строка итогов». Щелкните это поле, чтобы добавить эту строку в конец таблицы.

4. Попросите Excel подсчитать ячейки

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

Как посчитать цветные ячейки в Excel с помощью функции GET.CELLS Macro4

Вот как вы можете использовать функцию GET.CELLS Macro4 для подсчета цветных ячеек:

1. Создайте именованный диапазон

Функция GET.CELLS работает только с именованным диапазоном в Excel, поэтому очень важно добавить его в электронную таблицу. Откройте вкладку «Формулы» и нажмите «Определить имя». Это предложит Excel открыть диалоговое окно под названием «Редактировать имя», где вы можете ввести сведения о своем диапазоне. Вы можете назвать свой диапазон «GetColor» и оставить для «Scope» значение «Workbook». В поле «Относится к» вы можете ввести следующую формулу:

= ПОЛУЧИТЬ.ЯЧЕЙКА(38,[SHEET NUMBER]$[REFERENCE CELL WITH TARGETED BACKGROUND COLOR]

2. Примените функцию СЧЁТЕСЛИ

После применения формулы может появиться столбец, в котором каждому цвету присваивается постоянное значение. Например, если ваши ячейки белые или красные, во всех белых ячейках может отображаться 0, а во всех красных ячейках — 40. Выберите пустую ячейку под вашим набором данных в справочном столбце и добавьте цвет фона в столбец, который вы используется для вашей эталонной ячейки. В следующую ячейку введите следующую формулу:

=СЧЁТЕСЛИ($[COLUMN]$[ROW]:$[COLUMN]$[ROW]ПОЛУЧИТЬЦВЕТ)

Замените скобки столбца и строки начальной и конечной яростью в столбце именованного диапазона. Например, если ваш именованный диапазон находится в столбце E и проходит от строки 2 до строки 22, ваша формула будет выглядеть так:

=СЧЁТЕСЛИ($F$2:$F$22, ПОЛУЧИТЬЦВЕТ)

3. Просмотрите данные

Формула СЧЁТЕСЛИ подсчитывает количество ячеек в именованном диапазоне, соответствующем цвету фона. Просмотрите номер дает вам. Повторите этот процесс для любых других цветов, которые вы хотите посчитать.

Как подсчитать цветные ячейки в Excel с помощью VBA

Вот как вы можете подсчитать цветные ячейки в Excel, создав определяемую пользователем функцию (UDF) с помощью Visual Basic для приложений (VBA):

1. Создайте модуль

Используйте сочетание клавиш «Alt + F11», чтобы открыть окно редактора Visual Basic (VBE) в Excel. Перейдите в меню «Вставка» и выберите «Модуль». В поле введите следующий скрипт:

Функция CountColor (range_data как диапазон, критерии как диапазон) As Long

2. Протестируйте пользовательскую функцию

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

=CountCcolor(range_data,критерий)

Убедитесь, что Excel правильно считает цветные ячейки.

3. Сохраните пользовательскую функцию

Если вы хотите снова использовать эту функцию в других книгах, вы можете сохранить ее. Сохраните книгу и выберите «Надстройка Excel (.xlam)». Затем вы можете снова открыть Excel, перейти на вкладку «Разработчик» и нажать «Установить надстройки». Найдите сохраненную надстройку и нажмите «Открыть». Теперь вы можете использовать UDF в любом файле Excel.

Как посчитать цветные ячейки в Excel с помощью офисных скриптов

Вот как вы можете использовать офисные скрипты для подсчета цветных ячеек в Excel:

1. Просмотрите свой план Excel

Функция сценариев Office доступна только для владельцев плана Enterprise. Если у вас уже есть план Enterprise, вы можете продолжить, открыв Excel онлайн, где есть эта функция. Если у вас другой план Excel, вы можете рассмотреть другие методы.

2. Назовите ячейки

Выберите ячейку и введите «ColorCount» в поле имени. Затем вы можете выбрать пустую ячейку и заполнить ее целевым цветом фона. Назовите эту ячейку «Цвет». Этот шаг превращает эти ячейки в именованные диапазоны, на которые вы можете ссылаться позже в своей формуле.

3. Создайте офисный скрипт

Откройте вкладку «Автоматизация» и выберите «Новая команда». Это предложит Excel открыть окно «Редактор кода». Затем вы можете ввести следующий код:

основная функция (книга: ExcelScript.Workbook) {

пусть selectedSheet = workbook.getWorksheet(“Лист1”);

let myID = selectedSheet.getRange(“Заказы[Order ID]”);

let myIDCount = selectedSheet.getRange(“Заказы[Order ID]”).getCellCount();

пусть myColorCode = selectedSheet.getRange(“Цвет”).getFormat().getFill().getColor();

пусть счетчик = 0;

for (пусть я = 0; я < myIDCount; я ++) {

если (myID.getCell(i, 0).getFormat().getFill().getColor() == myColorCode) {

счетчик = счетчик + 1;

}

}

selectedSheet.getRange(“ColorCount”).setValue(счетчик);

}

Нажмите «Выполнить», чтобы активировать скрипт. В ответ количество окрашенных ячеек появляется в ячейке ColorCount.

Советы по подсчету цветных ячеек в Excel

Вот несколько советов, которые могут помочь вам при подсчете цветных ячеек в Excel:

Используйте последовательный метод

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

Рассмотрите возможность использования таблиц

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

Тестовые формулы

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

Обратите внимание, что ни один из продуктов, упомянутых в этой статье, не связан с компанией Indeed.