У цій статті наведено синтаксис формули та описано, як у програмі Microsoft Excel використовувати функцію AVERAGEIFS .
Опис
Повертає середнє (середнє арифметичне) усіх клітинок, які відповідають кільком умовам.
Синтаксис
AVERAGEIFS(діапазон_усереднення; діапазон_умови1; умова1; [діапазон_умови2; умова2]; ...)
Синтаксис функції AVERAGEIFS має такі аргументи:
- Average_range Необхідні. Одна або кілька клітинок, які містять числа, імена, масиви або посилання на числа, для яких потрібно обчислити середнє значення.
- діапазон_умови1; діапазон_умови2; … Параметр "діапазон_умови1" обов’язковий, а наступні параметри "діапазони_умови" необов’язкові. Пов’язані умови виконуються в діапазонах від 1 до 127.
- Критерій1, критерій2, ... Умова1 обов'язкова, наступні умови необов'язкові. Умови від 1 до 127 у формі числа, виразу, посилання на клітинку або тексту визначають клітинки, у яких обчислюватиметься середнє значення. Наприклад, умови можуть виражатися як 32, "32", ">32", "яблука" або B4.
Примітки
- Якщо "діапазон_усереднення" – пусте або текстове значення, AVERAGEIFS повертає значення помилки #DIV0! .
- Якщо клітинка в діапазоні умов пуста, AVERAGEIFS вважає її значенням "0".
- Клітинки в діапазоні, які містять значення TRUE, інтерпретуються як 1; клітинки, які містять значення FALSE, інтерпретуються як 0 (нуль).
- Кожна клітинка в параметрі "діапазон_усереднення" використовується в обчисленні середнього лише тоді, коли всі вказані умови виконуються для цієї клітинки.
- На відміну від діапазону та аргументів умов у функції AVERAGEIF, у функції AVERAGEIFS кожен criteria_range має бути однакового розміру та фігури, що й average_range.
- Якщо клітинки в параметрі "діапазон_усереднення" не можна перетворити на числа, AVERAGEIFS повертає значення помилки #DIV0! .
- Якщо клітинок, які відповідали б усім умовам, немає, AVERAGEIFS повертає значення помилки #DIV/0! .
- В умові можна використовувати символи узагальнення – знак питання (?) і зірочку (*). Знак питання відповідає будь-якому окремому символу, а зірочка – будь-якій послідовності символів. Якщо потрібно знайти власне знак питання або зірочку, перед відповідним символом введіть тильду (~).
Примітка.
Функція AVERAGEIFS вимірює середнє значення, тобто розташування центру групи чисел у статистичному розподілі. Нижче наведено найбільш розповсюджені показники середнього значення.
- Середнє – середнє арифметичне, яке обчислюється додаванням групи чисел, а потім діленням на кількість цих чисел. Наприклад, середнє від 2, 3, 3, 5, 7 і 10 дорівнює 30, поділеному на 6, тобто 5.
- Медіана – середнє число групи чисел; тобто значення половини чисел більші за медіану, а значення половини – менші за медіану. Наприклад, медіаною для 2, 3, 3, 5, 7 і 10 буде 4.
- Режим , який найчастіше зустрічається в групі чисел. Наприклад, модою для 2, 3, 3, 5, 7 і 10 буде 3.
Для симетричного розподілу групи чисел усі три показники основної тенденції однакові. Для асиметричного розподілу групи чисел вони можуть відрізнятися.
Приклади
Скопіюйте дані з наведеної нижче таблиці та вставте їх у клітинку A1 нового аркуша Excel. Щоб відобразити результат обчислення формул, виберіть їх, натисніть клавішу F2, а потім – клавішу Enter. За потреби можна змінити ширину стовпців, щоб відобразити всі дані.
| Студент | Перший іспит | Другий іспит | Фінальний іспит |
|---|---|---|---|
| Тест | Тест | Екзамен | |
| Оцінка | Оцінка | Оцінка | |
| Тарас | 75 | 85 | 87 |
| Юлія | 94 | 80 | 88 |
| Василь | 86 | 93 | Не складено |
| Сергій | Не складено | 75 | 75 |
| Формула | Опис | Результат | |
| =AVERAGEIFS(B2:B5;B2:B5;">70";B2:B5;"<90") | Середнє значення оцінок першого тесту в межах від 70 до 90 для всіх учнів (80,5). Оцінка "Не складено" не враховується в обчисленні, тому що це не числове значення. | 75 | |
| =AVERAGEIFS(C2:C5;C2:C5;">95") | Середнє значення оцінок другого іспиту, які перевищують 95, для всіх учнів. Оскільки оцінок, більших за 95, немає, повертається помилка #DIV0! . | #DIV/0! | |
| =AVERAGEIFS(D2:D5; D2:D5; "<>Не завершено", D2:D5, ">80") | Середнє значення оцінок випускного іспиту, які перевищують 80, для всіх учнів (87,5). Оцінка "Не складено" не враховується в обчисленні, тому що це не числове значення. | 87,5 |
Приклад 2
| Тип | Ціна | Місто | Кількість кімнат | Наявність гаража |
|---|---|---|---|---|
| Зручне ранчо | 230000 | Київ | 3 | Немає |
| Затишне бунгало | 197000 | Львів | 2 | Є |
| Сучасна вілла | 345678 | Львів | 4 | Є |
| Розкішний дворівневий особняк | 321900 | Київ | 2 | Є |
| Ексклюзивний маєток епохи Тюдорів | 450000 | Львів | 5 | Є |
| Елегантний будинок у колоніальному стилі | 395000 | Львів | 4 | Ні |
| Формула | Опис | Результат | ||
| =AVERAGEIFS(B2:B7; C2:C7; "Bellevue", D2:D7, ">2",E2:E7, "Yes") | Середня ціна будинку в Беллв’ю, в якому є не менше 3 спалень і гараж | 397839 | ||
| =AVERAGEIFS(B2:B7; C2:C7; "Іссакуа", D2:D7, "<=3",E2:E7, "Ні") | Середня ціна будинку в Іссакві, в якому є до 3 спалень і немає гаража | 230000 |