PDA

Vollständige Version anzeigen : Verrechnung von Werten aus zwei Spalten


monde
27.09.2016, 16:01
Hallo,

habe wieder ein Problem.

Es sollen Werte aus zwei Spalten (Buchhaltung: Soll Haben) ausgeziffert werden. Tabelle ist im Anhang. Ein Beispiel hier:

Spalte A



5
1
2
4




7 steht in Spalte B

Übrig bleiben müsste: 1 und 4 oder 5

Vielen herzlichen Dank im Voraus
Maria

R J
27.09.2016, 16:32
Hi Maria,

in Deinem Beispiel gibst Du an, " übrig bleiben sollen in diesem Fall : 1 und 2"

verrätst Du uns auch weshalb? Ich kann keine Regel erkennen...

Alles was kleiner ist als das kleinste Soll? Passt aber nicht zum obigen Beispiel...

monde
28.09.2016, 07:00
Hallo,

im Soll stehen ganz, ganz viele Rechnungen (in Euro-Beträgen), im Haben sind ganz, ganz viele Zahlungen, wobei es auch Teilzahlungen gibt. z. b. im Soll steht: 100,23 Euro und im Haben 80,02 Euro und 20,21 Euro. Diese drei Ziffern sollen nicht mehr erscheinen oder markiert werden, ergeben 0. Alles, was sich im Soll und Haben ausgleicht (Soll-Beträge - Haben Beträge= o), soll ausgeziffert werden, z. B. rot markiert werden. Diese Buchungen muss ich in Datev ausziffern d. h. unsichtbar machen. Auf dem Konto bleiben dann nur ein paar Zahlen, Differenzen, unbezahlte Rechnungen, Rechnungen die nicht ganz bezahlt wurden etc.

Wäre super, wenn das Problem mit Excel zu lösen wäre.
Vielen Dank im Voraus.
Maria

Benutzername:
28.09.2016, 07:33
Hallo Mario,

ohne wirklich Ahnung von der Buchhaltung zu haben.
Aber wie willst Du sicher sein die "richtigen" Buchungen zu erwischen?

Selbst bei Deinem genannten Beispiel hast Du ja schon 2 Möglichkeiten wobei nur eine richtig sein würde, oder?

Gruss,
Stephan

monde
28.09.2016, 08:22
Hallo,

es handelt sich um alte Einbuchungenen aus Vorjahren von jeweils einem bestimmten Kunden. Beim Ausziffern werden nur die ausglichen Ziffern unsichtbar, nicht wirklich gelöscht. Deswegen ist es unwichtig, was übrig bleibt. Ausziffern kann man aber nur, wenn die Soll-Beträge minus Haben-Beträge exakt Null ergeben. Man räumt quasi einfach auf.

Herzliche Grüße

Maria

Benutzername:
28.09.2016, 09:39
Moin Maria,

soweit habe ich es wohl verstanden.
Wenn ich diese Logik nun nutze habe ich 2 Fälle:

1. Wenn Soll gleich Haben, dann kann ich alle Posten ausziffern, oder?

2. Falls Soll ungleich Haben, dann kann ich alle Posten ausziffern bis auf einen/mehrere Posten deren Summe die Differenz zwischen den Spalten ist, oder?

Gruss,
Stephan

monde
28.09.2016, 12:12
Hallo Stephan,
da hast du recht. Wenn aber Soll minus Haben 0 ist, brauche ich Excel gar nicht.

Liebe Grüße

Maria

monde
29.09.2016, 07:02
Hallo Stefan,

zu 1.: Wenn Soll = Haben, soll alles ausgeziffert werden.

zu 2.: Es soll so sein, wie du schreibst. Es sollen nur die Posten stehen bleiben, die nicht verrechnet werden können.

Habe dir bei meiner letzten Mail etwas ungenau geantwortet, musste schnell gehen, weil ich in der Arbeit war.

