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

Способы подсчета количества ячеек в диапазоне с данными

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

1. Щелкните ячейку, в которой должен выводиться результат.
2. На вкладке Формулы щелкните Другие функции, наведите указатель мыши на пункт Статистические и выберите одну из следующих функции:
3. СЧЁТЗ: подсчитывает количество непустых ячеек.
4. СЧЁТ: подсчитывает количество ячеек, содержащих числа.
5. СЧИТАТЬПУСТОТЫ: подсчитывает количество пустых ячеек.
6. СЧЁТЕСЛИ: подсчитывает ячейки, отвечающие заданным условиям.

Совет: Чтобы ввести нескольких условий, используйте вместо этого функцию СЧЁТЕСЛИМН.

1. Щелкните ячейку, в которой должен выводиться результат.
2. На вкладке Формулы щелкните Вставить, наведите указатель мыши на пункт Статистические и выберите одну из следующих функции:
3. СЧЁТЗ: подсчитывает количество непустых ячеек.
4. СЧЁТ: подсчитывает количество ячеек, содержащих числа.
5. СЧИТАТЬПУСТОТЫ: подсчитывает количество пустых ячеек.
6. СЧЁТЕСЛИ: подсчитывает ячейки, отвечающие заданным условиям.

Совет: Чтобы ввести нескольких условий, используйте вместо этого функцию СЧЁТЕСЛИМН.

покупка

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

Быстро подсчитывайте заштрихованные ячейки с помощью функции COUNTSHADES Kutools for Excel

Подсчет затененных ячеек с помощью определения имени и формулы

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

1. Выберите пустую ячейку рядом с диапазоном данных и щелкните правой кнопкой мыши, чтобы выбрать Определить имя контекстное меню формы. Смотрите скриншот:

Внимание: В Excel 2007 контекстное меню не содержит элемента «Определить имя». Но вы можете получить его, нажав Формулы > Определить имя.

2. В открывшемся диалоговом окне укажите имя, которое вам нравится, в Имя текстовое поле и введите эту формулу = GET.CELL (38; Лист1! A1) в поле «См.». Смотрите скриншот:

Функции: В формуле Sheet1 — это лист, с которым вы работаете, вы можете изменить его при необходимости.

3. Затем в ячейке A22 (ячейка имеет определенное имя) введите эту формулу = Цвет (имя, которое вы определили на шаге выше), затем перетащите маркер заливки в диапазон, который имеет тот же масштаб, что и исходный диапазон данных, в котором вы хотите подсчитать затененный цвет. Смотрите скриншот:

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

4. В пустой ячейке введите эту формулу. = СЧЁТЕСЛИ (A22: K41; «>» & 0) чтобы подсчитать количество ячеек с числами больше нуля, затем нажмите клавишу Enter. Смотрите скриншот:

Функции: В формуле выше A22: K41 — это диапазон ячеек с числами.

Теперь 15 — количество закрашенных ячеек.

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

Быстро подсчитывайте заштрихованные ячейки с помощью функции COUNTSHADES Kutools for Excel

Вот функция под названием КОНФЕТЫ в Kutools Функции группа Kutools for Excel который может быстро подсчитать количество затененных ячеек с небольшим шагом.

После бесплатной установки Kutools for Excel, вы можете использовать функцию COUNTSHADES, выполнив следующие действия:

1. Выберите пустую ячейку, в которую вы будете выводить результат, и нажмите Кутулс > Kutools Функции > Статистические и математические > КОНФЕТЫ. Смотрите скриншот:

2. В появившемся диалоговом окне щелкните, чтобы выбрать диапазон, из которого вы хотите подсчитать затененные ячейки. Смотрите скриншот:

3. Затем вы увидите, что количество затененных ячеек в выбранном диапазоне отображается в Аргументы функций диалоговое окно, вы можете нажать OK чтобы экспортировать результат в ячейку, выбранную на шаге 1. См. снимок экрана:

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

Лучшие инструменты для офисной работы

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

Office Tab Добавляет в Office интерфейс с вкладками и значительно упрощает вашу работу

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
• Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
• Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Excel: подсчет ячеек по цвету

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

Первый метод — использование функции COUNTIF. Это функция, которая позволяет вам подсчитать количество ячеек, соответствующих определенному критерию. Например, если вы хотите посчитать количество закрашенных желтым цветом ячеек в диапазоне A1:B10, вы можете использовать следующую формулу: =COUNTIF(A1:B10,»yellow»).

