信息技术（拓展模块） 三维数字模型绘制+数字媒体创意 课件 模块5 数据报表编制

数据报表编制任务1创建原始数据报表模块5模块5数据报表编制模块5数据报表编制目录Contents5.1.1工作流程5.1.2知识与技能模块5数据报表编制│创建原始数据报表任务描述利用已掌握的数据创建和采集方法，创建原始的数据表格，为后续的学习和训练任务提供基础数据表格。Excel和WPS表格都可实现本模块的任务，此处以Excel为例。模块5数据报表编制│创建原始数据报表任务目标（1）创建如图5-1所示的员工薪资原始数据报表。（2）进行基本的格式设置。图5-1员工薪资原始数据报表模块5数据报表编制（1）启动电子表格，新建一个空白文档，在A1单元格中输入标题文字“员工薪资表”，在其他单元格输入相应内容。（2）将标题文字“员工薪资表”设置为宋体、18号、加粗，将A1:M1单元格区域合并后居中。（3）将字段名所在行的字体设置为加粗。5.1.1

工作流程模块5数据报表编制（4）选定“基本工资”和“奖金”数据区域，单击“开始”选项卡“数字”功能区下拉按钮，打开“设置单元格格式”对话框，在“数字”选项卡“分类”列表中选择“数值”，并将小数位数改为“2”，然后单击“确定”按钮，如图5-2所示。（5）将工作表名称修改为“薪资表”。（6）以“员工薪资”为文件名保存文档（工作簿）。5.1.1

工作流程图5-2

数字格式设置模块5数据报表编制以0开头的数字的输入方法：方法1：输入时可以先输一个单引号“'”（英文符号），然后输入具体的数字，本项目中“编号”字段内容即可按此方法输入。方法2：选中A列，单击“开始”选项卡“数字”功能区下拉按钮，打开“设置单元格格式”对话框，在“数字”选项卡“分类”列表中选择“文本”后单击“确定”按钮，如图5-3所示，然后便可以在表格中输入“编号”字段的相应内容了（将输入的数字当作文本处理）。5.1.2

知识与技能图5-3

“设置单元格格式”对话框模块5数据报表编制数据报表编制任务2完成基本数据计算模块5模块5数据报表编制模块5数据报表编制目录Contents5.2.1工作流程5.2.2知识与技能模块5数据报表编制│完成基本数据计算任务描述利用运算表达式，按要求对创建的原始表格数据进行适当的运算处理，完成相应的数据统计项目。模块5数据报表编制│完成基本数据计算任务目标（1）根据表格中“基本工资”和“奖金”总额，利用公式计算“住房公积金”（“基本工资”和“奖金”总额的5%）、“医疗保险金”（“基本工资”和“奖金”总额的2%）、“失业保险金”（“基本工资”和“奖金”总额的1%）、“养老保险金”（“基本工资”和“奖金”总额的8%）、“企业年金”（“基本工资”和“奖金”总额的4%）等5项数据。（2）假设“基本工资”和“奖金”总额扣除“住房公积金”“医疗保险金”“失业保险金”“养老保险金”“企业年金”即为实发工资，利用公式计算出“实发工资”。（3）计算各项目的总值和平均值。（4）计算其他分类项目数据。模块5数据报表编制│完成基本数据计算图5-1员工薪资原始数据报表模块5数据报表编制（1）启动电子表格，打开“员工薪资”工作簿，在“薪资表”工作表中选中H3单元格，输入公式“=(F3+G3)*0.05”，如图5-4所示，按【Enter】键后即可得到相应的计算值。5.2.1

工作流程图5-4输入公式模块5数据报表编制（2）再次选中H3单元格，按住单元格右下角的填充句柄不放，向下拖曳填充句柄至数据表本列的最后一行，即可获得所有员工的“住房公积金”数据，如图5-5所示。5.2.1

工作流程图5-5数据填充模块5数据报表编制（3）参照上面两步的操作方法，计算出“医疗保险金”“失业保险金”“养老保险金”“企业年金”等4项数据。（4）选中M3单元格，输入公式“=F3+G3-H3-I3-J3-K3-L3”，按【Enter】键后即可得到相应的“实发工资”数据。拖动M3单元格的填充句柄至本列末行即可完成全部员工的“实发工资”计算。（5）选中H3:M20单元格区域，利用如图5-2所示的“设置单元格格式”对话框，将所有计算出来的数据设置成“数值”类型并保留小数点后2位。（6）保存工作簿文件。5.2.1

