В этой статье мы узнаем, как получить среднее значение по нескольким критериям в 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. Нам нужно найти СРЕДНЕЕ количество, полученное по различным критериям.

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

  1. Дата должна быть до 31.01.2019.

  2. Город «Бостон»

  3. Продукт обозначен как «Морковь»

  4. Количество должно быть больше 50 (> 50).

image

Необходимо согласовать 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 Пояснение:

  1. order_date, «⇐» & F4 * первый критерий, который предлагает дату до 31.01.2019 в F4.

  2. город, «Бостон» — второй критерий, в котором город соответствует «Бостону»

  3. продукт, F5, где продукт соответствует моркови в ячейке F5. Количество должно соответствовать критериям в ячейке F6, т.е. больше 50.

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

image

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

Пример 2:

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

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

  1. Дата должна быть до 27.02.2019.

  2. Город «Нью-Йорк»

  3. Продукт можно определить как «Шоколадная крошка».

  4. Количество должно быть меньше 50 (<50).

image

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

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

=

AVERAGEIFS

( Quantity , order_date , «⇐» & F4 , city , «New York» , product , F5 ,

Quantity , F6 )

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

Пояснение:

  1. order_date, «⇐» & F4 * первый критерий, который предлагает дату до 27.02.2019 в ячейке F4.

  2. город, «Бостон» — второй критерий, в котором город соответствует «Бостону»

  3. продукт, F5, где продукт соответствует моркови в ячейке F5. Количество должно соответствовать критериям в ячейке F6, то есть меньше 50.

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

image

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

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

Примечания:

  1. Функция возвращает # ЗНАЧ! ошибка, если аргумент функции не является числовым.

  2. Функция возвращает # ЗНАЧ! ошибка, если ни одно значение не соответствует заданным критериям.

  3. Формула принимает такие операторы, как =, <, ⇐,> =, ⇐ & <>.

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

  5. Функция возвращает # ЗНАЧ! критерии, если ни одно значение не соответствует заданным критериям.

  6. Допускаются различные критерии внутри формулы, например, равно (=), меньше (<), меньше или равно (⇐), больше или равно (> =), больше (>) и не равно (<>).

Таким образом, мы узнали, как найти среднее значение на основе нескольких критериев в Microsoft Excel 2010. Вы можете использовать эти функции в Excel 2016, 2013 и 2010. Найдите больше статей о математических формулировках с различными критериями. Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected]

Похожие статьи

link: / logic-formulas-how-to-highlight-cells-above-and-below-average-value [Как выделить ячейки выше и ниже среднего значения]: выделить значения, которые выше или ниже среднего значения, используя условное форматирование в Excel.

link: / statistics-formulas-ignore-zero-in-the-average-of-numbers [Игнорировать ноль в среднем числе чисел]: вычислить среднее значение чисел в массиве, игнорируя нули, с помощью функции AVERAGEIF в Excel.

link: / Mathematical-functions-calculate-weighted-average [Рассчитать средневзвешенное значение]: найти среднее значение значений, имеющих разный вес, с помощью функции СУММПРОИЗВ в Excel.

link: / excel-array-formulas-Расчет-средней-разницы-между-прейскурантной-ценой-и-продажной-ценой [Средняя разница между списками]: вычислить разницу в среднем для двух разных списков. Узнайте больше о том, как рассчитать среднее значение с помощью основной математической формулы среднего.

link: / Mathematical-functions-average-numbers-if-not-blank-in-excel [Средние числа, если не пусто в Excel]: извлечь среднее значение, если ячейка не пуста в Excel.

link: / Mathemat-functions-average-of-top-3-score-in-a-list-in-excel [СРЕДНЕЕ из 3 лучших оценок в списке в excel]: Найдите среднее значение чисел с самым высоким критерием 3 числа из списка в Excel

Популярные статьи:

link: / tips-if-condition-in-excel [Как использовать функцию IF в Excel]: оператор IF в Excel проверяет условие и возвращает конкретное значение, если условие истинно, или другое конкретное значение, если оно ложно .

link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию ВПР в Excel]: Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листы.

link: / tips-countif-in-microsoft-excel [Как использовать функцию СЧЁТЕСЛИ в Excel]: Подсчет значений с условиями с помощью этой удивительной функции. Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.

link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]: Это еще одна важная функция информационной панели. Это поможет вам суммировать значения для конкретных условий.