第27关 综合实战应用2

第26关我们运用数据整理汇总、透视表制作、图表制作等功能，巩固了一遍看板汇报的全部流程。

今天我们要通过数据处理和函数公式运用，完成某公司11月库存表。

根据已有的【采购表】和【销售表】内容，我们需要完成【库存表】。最终效果如下：

1.制表提示

第一步：完成【采购表】

1.缺失的【采购日期】处理：批量填充空值

2.【采购金额】：采购数量*单价

3.【最低单价】：min函数与if函数嵌套，Ctrl+Shift+Enter生成数组公式

4.【供应商】：Index+match函数嵌套

结果如下：

第二步：完成【销售表】

1.【单价】：vlookup函数

2.【销售金额】：销售数量*单价

计算结果如下：

第三步：根据前面的数据完成【库存表】

1.本月采购的【数量】、【金额】：sumif函数

2.本月销售的【数量】、【金额】：sumifs函数

3.期末库存的【数量】：期初库存+本月采购数量-本月销售数量

4.【采购平均价格】：本月采购金额/本月采购数量

5.【存货占用资金】：数量*采购平均价格

6.【库存状态】：if函数+条件格式

库存表也就做好了：

2. 步骤详解

上面给出了主要步骤的核心提示。如果你还没有做出来，也不用着急，学到的知识点要真正实现落地，必须要经过多次实操，才能熟练使用。

接下来我们就来具体拆解一下制作步骤，同学需要参考操作步骤，重点回顾一下刚刚自己做不出来的地方。

第一步，我们来完成【采购表】:

“批量填充空值”

在拿到数据源的时候，第一步肯定是整理数据源，看看有没有明显的错漏。很明显，在【采购日期】一栏出现了空白单元格。这里我们可以批量填充空值。步骤如下：

然后用快捷键组合：首先按【“=”+“向上键”】，然后按【“Ctrl”+“Enter”】，空白的单元格就都填充完成了！

“min函数+if函数嵌套”

【采购金额】的计算很简单我们就不展开了，我们重点来讲【最低单价】的公式。

要求出【最低单价】需要用到两个函数，首先用【IF函数】找出B列所有的投影仪，然后用【min函数】求出投影仪的最低单价。

要特别注意：【IF函数】只能返回单个数值，而这里的投影仪有多个，所以我们需要用到数组的概念。

具体操作很简单，在输入公式的时候同时按住【Ctrl+Shift+Enter】就可以了：

在运用函数时，因为产品采购表的第一行单元格合并，会出现无法单独选中某一列的情况：

遇到这种情况有2种解决方式：

1.手动输入

2.取消合并单元格，设置为跨列居中，再输入函数

“Index+match函数嵌套”

紧接着我们来计算提供最低单价的供应商，思路是先用match函数找到对应的货品名称和最低单价在产品采购表中所在的行，再嵌套进**index函数中找到提供最低价产品的供应商所在单元格，返回名称即可：

第二步，完成【销售表】:

计算【单价】用到的是vlookup函数：

算出单价后，用单价*销售数量，销售金额就出来了。

第三步：根据前面的数据完成【库存表】

“Sumif函数”

用Sumif函数可以先计算出【本月采购数量】和【本月采购金额】：

“Sumifs函数”

然后用Sumifs函数计算出【本月销售数量】和【本月销售金额】。

注意：在产品销售表中，包含了10月和11月的数据，而我们只需要11月的数据。

“if函数”

算出前面的数据后，我们就可以用【IF函数】备注存货情况了。

“条件格式”

所有的公式填写好后，最后我们为“备注”设置警示，方便拿到表格的人第一眼就看到重点信息。操作如下：

课程总结

这一关，我们通过数据处理和函数公式运用，完成某公司11月库存表。恭喜所有完成作业的同学，已经熟练掌握了Excel中最难的函数部分！

复制这个链接打开后可以领取到价值99元的函数礼包！ 链接:https://pan.baidu.com/s/1jF92XMouNrt5b5C_MRtfvg 密码:cb4s

下关预告

下一关我们将进入办公综合实战3。

作为本次课程最后一关，我们需要制作一份完整的公司年度费用分析看板！在这个过程中，需要你重点回忆数据汇总、看板制作等的知识点，真正把所学知识用于实际场景。你准备好了吗？