Excel SUM en OFFSET-formule

Als uw Excel-werkblad berekeningen bevat op basis van een veranderend celbereik, vereenvoudigt het gebruik van de functies SOM en OFFSET samen in een SUM OFFSET-formule de taak om de berekeningen up-to-date te houden.

Creëer een dynamisch bereik met de functies SOM en OFFSET

Als u berekeningen gebruikt voor een periode die voortdurend verandert, zoals de totale verkoop voor de maand, kunt u met de functie OFFSET in Excel een dynamisch bereik instellen dat wordt gewijzigd naarmate de verkoopcijfers van elke dag worden toegevoegd.

Op zichzelf kan de SOM-functie meestal plaats bieden aan het invoegen van nieuwe gegevenscellen in het bereik dat wordt gesommeerd.

Een uitzondering doet zich voor wanneer de gegevens worden ingevoegd in de cel waar de functie zich momenteel bevindt.

In de bovenstaande voorbeeldafbeelding worden de nieuwe verkoopcijfers voor elke dag onder aan de lijst toegevoegd, waardoor het totaal wordt gedwongen telkens één cel omlaag te gaan wanneer de nieuwe gegevens worden toegevoegd.

Als de SOM-functie op zichzelf zou zijn gebruikt om de gegevens te totaliseren, zou het nodig zijn om het cellenbereik dat werd gebruikt als argument van de functie te wijzigen telkens wanneer nieuwe gegevens werden toegevoegd.

Door de functies SOM en OFFSET samen te gebruiken, wordt het bereik dat wordt getotaliseerd dynamisch. Met andere woorden, het verandert om ruimte te bieden aan nieuwe gegevenscellen. De toevoeging van nieuwe gegevenscellen veroorzaakt geen problemen omdat het bereik blijft worden aangepast wanneer elke nieuwe cel wordt toegevoegd.

Syntaxis en argumenten

Raadpleeg de afbeelding hierboven om deze zelfstudie te volgen.

In deze formule wordt de functie SOM gebruikt om het bereik van gegevens dat wordt geleverd als het argument te totaliseren. Het startpunt voor dit bereik is statisch en wordt geïdentificeerd als de celverwijzing naar het eerste getal dat moet worden opgeteld door de formule.

De OFFSET-functie is genest in de SUM-functie en wordt gebruikt om een ​​dynamisch eindpunt te maken voor het gegevensbereik dat door de formule wordt geteld. Dit wordt bereikt door het eindpunt van het bereik in te stellen op één cel boven de locatie van de formule.

De syntaxis van de formule:

= SOM (Range Start: OFFSET (Reference, Rows, Cols))

• Bereik start: Het startpunt voor het cellenbereik dat door de SOM-functie moet worden opgeteld, is vereist. In de voorbeeldafbeelding is dit cel B2.
• Referentie: De vereiste celverwijzing die wordt gebruikt voor het berekenen van het eindpunt van het bereik, bevindt zich op vele rijen en kolommen. In de voorbeeldafbeelding is het referentieargument de celverwijzing voor de formule zelf, omdat u altijd wilt dat het bereik één cel boven de formule eindigt.
• rijen: Het aantal rijen boven of onder het referentieargument dat wordt gebruikt bij het berekenen van de offset is vereist. Deze waarde kan positief, negatief of op nul zijn ingesteld. Als de locatie van de offset boven de Referentie argument, de waarde is negatief. Als dit lager is, is het argument Rijen positief. Als de offset zich in dezelfde rij bevindt, is het argument nul. In dit voorbeeld begint de offset één rij boven het referentieargument, dus de waarde voor dit argument is een negatieve (-1).
• Cols: Het aantal kolommen links of rechts van het referentieargument dat wordt gebruikt bij het berekenen van de offset is vereist. Deze waarde kan positief, negatief of op nul worden gezet. Als de locatie van de offset zich links van het Reference-argument bevindt, is deze waarde negatief. Als aan de rechterkant, is het argument van Cols positief. In dit voorbeeld bevinden de gegevens die worden getotaliseerd zich in dezelfde kolom als de formule, dus de waarde voor dit argument is nul.

Gebruik de SUM OFFSET-formule voor totale verkoopgegevens

In dit voorbeeld wordt een SUM OFFSET-formule gebruikt om het totaal voor de dagelijkse verkoopcijfers in kolom B van het werkblad te retourneren.

Aanvankelijk was de formule ingevoerd in cel B6 en telde de verkoopgegevens gedurende vier dagen.

De volgende stap is om de SUM OFFSET-formule op een rij te verplaatsen om plaats te maken voor het verkooptotaal van de vijfde dag. Dit wordt bereikt door een nieuwe rij 6 in te voegen, die de formule naar rij 7 verplaatst.

Als gevolg van de verplaatsing wordt het argument Referentie automatisch in cel bijgewerktB7 en voegt cel toeB6 tot het bereik gesommeerd door de formule.

De SUM OFFSET-formule invoeren

1. Klik op cel B6, de locatie waar de resultaten van de formule in eerste instantie worden weergegeven.
2. Klik op deformules tab van het lintmenu.
3. KiezenMath & Trigvan het lint om de vervolgkeuzelijst met functies te openen.
4. Klik opSOM in de lijst om het dialoogvenster van de functie te openen.
5. Klik in het dialoogvenster op deNummer 1lijn.
6. Klik op cel B2 om deze celverwijzing in te voeren in het dialoogvenster. Deze locatie is het statische eindpunt voor de formule.
7. Klik in het dialoogvenster op deNummer 2lijn.
8. Voer de volgende OFFSET-functie in: OFFSET (B6, -1,0) om het dynamische eindpunt voor de formule te vormen.
9. Klik OK om de functie te voltooien en het dialoogvenster te sluiten.

Het totaal verschijnt in cel B7.

De verkoopgegevens van de volgende dag toevoegen

Om de verkoopgegevens van de volgende dag toe te voegen:

1. Klik met de rechtermuisknop op de rijkop voor rij 6 om het contextmenu te openen.
2. Klik in het menu opinvoegen om een ​​nieuwe rij in het werkblad in te voegen.
3. Als gevolg hiervan gaat de SUM OFFSET-formule omlaag naar cel B7 en is rij 6 nu leeg.
4. Klik op cel A6.
5. Voer het nummer in5 om aan te geven dat het verkoopbedrag voor de vijfde dag wordt ingevoerd.
6. Klik op cel B6.
7. Typ het nummer$1458.25 en druk op deinvoeren toets op het toetsenbord.

Cel B7 updates voor het nieuwe totaal van$7137.40.

Wanneer u op cel B7 klikt, de bijgewerkte formule= SOM (B2: OFFSET (B7, -1,0)) verschijnt in de formulebalk.

De OFFSET-functie heeft twee optionele argumenten: hoogte en breedte , die in dit voorbeeld niet werden gebruikt.

Deze argumenten kunnen worden gebruikt om de functie OFFSET de vorm van de uitvoer te geven in termen van zoveel rijen hoog en zo veel kolommen breed.

Door deze argumenten weg te laten, gebruikt de functie standaard de hoogte en breedte van het argument Reference, dat in dit voorbeeld één rij hoog en één kolom breed is.