计算机应用基础-任务5.3 制作数据分析报表(0208)

9．数据图表：用分离型三维饼图显示每个销售代表销售总
金额额所占比例。
返回任务主菜单
任务要求
10．为"销售表"建立一个数据透视表，要求如下：透视表位置
：新工作表中；页字段：销售日期；列字段：销售代表；行字 段：类别、品名；数据项：金额（求和项）。 11．数据透视表：对比张默和宋晓两个销售代表的销售情况。 12．数据透视表：查询每个季度最畅销的三种产品的品名。
返回任务主菜单
IT工作室
4．用数据透视表来查找整个公司工资最高的前十个员工
，具体要求如下：
（1）透视表位置：新工作表中； （2）行字段：姓名； （3）数据项：岗位工资、薪级工资、绩效工资、应发工 资、住房公积金、养老保险金、失业保险、工会会费、所 得税、实发工资等。 将工作表重命名为“工资前十名”。
返回任务主菜单
1．统计各个系列各种型号的销售情况
为"销售表"建立一个数据透视表，要求
如下：透视表位置在新工作表中；页字段为
销售日期；列字段为销售代表；行字段为类
别、品名；数据项为金额（求和项）。
返回任务主菜单
1．统计各个系列各种型号的销售情况
返回任务主菜单
1．统计各个系列各种型号的销售情况
大于 table_array 的列数，函数 VLOOKUP 返回错误值 #REF!。
返回任务资讯
任务资讯：VLOOKUP函数
Range_lookup为一逻辑值，指明函数 VLOOKUP 查找 时是精确匹配，还是近似匹配。如果为false或0 ，则返回 精确匹配，如果找不到，则返回错误值 #N/A。如果 range_lookup 为TRUE或1，函数 VLOOKUP 将查找近
返回任务主菜单
IT工作室
5．用数据透视表来分析每个部门的工资情况，具体要求如下： （1）透视表位置：新工作表中； （2）页字段：月份；
（3）行字段：部门；
（4）数据项：岗位工资、薪级工资、绩效工资、应发工资 、住房公积金、养老保险金、失业保险、工会会费、所得 税、实发工资等，均统计所有数据的平均值。 将工作表重命名为“分析各部门的平均工资”。
归纳总结 IT工作室
返回任务主菜单
5.3.3.1函数计算数据
1．用VLOOKUP函数计算“单价”
所有货物的价格均在[价格表]中，在[销
售记录]表中直接显示对应型号的货物的单
价。
返回任务主菜单
1．用VLOOKUP函数计算“单价”
返回任务主菜单
2．用IF函数计算“折扣”
返回任务主菜单
2．用IF函数计算“折扣”
返回任务资讯
务资讯：数据透视图
数据透视图以图形形式表示数据透视表中的数据
。正如在数据透视表中那样，可以更改数据透视
图的布局和数据。数据透视图与数据透视表不同 的地方在于它可以选择适当的图形、多种色彩来 描述数据的特性。
返回任务资讯
任务小结
本任务以以制作销售数据统计分析表为工作任务，详细介
Sum_range 为实际求和区域，需要求和的单元格、区域或引
用。当省略第三个参数时，则条件区域就是实际求和区域。
返回任务资讯
任务资讯：数据透视表
数据透视表是一种交互式工作表，用于对现有工作
表进行汇总和分析。创建数据透视表后，可以按不
同的需要，依不同的关系来提取和组织数据。
返回任务资讯
任务资讯：数据透视表
（销售金额=零售价*数量）。
返回任务主菜单
5.3.3.2 分析比较数据
返回任务主菜单
1．用Sumif函数计算各销售代表总金额
多表计算：在“销售总表”中利用函数直
接计算三位销售代表的销售总金额。
返回任务主菜单
1．用Sumif函数计算各销售代表总金额
返回任务主菜单
操作练习
在“销售总表”中利用函数计算总销售金额；
工作任务三：

