Как использовать функцию СМЕЩЕНИЕ в Excel -

Содержание

Примеры
Похожие видео

Резюме

Функция СМЕЩЕНИЕ Excel возвращает ссылку на диапазон, построенный с пятью входными данными: (1) начальная точка, (2) смещение строки, (3) смещение столбца, (4) высота в строках, (5) ширина в столбцы. OFFSET удобен в формулах, требующих динамического диапазона.

Цель

Создать ссылку смещения от заданной начальной точки

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

Ссылка на ячейку.

Синтаксис

= СМЕЩЕНИЕ (ссылка; строки; столбцы; (высота); (ширина))

Аргументы

ссылка - начальная точка, указанная как ссылка на ячейку или диапазон.
rows - количество строк, которые нужно сместить ниже начальной ссылки.
cols - количество столбцов для смещения вправо от начальной ссылки.
height - (необязательно) высота в строках возвращаемой ссылки.
width - (необязательно) ширина в столбцах возвращаемой ссылки.

Версия

Excel 2003

Примечания по использованию

Функция СМЕЩЕНИЕ Excel возвращает динамический диапазон, состоящий из пяти входных данных: (1) начальная точка, (2) смещение строки, (3) смещение столбца, (4) высота в строках, (5) ширина в столбцах.

Отправной точкой ( ссылочным аргументом) может быть одна ячейка или диапазон ячеек. Эти строки и COLS аргументы число клеток «смещение» от начальной точки. В высоту и ширина аргумент является необязательным и определяет размер диапазона , который создается. Если высота и ширина опущены, они по умолчанию равны высоте и ширине ссылки .

Например, для ссылки на C5, начиная с A1, используется ссылка A1, rows - 4, а cols - 2:

=OFFSET(A1,4,2) // returns reference to C5

Чтобы ссылаться на C1: C5 из A1, ссылка - A1, rows - 0, cols - 2, height - 5, а width - 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Примечание: ширину можно не указывать, так как по умолчанию она будет равна 1.

СМЕЩЕНИЕ, как правило, заключено в другую функцию, которая ожидает диапазон. Например, для SUM C1: C5, начиная с A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

Основная цель OFFSET - позволить формулам динамически подстраиваться под доступные данные или вводимые пользователем данные. Функцию OFFSET можно использовать для создания динамического именованного диапазона для диаграмм или сводных таблиц, чтобы гарантировать, что исходные данные всегда актуальны.

Примечание. В документации Excel указано, что высота и ширина не могут быть отрицательными, но отрицательные значения, похоже, работали нормально с начала 1990-х годов. Функция OFFSET в Google Sheets не допускает отрицательного значения аргументов высоты или ширины.

Примеры

В приведенных ниже примерах показано, как можно настроить смещение для возврата различных типов диапазонов. Эти экраны были сняты с помощью Excel 365, поэтому OFFSET возвращает динамический массив, если результатом является более одной ячейки. В более старых версиях Excel вы можете использовать клавишу F9 для проверки результатов, возвращаемых функцией OFFSET.

Пример # 1

На экране ниже мы используем OFFSET, чтобы вернуть третье значение (март) во второй столбец (запад). Формула в H4:

=OFFSET(B3,3,2) // returns D6

Пример # 2

На экране ниже мы используем OFFSET, чтобы вернуть последнее значение (июнь) в третьем столбце (север). Формула в H4:

=OFFSET(B3,6,3) // returns E9

Пример # 3

Ниже мы используем СМЕЩЕНИЕ, чтобы вернуть все значения в третьем столбце (Север). Формула в 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 возвращает только ссылку, ячейки не перемещаются.
И строки, и столбцы могут быть указаны как отрицательные числа, чтобы изменить их нормальное направление смещения - отрицательное смещение столбцов влево и отрицательное смещение строк выше.
СМЕЩЕНИЕ - это «непостоянная функция» - она будет пересчитываться при каждом изменении рабочего листа. Неустойчивые функции могут замедлить работу больших и сложных книг.
OFFSET отобразит #REF! значение ошибки, если смещение находится за пределами края листа.
Если высота или ширина опущены, используются высота и ширина ссылки .
СМЕЩЕНИЕ можно использовать с любой другой функцией, которая ожидает получить ссылку.
В документации Excel говорится, что высота и ширина не могут быть отрицательными, но отрицательные значения работают.