Выкарыстоўвайце SUM і OFFSET знайсці выніковыя значэння для дынамічных дыяпазонаў дадзеных
Калі ваш Excel табліца ўключае ў сябе разлікі , заснаваныя на зменлівую дыяпазоне вочак, выкарыстоўваючы SUM і OFFSET функцыі разам у SUM OFFSET формулы спрашчае задачу захавання разлікаў у актуальным стане .
Стварэнне дынамічнага дыяпазону З SUM і OFFSET функцыі
Калі вы выкарыстоўваеце разлікі за пэўны перыяд часу, які ўвесь час змяняецца - напрыклад, на агульную суму продажаў за месяц - зрушаныя функцыя дазваляе наладзіць дынамічны дыяпазон, які ўвесь час змяняецца па меры дадання дадзеных аб продажах кожнага дня.
Сама па сабе функцыя сум звычайна можа змясціць новыя вочкі дадзеных, устаўляемыя ў дыяпазон падсумоўваюцца.
Адно выключэнне мае месца, калі дадзеныя ўстаўляюцца ў вочка, у якой функцыя ў цяперашні час знаходзіцца.
У дадзеным прыкладзе малюнак, якое суправаджае гэты артыкул, новыя дадзеныя аб продажах за кожны дзень дадаюцца ў ніжняй частцы спісу, які прымушае іх агульная колькасць пастаянна перамяшчацца ўніз на адну вочка кожны раз, калі дадаецца новыя дадзеныя.
Калі функцыя SUM выкарыстала на сваім уласным , каб сумаваць дадзеныя, неабходна было б змяніць дыяпазон вочак , якія выкарыстоўваюцца ў якасці функцыі ў аргуменце кожны раз дадавалі новыя дадзеныя.
З дапамогай SUM і Зрушэнне функцыі разам, аднак, дыяпазон, які склаў становіцца дынамічным. Іншымі словамі, змены для размяшчэння новых вочак дадзеных. Даданне новых вочак дадзеных не выклікае праблем, паколькі дыяпазон працягвае рэгуляваць па меры дадання кожнай новай ячэйкі.
Сінтаксіс і аргументы
Звярніцеся да выявы , якое суправаджае гэтую артыкул , каб прытрымлівацца разам з гэтым кіраўніцтвам.
У гэтай формуле функцыя сум выкарыстоўваецца для ўсяго дыяпазону дадзеных, прадстаўленых у якасці аргументу. Пачатковая кропка для гэтага дыяпазону з'яўляецца статычнай і ідэнтыфікуюцца як спасылка на вочка з першым нумарам будзе склала па формуле.
OFFSET функцыі укладзеная ўнутры функцыя SUM і выкарыстоўваецца для стварэння дынамічнай канчатковай кропкі да дыяпазону дадзеных склалі па формуле. Гэта дасягаецца шляхам ўстаноўкі канчатковай кропкі дыяпазону да адной вочку вышэй размяшчэння формулы.
Гэтая формула сінтаксіс :
= SUM (Range Start: OFFSET (спасылка, радкі, перавалы))Range Start - (абавязкова) адпраўную кропку для дыяпазону вочак , якія будуць падсумоўвацца з дапамогай функцыі SUM. У прыкладзе малюнку, гэта вочка B2.
Спасылка - (абавязкова) спасылка на вочка выкарыстоўваецца для вылічэння канчатковай кропкі размешчаны мноства радкоў і слупкоў у дыяпазоне прэч. У малюнку прыкладу, Референс аргумент з'яўляецца спасылкай на вочка для самой формулы , бо мы заўсёды хочам дыяпазон , каб скончыць адну вочка вышэй формулы.
Радка - (абавязкова) колькасць радкоў вышэй або ніжэй эталоннага аргументу , які выкарыстоўваецца пры вылічэнні зрушэння. Гэта значэнне можа быць станоўчым, адмоўным ці роўным нулю.
Калі месцазнаходжанне зрушэння знаходзіцца вышэй эталоннага аргументу, гэта адмоўнае значэнне. Калі яна ніжэй, то Шэрагі аргумент дадатны. Калі зрушэнне знаходзіцца ў той жа радку, гэты аргумент роўны нулю. У гэтым прыкладзе, зрушэнне пачынаецца на адзін радок вышэй эталоннага аргументу, так што значэнне гэтага аргументу з'яўляецца адмоўным (-1).
Cols - (абавязкова) колькасць слупкоў злева ці справа ад эталоннага аргументу , які выкарыстоўваецца пры вылічэнні зрушэння. Гэта значэнне можа быць станоўчым, адмоўным ці роўным нулю
Калі месцазнаходжанне зрушэння знаходзіцца злева ад эталоннага аргументу, гэта адмоўнае значэнне. Калі справа, то аргумент Cols станоўча. У гэтым прыкладзе, прычым дадзеныя налічваецца знаходзіцца ў тым жа слупку, што і формулы, так што значэнне гэтага аргументу роўна нулю.
Выкарыстанне SUM OFFSET Формула для Total дадзеных продажаў
Гэты прыклад выкарыстоўвае SUM OFFSET формулы для разліку агульных для штодзённых паказчыкаў продажаў, пералічаных ў Стоўбцах B працоўнага ліста.
Першапачаткова формула была ўведзена ў вочка B6 і склалі дадзеныя аб продажах на працягу чатырох дзён.
Наступны крок з'яўляецца пераходам SUM OFFSET формулы ўніз шэрагу, каб вызваліць месца для агульнай продажу пятага дня.
Гэта дасягаецца шляхам ўстаўкі новай радкі 6, які перамяшчае формулу ўніз да радка 7.
У выніку гэтага кроку, Excel аўтаматычна абнаўляе аргумент спасылкі на вочка B7 і дадае вочка B6 ў дыяпазоне сумаванага па формуле.
Ўвод SUM OFFSET Формула
- Націсніце на вочка B6, які з'яўляецца месцам, дзе першапачаткова адлюстроўваюцца вынікі формулы.
- Націсніце на ўкладцы Формулы ў стужках меню.
- Выберыце Math & Trig ад стужкі , каб адкрыць функцыю выпадальны спіс.
- Націсніце на SUM ў спісе , каб адкрыць функцыя ў дыялогавым акне .
- У дыялогавым акне, націсніце на лініі Number1.
- Націсніце на вочка B2 ўвесці гэтую спасылку на вочка ў дыялогавае акно. Гэта месца статычнай канчатковая кропка для формулы;
- У дыялогавым акне, націсніце на лініі Number2.
- Калі ласка, увядзіце наступную функцыю OFFSET: OFFSET (В6, -1,0) , каб сфармаваць дынамічную канчатковую кропку для формулы.
- Націсніце OK для завяршэння функцыі і зачыніць дыялогавае акно.
Агульны $ 5679,15 з'яўляецца ў вочку B7.
Пры націску на вочка B3, поўнай функцыі = SUM (B2: OFFSET (B6, -1,0)) з'яўляецца ў радку формул над лістом.
Даданне дадзеных па продажах на наступны дзень
Каб дадаць дадзеныя аб продажах на наступны дзень:
- Пстрыкніце правай кнопкай мышы на загалоўку радкі для 6 -й радку , каб адкрыць кантэкстнае меню.
- У меню абярыце Уставіць , каб ўставіць новы радок ў табліцу.
- У выніку, сума OFFSET формула перамяшчаецца ўніз да вочка B7 і радок 6 зараз пустая.
- Націсніце на вочка A6.
- Калі ласка, увядзіце нумар 5 , каб паказаць , што агульны аб'ём продажаў у пяты дзень быць уведзены.
- Націсніце на вочка B6.
- Калі ласка, увядзіце лік $ 1458,25 і націсніце клавішу Enter на клавіятуры.
Абнаўлення Cell B7 да новай агульнай складанасці $ 7.137,40.
Пры націску на вочка B7, абноўленая формула = SUM (B2: OFFSET (B7, -1,0)) з'яўляецца ў радку формул.
Заўвага: OFFSET функцыя мае два дадатковых аргументу: вышыня і шырыня, якія былі апушчаныя ў гэтым прыкладзе.
Гэтыя аргументы могуць быць выкарыстаны, каб сказаць функцыя зруху формы выхаднога сігналу з пункту гледжання прычым так шмат радкоў у вышыню і столькі слупкоў у шырыню.
Апускаючы гэтыя аргументы, функцыя, па змаўчанні, выкарыстоўвае вышыню і шырыню эталоннага аргументу замест гэтага, які ў дадзеным прыкладзе з'яўляецца адным радком высокай і адзін слупка шырокага.