solver voor een reeks van cellen (kolommen)

[lubbejeroen]

Hallo,

Een vraagje over solver. Ik heb een tabel waarvan ik de waarden van een bepaalde rij automatisch (of in ieder geval in één keer) door Excel wil laten optimaliseren

Wanneer ik solver gebruik kan ik wel 1 bepaalde cel (dus 1 kolom) oplossen, maar dit moet ik dan handmatig voor elke volgende kolom doen. Zie het bijgevoegde voorbeeld in de excelfile (als ZIP file)

Settings in excel solver (voor 1e kolom) voor de excelfile:

set target cel: B15
by changing cell: B4
subject to constraints :B15 <= B14

Dit levert een oplossing of maar hoe kan ik ditzelfde in één keer bereiken voor C15, D15, etc

Alvast bedankt !

Groeten Jeroen

 

[Demeter]

Bedoel je zoiets (voor cel B15)

=ALS((B2-B5)*B16<=B14;(B2-B5)*B16;B14)

Trek deze door naar de andere cellen.


Groet,
Ferenc

 

[lubbejeroen]

Dit is helaas niet het antwoord: de waarde van cel B en C hangt via een flink aantal formules af van de waarde van cel A: een directe algebraische oplossing is niet mogelijk..

 

[Demeter]

Denk dat ik de vraagstelling niet geheel vat.

Uit de aantekeningen in je file maak ik op dat je opzoek ben naar een warde welke niet over de max. constraint heen gaat. Dit gebeurt door verscheindene berekeningen welke naast de waarde vanuit cel B15 wordt gelegd. Is deze waarde kleiner of gelijk aan B15 dan wordt deze waarde aangehouden anders is B15 de geoptimaliseerde waarde.

De tussenliggende formules heb ik niet bekeken.
Volgens mij probeer je te bekijken welke max. waardes je kan hebben voordat iets bezwijkt!!!


Groet,
Ferenc

 

[GiMe]

Jeroen,

Hierbij macro die bij mij perfect doet war er door jou werd gevraagd.

Sub Macro2()

    SolverOk SetCell:="$B$15", MaxMinVal:=1, ValueOf:="0", ByChange:="$B$4"
    SolverDelete CellRef:="$B$15", Relation:=1, FormulaText:="$B$14"
    SolverAdd CellRef:="$B$15", Relation:=1, FormulaText:="$B$14"
    SolverSolve
    
    SolverOk SetCell:="$c$15", MaxMinVal:=1, ValueOf:="0", ByChange:="$c$4"
    SolverDelete CellRef:="$B$15", Relation:=1, FormulaText:="$B$14"
    SolverAdd CellRef:="$c$15", Relation:=1, FormulaText:="$c$14"
    SolverSolve
    
    SolverOk SetCell:="$d$15", MaxMinVal:=1, ValueOf:="0", ByChange:="$d$4"
    SolverDelete CellRef:="$c$15", Relation:=1, FormulaText:="$c$14"
    SolverAdd CellRef:="$d$15", Relation:=1, FormulaText:="$d$14"
    SolverSolve
    
    SolverOk SetCell:="$e$15", MaxMinVal:=1, ValueOf:="0", ByChange:="$e$4"
    SolverDelete CellRef:="$d$15", Relation:=1, FormulaText:="$d$14"
    SolverAdd CellRef:="$e$15", Relation:=1, FormulaText:="$e$14"
    SolverSolve
    
    SolverOk SetCell:="$f$15", MaxMinVal:=1, ValueOf:="0", ByChange:="$f$4"
    SolverDelete CellRef:="$e$15", Relation:=1, FormulaText:="$e$14"
    SolverAdd CellRef:="$f$15", Relation:=1, FormulaText:="$f$14"
    SolverSolve
    
    SolverOk SetCell:="$g$15", MaxMinVal:=1, ValueOf:="0", ByChange:="$g$4"
    SolverDelete CellRef:="$f$15", Relation:=1, FormulaText:="$f$14"
    SolverAdd CellRef:="$g$15", Relation:=1, FormulaText:="$g$14"
    SolverSolve
    
    SolverOk SetCell:="$h$15", MaxMinVal:=1, ValueOf:="0", ByChange:="$h$4"
    SolverDelete CellRef:="$g$15", Relation:=1, FormulaText:="$g$14"
    SolverAdd CellRef:="$h$15", Relation:=1, FormulaText:="$h$14"
    SolverSolve
    
    SolverOk SetCell:="$i$15", MaxMinVal:=1, ValueOf:="0", ByChange:="$i$4"
    SolverDelete CellRef:="$h$15", Relation:=1, FormulaText:="$h$14"
    SolverAdd CellRef:="$i$15", Relation:=1, FormulaText:="$i$14"
    SolverSolve
