Как подсчитать количество ячеек с определенным значением в Excel

Обновлено: 21.11.2024

Как посчитать ячейки выше определенного значения или среднего значения в Excel?

Если у вас есть диапазон значений на листе, и вы хотите подсчитать ячейки выше/ниже определенного значения или выше/ниже среднего значения диапазона, как их быстро подсчитать? В Excel для этого можно использовать формулу СЧЁТЕСЛИ.

  • Повторное использование всего: добавляйте наиболее часто используемые или сложные формулы, диаграммы и другие объекты в избранное и быстро используйте их повторно в будущем.
  • Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты объединения: несколько книг и листов в одну; Объединение нескольких ячеек/строк/столбцов без потери данных; Объединить повторяющиеся строки и суммировать.
  • Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
  • Вставить, пропуская скрытые/отфильтрованные строки; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных электронных писем нескольким получателям.
  • Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировка по неделям, дням, частоте и т. д.; Фильтр по полужирному шрифту, формулам, комментариям.
  • Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Подсчет ячеек выше/ниже среднего

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!

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

Выберите пустую ячейку, например, ячейку C6, введите эту формулу =СЧЁТЕСЛИ(A1:C5,"> "&СРЗНАЧ(A1:C5)) (диапазон A1: C5 указывает диапазон, в котором вы хотите посчитать ячейки выше среднего, вы можете изменить его, как вам нужно), и нажмите кнопку Enter на клавиатуре. Смотрите скриншот:

