Excel SUM at OFFSET Formula

Kung ang iyong Excel worksheet ay may kasamang mga kalkulasyon batay sa isang pagbabago ng hanay ng mga cell, gamit ang SUM at OFFSET function na magkasama sa isang SUM OFFSET formula pinapasimple ang gawain ng pagsunod sa mga kalkulasyon ng hanggang sa petsa.

Gumawa ng Dynamic Range kasama ang SUM at OFFSET Function

Kung gumagamit ka ng mga kalkulasyon para sa isang tagal ng panahon na patuloy na nagbabago - tulad ng kabuuang mga benta para sa buwan - ang OFFSET function sa Excel ay nagbibigay-daan sa iyo upang mag-set up ng isang dynamic na saklaw na nagbabago habang ang mga numero ng benta sa bawat araw ay idinagdag.

Sa pamamagitan ng mismo, ang SUM function ay karaniwang maaaring tumanggap ng pagpapasok ng mga bagong selula ng data sa saklaw na summed.

Ang isang eksepsiyon ay nangyayari kapag ang data ay ipinasok sa cell kung saan ang function ay kasalukuyang matatagpuan.

Sa halimbawang larawan sa itaas, ang mga bagong numero ng benta para sa bawat araw ay idinagdag sa ilalim ng listahan, na pinipilit ang kabuuang upang patuloy na ibababa ang isang cell sa tuwing ang bagong data ay idinagdag.

Kung ang SUM function ay ginamit sa sarili nitong kabuuan ng data, kinakailangan na baguhin ang hanay ng mga cell na ginamit bilang argumento sa pag-andar sa bawat oras na idinagdag ang bagong data.

Sa pamamagitan ng paggamit ng SUM at OFFSET function magkasama, gayunpaman, ang hanay na totaled nagiging dynamic. Sa madaling salita, nagbabago ito upang mapaunlakan ang mga bagong selula ng data. Ang pagdaragdag ng mga bagong cell ng data ay hindi nagiging sanhi ng mga problema dahil ang hanay ay patuloy na ayusin habang ang bawat bagong cell ay idinagdag.

Syntax and Arguments

Sumangguni sa larawan sa itaas upang sundin kasama ang tutorial na ito.

Sa formula na ito, ang SUM function ay ginagamit upang kumpletuhin ang hanay ng data na ibinigay bilang argumento nito. Ang panimulang punto para sa saklaw na ito ay static at ay nakilala bilang ang cell reference sa unang numero na puno ng formula.

Ang pag-andar ng OFFSET ay nakapaso sa loob ng SUM na function at ginagamit upang lumikha ng isang dynamic na endpoint sa hanay ng data na nakuha ng formula. Ito ay natapos sa pamamagitan ng pagtatakda ng endpoint ng saklaw sa isang cell sa itaas ng lokasyon ng formula.

Ang syntax ng formula:

= SUM (Saklaw ng Saklaw: OFFSET (Sanggunian, Mga Hilera, Cols))

• Saklaw ng Saklaw: Ang panimulang punto para sa hanay ng mga selulang gagamitin ng SUM ay kinakailangan. Sa halimbawa ng imahe, ito ay cell B2.
• Sanggunian: Ang kinakailangang sanggunian ng cell na ginagamit para sa pagkalkula ng endpoint ng hanay ay matatagpuan maraming mga hilera at haligi ang layo. Sa halimbawa ng imahe, ang Reference argument ay ang sanggunian ng cell para sa formula mismo dahil lagi mong nais ang hanay upang tapusin ang isang cell sa itaas ng formula.
• Mga Hilera: Ang bilang ng mga hanay sa itaas o ibaba ng Reference argument na ginamit sa pagkalkula ng offset ay kinakailangan. Ang halaga na ito ay maaaring positibo, negatibo, o nakatakda sa zero. Kung ang lokasyon ng offset ay nasa ibabaw ng Sanggunian argumento, negatibo ang halaga. Kung ito ay nasa ibaba, ang argumento ng mga Rows ay positibo. Kung ang offset ay matatagpuan sa parehong hilera, ang argument ay zero. Sa halimbawang ito, ang offset ay nagsisimula sa isang hilera sa itaas ng Reference argument, kaya ang halaga para sa argument na ito ay negatibong isa (-1).
• Cols: Ang bilang ng mga hanay sa kaliwa o kanan ng Reference argument na ginamit sa pagkalkula ng offset ay kinakailangan. Ang halaga na ito ay maaaring positibo, negatibo, o nakatakda sa zero. Kung ang lokasyon ng offset ay sa kaliwa ng argumento ng Reference, ang negatibong halaga na ito. Kung sa kanan, ang argumento ng Cols ay positibo. Sa halimbawang ito, ang bilang ng data ay nasa parehong hanay bilang formula, kaya ang halaga para sa argument na ito ay zero.

