第3章
电子表格处理



实验31工作表的编辑与格式化
一、 实验目的
(1) 掌握工作表的创建和保存方法。
(2) 掌握数据的导入和输入方法。
(3) 掌握单元格的格式设置方法。
二、 实验示例
以下任务需要使用素材“某公司销售额统计数据.txt”。
【任务1】新建工作表，导入素材文本文件中的全部数据。
【操作步骤】
(1) 新建空白工作簿文件，将文档命名为“销售额统计表.xlsx”。
(2) 导入素材数据。
① 设置Sheet1为活动工作表，单击“数据”选项卡“获取外部数据”功能组中的“自文本”(见图31)，选择需导入的文件路径，单击“导入”按钮。


图31“数据”选项卡“获取外部数据”功能组


② 在弹出的“文本导入向导”对话框中根据提示进行设置(见图32)，单击“完成”按钮。


图32文本导入向导设置


③ 在Sheet1标签上右击，在弹出的快捷菜单上选择“重命名”，输入新表名为“各分公司销售统计表”(见图33)。


图33工作表重命名


【任务2】插入批注。
【要求】为“南部分公司”单元格添加批注，内容为“新成立”。
【操作步骤】
(1) 选中“南部分公司”单元格并右击，在弹出的快捷菜单中选择“插入批注”。
(2) 输入批注内容时，先将默认出现在批注中的文字删除后再输入，如图34所示。




图34批注设置


【任务3】设置单元格格式。
【要求】
(1) 在“各分公司销售统计表”第1行前插入新行。在A1单元格中输入“某公司产品销售额统计表”。
(2) 合并A1:E1单元格； 将表中全部数据字体设置为黑体、14号，对齐方式设置为水平居中和垂直居中； 将表中全部数值内容设置为保留小数点后两位，添加人民币符号； 为表格添加粗实线外边框、双实线内边框； 将A1:E1单元格填充为“茶色，背景2”。
(3) 将A1:E6单元格设置为“自动调整列宽”。
【操作步骤】
(1) 插入新行并输入表格标题。
在第1行任意单元格上右击，在快捷菜单中选择“插入”，在打开的“插入”对话框中选择“整行”，如图35所示。在A1单元格中输入标题文字。
 提示： 插入新行的另一种方法是，在行号1上右击，在快捷菜单中选择“插入”。

(2) 设置单元格格式。
① 选中A1:E1单元格，右击，在快捷菜单中选择“设置单元格格式”。在“设置单元格格式”对话框的“对齐”选项卡中的“文本控制”中勾选“合并单元格”(见图36)，在“填充”选项卡中的“背景色”中选择“茶色，背景2”。



图35“插入”对话框




图36合并单元格选项



 提示： 在设置背景色时，当鼠标悬浮在“背景色”色块上时，并不显示该色块对应的颜色名称，只能通过“图案颜色”下拉菜单中的色块(见图37)来查看对应位置的色块颜色名称。


图37设置填充色菜单

 提示： 在“设置单元格格式”对话框的“对齐”选项卡下，设置“水平对齐”方式为“跨列居中”，也可实现类似合并单元格的视觉效果。

② 选中A1:E6单元格，在“设置单元格格式”对话框的“字体”“对齐”和“边框”选项卡下分别按要求设置字体、字号、对齐方式和内外边框。
③ 选中B3:E6单元格，在“设置单元格格式”对话框的“数字”选项卡“分类”中的“数值”项设置小数位数和货币符号，如图38所示。
(3) 设置“自动调整列宽”。
选中A1:E6单元格，选择“开始”选项卡→“单元格”组→“格式”→“自动调整列宽”，如图39所示。


图38数值内容设置




图39自动调整列宽


【任务4】设置条件格式。
【要求】将销售额低于100000元的单元格设置为“浅红色填充”； 将销售额高于400000元的单元格的字体颜色设置为“蓝色”，填充背景色设置为“黄色”。
【操作步骤】
(1) 选中B3:E6单元格，选择“开始”→“样式”组→“条件格式”→“突出显示单元格规则”→“小于”，在弹出的“小于”对话框中设置数值低于100000的单元格格式为“浅红色填充”，如图310所示。


