Цей посібник демонструє, як використовувати функції Excel AVERAGEIF та AVERAGEIFS у Excel та Google Таблицях для середнього обміну даними, що відповідають певним критеріям.
Огляд функції AVERAGEIF
Ви можете використовувати функцію AVERAGEIF в Excel для підрахунку клітинок, які містять певне значення, підрахунку клітинок, які більші або дорівнюють значенню тощо.
Щоб скористатися функцією робочого аркуша Excel AVERAGEIF, виберіть клітинку та введіть:
(Зверніть увагу, як виглядають вхідні дані формул)
Синтаксис та аргументи функції AVERAGEIF:
= AVERAGEIF (діапазон, критерії, [середній_діапазон])
діапазон - Діапазон клітинок для підрахунку.
критерії - Критерії, які контролюють, які клітини слід зараховувати.
середній_діапазон - [необов’язково] Комірки для усереднення. Якщо пропущено, використовується діапазон.
Що таке функція AVERAGEIF?
Функція AVERAGEIF - одна з старих функцій, що використовуються в електронних таблицях. Він використовується для сканування через діапазон клітинок, перевіряючи певний критерій, а потім дає середнє значення (він же математичне середнє), якщо значення в діапазоні відповідають цим значенням. Початкова функція AVERAGEIF була обмежена лише одним критерієм. Після 2007 року була створена функція AVERAGEIFS, яка дозволяє використовувати безліч критеріїв. Більшість загального використання залишається незмінним між ними, але є деякі критичні відмінності в синтаксисі, які ми обговоримо у цій статті.
Якщо ви ще цього не зробили, ви можете переглянути більшість подібної структури та приклади у статті COUNTIFS.
Основний приклад
Давайте розглянемо цей список зареєстрованих продажів і хочемо дізнатися середній дохід.
Оскільки у нас були витрати, від'ємне значення, ми не можемо просто зробити базове середнє значення. Натомість ми хочемо усереднити лише значення, які більші за 0. “Більше 0” - це наші критерії у функції AVERAGEIF. Наша формула стверджує це
= СЕРЕДНЯ (A2: A7, "> 0")
Приклад з двома колонками
Хоча початкова функція AVERAGEIF була розроблена, щоб дозволити вам застосувати критерій до діапазону чисел, які потрібно підсумувати, більшу частину часу вам потрібно буде застосувати один або кілька критеріїв до інших стовпців. Розглянемо цю таблицю:
Тепер, якщо ми будемо використовувати оригінальну функцію AVERAGEIF, щоб дізнатися, скільки в середньому у нас бананів. Ми помістимо наші критерії в клітинку D1, і нам потрібно буде вказати потрібний діапазон середній як останній аргумент, і тому наша формула буде такою
= СЕРЕДНЯ (A2: A7, D1, B2: B7)
Однак, коли програмісти врешті -решт зрозуміли, що користувачі хочуть надати більше одного критерію, була створена функція AVERAGEIFS. Для того, щоб створити одну структуру, яка буде працювати за будь -якою кількістю критеріїв, AVERAGEIFS вимагає, щоб діапазон сум був вказаний першим. У нашому прикладі це означає, що формула має бути такою
= СЕРЕДНІ (В2: В7, А2: А7, Д1)
ПРИМІТКА. Ці дві формули отримують однаковий результат і можуть виглядати однаково, тому зверніть пильну увагу на те, яка функція використовується, щоб переконатися, що ви перерахуєте всі аргументи у правильному порядку.
Робота з датами, кілька критеріїв
Під час роботи з датами в електронній таблиці, хоча є можливість ввести дату безпосередньо у формулу, найкраще використовувати цю дату в комірці, щоб ви могли просто посилатися на клітинку у формулі. Наприклад, це допомагає комп’ютеру зрозуміти, що ви хочете використовувати дату 27.05.2020, а не число 5, поділене на 27, поділене на 2022 рік.
Давайте подивимось на нашу наступну таблицю, де реєструється кількість відвідувачів сайту кожні два тижні.
Ми можемо вказати початкову та кінцеву точки діапазону, який ми хочемо подивитися в D2 та E2. Наша формула для визначення середньої кількості відвідувачів у цьому діапазоні може бути такою:
= СЕРЕДНІ КАРТИ (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Зверніть увагу, як нам вдалося об’єднати порівняння “=” із посиланнями на клітинки для створення критеріїв. Крім того, хоча обидва критерії застосовувалися до одного діапазону клітинок (A2: A7), вам потрібно виписати діапазон двічі, один раз для кожного критерію.
Кілька стовпців
При використанні кількох критеріїв ви можете застосувати їх до того самого діапазону, що й у попередньому прикладі, або застосувати до різних діапазонів. Давайте об’єднаємо наші приклади даних у цю таблицю:
Ми налаштували деякі клітинки, щоб користувач міг вводити те, що він хоче шукати, у клітинках від E2 до G2. Таким чином, нам потрібна формула, яка додасть загальну кількість яблук, зібраних у лютому. Наша формула виглядає так:
= СЕРЕДНІ КАРТИ (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
ПОСЕРЕДНІ з логікою типу АБО
До цього моменту всі приклади, які ми використовували, були порівнянням на основі І, де ми шукаємо рядки, які відповідають усім нашим критеріям. Тепер ми розглянемо випадок, коли ви хочете шукати можливість того, щоб рядок відповідав тому чи іншому критерію.
Давайте подивимося на цей список продажів:
Ми хотіли б скласти середні продажі як для Адама, так і для Боба. По -перше, коротка дискусія про середні показники. Якщо у вас є нерівна кількість речей, таких як 3 записи для Адама і 2 для Боба, ви не можете просто взяти середнє значення продажів кожної людини. Це відоме як середнє значення середніх показників, і ви в кінцевому підсумку надаєте несправедливу вагу елементу, у якого мало записів. Якщо це стосується ваших даних, вам потрібно буде обчислити середнє значення "ручним" способом: взяти суму всіх ваших предметів, поділену на кількість ваших предметів. Щоб переглянути, як це зробити, можна ознайомитися зі статтями тут:
Тепер, якщо кількість записів однакова, наприклад, у нашій таблиці, то у вас є кілька варіантів, які ви можете зробити. Найпростіший спосіб - скласти два СРЕДНІ значення разом, а потім поділити на 2 (кількість елементів у нашому списку)
= (СЕРЕДНІ КАРТИ (B2: B7, A2: A7, "Адам")+СЕРЕДНІ (B2: B7, A2: A7, "Боб"))/2
Тут комп’ютер обчислив наші індивідуальні бали, а потім додав їх разом.
Наш наступний варіант хороший для тих випадків, коли у вас більше діапазонів критеріїв, таких, що вам не потрібно повторно переписувати всю формулу. У попередній формулі ми вручну сказали комп’ютеру додати дві різні СРЕДНІ СИСТЕМИ разом. Однак ви також можете зробити це, написавши свої критерії всередині масиву, наприклад:
= СЕРЕДНЯ (СЕРЕДНЯ (B2: B7, A2: A7, {"Адам", "Боб"}))
Подивіться, як масив побудований у фігурних дужках. Коли комп’ютер оцінить цю формулу, він буде знати, що ми хочемо обчислити функцію AVERAGEIFS для кожного елемента нашого масиву, створюючи таким чином масив цифр. Зовнішня функція AVERAGE візьме цей масив чисел і перетворить його на єдине число. Перейшовши до оцінки формули, це виглядатиме так:
= СЕРЕДНЯ (СЕРЕДНЯ (B2: B7, A2: A7, {"Адам", "Боб"})) = СЕРЕДНЯ (13701, 21735) = 17718
Ми отримуємо той самий результат, але нам вдалося записати формулу трохи коротше.
Робота з заготовками
Іноді у наборі даних будуть порожні клітинки, які потрібно або знайти, або уникати. Встановлення критеріїв для них може бути трохи складним, тому давайте розглянемо інший приклад.
Зауважте, що клітинка А3 справді порожня, тоді як комірка А5 має формулу, що повертає рядок нульової довжини “”. Якщо ми хочемо знайти загальне середнє значення справді порожні клітинки, ми б використовували критерій “=”, і наша формула виглядатиме так:
= СЕРЕДНІ (В2: В7, А2: А7, "=")
З іншого боку, якщо ми хочемо отримати середнє значення для всіх клітинок, які візуально виглядають порожніми, ми змінимо критерій на "", а формула виглядає так
= СЕРЕДНІ (В2: В7, А2: А7, "")
Давайте поговоримо: що, якщо ви хочете знайти середнє значення непустих клітинок? На жаль, поточний дизайн не дозволяє уникнути рядка нульової довжини. Ви можете використовувати критерій "", але, як ви можете бачити у прикладі, він все ще містить значення з рядка 5.
= СЕРЕДНІ (В2: В7, А2: А7, "")
Якщо вам не потрібно рахувати клітинки, що містять рядки нульової довжини, вам слід розглянути можливість використання функції LEN всередині SUMPRODUCT
AVERAGEIF у Google Таблицях
Функція AVERAGEIF працює точно так само в Google Таблицях, як і в Excel:
