Функция ДСРЗНАЧ

В этой статье описаны синтаксис формулы и использование функции ДСРЗНАЧ в Microsoft Excel.

Описание

Усредняет значения в поле (столбце) записей списка или базы данных, удовлетворяющие заданным условиям.

Синтаксис

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

Аргументы функции ДСРЗНАЧ описаны ниже.

База_данных — интервал ячеек, образующих список или базу данных. База данных представляет собой список связанных данных, в котором строки данных являются записями, а столбцы — полями. Первая строка списка содержит заголовки всех столбцов.
Поле — столбец, используемый функцией. Введите текст с заголовком столбца в кавычках, например "Возраст" или "Урожай", либо число (без кавычек), задающее положение столбца в списке: 1 — для первого столбца, 2 — для второго и т. д.
Условия — диапазон ячеек, который содержит задаваемые условия. В качестве аргумента "условия" можно использовать любой диапазон, который содержит по крайней мере один заголовок столбца и по крайней мере одну ячейку с условием, расположенную под заголовком столбца.

Замечания

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

Например, если интервал G1:G2 содержит заголовок столбца "Доход" в ячейке G1 и значение 10 000 в ячейке G2, можно определить интервал "СоответствуетДоходу" и использовать это имя как аргумент "условия" в функции баз данных.
Несмотря на то, что диапазон условий может находиться в любом месте листа, не следует помещать его под списком. Это связано с тем, что данные, добавляемые в список, вставляются в первую строку после списка. Если эта строка уже содержит данные, Excel не сможет добавить новые данные в список.
Диапазон условий не должен перекрываться со списком.
Чтобы выполнить операцию над целым столбцом базы данных, вставьте пустую строку под строкой заголовков столбцов в диапазоне условий.

Примеры

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу Enter. При необходимости измените ширину столбцов, чтобы видеть все данные.

Дерево	Высота	Возраст	Урожай	Доход	Высота
=Яблоня	>3				<5
=Груша
Дерево	Высота	Возраст	Урожай	Доход
Яблоня	18	20	14	105
Груша	12	12	10	96
Вишня	13	14	9	105
Яблоня	14	15	10	75
Груша	9	8	8	76,8
Яблоня	8	9	6	45
Формула	Описание	Результат
=ДСРЗНАЧ(A4:E10;"Урожай";A1:B2)	Средний урожай яблонь высотой более 3 метров.	12
=ДСРЗНАЧ(A4:E10;3;A4:E10)	Средний возраст всех деревьев в базе данных.	13

Примеры условий

Для ввода формулы в ячейку необходимо сначала ввести знак равенства. Чтобы отобразить текст, включающий в себя знак равенства, поставьте текст со знаком равенства в кавычки, как показано далее:

"=Егоров"

Это же действие нужно выполнить при вводе выражения (комбинации формул, операторов и текста), если вам необходимо, чтобы Excel отобразил знак равенства, не используя его в вычислениях. Например:

=''= ввод ''

где ввод — искомый текст или значение. Например:

Вводится в ячейку	Вычисляется и отображается
="=Егоров"	=Егоров
="=3000"	=3000

При фильтрации текстовых данных в Excel не учитывается регистр букв. Однако для поиска с учетом регистра можно воспользоваться формулой. Пример см. далее в этой статье в разделе Фильтрация текста с использованием поиска с учетом регистра.

Примеры сложных условий отбора приведены в следующих разделах.

Несколько условий в одном столбце

Логическое выражение: (Продавец = "Егоров" ИЛИ Продавец = "Грачев")

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

В диапазоне данных (A6:C10) диапазон условий (B1:B3) отображает строки, содержащие значения "Егоров" или "Грачев" в столбце "Продавец" (A8:C10).

	A	B	C
1	Тип	Продавец	Продажи
2		=Егоров
3		=Грачев
4
5
6	Тип	Продавец	Продажи
7	Напитки	Шашков	5 122 ₽
8	Мясо	Егоров	450 р.
9	Фрукты	Грачев	6 328 ₽
10	Фрукты	Егоров	6 544 р.

Несколько условий в нескольких столбцах, где все условия должны быть истинными

Логическое выражение: (Тип = "Фрукты" И Продажи > 1000)

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

В диапазоне данных (A6:C10) диапазон условий (A1:C2) отображает все строки, содержащие слово "Фрукты" в столбце "Тип" и значения больше 1 000 р. в столбце "Продажи" (A9:C10).

	A	B	C
1	Тип	Продавец	Продажи
2	=Фрукты		>1 000
3
4
5
6	Тип	Продавец	Продажи
7	Напитки	Шашков	5 122 ₽
8	Мясо	Егоров	450 р.
9	Фрукты	Грачев	6 328 ₽
10	Фрукты	Егоров	6 544 р.

Несколько условий в нескольких столбцах, где любые условия могут быть истинными

Логическое выражение: (Тип = "Фрукты" ИЛИ Продавец = "Егоров")

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

В диапазоне данных (A6:C10) диапазон условий (A1:B3) отображает все строки, содержащие значение "Фрукты" в столбце "Тип" или значение "Егоров" в столбце "Продавец" (A8:C10).

	A	B	C
1	Тип	Продавец	Продажи
2	=Фрукты
3		=Егоров
4
5
6	Тип	Продавец	Продажи
7	Напитки	Шашков	5 122 ₽
8	Мясо	Егоров	450 р.
9	Фрукты	Грачев	6 328 ₽
10	Фрукты	Егоров	6 544 р.

Несколько наборов условий, где в каждом наборе имеются условия для нескольких столбцов

