Hvis Excel-regnearket ditt inneholder beregninger som er basert på et skiftende celleområde, bruk SUM- og OFFSET-funksjonene sammen i en SUM-OFFSET-formel for å forenkle oppgaven med å holde beregningene oppdatert.
Instruksjonene i denne artikkelen gjelder for Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013 og Excel 2010.
Opprett et dynamisk område med SUM- og OFFSET-funksjonene
Hvis du bruker beregninger for en periode som endrer seg kontinuerlig - for eksempel å bestemme salg for måneden - bruk OFFSET-funksjonen i Excel for å sette opp et dynamisk område som endres etter hvert som hver dags salgstall legges til.
I seg selv kan SUM-funksjonen vanligvis imøtekomme innsetting av nye celler med data i området som summeres. Ett unntak oppstår når dataene settes inn i cellen der funksjonen for øyeblikket er plassert.
I eksemplet nedenfor er de nye salgstallene for hver dag lagt til nederst på listen, noe som tvinger totalen til kontinuerlig å flytte ned én celle hver gang de nye dataene legges til.
For å følge med på denne opplæringen, åpne et tomt Excel-regneark og skriv inn eksempeldataene. Regnearket ditt trenger ikke å være formatert som eksempelet, men sørg for å legge inn dataene i de samme cellene.
Hvis bare SUM-funksjonen brukes til å summere dataene, må celleområdet som brukes som funksjonsargument endres hver gang nye data legges til.
Ved å bruke SUM- og OFFSET-funksjonene sammen, blir området som summeres dynamisk og endres for å romme nye dataceller. Tilføyelse av nye celler med data forårsaker ikke problemer fordi området fortsetter å justeres etter hvert som hver ny celle legges til.
Syntaks og argumenter
I denne formelen brukes SUM-funksjonen for å summere rekkevidden av data som leveres som argument. Startpunktet for dette området er statisk og identifiseres som cellereferansen til det første tallet som skal summeres med formelen.
OFFSET-funksjonen er nestet inne i SUM-funksjonen og skaper et dynamisk endepunkt for dataområdet summert av formelen. Dette oppnås ved å sette endepunktet til området til én celle over plasseringen av formelen.
Formelsyntaksen er:
=SUM(Range Start:OFFSET(Reference, Rows, Cols))
Argumentene er:
- Range Start: Startpunktet for celleområdet som summeres av SUM-funksjonen. I dette eksemplet er utgangspunktet celle B2.
- Reference: Den nødvendige cellereferansen som brukes til å beregne områdeendepunktet. I eksemplet er Reference-argumentet cellereferansen for formelen fordi området slutter én celle over formelen.
- Rows: Antall rader over eller under referanseargumentet som brukes til å beregne forskyvningen er påkrevd. Denne verdien kan være positiv, negativ eller satt til null. Hvis forskyvningsplasseringen er over referanseargumentet, er verdien negativ. Hvis forskyvningen er under, er radargumentet positivt. Hvis forskyvningen er plassert i samme rad, er argumentet null. I dette eksemplet begynner forskyvningen én rad over referanseargumentet, så verdien for argumentet er negativ én (-1).
- Cols: Antall kolonner til venstre eller høyre for referanseargumentet som brukes til å beregne forskyvningen. Denne verdien kan være positiv, negativ eller satt til null. Hvis forskyvningsplasseringen er til venstre for Referanseargumentet, er denne verdien negativ. Hvis forskyvningen er til høyre, er Cols-argumentet positivt. I dette eksemplet er dataene som summeres i samme kolonne som formelen, så verdien for dette argumentet er null.
Bruk SUM OFFSET-formelen for å totale salgsdata
Dette eksemplet bruker en SUM OFFSET-formel for å returnere totalen for de daglige salgstallene oppført i kolonne B i regnearket. Opprinnelig ble formelen lagt inn i celle B6 og samlet salgsdataene for fire dager.
Neste trinn er å flytte SUM OFFSET-formelen ned én rad for å gi plass til den femte dagens salgstotal. Dette oppnås ved å sette inn en ny rad 6, som flytter formelen til rad 7.
Som et resultat av flyttingen oppdaterer Excel automatisk referanseargumentet til celle B7 og legger til celle B6 i området summert av formelen.
- Velg celle B6, som er stedet der formelresultatene først vises.
-
Velg Formulas-fanen på båndet.
Image -
Velg Math & Trig.
Image -
Velg SUM.
Image - I dialogboksen Function Arguments plasserer du markøren i tekstboksen Number1.
-
I regnearket, velg celle B2 for å skrive inn denne cellereferansen i dialogboksen. Denne plasseringen er det statiske endepunktet for formelen.
Image - I dialogboksen Function Arguments, plasser markøren i tekstboksen Number2.
-
Angi OFFSET(B6, -1, 0). Denne OFFSET-funksjonen danner det dynamiske endepunktet for formelen.
Image -
Velg OK for å fullføre funksjonen og lukke dialogboksen. Totalen vises i celle B6.
Image
Legg til neste dags salgsdata
For å legge til neste dags salgsdata:
- Høyreklikk på radoverskriften for rad 6.
-
Velg Sett inn for å sette inn en ny rad i regnearket. SUM OFFSET-formelen flyttes én rad ned til celle B7 og rad 6 er nå tom.
Image - Velg celle A6 og skriv inn nummeret 5 for å indikere at salgssummen for den femte dagen legges inn.
-
Velg celle B6, skriv inn $1458.25, og trykk deretter Enter.
Image - Cell B7 oppdateres til den nye totalsummen på $7137,40.
Når du velger celle B7, vises den oppdaterte formelen i formellinjen.
=SUM(B2:OFFSET(B7, -1, 0))
OFFSET-funksjonen har to valgfrie argumenter: Height og Width, som ikke ble brukt i dette eksemplet. Disse argumentene forteller OFFSET-funksjonen formen på utdataene når det gjelder antall rader og kolonner.
Ved å utelate disse argumentene, bruker funksjonen i stedet høyden og bredden til Reference-argumentet, som i dette eksemplet er én rad høy og én kolonne bred.
