перевершувати OFFSET функція

Excel Функція OFFSET повертає посилання на комірку або діапазон комірок, що зміщується від певної комірки заданою кількістю рядків і стовпців.

синтаксис

=OFFSET (reference, rows, cols, [height], [width])

Аргументи

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

Функція OFFSET поверне зсув опорної комірки від заданої початкової точки.

Примітки до функцій

1. # ЗНАЧЕННЯ! значення помилки повернеться, коли посилання посилається на діапазон розривних комірок.
2. # REF! значення помилки повернеться, коли рядки та стовпці змістять посилання над краєм аркуша.

прикладів

Приклад 1: Основне використання функції OFFSET

Повернути посилання на клітинку з формулою нижче:
=OFFSET(B2,3,1)

У цьому випадку B2 є початковою точкою, цифри 3 і 1 означають, що потрібно перемістити 3 рядки вниз і 1 стовпець прямо з комірки B2 і повернути значення в C5 остаточно. Дивіться знімок екрана:

Повернути посилання на діапазон комірок із формулою нижче:
=OFFSET(B2,3,1,2,2)

У цьому випадку ви отримаєте результати діапазону 2 x 2, тобто 3 рядки нижче та 1 стовпець праворуч від комірки B2.

примітки: # ЗНАЧЕННЯ! Помилка трапиться, коли ви виберете лише одну комірку, щоб застосувати функцію OFFSET для повернення діапазону комірок. Потрібно вибрати діапазон 2 х 2 (наприклад, 4 порожні клітинки), ввести формулу та натиснути Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у клавіші для отримання результатів.

Приклад 2: Використовуйте функцію OFFSET для підсумовування діапазону значень

Як ми вже згадували вище, якщо ви намагаєтесь використовувати функцію OFFSET, таку як =OFFSET(B2,3,1,2,2) самостійно в одній комірці, він поверне #VALUE! Помилка. Однак якщо ви комбінуєте функції SUM і OFFSET, як показано на знімку екрана, це поверне суму значень у діапазоні C5: D6 безпосередньо.

1. Виділіть порожню комірку, скопіюйте в неї формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат.
=SUM(OFFSET(B2,3,1,2,2)))

Приклад 3: Сума стовпців на основі певних критеріїв

Як показано на знімку екрана, як отримати загальний обсяг продажів Mango від Sun до Tue? Будь ласка, спробуйте, як показано нижче.

1. Виділіть порожню комірку, скопіюйте в неї формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

примітки:

1. У наведеній вище формулі MATCH(G4,B3:B8) шукає Манго і повертає свою позицію в діапазоні B3: B8. Подивимось, Манго розміщує 5 рядків під коміркою В2, тому повертає число 5;
2. Оскільки результат МАТЧ є 5, а остання кома тут представляє 0, функція OFFSET тепер відображається як OFFSET(C2:E2,5,0), що означає, що зміщення починається з діапазону C2: E2, перемістіть на 5 рядків вниз і 0 стовпця вправо, щоб отримати посилання на діапазон C7: E7;
3. Потім функція SUM відображається як SUM(C7:E7)і, нарешті, повертає суму значень у діапазоні C7: E7.

Інші приклади

Як підсумувати кожні n рядків у Excel?

Як створити динамічний іменований діапазон в Excel?

Як повернути значення комірки кожні п’ятий або n-й рядок у Excel?