模块5--统计报表制作

模块5统计报表制作•5.1职业背景•5.2制作学生成绩统计表•5.3制作学生成绩汇总表•5.4制作公司员工工资表•5.5综合实战在日常工作与生活中，经常会用到各种二维表格，如学校中的课程表、成绩表、学籍表；企业中的工资表、销售数据统计表、生产管理表、商品价格表等。如果这些表格都需要笔画、手算，会给工作带来极大不便。而电子表格的使用，可以解决这种不便，如电子表格可以输入输出、显示数据，帮助用户制作各种复杂的表格文档，进行烦琐的数据计算，并能对输入的数据进行各种复杂统计运算后显示为可视性极佳的表格，同时它还能形象地将大量枯燥无味的数据变为多种彩色的商业图表，极大地增强了数据的可视性。因此，各行各业中，对于使用电子表格解决工作中的实际问题的能力要求，显得尤为重要。越来越多的职业岗位需要员工拥有利用软件熟练制作电子表格的职业能力，例如数据统计、分析结果、财务等应用领域，都对电子表格的制作能力提出了很高的要求。5.1.1职业背景5.1.2训练目标5.1.2训练目标（1）职业素质：遵纪守法，保守秘密；实事求是，讲求时效；忠于职守，谦虚谨慎；团结协作，爱护设备；爱岗敬业，无私奉献；服务热情，保守商业秘密。（2）熟练掌握电子表格软件的使用。（3）根据工作岗位的要求，保质保量地完成数据处理与计算工作。（4）根据录入的相关数据，进行数据统计与分析。（5）具备一定的数理统计知识。5.2制作公司销售统计表5.2.1任务目标本例制作如表5-1所示的销售统计表，并完成数据的处理与计算，计算每个销售员4个季度的销售总额，每季度所有销售员的平均销售额及销售员全年销售总额的平均值，统计每季度销售员销售额的最高值和最低值，统计每季度销售任务的完成情况（其中公司规定每季度每人的销售任务为4000元，凡个人季度销售额大于4000元的销售员），时间为20分钟。表5-1销售统计表销售统计表销售员姓名第一季度第二季度第三季度第四季度合计郭靖￥1,257￥2,433￥1,578￥1,687黄蓉￥3,981￥6,427￥5,312￥8,946杨过￥9,953￥8,969￥9,254￥9,637张无忌￥4,587￥3,569￥4,980￥4,056萧峰￥3,289￥3,199￥3,807￥3,932王宏￥9,784￥8,967￥9,164￥9,800宋平之￥6,579￥7,839￥5,804￥6,870平均销售额最高销售额最低销售额完成销售任务人数5.2制作公司销售统计表5.2.2工作流程（1）启动Excel，新建一工作簿，根据表5-1输入数据，如图5-1所示，其中销售数据所在单元格的格式设置为货币型，不保留小数。（2）计算每个销售员年度销售总额。在单元格F3中输入公式“=SUM(B3:E3)”。然后利用拖动填充方式，将此公式复制到单元格F4:F12中，完成所有销售员年度销售总额的计算。（3）计算销售员季度平均销售额及销售员年度销售总额的平均值。在单元格B10中输入公式“=AVERAGE(B3:B9)”。并将此公式复制到单元格C10:F10中，完成平均销售额的计算。图5-1销售统计表5.2制作公司销售统计表（4）统计每季度销售员最高销售额和最低销售额。在单元格B11中输入公式“=MAX(B3:B9)”，并将此公式复制到单元格C11:F11中，完成每季度最高销售额的统计；同时在单元格B12中输入公式“=MIN(B3:B9)”，并将此公式复制到单元格D12:F12中，完成每季度最低销售额的统计。（5）统计完成季度销售任务的人数情况。在单元格B13中输入公式“=COUNTIF(B3:B9,=4000)”，并将此公式复制到单元格C13:E13中，完成“完成销售任务”人数统计。（6）最终计算结果如图5-2所示。图5-2销售统计表计算结果5.2制作公司销售统计表5.2.3知识与技能一般公司为了进行销售数据管理，通常在某时段后，需要建立一销售数据管理表格，完成对销售员销售数据的统计与计算，如年度总销售额、季度平均销售额等。为了完成销售数据管理，首先需要对成绩管理表格进行设计，其中包括表格的功能与基本需要，从而可以安排表格中数据项目的次序与具体位置。数据表的设计需要确定表格数据项目的名称，一般都将数据项目放置在表格的第一行，然后在每一行表格中依次输入数据内容。对于已经录入到表格中需要进行处理的数据，一般依靠公式与函数来完成数据计算与统计分析。公式是进行数据计算与分析的等式，可以用它来完成数据的加、减、乘、除等运算。函数是一种特殊的公式，完成对数据表中制定区域的数据操作。因此，对成绩表中的总分、平均分、最高分、最低分等数据进行处理与分析，都可以利用公式或函数来完成相应的任务。5.3制作公司销售额汇总表5.3.1任务目标（1）制作如表5-2所示的销售额汇总表，完成数据的处理与计算，并按照总销售额对销售员进行排序，筛选出总销售额大于15000元的销售员，时间为10分钟。表5-2销售额汇总表销售额汇总表销售员姓名性别第一季度第二季度第三季度第四季度合计郭靖男￥1,257￥2,433￥1,578￥1,687￥6,955宋平之男￥6,579￥7,839￥5,804￥6,870￥27,092萧峰男￥3,289￥3,199￥3,807￥3,932￥14,227杨过男￥9,953￥8,969￥9,254￥9,637￥37,813张无忌男￥4,587￥3,569￥4,980￥4,056￥17,192黄蓉女￥3,981￥6,427￥5,312￥8,946￥24,666王宏女￥9,784￥8,967￥9,164￥9,800￥37,715（2）在上述销售额汇总表中，按照性别对平均分进行分类汇总，分别计算男女销售员的平均销售额，时间为5分钟。（3）在销售额汇总表中，为郭靖、萧峰、杨过3位销售员的各季度销售额进行对比分析，创建图表。5.3制作公司销售额汇总表5.3.2工作流程（1）启动Excel，新建一工作簿，根据表5-2输入数据，其中销售数据所在单元格的格式设置为货币型，不保留小数。（2）对销售员年度总销售额进行排序。选中数据表中任意一个单元格，选择菜单栏中的“数据→排序”命令，在弹出的“排序”对话框中将“合计”作为关键字，进行降序排列。最后，得到销售员年度总销售额由高到低的排序结果，如图5-3所示。图5-3年度销售额排序结果5.3制作公司销售额汇总表（3）筛选出总销售额大于15000元的销售员。选择数据表中任意一单元格，选择“数据→筛选→自动筛选”命令，选择“合计”筛选列，筛选方式为自定义，定义为“大于15000”。由此，将年度总销售额大于15000元的销售员筛选出来，如图5-4所示。图5-4年度销售额大于15000元的筛选结果5.3制作公司销售额汇总表（4）按照性别对平均销售额进行分类汇总。首先取消自动筛选，将数据按照“性别”为分类字段进行排序，然后选择菜单栏中的“数据→分类汇总”命令，在弹出的“分类汇总”对话框中，进行如图5-5所示的设置，完成对男女销售员各自平均销售额的计算。其结果如图5-6所示。（5）创建图表。在销售额汇总表中，为郭靖、萧峰、杨过3位销售员的销售额进行对比分析，分析3人年度总销售额与男销售员年总平均销售额的关系，创建柱形图图表，如图5-7所示。图5-5分类汇总设置5.3制作公司销售额汇总表（5）创建图表。在销售额汇总表中，为郭靖、萧峰、杨过3位销售员的销售额进行对比分析，分析3人年度总销售额与男销售员年总平均销售额的关系，创建柱形图图表，如图5-7所示。图5-6销售额分类汇总表5.3制作公司销售额汇总表￥0￥5,000￥10,000￥15,000￥20,000￥25,000￥30,000￥35,000￥40,000杨过萧峰郭靖男销售员年平均销售额杨过￥9,953￥8,969￥9,254￥9,637￥37,813萧峰￥3,289￥3,199￥3,807￥3,932￥14,227郭靖￥1,257￥2,433￥1,578￥1,687￥6,955男销售员年平均销售额￥20,656第一季度第二季度第三季度第四季度合计图5-7销售额统计图表5.3制作公司销售额汇总表5.3.3知识与技能公司销售经理通常在年底，对销售员销售额表进行分析，利用排序、筛选与分类汇总，可以非常方便地完成该项分析汇总工作。1.排序排序是将某个数据按照从小到大或由大到小的顺序进行排列。它是教师在进行数据分析和处理过程中，经常进行的一种操作。通过排序可以根据某特定列的内容来重新排列数据清单中的行。排序可以按照单关键字排序，也可以按照多关键字进行排序。2.筛选筛选是在数据表中查找满足需求条件的数据。当筛选一个数据集时，可以只显示出符合筛选条件的数据，而将无关数据暂时隐藏。筛选可进行自动筛选和高级筛选。5.3制作公司销售额汇总表3.分类汇总分类汇总建立在已排序的基础上，将相同类别的数据进行汇总分析，它可以对数据表中的某个字段提供“求和”、“求平均值”等汇总，实现对汇总值的计算，并将计算结果分类显示出来。4.图表图表是将工作表中的数据用图形化的方式显示出来的一种方式，图表是自动根据数据生成的，可以自动同步更新，它有嵌入式图表与图表工作表两种类型。5.4.1任务目标本例制作如表5-3所示的公司员工工资表，完成实发工资的计算，即实发工资=基本工资+岗位津贴-扣款，同时制作数据透视表，完成不同部门员工工资的对比分析，时间为15分钟。表5-3公司员工工资表公司员工工资表编号姓名部门基本工资岗位津贴扣款实发工资101郭靖采购部3,9502,500430102黄蓉销售部6,4003,500370103杨过工程部4,0503,000350104张无忌人事部3,2003,000240105萧峰工程部1,6002,000400106王宏人事部3,8002,400280107欧阳锋销售部2,2003,000320108李世民采购部1,5002,400200109张睿工程部2,4503,000200110马钰销售部6,8004,200420111李莫愁采购部3,5002,5001905.4制作公司员工工资表（1）启动Excel，新建一工作簿，根据表5-3输入数据，其中工资数据所在单元格的格式设置为数字型，使用千位分隔符，不保留小数，如图5-8所示。（2）计算员工实发工资。选中数据表中单元格G3:G13，添加公式“=D3+E3-F3”，完成实发工资计算。图5-8公司员工工资表5.4.2工作流程（3）添加数据透视表。首先选择要建立数据透视表的区域A2:G13，然后选择“数据→数据透视表和数据透视图”命令，弹出的对话框如图5-9所示，保留表中的默认选项，单击“下一步”按钮；弹出的对话框如图5-10所示，确定数据源区域＄A＄2:＄G＄13，单击“下一步”按钮；弹出的对话框如图5-11所示，选择新建工作表，单击其中的“布局”按钮，弹出对话框如图5-12所示，将字段名“姓名”拖动到“行”的位置，字段名“部门”拖动到“列”的位置，字段名“实发工资”拖动到“数据”位置，单击“确定”按钮。最终完成生成数据透视表，如图5-13所示。通过此透视表，可以非常方便地对比各部门员工的实发工资，辅助统计与分析。图5-9“数据透视表和数据透视图向导-3步骤之1”对话框5.4.2工作流程图5-10“数据透视表和数据透视图向导-3步骤之2”对话框图5-11“数据透视表和数据透视图向导-3步骤之3”对话框图5-12“数据透视表和数据透视图向导-布局”对话框5.4.2工作流程图5-13数据透视表5.4.2工作流程在发放工资时，人力资源经理会制作相应的工资明细表，完成工资计算。同时会利用数据透视表方式，针对公司员工工资收入做一定的统计分析。数据透视表是一种交互式统计报表，可以对大量数据进行快速汇总和建立交叉列表。创建数据透视表后，可以按照不同的需求，依据不同的关系来提取和组织各种数据。5.4.3知识与技能1.考核项目根据表5-4，制作一商品销售记录单，并完成相应的计算和统计分析任务。测试时间为30分钟。5.5综合实战2.考核具体要求（1）依据样表（见表5-4），制作商品销售记录单，计算出