Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1616to1620
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Kontenabstimmung Soll/Haben in Excel

Kontenabstimmung Soll/Haben in Excel
10.04.2018 18:11:44
Alex
Hallo,
ich arbeite bei einer Bank und bin auf der Suche nach einer Lösung um die Abstimmung eines einzelnen Kontos in Excel zu erleichtern.
Bisher gehen wird folgendermaßen vor:
Zunächst werden alle Eingänge und Ausgänge des Kontos aus SAP in Excel exportiert und anschließend wird nach Währung gefiltert und die Beträge aufsteigend sortiert.
Die Soll Beträge sind positiv und die Beträge im Haben sind negativ.
Ich kopiere mir die negativen Beträge neben die positiven und tausch das Vorzeichen um. Anschließend kann ich mit der "Bedingten Formatierung" und der Funktion "doppelte Werte anzeigen" sehen ob ich zu der Soll Buchung eine passende Haben Buchung habe. Bisher hat uns das vollkommen gereicht, allerdings kommen wir aktuell mit dem Vorgehen nicht weiter, wenn wir sehr viele Datensätze haben. Als Beispiel habe ich 2x eine 10€ Soll Buchung und 5x eine -10€ Haben Buchung, dann zeigt mir die Bedingte Formatierung mit der doppelten Werte Funktion trotzdem bei den fünf Haben Buchungen eine Übereinstimmung an, da ja der gleiche Wert 10€ gefunden wurde, allerdings sehe ich nicht wie oft. Das nächste Problem ist die oft geringe Abweichung, schon bei nur einem Cent Differenz, bekomme ich keine Match.
Ich hoffe, dass mein Problem einigermaßen verständlich ist.
Im Anhang habe ich eine Beispieltabelle mit einigen Datensätzen. Diese Tabellen enthalten an einigen Tagen bis zu 1000 Buchungen und ich sitze dann oft mehrere Stunden daran. In der zweiten Lasche(rot) sieht man das Problem etwas anschaulich geschildert.
Hat vielleicht jemand einen Lösunngsvorschlag oder eine Idee?
Vielen lieben Dank
lg
Alex
Link zur Datei:
https://www.herber.de/bbs/user/120981.xlsx

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Kontenabstimmung Soll/Haben in Excel
10.04.2018 18:20:15
onur
Du solltest mit VBA und den Buchungsnummern arbeiten - solange diese (und die Beträge natürlich) bei Soll und Haben nicht übereinstimmen, wird nicht markiert.
Du solltest mal etwas MIT Buchungsnummern posten.
AW: Warum keine andere Zuordnung
10.04.2018 18:20:42
Fennek
Hallo,
nach einem ersten Blick: Warum wird die Zuordnung nicht über Spalte C "Zuordnung", event. auch Spalte P "Text" gemacht. Dies sollte eindeutiger sein als der Betrag.
mfg
AW: Warum keine andere Zuordnung
10.04.2018 18:46:07
Alex
Hallo, danke für deine Antwort, doch leider ist eine Zuordnung über Spalte P nicht möglich, da der Kontrahent zb bei der Soll Buchung Bank heißt und bei der Haben Buchung BankXXXGB.
Spalte C ist in meiner Beispiel Datei abgeändert, aber auch damit ist kein Vergleich Möglich, da die Nummern bei Soll ind Haben Buchung unterschiedlich sind. In meinem Beispiel sind sie nur identisch, da dort echte Daten standen und ich sie überschrieben habe.
Anzeige
AW: Warum keine andere Zuordnung
10.04.2018 19:04:54
Alex
Hier ist noch mal eine andere Datei mit einer Soll und der passenden Haben Buchung. Die Buchungsnummern stimmen leider nie überein, genauso wenig wie der Buchungstext.
https://www.herber.de/bbs/user/120982.xlsx
Datei
11.04.2018 01:08:25
KlausF
Hi Alex,
probier mal:
Sub Pruefung()
Dim x As Integer
Dim ToleranzWert As Integer
Dim varBetragH As Currency
Dim varBetragS As Currency
Dim rng As Range
Dim plusNum As Long
Dim minusNum As Long
Dim i As Long
Dim a As Long
Dim minusWert As Long
Dim leere As Long
Dim ToleranzH As Long
Dim lastRow As Long
Dim lastRowHaben As Long
lastRow = Cells(Rows.Count, "K").End(xlUp).Row
lastRowHaben = WorksheetFunction.Match("S", Columns("K"), 0) - 1
plusNum = 1
minusNum = -1
Set rng = Range("A1: Q" & lastRow)
'********************************************* anpassen
ToleranzWert = 5 'Cent nach oben und unten
Application.ScreenUpdating = False
'Sortieren nach Haben-Soll
rng.Sort _
Key1:=Range("K1"), Order1:=xlAscending, _
Key2:=Range("L1"), Order2:=xlAscending, _
Header:=xlYes
'1. Genauer Vergleich
For i = 2 To lastRowHaben
varBetragH = Range("L" & i) * -1
For a = (lastRowHaben + 1) To lastRow
varBetragS = Range("L" & a)
If varBetragH = varBetragS Then
If Range("Q" & a) = "" Then
'Uebereinstimmung
Range("Q" & a) = plusNum
Range("Q" & i) = plusNum
plusNum = plusNum + 1
Exit For
End If
End If
'nicht gefunden
If a = lastRow Then
Range("Q" & i) = minusNum
End If
Next a
Next i
'Sortieren
rng.Sort Key1:=Range("Q1"), Order1:=xlAscending, Header:=xlYes
'2. Toleranzvergleich
minusWert = WorksheetFunction.CountIf(Range("Q2:Q" & lastRow), "-1")
If minusWert = 0 Then GoTo ENDE 'keine H-Werte zum Vergleichen gefunden
leere = WorksheetFunction.CountIf(Range("Q2:Q" & lastRow), "")
If leere = 0 Then GoTo ENDE 'keine Leere zum Vergleichen gefunden
For i = 2 To (minusWert + 1)
varBetragH = (Range("L" & i) * -1) * 100
For a = (lastRow - leere + 1) To lastRow
varBetragS = Range("L" & a) * 100
For x = ToleranzWert * -1 To ToleranzWert
If varBetragH = varBetragS + x Then
Range("Q" & a) = plusNum
Range("Q" & i) = plusNum
plusNum = plusNum + 1
Exit For
End If
Next x
Next a
Next i
'Sortieren
rng.Sort Key1:=Range("Q1"), Order1:=xlAscending, Header:=xlYes
ENDE:
'Range("Q2:Q" & lastRow).ClearContents
Set rng = Nothing
End Sub

