As jou Excel-werkblad berekeninge insluit wat gebaseer is op 'n veranderende reeks selle, gebruik die SOM- en OFFSET-funksies saam in 'n SUM-OFFSET-formule om die taak om die berekeninge op datum te hou, te vereenvoudig.
Instruksies in hierdie artikel is van toepassing op Excel vir Microsoft 365, Excel 2019, Excel 2016, Excel 2013 en Excel 2010.
Skep 'n dinamiese reeks met die SOM- en OFFSET-funksies
As jy berekeninge gebruik vir 'n tydperk wat voortdurend verander - soos om verkope vir die maand te bepaal - gebruik die OFFSET-funksie in Excel om 'n dinamiese reeks op te stel wat verander soos elke dag se verkoopsyfers bygevoeg word.
Op sigself kan die SUM-funksie gewoonlik die invoeging van nuwe selle data in die reeks wat opgetel word, akkommodeer. Een uitsondering vind plaas wanneer die data ingevoeg word in die sel waar die funksie tans geleë is.
In die voorbeeld hieronder word die nuwe verkoopsyfers vir elke dag onderaan die lys bygevoeg, wat die totaal dwing om elke keer voortdurend een sel af te skuif namate die nuwe data bygevoeg word.
Om saam met hierdie tutoriaal te volg, maak 'n leë Excel-werkblad oop en voer die voorbeelddata in. Jou werkblad hoef nie soos die voorbeeld geformateer te wees nie, maar maak seker dat jy die data in dieselfde selle invoer.
As slegs die SUM-funksie gebruik word om die data op te tel, sal die reeks selle wat as die funksie-argument gebruik word, gewysig moet word elke keer wanneer nuwe data bygevoeg word.
Deur die SUM- en OFFSET-funksies saam te gebruik, word die reeks wat opgetel word dinamies en verander dit om nuwe dataselle te akkommodeer. Die byvoeging van nuwe selle met data veroorsaak nie probleme nie, want die reeks bly aanpas soos elke nuwe sel bygevoeg word.
Sintaksis en argumente
In hierdie formule word die SUM-funksie gebruik om die reeks data wat as die argument verskaf word, op te tel. Die beginpunt vir hierdie reeks is staties en word geïdentifiseer as die selverwysing na die eerste getal wat deur die formule opgetel moet word.
Die OFFSET-funksie is geneste in die SUM-funksie en skep 'n dinamiese eindpunt vir die reeks data wat deur die formule saamgevoeg word. Dit word bewerkstellig deur die eindpunt van die reeks op een sel bo die ligging van die formule te stel.
Die formule-sintaksis is:
=SOM(Reikwydtebegin:AFSET(Verwysing, Rye, Kole))
Die argumente is:
- Reikbegin: Die beginpunt vir die reeks selle wat deur die SOM-funksie getel sal word. In hierdie voorbeeld is die beginpunt sel B2.
- Reference: Die vereiste selverwysing wat gebruik word om die reeks eindpunt te bereken. In die voorbeeld is die Verwysingsargument die selverwysing vir die formule omdat die reeks een sel bokant die formule eindig.
- Rows: Die aantal rye bo of onder die verwysingsargument wat gebruik word om die afwyking te bereken, word vereis. Hierdie waarde kan positief, negatief of op nul gestel wees. As die offset-ligging bo die verwysingsargument is, is die waarde negatief. As die afset onder is, is die Rye-argument positief. As die offset in dieselfde ry geleë is, is die argument nul. In hierdie voorbeeld begin die afset een ry bokant die verwysingsargument, dus is die waarde vir die argument negatiewe een (-1).
- Kols: Die aantal kolomme links of regs van die verwysingsargument wat gebruik word om die afwyking te bereken. Hierdie waarde kan positief, negatief of op nul gestel wees. As die offset-ligging aan die linkerkant van die verwysingsargument is, is hierdie waarde negatief. As die verrekening na regs is, is die Cols-argument positief. In hierdie voorbeeld is die data wat opgetel word in dieselfde kolom as die formule, dus die waarde vir hierdie argument is nul.
Gebruik die SUM-OFFSET-formule om verkopedata te totaal
Hierdie voorbeeld gebruik 'n SOM OFFSET formule om die totaal terug te gee vir die daaglikse verkoopsyfers gelys in kolom B van die werkblad. Aanvanklik is die formule in sel B6 ingevoer en het die verkoopsdata vir vier dae saamgetel.
Die volgende stap is om die SOM OFFSET formule een ry af te skuif om plek te maak vir die vyfde dag se verkoopstotaal. Dit word bewerkstellig deur 'n nuwe ry 6 in te voeg, wat die formule na ry 7 skuif.
As gevolg van die skuif dateer Excel outomaties die verwysingsargument op na sel B7 en voeg sel B6 by die reeks wat deur die formule opgesom word.
- Selekteer sel B6, wat die ligging is waar die formuleresultate aanvanklik sal vertoon.
-
Kies die Formules oortjie van die lint.
Image -
Kies Math & Trig.
Image -
Kies SUM.
Image - In die Function Arguments dialoogkassie, plaas die wyser in die Number1 tekskassie.
-
In die werkblad, kies sel B2 om hierdie selverwysing in die dialoogkassie in te voer. Hierdie ligging is die statiese eindpunt vir die formule.
Image - In die Function Arguments dialoogkassie, plaas die wyser in die Number2 tekskassie.
-
Voer OFFSET(B6, -1, 0) in. Hierdie OFFSET-funksie vorm die dinamiese eindpunt vir die formule.
Image -
Kies OK om die funksie te voltooi en die dialoogkassie toe te maak. Die totaal verskyn in sel B6.
Image
Voeg die volgende dag se verkoopsdata by
Om die volgende dag se verkoopsdata by te voeg:
- Regsklik op die rykop vir ry 6.
-
Kies Insert om 'n nuwe ry in die werkblad in te voeg. Die SOM OFFSET formule beweeg een ry af na sel B7 en ry 6 is nou leeg.
Image - Kies sel A6 en voer die nommer 5 in om aan te dui dat die verkoopstotaal vir die vyfde dag ingevoer word.
-
Kies sel B6, voer $1458.25 in, druk dan Enter.
Image - Sel B7 werk op na die nuwe totaal van $7137.40.
Wanneer jy sel B7 kies, verskyn die opgedateerde formule in die formulebalk.
=SOM(B2:AFSET(B7, -1, 0))
Die OFFSET-funksie het twee opsionele argumente: Height en Width, wat nie in hierdie voorbeeld gebruik is nie. Hierdie argumente vertel die OFFSET-funksie die vorm van die afvoer in terme van die aantal rye en kolomme.
Deur hierdie argumente weg te laat, gebruik die funksie eerder die hoogte en breedte van die Verwysingsargument, wat in hierdie voorbeeld een ry hoog en een kolom wyd is.
