第5章
Excel表格处理





实验项目一表格基本操作
一、 实验目的

（1） 理解工作簿、工作表、单元格等基本概念。掌握在Excel中新建工作簿、保存数据的基本方法。
（2） 掌握数据类型的概念和作用，能熟练判断并设置单元格的数据类型，快速准确输入各种类型数据。
（3） 掌握数据验证的设置方法，保证输入数据有效。
（4） 掌握工作表中单元格格式设置方法，能灵活运用格式及条件格式美化表格。
（5） 掌握单元格的插入、复制、移动、删除、清除等操作方法。
（6） 掌握工作表的插入、移动、复制、删除操作方法。

二、 实验范例
范例1： 输入数据★
新建一个工作簿，将Sheet1工作表重命名为“学生科研项目”，并在工作表中A1单元格开始区域中输入如图5.1所示数据。将工作簿保存为EF11.xlsx。★


图5.1学生科研项目表


要求： 项目编号、项目名称、姓名、专业为文本型； 开始日期为日期型，“年/月/日”形式显示(不受操作系统影响)； 实施进度为百分比，没有小数位； 项目经费为货币型，采用人民币货币符号，保留2位小数； 中期报告成绩为数值型，没有小数位。
操作步骤： 
(1) 启动Excel，单击“空白工作簿”，创建一个新工作簿。右击新工作簿中的Sheet1工作表标签，在弹出的快捷菜单中选择“重命名”，输入工作表名“学生科研项目”，按Enter键确认。
(2) 选定“学生科研项目”工作表的A1单元格，输入“项目编号”，按右移键，在B1单元格输入“项目名称”，并依次向右，完成标题行的输入。
(3) 选定A2至A11单元格区域，单击“开始”选项卡“数字”组中的“常规”下拉列表，选择“文本”，将A2至A11单元格区域的数字类型设置为文本型。
(4) 选定A2单元格，输入项目编号“01200101”。选定A2单元格，将鼠标指向单元格右下角的填充柄，向下拖动到A5单元格，完成第一组项目编号的序列填充。采用同样的方法在A6单元格输入项目编号“08200301”，并拖动填充柄完成第二组项目编号的序列填充。
(5) 选择B2至D11单元格区域，单击“开始”选项卡“数字”组右下角的对话框启动器，在“设置单元格格式”对话框“数字”选项卡的“分类”列表框中选择“文本”，单击“确定”按钮，设置选定区域的数字类型为文本型。

第
5
章
Excel表格处理


大学计算机实训教程（混合教学版）
(6) 分别在B2至B11、C2至C11单元格区域输入项目名称和学生姓名。
(7) 选择D2至D5单元格区域，输入“会计”，按Ctrl+Enter键，D2至D5单元格区域都将输入“会计”。在D6单元格输入“计算机”。右击D7单元格，在弹出的快捷菜单中选择“在下拉菜单中选择”选项，在弹出的下拉列表中选择“计算机”。拖动D7单元格右下角的填充柄，将“计算机”填充复制到D8至D11单元格。


图5.2“设置单元格格式”对话框

(8) 选择E2至E11单元格，单击“开始”选项卡“数字”组右下角的对话框启动器，在如图5.2所示的“设置单元格格式”对话框“数字”选项卡的“分类”列表框中选择“日期”，在“类型”列表框中选择“2012/3/14”，单击“确定”按钮。在E2至E11单元格输入各项目的开始日期。
(9) 选择F2至F11单元格，设置数字类型为“百分比”，小数位数为0。确定后输入百分比数据。
(10) 选择G2至G11单元格，设置数字类型为货币，在“货币符号(国家/地区)”列表中选择货币符号为“￥”，小数位数为2。确定后输入项目经费数据。
(11) 选择H2至H11单元格，设置数字类型为数值，小数位数为0。确定后输入中期报告成绩。
(12) 单击快速访问工具栏中的“保存”按钮，单击“另存为”菜单中的“浏览”按钮，在“另存为”对话框中设置文件保存位置，并输入文件名EF11，设置保存类型为“Excel工作簿(*.xlsx)”，单击“保存”按钮保存工作簿。

范例2： 数据验证
1. 数值范围验证★
打开“电脑销售.xlsx”工作簿，在“1月销售明细”工作表中设置数据验证条件： 销量应该是整数且大于等于0，输入数据不满足验证条件时，弹出标题为“销量输入错误”的停止对话框，提示“销量不能为负值！”。将工作簿保存为EF121.xlsx。
操作步骤： 
(1) 打开“电脑销售.xlsx”工作簿，选择“1月销售明细”工作表。
(2) 选择E2至E28单元格区域，单击“数据”选项卡“数据工具”组中的“数据验证”按钮，在“数据验证”对话框的“设置”选项卡中设置允许“整数”，数据“大于或等于”，最小值为0，如图5.3所示。



图5.3设置数据验证条件


(3) 在“出错警告”选项卡中设置样式为“停止”，标题为“销量输入错误”，在错误信息中输入“销量不能为负值！”，如图5.4所示。单击“确定”按钮完成设置。



图5.4设置出错警告信息


(4) 在E2单元格输入-5，确认时系统弹出对话框显示出错警告信息，如图5.5所示。



图5.5“销量输入错误”停止对话框


(5) 将文件另存为EF121.xlsx。

2. 数据序列验证★★
打开“电脑销售.xlsx”工作簿，在“1月销售明细”工作表中设置数据验证条件： 品名为序列，只能在下拉列表中选择“笔记本”“平板电脑”“台式机”三项中的一项，如图5.6所示。将工作簿保存为EF122.xlsx。★★



图5.6序列输入效果


操作步骤： 
(1) 打开“电脑销售.xlsx”工作簿。
(2) 选择“1月销售明细”工作表中B2至B28单元格，单击“数据”选项卡“数据工具”组中的“数据验证”按钮，在“数据验证”对话框的“设置”选项卡中设置允许“序列”，在“来源”文本框中输入“笔记本,台式机,平板电脑”(注意序列中的逗号应采用英文逗号)，如图5.7所示，单击“确定”按钮。



图5.7设置数据验证序列


(3) 单击B2单元格右侧的三角形，展开输入序列选择要输入的项。
(4) 将文件另存为EF122.xlsx。

3. 与其他单元格相关的数据验证★★★
打开“电脑销售.xlsx”工作簿，在“商品价格”工作表中设置数据验证条件： 单价应大于0、小于等于进价的1.3倍。输入单价超出范围时，弹出“单价超出范围”警告对话框，显示提示文字“单价应大于0，且不超过进价的1.3倍！”，如图5.8所示。将工作簿保存为EF123.xlsx。



图5.8“单价超出范围”警告效果


操作步骤： 
(1) 打开“电脑销售.xlsx”工作簿。
(2) 选择“商品价格”工作表中D2至D10单元格，单击“数据”选项卡“数据工具”组中的“数据验证”按钮，在“数据验证”对话框的“设置”选项卡中设置允许“小数”，数据“介于”，在最小值中输入0，单击最大值文本框右侧的“折叠对话框”按钮，单击C2单元格，系统自动在文本框中填入“=C2”。单击文本框右侧的“展开对话框”按钮，返回“数据验证”对话框，在最大值文本框中=C2后面继续输入“*1.3”，表示进价的1.3倍，如图5.9所示。



图5.9设置与C2单元格相关的数据验证条件


(3) 在“出错警告”选项卡中设置样式为“警告”，标题为“单价超出范围”，在错误信息中输入“单价应大于0，且不超过进价的1.3倍！”，如图5.10所示。单击“确定”按钮。



图5.10设置“价格超出范围”出错警告信息


(4) 将文件另存为EF123.xlsx。

范例3： 编辑表格★
打开“食物营养成分.xlsx”工作簿，完成以下表格编辑操作。将工作簿保存为EF13.xlsx。
(1) 将“蔬菜水果”工作表复制一份，重命名为“食品营养成分”。
(2) 删除“食品营养成分”工作表中的第22行。
(3) 在“钙(ug)”“能量(Kcal)”两列左侧分别插入一列。
(4) 将“肉类”工作表中数据列表的标题行复制到“食品营养成分”工作表的第一行。将“肉类”工作表中第2行到第8行的全部数据复制到“食品营养成分”工作表第22行开始的区域。
(5) 将“食品营养成分”工作表中所有vitamin替换为“维生素”。将A1到J27单元格区域中的空白单元格替换为0，将P列中的空白单元格替换为短横线“”。
(6) 删除“蔬菜水果”“肉类”两张工作表。
操作步骤： 
(1) 打开“食物营养成分.xlsx”工作簿。
(2) 右击“蔬菜水果”工作表，在弹出的快捷菜单中选择“移动或复制”选项，在“移动或复制工作表”对话框中选择“建立副本”复选框，如图5.11所示，单击“确定”按钮复制工作表。



图5.11“移动或复制工作表”对话框


(3) 双击复制的工作表“蔬菜水果(2)”标签，将工作表表名改为“食品营养成分”，按Enter键确认。
(4) 选择A22单元格，单击“开始”选项卡“单元格”组中的“删除”按钮，选择“删除工作表行”命令，删除第22行。
(5) 单击J列列标签选择J列，单击“开始”选项卡“单元格”组中的“插入”按钮，在“钙”列左侧插入一列。选择P1单元格，单击“开始”选项卡“单元格”组中的“插入”按钮下方的三角形，选择“插入工作表列”命令，在“能量”列左侧插入一列。
(6) 选择“肉类”工作表，单击第1行的行标签选择第一行所有单元格。单击“开始”选项卡“剪贴板”组中的“复制”按钮，将第一行数据复制到剪贴板中。选择“食品营养成分”工作表中的A1单元格，单击“开始”选项卡“剪贴板”组中的“粘贴”按钮，将剪贴板中的标题行粘贴到从A1单元格开始的区域中。采用同样的方法将“肉类”工作表中的第2行到第8行数据复制到“食品营养成分”工作表中从A22单元格开始的区域中。
(7) 选择“食品营养成分”工作表中A1单元格，单击“开始”选项卡“编辑”组中的“查找和选择”按钮，选择“替换”命令。在“查找和替换”对话框“替换”选项卡的“查找内容”文本框中填入vitamin，在“替换为”文本框中填入“维生素”，如图5.12所示，单击“全部替换”按钮完成替换。单击“关闭”按钮关闭对话框。


图5.12“查找和替换”对话框


(8) 选择“食品营养成分”工作表中A1至J27单元格区域，单击“替换”命令。在“查找和替换”对话框“替换”标签的查找内容中不填写内容，在替换为文本框中填入0，单击“全部替换”按钮进行替换。采用同样方法将P列中的空单元格替换为“”。单击“关闭”按钮关闭对话框。
(9) 右击“蔬菜与水果”工作簿标签，在弹出的快捷菜单中选择“删除”选项，系统弹出“Microsoft Excel将永久删除此工作表”警告，单击“删除”按钮删除工作表。采用同样方法删除“肉类”工作表。
(10) 将文件另存为EF13.xlsx。

