Содержание
Мы с вами уже рассматривали вопрос о том как посчитать в Excel количество ячеек/значений в подробном видео уроке. Сегодня мы бы хотели немного расширить данную статью для решения более узкой задачи. Допустим, вам необходимо посчитать количество ячеек в зависимости от цвета ячеек или цвета текста.
Начиная с Excel 2007 в программе встроили возможность сортировки ячеек по цвету. Таким образом, можно отфильтровать нужный нам цвет, выделить оставшиеся на виду ячейки и визуально посмотреть общее количество ячеек. Но что делать, если нам требуется делать это часто и при этом нам необходимо, чтобы все считалось и пересчитывалось с помощью формул.
Для этих целей необходимо использовать очень простенький макрос, а точнее пользовательскую функцию, назовем ее ColorNom, она позволит нам вытягивать числовой код цвета заливки и далее по этому коду мы и будет считать общее количество ячеек, используя приемы, описываемые в статье как посчитать в Excel количество ячеек/значений
Итак, приступим. Зайдите в редактор Visual Basic, для этого:
в Excel 2003 нажмите на Сервис, далее Макрос и затем Редактор Visual Basic.
в Excel 2007, 2010 и 2013 это делается по-другому. Зайдите в раздел Разработчик, далее выберите Visual Basic
Внимание! Раздел панели инструментов Разработчик в Excel 2007 доступен по умолчанию, а в Excel 2010 и 2013 его необходимо включить. Это особенно полезно сделать тем пользователям, которые будут часто работать с макросами. Чтобы включить панель инструментов Разработчик в Excel 2010 или 2013 необходимо запустить Файл | Параметры | Настройка ленты после этого необходимо с правой стороны необходимо поставить галочку напротив надписи Разработчик
После того как откроется редактор Visual Basic, вставьте пустой модуль, для этого выберите меню Insert и далее Module
и скопируйте туда текст простой функции:
Public Function ColorNom (Cell As Range)
ColorNom = Cell.Interior.ColorIndex
End Function
После этого закройте редактор Visual Basic и можно вернуться к нашему файлу. В любой пустой ячейки введите пользовательскую функцию, которую мы ввели раннее. В нашем случае это функция ColorNom, ее можно вызвать либо через меню Вставка, Функция — категория Определенные пользователем, либо просто можно напечатать ее в самой ячейке =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
Рассмотрим вышеуказанный пример с перечнем фруктов. Мы определили код ячеек и отобразили его напротив каждой ячейки.
Далее для удобства мы создадим вспомогательную таблицу из всех существующих цветов заливки. В нашем случае это красный, зеленый и желтый. Рядом с помощью все той же формулы определим код цвета.
В третьем столбце мы уже будет считать количество ячеек определенного цвета по условию, использую код цвета.
Считать количество мы будем с помощью функции СЧЁТЕСЛИ
Вот так выглядят аргументы данной функции
=СЧЁТЕСЛИ( диапазон ; критерий )
=СЧЁТЕСЛИ( $B$1:$B$8 ; E2 )
Диапазон мы указали со знаком доллара, чтобы он был закреплен и можно было протянуть формулу. Критерия у нас встречается всего три и они указаны в нашей вспомогательной таблице. Протянем формулу и получим количество ячеек по цветам.
Скачать пример файла: Цвет_Ячеек.xlsm (файл с поддержкой макросов)
Описание проблемы
Добавление заливки ячеек делает ваш документ Excel более понятным, добавляя наглядности для анализа данных. Часто возникает потребность посчитать количество ячеек с заданным цветом заливки.
К сожалению, Excel не имеет встроенной функции, которая использует цвета заливки в качестве условий в формулах. Альтернативой является использование собственных функций (написанный на VBA). Надстройка VBA-Excel содержит функцию СЧЁТЗАЛИВКА, с помощью которой можно это реализовать.
Описание функции
Функция =СЧЁТЗАЛИВКА(ДИАПАЗОН, ЯЧЕЙКА) имеет два обязательных аргумента:
- ДИАПАЗОН ячеек, где необходимо произвести подсчет ячеек с определенной заливкой.
- ЯЧЕЙКА ячейка с цветом заливки которые необходимо посчитать.
Если вы забыли синтаксис данной функции, вы всегда сможете вызвать формулу из выпадающего списка функций в надстройке.
Замечание об автоматическом вычислении. Так как пересчет формул в Excel автоматически не происходит при изменении заливки ячеек, то данная функция не пересчитывается автоматически. Для того, чтобы принудительно пересчитать формулы активного листа используйте сочетание клавиш Ctrl+Alt+F9.
Сводка
На вкладке формулы Microsoft Excel мы знаем, чтов категории " Дополнительные функции > " существует функция СЧЁТЕСЛИ, которая подсчитывает количество ячеек в диапазоне, соответствующих заданному условию. Условия для этой функции ограничены текстом или числами. Однако с помощью VBA можно создать функцию для подсчета количества ячеек с другими критериями, такими как цвет ячейки.
Дополнительные сведения
С помощью VBA пользовательская функция (UDF) может быть создана и сохранена в файле надстройки, чтобы ее можно было использовать в других книгах и переносить на другие компьютеры.
Создание пользовательской функции
Ниже приведены действия по созданию функции UDF для подсчета цвета ячеек.
Откройте Microsoft Excel, а затем нажмите клавиши ALT + F11, чтобы открыть окно редактора Visual Basic (VBE).
В меню Вставка выберите модуль , чтобы создать модуль. Затем введите следующий скрипт:
Закройте окно VBE, чтобы вернуться в Excel.
Чтобы протестировать UDF, создайте примеры данных, которые содержат столбец ячеек, в различных цветах.
В ячейке D3 Запишите функцию:
В аргументе «range_data» выберите ячейку C2 для C51.
В аргументе «критерии» выберите элемент F1.
Нажмите клавишу ВВОД. Результат в ячейке F2 — 6. Это означает, что число ячеек, затененных синим цветом, равно 6.
Для тестирования можно использовать другие цвета. Замените цвет в ячейке F1 на любой цвет из данных, используя домашнюю > заливку.
Кроме того, можно заархивировать UDF, чтобы функция могла использоваться в другой книге или на другом компьютере. Для этого выполните указанные ниже действия.
Шаг 1: сохранение книги
а. Выберите файл, а затем Сохранить как. (При необходимости нажмите кнопку Обзор ).
б) Выберите надстройку Excel (. xlam) в виде формата и присвойте файлу имя, например каунткколор.
Вы можете сохранить файл надстройки в любом месте. Но для того чтобы она была указана в качестве надстройки в программе Excel, сохраните ее в расположении по умолчанию. В Windows 7 расположением по умолчанию для любой версии Microsoft Excel является: К:усерсраддиниаппдатароамингмикрософтаддинс
Шаг 2: Установка надстройки
а. Откройте Microsoft Excel на компьютере, на котором необходимо установить надстройку.
б) Откройте диалоговое окно надстройки, выбрав надстройки Excel для Excel 2013 и более поздних версий на вкладке разработчик . (надстройки в Excel 2010.)
В. В диалоговом окне надстройки нажмите кнопку Обзор.
Г. Перейдите к расположению файла, в котором сохраняется файл надстройки (например, на USB-диске или в облачной папке). Выберите файл и нажмите кнопку Открыть.
Д. В диалоговом окне надстройки убедитесь, что флажок Надстройка снят. Затем нажмите кнопку ОК.
Набор UDF Color Cell Color установлен и готов к использованию. Вы сможете получить доступ к этой функции в любое время, поместив курсор в любую ячейку листа и введя текст:
Заявление об отказе от контента решений сообщества
КОРПОРАЦИЯ МАЙКРОСОФТ И/ИЛИ ЕЕ ПОСТАВЩИКИ НЕ СОДЕРЖАТ НИКАКИХ ПРЕДСТАВЛЕНИЙ О ПРИГОДНОСТИ, НАДЕЖНОСТИ ИЛИ ТОЧНОСТИ ИНФОРМАЦИИ И СВЯЗАННОЙ С НЕЙ ГРАФИКИ. ВСЯ ТАКАЯ ИНФОРМАЦИЯ И СВЯЗАННАЯ С НЕЙ ГРАФИКА ПРЕДОСТАВЛЯЮТСЯ БЕЗ КАКИХ-ЛИБО ГАРАНТИЙ. КОРПОРАЦИЯ МАЙКРОСОФТ И/ИЛИ ЕЕ СООТВЕТСТВУЮЩИЕ ПОСТАВЩИКИ НАСТОЯЩИМ СНИМАТЬИ ВСЕ ГАРАНТИИ И УСЛОВИЯ, ОТНОСЯЩИЕСЯ К ЭТОЙ ИНФОРМАЦИИ И СВЯЗАННЫМ С НИМИ ГРАФИКЕ, ВКЛЮЧАЯ ВСЕ ПОДРАЗУМЕВАЕМЫЕ ГАРАНТИИ И УСЛОВИЯ ДЛЯ ОБЕСПЕЧЕНИЯ ПРИГОДНОСТИ ДЛЯ ПРОДАВЦА, ПРИГОДНОСТИ ДЛЯ КОНКРЕТНОЙ ЦЕЛИ, ВОРКМАНЛИКЕ ТРУДОЗАТРАТЫ, НАЗВАНИЯ И НЕНАРУШЕНИЯ ПРАВ. ВЫ ЯВНО СОГЛАСНЫ, ЧТО В ПРОТИВНОМ СЛУЧАЕ КОРПОРАЦИЯ МАЙКРОСОФТ И/ИЛИ ЕЕ ПОСТАВЩИКИ НЕ НЕСУТ ОТВЕТСТВЕННОСТИ ЗА ЛЮБЫЕ ПРЯМЫЕ, КОСВЕННЫЕ, ПУНИТИВЕ, НЕПРЯМЫЕ, КОСВЕННЫЕ И НЕСУЩЕСТВЕННЫЕ УБЫТКИ, В ТОМ ЧИСЛЕ БЕЗ ОГРАНИЧЕНИЙ, УЩЕРБА ДЛЯ ПОТЕРИ ИСПОЛЬЗОВАНИЯ, ДАННЫХ ИЛИ ПРИБЫЛИ, КОТОРЫЕ СВЯЗАНЫ С ИСПОЛЬЗОВАНИЕМ ИЛИ НЕ МОГУТ ИСПОЛЬЗОВАТЬ ИНФОРМАЦИЮ И СВЯЗАННУЮ С НИМ ГРАФИКУ, СОДЕРЖАЩИЕСЯ В ЭТОМ ДОКУМЕНТЕ, НА ОСНОВАНИИ ДОГОВОРА, НАРУШЕНИЯМ, НЕБРЕЖНОСТЬЮ, ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТИ ИЛИ ИНЫМ СПОСОБОМ, ДАЖЕ ЕСЛИ КОРПОРАЦИЯ МАЙКРОСОФТ ИЛИ ЕЕ ПОСТАВЩИКИ РЕКОМЕНДУЕМ ВОЗМОЖНОСТЬ УЩЕРБА.
