Егер Excel жұмыс парағында ұяшықтардың өзгеретін диапазонына негізделген есептеулер болса, есептеулерді жаңартып отыру тапсырмасын жеңілдету үшін ҚОСЫНАДЫ және ОФСЕТТІК функцияларын бірге СУМЫНА ОФСЕТІ формуласында пайдаланыңыз.
Осы мақаладағы нұсқаулар 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(Анықтама, жолдар, бағалар))
Аргументтер:
- Ауқым басы: SUM функциясы арқылы қорытындыланатын ұяшықтар ауқымының бастапқы нүктесі. Бұл мысалда бастапқы нүкте B2 ұяшығы болып табылады.
- Анықтама: ауқымның соңғы нүктесін есептеу үшін пайдаланылатын қажетті ұяшық сілтемесі. Мысалдағы Анықтама аргументі формулаға арналған ұяшық сілтемесі болып табылады, себебі ауқым формуладан бір ұяшықта аяқталады.
- Rows: ығысуды есептеу үшін пайдаланылатын Анықтама аргументінің үстінде немесе астындағы жолдар саны қажет. Бұл мән оң, теріс немесе нөлге тең болуы мүмкін. Егер ығысу орны Анықтама аргументінен жоғары болса, мән теріс болады. Егер ығысу төменде болса, Жолдар аргументі оң болады. Егер ығысу бір жолда орналасса, аргумент нөлге тең болады. Бұл мысалда ығысу Анықтама аргументінен бір жолдан басталады, сондықтан аргументтің мәні теріс бір (-1) болады.
- Cols: ығысуды есептеу үшін пайдаланылатын Анықтама аргументінің сол немесе оң жағындағы бағандар саны. Бұл мән оң, теріс немесе нөлге тең болуы мүмкін. Егер ығысу орны Анықтама аргументінің сол жағында болса, бұл мән теріс болады. Егер ығысу оңға қарай болса, Cols аргументі оң болады. Бұл мысалда қорытындыланатын деректер формуламен бір бағанда, сондықтан бұл аргументтің мәні нөлге тең.
Жалпы сатылым деректері үшін ҚОСЫНДЫ ОФСЕТ формуласын пайдаланыңыз
Бұл мысалда жұмыс парағының B бағанында тізілген күнделікті сату сандары үшін жиынтықты қайтару үшін ҚОРЫНДЫ ОФСЕТТІК формуласы пайдаланылады. Бастапқыда формула 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 - B7 ұяшығы жаңа жалпы сомасы $7137,40 жаңартылады.
B7 ұяшығын таңдаған кезде жаңартылған формула формула жолында пайда болады.
=SUM(B2:OFSET(B7, -1, 0))
OFFSET функциясының екі қосымша аргументі бар: Height and Width, бұл мысалда пайдаланылмаған. Бұл аргументтер OFFSET функциясына жолдар мен бағандар саны бойынша шығыс пішінін айтады.
Бұл аргументтерді алып тастау арқылы функция орнына Анықтама аргументінің биіктігі мен енін пайдаланады, бұл мысалда бір жолдың биіктігі мен бір бағанының ені.
