Als uw Excel-werkblad berekeningen bevat die zijn gebaseerd op een veranderend celbereik, gebruikt u de functies SUM en OFFSET samen in een SUM OFFSET-formule om de taak van het up-to-date houden van de berekeningen te vereenvoudigen.
Instructies in dit artikel zijn van toepassing op Excel voor Microsoft 365, Excel 2019, Excel 2016, Excel 2013 en Excel 2010.
Creëer een dynamisch bereik met de functies SUM en OFFSET
Als u berekeningen gebruikt voor een periode die voortdurend verandert - zoals het bepalen van de verkoop voor de maand - gebruik dan de OFFSET-functie in Excel om een dynamisch bereik in te stellen dat verandert wanneer de verkoopcijfers van elke dag worden toegevoegd.
Op zichzelf kan de SOM-functie gewoonlijk het invoegen van nieuwe gegevenscellen in het bereik dat wordt opgeteld mogelijk maken. Een uitzondering treedt op wanneer de gegevens worden ingevoegd in de cel waar de functie zich momenteel bevindt.
In het onderstaande voorbeeld worden de nieuwe verkoopcijfers voor elke dag onder aan de lijst toegevoegd, waardoor het totaal telkens één cel naar beneden moet schuiven als de nieuwe gegevens worden toegevoegd.
Om deze zelfstudie te volgen, opent u een leeg Excel-werkblad en voert u de voorbeeldgegevens in. Uw werkblad hoeft niet te worden opgemaakt zoals in het voorbeeld, maar zorg ervoor dat u de gegevens in dezelfde cellen invoert.
Als alleen de functie SOM wordt gebruikt om de gegevens op te tellen, moet het celbereik dat als functieargument wordt gebruikt, worden gewijzigd telkens wanneer nieuwe gegevens worden toegevoegd.
Door de functies SUM en OFFSET samen te gebruiken, wordt het opgetelde bereik dynamisch en verandert het om plaats te bieden aan nieuwe gegevenscellen. Het toevoegen van nieuwe cellen met gegevens veroorzaakt geen problemen omdat het bereik zich blijft aanpassen naarmate elke nieuwe cel wordt toegevoegd.
Syntaxis en argumenten
In deze formule wordt de SOM-functie gebruikt om het gegevensbereik dat als argument is opgegeven te totaliseren. Het startpunt voor dit bereik is statisch en wordt geïdentificeerd als de celverwijzing naar het eerste getal dat door de formule moet worden opgeteld.
De OFFSET-functie is genest in de SUM-functie en creëert een dynamisch eindpunt voor het gegevensbereik dat door de formule wordt opgeteld. 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 is:
=SUM(Bereik Start:OFFSET(Referentie, Rijen, Kollen))
De argumenten zijn:
- Bereik Start: Het startpunt voor het bereik van cellen dat wordt opgeteld door de SOM-functie. In dit voorbeeld is het startpunt cel B2.
- Referentie: De vereiste celverwijzing die wordt gebruikt om het bereikeindpunt te berekenen. In het voorbeeld is het argument Referentie de celverwijzing voor de formule omdat het bereik één cel boven de formule eindigt.
- Rows: 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 worden gezet. Als de offset-locatie zich boven het referentieargument bevindt, is de waarde negatief. Als de offset 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 het argument is negatief één (-1).
- Cols: Het aantal kolommen links of rechts van het referentieargument dat wordt gebruikt om de offset te berekenen. Deze waarde kan positief, negatief of op nul worden gezet. Als de offset-locatie zich links van het referentieargument bevindt, is deze waarde negatief. Als de offset naar rechts is, is het Cols-argument positief. In dit voorbeeld staan de gegevens die worden opgeteld in dezelfde kolom als de formule, dus de waarde voor dit argument is nul.
Gebruik de SUM OFFSET-formule voor totale verkoopgegevens
Dit voorbeeld gebruikt een SUM OFFSET-formule om het totaal te retourneren voor de dagelijkse verkoopcijfers die in kolom B van het werkblad worden vermeld. Aanvankelijk werd de formule ingevoerd in cel B6 en werden de verkoopgegevens gedurende vier dagen opgeteld.
De volgende stap is om de SUM OFFSET-formule een rij naar beneden te verplaatsen om ruimte te maken voor het verkooptotaal van de vijfde dag. Dit wordt bereikt door een nieuwe rij 6 in te voegen, die de formule verplaatst naar rij 7.
Als gevolg van de verplaatsing werkt Excel automatisch het referentieargument bij naar cel B7 en voegt cel B6 toe aan het bereik dat wordt opgeteld door de formule.
- Selecteer cel B6, dit is de locatie waar de formuleresultaten in eerste instantie worden weergegeven.
-
Selecteer de Formules tab van het lint.
Image -
Kies Math & Trig.
Image -
Selecteer SUM.
Image - In het Functieargumenten dialoogvenster, plaats de cursor in het Number1 tekstvak.
-
Selecteer cel B2 in het werkblad om deze celverwijzing in het dialoogvenster in te voeren. Deze locatie is het statische eindpunt voor de formule.
Image - Plaats in het Functieargumenten dialoogvenster de cursor in het Number2 tekstvak.
-
Voer OFFSET(B6, -1, 0) in. Deze OFFSET-functie vormt het dynamische eindpunt voor de formule.
Image -
Selecteer OK om de functie te voltooien en het dialoogvenster te sluiten. Het totaal verschijnt in cel B6.
Image
Voeg de verkoopgegevens van de volgende dag toe
Om de verkoopgegevens van de volgende dag toe te voegen:
- Klik met de rechtermuisknop op de rijkop voor rij 6.
-
Selecteer Insert om een nieuwe rij in het werkblad in te voegen. De formule SUM OFFSET gaat één rij omlaag naar cel B7 en rij 6 is nu leeg.
Image - Selecteer cel A6 en voer het nummer 5 in om aan te geven dat het verkooptotaal voor de vijfde dag wordt ingevoerd.
-
Selecteer cel B6, voer $1458,25 in en druk vervolgens op Enter.
Image - Cell B7-updates naar het nieuwe totaal van $7137,40.
Wanneer u cel B7 selecteert, verschijnt de bijgewerkte formule in de formulebalk.
=SOM(B2:OFFSET(B7, -1, 0))
De functie OFFSET heeft twee optionele argumenten: Hoogte en Breedte, die in dit voorbeeld niet werden gebruikt. Deze argumenten vertellen de OFFSET-functie de vorm van de uitvoer in termen van het aantal rijen en kolommen.
Door deze argumenten weg te laten, gebruikt de functie in plaats daarvan de hoogte en breedte van het argument Referentie, dat in dit voorbeeld één rij hoog en één kolom breed is.
