Excel SUM un OFFSET Formula

Ja jūsu Excel darblapā ir aprēķini, kas pamatojas uz mainīgo šūnu diapazonu, izmantojot SUM un OFFSET funkcijas kopā SUM OFFSET formula vienkāršo uzdevumu saglabāt aprēķinus.

Izveidojiet dinamisko diapazonu, izmantojot SUM un OFFSET funkcijas

Ja jūs izmantojat aprēķinus periodam, kas nepārtraukti mainās, piemēram, mēneša kopējais pārdošanas apjoms, Excel funkcija OFFSET ļauj iestatīt dinamisko diapazonu, kas mainās, jo tiek pievienoti ikdienas pārdošanas rādītāji.

Parasti SUM funkcija parasti var apkopot jaunu datu šūnu ievietošanu diapazonā, kas tiek summēts.

Viens izņēmums rodas tad, kad dati tiek ievietoti šūnā, kurā šobrīd atrodas funkcija.

Attēlā iepriekš minētajā attēlā jaunie pārdošanas rādītāji par katru dienu tiek pievienoti saraksta apakšdaļā, tāpēc katru dienu, kad jaunie dati tiek pievienoti, kopējais apjoms katru reizi pastāvīgi tiek pārvietots uz leju.

Ja SUM funkcija tiktu izmantota atsevišķi, lai apkopotu datus, vajadzētu mainīt to šūnu diapazonu, kuras tika izmantotas kā funkciju arguments katru reizi, kad tika pievienoti jauni dati.

Tomēr, izmantojot kopā SUM un OFFSET funkcijas, kopējais diapazons kļūst dinamisks. Citiem vārdiem sakot, tas mainās, lai pielāgotos jaunām datu šūnām. Jaunu datu šūnu pievienošana nerada problēmas, jo diapazons turpina pielāgoties, pievienojot katru jauno šūnu.

Sintakse un argumenti

Skatiet iepriekš minēto attēlu, lai sekotu līdzi šai apmācībai.

Šajā formulā SUM funkcija tiek izmantota, lai apkopotu iesniegto datu diapazonu kā savu argumentu. Sākuma punkts šim diapazonam ir statisks un tiek identificēts kā šūnu atsauce uz pirmo skaitli, kas jāaprēķina pēc formulas.

OFFSET funkcija tiek ievietota SUM funkcijas funkciju iekšpusē, un to izmanto, lai izveidotu dinamisku gala punktu datu kopumam, ko veido formula. Tas tiek paveikts, nosakot diapazona gala punktu uz vienu šūnu virs formulas atrašanās vietas.

Formulas sintakse:

= SUM (diapazona sākums: OFFSET (atsauce, rindas, kolonnas))

• Sākuma diapazons: Ir nepieciešams sākumpunkts šūnu diapazonam, kas tiks summēti ar SUM funkciju. Piemērā attēlā tas ir B2.
• Atsauce: Nepieciešamā šūnu atsauce, ko izmanto diapazona gala punkta aprēķināšanai, atrodas daudzās rindās un kolonnās prom. Piemēra attēlā Atsauces arguments ir šūnu atsauce uz pašu formulu, jo jūs vienmēr vēlaties, lai diapazons beigtu vienu šūnu virs formulas.
• Rindas: Ir nepieciešams rindu skaits augšā vai zem atsauces argumenta, kas izmantots ieskaita aprēķināšanai. Šī vērtība var būt pozitīva, negatīva vai iestatīta uz nulli. Ja kompensācijas vieta atrodas virs Atsauce arguments, vērtība ir negatīva. Ja tas ir zemāk, rindu arguments ir pozitīvs. Ja nobīde atrodas tajā pašā rindā, arguments ir nulle. Šajā piemērā nobīde sākas vienā rindā virs Atsauces argumenta, tāpēc šī argumenta vērtība ir negatīva (-1).
• Cols: Ir nepieciešams kolonnu skaits, kas atrodas atsauces argumenta kreisajā vai labajā pusē, ko izmanto kompensācijas aprēķināšanai. Šī vērtība var būt pozitīva, negatīva vai iestatīta uz nulli. Ja kompensācijas vieta atrodas Atsauces argumenta kreisajā pusē, šī vērtība ir negatīva. Ja pa labi, kolāžu arguments ir pozitīvs. Šajā piemērā apkopotie dati ir tādā pašā slejā kā formula, tāpēc šī argumenta vērtība ir nulle.

