Если ваш рабочий лист Excel включает расчеты, основанные на изменяющемся диапазоне ячеек, используйте функции СУММ и СМЕЩ вместе в формуле СУММ СМЕЩ, чтобы упростить задачу поддержания вычислений в актуальном состоянии.
Инструкции в этой статье относятся к Excel для Microsoft 365, Excel 2019, Excel 2016, Excel 2013 и Excel 2010.
Создание динамического диапазона с помощью функций SUM и OFFSET
Если вы используете расчеты за период времени, который постоянно меняется, например, для определения продаж за месяц, используйте функцию СМЕЩЕНИЕ в Excel, чтобы настроить динамический диапазон, который изменяется по мере добавления показателей продаж за каждый день.
Сама по себе функция СУММ обычно может обеспечивать вставку новых ячеек данных в суммируемый диапазон. Одно исключение возникает, когда данные вставляются в ячейку, где в данный момент находится функция.
В приведенном ниже примере новые данные о продажах за каждый день добавляются в конец списка, заставляя общую сумму постоянно сдвигаться вниз на одну ячейку каждый раз, когда добавляются новые данные.
Чтобы следовать этому руководству, откройте пустой лист Excel и введите пример данных. Ваш рабочий лист не нужно форматировать, как в примере, но обязательно вводите данные в те же ячейки.
Если для суммирования данных используется только функция СУММ, диапазон ячеек, используемых в качестве аргумента функции, необходимо будет изменять каждый раз, когда добавляются новые данные.
При совместном использовании функций SUM и OFFSET суммируемый диапазон становится динамическим и изменяется для размещения новых ячеек данных. Добавление новых ячеек данных не вызывает проблем, поскольку диапазон продолжает корректироваться по мере добавления каждой новой ячейки.
Синтаксис и аргументы
В этой формуле функция СУММ используется для суммирования диапазона данных, переданных в качестве аргумента. Начальная точка для этого диапазона является статической и определяется как ссылка на ячейку первого числа, которое должно быть суммировано по формуле.
Функция СМЕЩ вложена в функцию СУММ и создает динамическую конечную точку для диапазона данных, суммируемых по формуле. Это достигается путем установки конечной точки диапазона на одну ячейку выше местоположения формулы.
Синтаксис формулы:
=СУММ(Начало диапазона:СМЕЩЕНИЕ(Ссылка, Строки, Столбцы))
Аргументы:
- Начало диапазона: Начальная точка для диапазона ячеек, которые будут суммироваться функцией СУММ. В этом примере отправной точкой является ячейка B2.
- Reference: необходимая ссылка на ячейку, используемая для расчета конечной точки диапазона. В этом примере аргумент «Ссылка» является ссылкой на ячейку для формулы, поскольку диапазон заканчивается на одну ячейку выше формулы.
- Rows: требуется количество строк выше или ниже аргумента Reference, используемого при вычислении смещения. Это значение может быть положительным, отрицательным или равным нулю. Если местоположение смещения выше аргумента Reference, значение отрицательное. Если смещение ниже, аргумент Rows положительный. Если смещение находится в той же строке, аргумент равен нулю. В этом примере смещение начинается на одну строку выше аргумента Reference, поэтому значение аргумента отрицательное (-1).
- Cols: количество столбцов слева или справа от аргумента Reference, используемого для вычисления смещения. Это значение может быть положительным, отрицательным или равным нулю. Если местоположение смещения находится слева от аргумента Reference, это значение отрицательное. Если смещение вправо, аргумент Cols положительный. В этом примере суммируемые данные находятся в том же столбце, что и формула, поэтому значение этого аргумента равно нулю.
Используйте формулу SUM OFFSET для суммирования данных о продажах
В этом примере используется формула СУММ СМЕЩ для возврата суммы ежедневных продаж, перечисленных в столбце B рабочего листа. Изначально формула была введена в ячейку B6 и суммировала данные о продажах за четыре дня.
Следующим шагом будет перемещение формулы SUM OFFSET на одну строку вниз, чтобы освободить место для общего объема продаж за пятый день. Это достигается вставкой новой строки 6, которая перемещает формулу в строку 7.
В результате перемещения Excel автоматически обновляет аргумент Ссылка на ячейку B7 и добавляет ячейку B6 к диапазону, суммируемому по формуле.
- Выберите ячейку B6, в которой изначально будут отображаться результаты формулы.
-
Выберите вкладку Формулы на ленте.
Image -
Выберите Math & Trig.
Image -
Выбрать СУММ.
Image - В диалоговом окне Аргументы функции поместите курсор в текстовое поле Число1.
-
На рабочем листе выберите ячейку B2, чтобы ввести ссылку на эту ячейку в диалоговом окне. Это местоположение является статической конечной точкой формулы.
Image - В диалоговом окне Аргументы функции поместите курсор в текстовое поле Число2.
-
Введите СМЕЩЕНИЕ(B6, -1, 0). Эта функция OFFSET формирует динамическую конечную точку для формулы.
Image -
Выберите OK, чтобы завершить функцию и закрыть диалоговое окно. Сумма отображается в ячейке B6.
Image
Добавить данные о продажах на следующий день
Чтобы добавить данные о продажах на следующий день:
- Щелкните правой кнопкой мыши заголовок строки для строки 6.
-
Выберите Вставить, чтобы вставить новую строку в рабочий лист. Формула SUM OFFSET перемещается на одну строку вниз к ячейке B7, а строка 6 теперь пуста.
Image - Выберите ячейку A6 и введите число 5, чтобы указать, что вводится общая сумма продаж за пятый день.
-
Выберите ячейку B6, введите $1458.25, затем нажмите Enter.
Image - Ячейка B7 обновляет новую общую сумму в размере 7137,40 долларов США.
Когда вы выбираете ячейку B7, обновленная формула появляется в строке формул.
=СУММ(B2:СМЕЩЕНИЕ(B7, -1, 0))
Функция OFFSET имеет два необязательных аргумента: Height и Width, которые не использовались в этом примере. Эти аргументы сообщают функции OFFSET форму вывода с точки зрения количества строк и столбцов.
Опуская эти аргументы, функция вместо этого использует высоту и ширину аргумента Reference, которые в этом примере имеют высоту в одну строку и ширину в один столбец.
