Деректердің динамикалық диапазондары үшін қорытындыларды табу үшін SUM және OFFSET пайдаланыңыз
Егер сіздің Excel жұмыс парағында ұяшықтардың өзгеретін ауқымына негізделген есептеулер болса, SUM OFFSET формуласында SUM және OFFSET функцияларын бірге қолданып, есептерді үнемі сақтау міндетін жеңілдетеді.
SUM және OFFSET функциялары бар динамикалық ауқымды жасаңыз
Егер сіз үнемі өзгеріп отыратын есептеулерді пайдалансаңыз - мысалы, айдағы сату жиынтығы - OFFSET функциясы әр күннің сату көрсеткіштері қосылған сайын өзгеретін динамикалық ауқымды орнатуға мүмкіндік береді.
Өз кезегінде, SUM функциясы әдетте жиналған ауқымға енгізілетін деректердің жаңа ұяларын орналастыра алады.
Бір деректерді енгізу функциясы қазір орналасқан жерде орналасқан ұяшыққа енгізілген кезде орын алады.
Осы мақаланы сүйемелдейтін мысалдағы суретте, әрбір күн үшін жаңа сату көрсеткіштері тізімнің төменгі жағына қосылады, бұл жаңа деректер қосылған сайын тұтас бір ұяшықты үнемі жылжытады.
Егер SUM функциясы деректерді толығымен пайдалану үшін пайдаланылса, жаңа деректер қосылған сайын функцияның аргументі ретінде пайдаланылатын ұяшықтар ауқымын өзгерту қажет болады.
SUM және OFFSET функцияларын бірге пайдалану арқылы, жиынтық ауқымы динамикалық болады. Басқаша айтқанда, деректердің жаңа ұяларын орналастыру үшін өзгереді. Деректердің жаңа ұяшықтарын қосу мәселені тудырмайды, себебі әр жаңа ұяшық қосылып, ауқым реттеледі.
Синтаксис және дәлелдер
Осы мақаланы сүйемелдеуге арналған суретті осы оқу құралымен бірге қарау үшін қараңыз.
Бұл формулада SUM функциясы дәлел ретінде берілген деректер ауқымын толықтыру үшін пайдаланылады. Бұл диапазонның бастау нүктесі статикалық болып табылады және формула бойынша көрсетілетін бірінші нөмірге ұяшық сілтемесі ретінде анықталады.
OFFSET функциясы SUM функциясының ішіне салынған және формула бойынша деректердің ауқымына динамикалық соңғы нүктені жасау үшін пайдаланылады. Бұл ауқымның соңғы нүктесін формуланың орнынан жоғары тұрған бір ұяшыққа орнату арқылы орындалады.
Формула синтаксисі :
= SUM (Range Start: OFFSET (Анықтама, Жолдар, Cols))Range Start - SUM функциясымен қамтылатын ұяшықтар ауқымына арналған бастапқы нүкте (қажет). Мысал суретте бұл B2 ұяшығы.
Reference - ауқымның соңғы нүктесін есептеу үшін пайдаланылатын ұяшық сілтемесі көптеген жолдар мен бағандарды орналастырады. Мысал суретте Сілтеме аргументі формуланың өзіндік ұяшық сілтемесі болып табылады, өйткені біз әрқашан ауқымды формуланың үстіндегі бір ұяшықты аяқтауды қалаймыз.
Жолдар - ( цифрландыруды есептеу кезінде пайдаланылатын Сілтеме дәлелінің жоғары немесе төменгі жолдар саны). Бұл мән оң, теріс болуы немесе нөлге тең болуы мүмкін.
Егер офсет орналасқан жері Reference дәлелінен жоғары болса, бұл мән теріс. Егер бұл төмен болса, жолдар аргументі оң болады. Егер офсет бірдей жолда болса, бұл аргумент нөлге тең. Бұл мысалда, офсет Сілтеме аргументінің үстінен бір қатардан басталады, сондықтан осы аргумент мәні теріс (-1).
Cols - (қажет) бағаны есептеу кезінде пайдаланылатын Сілтеме аргументінің сол немесе оң жақ бағандарының саны. Бұл мән оң, теріс болуы немесе нөлге тең болуы мүмкін
Егер офсет орналасқан жері Сілтеме аргументінің сол жағында болса, бұл мән теріс. Егер оң жақта Cols дәлелі оң болса. Бұл мысалда деректер жинақталған формула сияқты бір бағанда, сондықтан дәлелдің мәні нөлге тең.
SUM OFFSET формуласын жалпы сату деректеріне пайдалану
Бұл мысал жұмыс парағының B бағанында көрсетілген күнделікті сату көрсеткіштерінің жиынтық мәнін қайтару үшін SUM OFFSET формуласын қолданады.
Бастапқыда формула B6 ұяшығына енгізілді және төрт күндік сатылым деректерін құрады.
Келесі қадам - SUM OFFSET формуласын бесінші күннің сатылымы үшін бөлме жасау үшін қатарға жылжыту.
Бұл формуланы 7-жолға дейін жылжытатын жаңа 6- жолды енгізу арқылы жүзеге асады.
Жылжудың нәтижесінде Excel бағдарламасы Сілтеме аргументін B7 ұяшығына автоматты түрде жаңартады және формула бойынша жинақталған ауқымға B6 ұяшығын қосады.
SUM OFFSET формуласын енгізу
- Формуланың нәтижелері бастапқыда көрсетілетін орын болып табылатын B6 ұяшығын басыңыз.
- Таспа мәзірінің Формулалар қойындысын нұқыңыз.
- Функция ашылмалы тізімін ашу үшін таспадан Math & Trig таңдаңыз.
- Функцияның тілқатысу терезесін жетілдіру үшін тізімдегі SUM түймесін басыңыз.
- Диалогтық терезеде Number1 жолын басыңыз.
- Осы ұяшық сілтемесін тілқатысу терезесіне енгізу үшін B2 ұяшығын басыңыз. Бұл орын формуланың статикалық соңғы нүктесі болып табылады;
- Тілқатысу терезесінде Number2 жолын басыңыз.
- OFFSET функциясын енгізіңіз: OFFSET (B6, -1,0) формула үшін динамикалық соңғы нүктені қалыптастыру.
- Функцияны аяқтау үшін OK түймешігін басыңыз және тілқатысу терезесін жабыңыз.
Барлығы $ 5679,15 B7 ұяшығында пайда болады.
B3 ұяшығын басқанда, жұмыс парағының үстіндегі формула жолында толық функция = SUM (B2: OFFSET (B6, -1,0)) пайда болады.
Келесі күннің сату деректерін қосу
Келесі күннің сату деректерін қосу үшін:
- Мәтінмәндік мәзірді ашу үшін жолдың 6 тақырыбына оң жақ бойынша басыңыз.
- Мәзірде жұмыс парағына жаңа жол енгізу үшін Кірістіру түймешігін басыңыз.
- Нәтижесінде SUM OFFSET формуласы B7 ұяшығына жылжиды және 6-жол бос.
- A6 ұяшығын басыңыз.
- 5- інші күні бесінші күннің сатылымының жалпы сомасы енгізілгенін көрсетіңіз.
- B6 ұяшығын басыңыз.
- $ 1458.25 нөмірін енгізіп , пернетақтадағы Enter пернесін басыңыз.
Cell B7 жаңа $ 7137.40 жалпы сомасына жаңартады.
B7 ұяшығын нұқығанда, өрнектің формуласында жаңартылған формуласы = SUM (B2: OFFSET (B7, -1,0)) пайда болады.
Ескерту : OFFSET функциясында осы мысалда жіберілмеген екі биіктік аргумент бар: Биіктігі мен ені .
Бұл аргументтер OFFSET функциясына шығудың пішінін ол көптеген жолдар мен ені көп бағандарға қатысты айту үшін қолдануға болады.
Осы аргументтерді елемей, функция, Әдепкіде, осы мысалда бір жол жоғары және бір бағанның ені болатын Сілтеме аргументінің биіктігін және енін пайдаланады.