Uża SUM u OFFSET biex issib totali għal meded dinamiċi ta 'data
Jekk il- worksheet Excel tagħkom tinkludi kalkoli bbażati fuq firxa ta 'ċelloli li qed jinbidlu, bl-użu tal-funzjonijiet SUM u OFFSET flimkien f'format SUM OFFSET tissimplifika l-kompitu li żżomm il-kalkoli aġġornati.
Oħloq Firxa Dinamika Bil-Funzjonijiet SUM u OFFSET
Jekk tuża kalkoli għal perjodu ta 'żmien li kontinwament tinbidel - bħat-total tal-bejgħ għax-xahar - il-funzjoni OFFSET jippermettilek li tistabbilixxi medda dinamika li tibqa' tinbidel hekk kif jiżdiedu ċ-ċifri tal-bejgħ ta 'kull jum.
Minnu nnifsu, il- funzjoni SUM normalment tista 'takkomoda ċelloli ġodda ta' data li qed tiddaħħal fil-medda tiġi mqassra.
Eċċezzjoni waħda sseħħ meta d-data tiddaħħal fiċ-ċellola fejn il-funzjoni tinsab bħalissa.
Fl-immaġni ta 'l-eżempju li takkumpanja dan l-artikolu, iċ-ċifri tal-bejgħ il-ġodda għal kull jum huma miżjuda mal-qiegħ tal-lista, li jġiegħel lit-total iċċaqlaq kontinwament ċellola waħda kull darba li d-data ġdida tkun miżjuda.
Jekk il-funzjoni SUM intużat waħedha biex timla d-data, ikun meħtieġ li tiġi mmodifikata l-firxa ta 'ċelloli użati bħala l- argument tal-funzjoni kull darba li ġiet miżjuda data ġdida.
Bl-użu tal-funzjonijiet SUM u OFFSET flimkien, madankollu, il-firxa li hija totali ssir dinamika. Fi kliem ieħor, tinbidel biex takkomoda ċelloli ġodda ta 'data. Iż-żieda ta 'ċelluli ġodda ta' dejta ma toħloqx problemi minħabba li l-firxa tkompli taġġusta hekk kif kull ċellula ġdida tiġi miżjuda.
Sintassi u Argumenti
Irreferi għall-immaġni li takkumpanja dan l-artikolu biex isegwi flimkien ma 'dan it-tutorja.
F'din il-formula, il-funzjoni SUM hija użata biex tkabbar il-medda ta 'data fornuta bħala l-argument tagħha. Il-punt tat-tluq għal din il-medda huwa statiku u huwa identifikat bħala r -referenza taċ - ċellola għall-ewwel numru li għandu jiġi kkalkolat bil-formula.
Il-funzjoni OFFSET hija nested ġewwa l-funzjoni SUM u tintuża biex toħloq punt ta 'tmiem dinamiku għall-medda ta' data totali mill-formula. Dan jitwettaq bl-iffissar tal-punt tat-tmiem tal-firxa għal ċellola waħda 'l fuq mill-post tal-formula.
Is- sintassi tal-formula:
= SUM (Firxa tal-Medda: OFFSET (Referenza, Ringieli, Cols))Firxa tal-Bidu - (meħtieġ) il-punt tat-tluq għall-firxa ta 'ċelloli li se jkunu jammontaw għall-funzjoni SUM. Fl-immaġni ta 'eżempju, din hija ċellola B2.
Referenza - (meħtieġa) ir-referenza taċ-ċellula użata għall-kalkolu tal-endpoint tal-firxa li tinsab ħafna ringieli u kolonni 'l bogħod. Fl-immaġni ta 'l-eżempju, l-argument ta' Referenza huwa r-referenza taċ-ċellola għall-formula nfisha peress li dejjem irridu li l-firxa tieqaf ċellola waħda 'l fuq mill-formula.
Ringieli - (meħtieġa) in-numru ta 'ringieli ta' hawn fuq jew taħt l-argument ta ' Referenza użat fil-kalkolu tal-kumpens. Dan il-valur jista 'jkun pożittiv, negattiv, jew issettjat għal żero.
Jekk il-pożizzjoni tal-kumpens tkun 'il fuq mill-argument ta' Referenza , dan il-valur huwa negattiv. Jekk huwa hawn taħt, l-argument ta ' Rows huwa pożittiv. Jekk l-offset jinsab fl-istess ringiela, dan l-argument huwa żero. F'dan l-eżempju, l-offset jibda linja waħda 'l fuq mill-argument ta' Referenza , għalhekk il-valur għal dan l-argument huwa wieħed negattiv (-1).
Cols - (meħtieġ) in-numru ta ' kolonni lejn ix-xellug jew il-lemin ta' l-argument ta ' Referenza użat fil-kalkolu tal-kumpens. Dan il-valur jista 'jkun pożittiv, negattiv, jew issettjat għal żero
Jekk il-pożizzjoni tal-kumpens tkun fuq ix-xellug tal-argument ta ' Referenza , dan il-valur huwa negattiv. Jekk fuq il-lemin, l-argument Cols huwa pożittiv. F'dan l-eżempju, id-data li qed tiġi kkonsolidata hija fl-istess kolonna bħall-formula hekk il-valur għal dan l-argument huwa żero.
Uża l-formola SUM OFSETET to Total Sales Data
Dan l-eżempju juża formula SUM OFFSET biex jirritorna t-total għall-figuri tal-bejgħ ta 'kuljum elenkati fil-kolonna B tal-folja.
Inizjalment, il-formula ġiet imdaħħla fiċ-ċellula B6 u kienet tammonta għad-data tal-bejgħ għal erbat ijiem.
Il-pass li jmiss hu li titlaq il-formula SUM OFFSET fl ringiela biex tagħti spazju għall-bejgħ tal-ħames jum kollu.
Dan jitwettaq billi tiddaħħal ringiela ġdida 6, li timxi l-formula 'l isfel għar-ringiela 7.
Bħala riżultat tal-moviment, Excel jaġġorna awtomatikament l-argument ta 'Referenza għaċ-ċellula B7 u jżid iċ-ċellula B6 mal-medda magħduda mal-formula.
Dħul fil-Formola SUM OFFSET
- Ikklikkja fuq iċ-ċellula B6, li hija l-post fejn se jintwerew ir-riżultati tal-formula inizjalment.
- Ikklikkja fuq it-tab tal- Formuli tal-menu taż - żigarella .
- Agħżel Matematika u Trig mir-żigarella biex tiftaħ il-lista tal-drop-down tal-funzjoni.
- Ikklikkja fuq SUM fil-lista biex iġġib id- dialog box tal-funzjoni.
- Fil-kaxxa ta 'dialog, ikklikkja fuq il-linja Number1 .
- Ikklikkja fuq iċ-ċellula B2 biex tidħol f'din iċ-ċellula ta 'referenza fil-kaxxa ta' dialog Dan il-post huwa l-punt tat-tmiem statiku għall-formula;
- Fil-kaxxa ta 'dialog, ikklikkja fuq il-linja Number2 .
- Daħħal il-funzjoni ta 'OFFSETT li ġejja: OFFSET (B6, -1,0) biex tifforma l-endpoint dinamiku għall-formula.
- Ikklikkja OK biex timla l-funzjoni u tagħlaq il-kaxxa tad-djalogu
It-total ta '$ 5679.15 tidher fiċ-ċellula B7.
Meta tikklikja fuq iċ-ċellula B3, il-funzjoni kompluta = SUM (B2: OFFSET (B6, -1,0)) tidher fil- bar tal-formola 'l fuq mill-worksheet.
Żieda tad-Dejta tal-Bejgħ ta 'Jum li Jmiss
Biex iżżid id-data tal-bejgħ tal-jum ta 'wara:
- Ikklikkja fuq il-buttuna tar- ringiela għar-ringiela 6 biex tiftaħ il-menu tal-kuntest.
- Fil-menu, ikklikkja fuq Insert biex daħħal ringiela ġdida fil-worksheet.
- Bħala riżultat, il-formula SUM OFFSET titla 'għal ċellula B7 u r-ringiela 6 issa hija vojta.
- Ikklikkja fuq iċ-ċellula A6 .
- Daħħal in-numru 5 biex tindika li qed jiddaħħal it-total tal-bejgħ għall-ħames jum.
- Ikklikkja fuq iċ-ċellula B6.
- Tip in-numru $ 1458.25 u agħfas il-buttuna Enter fuq it-tastiera.
L-aġġornamenti taċ-Ċellula B7 għat-total ġdid ta ' $ 7137.40.
Meta tikklikja fuq iċ-ċellula B7, il-formula aġġornata = SUM (B2: OFFSET (B7, -1,0)) tidher fil-bar tal-formola.
Nota : Il-funzjoni OFFSET għandha żewġ argumenti fakultattivi: Għoli u Wisa ', li tħallew barra f'dan l-eżempju.
Dawn l-argumenti jistgħu jintużaw biex jgħidu l-funzjoni OFFSET il-forma tal-produzzjoni f'termini ta 'kemm tkun ringieli tant għoljin u b'ħafna kolonni wiesgħa.
Meta tħalli barra dawn l-argumenti, il-funzjoni, awtomatikament, tuża l-għoli u l-wisa 'tal-argument ta' Referenza minflok, li f'dan l-eżempju huwa ringiela waħda għolja u kolonna waħda wiesgħa.