Смещ в excel

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

В этой статье описаны синтаксис формулы и использование функции СМЕЩ в Microsoft Excel.

Описание

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

Синтаксис

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

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

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

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

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

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

Примечания

Если аргументы «смещ_по_строкам» и «смещ_по_столбцам» выводят ссылку за границы рабочего листа, функция СМЕЩ возвращает значение ошибки #ССЫЛ!.

Если высота или ширина опущена, то предполагается, что используется та же высота или ширина, что и в аргументе «ссылка».

Функция СМЕЩ фактически не передвигает никаких ячеек и не меняет выделения; она только возвращает ссылку. Функция СМЕЩ может использоваться с любой функцией, в которой ожидается аргумент типа «ссылка». Например, с помощью формулы СУММ(СМЕЩ(C2;1;2;3;1)) вычисляется суммарное значение диапазона, состоящего из трех строк и одного столбца и расположенного одной строкой ниже и двумя столбцами правее ячейки C2.

Пример

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

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

Функция СМЕЩ() часто используется при создании динамических диапазонов . Рассмотрим ее подробнее.

Синтаксис функции СМЕЩ()

• ссылка — ссылка, от которой вычисляется смещение;
• смещ_по_строкам — количество строк, которые требуется отсчитать вверх или вниз, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку (по умолчанию =0);
• смещ_по_столбцам – аналогично смещ_по_строкам, только смещение отсчитывается по столбцам (по умолчанию =0);
• высота — число строк возвращаемой ссылки. Значение аргумента «высота» должно быть положительным числом;
• ширина — число столбцов возвращаемой ссылки (по умолчанию =1).

Чтобы было понятнее, потренируемся с функцией СМЕЩ() , используя файл примера .

Примеры

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

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

Для подсчета суммы значений в столбце Продажа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) и т.д. Но, записав формулу ввиде:

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

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

Суммирование по «окну» на листе функцией СМЕЩ (OFFSET)

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

В выпадающих списках в желтых ячейках F3 и F5 пользователь выбирает станции отправления и назначения, а в зеленой ячейке F7 должна подсчитываться сумма всех ячеек в заданном «окне» на листе. Для проезда от Останкино до Ховрино, как на рисунке, например, нужно будет просуммировать все ячейки в обведенном зеленым пунктиром диапазоне.

Как считать суммму — понятно, а вот как определить диапазон ячеек, которые нужно просуммировать? Ведь при выборе станций он будет постоянно трансформироваться?

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

=СМЕЩ( Точка_отсчета ; Сдвиг_вниз ; Свиг_вправо ; Высота ; Ширина )

Эта функция на выходе дает ссылку на диапазон, сдвинутый относительно некоей стартовой ячейки (Точка_отсчета) на определенное количество строк вниз и столбцов вправо. Причем размер диапазона («окна») тоже может задаваться параметрами Высота и Ширина.

В нашем случае, если взять за точку отсчета ячейку А1, то:

• Точка отсчета = А1
• Сдвиг_вниз = 4
• Свиг_вправо = 2
• Высота = 4
• Ширина = 1

Чтобы рассчитать необходимые для СМЕЩ аргументы, давайте сначала применим функцию ПОИСКПОЗ (MATCH) , которую мы уже разбирали, для вычисления позиций станций отправления и назначения:

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

Вот и все, задача решена 🙂

В отличие от большинства остальных функций Excel, СМЕЩ является волатильной (volatile) или, как еще говорят, «летучей» функцией. Обычные функции пересчитываются только в том случае, если меняются ячейки с их аргументами. Волатильные же пересчитываются каждый раз при изменении любой ячейки. Само-собой, это отрицательно сказывается на быстродействии. В больших тяжелых таблицах разница по скорости работы книги может быть очень ощутимой (в разы). Для некоторых случаев быстрее оказывается заменить медленную СМЕЩ на неволатильную ИНДЕКС или другие аналоги.

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

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

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

Описание функции СМЕЩ

СМЕЩ(ссылка; смещ_по_строкам; смещ_по_столбцам; [высота]; [ширина])
Возвращает ссылку на диапазон, смещенный относительно заданной ссылки на указанное количество строк и столбцов.

• Ссылка(обязательный аргумент) — ссылка на ячейку или диапазон смежных ячеек;
• Смещение по строкам(обязательный аргумент) и по столбцам(обязательный аргумент) — число строк и столбцов, на которое результирующий диапазон смещен относительно исходной ссылки; Например, аргументы 4; 3 сместят ссылку на 4 строчки вниз и 3 столбца вправо. При этом оба параметра могут принимать различные значения — положительные (смещение вниз по строкам/вправо по столбцам), нулевые или отрицательные (смещение вверх по строкам/влево по столбцам).
• Высота(необязательный аргумент) и ширина(необязательный аргумент) — высота (в строках) и ширина (в столбцах) возвращаемого диапазона, по умолчанию высота и ширина совпадают с размерами исходной ссылки; Например, аргументы 5; 2 расширят ссылку до диапазона в 5 ячеек высотой и 2 ячейки шириной.

Разберем несколько примеров, чтобы понять принцип работы функции СМЕЩ:

К примеру, формула =СМЕЩ(A1;0;0;5;4) (на рисунке выделена красным цветом) сдвигает ячейку A1 (аргумент функции №1) на (№2) вниз, на (№3) вправо, получаем диапазон A1 (состоящий из одной ячейки), а затем расширяет его до размера 5 (№4) на 4 (№5), т.е. возвращаемая ссылка принимает вид A1:D5 (на рисунке область также выделена красным цветом).
Аналогично, формула =СМЕЩ(A1;1;2;8;3) (выделена синим цветом) сдвигает ячейку A1 на 1 вниз, на 2 вправо, получаем диапазон C2 и расширяет его до размера 8 на 3, т.е. в результате получаем ссылку C2:E9.

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

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

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

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

Пример 1. Функция ПОИСКПОЗ

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

Воспользуемся функцией ПОИСКПОЗ для поиска указанной даты (ячейка D2) в диапазоне с датами (A2:A10).
После чего сместим начальную ячейку (в данном случае B2) на рассчитанную величину вниз за вычетом единицы.
Мы дополнительно вычитаем единицу так как показываем именно смещение относительно начальной ячейки, например, чтобы перейти с первой строки на шестую мы смещаемся ровно на пять строк.
В итоге получаем следующий результат:

Идентичного результата можно добиться и с помощью функции ИНДЕКС — формула =ИНДЕКС(B2:B10;ПОИСКПОЗ(D2;A2:A10;0)) вернет точно такой же результат.

Пример 2. Функция СУММ

Возьмем начальные условия как в предыдущем примере, однако теперь мы посчитаем сумму продаж за последние 7 дней.
Можно воспользоваться стандартной формулой СУММ(B4:B10), но при добавлении новых строчек расчет становится неверным и нам придется каждый раз изменять формулу, поэтому мы пойдем по другому пути.
С помощью функции СЧЁТЗ находим последнюю введенную дату (указываем достаточно большой диапазон A2:A100, чтобы была возможность добавлять новые данные).
Из полученного результата вычитаем 7, чтобы найти первую дату искомого диапазона, поэтому производя сдвиг начальной ячейки (B2) на найденную величину и расширяя диапазон до размеров 7 на 1, мы получим данные за 7 последних дней.
Просуммируем их воспользовавшись функцией СУММ:

При добавлении новых данных в таблицу результат будет автоматически пересчитываться:

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

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