Excel表格处理技巧

第4章表格处理软件Excel2003

电子表格软件Excel是进行数据处理的常用软件。主要以表格的方式

来完成数据的揄入、计算、分析、制作图表和统计。本章是主教材的第

4章的配套试验，通过10个试脸分别介绍表珞的编排、数据的计算、统

计分析和图表的生成等匚xcel的操作方法和步骤。

4.1Excel2003的基础学问

试验4-1Excel的启动、退出和界面介绍

1.试脸目的

①驾驭Excel应用程序快捷方式的建立。

②通过快捷方式驾驭Excel的启动和退出。

③熟识Excel的工作界面。

④练习选择工作表和单元格区域的方法。

2.试验内容

建立Excel的快捷方式，并利用该快捷方式启动Ex二el,视察Excel用户界面，切换工

作表标签，确认及取消单元格输入内容，查看选中工作表及单元格区域状况。

3.试验步骤

①执行“起先”I“搜寻”I“文件或文件夹”吩咐，打开“搜寻结果”窗口，搜寻的

Excel应用程序文件名为"Excel.exe”。

②在搜寻结果中选定Excel应用程序文件，在桌面为其创建快捷方式。

③双击“Excel快捷方式”图标，打开Excel应用程序，相识Excel工作界面：标题

栏、菜单栏、工具栏、编辑栏、工作表标签、工作表窗口、单元格和任务窗格等，如图4T

所示。

④单击Sheet1工作表标签，在A1单元格中输入字符“高校计算机基础”，然后单击编

辑按钮“J”确认输入，同时查看名称框中的单元格名称“AI”；在A2单元格中输入字符

“上机试验”，然后单击“义”按钮取消输入。

⑤分别单击“全选”、“行号”、“列标”按钮，查看选中全部工作表、一行、一列的状

况。

⑥按下Shift键，单击A1单元格，再单击F8单元格，查看选择连续单元格区域的状

况；按下Ctrl键，选中ALB7和C3:E6单元格区域，查看选择不连续单元格区域的状况。

⑦分别单击Sheet2和Sheet3工作表标签，在不同的工作表中输入不同的数据，驾驭切

换不同工作表的方法。

⑧单击工具栏上的“关闭”按钮或执行“文件”I“关闭”吩咐，将新建的工作簿文件

保存到“1）:\练习”文件夹中（假如该文件夹不存在，靖自建），文件名为''基础学问.xls”,

退出Excel应用程序。

标题栏一

菜单栏一

常用工具栏

格式工具栏.

名称框

全选按钮

行号

工作表标签

状态栏-

图4-1Excel2003窗口

4.2工作簿的建立和管理

试验4-2保存、打开和爱护工作簿

1.试脸目的

①驾驭工作簿打开和保存的方法。

②驾驭工作簿设置打开、修改权限密码的方法。

2.试验内容

在磁盘上建立一个名称为“管理工作簿.xls”的工作薄，并将它设置权限密码。

3.试验步骤

①在“D:\练习”文件夹中双击打开名称为“基础学问.xls”的工作簿。

②执行“文件”I“另存为”吩咐，在“另存为”对话框中，将工作簿文件名改为“管

理工作簿.xls”。

③单击“另存为”对话框中“工具”按钮，在弹出的菜单中选择“常规选项”吩咐，

设置打开权限密码为“12345”，设置修改权限密码为“54321”。

④执行“文件”I“关闭”吩咐，退出Excel应用程序。

⑤在“D:\练习”文件夹中双击“管理工作簿.xls”文件，分别输入打开权限密码和修

改权限密码，查看两种密码的不同权限。

4.3工作表的建立

试验4-3Excel数据的输入

1.试脸目的

①驾驭数值、文本、日期等数据的输入。

②驾驭自动填充数据的方法。

2.试脸内容

在“输入数据”工作薄的sheetl中输入如图4-2所示的数据内容。

图4-2输入数据

3.试验步臊

①启动Excel2003窗口，新建一个工作簿文件。

②强制换行：单击“Sheetl”工作表标签，在单元格A1中输入“河南财经政法高校二

