Excel: як використовувати average і offset разом

Ви можете використовувати такий синтаксис із функціями AVERAGE та OFFSET в Excel, щоб обчислити середнє значення певного діапазону значень, зміщених на певну кількість рядків і стовпців:

 =AVERAGE(OFFSET( A1,6,2,5,1 ))

Ця конкретна формула усереднює діапазон значень довжиною 5 рядків і шириною 1 стовпця, зміщених на 6 рядків нижче та 2 стовпців праворуч від клітинки A1 .

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

Приклад: як використовувати AVERAGE і OFFSET в Excel

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

Зверніть увагу, що в кожній команді рівно п’ять гравців.

Скажімо, ми хочемо обчислити середню кількість очок, набраних гравцями команди Шпори.

Для цього ми можемо ввести таку формулу в клітинку E2 :

 =AVERAGE(OFFSET( A1,6,2,5,1 ))

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

Формула повертає значення 18,4 , яке є середнім значенням очок гравців команди Шпори.

Ми можемо підтвердити, що це правильно, вручну усереднивши очки гравців Шпор:

Середня кількість балів для Шпор: (6 + 8 + 15 + 40 + 23) / 5 = 18,4 .

Це відповідає значенню, розрахованому за нашою формулою.

Як працює ця формула?

Згадайте формулу, яку ми використовували для розрахунку середнього результату гравця команди Шпори:

 =AVERAGE(OFFSET( A1,6,2,5,1 ))

Ось візуальне пояснення того, як працює формула:

Спочатку ми починаємо з клітинки A1 .

Потім йдемо на 6 клітинок нижче цієї клітинки і на 2 клітинки праворуч.

Далі ми вказуємо, що ми хочемо переглянути діапазон комірок, який складається з 5 рядків і 1 стовпця завширшки.

Нарешті, ми використовуємо функцію AVERAGE , щоб усереднити значення в цьому діапазоні.

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

У наступних посібниках пояснюється, як виконувати інші типові завдання в Excel:

Excel: як використовувати INDEX і MATCH для повернення кількох значень
Excel: як використовувати OFFSET і MATCH разом
Excel: Як використовувати VLOOKUP для повернення всіх збігів