Excel: Macro voor Numerieke/Iteratieve oplossing

[KFS]

Hallo,

In Excel moet ik numeriek (iteratief) een berekening oplossen. Door in 2 cellen waarden te variëren moet de uitkomst tin 2 andere cellen zo dicht mogelijk 0 naderen. Ikzelf kan geen Macro's schrijven, dus met chatGPT de basis gemaakt. Echter als ik hem de macro wil laten uitbreiden om steeds nauwkeuriger bij het antwoord te komen gaat het mis. (Macro start niet, loopt vast, foutieve toets waarden, komt in een loop)

Wat de Macro moet doen:
Cel AF98 en cel AG98 moeten gevarieerd worden.
Het bereik van cel AF98 loopt van -3,5 tot 2. Het interval is 0,25.
Het bereik van cel AG98 loopt van -3,5 tot 32,5. Het interval is 0,25
De waarde in cel AF98 staat als eerste "vast". Deze begint dus eerst bij -3,5 en dan wordt eerst cel AF98 aangepast totdat deze de waarde 32,5 bereikt. Dan schuift de waarde in cel AF98 0,25 omhoog.
De toets waarden staan in cel U98 en V98. Deze moeten zodicht mogelijk 0 naderen. Omdat de intervallen vrij groot zijn (0,25) is de marge nog 50.

Voor dit deel heeft Chatgpt een werkende macro geschreven. Het antwoord is door de marge van 50 niet nauwkeurig genoeg. Ik post deze macro hieronder.

Nu was mijn idee om de intervallen en de marge eerst groter te houden om het aantal berekeningen te verminderen.
Dus eerst intervallen van 0,5 met een marge van 100 voor cel U98 en een marge van 50 voor cel V98. Zodra hij voldoet aan deze eisen moet hij eerst 1 interval stap "terug gaan" (om te voorkomen dat je voorbij het juiste antwoord gaat).
Dus als hij de eerste keer voldoet aan de eisen in BIJVOORBEELD de situatie AF98 = -2,0 en AG98 = 10,0. Dan begint hij dus eerst weer bij AF98=-2,5 en AG98=9,5.
Vervolgens worden de interval stappen en marge allen gehalveerd. Waarna het proces herhaalt totdat de marge voor cellen U98 en V98 hooguit 1 is.

De onderstaande macro werkt dus met vaste intervallen en marges. Nu moeten deze steeds kleiner worden totdat de marge hooguit 1 is.

Wie o wie weet hoe je de macro op deze manier goed uitbreidt?

Sub FindClosestToZero()
Dim afValue As Double
Dim agValue As Double
Dim uValue As Double
Dim vValue As Double
Dim minDifference As Double
Dim currentDifference As Double
Dim targetDifference As Double
Dim minAFValue As Double
Dim minAGValue As Double
Dim afRange As Double
Dim agRange As Double

afRange = 5.5 ' Het bereik van AF98 (-3,5 tot 2)
agRange = 36 ' Het bereik van AG98 (-3,5 tot 32,5)
targetDifference = 50 ' Marge ten opzichte van nul

minDifference = targetDifference + 1 ' Initieel de marge + 1

For afValue = -3.5 To 2 Step 0.25
For agValue = -3.5 To agRange Step 0.25
Range("AF98").Value = afValue ' Zet de waarde in AF98
Range("AG98").Value = agValue ' Zet de waarde in AG98
uValue = Range("U98").Value ' Haal de waarde van U98 op
vValue = Range("V98").Value ' Haal de waarde van V98 op

currentDifference = Abs(uValue) ' Bepaal het verschil met nul

' Controleer of het huidige verschil kleiner is dan het vorige minimum
If currentDifference < minDifference Then
minDifference = currentDifference ' Bijwerken van het minimum
minAFValue = afValue ' Bijwerken van de waarde in AF98
minAGValue = agValue ' Bijwerken van de waarde in AG98
End If

currentDifference = Abs(vValue) ' Bepaal het verschil met nul

' Controleer of het huidige verschil kleiner is dan het vorige minimum
If currentDifference < minDifference Then
minDifference = currentDifference ' Bijwerken van het minimum
minAFValue = afValue ' Bijwerken van de waarde in AF98
minAGValue = agValue ' Bijwerken van de waarde in AG98
End If

