任务15 利用表格对数据进行分析

第三章WPS电子表格处理任务15：利用表格对数据进行分析

合并计算、分类汇总、分级显示、数据透视表和数据透视图、单变量求解、规划求解01任务描述02任务实现03课后练习目录任务描述任务描述志愿服务是社会文明进步的重要标志，“奉献、友爱、互助、进步”的志愿服务精神，是社会主义核心价值观的重要体现。大学生参加志愿服务活动，不仅传递了爱心，传播了文明，也丰富了自己的生活体验，给自己提供了更多的锻炼机会。为进一步调动学生们参与志愿服务的积极性，计信学院就近段时间开展的各项志愿服务活动，对相关同学进行志愿服务的时长进行了统计，并利用WPS表格对统计数据进行分析和总结。考点分析1.掌握合并计算的操作步骤。2.通过分类汇总功能对数据进行分级显示。3.利用数据透视表和数据透视图对数据进行汇总、分析、浏览。4.掌握单变量求解的方法。5.掌握规划求解的方法。最终效果合并计算最终效果简单分类汇嵌套分类汇总最终效果分级显示数据透视表数据透视图最终效果单变量求解规划求解任务实现打开素材中的工作簿文件“学生志愿活动时长统计表”；选择“三项志愿活动总时长”工作表，选中“A1”单元格，表示合并计算结果从这个单元格开始放置。选择合并计算结果放置区域01单击功能区中的“数据”选项卡→“合并计算”按钮，弹出“合并计算”对话框。打开合并计算对话框02在“合并计算”对话框中，在“函数”下拉列表框中选择“求和”函数。单击“引用位置”参数框，单击工作表标签“学雷锋志愿活动时长统计”，选择单元格区域“A1:B7”，此区域是需要合并计算的区域，返回“合并计算”对话框，单击“添加”按钮，选定的合并计算区域显示在“所引用位置”列表框中，重复上述步骤，将工作表“植树节志愿活动时长统计”中的区域“A1:B7”和工作表“敬老院志愿活动时长统计”中的区域“A1:B7”，分别添加进“所引用位置”列表框中。在“标签位置”复选框中选中“首行”和“最左侧”两个复选框。合并计算操作03任务实现任务1：合并计算任务实现任务1：合并计算如果包含合并计算数据的工作表位于另一个工作簿中，可单击“引用位置”参数框最右边的“浏览”按钮，找到该工作簿，并选择相应的工作表区域。当所有合并计算数据所在的源区域具有完全相同的行列标签时，无需选中“标签位置”。当一个源区域中的标签与其他区域都不相同时，将会导致合并计算中出现单独的行或列。04合并计算需注意的点任务实现任务1：合并计算在工作簿文件“学生志愿活动时长统计表”中，选择“近三周各项活动志愿时长统计”工作表。选择数据区域“A1:D37”，单击功能区中的“数据”选项卡→“升序”按钮，对“学生姓名”列进行升序排序，按学生姓名列对数据排序01确保当前单元格在数据区域中，单击“数据”选项卡→“分类汇总”按钮打开“分类汇总”对话框中，在“分类字段”下拉菜单中选择“学生姓名”字段。在“汇总方式”下拉菜单中选择“求和”。在“选定汇总项”复选框中选择“志愿活动时长”复选框。选中“替换当前分类汇总”和“汇总结果显示在数据下方”两个复选框。单击“分类汇总”对话框下面的“确定”按钮，完成分类汇总，显示每位志愿者前三周的总志愿时长和六位志愿者的总志愿时长。对数据进行分类汇总02再次打开“分类汇总”对话框，单击“分类汇总”对话框下面的“全部删除”按钮，删除分类汇总结果。显示原表格。删除分类汇总结果03任务实现任务2：分类汇总简单分类汇总：显示每位志愿者前三周的总志愿时长和六位志愿者的总志愿时长任务实现任务2：分类汇总简单分类汇总：显示每位志愿者前三周的总志愿时长和六位志愿者的总志愿时长任务实现任务2：分类汇总简单分类汇总：显示每位志愿者前三周的总志愿时长和六位志愿者的总志愿时长任务实现任务2：分类汇总嵌套分类汇总：在简单分类汇总的结果上嵌套显示每位志愿者每周的志愿时长。在删除简单汇总的工作表“近三周各项活动志愿时长统计”中，选择数据区域“A1:D37”，单击功能区中的“数据”选项卡→“排序”按钮→“自定义排序”按钮，在“排序”对话框中，在“主要关键字”下拉列表框中选择“学生姓名”，在“排序依据”下拉列表框中选择“数值”，在“次序”下拉列表框中选择“升序”，单击“添加条件”按钮，在“次要关键字”下拉列表框中选择“志愿活动举办周次”，在“排序依据”下拉列表框中选择“数值”，在“次序”下拉列表框中选择“升序”，单击“确定”按钮。自定义排序01确保当前单元格在数据区域中，单击“数据”选项卡→“分类汇总”按钮，打开“分类汇总”对话框。在“分类字段”下拉菜单中选择“学生姓名”字段。在“汇总方式”下拉菜单中选择“求和”。在“选定汇总项”复选框中选择“志愿活动时长”复选框。选中“替换当前分类汇总”和“汇总结果显示在数据下方”两个复选框。单击“确定”按钮。再次打开“分类汇总”对话框。在“分类字段”下拉菜单中选择“志愿活动举办周次”字段。在“汇总方式”下拉菜单中选择“求和”。在“选定汇总项”复选框中选择“志愿活动时长”复选框。取消选中“替换当前分类汇总”复选框。单击“分类汇总”对话框下面的“确定”按钮，完成嵌套分类汇总，在显示每位志愿者前三周的总志愿时长和六位志愿者的总志愿时长的同时，又嵌套显示每位志愿者每周的志愿时长。对数据嵌套分类汇总02任务实现任务2：分类汇总嵌套分类汇总：在简单分类汇总的结果上嵌套显示每位志愿者每周的志愿时长。任务实现任务3：分级显示在删除简单汇总的工作表“近三周各项活动志愿时长统计”中，选择数在完成嵌套分类汇总的工作表“近三周各项活动志愿时长统计”中，单击分类汇总区域左上角的数字按钮“2”，此时可查看第2级的汇总结果。对分类汇总的数据进行分级显示01单击功能区中的“数据”选项卡中的“取消组合”下拉按钮，在打开的下拉列表中点击“清除分级显示”命令，删除分组显示。确保当前单元格在分类汇总数据区域中，再次打开“分类汇总”对话框，点击最下方的“全部删除”按钮，删除分类汇总结果，显示原表格。清除分级显示02在工作簿文件“学生志愿活动时长统计表”中选择已删除分类汇总的“近三周各项活动志愿时长统计”工作表选择单元格区域“A1:D37”，单击功能区“数据”选项卡→“数据透视表”按钮，打开“创建数据透视表”对话框。在打开的“创建数据透视表”对话框中，选择“请选择要分析的数据”单选按钮中的“请选择单元格区域”，在参数框中显示的单元格区域是已选中的工作表中的所有数据区域“A1:D37”。在“请选择放置数据透视表的位置”单选按钮中选择“新工作表”选项。单击“确定”按钮，完成空白数据透视表的创建。创建空白数据透视表01任务实现任务4：数据透视图和数据透视表数据透视表：灵活的分析和展现数据在“数据透视表”窗格中，在“字段列表”复选框中选择“学生姓名”、“志愿活动举办周次”和“志愿活动时长”三个字段，数据透视表将会自动汇总每位志愿者每周的志愿时长、每个志愿者三周总的志愿时长以及所有人三周总的志愿时长选择字段显示数据02任务实现任务4：数据透视图和数据透视表数据透视表：灵活的分析和展现数据在“数据透视表”窗格中，可以使用拖动的方法调整字段所在的区域，调整数据透视表的布局。按住鼠标左键选中“志愿活动举办周次”字段，将其拖动至“列”区域中。按住鼠标左键选中“志愿活动名称”字段，将其拖动至“筛选器”区域中，此时在数据透视表的最上方添加了用于筛选模块的报表字段“志愿活动名称”。单击报表筛选字段“志愿活动名称”旁边的下三角标志，在打开的下拉列表中选择“敬老院志愿活动”选项，单击“确定”按钮，数据透视表将只汇总各个志愿者“敬老院志愿活动”的志愿时长和总时长。拖动字段调整布局03任务实现任务4：数据透视图和数据透视表数据透视表：灵活的分析和展现数据选中数据透视表任意单元格，单击功能区中的“设计”上下文选项卡，选择“数据透视表样式”库中的“数据透视表样式3”，相应样式应用到当前数据透视表中。设计样式04任务实现任务4：数据透视图和数据透视表数据透视表：灵活的分析和展现数据选中数据透视表任意单元格，单击功能区中的“分析”上下文选项卡→“插入切片器”按钮，打开“插入切片器”对话框。在“插入切片器”对话框中选择“学生姓名”复选框，单击“确定”按钮，弹出“学生姓名”对话框。选择其中一位志愿者“高丽”，透视表中则只显示“高丽”的信息。单击“学生姓名”对话框右上角的“清除筛选器”按钮，可以清除筛选。使用切片器展示数据05任务实现任务4：数据透视图和数据透视表数据透视表：灵活的分析和展现数据选中数据透视表任意单元格，单击功能区中的“分析”上下文选项卡→“数据透视图”按钮。打开“图表”对话框，在“图表类型”列表框中选择“柱形图”，选择图表子类型“簇状”中的第一个柱形图。创建数据透视图06任务实现任务4：数据透视图和数据透视表数据透视图：用图表展示数据透视表数据点击选中的图表类型，数据透视图插入到当前数据透视表中，单击数据透视图中的“志愿活动名称”、“志愿活动举办周次”和“学生姓名”字段筛选器，可更改图表中显示的数据。更改显示的数据07任务实现任务4：数据透视图和数据透视表数据透视图：用图表展示数据透视表数据任务实现任务5：单变量求解在工作簿文件“学生志愿活动时长统计表”中选择“学期末志愿总时长”工作表。在“D2”单元格输入计算“学期末志愿总时长”的公式，“学期末志愿总时长”=每周平均志愿时长X本学期总周次01选中计算公式所在的单元格“D2”，单击功能区中的“数据”选项卡→“模拟分析”下拉菜单→“单变量求解”按钮，打开“单变量求解”对话框在打开“单变量求解”对话框中，单击“目标单元格”参数框，选择包含计算公式的单元格“D2”。“目标值”文本框中填入“高丽”同学的预期分数“1080”分。单击“可变单元格”参数框选择单元格“B2”。单击“确定”按钮，弹出“单变量求解状态”对话框。单击确定按钮，显示最终的计算结果“60”。02输入公式求解第一位同学任务实现任务5：单变量求解重复上述步骤，计算出其他志愿者为达到预期目标每周需要获得的平均志愿时长。重复操作过程求解所有同学03任务实现任务5：单变量求解任务实现任务6：规划求解在工作簿文件“学生志愿活动时长统计表”中选择“志愿时长组合”工作表。在“D2”单元格中输入公式“SUMPRODUCT(B2:B7,C2:C7)”，该公式表示用“B2”到“B7”的值分别乘以“C2”到“C7”的值，然后再相加。单元格“C2”到“C7”将分别存储数字“0”或者“1”，其中“0”表示不参加所对应的志愿活动，“1”表示参加所对应的志愿活动。01选中公式所在的单元格“D2”，单击功能区中的“数据”选项卡→“模拟分析”下拉菜单→“规划求解”按钮，打开“规划求解参数”对话框02输入公式打开规划求解对话框任务实现任务6：规划求解单击“设置目标”参数框，选择单元格“D2”。选择“目标值”单选框，并在右边的文本框中输入数值“24”。单击“通过更改可变单元格”参数框，选择数据区域“C2:C7”。点击“添加”按钮，打开“