Om ditt Excel-kalkylblad innehåller beräkningar som är baserade på ett skiftande cellområde, använd SUMMA- och OFFSET-funktionerna tillsammans i en SUMMA-OFFSET-formel för att förenkla uppgiften att hålla beräkningarna uppdaterade.
Instruktionerna i den här artikeln gäller Excel för Microsoft 365, Excel 2019, Excel 2016, Excel 2013 och Excel 2010.
Skapa ett dynamiskt intervall med SUM- och OFFSET-funktionerna
Om du använder beräkningar för en tidsperiod som ständigt förändras - som att bestämma försäljningen för månaden - använd OFFSET-funktionen i Excel för att ställa in ett dynamiskt intervall som ändras när varje dags försäljningssiffror läggs till.
I sig själv kan SUM-funktionen vanligtvis hantera infogning av nya dataceller i intervallet som summeras. Ett undantag inträffar när data infogas i cellen där funktionen för närvarande finns.
I exemplet nedan läggs de nya försäljningssiffrorna för varje dag till längst ner på listan, vilket tvingar summan att kontinuerligt flyttas ned en cell varje gång som ny data läggs till.
För att följa den här handledningen öppnar du ett tomt Excel-kalkylblad och anger exempeldata. Ditt kalkylblad behöver inte vara formaterat som exemplet, men se till att ange data i samma celler.
Om endast SUM-funktionen används för att summera data, skulle cellintervallet som används som funktionsargument behöva ändras varje gång ny data läggs till.
Genom att använda SUM- och OFFSET-funktionerna tillsammans blir intervallet som summeras dynamiskt och ändras för att ta emot nya dataceller. Tillägget av nya celler med data orsakar inga problem eftersom intervallet fortsätter att justeras när varje ny cell läggs till.
Syntax och argument
I den här formeln används SUM-funktionen för att summera mängden data som tillhandahålls som argument. Startpunkten för detta intervall är statisk och identifieras som cellreferens till det första talet som ska summeras med formeln.
Funktionen OFFSET är kapslad i SUM-funktionen och skapar en dynamisk slutpunkt för dataintervallet som summeras av formeln. Detta åstadkoms genom att ställa in slutpunkten för intervallet till en cell ovanför platsen för formeln.
Formelsyntaxen är:
=SUMMA(Range Start:OFFSET(Referens, rader, kolor))
Argumenten är:
- Range Start: Startpunkten för intervallet av celler som kommer att summeras av SUM-funktionen. I det här exemplet är utgångspunkten cell B2.
- Reference: Den obligatoriska cellreferens som används för att beräkna intervallets slutpunkt. I exemplet är referensargumentet cellreferens för formeln eftersom intervallet slutar en cell ovanför formeln.
- Rows: Antalet rader över eller under referensargumentet som används vid beräkning av offset krävs. Detta värde kan vara positivt, negativt eller satt till noll. Om offsetplatsen är ovanför referensargumentet är värdet negativt. Om förskjutningen är under är argumentet Rader positivt. Om förskjutningen är placerad i samma rad är argumentet noll. I det här exemplet börjar förskjutningen en rad ovanför referensargumentet, så värdet för argumentet är negativ ett (-1).
- Cols: Antalet kolumner till vänster eller höger om referensargumentet som används för att beräkna offset. Detta värde kan vara positivt, negativt eller satt till noll. Om offsetplatsen är till vänster om referensargumentet är detta värde negativt. Om offset är åt höger är Cols-argumentet positivt. I det här exemplet finns data som summeras i samma kolumn som formeln, så värdet för detta argument är noll.
Använd SUM OFFSET-formeln för att totala försäljningsdata
Det här exemplet använder en SUMMA OFFSET-formel för att returnera summan för de dagliga försäljningssiffrorna i kolumn B i kalkylbladet. Från början matades formeln in i cell B6 och summerade försäljningsdata under fyra dagar.
Nästa steg är att flytta SUM OFFSET-formeln ner en rad för att göra plats för den femte dagens försäljningssumma. Detta görs genom att infoga en ny rad 6, som flyttar formeln till rad 7.
Som ett resultat av flytten uppdaterar Excel automatiskt referensargumentet till cell B7 och lägger till cell B6 till intervallet som summeras av formeln.
- Välj cell B6, vilket är platsen där formelresultaten kommer att visas initi alt.
-
Välj fliken Formler på bandet.
Image -
Välj Math & Trig.
Image -
Välj SUM.
Image - I dialogrutan Function Arguments placerar du markören i textrutan Number1.
-
I kalkylbladet, välj cell B2 för att ange denna cellreferens i dialogrutan. Denna plats är den statiska slutpunkten för formeln.
Image - I dialogrutan Function Arguments placerar du markören i textrutan Number2..
-
Ange OFFSET(B6, -1, 0). Denna OFFSET-funktion bildar den dynamiska slutpunkten för formeln.
Image -
Välj OK för att slutföra funktionen och stänga dialogrutan. Summan visas i cell B6.
Image
Lägg till nästa dags försäljningsdata
För att lägga till nästa dags försäljningsdata:
- Högerklicka på radhuvudet för rad 6.
-
Välj Insert för att infoga en ny rad i kalkylbladet. SUMMA OFFSET-formeln flyttas ner en rad till cell B7 och rad 6 är nu tom.
Image - Välj cell A6 och ange numret 5 för att indikera att försäljningssumman för den femte dagen läggs in.
-
Välj cell B6, ange $1458.25 och tryck sedan Enter.
Image - Cell B7 uppdateras till den nya summan på 7137,40 $.
När du väljer cell B7 visas den uppdaterade formeln i formelfältet.
=SUMMA(B2:OFFSET(B7, -1, 0))
Funktionen OFFSET har två valfria argument: Height och Width, som inte användes i det här exemplet. Dessa argument talar om för OFFSET-funktionen formen på utdata i termer av antalet rader och kolumner.
Genom att utelämna dessa argument använder funktionen istället höjden och bredden på referensargumentet, som i detta exempel är en rad hög och en kolumn bred.
