Excel函数操作题第16套

Excel函数操作题第16套

某公司销售部门主管大华拟对本公司产品前两季度的销售情况进行统计，按下述要求帮助大华完成统计工作：

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

2. 参照“产品基本信息表”所列，运用公式或函数分别在工作表“一季度销售情况表”、“二季度销售情况表”中,填入各型号产品对应的单价，并计算各月销售额填入F列中。其中单价和销售额均为数值、保留两位小数、使用千位分隔符。(注意：不得改变这两个工作表中的数据顺序)

3. 在“产品销售汇总表”中,分别计算各型号产品的一、二季度销量、销售额及合计数，填入相应列中。所有销售额均设为数值型、小数位数0，使用千位分隔符，右对齐。

4. 在“产品销售汇总表”中，在不改变原有数据顺序的情况下，按一二季度销售总额从高到低给出销售额排名，填入I列相应单元格中。将排名前3位和后3位的产品名次分别用标准红色和标准绿色标出。

5. 为“产品销售汇总表”的数据区域A1:I21套用一个表格格式，包含表标题，并取消列标题行的筛选标记。

6. 根据“产品销售汇总表”中的数据，在一个名为“透视分析”的新工作表中创建数据透视表，统计每个产品类别的一、二季度销售及总销售额，透视表自A3单元格开始、并按一二季度销售总额从高到低进行排序。结果参见文件“透视表样例.png”。

7. 将“透视分析”工作表标签颜色设为标准紫色，并移动到“产品销售汇总表”的右侧。

对 应 步 骤

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

2、题中要求产品信息表添加一二季度对应的单价，我们可以利用型号查询对应的单价即可。

用vlookup查找函数即可，

vlookup函数参数(查找的数据，查找的范围，返回的值在范围的第几列，精确查找写0)

查找的数据为产品型号：

查找的范围为B2:C21(在这里可能有同学会把A列也选上，这是不对的，vlookup函数的限制是查找的数据必须位于查找范围的第一列，比如我们查找的是产品型号，那么产品型号必须位于范围的第一列。)

最后返回的单价值在范围的第2列，所以输入2，精确查找就写0

结果如下：=VLOOKUP(B2,产品基本信息表!$B$2:$C$21,2,0)

最后销售额=销量*单价

二季度表方法和上面一样，结果如图。

最后不要忘记把这两个表的单价和销售额的列数据，格式设置成保留两位小数的数值，勾选使用千位分隔符

3、如图，求一季度各个产品的销量总和。用条件求和函数即可，条件为产品型号。

条件求和函数sumif，sumif函数参数(条件区域，条件，求和区域)

条件区域为各个产品型号的区域，区域是固定不变的需要锁定

条件为产品型号，

求和区域为销量区域，区域是固定不变的需要锁定

最终结果如图：

=SUMIF(一季度销售情况表!$B$2:$B$44,产品销售汇总表!B2,一季度销售情况表!$D$2:$D$44)

同理一季度的销售额也这么求，不过求和区域不是销售量，而是一季度表中销售额的一列

同理，二季度和二季度的销售额也用这个方法，用二季度表的数据做即可，结果如图：

一二季度销售总量=一季度销量+二季度销量

一二季度销售总额=一季度销售额+二季度销售额

最后按ctrl键选中销售额的数据，右击设置单元格格式→数值，0位小数，勾选使用千位分隔符，并且右对齐

4、排名用rank排名函数即可

rank函数参数(排名的数据，排名的区域)

排名的区域是固定不变的需要锁定

设置条件格式的题目要求是前三名是红色字体，后3名是绿色字体。

但是如果我们直接去设置的话，前三名不是1，2，3，而是最大的数字如18,19,20

所以我们需要反着设置。

选中排名所在的区域→条件格式→项目选取规则→后10项

改为后3项→自定义格式为字体下的标准红色→确定

同理，还是选中排名的区域→条件格式→项目选取规则→前10项→改为前3项→自定义格式为字体下的标准绿色

5、选中A1：I21区域，套用表格样式，表包含标题确定，随便选择一个样式即可,

切换到数据选项卡，保存筛选标记不选中，如果选中单击一下即可

6、光标定位于产品销售汇总表的任意一个单元格，插入→数据透视表

然后重命名此表为透视分析

行标签是产品类别代码，值为一季度销售额，二季度销售额，一二季度销售总额

光标定位销售总额的单元格→开始选项卡→排序和降序→降序

选择数据透视表的数据区域→右击设置单元格格式→数值，0位小数，勾选使用千位分隔符

选中D3单元格，在编辑栏内更改名字为两个季度销售总额

最后把透视分析表移动到产品销售汇总表的右侧，右击更改标签颜色为紫色

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。