Как сделать смещение в эксель

Обновлено: 02.07.2024

Рассмотрим примеры использования функции СМЕЩ в 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 последних дней.
Просуммируем их воспользовавшись функцией СУММ:

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

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

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

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

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

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

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

Примеры

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

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

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

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

Добрый день!

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

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

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

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

А теперь рассмотрим несколько стандартных примеров, как используется функция СМЕЩ в Excel: При работе функция СМЕЩ в Excel может иметь некоторые ограничения или нюансы:

При работе с функцией СМЕЩ возникает закономерный вопрос, почему просто указать диапазон, типа A1:D5, нельзя прямо? А дело в том что причин может быть несколько:

Во-первых, в случае, когда точный адрес нам неизвестен. В большинстве случаев нам известна, только лишь стартовая ячейка и иногда отсутствуют знания фактического адреса. В таких ситуациям использовать функцию СМЕЩ вExcel обязательно;
Во-вторых, необходимость использования динамических диапазонов. Как вы понимаете указанный диапазон в ссылке A1:D5 и будет возвращать аналогичное значение, то есть он имеет статичную основу, а в некоторых случаях нам нужно динамичный диапазон. Эта необходимость возникает в случаях, когда данные изменяются (добавляются новые строчки и колонки).

Чтобы функция СМЕЩ в Excel стала вам ближе, рассмотрим ее на основе примера, вы ведете учет топлива в АТП, или транспортного отдела другой компании. Вот вам для заказа или учёта ГСМ нужно отслеживать постоянно расход топлива и сопутствующих материалов. Для этих целей мы создаем простую таблицу следующего вида (я использую небольшие числа для наглядности): Итак, какое же будет среднее значение расхода ГСМ, исходя из того что наши данные расхода находятся в диапазоне B2:B23, то нам можно написать формулу =СРЗНАЧ(B2:B23) и всё у нас получилось. Но, увы, нам очень важно, чтобы изменение в формуле происходили ежедневно и автоматически. Для этого нам нужно использовать функцию СМЕЩ всего один раз для вычисления среднего значения расхода топлива за последнюю неделю и вопрос будет закрыт, для этого нам нужна формула следующего вида:

=СРЗНАЧ(СМЕЩ(B2;СЧЁТЗ(B2:B300) -7;0;7;1))

До новых встреч на страницах сайта.

“Мало кто из нас может вынести бремя богатства. Конечно, чужого
”
Марк Твен

Дана такая формула. Что делает функция ЕСЛИ вроде как понятно, но не понятно что здесь делает функция СМЕЩ. До этого раза я с ней не сталкивалась. В инете посмотрела и мало что поняла. Но понимаю, что синтаксис построения в данном случае отличается от построения по умолчанию. Я не понимаю, где здесь смещение по строкам и столбцам и что значит "=2"? Почему именно "2"? Если есть возможность, то можно как нибудь словами описать действие этой формулы, пожалуйста!

Функция СМЕЩ смещает ссылку на указанное количество строк и столбцов относительно указанной ячейки. Например, =СМЕЩ(A1;1;1) вернет значение в ячейке B2, т.к. в формуле указано смещение на 1 строку (вниз) и 1 столбец (вправо) относительно A1.

В приведенной формуле проверяется условие совпадения номера из строки на одну ниже из столбца С с числом 2. Далее в зависимости от выполнения условия производится вычисление. Про смысл этой формулы нужно спрашивать у ее автора.

Функция СМЕЩ в Excel. Как автоматически расширить диапазон?

Функция СМЕЩ в Excel — хороший помощник. Для чего мы можем ее использовать? По большому счету, функция нужна для того, чтобы выбрать значение в ячейке, отстоящей на N столбцов и на M строк от уже заданной ячейки. И, если это необходимо, выделить указанный диапазон для последующих вычислений. Но ценность этих функций не так велика без комбинации с функцией, например, СЧЕТЗ. Это позволит автоматически расширять диапазон автосуммы или, к примеру, рассчитывать среднее значение за последние семь дней (при добавлении новых значений).

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

Как работает функция СМЕЩ в Excel?

Сначала начнем как всегда с официального объяснения и состава функции. Как говорит нам официальный сайт Microsoft:

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

Немного сложновато? Согласен, сейчас разберемся.

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

Ссылка. Ссылка на ячейку, от которой мы начинаем отсчет (ячейка C2 в примере). Обязательный аргумент.
Смещ_по_строкам. Чтобы найти ячейку D3, нам нужно отступить от ячейки C2 вниз на одну строку. Обязательный аргумент.
Смещ_по_столбцам. Чтобы найти ячейку D3, нам нужно отступить от ячейки C2 вправо на один столбец. Обязательный аргумент.
Высота. Если необходимо указать диапазон ячеек, то указываем высоту диапазона, т.е. выше ячейки D3 Необязательный аргумент.
Ширина. Если необходимо указать диапазон ячеек, то указываем ширину диапазона, т.е. левее ячейки D3. Необязательный аргумент

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

Если обязательные аргументы не указанны, они считаются за ноль

Теперь самое интересное:

Как автоматически расширить диапазон суммирования при добавлении строки в формуле со СМЕЩ в Excel?

Часто мы делаем Автосумму, формируется диапазон суммирования.

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

Запишем формулу, состоящую из функций СМЕЩ и СЧЁТЗ.

Т,е. отсчитываем от ячейки B2 вниз количество строк равное количеству значений в столбец А.

Как автоматом посчитать первые 4 значения этого диапазона при добавлении строки?

В ячейку B12 введите формулу =СУММ(СМЕЩ(B2;;;3)), диапазон отсчитывается от ячейки B2 вниз на 3 строки

Доброго времени. Суток подскажите как в excel сделать автоматический сдвиг выбранного диапазона, при добавлении нового столба.(см.скриншот)
Нужно чтоб при добавлении новой недели, этот диапазон как сдвигался и соответственно данные обновлялись в диаграмме.

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

Читайте также: