Синтаксис OFFSET

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

Функция OFFSET возвращает ссылку на ячейку из заданного смещения (строки и столбца) данной ссылки.

Например, если вы дадите ссылку на B4, строку смещения 0 и столбец смещения 1 функции OFFSET, то будет возвращено значение из C4.

Сбивает с толку? Приведем еще несколько примеров. Последний пример функции СМЕЩЕНИЕ рассказывает, как СУММИРОВАТЬ динамически.

Функция OFFSET для получения значения справа и слева от ячейки. У нас есть эта таблица.

1

Теперь, если я хочу получить значение из 2 ячеек прямо до B2 (что означает D2). Я напишу эту формулу СМЕЩЕНИЯ:

=OFFSET(B2,0,2)

Функция СМЕЩЕНИЕ переместит 2 ячейки вправо от ячейки B4 и вернет ее значение. См. Изображение ниже. Если я хочу получить значение от 1 ячейки слева до B2 (что означает A2). Я напишу эту формулу СМЕЩЕНИЯ:

=OFFSET(B2,0,-1)

Знак минус (-) указывает смещение для обратного перемещения.

2

Функция OFFSET для получения значения ячейки снизу и сверху Итак, снова в приведенной выше таблице, если я хочу получить значение из 2-х ячеек любимой в B3 (что означает B5). Я напишу эту формулу СМЕЩЕНИЯ:
=OFFSET(B2,2,0)

Если я хочу получить значение из 1 ячейки слева до B2 (что означает A2). Я напишу эту формулу СМЕЩЕНИЯ:

=OFFSET(B2,-2,0)

3

Совет от профессионалов: * Рассматривайте СМЕЩЕНИЕ как указатель графика, где Ссылка — начало координат, а строка и столбец — оси x и y.

Функция OFFSET для динамического суммирования диапазона Давайте посмотрим на этот пример.

4

В приведенной выше таблице итоговая строка находится в конце таблицы. Со временем вы будете добавлять строки в эту таблицу. Затем вам нужно будет изменить диапазон сумм в формуле. Здесь мы можем воспользоваться функцией OFFSET для динамического суммирования. В настоящее время в ячейке C11 у нас есть = SUM (C2: C10). Замените это формулой ниже.

=SUM(C2:OFFSET(C11,-1,0))

Эта формула просто берет первую строку данных, а затем суммирует диапазон над общей строкой.

5

Смещение для получения СМЕЩЕНИЯ массива функция имеет два необязательных аргумента, [высота] и [ширина]. Не принимайте их как должное. Когда функции OFFSET предоставлены аргументы [высота] и [ширина], она возвращает двумерный массив. Если в качестве аргумента передается только один из них, он возвращает размерный массив значений.

Если вы введете эту формулу в любую ячейку:

=SUM(OFFSET(C1,1,0,9,3))

Он суммирует значения в диапазоне от C2 до 9 строк ниже и 3 столбцов справа.

OFFSET (C1,1,0,9,3): это будет преобразовано в \ {8,8,7; 6,1,2; 8,5,8; 5,1,5; 8,3,5; 8 , 3,9; 8,9,2; 3,5,4; 6,6,5}.

6

Итак, в заключение, смещение — очень полезная функция Excel, которая может помочь вам решить множество проблемных потоков. Сообщите мне, как вы используете функцию СМЕЩЕНИЕ в своей формуле и где она вам больше всего помогала.

  • Статьи по теме:

===

link: / lookup-formulas-retrieving-every-nth-value-in-a-range [Как получить каждое N-е значение из списка]

link: / lookup-formulas-retrieving-every-nth-value-in-a-range [Как получить каждое N-е значение из списка]

link: / lookup-formulas-how-to-look-up-address-in-excel [Как найти адрес в Excel]

Популярные статьи:

link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-повышения-продуктивность [50 сочетаний клавиш Excel для повышения производительности]

link: / формулы-и-функции-введение-функции-vlookup [Как использовать функцию ВПР в Excel]

link: / tips-countif-in-microsoft-excel [Как использовать функцию СЧЁТЕСЛИ в Excel 2016]

link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]