Excel SUM og OFFSET Formula

Hvis Excel-regnearket inneholder beregninger basert på et skiftende utvalg av celler, forenkler det å holde beregningene oppdatert ved å bruke SUM og OFFSET-funksjonene sammen i en SUM OFFSET-formel.

Lag et dynamisk område med SUM og OFFSET-funksjonene

Hvis du bruker beregninger for en periode som kontinuerlig endres - for eksempel totalt salg for måneden - kan OFFSET-funksjonen i Excel sette opp et dynamisk rekkevidde som endres ettersom hver dags salgstall legges til.

I seg selv kan SUM-funksjonen vanligvis tilpasse innsetting av nye celler av data inn i området som summeres.

Et unntak oppstår når dataene er satt inn i cellen der funksjonen er for tiden lokalisert.

I eksempelbildet ovenfor blir de nye salgstallene for hver dag lagt til bunnen av listen, og tvinger summen til kontinuerlig å skifte ned en celle hver gang de nye dataene legges til.

Hvis SUM-funksjonen ble brukt til å summere dataene, ville det være nødvendig å endre rekkevidden av celler som ble brukt som funksjonens argument hver gang nye data ble lagt til.

Ved å bruke SUM og OFFSET-funksjonene sammen, blir rekkevidden som blir totalt dynamisk. Med andre ord endres det for å imøtekomme nye dataceller. Tillegg av nye dataceller forårsaker ikke problemer fordi rekkevidden fortsetter å justere etter hvert som hver ny celle er lagt til.

Syntaks og argumenter

Se bildet ovenfor for å følge med denne opplæringen.

I denne formelen brukes SUM-funksjonen til å summere rekkevidden av data som leveres som argument. Startpunktet for dette området er statisk og er identifisert som cellehenvisningen til det første nummeret som skal summen av formelen.

OFFSET-funksjonen er nestet inne i SUM-funksjonen, og brukes til å opprette et dynamisk endepunkt i rekkevidden av dataene som følger med formelen. Dette oppnås ved å sette endepunktet for rekkevidden til en celle over plasseringen av formelen.

Formulans syntaks:

= SUM (Range Start: OFFSET (referanse, rader, kols))

• Range Start: Utgangspunktet for rekkevidden av celler som vil bli summet av SUM-funksjonen er nødvendig. I eksempelbildet er dette celle B2.
• Henvisning: Den nødvendige cellereferansen som brukes til å beregne rekkeviddeens endepunkt, er plassert i mange rader og kolonner. I eksempelbildet er referanseargumentet cellereferansen for formelen selv fordi du alltid vil at området skal avslutte en celle over formelen.
• rader: Antall rader over eller under referanseparametet som brukes til å beregne forskyvningen, kreves. Denne verdien kan være positiv, negativ eller satt til null. Hvis offsettets plassering er over Henvisning argument, verdien er negativ. Hvis det er under, er Rows-argumentet positivt. Hvis forskyvningen er plassert i samme rad, er argumentet null. I dette eksemplet begynner kompensasjonen en rad over referanse argumentet, så verdien for dette argumentet er negativ en (-1).
• Cols: Antall kolonner til venstre eller høyre for referanseargumentet som brukes til å beregne forskyvningen, kreves. Denne verdien kan være positiv, negativ eller satt til null. Hvis forskyvningens plassering er til venstre for referanseargumentet, er denne verdien negativ. Hvis til høyre er Cols-argumentet positivt. I dette eksemplet er dataene som er samlet, i samme kolonne som formelen, så verdien for dette argumentet er null.

Bruk SUM OFFSET Formula til Total Salgsdata

Dette eksemplet bruker en SUM OFFSET-formel for å returnere summen for de daglige salgstallene som er oppført i kolonne B i regnearket.

I utgangspunktet ble formelen inngått i celle B6 og samlet salgsdata i fire dager.

Det neste trinnet er å flytte SUM OFFSET-formelen på rad for å få plass til den femte dagens salgssummen. Dette oppnås ved å sette inn en ny rad 6, som beveger formelen ned til rad 7.

Som et resultat av flyttingen oppdaterer Excel automatisk referansegrunnlaget til cellenB7 og legger til celleB6 til området som er oppsummert av formelen.

Angir SUM OFFSET Formula

1. Klikk på celle B6, som er stedet der resultatene av formelen først vil bli vist.
2. Klikk påformler fanen på båndmenyen.
3. VelgeMath & Trigfra båndet for å åpne funksjonsmenyen.
4. Klikk påSUM i listen for å hente funksjonens dialogboks.
5. I dialogboksen klikker du påtall1linje.
6. Klikk på cellen B2 å angi denne cellehenvisningen i dialogboksen. Denne plasseringen er det statiske endepunktet for formelen.
7. I dialogboksen klikker du påNummer 2linje.
8. Skriv inn følgende OFFSET-funksjon: OFFSET (B6, -1,0) for å danne det dynamiske endepunktet for formelen.
9. Klikk OK for å fullføre funksjonen og lukke dialogboksen.

Summen vises i celle B7.

Legge til neste dags salgsdata

For å legge til neste dags salgsdata:

1. Høyreklikk på radoverskriften for rad 6 for å åpne kontekstmenyen.
2. Klikk på menyen i menyenSett inn å sette inn en ny rad i regnearket.
3. Som et resultat går SUM OFFSET-formelen ned til celle B7 og rad 6 er nå tom.
4. Klikk på cellen A6.
5. Skriv inn nummeret5 for å indikere at salgsbeløpet for den femte dagen blir oppgitt.
6. Klikk på celle B6.
7. Skriv inn nummeret$1458.25 og trykk påTast inn tast på tastaturet.

Cell B7 oppdateringer til den nye totalen av$7137.40.

Når du klikker på celle B7, oppdaterte formel= SUM (B2: OFFSET (B7, -1,0)) vises i formellelinjen.

OFFSET-funksjonen har to valgfrie argumenter: Høyde og Bredde , som ikke ble brukt i dette eksemplet.

Disse argumentene kan brukes til å fortelle OFFSET-funksjonen formen på utgangen i form av så mange rader høye og så mange kolonner brede.

Ved å utelate disse argumentene bruker funksjonen som standard høyden og bredden til referanseargumentet i stedet, som i dette eksemplet er en rad høy og en kolonne bred.