Kung ang iyong Excel worksheet ay may kasamang mga kalkulasyon na batay sa nagbabagong hanay ng mga cell, gamitin ang SUM at OFFSET function nang magkasama sa isang SUM OFFSET na formula upang pasimplehin ang gawain ng pagpapanatiling napapanahon ang mga kalkulasyon.
Nalalapat ang mga tagubilin sa artikulong ito sa Excel para sa Microsoft 365, Excel 2019, Excel 2016, Excel 2013, at Excel 2010.
Gumawa ng Dynamic na Saklaw Gamit ang SUM at OFFSET Function
Kung gumagamit ka ng mga kalkulasyon para sa isang yugto ng panahon na patuloy na nagbabago - tulad ng pagtukoy sa mga benta para sa buwan - gamitin ang OFFSET function sa Excel upang mag-set up ng dynamic na hanay na nagbabago habang ang mga numero ng benta sa bawat araw ay idinagdag.
Sa mismong paraan, ang SUM function ay karaniwang maaaring tumanggap ng pagpasok ng mga bagong cell ng data sa hanay na isinu-summed. Ang isang pagbubukod ay nangyayari kapag ang data ay ipinasok sa cell kung saan ang function ay kasalukuyang matatagpuan.
Sa halimbawa sa ibaba, ang mga bagong numero ng benta para sa bawat araw ay idinaragdag sa ibaba ng listahan, na pinipilit ang kabuuan na patuloy na bumaba ng isang cell sa bawat oras na idinadagdag ang bagong data.
Upang sundan ang tutorial na ito, magbukas ng blangkong Excel worksheet at ilagay ang sample na data. Ang iyong worksheet ay hindi kailangang i-format tulad ng halimbawa, ngunit tiyaking ilagay ang data sa parehong mga cell.
Kung ang SUM function lang ang gagamitin sa kabuuan ng data, ang hanay ng mga cell na ginamit bilang argument ng function ay kailangang baguhin sa tuwing magdaragdag ng bagong data.
Sa pamamagitan ng paggamit ng SUM at OFFSET na mga function nang magkasama, nagiging dynamic at nagbabago ang saklaw na pinagsama-sama upang ma-accommodate ang mga bagong cell ng data. Ang pagdaragdag ng mga bagong cell ng data ay hindi nagdudulot ng mga problema dahil patuloy na nagsasaayos ang hanay habang idinaragdag ang bawat bagong cell.
Syntax and Argument
Sa formula na ito, ang SUM function ay ginagamit upang kabuuang saklaw ng data na ibinigay bilang argumento. Ang panimulang punto para sa hanay na ito ay static at kinikilala bilang ang cell reference sa unang numero na isasama ng formula.
Ang OFFSET function ay naka-nest sa loob ng SUM function at gumagawa ng dynamic na endpoint sa hanay ng data na pinagsama-sama ng formula. Nagagawa ito sa pamamagitan ng pagtatakda ng endpoint ng range sa isang cell sa itaas ng lokasyon ng formula.
Ang formula syntax ay:
=SUM(Range Start:OFFSET(Reference, Rows, Cols))
Ang mga argumento ay:
- Range Start: Ang panimulang punto para sa hanay ng mga cell na tutuusin ng SUM function. Sa halimbawang ito, ang panimulang punto ay cell B2.
- Reference: Ang kinakailangang cell reference na ginamit upang kalkulahin ang endpoint ng hanay. Sa halimbawa, ang Reference argument ay ang cell reference para sa formula dahil ang range ay nagtatapos sa isang cell sa itaas ng formula.
- Rows: Kinakailangan ang bilang ng mga row sa itaas o ibaba ng Reference argument na ginamit sa pagkalkula ng offset. Maaaring positibo, negatibo, o itakda sa zero ang value na ito. Kung ang offset na lokasyon ay nasa itaas ng Reference argument, ang value ay negatibo. Kung nasa ibaba ang offset, positibo ang argumento ng Rows. Kung ang offset ay matatagpuan sa parehong hilera, ang argumento ay zero. Sa halimbawang ito, ang offset ay magsisimula ng isang row sa itaas ng Reference argument, kaya ang value para sa argument ay negative one (-1).
- Cols: Ang bilang ng mga column sa kaliwa o kanan ng Reference argument na ginamit upang kalkulahin ang offset. Maaaring positibo, negatibo, o itakda sa zero ang value na ito. Kung ang offset na lokasyon ay nasa kaliwa ng Reference argument, negatibo ang value na ito. Kung ang offset ay nasa kanan, ang Cols argument ay positibo. Sa halimbawang ito, ang data na binibilang ay nasa parehong column bilang formula, kaya ang value para sa argument na ito ay zero.
Gamitin ang SUM OFFSET Formula sa Kabuuang Data ng Benta
Gumagamit ang halimbawang ito ng formula na SUM OFFSET upang ibalik ang kabuuan para sa mga pang-araw-araw na bilang ng benta na nakalista sa column B ng worksheet. Sa una, ang formula ay inilagay sa cell B6 at pinagsama-sama ang data ng mga benta sa loob ng apat na araw.
Ang susunod na hakbang ay ilipat ang formula ng SUM OFFSET pababa sa isang row para bigyang puwang ang kabuuang benta ng ikalimang araw. Nagagawa ito sa pamamagitan ng paglalagay ng bagong row 6, na naglilipat ng formula sa row 7.
Bilang resulta ng paglipat, awtomatikong ina-update ng Excel ang Reference argument sa cell B7 at idinaragdag ang cell B6 sa hanay na na-summed ng formula.
- Piliin ang cell B6, na siyang lokasyon kung saan unang ipapakita ang mga resulta ng formula.
-
Piliin ang Formulas tab ng ribbon.
Image -
Pumili Math at Trig.
Image -
Piliin ang SUM.
Image - Sa Function Arguments dialog box, ilagay ang cursor sa Number1 text box.
-
Sa worksheet, piliin ang cell B2 upang ilagay ang cell reference na ito sa dialog box. Ang lokasyong ito ay ang static na endpoint para sa formula.
Image - Sa Function Arguments dialog box, ilagay ang cursor sa Number2 text box.
-
Enter OFFSET(B6, -1, 0). Binubuo ng OFFSET function na ito ang dynamic na endpoint para sa formula.
Image -
Piliin ang OK upang kumpletuhin ang function at isara ang dialog box. Lumalabas ang kabuuan sa cell B6.
Image
Idagdag ang Data ng Benta sa Susunod na Araw
Upang magdagdag ng data ng benta sa susunod na araw:
- I-right click ang row header para sa row 6.
-
Piliin ang Insert upang magpasok ng bagong row sa worksheet. Ang formula ng SUM OFFSET ay lumilipat pababa sa isang row sa cell B7 at ang row 6 ay walang laman na ngayon.
Image - Piliin ang cell A6 at ilagay ang numerong 5 upang isaad na ang kabuuang benta para sa ikalimang araw ay ipinapasok.
-
Pumili ng cell B6, ilagay ang $1458.25, pagkatapos ay pindutin ang Enter.
Image - Mga update sa Cell B7 sa bagong kabuuang $7137.40.
Kapag pinili mo ang cell B7, lalabas ang na-update na formula sa formula bar.
=SUM(B2:OFFSET(B7, -1, 0))
Ang OFFSET function ay may dalawang opsyonal na argumento: Taas at Lapad, na hindi ginamit sa halimbawang ito. Sinasabi ng mga argumentong ito sa OFFSET function ang hugis ng output ayon sa bilang ng mga row at column.
Sa pamamagitan ng pag-alis sa mga argumentong ito, ginagamit na lang ng function ang taas at lapad ng Reference argument, na, sa halimbawang ito ay isang row ang taas at isang column ang lapad.
