Как использовать COUNTIFS, SUMIFS, AVERAGEIFS в Excel

Три наиболее часто используемые формулы в Excel, которые выполняют простые математические вычисления: COUNT, SUM а также СРЕДНИЙ. Если вы управляете финансовым бюджетом в Excel или просто отслеживаете свой следующий отпуск, вы, вероятно, использовали одну из этих функций раньше.

В этой статье мы рассмотрим основы этих трех функций и их соответствующих и полезных аналогов: COUNTIFS, SUMIFS и AVERAGEIFS..

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

Excel COUNT, SUM и AVERAGE

Чтобы узнать, сколько мобильных телефонов мы продали, мы можем быстро использовать COUNT формула как показано ниже:

= СЧЕТ (Е2: Е16)

С другой стороны, чтобы получить общий объем продаж, мы можем использовать SUM формула как показано ниже:

= СУММ (Е2: Е16)

Наконец, чтобы узнать средние продажи, которые мы сделали для всех телефонов, мы можем использовать СРЕДНИЙ формула как ниже:

= СРЗНАЧ (Е2: Е16)

Результат должен быть таким, как показано ниже:

Формулы COUNT, SUM и AVERAGE будут работать только для записей, в которых значение ячейки находится в числовом формате. Любая запись в пределах диапазона формул (т.е.. E2: E16 в этом примере) не в числовом формате будет игнорироваться.

Поэтому убедитесь, что все ячейки в формуле COUNT, SUM и AVERAGE отформатированы как Число, не Текст. Попробуйте использовать ту же формулу, но с E: E как диапазон вместо E2: E16. Он вернет тот же результат, что и раньше, потому что он игнорирует заголовок (т.е.. Цена продажи), который находится в текстовом формате.

Теперь, что если мы хотим узнать количество продаж, общий объем продаж и средний объем продаж на телефон, только для тех, что продаются в США? Здесь важную роль играют COUNTIFS, SUMIFS и AVERAGEIFS. Соблюдайте формулу ниже:

COUNTIFS

Формула разбивка:

1. = COUNTIFS ( - знак равно указывает на начало формулы в ячейке и COUNTIFS это первая часть функции Excel, которую мы используем.
2. D2: D16 - Относится к диапазону данных, чтобы проверить, соответствует ли он критериям, включаемым в формулу подсчета..
3. "СОЕДИНЕННЫЕ ШТАТЫ АМЕРИКИ" - Критерии для поиска в указанном диапазоне данных (D2: D16)
4. ) - Закрывающая скобка с указанием конца формулы.

Формула возвращает 6 - количество продаж товаров, отправленных со склада в США..

SUMIFS

Формула разбивка:

1. = СУММЕСЛИМН ( - знак равно снова указывает начало формулы.
2. E2: E16 - Относится к диапазону данных, которые мы хотели бы суммировать, т.е. цена продажи в нашем примере.
3. D2: D16 - Относится к диапазону данных, чтобы проверить, удовлетворяет ли он критериям, которые будут включены в общую сумму.
4. "СОЕДИНЕННЫЕ ШТАТЫ АМЕРИКИ" - Критерии для поиска в указанном диапазоне данных (D2: D16)
5. ) - Закрывающая скобка с указанием конца формулы.

Формула показывает $ 6050 общий объем продаж продукции, поставленной со склада в США.

AVERAGEIFS

Формула разбивка:

1. = AVERAGEIFS ( - знак равно указать начало формулы.
2. E2: E16 - Относится к диапазону данных, которые мы хотели бы усреднить. В этом примере мы хотим получить среднюю сумму продаж для всех телефонов, проданных в США..
3. D2: D16 - Относится к диапазону данных, чтобы проверить, удовлетворяет ли он критериям, включаемым в среднюю формулу.
4. "СОЕДИНЕННЫЕ ШТАТЫ АМЕРИКИ" - Критерии для поиска в указанном диапазоне данных
5. ) - Закрывающая скобка с указанием концов формулы.

Формула показывает, что мы продали продукт примерно за $ 1008 на телефон в США.

Все три формулы могут принимать более одного критерия. Например, если мы хотим знать одни и те же цифры (т.е.. COUNT, SUM а также СРЕДНИЙ) для продуктов, проданных в Соединенные Штаты Америки, но конкретно только для Samsung бренда, нам просто нужно добавить диапазон данных для проверки, а затем его критерии.

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

= COUNTIFS (D2: D16, «США», B2: B16, «Samsung») = SUMIFS (E2: E16, D2: D16, «США», B2: B16, «Samsung») = AVERAGEIFS (E2: E16, D2: D16, «США», B2: B16, «Samsung»)

Вы заметите, что Excel также имеет COUNTIF, SUMIF а также AVERAGEIF формулы без суффикса «S». Те, которые используются аналогично COUNTIFS, SUMIFS а также AVERAGEIFS. Тем не менее, те, без суффикса «S» в формуле есть ограничение, позволяющее только один критерий для формулы.

Поскольку синтаксис немного отличается, я бы рекомендовал использовать COUNTIFS, SUMIFS а также AVERAGEIFS только так, как это может быть использовано для одного или нескольких критериев, если это необходимо. наслаждаться!