图310条件格式的设置流程


(2) 重复上述操作步骤，在“大于”对话框中设置数值高于400000的单元格的“自定义格式”为字体颜色“蓝色”，填充背景色为“黄色”。设置完成后的效果如图311所示。


图311实验31完成效果图


三、 实验作业
本实验作业在素材“作业文档1.xlsx”中完成。
(1) 自动调整“销售情况表”表数据区域的列宽、行高。
(2) 将第1行的行高设置为第2行行高的2倍(28.5)。
(3) 设置数据区域各单元格内容水平居中和垂直居中。
(4) 更改标题“某公司销售情况表格”的字体为黑体，字号为16号。
(5) 将数据区域套用表格格式“表样式中等深浅27”，数据区域包含标题。
实验32公式与函数
一、 实验目的
(1) 掌握公式的使用方法。
(2) 掌握函数的使用方法。
(3) 掌握单元格地址的相对引用、绝对引用和混合引用。
二、 实验示例
1. 以下任务需要使用素材“图书销售情况统计表.xlsx”，原始素材数据如图312所示。


图312图书销售情况统计表原始素材数据


【任务】使用公式计算图书“销售额”。
【操作步骤】
(1) 在F2单元格内输入“=D2*E2”,按Enter键结束，如图313所示。


图313“销售额”计算完成效果


(2) 将光标置于F2单元格右下角，当光标图案变为填充柄“╋”时，按住鼠标左键将公式向下填充至F14单元格。任务完成效果如图313所示。
2. 以下任务需使用素材“学生成绩分析表.xlsx”，原始素材数据如图314所示。


图314学生成绩分析表原始素材数据


【任务1】使用函数计算“总分”“平均分”“最高分”和“最低分”。
【操作步骤】
(1) 计算“总分”。
① 选中F3单元格，单击编辑栏左侧的“插入函数”按钮

(见图315)，在弹出的“插入函数”对话框中选择函数SUM，单击“确定”按钮，在之后打开的“函数参数”对话框中设置函数参数为B3:E3，单击“确定”按钮，如图316所示。上述步骤设置完成后F3单元格编辑栏中自动出现函数完整表达式，如图317所示。


图315“插入函数”按钮




图316插入函数操作流程




图316（续）


 提示： 也可以选择“公式”→“函数库”组→“插入函数”，打开“插入函数”对话框。
 提示： 设置函数参数的方式有两种： 通过拖动鼠标选择连续单元格区域，另外也可以手动输入连续单元格区域。手动输入函数时系统也会自动给出输入提示信息，如图318所示。



图317F3单元格函数计算表达式




图318手动输出函数时的系统提示信息




② 将光标置于F3单元格右下角，当光标图案变为填充柄“╋”时,按住鼠标左键将函数向下填充至F13单元格。

(2) 计算“平均分”。
使用AVERAGE函数，参照“总分”操作步骤完成全部学生平均分的函数填充。
(3) 计算“最高分”。
使用MAX函数，在B14单元格上重复步骤(1)中类似操作，完成全部科目最高分的函数填充。注意： 利用B14单元格右下角填充柄“╋”向右填充至E14单元格。


图319自动求和按钮功能



(4) 计算“最低分”。
使用MIN函数，参照“最高分”操作步骤完成全部科目最低分的函数填充。
 提示： 任务1也可以通过单击“开始”选项卡“编辑”组中的“自动求和”按钮，完成4个函数的计算，如图319所示。

【任务2】使用函数计算“等级”和“名次”。
【要求】
(1) 使用IF函数计算“等级”，总分大于360分等级为“优秀”，总分小于或等于260分等级为不合格，其余数值范围等级为“合格”。注意IF函数的嵌套用法。
(2) 使用RANK函数计算“名次”，注意单元格地址的绝对引用方法。
(3) 使用公式和函数计算大学物理科目的及格与不及格的“人数”“比例”和“平均分”，计算“比例”时需要使用L7单元格中的总人数值。注意函数和公式的混合使用方法。
【操作步骤】
(1) 计算“等级”。
在H3单元格内输入如图320所示的函数计算表达式，按Enter键结束。使用H3单元格右下角填充柄将函数填充至H13单元格。


