Как подсчитать количество строк в Excel по условию

Обновлено: 24.11.2024

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

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

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

СЧЁТЕСЛИМН аргументов

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

criteria_range1 – ячейки для проверки критериев
criteria1 — критерии соответствия

Необязательные аргументы критериев

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

[criteria_range2] – второй диапазон ячеек для проверки критериев.
[criteria2] — второй критерий для соответствия
[criteria_range3] — третий диапазон ячеек для проверки критериев.
[criteria3] – третий критерий для соответствия

На снимке экрана ниже выделен аргумент критерии_диапазона2.

Квадратная скобка означает, что аргумент является необязательным

Правила диапазона критериев

При добавлении каждого набора критериев диапазона и критериев в функцию СЧЁТЕСЛИМН необходимо соблюдать следующие два правила:

Все диапазоны критериев должны быть одного размера (одинаковое количество строк и столбцов).
Каждый диапазон критериев должен представлять собой непрерывный блок ячеек.

СЧЁТЕСЛИМН – текст и цифры

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

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

Подсчет по нескольким критериям — текст и числа

В этом примере будут учитываться только строки, в которых элемент "Ручка" и количество больше или равно десяти.

Выберите ячейку, в которой вы хотите увидеть итог.
Введите знак равенства (=), чтобы начать формулу.
Тип: СЧЁТЕСЛИМН(
Выберите ячейки, содержащие значения для проверки первого критерия. В этом примере будут проверены ячейки A2:A10
Введите запятую и первый критерий: "Перо".
Примечание. Поскольку это текстовый критерий, он заключен в двойные кавычки.
Чтобы начать следующий набор критериев, введите запятую.
Выберите ячейки, содержащие значения для проверки второго критерия. В этом примере будут проверены ячейки B2:B10
Введите запятую и второй критерий: ">=10".
Примечание. Поскольку этот критерий включает операторы, он заключен в двойные кавычки. Для подсчета строк, в которых количество равно 10, потребуется только число 10.
Заканчивайте закрывающей скобкой: )
Заполненная формула показана ниже.
Нажмите клавишу Enter, чтобы завершить ввод.

Результат показывает количество строк, соответствующих критериям

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

Используйте типизированные критерии, чтобы получить количество ячеек:

или ссылки на ячейки:

В этом примере есть список заказов, и мы хотим подсчитать заказы на основе двух критериев, хранящихся в двух разных столбцах:

Регион – Восток (столбец B).
Задача содержит примечание в ячейке (столбец D)

На снимке экрана ниже заказы в строках 2 и 9 соответствуют обоим критериям:

Формула СЧЁТЕСЛИМН

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

=СЧЁТЕСЛИМН(B2:B11,F2,D2:D11,"<>")

Результат формулы равен 2, и для получения общего количества используются два набора аргументов:

Набор критериев 1
- проверьте названия регионов в диапазоне критериев 1 -- B2:B11
- ищите наши критерии региона — название региона в ячейке F2
Набор критериев 2
- проверьте примечания к проблеме в диапазоне критериев 2 -- D2:D11
- ищите наши критерии Проблемы – ячейки, которые не пусты – "<>"

ПРИМЕЧАНИЕ. Критерий «<>» — это оператор «не равно». При отдельном использовании это означает «не равно ‘нет текста’», поэтому будут учитываться непустые ячейки.

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

Примечание. Подсчет пустых или пустых строк

В примере "Регион/проблема" в столбце "Проблема" было введено несколько примечаний, а остальные ячейки были пусты. Наша формула учитывает только непустые ячейки.

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

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

В противном случае вместо СЧЁТЕСЛИМН можно использовать формулу СУММПРОИЗВ, например эту:

ПРИМЕЧАНИЕ. Это два знака минус (двойной унарный) перед каждым разделом формулы СУММПРОИЗВ, а не длинные тире.

Несколько критериев в одном столбце

В приведенных выше формулах критерии, которые вы хотите подсчитать, находятся в 2 разных столбцах, как в приведенном выше примере "Ручка" и "Количество".

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

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

В этом видео на листе вводятся минимальное и максимальное число.

В ячейке E6 формула СЧЁТЕСЛИМН подсчитывает количество ячеек в столбце B, которые соответствуют обоим следующим критериям

число в ячейках B2:B10 больше или равно минимальному числу, указанному в ячейке E4.
число в ячейках B2:B10 меньше или равно Максимальному числу, указанному в ячейке G4.

Вот формула в ячейке E6:

Предупреждения функции COUNTIFS

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

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

7 способов подсчета в Excel

Помимо СЧЁТЕСЛИМН, в Excel существует много других способов подсчёта. Чтобы увидеть краткий обзор 7 способов подсчета в Excel, посмотрите это короткое видео. И получите пример рабочей тетради 7 способов подсчета, чтобы вы могли следить за видео.

Кроме того, на странице "Функции подсчета в Excel" есть дополнительные примеры формул для подсчета и примеры файлов.

Изменить оператор для функции СЧЁТЕСЛИМН

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

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

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

Расшифровка видео: критерии текста и числа

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

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

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

Запустите формулу

В этой ячейке я начну со знака равенства, затем наберу СЧЁТЕСЛИМН и открою квадратную скобку

Первое, что я собираюсь проверить, это проданный товар. Диапазон, первый диапазон: от A2 до A10.

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

Добавить второй критерий

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

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

Было 2 заказа на ручку, количество которых превышает 10.

Ссылка на ячейку листа

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

