Excel сборник примеров и задач

  • View
    190

  • Download
    4

Embed Size (px)

Transcript

.

5

' ' .. , ..

. 256 ! . : , , , , . Visual Basic Excel 7.0. . . , ..8.,..11(

"

: 101000, , . , 7 ( "-", . ) .: (095) 925-35-02, 923-18-68 (095) 925-09-57 E-mail: mail@finstat.ru http://www.finstat.ru

.

..

Excel " " 2003

004.67(076.1) 32.973.26-018.2a7 13

1994

. , : . , ; . .

. .13 Excel: . - .: , 2003. - 336 .: . - ( ). ISBN 5-279-02130-. , Excel ( Excel 97, Excel 2000, Excel 5.0/7.0). . , ( ), . : . . . 2404000000-045 , 010(01)-2003 324 ISBN 5-279-02130- 004.67(076.1) 32.973.26-018.27 . . , 2000

... . . 1

... , . . .

, Microsoft Excel, . " Excel?" . ... , , , XXI . , , . ; . VisiCalc, .' . . : 1948. .243. .. .., , . .: " ", 1998. .86.2

, . , . , , Excel. : . : , . " " . ( , ) - , . . . , : , . . Excel . . . . , , ( ). ... , , . , , , ,

. : " - ; - ". - , , . "Excel-" . Microsoft Office Extensions, . , Microsoft Excel ... . . /4.

Excel. , , . Excel. > . . . , . ( ) . . . . . , , . . , , , . , , . [21, 34]. , Excel, , , .6

Excel . Excel 97 Excel 2000, Excel 7.0 Excel 5.O. , , , . , "Advanced Filter" " ", " ", . , Excel 7.0 Excel 5.0 , , Excel 97 Excel 2000. , Excel 97 Excel 2000 Excel 5.0/7.0. . VBA (Visual Basic for Applications). VBA Excel, . MS Query. , , , , SQL QBE. . Excel , ( ) , [16, 18, 22]. ( ) . . Windows. , , 2. (. 8), (. 11), (. 12).

: Excel. , , , . . -, , -, Excel , , Excel 5.0/7.0, Excel 97 Excel 2000, -, , , . . Excel. . , . , , . . . dBase 3. . , Excel. . , , : , , . , . [9] 10 . .8

[35]. 6 40 , 7 34 . . , . . . , , . 7 . 9 . 10 . 8, 11-14 . Excel. , , , [16, 18, 22]. , . Excel , html- . MS Query . [29, 32] . [28] Excel . Excel VBA. , . , Excel , , . VBA [3, 4, 10, 27, 37].

. , , : http://www.kiaml.rssi.ru/fis/ , : test.txt ( .7) kadr.dbf ( .9 10). , , . ( , , , .) , Excel. . , , , , , .

Excel

1.1. Excel. Excel Office. ("", "", "") . , . " " ( ), . Office : Excel ( ). [16, .987-990]. . , ( ), . , : Work ( , ). . ( , .) Excel ( . 1.3). "/ / " " " "C:\Work" ( : ). Excel . , .11

. Excel . , Ctrl+S . Ctrl , , S. S. , Shift ( CapsLock). Ctrl+S Shift ! Ctrl+S Ctrl+s ( ), . , Ctrl+Shift+Enter ( . 8). Ctrl Shift , , Enter. , . : End, Home. . Excel, . . . , , , . , .. , , . , . , , , . , . . - . , Excel. , . , "", , "", "" " " "C:\Work". "". : "/12

/ / ", "C:\Work". ( , . "" .) .

1.2. Windows , : , , /. , Excel Excel : , . , , , : =(1.2,2.8,-3.1) . (, , , =1.2 + 2.8-3.1.) Windows 95 : =(1,2;2,8;3,1) , . -, Excel , . (, ) . , , ? : "" VBA. VBA , Excel VBA, . Excel , Windows 95 , 13

.. . Windows 95 "", "", " ". " " ( ). "/ " "". " " ( ). " " ( ). "". " " ( ) "//". "" "". Windows 95 "Start/ Settings/ Control Panel/ Regional Settings". "Number" "Decimal symbol" , "List separator" . "Date" "Short date style" "dd/MM/yy", "Date separator" "/". , : Ctrl+Shift. " " ( "", "").

1.3. Excel Excel. Excel, "", "", "Microsoft Excel". Microsoft Office , Excel. . Excel. . "Microsoft Excel - !", .. (Microsoft Excel) , (!). . "" "". 14

, ( , , ). . . , . ( ). Excel 2000. Excel 2000. , "", . . . , , . , , Excel. . Excel. : "/ / " " ". . , Excel , . . . "" "" . . . ("") ( ). ( "/ / "), , , , . Ch01.xls. ( "" "chapter".) Ctrl+S "/ ". Excel 15

, "!" . " :" ChOl. xls , . "" . "!" "Ch01.xls". . (). (Cells) (Rows) (Columns). . : 1,2,.... : , ,...,Z.AA.AB,... ( .) 1.1. . , Ctrl + * ( Ctrl , , " "). 16 ( Excel 97 2000 65536 = 2 , Excel 5.0/7.0 14 16384 = 2 ). , Ctrl >. IV, 256. , Ctrl+Home. ( : End, ; End, > ; End, Home. . , .) ( ). 1. D3 D . D3 ( D3). , D3 , . . 45 ( PgDn PgUp). 1. 45 "/ ". ( ":") 45 ( 45). "/ " Ctrl+G. , "" , Ctrl+G. Excel.

16

: Tab , Shift+Tab , Enter , Shift-bEnter . . : "!", "2"... ( , ). , . , Ctrl+PgDn ( ), Ctrl+PgUp ( ). "!". . Excel 5.0/7.0 16 , Excel 97 2000 . : "/ / / ". ( ). ( Excel 5.0/7.0, "/ / / " 3 Excel Alt+F4. Excel. .) 1.2. . , ~ Windows, : , . . : ( ) - ; , ; - ; , ; , .2-3709 17

( , ). 5. . . . . B,C,D,E. 4,5,6,7. . 1.3. . . , . , 2:4 , 2, 4. : , , . : , Shift , , . : , Ctrl , , , Ctrl. 2:12 . C3:F44 . 2:14, D2:F14. 1.4. . 1 :200. 1- : 1) 1; 2) , 200; 3) Shift; 4) 200. 2- 1) 2) 3) : 1; : "/ " Ctrl+G; "" 200; 4) Shift; 5) "OK".

