Excel VBA Problem mit Runden

Hallo,

Ich verwalte ein Konto, das mehreren Personen gehört. Person A ist zur Hälfte, die Personen B und C zu je einem Viertel an Einnahmen und Ausgaben beteiligt.

Ich bastle gerade an einem Makro mit einer Eigabemaske. Das Makro teilt zunächst den eingegebenen Betrag unter den Beteiligten auf. In einem zweiten Schritt werden die drei Teilbeträge in die Excel-Tabelle übertragen.

Mein Problem ist die Rundung der Teilbeträge.

' für Person A: Division durch 2, Rundung auf 2 Stellen

ROUND (TextBox1 / 2, 2)

' für Person B und C jeweils: Division durch 4, Rundung auf 2 Stellen

ROUND (TextBox1 / 4, 2)

In die TextBox1 wird der Gesamtbetrag eingegeben. Je nach Höhe der Teilbeträge werden diese kaufmännisch auf- oder abgerundet. Dabei kann es vorkommen, dass alle drei Beträge aufgerundet werden und dadurch ein halber bis ein ganzer Cent zuviel in die Tabelle übertragen wird.

Natürlich könnte ich die Abweichung manuell korrigieren. Ein Beteiligter bekäme in diesem Fall einen Cent weniger gutgeschrieben bzw. er würde um 1 ct. weniger belastet.

Gibt es eine Möglichkeit, das Problem über das Makro (oder durch Formeln in der Tabelle) zu umgehen? Ideal wäre es, wenn darüberhinaus nicht immer die selbe Person die Mehreinnahme oder Mehrbelastung von 1 ct. erhielte. Dazu müsste man den letzten Kontostand daraufhin überprüfen, ob zwischen den Guthaben der Beteiligten gerade eine Differenz besteht, die auszugleichen wäre.

Hat jemand eine Idee?

Gruß, piano-forte

 

Danke, Hascheff, für diese schnelle Antwort auf den zweiten Teil meiner Frage. Jetzt muss Excel es nur noch merken, wenn die Rundung in der Summe einen Cent zu viel ergibt, um dann nach dem Zufallsprinzip bei einem der Beteiligten ab- statt aufzurunden.

Die drei gerundeten Beträge addieren, die Summe mit der ursprünglichen Eingabe vergleichen und bei einer Differenz den Cent irgendeiner Person abziehen.

In der Theorie vielleicht ganz gut - es sei denn es gibt noch andere Wege - , aber so fit bin ich in VBA nicht, um das in passenden Code umzusetzen.

Hast du oder ein anderer dazu noch einen Tipp?

Gruß, piano-forte

 

...klar, dachte er auch...
Jetzt ist daraus ein Monster geworden, auch wenn ich nicht absichtlich alles auseinander gezogen hätte.

Den Zufall kennt jedes Kind, aber je mehr ich mich damit beschäftige, um so unsicherer werde ich.
Der Mathematiker soll prüfen!

Zahl in TextBox1 muss positiv sein.

Code:

Sub Pfennigfuchser()

'Zeile nur für Test, nachher entfernen...
Dim TextBox1, xx, yy, zz

Dim ctGanz As Integer, numRand As Integer, numRand1 As Integer, ctTeil As Integer, ctRest As Integer
Dim partA1 As Single, partA2 As Single, partA As Single, partB As Single, partC As Single

'Zeile nur für Test, hier Zahl ändern > nachher entfernen... wenn Textbox1 existiert
TextBox1 = 18.35

        'rechnet in Cent
ctGanz = TextBox1 * 100
        'bezieht die Basis für den Zufallsgenerator vom Systemtakt
    Randomize
        'rechnet eine Zufallsganzzahl 1 - 4
numRand = Int(4 * Rnd + 1)
        'rechnet Viertelbetrag in ganzen Cent
ctTeil = Fix(ctGanz / 4)
        'rechnet den verbliebenen Rest
