Как правильно использовать функцию СМЕЩ в Excel

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

В компании создается база данных по приборам, купленным в течение года. Под таблицей указывается общая сумма стоимости всех приборов (рис.1). Пока нового прибора нет, таблица остается неизменной, и функция СУММ отлично справляется со своей задачей.
База данных по приборам, купленным в течение года
Но как только в таблицу добавляется новая строка, то функция СУММ никак не отреагирует на добавленное число и сумма не меняется (об этом говорит появление зеленого треугольника в верхнем левом углу ячейки и восклицательного знака рядом), потому что команда рассчитана для определенного диапазона. Но сумма, посчитанная с помощью функции СМЕЩ, изменилась и учитывает добавленную строку (рис. 2).
Сумма, посчитанная с помощью функции СМЕЩ, изменилась и учитывает добавленную строку
Рассмотрим как правильно записать формулу для команды СМЕЩ. Т.к. задача – посчитать сумму, то функция СУММ будет внешней. Первым числом в СУММ будет ссылка на первоначальную ячейку, с которой пойдет подсчет. Это D2. После нее записываем команду СМЕЩ, которая будет находиться внутри СУММ.

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

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

Таким образом, у нас получилась вот такая формула =СУММ(D2:СМЕЩ(D23;-1;0)).

Обратите внимание, поскольку в функции СУММ мы указываем диапазон, то между первой ячейкой и функцией СМЕЩ — стоит двоеточие, а не точка с запятой.
Top