制作销售数据分析表
销售业绩是反映运行情况的晴雨表，销售部经理 要求小文将该公司的产品销售情况汇总，制作一 份销售分析情况表，为下一步的销售策略提供决 策依据。
任务5.3 制作数据分析报
表
任务5.3 制作数据分析报表
任务描述 任务要求 任务完成效果 解决方案
任务重点
任务 实施
在“销售总表”中，对“销售代表总金额”列中
的所有数据设置成“使用千分位分隔符”，并
保留1位小数。
返回任务主菜单
2．用饼图来分析比较各销售代表的销售情况 用分离型三维饼图显示每个销售代表销售总
金额所占比例。
返回任务主菜单
2．用饼图来分析比较各销售代表的销售情况
返回任务主菜单
5.2.3.3 透视分析数据
似匹配值，也就是说，如果找不到精确匹配值，则返回小
于 lookup_value 的最大数值。
返回任务资讯
任务资讯：条件求和函数SUMIF函数
（1）函数的功能：根据指定条件对若干单元格、区域或引 用求和。 （2）函数语法：=SUMIF(range，criteria，sum_range) （3）函数的参数如下： Range为条件区域，用于条件判断的单元格区域。 Criteria是求和条件，由数字、逻辑表达式等组成的判定条件。
工作任务
工作任务一 ：

制作公司员工信息表
公司新产品销售业绩节节攀升，销售队伍不断 壮大，公司要求小文重新制作公司员基本工信 息表。
工作任务
工作任务二：

制作公司员工工资表
到了月底，需要核发员工工资，财务部经理要求 小文按照国家规定的“五险一金”的标准制作公司 员工工资表。
工作任务
号。col_index_num 为 1 时，返回 table_array 第一列的
数值，col_index_num 为 2 时，返回 table_array 第二列 的数值，以此类推。如果 col_index_num 小于1，函数
VLOOKUP 返回错误值 #VALUE!；如果 col_index_num
返回任务主菜单
2．对比两个销售代表的销售情况
对比张默和宋
晓两位销售代表
的销售情况。
返回任务主菜单
3．查询每个季度最畅销的三种产品
查询每个季
度最畅销的三种
产品的品名。
返回任务主菜单
5.2.3.4 图表分析数据
返回任务主菜单
1．用图表分析数据
用“数据透视图”分析各系列按品名分类的各种 商品的销售情况。
工作能力
作为公司文员在文档编辑与排版方面，具备以下工作能力：
能运用文档结构与编辑排版知识阅读分析文档资料。 能利用Word编辑功能对不同格式的公文、报告等文档进 行录入与排版。 能利用Word样式功能编辑排版长文档资料。 能利用Word表格处理功能编辑制作各种常用表格文档。 能利用Word图表处理功能编辑制作图文并茂的宣传资料。
返回任务主菜单
任务资讯
VLOOKUP函数 条件求和函数SUMIF函数
数据透视表 数据透视图
返回任务主菜单
任务资讯：VLOOKUP函数
（1）函数的功能：查找数据区域首列满足条件的元素 ，并返回数据区域当前行中指定列处的值。 （2）函数的语法规则如下：
VLOOKUP(
lookup_value ,
模块5 数据统计与分析
工作能力
任务：公司日常管理
人物：小文（公司文员） 工作内容：
需要收集与编制大量与生产、销售、人事、 财务等相关数据资料，
需要对这些数据资料进行统计分析，提炼 有价信息，编制数据分析报表或图表
工作性质：为市场销售、产品研发、决策 管理等部门或领导提供支持和服务。
绍透视表和图表的操作技巧。 通过本任务的学习和训练，提高学生利用数据透视表和图 表灵活地改变源数据表的布局结构，从多个角度观察表中 数据间的关系，从而得出有用的结论的能力，能独立完成 如业绩考核表、评奖评优表、销售数据分析表等多数据信 息表的分析工作。
返回任务主菜单
IT工作室
打开任务5.2制作的员工工资表.xlsx，完成如下操作： 1．在数据表的最后一列（即领取签名列）前插入一列，命名为“ 排名”，用Rank函数，计算出每个员工在本月中的收入排名。 提示：RANK(number,ref,order)，表示返回一个数字在数字列 表中的排位。 Number：为需要找到排位的数字。 Ref：为数字列表数组或对数字列表的引用。Ref 中的非数值型 参数将被忽略。 Order：为一数字，指明排位的方式。如果 order 为 0（零）或 省略，Microsoft Excel 对数字的排位是基于 ref 为按照降序排 列的列表。 如果 order 不为零，Microsoft Excel 对数字的排位 是基于 ref 为按照升序排列的列表。
返回任务主菜单
1．用图表分析数据
返回任务主菜单
操作练习
用“数据透视图”分析每个季度的销售情况。
“分析每个季度的销售情况”操作效果图 返回任务主菜单
5.3.4 项目拓展
返回任务主菜单
用函数来计算称呼
用函数求出每个销售代表的称呼，如称[
张默]为[张先生]。
返回任务主菜单
用函数来计算称呼
返回任务主菜单
任务重点
本任务学习和技能训练的重点是根据公式计算结 果，利用数据透视表查看报表，利用数据图表直
观对数据进行统计分析。
返回任务主菜单
任务实施
实现方法
5.3.3.1 函数计算数据
5.3.3.2 分析比较数据 5.2.3.3 透视分析数据 5.2.3.4 用图表分析数据 项目拓展
任务资讯
3．利用公式计算各行折扣后的零售价：零售价=单价*(1折扣)。
4．自己编写公式计算每种型号的销售金额：销售金额=零
售价*数量。
返回任务主菜单
任务要求
5．用LEFT和IF函数来求出每个销售代表的称呼，如称[张默 ]为[张先生]
6．多表计算：在“销售总表”中利用函数直接计算三位销售
代表的销售总金额。 7．求和计算：在“销售总表”中利用函数计算总销售金额； 8．设置格式：在“销售总表”中，对“销售代表总金额”列中 的所有数据设置成“使用千分位分隔符”，并保留1位小数。
任务资讯 任务归纳总结 IT工作室
任务描述
为了准确地反映产品的销售业绩，经理要求 小文将近两年的销售情况进行汇总，形成数
据分析报告，为制定销售战略提供依据。
返回任务主菜单
任务要求
1．用VLOOKUP函数实现：所有货物的价格均在[价格表] 中，在[销售记录]表中直接显示对应型号的货物的单价。 2．利用IF函数填入折扣数据：所有单价为1000元（含 1000元）以上的折扣为5%，其余折扣为3%。
返回任务主菜单
IT工作室
2．制作设计部职工“张力”的收入结构图表；图表类型是“饼 图”，图表标题是[张力收入结构图]；在新工作表中生成图表 ，并将新工作表命名为：张力收入结构图。 3．制作各部门“基本工资”、“实发工资”平均值图表；图表类 型是“簇状柱形图”，图表标题是“各部门工资对比图”，数值 轴标题是“元”；生成图表工作表、取名“部门工资对比图”； 编辑及格式化图表： （1）图表标题及数值轴标题均设置为隶书、20号、蓝色； （2）数值轴刻度最小值500，最大值4000；主要刻度单位300。
table_array ,
col_index_num ,
range_lookup )
（3）函数的参数如下： Lookup_value为需要在数据表第一列中进行查找的数 值。Lookup_value 可以为数值、引用或文本字符串。
返回任务资讯
任务资讯：VLOOKUP函数
Table_array为需要在其中查找数据的数据表。使用对区域 或区域名称的引用。注意：要查找的值一定要定义在要查 找的区域的第1列。 col_index_num为table_array 中待返回的匹配值的列序
13．用“数据透视图”分析各系列按品名分类的各种商品的销售
情况。 14．用“数据透视图”分析每个季度的销售情况。
返回任务主菜单
任务完成效果
返回任务主菜单
解决方案
小文分析经理布置的任务，发现自己有所有销售员工的 订单数据、销售记录和产品价格表，可以在Excel中通 过函数计算、数据透视表、数据透视图和数据图表等方 式，形象直观对数据进行统计分析。
利用函数填入折扣数据：所有单价为
1000元（含1000元）以上的折扣为5%，其
余折扣为3%。
返回任务主菜单
2．用IF函数计算“折扣”
返回任务主菜单
3．用公式计算“零售价”
利用公式计算各行折扣后的零售价（零售价=
单价*(1-折扣)）。
返回任务主菜单
操作练习
自己编写公式计算每种型号的销售金额