' Controleer of de waarden in U98 en V98 binnen de marge liggen
If Abs(uValue) <= targetDifference And Abs(vValue) <= targetDifference Then
Exit Sub ' Stop de macro
End If
Next agValue
Next afValue

' Plaats de gevonden waarden in de cellen
Range("AF98").Value = minAFValue
Range("AG98").Value = minAGValue
End Sub

Groet,
KFS

[Teun_2]

Excel heeft dit ingebouwd.
Define and solve a problem by using Solver - Microsoft Support
YouTube: Excel Solver - Example and Step-By-Step Explanation

Geen macro nodig.

[Djordjo]

Wat @Teun_2 zegt. In jouw geval kun je als 'objective' instellen dat het product van U98 en V98 gelijk aan 0 moet worden.

[KFS]

De oplosser heb ik geprobeerd. De afwijking blijft alleen groot en afhankelijk van de start waarden krijg ik een ander antwoord.

Kan je oplosser ook een antwoord vinden dat binnen het bereik van -1 tot 1 ligt? nu probeert hij MAX, MIN of exact 0 te bepalen.

[Teun_2]

KFS schreef op vrijdag 30 juni 2023 @ 13:57:
De oplosser heb ik geprobeerd. De afwijking blijft alleen groot en afhankelijk van de start waarden krijg ik een ander antwoord.

Kan je oplosser ook een antwoord vinden dat binnen het bereik van -1 tot 1 ligt? nu probeert hij MAX, MIN of exact 0 te bepalen.

Bedoel je dat je variabelen tussen -1 en 1 mogen liggen? Dat kan je bekomen met je randvoorwaarden. Die 0 is toch wat je wil bekomen?

Hoe ik het probleem zou aanpakken:
1) in een cel die je nog niet gebruikt zet je een formule die de absolute waarde van U98 en V98 optelt. Die wil je zo dicht mogelijk bij 0 hebben. Daar bepaal je je doelfunctie op.
2) Je selecteert cel AF98 en AG98 als variabelcellen die veranderd mogen worden
3) In de randvoorwaarden zet je 4 regels:
AF98 <2
AF98 >-3.5
AG98 > -3.5
AG98<32.5
Indien die 0.25 intervallen ook echt een harde eis zijn (en niet voor een werkbare oplossing in Macro te zijn) zou je 2 cellen kunnen definieren die de rest van deling(AF98)-025 = 0 moet zijn, maar ik vermoed niet dat dat nodig is.

Je zou ook een tabel kunnen maken met als kolomtitels de waardes -3.5 tot 2 in stappen van 0.25 en rijtitels -3.5 tot 32.5 en in de tabel zet je dan de formule om de absolute waarde vab U98 plus de absolute waarde van V98 te berekenen. Zet dan voorwaardelijke opmaak op je tabel en je hebt ook meteen zicht op wat de oplossing moet zijn.

[ Voor 60% gewijzigd door Teun_2 op 02-07-2023 20:53 ]

[KFS]

@Teun_2
Dankjewel voor je voorstel. De cellen die je invult zijn niet gekoppeld aan 1 cel met een formule. Zodra je de 2 waarden hebt ingevuld worden ongeveer 200 cellen berekend (allen in 1 rij). Dus een raster maken zoals je op het laatst voorstelt is geen optie.

Ik snap niet wat je met
"Indien die 0.25 intervallen ook echt een harde eis zijn (en niet voor een werkbare oplossing in Macro te zijn) zou je 2 cellen kunnen definieren die de rest van deling(AF98)-025 = 0 moet zijn, maar ik vermoed niet dat dat nodig is."
bedoelt

De rest heb ik al op die manier aangepakt. De antwoorden variëren dan alleen adv met welke waarden je start. De ene keer stopt hij bij een verschil van -44 andere keer bij 16 en als ik wat exacter bijd e oplossing begin is het antwoord 0.

De Macro waar ik mee begin is op hoofdlijn goed. Als ik de intervallen op 0,01 zet en de marge op 1 dan zou ik na 1 of 2 uur rekenen op het juist antwoord komen. Ik wil de berekening slimmer en daarmee sneller maken

[Teun_2]

@KFS Ik bedoelde of het een eis was dat de 2 inputwaarden meer veelvouden van 0,25 mochten zijn. Dat is het in dit geval niet. Wat je in de macro aan het bouwen bent is een enorm vereenvoudigde versie van de oplosser. Ik begrijp niet goed dat je bedoelt met dan stopt hij bij -44 en 16.

