Користете SUM и OFFSET за да пронајдете сума за динамички опсег на податоци
Ако вашиот работен лист на Excel вклучува пресметки врз основа на менување на опсегот на ќелии, користејќи ги SUM и OFFSET функциите заедно во SUM OFFSET формула ја поедноставува задачата да се ажурираат пресметките.
Креирај динамичен опсег со функциите SUM и OFFSET
Ако користите пресметки за одреден временски период што постојано се менуваат - како што е вкупната продажба за месецот - функцијата OFFSET ви овозможува да поставите динамичен опсег кој постојано се менува, како што се додаваат податоците за продажбата на секој ден.
Сама по себе, функцијата SUM обично може да приспособи нови ќелии на податоци кои се вметнуваат во опсегот што се сумира.
Еден исклучок се јавува кога податоците се вметнуваат во ќелијата каде што моментално се наоѓа функцијата.
Во примерот на сликата што ја придружува оваа статија, новите бројки за продажба за секој ден се додаваат на дното од листата, со што се намалува вкупниот број на постојано поместување на една ќелија секој пат кога се додаваат новите податоци.
Ако функцијата SUM била користена самостојно за да ги собере податоците, би било неопходно да се измени опсегот на ќелии што се користат како аргумент на функцијата секогаш кога ќе се додадат нови податоци.
Меѓутоа, со користење на функциите SUM и OFFSET заедно, опсегот што е вкупен станува динамичен. Со други зборови, се менува за да се приспособат нови ќелии на податоци. Додавањето на нови ќелии на податоци не предизвикува проблеми бидејќи опсегот продолжува да се приспособува со додавање на секоја нова ќелија.
Синтакса и аргументи
Погледнете ја сликата што ја придружува оваа статија за да го следите заедно со ова упатство.
Во оваа формула, функцијата SUM се користи за да го опфати опсегот на подадени податоци како негов аргумент. Почетната точка за овој опсег е статична и е идентификувана како референца на ќелијата на првиот број што треба да се пресмета со формулата.
Функцијата OFFSET е вгнездена во функцијата SUM и се користи за креирање на динамичка крајна точка до опсегот на податоци што се заокружуваат со формулата. Ова се постигнува со поставување на крајната точка на опсегот на една ќелија над локацијата на формулата.
Синтаксисот на формулата:
= SUM (опсег на проектот: OFFSET (референца, редови, Cols))Почеток на опсегот - (задолжително) почетна точка за опсегот на ќелии кои ќе бидат заклучени со функцијата SUM. Во примерот слика, ова е ќелија Б2.
Референца - (задолжително) референцата на ќелијата што се користи за пресметување на крајната точка на опсегот се наоѓа на повеќе редови и колони. Во сликата за примерот, референтниот аргумент е референца на ќелијата за самата формула, бидејќи ние секогаш сакаме опсегот да стави крај на една ќелија над формулата.
Редови - (задолжително) бројот на редови над или под референтниот аргумент што се користи при пресметувањето на офсет. Оваа вредност може да биде позитивна, негативна или поставена на нула.
Ако локацијата на офсет е над аргументот Reference , оваа вредност е негативна. Ако е подолу, аргументот Rows е позитивен. Ако офсет се наоѓа во истиот ред, овој аргумент е нула. Во овој пример, offset почнува еден ред над аргументот Reference , па вредноста за овој аргумент е негативна (-1).
Cols - (задолжително) бројот на колони лево или десно од референтниот аргумент што се користи при пресметувањето на офсет. Оваа вредност може да биде позитивна, негативна или поставена на нула
Ако локацијата на офсет е лево од референтниот аргумент, оваа вредност е негативна. Ако на десната страна, аргументот Cols е позитивен. Во овој пример, податоците се вкупно во истата колона како формулата, така што вредноста за овој аргумент е нула.
Користење на SUM OFFSET формула за вкупни податоци за продажба
Овој пример користи SUM OFFSET формула за да го врати вкупниот износ за дневните бројки за продажба наведени во колона B на работниот лист.
Првично, формулата била внесена во ќелијата Б6 и ги изнесувала продажните податоци за четири дена.
Следниот чекор е да ја преместите SUM OFFSET формула по ред за да направите простор за вкупно петтата продажба.
Ова се постигнува со вметнување на нов ред 6, кој ја преместува формулата на ред 7.
Како резултат на овој потег, Excel автоматски го ажурира аргументот Reference во ќелијата B7 и ја додава ќелијата B6 до опсегот сумиран со формулата.
Внесување на SUM OFFSET формула
- Кликнете на ќелијата B6, која е местото каде што првично ќе бидат прикажани резултатите од формулата.
- Кликнете на јазичето Формула на менито на лентата .
- Изберете го Math & Trig од лентата за да ја отворите паѓачката листа на функцијата.
- Кликнете на SUM во списокот за да го прикажете дијалогот за функцијата.
- Во дијалогот, кликнете на линијата Number1 .
- Кликнете на ќелијата Б2 за да ја внесете оваа референца во дијалогот. Оваа локација е статична крајна точка за формулата;
- Во дијалогот, кликнете на линијата Number2 .
- Внесете ја следната функција OFFSET: OFFSET (B6, -1,0) за да ја формирате динамичната крајна точка за формулата.
- Кликнете на OK за да ја завршите функцијата и да го затворите дијалогот.
Вкупно 5679,15 долари се појавува во ќелијата Б7.
Кога ќе кликнете на ќелијата Б3, целосната функција = SUM (B2: OFFSET (B6, -1,0)) се појавува на лентата со формули над работниот лист.
Додавање на продажните податоци на следниот ден
За да додадете податоци за продажба следниот ден:
- Десен-клик на заглавието на редот за редот 6 за да се отвори контекстното мени.
- Во менито, кликнете на Вметни за да внесете нов ред во работниот лист.
- Како резултат на тоа, SUM OFFSET формула се движи надолу кон ќелијата B7 и редот 6 сега е празен.
- Кликнете на ќелијата А6 .
- Внесете го бројот 5 за да означите дека се внесува вкупната продажба за петтиот ден.
- Кликнете на ќелијата Б6.
- Внесете го бројот $ 1458.25 и притиснете го копчето Enter на тастатурата.
Ќелијата B7 ќе се ажурира на новиот вкупен износ од 7137,40 долари.
Кога ќе кликнете на ќелијата B7, во формуларот се појавува ажурирана формула = SUM (B2: OFFSET (B7, -1,0)) .
Забелешка : Функцијата OFFSET има два опционални аргументи: Висина и Ширина, кои беа изоставени во овој пример.
Овие аргументи може да се искористат за да се прикаже функцијата OFFSET на обликот на излез, во смисла дека е толку многу редови високи и толку многу колони ширум.
Со исклучување на овие аргументи, функцијата, по правило, ја користи висината и ширината на референтниот аргумент, што во овој пример е еден ред висок и еден колона широк.