, Excel. , , , , , , . . , . , , , . , , , , , . , .
. 1. , , . 2. () , , , . . 3. , ($, , .), , .
@ , , , . , %, . Excel , , %. , , , 20%, 0,2, , 100%, 1. , .
, .
__=_ * __/100%.
Excel __=_ * __ .
.. . 100% . , , 100%º1.
, 20% 500: =500*20%=500*0,2=100.
Excel .
. , (, ).
_=_ _ *
_=_ * (1).
, 500 ., 20%. =500(120%)=500(100%20%)=500(10,2). Excel .
: _=_ * (1+).
20% 500 . =500(1+20%)=500(100%+20%)=500(1+0,2).
|
|
, , , (, 3 4), =3*(14) =3*(1+4) . , , , ( 4). , () - , ().
. , ( %) , ( ) / . , 200+10% 220 (.. ), , , . , , 200+200*10%/100%. Excel , .
, . . 5.0 , D. . ( 2 . ) ( 3 . ).
= * ___ + * __.
D3=80*2+4*3.
A | B | C | D | D | |||
. | . | ||||||
1 | =B3*2+C3*3 | ||||||
2 | =B4*2+C4*3 | ||||||
3 | =B5*2+C5*3 | ||||||
4 | =B6*2+C6*3 | ||||||
=(D3:D6) | |||||||
. 5.0 | |||||||
, , . , . , . , , , (.. ), . D3=B3*2+ C3*3. ( ) D3=B3*2+C3*3 . D4=B4*2+C4*3, D5=B5*2+C5*3 .. .
A | B | C | D | D | ||
/c: | 2,0/ | : | 3,0/ | 3,0/ | ||
. | . | |||||
1 | 80 | 172,0 | =B5*B$2+C5*D$2 | |||
2 | 60 | 129,0 | =B6*B$2+C6*D$2 | |||
3 | 110 | 235,0 | =B7*B$2+C7*D$2 | |||
4 | 120 | 264,0 | =B8*B$2+C8*D$2 | |||
370 | 800,0 | =(D5:D8) | ||||
. 5.0 |
. , ( ), , . . 2 D2 (. 5.0), (2 . 3 .) . , (D5=B5*B2+C5*D2), . , D6, D7 .. :
|
|
D5=B5*B2+C5*D2
D6=B6* B3 +C6* D3,
D7=B7* B4 +C6* D4 ..
( ). , , 2 2, 3. . . , () $ (D5=B5*B$2+C5* D$2).
A | B | B | ||
: | ||||
10% | 100,00 | =A4*$B$1 | ||
15% | 150,00 | =A5*$B$1 | ||
20% | 200,00 | =A6*$B$1 | ||
30% | 300,00 | =A7*$B$1 | ||
750,00 | =(B4:B7) | |||
. 5.1 |
D5=B5*B$2+C5*D$2
D6=B6*B$2+C6*D$2,
D7=B7*B$2+C6*D$2 ..
2 B$2 D$2 ( . 5.0).
. ( ) 4- 5- 8- 9- (D5:D8). , , .. D9=(D4:D8). ( Excel-2000), ( ). , (, ) , ( ) , . 9 9- 10- D11= (D4:D10), .
, (. @ 4.16 ) D9= (D$4:(D:D;()1)), ,
D9=((D:D;5):(D:D;()1)).
(, Ctrl+) .
, , (), , .
|
|
. , . 2 0,0/, D2 0,0/, 5-9 0, D5-D9 0,0.
5.1. . , 1000 ( 1), . , , 10%, 15%, 20% 30% . ( ) . 5.1. =*. .
5.2. ( ). , , , . . :
=*___ =___ + .
. ( 5). .
Excel ()
=(; __).
, , B7=(B1;A4:A7). . 5.2 (, , ), . , , , =(B$1; A$4:A7). , =(1000;{0,1;0,15;0,2;0,3}).
A | B | C | B | C | A | B | |||
: | : | ||||||||
10% | 100,0 | 1100,0 | =A4*B1 | =B1+B4 | 10% | =(B$1;A$4:A4) | |||
15% | 165,0 | 1265,0 | =A5*C4 | =B5+C4 | 15% | =(B$1;A$4:A5) | |||
20% | 253,0 | 1518,0 | =A6*C5 | =B6+C5 | 20% | =(B$1;A$4:A6) | |||
30% | 455,4 | 1973,4 | =A7*C6 | =B7+C6 | 30% | =(B$1;A$4:A7) | |||
973,4 | =(B4:B7) | ||||||||
. 5.2 | . 5.2 |
5.3. . ( ). (.. _/_),
_=/_*_.
, 8=(B4:B7). C4=B$1/B$8*B4. , , C8=(C4:C7). , (1000), .. . . 5.3 .
|
|
A | B | C | B | C | ||
: | ||||||
. | . | |||||
178,57 | =B$1/B$8*B4 | |||||
1,1 | 196,43 | 1,1 | =B$1/B$8*B5 | |||
357,14 | =B$1/B$8*B6 | |||||
1,5 | 267,86 | 1,5 | =B$1/B$8*B7 | |||
5,6 | 1000,00 | =(B4:B7) | =(C4:C7) | |||
. | 5.3 |
A | B | C | D | |
: | ||||
. | ||||
173,21 | ||||
1,1 | 210,54 | |||
306,43 | ||||
1,5 | 309,82 | |||
5,6 | 1000,00 | |||
. | 5.3 |
(. 5.3). , . , , . , - , . .
A | B | C | D | |
: | ||||
. | ||||
=(B$1B$8)/C$8*C4+B4 | ||||
1,1 | =(B$1B$8)/C$8*C5+B5 | |||
=(B$1B$8)/C$8*C6+B6 | ||||
1,5 | =(B$1B$8)/C$8*C7+B7 | |||
=(B4:B7) | =(C4:C7) | =(D4:D7) | ||
. 5.3 |
=(__)/_*_+_. , , , . D4=(B$1B$8)/C$8 *C4+B4. (. 5.3) . , (.. ). , ( 590), . , , ,
D4=(B$8>=B$1;
(B$1B$8)/C$8*C4+B4;B$1/C$8*C4).
, , , .
A | B | C | D | E | C | D | E | |||
530. | 100,00% | 1230. | 100,00% | = (B5:B10 | = (C5:C10) | = (D5:D10) | = (E5:E10) | |||
1 | 100. | 18,87% | 100. | 8,13% | 100. | =B5/B$4 | 100. | =D5/D$4 | ||
2 | 150. | 28,30% | 150. | 12,20% | 150. | =B6/B$4 | 150. | =D6/D$4 | ||
3 | 200. | 37,74% | 200. | 16,26% | 200. | =B7/B$4 | 200. | =D7/D$4 | ||
4 | 80. | 15,09% | 80. | 6,50% | 80. | =B8/B$4 | 80. | =D8/D$4 | ||
1 | 400. | 32,52% | 400. | =D9/D$4 | ||||||
2 | 300. | 24,39% | . 5.4 | 300. | =D10/D$4 |
5.4. . . , () ( ) ( ). , ( ) , 700 . ( 400 300 . . ). . 4 (, B4=(B5:B10)). C5=B5/B$4 E5=D5/D$4 . , 32,52% 24,39% , . 100% 4.
|
|
A | B | C | C | ||
100% | 100% | =(3:C8) | |||
1 | 20% | 14% | =(1(C$7+C$8))*B3 | ||
2 | 25% | 18% | =(1(C$7+C$8))*B4 | ||
3 | 20% | 14% | =(1(C$7+C$8))*B5 | ||
4 | 35% | 25% | =(1(C$7+C$8))*B6 | ||
1 | 20% | 20% | |||
2 | 10% | . 5.5 | 10% |
5.5. (). . , , (- ) . , , , , . , - , 20% 10% . , , 100%(20%+10%)=70%. ( 3, 4, 5, 6) :
___=(1__)*__.
3=(1(C$7+C$8))*B3. (2=100%).
5.6. . . , (1200 . .), , ( ). , ( ).
=__ * __ __.
A | B | C | D | E | D | E | ||||
... | - | % | % | |||||||
1 | 25% | 200 | 25% | =C$8*B3C3 | =(C3+D3)/C$8 | |||||
2 | 30% | 30% | =C$8*B4C4 | =(C4+D4)/C$8 | ||||||
3 | 20% | 20% | =C$8*B5C5 | =(C5+D5)/C$8 | ||||||
4 | 10% | 10% | =C$8*B6C6 | =(C6+D6)/C$8 | ||||||
5 | 15% | 420 | 15% | =C$8*B7C7 | =(C7+D7)/C$8 | |||||
100% | 100% | . 5.6 | =(D3:D7) | =(E3:E7) |