То же самое для этого критерия, для количества. Я уберу 10, просто удалив это, оставив операторы в двойных кавычках.

Затем я наберу амперсанд и ячейку с номером. Так что это больше или равно любому числу в ячейке E3.

Изменить критерии на листе

Когда я нажимаю Enter, я получаю тот же результат, но тогда его проще изменить.

Я мог бы ввести здесь 5, и мы видим, что было 4 заказа, в которых количество больше или равно 5, вместо 10, которые у нас были раньше.

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

Расшифровка видео: подсчет чисел в диапазоне

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

В Excel 2003 и более ранних версиях, если вы хотите подсчитывать данные на основе критериев, вы можете использовать СЧЁТЕСЛИ

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

Таким образом, если мы хотели подсчитать товары, количество которых находится в пределах от 5 до 10, в старой версии нам приходилось комбинировать два разных СЧЁТЕСЛИ

Запустите формулу

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

У нас может быть до 127 таких комбинаций, так что мы получаем некоторую гибкость.

В этом случае у нас есть СЧЁТЕСЛИМН, когда мы смотрим на этот диапазон ячеек и говорим: я хочу:

количества, которые больше или равны числу в E4
И они также должны быть меньше или равны числу в G4.

Итак, мы объединили два критерия

И когда мы нажимаем Enter, в результате получаем 6

Добавить дополнительные критерии

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

Поэтому я добавил еще один набор критериев, поэтому третий набор рассматривает диапазон от A2 до A10 и находит предметы, в которых есть ручка.

И когда мы нажимаем Enter, их теперь только 2, где

количество от 5 до 10
И продаваемым товаром была ручка.

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

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

Итак, теперь их 4, потому что мы получаем элементы "ручка" и "карандаш", где количество составляет от 5 до 10

Для подсчета строк с использованием нескольких критериев в разных столбцах с логикой ИЛИ можно использовать функцию СУММПРОИЗВ. В показанном примере формула в H7 выглядит так:

Результатом является количество строк, где Color — "синий", а Pet — "собака".

В показанном примере мы хотим подсчитать строки, в которых указан "синий" цвет ИЛИ питомец - "собака". Это можно сделать с помощью логической логики и функции СУММПРОИЗВ следующим образом:

Основой этой формулы являются два логических теста, по одному для каждого условия:

В булевой алгебре логика ИЛИ требует сложения, поэтому два логических теста соединяются сложением (+). Первый логический тест проверяет, является ли Color «синим»:

Поскольку мы тестируем 7 значений в диапазоне C5:C11, результатом является массив из 7 значений ИСТИНА/ЛОЖЬ. Второй логический тест проверяет, является ли Питомец «собакой»:

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

Затем два массива складываются. Математическая операция автоматически приводит значения TRUE FALSE к единицам и нулям, и в результате получается один массив, подобный этому:

Мы не можем просто сложить эти значения с помощью СУММПРОИЗВ, потому что это приведет к двойному подсчету строк как с "синим", так и с "собакой". Итак, чтобы справиться с этой ситуацией, мы используем «>0» вместе с двойным отрицанием (--), чтобы установить все значения либо в 1, либо в ноль:

Эти операции создают единый массив единиц и нулей внутри функции СУММПРОИЗВ:

Обрабатывая только один массив, СУММПРОИЗВ просто возвращает сумму элементов массива.

Другие логические тесты

В примере показаны проверки на простое равенство, но при необходимости вы можете заменить эти операторы другими логическими проверками. Например, для подсчета строк, в которых ячейки в столбце A содержат "красные" ИЛИ ячейки в столбце B содержат "синие", можно использовать следующую формулу:

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

Чтобы обрабатывать больше условий, просто добавьте больше логических тестов в функцию СУММПРОИЗВ, используя круглые скобки для управления порядком операций, где это необходимо.

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.

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

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

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

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

Приведенная выше функция сообщает, что если 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 или получить поддержку в сообществе ответов.

Если вы хотите подсчитать количество строк, соответствующих определенному критерию, многие пользователи используют инструмент "Фильтр".

Для 1, 2 или 3 разных критериев вы можете работать так. Но если вам нужно применить много критериев, фильтр не является хорошим инструментом.

Используйте функцию СЧЁТЕСЛИМН

Функция СЧЁТЕСЛИМН подсчитывает количество строк, соответствующих одному или нескольким критериям.

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

Аргумент для СЧЁТЕСЛИМН

Функция СЧЁТЕСЛИМН очень проста в построении 😎

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

СЧЁТЕСЛИ и СЧЁТЕСЛИ

Разница только в количестве критериев

С помощью COUNTIFS вы можете подсчитать строки по 256 критериям.

Конструкция такая же, просто добавьте больше критериев 😉

Пример с двумя критериями

Сколько одиноких мужчин в вашем наборе данных?

Выберите диапазон ячеек, в которых вы хотите выполнить поиск по критерию (столбец C).

Введите значение или текст для поиска (здесь "мужчина").

Слово "Человек" заключено в двойные кавычки, потому что это строка, а все строки в Excel должны быть заключены в двойные кавычки.

Затем для второго критерия

Следите за диапазонами данных ❗❗❗

=СЧЁТЕСЛИМН(C2:C20,"Человек",E2:E21,"Один")

Заменить критерии ссылкой

Вместо того, чтобы записывать значение критерия непосредственно в формулу, вы можете использовать содержимое ячейки, используя ссылку на ячейку 😎😀

Больше / Меньше

Вы можете создавать более сложные критерии с логическим символом симпатии больше (>) или меньше (

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