Excel SUM и OFFSET Формула

Използвайте SUM и OFFSET, за да намерите суми за динамични диапазони данни

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

Създайте динамичен диапазон с функциите SUM и OFFSET

© Тед Френски

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

Сам по себе си, функцията SUM обикновено може да приема нови клетки от данни, които се вмъкват в обхвата, който се сумира.

Едно изключение се получава, когато данните се вмъкват в клетката, в която се намира функцията.

В примерното изображение, придружаващо тази статия, новите данни за продажбите за всеки ден се добавят в долната част на списъка, което принуждава общия брой да се сменя непрекъснато една клетка всеки път, когато се добавят новите данни.

Ако функцията SUM беше използвана сама по себе си за обобщаване на данните, би било необходимо да се променя обхватът на клетките, използвани като аргумент на функцията всеки път, когато се добавят нови данни.

Чрез използването на функциите SUM и OFFSET заедно, обаче, обхватът, който е завършен, става динамичен. С други думи, тя се променя, за да се приспособят новите клетки от данни. Добавянето на нови клетки от данни не създава проблеми, тъй като диапазонът продължава да се коригира с добавянето на всяка нова клетка.

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

Обърнете се към изображението, придружаващо тази статия, която да следвате заедно с този урок.

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

Функцията OFFSET е вградена във функцията SUM и се използва за създаване на динамична крайна точка в обхвата на данните, възстановени от формулата. Това се постига чрез задаване на крайната точка на обхвата на една клетка над местоположението на формулата.

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

= SUM (Обхват на старта: OFFSET (Референция, Редове, Колони))

Старт на обхвата - (задължително) началната точка за диапазона от клетки, които ще бъдат запълнени от функцията SUM. В примерното изображение това е клетка В2.

Референция - (задължително) референтната клетка, използвана за изчисляване на крайната точка на диапазона, разположена на много редове и колони. В примерното изображение референтният аргумент е референтната клетка за самата формула, тъй като винаги искаме диапазонът да завърши една клетка над формулата.

Редове - (задължително) броя редове над или под референтния аргумент, използван при изчисляването на отместването. Тази стойност може да бъде положителна, отрицателна или да е нула.

Ако местоположението на отместването е над аргумента за справка , тази стойност е отрицателна. Ако е по-долу, аргументът за реда е положителен. Ако отместването се намира в същия ред, този аргумент е нулев. В този пример отместването започва един ред над референтния аргумент, така че стойността на този аргумент е отрицателна (-1).

Колони - (задължително) броя на колоните отляво или отдясно на референтния аргумент, използван при изчисляването на отместването. Тази стойност може да бъде положителна, отрицателна или да е нула

Ако местоположението на отместването е вляво от референтния аргумент, тази стойност е отрицателна. Ако надясно, аргументът на Cols е положителен. В този пример данните, които се попълват, са в същата колона като формулата, така че стойността за този аргумент е нула.

Използване на формулата SUM OFFSET за общите продажби

Този пример използва формула SUM OFFSET за връщане на сумата за дневните данни за продажбите, посочени в колона Б на работния лист.

Първоначално формулата бе въведена в клетка B6 и данните за продажбите се натрупаха в продължение на четири дни.

Следващата стъпка е да преместите формулата SUM OFFSET надолу по ред, за да направите място за продажбите за петия ден.

Това се постига чрез вмъкване на нов ред 6, който премества формулата до ред 7.

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

Въвеждане на формула SUM OFFSET

  1. Кликнете върху клетката B6, която е мястото, където първоначално ще се показват резултатите от формулата.
  2. Кликнете върху раздела Формули в лентата с менюта.
  3. Изберете " Math & Trig" от панделката, за да отворите падащия списък на функциите.
  4. Кликнете върху SUM в списъка, за да изведете диалоговия прозорец на функцията.
  5. В диалоговия прозорец кликнете върху линията Number1 .
  6. Кликнете върху клетка В2, за да въведете тази клетка в диалоговия прозорец. Това местоположение е статичната крайна точка за формулата;
  7. В диалоговия прозорец кликнете върху реда Number2 .
  8. Въведете следната функция OFFSET: OFFSET (B6, -1,0), за да създадете динамичната крайна точка за формулата.
  9. Кликнете върху OK, за да завършите функцията и да затворите диалоговия прозорец.

Общо $ 5679.15 се появява в клетка B7.

Когато кликнете върху клетка B3, пълната функция = SUM (B2: OFFSET (B6, -1,0)) се появява в лентата за формули над работния лист.

Добавяне на данните за продажбите на следващия ден

За да добавите данни за продажбите за следващия ден:

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

Cell B7 актуализира новата сума от 7137.40 долара.

Когато кликнете върху клетка B7, обновената формула = SUM (B2: OFFSET (B7, -1,0)) се появява в лентата за формули.

Забележка : Функцията OFFSET има два допълнителни аргумента: Height and Width, които са били пропуснати в този пример.

Тези аргументи могат да се използват, за да се каже на функцията OFFSET формата на изхода, тъй като то е толкова много редови и толкова много колони.

Чрез изпускането на тези аргументи функцията по подразбиране използва височината и ширината на аргумента за справка, което в този пример е един ред висок и една колона широка.