Zahlungskontrolle
Die Aufgabe
Eine Auflistung (Liste) von Rechnungen soll überprüft werden, ob das Fälligkeitsdatum bereits überschritten ist oder nicht. Alle überfälligen Rechnungen sollen in irgend einer Form deutlich angezeigt werden.
Hinweise
Für Übungszwecke und zur besseren Nachvollziehbarkeit haben wir Ihnen diese Datei zum Download bereit gestellt. Nach dem öffnen des Files sehen Sie 20 Rechnungen mit unterschiedlichen Daten. Ihnen wird auffallen, dass das Rechnungsdatum durchaus in zeitlicher Nähe zum aktuellen Datum und keineswegs älter als 45 Tage ist. Das haben wir u.a. per Zufallsfunktion so geregelt. Ähnlich verhält es sich mit weiteren Feldern. Wenn Sie diese Hintergründe nicht so sehr interessieren, dann gehen Sie einfach direkt zum Thema Vorbereitungen und gleich danach geht es mit den genau damit und den verschiedenen Lösungsmöglichkeiten weiter.
Rechn.Datum
Bei all diesen Erklärungen nutzen wir die Zeile 2 zur Analyse und Darstellung der Formeln. Es versteht sich, dass die jeweilige Formel stets nach unten kopiert wird. Die Formel für das Rechnungsdatum: =HEUTE()-ZUFALLSBEREICH(0;45)
sorgt dafür, dass dort ein zufälliges Datum eingetragen wird, welches 0 bis 45 Tage vor dem aktuellen Tag liegt. Die Funktion HEUTE() gibt immer den aktuellen Tag zurück. Mit der Funktion ZUFALLSBEREICH() wird eine zufällige Zahl zurückgegeben, welche welche durch die beiden Argumente 0 und 45 begrenzt wird. Lesen Sie hier mehr dazu.
Rechn.Nr.
Eine Rechnungsnummer wird ja vielfach so erstellt, dass die ersten beiden Ziffern das Jahr repräsentieren und dann folgt eine (beispielsweise) vierstellige Nummer. Diese hier verwendeten Rechnungsnummern können nicht der Realität entsprechen, weil es in Deutschland zwingend vorgeschrieben ist, dass die Rechnungen eine fortlaufende Nummer haben müssen und keine undokumentierten Lücken existieren dürfen. Beachten Sie das bitte bei Verwendung der verschiedenen Funktionen.
Damit die Nummern halbwegs praxisnah sind, Sie aber dennoch etwas von den verschiedenen Excel-Funktionen profitieren können, haben wir etwas getrickst:
=RECHTS(JAHR(HEUTE());2)&TEXT(MONAT(B2);"00")&TEXT(TAG(B2)+ZUFALLSBEREICH(0;68);"00")
Analyse: RECHTS(JAHR(B2) gibt die beiden letzten Ziffern des Jahres der Rechnungserstellung zurück. Durch das &TEXT(MONAT(B2);"00") wird der Monat des Rechnungsdatums als zweistellige Ziffernfolge in einer Zeichenkette (Text) ausgegeben, also beispielsweise "04" für den April. Mit &TEXT(TAG(B2)+ZUFALLSBEREICH(0;68);"00") verhält es sich ähnlich. Zu dem Tag des Rechnungsdatums (auch zweistellig) wird eine Zahl zwischen 0 und 68 dazu addiert. Warum gerade 68? Nun, damit wird gewährleistet, dass die letzten beiden Ziffern höchstens 99 sind, also immer zweistellig: 68 plus 31 (Tage) = 99. 🙂 Und das Zeichen & verkettet die einzelnen Elemente der verschiedenen Funktionen.
ZielTage
Verschiedene Kunden haben ein unterschiedliches Zahlungsziel, also die Anzahl der Tage, bis die Rechnung fällig ist. Auch hier habe ich das Zufallsprinzip walten lassen:
=WAHL(ZUFALLSBEREICH(1;4);5;10;21;30)
Mit der Funktion WAHL() wird aus den Argumenten, welche dem ersten folgen, ein bestimmtes ausgewählt und als Funktionsergebnis zurückgegeben. Welche Position gewählt wird, bestimmt das erste Argument, das hier eine Zufallszahl zwischen 1 und der Anzahl der folgenden Argumente (hier: 4) ist.
ZielDatum
Das bedarf prinzipiell keiner Erklärung. Zum Rechnungsdatum werden die ZielTage hinzu addiert. Natürlich wird der jeweilige Zufallswert, welcher sich nach jeder Neuberechnung der Tabelle ändert, als Grundlage der Berechnung genutzt.
Vorbereitung
Wir empfehlen Ihnen, die herunter geladene Datei jetzt, bevor Sie etwas mit der Mappe anfangen, unter einem anderen Namen zu speichern. Das hat den Vorteil, dass Sie auch zu einem späteren Zeitpunkt die Möglichkeit haben, mit dem dann aktuellen Datum zu arbeiten. Ein weiterer Grund: Damit Sie sich nicht stets mit neuen Werten „herumschlagen” müssen, sollten Sie nach dem speichern die Formeln in Werte umwandeln. Entweder Sie gehen einen Ihnen bekannten und vertrauten Weg oder Sie gehen beispielsweise so vor:
- Markieren Sie den kompletten Datenbereich
- Bearbeiten | Kopieren, bzw. über Rechtsklick im Kontextmenü Kopieren oder die Tastenkombination StrgC
- Einfügen | Werte oder über das Kontextmenü die Werte einfügen.
Damit ist dann gewährleistet, dass diese Zahlen sich nicht mehr verändern und Sie können in Ruhe und ohne Irritationen an den unterschiedlichen Lösungen arbeiten.
Lösung 1
Übersicht
Bei dieser Lösung handelt es sich um eine einfache Bedingte Formatierung. Automatisch werden alle Rechnungen, die überfällig sind, farblich hervorgehoben. Roter Hintergrund und der besseren Lesbarkeit wegen weiße Schrift. Das Ganze könnte dann so aussehen:
Sehr einfache bedingte Formatierung
Hier noch einmal der Hinweis, dass Sie mit Sicherheit andere Zahlen und Daten haben werden, weil wegen der Zufallsberechnungen immer neue Daten kreiert werden. Aber das Prinzip zählt hier mehr als der Vergleich der Daten.
Vorgehensweise
Und auch hier nochmals der Hinweis, dass Sie sich wesentlich leichter tun, wenn Sie mit statischen Daten arbeiten, also die gesamten Daten in Werte umgewandelt haben.
Gehen Sie folgendermaßen vor, um zum Ziel zu kommen:
- Markieren Sie die (reinen) Daten, also A2:F21
- Achten Sie darauf, dass A2 die aktive Zelle ist, also (normalerweise) mit einem helleren Hintergrund als die anderen markierten Zellen.
- Klicken Sie auf die Schaltfläche Bedingte Formatierung und wählen Sie dort den Menüpunkt Neue Regel…
- Im folgenden Fenster aktivieren Sie den Punkt Formel zur Ermittlung der zu formatierenden Zeilen verwenden und tragen Sie folgende Formel ein:
=$2<=HEUTE()
Eingabe der Formel für die bedingte Formatierung
- Klicken Sie nun auf Formatieren… und wählen als Hintergrundfarbe (Ausfüllen) rot und als Schriftfarbe weiß:
Nach Auswahl der Formate für Hintergrund und Schrift
- OK und das Ganze sollte nun so aussehen:
Fenster nach Abschluss der Formatierung
- Jetzt noch einmal OK, dann werden Sie umgehend den Erfolg sehen. Durch die Formatierung werden alle überfälligen Rechnungen sehr deutlich hervorgehoben. Siehe die oberste Abbildung.
Lösung 2
Übersicht
Bei dieser Lösung blenden Sie auf recht einfache Art und Weise alle noch nicht fälligen Rechnungen aus. Sie nutzen eine so genannte Dynamische Tabelle (früherer Name: Liste), wo ausgiebige Filter- und Sortiermöglichkeiten gegeben sind. Das könnte dann beispielsweise so aussehen:
Die fertiggestellte Dynamische Tabelle
Vorgehensweise
Auch wenn es auf den ersten Blick nicht so aussieht, Sie werden rascher und gewiss auch eleganter zum Ziel gelangen als bei der ersten Lösung. Das liegt schon alleine daran, dass die Formatierung vollautomatisch vorgenommen wird.
- Sorgen Sie dafür, dass sie wiederum mit einem unformatierten Datenblatt arbeiten, entsprechend dem Register Rechnungen.
- Klicken Sie irgendwo in die Daten, also in den Bereich A1:F21
- Drücken Sie (ab Windows 7) StrgT oder in früheren Versionen StrgL
Excel wird nun automatisch vorschlagen, in dem zusammenhängenden Bereich eine Dynamische Tabelle zu erstellen:
Bereich für die Dynamische Tabelle übernehmen und bestätigen
- Achten Sie darauf, dass das Kästchen Tabelle hat Überschriften auch mit einem Häkchen versehen ist (das ist Standard).
- Klicken Sie auf OK und machen Sie sich ein wenig mit den Möglichkeiten der Überschriftenzeile vertraut.
- Idealerweise lassen Sie die Spaltenbreite automatisch anpassen.
- Klicken Sie in der Überschriftszeile im Feld ZielDatum auf das Filtersymbol, wählen Datumsfilter und anschließend Vor…:
Erster Schritt zur Auswahl des Datums
- Im folgenden sich auftuenden Fenster geben Sie entweder das gewünschte Datum per Hand ein oder…
Entweder ein beliebiges Datum per Hand eingeben …
- … Sie Klicken auf das kleine Kalendersymbol, damit sich ein Kalender-Steuerelement öffnet:
… Sie wählen das Datum aus dem Steuerelement aus
- Hier ist der aktuelle Tag meist vormarkiert, alternativ genügt ein Klick auf die Schaltfläche Heute. Oder aber Sie wählen durch einen Klick das gewünschte Datum aus.
- OK und das Ergebnis ist sofort sichtbar.
Lösung 3
Übersicht
Die Bedingte Formatierung haben Sie ja schon weiter oben kennen gelernt. Alle überfälligen Rechnungen waren rot markiert. Diese Funktionalität können Sie aber noch mit einem Feintuning versehen. Mindestens drei Farbgebungen sind für (beispielsweise) drei Zeiträume möglich, ab Excel 2007 erheblich mehr.
Vorgehensweise
Angenommen, Sie wollen die ersten zwei Tage Überfälligkeit nicht hervorheben, danach ab 3, 10 und 28 Tagen eine Farbabstufung von hell nach dunkel. Dann gehen Sie genau so vor wie oben beschrieben; Sie beginnen mit dem niedrigsten Wert und arbeiten sich dann hoch. Nach Fertigstellung sieht das bei unserem Muster so aus:
Auflistung aller bedingter Formatierungen des Bereichs
Das Ergebnis sehen Sie im Tabellenblatt Lösung 3. – Natürlich können Sie einen zusätzlichen Filter anwenden, um ausschließlich die entsprechenden Rechnungen anzuzeigen.
Lösung 4
Übersicht
Wenn Sie das Tabellenblatt Lösung 4 öffnen, dann werden Sie gewiss zu Beginn etwas verwirrt sein. Spalten G:H enthalten einen Wahrheitswert, ob die entsprechende Rechnung überfällig ist oder nicht. Die Spalten I:L enthalten offensichtlich alle den gleichen Wert, nämlich die Anzahl der Tage, die eine Rechnung überfällig ist. Teilweise wird die Null angezeigt, teilweise nicht. Und Spalte M enthält entweder ein Datum der ersten Tage des vorherigen Jahrhunderts oder einen „Gartenzaun”.
Hier geht es nur um verschiedene Wege zum Ziel, welches ja immer gleich ist. Kleine Variationen wie beim Wahrheitswert oder das Unterdrücken der Nullwerte sind nur marginal werden aber durchaus diskutiert.
Vorgehensweise
Alle hier aufgeführten Formeln und Funktionen beziehen sich ausschließlich auf die Zeile 2 und sie werden ganz normal nach unten kopiert.
Spalte G,WAHR/FALSCH
Die Formel ist schlicht und einfach: =F2<HEUTE(). Das Ergebnis kann nur WAHR oder FALSCH sein, und genau das ist dann auch das Ergebnis.
Spalte H, Ja/Nein
Diese Formel unterscheidet sich kaum von der vorherigen. Das ist auch ziemlich einleuchtend, denn es soll ja auch nur ein Wahrheitswert ausgegeben werden: =N(F2<HEUTE()). Durch die Funktion N() wird ein WAHR in 1 und ein FALSCH in eine 0 umgewandelt.
Aber warum steht dort statt der Zahlen ein Text? Und das ohne jede WENN()-Funktion? Das ist nur eine Frage des Zahlenformats. Beim benutzerdefinierten Zahlenformat ist folgende Formatierung eingetragen worden:
Nur die Formatierung ist verantwortlich
Eine kurze Erklärung: Die drei ersten Gruppen der Formatierung, jeweils durch Semikola getrennt, stellen das Format für positiv;negativ;null dar. Und da nicht nur Ziffern wiedergegeben werden können, haben wir hier in Anführungszeichen eingefasste Texte verwendet: "- Ja -";(leer);"-Nein-".
Spalte I, WENN()
Auf den ersten Blick scheint diese Formel ganz einfach zu sein:
=WENN(F2<HEUTE();--HEUTE()-F2;"")
… und auf den zweiten Blick werden Sie vielleicht über das doppelte Minus stolpern. Mein Tipp: Probieren Sie es einmal in einer anderen, freien Zelle ohne das Doppelminus aus und finden Sie die Erklärung dann hier im Blog.
Eine kurze Erklärung zum Rest: Wenn das Zieldatum vor dem heutigen Tage liegt, dann berechne HEUTE() minus Zieldatum, sonst gebe einen Leertext zurück. Wegen des sonst-Teils bleibt die Zelle leer, wenn die Zahlung noch nicht im Verzug ist.
Spalte J, Multiplikation [1]
Sie sehen sofort, dass hier auch die Nullwerte angezeigt werden. Die Formel, um das Ziel zu erreichen ist diese:
=N(HEUTE()>F2)*(HEUTE()-F2)
Der Vergleich N(HEUTE()>F2) ergibt 1, wenn HEUTE() größer ist als das Fälligkeitsdatum, ansonsten 0. Dieses Ergebnis wird dann mit der Differenz in Tagen Zieldatum zu heute multipliziert. Angenommen, es sind 5 Tage, dann wird entweder (bei WAHR) 1*5 zurück gegeben, ansonsten 0*5.
Spalte K, MAX()
Eigentlich ist diese Formel genial einfach:=MAX(0;HEUTE()-F2)
Es wird das Maximum der Werte Null auf der einen Seite und den Differenztagen andererseits zurück gegeben. Zum zweiten Argument: Ist die Rechnung erst in der Zukunft fällig, dann ergibt es eine negative Zahl. Ist sie genau heute fällig, dann ist das Ergebnis eine Null. Und ist das Fälligkeitsdatum in der Vergangenheit, dann ist der berechnete Wert positiv, also in jedem Fall größer als 0. Bei negativen Werten ist 0 größer, positive Werte sing immer größer als 0 und bilden somit das Maximum.
Es bleibt die Frage, warum die Nullen nicht angezeigt werden, die positiven Werte aber sehr wohl. Hier ist ein wenig Ihr Engagement gefordert. Schauen Sie sich einmal das Zahlenformat dieser Spalte an und Sie werden gewiss die Ursache finden.
Spalte L, Multiplikation [2]
Erkennen Sie den Unterschied zur ersten Multiplikation? Die N() – Funktion fehlt und der erste Teil ist die umgekehrte Logik.
=(F2<HEUTE())*(HEUTE()-F2)
„Warum nicht gleich so?” Das scheint der ideale Weg zu sein. Nun ja, das Zahlenformat spielt da aber auch eine Rolle. Schauen Sie einfach einmal nach. Oder Sie konzentrieren sich auf die Spalte M, die gar nicht so unähnlich ist …
Spalte M, Einfachste Rechnung
Wirklich erstaunlich, diese Ergebnisse fallen ja wirklich total aus dem Rahmen. Entweder ein über 100 Jahre altes Datum oder ein „Lattenzaun”. Und wodurch wird das ausgelöst?
=HEUTE()-F2
Vielleicht denken Sie nun: „Bei so einer billigen Formel kann ja auch nichts besseres heraus kommen.” Na ja, es ist wirklich die kürzeste, einfachste und meinethalben auch primitivste Formel. Aber sie ist dennoch korrekt. Mit Ihrer Hilfe wird dort in wenigen Sekunden das gleiche Ergebnis sichtbar sein wie in der vorherigen Spalte.
Schritt für Schritt zum Ergebnis. Erst einmal: Warum steht dort teilweise ein Datum und bei den FALSCH-Werten diese Reihe von Raute-Zeichen? Die Antwort ist ganz schlüssig: Excel gibt als (sichtbares) Ergebnis ein Datum zurück, wenn zwei kalendarische Daten berechnet werden. Und die # erscheinen immer, wenn ein negativer Datum- bzw. Zeitwert dargestellt werden müsste. Hinweis: Das gilt nicht für die Mac-Version des Excel, da wird stets der korrekte Wert ausgegeben (wie auch bei den Zeiten). Fazit: Im Bereich M2:M21 stehen kalendarische Daten.
Nun sind Sie dran: Markieren Sie Spalte M und formatieren Sie diese ab Zeile 2 genau so wie Spalte K. Das geht auch sehr schön mit dem Format-Pinsel. Und Sie sehen, das Ergebnis passt. 🙂
Lösung 5
Übersicht
Noch eine recht einfache Bedingte Formatierung. Prinzipiell wie die erste Lösung, nur mit anderen Farben. Der gravierende Unterschied ist, dass Anwender hier in Zelle I1 per Hand ein Datum eingeben und die Bedingte Formatierung dann sofort diesem Wert angepasst wird.
Vorgehensweise
Wie Sie die Bedingung bei der Bedingten Formatierung eingeben, ist bereits in Lösung 1 beschrieben. Bleibt nur noch die Formel, und die ist wirklich kurz, knapp, prägnant:
=$F2<$I$1
Lösung n
Es gibt noch zig weitere Möglichkeiten. Aber das würde hier zu weit führen. Eine besonders interessante Variante kann eine Pivot-Tabelle sein, dazu bedarf es aber einiges an Erfahrung mit Excel. Wenn Sie experimentierfreudig sind, sollten Sie sich damit einmal befassen.
[NachObenLetzte Verweis=„ML: Zahlungskontrolle”]