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

Что такое офсетная формула в Excel?

Формула Excel OFFSET (Содержание)

Что такое офсетная формула в Excel?
Как использовать формулу OFFSET в Excel?

Что такое офсетная формула в Excel?

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

Синтаксис формулы OFFSET в Excel

Объяснение функции OFFSET в Excel:

ссылка: отправная точка для OFFSET или, можно сказать, диапазон ячеек, который можно рассматривать как основу для OFFSET.
rows : количество строк в OFFSET ниже или выше базовой ссылки.
cols: количество столбцов в OFFSET справа или слева от базовой ссылки.
высота: количество строк в возвращаемой ссылке.
ширина: количество столбцов в возвращенной ссылке.

Высота и ширина являются необязательными параметрами для этой функции и могут использоваться для определения размера создаваемой ссылки.

Как использовать формулу OFFSET в Excel?

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

Вы можете скачать этот шаблон Formula Excel здесь - OFFSET Formula Excel Template

Excel OFFSET Formula - Пример № 1

Предположим, у нас есть данные, приведенные ниже.

В ячейку E2 введите следующую формулу:

= OFFSET (A1, 11, 1)

Закончив с формулой, нажмите Enter и посмотрите результат.

Эта формула взяла ссылку из ячейки A1 (год) и переместилась на 11 строк после A1 (то есть строка № 12), а затем получила значение, которое является координатой 11- ^й строки после A1 и первого столбца ( столбец A = 0 столбец B = 1. В этом случае, как мы знаем, мы можем идти как вправо, так и влево). Что составляет $ 141, 27 млн.

Excel OFFSET Formula - Пример № 2

Теперь мы увидим еще один пример ссылочной ячейки для определенного года. Поместите следующую формулу в ячейку E3:

= OFFSET (A1, 11, 0)

Закончив с формулой, нажмите Enter и посмотрите результат.

Если вы видели формулу, она такая же, как и в предыдущем случае, единственное изменение - в ссылке на столбец. Ссылка на столбец 0 (ноль) означает, что значение будет получено из столбца A для 11- ^й строки после A1 (то есть 2010 года).

Excel OFFSET Formula - Пример № 3

В этом примере мы вычислим сумму последних N строк, используя функцию OFFSET в Excel. Общая формула для суммирования последних N чисел:

= СУММА (СМЕЩЕНИЕ (A1, COUNT (A: A), 0, -N))

Чтобы суммировать последние N наблюдений из ваших данных, вы можете использовать функцию SUM вместе с функциями OFFSET и COUNT вместе. В этом примере мы суммируем последние 5 точек данных. Обратите внимание, что когда мы говорим N, это обобщение любого целочисленного значения. Таким образом, вы можете рассчитать последние 3, 4, 5 и т. Д. В зависимости от набора данных и ваших требований.

Давайте сделаем это для нашего набора данных:

Введите следующую формулу в ячейку E2 файла Excel.

= СУММА (СМЕЩЕНИЕ (B1, СЧЕТ (B: B), 0, -5))

Нажмите Enter, чтобы увидеть результат.

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

В этом случае начальная ссылка предоставляется в терминах B1 (как первый аргумент функции OFFSET).
Мы использовали функцию COUNT, потому что нам нужна сумма наблюдений из последнего значения. Функция COUNT помогает OFFSET определить, сколько наблюдений (строк) имеется в столбце B. Она подсчитывает все числовые значения в столбце B (19 - это счетчик).
При значении 19 формула OFFSET становится следующей:

= СМЕЩЕНИЕ (B1, 19, 0, -5)

Эта формула начинает смещение в точке B1 с 19 строками и одним и тем же столбцом (0). Тогда параметр высоты -5 помогает этой функции расширить диапазон в обратном направлении на 5 строк (см. Знак минус, связанный с 5).
Динамический диапазон последних пяти наблюдений приводится в качестве входных данных для функции суммы. Что дает в сумме те из столбца B.

Excel OFFSET Formula - Пример № 4

В этом примере мы рассчитаем средние продажи за последние 3 года. Функция OFFSET может использоваться в качестве аргумента для вычисления того же.

Поместите следующую формулу в ячейку E2:

= МЕДИАНА (СМЕЩЕНИЕ (B1, СЧЕТ (B: B), 0, -3))

Нажмите Enter, чтобы увидеть результат.

Эта формула работает по линиям, похожим на предыдущую.

OFFSET Создает динамический диапазон последних трех наблюдений из столбца B с помощью функции COUNT (= OFFSET (A1, 19, 0, -3)).
Это может быть дано как выход в функцию MEDIAN. Затем сортирует наблюдения в порядке возрастания или убывания, а затем выбирает самое среднее значение, как вы видите в выводе ($ 212, 07).

Excel OFFSET Formula - Пример № 5

Здесь мы вычислим срединный год, используя функцию MEDIAN и OFFSET.

Используйте следующую формулу для того же в ячейке E3:

= Медиана (OFFSET (А1, COUNT (А: А), 0, -3))

Нажмите Enter, чтобы увидеть результат.

То же самое произошло здесь. Формула заняла динамический диапазон, созданный функцией OFFSET с помощью COUNT. И использовал его в качестве аргумента для функции MEDIAN, которая затем сортировала значения года в порядке возрастания или убывания, а затем выводила результат как 2017.

Использование формулы OFFSET при создании динамических диапазонов. Всегда лучше работать с динамическими диапазонами, а не со статическими. Поскольку у вас могут быть данные, которые добавляются время от времени. Например, Сотрудник Мастер. Основные данные вашего сотрудника будут постоянно увеличиваться время от времени, поскольку к ним должны присоединиться новые сотрудники. Каждый раз использовать одну и ту же формулу не имеет никакого смысла. Вместо этого создайте динамический диапазон, используя функцию OFFSET, и посмотрите на магию. Это, безусловно, сэкономит вам время.

То, что нужно запомнить

Функция OFFSET возвращает только ссылку на определенный диапазон ячеек в Excel. Он не перемещает клетки с их первоначальных позиций.
По умолчанию эта функция работает в верхней части (в терминах строк) и в правой части (в терминах столбцов). Однако, это может быть предоставлено с отрицательными аргументами, чтобы полностью изменить функциональность по умолчанию. Подобный -отрицательный аргумент строки помогает сместить данные снизу вверх, а отрицательный аргумент col помогает сместить данные влево.
Формула OFFSET может замедлить функциональность Excel, так как это формула, которая пересчитывается каждый раз, когда происходит изменение в листе.
Ошибка #REF будет выдана, если ваш диапазон находится вне диапазона вашей рабочей книги.
Любая другая функция, которая ожидает ссылку, может использовать функцию OFFSET внутри нее.

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