Второй метод — использование условного форматирования. Условное форматирование позволяет вам автоматически изменять внешний вид ячеек, основываясь на определенном критерии. Вы можете задать условие для закрашивания ячеек определенным цветом. Затем, используя функцию COUNTIF, вы можете подсчитать количество ячеек с этим цветом. Например, вы можете установить условное форматирование для закрашивания ячеек зеленым цветом, если их значение больше 10, и затем использовать функцию =COUNTIF(A1:B10,»green») для подсчета количества закрашенных зеленым цветом ячеек.

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

Что такое Excel?

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

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

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

Зачем считать количество закрашенных ячеек?

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

1. Анализ данных:

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

2. Подготовка отчетов:

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

3. Проверка целостности данных:

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

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

Как найти и выделить закрашенные ячейки?

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

Чтобы найти и выделить закрашенные ячейки в Excel, выполните следующие шаги:

1. Выделите диапазон ячеек, в которых вы хотите найти закрашенные ячейки.
2. Выберите меню «Главная» (Home) в ленте.
3. В разделе «Стили» (Styles) выберите пиктограмму «Условное форматирование» (Conditional Formatting) и нажмите на него.
4. Из выпадающего меню выберите пункт «Фильтр по цвету ячейки» (Filter by Color).
5. Выберите цвет, по которому вы хотите отфильтровать ячейки.

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

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

Как посчитать количество закрашенных ячеек вручную?

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

1. Выделите диапазон ячеек, в котором хотите подсчитать закрашенные ячейки.
2. Откройте диалоговое окно «Условное форматирование». Для этого нажмите правой кнопкой мыши на выделенном диапазоне ячеек и выберите «Условное форматирование» из контекстного меню.
3. В диалоговом окне «Условное форматирование» выберите вкладку «Общие».
4. Выберите опцию «Формат заполнения» и нажмите кнопку «ОК».
5. Теперь все закрашенные ячейки будут выделены выбранным цветом.
6. Сосчитайте количество выделенных ячеек вручную.

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

Как посчитать количество закрашенных ячеек с помощью формулы?

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

1. Выберите ячейку, в которую вы хотите поместить результат подсчета.
2. Введите формулу =COUNTIF(range,»<>«) , где range — это диапазон ячеек, в котором вы хотите посчитать количество закрашенных ячеек.
3. Нажмите клавишу Enter для завершения ввода формулы.

Например, если вам нужно посчитать количество закрашенных ячеек в диапазоне A1:A10, введите следующую формулу: =COUNTIF(A1:A10,»<>«) .

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

Также, вы можете использовать различные условия в формуле COUNTIF для подсчета закрашенных ячеек по определенным критериям. Например, если вы хотите посчитать количество закрашенных ячеек с определенным цветом фона, вы можете использовать формулу =COUNTIF(range,cell_format) , где cell_format — это условие заданного формата ячейки.

Теперь вы знаете, как посчитать количество закрашенных ячеек с помощью формулы в Excel. Этот способ позволяет автоматически подсчитывать количество закрашенных ячеек и обновлять результат при необходимости.

Примеры использования формулы для подсчета закрашенных ячеек

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

Используя функцию COUNTIF

Пример формулы: =COUNTIF(A1:F10,»<>«)

Данная формула подсчитывает количество ячеек, в которых содержится любое значение.

Пример формулы: =SUMPRODUCT(—(A1:F10<>«»))

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

Пример формулы: =COUNTIF(A1:F10,»<>«) — COUNTIF(A1:F10,»»)

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

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

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

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

Сводка

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

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

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

Как в 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

Читайте также: Excel как посчитать количество слов в ячейке

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

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 (файл с поддержкой макросов)

СчетЯчеек_Заливка

Данная функция является частью надстройки MulTEx

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

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

Вызов команды через стандартный диалог:

Мастер функций–Категория “MulTEx”– СчетЯчеек_Заливка

Вызов с панели MulTEx:

Сумма/Поиск/Функции – Математические – СчетЯчеек_Заливка

Синтаксис:
=СчетЯчеек_Заливка( $E$2:$E$20 ; $E$7 ; I13 ; $A$2:$A$20 )

ДиапазонСчета( $E$2:$E$20 ) – диапазон значений для подсчета. Можно указать несколько столбцов. Столбец с критерием(если планируется считать еще и по критерию) не обязательно должен входит в диапазон.

ЯчейкаОбразец( $E$7 ) – ячейка-образец с цветом заливки. Ячейки с этим цветом будут подсчитаны.

