Gamitin ang SUM at OFFSET upang makahanap ng mga kabuuan para sa mga dynamic na hanay ng data
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 pag-andar ng OFFSET ay nagbibigay-daan sa iyo na mag-set up ng isang dynamic na hanay na mapigil ang pagbabago habang ang mga benta ng bawat araw ay idinagdag.
Sa pamamagitan ng mismo, ang SUM function ay karaniwang maaaring tumanggap ng mga bagong cell ng data na ipinasok sa hanay na summed.
Ang isang eksepsiyon ay nangyayari kapag ang data ay ipinasok sa cell kung saan ang function ay kasalukuyang matatagpuan.
Sa halimbawang larawan na kasama ng artikulong ito, ang mga bagong benta para sa bawat araw ay idaragdag sa ilalim ng listahan, na pinipilit ang kabuuan upang patuloy na ibababa ang isang cell sa bawat oras na idagdag ang bagong data.
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 na kasama ng artikulong ito 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 Pagsisimula - (kinakailangang) ang panimulang punto para sa hanay ng mga selula na magkakaroon ng kabuuan ng SUM function. Sa halimbawa ng imahe, ito ay cell B2.
Sanggunian - (kailangan) ang sanggunian ng cell na ginamit para sa pagkalkula ng endpoint ng hanay na matatagpuan maraming mga hilera at mga haligi ang layo. Sa halimbawa ng imahe, ang argumento ng Sanggunian ay ang sanggunian ng cell para sa formula mismo dahil lagi naming nais ang hanay upang tapusin ang isang cell sa itaas ng formula.
Mga hilera - (kinakailangang) ang bilang ng mga hilera sa itaas o ibaba ng Reference argument na ginamit sa pagkalkula ng offset. Ang halaga na ito ay maaaring positibo, negatibo, o nakatakda sa zero.
Kung ang lokasyon ng offset ay nasa ibabaw ng Reference argument, ang negatibong halaga na ito. Kung ito ay nasa ibaba, ang argumento ng mga Rows ay positibo. Kung ang offset ay matatagpuan sa parehong hilera, ang argument na ito 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 - (kinakailangang) ang bilang ng mga haligi sa kaliwa o kanan ng Reference argument na ginamit sa pagkalkula ng offset. 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 data na totaled ay nasa parehong hanay bilang ang 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, ang Excel ay awtomatikong nag-a-update ng Reference argument sa cell B7 at nagdadagdag ng cell B6 sa saklaw na summed ng formula.
Pagpasok sa SUM OFFSET Formula
- Mag-click sa cell B6, na kung saan ay ang lokasyon kung saan ang mga resulta ng formula ay unang ipapakita.
- Mag-click sa tab na Formula ng laso menu.
- Piliin ang Math & Trig mula sa laso upang buksan ang drop-down na listahan ng function.
- Mag-click sa SUM sa listahan upang ilabas ang dialog box ng function.
- Sa dialog box, mag-click sa linya ng Number1 .
- 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;
- Sa dialog box, mag-click sa linya ng Number2 .
- Ipasok ang sumusunod na pag-andar ng OFFSET: OFFSET (B6, -1,0) upang mabuo ang dynamic na endpoint para sa formula.
- I - click ang OK upang makumpleto ang pag-andar at isara ang dialog box.
Ang kabuuang $ 5679.15 Lumilitaw sa cell B7.
Kapag nag-click ka sa cell B3, ang kumpletong function = SUM (B2: OFFSET (B6, -1,0)) ay lilitaw sa formula bar sa itaas ng worksheet.
Pagdaragdag ng Data ng Sales ng Susunod na Araw
Upang idagdag ang data ng mga benta sa susunod na araw:
- Mag-right-click sa header ng hilera para sa hilera 6 upang buksan ang menu ng konteksto.
- Sa menu, mag-click sa Magsingit upang magpasok ng isang bagong hilera papunta sa worksheet.
- Bilang resulta, ang SUM OFFSET formula ay gumagalaw pababa sa cell B7 at ang row 6 ay walang laman na ngayon.
- Mag-click sa cell A6 .
- Ipasok ang numero 5 upang ipahiwatig na ang kabuuang benta para sa ikalimang araw ay ipinasok.
- Mag-click sa cell B6.
- I-type ang numero $ 1458.25 at pindutin ang Enter key sa keyboard.
Ang mga update sa Cell B7 sa bagong kabuuang $ 7137.40.
Kapag nag-click ka sa cell B7, ang na-update na formula = SUM (B2: OFFSET (B7, -1,0)) ay lilitaw sa formula bar.
Tandaan : Ang pag-andar ng OFFSET ay may dalawang opsyonal na argumento: Taas at Lap, na tinanggal sa halimbawang ito.
Ang mga argumento na ito ay maaaring gamitin upang sabihin sa OFFSET function ang hugis ng output sa mga tuntunin ng pagiging kaya maraming mga hilera mataas at kaya maraming 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.