《高级文秘与办公自动化》课件第8章

第一节

Excel的窗口组成及基本操作第二节工作表格式化第三节公式与函数

第四节数据的图表化

第五节数据的排序和筛选

第六节页面设置和打印

1．Excel的窗口组成

Excel2003启动后，屏幕显示Excel2003应用程序窗口，如图8.1所示。第一节Excel的窗口组成及基本操作图8.1Excel2003窗口

1)工作簿

2)工作表

3)单元格

4)单元格区域

B2:D4表示单元格B2到D4之间所有包括的区域，sheet2!A1:A20表示在sheet2工作表中的A1到A20单元格之间所有单元格，如图8.2所示。

5)活动单元格

6)工作表标签

工作表标签用来识别工作簿中的每一个工作表的名称，如图8.3所示。图8.2单元格区域图8.3工作表标签

2．工作表的操作

1)选择工作表

2)工作表重命名

(1)双击工作表标签，工作表标签将突出显示，再输入新的工作表名称并按回车键确定。

(2)右击工作表标签，使用快捷菜单的重命名命令，如图8.4所示。图8.4工作表重命名操作步骤

3)插入工作表

4)删除工作表

3．数据输入

1)三种类型数据的输入

(1)数值输入。

(2)文本输入。

(3)日期和时间输入。

2)数据自动输入

(1)填充相同的数字型或不具有增减可能的文字型数据。单击填充内容所在的单元格，将鼠标移到右下角填充柄上，当鼠标指针变成黑色十字形时，按住鼠标左键拖曳即可，如图8.5所示。图8.5填充相同数据

(2)填充日期时间型及具有增减可能的文字型数据操作方法同(1)，只是在拖曳过程中按Ctrl键，如图8.6所示。

(3)产生一个序列。首先在单元格中输入初值并回车，然后单击【编辑】菜单→【填充】命令→从级联菜单选择【序列】命令，出现如图8.7所示的【序列】对话框。在对话框中选择【序列产生在】、【类型】，确定【步长值】、【终止值】数据，单击【确定】按钮可输入一个序列数值，如图8.7所示。图8.6填充增减数据图8.7【序列】对话框

(4)自定义序列。单击菜单【工具】→【选项】→【自定义序列】选项卡，单击【输入序号】文本框，输入一个序列成员按一次回车键，如“第一名”、“第二名”、……，输入完毕单击【添加】按钮。序列定义成功以后就可以使用它来进行自动填充了，如图8.8所示。图8.8自定义序列操作步骤

4．实践练习

内容：

(1)建立一个名为职工信息的工作簿。

(2)在职工信息工作簿中工作表Sheet1的A2：G6区域中输入如表8-1所示的数据。表8-1职工信息表

(3)在Sheet2工作表中，利用填充功能，在A1:A10区域中快速输入1、2、3、…、10；在B2:B9区域中输入星期1、星期2、星期3、…。

(4)将工作表Sheet1改名为职工花名册；Sheet2改名为自动填充表；插入两张工作表并更名。

(1)建立一个名为职工信息的工作簿。

(2)在职工信息工作簿中工作表Sheet1的A2:G6区域中输入表8-1的数据。

(3)在Sheet2工作表利用填充功能，在A1:A10区域中快速输入1、2、3、…、10；在B2：B9区域中输入星期1、星期2、星期3、…。

(4)将Sheet1改名为职工花名册；Sheet2改名为自动填充表；插入两张工作表并更名。

①双击Sheet1工作表标签，输入职工花名册后按回车键。

②右击Sheet2工作表标签，在快捷菜单中选择重命名，输入自动填充表后按回车键。

③单击【插入】→【工作表】，然后用上述命名工作表的方法给工作表取名，如图8.9所示。图8.9职工信息表

1．工作表的编辑

1)选定单元格及单元格区域

(1)选定单个单元格。

(2)选定单个单元格区域。

(3)选定多个不相邻单元格区域。

(4)选定所有单元格。

