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

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

Что возвращает функция

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

Описание

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

Синтаксис

=OFFSET(reference, rows, cols, [height], [width]) – английская версия

=СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина]) – русская версия

Синтаксис

СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])

Аргументы функции СМЕЩ описаны ниже.

  • Ссылка    — обязательный аргумент. Ссылка, от которой вычисляется смещение. Аргумент “ссылка” должен быть ссылкой на ячейку или на диапазон смежных ячеек, в противном случае функция СМЕЩ возвращает значение ошибки #ЗНАЧ!.

  • Смещ_по_строкам    Обязательный. Количество строк, которые требуется отсчитать вверх или вниз, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку. Например, если в качестве значения аргумента “смещ_по_строкам” задано число 5, это означает, что левая верхняя ячейка возвращаемой ссылки должна быть на пять строк ниже, чем указано в аргументе “ссылка”. Значение аргумента “смещ_по_строкам” может быть как положительным (для ячеек ниже начальной ссылки), так и отрицательным (выше начальной ссылки).

  • Смещ_по_столбцам    Обязательный. Количество столбцов, которые требуется отсчитать влево или вправо, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку. Например, если в качестве значения аргумента “смещ_по_столбцам” задано число 5, это означает, что левая верхняя ячейка возвращаемой ссылки должна быть на пять столбцов правее, чем указано в аргументе “ссылка”. Значение “смещ_по_столбцам” может быть как положительным (для ячеек справа от начальной ссылки), так и отрицательным (слева от начальной ссылки).

  • Высота    Необязательный. Высота (число строк) возвращаемой ссылки. Значение аргумента “высота” должно быть положительным числом.

  • Ширина    Необязательный. Ширина (число столбцов) возвращаемой ссылки. Значение аргумента “ширина” должно быть положительным числом.

Примеры

Дана исходная таблица с тремя столбцами.

Задавая параметры функции СМЕЩ() подсчитаем сумму значений в различных диапазонах таблицы. Для визуального наблюдения диапазона, возвращаемого функцией СМЕЩ() , использовано Условное форматирование . Для удобства изменения параметров функции СМЕЩ() использованы Элементы управления Счетчик .

Для подсчета суммы значений в столбце Продажа1 запишем формулу: =СУММ(СМЕЩ($B$2;0;0;8;1)) диапазон суммирования – $B$2:$B$9 (левый верхний угол – $B$2 , высота 8 , смещения верхнего угла нет). Результат 34 .

Для подсчета суммы значений в столбце Продажа2 запишем формулу: =СУММ(СМЕЩ($B$2;0; 1 ;8;1)) Теперь левый верхний угол диапазона суммирования смещен от $B$2 на один столбец вправо, т.е. диапазон суммирования стал $C$2:$C$9 . Результат 68 .

Для подсчета суммы значений в столбцах Продажа1 и Продажа2, изменим ширину диапазона. =СУММ(СМЕЩ($B$2;0;0;8; 2 )) указав ширину в 2 ячейки, результат составит 102 , диапазон будет модифицирован в $В$2:$С$9 .

Добавив смещение по строкам (+1), получим результат 99 : =СУММ(СМЕЩ($B$2; 1 ;0;8;2)) диапазон будет модифицирован в $В$3:$С$9 .

Функция СМЕЩ() vs ИНДЕКС()

Пусть имеется диапазон с числами ( А2:А10 ) Необходимо найти сумму первых 2-х, 3-х, …9 значений. Конечно, можно написать несколько формул =СУММ(А2:А3) , =СУММ(А2:А4) и т.д. Но, записав формулу ввиде:

=СУММ(СМЕЩ(A2;;;4))

получим универсальное решение, в котором требуется изменять только последний аргумент (если в формуле выше вместо 4 ввести 5, то будет подсчитана сумма первых 5-и значений). Вышеуказанная формула эквивалентна формуле =СУММ(A2:ИНДЕКС(A2:A10;4)) , которая, в свою очередь, эквивалентна формуле =СУММ(A2:A5)

Формула ИНДЕКС(A2:A10;4) возвращает ссылку на ячейку А5 .

Особенности применения

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

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

Примеры использования функции СМЕЩ в Excel

Пример 1. Ищем последнюю заполненную ячейку в колонке

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

Функция OFFSET (СМЕЩ) в Excel

=OFFSET(A1,COUNT(A:A)-1,0) – английская версия

=СМЕЩ(A1;СЧЁТ(A:A)-1;0) – русская версия

Эта формула предполагает, что кроме указанных значений нет никаких других, и в этой колонке нет пустых ячеек. Функция работает, подсчитывая общее количество заполненных ячеек и соответствующим образом смещает ячейку “A1”.

Например, в указанном примере есть 8 значений, поэтому функция COUNT(A:A) или СЧЁТ(A:A) возвращает 8. Мы смещаем ячейку “A1” на 7, чтобы получить последнее значение.

Пример 2. Создаем динамический выпадающий список с автоматическим дополнением новых данных

Вы можете использовать принцип из Примера 1 для создания динамического выпадающего списка с автоматическим дополнением новых данных. Например, вы создали выпадающий список и хотите, чтобы при добавлении новых строк, значения автоматически подгружались в выпадающий список.

OFFSET (СМЕЩ) функция в Excel

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

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

Как сделать такой список:

  • Выберите ячейку, в которой вы хотите создать выпадающий список;
  • Нажмите на вкладку Data => Data Tools => Data Validation;
  • В диалоговом окне Data Validation, в разделе Настройки выберите List из выпадающего списка;
  • В параметрах Source укажите формулу =OFFSET(A1,0,0,COUNT(A:A),1) или =СМЕЩ(A1;0;0;СЧЁТ(A:A);1)
  • Нажмите ОК

Как эта формула работает:

Первые три аргумента функции OFFSET (СМЕЩ) A1, 0, 0. Это означает что начальное значение в ячейке “A1”, которое не смещается ни по строкам и по колонкам (0, 0);
Четвертый аргумент функции указывает на высоту, и здесь функция COUNT (СЧЁТ) возвращает суммарное количество ячеек в диапазоне данных для выпадающего списка. Главное условие – отсутствие пустых ячеек в диапазоне.
Пятый аргумент функции “1”, обозначает ширину диапазона данных, которая в нашем случае равна одной колонке.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Формула

Описание

Результат

=СМЕЩ(D3;3;-2;1;1)

Отображает значение ячейки B6 (4)

4

=СУММ(СМЕЩ(D3:F5;3;-2;3;3))

Суммирует диапазон B6:D8.

34

=СМЕЩ(D3;-3;-3)

Возвращает ошибку, так как ссылка указывает на несуществующий диапазон листа.

#ССЫЛ!

Данные

Данные

4

10

8

3

3

6

Нужна дополнительная помощь?

Альтернативы функции OFFSET (СМЕЩ) в Excel

Ввиду некоторых ограничений функции, многие из вас рассматривают альтернативные методы:

  • Функция INDEX (ИНДЕКС) также может использоваться для возврата ссылки на ячейку.
  • Excel таблицы: если вы используете структурированные ссылки в таблице Excel, вам не нужно беспокоиться о добавлении новых данных и необходимости корректировки формул.
Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...