☰

So führen Sie in Excel eine laufende Bilanz von Zahlungen und Rechnungen

tag-icon • tag-icon microsoft-excel worksheet-function
So führen Sie in Excel eine laufende Bilanz von Zahlungen und Rechnungen

Ich habe eine Zellenzeile mit 12 Monatsspalten, G4 (Januar) bis R4 (Dezember), in die ich den für eine monatliche Rechnung gezahlten Betrag eingebe.[Beachten Sie, dass in einem früheren Kommentar mehrere Jahre in mehreren Zeilen besprochen wurden. Dies ist jedoch nicht mehr der Fall; es handelt sich nur um ein einzelnes Jahr in Zeile 4.]

Wenn für die Rechnung ein fälliger Betrag angegeben ist, handelt es sich um einen Standardbetrag, dessen Wert in Zelle F4 gespeichert ist.

Wenn für einen Monat kein Betrag fällig ist, lasse ich die zugehörige Zelle G4:R4 leer. Wenn ein Betrag fällig ist, trage ich den gezahlten Betrag (null, wenn nichts) in die zugehörige Zelle ein.

Ich möchte, dass in Zelle A4 die laufende Summe des ausstehenden Betrags angezeigt wird (fällige Beträge abzüglich gezahlter Beträge). Beispiel: Die monatliche Rechnung beträgt 100,00 (F4). Ich habe im Januar 75 Dollar bezahlt (G4), also beträgt die laufende Summe, die ich noch schulde, 25,00 (A4). Ich habe im Februar 50 Dollar für die Februarrechnung bezahlt (H4), also schulde ich jetzt 75 (A4).

Beachten Sie, dass es nie vorkommen wird, dass kein Rechnungsbetrag vorhanden ist, ich aber auf einen früheren Saldo zahle. Wenn ein früherer Saldo vorhanden ist, wird immer der Standardbetrag in Rechnung gestellt. Eine leere Zelle in G4:R4 bedeutet also, dass nichts in Rechnung gestellt wurde. Ein Wert in G4:R4 bedeutet, dass der Standardbetrag in Rechnung gestellt und der eingegebene Betrag bezahlt wurde.

Antwort1

In A4 einfach ausgedrückt:

=$F4*COUNT($G4:$R4)-SUM($G4:$R4)

BEARBEITEN: Nach Ihrem Kommentar wurden einige „$“-Zeichen entfernt, sodass die Formel einfach in die 15 benötigten Zeilen kopiert werden kann.

Antwort2

In den Kommentaren haben Sie angegeben, dass der Zellbereich für Ihre Monate G4:R4 ist.

Jetzt fürDeine Antwort:

Die Formel, die Sie in Ihr laufendes Ergebnis (A4) einsetzen möchten, lautet:

=WENN(SUMME($G4:$R4) < $F$4*ANZAHL2($G4:$R4), $F$4*ANZAHL2($G4:$R4) - SUMME($G4:$R4), 0)

Lassen Sie uns das aufschlüsseln undAnalysieren Sie die Teile:

ANZAHL2($G4:$R4)

COUNTA ist die Abkürzung für „Count All“. Es zählt die Anzahl der nicht leeren Zellen im Bereich G4:R4. Das liegt daran, dass Sie sagten, die Formel müsse Leerzeichen ignorieren. Außerdem wird so festgehalten, wie viele Monate „vergangen“ sind.

F4*ANZAHL2($G4:$R4)

Dies ist der Gesamtbetrag, der geschuldet wird. (Monatliche Zahlung)*(Anzahl der Monate)

SUMME($G4:$R4)

Dies ist der Gesamtbetrag, der gezahlt wurde. Standardmäßig werden die Leerzeichen ignoriert.

Zusammen haben wir also im Grunde

WENN (GesamtBezahlt < GesamtGeschuldet) zeige GesamtGeschuldet - GesamtBezahlt

Sonst wird 0 angezeigt

Antwort3

Hinweis: Diese Antwort wurde vor der Klärung der Frage verfasst und spiegelt nicht den aktuellen Wortlaut wider. Ich lasse sie nur deshalb so stehen, weil die Kommentare die Gründe für die Überarbeitung der Frage dokumentieren.

Es gibt mehrere Antworten, die davon ausgehen, dass Sie jeden Monat etwas bezahlen. Was ist, wenn Sie das nicht tun? Ohne auf Themen wie Strafen oder Verzugsgebühren einzugehen, ist der in Rechnung gestellte Betrag jeden Monat fällig, unabhängig vom Zahlungsverlauf. Das Zählen der Monate mit Zahlungen garantiert keine korrekte Antwort. Dazu benötigen Sie eine Methode, um anzugeben, wie viele Rechnungen es gegeben hat.

Ihr Abrechnungszeitraum weist möglicherweise Besonderheiten auf und der Referenzzeitraum könnte in einem früheren Jahr liegen. Wenn die Berechnung der Anzahl der Abrechnungszyklen bis heute nicht einfach ist, könnte diese Berechnung eine weitere Superuser-Frage sein. Um es für dieses Beispiel einfach zu halten, sagen wir, Januar ist Abrechnungszeitraum 1 und Sie sind nicht daran interessiert, an welchem ​​Tag des Monats die Rechnung fällig ist.

In diesem einfachen Beispiel könnte A4 Folgendes enthalten:

=$F$4*MONTH(TODAY())-SUM(G4:R4)

Ich gehe davon aus, dass Sie A4 für das nächste Jahr nach A5 kopieren, wenn Sie Werte in G5:R5 verwenden. Einige Hinweise:

  • Wenn sich der Standardrechnungsbetrag von Jahr zu Jahr ändern kann, verankern Sie F4 nicht mit Dollarzeichen. Jede Zeile hätte einen relevanten F-Spaltenwert.
  • Wenn sich der Standardabrechnungsbetrag im Laufe des Jahres ändern kann, benötigen Sie eine kompliziertere Formel.
  • Diese Formel geht davon aus, dass im Dezember alles ausgeglichen ist und nichts ins nächste Jahr übertragen wird. Wenn das nicht der Fall ist, müssen Sie den Jahresendsaldo in eine Zelle eingeben (logischerweise am Anfang des nächsten Jahres) und diesen in Ihre Summe einbeziehen.

verwandte Informationen