Эгер Excel иш барагыңызда уячалардын өзгөргөн диапазонуна негизделген эсептөөлөр камтылса, Эсептөөлөрдү жаңыртуу милдетин жөнөкөйлөтүү үчүн SUM жана OFFSET функцияларын биргелешип КОЛДОНУУ OFFSET формуласында колдонуңуз.
Бул макаладагы нускамалар Microsoft 365, Excel 2019, Excel 2016, Excel 2013 жана Excel 2010 үчүн Excelге колдонулат.
SUM жана OFFSET функциялары менен динамикалык диапазонду түзүңүз
Эгер сиз тынымсыз өзгөрүп турган убакыттын ичинде эсептөөлөрдү колдонсоңуз, мисалы, ай үчүн сатууну аныктоо - Excelдеги OFFSET функциясын колдонуп, ар бир күндүк сатуу көрсөткүчтөрү кошулган сайын өзгөрүп туруучу динамикалык диапазонду орнотуңуз.
Өзүнөн SUM функциясы, адатта, жаңы маалыматтардын уячаларын жыйынтыкталып жаткан диапазонго киргизүүнү камтышы мүмкүн. Функция учурда жайгашкан уячага маалыматтар киргизилгенде бир өзгөчө жагдай болот.
Төмөндөгү мисалда ар бир күн үчүн жаңы сатуу сандары тизменин ылдый жагына кошулуп, жаңы маалыматтар кошулган сайын жалпы сумма бирден уячаны ылдый жылдырууга мажбурлайт.
Бул окуу куралын аткаруу үчүн бош Excel иш барагын ачып, үлгү маалыматтарын киргизиңиз. Жумуш барагыңыздын үлгүдөгү форматталышынын кереги жок, бирок маалыматтарды ошол эле уячаларга киргизиңиз.
Дайындарды жалпылоо үчүн SUM функциясы гана колдонулса, функциянын аргументи катары колдонулган уячалардын диапазону жаңы маалыматтар кошулган сайын өзгөртүлүшү керек болот.
SUM жана OFFSET функцияларын чогуу колдонуу менен, жалпыланган диапазон динамикалуу болуп, маалыматтардын жаңы уячаларын жайгаштыруу үчүн өзгөрөт. Дайындардын жаңы уячаларын кошуу көйгөй жаратпайт, анткени ар бир жаңы уяча кошулган сайын диапазон тууралана берет.
Синтаксис жана аргументтер
Бул формулада SUM функциясы аргумент катары берилген маалыматтардын диапазонун жалпылоо үчүн колдонулат. Бул диапазондун башталгыч чекити статикалык жана формула менен жыйынтыкталуучу биринчи санга уяча шилтемеси катары аныкталат.
OFFSET функциясы SUM функциясынын ичине уя салынган жана формула боюнча жалпыланган берилиштер диапазонуна динамикалык акыркы чекит түзөт. Бул диапазонун акыркы чекитин формула жайгашкан жерден бир уячага коюу аркылуу ишке ашат.
Формуланын синтаксиси:
=SUM(Башталышы диапазон:OFFSET(Шилтеме, саптар, колонкалар))
Аргументтер:
- Range Start: SUM функциясы менен жыйынтыктала турган уячалардын диапазону үчүн баштапкы чекит. Бул мисалда, баштапкы чекит B2 уячасы.
- Шилтеме: Диапазондун акыркы чекитин эсептөө үчүн колдонулган клетка шилтемеси. Мисалда Шилтеме аргументи формуланын уяча шилтемеси болуп саналат, анткени диапазон формуладан бир уячага жогору бүтөт.
- Сатарлар: Оффсетти эсептөөдө колдонулган Шилтеме аргументинин үстүндө же астындагы саптардын саны талап кылынат. Бул маани оң, терс же нөлгө коюлушу мүмкүн. Эгерде офсеттин орду Шилтеме аргументинин үстүндө болсо, маани терс болот. Эгерде офсет ылдыйда болсо, Rows аргументи оң болот. Эгерде офсет бир катарда жайгашкан болсо, аргумент нөлгө барабар. Бул мисалда жылышуу Шилтеме аргументинен бир сап жогору башталат, андыктан аргументтин мааниси терс бир (-1).
- Cols: Салыштырууларды эсептөө үчүн колдонулган Шилтеме аргументинин сол же оң жагындагы тилкелердин саны. Бул маани оң, терс же нөлгө коюлушу мүмкүн. Эгерде офсеттин орду Шилтеме аргументинин сол жагында болсо, бул маани терс болот. Эгерде офсет оңго болсо, Колс аргументи оң болот. Бул мисалда, жалпыланган маалымат формула менен бир тилкеде, андыктан бул аргументтин мааниси нөлгө барабар.
Сатуулардын жалпы маалыматы үчүн СУМ OFSET формуласын колдонуңуз
Бул мисалда иш барагынын В тилкесинде саналган күнүмдүк сатуу көрсөткүчтөрүнүн жалпы суммасын кайтаруу үчүн СУМДУН ОРУНДАУ формуласы колдонулат. Башында, формула B6 уячасына киргизилип, төрт күндүк сатуу маалыматтарын бириктирди.
Кийинки кадам – бешинчи күндөгү жалпы сатууга орун бошотуш үчүн, СУМДУН ОФСЕТТҮҮ формуласын бир катар ылдый жылдыруу. Бул формуланы 7-сапка жылдыруучу жаңы 6-сапты киргизүү менен аткарылат.
Көчүрүүнүн натыйжасында Excel Шилтеме аргументин B7 уячасына автоматтык түрдө жаңыртат жана B6 уячасын формула менен жыйынтыкталган диапазонго кошот.
- B6 уячасын тандаңыз, бул формуланын натыйжалары алгач көрсөтүлө турган жер.
-
Тасмадан Формулалар өтмөгүн тандаңыз.
Image -
Тандоо Математика жана триг.
Image -
SUM тандаңыз.
Image - Функциянын аргументтери диалог кутусунда курсорду 1-сан текст кутучасына коюңуз.
-
Жумуш барагында бул уяча шилтемесин диалог кутусуна киргизүү үчүн B2 уячасын тандаңыз. Бул жер формуланын статикалык акыркы чекити.
Image - Функциянын аргументтери диалог кутусунда курсорду 2-сан текст кутучасына коюңуз.
-
OFSET(B6, -1, 0) киргизиңиз. Бул OFFSET функциясы формула үчүн динамикалык акыркы чекитти түзөт.
Image -
Функцияны аяктоо жана диалог кутусун жабуу үчүн OK тандаңыз. Жалпы сумма B6 уячасында көрүнөт.
Image
Кийинки күндүн сатуу маалыматтарын кошуу
Кийинки күндүн сатуу дайындарын кошуу үчүн:
- 6-саптын саптын башын оң баскыч менен чыкылдатыңыз.
-
Жумуш барагына жаңы сап киргизүү үчүн Insert тандаңыз. SUM OFFSET формуласы B7 уячасына бир сап ылдый жылып, 6-сап эми бош.
Image - A6 уячасын тандап, бешинчи күндөгү сатуунун жалпы суммасы киргизилип жатканын көрсөтүү үчүн 5 санын киргизиңиз.
-
B6 уячасын тандап, $1458.25 киргизип, андан соң Enter баскычын басыңыз.
Image - Cell B7 жаңыртылды $7137,40.
B7 уячасын тандаганыңызда, жаңыланган формула формула тилкесинде пайда болот.
=SUM(B2:OFSET(B7, -1, 0))
OFFSET функциясынын эки кошумча аргументи бар: Бийиктик жана Туурасы, бул мисалда колдонулган эмес. Бул аргументтер OFFSET функциясына саптардын жана мамычалардын саны боюнча чыгаруунун формасын айтып берет.
Бул аргументтерди өткөрүп жиберүү менен функция анын ордуна Шилтеме аргументинин бийиктигин жана туурасын колдонот, бул мисалда бул аргумент бир катар бийик жана бир мамыча туурасы болуп саналат.