End Sub

GiMe

 

[Demeter]

mmmmmmmm,

GiMe,

Kan jij mij uitleggen wat Solver doet?

Thanks,
Ferenc

 

[GiMe]

De Excel Solver zoekt in dit geval naar een waarde in rij(4) die zodanig is dat de waarde in rij(15) zijn maximale waarde bereikt, maar niet groter is dan rij(14).

GiMe

 

[Wigi]

De Excel Sover zoekt in dit geval naar een waarde in rij(4) die zodanig is dat de waarde in rij(15) zijn maximale waarde bereikt, maar niet groter is dan rij(14).

GiMe

Klopt, en aangezien het getal in rij 4 in redelijk wat cellen voorkomt is dit numerisch niet simpel. Vandaar de Solver. Ik zal een van de dagen proberen om deze Solver te programmeren, maar simpel gaat dat niet zijn vrees ik.

Wigi

 

[lubbejeroen]

Bedankt GiMe.

Dit lijkt op wat ik zoek.

Nieuw probleem: hoe maak ik een macro waar ik jouw tekst in kan kopieren ? Heb nog nooit met macro's gewerkt....

Groeten Jeroen

 

[Demeter]

Open je file
druk op linker alt en F11
Nu ben je in de VB-editor.
Rechtermuisknop op Blad1 in het linkervenster en kies hier:
invoegen/module
Nu verschijnt er een wit vlak aan de rechterkant.
Plak hier de code welke GiMe je heeft gegeven.
Ga terug naar Excel (linker alt + f11)

In excel druk je alt + f8 en kies je voor de macro van GiMe.

Succes,
Ferenc

 

[Wigi]

Open je file
druk op linker alt en F11
Nu ben je in de VB-editor.
Rechtermuisknop op Blad1 in het linkervenster en kies hier:
invoegen/module
Nu verschijnt er een wit vlak aan de rechterkant.
Plak hier de code welke GiMe je heeft gegeven.
Ga terug naar Excel (linker alt + f11)

In excel druk je alt + f8 en kies je voor de macro van GiMe.

Succes,
Ferenc

Klopt Ferenc.

Jeroen

vergeet in VBA bij Tools > References niet om een vinkje te zetten bij Solver.

Wigi

 

[Wigi]

Betere code:

Sub Macro2()
    Dim c As Range, MyRange As Range
    Set MyRange = Range("B15", Range("B15").End(xlToRight))
    
    With MyRange.Cells(1)
        For Each c In MyRange
            SolverOk SetCell:=c.AddressLocal, MaxMinVal:=1, ValueOf:="0", ByChange:=Cells(4, c.Column).AddressLocal
            If c.Column = .Column Then
                SolverDelete CellRef:=.AddressLocal, Relation:=1, FormulaText:=.Offset(-1).AddressLocal
                SolverDelete CellRef:=.End(xlToRight).AddressLocal, Relation:=1, FormulaText:=.End(xlToRight).Offset(-1).AddressLocal
            Else
                SolverDelete CellRef:=c.Offset(, -1).AddressLocal, Relation:=1, FormulaText:=c.Offset(-1, -1).AddressLocal
            End If
            SolverAdd CellRef:=c.AddressLocal, Relation:=1, FormulaText:=c.Offset(-1).AddressLocal
            SolverSolve
        Next
    End With
End Sub

De absolute verwijzingen zijn eruit nu.

Wigi

 

[GiMe]

Wigi,

Hierin ben je ONOVERTROFFEN !!

GiMe

 

[Wigi]

Wigi,

Hierin ben je ONOVERTROFFEN !!

GiMe

Zoals jij ook wel ervaren hebt met dit probleem, was de moeilijkheid het deleten van de voorwaarde: SolverDelete.

Ofwel staat die ingesteld voor de eerste kolom (B), ofwel voor de laatste kolom, ofwel staat er niets ingevuld. Ik wist het eerst ook niet, maar je kan gewoon meerdere voorwaarden opgeven, en als dat niet van toepassing is doet VBA niets. Daarom staat er dus 3 keer een SolverDelete. Als er niets te deleten valt, gaat VBA gewoon verder met de volgende regel code, zonder een foutmelding. En dat is natuulijk wat we moesten hebben .

Bedankt.

Wigi