图320H3单元格函数计算表达式


(2) 计算“名次”。
在I3单元格内输入如图321所示的函数计算表达式，按Enter键结束。使用I3单元格右下角填充柄将函数填充至I13单元格。注意： “$”符号既可以手动输入，也可以使用F4键切换，具体操作方法参照配套教材相关章节，此处不再赘述。


图321I3单元格函数计算表达式


 提示： 向下填充函数时，函数参数中的“行号”相对地址会自动递增。用来统计名次的总分范围是固定的，属于绝对地址引用，所以总分“行号”地址前必须添加“$”符号。

(3) 计算大学物理科目的“及格”和“不及格”人数与比例。
① 在L2单元格内输入函数“=COUNTIF(E3:E13,">=60")”。
② 在M2单元格内输入函数“=COUNTIF(E3:E13,"<60")”。
③ 在L3单元格内输入函数“=L2/$L7”，将函数向右拖动填充至M3单元格。
 提示： 向右填充函数时，函数参数中的“列标”相对地址会自动递增。用来计算人数比例的总人数是固定内容，属于绝对引用，所以总人数单元格“列标”地址前必须添加“$”符号。

④ 在L4单元格内输入函数“=SUMIF(E3:E13,">=60",E3:E13)/L2”。
⑤ 在M4单元格内输入函数“=SUMIF(E3:E13,"<60",E3:E13)/M2”，任务完成效果如图322所示。


图322“学生成绩分析表”完成效果图


3. 以下任务需要使用素材“期末成绩统计表.xlsx”，共包含“期末成绩统计表”和“姓名对照表”两张工作表。原始素材数据如图323所示。


图323期末成绩统计表原始素材数据


【任务1】查找学号对应的姓名。

查找学号对应
姓名

【要求】在“学号”列右侧插入一个新列，列标题是“姓名”； 利用VLOOKUP函数从“姓名对照表”里查找相应学号对应的姓名填入“姓名”列中。
【操作步骤】
(1) 选中B1单元格，右击，在弹出的快捷菜单中选择“插入”。选择“插入”对话框中的“整列”选项按钮。将新插入列标题设置为“姓名”。
(2) 选中B2单元格，在编辑栏中输入函数“=VLOOKUP(A2,姓名对照表!$A$2:$B$15,2,FALSE)”。
(3) 将B2单元格函数利用填充柄向下填充至B15单元格，填充后效果如图324所示。


图324“姓名”查找完成效果



根据学号计算学生所在班级

【任务2】根据学号计算学生所在班级。
【要求】在“大学语文”列左侧插入一个新列，列标题是“班级”； “学号”中第4个字符即是班级，利用MID函数截取“学号”中的第4个字符。
【操作步骤】
(1) 选中C1单元格并右击，在弹出的快捷菜单中选择“插入”。选择“插入”对话框中的“整列”选项按钮。将新插入列标题设置为“班级”。
(2) 选中C2单元格，在编辑栏中输入函数“=MID(A2,4,1)&"班"”，如图325所示，按Enter键结束。


图325MID函数计算表达式


(3) 将C2单元格函数利用填充柄向下填充至C15单元格，班级显示效果如图326所示。


图326“班级”计算完成效果

三、 实验作业
本实验作业在素材“作业文档2.xlsx”中完成。
(1) 在“销售情况表”表“咨询商品编码”与“预购类型”之间插入新列，列标题为“商品单价”，利用公式将工作表“商品单价”中对应的价格填入该列。
(2) 在“销售情况表”表“成交数量”与“销售经理”之间插入新列，列标题为“成交金额”，根据“成交数量”和“商品单价”利用公式计算并填入“成交金额”。
(3) 打开“月统计表”工作表，利用公式计算每位销售经理每月的成交金额。
(4) 计算“月统计表”工作表中的“总和”列和“总计”行。
实验33数 据 图 表
一、 实验目的
(1) 掌握图表的创建方法。
(2) 掌握图表的编辑方法。
二、 实验示例
以下任务需使用素材“销售额统计表.xlsx”，原始素材数据如图327所示。


