Office: Werte gegeneinander ausgleichen

Hallo WS-53

"Cursor irgendwo in die Ergebnistabelle > rechte Maustaste > Aktualisieren."


Leider gibt es mir folgende Fehlermeldung wenn ich aktualisiere:

Initialisierung der Datenbankquelle schlug fehl.

Überprüfen Sie den Datenbankserver oder kontaktieren Sie ihren Datenbankabdministrator.

Gruss
TruckerFranz

 

Hallo TruckerFranz,

das kann evtl daran liegen, dass dein Excel zu alt ist. Ab der Version 2016 ist Power Query immer on Board. Für 2013 immer und für 2010 tlws. kann das Add-In kostenlos installiert werden.

 

Hallo WS-53,
ich staune immer wieder, was man mit PQ alles machen kann.

Ich habe mir aber nur die Ergebnis-Tabelle angeschaut. Dort werden m.E. die Zeilen 7 bis 10 zu Unrecht aufgeführt, da die Werte in der Summe 0 ergeben und daher ein schönes Beispiel für die Teilsummen-Problematik darstellen.

Was mich aber wundert: der TE nimmt das offenbar ohne Kommentar so zur Kenntnis. Daraus könnte man schließen, dass Teilsummen nicht mehr Bestandteil der Aufgabenstellung sind.

 

Hallo Aloys,

vielen Dank für die Aufmwerksame Prüfung des Ergebnisses.

bezogen auf:

hast du natürlich absolut recht. ich hatte aber

irgendwie mehr Beachtung geschenkt.

Das lässt sich ja auch recht einfch abbilden.

Aber unabhängig, ob per Formel, VBA oder PQ, habe ich im Moment eigentlich keine Idee, wie das überhaupt gehen sollte. Bei Eurobeträgen bis 10.000 incl. 2. Dezimalstellen sind dies ja schon je 1 Mio Möglichkeiten für positive und neagtive Beträge. Jetzt müssten somit aus den Beträgen, für die es keinen betragsgleichen Gegenpart gibt, durchprobiert werden, welche Positionen mit umgekehrtem Vorzeichen, diesen ausgleichen könnten.

Da ich noch nie damit gearbeitet habe, weiß ich auch nicht, ob sich dies mit dem Solver realisieren lässt.

Auch wenn ich dies mit ABAP (SAP) oder Cobol programmieren wollte, hätte ich im Moment noch keine Idee, wie ich das zielführend angehend könnte.

 

Hallo Aloys,

ich stelle gerade fest, dass sich auch die Zeilen 12 und 12 mit der Zeile 13 ausgleichen. Somit würde bei völlig korrekter Ermittlung der sich nicht ausgleichenden Beträge nur die Zeile 6 mit dem Wert -1.007 übrig bleiben.

Habe darüber nachgedacht, wie es gehen könnte, auch einen Ausgleich über aufgehende Summen zu realisieren. Dazu müsste getrennt für die positiven und negativen Werte alle Komninationsmöglichkeiten (Fakultät) addiert werden, um dann prüfen zu können, ob es positive Kombinationssummen gibt, dies sich mit negativen Kombinationssummen ausgleichen.

Hier in indiesem Beispile mit 5 positiven und 3 negativen, die sich nicht 1.1 ausgleichen, ist dies noch ein Rechenaufwand, der nicht spürbar ist. Aber bereits bei 10 sich nicht direkt ausgleichenden Beträgen, haben wir es dann schon mit über 3,6 Mio Kombinationen zu tun und bei 15 sind es mehr als 1,3 Billionen Kombinationen. Das dann für die positiven und die Negativen getrennt um anschliessend Päärchen suchen, das wird schon eine ganze Weile dauern, bis das durchgerechnet ist.

 

Hallo

@WS...,

A) Permutationen Kombinationen
Bei 15 Werten gibt es 1,3 Billionen Permutationen aber nur 32.768 Kombinationen (bei 30 Elementen nur 1 Milliarde Kombinationen).
Und wie du geschrieben hast ist die Anzahl der Kombinationen hier von Bedeutung.

