Excel SUM и OFFSET Формула

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

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

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

Сам по себе си, функцията 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. В диалоговия прозорец кликнете върхуНомер 1линия.
6. Кликнете върху клетката B2 за да въведете тази клетка в диалоговия прозорец. Това местоположение е статичната крайна точка за формулата.
7. В диалоговия прозорец кликнете върхуnUMBER2линия.
8. Въведете следната функция OFFSET: OFFSET (В6, -1,0) за да се формира динамичната крайна точка за формулата.
9. Кликнете Добре за да завършите функцията и да затворите диалоговия прозорец.

Общото количество се показва в клетка B7.

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

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

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

Клетка B7 актуализира новата обща сума от$7137.40.

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

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

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

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