Excel SUM och OFFSET Formel

Om ditt Excel-kalkylblad innehåller beräkningar baserade på ett växlande cellområde, förenar funktionen SUM och OFFSET tillsammans i en SUM OFFSET-formel uppgiften att hålla beräkningarna uppdaterade.

Skapa ett dynamiskt område med SUM och OFFSET-funktionerna

Om du använder beräkningar under en tidsperiod som ständigt ändras - till exempel månadsförsäljning - kan OFFSET-funktionen i Excel låta dig skapa ett dynamiskt intervall som ändras eftersom varje dags försäljningssiffror läggs till.

I själva verket kan SUM-funktionen normalt rymma införandet av nya celler av data i det område som summeras.

Ett undantag uppstår när data sätts in i cellen där funktionen är närvarande.

I exemplet bilden ovan läggs de nya försäljningsfigurerna för varje dag längst ner i listan och tvingar summan att ständigt skifta ner en cell varje gång som den nya data läggs till.

Om SUM-funktionen användes för att sammanställa data, skulle det vara nödvändigt att ändra det antal celler som användes som funktionens argument varje gång nya data tillsattes.

Genom att använda SUM och OFFSET-funktionerna tillsammans blir emellertid det intervall som blir totalt dynamiskt. Med andra ord ändras det för att rymma nya celler av data. Tillsatsen av nya celler av data orsakar inte problem eftersom intervallet fortsätter att justeras när varje ny cell läggs till.

Syntax och Argument

Se bilden ovan för att följa med den här handledningen.

I denna formel används SUM-funktionen för att sammanställa det utbud av data som tillhandahålls som argument. Startpunkten för detta intervall är statiskt och identifieras som cellreferensen till det första talet som ska summeras med formeln.

OFFSET-funktionen är indelad i SUM-funktionen och används för att skapa en dynamisk slutpunkt i det område av data som uppnås med formeln. Detta uppnås genom att ställa in slutpunkten för intervallet till en cell ovanför formelens placering.

Formulans syntax:

= SUM (Range Start: OFFSET (referens, rader, kolumner))

• Range Start: Utgångspunkten för cellområdet som kommer att summeras med SUM-funktionen krävs. I exemplet är detta cell B2.
• Referens: Den erforderliga cellreferensen som används för att beräkna områdets slutpunkt ligger i många rader och kolumner bort. I exempelbilden är referensargumentet cellreferensen för formeln själv eftersom du alltid vill att intervallet ska sluta en cell ovanför formeln.
• rader: Antalet rader över eller under referensargumentet som används vid beräkning av offset krävs. Detta värde kan vara positivt, negativt eller inställt på noll. Om förskjutningsplatsen ligger ovanför Referens argument, värdet är negativt. Om det är nedan är raderargumentet positivt. Om förskjutningen ligger i samma rad är argumentet noll. I detta exempel börjar offset en rad ovanför referensargumentet, så värdet för detta argument är negativt en (-1).
• Cols: Antalet kolumner till vänster eller höger om referensargumentet som används vid beräkning av offset krävs. Detta värde kan vara positivt, negativt eller inställt på noll. Om förskjutningsplatsen är till vänster om referensargumentet är detta värde negativt. Om till höger är Cols-argumentet positivt. I det här exemplet är data som sammanfattas i samma kolumn som formeln, så värdet för detta argument är noll.

Använda SUM OFFSET-formuläret till totala försäljningsdata

I det här exemplet används en SUM OFFSET-formel för att returnera summan för de dagliga försäljningsfigurer som anges i kolumn B i arbetsbladet.

Ursprungligen ingick formeln i cell B6 och uppgick försäljningsdata i fyra dagar.

Nästa steg är att flytta SUM OFFSET-formuläret i rad för att göra plats för den femte dagens försäljningsantal. Detta uppnås genom att infoga en ny rad 6, som flyttar formeln ner till rad 7.

Som ett resultat av flytten uppdaterar Excel automatiskt referensargumentet till cellenB7 och lägger till cellB6 till det område som summeras av formeln.

Ange SUM OFFSET Formel

1. Klicka på cell B6, vilken är den plats där resultaten från formeln initialt kommer att visas.
2. Klicka påformler fliken på bandmenyn.
3. VäljaMath & Trigfrån bandet för att öppna rullgardinsmenyn.
4. Klicka påSUMMA i listan för att få fram funktionens dialogruta.
5. I dialogrutan klickar du påNummer ettlinje.
6. Klicka på cellen B2 att ange denna cellreferens i dialogrutan. Denna plats är den statiska slutpunkten för formeln.
7. I dialogrutan klickar du påNummer 2linje.
8. Ange följande OFFSET-funktion: OFFSET (B6, -1,0) för att bilda den dynamiska slutpunkten för formeln.
9. Klick ok för att slutföra funktionen och stäng dialogrutan.

Totalen visas i cell B7.

Lägger till nästa dags försäljningsdata

För att lägga till nästa dags försäljningsdata:

1. Högerklicka på radrubriken för rad 6 för att öppna snabbmenyn.
2. Klicka på på menynFöra in för att infoga en ny rad i arbetsbladet.
3. Som ett resultat går SUM OFFSET-formuläret ner till cell B7 och rad 6 är nu tom.
4. Klicka på cellen A6.
5. Ange numret5 för att ange att försäljningsbeloppet för den femte dagen anges.
6. Klicka på cell B6.
7. Skriv in numret$1458.25 och tryck påStiga på tangent på tangentbordet.

Cell B7 uppdateringar till den nya totalen av$7137.40.

När du klickar på cell B7, den uppdaterade formeln= SUMMA (B2: OFFSET (B7, -1,0)) visas i formulärfältet.

Funktionen OFFSET har två valfria argument: Höjd och Bredd , vilka inte användes i detta exempel.

Dessa argument kan användas för att berätta OFFSET-funktionens form på så många rader som höga och så många kolumner breda.

Genom att utelämna dessa argument använder funktionen som standard höjden och bredden av referensargumentet istället, vilket i det här exemplet är en rad hög och en kolumn bred.