范例4： 设置单元格格式
打开“大学生体育测试评分标准.xlsx”工作簿，在“男生”工作表中按如图5.13所示完成以下表格格式设置，并将设置后的表格格式复制到“女生”工作表中。将工作簿保存为EF14.xlsx。★



图5.13“男生”工作表设置格式效果


(1) 对照图5.13将“男生”工作表第一行中的等级、项目得分、各测试项目等单元格设置合并单元格，合并后的单元格对齐方式为水平居中、垂直居中。
(2) 将第二行中所有年级单元格设置为自动换行。
(3) 设置第C列至N列之间的所有列的列宽为7。
(4) 将第一列中的评分等级相同的单元格合并，设置文本竖排显示，并在水平、垂直方向都居中对齐。
(5) 为标题行A1至N2区域设置填充颜色为“蓝色，个性色1”。
(6) 设置数据区域A1至N22中所有文字字体为宋体，11号，标题行A1至N2区域文字加粗，颜色为“白色，背景1”，其他文字为“黑色，文字1”。
(7) 为数据区域内所有单元格设置深蓝色细实线内部框线、深蓝色加粗实线外侧框线。
(8) 使用格式刷将“女生”工作表设置为与“男生”工作表相同的格式。
操作步骤： 
(1) 打开“大学生体育测试评分标准.xlsx”工作簿，选择“男生”工作表。
(2) 选择A1至A2单元格，单击“开始”选项卡“对齐方式”组中的“合并后居中”按钮，将A1、A2单元格合并为一个单元格并水平居中。选定A1单元格，单击“开始”选项卡“对齐方式”组中的“垂直居中”按钮，设置单元格内容垂直居中。采用同样的方法对B1与B2单元格、C1与D1单元格、E1与F1单元格、G1与H1单元格、I1与J1单元格、K1与L1单元格设置合并后居中，并设置垂直居中。
(3) 选择C2至N2单元格，单击“开始”选项卡“对齐方式”组中的“自动换行”按钮，设置单元格中文本超过单元格宽度时自动换行。
(4) 选择第C列至N列，单击“开始”选项卡“单元格”组“格式”按钮中的“列宽”命令，在“列宽”对话框中输入列宽7，单击“确定”按钮。
(5) 选择A3至A5单元格，单击“开始”选项卡“对齐方式”组右下角的对话框启动器，在“设置单元格格式”对话框“对齐”选项卡中选择“合并单元格”复选框，在“方向”选项中选择竖排文本，在“水平对齐”和“垂直对齐”下拉列表中都选择“居中”，如图5.14所示，单击“确定”按钮。采用相同的方法合并其他评分等级单元格并设置竖排文字和对齐方式。


图5.14“设置单元格格式”对话框“对齐”选项卡


