8
ֲ ò '
. ' ; ' ' .
1. . .
2. .
3. ' ?
4. .
5. .
6. , .
7. ? .
8. , ' - .
1. , , , . 30000 . ', MS Excel ' , .
. 9 , 7 , 10 , 3 , 1 ' , 1 , 1 , 1 . 450 ., 1,5 , 3 , 30 . , , ' 2 , 40 . ᳭ , 4 , 20 . .
2. , , . '. MS Excel ' ' .
|
|
. ϳ . , 7 300 . 3200 150 . 1 . 3 㳭 . 1 1 : 25%,
1 20 %, 2 45 %, 10 %. 1 0,6 : 50%, 1 27 %, 2 13%, 10%. 1 0,3 : 63 %, 1 18 %, 2 9 %, 10 %. ֳ 1 320 ., 1 260 ., 2 220 ., 60 $.
1 25 ., 1 13 . , 1 ( ) , , ( ).
3. . '. MS Excel ' ' .
. : , , . ³ (. 27) .
27
PFXYPCIB ˲ֲ в
' , , , - , , -- .
, , , .
' 1 , , , :
* + ,
,
, .
, , :
, :
|
|
N1 (1 + 1) + N2 (2 + 2) +... + N8 (8 ■ + 8),
N1 ,
N2 ,
1...8 1...8 .
1...8 1...8, Ⳮ N1...N8. ' ( ') , ' Goal Seek (ϳ ) Solver ( ) MS Excel. ' .
, . . ' , .
1. ' , 1, (. 28)
28
ֲʲ Ͳ
1.1. .
1.2. ³ .
1.3. , Ⳮ .
1.4. (450) 0,00 .
2. D .
2.1. D3 =*$$3+3&
2.2. D3 D4:D10.
3. .
4. F , .
4.1. F3 =D3*E3 ( * ).
4.2. F3 F4:F10.
4.3. D F 0,00 .
5. .
5.1. F, - L .
5.2. F12 . .
5.3. . :5 , 30000 . ( F12 30000).
6.
Goal Seek ( ).
6.1. Tools () Goal Seek.
6.2. Set Cell ( ) $F$12.
6.3. value () 30 000.
6.4. By changing cell ( ) $$3 .
7. .
8. ϳ .
9. .
ϳ , . , -, . ' .
. , ' . , . . ' , (' ).
|
|
-(,, z2, z3,..., zm) (,, 2, 3,..., ), . :
y = F(xi,x2,xi...,xn;zl,z2,zi...,zm)->exti.
, , " :
(/=1,2,..., );
j '- (, ); Xj /- (, );
bJk j-ro - ;
;
a j , '- ;
j '- .
, , .
, .
, , , .
, , ' .
' , , , .
' , . (. 29).
29
Ѳ
ֳ 1 | ||||||
320,00 | ||||||
268,00 |
. 29
220,00 | |||||
60,00 | |||||
ֳ . | 150,00 | ||||
ʳ () | |||||
() | |||||
( 1 ) | 25,00 | ||||
( 1 ) | 13,00 | ||||
1. ² Ͳ.
2. 1 .
3. A3 ֳ .
4. 4:7 : , , , .
|
|
5. 4:7 . .
6. 9 ֳ 1 . , 9 $150,00.
7. 11 ʳ (), ȗ3200.
8. 13 (), ² 4200.
9. 15 ( 1 ), 16 25,00.
10. 17 ( 1 ), 18 13,00.
11. ³ , (. 30).
6:8 N6:N8 .
³ L6:N6, . SUM(L6:N6). 䳭 06:08.
G6 , , 1 : =6*6*! $$4 G6.G8. ' 1 , 2 6,16 J6:
, G6:G8, 6:8,16:18, J6:J8 .
³ G6J6 , 6 , 1 . 6 6:8.
Q6 =P6/F6 Q6:Q8.
R6 6-06, S6 R6/F6. ֳ R6:R8 S6:S8 .
6 =P6*S6 6:8. , ( 6:8) , ( 6:8).
Subjects to constaints () Solver. :
$$6<=!$$11, $0$6<=!$$13, $P$6=int, $$6>=0.
, Add (), ' Add Constraint ( ) .
, Cell reference ( ), , . .
, Constraint (), , , . ij Add .
, .
, , Change () Delete () Solver ( ). Solver Options ( ) Solver.
Solve () '. , ' Solver Results ( ) .
, '. Solver
found a solution, All constraints and optimality conditions are satisfied ( , ) Solver could not find a feasible solution ( ).
, Keep Solver Solution ( ) Restore Original Values ( ) , . : Answer (), Sensitivity (), Limits ().
.
.
|
|
7, 7. :
8, 8. :
9 =max(T6:T8), 볭 U6 =if(T6=T9, , ). U6:U8. , , .
, - . 3 :
*, + 52 + 4, -> ;
' - , . ' Solver.
Solver , .
1. (.31):
1) ;
2) ;
3) .
31
ֲ̲
. ,
, . ֳ ( ), 쳭 . Solver , ' , .
2. Solver.
3. Set Target Cell .
4. By Changing Cells . , '.
5. , 1517 2627 .
^
1. , . ' , .
2. . .
3. -, , , - .
4. ' - Goal Seek Solver MS Excel, Project Expert.
g3
9