Excel函数运算习题16

某公司销售部负责人大华计划对公司前两个季度的产品销售情况进行统计，并按照以下要求帮助大华完成统计：

1.在考生文件夹下，保存“Excel材料。xlsx“文件为”excel。xlsx”(")。xlsx "是扩展名)，所有后续操作都基于此文件，否则，不会给出分数。

2.参考“产品基本信息表”中的列表，使用公式或函数分别填写“第一季度销售表”和“第二季度销售表”中每类产品对应的单价，计算月销售额并填写在f栏，单价和销售额为数值，保留两位小数，使用千位分隔符。(注意：不要更改这两个工作表中的数据顺序。)

3.在“产品销售汇总表”中，分别计算第一季度和第二季度各型号产品的销量、销量和总数量，并填写相应的栏目。所有销售都是数字的，小数位数为0，有数千个分隔符，并且右对齐。

4.在“产品销售汇总表”中，在不改变原始数据顺序的情况下，按照第一、二季度销售总额由高到低进行销售排名，并在第一栏填写相应单元格.用标准红色和标准绿色分别标记前3名产品和后3名产品。

5.将表格格式应用于“产品销售汇总表”的数据区A1:I21，包括表格标题，并删除列标题行的过滤标记。

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

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

循序渐进。

1.复制Excel素材，粘贴一份，重命名为Excel。输入无扩展名，只需打开Excel即可做题。

2.需要在产品信息表中添加第一、二季度对应的单价。我们可以用模型来查询相应的单价。

使用vlookup查找函数，

vlookup函数的参数(搜索数据，搜索范围，返回值的哪一列在范围内，写0表示精确搜索)。

搜索数据是产品模型：

搜索范围为B2:C21(这里可能会有同学也会选择a列，这是错误的。vlookup函数的限制是要搜索的数据必须在搜索范围的第一列。例如，如果我们正在寻找产品模型，产品模型必须在范围的第一列。)

最终返回的单个值位于该范围的第二列，因此输入2，并写入0进行精确搜索。

结果如下：=VLOOKUP(B2，基本产品信息表！2:加元(21，2，0加元)

最终销售额=销量*单价。

第二个四分之一表格的方法同上，结果如图。

最后，别忘了设置这两张表的单价和销售金额的列数据格式，保留两个小数值，勾选使用千位分隔符。

3.如图所示，找出第一季度每种产品的总销量。使用条件和函数，条件是乘积模型。

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

条件是每个产品型号的区域，区域是固定的，需要锁定。

条件是产品型号。

求和区域是销量区域，区域是固定的，需要锁定。

最终结果如下：

=SUMIF(第一季度销售表！$B$2:$B$44，产品销售汇总表！B2，第一季度销售表！2:美元(44美元)

同样，第一季度的销售额也是一样的，但是求和的区域不是销售额，而是第一季度表中的一列销售额。

同样，利用二季度表中的数据可以做二季度和二季度的销售，结果如下：

第一季度总销售额=f销售额

第一季度总销售额=第一季度销售额。

最后按ctrl选择销售数据，右键设置单元格格式数值，0小数位，勾选使用千位分隔符，右对齐。

4.排名排名功能可用于排名。

排名函数的参数(排名数据、排名区域)。

排名区是固定的，需要锁定。

条件格式主题要求前三位是红色字体，后三位是绿色字体。

但如果我们直接设置，前三不是1、2、3，而是最大的数字，比如18、19、20。

所以我们需要把它倒过来。

选定区域条件格式项目选择规则最后10项。

更改为最后3项字体下自定义格式为标准红色确定。

同理，选择排名区域条件格式项目选择规则前10项改为前3项字体下自定义格式为标准绿色。

5.选择A1: I21区域，应用表格样式，确认表格标题，选择样式即可。

切换到数据选项卡，保存过滤器标记未选中。如果选中，请单击一次。

6.将光标定位在产品销售汇总表的任意单元格中，然后插入透视表。

然后将该表重命名为透视分析。

该行是产品类别代码，值是第一季度的销售额、第二季度的销售额以及第一和第二季度的总销售额。

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

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

选择单元格D3，将编辑栏中的名称改为两个季度的销售总额。

最后，将透视分析表移动到产品销售汇总表右侧，右键将标签颜色改为紫色。