backtotop

Categories: Формулы

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

СМЕЩ в Excel 1

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

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

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

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

Синтаксис:

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

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

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

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

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

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

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

СМЕЩ в Excel 3

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

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

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

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

СМЕЩ в Excel 4

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

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

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

.

.

.


Comments

Там нет комментариев

Leave a Comment:

Ваш e-mail не будет опубликован. Обязательные поля помечены *

четырнадцать − 10 =