Функция смещения в Excel

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

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

Это делает функцию СМЕЩ чрезвычайно полезной в нестабильных таблицах Excel. Вы можете просто использовать функцию СМЕЩ, но истинный потенциал этой функции Excel легко увидеть, только если вы вложите ее в другую функцию.

Что такое функция СМЕЩ в Excel?

Функция Excel СМЕЩ возвращает ссылку на ячейку или диапазон ячеек. OFFSET рассчитывает 5 параметров, и его формула выглядит следующим образом:

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

OFFSET примет позицию ссылки на ячейку, переместится оттуда по номеру строки и столбца, а затем вернет ссылку на целевую ячейку.

Если параметры высоты и ширины больше 1, возвращается диапазон ячеек внутри этой области. Высота и ширина — два необязательных параметра. Если они не введены, OFFSET по умолчанию помещает значение 1 в пустое место этих двух параметров и возвращает ссылку на одну ячейку.

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

Чтобы использовать СМЕЩ в Excel, вам необходимо ввести 3 необходимых параметра. После выбора контрольной ячейки OFFSET переместится оттуда в ячейки ниже в соответствии с параметром rows. Он переместится вправо в соответствии с параметром cols.

Отсюда OFFSET возвращает ссылку на целевую ячейку. Если установлены параметры высоты и ширины, OFFSET вернет ссылки на диапазон ячеек. Здесь исходной позицией целевой ячейки будет верхняя левая ячейка.

В теории это звучит сложно, но на практике все гораздо проще. Вы ясно увидите это на примере ниже.

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

В этом примере мы будем использовать функцию СМЕЩ для ссылки на C4. При простейшем использовании функции СМЕЩ выводится содержимое целевой ячейки. У нас будет содержимое ячейки C4 — номер 5 после ввода этой формулы. Подробности каждого шага следующие:

1. Выберите ячейку для ввода формулы. Вот G1.

2. В строке формул введите:

=OFFSET(A1, 3, 2)

Эта формула вызывает функцию СМЕЩ в ячейке A1 , затем перемещается вниз к ячейке 3, затем перемещается на 2 ячейки вправо к ячейке C4 .

3. Нажмите Enter , чтобы выполнить формулу, ячейка вернет число 5. Это число в ячейке C4. Эта формула работала правильно.

Ссылка на диапазон ячеек

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

На этот раз мы вернемся к ячейкам от C4 до D9, используя функцию СМЕЩ в Excel. Как и предыдущий пример, в этом примере также используется A1 в качестве первой контрольной точки.

1. Выберите ячейку, в которую вы хотите ввести формулу. Вот ячейка G1.

2. В строке формул введите:

=OFFSET(A1, 3, 2, 6, 2)

Эта формула работает так же, как первый пример, за исключением того, что когда она достигает целевой ячейки C4, она вычисляет 6 строк и 2 столбца вместо одной целевой ячейки. C4 будет первой ячейкой в ​​этом новом диапазоне.

3. Нажмите Enter .

Теперь в месте ввода формулы появится диапазон ячеек от C4 до D9. Результатом будет массив.

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