图327实验33原始素材数据


【任务1】建立簇状柱形图，完成效果如图328所示。


图328实验33任务1完成效果图


【要求】以A1:E5单元格区域为数据源，建立簇状柱形图； 添加图表标题“各分公司销售额统计图”，添加数据标签，添加横向次要网格线； 切换数据源中的“行/列”系列； 将图表对象放置在A8:K30单元格区域内。
【操作步骤】
(1) 建立“簇状柱形图”。
选中A1:E5单元格，选择“插入”→“图表”组→“柱形图”→“簇状柱形图”命令(见图329)。


图329“插入”选项卡“图表”组


(2) 添加图表标题。
选中图表对象，选择“图表工具|布局”→“标签”组→“图表标题”(见图330)→“图表上方”命令，将默认的图表标题修改为“各分公司销售额统计图”。


图330“图表工具|布局”选项卡


(3) 添加数据标签。
选中图表对象，选择“图表工具|布局”→“标签”组→“数据标签”→“数据标签外”命令。
(4) 添加横向次要网格线。
选中图表对象，选择“图表工具|布局”→“坐标轴”组→“网格线”→“主要横网格线”→“次要网格线”命令。图表完成效果如图331所示。


图331实验33任务1前（4）步骤完成效果图


(5) 切换“行/列”系列。
在图表对象上右击，选择“选择数据”。在弹出的“选择数据源”对话框中单击“切换行/列”按钮，切换行列前后对比效果如图332所示。


图332“切换行/列”前后对比效果


(6) 选中图表对象，先将图表对象左上角移动至A8单元格处，然后将鼠标放到图表对象右下角，当鼠标图案变成双向箭头时，按下鼠标左键同时拖动鼠标至K30单元格处。
【任务2】建立饼图，完成效果如图333所示。


图333实验33任务2图表完成效果图


【要求】以A1:A5,E1:E5两个不连续单元格区域为数据源，建立三维饼图； 将图表标题设置为“第4季度各分公司销售额统计图”； 为饼图添加百分比数据标签； 将图表对象放置到名称为“图表1”的新工作表中。
【操作步骤】
(1) 建立“三维饼图”。
先选中A1:A5单元格，按下Ctrl键的同时选中E1:E5单元格，选择“插入”→“图表”组→“饼图”→“三维饼图”命令。
(2) 修改图表标题。
将默认的图表标题修改为“第4季度各分公司销售额统计图”。
(3) 添加百分比数据标签。
选中图表对象，选择“图表工具|布局”→“标签”组→“数据标签”→“其他数据标签选项”命令，在弹出的“设置数据标签格式”对话框中勾选“标签选项”为“百分比”。
(4) 移动图表。
选中图表对象，右击，选择“移动图表”，在弹出的“移动图表”对话框中选择放置图表的位置为“新工作表”，并将新工作表命名为“图表1”，如图334所示。


图334“移动图表”对话框


三、 实验作业
本实验作业在素材“作业文档3.xlsx”中完成。
(1) 在Sheet1中利用“姓名”和“奖学金”列中的数据创建图表，图表标题为“奖学金情况”，图表类型为“带数据标记的折线图”并作为其中的对象插入Sheet1中。
(2) 在Sheet2中利用4种学科成绩和“姓名”列中的数据建立图表，图表类型为“簇状条形图”并放到新图表“Chart1”中。
实验34数 据 管 理
一、 实验目的
(1) 掌握数据的排序方法。
(2) 掌握数据的筛选方法。
(3) 掌握数据的分类汇总方法。
(4) 掌握数据透视表的使用方法。
(5) 掌握数据表的合并方法。
二、 实验示例
1. 以下任务需使用素材“期末成绩汇总表.xlsx”，共包含“期末成绩表1”和“期末成绩表2”两张工作表。初始素材数据如图335所示。


