Lückenanalyse Ausgangsrechnungen

Sehr geehrte Kolleginnen und Kollegen,
im Rahmen der laufenden Buchhaltung soll zukünftig Teil der Buchhaltungsbearbeitung und somit bereits unterjährig die Belegprüfung daraufhin erfolgen, ob Ausgangsrechnungsnummern nicht vergeben worden sind. Da ACL, welches eine Lückenanlayse vorsieht, nicht im Einsatz die Frage, wie dieses zu lösen ist. Eine Standardabfrage in Rechnungswesen pro habe ich zu der Fragestellung nicht gefunden. Über eine Idee für die Durchführung dieser Prüfung bin ich daher dankbar.
Viele Grüße
Martin Zerwer, Steuerberater

Hallo Herr Zerwer,
diese Analyse habe ich auch schon vermisst. Ich habe mir mit Excel beholfen. Konto 1200 Forderungen sortiert nach Belegnummer in Excel exportieren. Spalte mit Formel "Bel.Nr. - vorige Bel.Nr.", 2. Spalte mit Wenn-Dann-Formel (wenn Differenz größer 1, prüfen, sonst ok).
Viele Grüße, Anja Vogl

Anstatt mit der Wenn/Dann Funktion zu arbeiten kann man sich die Excel-Liste auch so zurecht biegen, dass von der ersten Rechnung die letzte Ziffer der Rechnungsnummer mit der letzten Ziffer der Zeilennummer übereinstimmt. Dann prüft man alle paar Bildschirme ob das kontinuierlich so ist, ansonsten hat man entweder eine Lücke (dort setzt man eine Leerzeile ein) oder eine mehrfache Vergabe bzw. Stornierung (bis auf eine Zeile diese löschen).

.. oder Sie fügen eine zusätzliche Spalte ein, in der Sie die ersten beiden Zellen mit 1 und 2 füllen und dann mit der Autoausfüllen-Funktion die restlichen Nummern ergänzen. Dann noch eine Spalte mit der Differenz zwischen lfd Nr und RechNr .. und dann filtern mit "scharfem Hinschauen" oder Excel Filter.

oder in einer Hilfsspalte einfach einen Startwert (1. Rechnungsnummer) eingeben, diese Spalte mit Reihe füllen (+1) und anschließend Spalte 1 mit Spalte 2 subtrahieren (Spalte 3). Da wo <> 0 ist, liegt eine Lücke vor. Dorthin springen und einfach eine Zelle in Spalte 2 einfügen (verschieben nach unten) und schon kann weiter geprüft werden. Das alles kann man natürlich noch automatisieren, so dass in der Spalte 2 (Hilfsspalte) dort wo die Lücken sind, eine oder mehrere Rechnungen fehlen.

Viele Wege führen nach Rom.

Gruß A. Martens

Hallo Herr Zerwer,

ich prüfe ob Rechnungsnummern fehlen im Pro über die Buchungsübersicht.
Es wird hierzu in der ersten Zeile folgendes eingestellt:

Belegfeld-gleich-von (hier wird die erste Rechnungsnummer erfasst)- bis (hier die letzte)
in der Zweiten Zeile erfasse ich:
Gegenkonto/Konte-gleich- erstes Erlöskonto-letztes Erlöskonto

Vielleicht konnte ich Ihnen hiermit helfen.

Gruß
W. Hunsen

Falls Interesse besteht könnte ich hier auch ein Excelmakro online stellen, der automatisch alle Rechnungsnummern auf Lückenlosigkeit prüft.

Ich habe mir gedacht dass für jede Lücke einfach an dieser Stelle eine Leerzeile eingefügt wird, so kann man sofort erkennen ob und wieviele Rechnungen fehlen.

Ist keine große Sache so etwas. ;-)

Beispiel:

12345
12346
...
12348
usw.

Gruß A. Martens

.. oder die Datev erkennt, dass eine Lückenanalyse nicht mehr nur zum Handwerkszeug von Wirtschaftsprüfern gehört und intergriert diese Modul in das bestehende Modul Fibu-Überwachung (dort gehört es nämlich hin, seitdem auch die Betriebsprüfer so etwas an Bord haben...)

Ich hatte mal mit der Fibu-Software "GDI" zu tun. Da war die Lückenanalyse mindestens in 2004 (vermutlich sogar schon 10 Jahre länger) schon Standard... :-|

Da es nun einmal bei DATEV diese Analysefunktion nicht gibt, wird diese einfach in Excel nachgerüstet:
Starten Sie den VBA-Editor mit Alt+F11 und kopiert diese Sub in ein Modul in der Personal.xlsb. Wird ein globales AddIn verwendet, kann man diese auch dort speichern.

Hier die Funktion:

'<Code>

Sub FindeFehlendeRgNr()
Dim myRangeS As Range, myRangeT As Range
Dim lResult As Long
Dim i As Long, q As Long
Dim lRow As Long
Dim lCol As Long
Dim lRgNrS As Long
Dim lRgNrT As Long
Dim lStartRow As Long

lRow = Selection.Rows.Count
lCol = Selection.Column

lStartRow = Selection.Row
If ActiveSheet.Rows.Count = lRow Then 'Ganze Spalte markiert
lStartRow = lStartRow + 2
End If

For i = lStartRow To (Selection.Row + lRow - 1)

Set myRangeS = Cells(i, lCol)
Set myRangeT = Cells(i + 1, lCol)

'1. Prüfung ob Leerzelle = Ende der Spaltenmarkierung
If myRangeS.Value = "" Then Exit Sub

lResult = (Val(myRangeT.Value) - Val(myRangeS.Value)) - 1 'Diff. 1 = normal

If lResult > 0 Then
For q = 1 To lResult
myRangeT.Insert xlShiftDown
Next
i = i + lResult
End If

