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