B) TheoretischPraktisch
Theoretisch kannn auch die Anzahl der möglichen Kombinationen zu gross für eine vollständige Ermittlung sein.
Wenn nach Werten gesucht werden die sich nicht ausgleichen ist aber zu erwarten, dass dies der Ausnahmefall ist. Ich nehme an es ist kein Zufall dass es in den Beispieldaten bei über 700 Werten nur 8 gibt die sich nicht direkt ausgleichen.
Man könnte trotzdem eine Suche der Ausgleichsmengen über die Rechenzeit und/oder eine maximale Anzahl der Elemente der Kombinationen begrenzen.

@TruckerFranz
C)
Die Werte können sich nur dann nicht ausgleichen wenn die Summe der Werte ungleich 0 ist.
Suchst du also "nur" eine Kombination von Werten (mit möglichst wenigen Elementen) die die gleiche Summe wie die Summe aller Werte hat?

 

Hallo TruckerFranz,

zu

Die markierte -1007 lässt sich doch mit +4044 und -3037 als Teil einer Summe ausgleichen.
1. Warum ist sie dann markiert?
2. Fehlt da noch eine Bedingung?

Es bleiben dann die Werte -4044 und + 3037 übrig.*wink.gif*

 

Hallo Oge,

da ja Trucker Franz bei Beruf "Buchhaltung" angibt, nehme ich an, dass er Zahlungseingänge gegen offenen Posten abgleichen will. Wenn dem so ist, dann würden sich nur Beträge mit unterschiedlichen Vorzeichen ausgleichen. Hier wäre dann nur noch die Besonderheit Gutschrift an Kunde, die ja die Forderungen reduziert.

Es ist natürlich nachvollziehbar, dass es völlig egal ist, in welcher Reihenfolge ich die Beträge 10, 20 und 30 addiere, somit anstatt 6 nur eine Kombination berücksichtigt werden muss und insofern die Anzahl der Kombinationen wesentlich geringer, als die der Permutationen ist. Aber per mathematischer Formel aus 1,3 Billionen Permutationen auf 32.768 Kombinationen zu kommen ist ja nur die eine Seite der Medaillie. Die andere Seite (Frage) ist, wie vorzugehen ist, um die 32.7568 Kombinationen mit Leben zu füllen?

Also die entsprechende Anzahl Zwischensummen zu bilden, um vergleichen zu können. wobei sich dann die Anzahl von 32.768 Kombinationen vermutlich noch einmal stark reduziert, da ja Einzelwerte die den Vergleichswert übersteigen, nicht berüchsichtigt werden müssen. Ebenso müssen Zwischensummen nur gebildet werden, solange deren Ergebnis nicht größer als der Vergleichswert ist.

 

Hallo TruckerFranz,

in der Anlage ein Lösungsvorschlag.

Neben einer Liste welche Werte sich nicht mehr mit der vorgegebenen Anzahl von Elementen ausgleichen lassen (Spalte I) wird zur Zeit auch noch ausgewiesen welche Ausgleiche berücksichtigt wurden (ab Spalte L).

Das Programm sucht der Reihe nach die möglichen Ausgleichskombinationen von 2 bis zur vorgegebenen Anzahl von maximal zu berücksichtigenden Elementen.
Intern sortiert das Programm die Werte absteigend nach den Absolutwerten, damit Paare sehr schnell gefunden und viele Kombinationssuchen vorzeitig abgebrochen werden können.
Sobald eine Ausgleichskombination gefunden wurde, werden die zugehörigen Werte bei der weiteren Suche nicht mehr berücksichtigt.

 

Hallo Oge,

zumindest mir gefällt diese Lösung sehr gut !!!

 

Hallo zusammen,

vielen Dank für eure Ideen und Lösungsvorschläge.

@Oge, ich habe die Liste heute mehrfach ausprobiert und genutzt. Genau so habe ich mir vorgestellt.

Vielen Dank dafür *Smilie*Smilie

Gruss
TruckerFranz

 

Hallo @Oge und @TruckerFranz78,
ich bin vermutlich auf der Suche nach eurer Datei, finde aber nichts zum Herunterladen hier. Werden die Daten nach einer Weile aus dem Forum gelöscht oder bin ich auf beiden Augen blöd?
Danke für eure Hilfe!