Формула для автоматического суммирования при добавлении новых строк в Excel

Программу Excel любят за возможность быстро и автоматически считать сложные формулы. Достаточно освоить элементарные команды, чтобы экономить значительное количество времени.

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

Примеры работы формулы автосуммы в Excel

Вашему вниманию предлагается формула для автоматического суммирования ячеек в столбце при добавлении и удалении новых строк. Формула использует две функции СУММ и СМЕЩЬ.

Пример функции СМЕЩ и ее синтаксис заполнения аргументов

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

1. Ссылка: это отправная ячейка. Относительно нее и происходит смещение.
2. Смещение по строкам: количество строк, на которые нужно спуститься или подняться от отправной ячейки. Если идем вниз – значение будет положительным (от 1 и далее); если вверх – отрицательным (от -1 и далее).
3. Смещение по столбцам: количество столбцов, на которые нужно уйти вправо или влево от отправной точки. Если вправо – положительное значение; влево – отрицательное.
4. Высота и ширина: параметры диапазона, который будет возвращен функцией СМЕЩ. Если нам нужна одна ячейка, мы ставим цифры 1 и 1. Если нужна таблица, задаем иные значения, например 3 и 4, 6 и 8 и т.д.

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



Пример использования функции СМЕЩ

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

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

Но как только склад предлагает новую марку зубной пасты (назовем ее M), предприятию приходится добавлять дополнительные строки. Если сделать это и ввести новую цифру, то СУММ никак не отреагирует на новую строку (об этом говорит появление зеленого треугольника в верхнем левом углу ячейки и восклицательного знака рядом), потому что команда рассчитана для определенного диапазона. И мы действительно видим, что сумма осталась прежней = 554.

Важно грамотно записать формулу для команды СМЕЩ, чтобы она работала правильно. Действуем по порядку. Наша цель – сосчитать сумму, поэтому функция СУММ будет внешней.

Первым числом в СУММ будет ссылка на первоначальную ячейку, с которой пойдет подсчет. Это D5. После нее записываем команду СМЕЩ, которая будет находиться внутри СУММ.

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

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

Смещение по столбцам нам не нужно, поэтому ставим либо 0, либо просто оставляем пустое место. Не забываем разделять аргументы формулы сумм - двоеточием.

Высоту и ширину не указываем. По умолчанию она будет равна одной ячейке.

После всех действий нажимаем Enter и получаем результат. Функция СМЕЩ просуммировала не только заданный диапазон, но и добавленную строку. Попробуем добавить еще одну, чтобы проверить работу команды. Пусть это будет марка зубной пасты под названием N по цене 51 рубль.

И видим, что сумма увеличилась на 51 (получилось 650), в то время как команда СУММ осталась прежней (554).

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

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

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

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