Как использовать СЧЁТЕСЛИМН, СУММЕСЛИМН, СРЗНАЧСЛИМН в Excel

Тремя наиболее часто используемыми формулами в Excel , которые выполняют простые математические вычисления, являются СЧЁТ(COUNT) , СУММ(SUM) и СРЗНАЧ(AVERAGE) . Независимо от того, управляете ли вы финансовым бюджетом в Excel или просто отслеживаете свой следующий отпуск, вы, вероятно, использовали одну из этих функций раньше.

В этой статье мы рассмотрим основы этих трех функций и их соответствующих и полезных аналогов: СЧЁТЕСЛИМН(COUNTIFS) , СУММЕСЛИМН и СРЗНАЧЕСЛИМН(SUMIFS and AVERAGEIFS) .

Excel СЧЁТ, СУММА и СРЕДНЕЕ

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

=COUNT(E2:E16)

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

=SUM(E2:E16)

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

=AVERAGE(E2:E16)

Результат должен быть следующим:

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

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

Теперь, что, если мы хотим узнать количество продаж, общую сумму продаж и среднюю сумму продаж на телефон, только для тех, которые продаются в США(USA) ? Именно здесь СЧЁТЕСЛИМН(COUNTIFS) , СУММЕСЛИМН и СРЗНАЧСЛИМН(SUMIFS and AVERAGEIFS) играют важную роль. Соблюдайте приведенную ниже формулу:

СЧЁТЕСЛИМН(COUNTIFS)

Разбивка формулы:

  1. =COUNTIFS( — “=” указывает на начало формулы в ячейке, а СЧЁТЕСЛИМН(COUNTIFS) — это первая часть используемой нами функции Excel(Excel function) .
  2. D2:D16 — Относится(Refers) к диапазону данных, чтобы проверить, удовлетворяют ли они критериям, которые должны быть включены в формулу подсчета(count formula) .
  3. «США»(“USA”) — критерии(Criteria) поиска в указанном диапазоне данных(data range) ( D2:D16 ) .
  4. ) – закрывающая скобка(Closing bracket) , обозначающая конец формулы.

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

СУММЕСЛИМН(SUMIFS)

Разбивка формулы:

  1. =SUMIFS( – Знак “=” снова указывает на начало формулы.
  2. E2:E16 — Относится(Refers) к диапазону данных, которые мы хотели бы суммировать, т . е. к цене продажи(sale price) в нашем примере.
  3. D2:D16 — Относится(Refers) к диапазону данных, чтобы проверить, удовлетворяют ли они критериям для включения в общую сумму.
  4. «США»(“USA”) — критерии(Criteria) поиска в указанном диапазоне данных(data range) ( D2:D16 ) .
  5. ) – закрывающая скобка(Closing bracket) , обозначающая конец формулы.

Формула показывает общий объем продаж товаров, отгруженных со склада в США , на (USA warehouse)$6,050 . США .

СРЕДНИЕСЛИ(AVERAGEIFS)

Разбивка формулы:

  1. =AVERAGEIFS( — Знак “=” указывает на начало формулы.
  2. E2:E16 — Относится(Refers) к диапазону данных, которые мы хотели бы усреднить. В этом примере мы хотим получить среднюю сумму продаж для всех телефонов, проданных в США(USA) .
  3. D2:D16 — Относится(Refers) к диапазону данных, чтобы проверить, удовлетворяют ли они критериям, которые должны быть включены в формулу среднего значения.
  4. «США»(“USA”) — критерии(Criteria) поиска в указанном диапазоне данных(data range)
  5. ) – закрывающая скобка(Closing bracket) , обозначающая концы формулы.

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

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

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

=COUNTIFS(D2:D16,"USA", B2:B16,"Samsung")

=SUMIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")

=AVERAGEIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")

Вы заметите, что в Excel также есть  формулы СЧЁТЕСЛИ(COUNTIF) , СУММЕСЛИ(AVERAGEIF) и СРЗНАЧЕСЛИ(SUMIF) без суффикса «S» . Они используются аналогично COUNTIFS , SUMIFS и AVERAGEIFS . Однако те, у кого нет суффикса «S» в формуле, имеют ограничение, позволяющее разрешать только один критерий в формуле.

Поскольку синтаксис немного отличается, я бы рекомендовал использовать  СЧЁТЕСЛИМН(COUNTIFS) , СУММЕСЛИМН(SUMIFS) и СРЗНАЧЕСЛИМН(AVERAGEIFS) , поскольку их можно использовать для одного или нескольких критериев, если это необходимо. Наслаждаться!



Елена Булгакова

About the author

Я инженер-программист и эксперт по Windows 10. У меня более двух лет опыта работы со смартфонами, Windows 10 и Microsoft Edge. Моя основная задача — сделать так, чтобы ваши устройства работали лучше и быстрее. Я работал над различными проектами для таких компаний, как Verizon, IMac, HP, Comcast и многих других. Я также являюсь сертифицированным инструктором по обучению работе с облачными технологиями Microsoft Azure.


Related posts