Екцел СУМ и ОФФСЕТ Формула

Ако ваш Екцел радни лист укључује прорачуне базиране на промјенљивом опсегу ћелија, кориштењем функција СУМ и ОФФСЕТ заједно у СУМ ОФФСЕТ формулацији поједностављује задатак одржавања ажурирања калкулација.

Креирајте динамички опсег помоћу функција СУМ и ОФФСЕТ

Ако користите прорачуне за временски период који се непрекидно мијења - као што је укупна продаја за мјесец - функција ОФФСЕТ у Екцел-у вам омогућава да подесите динамички опсег који се мијења пошто се додају бројке продаје сваког дана.

Сама по себи, функција СУМ може уобичајено да прими уношење нових ћелија података у опсег који се сумира.

Један изузетак се дешава када се подаци убацују у ћелију у којој се тренутно налази функција.

На горњој слици слике, нове бројке за продају за сваки дан се додаје на дну листе, чиме се тотално непрекидно помера по једну ћелију сваки пут када се додају нови подаци.

Ако је СУМ функција коришћена самостално за комплетирање података, било би неопходно модификовати опсег ћелија који се користе као аргумент функције сваки пут када су додати нови подаци.

Међутим, користећи СУМ и ОФФСЕТ функције, опсег који је тоталиран постаје динамичан. Другим речима, она се мења како би применила нове ћелије података. Додавање нових ћелија података не узрокује проблеме јер опсег наставља да се прилагођава пошто се додаје нова ћелија.

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

Погледајте горњу слику како бисте пратили овај водич.

У овој формули, функција СУМ се користи за укупан распон података који се добијају као његов аргумент. Стартна тачка за овај опсег је статична и идентификована је као референца ћелије на први број који ће се извршити формулом.

Функција ОФФСЕТ је угнежена унутар функције СУМ и користи се за креирање динамичке крајње тачке до опсега података који је тотализиран формулом. Ово се постиже постављањем крајње тачке опсега на једну ћелију изнад локације формуле.

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

= СУМ (Ранге Старт: ОФФСЕТ (Референца, редови, цолови))

• Старт Старт: Потребна је полазна тачка за опсег ћелија које ће се извршити помоћу функције СУМ. На слици слике, ово је ћелија Б2.
• Референца: Захтевана референца ћелије која се користи за израчунавање крајње тачке опсега налази се низ редова и колона. На слици слике, референтни аргумент је референце ћелија за саму формулу, јер увек желите да опсег заврши једну ћелију изнад формуле.
• Ровс: Потребан је број редова изнад или испод Референтног аргумента који се користи за израчунавање офсета. Ова вредност може бити позитивна, негативна или подешена на нулу. Ако је положај оффсет-а изнад Референца аргумент, вредност је негативна. Ако је испод, аргумент Ровс је позитиван. Ако се оффсет налази у истом реду, аргумент је нула. У овом примеру оффсет почиње један ред изнад референтног аргумента, тако да је вриједност за овај аргумент негативна (-1).
• Цолс: Потребан је број колона лево или десно од референтног аргумента који се користи за израчунавање офсета. Ова вредност може бити позитивна, негативна или подешена на нулу. Ако је положај оффсет-а лево од референтног аргумента, ова вредност је негативна. Ако је десно, аргумент Цолса је позитиван. У овом примјеру, подаци који се тоталирају налазе се у истој колони као и формула, тако да је вриједност за овај аргумент нула.

Коришћењем СУМ ОФФСЕТ Формуле на укупне продајне податке

Овај примјер користи СУМ ОФФСЕТ формулу за враћање укупног броја дневних продаја наведених у колони Б радног листа.

На почетку, формула је унета у ћелију Б6 и износила је податке о продаји четири дана.

Следећи корак је да преместите СУМ ОФФСЕТ формулу низом да бисте направили простор за продају петог дана. Ово се постиже уношењем новог реда 6, који помера формулу на ред 7.

Као резултат потеза, Екцел аутоматски ажурира референтни аргумент на ћелијуБ7 и додаје ћелијуБ6 до опсега сакупљене формулом.

Уношење СУМ ОФФСЕТ Формуле

1. Кликните на ћелију Б6, на којој ће се на почетку приказати резултати формуле.
2. Кликните наФормуле таб на менију траке.
3. ИзаберитеМатх & Тригиз траке отворити функцију падајуће листе.
4. Кликните наСУМ на листи да прикажете дијалошки оквир функције.
5. У дијалогу кликните наБрој 1лине.
6. Кликните на ћелију Б2 да унесете референцу ћелије у дијалог бок. Ова локација је статична крајња тачка за формулу.
7. У дијалогу кликните наБрој 2лине.
8. Унесите следећу ОФФСЕТ функцију: ОФФСЕТ (Б6, -1,0) да формира динамичку крајњу тачку за формулу.
9. Кликните У реду да довршите функцију и затворите дијалошки оквир.

Укупно се појављује у ћелији Б7.

Додавање података о следећем дану продаје

Да бисте додали податке о продаји следећег дана:

1. Десним тастером миша кликните на заглавље редова за ред 6 да бисте отворили контекстни мени.
2. У менију кликните наУбаци да убаците нови ред у радни лист.
3. Као резултат, формула СУМ ОФФСЕТ се помера на ћелију Б7, а ред 6 је сада празан.
4. Кликните на ћелију А6.
5. Унесите број5 како би се указало на то да се укупан промет петог дана уноси.
6. Кликните на ћелију Б6.
7. Упишите број$1458.25 и притиснитеЕнтер на тастатури.

Ћелија Б7 ажурира нови скуп$7137.40.

Када кликнете на ћелију Б7, ажурирана формула= СУМ (Б2: ОФФСЕТ (Б7, -1,0)) се појављује у траци формуле.

Функција ОФФСЕТ има два опциона аргумента: висина и ширина , који нису коришћени у овом примеру.

Ови аргументи се могу користити да се функцији ОФФСЕТ-у прикаже облик излаза у смислу толико редова високих и толико много колона.

Изостављањем ових аргумената, функција подразумевано користи висину и ширину референтног аргумента, што је у овом примеру један ред и један широк колона.