Як використовувати subtotal з averageif в excel

Ви можете використати наведену нижче формулу, щоб поєднати функції SUBTOTAL і AVERAGE в Excel:

 =AVERAGE(IF(SUBTOTAL(2,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),0)),IF( B2:B11 ="Guard", C2:C11 )))

Ця конкретна формула дозволяє обчислити середнє значення в діапазоні C2:C11, де відповідне значення в діапазоні B2:B11 дорівнює «Зберегти», навіть після того, як цей діапазон комірок було відфільтровано тим чи іншим способом. інший.

У наступному прикладі показано, як використовувати цю формулу на практиці.

Приклад: як використовувати SUBTOTAL з AVERAGEIF в Excel

Припустимо, що ми маємо наступний набір даних, що містить інформацію про різних баскетболістів:

Далі давайте відфільтруємо дані, щоб відобразити лише ті рядки, де знаходяться гравці Західної конференції.

Для цього виділіть діапазон клітинок A1:C11 . Потім натисніть вкладку «Дані» на верхній стрічці та натисніть кнопку «Фільтр» .

Потім клацніть стрілку спадного меню поруч із Conference (Конференція) і переконайтеся, що позначено лише прапорець біля West (Захід) , а потім натисніть OK :

Дані буде автоматично відфільтровано, щоб відобразити лише ті рядки, у яких стовпець Conference дорівнює West :

Якщо ми спробуємо використати функцію AVERAGEIF() , щоб обчислити середнє значення стовпця Points, де значення стовпця Position дорівнює “Guard”, вона фактично поверне середнє значення балів для рядків, які дорівнюють “Guard”. у вихідному наборі даних:

Замість цього нам потрібно використовувати таку формулу:

 =AVERAGE(IF(SUBTOTAL(2,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),0)),IF( B2:B11 ="Guard", C2:C11 )))

На наступному знімку екрана показано, як використовувати цю формулу на практиці:

Ця формула повертає правильне середнє значення 21,25 .

Ми можемо підтвердити, що це правильно, вручну усереднивши значення балів для рядків, де стовпець Position дорівнює “Guard”:

Середні очки гарда: (12 + 28 + 30 + 15) / 4 = 21,25 .

Додаткові ресурси

У наступних посібниках пояснюється, як виконувати інші типові операції в Excel:

Як видалити відфільтровані рядки в Excel
Як підрахувати відфільтровані рядки в Excel
Як додати відфільтровані рядки в Excel