18

B12:BN430. 1.5. , . , ( , , ). ( ) , Enter ( ). 1 "", Enter. ( , , .) , : ( Esc), ( Excel 5.0/7.0 " "^). , . Enter , . ( : " " "/ / ", .) Enter. Enter, . Tab, . Shift+Enter , a Shift+Tab . () F2 (). , . F2 . 1: "" "". 1, F2, ( ), Del "". 2 A3 24 47. , "" , 24 47 . 4 =2+. . : 71, ( 4) - . ( 71

->*

19

#?, , , ). =2*2+ ( 2 2 A3). 4, F2, , Enter. , Excel 97 2 A3 , ( ). 5 =4-, . =. 4 ( ). . A3. Enter. , . , 5 , 4. , , ( Excel 97). 2 -14. , 4 1 5 . 2 -14 (. 3 47 1.1). 4 19 1.6. 5 -28 . , 6 . 1.1 =3*2 - 4. =2*2 ( 3 2). "". , , " ", . =2*2 - 6, 6 . "". F2. : "". l Ctrl+ . 1 (Ctrl+Home). Ctrl+ , ! . Ctrl+ 1. Ctrl+ > Ctrl+ . . Shift 22

. 1:5 1:5. 1. Ctrl+Shift+4'. 1 :5. . 1:5 (Ctrl+*). Del. ( , ). 1.9. . . "!" "2". "!", . . "2". "2" "!". . , , " ". "". , "/". " ". " Ch01.xls :". "( )". " " ( ). "(2). . "", Ctrl. "". "(2)". "()". . "/ ". . "!". Excel 97 "4". . . "!", . "". : "". ( , , " ", , 23

.) : . . "()": "". . "2". . . "". , . 1.10, . "". Ctrl+End. IV65536, , F15. , F , , 15 , , F15 . , 1 ( Ctrl+Home). Ctrl+End F15! , " ". ( Ctrl+S), ( Ctrl+F4) ( Ctrl+O), F15 . 1,11. . "(2)". F1 :F5 ( ) ( F4 F5). , . F1:F5 F8:F12. F1:F5, , , , , . , , " ". , F8:F12 . F1:F5. , , " ".24

( ) , " ". D1:D5.c "(2)" "": D1:D5, ( Ctrl+Insert Ctrl+C), "", 1 , " ", "", "" Esc. 1.12. . "" . 3, 4, 5 " ". . 1.13. . "2". B1:D9 5. B1:D9. 5. Ctrl+Enter. . B3:F3 10. 1.14. . . "4". , 2, 1 14. 2 1. 2. "/ / ". : : , 1 ( ), 14. "". , 2:10,