Notaðu SUM og OFFSET til að finna samtöl fyrir dynamic svið gagna
Ef Excel verkstæði þín inniheldur útreikninga byggt á breyttum fjölda frumna, auðveldar notkun SUM og OFFSET virka saman í SUM OFFSET formúlunni að halda útreikningum uppfærð.
Búðu til Dynamic Range með SUM og OFFSET Aðgerðir
Ef þú notar útreikninga um tíma sem breytist stöðugt - eins og heildarsala fyrir mánuðinn - gerir OFFSET-stillingin þér kleift að setja upp víðtæka svið sem breytist eftir því sem sölutölur hvers dags eru bætt við.
Af sjálfu sér getur SUM-aðgerðin venjulega komið fyrir nýjum gögnum af gögnum sem sett eru inn í sviðið sem er stutt.
Ein undantekning kemur fram þegar gögnin eru sett í klefann þar sem aðgerðin er staðsett.
Í dæmi myndinni sem fylgir þessari grein eru ný sölutölur fyrir hvern dag bætt við neðst á listanum, sem knýja heildina á að stöðugt skipta niður einum reit í hvert skipti sem ný gögn eru bætt við.
Ef SUM-aðgerðin var notuð sjálf til að heildargögnin yrði nauðsynlegt að breyta fjölda frumna sem notuð voru sem röksemdafærslan í hvert skipti sem ný gögn voru bætt við.
Með því að nota SUM og OFFSET aðgerðir saman verður þó sviðið sem er samtengt. Með öðrum orðum breytist það til að mæta nýjum gögnum af gögnum. Að bæta við nýjum gögnum af gögnum veldur ekki vandamálum vegna þess að sviðið heldur áfram að laga sig þar sem hver nýr flokkur er bætt við.
Setningafræði og rök
Sjá myndina sem fylgir þessari grein til að fylgja með þessari kennslu.
Í þessari formúlu er SUM-aðgerðin notuð til að reikna út fjölda gagna sem eru til staðar. Upphafspunktur fyrir þetta svið er truflað og er auðkennt sem klefi tilvísun í fyrsta númerið sem samanstendur af formúlunni.
OFFSET aðgerðin er hreiður innan SUM-aðgerðarinnar og er notaður til að búa til dynamic endapunkt á bilinu gagna sem formúlan samanstendur af. Þetta er gert með því að stilla endapunkt sviðsins í eina reit fyrir ofan staðsetningu formúlunnar.
Setningafræði setningarinnar :
= SUM (Range Start: OFFSET (Tilvísun, Row, Cols))Range Start - (krafist) upphafspunkturinn fyrir fjölda frumna sem verða til í SUM-fallinu. Í dæmi myndinni er þetta klefi B2.
Tilvísun - (krafist) klefi tilvísunin sem notuð er til að reikna endapunkt sviðsins er staðsettur í margar línur og dálka í burtu. Í dæmi myndinni er tilvísunarargreinin klefivísirinn fyrir formúluna sjálft þar sem við viljum alltaf að bilið sé lokið til að binda enda á eina reit fyrir ofan formúluna.
Röð - (krafist) fjöldi raða fyrir ofan eða neðan við viðmiðunarargreinina sem notuð er við útreikning á móti. Þetta gildi getur verið jákvætt, neikvætt eða stillt á núll.
Ef staðsetning staðsetningarinnar er fyrir ofan viðmiðunarargreinina er þetta gildi neikvætt. Ef það er hér að neðan, er rörið rökin jákvæð. Ef móti er staðsett í sömu röð, er þetta rök núll. Í þessu dæmi byrjar á móti einum línu fyrir ofan viðmiðunarargreinina , þannig að gildi þessarar röks er neikvætt eitt (-1).
Cols - (krafist) fjölda dálka til vinstri eða hægri við viðmiðunarargreinina sem notuð er við útreikning á móti. Þetta gildi getur verið jákvætt, neikvætt eða stillt á núll
Ef staðsetningin á móti er vinstra megin við viðmiðunarargreinina , er þetta gildi neikvætt. Ef til hægri er rök Cols jákvætt. Í þessu dæmi eru gögnin sem samanstanda í sömu dálki og formúlan þannig að gildi þessarar röks er núll.
Notaðu SUM OFFSET formúluna í heildarsölu
Þetta dæmi notar SUM OFFSET formúlu til að skila heildarupphæðinni fyrir dagleg sölutölur sem eru taldar upp í dálki B í verkstæði.
Upphaflega var formúlan færð í reit B6 og nam sölugögnin í fjóra daga.
Næsta skref er að færa SUM OFFSET formúluna niður í röð til að búa til söludegi fimmta dags.
Þetta er gert með því að setja nýja línu 6, sem færir formúluna niður í röð 7.
Afleiðingin af því að færa, Excel uppfærir sjálfkrafa viðmiðunarargreinina í reit B7 og bætir frumu B6 við það bil sem summað er með formúlunni.
Sláðu inn SUM OFFSET Formula
- Smelltu á klefi B6, sem er staðsetningin þar sem niðurstöður formúlunnar verða upphaflega birtar.
- Smelltu á Formulas flipann á borði valmyndinni.
- Veldu Stærðfræði og Trig úr borði til að opna fallgluggann .
- Smelltu á SUM á listanum til að koma upp valmyndaraðgerðina .
- Í glugganum, smelltu á Number1 línu.
- Smelltu á klefi B2 til að slá inn þessa reit tilvísun í valmyndina. Þessi staðsetning er truflanir endapunktur fyrir formúluna;
- Í valmyndinni, smelltu á Number2 línuna.
- Sláðu inn eftirfarandi OFFSET virka: OFFSET (B6, -1,0) til að mynda dynamic endapunktinn fyrir formúluna.
- Smelltu á Í lagi til að ljúka aðgerðinni og lokaðu valmyndinni.
Alls $ 5679.15 birtist í reit B7.
Þegar þú smellir á klefi B3 birtist alla aðgerðin = SUM (B2: OFFSET (B6, -1,0)) í formúlunni fyrir ofan vinnublaðið.
Bætir við söludagi næsta dags
Til að bæta við söluupplýsingum næsta dags:
- Hægrismelltu á röðina fyrir röð 6 til að opna samhengisvalmyndina.
- Í valmyndinni, smelltu á Insert til að setja inn nýja röð í verkstæði.
- Þess vegna fer SUM OFFSET formúlan niður í B7 og Röð 6 er nú tóm.
- Smelltu á klefi A6 .
- Sláðu inn númer 5 til að gefa til kynna að heildarupphæðin fyrir fimmta daginn sé slegin inn.
- Smelltu á klefi B6.
- Sláðu inn númerið $ 1458,25 og ýttu á Enter takkann á lyklaborðinu.
Cell B7 uppfærir nýju samtals $ 7137,40.
Þegar þú smellir á klefi B7 birtist uppfærð formúla = SUM (B2: OFFSET (B7, -1,0)) í formúlunni.
Athugið : OFFSET virka hefur tvö valfrjálst rök: Hæð og breidd, sem voru sleppt í þessu dæmi.
Þessar röksemdir geta verið notaðir til að segja OFFSET virka lögun framleiðslunnar með tilliti til þess að vera svo margar raðir hár og svo mörg dálkar breiður.
Með því að sleppa þessum rökum notar virknin sjálfgefið hæð og breidd viðmiðunarargreinarinnar í staðinn, sem í þessu dæmi er ein röð hár og ein dálkur á breidd.