(5)选定某一行或一列：单击【行号】或【列标】按钮。第二节工作表格式化

2)移动数据

3)复制数据

4)清除数据

清除是删除单元格中的数据，而保留单元格位置的操作。

(1)选定单元格或区域，按Del键。

(2)选定单元格或区域，鼠标右击在快捷菜单中选择【清除内容】命令。图8.10【删除】对话框

(3)选定单元格或区域，单击【编辑】→【清除】命令。

5)删除数据

删除是将单元格中数据连同单元格一并删除。

(1)选定不需要的行、列或单元格，单击【编辑】→【删除】命令，在【删除】对话框中选择一项进行删除，如图8.10所示。

(2)选定不需要的行、列或单元格，右击出现快捷菜单，选择删除命令进行删除。

6)插入行、列

2．工作表

1)调整行高

(1)选定调整行高的区域。

(2)单击【格式】→【行】命令。

(3)单击子菜单的【行高】命令，屏幕出现【行高】对话框，如图8.11所示。

(4)在【行高】对话框中，输入要设定的高度。

(5)单击【确定】按钮，完成行高的调整。

2)调整列宽

方法一：用【格式】菜单命令调整列宽。

(1)选定调整列宽的区域。

(2)单击【格式】菜单中的【列】命令。

(3)单击子菜单的【列宽】命令，屏幕出现【列宽】对话框，如图8.12所示。图8.10【删除】对话框图8.11【行高】对话框

图8.12【列宽】对话框

(4)在【列宽】对话框中，输入要设定的宽度。

(5)单击【确定】按钮，完成列宽的调整。

3)设置工作表的颜色

(1)选定要设置颜色的单元格区域。

(2)单击工具栏上的【填充颜色】下拉按钮。

(3)在【填充颜色】下拉窗口中选择要使用的颜色，如图8.13所示。图8.13常用工具栏填充颜色调色板

(1)选定要设置颜色的单元格区域。

(2)选择【格式】菜单中的【单元格】命令。

(3)选择【图案】选项卡，选择填充颜色和图案，如图8.14所示。图8.14选择填充颜色和图案

4)设置表格框线

(1)选定要加上框线的单元格区域。

(2)选择【格式】菜单中的【单元格】命令，出现【单元格格式】对话框，如图8.15所示。

(3)单击【边框】选项卡，选择【线条】、【颜色】。

(4)选择【预置】或【边框】项下的各个按钮。

(5)单击【确定】按钮，完成操作。图8.15【单元格格式】对话框

(1)选定要加上框线的单元格区域。

(2)选择【格式】工具栏上的【边框】右边下拉列表，如图8.16所示。

(3)单击下拉边框列项。图8.16工具边框下拉列表

3．设置字符的格式

1)设置字体和字号

(1)选定要设置字体的所有单元格。

(2)单击菜单【格式】→【单元格】命令，屏幕出现单元格格式字体对话框，如图8.17所示。

(3)在【字体】选项列表中，选择字体。

(4)在【字号】列表中，选择字号。

(5)单击【确定】按钮。图8.17设置字体和字号使用格式工具栏中的【字体】命令，如图8.18所示。

(1)选定要设置字体的所有单元格。

(2)在格式工具栏的【字体】列表中选择字体。

(3)在格式工具栏的【字号】列表中选择字号。图8.18格式工具栏

2)设置字符的颜色

3)单元格数据的对齐方式

(1)常用对齐。

①先选定单元格，单击【格式】菜单中的【单元格】命令，选择【对齐】选项卡，在【文本对齐】组合框中，从【水平对齐】和【垂直对齐】下拉式列表框中选择对齐的方式，如图8.19所示。图8.19设置对齐方式② Excel在常用工具栏中还提供了四个工具按钮，用于对齐功能，它们依次是左对齐、居中、右对齐和合并居中，其操作与Word基本一样，如图8.20所示。

