Iterative Mengenermittlung

10.12.2025 23:53:18

Oliver

Guten Abend,

Ich stehe mal wieder vor einem Problem.
Ich möchte die Anzahl mehrere Module anhand ihrer Konfiguration bestimmen.
Leider habe ich gerade eine Beispiel Datei deswegen versuche ich es hier zu beschreiben

Das hier sogenannte Datenpunkte für die Auslegung einer SPS die eingegeben werden
AI 12
DO 10
AO 5
DI 20

Jetzt gibt es Module mit unterschiedlichen Anzahlen der AI DO AO DI.

Modul 1. Modul 2. Modul 3
Anzahl. X. X. X

AI. 8. 4. 0
DO. 10. 0. 4
AO. 3. 8. 5
DI. 0. 10. 10

Jetzt möchte ich das Excel mit die Anzahl der benötigten Module automatisch berechnet. Natürlich soll er die beste Auswahl treffen. Da wo das x ist muss dann die automatisch ermittelt Anzahl stehen.

Geht das mit excel?

Gruß Olli

AW: Iterative Mengenermittlung

11.12.2025 04:25:06

Onur

Poste eine Datei dazu (mit Wunschergebnis).

AW: Iterative Mengenermittlung

11.12.2025 13:10:15

Yal

Hallo Olli,

es gibt noch zu viel Wissen, die aus deinem Kopf in deine Beschreibung nicht geschafft hat.
Was ist ein SPS? Was sind Modul? Was sind AI, AO, DI, DO? Wie hängen sie miteinander? Was sind diese Zahlen, die Du hier auflistest?

Versuche nicht in Textform eine Excel nachzubilden, sondern erklärt mit Vokabular, das Unbeteiligten auch verstehen können, was die Eingänge sind (und was sie bedeuten) und was der Ausgang sein soll. Wenn etwas Excel-mässig aussehen soll, tippe diese 8 Zahlen in einer Excel und läde diese Datei hier hoch. So viel Mühe sollte es Dir Wert sein.

Von hier aus gesehen, sieht es aus wie eine lineare Optimierung, aber es fehlt eine Zielfunktion, die es zu minimieren/maximieren gilt.

VG
Yal

AW: Iterative Mengenermittlung

11.12.2025 14:11:34

UWCEurope

Hallo zusammen,

ich hatte gestern leider keinen Zugriff auf Excel. Hier unter dem folgenden Link habe ich ein Muster Hochgeladen.

https://www.herber.de/bbs/user/179824.xls

