Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как посчитать количество затененных ячеек в Excel?

Как посчитать количество цветных ячеек в excel

Добавление заливки ячеек делает документ Excel более удобным в использовании, а анализ данных — более наглядным. Часто возникает необходимость подсчета количества ячеек с заданным цветом заливки.

К сожалению, в Excel нет встроенной функции, использующей цвета заливки в качестве условий в формулах. Альтернативой является использование собственных функций (написанных на VBA). В надстройке VBA-Excel имеется функция READ FILL, которая позволяет это сделать.

Описание функции

Функция =Расчет(ДИАПАЗОН, ПИТАНИЕ) имеет два обязательных аргумента:

  • DYPASS ячеек, где требуется подсчитать ячейки с определенным цветом заливки. Ячейка
  • — это ячейка с цветом заливки, который вы хотите подсчитать.

Если вы забыли синтаксис данной функции, вы всегда можете вызвать формулу из выпадающего списка функций в надстройке.

Вычисление автоматическими средствами. При изменении заливки ячейки Excel не пересчитывает формулы автоматически. Данная функция этого не делает. Используйте комбинацию клавиш Ctrl+Alt+F9, чтобы заставить формулы активного листа пересчитываться.

Сводка

На вкладке формулы Microsoft Excel мы знаем, чтов категории " Дополнительные функции > " существует функция СЧЁТЕСЛИ, которая подсчитывает количество ячеек в диапазоне, соответствующих заданному условию. Условия для этой функции ограничены текстом или числами. Однако с помощью VBA можно создать функцию для подсчета количества ячеек с другими критериями, такими как цвет ячейки.

Дополнительные сведения

С помощью VBA можно создать пользовательскую функцию (UDF) и добавить ее в файл дополнений, что позволяет использовать ее на других компьютерах и в других книгах.

Создание пользовательской функции

Вот шаги для создания функции FROM для подсчета цветов клеток.

Откройте Microsoft Excel, а затем нажмите ALT + F11, чтобы открыть окно редактора Visual Basic (VBE).

Читайте так же:
Как подсчитать по нескольким критериям в Excel?

В меню Вставка выберите модуль, чтобы создать модуль. Затем введите следующую последовательность команд:

Закройте окно VBE, чтобы вернуться к Excel.

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

В ячейке D3 введите функцию

В аргументе "range_data" выберите ячейку C2 для C51.

В аргументе "критерии" выберите элемент F1.

Нажмите клавишу Enter. Результат в ячейке F2 равен 6. В данном случае имеется шесть ячеек с синим оттенком.

Для тестирования можно использовать другие цвета. Замените цвет ячейки F1 на любой цвет в данных, используя опцию Главная > Заливка.

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

Трин 1: арка из драгоценных камней

А. Выберите файл, затем сохраните его как. (При необходимости нажмите кнопку Обзор).

B) Выберите в качестве формата дополнение Excel (.xlam) и дайте файлу имя, например, countcolour.

Его можно сохранить в любом месте. В Excel сохраните его в месте по умолчанию, чтобы он отображался как надстройка. В Windows 7 местоположение по умолчанию для любой версии Microsoft Excel следующее: K:usersraddiniappdataroamingmicrosoftaddins

Шаг 2: Монтаж надстройки

А. Откройте Microsoft Excel на компьютере, на котором вы хотите установить надстройку.

B) Откройте диалоговое окно Add-Ins, выбрав Excel Add-Ins для Excel 2013 и более поздних версий на вкладке Developer . (Надстройки в Excel 2010.)

В. В диалоговом окне Add-In нажмите кнопку Browse (Обзор).

Г. Перейдите к месту, где сохранен файл расширения (например, на USB-накопителе или в облачной папке). Выберите файл и нажмите кнопку Открыть.

Д. Убедитесь, что в диалоговом окне Add-On не установлен флажок Add-On. Затем нажмите кнопку OK.

Читайте так же:
Как посмотреть поиск в другой книге?

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

Заявление об отказе от контента решений сообщества

КОРПОРАЦИЯ MICROSOFT И/ИЛИ ЕЕ ПОСТАВЩИКИ НЕ ДЕЛАЮТ НИКАКИХ ЗАЯВЛЕНИЙ ОТНОСИТЕЛЬНО ПРИГОДНОСТИ, НАДЕЖНОСТИ ИЛИ ТОЧНОСТИ ИНФОРМАЦИИ И СВЯЗАННОЙ С НЕЙ ГРАФИКИ. ВСЯ ТАКАЯ ИНФОРМАЦИЯ И СВЯЗАННАЯ С НЕЙ ГРАФИКА ПРЕДОСТАВЛЯЮТСЯ БЕЗ КАКИХ-ЛИБО ГАРАНТИЙ. КОРПОРАЦИЯ MICROSOFT И/ИЛИ ЕЕ СООТВЕТСТВУЮЩИЕ ПОСТАВЩИКИ НАСТОЯЩИМ ОТКАЗЫВАЮТСЯ ОТ ВСЕХ ГАРАНТИЙ И УСЛОВИЙ В ОТНОШЕНИИ ДАННОЙ ИНФОРМАЦИИ И СВЯЗАННОЙ С НЕЙ ГРАФИКИ, ВКЛЮЧАЯ ВСЕ ПОДРАЗУМЕВАЕМЫЕ ГАРАНТИИ И УСЛОВИЯ ТОВАРНОГО СОСТОЯНИЯ, ПРИГОДНОСТИ ДЛЯ КОНКРЕТНОЙ ЦЕЛИ, ПРАВА СОБСТВЕННОСТИ И НЕНАРУШЕНИЯ ПРАВ. ВЫ ОДНОЗНАЧНО СОГЛАШАЕТЕСЬ, ЧТО MICROSOFT И/ИЛИ ЕЕ ПОСТАВЩИКИ НЕ НЕСУТ ОТВЕТСТВЕННОСТИ ЗА ЛЮБЫЕ ПРЯМЫЕ, КОСВЕННЫЕ, ШТРАФНЫЕ, СЛУЧАЙНЫЕ, ПОБОЧНЫЕ, СПЕЦИАЛЬНЫЕ ИЛИ ОБРАЗЦОВЫЕ УБЫТКИ, ВКЛЮЧАЯ, НО НЕ ОГРАНИЧИВАЯСЬ, УБЫТКИ ОТ ПОТЕРИ ИСПОЛЬЗОВАНИЯ, ДАННЫХ ИЛИ ПРИБЫЛИ, ВОЗНИКАЮЩИЕ В СВЯЗИ С ИСПОЛЬЗОВАНИЕМ ИЛИ НЕВОЗМОЖНОСТЬЮ ИСПОЛЬЗОВАНИЯ ИНФОРМАЦИИ И СВЯЗАННОЙ С НЕЙ ГРАФИКИ, СОДЕРЖАЩЕЙСЯ В НАСТОЯЩЕМ ДОКУМЕНТЕ, БУДЬ ТО ПО КОНТРАКТУ, ДЕЛИКТУ, НЕБРЕЖНОСТИ, ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТИ ИЛИ ИНЫМ ОБРАЗОМ, ДАЖЕ ЕСЛИ MICROSOFT ИЛИ ЕЕ ПОСТАВЩИКИ РЕКОМЕНДОВАЛИ ИСПОЛЬЗОВАНИЕ ИНФОРМАЦИИ И СВЯЗАННОЙ С НЕЙ ГРАФИКИ ДЛЯ ЛЮБЫХ ЦЕЛЕЙ.

Мы уже рассматривали процесс подсчета количества ячеек/значений в Excel в конкретном видеоуроке. Сегодня мы хотели бы немного расширить эту статью и сосредоточиться на более узкой теме. Рассмотрим случай, когда вы хотите определить количество ячеек на основе цвета ячеек или текста.

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

Читайте так же:
Как подсчитать / суммировать нечетные / четные числа или строки в Excel?

Для этих целей необходимо использовать очень простой макрос, а точнее пользовательскую функцию, назовем ее ColorNom, она позволит нам извлечь числовой код цвета заливки, а затем по этому коду мы будем считать общее количество ячеек, используя приемы, описанные в статье как посчитать количество ячеек/значений в Excel

Итак, давайте приступим. Это можно сделать в редакторе Visual Basic:

в Excel 2003 нажмите на Сервис, далее Макрос и затем Редактор Visual Basic.

2007 и 2010 делают это по-разному; 2013 делает это по-другому. Перейдите в раздел «Разработчик», затем выберите Visual Basic

Предупреждение. По умолчанию в Excel 2007 есть панель инструментов разработчика, но ее необходимо включить в Excel 2010 и 2013. Это особенно полезно для пользователей, которые будут часто работать с макросами. Чтобы включить панель инструментов разработчика в Excel 2010 или 2013, необходимо выполнить команду Файл | Параметры | Настройка ленты, а затем установить флажок Разработчик в правой части окна

После того как откроется редактор Visual Basic, вставьте пустой модуль, выбрав меню Вставка, затем Модуль


и скопируйте туда текст простой функции:

Public Function ColorNom (Cell As Range)
ColorNom = Cell.Interior.ColorIndex
End Function

Затем закройте редактор VisualBasic и можете вернуться к нашему файлу. В любую пустую ячейку введите функцию, определяемую пользователем, которую мы ввели ранее. В нашем случае это функция ColorNom, вы можете вызвать ее из меню Insert, Function — User Defined, или ввести ее в самой ячейке =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

В приведенном выше примере у нас есть список фруктов. Мы определили код ячейки и отобразили его напротив каждой ячейки.

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

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

Вычислим количество с помощью функции CALCULATE.

Ниже приведены аргументы для данной функции

=СЧЁТЕСЛИ( диапазон ; критерий )

=СЧЁТЕСЛИ( $B$1:$B$8 ; E2 )

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

голоса
Рейтинг статьи
Читайте так же:
Как переместить несколько ячеек в одну в Excel?
Ссылка на основную публикацию
Adblock
detector