Die Soll- und Haben-Daten stehen nach Codeausführung zusammen.
Im Code kann die Toleranzgrenze der "Abweichung" angepasst werden (momentan plus/minus 5 Cent)
Die Spalte Q kann am Ende gelöscht werden. Die nicht verwerteten Werte stehen am Ende der Tabelle
(= ohne Wert in Spalte Q)
https://www.herber.de/bbs/user/120985.xls
Passt es?
Gruß
Klaus
Anzeige
AW: Datei
11.04.2018 08:23:38
Alex
Wow, wie viel Mühe du dir gegeben hast, damit habe ich nicht gerechnet. Ich bin heute außer Haus unterwegs und kann vermutlich erst morgen testen ob es funktioniert. Passend dazu habe ich morgen einen VBA Kurs 😬
1000 Dank schonmal.
AW: Datei
11.04.2018 10:35:24
KlausF
Hallo Alex,
danke für die Rückmeldung.
Ich habe soeben noch ein paar Fehler entdeckt (war schon spät gestern Abend).
Ich schaue heute Abend noch einmal drüber und poste dann die Korrekturen.
Gruß
Klaus
neue Datei
11.04.2018 18:04:24
KlausF
Hall Alex,
hier noch einmal eine überarbeitete Version:
https://www.herber.de/bbs/user/121011.xls
Einzelne Werte und Werte im Toleranzbereich werden gesondert gekennzeichnet.
Der Toleranzwert kann im Makro eingestellt werden
Sub Pruefung()
Dim x As Integer
Dim Toleranz As Integer
Dim varBetragH As Currency
Dim varBetragS As Currency
Dim rng As Range
Dim rngQ As Range
Dim Zelle As Range
Dim lngZaehler As Long
Dim i As Long
Dim a As Long
Dim anzMinus As Long
Dim anzLeere As Long
Dim ToleranzH As Long
Dim lastRow As Long
Dim lastRowHaben As Long
Application.ScreenUpdating = False
'********************************************* anpassen
Toleranz = 5 'Cent nach oben und unten
lastRow = Cells(Rows.Count, "K").End(xlUp).Row
lngZaehler = 1
Set rng = Range("A1: Q" & lastRow)
Set rngQ = Range("Q2: Q" & lastRow)
Range("Q1:Q" & lastRow).ClearContents
Range("Q1") = "Hilfsspalte"
'Sortieren nach Haben-Soll
rng.Sort _
Key1:=Range("K1"), Order1:=xlAscending, _
Key2:=Range("L1"), Order2:=xlAscending, _
Header:=xlYes
lastRowHaben = WorksheetFunction.Match("S", Columns("K"), 0) - 1
'1. Genauer Vergleich
For i = 2 To lastRowHaben
varBetragH = Range("L" & i) * -1
For a = (lastRowHaben + 1) To lastRow
varBetragS = Range("L" & a)
If varBetragH = varBetragS Then
If Range("Q" & a) = "" Then
'Uebereinstimmung
Range("Q" & a) = lngZaehler
Range("Q" & i) = lngZaehler
lngZaehler = lngZaehler + 1
Exit For
End If
End If
'nicht gefunden
If a = lastRow Then
Range("Q" & i) = -1
End If
Next a
Next i
'Sortieren nach Spalte Q
rng.Sort _
Key1:=Range("Q1"), Order1:=xlAscending, _
Key2:=Range("L1"), Order2:=xlAscending, _
Header:=xlYes
'2. Toleranzvergleich
anzMinus = WorksheetFunction.CountIf(rngQ, "-1")
anzLeere = WorksheetFunction.CountIf(rngQ, "")
If anzMinus = 0 Then GoTo ENDE 'keine H-Werte zum Vergleichen gefunden
If anzLeere = 0 Then GoTo ENDE 'keine anzLeere zum Vergleichen gefunden
For i = 2 To (anzMinus + 1)
varBetragH = (Range("L" & i) * -1) * 100
For a = (lastRow - anzLeere + 1) To lastRow
varBetragS = Range("L" & a) * 100
For x = (Toleranz * -1) To Toleranz
If varBetragH = varBetragS + x Then
Range("Q" & a) = lngZaehler & "  Toleranz"
Range("Q" & i) = lngZaehler & "  Toleranz"
lngZaehler = lngZaehler + 1
Exit For
End If
Next x
Next a
Next i
'Leere kennzeichnen
If WorksheetFunction.CountBlank(rngQ) > 0 Then
For Each Zelle In rngQ
If IsEmpty(Zelle) Then
Zelle = "Einzeln"
End If
Next
End If
'Sortieren
rng.Sort Key1:=Range("Q1"), Order1:=xlDescending, Header:=xlYes
ENDE:
'Range("Q1:Q" & lastRow).ClearContents
Set rng = Nothing
Set rngQ = Nothing
Set Zelle = Nothing
End Sub
Gruß
Klaus
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige