У цій статті подано синтаксис формули й описано використання функції AVERAGEIFS у програмі Microsoft Excel.
Опис
Повертає середнє (середнє арифметичне) усіх клітинок, які відповідають кільком умовам.
Синтаксис
AVERAGEIFS(діапазон_усереднення; діапазон_умови1; умова1; [діапазон_умови2; умова2]; ...)
Синтаксис функції AVERAGEIFS має такі аргументи:
-
діапазон_усереднення – обов’язковий аргумент. Одна або кілька клітинок, які містять числа, імена, масиви або посилання на числа, для яких потрібно обчислити середнє значення.
-
діапазон_умови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, "Беллв’ю", D2:D7, ">2",E2:E7, "Є") |
Середня ціна будинку в Беллв’ю, в якому є не менше 3 спалень і гараж |
397839 |
||
=AVERAGEIFS(B2:B7, C2:C7, "Іссаква", D2:D7, "<=3",E2:E7, "Немає") |
Середня ціна будинку в Іссакві, в якому є до 3 спалень і немає гаража |
230000 |