Ich habe die folgende Formel in der Zelle M8:
{=SUM((MOD(ROW(M$16:$M977)-ROW($M$16),4)=0)*(M$16:$M977))}
Mit dieser Formel erhalte ich die Gesamtzahl der Stunden pro Woche in Spalte M aus jeder vierten Zeile beginnend mit M16, also m16+m20+m24+m28 usw. Ich füge die neuen Daten immer oben hinzu, sodass ich beim Öffnen der Tabelle die aktuellsten Informationen oben habe. Wenn ich die vier neuen Zeilen für meine neuen Daten hinzufüge, sieht die Formel folgendermaßen aus:
{=SUM((MOD(ROW(M$20:$M977)-ROW($M$20),4)=0)*(M$20:$M977))}
Wie kann ich erreichen, dass mit der Addition immer am M16 begonnen wird, da dies meine neue Gesamtstundenzahl für die neue Woche ist?
Antwort1
Tragen Sie den Wert von M16 am Ende der Datei oder in eine Zelle ein, die Sie nicht verschieben! Tatsächlich wird jedes Mal, wenn Sie eine Zeile hinzufügen, automatisch jeder Verweis auf die Zelle geändert!
Antwort2
Zunächst sollten Sie dies in eine SUMPRODUCTFormel umwandeln. Sie müssen es nicht als Array-Formel eingeben und es ist meiner Meinung nach einfacher zu verwenden.
=SUMPRODUCT(M16:M977*(MOD(ROW(M16:M977)-ROW(M16),4)=0))
Um das Verhalten von Excel beim Einfügen von Zeilen zu umgehen, empfehle ich, Ihre Daten in eine Tabelle umzuwandeln. (Menüband „Einfügen“ > „Tabelle“) Dadurch wird das Verweisen auf „alle Daten in diesem Bereich“ ganz einfach und die Tabelle wird wie gewünscht aktualisiert, wenn sie größer oder kleiner wird. In diesem Fall würde die Formel etwa so aussehen:
=SUMPRODUCT(Table1[Hours]*(MOD(ROW(Table1[Hours])-ROW(Table1[[#Headers],[Hours]])-1,4)=0))
Wenn Sie es aus irgendeinem Grund nicht in eine Tabelle umwandeln können, empfehle ich, einen benannten Bereich zu erstellen und stattdessen darauf zu verweisen. Die Formel für den benannten Bereich wäre:
=OFFSET(Sheet3!$M$1,15,0,MATCH(9E+99,Sheet3!$M:$M)-15)
... und die darauf verweisende Formel wäre:
=SUMPRODUCT(rngHours*(MOD(ROW(rngHours)-ROW(OFFSET(rngHours,0,0,1,1)),4)=0))
... wobei rngHourssich der Name befindet, den Sie dem benannten Bereich gegeben haben.
Wenn Sie wirklich keine benannten Bereiche verwenden möchten, können Sie das Ganze in eine große Formel packen, aber das wird unübersichtlich, obwohl es eine Funktion ist:
=SUMPRODUCT(OFFSET(Sheet3!$M$1,15,0,MATCH(9E+99,Sheet3!$M:$M)-15)*(MOD(ROW(OFFSET(Sheet3!$M$1,15,0,MATCH(9E+99,Sheet3!$M:$M)-15))-ROW(OFFSET(Sheet3!$M$1,15,0)),4)=0))