Excel SUM ndi OFFSET Makhalidwe

Gwiritsani ntchito SUM ndi OFFSET kuti mupeze ziwerengero zamtundu wa data

Ngati pulogalamu yanu ya Excel ikuphatikizapo mawerengero okhudzana ndi kusintha kwa maselo, kugwiritsa ntchito SUM ndi OFFSET ntchito pamodzi mu SUM OFFSET fomu ikuthandizira ntchito yosunga chiwerengerocho.

Pangani Mphamvu Yopambana ndi Ntchito SUM ndi OFFSET

Ngati mumagwiritsa ntchito ziwerengero kwa nthawi yomwe imasintha - monga kuwonetsera malonda kwa mwezi - OFFSET ntchito ikukuthandizani kukhazikitsa kusiyana komwe kumasintha pamene ziwerengero za malonda a tsiku ndi tsiku zikuwonjezeredwa.

Pokhapokha, ntchito ya SUM imatha kukhala ndi maselo atsopano a deta kuti ayambe kufotokozedwa.

Chinthu chimodzi chokha chimapezeka pamene deta ikulowetsedwa mu selo kumene ntchitoyi ili pano.

Mu chithunzi chomwe chikugwirizana ndi nkhaniyi, zida zatsopano zogulitsa malonda tsiku ndi tsiku zikuwonjezeredwa pansi pa mndandanda, zomwe zimachititsa kuti chiwerengerocho chizipitirirabe kusuntha selo limodzi nthawi iliyonse deta yatsopano ikuwonjezeredwa.

Ngati ntchito ya SUM idagwiritsidwa ntchito yokha kuti iwononge deta, pangakhale koyenera kusintha maselo osiyanasiyana omwe amagwiritsidwa ntchito ngati ndondomeko ya ntchito nthawi iliyonse yowonjezera deta.

Pogwiritsira ntchito SUM ndi OFFSET ntchito pamodzi, komabe, kusiyana komwe kwapezeka kumakhala kolimba. M'mawu ena, amasintha kuti agwirizane ndi maselo atsopano a deta. Kuwonjezera kwa maselo atsopano a deta sikumayambitsa mavuto chifukwa mtunduwu ukupitirizabe kusintha monga selo yatsopano iliyonse yowonjezera.

Syntax ndi Arguments

Tchulani chithunzi chomwe chikutsatira ndondomekoyi ndikutsatirana ndi phunziroli.

Mu chigamulochi, ntchito ya SUM imagwiritsidwa ntchito kuti muwononge ma deta omwe amaperekedwa monga mtsutso wake. Poyambira pamtundu uwu ndi wowongolera ndipo umadziwika ngati selo loyang'ana selo yoyamba kuti likhale ndi chiganizocho.

Ntchito ya OFFSET imakhala mkati mwa ntchito SUM ndipo imagwiritsidwa ntchito popanga mapeto amphamvu ku deta yochuluka yomwe yalembedwa. Izi zikukwaniritsidwa mwa kukhazikitsa mapeto a kutalika kwa selo limodzi pamwamba pa malo a fomu.

Syntax ya fomu:

= SUM (Yambani Yambani: OFFSET (Fufuzani, Mizere, Cols))

Njira Yoyambira - (yofunika) chiyambi cha maselo ambiri omwe adzakhale ndi ntchito SUM. Mu chithunzi chithunzi, izi ndi selo B2.

Tsatanetsatane - (zofunikira) selo lotanthauzira ntchito yogwiritsira ntchito mapeto a mapepala ali ndi mizere ndi mizere yambiri. Mu fano lachitsanzo, ndemanga yowonjezeretsa ndilo selo la selo lokha chifukwa nthawi zonse timafuna kutseka selo limodzi pamwamba pa fomu.

Mizere - (yofunika) chiwerengero cha mizere pamwamba kapena pansi pa ndondomeko ya Mafotokozedwe yogwiritsidwa ntchito powerengera zolakwikazo. Mtengo umenewu ukhoza kukhala wabwino, wosasamala, kapena kuyika zero.

Ngati malo okhumudwawa ali pamwamba pa mtsutso wotsutsa, mtengo uwu ndi woipa. Ngati ili pansipa, mtsutso wa Mzere ndi wabwino. Ngati chotsutsana chiri mumzere womwewo, mfundo iyi ndi zero. Mu chitsanzo ichi, chotsutsanacho chimayambira mzere umodzi pamwamba pa mtsutso Wotchulidwa, kotero kufunika kwa mfundo iyi ndi imodzi (-1).

