Функция ДСРЗНАЧ для выборки средних значений по условию в Excel

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

Примеры выборки средних значений по нескольким условиям в Excel

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

Пример 1. В таблице, оформленной как БД, находятся сведения о сигаретах: наименование бренда, количественное содержание никотина и стоимость. Определить среднюю цену:

  1. Сигарет, содержание никотина в которых менее 8 мг.
  2. Всех сигарет, кроме марки «Прима».

Вид таблицы данных:

Пример 1.

Над БД создадим таблицу критериев, а также таблицу для вывода полученных значений:

.

Для определения среднего значения цены сигарет, содержащих менее 8 мг никотина, добавим условие "<8" в ячейку C3.

В ячейке C5 запишем следующую формулу:

Описание аргументов:

  • A12:D27 – диапазон ячеек, в которых находится БД;
  • D2 – ссылка на ячейку, содержащую название поля, по данным которого будет выполнен расчет среднего значения;
  • A2:D3 – диапазон ячеек, в котором находится таблица условий.

Полученный результат:

.

Удалим значение из C3, а в B2 введем ="<>Прима". Для нахождения второй неизвестной величины по условию задачи в ячейку C6 введем формулу:

Полученное значение:

.

В итоге получаем результат выборки по двум условиям.



Критерии выборки среднего значения из базы данных в Excel

Пример 2. Используя таблицу из примера 1 вычислить следующие значения:

  1. Среднюю цену сигарет, исключив отечественные марки.
  2. Среднее содержание никотина, округленное по правилам округления чисел, для сигарет стоимостью от 12 до 15 у.е. не включительно.

Для определения средней цены по первому условию создадим следующую таблицу критериев:

.

В ячейку B5 введем формулу:

Полученный результат:

.

Аналогичным способом дублирования столбцов для введения нескольких критериев для одного поля создадим следующую таблицу условий.

Для расчета используем формулу:

В результате получим:

.

Альтернативный вариант расчета с использование других функций для работы с БД в Excel:

.

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

Принципы работы функции ДСРЗНАЧ в Excel

Функция имеет следующую синтаксическую запись:

=ДСРЗНАЧ(база_данных;поле;условия)

Описание аргументов:

  • база – обязательный, принимает ссылку на диапазон ячеек, в которых могут находиться база данных, список либо таблица, оформленная в соответствии с правилами оформления БД в Excel;
  • поле – обязательный, принимает текстовую строку с названием поля (столбца таблицы), в котором требуется найти среднее значение содержащихся величин, либо ссылку на ячейку, содержащую название этого поля, или числовое значение N, характеризующее номер поля в БД (порядковый номер столбца в таблице, начиная от крайнего левого);
  • условия – обязательный, принимает ссылку на диапазон ячеек, в которых указаны условия отбора данных для дальнейшего расчета среднего значения.

Примечания:

  1. Если в качестве аргумента поле указано число, соответствующее номеру поля в БД, оно должно быть взято из диапазона [1;N], где N – порядковый номер последнего столбца. Если число взято из вне диапазона допустимых значений, функция ДСРЗНАЧ вернет код ошибки #ЗНАЧ!
  2. Для указания аргумента условия следует создать отдельную таблицу, содержащую те же поля, что и исследуемая БД. Минимальные требования к таблице условий: наличие хотя бы одного именованного столбца, под которым содержится хотя бы одна ячейка с условием.
  3. Таблицу с условиями размещают над БД. Размещение под БД недопустимо во избежание пересечения данных по мере добавления новых записей (строк).
  4. Если требуется выполнить расчет среднего значения в отношении всех записей, хранящихся в БД, таблица условий должна содержать пустые ячейки под строкой с наименованиями полей.

en ru