Hvis dit Excel-regneark indeholder beregninger, der er baseret på et skiftende celleområde, skal du bruge SUM- og OFFSET-funktionerne sammen i en SUM-OFFSET-formel for at forenkle opgaven med at holde beregningerne ajour.
Instruktionerne i denne artikel gælder for Excel til Microsoft 365, Excel 2019, Excel 2016, Excel 2013 og Excel 2010.
Opret et dynamisk område med SUM- og OFFSET-funktionerne
Hvis du bruger beregninger for en periode, der konstant ændrer sig - såsom at bestemme salget for måneden - brug OFFSET-funktionen i Excel til at opsætte et dynamisk interval, der ændrer sig, efterhånden som hver dags salgstal tilføjes.
I sig selv kan SUM-funktionen norm alt rumme indsættelse af nye celler med data i det område, der summeres. En undtagelse opstår, når dataene indsættes i den celle, hvor funktionen i øjeblikket er placeret.
I eksemplet nedenfor er de nye salgstal for hver dag tilføjet nederst på listen, hvilket tvinger totalen til kontinuerligt at flytte en celle ned, hver gang de nye data tilføjes.
For at følge med i denne øvelse skal du åbne et tomt Excel-regneark og indtaste eksempeldataene. Dit regneark behøver ikke at være formateret som eksemplet, men sørg for at indtaste dataene i de samme celler.
Hvis kun SUM-funktionen bruges til at summere dataene, skal celleområdet, der bruges som funktionsargument, ændres, hver gang nye data tilføjes.
Ved at bruge SUM- og OFFSET-funktionerne sammen, bliver det samlede interval dynamisk og ændres for at rumme nye dataceller. Tilføjelsen af nye celler med data giver ikke problemer, fordi området fortsætter med at justere, efterhånden som hver ny celle tilføjes.
Syntaks og argumenter
I denne formel bruges SUM-funktionen til at summere rækkevidden af data, der leveres som argument. Startpunktet for dette område er statisk og identificeres som cellereferencen til det første tal, der skal summeres med formlen.
FORSKYDNING-funktionen er indlejret i SUM-funktionen og skaber et dynamisk slutpunkt til det dataområde, der er samlet af formlen. Dette opnås ved at indstille endepunktet for området til én celle over placeringen af formlen.
Formelsyntaksen er:
=SUM(Range Start:OFFSET(Reference, Rows, Cols))
Argumenterne er:
- Range Start: Startpunktet for det celleområde, der summeres af SUM-funktionen. I dette eksempel er udgangspunktet celle B2.
- Reference: Den påkrævede cellereference, der bruges til at beregne områdets slutpunkt. I eksemplet er Reference-argumentet cellereferencen for formlen, fordi området slutter én celle over formlen.
- Rows: Antallet af rækker over eller under referenceargumentet, der bruges til at beregne offset, er påkrævet. Denne værdi kan være positiv, negativ eller sat til nul. Hvis offsetplaceringen er over referenceargumentet, er værdien negativ. Hvis forskydningen er under, er argumentet Rows positivt. Hvis forskydningen er placeret i samme række, er argumentet nul. I dette eksempel begynder forskydningen en række over referenceargumentet, så værdien for argumentet er negativ én (-1).
- Cols: Antallet af kolonner til venstre eller højre for referenceargumentet, der bruges til at beregne offset. Denne værdi kan være positiv, negativ eller sat til nul. Hvis offsetplaceringen er til venstre for referenceargumentet, er denne værdi negativ. Hvis forskydningen er til højre, er Cols-argumentet positivt. I dette eksempel er de data, der summeres, i samme kolonne som formlen, så værdien for dette argument er nul.
Brug SUM OFFSET-formlen til samlede salgsdata
Dette eksempel bruger en SUM-OFFSET-formel til at returnere totalen for de daglige salgstal, der er angivet i kolonne B i regnearket. Til at begynde med blev formlen indtastet i celle B6 og samlede salgsdata for fire dage.
Det næste trin er at flytte SUM OFFSET-formlen én række ned for at gøre plads til den femte dags salgstotal. Dette opnås ved at indsætte en ny række 6, som flytter formlen til række 7.
Som et resultat af flytningen opdaterer Excel automatisk referenceargumentet til celle B7 og føjer celle B6 til intervallet summeret af formlen.
- Vælg celle B6, som er det sted, hvor formelresultaterne oprindeligt vises.
-
Vælg fanen Formler på båndet.
Image -
Vælg Math & Trig.
Image -
Vælg SUM.
Image - Placer markøren i Number1 i dialogboksen Function Arguments..
-
I regnearket, vælg celle B2 for at indtaste denne cellereference i dialogboksen. Denne placering er det statiske slutpunkt for formlen.
Image - Placer markøren i Number2 i dialogboksen Function Arguments..
-
Enter OFFSET(B6, -1, 0). Denne OFFSET-funktion danner det dynamiske slutpunkt for formlen.
Image -
Vælg OK for at fuldføre funktionen og lukke dialogboksen. Summen vises i celle B6.
Image
Tilføj næste dags salgsdata
For at tilføje næste dags salgsdata:
- Højreklik på rækkeoverskriften for række 6.
-
Vælg Insert for at indsætte en ny række i regnearket. SUM OFFSET formlen flyttes en række ned til celle B7, og række 6 er nu tom.
Image - Vælg celle A6, og indtast nummeret 5 for at angive, at det samlede salg for den femte dag indtastes.
-
Vælg celle B6, indtast $1458,25, og tryk derefter på Enter.
Image - Cell B7 opdaterer til den nye total på 7137,40 $.
Når du vælger celle B7, vises den opdaterede formel i formellinjen.
=SUM(B2:OFFSET(B7, -1, 0))
Funktionen OFFSET har to valgfrie argumenter: Højde og Bredde, som ikke blev brugt i dette eksempel. Disse argumenter fortæller OFFSET-funktionen formen på outputtet i form af antallet af rækker og kolonner.
Ved at udelade disse argumenter, bruger funktionen i stedet højden og bredden af referenceargumentet, som i dette eksempel er en række høj og en kolonne bred.