Совет. Если вы хотите подсчитать количество ячеек ниже среднего, введите эту формулу =СЧЁТЕСЛИ(A1:C5," (диапазон A1:C5 указывает диапазон, в котором вы хотите подсчитать ячейки ниже среднего, вы можете изменить его как вам нужно).

Подсчет ячеек выше/ниже определенного значения

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

Выберите пустую ячейку, например, ячейку C6, введите эту формулу =СЧЁТЕСЛИ(A1:C5,">50") (диапазон A1: C5 указывает диапазон, который вы хотите подсчитать ячейки выше указанного значения 50, число 50 стоит критерий, вы можете изменить их как вам нужно) и нажмите кнопку Enter на клавиатуре. Смотрите скриншот:

Совет. Если вы хотите подсчитать количество ячеек ниже определенного значения, введите эту формулу =СЧЁТЕСЛИ(A1:C5," (диапазон A1:C5 указывает диапазон, в котором вы хотите подсчитать количество ячеек ниже указанного значения, число 50 стоит критерий, вы можете менять их по своему усмотрению).

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

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

Если диапазон, например A2:D20, содержит числовые значения 5, 6, 7 и 6, то число 6 встречается два раза.

Если столбец содержит "Бьюкенен", "Додсворт", "Додсворт" и "Додсворт", то "Додсворт" встречается три раза.

Существует несколько способов подсчета частоты появления значения.

Используйте функцию СЧЁТЕСЛИ, чтобы подсчитать, сколько раз определенное значение появляется в диапазоне ячеек.

Для получения дополнительной информации см. функцию СЧЁТЕСЛИ.

Функция СЧЁТЕСЛИМН аналогична функции СЧЁТЕСЛИ за одним важным исключением: СЧЁТЕСЛИМН позволяет применять критерии к ячейкам в нескольких диапазонах и подсчитывает количество совпадений всех критериев. Вы можете использовать до 127 пар диапазон/критерий с СЧЁТЕСЛИМН.

Синтаксис для СЧЁТЕСЛИМН:

COUNTIFS(диапазон_критериев1, диапазон_критериев1, [диапазон_критериев2, критерий2],…)

См. следующий пример:

Чтобы узнать больше об использовании этой функции для подсчета с несколькими диапазонами и критериями, см. раздел Функция СЧЁТЕСЛИМН.

Допустим, вам нужно определить, сколько продавцов продали конкретный товар в определенном регионе, или вы хотите узнать, сколько продаж на определенную сумму было совершено конкретным продавцом. Вы можете использовать функции ЕСЛИ и СЧЕТ вместе; то есть сначала вы используете функцию ЕСЛИ для проверки условия, а затем, только если результат функции ЕСЛИ равен True, вы используете функцию СЧЁТ для подсчета ячеек.

Формулы в этом примере должны быть введены как формулы массива.

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

Если вы открыли эту книгу в Excel для Windows или Excel 2016 для Mac и более поздних версиях и хотите изменить формулу или создать аналогичную формулу, нажмите F2, а затем нажмите Ctrl+Shift+Enter, чтобы формула вернулась результаты, которые вы ожидаете. В более ранних версиях Excel для Mac используйте +Shift+Enter.

Чтобы примеры формул работали, вторым аргументом функции ЕСЛИ должно быть число.

Подробнее об этих функциях см. в разделах Функция СЧЁТ и ЕСЛИ.

В следующих примерах мы используем функции ЕСЛИ и СУММ вместе. Функция ЕСЛИ сначала проверяет значения в некоторых ячейках, а затем, если результат проверки истинен, СУММ суммирует те значения, которые проходят проверку.

Примечания. Формулы в этом примере необходимо вводить как формулы массива.

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

Если вы открыли эту книгу в Excel для Windows или Excel 2016 для Mac и более поздних версиях и хотите изменить формулу или создать аналогичную формулу, нажмите F2, а затем нажмите Ctrl+Shift+Enter, чтобы формула вернулась результаты, которые вы ожидаете. В более ранних версиях Excel для Mac используйте +Shift+Enter.

Приведенная выше функция сообщает, что если C2:C7 содержит значения Buchanan и Dodsworth, то функция СУММ должна отображать сумму записей, в которых выполняется условие. Формула находит три записи для Бьюкенена и одну для Додсворта в заданном диапазоне и отображает 4.

Приведенная выше функция говорит, что если D2:D7 содержит значения меньше 9000 долларов США или больше 19 000 долларов США, то SUM должна отображать сумму всех тех записей, в которых выполняется условие. Формула находит две записи D3 и D5 со значениями меньше 9000 долларов США, а затем D4 и D6 со значениями больше 19000 долларов США и отображает 4.

Приведенная выше функция говорит, что если у D2:D7 есть счета для Бьюкенена на сумму менее 9000 долл. США, то функция SUM должна отображать сумму записей, в которых выполняется условие. Формула обнаруживает, что C6 соответствует условию, и отображает 1.

Вы можете использовать сводную таблицу для отображения итогов и подсчета вхождений уникальных значений. Сводная таблица — это интерактивный способ быстрого суммирования больших объемов данных. Вы можете использовать сводную таблицу для развертывания и свертывания уровней данных, чтобы сфокусировать результаты и перейти к подробностям из сводных данных для областей, которые вас интересуют. Кроме того, вы можете перемещать строки в столбцы или столбцы в строки («сведение»), чтобы увидеть, сколько раз значение встречается в сводной таблице. Давайте рассмотрим пример сценария электронной таблицы продаж, где вы можете подсчитать, сколько значений продаж имеется для гольфа и тенниса за определенные кварталы.

Введите следующие данные в электронную таблицу Excel.

Нажмите «Вставка» > «Сводная таблица».

В диалоговом окне "Создать сводную таблицу" нажмите "Выбрать таблицу или диапазон", затем нажмите "Новый лист" и нажмите "ОК".

Пустая сводная таблица создается на новом листе.

В области полей сводной таблицы выполните следующие действия:

Перетащите "Спорт" в область "Строки".

Перетащите квартал в область столбцов.

Перетащите Sales в область значений.

Имя поля отображается как SumofSales2 как в сводной таблице, так и в области значений.

На данный момент панель полей сводной таблицы выглядит следующим образом:

В области "Значения" щелкните раскрывающийся список рядом с SumofSales2 и выберите "Настройки поля значений".

В диалоговом окне "Параметры поля значения" выполните следующие действия:

В поле Суммировать значение по разделам выберите Количество.

В поле «Пользовательское имя» измените имя на «Количество».

В сводной таблице отображается количество записей о гольфе и теннисе в кварталах 3 и 4, а также данные о продажах.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

В этом видеоруководстве показано, как использовать функцию СЧЕТЕСЛИ для подсчета количества ячеек, содержащих определенную строку текста, например "Перо". Вы также можете найти текст, который является частью ячейки — сколько заказов было размещено на какой-либо вид ручек, например «гелевая ручка», «ручка» или даже «карандаш»?

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

Функция СЧЁТЕСЛИ в Excel

В Microsoft Excel вы можете использовать функцию СЧЕТЕСЛИ для подсчета ячеек, соответствующих одному критерию.

Примечание. Чтобы подсчитать количество ячеек по нескольким критериям, используйте функцию Excel СЧЁТЕСЛИМН.

Аргументы СЧЁТЕСЛИ

В синтаксисе функции СЧЁТЕСЛИ есть 2 обязательных аргумента:

  1. диапазон — ячейки для проверки критериев
  2. criteria – критерии для соответствия – введите формулу или укажите ссылку на ячейку.

Предупреждения СЧЁТЕСЛИ

Вот несколько предупреждений об ограничениях функции СЧЁТЕСЛИ, прежде чем использовать эту функцию в электронной таблице Excel.

  • Числа в числах. Функция СЧЁТЕСЛИ не может считать числа в числах.
    • Например, он не может подсчитать число 123, если значением ячейки является число 91 236, даже если это действительное число содержит строку 123.
    • Вместо этого используйте одну из других формул, которые будут подсчитывать числа внутри чисел.
    • СЧЕТЕСЛИ сопоставляют текстовые числа, например "00123", с действительными числами, например 123.
    • Это может привести к ложному подсчету дублирующегося значения.
    • Посмотрите другую формулу, использующую функцию СУММПРОИЗВ, которая поможет избежать этой проблемы.

    Точное соответствие определенному тексту

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

    Выполните следующие действия, чтобы создать формулу СЧЁТЕСЛИ для подсчета ручек:

    1. Выберите ячейку для формулы (в данном примере это ячейка D4).
    2. Введите знак равенства (=), чтобы начать формулу.
    3. Тип: СЧЁТЕСЛИ(
    4. Выберите диапазон ячеек, в которых перечислены элементы — в этом примере ячейки A2:A10
      • ПРИМЕЧАНИЕ. Если ваш список находится в именованной таблице Excel, в формуле будут отображаться имя таблицы и имя столбца -- =COUNTIF(tblExact[Item]
    5. Введите запятую, чтобы разделить аргументы
    6. Введите значение, которое вы хотите сопоставить. В этом примере значением является текст, заключенный в двойные кавычки: "Pen"
      • Примечание. Критерии можно использовать как в верхнем, так и в нижнем регистре – СЧЁТЕСЛИ регистр не учитывается
    7. Введите закрывающую скобку и нажмите Enter.

    Завершенная формула СЧЁТЕСЛИ

    Для именованной таблицы заполненная формула показывает имя таблицы (tblExact) и имя столбца (Item):

    и для списка рабочих листов формула показывает ссылку на ячейку:

    На этом снимке экрана показан результат приведенной выше формулы в ячейке D4. Есть 4 заказа с точным соответствием по критерию "ручка".

    Сопоставить любой текст в ячейке

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

    • Примечание. СЧЁТЕСЛИ не может найти числа среди действительных чисел.

    Как и в предыдущем примере, мы будем считать заказы "Pen". На этот раз формула будет содержать подстановочные знаки звездочки (*) до и после текстовой строки. Этот подстановочный знак означает любое количество неизвестных символов или отсутствие символов.

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

    1. Выберите ячейку для формулы (в данном примере это ячейка D4).
    2. Введите знак равенства (=), чтобы начать формулу.
    3. Тип: СЧЁТЕСЛИ(
    4. Выберите ячейки, в которых перечислены элементы — в этом примере ячейки A2:A10
      • ПРИМЕЧАНИЕ. Если ваш список находится в именованной таблице Excel, в формуле будут отображаться имя таблицы и имя столбца -- =COUNTIF(tblExact[Item]
    5. Введите запятую, чтобы разделить аргументы
    6. В двойных кавычках введите значение, которое вы хотите сопоставить, со звездочкой (*) подстановочным знаком до и после текста: "*Pen*"
      • Примечание. Можно использовать как верхний, так и нижний регистр — СЧЁТЕСЛИ регистр не учитывается
    7. Введите закрывающую скобку и нажмите Enter.

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

    Для именованной таблицы заполненная формула показывает имена таблицы и столбца:

    и для списка рабочих листов формула показывает ссылку на ячейку:

    Использовать ссылки на ячейки

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

    Затем в формуле укажите ячейку, в которую вы ввели текст критерия.

    Например, вот формула для сопоставления определенного элемента со ссылкой на ячейку E7, где был введен текст критерия "ручка".

    Вы также можете использовать ссылку на ячейку с подстановочными знаками. Используйте оператор & (амперсанд), чтобы соединить подстановочные знаки со ссылкой на ячейку.

    Расшифровка видео – подсчет определенных элементов

    ТЕХНОЛОГИЯ ВИДЕО

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

    для этого я могу использовать функцию СЧЁТЕСЛИ. в этой ячейке я начинаю со знака равенства, набираю СЧЁТЕСЛИ и открывающую скобку.

    Первый аргумент

    Первый аргумент — это диапазон, который я хочу проверить.

    Я выберу от A1 до A10.

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

    Второй аргумент

    Следующий аргумент — это критерии. Что именно я хочу, чтобы он учитывал?

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

    Когда я нажал Enter, в этом списке было найдено 4 элемента пера.

    Итак, 1, 2, 3, 4, он нашел все 4 элемента пера.

    Название элемента содержит «ручка»

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

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

    Поэтому не совсем ручка, я не хочу считать только ручки, но я хотел бы включить гелевую ручку и даже карандаш.

    Запустить формулу СЧЁТЕСЛИ

    Здесь снова я буду использовать СЧЁТЕСЛИ.

    • Я начну со знака равенства, СЧЕТЕСЛИ и открытой скобки, а затем диапазон будет от A1 до A10, введите запятую.
    • Для этого я хочу, чтобы он включал все, что содержит перо.
    • Я наберу свою двойную кавычку, а затем звездочку.
    • На моей клавиатуре это был Shift 8.
    • Это подстановочный знак, который представляет любое количество символов или отсутствие символов.
    • Затем текст, который я ищу. Я могу использовать верхний или нижний регистр здесь
    • Еще одна звездочка, еще одна двойная кавычка и закрывающая скобка.

    Он будет искать все, что содержит буквы P-E-N в строке.

    • До этого может быть что угодно или ничего.
    • И после этих трех букв может быть что угодно или ничего.

    Результат формулы

    И когда я нажимаю Enter, мы получаем 6. Итак, один, два, три, четыре, пять, шесть.

    Он нашел все, что содержало эту строку символов, P-E-N и регистр не имели значения.

    Получить образец файла

    Чтобы просмотреть все примеры СЧЁТЕСЛИ, загрузите пример книги "Подсчёт конкретных элементов". Заархивированный файл имеет формат xlsx и не содержит макросов.

    Как подсчитать, сколько ячеек содержит определенный текст или значение в Excel?

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

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

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

    <р>1. Выберите пустую ячейку, введите в нее приведенную ниже формулу и нажмите клавишу Enter, чтобы получить результат.

    =СЧЁТЕСЛИ($A$1:$D$8,"50")

    Примечание. В этой формуле измените ссылку на ячейку и текст или число по своему усмотрению. В этом случае мы подсчитываем количество ячеек в диапазоне A1:D8, равное 50.

    Подсчитайте, сколько ячеек содержат определенный текст или значение с помощью Kutools for Excel

    Утилита «Выбрать определенные ячейки» в Kutools for Excel поможет вам легко подсчитать количество ячеек, содержащих определенный текст или число, и сразу же выбрать эти ячейки.

    Перед применением Kutools for Excel сначала загрузите и установите его.

    <р>1. Выберите диапазон с ячейками текста или значений, которые вы хотите подсчитать, а затем нажмите Kutools > Выбрать > Выбрать определенные ячейки.

    <р>2. В диалоговом окне «Выбрать определенные ячейки» настройте следующие параметры.

    2.2) В разделе Определенный тип выберите Равно в первом раскрывающемся списке, введите определенный текст или число в текстовое поле;

    <р>3. Затем появится диалоговое окно, в котором будет указано общее количество подходящих ячеек. Нажмите кнопку ОК.

    Примечание. Эта утилита может подсчитывать и выбирать ячейки по двум критериям одновременно. Смотрите скриншот:

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

    Читайте также: