Функция ДСРЗНАЧ() - Вычисление среднего с множественными условиями в MS EXCEL

Функция ДСРЗНАЧ(), английский вариант DAVERAGE(), вычисляет среднее значение в столбце, но только среди значений удовлетворяющих заданным условиям.

Синтаксис функции ДСРЗНАЧ() и ее использование аналогичны функции БДСУММ(). Т.е. для ДСРЗНАЧ() требуется чтобы:

  • исходная таблица имела заголовки столбцов;
  • критерии должны были оформлены в виде небольшой таблицы с заголовками;
  • заголовки таблицы критериев совпадали с заголовками исходной таблицы (если критерий не задается формулой).

Задача: Пусть дана таблица с названиями деревьев, в которой указан возраст деревьев, высота и урожайность.

Найдем средний урожай яблонь высотой более 10. Для этого используем функцию ДСРЗНАЧ(). Создадим таблицу критериев:

Итоговая формула будет выглядеть так:
=ДСРЗНАЧ(A11:D17;"Урожай";A5:B6)

Первый аргумент A11:D17 – ссылка на таблицу с названиями деревьев (база данных) с заголовками.
Второй аргумент "Урожай" – название столбца, значения которого нужно усреднять. Вместо названия столбца можно использовать его порядковый номер в исходной таблице (ссылка на нее указана в первом аргументе), т.е. 4.
=ДСРЗНАЧ(A11:D17;4;A5:B6)

Последний, третий аргумент A5:B6 – ссылка на таблицу с критериями. В пятой строке расположены заголовки столбцов, точно совпадающие с заголовками таблицы, к значениям которых применяются критерии. В ячейке А6 введено значение ="=Яблоня", т.е. будут усредняться только те значения из столбца «Урожай», которые соответствуют яблоням. А в ячейке В6 введено >10, т.е. будут выбираться строки не со всеми яблонями, а только у тех, у которых высота более 10.

Функция ДСРЗНАЧ() также позволяет использовать подстановочные знаки *, ? Например, чтобы усреднить только, урожай деревьев, длина названия которых равна 5 знаков (Груша и Вишня), необходимо в качестве критерия указать ="=?????". Формула для вычисления среднего в этом случае будет =ДСРЗНАЧ(A11:D17;4;E5:E6), в ячейках E5:E6 содержится критерий.

Функции ДСРЗНАЧ() имеется достойная альтернатива. Начиная с версии EXCEL 2007, в EXCEL имеются специальные «умные» таблицы (таблицы в формате EXCEL 2007) Вставка/ Таблицы/ Таблица.
Чтобы получить тот же результат (средний урожай яблонь высотой более 10) необходимо сделать следующее:

  • выделите любую ячейку исходной таблицы;
  • через меню Вставка/ Таблицы/ Таблица преобразуйте таблицу в новый формат;

  • нажмите ОК;
  • через меню Конструктор/ параметры стилей таблиц создайте строку итогов;
  • в выпадающем списке заголовка столбца Дерево выберите Яблоня;

  • в выпадающем списке заголовка столбца Высота выберите Числовые фильтры / больше …;

  • В появившемся окне введите 10 и нажмите ОК;
  • В строке итогов столбца Урожай через выпадающий список выберите Среднее.

Результат будет тот же самый – 12. Какой вариант лучше – выбирать Вам. Преимущество функции ДСРЗНАЧ() в том, что все критерии представлены в наглядной форме, размещены в отдельной таблице и их можно сохранить в любом месте листа для дальнейшего использования.

В файле примера приведены решения и других задач.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (3 оценок)