Ich habe eine Tabelle, die die Funktion „Summewenn“ verwendet und die Monate in Datumsbereichen zählt.
=SUMMEWENNS(Betrag;DPIF;">=1.1.2021";DPIF;"<=31.1.2021")
Diese Formel wiederholt sich zwölf Mal (für jeden Monat) und mit dem Wechsel von 2021 auf 2022 muss ich jetzt die Datumsbereiche (Jahresbereiche) in allen zwölf Formeln manuell ändern.
Ich habe versucht, nur das Jahr (das 2021 in der Formel) zu ändern, um auf eine andere Zelle zu verweisen, aber Excel erkennt es nicht mehr als Datum.
Wie kann ich dafür sorgen, dass meine Tabelle auch von einem Jahr auf das nächste nutzbar bleibt?
Antwort1
Ansatz 1
Anstatt Start-/Enddaten in Ihren SUMIFS
Funktionen fest zu codieren, generieren Sie sie mithilfe von Excel-Formeln und verweisen Sie dann auf die Zellen, die diese berechneten Datumswerte enthalten. DieDATE
UndEOMONTH
Funktionen können wie im Screenshot unten gezeigt verwendet werden.
Ihre SUMIFS
Formel für Januar kann dann wie folgt geändert werden:
=SUMIFS(Amount;DPIF;">="&Sheet1!B3;DPIF;"<="&Sheet1!C3)
Für den Monat Januar Sheet1!B3
enthält die Zelle das Startdatum des Monats und Sheet1!C3
das Enddatum. Ändern Sie diese Zellbezüge dorthin, wo Sie Ihre berechneten Datumswerte ablegen. Für Februar lauten die Start-/Enddatumzellen Sheet1!B4
und Sheet1!C4
, für März Sheet1!B5
und Sheet1!C5
usw. Wenn Ihre 12 Monatsformeln SUMIFS
als Bereich von 12 Zeilen mal 1 Spalte angeordnet sind, können Sie die Januar-Formel in die nächsten 11 Monate kopieren.
Die Und-Zeichen (&-Zeichen) in den Formeln verketten jeweils einen Vergleichsoperator mit dem Wert in der referenzierten Zelle, ">="&Sheet1!B3
ist also in der geänderten Formel effektiv gleichbedeutend mit ">=01/01/2024"
weil Sheet1!B3
liefert den Datumswert 01.01.2024.
Um das Jahr zu ändern, ändern Sie einfach den Jahreswert in der entsprechenden Zelle - Zelle Sheet1!B1
im Screenshot. Ihre 12 Monatsformeln SUMIFS
beziehen sich alle automatisch auf die Start-/Enddaten des neuen Jahres, sodass die Formeln nicht mehr bearbeitet werden müssen.
Mit diesem Ansatz lässt sich auch das Monatsende-Datum für Februar in Schaltjahren verwalten (wie im Screenshot in Zelle gezeigt C4
).
Ansatz 2
DPIF
Ein alternativer Ansatz besteht darin, die Datumswerte in Monatszahlenwerte umzuwandeln, indem Sie Ihren vorhandenen Amount/DPIF
Bereichen einen zusätzlichen Bereich hinzufügen. Dazu verwenden Sie denMONTH
funktionieren als
=MONTH(date)
wobei date
eine Datumszelle im DPIF
Bereich darstellt.
Wenn Sie diese monatlichen Werte aufrufen MPIF
, können Sie Formeln verwenden wie
=SUMIFS(Amount;MPIF;1)
=SUMIFS(Amount;MPIF;2)
...
=SUMIFS(Amount;MPIF;12)
ohne sich überhaupt Gedanken über das Jahr machen zu müssen.
Dieser monatliche Ansatz setzt voraus, dass Ihre Datenbereiche nicht mehrere Jahre umfassen. Andernfalls werden die Daten für alle Januar-, Februar- und anderen Jahre summiert, was möglicherweise nicht das ist, was Sie möchten.
In diesem Fall besteht eine Verfeinerung der Lösung darin, den Daten in einem Bereich Jahreswerte hinzuzufügen, YPIF
indem manYEAR
Funktion, um diese Werte zu erhalten. Sie würden dann ein zweites Kriterium zu den SUMIFS
Formeln hinzufügen, um diese Jahreswerte einzuschließen, zum Beispiel
=SUMIFS(Amount;MPIF;1;YPIF;2024)
Das bedeutet natürlich, dass Sie jedes Jahr immer noch 12 Formeln ändern müssen, auch wenn die Änderungen etwas einfacher sind als die, die Sie derzeit haben. Um dies zu vermeiden, platzieren Sie den Jahreswert in einer eigenen Zelle und verweisen Sie darauf, indem Sie eine Formel wie diese verwenden:
=SUMIFS(Amount;MPIF;1;YPIF;Sheet1!$B$1)
Ansatz 3
Nicht unbedingt zu empfehlen, aber der Vollständigkeit halber aufgeführt. Ändern Sie Ihre Formeln in
=SUMIFS(Amount;DPIF;">=01/01/"&Sheet1!$B$1;DPIF;"<=31/01/"&Sheet1!$B$1)
=SUMIFS(Amount;DPIF;">=01/02/"&Sheet1!$B$1;DPIF;"<=28/02/"&Sheet1!$B$1)
usw
Hierbei handelt es sich um eine einmalige Bearbeitungsaufgabe, die für die Jahre 2021, 2022 und 2023 funktioniert, jedoch dazu führt, dass der 29.02.2024 aus der Monatssumme für Februar im Jahr 2024 wegfällt, sofern niemand daran denkt, eine Änderung vorzunehmen, und die möglicherweise im Jahr 2025 Probleme verursacht, wenn sie nicht wieder zurückgeändert wird.
Antwort2
- Tragen Sie die beiden vollständigen Daten (nicht nur das Jahr) jeweils in eine separate Zelle ein. Ersetzen Sie dann in Ihrer Formel den Datumswert durch Verweise auf die Zellen
- Um alle Formeln auf einmal zu ändern, führen Sie in Ihrem Blatt einen Vorgang „Alles ersetzen“ aus.
- Ändern Sie im nächsten Jahr nur die Datumswerte in den beiden separaten Zellen