Hallo Thomas,
wie kannst du unter Artikel Produkt verstehen, wenn in deinem Beispiel 18 Artikel sind? Unter Artikel hatte ich die Summe der Anzahl verstanden.
Dein Extrembeispiel ist doch ernüchternd. Eine Optimierung, wie ich sie angedacht habe kommt hierfür wohl nicht in Frage. Wahscheinlich benötigt hierfür schon die von mir gelieferte Aufteilung zuviel Zeit (werde ich einmal testen).
Für solche Mengen und bei so kleinen Kursdifferenzen könnte doch eine geschickte Verteilung, ähnlich der Verteilung bei Bundestagsmandaten ausreichen. Hier kenne ich mich aber nicht gut aus.
Trotzdem sende ich einmal die programmierte Optimierung zum Test.
Vielleicht kann man sie ja für kleinere Aufträge nutzen, für die eine einfache Verteilung zu ungenau ist.
Diese Optimierung kann aber schon bei Artikelmengen im höheren zweistelligen Bereich Minuten dauern.
Hallo Thomas,
bei deinen Daten macht weder das von mir vorgeschlagen Optimierungs- noch das Aufteilungsprogramm Sinn. (nach Rechenzeit)
Bevor ich mir Gedanken zur Programmierung von Verteilungen mache, die man sonst nicht benötigt, noch drei Fragen?
1) Warum?
Wenn man alle Kunden gleich behandeln will ist es doch am einfachsten und auch richtiger die Anzahl der Kundenwünsche mit dem Mittelwert zu multiplizieren und dann zu runden. Die Differnz zur Einkaufssumme dürfte kleiner als 5 €ct sein, was einen grössern Aufteilungsaufwand nicht rechtfertigt.
2) Warum sollen die Ergebnisse auf die Einkäufe verteilt werden?
In deinem Beispiel hattest du 101 Einkäufe, aber nur 14 verschiedene Preise.
3)Reicht nicht die Verteilung auf verschiedene Preise?
Guten Morgen Helmut,
danke für deine Rückantwort und Geduld.
Gerne komme ich mit Antworten auf die drei Fragen zurück:
1) Warum:
Wir müssen den Handelsplatz neutral stellen, eine Rundungsdifferenz darf hier nicht entstehen, daher haben wir den Schnittkurs auf die Gesamtanzahl am Handelsplatz berechnet und auf die geforderte 4. Nachkommastelle ausgewiesen (so rechnet der Handelsplatz dann gegen uns ab). D.h.: wir müssen gegen den Handelsplatz auch so einstellen. Die Rundungsdifferenz darf daher nur auf Kundenebene entstehen, hier sind wir dann flexibler, wobei die Rundungsdifferenz auch hier sehr gering sein sollte.
2) warum auf die Einkäufe verteilen:
Liegt leider im System der Sache begründet.
Der Handelsplatz an welchem wir die Käufe und Verkäufe tätigen lässt leider eine Abrechnung zum Schnittkurs nur zu, wenn wir aus den jeweiligen einzelnen Posten/Handelsprints ihm eine dementsprechende Aufteilung pro Kunde zukommen lassen. Danke für den Tipp zur Kumulierung der Anzahl mit gleichem Preis => siehe Antwort 3.
3) Verteilung von einer kumulierten Summe je gehandelten Preis:
Das Thema habe ich gerade mit dem Handelsplatz geklärt, das wäre auch machbar.
D.h.: wir können die Anzahl mit dem jeweils gleichen Kurs in einer Summe zusammenfassen und dann entsprechend auf die Kunden aufteilen. Müsste sich dann ja eigentlich positiv auf den Rechenaufwand auswirken .
Vielen Dank für deinen Einsatz Helmut.
Viele Grüße
Thomas
Hallo Thomas,
in der Anlage das Ergebnis.
Das Programm arbeitet, abhängig von der Anzahl der Artikel (Summe(Anzahl)) nach drei verschiedenen Verfahren:
1) Bei grossen Mengen (Anzahl der Artikel grösser als "MaxAuf") erfolgt eine Verteilung:
Eine Schleife bis alle Kundenwünsche erfüllt sind mit
Suche den Kunden, für den ich noch die meisten Artikel benötige.
Nehme abhängig vom Preisabstand zum Mittelwert den Artikel mit dem höchsten oder niedrigsten Wert.
Fülle den Kunden mit Artikeln vom anderen Ende der Preisliste bis der Mittelwert des Kunden den Mittelwert der Gesamtmenge überschritten hat.
Die Verteilung wird zur Zeit nur durchgeführt wenn die Summe der eingekauften Artikel gleich der Summe der Kundenwünsche ist.
2) Bei mittlegrossen Mengen (Anzahl der Artikel grösser als "MaxOpt" und kleiner gleich "MaxAuf") erfolgt eine Aufteilung:
Suche der Reihe nach für jeden Kunden einen Kundenmittelwert, der möglichst nahe am Gesamtmittelwert ist.
(Meistens geringfügig besser als die Verteilung)
3) Bei kleinen Mengen (Anzahl der Artikel kleiner gleich "MaxOpt") erfolgt eine Optimierung:
Es wird geprüft, ob es eine Kombination gibt, deren Quadratische Abweichnung vom Gesamtmittelwert kleiner als das Ergebnis der Aufteilung ist.
Mit der Änderung der Parameter MaxOpt und MaxAuf solltest du vorsichtig sein. Eine Optimierung für eine dreistellige Anzahl von Artikeln kann eventuell Wochen oder Jahre dauern.
Wenn du für beide Parameter die Werte auf 0 setzt, wird immer die Verteilung ausgeführt.
Ich habe im Ergebnis die Zuordnung zu der Einkaufsliste durchgeführt. Der Aufwand bei der Verteilung die Zeilenposition der Artikel mitzuführen war geringer als eine Preisgruppierung der Aufteilung und Optimierung.
Eine Beschreibung der Schnittstellen zwischen Tabellen und Programm erfolgt, wenn dir das Ergebnis zusagt. Wir können dann auch noch Feinanpassungen nach deinen Wünschen machen.
Hallo Helmut,
WOW, ein riesiges DANKESCHÖN :18: an dieser Stelle, das sieht sehr gut aus, was man mit VBA alles machen kann, sollte hier vielleicht auch mal einen Kurs belegen. :19:
Folgende Frage dazu hätte ich:
Ich habe die Gesamtsumme (F3) von ursprünglich Summe(D3:D103) auf Summe(D3:D110) geändert, hier unterscheiden sich dann die Gesamtsumme und die verteilte Summe auf die Kunden.
So wie ich dich verstanden habe wird dann keine Verteilung etc. vorgenommen.
Im Sheet lässt er mich das aber verteilen und die MessageBox erscheint nicht (siehe Screen Unterschied VerteilbarAufgeteilt).
Wenn ich hingegen die Gesamtsumme unverändert auf Summe(D3:D103) belasse und in der Kundenaufteilung nicht exakt die Summe verteile, dann lässt er mich das nicht durchführen (KeineVerteilung bei manuell).
Könntest du dir das nochmals ansehen bitte?
Nochmals vielen lieben Dank! Lass mich das die nächsten Tage mal genauer im operativen Betrieb testen. Dann melde ich mich sehr gerne nochmals falls wir noch feinadjustieren müssten.
Hallo Thomas,
das Programm kümmert sich nicht um irgendwelche berechneten Werte in dem Arbeitsblatt.
Es benötigt im Arbeitsblatt "Input" (über benannte Bereiche) nur die zur Zeit hellgelb hinterlegten Bereiche und ermittelt die Summen selbst.
Das Programm benötigt zur Zeit 7 benannte Bereiche (wenn du F5 drückst werden dir die Namen vorgeschlagen und du kannst sie auswählen):
1) Bereich "Einkauf"
Dieser Bereich muss 4 Spalten breit sein, in der dritten Spalte den Kurs und in der vierten Spalte die Anzahl haben. Der Bereich kann beliebig viele Zeilen haben.
Es weren nur Einkäufe berücksichtigt, die in diesem Bereich sind.
2) Bereich "Kunden"
In der ersten Zeile des Bereiches müssen die Artikelmengen der Kunden stehen.
Für die Verteilung muss zur Zeit die Summe der vierten Spalte des Bereiches "Einkauf" gleich der Summe der ersten Zeile des Bereiches "Kunden" sein.
3) Zelle "MaxOpt"
Aus dieser Zelle liest das Programm bis zu welcher Anzahl an Artikeln die Optimierung genutzt werden soll. (Wie gesagt nicht sehr hoch einstellen.)
4) Zelle "MaxAuf"
Aus dieser Zelle liest das Programm bis zu welcher Anzahl an Artikeln die Aufteilung genutzt werden soll. (Wie gesagt nicht zu hoch einstellen.)
5) Zelle "Ausgabe"
Ab dieser Zelle wird die Kopie des Bereichs "Einkauf" eingetragen.
6) Zelle "Ausgabe"
Ab dieser Zelle wird das Ergebnis eingetragen.
7) Zelle "Sort"
Ab dieser Zelle werden die für die Verteilung notwendigen Werte der Einkäufe (3 Spalten) sortiert nach dem Kurs eingetragen.
Alle anderen Zellen derDatei werden für das Programm nicht benötigt.
Du kannst die benannten Bereiche beliebig in dieser Datei positionieren, auch in andere Arbeitsblätter, ohne das Programm ändern zu müssen.
Du solltest nur darauf achten, dass unter und neben den drei benannten Zellen zur Ausgabe genügend Platz für die Ausgabelisten ist. Dort werden die Werte gnadenlos überschrieben.
Einrichten einer neuen Verteilung:
a) Wenn die Artikel auf weniger Kunden aufgeteil werden soll, kannst du die entsprechenden Spalten im Arbeitsblatt "Input" löschen.
Hierdurch wird der Bereich "Kunden" angepasst.
b) Wenn die Artikel auf mehr Kunden aufgeteil werden sollen, kannst du die entsprechende Anzahl von Spalten hinzufügen. Aber bitte nach dem ersten und vor dem letzten schon vorhandenen Kunden.
Hierdurch wird der Bereich "Kunden" angepasst.
Anschliessend kannst du die Überschriften anpassen.
c) Wenn die neue Verteilung mehr oder weniger Einkaufsdaten hat, kannst du hier genau so vorgehen wie beim Ändern der Kundenanzahl (nur Zeilen löschen oder hinzufügen).
Bei grösseren Einkaufslisten ist es aber praktischer über das Menü "Formeln"; "Namensmanager" den Bereich "Einkauf" anzupassen.
Achtung!
Die Funktion "leeren" leert "nur" die Ausgabefelder in der Grösse der zur Zeit eingerichtete Bereiche.
Wenn du also in der Datei (oder einer Kopie) eine neue Verteilung einrichten willst, solltest du zuerst mit "leeren" die Ausgaben der alten Verteilung leeren, wenn die neue Verteilung weniger Einkäufe oder weniger Kunden hat.