Критерий( I13 ) – необязательный аргумент. Если указан, то подсчитываются ячейки с указанным критерием и цветом заливки. Допускается применение в критерии символов подстановки – “*” и “?” . Например, для подсчета только ячеек, в которых содержится слово “мир” необходимо указать в качестве критерия – “*мир*” . Если необходимо посчитать количество непустых ячеек с указанным цветом заливки, то можно указать критерий: “*?*” . Если не указан, то подсчитываются все ячейки с указанным цветом заливки.
Так же данный аргумент может принимать в качестве критерия символы сравнения ( , =, <>, ):

• “>0” – будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых больше нуля;
• “>=2” – будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых больше или равно двум;
• ” – будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых меньше нуля;
• ” – будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых меньше или равно 60;
• “<>0″ – будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых не равно нулю;
• “<>” – будут просуммированы все ячейки в столбце суммирования, значения ячеек критериев для которых не пустые;

Вместо нуля может быть любое число или текст. Так же можно добавить ссылку на ячейку со значением: “<>“&D$1

ДиапазонКритерия( $A$2:$A$20 ) – Необязательный аргумент. Указывается диапазон, в котором следует искать критерий(если критерий указан). ДиапазонКритерия должен быть равен по количеству ячеек ДиапазонуСчета. Если ДиапазонКритерия не указан, то критерий просматривается в ДиапазонеСчета.

ИспУФ() – Необязательный аргумент. Допускается указание логических значений ИСТИНА(TRUE) или ЛОЖЬ(FALSE). По умолчанию принимает значение ИСТИНА. Если указан как ИСТИНА, то функция будет подсчитывать ячейки с учетом примененного к ним условного форматирования. Если указан как ЛОЖЬ, то функция будет подсчитывать ячейки без учета примененного условного форматирования, т.е. даже если условное форматирование применено и ячейка окрашена с его помощью, а реальный цвет заливки не соответствует цвету ЯчейкиОбразца – то она не будет подсчитана.

Читайте также: Меню в ячейке в excel

Функция подсчитывает любые ячейки, заливка которых равна заливке ячейки-образца. Даже если ячейка будет пустая, но заливка будет равна указанной – ячейка будет подсчитана. Чтобы подсчитать только заполненные ячейки в качестве критерия следует указать – “*?*” , а ДиапазонКритерия не указывать.

Важно: Функция не вычисляется при изменении цвета заливки. Для пересчета функции после изменения параметров необходимо выделить ячейку и нажать F2–Enter. Либо нажать сочетания клавиш Shift+F9(пересчет функций активного листа) или клавишу F9(пересчет функций всей книги)

Примечание: данная функция будет корректно работать даже при примененном к ячейке Условном форматировании. Однако если в ячейке/диапазоне присутствуют условия, формат для которых задан при помощи шкал, градиентов, гистограмм и значков – функция может вернуть некорректный результат. Связано это с тем, что Excel не предоставляет доступ к данным типам УФ извне.

Количество и сумма ячеек по цвету в 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 — для всей книги).

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

Счёт по цвету: СЧЁТ, СУММ, СРЗНАЧ, МИН, МАКС по цвету ячейки или цвету шрифта

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

Надстройка «Счёт по цвету» мгновенно и без VBA агрегирует значения любого типа цветовой расцветки:

• Вычисление СЧЁТ, СУММ, СРЗНАЧ, МИН, МАКС для каждого цвета
• Агрегация по цвету заливки и/или цвету условного форматирования
• Cчёт по цвету фона ячеек или цвету шрифта
• Предпросмотр и вставка таблицы результата на рабочий лист

Добавить «Счёт по цвету» в Excel 2019, 2016, 2013, 2010

Подходит для: Microsoft Excel 2019 – 2010, desktop Office 365 (32-бит и 64-бит).

Как работать с надстройкой:

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

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

1. Нажмите кнопку «Счёт по цвету» на панели XLTools > Укажите диапазон.
   Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
2. Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:
   • Заданные цвета – чтобы учитывать только ячейки со сплошной заливкой фона
   • Условные цвета – чтобы учитывать только ячейки с условным форматированием
   • Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
3. Из следующего выпадающего списка выберите «Вычислять по цвету» «Фона».
4. Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.
   Внимание: надстройка автоматически распознает и произведет расчёт по всем цветам в диапазоне. Ячейки без фона, без заливки и ячейки с белой заливкой обрабатываются вместе. Так, вы сможете сравнить результаты вычислений по цветным и бесцветным ячейкам.
