Функция Excel OFFSET

Функция Excel СМЕЩ возвращает ссылку на ячейку или диапазон ячеек, смещённый относительно указанной ячейки на заданное количество строк и столбцов.

Синтаксис

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

Аргументы

Возвращаемое значение

Функция СМЕЩ возвращает Ссылка на ячейку, смещённый относительно заданной начальной точки.

Примечания к функции

1. Ошибка #ЗНАЧ! возникает, если ссылка указывает на несмежный диапазон ячеек.
2. Ошибка #ССЫЛ! появляется, если смещение по строкам и столбцам выходит за пределы листа.

Примеры

Пример 1: Базовое использование функции СМЕЩ

Верните ссылку на ячейку с помощью приведённой ниже формулы:
=OFFSET(B2,3,1)

В данном случае B2 — это начальная точка; числа 3 и 1 задают смещение на 3 строки вниз и 1 столбец вправо от ячейки B2, в результате чего возвращается значение из ячейки C5. См. скриншот:

Верните ссылку на диапазон ячеек с помощью приведённой ниже формулы:
=OFFSET(B2,3,1,2,2)

В этом случае вы получите результаты для диапазона 2×2, расположенного на три строки ниже и на один столбец правее ячейки B2.

Примечание: ошибка #ЗНАЧ! возникает, если вы применяете функцию СМЕЩ к одной ячейке, пытаясь вернуть диапазон ячеек. В этом случае необходимо выделить диапазон из 2×2 ячеек (например, четыре пустые ячейки), ввести формулу и нажать клавиши Ctrl+Shift+Enter, чтобы получить результат.

Пример 2: Использование функции СМЕЩ для суммирования диапазона значений

Как упоминалось выше, если вы попробуете использовать функцию СМЕЩ отдельно в одной ячейке — например, =OFFSET(B2,3,1,2,2), — она вернёт ошибку #ЗНАЧ!. Однако при комбинации функций СУММ и СМЕЩ, как показано на скриншоте ниже, сразу возвращается сумма значений в диапазоне C5:D6.

1. Выделите пустую ячейку, вставьте в неё приведённую ниже формулу и нажмите клавишу Enter, чтобы получить результат.
=SUM(OFFSET(B2,3,1,2,2)))

Пример 3: Суммирование столбцов по определённому критерию

Как показано на скриншоте ниже, как получить общий объём продаж манго с воскресенья по вторник? Попробуйте следующее.

1. Выделите пустую ячейку, вставьте в неё приведённую ниже формулу и нажмите клавишу Enter, чтобы получить результат.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

Примечания:

1. В приведённой выше формуле MATCH(G4,B3:B8) выполняется поиск значения «Манго» и возвращается его позиция в диапазоне B3:B8. Поскольку «Манго» находится на 5 строк ниже ячейки B2, функция возвращает число 5;
2. Так как результат функции ПОИСКПОЗ равен 5, а последняя запятая здесь обозначает 0, функция СМЕЩ принимает вид OFFSET(C2:E2,5,0), то есть смещение начинается с диапазона C2:E2, перемещается на 5 строк вниз и на 0 столбцов вправо, чтобы получить ссылку на диапазон C7:E7;
3. Затем функция СУММ принимает вид SUM(C7:E7) и в итоге возвращает сумму значений в диапазоне C7:E7.

Дополнительные примеры

Как суммировать каждые n строк в Excel?

Как создать динамический именованный диапазон в Excel?

Как получить значение ячейки из каждой пятой или n-й строки в Excel?