В этой статье описаны синтаксис формулы и использование функции DAVERAGE в Microsoft Excel.
Описание
Усредняет значения в поле (столбце) записей списка или базы данных, удовлетворяющие заданным условиям.
Синтаксис
ДСРЗНАЧ(база_данных;поле;условия)
Аргументы функции ДСРЗНАЧ описаны ниже.
- База данных — это диапазон ячеек, составляющих список или базу данных. База данных представляет собой список связанных данных, в котором строки данных являются записями, а столбцы — полями. Первая строка списка содержит заголовки всех столбцов.
- Поле указывает, какой столбец используется в функции. Введите текст с заголовком столбца в двойных кавычках, например "Возраст" или "Урожай", или число (без кавычек), задающее положение столбца в списке: 1 — для первого столбца, 2 — для второго и т. д.
- Условие — это диапазон ячеек, содержащий заданные условия. В качестве аргумента "условия" можно использовать любой диапазон, который содержит хотя бы один заголовок столбца и хотя бы одну ячейку с условием, расположенную под заголовком столбца.
Замечания
- В качестве аргумента "условия" можно использовать любой диапазон, который содержит хотя бы один заголовок столбца и хотя бы одну ячейку с условием, расположенную под заголовком столбца.
Например, если интервал G1:G2 содержит заголовок столбца "Доход" в ячейке G1 и значение 10 000 в ячейке G2, можно определить интервал "СоответствуетДоходу" и использовать это имя как аргумент "условия" в функции баз данных. - Несмотря на то, что диапазон условий может находиться в любом месте листа, не следует помещать его под списком. Это связано с тем, что данные, добавляемые в список, вставляются в первую строку после списка. Если эта строка уже содержит данные, Excel не сможет добавить новые данные в список.
- Диапазон условий не должен перекрываться со списком.
- Чтобы выполнить операцию над целым столбцом базы данных, вставьте пустую строку под строкой заголовков столбцов в диапазоне условий.
Примеры
Скопируйте образец данных из следующей таблицы и вставьте его в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
| Дерево | Высота | Возраст | Урожай | Доход | Высота |
|---|---|---|---|---|---|
| =Яблоня | >10 | <16 | |||
| =Груша | |||||
| Дерево | Высота | Возраст | Урожай | Доход | |
| Яблоня | 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 отобразил знак равенства, не используя его в вычислениях. Например:
=''=entry'' '
Где запись — это текст или значение, которое вы хотите найти. Например:
| Вводится в ячейку | Вычисляется и отображается |
|---|---|
| ="=Егоров" | =Егоров |
| ="=3000" | =3000 |
- При фильтрации текстовых данных в Excel не учитывается регистр букв. Однако для поиска с учетом регистра можно воспользоваться формулой. Пример см. далее в этой статье в разделе Фильтрация текста с использованием поиска с учетом регистра.
Примеры сложных условий отбора приведены в следующих разделах.
Несколько условий в одном столбце
Логическая логика: (Salesperson = "Davolio" OR Salesperson = "Buchanan")
Чтобы найти строки, отвечающие нескольким условиям для одного столбца, введите условия непосредственно одно под другим в разных строках диапазона условий.
В диапазоне данных (A6:C10) диапазон условий (B1:B3) отображает строки, содержащие значения "Егоров" или "Грачев" в столбце "Продавец" (A8:C10).
| A | B | В | |
|---|---|---|---|
| 1 | Тип | Продавец | Продажи |
| 2 | =Егоров | ||
| 3 | =Грачев | ||
| 4 | |||
| 5 | |||
| 6 | Тип | Продавец | Продажи |
| 7 | Напитки | Шашков | 5 122 ₽ |
| 8 | Мясо | Егоров | 450 р. |
| 9 | Фрукты | Грачев | 6 328 р. |
| 10 | Фрукты | Егоров | 6 544 р. |
Несколько условий в нескольких столбцах, где все условия должны быть истинными
Логическая логика: (Type = "Produce" AND Sales > 1000)
Чтобы найти строки, удовлетворяющие нескольким условиям в нескольких столбцах, введите все условия в одной строке диапазона условий.
В диапазоне данных (A6:C10) диапазон условий (A1:C2) отображает все строки, содержащие слово "Фрукты" в столбце "Тип" и значения больше 1 000 р. в столбце "Продажи" (A9:C10).
| A | B | В | |
|---|---|---|---|
| 1 | Тип | Продавец | Продажи |
| 2 | =Фрукты | >1000 | |
| 3 | |||
| 4 | |||
| 5 | |||
| 6 | Тип | Продавец | Продажи |
| 7 | Напитки | Шашков | 5 122 ₽ |
| 8 | Мясо | Егоров | 450 р. |
| 9 | Фрукты | Грачев | 6 328 р. |
| 10 | Фрукты | Егоров | 6 544 р. |
Несколько условий в нескольких столбцах, где любые условия могут быть истинными
Логическая логика: (Type = "Produce" OR Salesperson = "Davolio")
Чтобы найти строки, удовлетворяющие нескольким условиям в нескольких столбцах, где любое условие может быть истинным, введите эти условия в разных строках диапазона условий.
В диапазоне данных (A6:C10) диапазон условий (A1:B3) отображает все строки, содержащие значение "Фрукты" в столбце "Тип" или значение "Егоров" в столбце "Продавец" (A8:C10).
| A | B | В | |
|---|---|---|---|
| 1 | Тип | Продавец | Продажи |
| 2 | =Фрукты | ||
| 3 | =Егоров | ||
| 4 | |||
| 5 | |||
| 6 | Тип | Продавец | Продажи |
| 7 | Напитки | Шашков | 5 122 ₽ |
| 8 | Мясо | Егоров | 450 р. |
| 9 | Фрукты | Грачев | 6 328 р. |
| 10 | Фрукты | Егоров | 6 544 р. |
Несколько наборов условий, где в каждом наборе имеются условия для нескольких столбцов
Логическая логика: ( (Salesperson = "Davolio" AND Sales >3000) OR (Salesperson = "Buchanan" AND Sales > 1500) )
Для поиска строк, отвечающих нескольким наборам условий, каждый из которых содержит условия для нескольких столбцов, введите каждый набор условий в отдельной строке.
В диапазоне данных (A6:C10) диапазон условий (B1:C3) отображает строки, содержащие фамилию "Егоров" в столбце "Продавец" и значение больше 3 000 р. в столбце "Продажи", или строки, содержащие фамилию "Грачев" в столбце "Продавец" и значение больше 1 500 р. в столбце "Продажи" (A9:C10).
| A | B | В | |
|---|---|---|---|
| 1 | Тип | Продавец | Продажи |
| 2 | =Егоров | >3000 | |
| 3 | =Грачев | >1500 | |
| 4 | |||
| 5 | |||
| 6 | Тип | Продавец | Продажи |
| 7 | Напитки | Шашков | 5 122 ₽ |
| 8 | Мясо | Егоров | 450 р. |
| 9 | Фрукты | Грачев | 6 328 р. |
| 10 | Фрукты | Егоров | 6 544 р. |
Несколько наборов условий, где в каждом наборе имеются условия для одного столбца
Логическая логика: ( (Продажи > 6000 И Продажи < 6500 ) OR (Продажи < 500) )
Чтобы найти строки, удовлетворяющие нескольким наборам условий, каждый из которых содержит условия для одного столбца, используйте несколько столбцов с одинаковым заголовком.
В диапазоне данных (A6:C10) диапазон условий (C1:D3) отображает строки, содержащие значения от 6 000 до 6 500 и значения меньше 500 в столбце "Продажи" (A8:C10).
| A | B | В | Г | |
|---|---|---|---|---|
| 1 | Тип | Продавец | Продажи | Продажи |
| 2 | >6000 | <6500 | ||
| 3 | <500 | |||
| 4 | ||||
| 5 | ||||
| 6 | Тип | Продавец | Продажи | |
| 7 | Напитки | Шашков | 5 122 ₽ | |
| 8 | Мясо | Егоров | 450 р. | |
| 9 | Фрукты | Грачев | 6 328 р. | |
| 10 | Фрукты | Егоров | 6 544 ₽ |
Условия отбора текстовых строк, содержащих определенные знаки
Чтобы найти текстовые значения с совпадающими знаками в некоторых из позиций, выполните одно или несколько действий, описанных ниже.
- Для поиска строк, в которых текстовое значение в столбце начинается с определенной последовательности знаков, введите эти знаки, не используя знак равенства. Например, если ввести условие Бел, будут отобраны строки с ячейками, содержащими слова "Белов", "Беляков" и "Белугин".
- Воспользуйтесь подстановочными знаками.
В условии сравнения можно использовать подстановочные знаки, указанные ниже.
| Используйте | Чтобы найти |
|---|---|
| ? (вопросительный знак) | Любой символ Пример: условию "стро?а" соответствуют результаты "строфа" и "строка" |
| Звездочка (*) | Любое количество символов Пример: условию "*-восток" соответствуют результаты "северо-восток" и "юго-восток" |
| ~ (тильда), за которой следует ?, * или ~ | Вопросительный знак, звездочка или тильда Например, fy91~? соответствует результат "ан91?" |
В диапазоне данных (A6:C10) диапазон условий (A1:B3) отображает строки, которые в столбце "Тип" содержат текст, начинающийся на "Мя", или в столбце "Продавец" содержат текст, вторая буква которого — "л" (A7:C9).
| A | B | В | |
|---|---|---|---|
| 1 | Тип | Продавец | Продажи |
| 2 | Я | ||
| 3 | =?л* | ||
| 4 | |||
| 5 | |||
| 6 | Тип | Продавец | Продажи |
| 7 | Напитки | Шашков | 5 122 р. |
| 8 | Мясо | Егоров | 450 р. |
| 9 | Фрукты | Грачев | 6 328 р. |
| 10 | Фрукты | Егоров | 6 544 р. |
Условия, полученные в результате применения формулы
В качестве условия можно использовать значение, вычисленное с помощью формулы. Обратите внимание на важные моменты, указанные ниже.
- Формула должна возвращать результат ИСТИНА или ЛОЖЬ.
- Поскольку используется формула, введенное строковое выражение должно иметь обычный вид, а не тот, который показан ниже:
=''=entry'' ' - Не используйте заголовок столбца в качестве заголовка условия. Либо оставьте заголовок условия пустым, либо используйте заголовок, не являющийся заголовком одного из столбцов диапазона (в примерах ниже "Среднее арифметическое" и "Точное совпадение").
Если вы используете метку столбца в формуле вместо ссылки на относительную ячейку или имя диапазона, Excel отображает значение ошибки, например #NAME? или #VALUE! #ЗНАЧ!. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации. - Формула, используемая для условий, должна использовать относительную ссылку для ссылки на соответствующую ячейку в первой строке (в приведенных ниже примерах C7 и A7).
- Все остальные ссылки в формуле должны быть абсолютными.
В следующих подразделах приведены примеры условий, полученных в результате применения формулы.
Фильтрация значений, превышающих среднее значение диапазона данных
В диапазоне данных (A6:D10) диапазон условий (D1:D2) отображает строки со значениями в столбце "Продажи", превышающими среднее арифметическое всех значений этого столбца (C7:C10). В этой формуле "C7" является ссылкой на отфильтрованный столбец (C) первой строки диапазона данных (7).
| A | B | В | Г | |
|---|---|---|---|---|
| 1 | Тип | Продавец | Продажи | Среднее арифметическое |
| 2 | =C7>AVERAGE($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 | В | Г | |
|---|---|---|---|---|
| 1 | Тип | Продавец | Продажи | Точное совпадение |
| 2 | =СОВПАД(A7;"Фрукты") | |||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 | Тип | Продавец | Продажи | |
| 7 | Напитки | Шашков | 5 122 ₽ | |
| 8 | Мясо | Егоров | 450 р. | |
| 9 | Фрукты | Грачев | 6 328 ₽ | |
| 10 | Фрукты | Егоров | 6 544 ₽ |