Логическое выражение: ( (Продавец = "Егоров" И Продажи >3000) ИЛИ (Продавец = "Грачев" И Продажи > 1500) )

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

В диапазоне данных (A6:C10) диапазон условий (B1:C3) отображает строки, содержащие фамилию "Егоров" в столбце "Продавец" и значение больше 3 000 р. в столбце "Продажи", или строки, содержащие фамилию "Грачев" в столбце "Продавец" и значение больше 1 500 р. в столбце "Продажи" (A9:C10).

	A	B	C
1	Тип	Продавец	Продажи
2		=Егоров	>3 000
3		=Грачев	>1 500
4
5
6	Тип	Продавец	Продажи
7	Напитки	Шашков	5 122 ₽
8	Мясо	Егоров	450 р.
9	Фрукты	Грачев	6 328 ₽
10	Фрукты	Егоров	6 544 р.

Несколько наборов условий, где в каждом наборе имеются условия для одного столбца

Логическое выражение: ( (Продажи > 6000 И Продажи < 6500 ) ИЛИ (Продажи < 500) )

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

В диапазоне данных (A6:C10) диапазон условий (C1:D3) отображает строки, содержащие значения от 6 000 до 6 500 и значения меньше 500 в столбце "Продажи" (A8:C10).

	A	B	C	D
1	Тип	Продавец	Продажи	Продажи
2			>6 000	<6 500
3			<500
4
5
6	Тип	Продавец	Продажи
7	Напитки	Шашков	5 122 ₽
8	Мясо	Егоров	450 р.
9	Фрукты	Грачев	6 328 ₽
10	Фрукты	Егоров	6 544 ₽

Условия отбора текстовых строк, содержащих определенные знаки

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

Для поиска строк, в которых текстовое значение в столбце начинается с определенной последовательности знаков, введите эти знаки, не используя знак равенства. Например, если ввести условие Бел, будут отобраны строки с ячейками, содержащими слова "Белов", "Беляков" и "Белугин".
Воспользуйтесь подстановочными знаками.

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

Используйте	Чтобы найти
? (вопросительный знак)	Любой символ (один)Пример: условию "стро?а" соответствуют результаты "строфа" и "строка"
* (звездочка)	Любое количество символовПример: условию "*-восток" соответствуют результаты "северо-восток" и "юго-восток"
~ (тильда), за которой следует ?, * или ~	Вопросительный знак, звездочку или тильдуПример: условию "ан91~?" соответствует результат "ан91?"

В диапазоне данных (A6:C10) диапазон условий (A1:B3) отображает строки, которые в столбце "Тип" содержат текст, начинающийся на "Мя", или в столбце "Продавец" содержат текст, вторая буква которого — "л" (A7:C9).

	A	B	C
1	Тип	Продавец	Продажи
2	Я
3		=?л*
4
5
6	Тип	Продавец	Продажи
7	Напитки	Шашков	5 122 ₽
8	Мясо	Егоров	450 р.
9	Фрукты	Грачев	6 328 ₽
10	Фрукты	Егоров	6 544 р.

Условия, полученные в результате применения формулы

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

Формула должна возвращать результат ИСТИНА или ЛОЖЬ.
Поскольку используется формула, введенное строковое выражение должно иметь обычный вид, а не тот, который показан ниже:

=''= ввод ''
Не используйте заголовок столбца в качестве заголовка условия. Либо оставьте заголовок условия пустым, либо используйте заголовок, не являющийся заголовком одного из столбцов диапазона (в примерах ниже "Среднее арифметическое" и "Точное совпадение").

Если в формуле используется название столбца, а не относительная ссылка на ячейку или имя диапазона, в ячейке с условием будет выведено значение ошибки #ИМЯ? или #ЗНАЧ!. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.
В формуле, используемой для создания условия отбора, для ссылки на соответствующую ячейку в первой строке (в примерах ниже — C7 и A7) необходимо использовать относительная ссылка.
Все остальные ссылки в формуле должны быть абсолютными.

В следующих подразделах приведены примеры условий, полученных в результате применения формулы.

Фильтрация значений, превышающих среднее значение диапазона данных

В диапазоне данных (A6:D10) диапазон условий (D1:D2) отображает строки со значениями в столбце "Продажи", превышающими среднее арифметическое всех значений этого столбца (C7:C10). В этой формуле "C7" является ссылкой на отфильтрованный столбец (C) первой строки диапазона данных (7).

	A	B	C	D
1	Тип	Продавец	Продажи	Среднее арифметическое
2				=C7>СРЗНАЧ($C$7:$C$10)
3
4
5
6	Тип	Продавец	Продажи
7	Напитки	Шашков	5 122 ₽
8	Мясо	Егоров	450 р.
9	Фрукты	Грачев	6 328 ₽
10	Фрукты	Егоров	6 544 ₽

Фильтрация текста с использованием функции поиска с учетом регистра

В диапазоне данных (A6:D10) диапазон условий (D1:D2) отображает строки, содержащие значение "Фрукты" в столбце "Тип" после использования функции СОВПАД для поиска с учетом регистра (A10:C10). В этой формуле "A7" является ссылкой на отфильтрованный столбец (A) первой строки диапазона данных (7).

	A	B	C	D
1	Тип	Продавец	Продажи	Точное совпадение
2				=СОВПАД(A7;"Фрукты")
3
4
5
6	Тип	Продавец	Продажи
7	Напитки	Шашков	5 122 ₽
8	Мясо	Егоров	450 р.
9	Фрукты	Грачев	6 328 ₽
10	Фрукты	Егоров	6 544 ₽