(2)标题合并及居中对齐。先选定标题在进行合并及居中排列时所用的单元格区域。单击工具栏上的合并及居中按钮，使选定的单元格区域合并为一个单元格，如图8.21所示。图8.20对齐工具图8.21标题和文本居中效果

4．设置数字的格式

1)设置单元格数字格式

(1)选定要格式化的单元格或区域。

(2)选择【格式】菜单→【单元格】命令→弹出【单元格格式】对话框。

(3)选定【数字】选项卡，如图8.22所示。

(4)在【分类】列表中，选择所需要的数字类型，在右边选择数字格式。

(5)单击【确定】按钮。图8.22设置单元格数字格式利用格式工具栏。

格式工具栏有五个用于设置单元格数字格式的工具按钮：货币样式、百分比样式、千分分隔样式、增加小数位数和减少小数位数，如图8.23所示。图8.23数字格式工具

5．实践练习

内容：

(1)合并单元格，输入江苏正达企业职工信息表，如图8.24所示，并设置字体为隶书，字号为28。图8.24实践窗口

(2)给表头添加背景颜色，设置文字对齐方式为居中。

(3)设置边框，并使外边框加粗，如图8.24所示。

(2)给表头添加背景颜色，设置文字对齐方式为居中。

①单击A2单元格，输入编号，单击B2，输入姓名，依次输入后面的内容。

②鼠标左键拖曳从A2:F2单元格区域，并单击右键。

③在快捷菜单中选择设置单元格格式，弹出【单元格格式】对话框，根据操作步骤设置，如图8.25所示。图8.25设置单元格底纹操作步骤④单击对齐标签，按图8.26所示步骤进行设置。

(3)设置边框，并使外边框加粗。

①鼠标左键拖曳所有内容。

②单击格式工具栏上的【边框】按钮。

③在边框列表框中选择边框，然后单击【加粗】边框按钮。图8.26设置单元格对齐格式操作步骤

1．使用公式

1)公式的输入

(1)选择要输入公式的单元格H3。

(2)在编辑栏的输入框中输入一个“=”。

(3)输入“E3+F3-G3”。

(4)按回车键或单击编辑栏上的【√】按钮，则在H3单元格中出现计算结果，如图8.27所示。第三节公式与函数图8.27在单元格H3中输入公式

2)公式的复制

若所有行的计算相同，则可采用公式复制的方法。复制公式的方法是：单击已有公式的单元格H3，鼠标拖曳至该单元格右下角，指针变为+，然后拖曳填充柄至最后一个单元格H12，这样所有员工的实发工资就全部计算出来了，如图8.28所示。

图8.28用填充方式复制公式

3)自动求和

在工作表窗口的工具栏中有一个【自动求和】按钮，利用该按钮，可以对工作表中所设定的单元格自动求和。

2．使用函数

Excel2003中的函数是一些已经定义好的公式，大多数函数是经常使用的公式的简写形式。函数通过参数接收数据，并返回结果。大多数情况下函数返回的是计算的结果，也可以返回文本、引用、逻辑值、数组或者工作表的信息。

1)函数语法

2)使用函数向导输入

(1)选定要输入函数的单元格。

(2)单击【插入】→【函数】命令，或者在常用工具栏上的【自动求和】按钮右侧的下拉列表框中，选择【其他函数】，屏幕上出现一个【插入函数】对话框，如图8.29所示。

(3)在【选择类别】列表框中，选择要输入的函数类型，例如选择【常用函数】。

(4)在【选择函数】列表框中，选择所需要的函数，例如选择【SUM】函数。

(5)单击【确定】按钮，弹出【函数参数】对话框，如图8.30所示。图8.29【插入函数】对话框图8.30【函数参数】对话框

3)几个常用函数

(1)求和函数：SUM()

(2)求平均值函数：AVERAGE()

(3)求最大值函数：MAX()

(4)求最小值函数：MIN()

(5)计数函数：COUNT()

(6)条件判断函数：IF()

(7)条件计数函数：COUNTIF()

(8)条件加法函数：SUMIF()

