✅ OFFSET в Excel (формула, примеры) - Как использовать функцию OFFSET?

СМЕЩЕНИЕ в Excel

Функция Excel OFFSET (Содержание)

СМЕЩЕНИЕ в Excel
ОФСЕТНАЯ формула в Excel
Как использовать функцию OFFSET в Excel?

СМЕЩЕНИЕ в Excel

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

Ниже приведена формула СМЕЩЕНИЕ в Excel:

Функция OFFSET в Excel состоит из следующих аргументов:

Ссылка: это аргумент, из которого мы хотим основать смещение. Это может быть ссылка на ячейку или диапазон соседних ячеек, если нет, Offset возвращает значение #VALUE! Значение ошибки.
Ряды: это нет. строк для смещения. Если мы используем положительное число, оно смещается в строки ниже, а если используется отрицательное число, то оно смещает строки выше.
Столбцы: это нет. столбцов для смещения. Таким образом, это та же концепция, что и для строк: если мы используем положительное число, оно смещается к столбцам справа, а если используется отрицательное число, то смещается к столбцам слева.
Высота: этот аргумент не является обязательным. Это должно быть положительное число. Это число, которое представляет количество строк в возвращенной ссылке.
Ширина: это также необязательный аргумент. Это должно быть положительное число. Это число, которое представляет количество столбцов в возвращенной ссылке.

Примечания:

Функция OFFSET в Excel возвращает #REF! значение ошибки, если строки и столбцы смещены относительно края листа.
В функции OFFSET предполагается, что она будет той же высоты или ширины, что и опорная, если высота или ширина не указаны.
OFFSET возвращает ссылку, он фактически не перемещает никакие ячейки или диапазон ячеек и не изменяет выбор. Может использоваться с любой функцией, ожидающей ссылочный аргумент.

Как использовать функцию OFFSET в Excel?

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

Вы можете скачать этот шаблон Excel с функцией OFFSET здесь - Шаблон Excel с функцией OFFSET

Пример № 1

Формула Offset возвращает ссылку на ячейку на основе начальной точки, строк и столбцов, которые мы указываем. Мы можем видеть это в приведенном ниже примере:

= СМЕЩЕНИЕ (А1, 3, 1)

Формула сообщает Excel, что нужно рассмотреть ячейку A1 для начальной точки (ссылка), затем переместить 3 строки вниз (строки) и 1 столбец влево (аргумент столбцов). После этого формула OFFSET возвращает значение в ячейке B4.

Рисунок слева показывает, что маршрут функции и снимок экрана справа демонстрируют, как мы можем использовать формулу OFFSET для данных в реальном времени. Разница между этими двумя формулами заключается в том, что вторая (справа) содержит ссылку на ячейку (D1) в аргументе строки. Но поскольку ячейка D1 содержит число 1, и в аргументе строк первой формулы фигурирует точно такое же число, оба возвращают одинаковый результат - значение в B2.

Пример # 2 Использование функции OFFSET для Sum

Поскольку мы знаем, что функцию OFFSET можно также использовать с другой функцией Excel, следовательно, в этом примере мы увидим функцию SUM.

Предположим, что на приведенном выше рисунке мы должны выяснить сумму оценок, а затем использовать функцию OFFSET.

Формула ниже:

Итак, в приведенной выше формуле D5 - первая ячейка, в которой начинаются данные, которая является ссылкой в качестве начальной ячейки. После этого значение строк и столбцов равно 0, поэтому мы можем поставить его как 0, 0. Тогда есть 4, что означает 4 строки ниже ссылки, для которой Excel должен вычислить сумму, а затем, наконец, это 1, это означает, что есть 1 столбец в качестве ширины.

Мы можем сослаться на рисунок выше, чтобы связать объясненный пример.

И ниже результат:

Просто нужно нажать Enter после ввода формулы в нужную ячейку, и тогда мы получим результат.

Что следует помнить о функции смещения в Excel

Функция OFFSET в Excel просто возвращает ссылку, она не перемещает никакие ячейки или диапазон ячеек в действительности.
Если формула OFFSET возвращает диапазон ячеек, строки и столбцы всегда ссылаются на верхнюю левую ячейку в возвращаемом диапазоне.
Ссылка должна включать ячейку или диапазон ячеек, в противном случае формула будет возвращать ошибку #VALUE.
Если строки и столбцы указаны по краю электронной таблицы, формула смещения в Excel вернет #REF! ошибка.
Функция OFFSET может использоваться в любой другой функции Excel, которая принимает ссылку на ячейку или диапазон в своих аргументах.

Например, если мы самостоятельно используем формулу = OFFSET (A1, 3, 1, 1, 3), это даст значение #VALUE! Ошибка, поскольку диапазон возврата (1 строка, 3 столбца) не помещается в ячейку. Однако, если мы сопоставим его с функцией SUM, как показано ниже:

= СУММА (СМЕЩЕНИЕ (А1, 3, 1, 1, 3))

Формула смещения возвращает сумму значений в 1 строке в диапазоне из 3 столбцов, который находится на 3 строки ниже и на 1 столбец справа от ячейки A1, т.е. сумма значений в ячейках B4: D4.

Как мы видим, общее количество 36 + 63 + 82 выделенного марта равно 181, что стало результатом G2 после применения формулы sum и Offset. (выделено желтым цветом)

Использование функции OFFSET в Excel

Поскольку мы увидели, что то, что функция OFFSET фактически делает с помощью приведенного выше примера, у нас могут возникнуть вопросы: зачем беспокоиться о спонтанной формуле OFFSET, которая немного сложна, почему бы просто не использовать прямую ссылку, такую как sum или B4: D4?

Функция OFFSET в Excel очень хороша для ниже:

Получение диапазона из начальной ячейки:

Иногда мы не знаем точный или фактический адрес диапазона, мы знаем только, откуда он начинается с определенной ячейки. В этом случае мы можем использовать функцию OFFSET, которая проста в использовании.

Создание динамического диапазона:

Ссылки, подобные приведенному выше примеру B1: C4, являются статическими, это означает, что они всегда ссылаются на заданный или фиксированный диапазон. Но в некоторых случаях задачи легче выполнять с динамическими диапазонами. Это особенно помогает, когда мы работаем с изменением данных. Например, у нас может быть электронная таблица, в которую новая строка или столбец вставляется или добавляется каждый день / неделю / месяц, в этом случае поможет функция OFFSET.

Ограничения и альтернативы функции OFFSET в Excel

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

Ниже приведены критические ограничения функции OFFSET в Excel:

Функция, требующая ресурсов:

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

Сложность в обзоре:

Как мы знаем, ссылки, возвращаемые функцией Offset, являются динамическими или изменяющимися и могут содержать большую формулу, которая может быть сложно исправить или отредактировать согласно требованию.

Альтернативы функции OFFSET в Excel:

Функция INDEX:

Функция INDEX в Excel также может быть использована для создания динамического диапазона ссылок. Это не совсем то же самое, что OFFSET. Но, как и в OFFSET, функция INDEX не слишком изменчива, поэтому она не будет замедлять работу Excel, что является ограничением для OFFSET.

НЕПОСРЕДСТВЕННАЯ функция:

Мы также можем использовать функцию INDIRECT для создания динамического диапазона ссылок из многих источников, таких как значения ячеек , текст и именованные диапазоны.

OFFSET в Excel (формула, примеры) - Как использовать функцию OFFSET?