“河南财经”输完后，按“AH+Enler”键，对单元格中的内容进行强制换行，再输入“政法

高校”。

③自动换行：在单元格A2中输入“2008北京奥运会”，此时文本长度超出单元格宽

度，若单元格A2中有内容，则超出单元格宽度的文本内容会被隐藏掉。可设置长文本在单

元格中自动换行。选定单元格A2,执行“格式”|“单元格”吩咐，在弹出的如图4-3所示

的“单元格格式”对话框中选择“对齐”选项卡，在“文本限制”区域选中“自动换行”复

选框。

④输入文本型和数值型数据：在单元格A3中输入电话号码，若干脆输入“”，单元

格中显示3716456199,故应将其作为文本输入。输入时，先输入英文的单撇号即键

图4-3设置臼动换行

入：输入数值型数捱时，若输入数据的长度超过单元格宽度，Excel2003自动改用科

学计数法表示，在C3单元格中输入“4102000”，数据显示为“4.10UE+17元

⑤输入分数：若在单元格A4中干脆输入“3/5”，则显示为“3月5日”，输入分数时，

应先输入0和一个空格，再输分数，即在单元格A4中键入：“03/5J

⑥输入日期和时间：在单元格B4中干脆输入“4/5”，则显示为“4月5日”；在C4

单元格按下“Ctrl”+“；”组合键输入当前日期;在D4按下“Ctrl”+“Shift”+“；”组

合键输入当前时间。

⑦自动填充系统定义好的序列：在A5单元格中输入“Sunday”，选中A5单元格，将

鼠标指针移向填充柄向右拖动到要填充数据的最终一个单元格G5,依次填充序列中其他内

容。

⑧填充数列：首先在起始的两个单元格区域A6

和B6分别输入起先的两个数据“1”和“4”，然后选定

这两个单元格区域，将鼠标指针移向该单元格区域右下

角的填充柄向右拖动到要填充数据的最终一个单元格

G6即可。

⑨填充等比序列：在A7单元格中输入初始数据3,

将鼠标指针指向A7单元格右下角的填充柄，并按住鼠

标右键再向右拖动到要填充的单元格位置G7,松开右键图4-4输入序列

时，在出现的快捷菜单中选择“序列”吩咐，在出现的

的“序列”对话框中，选中序列产生在“行”，序列类型选择“等比序列”，步长值设置为“2”,

终止值设置“96”，如图4-4所示，然后单击“确定”按钮。

⑩填充自定义序列：

•首先选择“工具”I“选项”吩咐，出现如图4-5所示的“选项”对话框。单击

“自定义序列”选项卡，在“自定义序列”列表框中选中“新序列”，在“输入序列”

列表框中分别输入“二匕京”、“上海”、“郑州”、“南京”、“杭州”，每输入一项按一下回

车键。输入完后单击“添加”按钮，将输入的数据系列添加到“自定义序列”中，然后

单击“确定”按钮，关闭对话框。

图4-5添加自定义序列

•在单元格A8中输入“北京”，将鼠标指针移向该单元格区域右下角的填充柄向

右拖动到要填充数据的最终一个单元格G8,可以依次自动填充序列中各项内容。

⑪保存工作簿：将工作簿文件以“数据输入”文件名保存到“D:\练习”文件夹中。

4.试验结果样式

试验结果样式如图4-2所示。

试验4-4Excel工作表的计算

1.试脸目的

①驾驭单元格引用。

②驾驭公式进行数据运算。

③驾驭内置的常用函数的运用。

2.试睑内容

建立一个名为“数据计算”的工作簿，计算和编辑表中的数据。

3.试验步骤

①在“D:\练习”文件夹中创建一个文件名为“数据计算”的工作簿。

②在“Shcctl”工作表中输入如图4-6所示的数据内容。

提示：“职工号”列中的数据用自动填充序列方式填充。

.xlS

ABC廊

一

门

部

名

职基本工资课时教课时费

姓应发工资

1_墩

融

系

授

金

而

王

2墩458038

易

系

小

贸31

梅

授

朱27

授4580

融

3欣然

金

系

31教

陈

4墩528036

计

寤

会

圜

系

授

吕3f

5师4580

易

元

讲

贸

系

王

战

傲

融380016

小

国

金

系

6_静

蒋

律

水

讲

7师458023

法

系

杜T

讲

计

柳

会

8师380045

系

青

杨

桶

律

9法3800

系32

段

玲

讨

10会5280

系33

赵玲

11刚458012

系

冯

12528012

系

13458045

系

14380056

值

15

值

16

值

17

18讲师人数40

・A

图4-6“数据计算”的原始数据

③计算课时费：课时费;课时数X小时课酬。

提示：小时课酬利用单元格地址肯定引用方法计算。

④计算应发工资：应发工资:基本工资+课时费。

⑤利用COUNT函数计算总人数。

@利用COUNTIF函数分别计算教授、副教授、讲师的人数。

⑦利用AVERAGE函数分别计算基本工资、课时数、课时费、应发工资的平均值。

提示：计算以上这些字段的最高值和最低值的方法和计算平均值的参数一样，最高值

的函数名为Max,最低值的函数名为Min。

职工号姓名基本工资课时数课时费应发工资

K2013001王雨45803815206100

K2013002米小梅45802710805660

授

K2013003陈欣然52803614406720

教

授

K2013004吕鑫45803714806060

师

K2013005王元3800166404440

K2013006蒋小静4580239205500

系

K2013007杜永宁38004518005600

系

K2013008杨裨膏.3800321280S080

K2013009段楠52803313206600

K2013010赵玲玲4580124805060

K2013011冯刚5280124805760

K2013012关场45804518006380

K2013013孟晓娟38005622406040

45015385316923081267692357692308

教授人数52805622406720

副教授人?38001248044；

讲师人数40

图4-7计算好的数据

4.试验结果样式

试验结果样式如图4-7所示。

4.4工作表的编辑和格式化操作

试验4-5匚作表的编辑

1.试脸目的

①驾驭工作表的数据修改、复制、移动和删除。

②驾驭工作表中单元格、行、列的插入和删除。

③学会为单元格添加批注。

2.试验内容

建立一个名为“编辑工作表”的工作簿，对其中的名称为“工资表”的工作表进行数据

编辑，按要求进行数据输入、编辑，修改、复制、删除表格内容，对表格进行格式设置。

一---

劲

BCDEFGHIT

姓名

序号性分公司工作时勤小时报酬薪水

男

亍

2南京24036

340028

43股泳

5（杨栋诗

6£段情

76刘朝阳

87王雷

女

9£：褚彤彤

另

10g陈勇强

女

1110朱小梅

11手洋男

12

女

1312赵玲玲

美

1413肖刚

女

1，郑丽

15女

161£孟晓妞

|；二》H\Sheetl/Sheet2/Sheet3/|<

图4-8”编辑工作表”原始数据

3.试验步骤

①在“D:\练习”文件夹中建立一个名称为“编辑工作表”的工作薄。

②在“工资表”中输入数据内容，如图4-8所示。

③将其中的工作表“Sheetl”改名为“工资表”。

④将姓名为“刘朝阳”所在的分公司改为“北京二

⑤删除姓名为“杨柳青”的行,将“部门”这一列移动到“分公司”后面。

©在首行前插入两行，在A1单元格中输入文字“诚信财务软件公司”，在A2单元格

中输入文字“员工薪水表”。

⑦分别将姓名为“杜永宁”、“于洋”、“赵玲玲”的单元格插入批注“分公司经理”。

⑧计算员工薪水：薪水=工作时数X小时酬劳。

⑨选中A1:H1单元格区域，单击“格式”工具栏上的“合并并居中”函工具按钮，

设置字体为“黑体”、加粗、18号字；选中A2:H2单元格区域，再次单击“格式”工具栏上

的“合并并居中”工具按机，使两行标题都跨行居中显示.

⑩保存所做编辑操作。

4.试脸结果样式

