模块一 数据的录入、格式化与美化
案例示范 1 数据的录入与格式化 打开“人事清单.xlsx”工作表，数据源如图 31 所示，完成后的效果如图 32 所示（供理 论教材的“实战案例 1”参考）。 图 31 “人事清单”数据源 图 32 “人事清单”效果图 操作要求： （1）参照效果图中的数据完善工作表，具体要求： l 录入“序号”列，利用填充功能完成。 l 录入“员工编号”列，先自定义格式以便快速完成。 l 录入“性别”和“职位”列，利用“数据的有效性规则”保证数据录入的正确性。 l 录入“何时来本单位”列，日期格式为“××年×月×日” 。（2）对数据的格式进行设置。 l 将 A1:M1 单元格区域合并，内容水平居中，其余单元格数据居中。 l 在“性别”列后边插入“身份证号”列并设置该列数据只能录入 18 位数，然后参照 效果图输入数据。 l 表格外框设置为深红色双线，表格内框设置为绿色虚线。 l 各行各列设置为最适合的高度与宽度。 l 将表格内容（A2:N17）套用表格格式“表样式中等深浅 3” 。 l 页面的纸张大小设置为 A4，页眉为“人事资料一览表” ，页脚为“第 页共 页”格式， 完成后保存到 D 盘，取名为“人事清单” 。 操作步骤： （1）选择 A3:A17 单元格区域并右击，选择“设置单元格格式”命令，在“单元格格式” 对话框的“数字”选项卡中的“分类”列表框中选择“文本” ，单击“确定”按钮，然后在 A3 单元格中录入“01” ，再利用填充功能实现其余单元格的序号输入。 （2）为了减少录入“员工编号”的工作量，先设置前面的公共参数，方法是选取 B3:B17 单元格区域，在“单元格格式”对话框的“数字”选项卡中的“分类”列表框中选择“自定义” ， 在“类型”文本框中输入“"XXX"000” ，如图 33 所示，然后单击“确定”按钮，这样在 B3 单元格中只需录入 1 即可，以后按住 Ctrl 键的同时拖动填充柄实现其余单元格的编号输入。 图 33 自定义文本格式 （3）为保证“性别”列信息的正确录入，可先选择 C3:C17 单元格区域，再选择“数据” →“数据工具”→“数据有效性”命令，在“设置”选项卡的“允许”下拉列表框中选择“序 列” ，在“来源”文本框中输入“男,女” （注意应用英文标点），如图 34 所示，然后在“性别” 列输入数据时可在列表框中选取所要的值。 （4） “职位”列的录入方法可参照“性别”列的输入方法。 （5）选取 H3:H17 单元格区域，在“设置单元格格式”对话框的“数字”选项卡中设置 日期格式为 “××年×月×日” 形式， 单击 “确定” 按钮返回， 在 H3 单元格中输入 “200671” ， 同理可实现本列其他日期型数据的输入。
图 34 数据有效性设置 （6）将 A1:M1 单元格合并，数据水平居中对齐，其余单元格数据居中。 （7）选中第 E 列，选择“开始”→“单元格”→“插入”→“插入列”命令，这时新插 入的列具有“性别”列的“数据有效性”规则，所以先对现在的 E3:E17 区域修改“数据有效 性”为文本长度等于 18 位，然后即可录入该列的数据。 （8）在“设置单元格格式”对话框的“边框”选项卡中按题目要求进行相应设置。 （9）选取各行（列），选择“开始”→“单元格”→“格式”→“自动调整行高（列宽） ” 命令。 （10）选取 A2:N17 单元格区域，选择“开始”→“样式”→“自动套用格式”→“表样 式中等深浅 3”命令。 （11）选择“页面布局”命令，在“页面设置”对话框中完成相应设置。 案例示范 2 数据表的格式化 打开“成绩表.xlsx”文件，数据源如图 35 所示，完成后的效果如图 36 所示（供理论教 材模块二、模块三的“学习实践”参考）。 图 35 “成绩表”数据源 图 36 “成绩表”效果图 操作要求： （1）在数据区域右边增加一列作“总分”字段，并填入每人的总分。 （2）在“李红”后面插入一条记录，该记录内容同第一条（张红）内容，然后删除第一 条记录。 （3）在“姓名”后面添加一列“学号” ，分别录入 001，002，003，……。
（4）给数据添上标题“成绩统计表” ，楷体、加粗、15 号、跨列居中、红色、浅绿色 底纹。 （5）给“刘洪”单元格加上批注“该生数学成绩优秀” 。 （6）将各科成绩为 80 的数值设为红色的 80。 （7）在标题行的下方增加一行，然后合并 A2:F2 单元格区域，录入当天的日期，靠右对 齐，日期格式为：×年×月×日。 （8）第三行文字（姓名、学号等）设置为宋体、12 号、加粗、居中（水平方向），下方 各行文字设置为宋体、12 号、左对齐。 （9）数值型数据（数学、英语、计算机的分值）保留 1 位小数。 （10）标题行的行高设为 22 磅，其余各行各列均采用自动调整。 （11）给表格添加边框（A3:F20 区域）：外框双细线，内框单细线，各列标题下方红色单 细线。 （12）将“总分”列大于 240 的数值设为浅红填充色、深红色文本。 （13）将工作表更名为“成绩表” ，文件名不变保存。 操作步骤： （1）单击 E1 单元格，录入“总分” ，选中 E2 单元格，录入“=SUM(B2:D2)”后回车， 再次选中 E2 单元格，鼠标拖动填充柄至 E18 单元格完成“总分”列的计算。 （2）选中第 6 行，选择“开始”→“单元格”→“插入”→“插入行”命令，再选择 A2:D2 区域，选择“开始”→“剪贴板”→“复制”命令，然后单击 A6 单元格并选择“开始”→“剪 贴板”→“粘贴”命令，最后选取第 2 行，选择“开始”→“单元格”→“删除”→“删除行” 命令。 （3）选中第 2 列，选择“开始”→“单元格”→“插入”→“插入列”命令，然后选中 B2 单元格并录入“’001” ，再次选中 B2 单元格，鼠标拖动填充柄至 B19 单元格完成“学号” 列的录入。注意，前导符号需要在英文半角状态下输入，也可以先将 B2:B19 区域设为文本格 式后直接录入 001。 （4）选中第 1 行，选择“开始”→“单元格”→“插入”→“插入行”命令，然后选中 A1 单元格并录入“成绩统计表” ，再选取 A1:F1 区域并右击，选择“设置单元格格式”选项， 在“设置单元格格式”对话框中完成相应的设置。 （5）选中 A14 单元格，选择“审阅”→“批注”→“新建批注”命令。 （6）单击数据区，选择“开始”→“编辑”→“查找和选择”→“替换”命令，如图 37 所示，注意勾选“单元格匹配”复选项。 图 37 “查找和替换”对话框 注意勾选
（7）选中第 2 行，选择“开始”→“单元格”→“插入”→“插入行”命令，然后选中 A2:F2 单元格区域，在“设置单元格格式”对话框的“对齐”选项卡中完成“合并单元格”和 “右对齐”格式的设置，同时在“数字”选项卡中设置日期格式为“×年×月×日”形式，单 击“确定”按钮返回，然后录入“2013117” 。 （8）选取 A3:F3 区域，在“设置单元格格式”对话框中完成相应的设置。 （9）选取 C3:F20 区域，在“设置单元格格式”对话框的“数字”选项卡中完成设置。 （10）选中标题行，选择“开始”→“单元格”→“格式”→“行高”命令，输入参数 22，然后选取其余各行，选择“开始”→“单元格”→“格式”→“自动调整行高”命令。同 理完成列宽的设置。 （11）选取 A3:F20 区域，在“设置单元格格式”对话框的“边框”选项卡中完成外框双 细线、内框单细线的设置。然后选取 A3:F3 区域，方法同上，只设下边线型为“红色单细线” 。 （12）选取 F4:F20 区域，选择“开始”→“样式”→“条件格式”→“突出显示单元格 规则”→“大于”命令，在对话框中设置所需参数。 （13）右击工作表标签，选择“重命名”选项，输入“成绩表” ，完成工作表的更名，保 存文件。 实训集 实训 1 打开“化工 2 班成绩表.xlsx” ，数据源如图 38 所示，最终效果如图 39 所示。 图 38 “化工 2 班成绩表”数据源 图 39 “化工 2 班成绩表”效果图 操作要求： （1）在“编号”列依次输入 XL0101，XL0102，……，XL0108，要求先定义好格式， 只需输入后四位数字就可实现数据的录入，并且利用填充功能实现快速输入该列数据。 （2）在“姓名”列的右边添加“性别”列，采用下拉列表，只能输入“男”或“女” ， 并按效果图录入具体值。 （3）将标题行行高设为 40 磅，合并 A1:F1 区域并将内容设为上下左右均居中，字体加 粗 16 磅，其余区域的数据内容水平居中。 （4）将各列的宽度设置为“最合适的列宽” 。 （5）将各科不及格的成绩以红色字体显示出来。 （6）将单元格 A2:F10 区域套用表格格式“表样式浅色 4” 。 （7）对所有的数值型数据保留 1 位小数。
（8）将工作表改名为“化工 2 班 3 组成绩表” 。 实训 2 打开“员工信息表.xlsx”工作表，数据源如图 310 所示，完成后的效果如图 311 所示。 图 310 “员工信息表”数据源 图 311 “员工信息表”效果图 操作要求： （1） “员工编号”列数据使用自定义单元格数字格式实现简单输入。 （2） “性别” （男、女）和“学历” （专科以下、专科、本科、硕士、博士）两列数据能 从下拉列表中选择输入。 （3） “身份证号码”列数据设置文本长度为 18 位，出错时弹出如图 312 所示的“出错警 告”对话框。
图 312 “出错警告”对话框 （4）完成如下打印设置：纸张方向为纵向，页边距为左右 1.8cm、上下 3.0cm，页眉页脚 为 1.8cm，页眉内容为“员工情况一览表” ，居中，页脚内容格式为“第 1 页 共 页” ，每页 打印标题行（工作表中的第一行）。
模块二 数据的计算
案例示范 1 数据的基本计算 打开 Excel.xlsx 文件，数据源如图 313 所示，效果如图 314 所示。 图 313 “工资明细表”数据源 图 314 计算后的“工资明细表” 操作要求： （1）计算“奖金”列，其金额为基本工资的 15%。 （2）求“应发数”列（应发数=基本工资+工龄津贴+职务津贴+奖金） 。（3）求“实发工资”列，各数值前带上￥符号（实发工资=应发数扣除） 。 （4）在 K14 单元格中计算实发工资的总和。 （5）计算每个人所得的实发工资占实发工资总额的百分比，保留 1 位小数。 操作步骤： （1）单击 H3 单元格，在编辑栏中输入公式“=E3*15%”按回车键确认，然后选中 H3 单元格，鼠标拖拉填充柄至 H13，从而实现奖金的计算。 （2）单击 J3 单元格，在编辑栏中输入公式“=E3+F3+G3+H3”按回车键确认，然后选中 J3 单元格，鼠标拖拉填充柄至 J13，从而实现应发数的计算。 （3）单击 K3 单元格，在编辑栏中输入公式“=J3I3”按回车键确认，然后选中 K3 单元 格，鼠标拖拉填充柄至 K13，从而实现实发工资的计算。 （4）单击 K14 单元格，在编辑栏中输入公式“=SUM(K3:K13)”按回车键确认，从而计 算出实发工资的总和。 （5）单击 L3 单元格，在编辑栏中输入公式“=K3/K$14”按回车键确认，然后选中 L3 单元格，鼠标拖拉填充柄至 L13，从而完成每个人的实发工资所占的百分比的计算。 案例示范 2 常用函数的应用 打开“文秘Ａ班成绩表.xlsx” ，数据源如图 315 所示，完成后的效果如图 316 所示（供 理论教材的模块四“学习实践”参考）。 图 315 “文秘 A 班成绩表”数据源 操作要求： （1）按“文秘 A 班成绩表”效果图进行格式设置。 （2）求出“学生成绩统计表”中的“总成绩”列、 “排名”列、 “有无补考”列（只注明 有补考的）和“平均分”行（保留 1 位小数） 。 （3）统计出“全班各门学科成绩最高分” 、 “全班男生各门学科的平均成绩” （保留 1 位 小数）、 “全班女生各门学科的平均成绩” 、 “全班总成绩>=320 的总人数” 和 “统计总成绩>=300
且总成绩<320 的学生人数” 。 图 316 “文秘 A 班成绩表”效果图 操作步骤： （1）格式设置的具体方法略。 （2）单击 H3 单元格，输入=SUM(D3:G3)确认（此步也可以利用插入函数，在“插入函 数”对话框中完成），然后再次单击 H3 单元格并拖动填充柄至 H14 单元格为止，完成求和列 的计算。 （3）单击 I3 单元格，在“插入函数”对话框中找到 RANK 函数，然后设置相应参数， 如图 317 所示，注意被排名的区域要用绝对引用或混合引用，然后再次单击 I3 单元格拖动填 充柄至 I14 单元格为止。 图 317 RANK 函数对话框 （4）单击 J3 单元格，在“插入函数”对话框中找到 IF 函数，然后设置相应参数，如图 318 所示，注意复合条件函数的用法，然后再次单击 J3 单元格拖动填充柄至 J14 单元格为止。 （5）单击 D17 单元格，输入=MAX(D3:D14)，也可以在“插入函数”对话框中找到 MAX 函数，然后设置相应参数，然后再次单击 D17 单元格拖动填充柄至 G17 单元格为止。
图 318 IF 函数对话框 （6）单击 D18 单元格，在“插入函数”对话框中找到 SUMIF 函数，如图 319 所示设置 相应参数（注意条件区域应使用混合引用以便填充其余列的统计），从而求出男生数学成绩之 和 ， 再 次 选 中 D18 单 元 格 ， 将 公 式 修 改 为 =SUMIF($C3:$C14," 男 ",D3:D14)/COUNTIF ($C3:$C14,"男")，其中 COUNTIF($C3:$C14,"男")是统计男生的人数，条件区域也应使用混合 引用以便填充其余列的统计。 图 319 SUMIF 函数对话框 （7）同上步骤实现“女生各门学科的平均成绩”的统计。 （8）鼠标单击 H20 单元格，在“插入函数”对话框中找到 COUNTIF 函数，如图 320 所示设置相应参数，实现“全班总成绩>=320 的总人数”的统计。 图 320 COUNTIF 函数对话框 （9）单击 H20 单元格，输入=COUNTIF(H3:H14,">=300")COUNTIF(H3:H14,">= 320")， 从而实现“统计总成绩>=300 且总成绩<320 学生人数” ，（此步是多条件的统计，公式的含义 是大于 300 分的人数减去 320 分以上的人数即是该题结果）。
案例示范 3 数据表的格式设置与数据运算 打开“工资表.xlsx”文件，数据清单如图 321 所示，完成后的效果如图 322 所示（供理 论教材的“实战案例 2”参考）。 图 321 “工资表”数据源 图 322 “工资表”效果图 操作要求： （1）参照效果图修改“工资表”的框架并格式化表格。 （2）对工资表排序：先按“所在部门”进行升序排列，然后按“职位”降序排列。 （3）录入员工的固定工资（经理 4800，科长 4200，职员 3500）和加班费（数据参见效 果图） 。 （4）计算“其他”列， “其他”工资为固定工资的 20%。 （5）计算“应付工资合计”列。 （6）计算“养老保险”列，养老保险金额为固定工资的 8%。
（7）计算“医疗保险”列，医疗保险金额为固定工资的 2%。 （8）计算“所得税”列，应付工资小于 3500，不扣所得税，应付工资超出 3500 基数以 上的扣 10%个人所得税（暂时不考虑其他级数和速算扣除数问题） 。 （9）计算“应扣工资合计”列。 （10）计算“实发工资”列。 （11） 把各个部门的情况分别复制到其他工作表中去建立各部门信息表， 工作表的名称改 为对应部门名。 （12）对财务部的三位员工进行打印“工资条”的制作。 操作步骤： （1）按照样表格式化表格。 （2）单击数据区内的某一单元格，选择“数据”→“排序和筛选”→“排序”命令，设 置对话框。 （3）录入固定工资和加班费。 （4）单击 G4 单元格，输入公式=E4*0.2，然后填充至 G18 单元格。 （5）单击 H4 单元格，输入公式=SUM(E4:G4)，然后填充至 H18 单元格。 （6）单击 I4 单元格，输入公式= E4*0.08，然后填充至 I18 单元格。 （7）单击 J4 单元格，输入公式= E4*0.02，然后填充至 J18 单元格。 （8）单击 K4 单元格，输入公式=IF(H4>3500,(H43500)*0.1,0)，然后填充至 K18 单元格。 （9）单击 L4 单元格，输入公式=SUM(I4:K4)，然后填充至 L18 单元格。 （10）单击 M4 单元格，输入公式=H4L4，然后填充至 M18 单元格。 （11）分别选取各部门的数据区域，选择“复制”操作，然后选取其他工作表执行“粘贴” 操作，并将工作表标签重命名为对应的部门名。 （12）由于财务部只有三位员工，所以采用“顶端标题行”生成工资条（实际中最好采 用 Word 的邮件合并功能生成工资条），方法是：单击“页面布局”→“页面设置”命令，弹 出“页面设置”对话框，在“工作表”选项卡中设置打印区域和顶端标题行区域如图 323 所 示，然后单击“打印预览”按钮，再单击“页边距”按钮，使用鼠标拖动“下边距”虚线至工 作表中“章晓月”行的下边线处。 图 323 “页面设置”对话框
实训集 实训 1 打开“生产情况表.xlsx”文件，数据源如图 324 所示，完成后的效果如图 325 所示。 图 324 “生产情况表”数据源 图 325 “生产情况表”效果图 操作要求： （1）将 A1:F1 单元格区域合并为一个单元格，内容水平居中。 （2）计算“产值”列的内容（产值=日产量×单价），计算日产量的总计和产值的总计， 置于“总计”行的 B13 和 D13 单元格。 （3）计算“产量所占比例”和“产值所占比例”列的内容（百分比型，保留 1 位小数） 。 （4）将工作表命名为“日生产情况表” ，保存工作簿文件。 实训 2 打开“运动会成绩统计.xlsx”文件，数据源如图 326 所示，完成后的效果如图 327 所示。 操作要求： （1）将 Sheet1 工作表的第 1 行行高设为 23 磅，其他各行设为最适合的行高，各列自动 调整列宽。
图 326 “运动会成绩统计表”数据源 图 327 “运动会成绩统计表”效果图 （2）计算“总积分”列的内容（利用公式：总积分=第一名项数×8+第二名项数×5+第 三名项数×3） 。 （3）按总积分的降序次序计算“积分排名”列的内容（利用 RANK 函数） 。 （4）利用套用表格格式将 A2:F10 数据区域设置为“表样式中等深浅 19” 。 实训 3 打开“销售额统计表.xlsx”文件，数据源如图 328 所示，完成后的效果如图 329 所示。 图 328 “销售额统计表”数据源 图 329 “销售额统计表”效果图 操作要求： （1）将 A2:E14 区域内的数据水平居中对齐。 （2）计算“同比增长”列的内容（同比增长=(11 年销售额10 年销售额)/10 年销售额， 百分比型，保留小数点后两位） 。 （3）如果“同比增长”列的内容高于 50%，在“备注”列内给出信息“A” ，否则给出信 息“B” （利用 IF 函数）。
（4）按“同比增长”列降序排列次序。 实训 4 打开“销售情况表.xlsx”文件，数据源如图 330 所示，完成后的效果如图 331 所示。 图 330 “销售情况表”数据源 图 331 “销售情况表”效果图 操作要求： （1）将 B3:B14 区域内的数值数据保留 1 位小数。 （2）计算“已销售出数量” （已销售出数量=进货数量库存数量） 。 （3）计算“销售额（元） ” 。 （4）给出“销售排名” （按销售额降序排列） 。 （5）利用单元格样式的“标题 2”修饰表的标题，利用“输出”修饰表的 A2:G14 单元格 区域。 （6）利用条件格式将“销售排名”列内容中数值小于或等于 5 的数字颜色设置为红色。 实训 5 打开“学生成绩表.xlsx”文件，数据源如图 332 所示，完成后的效果如图 333 所示。 操作要求： （1）计算“平均成绩”列的内容（数值型，保留小数点后 2 位） 。 （2）计算一组学生人数（置 G3 单元格内，利用 COUNTIF 函数） 。
图 332 “成绩统计表”数据源 图 333 “成绩统计表”效果 （3）计算一组学生平均成绩（置 G5 单元格内，利用 SUMIF 函数）。 （4）将工作表命名为“成绩统计表” ，保存文件。 实训 6 打开 “中文系 A 班学生成绩表.xlsx” 文件， 数据源如图 334 所示， 完成后的效果如图 335 所示。 图 334 “中文系 A 班学生成绩表”数据源
图 335 “中文系 A 班学生成绩表”效果图 操作要求： （1）给 A2 单元格加斜线，并在斜线上下分别输入“类别”和“姓名” ，其他格式参照效 果图完成。 （2）用函数统计各门课程的“最高分”和“最低分” 。 （3）用函数统计各门课程的“参考人数”和“及格人数” 。 （4）分别计算各门课程男生的平均成绩和女生的平均成绩（先利用 SUMIF 函数求总成 绩，保留小数点后 1 位） 。 （5）求出每个人的考试平均成绩和总成绩。 （6）对于每个人只要有一科在 60 分以下，就在补考栏注明“补考”二字。 （7）对于每个人只有三科均在 90 分及以上，才在备注栏注明“优秀”二字。 （8）按平均成绩从高到低排名次并填入相应的单元格内（利用 RANK 函数） 。 （9）统计平均成绩各分数段人数并计算及格率（有百分号，1 位小数） 。 （10）求出三门课程成绩中出现频率最多的数。 （11）将工作表命名为“课程成绩表” 。
模块三 数据的分析
案例示范 1 数据清单分析 打开“某公司人员情况”数据清单，如图 336 所示，完成后的效果如图 337 所示（供理 论教材的模块五“学习实践 1”参考）。 操作要求： （1）按效果图设置格式。 （2）按主要关键字“职称”递增次序和次要关键字“部门”递减次序进行排序。图 336 “某公司人员情况”数据清单 图 337 “某公司人员情况”效果图 （3）在工作表的 K25:P30 区域中建立数据透视表，显示各部门各职称基本工资的平均值 以及汇总信息，设置数据透视表内的数字为货币型，保留小数点后一位。 （4）进行筛选，条件为“部门为销售部或开发部并且学历为硕士或博士” ，筛选出来的 结果置于单元格 K1 起始处。 （5）对排序后的数据清单内容进行分类汇总，计算各职称基本工资的平均值（分类字段 为“职称” ，汇总方式为“平均值” ，汇总项为“基本工资” ） ，汇总结果显示在数据下方。 （6）将所建立的数据透视表移至单元格 K15 起始处。 操作步骤： （1）设置格式略。 （2）单击数据区域内的任一单元格，选择“数据”→“排序和筛选”→“排序”命令， 弹出如图 338 所示的对话框，主要关键字中选择“职称” 、 “升序” ，然后单击“添加条件”按 钮，在次要关键字中选择“部门” 、 “降序” ，即实现排序要求。
图 338 “排序”对话框 （3）单击数据区域内的任一单元格，选择“插入”→“表格”→“数据透视表”命令， 弹出如图 339 所示的对话框，设置“表/区域”和“放置数据透视表的位置” ，单击“确定” 按钮。 图 339 “创建数据透视表”对话框 （4）在如图 340 所示的“数据透视表”字段表中右击“部门” ，在弹出的快捷菜单中选 择“添加到行标签”选项，同理将“职称”添加到列标签， “基本工资”添加到值。 图 340 “数据透视表”字段表
（5）右击“数值”下方的“求和…” ，选取“值字段设置” ，如图 341 所示。 图 341 “值字段设置” （6）弹出如图 342 所示的对话框，在“计算类型”列表框选中“平均值” ，单击“数字 格式”按钮，在数字格式设置对话框中选择“货币”型。 图 342 “值字段设置”对话框 （7）此题因为需要将筛选结果放于 K1 处，所以要进行高级筛选，先在 B24:C28 区域建 立高级筛选的条件区，如图 343 所示，然后选择“数据”→“排序和筛选”→“高级筛选” 命令，如图 344 所示设置相应参数，单击“确定”按钮。 图 343 建立的“条件区” 图 344 “高级筛选”对话框 （8）由于前面已对“职称”进行了排序，所以可直接进行“分类汇总” ，单击数据区内 的任一单元格，选择“数据”→“分级显示”→“分类汇总”命令，弹出如图 345 所示的对 话框。
（9）单击已建立的数据透视表，选择“数据透视表工具”→“选项”→“操作”→“移 动数据透视表”命令，弹出如图 346 所示的对话框，选取现有工作表中的 K15 单元格。 图 345 “分类汇总”对话框 图 346 “移动数据透视表”对话框 案例示范 2 数据表分析 打开“红星销售.xlsx”文件，数据源如图 347 所示，完成后的效果如图 348 所示（供理 论教材的“实战案例 4”参考）。 图 347 “红星销售”数据源 操作要求： （1）按照效果图进行格式化。 （2）在 Sheet2 工作表的 A1 单元格为起始处建立数据透视表，进行数据的分析，了解公 司该月的销售具体情况。
图 348 “红星销售”效果图 （3）利用“高级筛选”选出车辆型号为红星 SQR7160ES 或销售人员为王红梅的销售情 况，结果存放在 A28 单元格的开始处。 （4）按照车辆的型号求和汇总销售金额，在统计出同一种型号车辆的销售总金额的基础 上再统计每个销售员的销售总台次。 操作步骤： （1）格式设置略。 （2）选择“插入”→“表格”→“数据透视表”命令，在“创建数据透视表”对话框中 设置“表/区域”为 A1:G26， “放置透视表位置”为 Sheet2!$A$1，单击“确定”按钮，然后在 “数据透视表字段”列表中将“车辆型号”和“销售员”字段添加入“行标签”内， “销售台 次”和“销售金额”添加到“求和项”中。 （3）建立条件区：在 I28 单元格中输入“车辆型号” ，I29 单元格中输入 SQR7160ES，J28 单元格中输入“销售员” ，J30 单元格中输入“王红梅” 。 （4）选择“数据”→“排序和筛选”→“高级筛选”命令，设置参数：方式为“将筛选 结果复制到其他位置” ，列表区域为 A1:G26，条件区域为 I28:J30，复制到 A28 单元格，单击 “确定”按钮，如图 349 所示。 （5）先按“车辆型号”进行排序：单击 C 列的任一单元格，选择“数据”→“排序和筛 选”中的 或 ；然后单击数据区的任一单元格，选择“数据”→“分级显示”→“分类汇 总”命令， “分类汇总”对话框设置：分类字段选择“车辆型号” ，汇总方式为“求和” ，选定 汇总项“销售金额” ，单击“确定”按钮。 （6）再次打开“分类汇总”对话框，进行如下设置：分类字段选择“销售员” ，汇总方 式为“求和” ，选定汇总项为“销售台次” ，取消对“替换当前分类汇总”复选框的选择，单击 “确定”按钮。
图 349 “高级筛选”对话框 实训集 实训 1 打开“产品销售表.xlsx”文件，数据源如图 350 所示，完成后的效果如图 351 所示。 图 350 “产品销售表”数据源 操作要求： （1）将工作表“产品销售情况表”内数据清单的内容复制到 Sheet2 工作表中，并按主要 关键字“分公司”的降序次序和次要关键字“季度”的升序次序进行排序。
图 351 “产品销售表”效果图 （2）在 Sheet2 工作表中对排序的数据进行高级筛选（在数据清单前插入 4 行，条件区域 设在 A1:G3 单元格区域，请在对应字段列内输入条件，条件为：产品名称为“空调”或“电 视”且销售额排名在前 20 名） 。 （3）将 Sheet2 工作表重命名为“产品销售分析表” 。 （4）对工作表“产品销售情况表”内数据清单的内容建立数据透视表，行标签为“产品 名称” ，列标签为“分公司” ，求和项为“销售额（万元） ” ，并置于现工作表的 Sheet3 工作表 的 A1 处，然后将 Sheet3 工作表重命名为“数据透视表” 。 （5）在工作表“产品销售情况表”内完成对各分公司销售额总和的分类汇兑，汇总结果 显示在数据下方，工作表名不变，保存工作簿。 实训 2 打开“产品销售记录.xlsx”文件，数据源如图 352 所示，完成后的效果如图 353 所示。 图 352 “产品销售记录”数据源
图 353 “产品销售记录”效果图 操作要求： （1）将 Sheet1 工作表的内容复制一份到 Sheet3 工作表中。 （2） 在 Sheet1 工作表中将产品销售记录按日期升序排列， 若日期相同则按金额降序排列。 （3）在 Sheet1 工作表中筛选出顾客为“刘振辉”或“黄碧秀”且金额大于或等于 500 的 数据，并将筛选结果（包括标题行）置于 Sheet2 工作表中。 （4）在 Sheet1 工作表中筛选出日期在 200811～2008315 间且顾客为“刘振辉”产品 为“按摩器”或顾客为“黄碧秀”产品为“手动钻”且总计大于或等于 500 的数据，并在表格 下方的空白区域显示。 （5）在 Sheet3 工作表中分类统计每位顾客的金额总值和平均值。
模块四 数据的图表分析
案例示范 1 格式设置与图表的建立 打开“成绩表.xlsx”文件，数据源如图 354 所示，完成后的效果如图 355 所示（供理论 教材的模块五“学习实践”参考）。 图 354 “成绩表”数据源图 355 “成绩表”效果图 操作要求： （1）合并 A1:E1 单元格区域为一个单元格，内容水平居中，计算“平均成绩”列的内容 （保留小数点后两位）；利用条件格式将“平均成绩”列成绩小于或等于 75 分的字体颜色设置 为红色；利用表格套用格式将 A2:El2 单元格区域设置为“表样式浅色 5” ；对数据清单的内容 按主要关键字“平均成绩”的递减次序和次要关键字“数学”的递减次序进行排序；将工作表 命名为“成绩统计表” 。 （2）选取“成绩统计表”的“学号”列（A2:A12 单元格区域）和“平均成绩”列（E2:E12 单元格区域）的内容建立“簇状条形图” ，图标题为“成绩统计图” ，清除图例；设置图表绘图 区格式图案区域填充为“浅色横线” ，将图插入到表 A14:G24 单元格区域。 操作步骤： 操作要求（1）的操作方法略。 （1）选取“成绩统计表”的“学号”列（A2:A12 单元格区域）和“平均成绩”列（E2:E12 单元格区域）的内容。 （2）选择“插入”→“图表”→“条形图”→“簇状条形图”命令。 （3）选择“图表工具”→“布局”→“标签”→“图表标题”→“图表上方”命令，将 图表的标题改为“成绩统计图” 。 （4）选择“图表工具”→“布局”→“标签”→“图例”→“无（关闭图例） ”命令， 即完成清除图例操作。 （5）选中“绘图区” （或选择“图表工具”→“格式”→“当前所选内容”→“绘图区” 命令），然后单击“设置所选内容格式” ，如图 356 所示。 （6）在“设置绘图区格式”对话框中单击“填充”→“图案填充”→“浅色横线” 。
（7）将图移到表的 A14:G24 单元格区域（调整图表区四周编辑柄可改变大小）。 图 356 “设置格式”对话框 案例示范 2 数据分析与图表的建立 打开“工资表”文件，数据源如图 357 所示，完成后的效果如图 358 所示（供理论教材 “实战案例 5”参考）。 图 357 “工资表”数据源 操作要求： （1）计算：①基本工资大于 2500 的捐款金额为基本工资的 10%，小于等于 2500 的捐款 金额为基本工资的 5%；②实发工资=基本工资+各种津贴捐款；③分别统计男性和女性的人 数；④统计人均基本工资、最高工资、最低工资。 （2）按部门升序，部门相同时再按年龄降序，如部门和年龄均相同就按实发工资升序排列。 （3）筛选出年龄大于 30 和工龄小于 5 年的记录，放在 A18 单元格的起始处。 （4）选取“姓名”列和“捐款百分比”列的内容建立“分离型三维饼图” ，图表标题为 “捐款统计图” ，图例置底部；将图插入到表的 N1:R12 单元格区域内，将工作表命名为“销 售收入统计表” 。 （5）利用工作表中的数据，以“部门”为分页，以“性别”为行字段， “实发工资”和 “各种津贴”为平均值项，从 Sheet1 工作表的 A1 单元格起建立数据透视表。 （6）以部门为分类汇总字段，统计出各部门的人数和平均年龄。
图 358 “工资表”效果图 操作步骤： 只对 4 题作答，其余略。 （1）选取“姓名”列（C1:C12）和“捐款百分比”列（M1:M12）的内容。 （2）选择“插入”→“图表”→“饼图”→“分离型三维饼图”命令。 （3）选择“图表工具”→“布局”→“标签”→“图表标题”→“图表上方”命令，并 将图表的标题改为“捐款统计图” 。 （4）选择“图表工具”→“布局”→“标签”→“图例”→“在底部显示图例”命令。 （5）将图移到表的 N1:R12 单元格区域。 （6）右击工作表标签，选择“重命名”命令，输入“销售收入统计表”完成工作表的重 命名。 实训集 实训 1 打开“成绩图.xlsx”文件，数据源如图 359 所示，完成后的效果如图 360 所示。 图 359 “成绩图”数据源 图 360 成绩统计图
操作要求： 生成一个工作表数据的簇状柱型图，系列产生在列，图表标题为“成绩统计图” ，分类轴 标题为“班级”并放在最右端，数值轴标题为“分数” ，置于最上端水平方向排列；设置图表 的图表区格式为白色，绘图区为浅黄色，数学系列的颜色设为“金色” ，图例位置靠右；数值 轴最大刻度为 120，主要刻度单位为 60。将图插入到表的 G1:L13 单元格区域内，将工作表重 命名为“成绩统计表” ，保存文件。 实训 2 打开 Excel.xlsx 文件，数据清单如图 361 所示，完成后的效果如图 362 所示。 图 361 “工资对比图”数据清单 图 362 “工资对比图”效果图 操作要求： （1）根据提供的工资浮动率计算工资的浮动额，再计算浮动后的工资。 （2）为“备注”列添加信息，如果员工的浮动额大于 800 元，在对应的备注列内填入“激 励” ，否则填入“努力” （利用 IF 函数） 。 （3）设置“备注”列的单元格样式为“40%—强调文字颜色 2” 。 （4）选取“职工号” 、 “原来工资”和“浮动后工资”列的内容，建立“堆积面积图” ，
设置图表样式为“样式 28” ，图例位于底部，图表标题为“工资对比图” ，位于图的上方，将 图插入到表的 A14:G33 单元格区域内。 实训 3 打开 E2.xlsx 文件，数据源如图 363 所示。 图 363 原始数据源 操作要求： 请使用多种方法实现以班级为分类字段，将各科成绩进行“平均值”分类汇总。 提示：分别使用“分类汇总” 、 “合并计算” 、 “数据透视表”和利用 SUMIF()､ COUNTIF() ｡ 函数实现
模块五 挑战自我部分
案例示范 1 制作“员工个人信息表” 操作要求： 打开“员工个人信息表.xlsx”文件，根据 Sheet2（如图 364 所示）和 Sheet3（如图 365 所示）工作表中的数据在 Sheet1 工作表中制作如图 366 所示的“员工个人信息表” 。 图 364 Sheet2 中的数据源 操作步骤： （1） 在 Sheet2 工作表中选中 A2:A7， 在 “名称” 框中定义该单元格区域的名称为 “部门” ， 按照此方法依次定义其他单元格区域的名称 （或者按住 Ctrl 键的同时选中区域 A1:A7、 B1:B3、 C1:C4、D1:D4、E1:E5、F1:F5，然后选择“公式”→“定义的名称”→“根据所选内容创建” 命令，弹出“以选定区域创建名称”对话框，选中“首行”复选项，如图 367 所示，单击“确 定”按钮实现一次性对部门、性别等 6 个单元格区域名称的定义。图 365 Sheet3 中的数据源 图 366 “员工信息表”效果图 （2）将当前工作表切换到 Sheet3 中，选取所有的数据源，执行“复制”操作，然后将当 前工作表切换到 Sheet1 中，执行“粘贴”操作。 （3）利用“开始”选项卡中的相应命令或“单元格格式”对话框设置表格标题格式为字 号 18、跨列居中，外边框线为双实线，内框线为细实线，参考效果图实现部分单元格合并居 中，全部单元格文字居中对齐，适当调整行高和列宽，并在“单元格格式”对话框的“对齐” 选项卡中设置“照片”两个字的方向为竖排。
图 367 单元格区域名称的定义 （4）选定填写部门信息的单元格 C2，选择“数据”→“数据工具”→“数据有效性”命 令，在“设置”选项的“允许”下拉列表中选择“序列” ，在“来源”文本框中输入“=部门” （前面已定义各部门区域的名称为“部门” ），如图 368 所示，单击“确定”按钮返回。类似 地实现填写性别、政治面貌、婚姻状况、血型及最高学历下拉列表的创建。 图 368 “数据有效性”设置 （5）选中照片名称单元格 H3，选择“审阅”→“批注”→“新建批注”命令，在批注框 中输入提示信息，如图 369 所示。 图 369 插入“批注”后的效果 （6）修改完善表格，保存工作簿文件。
案例示范 2 制作“员工档案表” 操作要求： 通过“员工档案原表.xlsx” （如图 370 所示）中的数据制作一份资料准确、详细的员工档 案表（如图 371 所示），具体要求是添加性别、出生日期、工龄三列，但这三列的数据均来源 于原表。 图 370 员工档案原表 图 371 员工档案详表 操作步骤： （1）打开“员工档案原表”文件，在“职称”列前插入“性别”和“出生日期”列。 （2） 在 C3 单元格中输入公式 “=IF(MOD(IF(LEN(F3)=15,MID(F3,15,1),MID(F3,17,1)),2)=0, "女","男")”后按回车键确认，如图 372 所示。 （3）选中 C3 单元格，将鼠标移到该单元格的右下角，当指针变为“+”时直接往下拖动，
利用自动填充功能完成“性别”列数据的输入。 图 372 计算性别函数 （4）在 D3 单元格中输入公式“=IF(LEN(F3)=15,MID(F3,7,6),MID(F3,7,8))”并按回车确 认，并利用自动填充功能进行填充，如图 373 所示。 图 373 计算出生日期函数 （5）选中“出生日期”列的 D3:D68 单元格区域，先执行“复制”操作，再选择“开始” →“剪贴板”→“选择性粘贴”命令，弹出如图 374 所示的对话框，在其中选中“数值”单 选项，单击“确定”按钮。 图 374 “选择性粘贴”对话框
（6）继续选中“出生日期”列的 D3:D68 单元格区域，选择“数据”→“数据工具”→ “分列”命令，弹出如图 375 所示的“文本分列向导第 1 步，共 3 步”对话框，在其中选中 “固定宽度”单选项，单击“下一步”按钮。 图 375 “文本分列向导”对话框“第 1 步” （7）弹出如图 376 所示的“文本分列向导第 2 步，共 3 步”对话框，单击“下一步” 按钮。 图 376 “文本分列向导”对话框“第 2 步” （8）弹出如图 377 所示的“文本分列向导第 3 步，共 3 步”对话框，在“列数据格式” 区域中选中“日期”单选项，并在其右边的下拉列表框中选择 YMD，单击“完成”按钮，即 可得到员工的出生日期列的数据。 （9）在“工作日期”列后插入“工龄”列。 （10）在 H3 单元格中输入公式“=TRUNC(DAYS360(G3,TODAY())/360,0)”后按回车键 确认，如图 378 所示。 说明：其中 DAYS360(G3,TODAY())/360 表示求出当前系统日期和工作日期之间的天数后除 以 360 换算成年份，TRUNC(DAYS360(G3,TODAY())/360,0)求出年份的整数值。也可在 H3 单元 格 中 输 入 公 式 “ =YEAR(TODAY())YEAR(G3)IF(G3>DATE(YEAR(G3),MONTH(TODAY()), DAY(TODAY())),1,0)” 。 （11）利用自动填充功能完成其他人的工龄计算。
图 377 “文本分列向导”对话框“第 3 步” 图 378 工龄计算 案例示范 3 动态图表的制作 操作要求： 打开“产品销售表.xlsx”文件，请制作如图 379 所示的产品销售统计图，要求单击图表 左上角各个产品的选项按钮，则图表中即显示相应产品的销售情况，将图例置底部，图表区填 充色为浅绿色中心辐射等。 图 379 “产品销售”统计图
操作步骤： （1）打开“产品销售表.xlsx”文件，在单元格 F2 中输入公式“=A2” ，单元格显示 A2 单元格内的内容“地区” ，然后用填充的方法把该公式复制到 F3:F8 区域，则 F2:F8 区域显示 产品销售表中的第一列内容。 （2）在 F1 单元格中输入 1，该单元格内容用来控制要提取的是哪一种产品的数据（也就 是图表要描述的是哪一批数据） 。 （3）在 G2 单元格中输入公式=OFFSET(A2,0,$F$1)，确认后该单元格显示“A 产品”内 容，然后用填充的方法把该公式复制到 G3:G8 区域， 则 A 产品的数据被提取显示在该区域中。 （4）选取单元格区域 F2:G8，选择“插入”→“图表”→“饼图”→“饼图”命令。 （5）添加选项按钮：选择“文件”→“选项”→“自定义功能区” ，在下拉列表中选择“不 在功能区中的命令” ，找到“选项按钮（窗体控件） ” ，添加到自定义的选项卡内，如图 380 所示。 图 380 自定义功能区 （6）选择“自定义”→“自定义控件”→“选项按钮” ，如图 381 所示，在图表左上的 空白处单击或拖动画出“选项按钮 1” 。类似地画出“选项按钮 2” 、 “选项按钮 3” ，并合理地 调整它们的位置。 图 381 自定义的“选项按钮”命令 （7）设置选项按钮：右击“选项按钮 1” ，在弹出的快捷菜单中选择“编辑文字”命令， 修改为“A 产品” ，类似地修改“选项按钮 2”和“选项按钮 3”为“B 产品”和“C 产品” 。 （8）右击“A 产品”选项按钮，在弹出的快捷菜单中选择“设置控件格式”命令，弹出“设 置控件格式”对话框，如图 382 所示，切换到“控制”选项卡，设置“单元格链接”为$F$1， 值选中“已选择” ，单击“确定”按钮。注意，设置第一个选项按钮即“A 产品”选项按钮的“控 制”属性时，第二个和第三个选项按钮的属性也被自动设置，无须一个一个设置。
图 382 “设置控件格式”对话框 （9）按题意美化图表。 实训集 实训 1 工资表的制作 操作要求： 打开“工资管理.xlsx”文件，如图 383 所示。利用“员工基本工资表” 、 “员工出勤统计 表” 、 “员工福利表” 、 “员工奖金表”中的数据，应用 Excel 中的单元格引用及公式制作出“员 工工资表” ，如图 384 所示，并统计出各部门的工资总额。 图 383 “员工工资表”所需的源数据 提示： （1）奖金、住房补助、车费补贴、保险金、请假扣款列可利用函数 VLOOKUP 从其他工 作表中获取。 （2）扣税所得额为：如果应发工资少于 1000 元，扣税所得额为 0，否则扣税所得额为应 发金额减去 1000。 （3）个人所得税计算方法： 扣税所得额<500，个人所得税=扣税所得额×5%；
500<=扣税所得额<2000，个人所得税=扣税所得额×10%25； 2000<=扣税所得额<5000，个人所得税=扣税所得额×15%125。 图 384 “员工工资表”效果图 实训 2 数据统计 操作要求： 打开“课务统计.xlsx”文件，如图 385 所示为第 1 周的课务统计。 图 385 “课务统计”数据源
完成如下要求： （1）要求每一张工作表向下滚动时工作表字段和标题不会滚动。 （2）给“第 1 周”工作表的 E2 和 F2 两个单元格添加批注，内容分别为“由原 04V2 与 04V4 组成”和“由原 04V3 与 04V5 高考学生组成” 。 （3）在“第 1 周”到“第 4 周”工作表的 Z2 单元格处增加“合计”列，并用所学知识 填充相应数据。 （4）根据“月统计表”工作表的布局用所学 Excel 知识统计相应数据，效果如图 386 所示。 图 386 “课务统计”效果图 实训 3 数据分析 操作要求： 打开“数据分析.xlsx”文件，内有 3 张工作表：电脑销售记录表、工资表、产品销售记录 单，如图 387 所示，完成以下操作： （1）对“电脑销售记录表”中的记录按联想、IBM、方正等品牌顺序进行排序，同一品 牌按金额从高到低排列。 （2）将提供的“工资表”中的分类汇总取消，按照“职称”重新分类汇总，统计出各类 职称的“实发工资”总和，以及各类职称的最高工资。 （3）根据“产品销售记录单”中的数据筛选出“顾客”列中含有 Mart 且在 1980 年出生， 且“产品”列中第一个字母为 G 最后一个为 S 的产品数据清单，分别在 A26 单元格起始处和 原来区域显示出来。
图 387 “数据分析”数据源 实训 4 成绩分析 小蒋是一位中学教师，在教务处负责初一年级学生的成绩管理。由于学校地处偏远地区， 缺乏必要的教学设施，只有一台配置不太高的 PC 可以使用。他在这台计算机中安装了 Microsoft Office，决定通过 Exce1 来管理学生成绩，以弥补学校缺少数据库管理系统的不足。 现在，第一学期期末考试刚刚结束，小蒋将初一年级三个班的成绩均录入了文件名为“学 生成绩单”的 Exce1 工作簿文档中，如图 388 所示。 图 388 “成绩单”数据源 请根据下列要求帮助小蒋老师对该成绩单进行整理和分析，效果如图 389 所示。 （1）对工作表“第一学期期末成绩”中的数据列表进行格式化操作：将第一列“学号” 列设为文本，将所有成绩列设为保留两位小数的数值；适当加大行高列宽，改变字体、字号， 设置对齐方式，增加适当的边框和底纹以使工作表更加美观。 （2）利用“条件格式”功能进行下列设置：将语数英三科中不低于 110 分的成绩所在的 单元格以一种颜色填充，其他四科中高于 95 分的成绩以另一种颜色的字体标出，所用颜色深 浅以不遮挡数据为宜。
图 389 “成绩分析”效果图 （3）通过函数计算第一个学生的总分及平均分，以公式复制的形式得到其他人的总分及 平均分。 （4）从左往右数， “学号”列的第 3、4 位代表学生所在的班级，例如 120101，代表 12 级 1 班 1 号。请通过函数提炼出每个学生所在的班级并按下列对应关系填写在“班级”对应列 中，例如： “学号”的 3、4 位 对应班级 01 1 班 02 2 班 03 3 班 （5）复制工作表“第一学期期末成绩” ，将复本放置到原表的右侧；改变复本表标签的 颜色，并将复本重新命名，新表名需要包含“分类汇总”字样。 （6）通过分类汇总功能求出每个班各科的平均分，并将每组结果分页显示。 （7）以分类汇总结果为基础创建一个簇状柱形图，对每个班各科的平均分进行比较，并 将该图表放置在一个新工作表中。 实训 5 数据透视表的制作 操作要求： 打开“数据透视表.xlsx”文件，如图 390 所示。 这是某外贸公司的销售数据清单，清单以流水形式记录，清单中包括日期、商品编号、 销售额、销售员、国家等众多信息，时间跨度为 2007 年一整年，现要求： （1）按季度显示销售员的销售总额，并根据销售总额确定销售员的奖金，销售总额超过 30000（包括 30000）的销售员，每季度按销售总额的 10%计算，否则按销售总额的 5%计算， 最后适当美化统计结果，效果如图 391 所示。 （2）在 Sheet2 工作表中统计每位销售员的平均销售额（以货币形式显示，保留整数）， 按平均销售额降序排序并显示前 3 名销售员，且不同国家在不同页面显示，效果如图 392 所示。
图 390 “销售清单”数据源
图 392 数据透视表 2 实训 6 动态图表的制作 操作要求： 打开“动态图表的制作.xlsx”文件，根据提供的数据源制作如图 393 所示的各年费用支 出统计图，要求单击右上角的选项按钮图表即显示相应年份的费用支出情况。 图 393 动态“图表”示意图 释疑栏
