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

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

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

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

Excel COUNT, SUM і AVERAGE

Щоб дізнатися, скільки мобільних телефонів, які ми продали, ми можемо швидко використовувати COUNT формула, як показано нижче:

= 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. ) - Замикаюча дужка, що вказує кінці формули.

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

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

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

= COUNTIFS (D2: D16, "США", B2: B16, "Samsung")

=SUMIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")

=AVERAGEIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung")

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

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

Залишити коментар

Цей сайт використовує Akismet для зменшення спаму. Дізнайтеся, як обробляються ваші дані коментарів.