工作流程模块5数据报表编制某列单元格内数据显示“#######”的处理方法如下。产生原因是该列中的数据长度超出了列宽，以致无法完整显示。此时只需将该列的列宽加大；或者选中该列后，选择“开始”选项卡“单元格”功能区“格式”下拉菜单中的“自动调整列宽”选项即可，如图5-6所示。5.2.2

知识与技能图5-6自动调整列宽模块5数据报表编制数据报表编制任务3函数的使用模块5模块5数据报表编制模块5数据报表编制目录Contents5.3.1工作流程5.3.2知识与技能模块5数据报表编制│函数的使用任务描述利用电子表格软件提供的函数功能，按要求对创建的原始表格数据进行适当的运算处理，完成相应的数据统计项目。模块5数据报表编制│函数的使用任务目标（1）熟悉本套教材基础模块（第4章）中常用函数的使用格式和参数说明。（2）熟练掌握求和函数SUM()、求平均值函数AVERAGE()、排名函数RANK()、条件函数IF()、条件计数函数COUNTIF()、条件求和函数SUMIF()等常用函数在实际工作中的灵活运用。模块5数据报表编制（1）启动电子表格，打开“员工薪资”工作簿，在“薪资表”工作表中，选取A21:E21单元格区域，合并后居中，并输入“合计”；选取A22:E22单元格区域，合并后居中，并输入“平均值”。（2）选择F21单元格，输入公式“=SUM(F3:F20)”后按【Enter】键，利用求和函数SUM()计算出“基本工资”的总额；再选中F21单元格，用鼠标按住填充句柄不放，向右拖动填充句柄到本行的末尾，完成其他各项合计值的计算。5.3.1

工作流程1.使用求和函数SUM()和求平均值函数AVERAGE()模块5数据报表编制（3）选择F22单元格，输入公式“=AVERAGE(F3:F20)”，利用求平均值函数AVERAGE()计算出“基本工资”的平均值；再选中F22单元格，用鼠标按住填充句柄不放，向右拖动填充句柄到本行的末尾，完成其他各项平均值的计算，如图5-7所示。5.3.1

工作流程图5-7求和、求平均值函数运算结果模块5数据报表编制（1）选中N2单元格，并输入“收入排名”。（2）选择N3单元格，输入公式“=RANK(M3,$M$3:$M$20,0)”后按【Enter】键，依据“实发工资”列的数据，利用排名函数RANK()计算出第一位员工“实发工资”的排名；再次选中N3单元格，用鼠标按住填充句柄不放，向下拖动填充句柄至本列最后一位员工所在的行，完成所有员工按“实发工资”的收入排名，如图5-8所示。此操作中使用了自动填充功能，所以在输入排名函数中的区域地址时采用了绝对地址引用方式，否则在自动填充时将发生错误。5.3.1

工作流程2．使用排名函数RANK()图5-8排名函数模块5数据报表编制（1）选中O2单元格，并输入“备注”。（2）选择O3单元格，输入公式“=IF(M3>=7500,"高收入","正常")”后按【Enter】键，依据“实发工资”列的数据，利用条件函数IF()进行判断，若“实发工资”大于或者等于7500，则在“备注”列显示“高收入”，否则在“备注”列显示“正常”。完成O3单元格计算后，再次选中O3单元格，用鼠标按住填充句柄不放，向下拖动填充句柄至最后一位员工所在的行，完成其他单元格的填充，如图5-9所示。5.3.1

工作流程3．使用条件函数IF()图5-9条件函数模块5数据报表编制（1）选择A24:B24单元格区域，合并后居中，并输入“男职工人数”；选择A25:B25单元格区域，合并后居中，并输入“女职工人数”。（2）选择C24单元格，输入公式“=COUNTIF(C3:C20,"男")”后按【Enter】键，利用条件计数函数COUNTIF()统计出男职工人数。按同样的方法，选择C25单元格，输入公式“=COUNTIF(C3:C20,"女")”，统计出女职工人数，如图5-10所示。5.3.1