图335“期末成绩表1”和“期末成绩表2”初始数据



合并两张期末成绩表

【任务】合并两张期末成绩表。
【要求】新建工作表并重命名为“期末成绩统计表”，将合并后的表格从新表A1单元格起始开始放置。
【操作步骤】
(1) 在任意工作表标签上右击，选择“插入”，在弹出的对话框中选择“工作表”，单击“确定”结束。新建工作表默认名Sheet1，在Sheet1标签上右击，选择“重命名”，输入新表名“期末成绩统计表”。
(2) 在A1单元格内输入“姓名”。
(3) 选择“数据”→“数据工具”组→“合并计算”命令，在弹出的“合并计算”对话框(见图336)中“引用位置”处通过单击“浏览”按钮选择“期末成绩表1”的数据区域，单击“添加”按钮后，在“所有引用位置”列表框中出现“期末成绩表1”的引用区域。“期末成绩表2”引用位置的添加方法与“期末成绩表1”类似。勾选“标签位置”的“首行”和“最左列”，单击“确定”按钮。两张表合并后效果如图337所示。


图336“合并计算”对话框




图337两张表合并后效果


2. 以下任务需使用Excel实验素材“学生成绩表.xlsx”，原始素材数据如图338所示。


图338学生成绩表原始素材数据


【任务1】数据排序。
【要求】按照“高等数学”升序、“体育”降序的顺序排序。
【操作步骤】
(1) 选中A2:G13单元格，选择“数据”→“排序和筛选”组→“排序”命令。

 提示： 也可以选择“开始”→“编辑”组→“排序和筛选”→“自定义排序”命令。

(2) 在弹出的“排序”对话框中设置“主要关键字”为“高等数学”，“排序依据”为“数值”，“次序”为“升序”。
(3) 单击“添加条件”按钮，设置“次要关键字”为“体育”，“排序依据”为“数值”，“次序”为“降序”，单击“确定”按钮，如图339所示。


图339“排序”设置


 提示： 进行排序的数据区域中不能包括已合并的单元格。

【任务2】数据自动筛选。
【要求】
(1) 筛选出“总分”介于280~350分的学生。
(2) 筛选出各科成绩都及格的学生。
【操作步骤】
(1) 针对“总分”的单字段筛选。
① 撤销任务1的排序操作，恢复为素材初始状态。
② 选中A2:G13单元格，选择“数据”→“排序和筛选”组→“筛选”命令，各列数据标题单元格右侧自动出现下拉筛选按钮

。
 提示： 也可以选择“开始”→“编辑”组→“排序和筛选”→“筛选”命令。

③ 单击“总分”列筛选按钮

，选择“数字筛选”→“介于”，在弹出的“自定义自动筛选方式”对话框中，在“总分”的“大于或等于”中输入280，在“小于或等于”中输入350，单击“确定”按钮，如图340所示。


图340“自定义自动筛选方式”对话框


(2) 针对各科成绩的多字段筛选。
① 选择“数据”选项卡→“排序和筛选”组→“清除”命令，清除之前的总分筛选。
② 分别单击“高等数学”“大学英语”“体育”和“大学物理”列标题右侧筛选按钮，选择“数字筛选”→“大于或等于”命令，在弹出的“自定义自动筛选方式”对话框中设置为大于或等于60，单击“确定”按钮。筛选后效果如图341所示。


图341各科目均及格学生筛选结果


【任务3】数据分类汇总。
【要求】撤销任务2的筛选操作，恢复为素材初始状态。在“姓名”列右侧插入“性别”列，输入学生性别。对男女生4门科目的平均分进行分类汇总。
【操作步骤】
(1) 取消筛选。
选择“开始”→“编辑”组→“排序和筛选”→“筛选”命令，筛选按钮自动消失，恢复为素材初始状态。
(2) 插入新列。
在“高等数学”列任意单元格上右击，选择“插入”命令，在打开的“插入”对话框中选择“整列”。
(3) 数据分类汇总。
① 将新列标题设置为“性别”，输入学生性别，如图342所示。


图342插入“性别”列


