Функция СМЕЩ в Excel — хороший помощник. Для чего мы можем ее использовать? По большому счету, функция нужна для того, чтобы выбрать значение в ячейке, отстоящей на N столбцов и на M строк от уже заданной ячейки. И, если это необходимо, выделить указанный диапазон для последующих вычислений. Но ценность этих функций не так велика без комбинации с функцией, например, СЧЕТЗ. Это позволит автоматически расширять диапазон автосуммы или, к примеру, рассчитывать среднее значение за последние семь дней (при добавлении новых значений).
В общем функция сильная и интересная, особенно для определения смещения ячеек в формулах.
Содержание
Как работает функция СМЕЩ в Excel?
Сначала начнем как всегда с официального объяснения и состава функции. Как говорит нам официальный сайт Microsoft:
Эта функция возвращает ссылку на диапазон ячеек, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов. Возвращаемая ссылка может быть отдельной ячейкой или диапазоном ячеек. Можно задавать количество возвращаемых строк и столбцов диапазона
Немного сложновато? Согласен, сейчас разберемся.
Синтаксис:
=СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])
- Ссылка. Ссылка на ячейку, от которой мы начинаем отсчет (ячейка C2 в примере). Обязательный аргумент.
- Смещ_по_строкам. Чтобы найти ячейку D3, нам нужно отступить от ячейки C2 вниз на одну строку. Обязательный аргумент.
- Смещ_по_столбцам. Чтобы найти ячейку D3, нам нужно отступить от ячейки C2 вправо на один столбец. Обязательный аргумент.
- Высота. Если необходимо указать диапазон ячеек, то указываем высоту диапазона, т.е. выше ячейки D3 Необязательный аргумент.
- Ширина. Если необходимо указать диапазон ячеек, то указываем ширину диапазона, т.е. левее ячейки D3. Необязательный аргумент
Если вы указали необязательные аргументы Высота и Ширина, то функция возвратит ошибку, такую функцию можно использовать только в дальнейших вычислениях.
Если обязательные аргументы не указанны, они считаются за ноль
Теперь самое интересное:
Как автоматически расширить диапазон суммирования при добавлении строки в формуле со СМЕЩ в Excel?
Часто мы делаем Автосумму, формируется диапазон суммирования.
Если мы добавим еще строку с новым месяцем, то сумму придется растягивать или заново формировать. Как сделать, чтобы диапазон расширялся автоматически?
Запишем формулу, состоящую из функций СМЕЩ и СЧЁТЗ.
=СУММ(СМЕЩ(B2;СЧЁТЗ(A:A);))
Т,е. отсчитываем от ячейки B2 вниз количество строк равное количеству значений в столбец А.
Как автоматом посчитать первые 4 значения этого диапазона при добавлении строки?
В ячейку B12 введите формулу =СУММ(СМЕЩ(B2;;;3)), диапазон отсчитывается от ячейки B2 вниз на 3 строки