Izmantojot SUM OFFSET formulu kopējiem pārdošanas datiem

Šajā piemērā tiek izmantota SUM OF OFFSET formula, lai atgrieztu kopējo summu ikdienas pārdošanas rādītājiem, kas uzskaitīti darblapas B slejā.

Sākotnēji formula tika ievadīta šūnā B6, un pārdošanas dati tika apkopoti četras dienas.

Nākamais solis ir pārvietot SUM OFFSET formulu rindā, lai atļautu vietu piektajā dienā. Tas tiek paveikts, ievietojot jaunu rindu 6, kas pārvieto formulu uz 7 rindu.

Pārvietošanas rezultātā Excel automātiski atjauno atsauces argumentu šūnaiB7 un pievieno šūnuB6 uz diapazonu, ko summē formula.

SUMMAKSAS IESLĒGŠANAS formulas ievadīšana

1. Noklikšķiniet uz šūnas B6, kas ir vieta, kur sākotnēji parādīsies formulas rezultāti.
2. Noklikšķiniet uzFormulas lentas izvēlnes cilne.
3. IzvēliesMatemātika un treniņšno lentes, lai atvērtu nolaižamo sarakstu funkciju.
4. Klikšķiniet uzSUM sarakstā, lai atvērtu funkciju dialoglodziņu.
5. Dialoglodziņā noklikšķiniet uzNumurs1līnija
6. Noklikšķiniet uz šūnas B2 lai ievadītu šo šūnu atsauci dialoglodziņā. Šī vieta ir formas galīgais parametrs.
7. Dialoglodziņā noklikšķiniet uzNumurs2līnija
8. Ievadiet šādu OFFSET funkciju: OFFSET (B6, -1,0) lai veidotu dinamisko parametru formulai.
9. Klikšķis labi lai pabeigtu funkciju un aizveriet dialoglodziņu.

Kopā parādās šūnā B7.

Nākamās dienas pārdošanas datu pievienošana

Lai pievienotu nākamās dienas pārdošanas datus:

1. Lai atvērtu kontekstizvēlni, ar peles labo pogu noklikšķiniet uz 6. rindas rindkopas.
2. Izvēlnē noklikšķiniet uzIevietot lai darblapā ievietotu jaunu rindu.
3. Tā rezultātā SUM OFTSS formulas pārvietojas uz leju līdz šūnai B7, un 6. rindiņa tagad ir tukša.
4. Noklikšķiniet uz šūnas A6.
5. Ievadiet numuru5 lai norādītu, ka ir ievadīta pārdošanas kopapjoms piektajai dienai.
6. Noklikšķiniet uz šūnas B6.
7. Ierakstiet numuru$1458.25 un nospiedietIevadiet tastatūras taustiņš.

Cell B7 atjaunina jauno kopiju$7137.40.

Noklikšķinot uz šūnas B7, tiek atjaunināta formula= SUM (B2: OFFSET (B7, -1,0)) parādās formulas joslā.

OFFSET funkcijai ir divi izvēles argumenti: Augstums un platums , kas šajā piemērā nav izmantoti.

Šos argumentus var izmantot, lai parādītu OFFSET funkcijai izejas formu, ņemot vērā tik daudz rindu augstu un tik daudz kolonnu platas.

Izlaižot šos argumentus, funkcija pēc noklusējuma izmanto atsauces argumenta augstumu un platumu, kas šajā piemērā ir viena rinda augsta un viena sleja platuma.