|
こんばんは。
こんなのでどうでしょう。
いつも解が見つかるとは限りませんが。
Sub sample()
Range("A1") = 100
Range("A2") = 199
Range("A3") = 300
Range("A4") = 400
Range("A5") = 500
Range("B6") = 200
Range("C6") = 600
Range("D6") = 700
Range("B7:D7").FormulaR1C1 = "=SUMPRODUCT(R1C1:R5C1,R[-6]C:R[-2]C)"
Range("E1:E5").FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
Range("B8:D8").FormulaR1C1 = "=R[-2]C-R[-1]C"
Range("B9").FormulaR1C1 = "=SUM(R[-1]C:R[-1]C[2])"
Range("B10").FormulaR1C1 = "=SUM(R[-9]C:R[-5]C[2])"
SolverReset
SolverOk SetCell:="$B$9", MaxMinVal:=2, ByChange:="$B$1:$D$5"
SolverAdd CellRef:="$B$10", Relation:=2, FormulaText:=5
SolverAdd CellRef:="$B$8:$D$8", Relation:=3, FormulaText:=0
SolverAdd CellRef:="$E$1:$E$5", Relation:=2, FormulaText:=1
SolverAdd CellRef:="$B$1:$D$5", Relation:=4
SolverSolve UserFinish:=True
SolverReset
SolverOk SetCell:="$B$9", MaxMinVal:=2, ByChange:="$B$1:$D$5"
SolverAdd CellRef:="$B$10", Relation:=2, FormulaText:=5
SolverAdd CellRef:="$B$8:$D$8", Relation:=3, FormulaText:=0
SolverAdd CellRef:="$E$1:$E$5", Relation:=2, FormulaText:=1
SolverAdd CellRef:="$B$1:$D$5", Relation:=5
SolverSolve
End Sub
|
|