格式：SUMIF(取值范围，条件表达式)

功能：计算某区域内满足条件的单元格并进行累加。

(9) RANK：排名次函数

格式：RANK(参数，数据区域，升降序)

功能：计算某数值在数据区域内相对其他数值的大小排位。

(10)与函数：AND()

(1)单击E2单元格，输入“=IF(AND(C2>60,D2>60),(C2+D2)/2,IF(AND(C2>60,D2<60),”补机试“,IF(AND(C2<60,D2>60),”补笔试“,”重修“)))”。

(2)鼠标拖曳至E2单元格，鼠标指针变成“+”，拖曳到E5单元格，结果如图8.31所示。图8.31笔试和机试判断操作步骤

(11)或函数：OR()

操作步骤如下：

①单击D2单元格，输入“==IF(OR(B2=”女“,C2>=20),50,”“)”。

②鼠标拖曳至D2单元格，鼠标指针变成“+”拖曳到D7单元格，结果如图8.32所示。

(12)非函数：NOT()

(13)截获字符函数：MID()

(14)取整函数：TRUNC()图8.32三八妇女节发奖金的操作步骤

3．单元格引用

1)相对引用

用相对引用的方法计算图8.33中每个学生总分的步骤如下：

(1)用输入公式的方法在王旭的总分单元格F2中输入公式“=B2+C2+D2+E2”，之后按回车键，把王旭的总分计算出来，如图8.33所示。图8.33相对引用公式

(2)用鼠标单击王旭的总分单元格F2，使其成为活动单元格，用鼠标拖曳其填充柄向下到F8单元格处，松开鼠标就会把公式复制到其他同学的总分处，并可以把其他同学的总分都计算出来，结果如图8.34所示。图8.34相对引用的计算结果

2)绝对引用

绝对引用是指在把公式复制或填入到新的位置时，使其中的单元格地址保持不变。设置绝对地址需在行号和标号前加符号【$】，如$B$3、$A$2:$F$5等。

例如：在职工工资表中按基本工资每月增长15%的增长率计算职工实发工资，如图8.35所示。图8.35绝对引用的计算结果

3)混合引用

混合引用是指在一个单元格地址中，既有相对引用又有绝对引用。例如C$14，$C14。如用混合引用的方法实现乘法口诀表，如图8.36所示。图8.36乘法口诀表

4．实践练习

内容一：

(1)按照学生成绩表完成各学生的总分、平均分、等级、名次等选项，如图8.37所示。图8.37成绩表实践操作窗口

(2)根据成绩统计表完成表内所有选项，如图8.38所示。图8.38成绩统计表实践操作窗口步骤：

(1)按照学生成绩表完成各学生的总分、平均分、等级、名次等选项。

●求和：

①单击单元格I3后再单击地址栏上的按钮，弹出【插入函数】对话框，如图8.39所示。

②选择求和函数SUM后单击【确定】按钮。图8.39【插入函数】对话框③在弹出的【函数参数】对话框的【Number1】框后单击【单元格选择】框，如图8.40、图8.41所示。

④将鼠标从单元格D3拖曳到H3，如图8.42所示。图8.40【函数参数】对话框图8.41【单元格选择】框图8.42计算单元格中数值的和⑤单击【函数参数】对话框中的【确定】按钮，这时结果保存在I3单元格中。

⑥将鼠标拖曳到单元格I3的右下角，变成“+”后拖曳到I16单元格，完成所有学生的成绩总分计算，如图8.43所示。图8.43成绩求和结果①单击单元格K3后，单击地址栏上的按钮。

②在【插入函数】对话框中选择判断函数IF后单击【确定】按钮。

③在Logical_text中输入“J3>=300”，在Value_if_true中输入“合格”，在Value_if_false中输入“不合格”后单击【确定】按钮，如图8.44所示。图8.44IF函数对话框④将鼠标拖曳到单元格K3的右下角变成“+”后再拖曳到K16单元格，完成等级结果，如图8.45所示。

