συναρτήσεις στο excel

24

2

Excel

.

:

Excel

, .

-

&

Excel ,

.

.

Excel

25

Financial () Logical () Text () Date & Time ( ) Lookup & Reference ( ) Math & Trig ( ) Statistical () Engineering () Database ( ) Cube () Information () User defined ( )

.

2.1 :

=, .

Insert Function ( ):

Insert Function

.

26

(=),

, ,

.

, , , TRUE FALSE, , ,

#/ .

. ,

.

,

, .

Insert Function ( ) ,

, ,

,

( 2 ). ,

.

64 .

&

COMBIN. COMBIN

27

. Function Arguments (

) .

(Number, Number_chosen),

(The number of combinations for a given number of items),

(Number_chosen is the number of items in each combination)

(6) (Formula result = 6)

COMBIN .

..

. Help on this function

( ).

2

.

, com

com,

28

.

.

, .. Number, Number_chosen

( 2 ) ,

,

.

, ;

.

.

2.2 & .

.

1 :

1

ABS(number) .

COMBIN(number, number_chosen)

.

COMBIN,

number_chosen

number.

EXP(number) e

.

29

(

)

.

FACT(number) .

1*2*3*...*.

GCD(number1, number2,)

number1, number2, .

(number) ,

LCM(number1, number2,)

number1, number2,

LN(number), LOG10(number) .

,

number .

LOG10(number)

.

RAND()

0 1,

.

,

.

RANDBETWEEN(bottom,top)

.

ROUND(number, number_digits) number

number_digits.

SQRT(number)

. ,

30

number.

SUM(number1, number2,)

/

.

SUMIF(number1, number2,)

/ .

SUMPRODUCT(array1, array2,)

.

3 Excel.

31

3

1 / 2

. A, B, C, D

wA, wB, wC, wD

rA, rB, rC, rD , :

p A A B B C C D Dr w r w r w r w r= + + +

SUMPRODUCT.

32

2.2.1 , .

Excel.

:

CTRL+SHIFT+ENTER

.

.

MDETERM

.

.

{ }

CTRL+SHIFT+ENTER.

:

1: ( )

2: / .. TRANSPOSE

33

,

3: /

4:

CTRL+SHIFT+ENTER

2

MDETERM(array)

TRANSPOSE(array)

MMULT(array1, array2) .

array1

array2.

MINVERSE(array)

.

34

4

2 / 2

.

Excel .

:

1234273316643

321

32

321

=++=+=++

xxxxxxxx

:

35

=

=

171

23421330

6643

3

2

1

xxx

BXA

(detA 0) :

BAXBXA == 1 =MMULT(MINVERSE(A37:C39),E37:E39) .

2.3

.

:

Data

Analysis ( ).

:

,

,

More

Functions ( ).

36

2.3.1

/, , ,

.

3

(number1,number2,)

( )

(number1,number2,)

(

)

AVERAGE(number1,number2,)

MEDIAN(number1,number2,)

MODE(number1,number2,)

PERCENTILE(array,k) k ,

.

QUART(array,quart)

array. quart

0,1,2,3, 4

, 25%, 50%, 75%

.

STDEV(number1,number2,)

(

).

SKEW(number1,number2,) .

37

KURT(number1,number2,) .

FREQUENCY(data_array, bins_array)

. ,

.

FREQUENCY :

1:

.

bins_array.

.

2: FREQUENCY

3:

(data_array)

(bins_array)

4:

CTRL+SHIFT+ENTER

38

5

3 / 2

1994 2007

( ) .

1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007

$/15.53 16.86 20.29 18.68 12.28 17.48 27.6 23.12 24.36 28.1 36.05 50.64 61.08 63.34

1994 2007.

39

2.3.2

.

Excel

,

,

, .. , ,

, 2 , t Student, F .. 4

.

4

BINOMDIST(number_s,trials,proba

bility_s,cumulative)

,

CRITBINOM

(trials,probability_s,alpha)

,

.

40

HYPGEOMDIST(sample_s,

number_sample,population_s,numbe

r_pop)

POISSON(x, mean, cumulative) Poisson

NORMDIST(x, mean, standard_dev,

cumulative), NORMSDIST(x)

NORMSINV(probability)

,

EXPONDIST(x, lambda,

cumulative)

CHIDIST(x,deg_freedom) 2

TDIST(x,deg_freedom, tails) t Student

FDIST(x, x,deg_freedom1,

x,deg_freedom2)

F

41

6

2.3.3

. Excel

.

5

TEST(array, x, sigma) p z.

z

x,

(),

.

TTEST(array1, array2, tails, type)

42

t Student. T array1,

array2 .

tails

(1 2

). ype

t : 1,

, 2, (

) , 3,

.

FTEST(array1, array2) F,

,

