Excel works!

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

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

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

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

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

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

Синтаксис:

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

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

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

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

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

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

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

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

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

=СУММ(СМЕЩ(B2;СЧЁТЗ(A:A);))

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

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

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