«  »

Прикладная программа Excel



-



:

" EXCEL"

: :

..
..


..

1996





1.

1.1.

1.2.

1.3.

1.4.

1.5.

1.6.

1.7.

2.

2.1.

2.2.

2.3.

2.4.

2.5.

3.

3.1.

3.2.

3.3.

3.4.

3.5.

3.6.

3.7.

4.

4.1.

4.2.

4.3.

4.4.

4.5.

5.

5.1.

5.2.

5.3.

5.4.

5.5.

6.

6.1.

6.2.

6.3.

6.4.

6.5.

6.6.

6.7.

7.

7.1. Excel

7.2.

7.3.

8.

8.1.

8.2.

8.3.

8.4.

8.5.

8.6.

8.7.

9.

9.1.

9.2.

9.3.

10.

10.1.

10.2. Query

10.3.

10.4.

10.5.

10.6.

11. EXCEL

11.1.

11.2.

11.3.

11.4.

11.5.

11.6.

11.7.

11.8.

12. VISUAL BASIC

12.1.

12.2.

12.3.





MS Excel,
, .
4.0 Excel

. 5.0,
.

Excel
().

.
,
.

(),
,
.
Excel ,
.


.
,
, Excel
Visual Basic (VBA) .
VBA Microsoft
Excel 4.0,
, VBA
,
. ,
Excel 5.0:

- ,
;

- -;

- ;

- Drag & Plot,
.

1.

.
Excel .
(Sheets) ,
,
. Excel
, .

,
(, ).


.

1.1. .

File New.

Book2: Excel
Book ()
.

, ,
() (
).

1.2. .


, Open File.

,
.
. Drives ,
Directories , .
,
, .
, File Name.
, OK
.

1.3. .

File
Save As... ,
,
, . Excel
(Book[
]), .
(, $ & %
( ) -), .
,
Windows. Excel
.XLS, .
,
OK.

1.4. .

Add-Ins... Tools,
.
AutoSave, ,
, OK.

Tools,
AutoSave... ,
AutoSave,
. ,
,

.

1.5. .

Save As...
File.
Options. ,
Save Options.
Always Create Backup.

, , .BAK.

1.6. .

e Save As File.

Options. Save Options.
Protection Password:
,
.
. Save Options
Confirm Password, .
Write Reservation Password,
,
Read Only.
, .

1.7. .

Save As
Summary Info.
Title:, Subject: Keywords:. Author
,
, .
Comments .
,
.
OK.

2.

(, ) -
Excel 5.0,

. 255
, VBA- ,
.
,
, ,
.


.
, -
,
.

, .. .

.
, , Excel
5.0 .

2.1. .

.
( ),
, .
,
: Sheet1, Sheet2 ..

, ,
Sheet2.
.
.
,
, .


() .
.

.

2.2. .

Insert.
:

- ;

- ( );

-
( Excel 4.0 Visual Basic);

- .

.
,
Sheet17
. ,
, Chart1 ..
( Excel 4.0)
Macro1, -
Dialog1 ..
Visual Basic, Module1.
,
( ) ,
. ,

.

2.3. .

,
,
. Move or Copy
.
Move or Copy OK,
. Create a
Copy, ,
.

, , Sheet1(2).

.
,
[Shift]. Move or Copy,

.

2.4. .

,
, .
, Rename
Rename Sheet.
.
Name , 31
, . OK,
.
:
"[ ]"; ":"; "/";
"\".

2.5. .


.
() . Format
Row Column.
. Column Width,
,
.
. , 10 ,
Column Width 10.
AutoFit Selection Column
.

.

.
. ,
, ,
, .
,
.
( ), ()
()
.

3.

,
.
Excel
, . ,
.

Excel ,
, .
.

( ),
.

3.1. .

()
.
,
.
( ) ,
,
( ). ,
.

, , [Shift] ,

.


(), .

(), , ,
.

3.2. .

, ,
Undo Edit [Ctrl-Z].
, 11-
.


Redo. ,
..
[Ctrl-Z] 11-
.

-
Repeat Edit.
[F4] 12-
.

3.3. .

,
, Copy
Edit. .
,
, [Enter].
. ,
, ,
,
.


.
[Ctrl-C] 8- .


, Cut
Edit. 7-
(), -
[Ctrl-X].

3.4. .

,
.
. Edit
Clear, :

All - ;

Formats - ;

Contents - ;

Notes - .

Clear Contents .
.
[Del].
,
[Ctrl-Z], .

3.5. .

,
.
Format Cells,
Cell Format.
Format Cells.
Number,
. Category
,
. Format Codes:
. Code:

. Format Cells
Sample:, ,
.

$ ()
(
). %
() (
).



3.6. .

Excel
,
(4-, 5-, 6-).
.
Format Cells,
Cell Format.
Format Cells.
Alignment. Horizontal
: (Left),
(Center) (Right).
, .


.
.

3.7. .

,
.
Format Cells,
Cell Format. Format Cells
Font. Font:,
.
, Windows.

Size () .
Excel 10 .
Font Style: (Regular),
(Italic), (Bold) (Bold Italic).
Underline (
). Effects
, (Strikethrough)
(Superscript)
(Subscript) .

(1-, 2-, 3-)
: ( B), (
I), ( U ).

4.


. , ,
.

, ,
(=), Excel
.
, , .


.
: + (); - (); * (); /
().



,
.
.

.

, Excel
, .
, Excel
, .

4.1. .

Excel
( ),
( ).
,
(=).

, 1 100, 1
- 20.
1, 1
(=1/1) [Enter].

,
.
,
.

4.2. .

.

B C D E

10

11

2 $ 17.80

$ 35.60

12

4 $ 3.80 $ 15.20

13 8 $ 1.56 $ 12.48

14





15





16

$ 63.28

17 15%

$ 9.49



.1.

( ),
, D - ,
- ,
. 16 .
: =12+13+14.
0,15,
, 17: =16*0,15.

, (,
19) ,
0,15. :
=(12+13+14)*0,15. ,
16 17.
SUM(),
: =Sum(E12:E14)*0,15.

4.3. .

,
.
, [F2]
. (
) ,
, .

4.4. .

Excel ,
. ,
, .

, ,
.
, 3 Sheet2
: =Sheet2!B3. ,
() .
.
,
( [Ctrl-C])
, .
Paste Special Edit
Paste Link.

4.5. .

, .
, ,
.
.

Insert
Name Define.
Define Name .
255
. .
(1 ..).
,
.

,
.
(), ,
, Create Insert
Name.
( )
OK.

Define Name,
, Excel
() .

Refers to Define Name
.
, ,
.


. ,
, .
,
.

, (. . 1) ""
11, 12 13,
,
: =Sum(.


"". Excel .
[Enter],
.

5.

,
Excel
. Excel
,
" ".

,
. MS Excel 5.0
- ,

.


.


Excel.
. ,
, .
.

5.1. .

,
, .
(ChartWizard), 17- .


,
.
,
. , ,
,

. ,
Insert Chart As
New Sheet.

,
. ,
, .
,
.

, Next

.
. ()
, (..
),
.
Finish,
.

5.2. .

.
5.1.
,
.

Format Chart Type.

.

.

Chart.
.
. ,
, ,
.

5.3. .


, .
Titles Insert.
, .
, Chart Title,
Value (Y) Axis Category (X) Axis.
.

Value (Y) Axis
, Y.
. ,
. ,
.
, .

5.4. .

Insert ,
. ,
Picture,
(BMP, PCX ..).
.
, .
.

,
,
.
[Enter].
,
,
.


Drawing,
.
Toolbars View.

5.5. .

,
, Excel
.
, .
.

, , .
,
(). Microsoft
Drag & Drop Drag & Plot.

,
New Data Insert.
,
. ,
. OK,
.
,
Undo Edit.

6.

.
,
Excel 200
, .

,
.
.
.


, , ;
.
,
,
.
, .
.
.

6.1. .

,
. 14-
( fx) .

, -
, .
, All .

,

. .
.
, Next.


. ,
, ()
. ,
, ,
.
.

,
Back, .
,
Value ,
. Finish,
.

,
.

.

6.2. .

, .
Editing
Function.

,
.
, ,
,
.
Finish.

, ,
, .
,
.
, .
( X, ""
fx).

, ,
.
.
,
. ""
, .

6.3. .

, ,
13- .

"SUM",
,

. Excel "" ,
"".

, .
,
[Ctrl].
, .

. ,
,
- ( ,
International Windows).
.
"", .

6.4. .

, ,
.

.
Statistical,
AVERAGE. Next
.

.

[Ctrl], .


Finish,
.

6.5. .

-
(, )
,
. , .
,
, .

Financial.
SLN ( ).
.
Next.

.
. ,
Finish.
.

6.6. .


.
, .

( fx).
.

,
() () .

, .
,
.
.
, .
,
.

, ,
, .

6.7. .

Excel
.
() , [Ctrl-~].

,
.
,
.

,
. ,
.
.
,
.
.

7.

Excel

() .


Windows. ,
, ,
, .
, , ,
, () ,
.

,
, Excel .
, OLE
2.0 (Object Linking and Embedding).

7.1. Excel.


Object Insert.
Excel ,
.
,
Windows.
Paintbrush Picture OK.

Paintbrush,
Paste from
Edit.
.
Exit & Return File,
Excel.

,
. Yes.
( OLE)
.
.

7.2. .

,
.
Paintbrush,
.

, ,
.
Exit & Return File,
Excel.
.
Yes.
Excel,
.

7.3. .

Excel ,
. 5-
. Excel
Windows.

Excel
WinWord .
, .

( Word 6- , Excel
- 9-). .

8.


.

,
. ,
,
. ,
().
Excel ,
.
, , -
.

Excel Microsoft
.
,
.

8.1. .


, Excel
.
. ,
: ,
, ().
Excel
. .

.
, .
Excel
. .

8.2. .

Form Data
, ,
. ,
OK.

,
.
, New,
.
..,
.
Close.

.
Excel
.

8.3. .

Form Data
.
Criteria, . ;
Criteria Form.
,
. .
,
,
. .

Find Next. Excel
,
, .
, .
,
, Find Next.

,
Find Prev.
. , ,
,
.

,
"?" "*".
, .
, "???" "",
"" . .
. , "*"
, "".

.
, "<100" ,
, , 100.

, , ,
.
, ,
.

8.4. .

Form Data. ,
,
.
,
, Form.
. -
, Restore. ,
Restore ,
.
, Excel
.

,
Delete. ,
.
, .

8.5. .

Data Filter
AutoFilter.
. ,
.

.
,
. ,
. ,
.
,
.

,
All , .

8.6. .

(),
. ,
.
Custom.

Custom AutoFilter,
.
, ,
, ("",
"", "" ..).
, , " " (>=).
,
. , , 20.
>=20.

. , ,
<=50. And, ,
, Or, ,
.
. OK,
,
. ,
And, ,
20 50.

8.7. .

Sort Data.
. Sort by
.
.
,
,
.

Ascending Descending,
,
, .
OK, Excel
.

9.

Excel
.

,
, .
(Pivot Table),
Excel, .
.


.

, Excel
.
,
, .

9.1. .

PivotTable Data
.
.

.
,
Microsoft Excel List or Database
Next.

,
. ,
.
, .
Next, .

(Layout) .
, -
.
, .
,
.
. ,
, Data.
Row Column
,
() .
Next, .


. Pivot Table Starting Cell ,
( ).
Pivot Table Name .
PivotTable1
. Grand
Totals for Columns Grand Totals for Rows,
.
Save Data With Table Layout
.
AutoFormat Table
.
Finish
Excel .


, .. ,
.
.
, Refresh Data
Data.

9.2. .

, ,
, .
, PivotTable
Data
(Pivot) .
,
. ,
,
.

9.3. .

,
, ,
.

, ,
,
2000$ .

Excel PMT,
,
.
,
.
Goal Seek Tools.

. Set Cell:
,
( ,
PMT). To Value ,
2000$. By changing Cell
, ,
.
, .
, OK.

Excel
.
.
,
OK,
.
Cancel, .

10.

Excel 5.0
MS Query.

Query ()
,
: Ms Access, dBASE, FoxPro, Oracle,
Paradox, SQL Server. , ,
,
.

Excel
,
Query.
Excel.

Query.

10.1. .

Add-Ins Tools.
.
,
,
.

.
MS Query Add-In
, .
OK.

Data.
Get External Data,
Query.

10.2. Query.

Query ,
Windows.

. ,
.


( ),
.
,
, .



.
. ,
, .

,
.
.

. ,
,
.

10.3. .

Query Select Data
Source, .
Use.
Add Tables,
Add.

. ,
Add.
,
Close.

Select Data
Source, Other.
ODBC Data Sources.
New
, , Microsoft Access.
OK. ODBC
Microsoft Access Setup; Select
Database ,
, ,
. OK,
,

.


Query ,
. ,
.
.

- .
,
.
,
,
,
.
..
,
Excel, ,
.
,
.
.

,
10- 11- Query.
,
.

Records
, .

,
Go To
, OK.

Excel, Query Format,
,
.

10.4. .

, .
/ (6-
), ,
.

(
), .
, . ,
.

( )
.
Edit Criteria.
Operator ("", "
" ..). Values
Select Value[s] .
OK,
Edit Criteria.
OK.

Query ,
, .
Return Data to Microsoft Excel,
Query
Excel .

10.5. .

Query.
/ (6- ),
.
,
.

Add Criteria Criteria
.
And,
, Or,
. Field
,
. Operator

("", "" ..). ,
Value , .
,
Values.
Add.
Close.
Query ,
, .

10.6. .

Query,
Excel.

,
Query, Allow Editing
Records.
.
, ..
.

Excel
. ,

. ,
,
.. .

11. EXCEL

Excel
.
Excel, ,
.
, ,
, ,
.

11.1. .


.
Customize .

. ,
Buttons ,
.
,
.

,
.
, ,
. ,
. ,
.
.
.

,
Close, .
. ,
,
.

11.2. .

Options Tools.
, View.
Show ( ),
.

,
. ,

(Formula Bar) (Status Bar).
, .
/ (Note Indicator)
(Info Window).
, , ,
.
,
.

, ,
Full Screen View, .
,
; Full.

.

11.3. .

Options Tools,
, View.
Windows Options ,
.

Automatic Page Break,
, ,

. Formulas
,
.

Gridlines
. ,
(Row & Column Headers),
(Zero Values), (Horizontal
Scroll Bar) (Vertical Scroll Bar)
, (Sheet Tabs).
, OK.

.

11.4. .

Options Tools.
General. Standard
Font: ,
. , ,
.
.
,
Standard Font:.
,
Excel,
Options.

Standard Font:
(Size). 10 ,
.
, , ,
.
.

,
.
, (c:\excel),
Default File Location: .

11.5. .

Options Tools
Module General.
International,
.
,
(English/USA).
. ,
(List Separator), ,
Excel
.

,
Language/Country:. ,
Excel.
,
. ,
International
(Control Panel) Windows. :
,
,
,
.
OK.

11.6. .

Options Tools
Color.

(Standard Colors:),
.
,
(Chart Fills:), -
(Chart Colors:),
-
(Chart Lines:) 24-
,
(Other Colors:).

Copy Colors from ,
.
- ,
, Modify.
Color Picker .
Hue:, Sat: Lum: ,
.
, .

11.7. .

,
. Save As File
XLSTART
Excel. Excel
, .

General
Tools Options. Alternate Startup File Location:
, .


. (Program
Manager) Windows. Microsoft
Office Excel.
Properties File.
, Command Line
Excel.
.
OK. Excel
.
,
.

11.8. .


. ,
.

Save As File.
,
. Save File As Type:
Template.
OK.

.XLT,
. ,
, ,
.

Open File.
[Shift] OK.
.

12. VISUAL BASIC

5.0 Excel
, Visual Basic for
Applications (VBA).

Excel
,

.

VBA,
,
Microsoft. , Word
for Windows, Power Point . VBA
, ,
Windows.
Excel, ,
. ,
Excel,
.

12.1. .

,
Insert Module
Visual Basic (1- ) Module
Insert Macro.
"Modele1".
VBA. ,
, ,
.
Function,
, .
,
End Function.


(
),
.
,
.

Function NDS(Value)

NDS=Value*0.15

End Function

12.2. .

3- Object Browser
VBA View.

, ,
Excel . Methods/Properties:
.
, Options,
Macro Options. Description: ,
.
Function Category ,
. , , ,
Financial.

. Macro Options
OK, Object Browser - Close.

12.3. .

, .
, ,
. 14-
.

.
Financial NDS.
, ,
Next.

.
,
.
Value Finish.

, 15% .

Excel.

Excel


?
?
?