5. Выберите поместить результаты на новый или существующий лист.
6. Нажмите OK > Готово!

Как посчитать значения ячеек на основе цвета шрифта

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

1. Нажмите кнопку «Счёт по цвету» на панели XLTools > Укажите диапазон.
   Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
2. Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:
   • Заданные цвета – чтобы учитывать только ячейки заданным цветом шрифта
   • Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
3. Из следующего выпадающего списка выберите «Вычислять по цвету» «Шрифта».
4. Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.
5. Выберите поместить результаты на новый или существующий лист.
6. Нажмите OK > Готово!

Читайте также: Уместить текст в одной ячейке в excel

Как посчитать значения ячеек на основе цвета условного форматирования

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

1. Нажмите кнопку «Счёт по цвету» на панели XLTools > Укажите диапазон.
   Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
2. Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:
   • Условные цвета – чтобы учитывать только ячейки с условным форматированием
   • Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
3. Из следующего выпадающего списка выберите «Вычислять по цвету» «Фона» или «Шрифта», в зависимости от типа условного форматирования.
4. Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.
5. Выберите поместить результаты на новый или существующий лист.
6. Нажмите OK > Готово!

Доступные агрегатные вычисления (Count, Sum, Average, Minimum, Maximum)

Надстройка производит расчёт самых частых агрегатных функций, на основе цвета шрифта или фона ячейки:

• СЧЁТ (COUNT) – подсчёт количества всех значений в диапазоне по цвету
• СУММ (SUM) – сумма всех значений в диапазоне по цвету
• СРЗНАЧ (AVERAGE) – среднее (арифметическое среднее) всех значений в диапазоне по цвету
• МИН (MIN) – наименьшее значение в диапазоне по цвету
• МАКС (MAX) – наибольшее значение в диапазоне по цвету

Как ячейки и значения учитываются при вычислениях

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

• В расчёт принимаются: числовые значения, а также формулы, функции, ссылки на ячейки, которые возвращают числовое значение. Все пустые ячейки и ячейки, которые содержат текст, даты, ошибки игнорируются.
• Надстройка игнорирует скрытые строки или столбцы, т.е. в вычислениях учитываются только видимые ячейки. Если вы хотите провести вычисления по всему диапазону, пожалуйста, отобразите строки/столбцы и очистите фильтры.
• Вставленная на лист, сводная таблица с результатами вычислений содержит значения (не формулы и не ссылки).
• Объединённые ячейки обрабатываются как одна ячейка.

Появились вопросы или предложения? Оставьте комментарий ниже.

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

есть 2 столбца
ячейки в них закрашены 4 цветами (причем, некоторые из них нестандартны – выбирал оттенки через “другие цвета”)

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

насколько я понимаю, подобную задачу надо решать посредством VBA, некоторые варианты я видел в интернете, у меня не получилось

Посчитать количество непустых ячеек
Добрый день! Нужно посчитать количество непустых ячеек (содержащих число) каждому региону. .

Как посчитать количество ячеек по двум параметрам
Помогите пожалуйста! Мне нужно посчитать количество ячеек по двум параметрам: пол и возраст.

посчитать количество не пустых ячеек в промежуточных строках
Ребята подскажите как формулой, в приложенном “примере” посчитать количество обращений для каждого.

Как посчитать заданное количество непустых ячеек по указанным параметрам
Здравствуйте, уважаемые! Помогите определиться с формулой подсчёта. Имеется, например, график.

сразу предупреждаю, с VBA работал давно

создал модуль, забил туда этот код и сохранил, выбрал имя модуля “test” и в конце добавилось 2 строки

забил внутрь текст функций – пишется компайл ерор, ожидается End Sub, в чем ошибка?

и попутно вопрос – как узнать код цвета, насколько я понял именно его значение нужно подставить в ObrazecCvet

Текст функций не нужно никуда забивать – они должны быть на верхнем уровне.

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

KoGG, прекрасный код, но я бы ещё добавил Application.Volatile, чтобы функция пересчитывалась хотя бы по F9.

Byronel, В приложении образец – можно поиграть с ним.

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

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

Как при суммировании ячеек произвести деление на количество не пустых ячеек
Всем добрый вечер. Допустим есть ряд не пустых ячеек А1-А10, и нужно найти среднее значение. Берем.

Посчитать число отформатированных ячеек
Большой массив данных содержит несколько клеток, отформатированных особым образом. выискивать их.

Посчитать сумму четных ячеек в столбце
Есть столбец G Как посчитать сумму например, G12+G14+. +G33344? спасибо