Defnyddiwch SUM ac OFFSET i ddod o hyd i gyfansymiau ar gyfer ystodau data deinamig
Os yw eich taflen waith Excel yn cynnwys cyfrifiadau yn seiliedig ar ystod newidiol o gelloedd, gan ddefnyddio'r swyddogaethau SUM a OFFSET gyda'i gilydd mewn fformiwla OFFERS SUM yn symleiddio'r dasg o gadw'r cyfrifiadau yn gyfoes.
Creu Ystod Ddynamig gyda'r Swyddogaethau SUM a OFFSET
Os ydych chi'n defnyddio cyfrifiadau am gyfnod o amser sy'n newid yn barhaus - fel gwerthiant cyfan y mis - mae swyddogaeth OFFSET yn caniatáu i chi sefydlu ystod ddeinamig sy'n cadw'n newid wrth i ffigyrau gwerthiant bob dydd gael eu hychwanegu.
Drwy'i hun, gall y swyddogaeth SUM gynnwys celloedd newydd o ddata yn cael eu cynnwys yn yr ystod sy'n cael ei grynhoi.
Mae un eithriad yn digwydd pan fydd y data yn cael ei fewnosod yn y gell lle mae'r swyddogaeth wedi'i leoli ar hyn o bryd.
Yn y ddelwedd enghreifftiol sy'n cyd-fynd â'r erthygl hon, mae'r ffigurau gwerthiant newydd ar gyfer pob diwrnod yn cael eu hychwanegu at waelod y rhestr, sy'n gorfodi'r cyfanswm i symud i lawr un cell yn barhaus bob tro y bydd y data newydd yn cael ei ychwanegu.
Pe bai'r swyddogaeth SUM yn cael ei ddefnyddio ar ei ben ei hun i gyfanswm y data, byddai angen addasu'r ystod o gelloedd a ddefnyddir fel dadl y swyddogaeth bob tro ychwanegwyd data newydd.
Trwy ddefnyddio'r swyddogaethau SUM a OFFSET gyda'i gilydd, fodd bynnag, mae'r ystod sy'n cael ei gyfanswm yn dod yn ddeinamig. Mewn geiriau eraill, mae'n newid i ddarparu celloedd newydd o ddata. Nid yw ychwanegu celloedd newydd o ddata yn achosi problemau oherwydd bod yr ystod yn parhau i addasu wrth i bob cell newydd gael ei ychwanegu.
Cystrawen a Dadleuon
Cyfeiriwch at y ddelwedd sy'n cyd-fynd â'r erthygl hon i'w dilyn ynghyd â'r tiwtorial hwn.
Yn y fformiwla hon, defnyddir y swyddogaeth SUM i gyfanswm yr ystod o ddata a gyflenwir fel ei ddadl. Mae'r pwynt cychwyn ar gyfer yr ystod hon yn sefydlog ac fe'i dynodir fel y cyfeirnod cell at y rhif cyntaf i'w gyfanswm gan y fformiwla.
Mae swyddogaeth OFFSET wedi'i nythu y tu mewn i swyddogaeth SUM ac fe'i defnyddir i greu pen pen dynamig i'r ystod o ddata a gyfanswmir gan y fformiwla. Gwneir hyn trwy osod pen pen yr ystod i un gell uwchlaw lleoliad y fformiwla.
Cystrawen y fformiwla:
= SUM (Dechrau Ystod: OFFSET (Cyfeirnod, Llifau, Cols))Ystod Dechrau - (gofynnol) y man cychwyn ar gyfer yr ystod o gelloedd a fydd yn cael eu cyfansymu gan y swyddogaeth SUM. Yn y ddelwedd enghreifftiol, mae hyn yn gell B2.
Cyfeirio - (gofynnol) y cyfeirnod cell a ddefnyddiwyd ar gyfer cyfrifo pen pen yr ystod a leolir sawl rhes a cholofn i ffwrdd. Yn y ddelwedd enghreifftiol, y ddadl Gyfeirio yw'r cyfeirnod celloedd ar gyfer y fformiwla ei hun gan ein bod bob amser am i'r ystod ddod i ben un cell uwchben y fformiwla.
Rhesymau - (gofynnol) nifer y rhesi uwchlaw neu islaw'r ddadl Gyfeirio a ddefnyddir wrth gyfrifo'r gwrthbwyso. Gall y gwerth hwn fod yn bositif, negyddol, neu ei osod i sero.
Os yw lleoliad y gwrthbwyso yn uwch na'r ddadl Gyfeirio , mae'r gwerth hwn yn negyddol. Os yw'n is, mae'r ddadl Rhesymau yn gadarnhaol. Os yw'r gwrthbwyso wedi'i leoli yn yr un rhes, mae'r ddadl hon yn sero. Yn yr enghraifft hon, mae'r gwrthbwyso yn dechrau un rhes uwchlaw'r ddadl Gyfeirio , felly mae'r gwerth am y ddadl hon yn un negyddol (-1).
Cols - (gofynnol) nifer y colofnau ar y chwith neu'r dde o'r ddadl Gyfeirio a ddefnyddir wrth gyfrifo'r gwrthbwyso. Gall y gwerth hwn fod yn bositif, negyddol, neu ei osod i sero
Os yw'r lleoliad gwrthbwyso ar ochr chwith y ddadl Gyfeirio , mae'r gwerth hwn yn negyddol. Os i'r dde, mae'r ddadl Cols yn gadarnhaol. Yn yr enghraifft hon, mae cyfanswm y data yn yr un golofn â'r fformiwla felly mae'r gwerth ar gyfer y ddadl hon yn sero.
Defnyddio Fformiwla SUM OFFSET i Cyfanswm Data Gwerthu
Mae'r enghraifft hon yn defnyddio fformiwla OFFERI SUM i ddychwelyd cyfanswm y ffigurau gwerthiant dyddiol a restrir yng ngholofn B o'r daflen waith.
I ddechrau, rhoddwyd y fformiwla i mewn i gelloedd B6 a chyfanswm y data gwerthu am bedwar diwrnod.
Y cam nesaf yw symud y fformwla SUM OFFSET i lawr rhes i wneud lle ar gyfer cyfanswm gwerthiant y pumed diwrnod.
Gwneir hyn trwy fewnosod rhes newydd 6, sy'n symud y fformiwla i redeg 7.
O ganlyniad i'r symudiad, mae Excel yn diweddaru'r ddadl Gyfeirio yn awtomatig i gell B7 ac yn ychwanegu cell B6 i'r ystod a grynhoir gan y fformiwla.
Ymuno â'r Fformiwla FFURFLEN SUM
- Cliciwch ar gell B6, sef y lleoliad lle bydd canlyniadau'r fformiwla yn cael ei harddangos i ddechrau.
- Cliciwch ar daflen Fformiwlâu'r ddewislen rhuban .
- Dewiswch Mathemateg a Trig o'r rhuban i agor y rhestr i lawr y swyddogaeth.
- Cliciwch ar SUM yn y rhestr i ddod â blwch deialog y swyddogaeth i fyny.
- Yn y blwch deialog, cliciwch ar linell Number1 .
- Cliciwch ar gell B2 i nodi'r cyfeirnod celloedd hwn yn y blwch deialog. Y lleoliad hwn yw'r pen pen statig ar gyfer y fformiwla;
- Yn y blwch deialog, cliciwch ar y llinell Number2 .
- Rhowch y swyddogaeth OFFSET canlynol: OFFSET (B6, -1,0) i ffurfio'r pen pen dynaidd ar gyfer y fformiwla.
- Cliciwch OK i gwblhau'r swyddogaeth a chau'r blwch deialog.
Cyfanswm $ 5679.15 yn ymddangos yng nghell B7.
Pan fyddwch yn clicio ar gell B3, mae'r swyddogaeth gyflawn = SUM (B2: OFFSET (B6, -1,0)) yn ymddangos yn y bar fformiwla uwchben y daflen waith.
Ychwanegu Data Gwerthu Nesaf y Dydd
I ychwanegu data gwerthiant y diwrnod canlynol:
- De-gliciwch ar y pennawd rhes ar gyfer rhes 6 i agor y ddewislen cyd-destun.
- Yn y ddewislen, cliciwch ar Insert i mewnosod rhes newydd i'r daflen waith.
- O ganlyniad, mae fformiwla SUM OFFSET yn symud i lawr i gell B7 ac mae rhes 6 bellach yn wag.
- Cliciwch ar gell A6 .
- Rhowch y rhif 5 i nodi bod cyfanswm y gwerthiant ar gyfer y pumed diwrnod yn cael ei gofnodi.
- Cliciwch ar gell B6.
- Teipiwch y rhif $ 1458.25 a phwyswch yr Allwedd Enter ar y bysellfwrdd.
Mae diweddariadau Cell B7 i'r cyfanswm newydd o $ 7137.40.
Pan fyddwch yn clicio ar gell B7, mae'r fformiwla wedi'i ddiweddaru = SUM (B2: OFFSET (B7, -1,0)) yn ymddangos yn y bar fformiwla.
Sylwer : Mae gan swyddogaeth OFFSET ddau ddadl opsiynol: Uchder a Lled, a hepgorwyd yn yr enghraifft hon.
Gellir defnyddio'r dadleuon hyn i ddweud wrth swyddogaeth OFFSET siâp yr allbwn o ran ei fod yn gymaint o resymau yn uchel a chymaint o golofnau yn eang.
Trwy hepgor y dadleuon hyn, mae'r swyddogaeth, yn ddiofyn, yn defnyddio uchder a lled y ddadl Gyfeirio yn lle hynny, sydd, yn yr enghraifft hon, yn un rhes uchel ac un golofn ar led.