.

CHTEST(actual_range,

expected_range)

.

2

.

43

7

2.3.4 Excel

.

:

. Excel

.

6

INTERCEPT(known_y's,

known_x's)

y,

known_y's known_x's.

SLOPE(known_y's, known_x's)

44

known_y's

known_x's.

RSQ(known_y's, known_x's)

.

PEARSON(array1, array2) Pearson

FORECAST(x, known_y's,

known_x's)

y

x ,

.

STEYX(known_y's, known_x's)

y x

.

8

LINEST(known_y's, known_x's, const,

45

stats) .

:

Excel

LINEST.

61:J65 H61:H65 K61:K65

LINEST.

, Alpha ( ) Beta ( ),

[Beta (SE), Alpha (SE)], R2 (RSQ)

y (STEYX), F,

(N-K), (Regression SS)

(Residual SS).

46

LINEST(known_y's, known_x's, const, stats)

,

5x2

CTRL+SHIFT+ENTER

LINEST(known_y's, known_x's, const, stats)

LOGEST(known_y's, known_x's, const, stats)

. ,

.

2.4

.

7

VLOOKUP(lookup_value,

table_array, col_index_num,

range_lookup)

.

VLOOKUP,

47

.

LOOKUP(lookup_value,

table_array, col_index_num,

range_lookup)

.

HLOOKUP,

,

.

MATCH(lookup_value,

lookup_array, match_type)

,

.

LOOKUP

.

INDEX(array, row_num,

column_num)

,

.

48

9

4 / 2

(.. 8,000 15%

).

=VLOOKUP(B7,A2:B5,2)

A2:B5 7 (=15,000 )

2. A2:B5

. range_lookup

TRUE

FALSE .

=MATCH(B10,B2:B5).

=INDEX(A2:B5,B11,B12).

49

10

2.5

.

8

AND(logical1, logical2,...) TRUE ()

TRUE ()

IF(logical_test, value_if_true,

value_if_false)

,

TRUE (), ,

FALSE ().

IFERROR(value,value_if_error)

.

OR(logical1, logical2,...) TRUE

50

FALSE FALSE

FALSE.

11

5 / 2

3 .

: ( ),

,

.

,

.

IF : =IF($B11

51

2.6

Excel.

( ), (

), , ( )

.

2.6.1 ,

. To Excel

.

52

9

PV(rate, nper, pmt, fv, type)

. Rate

. Nper

. Pmt

. Fv

.

Type 0 1

. 0

, 1

.

FV(rate, nper, pmt, fv, type) FV(rate, nper, pmt, pv, type)

,

. rate, nper

.type PV. Pv

.

pmt,

pv .

PV FV,

,

,

.

53

4%

4 ,

4%/12 rate

4*12 nper.

PMT(rate, nper, pv, fv, type)

-

.

PV FV.

IPMT(rate, per, nper, pv, fv, type) .

, per,

(

1 nper).

PPMT(rate, per, nper, pv, fv, type) nper.

12

54

6 / 2

1,000

3% ,

5 .

:

FV(3%,5,-1000) 5,309.14

7 / 2

1,000

. 1,2,3

4.

5 .

:

12

345

%)31(000,1%)31(000,1 %)31(000,1%)31(000,1%)31(000,1

+++++++++=

FV(rate, nper, pmt, pv,

type). .

0 30 type

FV D38 1

.

pv FV C18. type

.

8 / 2

10,000

7% .

6 .

PMT ( ),

IPMT (), PPMT (). rate

7%

7%/4. pv

.

55

.

0, .

:

2,097.96

.

.

9 / 2

:

$10,000 10 8%

$50,000 18 .

6%.

:

PMT(8%/12, 10, 10000) -$1,037.03

PMT(8%/12, 10, 10000, 0, 1) -$1,030.16

56

PMT(6%/12, 18*12, 0, 50000) -$129.08. ,

$129.08 6% 18

$50,000.

10 / 2

:

2000 2 10%

10 200000 10 8%:

:

PPMT(10%/12, 1, 24, 2000) -$75.62

PPMT(8%, 10, 10, 200000) -$27,598.05

2.6.2 Excel

.

.

.

SLN(cost, salvage, life)

(straight line depreciation).

(cost),

(salvage)

( ).

SYD(cost, salvage, life, per)

,

. per

life.

DDB(cost, salvage, life, period, factor)

-

.

57

factor .

factor, 2.

10

SLN(cost, salvage, life)

(straight line depreciation).

(cost),

(salvage)

(

).

SYD(cost, salvage, life, per)

,

. per

life.

DDB(cost, salvage, life, period,

factor)

-

.

factor

.

factor,

2.

58

13

11 / 2

100,000 10 .

59

=SLN($C$2,$C$3,$B$19) C10:C19,

=SYD($C$2,$C$3,$C$4,B10) D10 D11:D19

=DDB($C$2,$C$3,$C$4,B10) E10

E11:E19.

. C22 (=SUM(C10:C19)), D22 (=SUM(D10:D19).

. 20 =C2-SUM(E10:E19).

2.6.3 :

(Net Present Value, NPV)

60

(Internal Rate of Return, IRR).

.

11

NPV(rate, value1, value2, )

( )

( ).

value1

.

0.

NPV

.

XNPV(rate, values, dates)

IRR(values, guess)

. Values

( ),

.

. Guess

IRR.

61

MIRR(values,

finance_rate,reinvest_rate)

.

MIRR

.

IRR(values, dates, guess)

.

NPV PV .

PV

. PV

NPV.

Excel

guess, IRR ,

0,00001. IRR

20 ,

#!. ,

guess IRR ( 10%).

62

14

12 / 2

5

100 , .

.. , 4%

5 .

4%.

100 PV NPV.

. .

Excel

, NPV(rate, value1, value2, ...)

( ) ( ).

0.

.

63

445.18 .

460 ,

.

:

, .

:

64

13 / 2

.

$70,000

: $12,000, $15,000, $18,000, $21,000, $26,000 .

2, 4 5 ..

1 6 .

:

IRR(B1:B3,-10%) -44.35%

:

IRR(B1:B5) -2.12%

, :

IRR(B1:B6) 8.66%

14 / 2

145, 100

- 275 .

0% 40% 2.5%.

.

( )

r.

65

.

IRR(values, guess), guess,

.

66

15 / 2

5 .

$120,000 10%

. $39,000, $30,000, $21,000, $37,000,

$46,000 .

12% .

3 5 .

14%

.

:

MIRR(B1:B6, 10%, 12%) 12.61 %

:

MIRR(B1:B4, 10%, 12%) -4.80 %

14% :

MIRR(B1:B6, 10%, 14%) 13.48 %

2.7

. Excel

Formulas

Formula Auditing ( ).

.

Trace Precendents ( ),

D12.

67

Trace Dependents ( ),

D5.

Remove

Arrows ( ) .

Show Formulas ( )

Error Checking ( )

. Evaluate Formula ( )

. Watch Window (

)

68

.

.

:

,

, .

69

1 / 2

50 .

.

21,111 9.43 20,191 7.68

19,970 8.50 20,892 5.85

19,450 6.85 20,375 9.73

22,193 9.70 18,646 7.83

19,698 6.75 21,924 8.50

21,434 8.40 23,644 8.90

20,706 6.43 21,572 6.85

23,325 9.53 19,188 5.10

20,936 6.75 22,540 9.03

20,818 8.23 20,719 6.55

18,673 7.43 19,324 5.80

21,043 5.40 22,626 5.88

20,279 7.10 23,164 6.75

20,410 6.33 24,302 9.73

20,543 6.98 23,419 6.30

21,189 9.53 24,898 8.38

19,629 5.45 24,394 7.78

20,847 5.63 25,061 9.30

20,930 8.60 23,941 5.65

20,655 7.60 26,332 5.58

23,456 9.45 23,650 6.48

19,484 5.08 23,471 5.48

18,951 5.38 23,701 6.83

23,241 9.73 24,891 7.40

20,424 6.65 24,560 6.65

70

, , ,

,

.

1,000 ,

19,000 26,000 0.5

, 6 9.5.

.

2 / 2

.

2010-2024:

2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024

. . 8 8.6 9 9 9 7.2 7 5 5 5 3 2 2 2 2

:

2007 2008 2009 2010

. $ -20 -60 -120 -20

:

- 10%

- $2.5/

5% .

- .

- :

o . o $50,000,000 35%. o $50,000,000 50%.

71

1 .

25%.

72

1 / 2

.

2:51.

.

3:F13

. , , ,

, .

,

. 3:13

F3:F13.

73

.

FREQUENCY

. FREQUENCY

, CTRL+SHIFT+ENTER.

:

= + +

.

SLOPE,

=SLOPE(A2:A51,B2:B51) 37.

315

.

2 / 2

. 2:9

.

.

D, E, F . G

. G5 =(1-

$B$2)*E5. B2

G6:G19

2. 5

3 6 =(1+$B$4)*H5.

7:19.

. $ 5 =H5*G5

6:19. J5 . $

=G5*$B$8-I5 J6:J19

.

6 . IF :

=IF(J5

74

,

. ,

.., .

: ..

.. .. ..

2007 L2 =F2+J2-K2,

20025 2004

L3:L20.

22

=NPV(B9,L2:L20) $ 430 .

.

15%, 9 15%

$ 789 .

συναρτήσεις στο excel

Documents

Transcript of συναρτήσεις στο excel