② 将学生成绩表按照“性别”进行排序，升序降序均可。
③ 选中A2:H13单元格，选择“数据”选项卡→“分级显示”组→“分类汇总”命令，在弹出的“分类汇总”对话框中设置分类字段为“性别”、汇总方式为“平均值”，勾选汇总项“高等数学”“大学英语”“体育”和“大学物理”，单击“确定”按钮，如图343所示。分类汇总效果如图344所示。
 


图343“分类汇总”对话框





图344“分类汇总”效果图


【任务4】数据透视表的使用。
【要求】删除任务3的数据分类汇总。在“性别”列右侧插入“班级”列，输入学生班级。按性别为行标题、班级为列标题，在新工作表中建立各班级学生高等数学和体育平均分的数据透视表。
【操作步骤】
(1) 删除分类汇总。
选中A2:H16单元格，选择“数据”→“分级显示”组→“分类汇总”,在弹出的“分类汇总”对话框中单击“全部删除”按钮。
(2) 插入新列。
重复任务3步骤(2)，在“高等数学”列右侧插入“班级”列，输入学生班级如图345所示。


图345插入“班级”列




图346“数据透视表字段列表”对话框



 提示： “分类汇总”只能针对一个字段进行分类和汇总，若要对两个字段同时进行分类汇总，则必须使用数据透视表(或数据透视图)功能。

(3) 建立数据透视表。
① 选中A2:I13单元格，选择“插入”→“表格”组→“数据透视表”命令，放置数据透视表的位置设置为“新工作表”，单击“确定”按钮。
② 在“数据透视表字段列表”对话框(见图346)中进行设置，注意“高等数学”和“体育”默认数值汇总方式为“求和项”，通过如图347所示“值字段设置”对话框可以修改值汇总方式。数据透视表完成效果如图348所示。



图347“值字段设置”操作流程




图348任务4“数据透视表”完成效果图


三、 实验作业
本实验作业在素材“作业文档4.xlsx”中完成。
(1) 在Sheet1中利用“总分”进行升序排序。
(2) 在Sheet1筛选出“数学”“英语”“物理”和“计算机” 4科均大于70分的学生。
(3) 在Sheet1中利用“专业”进行分类汇总出4科成绩的平均分。
(4) 以Sheet2中数据建立数据透视表，数据透视表放置到一个名为“成绩透视表”的新工作表中，透视表行标签为“专业”，列标签为“性别”，对4科成绩汇总最高分。
四、 Excel综合实验作业
在给定的素材“综合实验作业文档.xlsx”中完成2017级法律专业学生期末成绩分析表的制作。具体要求如下： 
(1) 在“2017级法律”工作表列标题最右侧依次插入“总分”“平均分”“年级排名”列； 将A1:O1区域合并居中，并设置标题为黑体、14号。设置所有列标题居中对齐，其中排名为整数，其他成绩的数值保留1位小数。
(2) 在“2017级法律”工作表中，利用函数分别计算“总分”“平均分”“年级排名”列的值。对学生成绩不及格(小于60分)的单元格利用条件格式将字体颜色设置为红色。
(3) 在“2017级法律”工作表中，利用公式、根据学生的学号、将其班级的名称填入“班级”列，规则为： 学号的第三位为专业代码、第四位代表班级序号，即01为“法律一班”，02为“法律二班”，03为“法律三班”，04为“法律四班”。
(4) 根据“2017级法律”工作表，创建一个数据透视表，放置于表名为“班级平均分”的新工作表中。要求数据透视表中按照英语、体育、计算机、近代史、法制史、刑法、民法、法律英语、立法的顺序统计各班各科成绩的平均分，其中行标签为班级。所有列的对齐方式设为居中，成绩的单元格格式设置为数值型且保留1位小数。
(5) 在“班级平均分”工作表中，针对各课程的班级平均分创建簇状柱形图，其中水平簇标签为班级，图例项为课程名称，并将图表放置在表格下方的A10:H30区域中。
(6) 在“2017级法律”工作表中A2:O102区域设置套用表格格式为“表样式浅色15”。
(7) 原名保存文档。