Средняя с несколькими критериями в Excel

Posted on: 2020年10月22日 2020年11月12日
Tags: mathematical-functions

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

Проблема?

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

Как решить проблему.

Теперь составим формулу из функции. Здесь нам даны данные, и нам нужно было найти СРЕДНЕЕ чисел, имеющих некоторые критерии

Общая формула:

= AVERAGEIFS ( Average_range , range1, criteria1, [ range2, criteria2 ], [ range3, criteria3 ], … )

Average_range: диапазон значений, для которых необходимо оценить среднее значение range1: первый диапазон, в котором применяется критерий1.

критерий1: первый критерий, примененный к диапазону1.
диапазон2: второй диапазон, в котором применяется критерий1.
критерии2: второй критерий, применяемый к диапазону 2.
диапазон3: третий диапазон, в котором применяется критерий1.
критерии3: третий критерий, применяемый к диапазону3.

Примечания: не указывайте дату непосредственно в функции. Используйте функцию ДАТА или используйте ссылку на ячейку для аргумента даты в Excel, поскольку Excel считывает дату только в правильном порядке.

Пример:

Все это может сбивать с толку. Итак, давайте протестируем эту формулу, запустив ее на примере, показанном ниже.

Здесь мы привели данные из A1: D51. Нам нужно найти СРЕДНЕЕ количество, полученное по различным критериям.

Условия следующие.

Дата должна быть до 31.01.2019.
Город «Бостон»
Продукт обозначен как «Морковь»
Количество должно быть больше 50 (> 50).

Необходимо согласовать 4 приведенных выше условия или критерия. Функция СРЗНАЧЕСЛИМН может помочь нам извлечь среднее значение по различным критериям.

Используйте формулу:

= AVERAGEIFS ( Quantity , order_date , "<=" & F4 , city , "Boston" , product , F5 , Quantity , F6 )

Количество: именованный диапазон, используемый для массива D2: D50 Order_date: именованный диапазон, используемый для массива A2: A50 City: именованный диапазон, используемый для массива B2: B50 Продукт: именованный диапазон, используемый для массива C2: C50 F4: ссылка на ячейку, используемая для критерия 1 F5: ссылка на ячейку, используемая для критерия 2 F6: ссылка на ячейку, используемая для критерия 3 Пояснение:

order_date, «⇐» & F4 * первый критерий, который предлагает дату до 31.01.2019 в F4.
город, «Бостон» — второй критерий, в котором город соответствует «Бостону»
продукт, F5, где продукт соответствует моркови в ячейке F5. Количество должно соответствовать критериям в ячейке F6, т. Е. Больше 50. Здесь A2 задается как ссылка на ячейку, а именованные диапазоны указаны как rng (D2: D51) и order_date (A2: A51).

Как вы можете видеть на снимке выше, СРЕДНЕЕ количество в январе составляет 54,00. Поскольку есть только одно значение, удовлетворяющее всем условиям.

Пример 2:

Здесь мы привели данные из A1: D51. Нам нужно найти СРЕДНЕЕ количество, полученное по различным критериям.

Условия следующие.

Дата должна быть до 27.02.2019.
Город «Нью-Йорк»
Продукт можно определить как «Шоколадная крошка».
Количество должно быть меньше 50 (<50).

Используйте формулу:

= AVERAGEIFS ( Quantity , order_date , "<=" & F4 , city , "New York" , product , F5 , Quantity , F6 )

order_date, «⇐» & F4 * первый критерий, который предлагает дату до 27.02.2019 в ячейке F4.
город, «Бостон» — второй критерий, в котором город соответствует «Бостону»
продукт, F5, где продукт соответствует моркови в ячейке F5. Количество должно соответствовать критериям в ячейке F6, то есть меньше 50.

Здесь A2 указывается как ссылка на ячейку, а именованные диапазоны — как rng (D2: D51) и order_date (A2: A51).

Как вы можете видеть на снимке выше, СРЕДНЕЕ количество в январе составляет 33,33 … Поскольку есть только одно значение, удовлетворяющее всем условиям.

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

Примечания:

Функция возвращает # ЗНАЧ! ошибка, если аргумент функции не является числовым.
Функция возвращает # ЗНАЧ! ошибка, если ни одно значение не соответствует заданным критериям.
Формула принимает такие операторы, как =, <, ⇐,> =, ⇐ & <>.
Не передавайте аргумент даты непосредственно функции. Используйте функцию ДАТА или используйте ссылку на ячейку для аргумента даты в Excel, поскольку Excel считывает дату только в правильном порядке.

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