Подсчитать данные, соответствующие критериям, с помощью функции Excel COUNTIFS

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

Примечание . Информация в этой статье относится к Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel Online и Excel для Mac.

COUNTIFS Функция пошаговое руководство

COUNTIFS расширяет полезность функции COUNTIF, позволяя вам указать несколько диапазонов и несколько критериев, а не только один, как в представлении COUNTIF.

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

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

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

Функция COUNTIFS может сделать это, поскольку она позволяет указывать в качестве аргументов несколько диапазонов (Criteria_range ) и несколько условий (Criteria).

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

Введите учебные данные

Первый шаг к использованию функции COUNTIFS в Excel – это настройка данных.

Для этого урока введите данные, показанные на рисунке выше, в ячейки с D1 по F11 на листе Excel.

В строке 12 под данными находится функция COUNTIFS и два критерия поиска. Это критерии:

• Торговые агенты из Восточного региона продаж
• За год размещено более 250 заказов

Чтобы установить этот критерий, введите Восток в ячейку D12 и > 250 в ячейку E12 .

Синтаксис функции COUNTIFS

В Excel синтаксис функции относится к макету функции и включает в себя имя функции, скобки и аргументы.

Синтаксис для функции COUNTIFS:

 = COUNTIFS (Criteria_range1, Criteria_range, Criteria_range2, Criteria2, ...) 

В функции можно указать до 127 пар Criteria_range и Criteria.

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

Все аргументы в этой функции обязательны.

Criteria_range : группа ячеек, в которой функция должна искать соответствие соответствующему аргументу Criteria.

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

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

Запустите функцию COUNTIFS

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

Учебное пособие

1. Выберите ячейку F12 , чтобы сделать ее активной. Это где функция COUNTIFS будет введен.
2. Выберите Формулы .
3. Выберите Дополнительные функции > Статистика , чтобы открыть список статистических функций.
4. Выберите COUNTIFS , чтобы открыть диалоговое окно «Аргументы функции».

Данные, введенные в пустые строки в диалоговом окне, образуют аргументы функции COUNTIFS.

Введите аргумент Criteria_range1

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

• Торговые агенты из Восточного региона продаж
• Торговые агенты, у которых более 250 заказов на продажу за год

Аргумент Criteria_range1 указывает диапазон ячеек для функции COUNTIFS, которую нужно искать при попытке сопоставить первый критерий, который является восточным регионом продаж.

Учебное пособие

1. В диалоговом окне «Аргументы функции» поместите курсор в текстовое поле «Criteria_range1».
2. Выделите ячейки от D3 до D9 на листе, чтобы ввести эти ссылки на ячейки в качестве диапазона, который будет искать функция.

Введите аргумент Критерий1

В этом учебном пособии первым критерием будет соответствие данных в диапазоне D3: D9 восточному.

Хотя в диалоговое окно могут быть введены фактические данные, такие как слово East , , обычно лучше всего указывать ссылку на ячейку данных в диалоговом окне.

Учебное пособие

1. Поместите курсор в текстовое поле Criteria1 диалогового окна Function Arguments.
2. Выберите ячейку D12 , чтобы ввести ссылку на эту ячейку в диалоговое окно.
3. Поисковый термин Восток добавляется в ячейку D12.

Если в качестве аргумента Criteria введена ссылка на ячейку, например D12, функция COUNTIFS ищет совпадения с данными, введенными в эту ячейку на листе.

Поскольку для критериев используется ссылка на ячейку, после подсчета агентов из восточного региона, которые превысили 250 продаж, легко найти те же данные для другого региона продаж, изменив данные в ячейке D12 (например, изменить восток на север). или запад). Функция автоматически обновляет и отображает новый результат.

Введите аргумент Criteria_range2

Аргумент Criteria_range2 указывает диапазон ячеек, которые должна выполнять функция COUNTIFS при попытке сопоставления со вторым критерием. В этом уроке торговые агенты продали более 250 заказов в этом году.

Учебное пособие

1. В диалоговом окне «Аргументы функции» поместите курсор в текстовое поле «Criteria_range2».
2. Выделите ячейки от E3 до E9 на рабочем листе, чтобы ввести эти ссылки на ячейки в качестве второго диапазона, который будет искать функция.

Введите Критерии2 Аргументы

В этом учебном пособии второй критерий будет соответствовать, если данные в диапазоне E3: E9 превышают 250 заказов на продажу.

Как и в случае с аргументом Criteria1, введите ссылку на ячейку для местоположения Criteria2 в диалоговом окне, а не сами данные.

Учебное пособие

1. Поместите курсор в текстовое поле Criteria2 в диалоговом окне «Аргументы функции».
2. Выберите ячейку E12 , чтобы ввести ссылку на эту ячейку. Функция ищет диапазон, выбранный на предыдущем шаге, для поиска данных, соответствующих этому критерию.
3. Выберите ОК , чтобы завершить функцию COUNTIFS и закрыть диалоговое окно.
4. Ответ два (2) появляется в ячейке F12, ячейке, в которую вы ввели функцию.

Завершить учебник

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

Как работает COUNTIFS Logic

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

• Только два агента, Ральф и Сэм, работают в восточном регионе продаж и сделали более 250 заказов за год. Только эти две записи учитываются функцией.
• Хотя Марта работает в Восточном регионе, у нее было менее 250 заказов. Ее запись не засчитывается.
• У Джо и Тома было более 250 заказов на год, но ни один из них не работает в восточном регионе продаж. Их записи не учитываются.

При выборе ячейки F12 полная функция появляется на панели формул над рабочим листом. Это выглядит так:

 = COUNTIFS (D3: D9, D12, E3: E9, E12) 

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