Shandisa SUM uye OFFSET kuti uwane zviyero zvezvinhu zvakasimba zve data
Kana yako Excel worksheet inosanganisira kuverenga zvichienderana nekuchinja kwemasero, kushandisa SUM uye OFFSET zvinoshanda pamwe chete muSUM OFFSET fomu inotsanangurira basa rekuchengeta maverengi kusvikira zvino.
Ita Dynamic Range neSUM uye OFFSET Mabasa
Kana ukashandisa kuverenga kwenguva yakati inoramba ichichinja - yakadai sekuzara kwekutengesa kwemwedzi - OFFSET basa inokubvumira kuisa mararamiro anosimba anoramba achichinja sezvo mazuva ose ekutengesa kuwedzerwa.
Nayo pachayo, basa reSUM rinogona kugara rakagadzirirwa masero matsva epa data ari kuiswa muhuwandu hunopfupiswa.
Chimwe chete chinowanikwa apo data inowanikwa muchitokisi umo basa iri ikozvino riripo.
Mumuenzaniso mufananidzo unofambidzana nenyaya ino, mitsva yekutengesa kwezuva rimwe nerimwe yakawedzerwa kune zvakaderera pasi, iyo inokonzera kukwana kuenderera mberi kushandura rimwe sero pose nguva iyo data itsva ichiwedzerwa.
Kana basa reSUM rikashandiswa pachega kuti rive rakazara data, zvingada kuchinja mararamiro emasero anoshandiswa semashoko ekushandura nguva imwe neimwe nguva dhepfenyuro itsva yakawedzerwa.
Nokushandisa SUM uye OFFSET mabasa pamwe chete, zvisinei, rutivi rwakazara runoita simba. Mune mamwe mazwi, inoshandura kugadzirisa masero matsva e data. Kuwedzerwa kwemasero matsva e data hakubvumiri matambudziko nokuti urongwa hunoramba huchigadzirisa sezvo rimwe sero idzva rawedzerwa.
Syntax uye Arguments
Tarisa mufananidzo unofamba nenyaya ino kuti uite pamwe neyikudzidzisa.
Muchirevo ichi, basa reSUM rinoshandiswa kukwana huwandu hwemashoko anopiwa senyaya yake. Nzvimbo yekutanga yezera iri yakanyanyisa uye inoratidzika senhare yekutsanangurira nhamba yekutanga yekuzadzikiswa neyo fomu.
IO OFFSET basa iri rakapoteredzwa mukati memabasa eSUM uye inoshandiswa kugadzira purogiramu yekugadzirisa kune yakadzama ye data yakazara nefomu. Izvi zvinogadziriswa nekuisa kuguma kwehurefu kune rimwe sero kumusoro pamusoro penzvimbo yemaitiro.
Syntax ye formula:
= SUM (Range Tanga: OFFSET (Tora, Ropa, Cols))Range Kutanga - (inoda) kutanga kweruzhinji rwemasero achazongowanikwa nebasa reSUM. Mumufananidzo mufananidzo, iyi iseri B2.
Referensi - (inoda) tsanangudzo yesero inoshandiswa pakuverenga mhedziso yemagumo ine mitsara mizhinji nemirasi kure. Mumuenzaniso mufananidzo, iyo Reference Reference ndiyo sero rinoshandiswa pamutsetse iwoyo pachedu sezvo isu nguva dzose tichida kuburitsa imwe sero iri pamusoro pemarara.
Mitsara - (inodikanwa) nhamba yemigwagwa yepamusoro kana pasi peNhaurirano yekufambisa inoshandiswa mukuverenga kuregererwa. Ichi chinokosha chinogona kuva chakanaka, chakaipa, kana chinotarirwa ku zero.
Kana nzvimbo yakakanganiswa iri pamusoro pemafungiro ekutaura, kukosha uku hakuna kunaka. Kana iri pasi, Mhirizhonga yeropa inofadza. Kana kukanganisa kuripo mumuganhu mumwechete, iyi nharo ndeye zero. Mumuenzaniso uyu, kukanganisa kunotanga mutsara mumwe pamusoro peNhaurirano yenyaya, saka kukosha kwenyaya iyi ndisina kunaka (-1).
Cols - (inoda) nhamba yemakoroni kuruboshwe kana kurudyi kweArudzo yekufambisa inoshandiswa mukuverenga kuregererwa. Ichi chinokosha chinogona kuva chakanaka, chakaipa, kana kuti chiiswa ku zero
Kana kukanganisa kwaro kuri kuruboshwe rweMafungiro ekuTaura, kukosha uku hakuna kunaka. Kana kune kurudyi, nharo yeCols yakanaka. Mumuenzaniso uyu, deta iri kuwanikwa iri muhomwe imwechete seyemurairo kuitira kuti kukosha kwepfungwa iyi kuve zero.
Kushandisa SUM OFFSET Murairo kune Total Sales Data
Uyu muenzaniso unoshandisa SUM OFFSET fomu yekudzorera chiyero chemazuva ose ekutengesa matanho akanyorwa muchitsetse B chebasa rekushanda.
Pakutanga, maitiro acho akaiswa muchitokisi B6 uye akawana dhip data yemazuva mana.
Nhanho inotevera ndeyokutora SUM OFFSET rondedzero pasi mumutsara kuitira nzvimbo yezuva rechishanu rekutengesa.
Izvi zvinozadzikiswa nekuisa mutsara mutsva 6, unoita kuti chirevo chese chigone kurongedza 7.
Somugumisiro wekufamba, Excel inongoruramisa mhirizhonga yeReferensi kumusero B7 uye inowedzera cell B6 kusvika pane zvakapfupikiswa nesarudzo.
Kupinda muSUM OFFSET Formula
- Dzvanya pachitokisi B6, iyo ndiyo nzvimbo iyo migumisiro yefomu yacho inotanga kuratidzwa.
- Dzvanya paMafomu yebhu yebhoni yebhoni .
- Sarudza Math & Trig kubva kubhoni kuti uzarure urongwa hwekuderera pasi.
- Dzvanya paSUM murugwaro kuti ubudise bhokisi rebasa rebasa.
- Mubhokisi rebhokisi, tora paNhamba1 .
- Dzvanyira pane sero B2 kuti ugoona tsanangudzo yesero mubhokisi rebhokisi. Iyi nzvimbo inopedzisira kugadziriswa kwemuumbi;
- Mubhokisi rebhokisi, tora paNhamba2 line.
- Pinda zvinotevera OFFSET basa: OFFSET (B6, -1,0) kuti uite purogiramu yekugadzirisa yefomu.
- Dzvanya OK kuti upedze basa uye uvhare bhokisi rebhokisi.
Yose $ 5679.15 rinowanikwa muchitokisi B7.
Paunenge uchinge uri pa cell B3, basa rakakwana = SUM (B2: OFFSET (B6, -1,0)) inowanikwa mubhairi yepamusoro pane pepa pepaji.
Kuwedzera Zuva Rokutsvaga Dhavhidha
Kuwedzera dheta rekutengesa zuva rakatevera:
- Kungofa-kanda pamutsara we musoro wezera rekutanga 6 kuti uzarure mamiriro ekutenderera.
- Mune menyu, tora paIndai kuti uise mutsva mutsva muhwhepa.
- Somugumisiro, iyo SUM OFFSET inoshandiswa inotsika pasi kune cell B7 uye mutsara 6 ikozvino isina chinhu.
- Dzvanya pane sero A6 .
- Pinda nhamba 5 kuratidza kuti kutengesa kwezuva rechishanu kuri kuiswa.
- Dzvanya pane cell B6.
- Tora iyo nhamba $ 1458.25 uye pinda paIndineni pakibhodi .
Siri B7 inoshandura kune imwe itsva ye $ 7137.40.
Paunenge uchinyora pa cell B7, iyo yakarongedzerwa fomu = SUM (B2: OFFSET (B7, -1,0)) inowanikwa muburani rekushandisa.
Cherechedza : The OFFSET basa rine mazano maviri okuzvisarudzira: Urefu uye Upamhi, izvo zvisingabvumirwi mumuenzaniso uyu.
Izvi zvinogona kushandiswa kutaurira OFFSET basa chimiro chekubudiswa mukati maro kuva nemigwagwa yakawanda uye yakawanda yakawanda yakawanda.
Nokusiya masangano aya, basa racho, nekusingaperi, rinoshandisa kureba uye upamhi hweMafungiro ekutaura panzvimbo iyo, iyo, mumuenzaniso uyu mune imwe mutsara wepamusoro uye imwe koruva yakazara.