●求名次：

①单击单元格L3后单击地址栏上的按钮。

②在弹出的【插入函数】对话框中的选择类别中选择统计函数RANK后单击【确定】按钮，如图8.46所示。图8.45等级结果图8.46RANK函数对话框③在弹出的【函数参数】对话框的【Number】框中输入J3。

④在Ref后单击单元格选择按钮。

⑤将鼠标从J3拖曳到J16单元格，单击单元格选择按钮。

⑥在Order框中输入0后，单击【确定】按钮，如图8.47所示。

⑦将鼠标拖曳到单元格L3的右下角变成“+”后再拖曳到L16单元格，完成名次排序，结果如图8.48所示。图8.47RANK参数对话框图8.48名次结果●求各科总分：

①单击D18单元格后，单击地址栏中的按钮。

②在弹出的【插入函数】对话框中选择求和函数SUM后单击【确定】按钮。

③在弹出的【函数参数】对话框中的【Number1】框后单击单元格选择按钮。

④用鼠标从单元格D3拖曳到D16，单击函数参数的地址选择按钮。

⑤单击【函数参数】对话框中的【确定】按钮，这时结果保存在D18单元格中。

⑥将鼠标拖曳到单元格D18的右下角变成“+”后再拖曳到H18单元格，完成各科成绩总分计算，如图8.49所示。图8.49成绩列项计算结果●统计优良率：

①单击成绩统计表B12单元格。

②在地址栏中输入“=B7/B3”。

③将鼠标拖曳到单元格B12的右下角，变成“+”后再拖曳到单元格F12，如图8.50所示。

内容二：

(1)在职工信息表中通过身份证号码计算出生年月、性别、年龄，如图8.51所示。图8.50成绩统计表计算结果图8.51职工信息表●计算年龄：

①单击单元格F3。

②输入“=YEAR(TODAY())-YEAR(D3)”。

③在地址栏中单击【√】按钮。

④将鼠标拖曳到单元格F3的右下角变成“+”后拖曳到单元格F12，如图8.52所示。

(2)计算职工扣款、应发工资、所得税、实发工资，如图8.53所示。图8.52职工信息计算结果图8.53职工工资表数据图表就是将单元格中数据以各种统计图表的形式显示，使得数据更加直观。通过图表可以清楚地了解各个数据的大小以及数据的变化情况，方便对数据进行对比和分析。

1．创建图表

(1)在如图8.54所示的学生成绩表中，选择用于创建图表的数据区域A1:G8，或单击任一单元格。第四节数据的图表化图8.54学生成绩表

(2)单击常用工具栏中的【图表向导】按钮，或单击【插入】菜单中的【图表】，出现如图8.55所示的【图表向导-4步骤之1-图表类型】对话框。

(3)单击【标准类型】选项卡，选择一种类型，再单击【下一步】按钮后进入图8.56所示的【图表向导】对话框。

(4)在该对话框的【数据区域】选项卡中可以修改创建图表的数据区域，在【系列】选项卡中可以修改系列的名称和值以及分类轴的标志。不修改或修改完毕后，单击【下一步】按钮，进入图8.57所示的【图表选项】对话框。

图8.55【图表类型】对话框图8.56【图表源数据】对话框图8.57【图表选项】对话框

(5)在【图表选项】对话框中单击【下一步】按钮，出现如图8.58所示的对话框。

(6)在该对话框中选择把图表建在新工作表上还是嵌入到工作表中，本例选择嵌入。单击【完成】按钮，创建的图表如图8.59所示。图8.58【图表位置】对话框图8.59嵌入式工作图表示例

2．实践练习

内容：

(1)根据如图8.60所示的江苏正达企业销售情况，制作柱形图表。

(2)根据钼冶炼销售情况制作饼形图表。图8.60销售情况表

步骤：

(1)根据江苏正达企业销售情况制作柱形图表。

①将鼠标从A3拖曳到E7(选择数据源)。

②单击【插入】→【图表】，弹出【图表向导】对话框，如图8.61所示。

