= + 8:00+( <8:00; 0; 1)
F5=(E5)+E$2+((E5;1)<=E$2;0;1).
() 5 (.. ) _ .
, . ( ), . , , 24.
=(<; 0;(;)(;))*24
G5=(C5<E5;0;(C5;F5)(B5;E5))*24.
, .
= + +30,
H5=(D5G5)*H$2+G5*2*H$2+(G5>0;30).
. , , , , . , . F1 =() _. , F9. 5, 6, 7
1 =(($G$1>B6;$G$1<=C6);1),
( ) . , , F9, , ( ).
A | B | C | D | E | F | G | H | |
$200000 | ||||||||
: | 1 | 100 | ||||||
% : | 5% | 10% | 5% | 3% | ||||
. | . | 1 | ||||||
70 | 85% | 59,5 | $ 34724 | $496 | ||||
160 | 97% | 155,2 | $ 90575 | $566 | ||||
60 | 100% | 60,0 | $ 35016 | $584 | ||||
80 | 85% | 68,0 | $ 39685 | $496 | ||||
370 | 342,7 | $200000 | ||||||
. | 5.33 |
5.33. . , , 200 000$, , . , () , ( 100 .) , . . , , . . . 100% .
|
|
E6=100%(C6=1;$C$3)(C6=4;$C$3)(D6="";$B$3)(B6>100;$E$3).
A | B | C | D | E | F | G | H | I | J | |
: | ||||||||||
20 | ||||||||||
15 | ||||||||||
3/ | ||||||||||
4/ | ||||||||||
. | 3/ | |||||||||
10% | ||||||||||
- | ||||||||||
/ | . | (/) | . | |||||||
100 | + | 270 | 28 | 20 | 318 | |||||
60 | + | 180 | 0 | 15 | 195 | |||||
70 | + | 189 | 16 | 0 | 205 | |||||
80 | 240 | 9 | 20 | 269 | ||||||
90 | + | 243 | 32 | 0 | 275 | |||||
400 | 1122 | 85 | 55 | 1262 | ||||||
. | 5.3 |
, , F6=B6*E6. , 342 370. . , G6=$B$1/$F$10*F6, H6=G6/B6. , (G10) (B1).
5.34. . , .
(. ):
( ).
( +) . . , , .
. . ( +) 10% ( , ).
:
=_ * __*( , )
|
|
G11=B11*$C$6*(E11="+";1+$C$7;1).
= ( , 1, 0)*_*
( , _ , _)
H11=(D11<>"+";1;0)*C11*(E11="+";$C$5;$C$4).
= ( , , ( , , 0))
I11=(F11="";$C$2;(F11="";$C$3;0)).
, D11=ר(D11:D15;"+"), E11=ר(E11:E15;"+") F11=ר(F11:F15).
.
J11=(G11:I11), B16= (B11:B15), C16=(C11:C15) ..
5.35. . , . , , . , ( ) , . , , , . , ,
G2=B2&C2&D2&E2&F2 G2=(B2;C2;D2;E2;F2).
. (), B7&C7&D7&E7&F7 ,
G7=(B7&C7&D7&E7&F7;G$2:H$4;2;0).
3 , ( #/). , , , .
, , .
A | B | C | D | E | F | G | H | |||
1 | 2 | 3 | 4 | 5 | ||||||
1 | 2 | 3 | 4 | 5 | ||||||
1 | ||||||||||
2 | ||||||||||
3 | #/ | . 5.35 |
A | B | C | D | E | F | G | H | |
04.01 | ||||||||
05.01 | ||||||||
06.01 | 04.01 | 13.01 | ||||||
07.01 | 13.01 | 15.01 | ||||||
08.01 | 14.01 | |||||||
11.01 | 15.01 | 15.01 | ||||||
12.01 | 16.01 | #/ | ||||||
13.01 | 17.01 | #/ | ||||||
14.01 | ||||||||
15.01 | ||||||||
. | 5.36 |
5.36. . , , . , , , , . 1% . , . , () () . , . . , , , ( ), . , ( ), . . (D4;A:A;0)+F4 , (F4). ,
|
|
=(A:A;(D4;A:A;0)+F4;1).
, . , 14 23 , , 9 23 , .. 32- . 15 , . , , " ",
G4=((A:A;(D4;A:A;0)+F4;1)>0;(A:A;(D4;A:A;0)+F4;1);" ").
" ", .
, H4=E4*(1+F4*1%).
. . , Exel #/ (, G8 G9). D ( D4)
=((D4;$A:$A;0))
, , , .
:
1. .
2. (, ).
, , , . () , , .
A | B | C | D | E | |
- | - | ||||
1 | |||||
2 | 2,7 | ||||
3 | |||||
. 5.37 | . |
5.37. . , , ( ) , , , , . , , .
|
|
, 70, 80 90 ( 70), 10, 40 60 ( 60), 1, 3 5 ( 1). . ( 7-10, 11-14, 15-18 ). , . 2 . 3 , ; 4 , ; 5 , . (), .
, , . , , . 2
=( 70 .;
2 . 70 .;
70 . 70 .)
=(!C8:E10; (C2;!B8:B10;1); (D2;!C7:E7;1)).
A | B | C | D | E | |||
70 | 80 | 90 | |||||
!C8:E10 | !C12:E14 | !C16:E18 | |||||
. | !B8:B10 | !B12:B14 | !B16:B18 | ||||
!C7:E7 | !C11:E11 | !C15:E15 | |||||
70 | \ | 10 | 40 | 60 | |||
1 | 1 | 2 | 3 | ||||
3 | 1,1 | 1,5 | 1,7 | ||||
5 | 1,8 | 2 | 2,5 | ||||
80 | \ | 10 | 40 | 60 | |||
1 | 1,5 | 3 | 4,5 | ||||
3 | 1,65 | 2,25 | 2,55 | ||||
5 | 2,7 | 3 | 3,75 | ||||
90 | \ | 10 | 40 | 60 | |||
1 | 2 | 4 | 6 | ||||
3 | 2,2 | 3 |
| ||||
5 | 3,6 | 4 | 5 |
, 70 , 1.
, , ()
=((!B3); (C2;(!B4);1); (D2;(!B5);1)).
( 70º), , .
. , !$B$3, $B$4 $B$5 , ()
E2= (((B2;!$B$2:$D$5;2;1));
(C2;((B2;!$B$2:$D$5;3;1));1);
(D2;((B2;!$B$2:$D$5;4;1));1)).
. .
. 1. , , , , , . 2. , .
, , () . 70 , 80- 90 (. 6.38). 70, 80 90 . .
=( < 80, 70;
( < 90, 80;
90)).
E2=(B2<80;(70!$A$2:$D$5; (C2;70!$A$2:$A$5;1);(D2;70!$A$2:$D$2;1));
(B2<90;(80!$A$2:$D$5; (C2;80!$A$2:$A$5;1);(D2;80!$A$2:$D$2;1));