Меню

Как подсчитать ячейки выделенные цветом



Количество и сумма ячеек по цвету в Excel

Разберем простые способы как посчитать количество, и как суммировать ячейки по цвету в Excel.

Приветствую всех, дорогие читатели блога TutorExcel.Ru.

Мы часто при работе в Excel окрашиваем ячейки различными цветами для лучшей визуализации данных.

Однако, когда возникает необходимость произвести какие-либо расчеты с обработанными данными мы сталкиваемся с трудностями в связи с малыми возможностями стандартных средств Excel.

Если сделать сортировку и фильтр ячеек по цвету мы стандартными способами Excel можем, то вот посчитать сумму или количество пока еще нет.

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

Суммирование ячеек по цвету

Для начала перейдем в редактор VBA, для этого в панели вкладок выбираем Разработчик -> Visual Basic (или нажимаем комбинацию клавиш Alt + F11).

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

Функция СУММЦВЕТ содержит два аргумента:

  • MyRange(обязательный аргумент) — диапазон ячеек для суммирования;
  • MyCell(обязательный аргумент) — ячейка, по цвету заливки которой рассчитывается сумма.

Функция СУММЦВЕТ теперь будет определяться при вводе формулы в ячейку, переходим из редактора на лист Excel и, чтобы просуммировать ячейки воспользуемся новой функцией:

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

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

Подсчет количества ячеек по цвету

Чтобы посчитать ячейки одного цвета достаточно немного видоизменить функцию для подсчета суммы — вместо прибавления значения текущей ячейки (Sum = Sum + cell.Value) мы добавляем 1 (Sum = Sum + 1).

Замечания

При работе с данными функциями обратите внимание на два важных момента:

  • Если цвет выбранной ячейки определяется с помощью условного форматирования (т.е. цвет ячейки определяется не за счет заливки), то рассмотренные функции для суммирования и подсчета ячеек не сработают.
  • В случае изменения раскраски ячейки в Excel формулы автоматически не пересчитываются, так как не изменяется содержимое ячейки, поэтому для корректного расчета необходимо произвести пересчет формул. Комбинация клавиш Shift + F9 пересчитает формулы на активном листе (F9 — для всей книги).

Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — пишите в комментариях.

Источник

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

Сводка

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

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

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

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

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

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

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

Читайте также:  Anastasia beverly hills помада для бровей как выбрать цвет

Закройте окно 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 установлен и готов к использованию. Вы сможете получить доступ к этой функции в любое время, поместив курсор в любую ячейку листа и введя текст:

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

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

Источник

Как в Excel посчитать количество ячеек по цвету ячейки или цвету текста

Мы с вами уже рассматривали вопрос о том как посчитать в 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

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

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

Читайте также:  Куртка адидас зеленого цвета 1

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

Считать количество мы будем с помощью функции СЧЁТЕСЛИ

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

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

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

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

Скачать пример файла: Цвет_Ячеек.xlsm (файл с поддержкой макросов)

Источник

Сумма ячеек по цвету

Помечать ячейки цветом, используя заливку или цвет шрифта, очень удобно и наглядно. Если вы не дальтоник, конечно 🙂 Трудности возникают тогда, когда по такой раскрашенной таблице возникает необходимость сделать отчет. И если фильтровать и сортировать по цвету Excel в последних версиях научился, то суммировать по цвету до сих пор не умеет.

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

Откройте редактор Visual Basic:

  • В Excel 2003 и старше для этого нужно выбрать в меню Сервис — Макрос — Редактор Visual Basic (Tools — Macro — Visual Basic Editor)
  • В новых версиях Excel 2007-2013 перейти на вкладку Разработчик (Developer) и нажать кнопку Visual Basic. Если такой вкладки у вас не видно, то включите ее в настройках Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon)

В окне редактора вставьте новый модуль через меню Insert — Module и скопируйте туда текст вот такой функции:

Если теперь вернуться в Excel, то в Мастере функций (Вставка — Функция) в появившейся там категории Определенные пользователем (User Defined) можно найти нашу функцию и вставить ее на лист:

У нее два аргумента:

  • DataRange — диапазон раскрашенных ячеек с числами
  • ColorSample — ячейка, цвет которой принимается как образец для суммирования

Цвет шрифта

Легко изменить нашу функцию, чтобы она учитывала не цвет заливки фона, а цвет шрифта ячейки. Для этого в строке 6 просто замените свойство Interior на Font в обеих частях выражения.

Количество вместо суммы

Если вам нужно подсчитывать не сумму покрашенных определенным цветом ячеек, а всего лишь их количество, то наша функция будет еще проще. Замените в ней 7-ю строку на:

Нюансы пересчета

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

Полностью решить эту проблему невозможно, но можно ее существенно облегчить. Для этого в третьей строке нашей функции используется команда Application.Volatile True. Она заставляет Excel пересчитывать результаты нашей функции при изменении любой ячейки на листе (или по нажатию F9).

И помните о том, что наша функция перебирает все (и пустые тоже) ячейки в диапазоне DataRange и не задавайте в качестве первого аргумента целый столбец — «думать» будет долго 🙂

Источник