③选择柱形图类型后单击【下一步】按钮。

④在【图表源数据】对话框中单击【下一步】按钮，如图8.62所示。图8.61【图表向导】对话框图8.62【图表源数据】对话框⑤在图表选项中的图表标题中输入“正达企业销售情况表”后，单击【下一步】按钮，如图8.63所示。

⑥在如图8.64所示的【图表位置】对话框中，选择保存图表的工作表后单击【完成】按钮，制成的柱形图表如图8.65所示。图8.63输入图表标题图8.64【图表位置】对话框图8.65销售情况柱形图表

(2)根据钼冶炼销售情况制作饼图图表。

①将鼠标从A5拖曳到E5(选择数据源)。

②单击【插入】→【图表】按钮。

③选择饼形图表后单击【下一步】按钮。

④在【图表源数据】对话框中单击【下一步】按钮。

⑤在图表选项中的图表标题中输入“钼冶炼销售情况表”后，单击【下一步】按钮。

⑥在图表位置对话框中选择保存图表的工作表后单击【完成】按钮，制成的饼形图表，如图8.66所示。图8.66钼冶炼销售情况饼形图表

1．数据的排序

其具体操作如下：

(1)单击需要进行排序操作的数据清单中的任一单元格。

(2)选择【数据】→【排序】命令，打开【排序】对话框，如图8.67所示。

(3)在该对话框中可设置进行排序所依据的主要、次要或第三关键字。

(4)单击【确定】按钮，表格中的数据就按所设置的条件进行重新排序。第五节数据的排序和筛选图8.67【排序】对话框

2．数据的筛选

(1)单击需要进行筛选的数据清单中的任一单元格。

(2)选择【数据】→【筛选】命令。

(3)数据清单中各字段名称的右侧均出现按钮，如图8.68所示，单击项目右侧的按钮，打开如图8.69所示的下拉列表。

(4)在列表的选择条件中选择“女”，最终显示筛选结果如图8.70所示。图8.68自动筛选设置图8.69筛选条件选择列表图8.70筛选结果

3．实践练习

内容：

(1)在学生成绩表中进行排序，排序方法是大学语文升序、高数升序、计算机降序。

(2)学生成绩表数据按高数成绩>80分进行自动筛选。

步骤：

(1)在学生成绩表中进行排序，排序方法是大学语文升序、高数升序、计算机降序。

①在学生成绩表数据区任意单击。

②单击【数据】→【排序】，弹出【排序】对话框。

③在主关键字中选择“大语”。

④在次关键字中选择“高数”。

⑤在第三关键字中选择“计算机”，并在后面单击【降序】按钮，如图8.71所示。图8.71排序设置⑥在排序对话框中单击【确定】按钮。排序结果如图8.72所示。

(2)学生成绩表数据按高数成绩>80分进行自动筛选。

①在学生成绩表数据区任意单击。

②单击【数据】→【筛选】→在级联菜单中单击【自动筛选】按钮。

③单击“高数”字段右边的下拉按钮，在下拉列表框中选择自定义，如图8.73所示。图8.72排序结果图8.73学生成绩表自动筛选图④在【自定义自动筛选方式】对话框“高数”下面选择“大于”，后面输入80，如图8.74所示。

⑤单击【确定】按钮。结果如图8.75所示。图8.74【自定义自动筛选方式】对话框图8.75自定义筛选结果

1．页面设置

单击【文件】菜单下的【页面设置】命令，在【页面设置】对话框中可进行【页面】、【页边距】、【页眉和页脚】、【工作表】等的设置，如图8.76所示。第六节页面设置和打印图8.76【页面设置】对话框

1)【页面】、【页边距】选项卡

如图8.76所示，【页面】选项卡可以设置纸张方向、缩放比例、纸张大小、打印质量、起始页码；【页边距】选项卡可设置页面四个边界的距离、页眉和页脚的上下边距等，设置方法与Word2003类似，不再表述。