Excel SUM und OFFSET Formula

Wenn Ihr Excel-Arbeitsblatt Berechnungen enthält, die auf einem sich ändernden Zellbereich basieren, vereinfacht die Verwendung der Funktionen SUMME und OFFSET in einer SUM OFFSET-Formel die Aufgabe, die Berechnungen auf dem neuesten Stand zu halten.

Erstellen Sie einen Dynamikbereich mit den Funktionen SUM und OFFSET

Wenn Sie Berechnungen für einen Zeitraum verwenden, der sich ständig ändert, z. B. das Summieren des Umsatzes für den Monat, können Sie mit der Funktion OFFSET in Excel einen dynamischen Bereich einrichten, der sich ändert, wenn die Umsatzzahlen jedes Tages hinzugefügt werden.

An sich kann die SUM-Funktion normalerweise das Einfügen neuer Datenzellen in den zu summierenden Bereich berücksichtigen.

Eine Ausnahme tritt auf, wenn die Daten in die Zelle eingefügt werden, in der sich die Funktion aktuell befindet.

Im obigen Beispielbild werden die neuen Verkaufszahlen für jeden Tag am Ende der Liste hinzugefügt, sodass die Gesamtsumme jedes Mal um eine Zelle nach unten verschoben wird, wenn die neuen Daten hinzugefügt werden.

Wenn die SUMME-Funktion alleine zum Summieren der Daten verwendet würde, müsste der Zellbereich, der als Argument der Funktion verwendet wird, jedes Mal geändert werden, wenn neue Daten hinzugefügt werden.

Durch die Verwendung der Funktionen SUMME und OFFSET wird der Bereich, der summiert wird, jedoch dynamischer. Mit anderen Worten, es ändert sich, um neue Datenzellen aufzunehmen. Das Hinzufügen neuer Datenzellen verursacht keine Probleme, da der Bereich mit dem Hinzufügen jeder neuen Zelle weiter angepasst wird.

Syntax und Argumente

In der Abbildung oben finden Sie weitere Informationen zu diesem Tutorial.

In dieser Formel wird die SUM-Funktion verwendet, um den als Argument angegebenen Datenbereich zu summieren. Der Startpunkt für diesen Bereich ist statisch und wird als Zellbezug auf die erste Zahl identifiziert, die durch die Formel summiert wird.

Die OFFSET-Funktion ist in der SUM-Funktion verschachtelt und wird zum Erstellen eines dynamischen Endpunkts für den durch die Formel summierten Datenbereich verwendet. Dies wird erreicht, indem der Endpunkt des Bereichs auf eine Zelle oberhalb der Position der Formel festgelegt wird.

Die Syntax der Formel:

= SUMME (Bereichsanfang: OFFSET (Referenz, Zeilen, Spalten))

• Bereichsanfang: Der Ausgangspunkt für den Zellbereich, der durch die SUMME-Funktion summiert wird, ist erforderlich. Im Beispielbild ist dies die Zelle B2.
• Referenz: Die für die Berechnung des Endpunkts des Bereichs erforderliche Zellenreferenz befindet sich viele Zeilen und Spalten entfernt. Im Beispielbild ist das Referenzargument die Zellenreferenz für die Formel selbst, da der Bereich immer eine Zelle oberhalb der Formel beenden soll.
• Reihen: Die Anzahl der Zeilen über oder unter dem Referenzargument, das zur Berechnung des Offsets verwendet wird, ist erforderlich. Dieser Wert kann positiv, negativ oder auf Null gesetzt werden. Wenn die Position des Versatzes über der liegt Referenz Argument ist der Wert negativ. Wenn es unten ist, ist das Argument Rows positiv. Wenn sich der Versatz in derselben Zeile befindet, ist das Argument Null. In diesem Beispiel beginnt der Versatz eine Zeile oberhalb des Reference-Arguments, daher ist der Wert für dieses Argument negativ (-1).
• Cols: Die Anzahl der Spalten links oder rechts des Referenzarguments, die zur Berechnung des Offsets verwendet werden, ist erforderlich. Dieser Wert kann positiv, negativ oder auf Null gesetzt werden. Wenn sich die Position des Versatzes links vom Reference-Argument befindet, ist dieser Wert negativ. Wenn rechts, ist das Argument Cols positiv. In diesem Beispiel befinden sich die Daten, die summiert werden, in derselben Spalte wie die Formel, daher ist der Wert für dieses Argument Null.

