У цій статті наведено синтаксис формули й описано використання функції DAVERAGE у програмі Microsoft Excel.
Опис
Усереднює значення в полі (стовпці) записів у списку або базі даних, які відповідають заданим умовам.
Синтаксис
DAVERAGE(база_даних;поле;умови)
Синтаксис функції DAVERAGE має такі аргументи:
-
База даних – це діапазон клітинок, які утворюють список або базу даних. База даних – це список пов’язаних даних, у якому рядки пов’язаних відомостей є записами, а стовпці даних – полями. Перший рядок списку містить підписи для всіх стовпців.
-
Поле – визначає, який стовпець використовуватиметься у функції. Введіть підпис стовпця в подвійних лапках, наприклад "Вік" або "Урожай". Можна також ввести число (без лапок), яке відповідає номеру стовпця у списку: 1 – для першого стовпця, 2 – для другого тощо.
-
Умови – діапазон клітинок, який містить указані користувачем умови. Можна вказувати будь-який діапазон для аргументу ''Умови'', якщо він містить принаймні один підпис стовпця та принаймні одну клітинку під цим підписом, у якій вказано умову для відповідного стовпця.
Примітки
-
Можна вказувати будь-який діапазон для аргументу «Умови», якщо він містить принаймні один підпис стовпця та принаймні одну клітинку під цим підписом, у якій вказано умови для відповідного стовпця.
Наприклад, якщо діапазон G1:G2 містить підпис стовпця ''Прибуток'' у G1 і суму 10 000 грн. у G2, можна визначити діапазон як ''ЗбігПрибутку'' й використовувати це ім’я як значення аргументу ''Умови'' у функціях бази даних.
-
Хоча діапазон умов може бути розташовано в довільному місці аркуша, не розташовуйте діапазон умов під списком. У разі додавання нової інформації до списку її буде додано до першого рядка під списком. Якщо рядок під списком не пустий, програма Excel не зможе додати нову інформацію.
-
Переконайтеся, що діапазон умов не перекриває список.
-
Щоб виконати операцію над усім стовпцем бази даних, введіть пустий рядок під підписами стовпців у діапазоні умов.
Приклади
Скопіюйте дані прикладу з наведеної нижче таблиці та вставте їх у клітинку A1 нового аркуша Excel. Щоб відобразити результат обчислення формул, виберіть їх, натисніть клавішу F2, а потім – клавішу Enter. За потреби можна змінити ширину стовпців, щоб відобразити всі дані.
Дерево |
Висота |
Вік |
Урожай |
Прибуток |
Висота |
---|---|---|---|---|---|
=Яблуня |
>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 |
|
Формула |
Опис |
Результат |
|||
=DAVERAGE(A4:E10, "Урожай", A1:B2) |
Середній урожай яблунь, висота яких більше 10. |
12 |
|||
=DAVERAGE(A4:E10, 3, A4:E10) |
Середній вік усіх дерев у базі даних. |
13 |
Приклади умов
-
Введення знаку рівності в клітинку означає, що ви збираєтеся ввести формулу. Щоб відобразити текст, який містить знак рівності, цей текст разом зі знаком рівності необхідно огорнути подвійними лапками:
="=Давидова"
Це також можна зробити, якщо ви вводите вираз (комбінацію формул, операторів і тексту), і хочете відобразити знак рівності, а додаток Excel не використовує його в розрахунку. Приклад:
=''= запис ''
де запис – це текст або значення, які слід знайти. Наприклад:
Дані, що вводяться у клітинку |
Програма Excel визначає та відображає |
---|---|
="=Давидова" |
=Давидова |
="=3 000" |
=3 000 |
-
Фільтруючи текстові дані, Excel не розрізняє великі та малі букви. Проте, пошук виразу з урахуванням регістру можна виконати за допомогою формули. Приклад такого пошуку наведено далі в розділі Фільтрування тексту за допомогою пошуку з урахуванням регістру.
Нижче надано приклади складних умов.
Кілька умов в одному стовпці
Логічний вираз: (Торговий представник = "Давидова" АБО Торговий представник = "Пустовіт")
Щоб знайти рядки, які відповідають кільком умовам для одного стовпця, введіть умови безпосередньо одну під одною в окремі рядки діапазону умов.
У наведеному нижче діапазоні даних (A6:C10) діапазон умов (B1:B3) відображає рядки, які містять ''Давидова'' або ''Пустовіт'' у стовпці ''Торговий представник'' (A8:C10).
|
A |
B |
C |
---|---|---|---|
1 |
Тип |
Продавець |
Продаж, грн. |
2 |
=Давидова |
||
3 |
=Пустовіт |
||
4 |
|||
5 |
|||
6 |
Тип |
Продавець |
Продаж, грн. |
7 |
Напої |
Семенів |
5122 грн. |
8 |
М’ясо |
Давидова |
450 грн. |
9 |
продукти |
Пустовіт |
6328 грн. |
10 |
Овочі |
Давидова |
6 544 грн. |
Кілька умов для кількох стовпців, для всіх умов обов’язкове значення «істина»
Логічний вираз: (Тип = "Овочі" І Продаж, грн. > 1000)
Щоб знайти рядки, які відповідають кільком умовам у кількох стовпцях, введіть усі умови в один рядок діапазону умов.
У наведеному нижче діапазоні даних (A6:C10) діапазон умов (A1:C2) відобразить усі рядки, які містять ''Овочі'' у стовпці ''Тип'' і значення, більші за 1 000 грн. у стовпці ''Продаж'' (A9:C10).
|
A |
B |
C |
---|---|---|---|
1 |
Тип |
Продавець |
Продаж, грн. |
2 |
=Овочі |
>1 000 |
|
3 |
|||
4 |
|||
5 |
|||
6 |
Тип |
Продавець |
Продаж, грн. |
7 |
Напої |
Семенів |
5122 грн. |
8 |
М’ясо |
Давидова |
450 грн. |
9 |
продукти |
Пустовіт |
6328 грн. |
10 |
Овочі |
Давидова |
6 544 грн. |
Кілька умов для кількох стовпців, будь-яка умова може мати значення «істина»
Логічний вираз: (Тип = "Овочі" АБО Продавець = "Давидова")
Щоб знайти рядки, які відповідають кільком умовам у кількох стовпцях, коли будь-яка умова може бути істиною, введіть умови в різних рядках діапазону умов.
У наведеному нижче діапазоні даних (A6:C10) діапазон умов (A1:B3) відображає всі рядки, які містять ''Овочі'' у стовпці ''Тип'' або ''Давидова'' у стовпці ''Торговий представник'' (A8:C10).
|
A |
B |
C |
---|---|---|---|
1 |
Тип |
Продавець |
Продаж, грн. |
2 |
=Овочі |
||
3 |
=Давидова |
||
4 |
|||
5 |
|||
6 |
Тип |
Продавець |
Продаж, грн. |
7 |
Напої |
Семенів |
5122 грн. |
8 |
М’ясо |
Давидова |
450 грн. |
9 |
продукти |
Пустовіт |
6328 грн. |
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 |
Напої |
Семенів |
5122 грн. |
8 |
М’ясо |
Давидова |
450 грн. |
9 |
продукти |
Пустовіт |
6328 грн. |
10 |
Овочі |
Давидова |
6 544 грн. |
Кілька наборів умов, кожний набір містить умови для одного стовпця
Логічний вираз: ( (Продаж, грн > 6000 І Продаж, грн < 6500) АБО (Продаж, грн < 500) )
Щоб знайти рядки, які відповідають кільком наборам умов, в яких кожний набір містить умови для одного стовпця, включіть кілька стовпців з одним заголовком.
У наведеному нижче діапазоні даних (A6:C10) діапазон умов (C1:D3) відображає рядки, які містять значення між 6 000 і 6 500 і значення, менші за 500 у стовпці ''Продаж'' (A8:C10).
|
A |
B |
C |
Г |
---|---|---|---|---|
1 |
Тип |
Продавець |
Продаж, грн. |
Продаж, грн. |
2 |
>6 000 |
<6 500 |
||
3 |
<500 |
|||
4 |
||||
5 |
||||
6 |
Тип |
Продавець |
Продаж, грн. |
|
7 |
Напої |
Семенів |
5122 грн. |
|
8 |
М’ясо |
Давидова |
450 грн. |
|
9 |
продукти |
Пустовіт |
6328 грн. |
|
10 |
Овочі |
Давидова |
6544 грн. |
Умови для пошуку текстових значень із певними однаковими символами
Щоб знайти текстові значення, що містять кілька спільних символів (але не всі), виконайте одну або кілька таких дій:
-
Введіть один або кілька символів без знака рівності (=), щоб знайти рядки з текстовим значенням у стовпці, який починається цими символами. Наприклад, якщо використати текст Дав як умову, програма Excel знайде "Давидова", "Давид" і "Давиденко".
-
Використайте символи узагальнення.
Як умови порівняння можна застосовувати такі символи узагальнення:
Використовуйте |
Щоб знайти |
---|---|
? (знак питання) |
Будь-який окремий символ. Наприклад, умові «ма?ка» відповідають результати «мавка» та «марка». |
* (зірочка) |
Будь-який набір символів. Наприклад, умові «пів*» відповідають результати «південь» і «північ». |
~ (тильда) зі знаком ?, * або ~ в кінці |
Знак питання, зірочку або тильду. Наприклад, за умовою "фр91~?" буде знайдено "фр91?". |
У наведеному нижче діапазоні даних (A6:C10) діапазон умов (A1:B3) відображає рядки, у яких першими символами у стовпці ''Тип'' є ''М’я'', і рядки, де у стовпці ''Торговий представник'' є символ ''в'' (A7:C9).
|
A |
B |
C |
---|---|---|---|
1 |
Тип |
Продавець |
Продаж, грн. |
2 |
М’я |
||
3 |
=?в* |
||
4 |
|||
5 |
|||
6 |
Тип |
Продавець |
Продаж, грн. |
7 |
Напої |
Семенів |
5122 грн. |
8 |
М’ясо |
Давидова |
450 грн. |
9 |
продукти |
Пустовіт |
6328 грн. |
10 |
Овочі |
Давидова |
6544 грн. |
Умови, утворені як результат обчислення формули
Обчислюване значення, отримане як результат формула, можна використовувати як умову. Слід пам’ятати про такі важливі моменти:
-
Формула має повертати результат TRUE або FALSE.
-
Оскільки використовується формула, потрібно вводити її звичайним способом. Не вводьте її як вираз, тобто:
=''= запис ''
-
Не використовуйте заголовок стовпця як заголовок умови; залиште умову без заголовка або використайте заголовок, який не є заголовком стовпця в діапазоні (у нижченаведених прикладах, «Обчислене середнє значення» та «Точна відповідність»).
Якщо замість відносного посилання на клітинку або імені діапазону вказати підпис стовпця, Excel відобразить значення помилки, таке як #NAME? або #VALUE у клітинці, яка містить умову. Ця помилка не критична, оскільки не впливає на фільтрування діапазону.
-
У формулі, яка використовується для створення умов, має застосовуватися відносне посилання на відповідну клітинку в першому рядку (у наведеному нижче прикладі – C7 і A7).
-
Решта посилань у формулі мають бути абсолютні.
У наступних підрозділах наведено конкретні приклади умов, утворених як результат формули.
Фільтрування для значень, більших за середнє всіх значень діапазону даних
У діапазоні даних (A6:D10) діапазон умов (D1:D2) відображає рядки, які у стовпці ''Продажі'' мають значення, більші за середнє всіх значень ''Продажі'' (C7:C10). У формулі значення ''C7'' є посиланням на відфільтрований стовпець (C) для першого рядка діапазону даних (7).
|
A |
B |
C |
Г |
---|---|---|---|---|
1 |
Тип |
Продавець |
Продаж, грн. |
Обчислене середнє значення |
2 |
=C7>AVERAGE($C$7:$C$10) |
|||
3 |
||||
4 |
||||
5 |
||||
6 |
Тип |
Продавець |
Продаж, грн. |
|
7 |
Напої |
Семенів |
5122 грн. |
|
8 |
М’ясо |
Давидова |
450 грн. |
|
9 |
продукти |
Пустовіт |
6328 грн. |
|
10 |
Овочі |
Давидова |
6 544 грн. |
Фільтрування тексту за допомогою пошуку з урахуванням регістру
У діапазоні даних (A6:D10) діапазон умов (D1:D2) відображає рядки, які містять ''Овочі'' у стовпці ''Тип'' за допомогою функції EXACT, що виконує пошук з урахуванням регістра (A10:C10). У формулі ''А7'' відповідає стовпцю, за яким проводиться фільтрування (А), першого рядка діапазону даних (7).
|
A |
B |
C |
Г |
---|---|---|---|---|
1 |
Тип |
Продавець |
Продаж, грн. |
Точний збіг |
2 |
=EXACT(A7;"Овочі") |
|||
3 |
||||
4 |
||||
5 |
||||
6 |
Тип |
Продавець |
Продаж, грн. |
|
7 |
Напої |
Семенів |
5122 грн. |
|
8 |
М’ясо |
Давидова |
450 грн. |
|
9 |
продукти |
Пустовіт |
6328 грн. |
|
10 |
Овочі |
Давидова |
6 544 грн. |