. , , ( ) (). , . . , . , 1 0 .
( . 4.8-1).
(7;B2:F4;3;)=12
B2:F4 , 7. , , .
(_ 7;_ B2:F4;
,____ 3; ___ ).
4, 12. (7;B2:F4;3;0) 0 .
(7;B2:F4;3;1) (7;B2:F4;3) , (#/), . , .
(25;B3:F4;2;)=45
B3:F4 , 25. 3:F3 , 12 3, (30) (25). , . 4.
(25;B3:F4;2;1), 1 .
ü ( ; ; [; ])
. .
A | B | C | |
. 4.8-2 |
().
( . 4.8-2):
(6;B2:C6;2;0)=(6;B2:C6;2;1)= 6 , ( ) C.
(7;B2:C6;2;1)= 7 , , 6 () C.
(15;B2:C6;2;1)= 15 . 11. .
(1;B2:C6;2;0)= #/, (7;B2:C6;2;0)= #/,
(15;B2:C6;2;0)= #/, (1;B2:C6;2;1)= #/.
1, 7 15 ( ), ( ). , , 3.
|
|
. ()/() , . (. 4.8-3) . , . ( ) C1:E1.
A | B | C | D | E | |
: | 6. | 10. | 12. | ||
10. | 11. | 12. | 13. | ||
6. | 9. | 10. | 11. | ||
9. | 11. | 12. | 13. | ||
. 4.8-3 |
4 :
1
=(B4;$C$1:$E$1;1;0)
. . , ( ).
. (. 4.8-4) , ().
( 8) (D5) . ( ).
C8 (.. 42 .)
_=(_;_; _; _)
8=(8;B$4:E$5;2;0), 8 B4:E5. , 8.
, , . , , . , ( ) 8=(B8;{1;2;3;4:30;35;42;50};2;0).
A | B | C | D | |
. 4.8-5 |
A | B | C | D | E | ||
. 4.8-4
, , .
. , , ( 29 ), (). (. ), 8= (8;B$4:E$5;2;0) 8=(8;B$5;C$5;D$5;E$5).
|
|
. () (. 4-8.5).
-, ( 2:5). , , (, 8) ( 8) , , . . 0.
8=(A8;A$2:B$5;2;0).
, , , #/ ( ). , ,
_=( , 0., ).
B8=(((A8;A$2:B$5;2;0));0;(A8;A$2:B$5;2;0)).
() (. 4.13). : D8=B8-C8.
. ( ) ()/() . =(8;B$4:E$5;2;0) , =(8; B$4:E$5;2;1), . , , - 0. . , (. 4.8-4), Excel . =1, . , .
ü (; ; ; ; )
, . , . , ( ()).
. ((A1;0;1;1;3))=(B1:D1).
A1 0 1 (.. A2) (.. B1:D1).
ü () .
: (A1:C4) = 3 ( ),
({1;2;3:4;5;6}) = 3 ( ).
ü () .
: (A1:C4) = 4 ( ),
({1;2;3:4;5;6}) = 2 ( ).
ü ( ; ; [ ]; [ ]; [ ])
, .
:
1 (, $A$1);
2 , (A$1);
3 , ($A1);
4 (A1).
, ( 1).
. ( ) 1; 11.
.
: (2;3) = "$C$2", (2;3;2) = "C$2",
(2;3;2;) = "R2C[3]", (2;3;1;;"1") = "1!R2C3".
ü ( ; ) , .
, 1, R1C1, , . , #!.
|
|
A | B | C | D | E | |
. 4.8-6 |
, , ( A1, R1C1).
, , , . : A1 "B2", B2 8, (A1) = 8.
Excel R1C1 R3C5 R2C2, 2, 3*(R3C5;) = 6.
ü (; ; ) , .
A | B | C | D | E | F | |
. | ||||||
0 | 1,0 | 2,0 | 3,0 | 5,0 | ||
5 | 1,2 | 2,3 | 3,5 | 5,5 | ||
10 | 2,0 | 2,5 | 3,8 | 6,0 | ||
15 | 2,2 | 2,7 | 4,4 | 6,7 | ||
. | 4.8-7 |
(), () (, ), . /, , , . , (;;). / 0, /.
( . 4.8-6).
=(B2:B5;3)=B4=9,
=(B5:E5;;4)=E5=16,
=(B2:E5;3;2)=C4=10,
=((B2:E5;2;2):(B2:E5;3;4))=(C3:E4)=54,
=((B2:B5;0))=(B2:B5)=28.
() .
. (. 4.8-7) ( . .) . 4:7 ## , .., , , ( 0, 5, 10, 15).
, 8 . 3,5 ( ), C4:F7. ,
($C$4:$F$7;2;3)=3,5.
. , . (3) ( )
=("";$C$3:$F$4;0)), (2) =(8;$B$4:$B$7;1).
, .
=($C$4:$F$7;(8;$B$4:$B$7;1);("";$C$3:$F$4;0)).
, , ( 8), , .
ü ([]) .
|
|
: (A3) = 1.
ü ([]) . : (A3)=3.
() () .
. (.4.8.8) . =(A2:A4;((B2:B4);B2:B4)), . () , .
A | B | C | D | E | F | G | ||||||
. 4. | 8-8 | #/ | . 4.8-9 |
. , (. 4.8-9), ( . .) . .
B5=($A$2:$A$4;((B2:B4);B2:B4;0)),
G1=($B$1:$F$1;((B2:F2);B2:F2;0)).
, .
ü ( ; [; ]) , . , . .
ü ר( ) . , .
ü ר( ) , , ("").
ü ר( ; ) , .
ר() () , . , .. . ( . 4.9-1, ):
(A2:A5;"";B2:B5)=34 , ;
ר(B2:B5;">20")=2 , 20 ;
ר(A2:A5;"")=2 ;
ר(A2:A5;A3)=2 .
ü (1;2;...) .
: (B2:B5)=(23+12+20+22)/4.
ü (1;2;...) . , 0. : (12;7;-2;4;7)=-2, (B2:B5)=12.
ü ( ) ( ).
ü (1;2;...) . , 0.
ü ( ;) .
ü ( ;) . :
(B2:B5;1)=23 ,
({5;2;4;8};2)=5 , 2- , ,
({5;2;4;8};1)=2 ,
({5;2;4;8};2)=4 , 2 , .
, , () () #!. , , / .
1 - | 7 - | A | |||
2 - | 8 - | ||||
3 - | 9 - | ||||
4 - | 10 - | .(9;1:2) | |||
5 - | 11 - | ||||
6 - | |||||
.(9;4:5) | |||||
.(9;1:6) | |||||
. | 4.9-2 | . 4.9-3 |
ü .( ; ) , . (. 4.9-2) Excel. :
|
|
.(9,C3:C5)
C3:C5, ( (C3:C5)).
- , , , .
1. .() , ( ). (. 4.9-3). 3,A6,A7 .(9;A1: A2), έ.(9;A4:A5), .έ(9;A1:A6). , A7 , .. ( 3 6 ). , - .
2. , (. 8.2). , ., , , . . 4-9.4 () , . 6 ר(A2:A4), A7 .(3;A2:A4), , , . , () ר , . ( 2 ).
ü (;;) . . 0 , Excel , , . , .
. (), . , 10 5, 11 7 6.
(. 4.9-5).
2=(B1;$B$1:$G$1), 3=(B1;$B$1:$G$1;1).
A | B | C | D | E | F | G | . 4.9-5 | |
: | ||||||||
(): | 2 | |||||||
(): | 2 |
ü (1;1; ) , . :
(A1:3;1:3) = A1*B1+A2*B2+A3*B3,
({2;3;1};{4;5;0}) = 2*4+3*5+1*0=31.
ü (;;) , , , . : (98;10;03)=03.10.98
ü () .
ü (; ) . :
1 - 1 () 7 (),
2 - 1 () 7 (),
3 - 0 () 6 ().
, , 2.
: (B8;2), ("8.5.2001";2) =2 ().
ü () . : ("10.01.99") =1999.
ü () . : ("10.01.99") =1.
üü (;1) , .
: ("13.8.99";1) =33- .
ü () . : ("10.01.99") =10.
, 1 ( 25.01.99 )
1-(A1)+1 =1.01.99.
ü () .
: ("24.12.97")=336518 24.12.97
.
üü ( ; [;])
, . , .
: ("01..98"; 80; "07..98")
, 80 1 1998. 7 .
, ("01..99";1) = 01..99".
üü ( ; [;]) .
: ("01.03.98";"12.06.98";"01.05.98")
1 1998. 12 1998. 1 .
, . , .
("01.09.99";"01.09.99") =1.
: . , , 1998. . (). (1 2 )
=("1.5.98";("1.5.98";1);{"1.5.98";"2.5.98"})
. , , .
. , . (, , ) , . .
üü ( ; ) , . , . . .
: ("18.09.97";5)=28.02.98, ("18.09.97";-5)=30.04.97.
üü ( ; ) , . , . . .
: ("18.09.97";-5) = 35538,
("18.09.97";-5) = 18.04.97, ("18.09.97";5) = 18.02.98.
,
("30.01.97";1) = 28.02.97.
30- , .
, 8 (();8).
ü () . . () =2.12.99 15:06.
. , ( 1, 4). , , . , , ( ).
=((B1;".")=(4;".");" ";"").
((; ".")). , 1=10.16.80, 4=10.16.99.
=(("10.16.1980";".")=("10.16.1999";".");" ";"")
=("10.16"="10.16;" ";"").
" ".
. , . ( 1) (.. 31 ) . , 1 ((1)-1). 0000 (, 1998) 31.12. (31.12.1998) .
=B1-("31.12."&((B1)-1;"0000")).
, 1=25.6.1999. ( )
=25.6.1999-("31.12."&((25.6.1999)-1;"0000")),
=25.6.1999-("31.12."&(1998;"0000")),
=25.6.1999-("31.12.1998"),
=25.6.1999-31.12.1998=176 .
Excel. . . , . , .. -.
. 100 000$ . 50 000$ 1 000$ 60 , 30 000$, 1 000$ 100 . , 100 000$, 110 000$ 130 000$. , (, 130 000$ ) , , (100 000$), .. . , , , - . , , , . . , , , , .
. . , . . ( ) , , . () N :
1 = (1+1) ,
2 = (1+1)(1+2) ,
2 = (1+1)(1+2)(1+3) ,
...
N = (1+1)(1+2) (1+N) N-.
, ,
N = (1+)N.
() () N , ..
= N/[(1+1)(1+2)(1+N)] ,
= N/(1+)N .
, , , . : , , , . , , . , , .
.
:
.
, ( ).
.
/ . .
ü (; ; ; [ ]; []) ( ) , .
: : / (0/1) . , 0. :
6 , 10000 . 10% .
(10%;6;;-10000) = 17 715,61 .
( ) ( 0,1). , .
, .
(10%/2;6*2;;-10000) = 17 958,56 .
, 800 .
(10%/2;6*2;800;-10000) = 5 224,86 .
, 500 .
(10%/12;3*12;-500) = 20 890,91 .
,
(10%/12;3*12;-500;;1) = 21 065,00 .
üü ( ; ) ( ) . :
, , 10000 ., 10%, 20% 25% . :
(10000;{0,1;0,2;0,25})=16 500.
, .. .
,
(B1;A4:A7).
ü (; ; [ ];[ ]; [])
() . ().
. 20000$ , 14% . .
(14%;5;;20000) = -10 387,373$.
, , .
, . , 10387,383$.
(14%;5;;-10387,373) = 20 000,00$.
().
. .
1. 10 3000$ .
2. 15 1500$ .
, 3000*2*10=60000$ 1500* 3*15= 67500$. , , , . , . , 12%
(12%/2;10*2;-3000) = 34 409,76$ (12%/3;15*3;-1500) = 31 080,06$.
, 34409$ 31080$ , .. , , (67500$ 60000$).
ü (; ; ;[];[])
( ) . , 0.
. , ( ) 100000 ., 3 , 6%
=(6%/12; 3*12; 100000) = -3 042,19 /,
=(6%/4; 3*4; 100000) = -9 168,00 /,
=(6%; 3; 100000) = -37 410,98 /.
. , . .
ü () , , , . : =(1).
ü () .
ü () .
ü () .
ü () .
ü () , .
, /.
ü () , : 1 , 2 , 3 , 8 , 16 , 64 . , .
: ("")=2, (56,12)=1, ({1;2:3;4})=64.
üü ר() , , .
üü ר() , , . , ר() ר() .
: ר(3)=, ר(4)=, ר(3,4)=,
ר(3)=, ר(4)=, ר(3,4)=.
Excel, . , .
#/0! .
#! . , .
#? . , , , .
#/ ( ).
#! , .
#! ( ) .
#! , , .
. (), .
ü () , , #/.
ü () (#/, #!, #!, #/0!, #!, #? #!).
ü () , #/ ( ).
ü .() , , : #! 1, #/0! 2, #! 3, #! 4, #! 6, #/ 7. #/.
. , , 4, . , , , , , . () , . , , 1:M1. ,
(((A4;A1:M1;1;0)); ""; "")
A | B | |
. 4.14-1 |
4 A1:M1, (() #/, (), ). , .
. , (. 4.14-1).
, , . , , , , , . : , , . (). , #!. , (), , . . , . 1
B1 =((("";A1)); ""; " ") &((("";A1)); ""; " ")
&((("";A1)); ""; " ")
(2, 3, 4) , . 4.14-1. ( ).
. , , , . . () ר() , . , , , , .
, , . , . 4.15-1.
, . ,
A | B | C | D | E | F | G | |
. . | |||||||
. 4.15-1 |
=ר(D2:D6;"")
=(D2:D6;"";E2:E6).
, , , , , .. , ר(), (), () , . ,
{=ר((D2:D6="";1))} {=((D2:D6="";1))}.
, D2:D5 , .
{=((D2:D6="";E2:E6))}.
, , , Shift+Ctrl+ Enter, .
, , () 1, , . , , (), (), (), ().
. , .
.
1. , 1000 .,
{=((E2:E6>1000; (D2:D6="";1)))}
{=ר((E2:E6>1000; (D2:D6="";1)))},
{=((E2:E6>1000; (D2:D6="";E2:E6)))}.
. , , .
{=(((E2:E6>1000)*(D2:D6="");1))}
{=ר(((E2:E6>1000)*(D2:D6="");1))}
{=(((E2:E6>1000)*(D2:D6="");E2:E6))}.
Excel , 2:6, >1000, 1. D2:D6=. , , 1*1=1. , () (ר) . , .
2. , ,
{=(((B2:B6=3)*(D2:D6="");1))}.
3. , 1000 2000 .,
{=(((E2:E6>1000)*(E2:E6<=2000);1))}
{=(((E2:E6>1000)*(E2:E6<=2000);E2:E6))}.
4. , ,
{=(((C2:C6="")*(D2:D6="");1))}
{=(((C2:C6="")*(D2:D6="");E2:E6))}.
5. , ,
{=((C2:C6="";1))+((C2:C6="";1))}.
, , . , , , .
{=(((C2:C6="")+(C2:C6="");1))}.
,
=ר(C2:C6;"")+ר(C2:C6;"").
6. ,
{=(((D2:D6="")*((C2:C6="")+(C2:C6=""));1))}.
{=((="" (="" ="");1))}.
7. , ( G2),
{=((E2:E6<5*G2;1)).
8. , ,
{=((E2:E6<(E2:E6);1))}.
9. ( 10% , 0 .)
{=((E2:E6>(E2:E6)*(1-10%);1))}.
10. , ,
{=(((C2:C6="")*(D2:D6="");E2:E6))}.
11. , ,
{=(((C2:C6="")*(D2:D6="");B2:B6))}.
.
12. , , . . . , .
. (. 4.15-2), AF ( 31 ).
; , ; . , ( ). .
AG2=ר(B2:AF2), AH2=(B2:AF2).
, , ,
AI2 {=ר((((B$1:AF$1;2)>5)*(B2:AF2>0);1))}.
A | B | C | D | E | F | G | H | I | AG | AH | AI | AJ | AK | AL | ||
01.. | 02.. | 03.. | 04.. | 05.. | 06.. | 07.. | 08.. | .... | ||||||||
36 | 22 | 12 | 6 | |||||||||||||
29 | 12 | 0 | 0 | |||||||||||||
30 | 0 | 6 | 0 | |||||||||||||
. 4.15-2 |
, 5 (.. ) - (>0) . ,
AJ2 {=(((B$1:AF$1;2)>5;
B2:AF2))}.
( ) , 8,
AK2 {=((B2:AF2>8;B2:AF2-8))}.
AL2 {=((((B$1:AF$1;2)>5)*(B2:AF2>8);B2:AF2-8))}.
, , , , . , .
. , , , .
ü (1;2) . 1 2. , 1, , 2.
ü () , . ( ) , .. . ( #!). (, , ).
ü () , .
ü () .
, ((), (), ()), :
, ;
;
Shift+Ctrl+Enter ( ).
. .4.16-1 2:3.
A | B | C | D | E | F | G | H | |
-2 | ||||||||
-1 | ||||||||
|
|
|
|
: 2016-10-07; !; : 1130 |
:
, .
==> ...
: 0.338 .