Excel Notes

download Excel Notes

of 39

  • date post

    29-Nov-2014
  • Category

    Documents

  • view

    859
  • download

    5

Embed Size (px)

Transcript of Excel Notes

& . /

: & . / ( .)

(.. Excel)

. . . , . . . 2009-2010

. . . , . .

1/39

& . /

........................................................................................................................ 2 1. ....................................................................................................................................... 4 1.1 ....................................................................................................... 4 1.1.1 ..................................................................................................... 4 1.1.2 ........................................................................................................... 4 1.1.3 ................................................................................................ 4 1.2 ................................................................................................ 6 1.2.1 .................................................................................................................. 6 1.2.2 ............................................................................... 7 1.3 ...................................................................................... 8 1.3.1 , ..................................................................... 8 1.3.2 ............................................................................................................. 9 1.4 ..................................................................................................... 10 2. ............................................. 11 2.1 ....................................................................................................... 11 2.1.1 ......................................................................... 11 2.1.2 ... 13 2.1.3 .................................................................................. 14 2.2 ................................................................................................................. 14 2.2.1 () IF ( ; ; ) ......................................................... 15 2.2.2 IF............................................................................................. 16 2.3 ..................................................................................................... 17 3. ........................................................ 20 3.1 ............................................................................................................... 20 3.1.1 ....................................................................... 20 3.1.2 .................................................. 21 3.2 ................................................. 23 3.2.1 LINE, BAR, COLUMN, PIE ........................................... 23 3.2.2 / () SCATTER ................................. 24 3.2.3 3-D ................................................................................ 25 4. & .................................... 27 4.1 , , (GOAL SEEK)............................................ 27 4.1.1 Goal Seek ........................................................................................ 27 4.1.2 ......................................................... 27 4.1.3 ...................................................... 31 4.2 , , (SOLVER) ........ 32 4.2.1 Solver............................................................................................... 32 4.2.2 / , Min & Max...... 32 4.3 & ............................................ 34 4.3.1 () ............. 34 5. ()................................................................ 36 5.1 & ............... 36. . . , . . 2/39

& . /

5.1.1 CTRL+SHIFT+ENTER..................................... 36 5.1.2 & ....................................................................... 37 5.2 ..... 38 5.2.1 (MDETERM).......................................................... 38 5.2.3 (MINVERSE) .................................................... 39 5.2.2 & (MMULT) ................................ 39 5.2.4 (TRANSPOSE) .................................................. 39

. . . , . .

3/39

& . /

1. 1.1

1.1.1

MS-Windows & Office. PC/Laptop : / (- , , , /, ./., , Del/BackSp, , // ). Windows Explorer (, , , , & , // , /, (path), / flash). / MS (, , , , , , / , (Undo), , OK/Cancel). / .1.1.2

/ . Office Microsoft Excel /. OpenOffice (openoffice.org) . (Complete) (.., Solver) .1.1.3

(.. Excel: > > Microsoft Office > Microsoft Excel), .. . . , . . 4/39

& . /

Excel 2003

. . . , . .

5/39

& . /

1.2

1.2.1

() (). , , , . ( ), , , . , , , Word. (.., , label, ). (%). Excel : . . : . ! / Windows (,). / Windows (.). .: .

. . . , . .

6/39

& . /

1.2.2

: (. )(. ), ..: 2 : [.xls]1!$$2 : ! Name Box, Formula Bar, .

name box

formula bar

. , Excel .

Excel .

. . . , . .

7/39

& . /

1.3

1.3.1 ,

(=), , , .

Excel () (, , , .) . . (. , Modulo, ) . ( ) Excel: 1. (^) 2. (*) & (/) 3. (+) & (-)

2++=0. . . , . . 8/39

& . /

1.3.2

( ) . ( ; , Windows /). insert function: fx ( formula bar, ).

: : SUM, PI, ABS, SQRT, EXP, LOG, RAND, INT, ROUND, MOD : COS, SIN, TAN, ACOS, ASIN, ATAN : COUNT, AVERAGE, MIN, MAX, VAR : LEN, FIND, REPLACE, CONCATENATE, LOWER, MID, UPPER, TEXT, VALUE : IF, OR, AND, NOT, TRUE, FALSE

. . . , . .

9/39

& . /

1.4

. . . , . .

10/39

& . /

2. 2.1

Excel . , ( ) . 10 100, . , .2.1.1

.., 1 9 2, , . . , (1 & 3), , (+), , (9) . .

, , , , .

. . . , . .

11/39

& . /

, () :

, Excel :

. . . , . .

12/39

& . /

2.1.2

Excel . , , , .

Copy & Paste Copy & Paste

. ($) .

1 $ 1.

. . . , . .

13/39

& . /

2.1.3

, ( ). , = +, = 5 = -10 -4 4 0,5 :

2.2

. IF, IF.

. . . , . .

14/39

& . /

2.2.1 () IF ( ; ; )

Excel IF IF(;;), (3) : , , , . (;). a & b (m) . . IF : : a>b m=a m=b .: IF (a>b) THEN m=a ELSE m=b . ( m): = IF ( a>b ; a ; b) a & b , . C2 = IF ( A2>B2 ; A2 ; B2) IF ( !) ( insert function: fx ) 3 . , .

. . . , . .

15/39

& . /

.

2.2.2 IF

IF IF ( nested). , IF a & b , : .(Nested) IF ELSE

. . . , . .

16/39

& . /

2.3

1. ! ! = 1*2**, , ! = (-1)! * , , 0!=1. .. 16! , =1 =2, 16. :

2. Fibonacci Fibonacci . : FN = FN-1 + FN-2 , F1 = 0 F2 = 1. .. 11 , (F1 F2) ( 3 & 4), , (=3+4). 11 . F4, F5, () .. . . , . . 17/39

& . /

3. : ) , ) (, , ), , ) (, , ). .: , () ( & ).

. . . , . .

18/39

& . /

4. :

t=

a+b+c , 2

E = t (t a ) (t b) (t c )

( ).

. . . , . .

19/39

& . /

3. 3.1

3.1.1

/ : , , , . 2.1 . (2.3) . . . . , Y = AX + B & , , , () . -.

. . . , . .

20/39

& . /

3.1.2

: (TEXT) (.txt) ASCII (.asc) (Comma Separated Values) (.csv) .

Editor (Notepad, Word, .), , ( Save As) .

/ Notepad, ) CSV, ) TEXT Excel (Open) format (*.txt, *.csv, ).

CSV .. . . , . . 21/39

& . /

, 3 Text Import Wizard. , , .

(ANSI/ASCII), (preview) . NEXT.

, (preview) .

format General. Advanced.

, Finish.

, , ..:. . . , . . 22/39

& . /

3.2

Excel . . . , Chart Wizard, . .3.2.1 LINE, BAR, COLUMN, PIE