❾编份工作表.X1M

门

男

软

销

女

培

男

软

女

销

男

培

男

陈

牝

于

一理217126205)0

小

;八

/•京I

南

沟72o0

男

北

郑

京8oO0

女

孤

郑

州

女6O80

38

图4-9编辑好的工作表

试验结果样式如图4-9所示。

试验4-6工作表的格式化

1.试验目的

①学会设置数据的显示格式。

②学会设置表格的边框和底纹。

③驾驭条件格式的用法。

2.试验内容

对试验4-4建立的“数据计算”工作薄中的工作表的数据进行

格式化设置.，运用条件格式功能将“应发工资”介于50(X)元和60(X)

元之间的数据加上灰色底纹，给表格加上合适的边框和底纹。

3.试验步骤

①打开“数据计算”工作簿，用鼠标右键单击“Sheeil”工

作表标签，出现的快捷菜单中选择“移动或复制工作表”吩咐，在

“移动或复制工作表”对话框中的“工作簿”下拉列表框中选择“新

图4-10复制工作表

工作簿”，在“建立副本”复选框中打上勾，单击确定按钮，如图

4-11所示，这时就将“Shcetl”工作表更制到新的工作簿了。

②关闭“数据计算”工作簿，将新工作簿的“Sheetl”工作表标签改名为“工资表二

③在第一行插入标题“老师工资表”，并将A1:H1单元格区域跨行居中显示，设置标

题字体为华文彩云、红色、加粗、20号字，黄色加上12.5灰色图案底纹。

④将工作表中全部数据居中对齐显示。

⑤将“应发工资”前面加上“¥”符号，并利用条件格式吩咐将“应发工资”介于

500()元和6000元之间的数据加上灰色底纹：选定H3:H15单元格区域，执行“格式条

件格式”菜单吩咐，在弹出的如图4-10所示的“条件格式”对话框中的“条件1”下拉列

表框中选择“单元格数值”，在条件运算符下拉列表框口选择“介于”，在条件值框中输入

“5000”到“6000”，单击“格式”按钮，在“单元格格式”对•话框选择“图案”选项卡，

然后选择灰色底纹，单击“确定”按钮，返回“条件格式”对话框，再按一次“确定”按钮

即可。

图4-11“条件格式”对话框

⑥选定A17:A19单元格区域，利用“格式”|“单元格”|“边框”吩咐，给这些单元

格加上两条交叉的斜线

©选中A2:H19单元格区域，利用“格式”|“单元格”|“边框”吩咐，将数据表加上

紫罗兰颜色的双线外边框，蓝色单线内边框。

⑧保存工作簿，文件名为“老师工资表”。

4.试验结果样式

试验结果样式如图4-12所示。

图4-12老师工资表

4.4数据管理

试验4-7数据管理

1.试脸目的

①了解数据管理功能。

②驾驭数据排序的方法。

③驾驭数据筛选的方法。

④驾驭分类汇总的方法。

2.试脸内容

在工作表中进行数据排序、筛选和分类汇总，并进行简洁的图表制作。

3.试脸步骤

①打开试验4-5所建的“编辑工作表”工作簿，将它以“数据管理”文件名另存。

②将工作表标签“Sheet2”重命名为“数据排序”，将“Sheel3”重命名为“自动筛

选”，添加二个工作表，一个命名为“高级筛选”，一个命名为“分类汇总”。

③将“工资表”表中的A3:1I17单元格区域的数据复制到其他四个工作表的A1:G15

区域。

④在“数据排序”工作表进行数据排序：单击“数据排序”工作表中的随意单元格,

执行“数据”I“排序”吩咐，在出现的“排序”对话框中按“薪水”为第一关键字升序,

“小时酬劳”为其次关键字降序，“工作时数”为第三关键字”升序进行排序。排序后的结

果如图4-13所示。

砂数据管理-xLs

AHBC|DEFG

部n

1，序号姓名工作时数小时报酬薪水

23殷泳150314650

310朱小梅180315580

415孟晓姗160386080

513冯刚160457200

65段楠180417380

