Ikiwa laha yako ya kazi ya Excel inajumuisha hesabu ambazo zinatokana na anuwai ya seli zinazobadilika, tumia vitendaji vya SUM na OFFSET pamoja katika fomula ya SUM OFFSET ili kurahisisha kazi ya kusasisha hesabu.
Maagizo katika makala haya yanatumika kwa Excel kwa Microsoft 365, Excel 2019, Excel 2016, Excel 2013, na Excel 2010.
Unda Safu Inayobadilika Ukitumia Majukumu ya SUM na OFFSET
Kama unatumia hesabu kwa kipindi cha muda ambacho hubadilika kila mara - kama vile kubainisha mauzo ya mwezi - tumia chaguo la kukokotoa la OFFSET katika Excel ili kuweka safu badilika ambayo hubadilika kadri takwimu za mauzo za kila siku zinavyoongezwa.
Kwenyewe, chaguo la kukokotoa la SUM kwa kawaida linaweza kushughulikia uwekaji wa visanduku vipya vya data kwenye masafa yanayotolewa. Isipokuwa moja hutokea wakati data inapoingizwa kwenye kisanduku ambapo chaguo za kukokotoa zinapatikana kwa sasa.
Katika mfano ulio hapa chini, takwimu mpya za mauzo kwa kila siku huongezwa kwenye sehemu ya chini ya orodha, na hivyo kulazimisha jumla ya kila kisanduku kushushwa chini kila wakati data mpya inapoongezwa.
Ili kufuata mafunzo haya, fungua laha tupu ya Excel na uweke sampuli ya data. Laha yako ya kazi haihitaji kuumbizwa kama mfano, lakini hakikisha kuwa umeingiza data katika visanduku sawa.
Ikiwa tu chaguo la kukokotoa la SUM ndilo linalotumiwa kujumlisha data, safu mbalimbali za visanduku vinavyotumiwa kama hoja ya kukokotoa zitahitaji kurekebishwa kila wakati data mpya inapoongezwa.
Kwa kutumia chaguo za kukokotoa za SUM na OFFSET pamoja, masafa ambayo yanajumlishwa yanabadilika na kubadilika ili kushughulikia visanduku vipya vya data. Kuongezwa kwa visanduku vipya vya data hakuleti matatizo kwa sababu masafa yanaendelea kubadilika kila seli mpya inapoongezwa.
Sintaksia na Hoja
Katika fomula hii, chaguo za kukokotoa za SUM hutumika kujumlisha masafa ya data iliyotolewa kama hoja. Mahali pa kuanzia kwa fungu hili la visanduku ni tuli na hutambuliwa kama marejeleo ya seli kwa nambari ya kwanza ya kujumlishwa na fomula.
Kitendakazi cha OFFSET kimewekwa ndani ya chaguo za kukokotoa za SUM na huunda ncha inayobadilika ya anuwai ya data iliyojumlishwa na fomula. Hili linakamilishwa kwa kuweka ncha ya masafa hadi kisanduku kimoja juu ya eneo la fomula.
Sintaksia ya fomula ni:
=SUM(Msururu Anza:OFFSET(Rejea, Safumlalo, Rangi)
Hoja ni:
- Msururu Anza: Mahali pa kuanzia kwa safu ya visanduku ambavyo vitajumlishwa na chaguo za kukokotoa za SUM. Katika mfano huu, mahali pa kuanzia ni seli B2.
- Rejea: Rejeleo la seli linalohitajika linalotumika kukokotoa ncha ya masafa. Katika mfano huo, hoja ya Marejeleo ni rejeleo la seli kwa fomula kwa sababu safu huishia kisanduku kimoja juu ya fomula.
- Safu mlalo: Idadi ya safu mlalo juu au chini ya hoja ya Marejeleo inayotumika katika kukokotoa usawa inahitajika. Thamani hii inaweza kuwa chanya, hasi, au kuweka sifuri. Ikiwa eneo la kukabiliana liko juu ya hoja ya Marejeleo, thamani ni hasi. Ikiwa urekebishaji uko hapa chini, hoja ya Safumlalo ni chanya. Ikiwa kukabiliana iko katika safu sawa, hoja ni sifuri. Katika mfano huu, usawazishaji huanza safu mlalo moja juu ya hoja ya Marejeleo, kwa hivyo thamani ya hoja hiyo ni hasi (-1).
- Nambari: Idadi ya safu wima upande wa kushoto au kulia wa hoja ya Marejeleo inayotumika kukokotoa ulinganifu. Thamani hii inaweza kuwa chanya, hasi, au kuweka sifuri. Ikiwa eneo la kukabiliana liko upande wa kushoto wa hoja ya Marejeleo, thamani hii ni hasi. Ikiwa suluhu ni sawa, hoja ya Cols ni chanya. Katika mfano huu, data inayojumlishwa iko katika safu wima sawa na fomula, kwa hivyo thamani ya hoja hii ni sifuri.
Tumia Mfumo wa SUM OFFSET kwa Jumla ya Data ya Mauzo
Mfano huu unatumia fomula ya SUM OFFSET kurudisha jumla ya takwimu za mauzo za kila siku zilizoorodheshwa katika safu wima B ya lahakazi. Hapo awali, fomula iliingizwa kwenye kisanduku B6 na kujumlisha data ya mauzo kwa siku nne.
Hatua inayofuata ni kusogeza fomula ya SUM OFFSET chini ya safu mlalo moja ili kutoa nafasi kwa jumla ya mauzo ya siku ya tano. Hili linakamilishwa kwa kuingiza safu mlalo mpya ya 6, ambayo husogeza fomula hadi safu mlalo ya 7.
Kutokana na hatua hiyo, Excel inasasisha kiotomatiki hoja ya Marejeleo hadi kisanduku B7 na kuongeza kisanduku B6 kwenye safu iliyojumlishwa na fomula.
- Chagua kisanduku B6, ambapo ni mahali ambapo matokeo ya fomula yataonyeshwa mwanzoni.
-
Chagua kichupo cha Mfumo cha utepe.
Image -
Chagua Hesabu na Trig.
Image -
Chagua SUM.
Image - Katika Hoja za Kazi kisanduku cha mazungumzo, weka kishale kwenye kisanduku cha maandishi cha Nambari1..
-
Katika lahakazi, chagua kisanduku B2 ili kuingiza rejeleo hili la kisanduku kwenye kisanduku cha mazungumzo. Mahali hapa ndio mwisho tuli wa fomula.
Image - Kwenye Hoja za Kazi kisanduku cha mazungumzo, weka kishale kwenye kisanduku cha maandishi cha Namba2..
-
Ingiza OFFSET(B6, -1, 0). Chaguo hili la kukokotoa la OFFSET huunda ncha inayobadilika ya fomula.
Image -
Chagua Sawa ili kukamilisha kitendakazi na ufunge kisanduku cha mazungumzo. Jumla inaonekana katika kisanduku B6.
Image
Ongeza Data ya Mauzo ya Siku Inayofuata
Ili kuongeza data ya mauzo ya siku inayofuata:
- Bofya kulia kichwa cha safu mlalo kwa safu mlalo ya 6.
-
Chagua Ingiza ili kuingiza safu mlalo mpya kwenye lahakazi. Fomula ya SUM OFFSET inasogezwa chini kwenye safu mlalo moja hadi kisanduku B7 na safu mlalo ya 6 sasa ni tupu.
Image - Chagua kisanduku A6 na uweke nambari 5 ili kuashiria kuwa jumla ya mauzo ya siku ya tano inaingizwa.
-
Chagua kisanduku B6, weka $1458.25, kisha ubofye Enter..
Image - Sasisho za Cell B7 hadi kufikia jumla mpya ya $7137.40.
Unapochagua kisanduku B7, fomula iliyosasishwa inaonekana kwenye upau wa fomula.
=SUM(B2:OFFSET(B7, -1, 0))
Kitendakazi cha OFFSET kina hoja mbili za hiari: Height na Width, ambazo hazikutumika katika mfano huu. Hoja hizi huambia kitendakazi cha OFFSET umbo la towe kulingana na idadi ya safu mlalo na safu wima.
Kwa kuacha hoja hizi, chaguo la kukokotoa hutumia urefu na upana wa hoja ya Marejeleo badala yake, ambayo, katika mfano huu ni safu mlalo moja juu na upana wa safu moja.