[KFS]

@Teun_2
Als ik aangeef in de oplosser dat "0" de doelwaarde is en hij staat op "Max". Dan geeft de oplosser aan dat het een oplossing heeft gevonden, ook al is het resultaat -44 of 16 of .... dus niet 0.

De macro komt inderdaad neer op een "oplosser". Uiteindelijk wil ik ook meerdere regels onder elkaar zetten. en dan steeds de oplosser opnieuw activeren wordt dan ook erg repeterend werken. Daarbij komt ook nog dat ik de oplosser "op weg moet helpen".

[ Voor 42% gewijzigd door KFS op 03-07-2023 14:57 ]

[Teun_2]

@KFS vreemd. Stuur me gerust een DM om het brondbestand eens door te sturen. Dan kan ik er wel eens naar kijken. Wat jij beschrijft heb ik nog niet meegemaakt. Wat je met de oplosser wel vaak voor hebt, is dat hij getallen uitkomt die heel dicht, maar niet op 0 zitten. Dan heb je vaak iets in wetenschappelijke notatie. Bij opties kan je de precisie wat bijstellen om echt op 0 te komen.

[Lustucru]

Om die macro goed te optimaliseren is het ook van belang om te weten hoe de resultaten afhangen van de input. Is het een continue functie of schiet de uitkomst bij kleine wijzigingen alle kanten op?

[KFS]

Lustucru schreef op dinsdag 4 juli 2023 @ 14:06:
Om die macro goed te optimaliseren is het ook van belang om te weten hoe de resultaten afhangen van de input. Is het een continue functie of schiet de uitkomst bij kleine wijzigingen alle kanten op?

Goede vraag.

Ik geef wel eerst een stukje achtergrond.

Het gaat over de toetsing van een betondoorsnede. De waarden die je invult zijn de rek links en de rek rechts.
Zijn beide waarden negatief (-3,5) dan zit in de volledige doorsnede alleen druk. Is links bijvoorbeeld -3,5 en rechts +3,5 dan zit er naast een drukkracht ook een moment in.

De antwoorden die ik krijg zijn dus de (druk)krachten en momenten.

Je start altijd met links op -3,5 (druk). Dus het antwoord is altijd eerst negatief en loopt op naar positief. Dit kan je gebruiken voor de optimalisatie. Als je bijvoorbeeld -500 aan druk hebt en bij een iteratie sta je op bijvoorbeeld -400 dan kom je nooit meer bij -500, aangezien de waarde stijgt.

De bereiken waar je aan moet denken zijn:
Rek links: -3,5 tot 2
Rek rechts: -3,5 tot 32,5
Krachten: -5000 tot 5000 (hoe grotere de betondoorsnede, hoe groter dit kan zijn)
Momenten: 0 tot 1000 (groter de beton doorsnede, hoe groter dit kan zijn)

Kleine veranderingen in de rek (sprongen van 0,25) heeft een flinke impact in op zowel de krachten als momenten. De sprongen zijn vooral groot als de rekken nog negatief zijn. Denk hierbij aan sprongen van 200 tot 500 voor de kracht en 25 tot 150 voor momenten.

De functie is wel continue, maar niet lineair of parabool. Het moment zal eerst toenemen en daarna weer afnemen. Bij kleine rekken (denk aan -1 tot 1) komt er een soort sinus vorm in de grafiek.

[ Voor 6% gewijzigd door KFS op 05-07-2023 10:03 ]

[Djordjo]

KFS schreef op vrijdag 30 juni 2023 @ 13:57:
De oplosser heb ik geprobeerd. De afwijking blijft alleen groot en afhankelijk van de start waarden krijg ik een ander antwoord.

Da's vreemd, welke oplossingsmethode heb je bij de solver gebruikt?

Verder zou je ook randvoorwaarden kunnen toevoegen om tot een betere oplossing te komen. Mijn kennis van druk- en trekkrachtformules van gewapend (hoop ik) beton is te ver weggezakt om concrete adviezen te geven, maar kun je bij voorbaat niet bepaalde combinaties van input uitsluiten op basis van te groot moment?

_{pun intended}

[KFS]

Teun heeft mij verder geholpen. Je kon in de solver nog een andere oplossings methode selecteren. Die werkt beter. Duurt alleen wel lang