Excel函数练习题目

销售部助理小王需要针对公司上半年产品销售情况进行统计分析，并根据全年销售计划执行进行评估。按照如下要求完成该项工作：

1.在考生文件夹下，打开“Excel素材.xlsx”文件，将其另存为“Excel.xlsx”(“.docx”为扩展名)，之后所有的操作均基于此文件，否则不得分。

2.在“销售业绩表”工作表的“个人销售总计”列中，通过公式计算每名销售人员1月～6月的销售总和。

3.依据“个人销售总计”列的统计数据，在“销售业绩表”工作表的“销售排名”列中通过公式计算销售排行榜，个人销售总计排名第一的，显示“第1名”;个人销售总计排名第二的，显示“第2名”;以此类推。

4.在“按月统计”工作表中，利用公式计算1月～6月的销售达标率，即销售额大于60000元的人数所占比例，并填写在“销售达标率”行中。要求以百分比格式显示计算数据，并保留2位小数。

5.在“按月统计”工作表中，分别通过公式计算各月排名第1、第2和第3的销售业绩，并填写在“销售第一名业绩”、“销售第二名业绩”和“销售第三名业绩”所对应的单元格中。要求使用人民币会计专用数据格式，并保留2位小数。

6.依据“销售业绩表”中的数据明细，在“按部门统计”工作表中创建一个数据透视表，并将其放置于A1单元格。要求可以统计出各部门的人员数量，以及各部门的销售额占销售总额的比例。数据透视表效果可参考“按部门统计”工作表中的样例。

7.在“销售评估”工作表中创建一标题为“销售评估”的图表，借助此图表可以清晰反映每月“A类产品销售额”和“B类产品销售额”之和，与“计划销售额”的对比情况。图表效果可参考“销售评估”工作表中的样例。

对 应 步 骤

1、复制Excel素材，粘贴一份，重命名为Excel。不用输入扩展名，打开Excel做题即可。

2、光标定位J3单元格→单击→自动求和

3、求排名用rank函数即可。rank函数参数(排名的数据，排名的范围)

排名的数据为J3单元格，范围为J3:J46。

因为此数据为表格，所以区域会自动变成结构化的引用。(通俗的来说就是这个表如果是表格的话，那么区域会变成相应的名称，做题的时候直接选择区域就行)

题中要求显示第几名的样式，我们需要在排名后的基础上链接“第”和“名”这两个字。用连接符&即可。文本数据需要加双引号才能识别。

4、求销售达标率，题中已给出计算方法。即销售人数>60000 然后除以总人数

所以我们先计算大于60000的人数。用条件计数函数countif，即有条件的统计数量

countif函数参数(条件区域，条件)

光标定位一月份的销售达标率，输入函数名称和一对括号，

接着光标定位括号里面输入参数，即(条件区域，条件)

如图所示，统计一月份的数据列中，有几个是大于60000的，回车确定

这时候求出来的是大于60000的销售人数，我们还需要除以总人数

总人数从刚才的排名就能看出来一共是44个人，或者用count函数统计数量也可以。为了简单咱们直接除以44即可。最后选中右击设置单元格格式→百分比，两位小数。2-6月的数据直接向右填充即可

5、这道题让我们用公式写出来写出来对应的月份的前三名的数据，即用公式返回前几名的数据

可以用一个large函数，即返回数组用第几个最大值。

large函数参数(数据区域，第几个)

比如=large(A1:B6，1)含义就是返回A1:B6这个区域中第一个最大的。

那么本题中销售第一名即可求，即返回一月份数据列中第1个最大的。

那么第二名也可以求，返回一月份数据列中第2个最大的，也就是第二名的数据

同理，第三名，就是返回第3个最大的数据

剩下的月份直接向右填充即可，然后选中这些数据，右击设置单元格格式为会计专用，保留两位小数

6、光标定位按部门统计表的A1单元格→插入→数据透视表→表区域那里输入：表1→确定

行标签为销售团队，值区域为销售团队和个人销售总计

选择标题，在编辑栏修改名称，第1个改为部门，第2个改为销售团队人数，第3个改为各部门所占销售比例

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

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

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

选择组合图表→都选择堆积柱形图，选择最后一个计划销售额勾选在次坐标轴。(我的软件是2016版本，考试为2010版本，功能位置不一样，需要大家自己找一下)

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

填充设置为无填充，红色实线，适当加粗边框线。