1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
| Sub Макрос2()
Range("B16").ClearContents
Range("B16").FormulaR1C1 = "=SUMPRODUCT(R[-12]C:R[-9]C[3],R[-6]C:R[-3]C[3])"
Range("F10").ClearContents
Range("F10").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
Range("F10").ClearContents
Range("F10").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
Range("F11").ClearContents
Range("F11").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
Range("F12").ClearContents
Range("F12").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
Range("F13").ClearContents
Range("F13").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
Range("B14").ClearContents
Range("B14").FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
Range("C14").ClearContents
Range("C14").FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
Range("D14").ClearContents
Range("D14").FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
Range("E14").ClearContents
Range("E14").FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
SolverOk SetCell:=Range("$B$16"), MaxMinVal:=2, ValueOf:="0", ByChange:=Range("$B$10:$E$13")
SolverAdd CellRef:=Range("$F$10"), Relation:=2, FormulaText:=Range("$G$10")
SolverAdd CellRef:=Range("$F$11"), Relation:=2, FormulaText:=Range("$G$11")
SolverAdd CellRef:=Range("$F$12"), Relation:=2, FormulaText:=Range("$G$12")
SolverAdd CellRef:=Range("$F$13"), Relation:=2, FormulaText:=Range("$G$13")
SolverAdd CellRef:=Range("$B$14"), Relation:=2, FormulaText:=Range("$B$15")
SolverAdd CellRef:=Range("$C$14"), Relation:=2, FormulaText:=Range("$C$15")
SolverAdd CellRef:=Range("$D$14"), Relation:=2, FormulaText:=Range("$D$15")
SolverAdd CellRef:=Range("$E$14"), Relation:=2, FormulaText:=Range("$E$15")
SolverAdd CellRef:=Range("$E$14"), Relation:=2, FormulaText:=Range("$E$15")
SolverAdd CellRef:=Range("$E$14"), Relation:=2, FormulaText:=Range("$E$15")
SolverOk SetCell:=Range("$B$16"), MaxMinVal:=2, ValueOf:="0", ByChange:=Range("$B$10:$E$13")
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _
:=True, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True
SolverOk SetCell:=Range("$B$16"), MaxMinVal:=2, ValueOf:="0", ByChange:=Range("$B$10:$E$13")
SolverSolve
End Sub |
(