Makoloni - (oyenerera) chiwerengero cha zipilala kumanzere kapena kumanja kwa ndondomeko ya Reference yomwe ikugwiritsidwa ntchito powerengera zolakwikazo. Mtengo umenewu ukhoza kukhala wabwino, wosasamala, kapena kuyika zero

Ngati malo ochotsedwawo ali kumanzere kwa mtsutso wa Reference , mtengo uwu ndi woipa. Ngati kumanja, mtsutso wa Cols uli wolimbikitsa. Mu chitsanzo ichi, chiwerengerocho chiwerengedwera chiri mu gawo lomwelo monga chikhomo kotero kuti phindu la ndemanga imeneyi ndi zero.

Pogwiritsa ntchito SUM OFFSET Mpangidwe ku Total Sales Data

Chitsanzo ichi chimagwiritsa ntchito fomu ya SUM OFFSET kuti ibwezere chiwerengero cha ziwerengero zamalonda zamalonda zomwe zili m'ndandanda B wa tsamba.

Poyamba, ndondomekoyi inalowetsedwa mu selo B6 ndipo inafika pa chiwerengero cha malonda kwa masiku anayi.

Chinthu chotsatira ndichotsitsa ndondomeko ya SUM OFFSET pamzere kuti mupeze malo a malonda a tsiku lachisanu.

Izi zikukwaniritsidwa mwa kuyika mzere watsopano 6, womwe umapangitsa kuti chiwerengerocho chikhale chotsatira 7.

Chifukwa cha kusunthira, Excel imangosintha ndondomeko yowonjezera ku selo B7 ndipo imapangitsa selo B6 kuti liwonetsedwe pamtunduwu.

Kulowa mu SUM OFFSET Fomu

1. Dinani pa selo B6, komwe kuli komwe zotsatira za fomuyi zidzasonyezedwe poyamba.
2. Dinani pa Fomu tab ya menyu yowonjezera.
3. Sankhani Math & Trig kuchokera paboni kuti mutsegule ntchito yolemba pansi.
4. Dinani pa SUM mu mndandanda kuti mubweretse bokosi lazokambirana .
5. Mu bokosi la bokosi, dinani pa Number1 mzere.
6. Dinani pa selo B2 kuti mulowetse selo ili mu bokosi la bokosi. Malo awa ndi mapeto otsimikizika a mawonekedwe;
7. Mu bokosi la bokosi, dinani pa Nambala Number2 .
8. Lowetsani ntchito zotsatirazi: OFFSET (B6, -1,0) kuti mupangire mapeto amphamvu omwe angapangidwe.
9. Dinani OK kuti mutsirize ntchitoyi ndi kutseka bokosi la bokosi.

Chiwerengero cha $ 5679.15 imapezeka mu selo B7.

Mukasinthana pa selo B3, ntchito yonse = SUM (B2: OFFSET (B6, -1,0) imapezeka mu barra ya fomu pamwamba pa tsamba.

Kuwonjezera Zolemba Zotsatira Zotsatira

Kuwonjezera deta ya tsiku lotsatira:

1. Dinani pamanja pamutu wa mzere 6 kuti mutsegule mndandanda.
2. Mu menyu, dinani pa Insert kuti muike mzere watsopano mu tsamba.
3. Zotsatira zake, SUM OFFSET ndondomeko imapita pansi ku cell B7 ndi mzere 6 tsopano mulibe.
4. Dinani pa selo A6 .
5. Lowani nambala 5 kuti asonyeze kuti malonda onse a tsiku lachisanu akulowa.
6. Dinani pa selo B6.
7. Lembani chiwerengero cha $ 1458.25 ndipo pindani makiyi a Enter mu makina.

Kagulu B7 amasintha kwa ndalama zatsopano za $ 7137.40.

Mukasindikiza pa selo B7, ndondomeko yosinthidwa = SUM (B2: OFFSET (B7, -1,0)) ikupezeka pa bar.

Zindikirani : Ntchito ya OFFSET ili ndi zifukwa ziwiri zomwe mungasankhe: Kutalika ndi M'lifupi, zomwe sizinachitike m'chitsanzo ichi.

Zokambiranazi zingagwiritsidwe ntchito pofotokozera OFFSET ntchito mawonekedwe a zotsatira kuchokera pamakhala mizere yambiri komanso mizere yambirimbiri.

Potsutsa zifukwa izi, ntchitoyi, mwachisawawa, imagwiritsa ntchito kutalika ndi kupingasa kwa mtsutso Wolembera mmalo mwake, omwe, mu chitsanzo ichi ndi mzere umodzi wokwera ndi khola limodzi lonse.