Як використовувати COUNTIFS, SUMIFS, AVERAGEIFS в Excel

Три найчастіше використовувані формули в Excel, які виконують прості математичні розрахунки: РАХУВАТИ, SUM і СЕРЕДНЯ. Якщо ви керуєте фінансовим бюджетом в Excel або просто відстежуєте ваш наступний відпустку, ви, напевно, раніше використовували одну з цих функцій.

У цій статті ми розглянемо основи цих трьох функцій та їх відповідних і корисних партнерів: COUNTIFS, SUMIFS і AVERAGEIFS.

Припустімо, що ми починаємо новий інтернет-бізнес, який продає мобільні телефони, і ми маємо аркуш із переліком продажів, які ми зробили за перші два місяці. Завантажте приклад електронної таблиці Excel тут.

Excel COUNT, SUM і AVERAGE

Щоб дізнатися, скільки мобільних телефонів, які ми продали, ми можемо швидко використовувати РАХУВАТИ формула, як показано нижче:

= COUNT (E2: E16)

З іншого боку, щоб отримати загальний обсяг продажів, який ми зробили, ми можемо використовувати SUM формула, як показано нижче:

= SUM (E2: E16)

Нарешті, щоб дізнатися середні продажі, які ми зробили для всіх телефонів, ми можемо використовувати СЕРЕДНЯ формула нижче:

= СЕРЕДНЯ (E2: E16)

Результат має бути таким:

Формули COUNT, SUM і AVERAGE працюватимуть лише для записів, де значення клітинки має формат чисел. Будь-який запис у межах діапазону формул (тобто. E2: E16 у цьому прикладі) не у форматі чисел буде проігноровано.

Отже, переконайтеся, що всі комірки у формулі COUNT, SUM і AVERAGE відформатовані як Номер, ні Текст. Спробуйте використовувати ту ж формулу, але з E: E як діапазон замість E2: E16. Він повертає той самий результат, що і раніше, оскільки він ігнорує заголовок (тобто. Ціна продажу), який знаходиться в текстовому форматі.

Тепер, що, якщо ми хочемо знати кількість продажів, загальний обсяг продажів і середній обсяг продажів на телефон, тільки для тих, хто продається в США? Тут важливу роль відіграють COUNTIFS, SUMIFS і AVERAGEIFS. Дотримуйтесь наведеної нижче формули:

КОНКУРСИ

Розподіл формули:

1. = COUNTIFS - The "=" вказує на початок формули в комірці і КОНКУРСИ - це перша частина функції Excel, яку ми використовуємо.
2. D2: D16 - Посилається на діапазон даних, щоб перевірити, чи задовольняє він критеріям, які повинні бути включені до формули підрахунку.
3. "США" - Критерії для пошуку в вказаному діапазоні даних (D2: D16)
4. ) - Замикаюча дужка вказує кінець формули.

Формула повертає 6, яка є кількістю продажів продукції, що поставляється зі складу США.

SUMIFS

Розподіл формули:

1. = SUMIFS ( - The "=" знову вказує на початок формули.
2. E2: E16 - Посилається на діапазон даних, які ми хотіли б отримати в цілому, тобто ціна продажу в нашому прикладі.
3. D2: D16 - Посилається на діапазон даних, щоб перевірити, чи задовольняє він критеріям, які потрібно включити до загальної суми.
4. "США" - Критерії для пошуку в вказаному діапазоні даних (D2: D16)
5. ) - Замикаюча дужка вказує кінець формули.

Формула показує $ 6,050 загальний обсяг продажів, зроблених на товари, відвантажені зі складу США.

AVERAGEIFS

Розподіл формули:

1. = AVERAGEIFS ( - The "=" позначають початок формули.
2. E2: E16 - Мається на увазі діапазон даних, які ми хотіли б середнє. У цьому прикладі ми хочемо отримати середній обсяг продажів для всіх телефонів, що продаються в США.
3. D2: D16 - Посилається на діапазон даних, щоб перевірити, чи задовольняє він критеріям, щоб бути включеними до середньої формули.
4. "США" - Критерії для пошуку в заданому діапазоні даних
5. ) - Закриваюча дужка вказує кінці формули.

Формула показує, що ми продавали продукт навколо $ 1008 на телефон у США.

Всі три формули можуть приймати більше одного критерію. Наприклад, якщо ми хочемо знати ті ж цифри (тобто. РАХУВАТИ, SUM і СЕРЕДНЯ) для товарів, що продаються в Росії США, але конкретно тільки для Samsung бренд, нам просто потрібно додати діапазон даних, який потрібно перевірити, а потім його критерії.

Див. Приклад нижче, де до початкової перевірки критеріїв додано другий критерій. (Синій текст вказує на перший критерій, а червоний - на другий критерій)

= COUNTIFS (D2: D16, "США", B2: B16, "Samsung") = SUMIFS (E2: E16, D2: D16, "США", B2: B16, "Samsung") = AVERAGEIFS (E2: E16, D2: D16, "США", B2: B16, "Samsung")

Ви помітите, що Excel також має COUNTIF, SUMIF і AVERAGEIF Формули без суфікса "S". Вони використовуються аналогічно КОНКУРСИ, SUMIFS і AVERAGEIFS. Проте ті без суфікса "S" у формулі мають обмеження лише дозволу одного критерію на формулу.

Оскільки синтаксис дещо відрізняється, я б рекомендував використовувати КОНКУРСИ, SUMIFS і AVERAGEIFS тільки тому, що вона може бути використана для одного або більше критеріїв, якщо це необхідно. Насолоджуйтесь!