Подсчет количества ячеек с определенным цветом ячеек с помощью VBA

Аннотация

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

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

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

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

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

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

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

   Function CountCcolor(range_data As range, criteria As range) As Long     Dim datax As range     Dim xcolor As Long xcolor = criteria.Interior.ColorIndex For Each datax In range_data     If datax.Interior.ColorIndex = xcolor Then         CountCcolor = CountCcolor + 1     End If Next datax End Function
   

   

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

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

5. В ячейке D3 напишите функцию:

=CountCcolor(range_data,criteria)

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

• В аргументе criteria выберите ячейку F1.

  

6. Нажмите клавишу ВВОД. Результат в ячейке F2 — 6. Это означает, что число ячеек, затененного синим цветом, равно 6.

   

7. Тестирование можно выполнять с помощью других цветов. Измените цвет ячейки F1 на любой цвет из данных с помощью цвета домашней>заливки.

   

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

   Шаг 1. Сохранение книги

   А. Выберите "Файл" и нажмите кнопку "Сохранить как". (При необходимости нажмите кнопку " Обзор".)

   Б. Выберите формат Add-In Excel (XLAM) и присвойте файлу имя, например CountCcolor.

   Примечание.

   Вы можете сохранить Add-In в любом месте. Но чтобы он был указан в качестве Add-In в программе Excel, сохраните его в расположении по умолчанию. В Windows 7 расположение по умолчанию для любой версии Microsoft Excel: C:\Users\RADDINI\AppData\Roaming\Microsoft\AddIns

   Шаг 2. Установка надстройки

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

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

   c. В диалоговом окне "Надстройки " нажмите кнопку " Обзор".

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

   e. В диалоговом Add-Ins убедитесь, что флажок надстройки снят. После этого нажмите кнопку ОК.

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

=CountCcolor(range_data,criteria)

Отказ от ответственности за содержимое общедоступных решений

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