Домашня » Поради MS Office » Як використовувати COUNTIFS, SUMIFS, AVERAGEIFS в Excel

    Як використовувати 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 тільки тому, що вона може бути використана для одного або більше критеріїв, якщо це необхідно. Насолоджуйтесь!