工作流程4．使用条件计数函数COUNTIF()图5-10条件计数函数模块5数据报表编制（1）选择E24:F24单元格区域，合并后居中，并输入“男职工平均收入”；选择E25:F25单元格区域，合并后居中，并输入“女职工平均收入”。5.3.1

工作流程5．使用条件求和函数SUMIF()模块5数据报表编制（2）选择G24单元格，输入公式“=SUMIF(C3:C20,"男",M3:M20)/C24”后按【Enter】键，利用条件求和函数SUMIF()统计出男职工“实发工资”总额，再除以C24单元格内统计出来的男职工人数，即可得到男职工平均收入。以同样的方法，选择C25单元格，输入公式“=SUMIF(C3:C20,"女",M3:M20)/C25”后按【Enter】键，计算出女职工平均收入，如图5-11所示。5.3.1

工作流程图5-11条件求和函数模块5数据报表编制5.3.1

工作流程6．保存工作簿文件模块5数据报表编制在使用排名函数RANK()、条件函数IF()、条件计数函数COUNTIF()、条件求和函数SUMIF()等的过程中，会涉及用于判断和计算的单元格区域，包括在分母中引用的单元格地址，须仔细判断是否需要使用绝对地址，否则在进行数据填充时该地址会发生相应的变化，从而得到错误结果。绝对地址的引用有如下两种方法：（1）在列号和行号前手动添加“$”符号。（2）引用单元格后按【F4】键，所引用的单元格行号和列号前自动添加“$”符号。5.3.2

知识与技能１．绝对地址的灵活运用模块5数据报表编制引用同一工作簿中不同工作表的数据，格式为“工作表名!单元格地址”，如“Sheet2!A1”。若要在不同工作簿之间引用数据，则格式为“[工作簿名]工作表名!单元格地址”，如“=[ABC.XLSX]Sheet1!A1”。5.3.2

知识与技能2．不同工作表中数据的引用模块5数据报表编制数据报表编制任务4数据整理模块5模块5数据报表编制模块5数据报表编制目录Contents5.4.1工作流程5.4.2知识与技能模块5数据报表编制│数据整理任务描述利用电子表格软件提供的排序、筛选、分类汇总等数据统计功能，按要求完成相应的数据统计工作。模块5数据报表编制│数据整理任务目标（1）熟悉排序、筛选（包括高级筛选）、分类汇总等常用数据清单整理功能。（2）在实际工作中灵活运用上述数据清单整理功能。模块5数据报表编制（1）启动电子表格，打开“员工薪资”工作簿，在“薪资表”工作表中准确选取将要排序的数据清单范围，如图5-12所示，不需要参加排序的数据内容不能包括在选择范围内。5.4.1

工作流程1．数据排序图5-12排序范围选择模块5数据报表编制（2）单击“数据”选项卡“排序和筛选”功能区中的“排序”按钮，打开“排序”对话框。（3）在“排序”对话框中，选择“主要关键字”为“部门”，次序为“升序”；单击上方的“添加条件”按钮，然后设置“次要关键字”为“实发工资”，次序为“降序”，如图5-13所示。单击“确定”按钮，数据清单将首先按照“部门”的升序排列（依据汉字拼音首字母排序），在相同部门内再按“实发工资”的降序排列，如图5-14所示。5.4.1

工作流程图5-14排序后的数据清单图5-13“排序”对话框模块5数据报表编制（4）完成上述排序后，再重复进行一次排序操作，将“主要关键字”设置为“编号”，次序为“升序”，利用“排序”对话框中的“删除条件”按钮删除“次要关键字”，单击“确定”按钮，弹出“排序提醒”对话框，如图5-15所示。此案例中因编号列全部为文本型数据，因此该对话框中两个选项没有影响，可任选一个处理方式后单击“确定”按钮，数据清单便恢复到排序前的原始状态。5.4.1

工作流程图5-15“排序提醒”对话框模块5数据报表编制（1）单击数据表中需要进行筛选操作的任意一条记录的任一单元格。（2）单击“数据”选项卡“排序和筛选”功能区中的“筛选”按钮，此时，数据表中的每个列标题内的右边出现一个下拉按钮，单击“部门”列标题右边的下拉按钮，弹出下拉菜单，在该菜单的下方只选中“生产部”，单击“确定”按钮，即可筛选出仅包含“生产部”员工的数据清单，如图5-16所示。5.4.1

