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

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

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

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

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

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

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

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

Припустимо, ми хочемо скласти значення очок, набраних гравцями команди Шпори.

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

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

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

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

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

Сума очок для Шпор: 6 + 8 + 15 + 40 + 23 = 92 .

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

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

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

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

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

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

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

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

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

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

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

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