ctRest = Round(ctGanz - ctTeil * 4)
        
        'Verteilt den Rest
    If ctRest = 1 Or ctRest = 2 Then
        If numRand = 1 Then
            partA1 = 1
        ElseIf numRand = 2 Then
            partA2 = 1
        ElseIf numRand = 3 Then
            partB = 1
        Else
            partC = 1
        End If
    ElseIf ctRest = 3 Then
        ctRest = ctRest + 1
        If numRand = 1 Then
            partA1 = -1
        ElseIf numRand = 2 Then
            partA2 = -1
        ElseIf numRand = 3 Then
            partB = -1
        Else
            partC = -1
        End If
    End If
            'wenn noch ein Cent zu verteilen ist
            'Ob das wirklich "zufällig" ist? Zufällig könnte doch auch ein Part 2ct bekommen...
    If ctRest = 2 Then
            'neue Zufallszahl 1 - 3
        Randomize
        numRand1 = Int(3 * Rnd + 1)
        If numRand1 = 1 And partA1 <> 1 Then
            partA1 = 1
        ElseIf numRand1 = 2 And partA2 <> 1 Then
            partA2 = 1
        ElseIf numRand1 = 3 And partB <> 1 Then
            partB = 1
        Else
            partC = 1
        End If
    End If

    
            'Endverteilung
partA1 = (partA1 + ctTeil) / 100
partA2 = (partA2 + ctTeil) / 100

partA = partA1 + partA2
partB = (partB + ctTeil) / 100
partC = (partC + ctTeil) / 100


'diese 3 Zeilen nur f&#252;r Test (wenn Haltepunkt auf "End Sub"), nachher entfernen...
xx = partA + partB + partC
yy = 4 * ctTeil / 100
zz = Fix(TextBox1 - xx)

End Sub

Es geht hier doch um eine Art Buchhaltung, und dort gibt es keine Zufälle!
Der Bäcker rechnet ja auch nicht für jedes Brötchen die Amortisation des Ofens und die Mehrwertsteuer ab, sondern für alle Brötchen und Brote zusammen, in einer bestimmten Periode.

Ich würde die Zwischenresultate pro Vorgang (wenn sie schon nötig sind) den Teilhabern nicht Runden, sondern als Double rechnen lassen, im Blatt aber die Zellen als "17.36" formatieren. Das wären dann nur Indikationen...

Die echte Abrechnung würde ich erst aus der Kolonnensumme machen. Dann gibt es insgesamt doch noch eine Differenz von maximal 3 Cents, und diese würde dann über das Konto "Rundungsdifferenzen" abgebucht...

Gruss Urs

 

Hallo Hascheff,

2 x Randomize >> Weiss ich nicht.
Hier vielleicht schon unnötig, weil ja einmal 4 und einmal 3 Zahlen generiert werden. Sonst müsste man das in einer 500x-Schleife einmal testen > je 3x generieren, zuerst mit, dann ohne und dann noch einmal mit neuem Randomize

Monster verkleinern >> Ja müsste man können, obwohl das Verteilen der Zufallszahlen auf die Parts knifflig ist.
Viel Mühe habe ich mir nicht gegeben, ich ziehe die Rechnung auf Basis Total ja vor...

Gerechte Methode >> Mit Zufall denke ich nicht.
Bei politischen Proporzwahlen werden alle möglichen Verteilverfahren angewendet, um überzählige Wählerstimmen, die nicht für ein ganzes Mandat reichen, doch noch in ein solches umzumünzen. Vom Zufallsverfahren habe ich noch nichts gehört.
Die Profiteure finden jedes Verfahren gut, die anderen nicht...

Bei Rest 2 >> Das schien mir weniger ungerecht!
Die 4 Parts bekommen die Cent. Wem die Parts gehören, sollte für die Verteilung nicht relevant sein.
A könnte ja einen Part seiner Frau überschreiben, so wie B und C vielleicht sogar schon verheiratet sind (siehe oben Politik).

Rundungsdifferenz der Kolonne ins Form >> Dann kostet die tägliche Bildzeitung einmal mehr und einmal weniger für den Einzelnen ( = Schaissprogramm - taugt nichts! ). Mit meinem Kolonnenvorschlag sind die Einzelverteilungen mit 15 signifikanten Stellen im Blatt, jeder darf prüfen, er muss nur die Zellen anders formatieren.
Wenn es nicht gerade um Milliarden-Beträge geht, sollten die Restdifferenzen keine Auswirkung zeigen.