工作流程2．数据筛选图5-16筛选下拉菜单模块5数据报表编制（3）单击“实发工资”列标题右边的下拉按钮，在下拉菜单中选择“数字筛选”命令，再选择“小于”命令（如图5-17所示），打开“自定义自动筛选方式”对话框，在该对话框中按“实发工资”小于“6000”设置（如图5-18所示），单击“确定”按钮，即可筛选出“生产部”中“实发工资”小于“6000”的员工记录（如图5-19所示）。注意：筛选效果中，凡是进行过筛选操作的列标题右边的下拉按钮都改变为。5.4.1

工作流程图5-18“自定义自动筛选方式”对话框图5-17数字筛选模块5数据报表编制5.4.1

工作流程图5-19筛选结果模块5数据报表编制（4）单击“数据”选项卡“排序和筛选”功能区中的“清除”按钮，可显示数据表中的所有数据，此时列标题右侧的下拉菜单按钮仍在；再次单击“数据”选项卡“排序和筛选”中的“筛选”按钮，取消当前筛选状态，可将数据清单恢复至原始状态。5.4.1

工作流程模块5数据报表编制（1）单击“薪资表”工作表标签右边的“新工作表”按钮，在工作簿内新建一个工作表，并修改表名称为“高级筛选结果”。（2）选择“薪资表”工作表，在数据区域下面任意位置（本案例中选择F27:G29单元格区域）设置高级筛选的条件区域，设置筛选条件为“职称”为“职员”或者“收入排名”在15位以后（含15位），如图5-20所示。5.4.1

工作流程3．高级筛选图5-20设置高级筛选条件区域模块5数据报表编制（3）选择“高级筛选结果”工作表，单击“数据”选项卡“排序和筛选”功能区中的“高级”按钮，打开“高级筛选”对话框。首先选择“将筛选结果复制到其他位置”单选按钮，然后将光标置于“列表区域”后的文本框内，接着单击“薪资表”表标签，选择A2:O20单元格区域；用同样的方法，将对话框中“条件区域”设置为“薪资表”的F27:G29单元格区域；最后将“复制到”中的地址选择为“高级筛选结果”工作表的A1单元格，如图5-21所示。单击“确定”按钮，即从“高级筛选结果”工作表的A1单元格起显示筛选结果，如图5-22所示。5.4.1

工作流程模块5数据报表编制5.4.1

工作流程图5-22高级筛选结果图5-21“高级筛选”对话框模块5数据报表编制（1）在工作簿中插入一张新表并改名为“分类汇总”，将“薪资表”中A1:O20单元格区域的数据复制到“分类汇总”工作表。（2）选择“分类汇总”工作表为当前工作表，单击数据表中的任意一条记录的任一单元格。（3）以分类字段“部门”为依据对表格数据进行排序，使相同部门的记录连续排列在一起。（4）单击“数据”选项卡“分级显示”功能区中的“分类汇总”按钮，打开“分类汇总”对话框，可在该对话框中设置相应内容，如图5-23所示。（5）单击“确定”按钮，将显示分类汇总结果，如图5-24所示。5.4.1

工作流程4．分类汇总图5-23“分类汇总”对话框模块5数据报表编制5.4.1

工作流程图5-24“分类汇总”结果模块5数据报表编制高级筛选的条件区域设置可以分为两种情况：若条件之间是“与”的关系（即“并且”的关系），则各条件在同一行上设置；若条件之间是“或”的关系（即“或者”的关系），则各条件在不同行上进行设置，如本案例中所示。5.4.2

知识与技能1．高级筛选条件区域设置模块5数据报表编制（1）在进行分类汇总操作前，必须以分类字段为依据对数据清单进行排序，使分类字段值相同的记录连续排列在一起，然后才能完成后续的分类汇总操作，否则将出现错误的结果。（2）分类汇总界面的左上角有3个分级显示按钮。单击第1级按钮，只显示总的分类汇总结果，不显示明细；单击第2级按钮，只显示总的汇总结果和各分类的汇总结果，不显示明细；单击第3级按钮，数据表将全部显示出来。5.4.2