(6） 选择A1至N2单元格区域，单击“开始”选项卡“字体”组中“填充颜色”按钮右侧的三角形，选择色板中“主题颜色”中的“蓝色、个性色1”，将标题行填充为蓝色。
(7) 选择A1至N22单元格区域，在“开始”选项卡“字体”组中设置字体为“宋体”，字号为11。选择A1至N2单元格区域，单击“开始”选项卡“字体”组中的“加粗”按钮，单击“字体颜色”按钮右侧的三角形，选择色板中“主题颜色”中的“白色、背景1”，设置标题行为白色加粗文字。选择A3至N22单元格区域，在“字体颜色”中选择色板中的“黑色，文字1”，设置数据为黑色。
(8) 选择A1至N22单元格区域，单击“开始”选项卡“字体”组“边框”按钮右侧的三角形，执行“其他边框”命令。在“设置单元格格式”对话框“边框”选项卡中选择颜色为标准色“深蓝”，在线条样式中选择细实线，单击“预置”中的“内部”按钮，设置内部框线。在线条样式中选择加粗实线，单击“预置”中的“外边框”按钮，设置加粗外部框线，如图5.15所示。单击“确定”按钮。
(9) 选择“男生”工作表A1至N22区域，单击“开始”选项卡“剪贴板”组中的“格式刷”按钮，复制已设置的男生体测评分标准表的格式。单击“女生”工作表，从A1单元格按下鼠标，拖动到N22单元格，将复制的格式应用到女生体测标准表中。



图5.15“设置单元格格式”对话框“边框”选项卡



(10) 将文件另存为EF14.xlsx。

范例5： 条件格式
1. 突出显示规则★
打开“电脑销售.xlsx”工作簿，在“商品价格”工作表中设置条件格式，将“进价”列中进价介于3500到4500之间(含3500、4500)的单元格中的进价采用标准色蓝色、加粗显示，如图5.16所示。将工作簿保存为EF151.xlsx。



图5.16突出显示进价在3500至4500单元格效果


操作步骤： 
(1) 打开“电脑销售.xlsx”工作簿。
(2) 选择“商品价格”工作表中的C2至C10单元格。单击“开始”选项卡“样式”组中的“条件格式”按钮，在列表中选择“突出显示单元格规则”中的“介于”。在“介于”对话框中设置介于的值为3500到4500，如图5.17所示。在“设置为”列表中选择“自定义格式”，在弹出的“设置单元格格式”对话框中设置字体颜色为标准色蓝色，字形为加粗，单击“确定”按钮返回“介于”对话框。继续单击“确定”按钮完成设置。



图5.17突出显示单元格格式“介于”对话框


(3) 将文件另存为EF151.xlsx。

2.  项目选取规则★★
打开“电脑销售.xlsx”工作簿，在“商品价格”工作表的单价列中设置条件格式，将单价前三的单元格设置为标准色红色、加粗文本，将单价最低的单元格设置为标准色绿色、倾斜文本，如图5.18所示。将工作簿保存为EF152.xlsx。



图5.18设置单价前三及单价最低单元格条件格式效果


操作步骤： 
(1) 打开“电脑销售.xlsx”工作簿。
(2) 选择“商品价格”工作表中的D2至D10单元格区域。单击“开始”选项卡“样式”组中的“条件格式”按钮，在列表中选择“项目选取规则”中的“前10项”。在“前10项”对话框中设置单元格数量为3，设置格式为“自定义格式”，在“设置单元格格式”对话框中设置字体颜色为标准色红色，字形加粗，如图5.19所示。单击“确定”按钮完成设置。



图5.19项目选取规则“前10项”对话框


(3) 选择“商品价格”工作表中的D2至D10单元格区域。单击“开始”选项卡“样式”组中的“条件格式”按钮，在列表中选择“项目选区规则”中的“最后10项”，在“最后10项”对话框中设置单元格数量为1，设置格式为“自定义格式”，在“设置单元格格式”对话框中设置字体颜色为标准色绿色，字形倾斜。单击“确定”按钮完成设置。
(4) 将文件另存为EF152.xlsx。

3.  数据条★★
打开“电脑销售.xlsx”工作簿，在“商品价格”工作表中的单价列中使用实心蓝色数据条展示各商品价格的对比情况，要求数据条最小值表示价格为3000元，数据条最大值表示价格为6500元，数据条效果如图5.20所示。将工作簿保存为EF153.xlsx。



图5.20单价实心蓝色数据条效果


操作步骤： 
(1) 打开“商品价格”工作簿。
(2) 选择“商品价格”工作表中的D2至D10单元格区域。单击“开始”选项卡“样式”组中的“条件格式”按钮，在列表中选择“数据条”中的“实心填充蓝色数据条”。
(3) 继续选择D2至D10单元格区域，单击“条件格式”按钮中的“管理规则”，打开“条件格式规则管理器”对话框，如图5.21所示。在规则列表中选择“数据条”，单击“编辑规则”按钮，打开“编辑格式规则”对话框。设置最小值类型为“数字”，值为3000，最大值类型为“数字”，值为6500，如图5.22所示。依次单击“确定”按钮，完成设置。



图5.21“条件格式规则管理器”对话框





图5.22“编辑格式规则”对话框


(4) 将文件另存为EF153.xlsx。

4. 与公式相关的条件格式★★★
打开“电脑销售.xlsx”工作簿，在“1月销售明细”工作表中设置条件格式，将“销量”列中销量大于等于对应销售目标的单元格设置为标准色蓝色、加粗显示，将销量低于对应销售目标80%的单元格设置为标准色红色、倾斜显示如图5.23所示。将工作簿保存为EF154.xlsx。



图5.23“销量”列条件格式设置结果


操作步骤： 
(1) 打开“商品价格”工作簿。
(2) 选择“1月销售明细”工作表中的E2至E28单元格。单击“开始”选项卡“样式”组中的“条件格式”按钮，在列表中选择“突出显示单元格规则”中的“其他规则”。在“新建格式规则”对话框中设置选择规则类型为“只为包含以下内容的单元格设置格式”。在编辑规则说明的下拉列表中设置“单元格值”“大于或等于”。单击条件后面输入框右侧的“折叠对话框”按钮，选择第一个销量单元格E2对应的销售目标D2单元格，系统自动填入公式“=$D$2”。单击“展开对话框”按钮展开对话框，删除公式中的$，将D2单元格的引用方式改为相对引用，如图5.24所示。单击“格式”按钮，在“设置单元格格式”对话框设置字体为标准色蓝色、加粗。单击“确定”按钮。


图5.24“新建格式规则”对话框


(3) 选择“1月销售明细”工作表中的E2至E28单元格。再次添加“突出显示单元格规则”中的“其他规则”。在“新建格式规则”对话框中设置选择规则类型为“只为包含以下内容的单元格设置格式”，规则为“单元格值”“小于”“=D2*0.8”，并设置字体格式为标准色红色、倾斜。单击“确定”按钮。
(4) 将文件另存为EF154.xlsx。

三、 实验练习
项目背景： 小明同学是班级学习委员，需要将班上同学们本课程的学习进度数据制作成表格保存，并对表格进行美化。
练习1： 新建一个Excel工作簿文件，在Sheet1工作表中输入如图5.25所示数据。将工作簿保存为E11.xlsx。★


图5.25学生学习进度数据


数据类型要求： 学号、姓名、性别、专业为文本型； 学习进度为分数，分母为一位数； 视频观看比例为百分比，保留一位小数； 最近访问日期为日期型，****年*月*日格式(不受操作系统影响)； 作业得分、测验得分为数值型，保留1位小数。
练习2： 打开“学习进度.xlsx”工作簿，在Sheet1工作表中设置“作业得分”及“测验得分”列中分数验证条件为小数，介于0到100分之间，如果输入分数超出范围，弹出标题为“分数输入错误”、样式为“停止”的对话框，显示“分数输入超出范围(0100)”出错信息，如图5.26所示。将工作簿另存为E12.xlsx。★



图5.26“分数输入错误”对话框


练习3：  打开“学习进度.xlsx”工作簿，在Sheet1工作表中设置“性别”列只能在列表中选择“男”或“女”，如图5.27所示。 将工作簿另存为E13.xlsx。★★



图5.27“性别”列的输入序列


练习4： 打开“学习进度.xlsx”工作簿，在Sheet1工作表中设置“视频观看比例”输入的数值应介于学习进度数值±0.2范围内，如果超出范围，弹出系统默认的出错提示对话框，如图5.28所示。将工作簿另存为E14.xlsx。★★★



图5.28“视频观看比例”超出范围的默认提示对话框


练习5： 打开“学习进度.xlsx”工作簿，完成以下编辑操作。将工作簿另存为E15.xlsx。★ 
(1) 将Sheet1工作表复制两份，放置在所有工作表的右侧，从左到右依次重命名为“计算机专业学习进度”“会计专业学习进度”。
(2) 在“计算机专业学习进度”工作表中删除会计专业学生数据，在“会计专业学习进度”工作表中删除计算机专业学生数据。
(3) 在“计算机专业学习进度”工作表左侧插入一张工作表，重命名为“计算机专业讨论情况”。
(4) 将“讨论成绩.xlsx”工作簿“讨论成绩”工作表中A1至C6单元格区域中的学号、姓名、讨论次数数据(包含标题行)复制到“计算机专业讨论情况”工作表从A1单元格开始的连续区域中。
(5) 在“计算机专业学习进度”工作表“作业得分”列左侧插入一列，将“计算机专业讨论情况”表中“讨论次数”列的数据复制到插入的空列中。
(6) 删除Sheet2工作表。
(7) 将工作簿另存为E15.xlsx。

练习6： 打开“学习进度.xlsx”工作簿，在Sheet1工作表中对照图5.29所示效果，完成以下格式设置操作。将工作簿另存为E16.xlsx★ 
(1) 在数据区域上方插入一行，在A1单元格输入“学习进度表”，设置字体为黑体、字号16。将A1到I1单元格区域合并并水平居中对齐。
(2) 设置A2到I12单元格区域中所有单元格字体为宋体，12号，水平居中，垂直居中。设置表格标题行字体加粗。
(3) 设置A2到I12单元格区域所有单元格边框为主题颜色“黑色，文字1”，数据区域内部为细实线边框，数据区域外侧为双线边框。
(4) 将数据区域标题行设置为标准色蓝色填充，标题行文字颜色为主题颜色“白色，背景1”，将数据行设置为标准色橙色填充，数据行文字为主题颜色“黑色，文字1”。
(5) 设置第2至12行行高为20。
(6) 完成所有操作后，将工作簿另存为E16.xlsx。



图5.29学习进度表格式设置效果



练习7： 打开“学习进度.xlsx”工作簿，将“测验得分”列中不及格的成绩设为红色加粗显示，如图5.30所示。将工作簿另存为E17.xlsx。★



图5.30红色加粗显示不及格成绩



练习8： 打开“学习进度.xlsx”工作簿，将“作业得分”列中前三名的单元格设置为“绿色填充深绿色文本”，在“视频观看比例”列中显示视频观看比例为绿色实心填充数据条，数据条填满表示1，数据条为空表示0，如图5.31所示。将工作簿另存为E18.xlsx。★★



图5.31设置项目选取规则及进度条效果



练习9： 打开“学习进度.xlsx”工作簿，将“测验得分”列中测验得分低于作业得分的成绩显示为标准色绿色、倾斜效果，如图5.32所示。将工作簿另存为E19.xlsx。★★★



图5.32绿色倾斜显示低于作业得分的测验得分



四、 实验思考
（1） 为什么Word表格里的单元格可以拆分，Excel工作表中单元格只有合并没有拆分？
（2） 如果设置A1单元格为数字型，保留2位小数，并输入数据12.3456，单元格中显示的数值和编辑栏中显示的数值分别是多少？如果A1单元格参与计算，会采用哪个值进行计算？
（3） 设置单元格数字类型为分数后，输入分数1/8，单元格中将显示什么值？编辑栏中又显示什么值？这说明分数在计算机中是如何存放的？
（4） 数据验证能否保证表格中的数据完全正确？
（5） 在设置数据验证时，出错警告信息有“停止、警告、信息”三种样式。这三种样式有哪些区别，Excel为什么要提供三种不同警告样式？
（6） 对一个单元格设置格式后，有哪些方法能快速将设置的格式应用到其他单元格中？
（7） Word表格和Excel在处理表格时各有哪些优势？什么样的表格你会选择用Word处理，什么样的表格你又会选择在Excel中处理？

实验项目二公式与函数
一、 实验目的

（1） 理解公式、运算符、函数的基本概念。
（2） 掌握公式的输入和编辑方法，能灵活运用公式对数据进行统计计算。
（3） 理解相对引用、绝对引用、混合引用的地址变化规则，能根据需要正确选择引用方式。
（4） 掌握常用函数的功能及用法，能灵活选择各种函数解决实际问题。

二、 实验范例
范例1： 基本公式与基本函数
1. 基本公式★
打开“电脑销售.xlsx”工作簿，完成以下计算。将工作簿保存为EF211.xlsx。
(1) 在“1月销售明细”工作表F2至F28单元格区域中计算各门店每种商品完成1月销售目标的百分比，计算结果用百分数形式显示，并在格式中设置保留一位小数。
(2) 在“商品价格”工作表E2至E10单元格区域中计算每种商品的利润。利润计算公式为： 利润=(单价进价)×30%。
(3) 在“商品价格”工作表F2至F10单元格区域中将每种商品的品名、型号和单价连接在一起显示，显示形式为“笔记本A单价 4200元”。
操作步骤： 
(1) 打开“电脑销售”工作簿。
(2) 选择“1月销售明细”工作表中的F2单元格，输入公式： =E2/D2，单击编辑栏左侧的“输入”按钮完成公式输入。选择F2单元格，在“开始”选项卡“数字”组中设置单元格类型为“百分比”，并设置小数位数为1位。双击或向下拖动F2单元格右下角的填充柄，将公式填充到F3至F28单元格，公式及计算结果如图5.33所示。



图5.33计算完成销售目标百分比


(3) 选择“商品价格”工作表中的E2单元格，输入公式： =(D2C2)*30%，单击“输入”按钮完成公式输入。双击E2单元格右下角的填充柄，将公式填充到E3至E10单元格。
(4) 选择“商品价格”工作表中的F2单元格，输入公式： =A2&B2&"单价"&D2&"元"，单击“输入”按钮确认。输入公式时，可以通过单击A2、B2、D2单元格，自动填入单元格地址。双击F2单元格的填充柄，将公式填充到F3至F10单元格。公式及计算结果如图5.34所示。



图5.34连接商品信息和单价


(5) 将文件另存为EF211.xlsx。

2. 基本函数★
打开“电脑销售.xlsx”工作簿，完成以下计算。将工作簿保存为EF212.xlsx。
(1) 在“上半年销售情况”工作表J2至J10单元格中计算上半年每件商品的总销量。
(2) 在“上半年销售情况”工作表K2至K10单元格中计算每件商品每月平均销量，使用INT函数将平均销量四舍五入保留一位小数。
(3) 在“上半年销售情况”工作表中，判断每种电脑是否完成上半年销售目标。若完成，在L列对应单元格中填写“完成”，否则填写“未完成”。
(4) 在“商品价格”工作表C14单元格中统计公司代理销售电脑的品种总数。
(5) 分别在“商品价格”工作表C15、C16单元格统计单价最高电脑和最低电脑的价格。
操作步骤： 
(1) 打开“电脑销售”工作簿。
(2) 选择“上半年销售情况”工作表中的J2单元格，单击“公式”选项卡“函数库”组中的“自动求和”按钮，系统自动填入求和函数公式： =SUM(C2:I2)。此时求和范围错误，删除公式中的求和区域“C2:I2”，使用鼠标拖动选择D2至I2单元格区域，重新选择求和范围，将公式改为： =SUM(D2:I2)，单击“输入”按钮确认。双击J2单元格右下角的填充柄，将公式填充复制到J3至J10单元格。
(3) 选择“上半年销售情况”工作表中的K2单元格，单击“公式”选项卡“函数库”组中的“自动求和”按钮右侧三角形，选择“平均值”命令，选择求值范围为D2:I2单元格区域，填入公式： =AVERAGE(D2:I2)，单击“输入”按钮得到商品的月平均销量。选定J2单元格，在编辑栏中将公式修改为： =INT(AVERAGE(D2:I2)*10+0.5)/10，将平均销量四舍五入保留1位小数，如图5.35所示。确认公式后，将公式填充复制到K3至K10单元格。



图5.35计算商品月平均销量并保留1位小数


(4) 选择“上半年销售情况”工作表中的L2单元格，单击“公式”选项卡“函数库”组中的“插入函数”按钮，在“插入函数”对话框中选择函数类别“逻辑”，选择函数IF，如图5.36所示，单击“确定”按钮。在“函数参数”对话框的测试条件中填入条件“J2>=C2”，在条件为真的返回值中填入“已完成”，条件为假的返回值中填入“未完成”，如图5.37所示，单击“确定”按钮。将公式填充复制到L3至L10单元格，得到每种电脑销售完成情况，如图5.38所示。



图5.36“插入函数”对话框






图5.37“函数参数”对话框




(5) 选择“商品价格”工作表的C14单元格，单击“公式”选项卡“函数库”组中的“自动求和”按钮右侧三角形，选择“计数”命令，设置计数范围为数值型单元格区域C2:C10，此时公式为： =COUNT(C2:C10)。单击“输入”按钮得到电脑的品种总数。



图5.38判断商品销售目标完成情况


(6) 选择“商品价格”工作表的C15单元格，单击“公式”选项卡“函数库”组中的“自动求和”按钮右侧三角形，选择“最大值”命令，设置求最大值范围为单元格区域D2:D10,填入公式： =MAX(D2:D10)。单击“输入”按钮得到单价最高电脑价格。采用同样方法在C16单元格输入公式： =MIN(D2:D10)，得到单价最低电脑的价格。
(7) 将文件另存为EF212.xlsx。

3. IF函数的嵌套★★
打开“电脑销售.xlsx”工作簿，在“1月销售明细”工作簿中根据销量和1月销售目标填写销售提成比例。提成规则如下： 超过销售目标的120%，销售提成比例为0.1； 完成销售目标但未超过销售目标的120%，销售提成比例为0.05； 未完成销售目标，销售提成比例为0。将工作簿保存为EF213.xlsx。
操作步骤： 
(1) 打开“电脑销售”工作簿。
(2) 选择“1月销售明细”工作表中的G2单元格，在编辑栏中输入公式： =IF(E2>D2*1.2,0.1,IF(E2>=D2,0.05,0))，单击“输入”按钮确认。
(3) 将公式向下填充复制到G28单元格。公式及计算结果如图5.39所示。



图5.39计算销售提成比例


(4) 将文件另存为EF212.xlsx。

范例2： 单元格引用
1. 绝对引用★
在“教师招聘考试成绩.xlsx”工作簿“招聘考试成绩”工作表中计算每位应聘者的综合成绩。综合成绩=笔试成绩*笔试成绩比例+面试成绩*面试成绩比例。将工作簿文件保存为EF221.xlsx。
操作步骤： 
(1) 打开“教师招聘考试成绩.xlsx”工作簿。
(2) 选择“招聘考试成绩”工作表中的H2单元格，输入公式： =F2*L1+G2*L2，计算第一位考生的综合成绩。
(3) 由于公式中笔试成绩比例和面试成绩比例对应的L1和L2单元格在公式复制时应保持原来位置不变，所以需要转换为绝对引用。选择H2单元格，在编辑栏中将光标依次定位到L1、L2单元格地址，按F4键，将两个单元格的引用方式设置为绝对引用。或者在编辑栏中，直接在L1、L2单元格地址的行号和列标前加入绝对引用符号“$”。单击“输入”按钮确认。
(4) 将公式向下填充复制到H43单元格。公式及计算结果如图5.40所示。



图5.40计算综合成绩


(5) 将文件另存为EF221.xlsx。

2. 混合引用★★
“存款.xlsx”工作簿“存款利率”工作表B3至E4单元格为某银行不同年限的存款利率。请在B5至E10单元格区域计算不同本金存1年、2年、3年、5年后的本利和。计算公式为： 本利和=本金*(1+存期年数*年利率)。将工作簿文件保存为EF222.xlsx。
操作步骤： 
(1) 打开“存款.xlsx”工作簿。
(2) 在“存款利率”工作表B5单元格中输入公式： =A5*(1+B3*B4)，计算100元存1年后的本利和。
(3) 分析B5单元格的公式，当公式向右移动时，公式中引用的A5单元格不向右移动，而B3、B4单元格要向右移动，所以A5单元格的列号应采用绝对引用，B3、B4单元格列号应采用相对引用。当公式向下移动时，公式中引用的A5单元格要随之向下移动，而B3、B4单元格不向下移动，所以A5单元格的行号应采用相对引用，B3、B4单元格行号应采用绝对引用。选择B5单元格，在编辑栏中修改公式中A5、B3、B4单元格的引用方式，修改后的公式为： =$A5*(1+B$3*B$4)， 单击“输入”按钮确认。
(4) 将B5单元格向下填充复制到B10单元格，再选择B5至B10单元格区域，向右填充复制到E5至E10单元格区域。公式及计算结果如图5.41所示。



图5.41定期存款到期本利和


(5) 将文件另存为EF222.xlsx。

3. 基本公式综合运用★★★
在“乘法表.xlsx”工作簿“下三角乘法表”工作表中完成下三角乘法表。将工作簿文件保存为EF223.xlsx。
操作步骤： 
(1) 打开“乘法表”工作簿。
(2) 在“下三角乘法表”工作表C3单元格中输入公式： =IF(B3>=C2,B3&"*"&C2&"="&B3*C2,"")。公式中使用IF函数判断两个乘数的大小，当B3单元格的乘数a大于等于C2单元格的乘数b时，在单元格中显示对应乘法算式，否则显示空单元格。
(3) 分析C3单元格的公式，当公式向下填充时，B3单元格中的乘数a要向下移动，C2单元格中的乘数b不动，所以B3单元格行号应采用相对引用，C2单元格行号应采用绝对引用，当公式向右填充时，B3单元格中的乘数a不动，C2单元格中的乘数b向右移动，所以B3单元格列号应采用绝对引用，C2单元格列号应采用相对引用。选定C3单元格，在编辑栏中将公式修改为： =IF($B3>=C$2,$B3&"*"&C$2&"="&$B3*C$2,"")，单击“输入”按钮确认。
(4) 将C3单元格向下填充复制到C11单元格，再选择C3至C11单元格区域，向右填充复制到K3至K11单元格区域。公式及计算结果如图5.42所示。




图5.42下三角乘法表


(5) 将文件另存为EF223.xlsx。

范例3： 常用函数
1. AND、OR函数★★
在“天气.xlsx”工作簿“9月天气预测”工作表中根据天气状况判断是否适宜晾晒和适宜出游，分别在G2至G16、H2至H16单元格中填写“适宜”或“不适宜”。适宜晾晒标准为： 天气为晴或多云，适宜出游标准为： 天气为晴并且最高气温低于32℃。将文件保存为EF231.xlsx。
操作步骤： 
(1) 打开“天气.xlsx”工作簿。
(2) 选择“9月天气预测”工作表中的G2单元格，在编辑栏中输入公式： =IF(OR(D2="晴",D2="多云"),"适宜","不适宜")，单击“输入”按钮确认。
(3) 选择“9月天气预测”工作表中的H2单元格，在编辑栏中输入公式： =IF(AND(D2="晴",B2<32),"适宜","不适宜")，单击“输入”按钮确认。
(4) 将G2、H2单元格分别向下填充复制到G16、H16单元格，计算结果如图5.43所示。



图5.43判断是否适宜晾晒及出游


(5) 将文件另存为EF231.xlsx。

2. RANDBETWEEN、RAND、RANK、MOD ★★★
在“随机数.xlsx”工作簿中利用随机数函数完成以下计算。将文件保存为EF232.xlsx。
(1) 在“随机验证码”工作表中生成20个由四位数字组成的随机验证码。验证码生成方法如下： 
① 在A2至D21单元格生成一位随机整数。
② 在E2至E21单元格将同一行的四个随机数字连接成验证码。
(2) 在“随机分组”工作表中将学生随机分成A、B两组，要求每组人数相等。分组方法如下： 
① 在第C列为每位学生生成一个随机小数。
② 在D列计算C列中的每个随机小数在所有随机数中的排名，使每个学生对应一个无重复的随机序列中的数字。
③ 在E列判断学生的随机数排名是奇数还是偶数，排名为奇数的同学分配A组，排名为偶数的同学分配B组。
操作步骤： 
(1) 打开“随机数.xlsx”工作簿。
(2) 选择“随机验证码”工作表A2单元格，输入公式： =RANDBETWEEN(0,9)，单击“输入”按钮确认，产生0至9之间的一个随机整数。将A2单元格公式向右复制填充到D2单元格，生成四个随机一位整数。
(3) 选择E2单元格，输入公式： =A2&B2&C2&D2，单击“输入”按钮确认，将四个随机数连接成四位验证码。
(4) 选择A2至E2单元格区域，向下填充复制到A21至E21单元格区域，生成20个随机验证码，如图5.44所示。




图5.44生成随机验证码


(5) 选择“随机分组”工作表C2单元格，输入公式： =RAND()，单击“输入”按钮确认，产生一个随机小数。将公式向下填充复制到C11单元格，为每位学生生成一个随机小数。
(6) 选择D2单元格，输入公式： =RANK.EQ(C2,$C$2:$C$11)，单击“输入”按钮确认，计算每位同学的随机数在所有学生中的排名。注意排名函数RANK.EQ中的排名数据范围“$C$2:$C$11”应采用绝对引用。将公式向下填充复制到D11单元格，得到每位学生的排名顺序。
(7) 选择E2单元格，输入公式： =IF(MOD(D2,2)=1,"A","B")，如果排名为奇数，将学生分配到A组，否则分配到B组。单击“输入”按钮确认。将公式向下填充复制到E11单元格，得到每位学生的分组号，如图5.45所示。



图5.45根据排名奇偶性分组


(8) 将文件另存为EF232.xlsx。

3. COUNTIFS、AVERAGEIFS 、ROUND★★
在“教师招聘考试成绩.xlsx”工作簿“招聘考试成绩”工作表中完成以下计算。将文件另存为EF233.xlsx。
(1) 在E36单元格计算应聘数学科目的男考生人数。
(2) 在E37单元格计算应聘一中语文老师的考生面试平均分，并使用ROUND函数四舍五入保留一位小数。
操作步骤： 
(1) 打开“教师招聘考试成绩.xlsx”工作簿。
(2) 选择“招聘考试成绩”工作表E36单元格，输入公式： =COUNTIFS(E2:E33,"数学",C2:C33,"男")，单击“输入”按钮确认，得到应聘数学科目的男考生人数。
(3) 选择E37单元格，输入公式： =ROUND(AVERAGEIFS(G2:G33,D2:D33,"一中",E2:E33,"语文"),1)，单击“输入”按钮确认，得到应聘一中语文老师的考生面试平均分，如图5.46所示。


图5.46应聘一中语文老师的考生面试平均分


(4) 将文件另存为EF233.xlsx。

4. LOOKUP函数 ★★★
打开“电脑销售.xlsx”工作簿，在“商品价格”工作表D18、E18单元格分别查找单价为4000元的电脑的品名和型号。将文件另存为EF234.xlsx。
操作步骤： 
(1) 打开“电脑销售.xlsx”工作簿。
(2) 单击“开始”选项卡“编辑”组“排序和筛选”中的“升序”命令，将商品价格表按单价的升序排序。
(3) 选择D18单元格，输入公式： =LOOKUP(4000,D2:D10,A2:A10)，单击“输入”按钮确认，查找单价4000元电脑对应的品名。
(4) 选择E18单元格，输入公式： =LOOKUP(4000,D2:D10,B2:B10)，单击“输入”按钮确认，查找单价4000元电脑对应的型号，查找结果如图5.47所示。



图5.47查找单价4000元电脑的品名和型号


(5) 将文件另存为EF234.xlsx。

5. VLOOKUP函数精确查找 ★★★
打开“电脑销售.xlsx”工作簿，在“1月销售明细”工作表H2至H28单元格中计算1月各店铺每种电脑的销售额。销售额=单价*销量，其中每种电脑单价需要根据电脑型号到“商品价格”工作表中查找。将工作簿保存为EF235.xlsx。
操作步骤： 
(1) 打开“电脑销售.xlsx”工作簿。
(2) 选择“1月销售明细”工作表H2单元格，输入公式： =VLOOKUP(C2,商品价格!$B$2:$D$10,3,FALSE)*E2。注意查找范围“商品价格!$B$2:$D$10”应采用绝对引用，查找方式参数为FALSE，表示精确查找。单击“输入”按钮确认。
(3) 将H2单元格向下填充复制到H28单元格，得到每种电脑的销售额，如图5.48所示。
(4) 将文件另存为EF235.xlsx。



图5.48计算每种电脑销售额



6. VLOOKUP函数非精确查找 ★★★
在“天气.xlsx”工作簿“8月天气”工作表中G2至G16单元格中根据当天风速填写风级，风速与风级对照关系在根据“风力等级划分”工作表中。将文件保存为EF236.xlsx。

操作步骤： 
(1) 打开“天气.xlsx”工作簿。
(2) 在“风力等级划分”工作表“风级”列左侧插入“风速起点”辅助列，输入每级风级对应的风速最低值，如图5.49所示。



图5.49插入风速起点辅助列


(3) 选择“8月天气”工作表G2单元格，输入公式： =VLOOKUP(F2,风力等级划分!$B$2:$C$14,2,TRUE)。注意查找范围“风力等级划分!$B$2:$C$14”应采用绝对引用，查找方式参数为TRUE，表示非精确查找。单击“输入”按钮确认。
(4) 将G2单元格向下填充复制到G16单元格，得到每天的风级，如图5.50所示。



图5.50根据每天风速计算风级


(5) 将文件另存为EF236.xlsx。

7. LEFT、MID、LEN、YEAR、TODAY函数★★★
在“职工工资.xlsx”工作簿“职工信息”工作表中完成以下计算。将文件保存为EF237.xlsx。
(1) 在E1、F1单元格分别输入“姓”“名”，使用公式将职工姓名拆分成姓和名两部分，将姓放在E2至E26单元格中，将名放在F2至F26单元格中。
(2) 在G1单元格分别输入“部门”，根据职工编号的第一位字母判断职工所属部门，结果放在G2至G26单元格中。部门详细信息在“部门对照”工作表中。
(3) 在H1单元格输入“工龄”，在H2至H16单元格根据每位职工入职日期计算每位职工工龄。工龄=当前系统日期的年份-出生日期的年份。
操作步骤： 
(1) 打开“职工工资.xlsx”工作簿。
(2) 选择“职工信息”工作表，在E1单元格输入“姓”。选择E2单元格，输入公式： =LEFT(B2,1)。单击“输入”按钮确认。
(3) 在F1单元格输入“名”。选择F2单元格，输入公式： =MID(B2,2,LEN(B2))。单击“输入”按钮确认。
(4) 选择E2至F2单元格区域，向下填充复制到E26至F26单元格区域，得到每位学生的姓和名，如图5.51所示。



图5.51拆分职工姓名


(5) 在G1单元格输入“部门”。选择G2单元格，输入公式： =VLOOKUP(LEFT(A2,1),部门对照!$A$2:$B$5,2,FALSE) 。单击“输入”按钮确认。
(6) 将G2单元格向下填充复制到G26单元格，得到每位职工的部门，如图5.52所示。



图5.52查找职工所在部门


(7) 在H1单元格输入“工龄”。选择H2单元格，输入公式： =YEAR(NOW())-YEAR(D2)。单击“输入”按钮确认。注意此时系统可能自动将H1单元格转换为日期型，需要选择H1单元格，将数字类型设置为常规型或数值型。
(8) 将H2单元格向下填充复制到H26单元格，得到每位职工的工龄，如图5.53所示。



图5.53计算职工工龄


(9) 将文件另存为EF237.xlsx。

三、 实验练习
项目背景： 小明同学是班级学习委员，需要对同学们本课程的学习进度、作业成绩、讨论成绩、测验等学习数据进行统计计算。
练习1： 打开“学习进度.xlsx”工作簿，在Sheet1工作表中完成如图5.54所示计算。将工作簿另存为E21.xlsx。★
(1) 在J1单元格输入“综合成绩”，在J2到J11单元格计算每位同学的综合成绩。计算公式为： 综合成绩=视频观看比例*100*20%+作业得分*30%+测验得分*50%。
(2) 在K2到K11单元格中生成每位同学的综合成绩通知文本，内容为“**专业**同学**分”。




图5.54计算综合成绩及通知文本效果



练习2： 打开“作业成绩.xlsx”工作簿，在“作业加权成绩”工作表中“作业成绩”列中计算每位同学作业成绩。作业成绩为每次作业成绩乘以对应权值后求和，要求引用H3:J3单元格区域中的作业权值进行计算。将工作簿另存为E22.xlsx，如图5.55所示。★



图5.55作业成绩计算结果



练习3： 课程开展小组学习活动，同学们自行组建学习小组，每个小组不超过三人，以小组为单位完成作品。作品按照等级评分，评分后再换算为具体分值。换算规则为： 各等级分别对应指定基础得分，小组成员根据参与作品的贡献排序，分别给予基础分的10%、5%、0%加分。
打开“小组活动.xlsx”工作簿，在“得分标准”工作表中计算不同等级作品各类型组员可以取得的实际成绩，如图5.56所示。要求在C4单元格中输入公式后，使用填充复制得到其他单元格成绩。将工作簿另存为E23.xlsx。★★



图5.56不同等级各类型组员可取得成绩

练习4： 打开“作业成绩.xlsx”工作簿，在“作业”工作表中使用函数完成以下计算，将工作簿另存为E24.xlsx。
(1) 在F2至H11单元格区域分别使用SUM、AVERAGE、COUNT函数计算每位同学作业总分、平均分、完成作业次数。
(2) 在C12至E12单元格区域计算每次作业的平均分，并使用INT函数将计算结果保留一位小数。
(3) 在I2至I11单元格区域根据作业完成次数判断作业状态。三次作业都完成的同学作业状态为“已完成”，有作业缺交的同学作业状态为“缺作业”。

练习5： 打开练习4完成的E24.xlsx工作簿，在“作业”工作表“作业提醒”列中根据作业状态及作业平均分填写作业提醒方式。作业提醒内容判断规则如下： 
 作业完成次数为3次且平均分及格的同学，作业提醒为空。
 3次作业都完成但平均分不及格的同学，作业提醒为“请注意作业质量”。
 缺作业但作业平均分及格的同学，作业提醒为“请按时提交作业”。
 缺作业且作业平均分不及格的同学，作业提醒为“请立即与老师联系”。
作业提醒判断过程如图5.57所示，填写结果如图5.58所示。完成后将工作簿另存为E25.xlsx。★★ 



图5.57作业提醒判断过程





图5.58作业成绩、状态及提醒计算结果



练习6： 打开“学习进度.xlsx”工作簿，在Sheet1工作表中根据学习进度、作业得分、测验得分填写学习评价及预警，填写结果如图5.59所示。将工作簿另存为E26.xlsx。★★
(1) 使用IF、AND函数，对学习进度超过1/2且测验及格的同学，在“学习评价”列中填写“合格”，否则填写“不合格”。
(2) 使用IF、OR函数，对作业不及格或测验不及格的同学，在“预警”列中填写“学习预警”。




图5.59学习评价及预警计算结果



练习7： 在课程进行中有两次测试，每次测试学生从课程试卷库中随机抽卷。课程试卷库中一共有80套试卷，前40套用于测验1，后40套用于测验2。
打开“测验.xlsx”工作簿，在“测验”工作表中使用随机函数分配学生两次测验抽卷的卷号。如图5.60所示。将工作簿另存为E27.xlsx。★★


图5.60随机分配测验卷号结果

(1) 在C2至C11单元格使用RAND函数生成“测验1”卷号，要求卷号范围在1~40之间(含1、40)。
(2) 在D2至D11单元格使用RANDBETWEEN函数生成“测验2”卷号，要求卷号为范围在41~80分之间的整数(含41、80)。

练习8： 打开“学习进度.xlsx”工作簿，在Sheet1工作表中完成以下计算。结果见图5.61。将工作簿另存为E28.xlsx。★★★
(1) 在J2至J11单元格使用RANK.EQ函数计算每位同学的测验排名。
(2) 在D14单元格使用COUNTIFS函数计算会计专业女生人数。
(3) 在D15单元格使用AVERAGEIFS函数计算计算机专业男生测验平均分。



图5.61测验排名及按条件统计结果



练习9： 打开“学习进度.xlsx”工作簿，在Sheet1工作表A18单元格输入“请在E18单元格输入学生姓名： ”，在E18单元格输入任意学生姓名，在F18单元格利用LOOKUP函数查找E18单元格学生的测验得分。结果见图5.62。将工作簿另存为E29.xlsx。★★★ 



图5.62查找指定学生测验得分



练习10： 打开“讨论成绩.xlsx”工作簿，在“讨论成绩”工作表中完成以下计算。结果见图5.63。将工作簿另存为E210.xlsx。★★★
(1) 使用VLOOKUP函数精确匹配，将每位同学在“讨论加分”工作表中的讨论加分填入“讨论成绩”表中。

(2) 使用VLOOKUP函数非精确匹配，根据“讨论成绩”工作表中的讨论次数计算每位同学的讨论基础得分，讨论次数与讨论基础得分对应关系在“讨论成绩标准”工作表中。(需要先修改“讨论成绩标准”表，使其符合非精确匹配要求)
(3) 计算讨论成绩。讨论成绩=讨论基础得分+讨论加分。



图5.63讨论成绩计算结果



练习11： 打开“学生信息.xlsx”工作簿，在“学生信息”工作表中使用MID、MOD、IF函数从身份证号码中提取学生性别、年龄，使用LEFT、VLOOKUP函数提取学生来源地，提取规则如下。完成后将工作簿另存为E211.xlsx。★★★
 性别： 身份证号的第17位表示性别，奇数为男，偶数为女。
 年龄： 身份证号的第7~11位是出生年份。年龄=当前系统日期年份-出生年份。
 来源地： 身份证号的第1~2位是对应省份，具体对应关系见“身份证来源省份对照”工作表。结果见图5.64。



图5.64从身份证号码提取个人信息结果



四、 实验思考
（1） 使用INT或ROUND函数保留2位小数和在格式中设置单元格为数值型并保留2位小数有什么区别？
（2） 使用RAND和RANDBETWEEN函数产生随机数时，每当单元格中数据发生改变，随机数将全部自动更新，与随机数所在单元格相关的公式计算结果也都随之改变。怎样能让随机数产生以后就不再改变？
（3） 要将一组顺序编号的学生平均分成两组，可以根据编号是奇数还是偶数分组，如果要将学生平均分成三组，你将采用什么方法？请试试写出分组的公式。
（4） 公式中可以引用当前工作表中的单元格，也可以引用当前工作簿中其他工作表中的单元格，那么公式中怎样引用其他工作簿中的单元格？引用的格式是什么？跨工作簿引用后，公式的结果能自动更新吗？
（5） VLOOKUP查找时，查找目标必须处于查找范围的第1列。如果查找目标在查找范围的第2列，返回数据在查找范围的第1列，例如在如图5.64所示的学生信息表中，要根据学生姓名查找对应学号，可以用什么办法处理？
实验项目三图表
一、 实验目的

（1） 了解图表的常用类型和作用。
（2） 掌握图表的创建方法，能根据需要选择合适的图表来表达数据。
（3） 掌握图表的编辑方法，能灵活修改图表的各种属性使图表更加形象直观。

二、 实验范例
范例1： 柱形图★
在“收支明细.xlsx”工作簿中绘制以下柱形图。将工作簿保存为EF31.xlsx。
(1) 在“收支”工作表内插入12个月每月总收入和总支出的三维簇状柱形图，横轴为月份，纵轴为每月总收入和总支出，图表标题为“每月收支情况”，如图5.65所示。



图5.65每月收支情况三维簇状柱形图


(2) 在“收支”工作表选择下半年兼职收入数据，建立二维簇状柱形图，横轴为月份，纵轴为每月兼职收入。图表放置在新工作表“下半年兼职收入”中，如图5.66所示。



图5.66下半年兼职收入二维簇状柱形图工作表


操作步骤： 
(1) 打开“收支明细.xlsx”工作簿。
(2) 选择“收支”工作表中的A1至A13单元格区域，按住Ctrl键，继续选择E1至E13、M1至M13单元格区域。单击“插入”选项卡“图表”组中的“插入柱形图或条形图”按钮，选择“三维簇状柱形图”类型，插入三维簇状柱形图。
(3) 单击图表标题，进入图表标题编辑状态，将图表标题改为“每月收支情况”。
(4) 选择“收支”工作表中的A1单元格，按住Ctrl键，依次选择A8至A13单元格区域、C1单元格、C8至C13单元格区域。单击“插入”选项卡“图表”组中的“插入柱形图或条形图”按钮，选择“二维簇状柱形图”类型，插入二维簇状柱形图。
(5) 选择生成的二维簇状柱形图，单击“设计”选项卡中的“移动图表”按钮，在“移动图表”对话框中设置图表放置位置为新工作表，并在新工作表选项后面填入工作表的名称“下半年兼职收入”，如图5.67所示。单击“确定”按钮。这里也可以在选定数据区域后直接按下F11键，快速生成独立二维簇状柱形图工作表。



图5.67“移动图表”对话框


(6) 将文件另存为EF31.xlsx。

范例2： 饼图★
在“收支明细.xlsx”工作簿“收支”工作表中绘制3月各项支出二维饼图。设置图例显示在饼图右侧，每个扇形区域外侧显示数据标签，标签内容为各项支出占总支出的百分比。设置图表标题为“3月支出分布”，如图5.68所示。将工作簿保存为EF32.xlsx。



图5.683月支出分布饼图 


操作步骤： 
(1) 打开“收支明细.xlsx”工作簿。
(2) 选择“收支”工作表中的A1单元格，按住Ctrl键，依次选择A4单元格、F1至L1、F4至L4单元格区域，单击“插入”选项卡“图表”组中的“插入饼图或圆环图”按钮，选择“二维饼图”类型，插入二维饼图。
(3) 单击图表标题，进入图表标题编辑状态，将图表标题改为“3月支出分布”。
(4) 单击饼图右上角的“图表元素”按钮，展开“图例”菜单，选择“右”，将图例靠右显示，如图5.69所示。



图5.69设置图例位置


(5) 单击饼图右上角的“图表元素”按钮，展开“数据标签”菜单，单击“更多选项”，在“设置数据标签格式”窗格中选择标签包括百分比，标签位置在数据标签外，如图5.70所示。



图5.70设置数据标签


(6) 将文件另存为EF32.xlsx。

范例3： 条形图★★
在“收支明细.xlsx”工作簿“收支”工作表中绘制下半年各项支出的二维堆积条形图，横轴为支出金额，纵轴为月份，各类支出在每月数据条内分段显示。设置图表标题为“下半年支出情况”。结果如图5.71所示。将工作簿保存为EF33.xlsx。



图5.71下半年支出情况条形图 


操作步骤： 
(1) 打开“收支明细.xlsx”工作簿。
(2) 选择“收支”工作表中的A1单元格，按住Ctrl键，依次选择A8至A13、F1至L1、F8至L13单元格区域，单击“插入”选项卡“图表”组中的“插入柱形图或条形图”按钮，选择“二维堆积条形图”类型，插入二维堆积条形图。
(3) 选择插入的图表，单击“设计”选项卡“数据”组中的“切换行/列”按钮，将月份作为纵轴，支出金额作为横轴。
(4) 单击图表标题，进入图表标题编辑状态，将图表标题改为“下半年支出情况”。
(5) 将文件另存为EF33.xlsx。

范例4： 折线图★★
在“部分城市气温及降水量.xlsx”工作簿“部分城市月平均气温”工作表中插入带数据标记的二维折线图，显示北京、上海、广州全年气温变化情况，图表标题为“北京、上海、广州全年气温变化趋势”，如图5.72所示。将工作簿保存为EF34.xlsx。



图5.72北京、上海、广州全年气温变化趋势折线图


操作步骤： 
(1) 打开“部分城市气温及降水量.xlsx”工作簿。
(2) 选择“部分城市月平均气温”工作表中的A1至M2区域，按住Ctrl键，依次选择A7至M7、A12至M12单元格区域。单击“插入”选项卡“图表”组中的“插入折线图或面积图”按钮，选择“带数据标记的折线图”类型，插入二维折线图。
(3) 单击图表标题，将图表标题改为“北京、上海、广州全年气温变化趋势”。
(4) 将文件另存为EF34.xlsx。

范例5： 组合图表★★★
在“部分城市气温及降水量.xlsx”工作簿“武汉全年气温降水量”工作表中绘制如图5.73所示组合图，其中对降水量数据绘制簇状柱形图，使用主坐标轴，对平均气温数据绘制折线图，使用次坐标轴，图表标题为“武汉全年气温及降水量”。将工作簿保存为EF35.xlsx。



图5.73武汉全年气温及降水量组合图 


操作步骤： 
(1) 打开“部分城市气温及降水量.xlsx”工作簿。
(2) 选择“武汉全年气温降水量”工作表中的A1至M3区域。单击“插入”选项卡“图表”组中的“推荐图表”按钮，在“插入图表”对话框中选择“所有图表”选项卡，在左侧图表类型中选择“组合”，在右边选择图表类型和轴选项中设置平均气温的图表类型为折线图，在次坐标轴显示。设置降水量图表类型为簇状柱形图，在主坐标轴显示，如图5.74 所示。单击“确定”按钮。



图5.74设置组合图表类型


(3) 单击图表标题，将图表标题改为“武汉全年气温及降水量”。
(4) 将文件另存为EF35.xlsx。

三、 实验练习
项目背景： 小明要撰写关于近年来我国社会经济发展的小论文，需要使用图表展示2016—2019年人均可支配收入及消费支出数据。
练习1： 在“人均可支配收入及消费支出.xlsx”工作簿中分别绘制以下图表。将工作簿保存为E31.xlsx。★
(1) 选取“人均可支配收入”工作表中的2016—2019年全国居民人均可支配收入、城镇居民人均可支配收入、农村居民人均可支配收入数据，绘制二维簇状柱形图，图表放置在新工作表中，将工作表命名为“人均可支配收入对比”，图表标题为“2016—2019年人均可支配收入对比”，如图5.75所示。



图5.752016—2019年人均可支配收入柱形图表工作表


(2) 在“人均消费支出”工作表中，选择2019年各项详细消费数据，绘制二维饼图，图表放置在“人均消费支出”工作表内，每个扇形区域外侧显示数据标签，包括数值、百分比，图例放置在图的右侧，图表标题为“2019年人均消费支出分布”，如图5.76所示。



图5.762019年人均消费支出分布饼图


练习2： 在“人均可支配收入及消费支出.xlsx”工作簿中分别绘制以下图表。将工作簿保存为E32.xlsx。★★
(1) 选取“人均可支配收入”工作表中的2016—2019年全国居民人均可支配收入、城镇居民人均可支配收入、农村居民人均可支配收入数据，在当前工作表内绘制带数据标记的折线图，在数据标记上方显示对应人均可支配收入数值，图例显示在图表下方，图表标题为“2016—2019年人均可支配收入增长情况”，图表效果如图5.77所示。



图5.772016—2019年全国居民人均可支配收入折线图


(2） 在“人均消费支出”工作表中，选择2016年至2019年各项详细消费数据，绘制堆积条形图，将图表放置在新工作表中，新工作表命名为“消费支出堆积条形图”，图表标题为“2016—2019年居民消费支出”，在图表样式中设置图表样式为“样式2”，条形图结果如图5.78所示。



图5.782016—2019年居民消费支出堆积条形图



练习3： 在“人均可支配收入及消费支出.xlsx”工作簿“人均可支配收入”工作表中选取2016—2019年全国居民人均可支配收入、比上年名义增长、扣除价格因素实际增长数据，在工作表内绘制组合图表。其中全国居民人均可支配收入放置在主坐标轴，使用二维簇状柱形图，比上年名义增长、扣除价格因素实际增长的百分比放置在次坐标轴，使用带数据标记的折线图。结果如图5.79所示。将工作簿保存为E33.xlsx。★★★



图5.792016—2019年全国居民人均可支配收入及增长率组合图表



四、 实验思考
（1） 图5.80是两个销售团队半年的业绩增长情况，哪个团队业绩增幅更高？这两个图对比说明了什么问题？



图5.80A、B团队上半年销售额柱形图


（2） 柱形图和条形图都是采用矩形表达数据，它们在表达数据上有哪些共同点和区别？举例说明什么情况使用柱形图，什么情况下使用条形图能更清晰的表达数据。
（3） 饼图能够表达数据整体与部分的关系，但只能选择一个数据系列，如果要在同一个图表中表达两个数据系列的整体和部分的关系，可以怎么处理？
（4） Excel中除了折线图可以表达数据变化趋势，还有一类散点图也能表达数据变化趋势。这两类图表看起来很相似，请试试分别绘制折线图和散点图，比较它们有哪些区别，分别适合表现什么样的数据？


实验项目四数据管理
一、 实验目的

（1） 掌握数据排序的基本方法，能熟练对数据进行单关键字、多关键字排序。
（2） 掌握在数据列表中建立筛选的方法，能熟练设置筛选条件筛选数据。
（3） 理解高级筛选中条件区域的构成规则，能熟练构建高级筛选条件，灵活运用高级筛选功能筛选数据。
（4） 掌握数据分类汇总的方法和原理，灵活运用分类汇总对数据进行分组统计和分级显示。
（5） 掌握数据透视表和数据透视图的使用方法，能利用数据透视表对数据进行多维度交互分析统计，并通过数据透视图展示分析结果。

二、 实验范例
范例1： 排序
1. 单关键字排序★
在“职工工资.xlsx”工作簿“职工信息”工作表中设置按职工编号升序排序。将工作簿保存为EF411.xlsx。
操作步骤： 
(1) 打开“职工工资”工作簿。
(2) 选择“职工信息”工作表“职工编号”字段中任意一个单元格，单击“开始”选项卡“编辑”组中的“排序和筛选”按钮，执行“升序”命令，按职工编号升序排序。
(3) 将文件另存为EF411.xlsx。

2. 多关键字排序★
在“职工工资.xlsx”工作簿“工资”工作表中设置按照部门升序，部门相同的按职务升序，职务也相同按实发工资降序排序。将工作簿保存为EF412.xlsx。
操作步骤： 
(1) 打开“职工工资”工作簿。
(2) 选择“工资”工作表中工资数据列表中的任意一个单元格，单击“数据”选项卡“排序和筛选”组中的“排序”按钮。在“排序”对话框中设置主要关键字为“部门”，次序为“升序”。单击“添加条件”按钮，设置次要关键字为“职务”，次序为“升序”。再单击“添加条件”按钮，设置次要关键字为“实发工资”，次序为“降序”，如图5.81所示。单击“确定”按钮。



图5.81设置排序条件


(3) 将文件另存为EF412.xlsx。

范例2： 筛选★
在“职工工资.xlsx”工作簿中完成以下筛选操作。将工作簿保存为EF42.xlsx。
(1) 在“职工信息”工作表中筛选出2010年至2015年(含2010、2015)之间入职的职工。
(2) 在“工资”工作表中筛选出市场部和技术部中实发工资大于6000元的职工。
操作步骤： 
(1) 打开“职工工资”工作簿。
(2) 选择“职工信息”工作表数据列表中的任意单元格，单击“数据”选项卡“排序和筛选”组中的“筛选”按钮，建立自动筛选。
(3) 单击“入职日期”字段名右侧的三角形，执行“日期筛选”菜单中的“介于”命令，在“自定义自动筛选方式”对话框中设置入职日期在“2010/1/1”与“2015/12/31”之间，如图5.82所示。单击“确定”按钮，显示2010年至2015年之间入职的员工，隐藏其他员工，如图5.83所示。



图5.82“自定义自动筛选方式”对话框




图5.83筛选2010年至2015年之间入职的员工


(4) 选择“工资”工作表数据列表中的任意单元格，单击“数据”选项卡“排序和筛选”组中的“筛选”按钮，建立自动筛选。
(5) 单击“部门”字段名右侧的三角形，在部门列表中取消“全选”复选框，选择“技术部”“市场部”复选框，如图5.84所示，单击“确定”按钮，只显示市场部和技术部的职工。



图5.84选择部分要显示的值


(6) 单击“实发工资”字段名右侧的三角形，执行“数字筛选”菜单中的“大于”命令，在“自定义自动筛选方式”对话框中设置实发工资大于6000，单击“确定”按钮，显示市场部和技术部实发工资大于6000元的职工，如图5.85所示。



图5.85筛选市场部和技术部实发工资大于6000元的职工


(7) 将文件另存为EF42.xlsx。

范例3： 高级筛选
1. “与”“或”条件筛选★
在“职工工资.xlsx”工作簿“工资”工作表中使用高级筛选选出实发工资大于7000元的二级职员和三级职员。筛选条件写在从A28开始的单元格区域，筛选结果复制到从A32开始的单元格区域。将工作簿保存为EF431.xlsx。
操作步骤： 
(1) 打开“职工工资”工作簿。
(2) 选择“工资”工作表，将工资表标题行复制到第28行，作为高级筛选条件的标题行。在D29、D30单元格分别输入“二级职员”“三级职员”，在K29、K30单元格分别输入“>7000”，建立高级筛选条件。
(3) 选择“工资”工作表数据区域中的任意单元格，单击“数据”选项卡“排序和筛选”组中的“高级筛选”命令，单击列表区域右侧的“折叠对话框”按钮，选择A1至K26单元格区域，单击“展开对话框”按钮，返回“高级筛选”对话框。单击条件区域右侧的“折叠对话框”按钮，选择A28至K30单元格区域，单击“展开对话框”按钮，返回“高级筛选”对话框。选择“将筛选结果复制到其他位置”单选按钮，设置复制到区域为A32开始的单元格，如图5.86所示。单击“确定”按钮，筛选条件及结果如图5.87所示。



图5.86“高级筛选”对话框





图5.87高级筛选实发工资大于7000元的二级职员和三级职员


(4) 将文件另存为EF431.xlsx。

2. 同一字段“与”条件筛选
在“职工工资.xlsx”工作簿“职工信息”工作表中使用高级筛选选出2010年入职的女职工。筛选条件写在从A28开始的单元格区域，筛选结果复制到从A32开始的单元格区域。将工作簿保存为EF432.xlsx。
操作步骤： 
(1) 打开“职工工资”工作簿。
(2) 选择“职工信息”工作表，将标题行复制到第28行，作为高级筛选条件的标题行。在C29单元格输入“女”。将D28单元格“入职日期”复制到E28单元格，分别在D29、E29单元格分别输入“>2010/1/1”“<2010/12/31”，建立高级筛选条件。
(3) 选择“职工工资”工作表数据列表中的任意单元格，单击“数据”选项卡“排序和筛选”组中的“高级筛选”命令，在“高级筛选”对话框中设置列表区域为A1至D26单元格区域，条件区域为A28至E29单元格区域，将筛选结果复制到A32开始的单元格区域。单击“确定”按钮，高级筛选条件及筛选结果如图5.88所示。



图5.88高级筛选2010年入职的女职工


(4) 将文件另存为EF432.xlsx。

范例4： 分类汇总
1. 一次分类汇总★
在“职工工资.xlsx”工作簿中完成以下分类汇总操作，其中所有排序均采用升序。将工作簿保存为EF441.xlsx。
(1) 在“职工信息”工作表中统计男女职工人数，要求对入职日期字段计数。
(2) 在“工资”工作表中统计各部门基本工资、绩效工资、实发工资的平均值。
操作步骤： 
(1) 打开“职工工资”工作簿。
(2) 选择“职工信息”工作表“性别”字段的任意单元格，单击“数据”选项卡“排序和筛选”组中的“升序”按钮，按性别升序排序。
(3) 选择“职工信息”数据列表中的任意一个单元格，单击“数据”选项卡“分级显示”组中的“分类汇总”按钮，在“分类汇总”对话框中设置分类字段为“性别”，汇总方式为“计数”，选定汇总项为“入职日期”，如图5.89所示。单击“确定”按钮，分类汇总结果如图5.90所示。



图5.89“分类汇总”对话框




图5.90按性别统计人数结果


(4) 选择“工资”工作表“部门”字段的任意单元格，单击“数据”选项卡“排序和筛选”组中的“升序”按钮，按部门升序排序。
(5) 选择“工资”数据列表中的任意一个单元格，单击“数据”选项卡“分级显示”组中的“分类汇总”按钮，在“分类汇总”对话框中设置分类字段为“部门”，汇总方式为“平均值”，选定汇总项为“基本工资”“绩效工资”“实发工资”，单击“确定”按钮。单击行标签左侧的分级按钮2，隐藏明细数据，分类汇总结果如图5.91所示。



图5.91按部门统计基本工资、绩效工资、实发工资平均值


(6) 将文件另存为EF441.xlsx。

2. 嵌套分类汇总
在“职工工资.xlsx”工作簿中完成以下分类汇总操作，其中所有排序均采用升序。将工作簿保存为EF442.xlsx。
(1) 在“职工信息”工作表中统计男女职工中最早入职和最晚入职的日期。要求先统计最早入职日期，再统计最晚入职日期。
(2) 在“工资”工作表中统计各部门男、女职工实发工资的平均值。
操作步骤： 
(1) 打开“职工工资”工作簿。
（2） 选择“职工信息”工作表“性别”字段的任意单元格，单击“数据”选项卡“排序和筛选”组中的“升序”按钮，按性别升序排序。
(3) 选择“职工信息”数据列表中的任意单元格，单击“数据”选项卡“分级显示”组中的“分类汇总”按钮，在“分类汇总”对话框中设置分类字段为“性别”，汇总方式为“最小值”，选定汇总项为“入职日期”。单击“确定”按钮，得到男女职工中的最早入职日期。
(4) 选择“职工信息”数据列表中的任意单元格，单击“数据”选项卡“分级显示”组中的“分类汇总”按钮，在“分类汇总”对话框中设置分类字段为“性别”，汇总方式为“最大值”，选定汇总项为“入职日期”，取消“替换当前分类汇总”复选框，如图5.92所示。单击“确定”按钮。单击行标签左侧的分级按钮3，隐藏明细数据。统计结果如图5.93所示。



图5.92设置嵌套的分类汇总



图5.93统计男女职工最早及最晚入职日期

(5) 选择“工资”数据列表中的任意单元格，单击“数据”选项卡中的“排序”按钮，在“排序”对话框中设置主要关键字为“部门”，次要关键字为“性别”，次序均为“升序”，单击“确定”按钮完成排序。
(6) 选择“工资”数据列表中的任意一个单元格，单击“数据”选项卡“分级显示”组中的“分类汇总”按钮，在“分类汇总”对话框中设置分类字段为“部门”，汇总方式为“平均值”，选定汇总项为“实发工资”。单击“确定”按钮，得到各部门的实发工资平均值。
(7) 继续选择“工资”数据列表中的任意一个单元格，单击“数据”选项卡“分级显示”组中的“分类汇总”按钮，在“分类汇总”对话框中设置分类字段为“性别”，汇总方式为“平均值”，选定汇总项为“实发工资”，取消“替换当前分类汇总”复选框。单击“确定”按钮，得到各部门中男女职工实发工资平均值。单击行标签左侧的分级按钮3，隐藏明细数据，分类汇总结果如图5.94所示。



图5.94统计各部门男女职工平均实发工资


(8) 将文件另存为EF442.xlsx。

范例5： 数据透视表
1. 基本数据透视表★
根据“职工工资.xlsx”工作簿“工资”工作表中的数据，插入以下数据透视表和数据透视图。将工作簿保存为EF451.xlsx。
(1) 将部门作为行标签，职务作为列标签，性别作为筛选器，显示各部门不同职务男职工的平均实发工资。数据透视表放置在新工作表中，工作表命名为“各部门男职工实发工资”。
(2) 在数据透视表所在的工作表中插入对应的数据透视图，数据图类型为二维簇状柱形图。
操作步骤： 
(1) 打开“职工工资”工作簿。
(2) 选择“工资”工作表中数据列表中的任意一个单元格，单击“插入”选项卡“表格”组中的“数据透视表”按钮，检查“创建数据透视表”对话框“表/区域”中填写的单元格区域“工资!$A$1:$K$26”是否包含工资数据列表中的所有单元格。设置数据透视表放置的位置为新工作表，如图5.95所示。
(3) 在插入的新工作表右侧“数据透视表字段”任务窗格中，将“部门”字段拖动到行区域中，将“职务”字段拖动到列区域中，将“性别”字段拖动到筛选器区域中，将“实发工资”字段拖动到值区域中。单击值区域中的“求和项： 实发工资”列表框，选择“值字段设置”，在“值字段设置”对话框中设置计算类型为“平均值”，如图5.96所示。单击“确定”按钮，“数据透视表字段”任务窗格如图5.97所示。


图5.95“创建数据透视表”对话框





图5.96“值字段设置”对话框




图5.97“数据透视表字段”任务窗格


(4) 单击数据透视表左上角“性别”筛选条件右侧的三角形筛选按钮，在列表中选择“男”，只显示男职工的统计结果。数据透视表及任务窗格如图5.98所示。



图5.98各部门不同职务男职工的平均实发工资


(5) 选择数据透视表中的任意一个单元格，单击“分析”选项卡“工具”组中的“数据透视图”按钮，在“插入图表”对话框中选择“簇状柱形图”，单击“确定”按钮，根据数据透视表数据插入数据透视图，如图5.99所示。



图5.99各部门不同职务男职工的平均实发工资数据透视图


(6) 将当前工作表重命名为“各部门男职工实发工资”。
(7) 将文件另存为EF451.xlsx。

2. 数据透视表切片★★
根据“职工工资.xlsx”工作簿中的数据，插入以下数据透视表。将工作簿保存为EF452.xlsx。
(1) 根据“工资”工作表中的数据插入数据透视表，统计各部门不同级别职工的平均绩效工资，其中部门为行标签，职务为列标签，数据透视表所在工作表名为“女职工绩效”。在数据透视表中插入“性别”切片器，利用切片器在数据透视表中显示各部门不同级别女职工的平均绩效工资。


图5.100“插入切片器”对话框

(2) 根据“工资”工作表中的数据插入数据透视表，统计各部门不同级别职工的人数，其中部门为行标签，职务为列标签，计数项为姓名，数据透视表所在工作表名为“技术部二、三级职员人数”。在数据透视表中插入“部门”和“职务”切片器，利用切片器在数据透视表中显示技术部二级职员和三级职员的人数。
操作步骤： 
(1) 打开“职工工资”工作簿。
(2) 选择“工资”工作表中数据列表中的任意一个单元格，单击“插入”选项卡“表格”组中的“数据透视表”按钮，在新工作表中插入数据透视表。
(3) 在插入的工作表右侧“数据透视表字段”任务窗格中，将“部门”字段拖动到行区域中，将“职务”字段拖动到列区域中，将“绩效工资”字段拖动到值区域中。单击值区域中的“求和项： 绩效工资”列表框，选择“值字段设置”，在“值字段设置”对话框中设置计算类型为“平均值”。
(4) 选择插入的数据透视表中的任意一个单元格，单击“分析”选项卡“筛选”组中的“插入切片器”按钮，在“插入切片器”对话框选中“性别”复选框，如图5.100所示，单击“确定”按钮。
(5) 在切片器中单击“女”按钮，只显示女职工的统计情况，如图5.101所示。



图5.101使用“性别”切片器显示各部门不同级别女职工的平均绩效工资


(6) 将数据透视表所在工作表重命名为“女职工绩效”。
(7) 采用同样方法根据“工资”工作表中的数据插入数据透视表，设置部门为行标签，职务为列标签，计数项为姓名，计算方式为计数。将数据透视表所在工作表重命名为“技术部二、三级职员人数”。
(8) 选择数据透视表中的任意单元格，单击“分析”选项卡“筛选”组中的“插入切片器”按钮，在“插入切片器”对话框中选择“部门”“职务”字段左侧的复选框，插入两个切片器。
(9) 在“部门”切片器中选择“技术部”，单击“职务”切片器右上角的“多选”按钮，选择“二级职员”和“三级职员”，数据透视表中只显示技术部二级职员和三级职员的人数，如图5.102所示。



图5.102使用切片器查看技术部二级、

三级职员人数

(10) 将文件另存为EF452.xlsx。

3. 数据透视表分组
根据“职工工资.xlsx”工作簿中的数据，插入以下数据透视表。将工作簿保存为EF453.xlsx。
(1) 根据“职工信息”工作表中的数据插入数据透视表，统计每年入职的职工人数。入职日期为行标签，计数项为姓名。数据透视表放置在新工作表“入职年份分组”中。
(2) 根据“工资”工作表中的数据插入数据透视表，统计各部门职员和主管的平均基本工资。其中职务为行标签，部门为列标签。选择一级职员、二级职员、三级职员创建数据组，命名为“职员”，在数据组中显示分类汇总结果。数据透视表放置在新工作表“职务分组”中。
(3) 根据“工资”工作表中的数据插入数据透视表，统计实发工资在6000元以下、6000至7999元、8000至9999元、10000元以上各档中不同职务的职工人数。其中实发工资分组为行标签，职务为列标签，计数项为姓名。数据透视表放置在新工作表“工资分组”中。
操作步骤： 
(1) 打开“职工工资”工作簿。
(2) 根据“职工信息”工作表中的数据插入数据透视表。在“数据透视表字段”任务窗格中将“入职日期”字段拖动到行区域，系统自动添加“年”“季度”字段到行区域，并在数据透视表的行标签中显示“年”分组。将“姓名”字段拖动到值区域，设置计算方式为计数，得到每年入职职工人数。将数据透视表所在工作表重命名为“入职年份分组”，分组的数据透视表如图5.103所示。


图5.103每年入职职工人数


(3) 根据“工资”工作表中的数据插入数据透视表。在“数据透视表字段”任务窗格中将“职务”字段拖动到行区域，将“部门”字段拖动到列区域，将“基本工资”字段拖动到“值”区域，设置计算方式为平均值。选择数据透视表中的A5至A7单元格，单击“分析”选项卡“分组”组中的“组选择”按钮，为所有职员创建数据组。选择A5单元格，在编辑栏中将分组标题改为“职员”，分组后的数据透视图如图5.104所示。依次双击A5、A9单元格，可以只显示各组的统计数据。将数据透视表所在工作表重命名为“职务分组”。



图5.104插入“职员”分组的各部门不同职务职工平均基本工资数据透视表


(4) 根据“工资”工作表中的数据插入数据透视表。在“数据透视表字段”任务窗格中将“实发工资”字段拖动到行区域，将“职务”字段拖动到列区域，将“姓名”字段拖动到“值”区域，设置计算方式为计数。选择行标签中的任意一个单元格，单击“分析”选项卡“分组”组中的“组选择”按钮，在“组合”对话框中设置数值起始于6000，终止于9999，步长为2000，如图5.105所示。单击“确定”按钮，得到每段实发工资范围中的各类职员人数，如图5.106所示。将数据透视表所在工作表重命名为“工资分组”。



图5.105“组合”对话框




图5.106分段统计各实发工资范围内的各类职员人数


(5) 将文件另存为EF453.xlsx。

三、 实验练习
项目背景： 小明到某奶茶店实习，需要分析店铺每个月奶茶等饮料营业数据。
练习1： 在“奶茶店营业数据.xlsx”工作簿中，完成以下排序操作。将工作簿保存为E41.xlsx。★
(1) 将“价格”表中所有饮品按价格降序排序。
(2) 将“营业数据”表中的数据依次按类别升序，类别相同的按销售数量降序、销售数量也相同的按销售额降序排序。

练习2： 在“奶茶店营业数据.xlsx”工作簿“营业数据”表中建立筛选，筛选出打包鲜榨果汁的销售情况，按销售额降序排序显示。将工作簿保存为E42.xlsx。★

练习3： 在“奶茶店营业数据.xlsx”工作簿“营业数据”表中建立高级筛选，筛选出销售类别为打包、销售额大于200元或销售类别为外卖、销售额大于150元的销售数据。要求高级筛选条件写在从A40单元格开始的数据区域中，筛选结果复制到A45单元格开始的区域中。将工作簿保存为E43.xlsx。★

练习4： 在“奶茶店营业数据.xlsx”工作簿“营业数据”表中建立高级筛选，筛选出奶茶中销售数量在10~20杯之间(含10、20)的销售数据。要求高级筛选条件写在从A40单元格开始的数据区域中，筛选结果复制到A45单元格开始的区域中。将工作簿保存为E44.xlsx。★★

练习5： 对“奶茶店营业数据.xlsx”工作簿“营业数据”表中的数据进行分类汇总。要求： 按品名升序排序，统计每种饮料的总销量和总销售额。将工作簿保存为E45.xlsx。★

练习6： 对“奶茶店营业数据.xlsx”工作簿“营业数据”表中的数据进行分类汇总。要求： 按产品类别升序、销售类别升序排序，统计每类饮料不同销售类别下的销售总额。将工作簿保存为E46.xlsx。★★

练习7： 对“奶茶店营业数据.xlsx”工作簿“营业数据”表中的数据建立数据透视表，以饮品类别为行标签，销售类别为列标签，统计各种饮品不同销售类别的总销售额。数据透视表创建在新工作表中，命名为“销售额统计”。根据数据透视表结果，在“销售额统计”工作表中创建数据透视图，图表类型为二维簇状柱形图。将工作簿保存为E47.xlsx。★ 

练习8： 对“奶茶店营业数据.xlsx”工作簿“营业数据”表中的数据建立数据透视表，以饮品品名为行标签，销售类别为列标签，统计各种饮品不同销售类别的总销售额。数据透视表创建在新工作表中，命名为“各类饮品销售额分析”。在数据透视表中插入“类别”切片器，在“类别”切片器中选择咖啡和奶茶，只显示各种咖啡和奶茶的销售总额，如图5.107所示。将工作簿保存为E48.xlsx。★★



图5.107各种咖啡和奶茶销售总额数据透视表


练习9： 对“奶茶店营业数据.xlsx”工作簿“营业数据”表中的数据建立数据透视表。统计各种销售类别订单中，销售数量在1~10、11~20、21~30、31~40的订单数量。其中行标签是销售数量分组，列标签是销售类别，计数项是品名，如图5.108所示。数据透视表创建在新工作表中，命名为“订单分组统计”。将工作簿保存为E49.xlsx。★★★




图5.108各种销售类别订单销售数量分组统计



四、 实验思考
（1） 在对“职工工资.xlsx”工作簿“工资”工作表职务字段进行升序排序时，排序结果是“二级职员、三级职员、一级职员、主管”。为什么是这样的排列次序？如果希望排序顺序按照职员的实际级别次序“一级职员、二级职员、三级职员、主管”，应该怎样操作？
（2） 举例说明在什么情况下只能使用高级筛选，不能使用自动筛选。
（3） 分类汇总后，如果要对分类字段或汇总项字段排序，需要先单击分级按钮隐藏详细数据，如果要对其他字段进行排序，必须先删除分类汇总。想一想为什么系统要采用这样一种机制？
（4） 分类汇总和数据透视表都能对数据进行分类统计，你更喜欢使用哪种统计方法？为什么？