Gamit ang SUM OFFSET Formula sa Kabuuang Sales Data

Ang halimbawang ito ay gumagamit ng SUM OFFSET formula upang ibalik ang kabuuan para sa pang-araw-araw na mga numero ng pagbebenta na nakalista sa haligi B ng worksheet.

Sa una, ang formula ay ipinasok sa cell B6 at totaled ang data ng benta para sa apat na araw.

Ang susunod na hakbang ay upang ilipat ang SUM OFFSET formula pababa sa isang hilera upang gumawa ng kuwarto para sa kabuuang benta ng ikalimang araw. Ito ay natapos sa pamamagitan ng pagpasok ng isang bagong hilera 6, na gumagalaw sa formula pababa sa hilera 7.

Bilang resulta ng paglipat, Awtomatikong i-update ng Excel ang argumento ng Reference sa cellB7 at nagdadagdag ng cellB6 sa saklaw na summed ng formula.

Pagpasok sa SUM OFFSET Formula

1. Mag-click sa cell B6, na kung saan ay ang lokasyon kung saan ang mga resulta ng formula ay unang ipapakita.
2. Mag-click saFormula tab ng menu ng laso.
3. PumiliMath & Trigmula sa laso upang buksan ang drop-down na listahan ng function.
4. Mag-click saSUM sa listahan upang ilabas ang dialog box ng function.
5. Sa dialog box, mag-click saNumber1linya.
6. Mag-click sa cell B2 upang ipasok ang reference ng cell na ito sa dialog box. Ang lokasyong ito ay ang static endpoint para sa formula.
7. Sa dialog box, mag-click saNumber2linya.
8. Ipasok ang sumusunod na pag-andar ng PAGKATAPOS: OFFSET (B6, -1,0) upang bumuo ng dynamic na endpoint para sa formula.
9. Mag-click OK upang makumpleto ang pag-andar at isara ang dialog box.

Lumilitaw ang kabuuan sa cell B7.

Pagdaragdag ng Data ng Sales ng Susunod na Araw

Upang idagdag ang data ng mga benta sa susunod na araw:

1. Mag-right-click sa header ng hilera para sa hilera 6 upang buksan ang menu ng konteksto.
2. Sa menu, mag-click saMagsingit upang magsingit ng isang bagong hilera sa worksheet.
3. Bilang resulta, ang SUM OFFSET formula ay gumagalaw pababa sa cell B7 at ang row 6 ay walang laman na ngayon.
4. Mag-click sa cell A6.
5. Ipasok ang numero5 upang ipahiwatig na ang kabuuang benta para sa ikalimang araw ay ipinasok.
6. Mag-click sa cell B6.
7. I-type ang numero$1458.25 at pindutin angIpasok susi sa keyboard.

Ang mga update sa Cell B7 sa bagong kabuuan$7137.40.

Kapag nag-click ka sa cell B7, ang na-update na formula= SUM (B2: OFFSET (B7, -1,0)) Lumilitaw sa formula bar.

Ang OFFSET function ay may dalawang opsyonal na argumento: Taas at Lapad , na hindi ginamit sa halimbawang ito.

Ang mga argumento na ito ay maaaring gamitin upang sabihin sa OFFSET function ang hugis ng output sa mga tuntunin ng napakaraming mga hilera mataas at napakaraming mga haligi malawak.

Sa pamamagitan ng pag-omit sa mga argumentong ito, ang pag-andar, sa pamamagitan ng default, ay gumagamit ng taas at lapad ng Reference argument sa halip, kung saan, sa halimbawang ito ay isang hanay na mataas at isang haligi na lapad.