Verwenden der SUM OFFSET-Formel zur Summe der Verkaufsdaten

In diesem Beispiel wird eine SUM OFFSET-Formel verwendet, um die Summe der täglichen Verkaufszahlen in Spalte B des Arbeitsblatts zurückzugeben.

Anfangs 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 eine Zeile nach unten zu verschieben, um Platz für den Gesamtumsatz des fünften Tages zu schaffen. Dies geschieht durch Einfügen einer neuen Zeile 6, wodurch die Formel in Zeile 7 nach unten verschoben wird.

Als Ergebnis der Verschiebung aktualisiert Excel das Referenzargument für die Zelle automatischB7 und fügt Zelle hinzuB6 auf den durch die Formel summierten Bereich.

Eingabe der SUM OFFSET-Formel

1. Klicken Sie auf Zelle B6. Dies ist der Ort, an dem die Ergebnisse der Formel anfänglich angezeigt werden.
2. Klicke auf dasFormeln Registerkarte des Menübandes.
3. WählenMathe & TrigÜber die Multifunktionsleiste können Sie die Dropdown-Liste der Funktionen öffnen.
4. Klicke aufSUMME in der Liste, um das Dialogfeld der Funktion anzuzeigen.
5. Klicken Sie im Dialogfeld auf die SchaltflächeNummer 1Linie.
6. Klicken Sie auf Zelle B2 um diese Zellenreferenz in das Dialogfeld einzugeben. Dieser Speicherort ist der statische Endpunkt für die Formel.
7. Klicken Sie im Dialogfeld auf die SchaltflächeNummer 2Linie.
8. Geben Sie die folgende OFFSET-Funktion ein: OFFSET (B6, -1,0) um den dynamischen Endpunkt für die Formel zu bilden.
9. Klicken OK um die Funktion abzuschließen und das Dialogfeld zu schließen.

Die Summe erscheint in Zelle B7.

Hinzufügen der Verkaufsdaten für den nächsten Tag

Um die Verkaufsdaten des nächsten Tages hinzuzufügen:

1. Klicken Sie mit der rechten Maustaste auf den Zeilenkopf für Zeile 6, um das Kontextmenü zu öffnen.
2. Klicken Sie im Menü aufEinfügen eine neue Zeile in das Arbeitsblatt einfügen.
3. Als Ergebnis wird die SUM OFFSET-Formel in Zelle B7 nach unten verschoben und Zeile 6 ist jetzt leer.
4. Klicken Sie auf Zelle A6.
5. Geben Sie die Nummer ein5 um anzuzeigen, dass die Verkaufssumme für den fünften Tag eingegeben wird.
6. Klicken Sie auf Zelle B6.
7. Geben Sie die Nummer ein$1458.25 und drücken Sie dieEingeben Taste auf der Tastatur.

Zelle B7 wird auf die neue Summe von aktualisiert$7137.40.

Wenn Sie auf Zelle B7 klicken, wird die aktualisierte Formel angezeigt= SUMME (B2: OFFSET (B7, -1,0)) erscheint in der Bearbeitungsleiste.

Die OFFSET-Funktion hat zwei optionale Argumente: Höhe und Breite , die in diesem Beispiel nicht verwendet wurden.

Diese Argumente können verwendet werden, um der OFFSET-Funktion die Form der Ausgabe in Bezug auf so viele Zeilen hoch und so viele Spalten breit zu machen.

Durch das Weglassen dieser Argumente verwendet die Funktion standardmäßig die Höhe und Breite des Reference-Arguments, das in diesem Beispiel eine Zeile hoch und eine Spalte breit ist.