Користите СУМ и ОФФСЕТ да бисте пронашли укупне вредности за динамичке опсеге података
Ако ваш Екцел радни лист укључује прорачуне базиране на промјенљивом опсегу ћелија, кориштењем функција СУМ и ОФФСЕТ заједно у СУМ ОФФСЕТ формулацији поједностављује задатак одржавања ажурирања калкулација.
Креирајте динамички опсег помоћу функција СУМ и ОФФСЕТ
Ако користите прорачуне за временски период који се непрестано мења - као што је укупна продаја за месец - функција ОФФСЕТ вам омогућава да подесите динамички опсег који се мења, пошто се додају бројке за продају сваког дана.
Сама по себи, функција СУМ може обично да прими нове ћелије података који се уносе у опсег који се сумира.
Један изузетак се дешава када се подаци убацују у ћелију у којој се тренутно налази функција.
У примеру слике која прати овај чланак, нове бројке за продају за сваки дан се додаје на дну листе, што доводи до тога да се укупан број непрекидно помера по једну ћелију сваки пут када се додају нови подаци.
Ако је СУМ функција коришћена самостално за комплетирање података, било би неопходно модификовати опсег ћелија који се користе као аргумент функције сваки пут када су додати нови подаци.
Међутим, користећи СУМ и ОФФСЕТ функције, опсег који је тоталиран постаје динамичан. Другим речима, она се мења како би применила нове ћелије података. Додавање нових ћелија података не узрокује проблеме јер опсег наставља да се прилагођава пошто се додаје нова ћелија.
Синтакса и аргумената
Погледајте слику која прати овај чланак да бисте пратили овај водич.
У овој формули, функција СУМ се користи за укупан распон података који се добијају као његов аргумент. Стартна тачка за овај опсег је статична и идентификована је као референца ћелије на први број који ће се извршити формулом.
Функција ОФФСЕТ је угнежена унутар функције СУМ и користи се за креирање динамичке крајње тачке до опсега података који је тотализиран формулом. Ово се постиже постављањем крајње тачке опсега на једну ћелију изнад локације формуле.
Синтакса формула:
= СУМ (Ранге Старт: ОФФСЕТ (Референца, редови, цолови))Опсег Почетак - (потребно) полазна тачка за опсег ћелија које ће се извршити помоћу функције СУМ. На слици слике, ово је ћелија Б2.
Референца - (обавезно) референца ћелије која се користи за израчунавање крајње тачке опсега налази се на више редова и колона. На слици слике, референтни аргумент је референца ћелија за саму формулу, јер ми увек желимо да опсег заврши једну ћелију изнад формуле.
Редови - (потребан) број редова изнад или испод Референтног аргумента који се користи за израчунавање офсета. Ова вредност може бити позитивна, негативна или подешена на нулу.
Ако је локација офсета изнад референтног аргумента, ова вредност је негативна. Ако је испод, аргумент Ровс је позитиван. Ако се оффсет налази у истом реду, овај аргумент је нула. У овом примеру оффсет почиње један ред изнад референтног аргумента, тако да је вриједност за овај аргумент негативна (-1).
Цолс - (потребан) број колона са леве или десне стране референтног аргумента који се користи за израчунавање офсета. Ова вредност може бити позитивна, негативна или подешена на нулу
Ако је положај оффсет-а лево од референтног аргумента, ова вредност је негативна. Ако је десно, аргумент Цолса је позитиван. У овом примјеру, подаци који се тоталирају налазе се у истој колони као формула, тако да је вриједност за овај аргумент нула.
Коришћењем СУМ ОФФСЕТ Формуле на укупне продајне податке
Овај примјер користи СУМ ОФФСЕТ формулу за враћање укупног броја дневних продаја наведених у колони Б радног листа.
На почетку, формула је унета у ћелију Б6 и износила је податке о продаји четири дана.
Следећи корак је да преместите СУМ ОФФСЕТ формулу низом да бисте направили простор за продају петог дана.
Ово се постиже уношењем новог реда 6, који помера формулу на ред 7.
Као резултат тог потеза, Екцел аутоматски ажурира референтни аргумент на ћелију Б7 и додаје ћелију Б6 у опсег који се своди са формулом.
Уношење СУМ ОФФСЕТ Формуле
- Кликните на ћелију Б6, на којој ће се на почетку приказати резултати формуле.
- Кликните на картицу Формуле у менију траке .
- Изаберите Матх & Триг са траке да бисте отворили функцију падајуће листе.
- Кликните на СУМ на листи да бисте приказали дијалошки оквир функције.
- У дијалогу кликните на линију Нумбер1 .
- Кликните на ћелију Б2 да бисте унели ову референцу ћелије у дијалошки оквир. Ова локација је статичка крајња тачка за формулу;
- У дијалогу кликните на линију Нумбер2 .
- Унесите следећу ОФФСЕТ функцију: ОФФСЕТ (Б6, -1,0) да бисте формирали динамичку крајњу тачку за формулу.
- Кликните ОК да бисте довршили функцију и затворили дијалошки оквир.
Укупно 5679,15 долара појављује се у ћелији Б7.
Када кликнете на ћелију Б3, комплетна функција = СУМ (Б2: ОФФСЕТ (Б6, -1,0)) се појављује у траци формуле изнад радног листа.
Додавање података о следећем дану продаје
Да бисте додали податке о продаји следећег дана:
- Десним тастером миша кликните на заглавље редова за ред 6 да бисте отворили контекстни мени.
- У менију кликните на ставку Уметни да бисте уметнули нови ред у радни лист.
- Као резултат, формула СУМ ОФФСЕТ се помера на ћелију Б7, а ред 6 је сада празан.
- Кликните на ћелију А6 .
- Унесите број 5 да бисте назначили да се уноси укупан промет петог дана.
- Кликните на ћелију Б6.
- Унесите број $ 1458.25 и притисните тастер Ентер на тастатури.
Ћелија Б7 ажурира нови износ од 7137,40 долара.
Када кликнете на ћелију Б7, ажурирана формула = СУМ (Б2: ОФФСЕТ (Б7, -1,0)) се појављује у траци формуле.
Напомена : ОФФСЕТ функција има два опциона аргумента: висина и ширина, који су изостављени у овом примеру.
Ови аргументи се могу користити да се функцији ОФФСЕТ-у прикаже облик излаза у смислу да је толико редова висок и толико колона.
Изостављањем ових аргумената, функција подразумевано користи висину и ширину референтног аргумента, што је у овом примеру један ред и један широк колона.