Ein Leser hat mir diese Woche ein interessantes Formelproblem geschickt, also dachte ich, ich würde es als Formelherausforderung teilen. Das Problem ist folgendes:
Sie haben eine feste monatliche Zahlung, ein Startdatum und eine bestimmte Anzahl von Monaten. Welche Formel können Sie verwenden, um die Gesamtzahlungen pro Jahr basierend auf dem folgenden Arbeitsblatt zu summieren:
Mit anderen Worten, welche Formel funktioniert in E5, kopiert nach I5, um eine Summe für jedes angezeigte Jahr zu erhalten?
Ich habe mir selbst eine Formel ausgedacht, aber ich würde auch gerne Ihre Ideen sehen. Wenn Sie interessiert sind, hinterlassen Sie einen Kommentar mit der von Ihnen vorgeschlagenen Formel.
Sie können die folgenden benannten Bereiche in Ihrer Formel verwenden, wenn Sie möchten: mos (C5), Menge (C6), Start (C7), Ende (C8).
Sie können das Arbeitsblatt unten herunterladen.
Antwort (zum Vergrößern anklicken)So viele tolle Formeln! Vielen Dank an alle, die sich die Zeit genommen haben, eine Antwort einzureichen. Im Folgenden sind meine ausführlichen Gedanken zum Problem und einige der folgenden Lösungen aufgeführt.
Hinweis: Ich habe nie geklärt, wie mit Monatsgrenzen umgegangen werden soll. Ich folgte nur einem anderen Arbeitsblatt als Beispiel. Die wichtigsten Informationen sind 30 Zahlungen, beginnend am 1. März: 10 Zahlungen im Jahr 2017, 12 Zahlungen im Jahr 2018 und 8 Zahlungen (der Restbetrag) im Jahr 2019.
Wenn Sie also Schwierigkeiten haben zu verstehen, wie Sie versuchen könnten, ein solches Problem zu lösen, konzentrieren Sie sich zunächst auf Zahlungen. Sobald Sie wissen, wie viele Zahlungen ein Jahr hat, können Sie diese Zahl einfach mit dem Betrag multiplizieren und fertig.
Wie können Sie also die Anzahl der Zahlungen in einem bestimmten Jahr ermitteln? In den Kommentaren unten finden Sie viele gute Ideen. Es gibt mehrere Muster, die mir aufgefallen sind, und ich habe unten einige aufgelistet. Dies ist eine laufende Arbeit …
Designmuster
IF + AND/OR + YEAR + MONTH
IF ist in so vielen Formeln ein vertrauenswürdiger Standby-Modus und wird in vielen der vorgeschlagenen Formeln verwendet, um herauszufinden, ob das interessierende Jahr "in Grenzen" des Start- und Enddatums liegt. In vielen Fällen wird IF mit ODER oder UND kombiniert, um die Formeln kompakt zu halten.
IFERROR + DATEDIF + MAX + MIN
DATEDIF kann die Differenz zwischen zwei Daten in Monaten zurückgeben. Daher besteht die Idee darin, MAX und MIN (der Kürze halber anstelle von IF) zu verwenden, um ein Start- und Enddatum für jedes Jahr zu berechnen, und DATEDIF die Monate dazwischen abrufen zu lassen. DATEDIF gibt einen # NUM-Fehler aus, wenn das Startdatum nicht unter dem Enddatum liegt. Daher wird IFERROR verwendet, um den Fehler abzufangen und Null zurückzugeben. Siehe Formeln von 闫 强, Arun und David unten.
MAX + MIN + YEAR + MONTH
Die Formeln von Robert und Peter erledigen fast die gesamte Arbeit mit MAX und MIN, ohne dass ein IF in Sicht ist. Tolle. Wenn Ihnen die Idee, MAX und MIN als Ersatz für IF zu verwenden, neu ist, wird in diesem Artikel das Konzept erläutert.
DAYS360
Die Excel DAYS360-Funktion gibt die Anzahl der Tage zwischen zwei Daten basierend auf einem 360-Tage-Jahr zurück. Es ist eine Methode zur Berechnung von Monaten basierend auf der Idee, dass jeder Monat 30 Tage hat.
SUM + DATE
Dies ist mein ineffizienter (aber eleganter!) Ansatz, bei dem die DATE-Funktion und eine Array-Konstante mit einer Zahl für jeden Monat verwendet werden. Die DATE-Funktion dreht ein Datum für jeden Monat eines Jahres mithilfe einer Array-Konstante und die Boolesche Logik wird verwendet, um die Überlappung zu überprüfen.
