Excel函数练习

销售部助理小王需要对公司上半年的产品销售情况进行统计分析,并根据年度销售计划的执行情况进行考核。按照以下要求完成工作:

1.在考生文件夹下,打开“Excel Material.xlsx”文件,另存为“Excel.xlsx”()。docx”是扩展名)。之后所有操作都以这个文件为准,否则不得分。

2.在“销售业绩表”工作表的“个人总销售额”列中,用公式计算每个销售人员从1月到6月的总销售额。

3.根据“个人销售总额”一栏中的统计数据,在“销售业绩表”工作表的“销售排名”一栏中用公式计算出销售排名。如果个人销售总额排名第一,则显示“第一”;如果个人总销售额排名第二,则显示“2号”;诸如此类。

4.在“月度统计”工作表中,用公式计算出1-6月的销售达标率,即销售金额在6万元以上的人数所占的比例,并填入“销售达标率”一行。要求以百分比格式显示计算数据,并保留两位小数。

5.在“月度统计”工作表中,通过公式计算出每个月的第1、2、3次销售业绩,分别填入“第1次销售业绩”、“第2次销售业绩”、“第3次销售业绩”对应的单元格中。要求使用人民币核算专用数据格式,保留小数点后2位。

6.根据“销售业绩”表中的数据详细信息,在“按部门统计”工作表中创建一个数据透视表,并将其放在单元格A1中。要求统计各部门的人员数量以及各部门销售额占总销售额的比例。透视表效果请参考“按部门统计工作表”中的示例。

7.在“销售评估”工作表中创建一个标题为“销售评估”的图表。有了这个图表,可以清晰的反映出A类产品和B类产品的月销售额以及与计划销售额的对比情况。关于图表效果,请参考销售评估工作表中的示例。

Excel函数练习题目-大盘站插图

逐步地

1.复制Excel材料,粘贴副本,并将其重命名为Excel。不用输入扩展名,直接打开Excel做题即可。

Excel函数练习题目-大盘站插图1

2.光标JBOY3乐队单元格→点击→自动求和。

Excel函数练习题目-大盘站插图2

Excel函数练习题目-大盘站插图3

3.用rank函数找到排名就行了。排名函数参数(排名数据、排名范围)

的排名数据为JBOY3乐队单元格,范围为JBOY3乐队:J46。

因为该数据是一个表,所以该区域将自动成为结构化引用。(通俗地说,如果这张桌子是一张桌子,那么面积就会变成相应的名称。做题的时候,直接选择区域就可以了。)

Excel函数练习题目-大盘站插图4

在题目中,要求表现出哪个数字的风格。我们需要在排名的基础上把“号”和“名”这两个字联系起来。使用连接器&只是这样做。数据需要双引号才能被识别。

Excel函数练习题目-大盘站插图5

4.求销售达标率。题目中已经给出了计算方法。即销售人数>:60000然后除以总人数

所以我们先来统计一下6万以上的人数。使用条件计数函数countif,即条件统计量。

countif函数的参数(条件区域,条件)

将光标定位到一月份的销售成功率上,输入函数名和一对括号,

Excel函数练习题目-大盘站插图6

然后在光标定位括号中输入参数,即(条件区,条件)

如图,一月份的数据列中,有几个大于60000,回车确认。

Excel函数练习题目-大盘站插图7

这时候的销售数是6万多,我们需要用它除以总销售数。

Excel函数练习题目-大盘站插图8

总人数从刚才的排名可以看出一共是44人,或者你可以用count函数来统计人数。为了简单起见,我们就把它除以44吧。最后选择右键设置单元格格式→百分比,两位小数。把2月到6月的数据直接填在右边。

Excel函数练习题目-大盘站插图9

5.我们用公式写出对应月份的前三个数据,也就是用公式返回前三个数据。

Excel函数练习题目-大盘站插图10

可以用大函数,也就是用哪个最大值来返回数组。

大型函数的参数(数据区,哪个数字)

例如,=large(A1:B6,1)表示返回A1:B6中的第一个最大值。

那么就可以找到这个问题的销量第一名,也就是返回一月数据列的第一大。

Excel函数练习题目-大盘站插图11

那么也可以找到第二位,返回一月数据列中第二大的数据,也就是第二位的数据。

Excel函数练习题目-大盘站插图12

同样,第三位是返回第三大数据。

Excel函数练习题目-大盘站插图13

右边直接填写剩余月份,然后选择这些数据,右键设置核算的单元格格式,保留两位小数。

Excel函数练习题目-大盘站插图14

6.光标位于部门统计表→插入→透视表→表格区的A1单元格,此处输入为:表1→确定。

Excel函数练习题目-大盘站插图15

标签是销售团队,值区域是销售团队和个人销售总额。

Excel函数练习题目-大盘站插图16

选择标题,并在编辑栏中更改名称。第一个改为部门,第二个改为销售团队数量,第三个改为各部门销售占比。

Excel函数练习题目-大盘站插图17

选择第三列→右键单击→值字段设置。

Excel函数练习题目-大盘站插图18

数值显示模式下的列百分比→汇总

Excel函数练习题目-大盘站插图19

7.选择数据区域→插入→图表。

Excel函数练习题目-大盘站插图20

选择组合图→两个堆积柱形图,并选择要在次坐标轴上检查的最后一个计划销售额。(我的软件是2016版,考试是2010版。功能位置不一样,需要自己找。)

Excel函数练习题目-大盘站插图21

选择计划销售→右键设置数据系列格式→调整分类间距。

Excel函数练习题目-大盘站插图22

填充设置为无填充、红色实线和适当的粗体边框线。

Excel函数练习题目-大盘站插图23