Як користуватися функцією Excel AVERAGEIFS -

Резюме

Функція Excel AVERAGEIFS обчислює середнє число в діапазоні, що відповідає одному або декільком критеріям. Критерії, що використовуються для AVERAGEIFS, можуть включати логічні оператори (>, <,, =) та символи підстановки (* ,?) для часткового збігу.

Призначення

Середнє значення клітинок, які відповідають кільком критеріям

Повернене значення

Середнє значення клітин, які відповідають усім критеріям

Синтаксис

= AVERAGEIFS (avg_rng, діапазон1, критерії1, (діапазон2), (критерії2),…)

Аргументи

  • avg_rng - діапазон для усереднення.
  • range1 - Перший діапазон для evaulate.
  • критерії1 - критерії, які слід використовувати для діапазону1.
  • range2 - (необов’язково) Другий діапазон для оцінки.
  • критерії2 - (необов’язково) Критерії, які слід використовувати для діапазону2.

Версія

Excel 2007

Примітки щодо використання

Функція AVERAGEIFS обчислює середнє число в діапазоні, який відповідає наведеним критеріям. Перший діапазон, наданий AVERAGEIFS, - це діапазон, що містить середні числа. Критерії подаються парами (діапазон, критерії) і потрібна лише перша пара. Для кожного додаткового критерію вкажіть іншу пару діапазонів / критеріїв. Критерії, надані AVERAGEIFS, застосовуються за допомогою логіки AND - всі критерії повинні бути істинними, щоб число було включено в середнє значення.

Критерії можуть надаватися як числа, рядки або посилання. Наприклад, дійсними критеріями можуть бути 10, "> 10", A1 або "<" & A1. Критерії можуть також включати символи підстановки для часткової відповідності.

AVERAGEIFS входить до групи з восьми функцій в Excel, які розділяють логічні критерії на дві частини (діапазон + критерії). Як результат, синтаксис, який використовується для побудови критеріїв, відрізняється, і AVERAGEIFS вимагає діапазон комірок для аргументів діапазону - ви не можете використовувати масив.

AVERAGEIFS автоматично ігнорує порожні клітинки, навіть якщо критерії збігаються. Іншими словами, AVERAGEIFS не буде включати порожні клітинки в середнє. AVERAGEIFS повертає # DIV / 0! якщо жодна клітина не відповідає критеріям.

Приклади

У наведеному прикладі формули в H5: H7:

=AVERAGEIFS(C5:C15,C5:C15,">0") =AVERAGEIFS(C5:C15,C5:C15,">0",C5:C15,"=2",E5:E15,">1")

Ці формули повертають середню ціну властивостей, де:

  1. ціни більше нуля
  2. ціни перевищують нуль і менше 500 000 доларів
  3. об'єкти мають принаймні 2 спальні та більше 1 ванної кімнати

Подвійні лапки ("") у критеріях

Як правило, текстові значення в критеріях укладаються у подвійні лапки (""), а цифри - ні. Однак, коли логічний оператор включається в число, номер і оператор повинні бути в лапках. Зверніть увагу на різницю в двох прикладах нижче. Оскільки друга формула використовує оператор (> =) більший або дорівнює, і оператор, і число містяться у подвійних лапках.

=AVERAGEIFS(C5:C15,D5:D15,2) // 2 bedrooms =AVERAGEIFS(C5:C15,D5:D15,">=2") // 2+ bedrooms

Подвійні лапки також використовуються для текстових значень. Наприклад, для середніх значень у B1: B10, коли значення в A1: A10 дорівнюють "червоному", ви можете скористатися такою формулою:

=AVERAGEIFS(B1:B10,A1:A10,"red")

Кілька критеріїв

Введіть критерії попарно (діапазон, критерії). Наприклад, для середніх значень в А1: А10, де В1: В10 = "А" та С1: С10> 5, використовуйте:

=AVERAGEIFS(A1:A10,B1:B10,"A",C1:C10,">5")

Значення з іншої комірки

Значення з іншої комірки може бути включено до критеріїв за допомогою конкатенації. У наведеному нижче прикладі AVERAGEIFS поверне середнє число в A1: A10, яке менше значення в комірці B1. Зверніть увагу, що оператор менше ніж (який є текстом) укладено у лапки.

=AVERAGEIFS(A1:A10,A1:A10,"<"&B1) // average values less than B1

Підстановочні знаки

У критеріях можна використовувати символи підстановки (?), Зірочку (*) або тильду (~). Знак питання (?) Відповідає будь-якому одному символу, а зірочка (*) відповідає нулю або більше символів будь-якого типу. Наприклад, для середніх значень у B1: B10, коли значення в A1: A10 містять текст "червоний", ви можете використовувати формулу, подібну до цієї:

=AVERAGEIFS(B1:B10,A1:A10,"*red*")

Тильда (~) - це символ втечі, який дозволяє вам знаходити буквальні символи підстановки. Наприклад, щоб зіставити буквальний знак запитання (?), Зірочку (*) або тильду (~), додайте тильду перед підстановкою (тобто ~ ?, ~ *, ~~).

Примітка: порядок аргументів різниться між AVERAGEIFS та AVERAGEIF. Середній діапазон завжди є першим аргументом у AVERAGEIFS.

Примітки

  • Якщо жодні дані не відповідають критеріям, AVERAGEIFS повертає # DIV0! помилка
  • Кожен додатковий діапазон повинен мати таку ж кількість рядків і стовпців, як середній_діапазон .
  • Нечислові критерії потрібно вкладати у подвійні лапки, а числові - ні. Наприклад: 100, "100", "> 32", "jim" або A1 (де A1 містить число).
  • Символи підстановки? і * може використовуватися в критеріях. Знак питання відповідає будь-якому одному символу, а зірочка відповідає нулю або більше символів будь-якого типу.
  • Щоб знайти буквальний знак питання або зірочку, використовуйте тильду (~) перед знаком питання або зірочкою (тобто ~ ?, ~ *).

Цікаві статті...