Gebruik SUM en OFFSET om totale te vind vir dinamiese data
As u Excel- werkblad berekenings bevat wat gebaseer is op 'n veranderende reeks selle, gebruik die SUM- en OFFSET-funksies saam in 'n SUM OFFSET-formule die taak om die berekeninge op datum te hou.
Skep 'n dinamiese reeks met die SUM- en OFFSET-funksies
As u berekenings gebruik vir 'n tydperk wat voortdurend verander - soos totale verkope vir die maand - kan u met die OFFSET-funksie 'n dinamiese reeks instel wat steeds verander, aangesien elke dag se verkoopsyfers bygevoeg word.
Op sigself kan die SUM-funksie gewoonlik nuwe data-selle akkommodeer in die reeks wat opgesom word.
Een uitsondering vind plaas wanneer die data in die sel geplaas word waar die funksie tans geleë is.
In die voorbeeldbeeld wat hierdie artikel vergesel, word die nuwe verkoopsyfers vir elke dag aan die onderkant van die lys bygevoeg, wat die totaal dwing om voortdurend een sel te verskuif elke keer as die nuwe data bygevoeg word.
As die SUM-funksie op sy eie gebruik is om die data te totaal, sal dit nodig wees om die reeks selle wat gebruik word as die funksie se argument, elke keer as nuwe data bygevoeg is, te verander.
Deur die SUM- en OFFSET-funksies saam te gebruik, word die omvang wat getel word dinamies. Met ander woorde, dit verander om nuwe data-selle te akkommodeer. Die toevoeging van nuwe data-selle veroorsaak nie probleme nie, aangesien die reeks steeds aanpas soos elke nuwe sel bygevoeg word.
Sintaksis en Argumente
Verwys na die prent wat hierdie artikel vergesel om saam met hierdie tutoriaal te volg.
In hierdie formule word die SUM-funksie gebruik om die omvang van data wat as argument verskaf word, te bereik. Die beginpunt vir hierdie reeks is staties en word geïdentifiseer as die selverwysing na die eerste getal wat deur die formule getel moet word.
Die OFFSET funksie is geneste in die SUM funksie en word gebruik om 'n dinamiese eindpunt te skep volgens die omvang van data wat deur die formule bereken word. Dit word bereik deur die eindpunt van die reeks te stel na een sel bo die ligging van die formule.
Die formule se sintaksis :
= SUM (Bereik begin: OFFSET (Verwysing, Rye, Kolomme))Bereik Begin - (benodig) die beginpunt vir die reeks selle wat deur die SUM-funksie getel sal word. In die voorbeeldbeeld is dit sel B2.
Verwysing - (vereis) die selverwysing wat gebruik word om die bereik se eindpunt te bereken, het baie rye en kolomme weggelaat. In die voorbeeldbeeld is die verwysingsargument die selverwysing vir die formule self aangesien ons altyd die reeks wil hê om een sel bo die formule te beëindig.
Rye - (vereis) die aantal rye bo of onder die verwysingsargument wat gebruik word om die offset te bereken. Hierdie waarde kan positief, negatief of op nul wees.
As die offset se ligging bo die verwysingsargument is , is hierdie waarde negatief. As dit onder is, is die Rows- argument positief. As die offset in dieselfde ry is, is hierdie argument nul. In hierdie voorbeeld begin die offset een ry bokant die verwysingsargument , dus die waarde vir hierdie argument is negatief een (-1).
Kolomme - (vereis) die aantal kolomme aan die linkerkant of regs van die verwysingsargument wat gebruik word om die offset te bereken. Hierdie waarde kan positief, negatief of op nul wees
As die offset se ligging links van die verwysingsargument is , is hierdie waarde negatief. As dit reg is, is die Cols- argument positief. In hierdie voorbeeld is die totale data in dieselfde kolom as die formule, dus die waarde vir hierdie argument is nul.
Gebruik die SUM OFFSET-formule vir totale verkoopsdata
In hierdie voorbeeld word 'n SUM OFFSET formule gebruik om die totaal vir die daaglikse verkoopsyfers wat in kolom B van die werkblad voorkom, terug te gee.
Aanvanklik is die formule ingeskryf in sel B6 en het die verkoopsdata vir vier dae opgetel.
Die volgende stap is om die SUM OFFSET formule in 'n ry te skuif om plek te maak vir die vyfde dag se verkoops totaal.
Dit word bereik deur 'n nuwe ry 6 in te voeg wat die formule na ry 7 beweeg.
As gevolg van die skuif, werk Excel outomaties die verwysingsargument na sel B7 by en voeg sel B6 by tot die omvang wat deur die formule opgesom word.
Die SUM OFFSET Formule inskryf
- Klik op sel B6, wat is die plek waar die resultate van die formule aanvanklik vertoon sal word.
- Klik op die Formules- oortjie van die lintmenu .
- Kies Math & Trig uit die lint om die aftreklys vir funksies oop te maak.
- Klik op SUM in die lys om die dialoog van die funksie te vertoon .
- Klik in die dialoogkassie op die Number1 reël.
- Klik op sel B2 om hierdie selverwysing in die dialoogkassie in te voer. Hierdie plek is die statiese eindpunt vir die formule;
- Klik in die dialoogkassie op die Number2- reël.
- Gee die volgende OFFSET funksie: OFFSET (B6, -1,0) om die dinamiese eindpunt vir die formule te vorm.
- Klik OK om die funksie te voltooi en sluit die dialoogkassie.
Die totale $ 5679.15 verskyn in sel B7.
As jy op sel B3 klik, verskyn die volledige funksie = SUM (B2: OFFSET (B6, -1,0)) in die formulebalk bokant die werkblad.
Voeg die volgende dag se verkoopsdata by
Om die volgende dag se verkoopsdata by te voeg:
- Regskliek op die ryopskrif vir ry 6 om die konteks kieslys oop te maak.
- Klik in die kieslys op Insert om 'n nuwe ry in die werkblad in te voeg.
- As gevolg hiervan beweeg die SUM OFFSET-formule na sel B7 en ry 6 is nou leeg.
- Klik op sel A6 .
- Voer die nommer 5 in om aan te dui dat die verkoopsbedrag vir die vyfde dag ingeskryf word.
- Klik op sel B6.
- Tik die nommer $ 1458.25 en druk die Enter sleutel op die sleutelbord.
Sel B7 updates vir die nuwe totaal van $ 7137.40.
As jy op sel B7 klik, verskyn die opgedateerde formule = SUM (B2: OFFSET (B7, -1,0)) in die formulebalk.
Nota : Die OFFSET funksie het twee opsionele argumente: Hoogte en Breedte, wat in hierdie voorbeeld weggelaat is.
Hierdie argumente kan gebruik word om die OFFSET funksie te vertel wat die vorm van die uitset in terme daarvan is dat soveel rye hoog en soveel kolomme wyd is.
Deur hierdie argumente weg te laat, gebruik die funksie standaard die hoogte en breedte van die verwysingsargument, wat in hierdie voorbeeld een ry hoog en een kolom wyd is.