In den Zellen B2-B5 werden händisch Zahlen eingetragen. (orange)
In den Zellen C9-G9 soll das Berechnungsergebnis raus kommen.
Im grauen Bereich sind die jeweiligen Konfigurationen eines I/O Moduls immer senkrecht. D.H. das 1. I/O Modul hat 16 DI das 2. hat 8 DI und 8 AI usw. Dies sind Ein/Ausgänge eines SPS (Speicherprogrammierbaren Steuerung. Also ein kleine Computer.

Excel soll anhand dieser einzelnen Modulkonfigurationen die bestmöglich Auswahl der Module treffen. Im Beispiel habe ich es mal händisch eingetragen 1,1,1,0,2.

Die Zahlen in Zelle B2-B5 sind die benötigten Ein/Ausgänge.

Gruß Olli

AW: Iterative Mengenermittlung

11.12.2025 14:32:22

Yal

Hmm...

Was bedeuten die Zahlen im gelben Bereich? Warum wird z.B. in B2 20 eingetragen und nicht 18?
Wie ergibt sich die Aufteilung der Module in Spalten C-G? (nur verständlichkeitshalber relevant)
UND VOR ALLEM: was ist der Zusammenhang zwischen diese Zahlen?
Oder anders gesagt: wie entstehen daraus diese 1-1-1-0-2 in Zeile 9?

"Was Du nicht erklären kannst, hast Du nicht verstanden. Was Du nicht verstehst, wirst Du nicht programmieren können." (alte Programmierer-Weisheit. Lao-Tse war das. Oder Archimedes? ach, egal)

VG
Yal

AW: Iterative Mengenermittlung

11.12.2025 15:39:13

UWCEurope

Hi,

die Zahlen in den gelben Bereichen ergeben sich anhand der Planung. Also z.b. habe ich 20 digitale Eingänge. Das sind Rückmeldungen von Brandschutzklappen in diesem Fall die AUF-Meldung.

Gibt es nur 18 Brandschutzklappen würde dort eine 18 stehen.

AI steht für analoge Eingänge z.b. Temperaturfühler die einen Messbereich von 0-10V haben.

DO sind Relaisausgänge die die Brandschutzklappe AUF/ZU schalten.

AO sind analoge Ausgänge die Ventile mit 0-10V ansteuern.

Die Konfiguratioen in den grauen Bereichen sind vom Hersteller vorgebeben. Die Module sind elektronisch so gebaut, das sie diese Konfiguration so unterstützen. D.H. an Modul 1 können 16 AUF-Meldungen angeschlossen werden. An Modul 2, 8 Aufmeldungen und 8 Temperaturfühler.

Die Zeile 9 entsteht in dem ich geschaut habe welche Module in welcher Anzahl benötige ich um die Zahlen in gelb abzudecken. Ich glaube das ganze wird kompliziert, da ich mehrere Möglichkeiten habe umd z.B. DI=20 zu erreichen. Ich möchte aber natürlich die bestmöglichste Kombination erreichen. Was bedeutet das ich auch Überschußeingänge habe. Hier im Beispiel hätte ich in Summe 40 DI und benötige aber nur 20. Da ich aber 10 AI benötige muss ich das Module 2 benutzen wo ich dann (eigentlich sogar 2) Module benötige und somit 16 AI habe und auch 16 DI. Bei den DO ist es einfach weil ich 3 Brauch und das Modul halt 6 Stück hat. Bei dem 5.Modul habe ich 16AO also 2 Module weil ich 10 AO benötige. Da ich 2 Module benötige habe ich Automatisch auch 16 DI verfügbar.

Gruß Olli

AW: Iterative Mengenermittlung

11.12.2025 19:36:02

Yal

Hallo Olli,

also doch eine lineare Optimierung:
- Du hast verschiedene Modulen, die verschiedene "Fähigkeiten" haben,
- Du hast "Bedarf" oder Vorgabe (deine Eingabe), die abgedeckt werden sollen,
- Ziel ist es, der kleinste Anzahl an Module zu verwenden. Also eine Minimierung als Zielfunktion.

Es gibt eine Vereinfachung, weil hier nicht betrachtet wird, dass Module Preisunterschied haben, Falls 2 Lösungen dieselbe Anzahl an Module hätten.
Ich würde die Gestaltung leicht umstellen, um eine bessere Übersicht zu haben:

Userbild

- bis Spalte H, keine Änderung. Es sind die "Fähigkeiten". X sind durch 0 ersetzt und mit Zahlenformat "0;-0;-" belegt, um die 0 als "-" zu haben.
- Eingabe AI-AO-DI-DO werden auf dieselbe Höhe wie die Fähigkeiten gebracht, Es bildet die Brücke zwischen linke und rechte Teil der Anwendung.
- ein neuen Block L7:P10, wo die Mengen (Zeile 5) mit den Vorgaben multipliziert werden.
- in Spalte Q die Summen pro Zeile
- in Spalte R die Summen minus Vorgaben. Keine diesen Zahlen darf negativ sein.

Für eine lineare Optimierung, was in dem Fall ein iterative Verfahren ist, brauchen wir den Solver: in den Excel-Optionen, Unter "add-in", bei "Los..." den Solver einschalten.
Dann ist im Menü "Daten" ganz rechts der Solver verfügbar. Diese wird gestartet und die Parameter eingegeben:
- Q5 ist dein Ziel-Zelle, den es zu minimieren gilt (die 2 ersten Parametern)
- L5:P5 sind die veränderbare Zellen
- als Bedingung:
-- L5:P5 nehmen nur ganzzählige Werte an ("int")
-- L5:P5 darf nur => 0 sein
-- R7:R10 darf nur => 0 sein
-- die Lösungsmethode sollte Simplex-LP sein (eventuell im Netz nachlesen)
- "Lösen" drücken und Solver-Lösung akzeptieren (es ändern sich nur die Werte in L5:P5)

In deinem Beispiel (der sicher sehr vereinfacht ist), ist die Lösung quasi offensichtlich: 0-2-1-0-2, also 5 Modulen insgesamt.
Würden die Modulen je ein Preis haben, würden wir nicht die Anzahl summieren sondern eine Kostfunktion in Q5 bilden (Summe von Menge i * Preis i) und diese auf genau dieselben Art minimieren.

VG
Yal

AW: Iterative Mengenermittlung

11.12.2025 19:59:51

UWCEurope

Hi Yal,

danke für deine Antwort. Ich werde das später mal probieren. Kann ich die Reinfolge auch so ändern das die Eingabe vorne steht?
Ja das ganze wird dann später mit Kosten pro Modul verknüpft.

Ist es auch möglich das ganze mehrfach in einem Tabellenblatt zu machen? Es kann sein das das ganze 10mal gebraucht wird aber nicht für einen Schaltschrank sondern für 10 verschiedene.
Kann man den Solver auch über einen Button z.B. berechne starten. Quasi hinter jedem Bereich ?

gruß Olli

AW: Iterative Mengenermittlung

11.12.2025 20:14:40

Yal

Hallo Olli,

wenn es 10 mal dieselbe Umfang wie im Beispiel, würde ich kein Solver verwenden: da je bestimmte Module nur AI-nur DO-nur AO können, zwingen diese Module zu einem Basis-Bestückung. Ob darauf ein Modul1 noch gebrauch wird, hängt nur noch daran, ob den DI-Bedarf abgedeckt ist.

Die Solver-Einstellungen werden mit der Datei gespeichert. Ob mehrere Solver in einer Datei funktionieren, ohne sich in die Queere zu kommen, weiß ich nicht, gehe eher nicht davon aus. In dem Fall würde es Sinn, eine Datei pro Solver-Konstellation zu haben (Durch Kopie der Datei schnell gemacht).

Die Platzierung der Blöcke ist nur für den Übersicht relevant. Wichtig ist, dass die Formeln und die Parametrisierung der Solver passen. Also make it as you like.

Per Knopfdruck starten: geht. Makrorekorder starten, Solver anstossen (nur das Lösen, nicht die Parametrisierung), Makrorekorder stoppen, Code anschauen.
Ich würde aber darauf verzichten. Es ist wichtig, vor jedem Durchlauf sich die Parameter vor Augen zu nehmen und prüfen, dass keine Änderung der Parameter stattfand. Blödsinn kommt sehr schnell zustande. Wenn dahinter teuere Bestellung anstehen, muss man die Anstrengung auf sich nehmen. Ausserdem hat es einen -wichtigen- Lerneffekt durch Wiederholung (aber nur meine Meinung. Aus 30 Jahre Erfahrung).

VG
Yal

AW: Iterative Mengenermittlung

12.12.2025 21:47:43

Oliver

Kann man das lösen auch mit vba hin bekommen? So könnte man ja mehre Buttons in einer Tabelle haben

Gruß Olli

AW: Iterative Mengenermittlung

14.12.2025 01:24:35

Yal

Hallo Olli,

ja, es wurde gehen. Da es sich um eine einfache lineare Optimierung (LO), gibt es sicher dafür Lösungen im Netz oder über genAI.
Du müsstest dich aber in der Methodik der LO einarbeiten, um beurteilen zu können, was diese Programmierungen machen und diese an deiner Datenkonstellation anzupassen.

Der Solver selbst ist über den Makrorekorder komplett steuerbar. Es sollte so möglich, mehrere Solver-Parametrisierung in einer Datei zu steuern. Hier müsstest Du ein Grundwissen über den Solver und deren Umsetzung in VBA dir eineignen.

VG
Yal

AW: Iterative Mengenermittlung

14.12.2025 14:49:28

Oliver

Kann man excel eigentlich selbst Formeln erstellen lassen?

AW: Iterative Mengenermittlung

14.12.2025 20:53:14

Yal

Hallo Olli,

nein, was man heutzutage von KI kennt, ist noch nicht auf Excel-Ebene angekommen. Du kannst aber mit chatGPT oder ähnliches (also außerhalb von Excel), dir einen VBA-Code herstellen lassen. Du musst aber weiterhin in der Lage sein, diesen Code zu verstehen und bei Bedarf zu korrigieren.
Es ist auch gut so, weil sonst wärst Du (aber nicht nur Du) sehr schnell arbeitslos.

Außerdem lässt sich deine Fragestellung nicht "nur" mit Formeln lösen.

VG
Yal

Iterative Mengenermittlung

Forumthread: Iterative Mengenermittlung

12
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Iterative Mengenermittlung

Forumthread: Iterative Mengenermittlung

12 Beiträge zum Forumthread Beiträge zu diesem Forumthread

12
Beiträge zum Forumthread

Beiträge zu diesem Forumthread