Warten wir ab, was piano-forte meint.

Gruss Urs

 

Hallo Hascheff und Urs,

Erst einmal vielen Dank für Eure Mühe. Ich hätte nicht gedacht, dass ich gleich eine Expertendiskussion auf hohem Niveau auslöse.

Ob man die Zufallsverteilung noch gerechter machen kann als es Urs' Makro leistet, soll ein Mathematiker beurteilen. Das Grundprinzip habe ich jedenfalls verstanden.

Im Grunde geht es um ein Konto von Privatpersonen, das recht überschaubar ist. Etwas überspitzt gesagt, könnte man bei näherem Hinsehen Abweichungen mit bloßem Auge erkennen und am Kolonnenende manuell einen Ausgleich vornehmen. Der bisherige Kontoführer der Gemeinschaft hat das Ganze noch problemlos von Hand mit Papier und Bleistift statt Computer bewältigt, ist jetzt aber aus Altersgründen damit überfordert.

Man könnte auch sagen, es kommt nicht darauf an, dass die Software eine perfekte Finanzbuchhaltung mit besonderem Rundungsdifferenzkonto u.s.w. absolut fehlerfrei und ohne jedes manuelle Eingreifen erledigt, wenn der Programmieraufwand dafür größer ist als die Arbeitsersparnis.

Trotzdem haben mich eure Vorschläge in meinen eigenen Überlegungen schon weiter gebracht. Wenn ich wieder etwas mehr Zeit habe, werde ich gelegentlich Urs' Makro mal mit einbauen oder mich mit der Rundungsdifferenz am Kolonnenende beschäftigen (Es könnte ja auch sein, dass das Makro dafür sorgt, dass die Notwendigkeit zu einer Korrektur am Ende möglichst gering ist.). Es eilt nicht, denn dahinter steht kein Chef, der darauf wartet, dass ich pünktlich ein Ergebnis abliefere.

Mir geht es auch daraum, die Tabelle auf längere Sicht für andere möglichst idiotensicher benutzbar zu machen. Ein bisschen spielt auch der Reiz mit, so etwas hinzubekommen (wenn auch teilweise mit Unterstützung wie der Euren). Die Word-Makros, die ich für meine Arbeit brauche, laufen schon seit "Urzeiten" und wurden nur bei Bedarf an neuere Office-Versionen adaptiert. Ansonsten bin ich bei dem bisschen VBA, das ich mir dafür mal angeeignet habe, aus der Übung und finde mich über dieser Excel-Aufgabe allmählich wieder rein.

Nochmal danke für Eure Hilfe,

piano-forte

 

Hallo piano-forte,

So ähnlich hatte ich mir die Voraussetzungen schon vorgestellt.
Es war einfach wieder einmal ein interessantes Thema. Nur wegen dem einen Cent lohnt sich ja eigentlich gar kein Aufwand...

Aber es stimmt schon, von Hand kann man alles ausbügeln - wenn es der PC macht, muss es perfekt sein.
In der Buchhaltung besteht ja auch immer die Gefahr, dass der läppische eine fehlende Cent aus mehreren grossen, positiven und negativen Fehlern besteht. Bei meinen Buchführungen für das Finanzamt und den Prüfer hätte ich deshalb nie einen Zufallsgenerator auch nur in die Nähe kommen lassen.

Ein Mathematiker für die Begutachtung des Zufalls war ja schon hier... ich bin es nicht...

Nach diesen neuen Informationen, und damit später auch andere, weniger verständnisvolle Leute damit umgehen können, würde ich es so machen >

- wenn überhaupt nötig, die Einzelposten genau berechnen und gerundet anzeigen, aber für die Teilsummen gar nicht verwenden,

- die Summe der Hauptspalte laufend berechnen lassen, mit Nachführung der gerundeten Teilsummen.

- die maximale Differenz von 3 Cent, zwischen Hauptsumme und Teilsummen, in einer eigenen Zelle als "Rundungsdifferenz" anzeigen lassen.

Dann bleibt die Kirche im Dorf.
Dass auch der perfekte PC einen Cent nicht spalten kann, wird jedem einleuchten...

Gruss Urs