712赵玲玲170457650

814郑丽200408000

98160528320

攀

10922340o388740

空

11140o4611040

122雷3815200

王

州40o

13740O3815200

矗

141145321200

156006024000

>”八数据挂庄乂自动筛选乂高怒筛选/分类

图4-13排序后的结果

⑤在“自动筛选”工作表进行自动筛选：单击“自动筛选”工作表中的的随意单元格,

执行“数据”I“筛选”I“自动筛选”吩咐，筛选出分公司=“郑州”并且“薪水”大于等

于6000元并小于等于20C00元的员工。筛选结果如图4-14所示。

故理10

g1^

S1

也

J蜘

ra壬

罕

2J为M

4

二

胡

LLNIW就件¥w

翅1

血1

V

Ll比份

期

嘴-

L

IrnH-f;iy-

图4-14自动筛选后的结果

⑥在“高级筛选”工作表中筛选出工作时数等于160,或者薪水小于5000元的人员

名单:

•单击“高级筛选”工作表标签，在jl：k3单元格将筛选❾数提管理.X1S

条件输入到如图4-15所示的条件区域。

・单击数据区域中的随意

高级第选I又I

单元格，选择“数据”I“筛选”I

方式

O在原有区域显示斯选结果（L）“高级筛选”吩咐，出现如图4-16

©将浦选结果复售瞪」耳怙位置

所示的“高级筛选”对话框，选中

列表区爆C）：$A$lSKT15区I“将筛选结果复制到其他位置”单图4/5设置条件区域

条件区煤（£）：$J$1SK33（53

复隹画Q）：A17回选按钮，设置“列表区域”地址范

团选探不重懿记录围为$A$1:$H$15,“条件区域”地址范围为$J$1:$K$3,选择

I瑜定1[取消|将筛选后的结果“复制到"A17单元格，在“选择不重复的

记录”复选框中打上勾，单击“确定”按钮。高级筛选后的

图4-16高级筛选对话框结果如图4-17所示。

目数据管理.X13E0®

姓名

於号

也%

17部门工作时数小时报酬薪水

股

泳

郑

培训

州

男

3部5o

18

南

京

轨件

女

部

8借

彤832o

1J31

京

南

软件

男

浮

冯

刚52720o

20

孟

洲

邠

软件

女

部

姗45608O

2138

…祓据排序技动筛选'高逊选《分娄|<

图4-17高级筛选后的结果

⑦在“分类汇总”工作表中根据“分公司”以“最大值”和“求和”的方式对“薪水”

图4-18分公司薪水最大值图4-19分公司薪水求和汇总

进行分类汇总:

•按“分公司”排序：单击“分类汇总”工作表中的“分公司”字段下的随意一个单

元格（如：D4单元格），单击常用工具栏中的升序排序按钮。

•计算每个分公司中薪水的最大值：执行“数据”I“分类汇总”吩咐，在弹出的如

图4T8所示的“分类汇息”对话框中选择“分类字段”为“分公司”，“汇总方式”为“最

大值”，选定“汇总项”为“薪水”，单击“确定”按钮.

•计算每个分公司薪水的总值：执行“数据”I“分类汇总”吩咐，在弹出的如图

4-19所示的“分类汇总”对话框中选择“分类字段”为“分公司”，“汇总方式”为“求和”,

选定“汇总项”为“薪水，将”替换当前分类汇总”复选框对勾去抻，单击“确定”按钮。

汇总结果如图4-20。

的数据管理.

xlsE®因

习2JABCD一EFGH

1序号姓名性别分公司部门工作时数小时报酬薪水

IT25段桶北京软件部180417380

1：■3_6刘朝阳北京销售部4006024000

49陈勇强男北京销售部230388740

514郑丽女北京软件部200408000

6北京最大值24000

•7北京汇总48120

87王雷男杭州培训部4003815200

r1-•

912赵玲玲女杭州软件部170457650

10杭州最大值15200

杭州汇总22850

・11

121杜永宁'男南京软件部2如4611040

•138楮彤彤南京软件部1