Формула Excel SUM и OFFSET

Если рабочий лист Excel включает вычисления, основанные на изменении диапазона ячеек, использование функций SUM и OFFSET вместе в формуле SUM OFFSET упрощает задачу поддержания актуальности вычислений.

Создание динамического диапазона с помощью функций SUM и OFFSET

Если вы используете вычисления в течение периода времени, который постоянно изменяется - например, общий объем продаж за месяц - функция OFFSET в Excel позволяет вам настроить динамический диапазон, который изменяется по мере добавления показателей продаж за каждый день.

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

Единственное исключение возникает, когда данные вставляются в ячейку, где в данный момент находится функция.

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

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

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

Синтаксис и аргументы

См. Изображение выше, чтобы следовать этому руководству.

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

Функция OFFSET встроена в функцию SUM и используется для создания динамической конечной точки в диапазоне данных, полученных по формуле. Это достигается путем установки конечной точки диапазона в одну ячейку выше местоположения формулы.

Синтаксис формулы:

= SUM (Range Start: OFFSET (Reference, Rows, Cols))

• Начало диапазона: Начальная точка для диапазона ячеек, которые будут суммироваться функцией SUM, требуется. В образце примера это ячейка B2.
• Ссылка: Требуемая ссылка на ячейку, используемая для вычисления конечной точки диапазона, расположена во многих рядах и столбцах. В образце примера аргумент Reference является ссылкой ячейки для самой формулы, потому что вы всегда хотите, чтобы диапазон заканчивал одну ячейку над формулой.
• Ряды: Требуется количество строк выше или ниже аргумента Reference, используемого при вычислении смещения. Это значение может быть положительным, отрицательным или равным нулю. Если местоположение смещения находится выше Ссылка аргумент, значение отрицательное. Если он ниже, аргумент Rows положителен. Если смещение расположено в той же строке, аргумент равен нулю. В этом примере смещение начинается с одной строки над аргументом Reference, поэтому значение для этого аргумента отрицательное (-1).
• Cols: Требуется количество столбцов влево или вправо от аргумента Reference, используемого при вычислении смещения. Это значение может быть положительным, отрицательным или равным нулю. Если местоположение смещения находится слева от аргумента Reference, это значение отрицательное. Если справа аргумент Cols положителен. В этом примере суммируемые данные находятся в том же столбце, что и формула, поэтому значение для этого аргумента равно нулю.

Использование формулы SUM OFFSET для итоговых данных продаж

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

Первоначально формула вводилась в ячейку B6 и составляла данные о продажах в течение четырех дней.

Следующий шаг - переместить формулу SUM OFFSET в ряд, чтобы освободить место для продаж на пятый день. Это достигается добавлением новой строки 6, которая перемещает формулу до строки 7.

В результате перемещения Excel автоматически обновляет аргумент Reference в ячейкеB7 и добавляет ячейкуB6 к диапазону, суммированному по формуле.

Ввод формулы SUM OFFSET

1. Нажмите на ячейку B6, где будет отображаться результат формулы.
2. Нажми наФормулы вкладку меню ленты.
3. выберитеMath & Trigот ленты, чтобы открыть раскрывающийся список функций.
4. Нажмите наSUM в списке, чтобы открыть диалоговое окно функции.
5. В диалоговом окне нажмитеНомер 1линия.
6. Нажмите на ячейку Би 2 для ввода этой ссылки ячейки в диалоговое окно. Это местоположение является статической конечной точкой для формулы.
7. В диалоговом окне нажмитеНомер 2линия.
8. Введите следующую функцию OFFSET: OFFSET (В6, -1,0) для формирования динамической конечной точки для формулы.
9. Нажмите Хорошо для завершения функции и закрытия диалогового окна.

Общее количество появляется в ячейке B7.

Добавление данных о продажах на следующий день

Чтобы добавить данные о продажах на следующий день:

1. Щелкните правой кнопкой мыши заголовок строки для строки 6, чтобы открыть контекстное меню.
2. В меню нажмитеВставить для вставки новой строки в рабочий лист.
3. В результате формула SUM OFFSET переходит к ячейке B7, а строка 6 теперь пуста.
4. Нажмите на ячейку A6.
5. Введите номер5 чтобы указать, что вводится сумма продаж на пятый день.
6. Нажмите на ячейку B6.
7. Введите число$1458.25 и нажмитеВойти на клавиатуре.

Cell B7 обновляется до нового$7137.40.

Когда вы нажимаете на ячейку B7, обновленная формула= СУММ (В2: OFFSET (В7, -1,0)) появляется в строке формул.

Функция OFFSET имеет два необязательных аргумента: высота и ширина , которые в этом примере не использовались.

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

Отпуская эти аргументы, функция по умолчанию использует высоту и ширину аргумента Reference вместо этого, который в этом примере представляет собой одну строку с высокой и одной шириной столбца.