Tim hat ein Arbeitsblatt, in dem Zelle B1 die Formel = SUMME (A1: A7) enthält.

Er möchte diese Formel nach unten kopieren und den Bereich um 7 Zeilen erhöhen lassen, damit Zelle B2 die Formel = SUMME (A8: A14), Zelle B3 = SUMME (A15: A21) usw. enthält. Das Problem ist das Wenn er es nach unten kopiert, wird jedes „Ende“ des Bereichs nur um 1 erhöht, wobei es um 7 erhöht werden sollte, um seinen Bedarf zu decken. Er fragt sich, wie er Excel dazu bringen kann, das richtige Inkrementieren durchzuführen.

Sie können Excel nicht dazu bringen, das richtige Inkrementieren durch Kopieren und Einfügen durchzuführen. es wird es einfach nicht tun. Der Grund ist einfach: Es gibt Zeiten, in denen das Erhöhen um 1 aus formelhafter Sicht sinnvoll ist. Da Excel Ihre Gedanken nicht lesen kann (zumindest bis zur nächsten Version:>)), wird davon ausgegangen, dass es nur um 1 erhöht werden sollte.

Die Lösung besteht darin, Ihre Formel zu ändern. Mit einigen Arbeitsblattfunktionen können Sie Excel den gewünschten Bereich für die Summierung erstellen lassen. Betrachten Sie das folgende Beispiel einer Formel, die die gewünschte Summe liefert:

=SUM(INDIRECT("A" & (ROW()-1)7+1 & ":A" & (ROW()-1)7+7))

Wenn Sie diese Formel in Zelle B1 einfügen, funktioniert dies, da die Zeilennummer (von der ROW-Funktion zurückgegeben) der Zeile angezeigt wird, in der die Formel enthalten ist. Da es sich in Zeile 1 befindet, wird die Formel auf folgende Weise von Excel ausgewertet:

=SUM(INDIRECT("A" & (ROW()-1)7+1 & ":A" & (ROW()-1)7+7))

=SUM(INDIRECT("A" & (1-1)7+1 & ":A" & (1-1)7+7))

=SUM(INDIRECT("A" & 07+1 & ":A" & 07+7))

=SUM(INDIRECT("A" & 0+1 & ":A" & 0+7))

=SUM(INDIRECT("A" & 1 & ":A" & 7))

=SUM(INDIRECT("A1:A7"))

=SUM(A1:A7)

Was Sie in B1 erhalten, ist die gewünschte Summe. (Die INDIRECT-Funktion verwendet den Wert in der Zeichenfolge so, als wäre es ein realer Bereich. Dies ist genau das, was Sie möchten.) Wenn Sie die Formel in die Spalte kopieren und die Zeilennummer erhöht, liefert die Formel an beiden Enden die richtigen Inkremente von 7 des Bereichs.

Es gibt andere Variationen dieser Technik, die Sie verwenden können. Der einzige Unterschied besteht darin, dass die Variationen unterschiedliche Arbeitsblattfunktionen verwenden, um dieselbe Aufgabe auszuführen. Beispielsweise verwendet die folgende Variante weiterhin die ROW-Funktion, stützt sich jedoch letztendlich auf die OFFSET-Funktion, um den gewünschten Bereich zu berechnen:

=SUM(OFFSET(A1,((ROW()-1)6),0):OFFSET(A7,((ROW()-1)6),0))

Ein kürzerer Ansatz, der OFFSET verwendet, lautet wie folgt:

=SUM(OFFSET($A$1,ROW()*7-7,0,7,1))

Unabhängig vom Ansatz können Sie wahrscheinlich feststellen, dass die Idee darin besteht, eine Formel zu erstellen, die die Zeile verwendet, in der die Formel angezeigt wird, um den Bereich zu erstellen, den Sie wirklich möchten. In jedem der bisherigen Beispiele wird davon ausgegangen, dass Sie in Zelle B1 beginnen. Wenn Sie in Zelle B2 beginnen möchten, müssen Sie die Formeln ändern, um die Zeile zu berücksichtigen, mit der Sie beginnen. Um Ihnen nur eine Vorstellung davon zu geben, wie dies funktioniert, wenn Sie stattdessen in Zelle B2 beginnen, werden die drei in diesem Tipp dargestellten Formeln folgendermaßen geändert:

=SUM(INDIRECT("A" & (ROW()-2)7+2 & ":A" & (ROW()-2)7+8))

=SUM(OFFSET(A2,((ROW()-2)6),0):OFFSET(A8,((ROW()-2)6),0))

=SUM(OFFSET($A$2,(ROW()-1)*7-7,0,7,1))

Beginnen Sie an einem anderen Ort, und Sie müssen weitere Änderungen an der von Ihnen ausgewählten Formel vornehmen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (8387) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Inkrementieren von Referenzen um Vielfache beim Kopieren von Formeln.