Як створити функцію зсуву в excel: із прикладами

Часто вам може знадобитися обчислити лаговані значення в Excel. На щастя, це легко зробити за допомогою функції OFFSET() .

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

Приклад 1: обчислення зміщених значень в Excel

Припустімо, у нас є такий набір даних у Excel, який показує загальну кількість продажів, здійснених магазином протягом 10 днів поспіль:

Ми можемо використовувати наступну формулу для розрахунку зміщених значень продажів у новому стовпці:

 =OFFSET( B3 ,-1,0)

Ми можемо ввести цю формулу в клітинку C3 і перетягнути її до кожної клітинки, що залишилася в стовпці C:

Стовпець «Затримка продажів» відображає продажі за лаг n=1.

Наприклад, на другий день магазин здійснив 19 продажів. Відкладене значення продажів за день 2 (наприклад, продажів, здійснених у день 1) становить 13 продажів.

Приклад 2: Розрахунок зміщених значень за групою в Excel

Припустімо, у нас є такий набір даних в Excel, який показує загальні продажі, здійснені двома різними магазинами за 5 днів кожен:

Ми можемо використовувати таку формулу, щоб обчислити відстаючі значення продажів магазином у новому стовпці:

 =IF( A3 = A2 , OFFSET( B3 , -1, 0), "")

Ми можемо ввести цю формулу в клітинку C3 і перетягнути її до кожної клітинки, що залишилася в стовпці C:

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

Якщо так, він повертає відкладене значення продажів. Якщо ні, повертається порожнє значення.

Наприклад, у рядку 3 вартість продажів становила 19 . Оскільки магазинна вартість у рядку 2 дорівнює рядку 3, відкладене значення продажів обчислюється як 13 .

Однак у рядку 7 магазинне значення не відповідає магазинному значенню в рядку 6; тому замість зміщеного значення продажу повертається пусте значення.

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

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

Як інтерполювати відсутні значення в Excel
Як знайти перші 10 значень у списку в Excel
Як знайти верхні 10% значень у стовпці Excel