Next

End Sub

'</Code>

Funktionsweise:

Einfach das gewünschte Forderungskonto exportieren und die Suchspalte markieren (Spaltenkopf).
Soll nur ein Teilbereich geprüft werden, dann einfach nur die entsprechenden Zellen in der Prüfspalte markieren. In dem Zellbereich dürfen keine Leerzellen enthalten sein und die Spalte muss natürlich aufsteigend sortiert sein. Ferner wurde nicht geprüft wenn Zeilen unsichtbar sind oder mit Zellengruppierung formatiert sind.

Gefunden und geprüft werden:

a) echte Zahlen => 123456
b) Zahlen & Buchstaben => 123456/2010 => wird zu 123456 (s. auch Funktionweise Val())

nicht gefunden und geprüft werden:

Buchstabe & Zahlen & Buchstabe => 111-123456/2010, oder Rg_123456

Bei gemischten Rechnungsnummern sind alle Rechnungen in ein einheitliches Format überzuleiten.
Hierfür könnte man noch die Funktion erweitern mittels Left, Right und Mid (s. OH)., oder aber man arbeitet in so einem Fall mit einer Hilfsspalte, die geprüft wird.

Aber für alle 'normalen' Rechnungsnummern sollte es einwandfrei funktionieren.
Meine ersten Tests haben einwandfrei funktioniert. Ich übernehme aber keine Haftung für Schäden etc.!

Bei Fragen bitte hier einfach melden. Ich helfe gerne, aber ein Anspruch auf Support gibt es nicht. ;-)

Gruß A. Martens

Hallo Leute,

ein kleiner Fehler hatte sich eingeschlichen, und zwar wurde nur eine Zelle und nicht eine ganze Zeile eingefügt.

Hier nun die mit einem echten Export eines DATEV-Forderungskonto getestete Prozedur:

'<Code>

Sub FindeFehlendeRgNr()
Dim myRangeS As Range, myRangeT As Range
Dim lResult As Long
Dim i As Long, q As Long
Dim lRow As Long
Dim lCol As Long
Dim lStartRow As Long

10 On Error GoTo FindeFehlendeRgNr_Error

20 lRow = Selection.Rows.Count
30 lCol = Selection.Column

40 lStartRow = Selection.Row
50 If ActiveSheet.Rows.Count = lRow Then 'Ganze Spalte markiert
60 lStartRow = lStartRow + 2
70 End If

80 For i = lStartRow To (Selection.Row + lRow - 1)

90 Set myRangeS = Cells(i, lCol)
100 Set myRangeT = Cells(i + 1, lCol)

'1. Prüfung ob Leerzelle = Ende der Spaltenmarkierung
110 If myRangeS.Value = "" Then Exit Sub

120 lResult = (Val(myRangeT.Value) - Val(myRangeS.Value)) - 1 'Diff. 1 = normal

130 If lResult > 0 Then
140 For q = 1 To lResult
150 Rows(myRangeT.Row).Insert xlShiftDown
160 Next
170 i = i + lResult
180 End If

190 Next

200 On Error GoTo 0
210 Exit Sub

FindeFehlendeRgNr_Error:

220 MsgBox "Fehlernr.: " & Err.Number & " (" & Err.Description & ") in Prozedur FindeFehlendeRgNr von Modul mdlDATEV_Tools", , "Fehler in Zeile: " & Erl

End Sub

'</Code>

Ergänzung Vorgehensweise:

1.) in gefilterte Excellisten kann nicht geprüft werden.
2.) Man gehe wie folgt vor:
a.) Filter setzen mit Gegenkonto <> Erlöskonten (z. B. Skto., Bank usw.)
b.) Filterergebnis löschen (= nur noch echte Erlösbuchungen vorhanden)
c.) ggf. Aufbereitung Belegfeld1 (s. auch vorherigen Beitrag)

Achtung:
Rechnungsnummern müssen aufsteigend sortiert sein.
Eventuell sind auch noch nicht zu prüfende Rechnungsnummern zu löschen:
Beispiel:
08/2015
09/2015
10/2015
15/2014 (Nachbuchung in 2015 f. 2014)

=> Ergebnis: Es würden 4 Leerzeilen eingefügt werden, da nur die ersten Zahlenfolgen geprüft werden.
Also bitte selber darauf achten.

Gruß A. Martens

Vielen Dank für die Vorlage :)

Habe es gleich bei einem Mandanten getestet und es funktioniert echt prima.
Ich fand es aber auf Grund der hohen Anzahl von Rechnungen schwierig die Lücken zu erkennen.

Ich habe für mich noch eine Zeile nach 150 ergänzt, damit die fehlende Zeile rot eingefärbt wird.

Rows(myRangeS.Row + 1).Interior.Color = vbRed

Vielen Dank nochmal

BG

Tegin Hubl

Hallo Frau Hubl,

ich hatte zuerst überlegt, es mit noch mehr Komfort zu belegen, so z. B. dass automatisch die Zeilen nach bestimmten Kriterien gelöscht werden. Ferner auch noch eine Bereinigung des Belegfeldes1, indem man den zu löschenden Teil definiert. Das wäre aber so komplex geworden, dass das hier den Rahmen sprengen würde. Daher habe ich mich auf diese einfache Variante zurück besonnen. Die Sub-Routine ist einfach gehalten, so dass dieser auch "leicht" zu begreifen ist. Die Datenbereinigung bzw. -aufbereitung ist dann mit Excel-Bordmitteln in relativer kurzer Zeit zu bewerkstelligen (Filterfunktion).

Ich weiß auch nicht, wie wichtig so eine Funktion ist und ob sich dann auch die ganze Mühe wirklich lohnen würde.

Gruß A. Martens