ich habe eine für mich sehr knifflige Aufgabenstellung und weiß leider nicht mehr weiter.
Folgend das Problem:
Wir kaufen für mehrere Kunden eine kumuliert Anzahl eines Produkt zu unterschiedlicher Stückzahl (Spalte D) und Preisen (Spalte C) ein. Nun müssen wir den jeweiligen Bedarf jedes einzelnen Kunden zum einstehenden Schnittkurs (Feld G3) so aufteilen, dass wir aus den jeweils eingekauften Mengen den Bedarf pro Kunde füllen.
D.h.: Die jeweilige Anzahl zu verschiedenen Einstandskurses müssen wir nun so auf die unterschiedlichen Kunden aufteilen, dass jeder Kunde seine gewünschte Anzahl zum gleichen Schnittkurs (auf die 3 Nachkommastelle genau) wie die anderen Kunden auch erhält. Dabei ist zu beachten, dass wir nur eine GanzeZahl von jedem einzelnen Kauf abgeben können. Die Anzahl an Käufen ist variabel, d.h.: es können in den Spalten A:E auch 100 Positionen etc. stehen.
Das Problem sieht visuell so aus:
=> Kunde 1 möchte 7 Stück zum Durchschnitt von 161,617 aus der Anzahl und Kurs von C3-D9 erhalten, wohingegen Kunde 2 bereits 11 Stück benötigt und Kunde 3 mit 20 Stück anfrägt usw.
Wie kann ich dieses Problem in Excel lösen? Ich komme hier nicht wirklich weiter. Habe bereits die Normalverteilung und den Solver versucht, alles leider ohne Erfolg. :22:
ich sehe das du noch keine Antwort erhalten hast. Aus deinem Bild kann ich nicht soviel ersehen. Kannst du uns bitte eine kleine Tabelle mit 5-10 Beispieldaten machen, damit man sehen kann wie die Lösung aussehen soll? Wo genau findet man die gesamt Anzahl, die ja auf einzelne Kunden verteilt werden muss. Wenn wir konkrete Daten sehen ist die Sache leichter zu bearbeiten. Die Beispieldatei ohne Kunden Namen, uns interessieren nur Zahlen, und wie sie zugeordnet werden müssen. Wenn wir deine Aufgabe verstehen bekommst du sicher Hilfe.
Ich habe mich jetzt länger mit der Problemstellung beschäftigt. Die "Verzweifelung" verstehe ich zwar, aber nicht die Aufgabenstellung noch den Zusammenhang. Mir scheint es hier um eine Aufgabe aus der höheren Mathematik zu handeln.
Die gestellte Aufgabe ist, vor allem unter der Lösungsforderung (Ganzzahl und alle zum gleichen Schnittwert), so nicht zumachen. Da eine genaue Verteilung überhaupt nicht möglich ist.
Als Beispiel im Anhang die Excel-Datei. Hier gehe ich von leichten Extremwerten aus und schon können wir uns am gewogenen Durchschnitt überhaupt nicht mehr orientieren, sondern nur näherungsweise eine "gerechte Verteilung" herbeiführen.
Selbstverständlich kommen wir der Schnittmenge sehr nahe, aber nur wenn die "Einkaufswerte" eine gewisse Größe (Menge) haben und in einem lösbaren Verhältnis (Abweichung) zueinander stehen.
Man verzeihe mir, das ich in Kunden die Zahlen eingetragen und nicht funktional herbeigeführt habe. Das war mir bei einer Lösung ohne Ansatz erstmal zu viel.
Bitte tompas verrate uns wie du das auf dem Blatt hättest rechnen wollen. Gast 123 hat Recht, ohne einen Ansatz ist es so nicht zu machen.
Früher dachte ich etwas über Office zu wissen, bis ich in diesem Forum ankam. Es gibt noch viel zu lernen. Reaktionen auf angebotene Lösungen wären sehr nett.
erstmal danke für Eure beiden Beiträge. Mit einer ExcelTabelle wird es hoffentlich einfacher.
@Palomino: Ich denke auch, dass es ohne einen komplexen VBA Code nicht zu machen sein wird, ich hoffe ich täusche mich!
Leider sind in dieser Anfrage ziemlich viele Nebenbedingungen enthalten, welche sich untereinander auch bedingen.
Ich habe Euch ein Excel angehängt, hier seht ihr in den Spalten A-G folgende Werte:
A = Produkt = immer gleich! Für die Verarbeitung haben wir diese Spalte eingefügt, da es aus unserem Handelsprogramm diese Spalte benötigt. B = Richtung = immer gleich! Für die Verarbeitung haben wir diese Spalte eingefügt, da es aus unserem Handelsprogramm diese Spalte benötigt. C = Kurs = zu welchem die jeweilige Anzahl eingekauft wurde D = Anzahl = welche am Markt eingekauft wurde E = Hilfsrechnung = damit wir den Schnittkurs in G3 errechnen können (hätte man auch in eine Formel packen können) F = Summe der Spalte D
In der Tabelle H-I seht ihr in der ersten Zeile die pro Kunde zu verteilende Anzahl, im orangen Bereich die manuell verteilte Anzahl pro Kunde. Unter "verteilt" summieren wir die oberen Zellen auf, unter Schnitt berechnen wir den für diesen Kunden jeweiligen Schnitt bezogen auf die manuelle Verteilung.
In der Kontrollspalte wird die verteilte Anzahl zum jeweiligen Kurs aufsummiert, da wir nicht mehr als die Anzahl in Spalte D verteilen können.
Nun kommt die Krux an der Geschichte: Wir haben manuell die Anzahl pro Kunde verteilt, erreichen einen Schnittkurs, welcher auf die zweite Nachkommastelle dem einstehenden Schnittkurs entspricht, was nicht so schlecht ist. Bei einer Tabelle mit deutlich mehr als den aufgeführten 7 Positionen ist eine derartige manuelle Verteilung aber nicht mehr möglich.
Der Solver kann zwar Nebenbedingungen verarbeiten, aber eine solch komplexe Verteilungstabelle nicht lösen.
Für die Verteilung auf die ersten Kunden klappt das noch gut, je später der Kunde in der Reihe ist desto schwieriger wird das dann, da wir die Kunden untereinander nicht bedingen können, zumindest fällt uns hier nichts praktikables ein.
29.01.2017, 22:24 (Dieser Beitrag wurde zuletzt bearbeitet: 29.01.2017, 22:24 von Palomino.)
Ich verstehe euer grundsätzliches Problem nicht.
Ich kauft Waren auf Bestellung ein in Summe-X der Aufträge die ihr erhaltet.
Habt ihr die Auftragsanzahl erreicht bildet ihr die Schnittmenge des Bezugspreises und verkauft die Waren. Heißt ihr setzt einen Stopppunkt.
Dabei ist es bei gleicher Ware völlig egal wie ihr es verteilt, da ihr ja nach gewogenem Durchschnittspreis verkauft.
Sobald ihr alle Ware abgesetzt habt, ist das Thema erledigt. Wozu also die gerechte Verteilung? Sie macht keinen Sinn!
Kauft ihr weitere Ware während des Absatzes hinzu, bekommt euer Schnittpreis eine Dynamik die nicht mehr ausgleichbar ist, da für die verbleibenden Kunden dieser steigt oder fällt jedoch nicht mehr den Realwert abbildet. Es kommt zu Verfälschungen.
In meiner Tabelle siehst du doch das eigentliche Problem. Hier ist die gerechte Verteilung zum Schnittpreis gar nicht mehr möglich.
Aber lange Rede gar kein Sinn. Ich habe ein ähnliches Problem im Netz entdeckt. Da ich aber nur über begrenzte VBA-Fähigkeiten verfüge hier einen Link http://www.herber.de/forum/archiv/1164to...asten.html
vielleicht können die VBA-Profis hier damit dein Problem lösen.
Früher dachte ich etwas über Office zu wissen, bis ich in diesem Forum ankam. Es gibt noch viel zu lernen. Reaktionen auf angebotene Lösungen wären sehr nett.
Leider könne wir mit dem Schnittkurs und der Gesamtanzahl nicht arbeiten. Dies dient nur zur Orientierung.
Wir müssen die jeweilig einzeln eingekaufte Anzahl zum jeweiligen Einstandspreis so aufteilen, dass wir unterm Strich für alle Kunden den gleichen Einstand haben. Danke, dass du hier nochmal beim Mathelehrer nachfrägst.
Danke auch für den Tipp, ich werde mir das heute zu Gemüte führen.
1) Deine Verteilmatrix ist ungünstig. Es könnte doch sein, dass ein Kunde zwei Stück aus dem gleichen Einkauf erhält.
In die Matrix gehört die Anzahl. Der Mittelwert kann dann über Summenprodukt ermittelt werden:
=SUMMENPRODUKT($C$3:$C$8;I3:I8)/SUMME(I3:I8)
2) Es ist theoretisch möglich per VBA alle möglichen Kombinationen auszuprobieren und die Kombination, bei der die Summe der quadratischen Abstände zum Mittelwert am kleinsten ist auszuwählen.
Da der Zeitaufwand aber mit der Anzahl exponentiell steigt (bei jedem zusätzlichen Stück verdoppelt sich die Zeit) ist es sehr stark abhängig von der maximal mögliche Anzahl von Einkäufen, Kunden und Stücken.
Wenn dies zu gross ist, können nicht alle Kombinationen ausprobiert werden. Wei gross können die Werte denn bei dir werden?
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
danke für deine Antwort und den Tipp mit dem Summenprodukt, das bau ich mit ein.
Bei den Kunden können wir die Begrenzung auf 6 Stück, wie in meiner Tabelle fixieren.
Die Einstandsliste kann durchaus 100 Posten und mehr aufweisen, wobei ich bei 100 Posten grundsätzlich erst mal die Grenze ziehen würde.
In VBA kenne ich mich leider nur sehr wenig aus, kann man hier vielleicht mit einer Art "Zellen Zählfunktion" die Rechenoperationen eindampfen, da wir nicht immer 100 Posten bzw. immer 6 Kunden haben?
Manchmal sind es 7, dann 20, oder 75 Posten und bei den Kunden verhält es sich gleich.
Palomino gab mir auch schon den Tipp mir das VBA Forum näher anzusehen und hier evtl. ein neues Thema aufmachen.
Kennst du dich in VBA gut aus und kannst hier behilflich sein?
30.01.2017, 15:38 (Dieser Beitrag wurde zuletzt bearbeitet: 30.01.2017, 15:38 von Ego.)
Hallo Thomas,
zu deinem letzten Beitrag:
Zitat:mit dem Summenprodukt, das bau ich mit ein
in der Anlage habe ich deine Daten einmal so aufgebaut (Kopie) , wie ein Programm das Ergebnis liefern kann.
Zitat:Bei den Kunden können wir die Begrenzung auf 6 Stück, wie in meiner Tabelle fixieren.
Die Einstandsliste kann durchaus 100 Posten und mehr aufweisen, wobei ich bei 100 Posten grundsätzlich erst mal die Grenze ziehen würde.
Manchmal sind es 7, dann 20, oder 75 Posten und bei den Kunden verhält es sich gleich.
Zur Klärung der Begriffe (ich würde zu Kunden nicht Stück sagen) hier drei Fragen zur ungefähren maximalen Anzahl (in Klammern die Werte aus deinem Beispiel):
Kunden (6) Max = ??
Einkäufe(6) Max = ??
Artikel (18) Max = ??
Kannst du einmal einmal ein aus deiner Sicht grosses Beispiel zusammenstellen?
Zitat:In VBA kenne ich mich leider nur sehr wenig aus, kann man hier vielleicht mit einer Art "Zellen Zählfunktion" die Rechenoperationen eindampfen ...
Über die genaue Übermittlung der auszuwertenden Daten an das Programm können wir sprechen, wenn sich eine Optimierung (nach Rechenzeit) als sinnvoll erweist.
Auf jeden Fall wird das Programm so geschrieben, dass keine Begrenzungen in den Eingabedaten vorhanden sind. Die Begrenzung kommt schon über die Laufzeit.
In dem Beispiel benötigt das Programm als Eingabewerte die grün markierten Bereiche und unter der beige markiertem Bereich genügend Platz für die Ausgabe.
Zitat:Palomino gab mir auch schon den Tipp mir das VBA Forum näher anzusehen und hier evtl. ein neues Thema aufmachen.
Wenn ich das Forum "Beispiele mit VBA" in diesem Form hier richtig verstanden habe, gehören dort keine speziellen Anfragen hinein, sondern nur Beispiele von Anwendungen, die allgemein nützlich sein können.
Zitat:Kennst du dich in VBA gut aus und kannst hier behilflich sein?
ein wenig; ja
zum Beispiel:
Bisher habe ich "nur" eine Aufteilung realisiert (Idee von Gast123). Es werden die Artikel für die Kunden nacheinander aufgeteilt.
Diese Aufteilung ist auch bei grösseren Mengen schnell. Sie liefert oft ein gutes aber selten das optimale Ergebnis (siehe Beispiel).
Wenn man diese Aufteilung aber als Startwert für eine Optimierung nutzt, kann man die Rechenzeit für die Optimierung ein wenig verkürzen.
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
herzlichen Dank für deine Hilfe und die Excel Tabelle bzgl. Verteilung. Das sieht für den ersten Wurf wirklich schon nicht schlecht aus.
Im Anhang findest du eine Tabelle wie wir sie heute von Kundenseite angefragt bekamen.
Im Input findest du in den Spalten A bis D die Werte aus unserem Handelssystem in Spalte E die Hilfsrechnung bzgl. Schnittkursberechnung, in Spalte F die Gesamtsumme aus Spalte D und in Spalte G den Schnittkurs. In den Spalten H bis M die jeweilig pro Kunde benötigte Menge und in Spalte N zur Kontrolle die auf die Kunden verteilte Menge (sollte gleich der Gesamtsumme sein) :19: .
Im Tabellenblatt Output haben wir die Spalten A bis D aus der Inputtabelle visuell dargestellt, daneben die Aufteilungstabelle über die Anzahl je Kunden. Leider müssen wir für die Abrechnung und Aufteilung eine derartig aussehende Liste bereitstellen, d.h.: wir benötigen das Produkt, die Richtung (Buy/Sell), Kurs und Anzahl sowie die pro Kunde von dieser Position zugeteilten Anzahl.
Zu deinen offenen Fragen:
Kunden = 6 (aktuell, sofern es die Rechenoperation zulässt, gerne auch 10 - kein MUSS)
Einkäufe = offen (theoretisch unbegrenzt, wobei wir hier die Grenze vorerst bei 100 ziehen würden)
Artikel = 1 (ich interpretiere das als Produkt d.h.: wir handeln für alle Kunden immer ein identisches Produkt/Artikel eine Vermischung erfolgt nicht) => wir kaufen z.B.: für alle Kunden in einer Aktion nur Produkt A und in einer separaten Aktion dann für alle Kunden nur Produkt B.
Ich hoffe ich habe alle offenen Fragen beantwortet. Ich danke dir herzlich für deine Mühe.
