Как создать функцию смещения в 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