Excel SUM og OFFSET Formel

Hvis dit Excel-regneark indeholder beregninger baseret på et skiftende udvalg af celler, forenkles det ved at bruge SUM og OFFSET-funktionerne sammen i en SUM OFFSET-formel opgaven at holde beregningerne opdaterede.

Opret et dynamisk område med SUM og OFFSET funktionerne

Hvis du bruger beregninger i en periode, der løbende ændrer sig - som i alt salg for måneden - giver OFFSET-funktionen i Excel dig mulighed for at oprette et dynamisk interval, der ændres, da hver dags salgstal tilføjes.

I sig selv kan SUM-funktionen sædvanligvis rumme indsættelsen af ​​nye celler af data ind i området, der summeres.

En undtagelse opstår, når dataene indsættes i cellen, hvor funktionen er i øjeblikket placeret.

I eksemplet ovenstående billede tilføjes de nye salgstal for hver dag i bunden af ​​listen, hvilket tvinger summen til løbende at skifte ned en celle hver gang som de nye data tilføjes.

Hvis SUM-funktionen blev brugt alene for at summere dataene, ville det være nødvendigt at ændre rækkevidden af ​​celler, der blev anvendt som funktionens argument hver gang nye data blev tilføjet.

Ved at bruge SUM og OFFSET-funktionerne sammen, bliver det samlede interval imidlertid dynamisk. Med andre ord ændres det for at rumme nye dataceller. Tilføjelsen af ​​nye celler af data forårsager ikke problemer, fordi området fortsætter med at justere, da hver ny celle tilføjes.

Syntaks og argumenter

Se ovenstående billede for at følge med denne vejledning.

I denne formel bruges SUM-funktionen til at summere det leverede data som argument. Startpunktet for dette interval er statisk og er identificeret som cellehenvisningen til det første tal, der skal summen af ​​formlen.

OFFSET-funktionen er indlejret inde i SUM-funktionen og bruges til at oprette et dynamisk endepunkt til det interval af data, der er opgjort med formlen. Dette opnås ved at indstille endepunktet for området til en celle over formlenes placering.

Formulans syntaks:

= SUM (Range Start: OFFSET (Reference, Rækker, Kols))

• Range Start: Startpunktet for den række af celler, der vil blive summen af ​​SUM-funktionen, er påkrævet. I eksempelbilledet er dette celle B2.
• Reference: Den krævede cellereference, der bruges til at beregne områdets endepunkt, er placeret mange rækker og kolonner væk. I eksempelbilledet er referenceargumentet cellereferencen for selve formlen, fordi du altid vil have området for at afslutte en celle over formlen.
• rækker: Antallet af rækker over eller under referenceniveauet, der bruges til beregning af forskydningen, er påkrævet. Denne værdi kan være positiv, negativ eller indstillet til nul. Hvis offsetens placering er over Reference argument, værdien er negativ. Hvis det er nedenfor, er rækken argumentet positivt. Hvis forskydningen er placeret i samme række, er argumentet nul. I dette eksempel starter forskydningen en række over referencen, så værdien for dette argument er negativ en (-1).
• Cols: Antallet af kolonner til venstre eller højre for referenceargumentet, der anvendes til beregning af forskydningen, er påkrævet. Denne værdi kan være positiv, negativ eller indstillet til nul. Hvis forskydningsstedets placering er til venstre for referenceargumentet, er denne værdi negativ. Hvis til højre er Cols-argumentet positivt. I dette eksempel er dataene i alt i samme kolonne som formlen, så værdien for dette argument er nul.

Brug SUM OFFSET Formula til Total Sales Data

Dette eksempel bruger en SUM OFFSET formel til at returnere summen for de daglige salgstal anført i kolonne B i regnearket.

Indledningsvis blev formlen indgået i celle B6 og samlede salgsdata i fire dage.

Det næste skridt er at flytte SUM OFFSET-formlen ned for en række for at gøre plads til den femte dags salgstal. Dette opnås ved at indsætte en ny række 6, som flytter formlen ned til række 7.

Som et resultat af flytningen opdateres Excel automatisk Reference-argumentet til cellenB7 og tilføjer celleB6 til det interval, der er opsummeret af formlen.

Indtastning af SUM OFFSET Formula

1. Klik på celle B6, som er den placering, hvor resultaterne af formlen først vil blive vist.
2. Klik påformler fanebladet på båndmenuen.
3. VælgeMath & Trigfra båndet for at åbne funktionen rullelisten.
4. Klik påSUM i listen for at hente funktionens dialogboks.
5. Klik på dialogboksen i dialogboksenNummer 1linje.
6. Klik på cellen B2 at indtaste denne cellehenvisning i dialogboksen. Denne placering er det statiske endepunkt for formlen.
7. Klik på dialogboksen i dialogboksental2linje.
8. Indtast følgende OFFSET-funktion: OFFSET (B6, -1,0) for at danne det dynamiske endepunkt for formlen.
9. Klik Okay for at fuldføre funktionen og lukke dialogboksen.

Den samlede mængde vises i celle B7.

Tilføjelse af næste dags salgsdata

For at tilføje den næste dags salgsdata:

1. Højreklik på rækkeoverskriften for række 6 for at åbne kontekstmenuen.
2. Klik på menuen i menuenIndsæt at indsætte en ny række i regnearket.
3. Som følge heraf flyttes SUM OFFSET-formlen ned til celle B7, og række 6 er nu tom.
4. Klik på cellen A6.
5. Indtast nummeret5 at angive, at salget for den femte dag er angivet.
6. Klik på celle B6.
7. Indtast nummeret$1458.25 og tryk påGå ind tast på tastaturet.

Celle B7 opdateringer til den nye total af$7137.40.

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

OFFSET-funktionen har to valgfrie argumenter: Højde og Bredde , som ikke blev brugt i dette eksempel.

Disse argumenter kan bruges til at fortælle OFFSET-funktionen udformningen i form af så mange rækker høje og så mange kolonner brede.

Ved at udelade disse argumenter bruger funktionen som standard højden og bredden af ​​referenceargumentet i stedet, hvilket i dette eksempel er en række højt og en søjle bred.