Excel: So aktualisieren Sie die Summenformel automatisch, wenn eine Spalte hinzugefügt wird, während der gleiche Bereich beibehalten wird

Question

Ich habe derzeit ein Problem mit Excel. Ich versuche, die Summe der jährlichen Kosten zu ermitteln, und diese Excel-Tabelle wird im Laufe des Monats aktualisiert. Das Problem ist, dass ich die jährlichen Kosten problemlos mit der Summenfunktion summieren könnte, aber ich möchte, dass die jährlichen Kosten trotzdem berechnet werden, wenn eine neue Spalte (Monat) und die Kosten in dieser Spalte aktualisiert werden.

So würden sich beispielsweise die jährlichen Kosten im Moment auf die Summe von Juni 2022 bis Juli 2021 belaufen, und im nächsten Monat wären es Juni 2022 bis August 2022. Ich habe mir die Funktion „Summe (indirekt)“ angesehen, aber diese scheint nicht die Tatsache zu berücksichtigen, dass ich die Anfangsspalte aus meiner Formel entfernen muss, wenn eine neue Spalte hinzugefügt wird.

Ich wäre für Ihre Hilfe sehr dankbar, vielen Dank.

Answer 1

Wenn ich möchte, dass eine Summe Spalten enthält, die ich später einfüge, erreiche ich dies normalerweise, indem ich eine leere Spalte zwischen den einzelnen zu summierenden Spalten und der Summenspalte einfüge und diese leere Spalte in den SUM-Bereich einschließe. Wenn ich dann links von der leeren Spalte eine Spalte einfüge, schließt die SUM-Funktion die Zelle in diese neue Spalte ein, da sie innerhalb des Bereichs liegt.

Ein vielseitigerer Ansatz, der diese leere Spalte auch überflüssig macht, ist die Verwendung einer Formel wie der folgenden:

=SUM(A1:OFFSET(K1,0,-1))

In diesem Beispiel soll in Zelle K1 die Summe des Bereichs von A1 bis J1 eingegeben werden. Die OFFSETFunktion arbeitet mit dem Zellbezug und nicht mit dem Wert in der Zelle. OFFSET(cell, y, x)bedeutet die Zellenzeilen ydarunter und xdie Spalten rechts davon cell.

Wenn Sie möchten, dass der Anfang des Summenbereichs nach vorne verschoben wird, können Sie hierfür ebenfalls die OFFSET-Funktion verwenden. Beispielsweise

=SUM(OFFSET(K1,0,-6):OFFSET(K1,0,-1))

würde Ihnen die letzten sechs Spalten geben. Ich bin verwirrt, was genau Sie versuchen zu tun.

Answer 2

Eine Formel wie diese:

=SUMME(INDIREKT(ZELLE("Adresse",OFFSET(A2,0,ANZAHL2(2A:ZZ2)-3))&":"&ZELLE("Adresse",OFFSET(A2,0,ANZAHL2(A2:ZZ2)-1))))

zählt zuerst die Anzahl der Zellen mit Werten in A2:ZZ2
(dadurch COUNTA(A2:ZZ2)wird auch der erste OFFSET()-Parameter auf A2 gesetzt

CELL("address",OFFSET(A2,0,COUNTA(A2:ZZ2)-3))erzeugt einen String mit der Adresse der ZelleZWEISpalten links von der letzten Zelle (mit Wert) im Bereich.(Das -3ist -1im zweiten OFFSET(), damit der letzte Wert gefunden wird)

... und dadurch wird der durch die CELL(...) & ":" & CELL(...)Erstellung eines Zellbereichs gebildete Text erstellt, der den letzten drei Zellen in dieser Zeile entspricht.

INDIRECT()„interpretiert“ dann diesen Zellbereichstext und übergibt ihn an SUM(...), das dann die Berechnung durchführen kann.

Hinweis: Um einen VOLLSTÄNDIGEN Zeilenbereich zu erhalten, verwenden Sie z. B. COUNTA(2:2)für die zweite Zeile.

Verifiziert in LibreOffice, sollte in jeder SpreadSheet-Version funktionieren.
Viele INDIRECT()-Funktionsaufrufe können Ihr Spreadsheet verlangsamen