Vielen Dank und viele Grüße

Maria

Benutzername:
29.09.2016, 07:27
Moin Maria,

danke für die Erläuterung, ich denke die Aufgabenstellung ist nun ganz gut definiert.

Ich hatte mir schon Gedanken gemacht wie das elegant gelöst werden könnte.

Könnte mir gut vorstellen das schick und relativ einfach mit einer Rekursion im VBA lösen zu können. Aber Rekursionen machen mir immer Angst ;)

Grob geschätzt, aus wievielen Teilzahlungen kann denn so ein Posten denn maximal bestehen?

Gruss,
Stephan

monde
29.09.2016, 08:59
Hallo Stefan,

Teilzahlungen bestehen meist aus 2 Beträgen. Ich denke, es kommt gar nicht vor, dass in drei Teilbeträgen bezahlt wird.

Viele Grüße
Maria

R J
29.09.2016, 10:52
Hi Maria,

das umständliche zusammenpuzzeln irgendwelcher Teilzahlungsbeträge, dessen Ergebnis letztendlich auch nicht wirklich konkret sondern mehr oder weniger zufällig erfolgt (Bsp.: es gibt einen Teilzahlungsbetrag von 11,23 € und einen Einmalbetrag in gleicher Höhe), bringt aus meiner Sicht nicht viel. Das Problem ist vielmehr tieferliegend. Wenn Ihr zu jedem Betrag auch die Rechnungsnr. oder irgendein anderes eindeutiges Merkmal vermerkt, dann wird da ein Schuh draus. Alles andere ist Kaffeesatzlesen...

monde
29.09.2016, 11:38
Hallo Ralf,

es macht schon einen Sinn. Ich beschreibe dir das ganze, falls es dich interessiert, ganz genau.

Es geht um ein Seniorenheim. Für jeden Bewohner wird ein Debitorenkonto geführt, d. h. alles, was den Bewohner betrifft, landet auf diesem Konto. Es werden Rechnungen im Voraus gestellt an den Bewohner, den Bezirk oder die Krankenkasse. Beim Bezahlen werden oft die Rechnungsnummern nicht angegeben oder es kommt zu Differenzen, weil die Rente erhöht wurde, sich die Pflegestufe geändert hat etc. Dann kommt es zu Nachberechnungen. Letztendlich soll aber alles, was in Rechnung gestellt wurde, beglichen werden. Es bleiben aber oft Differenzen stehen. Diese Differenzen bestehen seit 2011, sind teilweise endlos lang. Die Abschlüsse sind bis 2014 gemacht. Wenn das Konto des Bewohners ausgedruckt wird, erscheint der ganze Salat aus den Vorjahren. Wenn beim Einbuchen und dann beim Zahlen der Rechnungen Nummern angegeben wurden, bucht Datev automatisch aus (sind nicht zu sehen). Von welchen Rechnungen Differenzen da sind, ist quasi egal, alle betreffen sowieso den jeweiligen Bewohner.
Es ist soo mühsam, aus so vielen Beträgen, Beträge herauszufinden, die beim Verrechnen Null ergeben, so habe ich gedacht, mit Excel kann das funktionieren.

Viele Grüße und danke für dein Interesse.

Maria

R J
29.09.2016, 20:10
...ist ja wie Weihnachten.... 'ne schöne Bescherung....;)
Wenn es denn nun mal so ist, und es sowieso wurscht ist welche Beträge mit welchen verrechnet werden, dann würde ich so vorgehen:

