Bain úsáid as SUM agus OFFSET chun iomlán a fháil le haghaidh raon dinimiciúil sonraí
Má chuimsíonn do bhileog oibre Excel ríomhairí bunaithe ar raon athraitheach cealla, déanann na feidhmeanna SUM agus OFFSET le chéile i bhfoirmle SUM OFFSET an tasc a choinneáil chun na ríomhanna a choinneáil cothrom le dáta.
Cruthaigh Raon Dinimiciúil Leis na Feidhmeanna SUM agus OFFSET
Má úsáideann tú ríomhanna ar feadh tréimhse ama a athraíonn go leanúnach - amhail díolacháin iomlána don mhí - is féidir le feidhm OFFSET raon dinimiciúil a choinneáil a choinníonn athrú mar a chuirtear figiúirí díolacháin gach lae leis.
De réir féin, is féidir leis an fheidhm SUM freastal ar chealla nua sonraí a chur isteach sa réimse atá á achoimriú.
Tarlaíonn eisceacht amháin nuair a chuirtear na sonraí isteach sa chill ina bhfuil an fheidhm suite faoi láthair.
Sa íomhá sampla a ghabhann leis an alt seo, cuirtear na figiúirí díolacháin nua le haghaidh gach lá le bun an liosta, rud a chuireann ar chumas an t-iomlán cill amháin a aistriú go leanúnach gach uair a chuirtear na sonraí nua leis.
Má úsáidtear an fheidhm SUM ar a shon féin chun na sonraí a chomhlánú, bheadh sé riachtanach an raon cealla a úsáideadh mar argóint an fheidhm a mhodhnú gach uair a cuireadh sonraí nua isteach.
Trí úsáid a bhaint as feidhmeanna SUM agus OFFSET le chéile, áfach, déantar an raon atá iomlán le bheith dinimiciúil. I bhfocail eile, athraíonn sé chun freastal ar chealla nua sonraí. Ní chuireann fadhbanna le cealla nua sonraí mar gheall ar leanann an raon ag athrú mar a chuirtear gach cill nua leis.
Comhréir agus Argóintí
Déan tagairt don íomhá a ghabhann leis an alt seo a leanúint chomh maith leis an teagasc seo.
Sa fhoirmle seo, úsáidtear feidhm SUM chun raon na sonraí a sholáthraítear mar a argóint a chomhlánú. Tá an pointe tosaigh don raon seo statach agus aithnítear go bhfuil an fhoirmle ag tagairt an chill don chéad uimhir.
Tá an fheidhm OFFSET neadraithe taobh istigh den fheidhm SUM agus úsáidtear é chun deireadhphointe dinimiciúil a chruthú don raon sonraí atá iomlán ag an bhfoirmle. Cuirtear é seo i gcrích trí dheireadhphointe an raoin a shocrú le ceall amháin os cionn suíomh na foirmle.
Comhréir an fhoirmle:
= SUM (Raon Tosaigh: OFFSET (Tagartha, Rásanna, Cols))Raon Tosaigh - (ag teastáil) an pointe tosaigh don raon cealla a bheidh ag iomlán na feidhme SUM. Sa íomhá sampla, is é seo an ceall B2.
Tagairt - (ag teastáil) an tagairt cille a úsáidtear chun deireadhphointe an raoin a ríomh lonnaithe go leor sraitheanna agus colúin ar shiúl. Sa íomhá sampla, is é an argóint Tagartha an tagairt cille don fhoirmle féin ós rud é gur mian linn an raon i gcónaí chun deireadh a chur le ceall amháin os cionn na foirmle.
Rows - (riachtanach) líon na sraitheanna thuas nó thíos an argóint Tagartha a úsáidtear chun an fhritháireamh a ríomh. Is féidir an luach seo a bheith dearfach, diúltach, nó socraithe go nialas.
Má tá suíomh an fhritháireamh os cionn an argóint Tagartha , tá an luach seo diúltach. Má tá sé thíos, tá an argóint Rows dearfach. Má tá an fritháireamh suite sa tsraith chéanna, níl an argóint seo nialas. Sa sampla seo, tosaíonn an fhritháireamh sraith amháin os cionn an argóint Tagartha , agus mar sin tá an luach don argóint seo diúltach (-1).
Cols - (riachtanach) líon na gcolún ar chlé nó ar dheis an argóint Tagartha a úsáidtear chun an fhritháireamh a ríomh. Is féidir an luach seo a bheith dearfach, diúltach, nó socraithe go nialas
Má tá suíomh an fhritháireamh ar thaobh na láimhe clé den argóint Tagartha , tá an luach seo diúltach. Más rud é ar dheis, tá an argóint Cols dearfach. Sa sampla seo, tá na sonraí ina iomláine sa cholún céanna leis an bhfoirmle mar sin is é an luach don argóint seo nialas.
Ag baint úsáide as Foirmle SUM OFFSET le Sonraí Díolacháin Iomlán
Úsáideann an sampla seo foirmle SUM OFFSET chun an t-iomlán a thabhairt ar ais do na figiúirí díolacháin laethúla atá liostaithe i gcolún B den bhileog oibre.
Ar dtús, cuireadh an fhoirmle isteach i gcill B6 agus iomlán na sonraí díolacháin ar feadh ceithre lá.
Is é an chéad chéim eile ná an fhoirmle SUM OFFSET a aistriú as a chéile chun seomra a dhéanamh le haghaidh iomlán díolacháin an chúigiú lá.
Déantar é seo trí shraith nua 6 a chur isteach , rud a ghluaiseann an fhoirmle síos go dtí a chéile 7.
Mar thoradh ar an aistriú, nuashonraíonn Excel an argóint Tagartha go huathoibríoch i gcill B7 agus cuireann sé ceall B6 leis an raon a achoimrítear leis an bhfoirmle.
Ag cur isteach ar an bhFoirmle SUM OFFSET
- Cliceáil ar chill B6, arb é an áit ina dtarlaíonn torthaí na foirmle ar dtús.
- Cliceáil ar an gclár Formulas an roghchlár ribáin .
- Roghnaigh Math & Trig as an ribín chun an liosta díolacháin a oscailt.
- Cliceáil ar SUM sa liosta chun bosca dialóg an fheidhm a thabhairt suas.
- Sa bhosca dialóg, cliceáil ar an líne Number1 .
- Cliceáil ar chill B2 chun an tagairt cille seo a chur isteach sa bhosca dialóg. Is é an suíomh seo an deireadhphointe statach don fhoirmle;
- Sa bhosca dialóg, cliceáil ar an líne Number2 .
- Cuir isteach an fheidhm OFFSET seo a leanas: OFFSET (B6, -1,0) chun an pointe críochnaitheach dinimiciúil don fhoirmle a chruthú.
- Cliceáil OK chun an fheidhm a chomhlánú agus an bosca dialóg a dhúnadh.
An $ 5679.15 iomlán le feiceáil i gcill B7.
Nuair a chliceálann tú ar chill B3, is é an fheidhm iomlán = SUM (B2: OFFSET (B6, -1,0)) sa bharra foirmle os cionn an bhileog oibre.
Ag Cur Sonraí Díolacháin an Chéad Laethanta
Chun sonraí díolachán an lae inniu a chur leis:
- Ceart cliceáil ar an gceannteideal as an tsraith i ndiaidh a chéile chun an roghchlár comhthéacs a oscailt.
- Sa roghchlár, cliceáil ar Ionsáigh sraith nua a chur isteach sa bhileog oibre.
- Mar thoradh air sin, bogann an fhoirmle SUM OFFSET síos go dtí cill B7 agus tá sraith 6 folamh anois.
- Cliceáil ar chill A6 .
- Cuir isteach uimhir 5 le fios go bhfuil an t-iomlán díolacháin don chúigiú lá á gcur isteach.
- Cliceáil ar chill B6.
- Cineál an uimhir $ 1458.25 agus brúigh an eochair Enter isteach ar an méarchlár.
Nuashonruithe Cell B7 don iomlán nua de $ 7137.40.
Nuair a chliceálann tú ar chill B7, tá an fhoirmle nuashonraithe = SUM (B2: OFFSET (B7, -1,0)) le feiceáil sa bharra foirmle.
Nóta : Tá dhá argóintí roghnacha ag feidhm OFFSET: Airde agus Leithead, a fágtha ar lár sa sampla seo.
Is féidir na hargóintí seo a úsáid chun feidhm an OFFSET a insint cruth an aschuir i dtéarmaí go bhfuil an oiread sin sraitheanna ard agus an oiread sin de na colúin.
Trí na hagóintí seo a fhágáil, cuireann an fheidhm, de réir réamhshocraithe, airde agus leithead an argóint Tagartha ina ionad, a bhfuil, sa sampla seo, sraith amháin ard agus colún amháin leathan.