Wenn Ihr Excel-Arbeitsblatt Berechnungen enthält, die auf einem sich ändernden Zellbereich basieren, verwenden Sie die SUMME- und OFFSET-Funktionen zusammen in einer SUMME-OFFSET-Formel, um die Aufgabe zu vereinfachen, die Berechnungen auf dem neuesten Stand zu h alten.
Die Anweisungen in diesem Artikel gelten für Excel für Microsoft 365, Excel 2019, Excel 2016, Excel 2013 und Excel 2010.
Dynamikbereich mit den SUM- und OFFSET-Funktionen erstellen
Wenn Sie Berechnungen für einen Zeitraum verwenden, der sich ständig ändert - wie z. B. die Bestimmung der Verkäufe für den Monat -, verwenden Sie die OFFSET-Funktion in Excel, um einen dynamischen Bereich einzurichten, der sich ändert, wenn die Verkaufszahlen jedes Tages addiert werden.
Die SUM-Funktion selbst kann normalerweise das Einfügen neuer Datenzellen in den zu summierenden Bereich aufnehmen. Eine Ausnahme tritt auf, wenn die Daten in die Zelle eingefügt werden, in der sich die Funktion gerade befindet.
Im Beispiel unten werden die neuen Verkaufszahlen für jeden Tag am Ende der Liste hinzugefügt, wodurch die Gesamtzahl jedes Mal, wenn die neuen Daten hinzugefügt werden, kontinuierlich um eine Zelle nach unten verschoben wird.
Um diesem Tutorial zu folgen, öffnen Sie ein leeres Excel-Arbeitsblatt und geben Sie die Beispieldaten ein. Ihr Arbeitsblatt muss nicht wie im Beispiel formatiert sein, aber achten Sie darauf, die Daten in die gleichen Zellen einzugeben.
Wenn nur die SUM-Funktion verwendet wird, um die Daten zu summieren, müsste der als Funktionsargument verwendete Zellbereich jedes Mal geändert werden, wenn neue Daten hinzugefügt werden.
Durch die gemeinsame Verwendung der Funktionen SUMME und OFFSET wird der summierte Bereich dynamisch und ändert sich, um neue Datenzellen aufzunehmen. Das Hinzufügen neuer Datenzellen verursacht keine Probleme, da sich der Bereich weiter anpasst, wenn jede neue Zelle hinzugefügt wird.
Syntax und Argumente
In dieser Formel wird die SUM-Funktion verwendet, um den als Argument gelieferten Datenbereich zu summieren. Der Startpunkt für diesen Bereich ist statisch und wird als Zellbezug auf die erste zu summierende Zahl durch die Formel gekennzeichnet.
Die OFFSET-Funktion ist in die SUM-Funktion eingebettet und erstellt einen dynamischen Endpunkt für den Datenbereich, der durch die Formel summiert wird. Dies wird erreicht, indem der Endpunkt des Bereichs auf eine Zelle über der Position der Formel gesetzt wird.
Die Formelsyntax lautet:
=SUM(Range Start:OFFSET(Reference, Rows, Cols))
Die Argumente sind:
- Range Start: Der Startpunkt für den Zellbereich, der von der SUM-Funktion summiert wird. In diesem Beispiel ist der Ausgangspunkt Zelle B2.
- Referenz: Die erforderliche Zellreferenz, die zur Berechnung des Bereichsendpunkts verwendet wird. Im Beispiel ist das Argument Referenz der Zellbezug für die Formel, da der Bereich eine Zelle über der Formel endet.
- Rows: Die Anzahl der Zeilen über oder unter dem Reference-Argument, das zur Berechnung des Offsets verwendet wird, ist erforderlich. Dieser Wert kann positiv, negativ oder auf Null gesetzt werden. Wenn die Offset-Position über dem Reference-Argument liegt, ist der Wert negativ. Wenn der Offset darunter liegt, ist das Rows-Argument positiv. Wenn sich der Offset in derselben Zeile befindet, ist das Argument Null. In diesem Beispiel beginnt der Offset eine Zeile über dem Reference-Argument, sodass der Wert für das Argument minus eins (-1) ist.
- Cols: Die Anzahl der Sp alten links oder rechts vom Reference-Argument, die zur Berechnung des Offsets verwendet werden. Dieser Wert kann positiv, negativ oder auf Null gesetzt werden. Wenn sich die Offset-Position links vom Reference-Argument befindet, ist dieser Wert negativ. Wenn der Offset rechts ist, ist das Cols-Argument positiv. In diesem Beispiel befinden sich die summierten Daten in derselben Sp alte wie die Formel, daher ist der Wert für dieses Argument Null.
Verwenden Sie die SUM OFFSET-Formel, um Verkaufsdaten zu summieren
In diesem Beispiel wird eine SUM-OFFSET-Formel verwendet, um die Gesamtsumme für die in Sp alte B des Arbeitsblatts aufgelisteten täglichen Verkaufszahlen zurückzugeben. Zunächst wurde die Formel in Zelle B6 eingegeben und summierte die Verkaufsdaten für vier Tage.
Der nächste Schritt besteht darin, die SUM OFFSET-Formel um eine Zeile nach unten zu verschieben, um Platz für den Gesamtumsatz des fünften Tages zu schaffen. Dies wird erreicht, indem eine neue Zeile 6 eingefügt wird, wodurch die Formel in Zeile 7 verschoben wird.
Infolge des Verschiebens aktualisiert Excel automatisch das Referenzargument in Zelle B7 und fügt Zelle B6 zu dem durch die Formel summierten Bereich hinzu.
- Wählen Sie die Zelle B6, an der die Formelergebnisse anfänglich angezeigt werden.
-
Wählen Sie die Registerkarte Formeln der Multifunktionsleiste.
Image -
Choose Math & Trig.
Image -
Auswahl SUMME.
Image -
Platzieren Sie im Dialogfeld Funktionsargumente den Cursor im Textfeld Zahl1.
-
Wählen Sie im Arbeitsblatt die Zelle B2 aus, um diesen Zellbezug in das Dialogfeld einzugeben. Dieser Ort ist der statische Endpunkt für die Formel.
Image - Platzieren Sie im Dialogfeld Funktionsargumente den Cursor im Textfeld Zahl2.
-
Geben Sie OFFSET(B6, -1, 0) ein. Diese OFFSET-Funktion bildet den dynamischen Endpunkt für die Formel.
Image -
Wählen Sie OK, um die Funktion abzuschließen und das Dialogfeld zu schließen. Die Summe erscheint in Zelle B6.
Image
Umsatzdaten des nächsten Tages hinzufügen
So fügen Sie die Verkaufsdaten des nächsten Tages hinzu:
- Klicken Sie mit der rechten Maustaste auf die Zeilenüberschrift für Zeile 6.
-
Wählen Sie Einfügen, um eine neue Zeile in das Arbeitsblatt einzufügen. Die SUM OFFSET-Formel bewegt sich eine Zeile nach unten zu Zelle B7 und Zeile 6 ist jetzt leer.
Image - Wählen Sie die Zelle A6 und geben Sie die Zahl 5 ein, um anzuzeigen, dass der Gesamtumsatz für den fünften Tag eingegeben wird.
-
Zelle B6 auswählen, $1458.25 eingeben und dann Enter. drücken
Image - Cell B7 wird auf den neuen Gesamtbetrag von 7137,40 $ aktualisiert.
Wenn Sie Zelle B7 auswählen, erscheint die aktualisierte Formel in der Bearbeitungsleiste.
=SUMME(B2:OFFSET(B7, -1, 0))
Die OFFSET-Funktion hat zwei optionale Argumente: Height und Width, die in diesem Beispiel nicht verwendet wurden. Diese Argumente teilen der OFFSET-Funktion die Form der Ausgabe in Bezug auf die Anzahl der Zeilen und Sp alten mit.
Indem diese Argumente weggelassen werden, verwendet die Funktion stattdessen die Höhe und Breite des Reference-Arguments, das in diesem Beispiel eine Zeile hoch und eine Sp alte breit ist.