知识与技能2．分类汇总模块5数据报表编制数据报表编制任务5

数据分析模块5模块5数据报表编制模块5数据报表编制目录Contents5.5.1工作流程5.5.2知识与技能模块5数据报表编制│数据分析任务描述利用电子表格软件提供的数据透视表工具，对数据表格按多个类别进行重新布局和分类汇总，对不同类别之间的数据进行比较和分析。模块5数据报表编制│数据分析任务目标（1）了解电子表格常用的数据分析手段和方法。（2）掌握数据透视表、合并计算等基本的数据分析技能。模块5数据报表编制（1）启动电子表格，打开“员工薪资”工作簿，选择“薪资表”工作表，单击“插入”选项卡“表格”功能区中的“数据透视表”按钮，打开“创建数据透视表”对话框，如图5-25所示。5.5.1

工作流程1．数据透视表图5-25“创建数据透视表”对话框模块5数据报表编制（2）在“创建数据透视表”对话框中，首先选择用于创建数据透视表的数据清单（本案例选择“薪资表”中的A2:O20单元格区域），然后选择数据透视表的放置位置（本案例选择“新工作表”），单击“确定”按钮，将在新工作表中进入数据透视表创建界面，如图5-26所示。5.5.1

工作流程图5-26数据透视表创建界面模块5数据报表编制（3）在界面右侧“选择要添加到报表的字段”区域中，将“部门”字段拖到“行标签”区域，将“职称”字段拖到“列标签”区域，将“实发工资”字段拖到“数值”区域，即可得到相应的数据透视表，如图5-27所示。（4）将存放数据透视表的工作表标签改为“数据透视”。5.5.1

工作流程图5-27数据透视表效果模块5数据报表编制（1）在“员工薪资”工作簿中插入一张新工作表，并重命名为“部门合并计算”。（2）在“部门合并计算”工作表中选择要存放“合并计算”结果的单元格，如A1单元格。（3）单击“数据”选项卡“数据工具”功能区中的“合并计算”按钮，打开“合并计算”对话框。在该对话框的“函数”列表中选择“求和”命令，单击“引用位置”右边的折叠按钮，选取“薪资表”的E2:M20单元格区域为合并计算区域，再次单击“折叠”按钮返回对话框，单击“添加”按钮将选择的引用位置添加到“所有引用位置”栏中。选中“标签位置”区域的“首行”和“最左列”复选框（如图5-28所示）。5.5.1

工作流程2．合并计算图5-28“合并计算”对话框模块5数据报表编制5.5.1

工作流程图5-29合并计算的结果（4）单击“确定”按钮，完成合并计算。选中结果区域，进行“自动调整列宽”操作后，即可看到所有数据合并计算的结果，如图5-29所示。模块5数据报表编制数据透视表是一种可以快速汇总大量数据的交互式方法。使用数据透视表可以深入分析数值数据，并且可以回答一些预料不到的数据问题。数据透视表是专门针对以下用途设计的：（1）以多种用户友好方式查询大量数据。（2）对数值数据进行分类汇总和聚合，按分类和子分类对数据进行汇总，创建自定义计算和公式。（3）展开或折叠要关注结果的数据级别，查看感兴趣区域汇总数据的明细。（4）将行移动到列或将列移动到行（或“透视”），以查看源数据的不同汇总。（5）对最有用和最关注的数据子集进行筛选、排序、分组以及有条件地设置格式，方便用户关注所需的信息。（6）提供简明、有吸引力并且带有批注的联机报表或打印报表。如果要分析相关的汇总值，尤其是在合计较大的数字列表并对每个数字进行多种比较时，通常使用数据透视表。5.5.2

