Ако вашият работен лист в Excel включва изчисления, базирани на променящ се диапазон от клетки, използвайте функциите SUM и OFFSET заедно във формула SUM OFFSET, за да опростите задачата за поддържане на изчисленията актуални.
Инструкциите в тази статия се отнасят за Excel за Microsoft 365, Excel 2019, Excel 2016, Excel 2013 и Excel 2010.
Създаване на динамичен диапазон с функциите SUM и OFFSET
Ако използвате изчисления за период от време, който непрекъснато се променя - като например определяне на продажбите за месеца - използвайте функцията OFFSET в Excel, за да настроите динамичен диапазон, който се променя, когато се добавят данните за продажбите за всеки ден.
Само по себе си функцията SUM обикновено може да поеме вмъкването на нови клетки с данни в диапазона, който се сумира. Едно изключение възниква, когато данните се вмъкнат в клетката, където в момента се намира функцията.
В примера по-долу новите данни за продажбите за всеки ден се добавят в долната част на списъка, принуждавайки общата сума непрекъснато да се измества с една клетка надолу всеки път, когато се добавят нови данни.
За да следвате този урок, отворете празен работен лист в Excel и въведете примерните данни. Вашият работен лист не е необходимо да бъде форматиран като примера, но не забравяйте да въведете данните в същите клетки.
Ако само функцията SUM се използва за сумиране на данните, диапазонът от клетки, използвани като аргумент на функцията, ще трябва да се променя всеки път, когато се добавят нови данни.
Чрез съвместното използване на функциите SUM и OFFSET сумираният диапазон става динамичен и се променя, за да побере нови клетки с данни. Добавянето на нови клетки с данни не създава проблеми, тъй като диапазонът продължава да се коригира с добавянето на всяка нова клетка.
Синтаксис и аргументи
В тази формула функцията SUM се използва за сумиране на диапазона от данни, предоставени като аргумент. Началната точка за този диапазон е статична и се идентифицира като препратка към клетка към първото число, което трябва да бъде сумирано от формулата.
Функцията OFFSET е вложена във функцията SUM и създава динамична крайна точка за диапазона от данни, събрани от формулата. Това се постига чрез задаване на крайната точка на диапазона на една клетка над местоположението на формулата.
Синтаксисът на формулата е:
=SUM(Начало на диапазон:ОТСТЪПКА(Препратка, Редове, Колони))
Аргументите са:
- Начало на диапазона: Началната точка за диапазона от клетки, които ще бъдат сумирани от функцията SUM. В този пример началната точка е клетка B2.
- Reference: Необходимата препратка към клетка, използвана за изчисляване на крайната точка на диапазона. В примера аргументът Reference е препратката към клетката за формулата, тъй като диапазонът завършва една клетка над формулата.
- Редове: Изисква се броят на редовете над или под референтния аргумент, използван при изчисляване на отместването. Тази стойност може да бъде положителна, отрицателна или зададена на нула. Ако местоположението на отместването е над аргумента Reference, стойността е отрицателна. Ако отместването е по-долу, аргументът Редове е положителен. Ако отместването се намира в същия ред, аргументът е нула. В този пример отместването започва един ред над аргумента Reference, така че стойността на аргумента е отрицателна единица (-1).
- Cols: Броят на колоните отляво или отдясно на референтния аргумент, използван за изчисляване на отместването. Тази стойност може да бъде положителна, отрицателна или зададена на нула. Ако местоположението на отместването е отляво на аргумента Reference, тази стойност е отрицателна. Ако отместването е надясно, аргументът Cols е положителен. В този пример сумираните данни са в същата колона като формулата, така че стойността за този аргумент е нула.
Използвайте формулата SUM OFFSET за общи данни за продажби
Този пример използва формула SUM OFFSET, за да върне общата сума за дневните продажби, посочени в колона B на работния лист. Първоначално формулата беше въведена в клетка B6 и обобщи данните за продажбите за четири дни.
Следващата стъпка е да преместите формулата SUM OFFSET един ред надолу, за да направите място за общите продажби за петия ден. Това се постига чрез вмъкване на нов ред 6, който премества формулата на ред 7.
В резултат на преместването, Excel автоматично актуализира аргумента за препратка към клетка B7 и добавя клетка B6 към диапазона, сумиран от формулата.
- Изберете клетка B6, която е мястото, където резултатите от формулата първоначално ще се показват.
-
Изберете раздел Формули на лентата.
Image -
Изберете Math & Trig.
Image -
Изберете SUM.
Image - В диалоговия прозорец Аргументи на функцията поставете курсора в текстовото поле Number1.
-
В работния лист изберете клетка B2, за да въведете тази препратка към клетка в диалоговия прозорец. Това местоположение е статичната крайна точка за формулата.
Image - В диалоговия прозорец Аргументи на функцията поставете курсора в текстовото поле Number2.
-
Въведете OFFSET(B6, -1, 0). Тази функция OFFSET формира динамичната крайна точка за формулата.
Image -
Изберете OK, за да завършите функцията и да затворите диалоговия прозорец. Общата сума се показва в клетка B6.
Image
Добавяне на данни за продажбите на следващия ден
За да добавите данни за продажбите за следващия ден:
- Щракнете с десния бутон върху заглавката на ред за ред 6.
-
Изберете Insert, за да вмъкнете нов ред в работния лист. Формулата SUM OFFSET се премества един ред надолу до клетка B7 и ред 6 вече е празен.
Image - Изберете клетка A6 и въведете числото 5, за да укажете, че се въвеждат общите продажби за петия ден.йени
-
Изберете клетка B6, въведете $1458.25, след което натиснете Enter.
Image - Клетка B7 се актуализира до новата обща сума от $7137,40.
Когато изберете клетка B7, актуализираната формула се появява в лентата с формули.
=SUM(B2:OFFSET(B7, -1, 0))
Функцията OFFSET има два незадължителни аргумента: Височина и Ширина, които не са използвани в този пример. Тези аргументи казват на функцията OFFSET формата на изхода по отношение на броя на редовете и колоните.
Като пропуска тези аргументи, функцията използва вместо това височината и ширината на аргумента Reference, който в този пример е един ред висок и една колона широк.
