用活Excel函数 先进先出计算更简单

俞木发

1. 问题分析

下表是公司产品的库存统计。现在需要根据今日出库数量，统计出实时库存和今日销售金额（图1）。根据“先进先出”的原则，我们在每次发货时需要根据本次发货数量和累计入库数量进行核对，如果发货数小于截止到当期的累计入库数，那么实时库存就是“累计入库-出库数”，销售额为“出库数*入库价格”。但是，如果发货数大于截止到当期的累计入库数，那么就需要从下一批次入库数中提取部分补充出库（以此类推），这样实时库存数和销售额就要分开计算。显然，解决问题的方法就是先统计累计库存，然后使用条件函数判断，最后分批计算销售金额。

2. 统计累计库存数

在A 列前插入“入库累计”“今日出库”“入库数量”“ 实时库存”“产品各批次出库额”5个新列，接着在A 2单元格中输入公式“=SUMIF（F$2：F2，F2，H$2：H2）”并下拉填充，即可得到所有产品的入库累计数量（图2）。

公式解释：这里使用SUMIF函数进行条件求和，其中F$2：F2作为求和条件区域，求和条件是F2的产品名称，H$2：H2是实际求和区域。

3. 引用今日出库数

量定位到B2单元格，输入公式“=IFERROR（VLOOKUP（F：F，$L$2：$M$7，2，0），0）”并下拉填充，将当天出库的数量引用到B列中，将其和A列中的累计入库数量进行比较（图3）。

公式解释：这里使用VLOOKUP函数对今日出库数量进行引用，其中F：F是要匹配的产品，查找的区域是$L$2：$M$7，即今日出库的产品列表。最后在外层嵌套IFERROR函数进行判断，没有引用值时显示为“0”，否则显示引用值。

4. 引用入库数量和计算实时库存

定位到C2单元格，输入公式“=H2”并下拉填充;定位到D2单元格，输入公式“=IFS（A2<=B2，0，A2>B2，A2-B2）”并下拉填充。这样通过条件函數的比较可以获得实时库存的数据（图4）。

公式解释：这里使用I FS函数进行多条件的判断，如果A2<=B2，即累计入库的数量小于等于当天的出库数，也就是截止到当日累计入库的数量不够当日的出库，这时实时库存就是“0”。如果A 2>B2，即累计入库数比当天出库大，实时库存数就是“累计入库数-当日出库”。

5. 统计当日各批次产品的出库金额

由于不同批次的产品价格不同，在累计库存不足需要提取下一批次的数量时，需要对不同批次数量分别统计。定位到E2单元格，输入公式“=IFS（B2= 0，0，D2= 0，C2 *I2，C2-D2<=0，0，AND（A2>B2，C2-D2>0），（C2-D2）*I2）”并下拉填充（图5）。

公式解释：这里使用IFS函数进行多条件判断，当B2= 0时，即表示当天没有出库;当D2=0时，即表示这一批次全部出库，销售额=“入库数*入库单价”;当C2-D2<=0时，即表示累计库存小于等于实时库存，本批次没有出库，即销售额也为0。当A2>B2且C2-D2>0时，即表示累计库存数大于出库数，且累计库存大于实时库存，表示这批次有部分出库销售，这时“销售额=（当日出库-实时库存）*入库单价”。

6. 引用去重产品数据

当日出库的产品都是从F列中提取，但是F列中入库的产品名称会有重复（不同日期入库同一产品），所以需要去重。选中F2：F10数据区域，复制后粘贴到L 2：L10区域，选中L2：L10区域并依次点击“数据→去除重复数据→以当前选定的区域排序”即可。

7. 统计当天产品的总计金额

完成各批次产品的出库金额统计后，定位到N2单元格，输入公式“=SUMIF（$F$2：$F$10，L2，$E$2：$E$10）”并下拉填充。

公式解释：这里使用SUMI F函数进行条件求和，其中$F$2：$F$10作为求和条件区域，求和条件是L 2的产品名称，$E$2：$E$10是实际求和区域。

8. 实际使用

完成上述的设置后，选中A：E列并将其隐藏。定位到J2单元格，输入公式“=D2”并下拉填充，这样每天需要出库时，只要在L、M列中输入当天的出库产品和出库数量，J列和N列中就会自动显示实时库存和销售金额的数据了（图6）。