知识与技能模块5数据报表编制数据报表编制任务6可视化数据分析模块5模块5数据报表编制模块5数据报表编制目录Contents5.6.1工作流程5.6.2知识与技能模块5数据报表编制│可视化数据分析任务描述利用电子表格软件提供的图表工具，直观形象地将表格中的数据以图形的形式展现，达到可视化分析的目标。模块5数据报表编制│可视化数据分析任务目标（1）了解电子表格常用图表制作手段和方法。（2）掌握图表中丰富的数据表达和比较分析功能。模块5数据报表编制（1）启动电子表格，打开“员工薪资”工作簿，选择“部门合并计算”工作表为当前工作表。（2）在“部门合并计算”工作表中选择A1:C5单元格区域，将其作为创建图表所需的数据源。（3）单击“插入”选项卡“图表”功能区中的“插入柱形图或条形图”按钮，打开其下拉列表，单击“三维簇状柱形图”按钮，生成图表，如图5-30所示。5.6.1

工作流程1．创建图表图5-30三维簇状柱形图模块5数据报表编制选中生成的图表后，会在电子表格的菜单选项卡末尾出现“图表工具”选项区，利用“图表工具”中的相应工具，可以调整已生成图表的对应组成元素，如图5-31所示。5.6.1

工作流程2．调整图表布局图5-31图表工具模块5数据报表编制（1）单击图表区空白处选中图表，单击“图表工具”选项区“设计”选项卡“类型”功能区中的“更改图表类型”按钮，可以将已生成的图表更改为其他类型，如“簇状柱形图”，如图5-32所示。（2）单击“图表工具”选项区“设计”选项卡“数据”功能区中的“切换行/列”按钮，可以将数据源中的行、列数据在图表中交换位置显示，以得到不同的数据比较效果，如图5-33所示。5.6.1

工作流程图5-32簇状柱形图图5-33切换行/列模块5数据报表编制（3）将图表切换回如图5-32所示的行列状态，单击“图表标题”为图表添加标题“部门薪资汇总”，如图5-34所示。（4）单击“图表工具”选项区“设计”选项卡“图表布局”功能区中的“添加图表元素”按钮，打开其下拉菜单，选择“图例”→“右侧”，可将图例移到图表右侧，如图5-35所示。5.6.1

工作流程图5-34添加图表标题图5-35在右侧显示图例模块5数据报表编制（5）单击“图表工具”选项区“设计”选项卡“图表布局”功能区中的“添加图表元素”按钮，打开其下拉菜单，选择“数据标签”→“数据标注”，可在图表上显示具体的数据值，如图5-36所示。5.6.1

工作流程图5-36显示数据标签模块5数据报表编制（6）选中图表标题，单击“图表工具”选项区“格式”选项卡“艺术字样式”功能区中的艺术字样式的下拉列表，选择其中第三行第一列的艺术字样式，将图表标题设置成艺术字样式，如图5-37所示。5.6.1

工作流程图5-37设置艺术字样式模块5数据报表编制对于生成的图表，可以打开“图表工具/设计”子选项卡，单击“类型”功能区中的“更改图表类型”按钮，弹出“更改图表类型”对话框，根据实际需求，选择更为直观的图表类型，灵活地满足从不同角度分析比较数据的需求。5.6.2

知识与技能模块5数据报表编制数据报表编制任务7联机协作编辑电子表格模块5模块5数据报表编制模块5数据报表编制目录Contents5.7.1工作流程5.7.2知识与技能模块5数据报表编制│联机协作编辑电子表格任务描述通过局域网环境下的共享工作机制，学习使用共享文档的方式实现多人共同编辑电子表格文档，完成团队协作任务。模块5数据报表编制│联机协作编辑电子表格任务目标通过“共享工作簿”实现多人同时协作编辑同一个电子表格。模块5数据报表编制（1）打开需要协作编辑的电子表格，单击“审阅”选项卡“更改”功能区中的“共享工作簿”按钮，打开“共享工作簿”对话框，在“编辑”选项卡中选择“允许多用户同时编辑，同时允许工作簿合并”复选框，如图5-38所示。5.7.1

工作流程1．工作簿共享设置图5-38“共享工作簿”对话框“编辑”选项卡模块5数据报表编制（2）切换到“高级”选项卡，将“自动更新间隔”设置为最小值5分钟，然后选中“保存本人的更改并查看其他用户的更改”单选按钮，如图5-39所示。单击“确定”按钮，将弹出“此操作将导致保存文档。是否继续？”提示框，单击“确定”按钮。5.7.1

工作流程图5-39“共享工作簿”对话框“高级”选项卡模块5数据报表编制（3）上述操作完成后，标题栏中工作