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

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