Setzt aber voraus, dass Soll in Spalte A, Haben in Spalte B steht und sonst nix in der Tabelle.
1, Wert (egal on soll oder haben, steht in Zeile 2
Spalten G und H sind Hilfsspalten zum sortieren (werden am Ende wieder gelöscht)

Benutzung auf eigene Gefahr.... also, vorher lieber 'ne Kopie anlegen!

Sub Verrechnen()
Dim erg#, lz&, var
Dim rg As Range, shReport As Worksheet
Dim rest#, nrs$

lz = Tabelle1.Cells.SpecialCells(xlCellTypeLastCell).Row

Set shReport = Tabelle1
Set rg = Range("A2:B" & lz)
rg.Copy Destination:=rg.Offset(0, 6)
rg.Offset(0, 6).Sort rg.Columns("H"), xlAscending

erg = WorksheetFunction.Sum(Range("B2:B" & lz)) - WorksheetFunction.Sum(Range("A2:A" & lz))

If erg > 0 Then
For Each var In Range("H2:h" & lz)
If var <> "" Then
erg = erg - var.Value
If erg < 0 Then
Dim rng As Range
Set rng = Columns(2).Find(var.Value, after:=Cells(1, 2))
If Not rng Is Nothing Then
rng = ""
Set rng = Nothing
End If
End If
End If
Next
End If

Set shReport = Nothing
Set rg = Nothing

Columns("G:H").Delete
End Sub

Habe Dein erstes Beispiel als Vorlage genommen. Was mich allerdings wundert, jetzt sind (wenn 1 und 2 bleiben sollen), soll und haben alles andere als ausgeglichen. Sicher, dass das so gewollt ist? Oder geht es nur darum, die noch offenen Beträge zu finden?

monde
30.09.2016, 07:16
Hallo,
vorerst mal danke, werde das ganze morgen mal ausprobieren.

Ziel ist, dass nur Beträge im Soll und Haben stehen bleiben, die beim Subtrahieren (Soll Posten - Haben Posten) nicht exakt Null ergeben.

Noch ein Beispiel:

Soll:
80
11,00
0,01
0,01
90


Haben:
1
79,01
0,99
10,00
1,02


bleiben würde: 90, und 1

Lieben Dank
Maria

R J
30.09.2016, 14:00
...das ist ja wieder anders als am Anfang....

Probier mal das:

Sub Verrechnen()
Dim erg As Double, lz&, varSoll, VarHaben
Dim soll#, haben#, rest As Double

lz = Tabelle1.Cells.SpecialCells(xlCellTypeLastCell).Row

soll = WorksheetFunction.Sum(Range("A2:A" & lz))
haben = WorksheetFunction.Sum(Range("B2:B" & lz))
erg = soll - haben

If erg > 0 Then
For Each varSoll In Range("A2:A" & lz)
For Each VarHaben In Range("B2:B" & lz)
rest = varSoll.Value - VarHaben.Value
If CStr(rest) = CStr(erg) Then
varSoll.Interior.Color = vbRed
VarHaben.Interior.Color = vbRed
GoTo Fertig
End If
Next
Next
End If
End Sub

monde
01.10.2016, 14:13
Hallo,

ich weiß leider nicht, wo ich den Code einfügen muss.
Danke
Maria

monde
11.10.2016, 18:56
hallo,
habe es geschafft, den Code einzufügen, jedoch kommt die Meldung: fehler beim komplimieren, "ende fehlt oder ähnlich".

bitte hilf mir
danke
maria

monde
16.10.2016, 12:04
hallo ralf,

Code konnte ich einfügen. jetzt kommt leider die Meldung;
"fehler beim komplimieren, endsub erwartet".
danke im voraus.

noch ne frage: ich besitze "Excel 2007, das Handbuch". leider habe ich dazu nur eine cd. eine cd kann ich bei meinem Laptop nicht einlegen. gibt es da eine lösung?

maria

monde
17.10.2016, 18:30
hallo ralf,

habe schon zweimal zurückgeschrieben, leider hast du nicht geantwortet. andererseits: ich finde meine antwort nicht, habe wohl irgendwas falsch gemacht.

es kommt beim Code die Meldung "fehler beim komplimieren und sub-ende fehlt (so ähnlich).
kannst du mir weiterhelfen?
vielen dank im voraus