Функция смещения в Excel — использование и примеры

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

Изображение 1 функции смещения в Excel - использование и примеры

Описание

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

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

Синтаксис

= СМЕЩЕНИЕ (ссылка; строки; столбцы; [height]; [width])

Внутри:

  1. ссылка — это обязательный аргумент, это эталонная область, которая должна быть отправной точкой (как ориентир), а затем на основе других аргументов перейти к эталонной ссылке.
  2. rows — обязательный аргумент, это количество строк, которые вы хотите переместить вверх или вниз по ссылке. Если строки положительные, функция переместится ниже ссылки, если строки отрицательны, то переместится вверх к ссылке.
  3. cols — обязательный аргумент, это количество столбцов, на которые вы хотите переместить ссылку влево или вправо после того, как функция переместилась в строках. Если столбцы положительны, функция переместится вправо на количество ячеек, равное количеству столбцов, если количество столбцов отрицательное, функция переместится влево от номера ячейки на количество столбцов.
  4. height — необязательный аргумент, это высота, рассчитанная по количеству строк, которые должна возвращать ссылка.
  5. width — необязательный аргумент, это ширина, рассчитанная по количеству столбцов, которые должна возвращать ссылка.

Примечание

  1. Если аргументы row и cols заставляют возвращаемую ссылочную область выходить за пределы границы листа, функция вернет значение ошибки #REF.
  2. Если высота и ширина опущены в функции OFFSET, возвращаемая ссылка по умолчанию имеет ту же высоту и ширину, что и эталонная область.
  3. Если высота и ширина> 1 означает, что функция должна возвращать более 1 ячейки, необходимо использовать функцию СМЕЩЕНИЕ формулы массива, иначе она получит # ЗНАЧ!
  4. Аргументы высоты и ширины должны быть положительными.
  5. Функция СМЕЩЕНИЕ на самом деле не перемещает ячейки и не изменяет выделение, она только возвращает ссылку.

Например

1. Возвращает ссылку на 1 ячейку, начиная с D8, перемещаясь на 3 строки вправо на 2 столбца.

Используйте следующую формулу: = СМЕЩЕНИЕ (D8; 3; 2)

Изображение 2 функции смещения в Excel - использование и примеры

2. Возвращает ссылку на 2 ячейки C10: C11 начинается с ячейки D8, перемещается на 2 строки вниз и на 1 столбец влево.

Если вы просто напишете формулу формулы СМЕЩЕНИЕ = СМЕЩЕНИЕ (D8; 2; -1; 2; 1), функция вернет # ЗНАЧ! Ошибка. Поскольку этот пример возвращает ссылку на 2 ячейки, вам необходимо применить формулу массива.

Изображение 3 функции смещения в Excel - использование и примеры

Поскольку ссылка возвращается как 2 ячейки C10: C11, если вы выбираете любые две соседние ячейки в столбце, затем введите функцию = OFFSET (D8; 2; 1; 2; 1), вводя их, вы нажимаете Ctrl + Shift + Enter, чтобы преобразовать в формулу массива. Вы получите следующий результат:

Изображение 4 функции смещения в Excel - использование и примеры

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

Если вы возвращаете ссылку с несколькими ячейками, вам нужно написать формулу массива, как в приведенном выше примере, но в сочетании с функцией SUM вам больше не нужно использовать формулы массива. Вы вводите функцию = СУММ (СМЕЩЕНИЕ (D8; 2; -1; 2; 1))

Изображение 5 функции смещения в Excel - использование и примеры

4. Примените конкретный пример, используя функцию СУММ в сочетании с СМЕЩЕНИЕ.

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

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

Изображение 6 функции смещения в Excel - использование и примеры

Каждый день у вас появляются новые данные, поэтому вам нужно добавлять новые строки в таблицу данных.

Изображение 7 функции смещения в Excel - использование и примеры

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

= СУММ (D7: СМЕЩЕНИЕ (D14; -1; 0))

Функция СУММ суммирует из первой ячейки столбца «Доход», которая является ячейкой D7, до возвращенной ссылки функции СМЕЩЕНИЕ. Функция СМЕЩЕНИЕ возвращает ссылку, которая является позицией в строке 1 строки общего дохода, которая, как показано, является позицией D13.

Изображение 8 функции смещения в Excel - использование и примеры

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

Изображение 9 функции смещения в Excel - использование и примеры

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

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован.