Функция OFFSET (СМЕЩ) в Excel возвращает ссылку на диапазон, построенный с пятью входными данными: (1) начальная точка, (2) смещение строки, (3) смещение столбца, (4) высота в строках, (5) ширина в столбцы. СМЕЩ удобен в формулах, требующих динамического диапазона.
Что делает функция OFFSET в Excel?
Функция OFFSET (СМЕЩ) в Excel создает ссылочное смещение от заданной начальной точки.
Возвращаемое значение
Ссылка на ячейку.
Аргументы
- reference — начальная точка, указанная как ссылка на ячейку или диапазон.
- rows — количество строк для смещения ниже начальной ссылки.
- cols — количество столбцов для смещения справа от начальной ссылки.
- height — [необязательный] Высота возвращаемой ссылки в строках.
- width — [необязательный] Ширина в столбцах возвращаемой ссылки.
Синтаксис
=OFFSET(reference, rows, cols, [height], [width])
Примечания по использованию
Функция OFFSET возвращает динамический диапазон, построенный с пятью входными данными: (1) начальная точка, (2) смещение строки, (3) смещение столбца, (4) высота в строках, (5) ширина в столбцах.
OFFSET — это непостоянная функция , которая может вызвать проблемы с производительностью в больших или сложных листах.
Отправной точкой ( аргументом ссылки ) может быть одна ячейка или диапазон ячеек. Аргументы rows и cols — это количество ячеек, которые нужно «сместить» от начальной точки. Аргументы высоты и ширины являются необязательными и определяют размер создаваемого диапазона. Когда высота и ширина опущены, они по умолчанию равны высоте и ширине ссылки .
Например, для ссылки на C5, начинающейся с A1, ссылка — это A1, строки — 4, а столбцы — 2:
=OFFSET(A1,4,2) // возвращает ссылку на C5
Чтобы сослаться на C1:C5 из A1, ссылка — это A1, строки — 0, столбцы — 2, высота — 5, а ширина — 1:
=OFFSET(A1,0,2,5,1) // возвращает ссылку на C1:C5
Примечание: ширину можно не указывать, так как по умолчанию она равна 1.
Часто можно увидеть, что OFFSET завернут в другую функцию, которая ожидает диапазон. Например, для СУММ C1:C5, начиная с A1:
=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5
Основная цель OFFSET — позволить формулам динамически адаптироваться к доступным данным или пользовательскому вводу. Функцию можно использовать для создания динамического именованного диапазона для диаграмм или сводных таблиц, чтобы гарантировать, что исходные данные всегда актуальны.
Примечание. В документации Excel указано, что высота и ширина не могут быть отрицательными, но с начала 1990-х отрицательные значения отлично работали. Функция OFFSET в Google Таблицах не допускает отрицательного значения аргументов высоты или ширины.
Примеры
В приведенных ниже примерах показано, как можно настроить OFFSET для возврата различных типов диапазонов. Эти экраны были сделаны с помощью Excel 365, поэтому OFFSET возвращает динамический массив , когда результатом является более одной ячейки.
В более старых версиях Excel вы можете использовать клавишу F9 для проверки результатов, возвращаемых функцией OFFSET.
Пример №1
На приведенном ниже экране мы используем OFFSET, чтобы вернуть третье значение (март) во втором столбце (запад). Формула в H4:
=OFFSET(B3,3,2) // вернет D6
Пример #2
На приведенном ниже экране мы используем OFFSET, чтобы вернуть последнее значение (июнь) в третьем столбце (север). Формула в H4:
=OFFSET(B3,6,3) // returns E9
Пример №3
Ниже мы используем OFFSET для возврата всех значений в третьем столбце (север). Формула в H4:
=OFFSET(B3,1,3,6) // returns E4:E9
Пример №4
Ниже мы используем OFFSET для возврата всех значений за май (пятая строка). Формула в H4:
=OFFSET(B3,5,1,1,4) // returns C8:F8
Пример №5
Ниже мы используем OFFSET для возврата значений апреля, мая и июня для западного региона. Формула в H4:
=OFFSET(B3,4,2,3,1) // returns D7:D9
Пример №6
Ниже мы используем OFFSET для возврата значений апреля, мая и июня для запада и севера. Формула в H4:
=OFFSET(B3,4,2,3,2) // returns D7:E9
Примечания
- OFFSET в Excel возвращает только ссылку, никакие ячейки не перемещаются.
- И строки, и столбцы могут быть представлены как отрицательные числа, чтобы изменить их нормальное направление смещения — отрицательные столбцы смещены влево, а отрицательные строки смещены вверх.
- СМЕЩ в экселе — это « изменчивая функция ». Изменчивые функции могут замедлять работу больших и сложных книг.
- Отобразит #REF! значение ошибки, если смещение находится за пределами края рабочего листа.
- Когда высота или ширина опущены, используются высота и ширина ссылки .
- Функцию можно использовать с любой другой функцией, которая ожидает получить ссылку.
- В документации Excel говорится, что высота и ширина не могут быть отрицательными, но отрицательные значения работают.